Home » RDBMS Server » Performance Tuning » Ideas for fixing plan cardinality error
Ideas for fixing plan cardinality error [message #583423] Tue, 30 April 2013 11:59 Go to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Databaser version is 9.2.0.8.0 - 64bit

From this plan excerpt please note that after filtering the HASH JOIN step (15) believes it will have only one row. I believe this is because of the many filter predicates applied after the join is done. Or it might be due to a bug: can someone comfirm? The actual cardinality is 400K rows since only a few rows are eliminated from CLAIM_EVENT_HISTORY during the additional join/filtering. Unfortunately the rest of the query plan is lots of nested loops that think they are looping over one row.

Anyone have a fix for getting the HASH JOIN step to recognize the correct row count in a 9.2.0.8 database?

Got ideas on what to do with this?

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  |  Name                    | Rows  | Bytes |TempSpc| Cost  |
---------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                           |                          |     1 |   902 |       | 26684 |
|   1 |  LOAD AS SELECT                            |                          |       |       |       |       |
...


|* 15 |                HASH JOIN                   |                          |     1 |   312 |    19M| 26655 |
|* 16 |                 TABLE ACCESS FULL          | CLAIM_EVENT_HISTORY      |   413K|    14M|       |   729 |
|* 17 |                 TABLE ACCESS FULL          | CL_HISTORY               |  1931K|   506M|       | 17194 |
...

---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

...

  15 - access("SYS_ALIAS_1"."RSRVID"="SYS_ALIAS_2"."RSRVID")
       filter(CASE "SYS_ALIAS_2"."SRC_SYSTEM" WHEN 'CMS' THEN DECODE("SYS_ALIAS_1"."CO_CODE",'0','1','2') WHEN
              'CNA' THEN DECODE("SYS_ALIAS_1"."CO_CODE",NULL,'51','CNA','51',"SYS_ALIAS_1"."CO_CODE") WHEN 'DIRCT' THEN
              NVL("SYS_ALIAS_1"."CO_CODE",'2') WHEN 'PL' THEN NVL("SYS_ALIAS_1"."CO_CODE",'4') ELSE "SYS_ALIAS_1"."CO_CODE"
              END <>'46' AND CASE "SYS_ALIAS_2"."SRC_SYSTEM" WHEN 'CMS' THEN DECODE("SYS_ALIAS_1"."CO_CODE",'0','1','2')
              WHEN 'CNA' THEN DECODE("SYS_ALIAS_1"."CO_CODE",NULL,'51','CNA','51',"SYS_ALIAS_1"."CO_CODE") WHEN 'DIRCT' THEN
              NVL("SYS_ALIAS_1"."CO_CODE",'2') WHEN 'PL' THEN NVL("SYS_ALIAS_1"."CO_CODE",'4') ELSE "SYS_ALIAS_1"."CO_CODE"
              END <>'51' AND CASE "SYS_ALIAS_2"."SRC_SYSTEM" WHEN 'CMS' THEN DECODE("SYS_ALIAS_1"."CO_CODE",'0','1','2')
              WHEN 'CNA' THEN DECODE("SYS_ALIAS_1"."CO_CODE",NULL,'51','CNA','51',"SYS_ALIAS_1"."CO_CODE") WHEN 'DIRCT' THEN
              NVL("SYS_ALIAS_1"."CO_CODE",'2') WHEN 'PL' THEN NVL("SYS_ALIAS_1"."CO_CODE",'4') ELSE "SYS_ALIAS_1"."CO_CODE"
              END <>'52')
...


Kevin

[Updated on: Tue, 30 April 2013 12:01]

Report message to a moderator

Re: Ideas for fixing plan cardinality error [message #583448 is a reply to message #583423] Wed, 01 May 2013 02:28 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
With the following caveats:

*This may be an undesirable approach or cause more issues than it solves...
*This is 11.2.0.3 with optimizer_features_enable='9.2.0'. I dont have a 9.2 DB handy I can play with, hopefully this is close enough.

The closest I could get was something like this:


A = CTAS * from dba_objects
B = CTAS * from dba_tables

  1  with bad_card as(
  2  select /*+ materialize cardinality(1500) */ * from
  3  a,b
  4  where a.object_name=b.table_name)
  5  select
  6  a.*
  7  from
  8  a,bad_card c,b
  9  where a.object_id=c.object_id
 10  and a.object_name=b.table_name
 11* and b.owner = 'SYSTEM'
08:21:35 SQL> /


Execution Plan
----------------------------------------------------------
Plan hash value: 656351396

--------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                        | Rows  | Bytes |TempSpc| Cost  |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             |   480 |   119K|       |   645 |
|   1 |  TEMP TABLE TRANSFORMATION |                             |       |       |       |       |
|   2 |   LOAD AS SELECT           | SYS_TEMP_0FD9D673C_B51E02D2 |       |       |       |       |
|*  3 |    HASH JOIN               |                             | 91155 |    61M|  5016K|   504 |
|   4 |     TABLE ACCESS FULL      | B                           |  9965 |  4894K|       |    13 |
|   5 |     TABLE ACCESS FULL      | A                           | 91155 |    17M|       |   109 |
|*  6 |   HASH JOIN                |                             |   480 |   119K|       |   141 |
|   7 |    VIEW                    |                             |  1500 | 19500 |       |    16 |
|   8 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D673C_B51E02D2 |  1500 |  1040K|       |    16 |
|*  9 |    HASH JOIN               |                             |   912 |   214K|       |   124 |
|* 10 |     TABLE ACCESS FULL      | B                           |   100 |  3400 |       |    13 |
|  11 |     TABLE ACCESS FULL      | A                           | 91155 |    17M|       |   109 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("A"."OBJECT_NAME"="B"."TABLE_NAME")
   6 - access("A"."OBJECT_ID"="C"."OBJECT_ID")
   9 - access("A"."OBJECT_NAME"="B"."TABLE_NAME")
  10 - filter("B"."OWNER"='SYSTEM')

Note
-----
   - cpu costing is off (consider enabling it)





It doesnt direct alter the hash join output cardinality but it does take the hint into the materialized temp table (which should then cascade up), perhaps it may help you? Without the system it's manifesting on, it's hard to test (at least quickly). Hopefully it is of some assistance though.

[Updated on: Wed, 01 May 2013 02:29]

Report message to a moderator

Re: Ideas for fixing plan cardinality error [message #583468 is a reply to message #583448] Wed, 01 May 2013 07:39 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Thanks, it never occured to me to use CARDINALITY in that way (at a query level). I will see if I can work it in.

I will also be taking a whack at using DYNAMIC SAMPLING as well.

Kevin
Re: Ideas for fixing plan cardinality error [message #583471 is a reply to message #583468] Wed, 01 May 2013 07:51 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Be interesting to see if it works.

I had originally been overruling it more directly using qb_names, but iirc that doesnt work in 9i, at least for directing hints - you can name the blocks but I'm sure its just a readability feature at that version.

There was a reason I hadn't mentioned dynamic sampling, I did consider it...but I'm damned if I can remember why I discounted it. I suspect because you cant apply it to the join itself or its outputs (unless you can shoehorn it into a materialized subfactoring) therefore whatever is making the optimizer freak out is liable to still do so. In fact as I ramble, I think maybe I just couldnt get it to take in the right place - but that may be down to the simplicity of my test.


Purely for science, what happens if you force a SM join, are the estimates still bad? I wonder if it's the hash itself.
Re: Ideas for fixing plan cardinality error [message #583474 is a reply to message #583471] Wed, 01 May 2013 08:08 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
1) I have tried using cardinality hints for some of the tables in a view that is part of the problem. I used cardinality on these tables to raise the number or rows returned from the table to abnormally high numbers and this in turn results in the JOIN between these two producting a number that is about 1/2 the correct number of rows. This changes the plan. However, the results plan depends upon other factors, mainly memory management. If I use WORKAREA_SIZE_POLICY=MANUAL and set the hash and sort sizes to 2,000,000,000 (slightly less than 2GB) then I get a sort/merge heavy plan. This plan removes about 80% of the time to complete the query. Unfortunately I have not yet determined if I like this method or if I would even be allowed to use it as our policy is always set to AUTO as a rule for our systems.

2) I am currently testing DYNAMIC SAMPLING with a value set to 10. This will cause the query to do 100% sampling and thus I figure tell me what Oracle thinks will be the best plan. From there I can determine if sampling offers any hope at all. RESULTS ARE JUST IN. No help at all. Same plan almost identical numbers for all cardinalities.

3) I have also tried using a SORT/MERGE join but as expected, there was no difference. But as expected this did not fix anything either. The cardinalit estimate after the sort/merge join was also 1. To reiterate, the issue is not the join method chosen, but rather the string of nested loops that results what the cardinality estimate of the join is 1 row. Thus the problem appears to me the calculation Oracle uses when faced with multiple predicates used to filter after joining rows.

Maybe I can figure out a way to filter before joining or to filter late.

Thanks very much. Kevin
Re: Ideas for fixing plan cardinality error [message #583475 is a reply to message #583474] Wed, 01 May 2013 08:09 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
FYI here is the output piece of the QP. Note how the join method does not matter. The cardinality estimate is still 1 which ripples through the rest of the plan causing a lot of NL.

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                        |  Name                    | Rows  | Bytes |TempSpc| Cost  |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                 |                          |     1 |   356 |       | 40191 |
|*  1 |  FILTER                                          |                          |       |       |       |       |
|*  2 |   TABLE ACCESS BY INDEX ROWID                    | CLAIM_EVENT_HISTORY      |     1 |    24 |       |     4 |
|*  3 |    INDEX RANGE SCAN                              | ICLAIM_EVENT_HISTORY1    |     1 |       |       |     3 |
|   4 |   SORT AGGREGATE                                 |                          |     1 |     8 |       |       |
|*  5 |    TABLE ACCESS BY INDEX ROWID                   | CLAIM_EVENT_HISTORY      |     1 |     8 |       |     4 |
|*  6 |     INDEX RANGE SCAN                             | ICLAIM_EVENT_HISTORY1    |     1 |       |       |     3 |
|   7 |  SORT GROUP BY                                   |                          |     1 |   356 |       | 40189 |
|*  8 |   FILTER                                         |                          |       |       |       |       |
|   9 |    TABLE ACCESS BY INDEX ROWID                   | VORTEX_RESERVE_CALC      |     1 |    31 |       |     4 |
|  10 |     NESTED LOOPS                                 |                          |     1 |   356 |       | 40183 |
|  11 |      NESTED LOOPS                                |                          |     1 |   325 |       | 40179 |
|  12 |       NESTED LOOPS                               |                          |     1 |   312 |       | 40176 |
|  13 |        NESTED LOOPS                              |                          |     1 |   301 |       | 40175 |
|  14 |         NESTED LOOPS                             |                          |     1 |   290 |       | 40173 |
|  15 |          NESTED LOOPS                            |                          |     1 |   254 |       | 40167 |
|  16 |           NESTED LOOPS                           |                          |     1 |   224 |       | 40163 |
|  17 |            NESTED LOOPS OUTER                    |                          |     1 |   218 |       | 40162 |
|  18 |             NESTED LOOPS                         |                          |     1 |   175 |       | 40160 |
|  19 |              NESTED LOOPS OUTER                  |                          |     1 |   145 |       | 40096 |
|  20 |               NESTED LOOPS                       |                          |     1 |   137 |       | 40093 |
|* 21 |                FILTER                            |                          |       |       |       |       |
|* 22 |                 HASH JOIN OUTER                  |                          |       |       |       |       |
|  23 |                  NESTED LOOPS                    |                          |     1 |    89 |       | 40089 |
|  24 |                   NESTED LOOPS                   |                          |     1 |    69 |       | 40087 |
|* 25 |                    INDEX UNIQUE SCAN             | URSRVVALDTL1             |     1 |     8 |       |       |
|  26 |                    VIEW                          |                          |     1 |    61 |       | 40086 |
|* 27 |                     FILTER                       |                          |       |       |       |       |
|  28 |                      MERGE JOIN                  |                          |     1 |    86 |       | 40082 |
|  29 |                       SORT JOIN                  |                          |   413K|  9681K|    28M|  2750 |
|* 30 |                        TABLE ACCESS FULL         | CLAIM_EVENT_HISTORY      |   413K|  9681K|       |   729 |
|* 31 |                       FILTER                     |                          |       |       |       |       |
|* 32 |                        SORT JOIN                 |                          |       |       |       |       |
|* 33 |                         TABLE ACCESS FULL        | CL_HISTORY               |  1931K|   114M|       | 17194 |
|  34 |                      SORT AGGREGATE              |                          |     1 |     8 |       |       |
|* 35 |                       TABLE ACCESS BY INDEX ROWID| CLAIM_EVENT_HISTORY      |     1 |     8 |       |     4 |
|* 36 |                        INDEX RANGE SCAN          | ICLAIM_EVENT_HISTORY1    |     1 |       |       |     3 |
|* 37 |                   TABLE ACCESS FULL              | RSRV_INTEREST_RATES      |     1 |    20 |       |     2 |
|  38 |                  TABLE ACCESS FULL               | RSRV_BUYOUT_RANGE        |    50 |  1000 |       |     2 |
|* 39 |                TABLE ACCESS BY INDEX ROWID       | RSRV_POLICY_SEGMENT      |     1 |    28 |       |     1 |
|* 40 |                 INDEX UNIQUE SCAN                | UPOLC_SEGMENT_ID1        |     1 |       |       |       |
|* 41 |               TABLE ACCESS BY INDEX ROWID        | CLAIM_EVENT_HISTORY      |     1 |     8 |       |     3 |
|* 42 |                INDEX RANGE SCAN                  | ICLAIM_EVENT_HISTORY1    |     1 |       |       |     2 |
|* 43 |              TABLE ACCESS BY INDEX ROWID         | VORTEX_RESERVE_CLM       |     1 |    30 |       |    64 |
|* 44 |               INDEX RANGE SCAN                   | IVORTEX_RESERVE_CLM1     |     1 |       |       |     2 |
|* 45 |             TABLE ACCESS BY INDEX ROWID          | POLICY_ELIM_PERIOD       |     1 |    43 |       |     2 |
|* 46 |              INDEX RANGE SCAN                    | UX_POLICY_ELIM_PERIOD_2  |     1 |       |       |     1 |
|* 47 |            INDEX UNIQUE SCAN                     | PKCLAIM_RESERVE          |     1 |     6 |       |     1 |
|* 48 |           TABLE ACCESS BY INDEX ROWID            | CL_BENEFIT               |     4 |   120 |       |     4 |
|* 49 |            INDEX RANGE SCAN                      | UICL_BENEFIT1            |     4 |       |       |     2 |
|* 50 |          TABLE ACCESS BY INDEX ROWID             | CL_STATUS                |     1 |    36 |       |     6 |
|* 51 |           INDEX RANGE SCAN                       | ICL_STATUS1              |     9 |       |       |     2 |
|* 52 |         TABLE ACCESS FULL                        | RSRV_ICD9_GROUPS         |     1 |    11 |       |     2 |
|* 53 |        TABLE ACCESS BY INDEX ROWID               | RSRV_ICD9_CATEGORY       |     1 |    11 |       |     1 |
|* 54 |         INDEX RANGE SCAN                         | IX_RSRV_ICD9_CATEGORY_1  |    10 |       |       |       |
|* 55 |       INDEX RANGE SCAN                           | IVORTEX_RESERVE_CALC1    |     1 |    13 |       |     3 |
|* 56 |      INDEX RANGE SCAN                            | IVORTEX_RESERVE_CALC1    |     1 |       |       |     3 |
|* 57 |    TABLE ACCESS FULL                             | POLICY_EXCEPTION         |     1 |    13 |       |     2 |
---------------------------------------------------------------------------------------------------------------------

[Updated on: Wed, 01 May 2013 08:10]

Report message to a moderator

Re: Ideas for fixing plan cardinality error [message #583476 is a reply to message #583475] Wed, 01 May 2013 08:15 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
I seem to recall J.Lewis wrote about this in his book on Cost Based Fundamentals. I think I will go read that part again.
Re: Ideas for fixing plan cardinality error [message #583477 is a reply to message #583475] Wed, 01 May 2013 08:26 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
This is a bit of a brain dump coming:

*Did you try the cardinality in conjunction with a materalize hint in a subfactored block? Wouldnt work worth a damn without it for me.

*Can you hint it to death and apply an outline with it? I'm sure you're well aware of the limitations here but it strikes me as something which might actually be a good fit.

*I hadn't high hopes for the SM - but at least it rules it out.

*What about pushing it into a subquery with the predicates applied later and an no_unnest hint? I have a feeling that will only delay the estimate of 1 coming out, rather than correct it however.

*I assume there's not the desire/flexibility to create an MV around this and force the thing to get it right?




I'm still partway through that book. Too much reading, too little time. Embarassed



Edit: I assume a 10053 trace didnt shed any (useful) light on it's thought process?

[Updated on: Wed, 01 May 2013 08:28]

Report message to a moderator

Re: Ideas for fixing plan cardinality error [message #583479 is a reply to message #583477] Wed, 01 May 2013 08:42 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
I first admit that I an not a TRACE guy. I feel trace is a bad tool. Not because it does not provide great information, but rather because it cannot be used by the masses to do tuning. You need access to the host to use it. If oracle would create a set of privileges and views in the database that allowed us to manipulate trace data without writing files to the host or needed access to the use (think autotrace and explain plan), then it would be a tool I would use.

Also good news. I put a CARDINALITY (500000) hint into the section of the view that contained the problem. This is the correct number of rows returned from this subquery. As expected it did not change the estimate of 1 row for the join between these two tables. However, it did change the expected number of rows from the nested select that does the join. When the view is combined in the larger query this cases the query plan to change dramatically and use a different driving table and use lots of sort/merge instead of nested loops. The runtime is 30% of the original which though maybe not game changing I believe will be acceptable to the user requesting the tuning.

            LTD_STD_IND
     --CO28943 T803 End
     FROM            (SELECT   /*+ cardinality (500000) */ CL_HISTORY_ID,
                               CL_HISTORY.RSRVID,
                               LAST_NAME,


I will now check on some other features like index compression/manual workareas/partitioning/pq to see if there is any other significant benefit that might be worth going after but just this one change is one I can work with. I feel it is reasonable for this view given the way it is used elsewhere so the hint should throw other queries off (will test that of coures).

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                              |  Name                  | Rows  | Bytes |TempSpc| Cost  |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                       |                        |    21 |  7476 |       |   531K|
|*  1 |  FILTER                                                |                        |       |       |       |       |
|*  2 |   TABLE ACCESS BY INDEX ROWID                          | CLAIM_EVENT_HISTORY    |     1 |    24 |       |     4 |
|*  3 |    INDEX RANGE SCAN                                    | ICLAIM_EVENT_HISTORY1  |     1 |       |       |     3 |
|   4 |   SORT AGGREGATE                                       |                        |     1 |     8 |       |       |
|*  5 |    TABLE ACCESS BY INDEX ROWID                         | CLAIM_EVENT_HISTORY    |     1 |     8 |       |     4 |
|*  6 |     INDEX RANGE SCAN                                   | ICLAIM_EVENT_HISTORY1  |     1 |       |       |     3 |
|   7 |  SORT GROUP BY                                         |                        |    21 |  7476 |       |   531K|
|*  8 |   FILTER                                               |                        |       |       |       |       |
|*  9 |    FILTER                                              |                        |       |       |       |       |
|  10 |     MERGE JOIN OUTER                                   |                        |       |       |       |       |
|  11 |      SORT JOIN                                         |                        |    21 |  7056 |       |   531K|
|  12 |       MERGE JOIN OUTER                                 |                        |    21 |  7056 |       |   531K|
|  13 |        SORT JOIN                                       |                        |    21 |  6153 |       |   531K|
|  14 |         MERGE JOIN OUTER                               |                        |    21 |  6153 |       |   531K|
|  15 |          MERGE JOIN                                    |                        |    21 |  5985 |       |   530K|
|  16 |           MERGE JOIN                                   |                        |   112 | 28560 |       |   512K|
|  17 |            SORT JOIN                                   |                        |   112 | 27888 |       |   511K|
|  18 |             MERGE JOIN                                 |                        |   112 | 27888 |       |   510K|
|  19 |              SORT JOIN                                 |                        |   113 | 24973 |       |   510K|
|  20 |               MERGE JOIN                               |                        |   113 | 24973 |       |   510K|
|  21 |                SORT JOIN                               |                        |   135 | 28350 |       |   510K|
|  22 |                 MERGE JOIN                             |                        |   135 | 28350 |       |   510K|
|  23 |                  SORT JOIN                             |                        |  1502 |   291K|   664K|   510K|
|  24 |                   MERGE JOIN                           |                        |  1502 |   291K|       |   510K|
|  25 |                    SORT JOIN                           |                        |  3432 |   546K|  1208K|   475K|
|  26 |                     MERGE JOIN                         |                        |  3432 |   546K|       |   475K|
|  27 |                      SORT JOIN                         |                        |   102K|    14M|    34M|   475K|
|  28 |                       MERGE JOIN                       |                        |   102K|    14M|       |   472K|
|  29 |                        SORT JOIN                       |                        |   102K|  8245K|    19M|   447K|
|  30 |                         TABLE ACCESS BY INDEX ROWID    | VORTEX_RESERVE_CALC    |     1 |    31 |       |     4 |
|  31 |                          NESTED LOOPS                  |                        |   102K|  8245K|       |   445K|
|* 32 |                           HASH JOIN                    |                        | 84785 |  4222K|  3416K|   106K|
|  33 |                            NESTED LOOPS                |                        | 69811 |  2590K|       |  2932 |
|* 34 |                             INDEX UNIQUE SCAN          | URSRVVALDTL1           |     1 |     8 |       |       |
|* 35 |                             TABLE ACCESS BY INDEX ROWID| VORTEX_RESERVE_CLM     | 69811 |  2045K|       |  2931 |
|* 36 |                              INDEX RANGE SCAN          | IVORTEX_RESERVE_CLM2   |   139K|       |       |   560 |
|* 37 |                            INDEX FAST FULL SCAN        | IVORTEX_RESERVE_CALC1  |  5557K|    68M|       |   101K|
|* 38 |                           INDEX RANGE SCAN             | IVORTEX_RESERVE_CALC1  |     1 |       |       |     3 |
|* 39 |                        SORT JOIN                       |                        |   500K|    29M|    88M| 25471 |
|  40 |                         VIEW                           |                        |   500K|    29M|       |  2020K|
|* 41 |                          FILTER                        |                        |       |       |       |       |
|* 42 |                           HASH JOIN                    |                        |     1 |    86 |    14M| 20322 |
|* 43 |                            TABLE ACCESS FULL           | CLAIM_EVENT_HISTORY    |   413K|  9681K|       |   729 |
|* 44 |                            TABLE ACCESS FULL           | CL_HISTORY             |  1931K|   114M|       | 17194 |
|  45 |                           SORT AGGREGATE               |                        |     1 |     8 |       |       |
|* 46 |                            TABLE ACCESS BY INDEX ROWID | CLAIM_EVENT_HISTORY    |     1 |     8 |       |     4 |
|* 47 |                             INDEX RANGE SCAN           | ICLAIM_EVENT_HISTORY1  |     1 |       |       |     3 |
|* 48 |                      FILTER                            |                        |       |       |       |       |
|* 49 |                       SORT JOIN                        |                        |       |       |       |       |
|* 50 |                        TABLE ACCESS FULL               | RSRV_INTEREST_RATES    |    13 |   260 |       |     2 |
|* 51 |                    FILTER                              |                        |       |       |       |       |
|* 52 |                     SORT JOIN                          |                        |       |       |       |       |
|* 53 |                      TABLE ACCESS FULL                 | CL_STATUS              |  4375K|   150M|       |  6907 |
|* 54 |                  FILTER                                |                        |       |       |       |       |
|* 55 |                   SORT JOIN                            |                        |       |       |       |       |
|  56 |                    TABLE ACCESS FULL                   | RSRV_ICD9_GROUPS       |    36 |   396 |       |     2 |
|* 57 |                SORT JOIN                               |                        |    20 |   220 |       |     8 |
|* 58 |                 TABLE ACCESS FULL                      | RSRV_ICD9_CATEGORY     |    20 |   220 |       |     2 |
|* 59 |              SORT JOIN                                 |                        |    89 |  2492 |       |     8 |
|* 60 |               TABLE ACCESS FULL                        | RSRV_POLICY_SEGMENT    |    89 |  2492 |       |     2 |
|* 61 |            SORT JOIN                                   |                        |   696K|  4083K|    16M|  1789 |
|  62 |             INDEX FAST FULL SCAN                       | PKCLAIM_RESERVE        |   696K|  4083K|       |   221 |
|* 63 |           FILTER                                       |                        |       |       |       |       |
|* 64 |            SORT JOIN                                   |                        |       |       |       |       |
|* 65 |             TABLE ACCESS FULL                          | CL_BENEFIT             |  1912K|    54M|       |  6510 |
|* 66 |          SORT JOIN                                     |                        | 49943 |   390K|  1576K|   860 |
|* 67 |           TABLE ACCESS FULL                            | CLAIM_EVENT_HISTORY    | 49943 |   390K|       |   729 |
|* 68 |        FILTER                                          |                        |       |       |       |       |
|* 69 |         SORT JOIN                                      |                        |       |       |       |       |
|  70 |          TABLE ACCESS FULL                             | POLICY_ELIM_PERIOD     | 91120 |  3826K|       |   105 |
|* 71 |      FILTER                                            |                        |       |       |       |       |
|* 72 |       SORT JOIN                                        |                        |       |       |       |       |
|  73 |        TABLE ACCESS FULL                               | RSRV_BUYOUT_RANGE      |    50 |  1000 |       |     2 |
|* 74 |    TABLE ACCESS FULL                                   | POLICY_EXCEPTION       |     1 |    13 |       |     2 |
-------------------------------------------------------------------------------------------------------------------------


Most of the time has been spent on step 37.

Thanks man, you are great. Kevin
Re: Ideas for fixing plan cardinality error [message #583480 is a reply to message #583479] Wed, 01 May 2013 08:57 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
OK, partitioning has made a substantial difference as well. One of the tables (not related to the original problem noted) is being partitioned because it has grown beyond a certain size. The partitioning scheme happens to apply to this query so when I fact in the partitioned table as well, the plan changes again the the runtime is cut in half. This is I believe due entirely to a reduction in physical I/O.

But the primary fix was the CARDINALITY hint so I am going to recommend additional testing with that hint in place.

thanks, I am satisfied. Kevin
Re: Ideas for fixing plan cardinality error [message #583481 is a reply to message #583479] Wed, 01 May 2013 08:58 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Glad you got it working to a certain degree.

I'd hoped some others might weigh in here, these are the kind of problems that bite you in the ass in production and its FAR nicer to have read about them first/have elegant solution in the back pocket in advance!

I'm sure there's a more elegant solution out there somewhere, though most of the neat tricks are in higher versions.

Happy to have helped a little though Smile
Re: Ideas for fixing plan cardinality error [message #583495 is a reply to message #583481] Wed, 01 May 2013 10:51 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
FYI, here is a link the the LEWIS SratchPad blog where he discusses this issue a little using a PDF borrowed from Michelle Deng.

http://jonathanlewis.wordpress.com/2009/05/11/cardinality-feedback/.

Also, I took a crack ad MANUAL WORKAREA MANANAGMENT and this helped too now that we are doing lots of SORTING.

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------
hash_area_size                       integer     200000000
sort_area_size                       integer     200000000
workarea_size_policy                 string      MANUAL

So in total:

1) added the CARDINALITY hint to a table expression to tell oracle the expected number of rows from this subquery.
2) took advantage of new partitioning being done to an unrelated table also used in the query
3) used session settings for managing work areas to make all the sorting OPTIMAL

runtime is about 80 seconds now instead of 27 minutes (about 1600 seconds) so all together we can get an order of magnitued improvement (a game changer).

Now I have to work with the app team and sysdba to discuss just how many of these features they are willing to implement in order to get the associated speed increase. Will be interesting to see what happens and how much work other people are willing to do to get what they want. Alas there is no "make it quick" button anwwhere; it all requires work in so many stages.

Kevin

[Updated on: Wed, 01 May 2013 10:58]

Report message to a moderator

Previous Topic: Oracle Tuning
Next Topic: Performance for Table analysis
Goto Forum:
  


Current Time: Thu Mar 28 08:00:53 CDT 2024