From bakerb@rockymountainnews.com Fri, 14 Sep 2001 13:24:23 -0700 From: "Baker, Barbara" Date: Fri, 14 Sep 2001 13:24:23 -0700 Subject: RE: CBO changed path - why?? Message-ID: MIME-Version: 1.0 Content-Type: text/plain !! 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@FuelSpot.com] > Reply To: ORACLE-L@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, > > 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@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@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@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@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@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@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).