| Ideas for fixing plan cardinality error [message #583423] |
Tue, 30 April 2013 11:59  |
 |
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  |
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 #583471 is a reply to message #583468] |
Wed, 01 May 2013 07:51 |
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 #583475 is a reply to message #583474] |
Wed, 01 May 2013 08:09 |
|
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 #583477 is a reply to message #583475] |
Wed, 01 May 2013 08:26 |
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. 
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 #583495 is a reply to message #583481] |
Wed, 01 May 2013 10:51 |
|
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
