optimizer and bind variables

From: Steffen Ramlow <s.ramlow_at_gmx.de>
Date: Tue, 26 Mar 2002 18:31:47 +0100
Message-ID: <a7qb7m$na1s4$1@ID-54600.news.dfncis.de>

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)

Received on Tue Mar 26 2002 - 11:31:47 CST