RE: CBO changed path - why??

!! Please do not post Off Topic to this List !!

Christopher:
Unfortunately we're not on 8i yet, so dbms_stats is not an option. I see from the tkprof that it's walking an extraordinary number of rows, but I can't
see why.

Thanks for taking a look - I really appreciate it. Barb

> ----------
> From: Christopher Spence[SMTP:cspence_at_FuelSpot.com]
> Reply To: ORACLE-L_at_fatcity.com
> Sent: Friday, September 14, 2001 11:15 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: CBO changed path - why??
>
> !! Please do not post Off Topic to this List !!
>
> What I would do is use DBMS_STATS to move the production stats to the
> staging db and see if the tests lead the same results.
>
> Are ya stats up to date? You using analyze or dbms_stats?
> Do you have comparing explain plans, trace files?
>
> I would highly recommend tracing it and checking which step is doing a lot
> of rows, and comparing that to determine where it is slowing down.
>
> "Do not criticize someone until you walked a mile in their shoes, that way
> when you criticize them, you are a mile a way and have their shoes."
>
> Christopher R. Spence
> Oracle DBA
> Phone: (978) 322-5744
> Fax: (707) 885-2275
>
> Fuelspot
> 73 Princeton Street
> North, Chelmsford 01863
>
>
>
> -----Original Message-----
> Sent: Friday, September 14, 2001 12:30 PM
> To: Multiple recipients of list ORACLE-L
>
> !! Please do not post Off Topic to this List !!
>
>
> We have had 2 querys go wacko on us. Both are cursors in a large
> (5000 line) pl/sql package. This interface package runs daily.
> The cursor execution below ran in less than 30 seconds on Tues;
> ran 2 hrs 15 minutes Wed. (yikes!) We had the same problem
> with a similar cursor 2 weeks ago.
>
> I've fixed the query by adding more selectivity to the where clause.
> Here's the real mystery. I pulled the 3 tables from this join
> from the production box (E4500 Solaris 2.6, Oracle 8.0.5) to our
> smaller test box. Small test box is running identical stuff (solaris 2.6,
> Oracle 8.0.5). The query still runs in under 30 seconds on small
> test box. I dumped all the init parameters (SELECT NAME, VALUE FROM
> V$PARAMETER) from both databases, then did a diff in the output files.
> No significant differences that I can see.
>
> I'm wondering why the query still runs ok on the test box, but went
> wacko on the "real" system.
>
> These 3 tables are small (invrows 95,062 rows 21 megs; sub_ad 5,993 rows,
> 20 megs; sub_pub 45,553 rows 30 megs). All 3 tables have identical
> indexes on both boxes; all 3 have been analyzed on both boxes.
> All 3 tables have index on column adno. optimizer is choose on both
> instances.
>
>
> Sorry this is so long. I'd appreciate any insights.
> Thx!!!
>
> Barb
>
>
>
> select
> i.adno,
> <more stuff>
> from invrows i,
> sub_ad a,
> sub_pub p
> WHERE A.RUNNO=860 and I.ROWTYPE=4
> and I.ADNO=A.ADNO and I.VNO=A.VNO
> and i.adno=p.adno and i.pubno=p.pubno
> and a.vno=p.vno and A.VNO=1
> and a.startdate > a.rdate
> and a.enddate = to_date(a.cus4name,'mm/dd/rrrr')
> and to_char(a.rdate,'mm/dd/rrrr') = to_char(p.mdate,'mm/dd/rrrr')
>
> _______________________________
> autotrace from production (BAD!!)
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=313)
> 1 0 NESTED LOOPS (Cost=1 Card=1 Bytes=313)
> 2 1 NESTED LOOPS (Cost=56 Card=7 Bytes=1267)
> 3 2 TABLE ACCESS (FULL) OF 'SUB_AD' (Cost=10 Card=1 Bytes=133)
> 4 2 TABLE ACCESS (FULL) OF 'SUB_PUB' (Cost=46 Card=5820
> Bytes=279360)
> 5 1 TABLE ACCESS (BY INDEX ROWID) OF 'INVROWS' (Cost=1 Card=6791
> Bytes=896412)
> 6 5 INDEX (RANGE SCAN) OF 'I_INVR1' (NON-UNIQUE) (Cost=1
> Card=6791)
>
>
>
> _______________________________
> autotrace from test box (Good!)
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=313)
> 1 0 NESTED LOOPS (Cost=1 Card=1 Bytes=313)
> 2 1 NESTED LOOPS (Cost=1 Card=1 Bytes=265)
> 3 2 TABLE ACCESS (FULL) OF 'SUB_AD' (Cost=11 Card=1 Bytes=133)
> 4 2 TABLE ACCESS (BY INDEX ROWID) OF 'INVROWS' (Cost=1
> Card=6906
> Bytes=911592)
> 5 4 INDEX (RANGE SCAN) OF 'I_INVR1' (NON-UNIQUE) (Cost=1
> Card=6906)
> 6 1 TABLE ACCESS (BY INDEX ROWID) OF 'SUB_PUB' (Cost=1 Card=6911
> Bytes=331728)
> 7 6 INDEX (UNIQUE SCAN) OF 'I_SUBPUB1' (UNIQUE)
>
>
>
> _______________________________
> tkprof from production (BAD!!)
> (The tkprof shows 129,696,658 rows returned for sub_pub when the
> entire table is only 45,000 rows.)
>
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.01 0.01 0 0 0
> 0
> Execute 1 0.01 0.01 0 0 0
> 0
> Fetch 7 8139.09 8153.17 907 56669565 8361
> 102
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 9 8139.11 8153.19 907 56669565 8361
> 102
>
>
> Misses in library cache during parse: 1
> Optimizer goal: CHOOSE
> Parsing user id: 618 (AMAX)
>
> Rows Execution Plan
> ------- ---------------------------------------------------
> 0 SELECT STATEMENT GOAL: CHOOSE
> 102 NESTED LOOPS
> 54193272 NESTED LOOPS
> 5993 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'SUB_AD'
> 129696658 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'SUB_PUB'
> 27213 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'INVROWS'
> 155138410 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'I_INVR1'
> (NON-UNIQUE)
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Baker, Barbara
> INET: bakerb_at_rockymountainnews.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Christopher Spence
> INET: cspence_at_FuelSpot.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Baker, Barbara
  INET: bakerb_at_rockymountainnews.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).