Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> xplan row source 9i

xplan row source 9i

From: Barbara Baker <barb.baker_at_gmail.com>
Date: Fri, 10 Dec 2004 09:45:21 -0700
Message-ID: <47a6f72b0412100845706d0f83@mail.gmail.com>


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
********************************************************************************
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Dec 10 2004 - 10:46:50 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US