RE: Different Plans for Literal Vs Bind Variables

Ian,

        I am going to take an "educated guess" at your problem that's
based on experience.  Some time ago I ran into a similar problem that
was close to impossible to explain, until I did some playing around.
First take a look at the ANALYZED_DATE column of the table & index
views.  If the B.EFFDT that your looking for is after the last analysis
it is possible that Oracle, when looking at the hard coded  value is
making an assumption that it is not currently in the index and
consequently is leaning toward a full table scan.  With the bind
variable it can't see the value directly and consequently makes a
different choice.  Consequently re-analyzing the table may solve the
problem.  It worked for me once and I was as baffled as you currently
are, for the same reasons.


Dick Goulet
Senior Oracle DBA
PAREXEL International
978.313.3426
 information transmitted in this communication is intended only for the
person or entity to which it is addressed and may contain confidential
and/or privileged material. Any review, retransmission, dissemination or
other use of, or taking of any action in reliance upon, this information
by persons or entities other than the intended recipient is prohibited.
If you received this in error, please destroy any copies, contact the
sender and delete the material from any computer.



-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Ian MacGregor
Sent: Friday, January 09, 2009 5:22 PM
To: Oracle-L Freelists
Subject: 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


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

Other related posts: