Different Plans for Literal Vs Bind Variables

These two statements have different explain plans


SELECT MAX(B.EFFDT) FROM PS_DEPT_SCRTY_VW B
WHERE B.SETID='SLAC' AND B.EFFDT<=TO_DATE('2008-01-08','YYYY-MM-DD');



And 

SELECT MAX(B.EFFDT) FROM PS_DEPT_SCRTY_VW B
WHERE B.SETID=:A1 AND B.EFFDT<=TO_DATE(:A2,'YYYY-MM-DD');


It is not because the second statement is in the library cache.  The query
is  new. Also a level 12 10046 trace  confirmed this.

The problem is that Oracle is not handling the :A2 substitution well.

The statement with the literals is faster.  Here is its plan

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=74 pr=0 pw=0 time=122348 us)
   6007   VIEW  (cr=74 pr=0 pw=0 time=120300 us)
   6007    FILTER  (cr=74 pr=0 pw=0 time=114287 us)
  11224     HASH GROUP BY (cr=74 pr=0 pw=0 time=113493 us)
  15818      HASH JOIN  (cr=74 pr=0 pw=0 time=57310 us)
    891       INDEX FAST FULL SCAN IDX$$_7D3D0001 (cr=7 pr=0 pw=0 time=1182
us)(object id 239414)
  11240       HASH JOIN  (cr=67 pr=0 pw=0 time=15946 us)
    894        INDEX FAST FULL SCAN IDX$$_7D3D0001 (cr=7 pr=0 pw=0 time=1092
us)(object id 239414)
   6029        INDEX RANGE SCAN PSAPSTREENODE (cr=60 pr=0 pw=0 time=6118
us)(object id 84565)

It's resource usage

call     count       cpu    elapsed       disk      query    current
rows
------- ------  -------- ---------- ---------- ---------- ----------
----------
Parse        1      0.15       0.14          0          2          0
0
Execute      1      0.00       0.00          0          0          0
0
Fetch        2      0.12       0.12          0         74          0
1
------- ------  -------- ---------- ---------- ---------- ----------
----------
total        4      0.27       0.26          0         76          0
1

And waits
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total
Waited
  ----------------------------------------   Waited  ----------
------------
  SQL*Net message to client                       2        0.00
0.00
  SQL*Net message from client                     2        2.58
2.58
----------------------------------------------------------------------------
For the query using bind variables:

The plan

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=2000432 pr=37563 pw=37441 time=52938101 us)
   6007   NESTED LOOPS  (cr=2000432 pr=37563 pw=37441 time=52278837 us)
2000246    HASH JOIN  (cr=184 pr=37557 pw=37441 time=39994374 us)
2000246     VIEW  VW_SQ_1 (cr=124 pr=27911 pw=27795 time=18708123 us)
2000246      HASH GROUP BY (cr=124 pr=27911 pw=27795 time=18708117 us)
2867021       MERGE JOIN  (cr=124 pr=116 pw=0 time=3032698 us)
    891        SORT JOIN (cr=16 pr=13 pw=0 time=40303 us)
    891         INDEX FAST FULL SCAN PS1DEPT_TBL (cr=16 pr=13 pw=0
time=14728 us)(object id 116752)
2867021        SORT JOIN (cr=108 pr=103 pw=0 time=3002303 us)
   6029         INDEX FAST FULL SCAN PSFPSTREENODE (cr=108 pr=103 pw=0
time=6697 us)(object id 84569)
   6029     INDEX RANGE SCAN PSAPSTREENODE (cr=60 pr=0 pw=0 time=149
us)(object id 84565)
   6007    INDEX UNIQUE SCAN PS_DEPT_TBL (cr=2000248 pr=6 pw=0 time=11796643
us)(object id 116750)

The resource usage

call     count       cpu    elapsed       disk      query    current
rows
------- ------  -------- ---------- ---------- ---------- ----------
----------
Parse        1      0.00       0.00          0          0          0
0
Execute      1      0.21       0.20          0          0          0
0
Fetch        2     38.18      52.93      37563    2000432          0
1
------- ------  -------- ---------- ---------- ---------- ----------
----------
total        4     38.39      53.14      37563    2000432          0
1

And the waits

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total
Waited
  ----------------------------------------   Waited  ----------
------------
  SQL*Net message to client                       2        0.00
0.00
  db file sequential read                         8        0.02
0.06
  db file scattered read                         16        0.03
0.14
  direct path write temp                       3231        0.09
16.14
  direct path read temp                        3231        0.02
1.68
  SQL*Net message from client                     2       48.93
48.93
----------------------------------------------------------------------------

This apparent problem with the date bind variable while a nuisance in the
example cripples the server when  the actual query is  run.  That query is

SELECT /*+ FIRST_ROWS */ SETID, DEPTID, DESCR FROM PS_DEPT_SCRTY_VW A
WHERE SETID=:1 AND EFFDT= (SELECT MAX(B.EFFDT) FROM PS_DEPT_SCRTY_VW B
WHERE B.SETID=A.SETID AND B.EFFDT<=TO_DATE(:2,'YYYY-MM-DD')) ORDER BY
SETID, DEPTID


The query is generated by PeopleSoft.  If both variables are replaced by
literals or just the bind variable ":2" the query runs fine.  If the :2 bind
variable is used instead of a literal, the query takes a great deal of time.

How does one get around this problem?

Ian MacGregor
SLAC National Accelerator Laboratory








--
http://www.freelists.org/webpage/oracle-l

Other related posts: