RE: Different Plans for Literal Vs Bind Variables
- From: "Goulet, Richard" <Richard.Goulet@xxxxxxxxxxx>
- To: <ian@xxxxxxxxxxxxxxxxx>, "Oracle-L Freelists" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 12 Jan 2009 08:38:57 -0500
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
