Re: Optimization question: Unrolling subquery into IN clause
From: Steve Howard <stevedhoward_at_gmail.com>
Date: Mon, 19 Oct 2009 05:08:41 -0700 (PDT)
Message-ID: <c365a644-242b-4226-92df-1a16bb1fd53c_at_a32g2000yqm.googlegroups.com>
On Oct 19, 2:46 am, Wolfram Roesler <w..._at_spam.la> wrote:
> "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote in news:8
> _idnYnpBISXEUTXnZ2dnUVZ8uqdn..._at_bt.com:
>
> > I'd say the same as Steve Howard.
> > explain plan for ...
> > select * from table(dbms_xplan.display(null,null,'outline'));
>
> > We need to see the execution plan before suggesting how to change it.
>
> 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.
>
> > Either way, its choice is dictated by the arithmetic and the current
> > constraints. You may have to put in a stack of hints to get the path
> > you want - is that acceptable, or are you not allowed to modify the
> > query at all ?
>
> 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
Date: Mon, 19 Oct 2009 05:08:41 -0700 (PDT)
Message-ID: <c365a644-242b-4226-92df-1a16bb1fd53c_at_a32g2000yqm.googlegroups.com>
On Oct 19, 2:46 am, Wolfram Roesler <w..._at_spam.la> wrote:
> "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote in news:8
> _idnYnpBISXEUTXnZ2dnUVZ8uqdn..._at_bt.com:
>
> > I'd say the same as Steve Howard.
> > explain plan for ...
> > select * from table(dbms_xplan.display(null,null,'outline'));
>
> > We need to see the execution plan before suggesting how to change it.
>
> 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.
>
> > Either way, its choice is dictated by the arithmetic and the current
> > constraints. You may have to put in a stack of hints to get the path
> > you want - is that acceptable, or are you not allowed to modify the
> > query at all ?
>
> 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
What plan does it show when you pass in the list rather than use the subquery? Also, are these bind variables or literals you are using in the actual query? I ask because you said it should only return 300 rows total, so this looks like the problem...
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 227K| 25M| |20352 (2)| 00:04:05 | Received on Mon Oct 19 2009 - 07:08:41 CDT