Re: Optimization question: Unrolling subquery into IN clause
Date: Thu, 22 Oct 2009 02:01:31 -0700 (PDT)
Message-ID: <badd2686-0166-47fa-a260-bfd7c7c43d01_at_e34g2000vbm.googlegroups.com>
As already mentioned by other contributors, the cardinality estimates are way off. This is very likely the reason for the plan chosen. If they were correct, that execution plan probably would be the most efficient one.
Try the TAB2 query first:
SELECT key
FROM tab2
WHERE tab2.col3='Something'
This is supposed to return 20 rows if I understand you correctly. What is the cardinality estimate of this query? Very likely approx. 10,000 instead.
So if the TAB2.COL3 column is skewed and the value "L" is only showing up 20 times, then you need to tell the optimizer about that and add a histogram on this column, if it's not there already. You say there are no histograms, but 10g by default would generate a histogram on such a column if you used the default METHOD_OPT=>"FOR ALL COLUMNS SIZE AUTO" of the DBMS_STATS calls, so I assume you're using an individual call to DBMS_STATS using non-default options or changed the default for METHOD_OPT using DBMS_STATS.SET_PARAM.
Once this estimate is in the right ballpark you can see if Oracle gets the filter selectivity of the join right, and automatically chooses the index on TAB1 for COL2 (and a nested loop join). May be you need to tell Oracle more about the TAB1.COL2 statistics if it still doesn't work out.
I think the CARDINALITY was malformed and therefore wasn't applied. It requires a table alias, e.g. CARDINALITY(A, 20), but since this is a uncorrelated subquery there is no alias to mention. Hints should only be used if there is no other way to fix the problem.
The TKPROF output (application bind variable usage) you've shown joins the TAB2 table twice, so I wonder where that second join comes from. Very likely this is not the same query, as you seem to say with "slightly more complex version of the query".
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/
On Oct 19, 8:46 am, Wolfram Roesler <w..._at_spam.la> wrote:
> Ok, here we go. I edited this from my actual SQL to the pseudo
> table/column names I used in my original posting.
>
> PLAN_TABLE_OUTPUT
>
> Plan hash value: 593971052
>
> -------------------------------------------------------------------------
> --------------
> | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%
> CPU)| Time |
> -------------------------------------------------------------------------
> --------------
> | 0 | SELECT STATEMENT | | 227K| 25M| | 20352
> (2)| 00:04:05 |
> |* 1 | HASH JOIN | | 227K| 25M| | 20352
> (2)| 00:04:05 |
> |* 2 | TABLE ACCESS FULL| tab2 | 9554 | 149K| | 140
> (2)| 00:00:02 |
> |* 3 | TABLE ACCESS FULL| tab1 | 681K| 64M| | 20206
> (2)| 00:04:03 |
> -------------------------------------------------------------------------
> --------------
>
> Outline Data
> -------------
>
> /*+
> BEGIN_OUTLINE_DATA
> USE_HASH(_at_"SEL$5DA710D3" "tab1"@"SEL$1")
> LEADING(_at_"SEL$5DA710D3" "tab2"@"SEL$2" "tab1"@"SEL$1")
> FULL(_at_"SEL$5DA710D3" "tab1"@"SEL$1")
> FULL(_at_"SEL$5DA710D3" "tab2"@"SEL$2")
> OUTLINE(_at_"SEL$2")
> OUTLINE(_at_"SEL$1")
> UNNEST(_at_"SEL$2")
> OUTLINE_LEAF(_at_"SEL$5DA710D3")
> ALL_ROWS
> OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
> IGNORE_OPTIM_EMBEDDED_HINTS
> END_OUTLINE_DATA
> */
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 1 - access("col2"="key")
> 2 - filter("col3"='L')
> 3 - filter("col1='Value')
>
> 38 rows selected.
>
> I have some limited influence on the query, but since a workaround has
> already been implemented, I'm rather interested in understanding what's
> going on in order to avoid similar problems in the future. Also, the
> query must work (with proper performance) with all Oracle versions >=8i.
>
> Thanks for your help, and best regards
> W. Rösler
Received on Thu Oct 22 2009 - 04:01:31 CDT