Re: optimizer and bind variables

Steffen Ramlow wrote:
>
> i have some trouble with an exec plan the optimizer generates when i use a
> bind variable (via jdbc on a ora 8.1)
>
> the plain stmt is fast, the prepared one is very slow coz of another exec
> plan
>
> the smts only differ in a single column:
>
> instead of a1.c9 = to_date('1990-10-23', 'YYYY-MM-DD'))
> the prep. stmt uses: a1.c9 = :1
>
> here are the traces
>
> what's going on???
>
> using no bind vars:
>
> select a0.resubmissionTime ,a0.objectType ,a0.oid ,a0.state
> ,a0.lockDateTime
> ,a0.targetId ,a0.lockUser ,a0.path ,a0.showContent ,a1.c9 ,a1.c8 ,a0.oid
> as
> oid_,a0.indexdata as indexdata_,a0.indexclassid as indexclassid_,
> a0.objecttype as objecttype
> from
> ix_ds2 a1, indexedobject a0 where a0.path like '/fa62/%' and ( (
> a0.objectType in ( 10, 23 ) ) and a1.c9 = to_date('1990-10-23',
> 'YYYY-MM-DD')) and not exists(select a_.id from ace a_ where a_.aclid=
> a0.aclid and a_.m_read>0 and a_.grantee in( 'grantee2b', 'grantee29',
> 'grantee2a', 'grantee2c')) and exists(select a_.id from ace a_ where
> a_.aclid=a0.aclid and a_.m_read=0 and a_.grantee in( 'grantee2b',
> 'grantee29', 'grantee2a', 'grantee2c')) and a0.indexdata=a1.id(+)
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ---------- ------
> ----
> Parse 1 0.18 0.23 0 0 0
> 0
> Execute 1 0.00 0.00 0 0 0
> 0
> Fetch 1 0.00 0.13 10 13 0
> 1
> ------- ------ -------- ---------- ---------- ---------- ---------- ------
> ----
> total 3 0.18 0.36 10 13 0
> 1
>
> Misses in library cache during parse: 1
> Optimizer goal: FIRST_ROWS
> Parsing user id: 127 (CS4007)
>
> Rows Row Source Operation
> ------- ---------------------------------------------------
> 1 FILTER
> 2 NESTED LOOPS
> 2 TABLE ACCESS BY INDEX ROWID IX_DS2
> 2 INDEX RANGE SCAN (object id 31114)
> 2 TABLE ACCESS BY INDEX ROWID INDEXEDOBJECT
> 2 INDEX RANGE SCAN (object id 31063)
> 1 TABLE ACCESS BY INDEX ROWID ACE
> 1 INDEX RANGE SCAN (object id 31053)
> 1 TABLE ACCESS BY INDEX ROWID ACE
> 5 INDEX RANGE SCAN (object id 31053)
>
> Rows Execution Plan
> ------- ---------------------------------------------------
> 0 SELECT STATEMENT GOAL: FIRST_ROWS
> 1 FILTER
> 2 NESTED LOOPS
> 2 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'IX_DS2'
> 2 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'XIFEXTRA13IX_DS2'
> (NON-UNIQUE)
> 2 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
> 'INDEXEDOBJECT'
> 2 INDEX GOAL: ANALYZED (RANGE SCAN) OF
> 'XIF18INDEXEDOBJECT' (NON-UNIQUE)
> 1 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'ACE'
> 5 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'ACLINDEXACE'
> (NON-UNIQUE)
> 1 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'ACE'
> 1 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'ACLINDEXACE'
> (NON-UNIQUE)
>
> using one bind var on a1.c9:
>
> select a0.resubmissionTime ,a0.objectType ,a0.oid ,a0.state
> ,a0.lockDateTime
> ,a0.targetId ,a0.lockUser ,a0.path ,a0.showContent ,a1.c9 ,a1.c8 ,a0.oid
> as
> oid_,a0.indexdata as indexdata_,a0.indexclassid as indexclassid_,
> a0.objecttype as objecttype
> from
> ix_ds2 a1, indexedobject a0 where a0.path like '/fa62/%' and ( (
> a0.objectType in ( 10, 23 ) ) and a1.c9 = :1 ) and not exists(select
> a_.id
> from ace a_ where a_.aclid=a0.aclid and a_.m_read>0 and a_.grantee in(
> 'grantee2b', 'grantee29', 'grantee2a', 'grantee2c')) and exists(select
> a_.id from ace a_ where a_.aclid=a0.aclid and a_.m_read=0 and a_.grantee
> in( 'grantee2b', 'grantee29', 'grantee2a', 'grantee2c')) and a0.indexdata=
> a1.id(+)
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ---------- ------
> ----
> Parse 1 0.00 0.01 0 0 0
> 0
> Execute 1 0.01 0.01 0 0 0
> 0
> Fetch 1 184.21 847.63 193591 19501146 0
> 1
> ------- ------ -------- ---------- ---------- ---------- ---------- ------
> ----
> total 3 184.22 847.65 193591 19501146 0
> 1
>
> Misses in library cache during parse: 1
> Optimizer goal: FIRST_ROWS
> Parsing user id: 127 (CS4007)
>
> Rows Row Source Operation
> ------- ---------------------------------------------------
> 1 FILTER
> 2 NESTED LOOPS
> 5110347 TABLE ACCESS BY INDEX ROWID INDEXEDOBJECT
> 5166682 INDEX RANGE SCAN (object id 31178)
> 5110347 TABLE ACCESS BY INDEX ROWID IX_DS2
> 10220692 INDEX RANGE SCAN (object id 31114)
> 1 TABLE ACCESS BY INDEX ROWID ACE
> 1 INDEX RANGE SCAN (object id 31053)
> 1 TABLE ACCESS BY INDEX ROWID ACE
> 5 INDEX RANGE SCAN (object id 31053)
>
> Rows Execution Plan
> ------- ---------------------------------------------------
> 0 SELECT STATEMENT GOAL: FIRST_ROWS
> 1 FILTER
> 2 NESTED LOOPS
> 5110347 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
> 'INDEXEDOBJECT'
> 5166682 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'XIE3INDEXEDOBJECT'
> (NON-UNIQUE)
> 5110347 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'IX_DS2'
> 10220692 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'XIFEXTRA13IX_DS2'
> (NON-UNIQUE)
> 1 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'ACE'
> 5 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'ACLINDEXACE'
> (NON-UNIQUE)
> 1 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'ACE'
> 1 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'ACLINDEXACE'
> (NON-UNIQUE)
When you have literals you are giving the optimizer more information to work with. In 9i, the optimizer can "take a glance" at the value of bind variables under certain circumstances, but probably the easiest course of action would be to add a hint to the SQL to force it to use the access path you want...

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."