RE: Different Plans for Literal Vs Bind Variables
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <ian@xxxxxxxxxxxxxxxxx>, "'Oracle-L Freelists'" <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 10 Jan 2009 20:04:39 -0500
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
