RE: Different Plans for Literal Vs Bind Variables

Thanks for the very complete setup description and for ruling out previous
peek'd plans in memory.

I'm thinking you'll need a Wolfgang (10053) on this (with queries fresh and
new in some way to again avoid the peek problem) to see why the CBO is not
using IDX$$_7D3D0001 in the bind variable case. It seems to me without
thinking tooo much that it can't be a variable type conversion since in both
cases the results of the to_date function are the comparator to B.EFFDT.

Hmm. Since you're getting a join, and since the object suffix VW sure sounds
like a view to me, perhaps it goes after plan possibilities in a different
order when you are or are not using bind variables. How high is your
permutations value?

If that (a high enough permutations number) fixes it, then maybe you could
run a session immediately after system restart with reasonable bind variable
fill ins and a high permutations value (hmm, I'd have to look up whether
that can be set at the session level) and then presumably that would cache a
good plan (using the peek "problem" to your advantage.) Again, IF more
permutations solves your problem you can just add this to your "keeps"
script that you run whenever you restart intending to leave the system up
for production for normal workshifts.

Good luck Ian. Perhaps Mr. Kurtz or Mr. Breitling will chime in on this, as
they certainly have relevant expertise.

Regards,

mwf

PS: oh, I hope IDX$_7D3D0001 is references things that feed into B.EFFDT, or
I'm barking up entirely the wrong tree. Also PSAPSTREENODE.

-----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: