xplan row source 9i
- From: Barbara Baker <barb.baker@xxxxxxxxx>
- To: Oracle-L@xxxxxxxxxxxxx
- Date: Fri, 10 Dec 2004 09:45:21 -0700
WARNING: The code you are about to look at is incredibly stupid.
You'll probably lose IQ points just viewing it. It's vendor-supplied.
I cannot change it.
This job runs in under 2 hours on 8.1.7.4, and 6.5 hours on 9.2.0.4
(both on VMS 7.3-1)
The data is identical (exact copy of the data files), but the cpu is
different. It's possible that the cpu difference accounts for all 4
hours difference.
But what I'd really like to understand is this:
The row source operations shows an index full scan, while the
execution plan shows 2 index range scans concatenated. My
understanding is that the row source is what actually occurred. Would
the differences between 8i and 9i account for this difference? Did
version 9i do some "bind variable peeking" that 8i did not do? If so,
is there a hint to turn it off?
(WOD has 2.6 million rows)
(I cannot easily trace this mess in the current production 8i
environment, so I cannot compare apples/apples)
this is 9.2.0.4 on vms 7.3-1
select /*+ INDEX(WOD PK_WOD) */ rowid ,WO_KEY ,XRF_KEY
from
WOD where (WO_KEY>:b1 or (WO_KEY=:b1 and XRF_KEY>=:b2))
call count cpu elapsed disk query rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.04 0.04 0 61 0
Execute 6381 2.62 3.64 0 0 0
Fetch 6452 20623.61 20895.49 67337 231172726 6523
------- ------ -------- ---------- ---------- ---------- ----------
total 12834 20626.27 20899.18 67337 231172787 6523
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 21 (ADMARC)
Rows Row Source Operation
------- ---------------------------------------------------
6523 INDEX FULL SCAN OBJ#(5983) (cr=231172726 r=67337 w=0
6524 time=20887021053 us)(object id 5983)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
6523 CONCATENATION
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'PK_WOD' (UNIQUE)
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'PK_WOD' (UNIQUE)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
row cache lock 3 0.00 0.00
SQL*Net message to client 6452 0.00 0.04
db file sequential read 67337 0.25 247.32
SQL*Net message from client 6452 0.00 4.72
latch free 5 0.00 0.00
********************************************************************************
--
//www.freelists.org/webpage/oracle-l
Other related posts:
