Re: xplan row source 9i

From: Barbara Baker <barb.baker_at_gmail.com>
Date: Fri, 10 Dec 2004 12:12:53 -0700
Message-ID: <47a6f72b04121011123584950c@mail.gmail.com>

Well, Mladen gets the gold star and virtual beer for this one. Setting _optim_peek_user_binds=FALSE caused this job to drop from 6.5 hours to 23 minutes. The "row source operation" in the tkprof now matches the explain plan.

Thanks, everyone, for your help!
Barb

Here's the new tkprof:

call count cpu elapsed disk query rows
------- ------ -------- ---------- ---------- ---------- -------

Parse        1      0.03       0.05          0         61        0
Execute   6381      2.33       3.12          0          0        0
Fetch     6452      2.05       5.68        512      52938     6523


------- ------  -------- ---------- ---------- ----------  -------

total    12834      4.41       8.86        512      52999     6523

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 21 (ADMARC)

Rows Row Source Operation
------- ---------------------------------------------------

6523 CONCATENATION (cr=52938 r=512 w=0 time=4905937 us) 142 INDEX RANGE SCAN OBJ#(5983) (cr=26642 r=506 w=0 time=4374721 us)(object id 5983)

6381 INDEX RANGE SCAN OBJ#(5983) (cr=26296 r=6 w=0 time=439423 us)(object id 5983)

Rows Execution Plan
------- ---------------------------------------------------

0 SELECT STATEMENT GOAL: CHOOSE 6523 CONCATENATION
142 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'PK_WOD' (UNIQUE) 6381 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'PK_WOD' (UNIQUE)
--

http://www.freelists.org/webpage/oracle-l Received on Fri Dec 10 2004 - 13:08:11 CST