Re: Adding Indexes drastically slows down App

From: E Elliott <emelliott_99_at_yahoo.com>
Date: 21 Jun 2001 08:04:55 -0700
Message-ID: <9c4485cf.0106210704.2d9bd801@posting.google.com>

"Howard J. Rogers" <howardjr_at_www.com> wrote in message news:<3b31c3ef_at_news.iprimus.com.au>...
> Well, it's a hint, not a command. And the optimiser is a law unto itself,
> even on sunny Wednesdays, with a following wind, fingers crossed and copious
> quantities of Irish stout and shamrock juice.
>
> I'd love to see the explain plans! You must have some wildly skewed data in
> there for two pathetic indexes to make such a wild difference... and for an
> estimate statistics to be hopeless, too.
>
> Regards
> HJR
I figured it was something like that for the hints. I guess Oracle just knows what is best and will do what it wants!

Here are the explain plans: I've included 3 - One without the indexes, the second with the indexes and estimate stats no sample and the third is the full blown compute stats. Just an FYI the compute stats didn't change much over the smaple size of 20% for the stats.

Q_PLAN WITHOUT NEW INDEXES

---

0 SELECT STATEMENT Parent: Position: 2847

1     NESTED LOOPS OUTER  Parent: 0 Position: 1
2       HASH JOIN   Parent: 1 Position: 1
3         NESTED LOOPS   Parent: 2 Position: 1
4           NESTED LOOPS   Parent: 3 Position: 1
5             INDEX UNIQUE SCAN SYS_C002215 Parent: 4 Position: 1
6             TABLE ACCESS BY INDEX ROWID ACCOUNT Parent: 4 Position:
2
7               INDEX UNIQUE SCAN SYS_C00817 Parent: 6 Position: 1
8           TABLE ACCESS BY INDEX ROWID ITEM Parent: 3 Position: 2
9             INDEX RANGE SCAN IX_ITEM_TRNDT Parent: 8 Position: 1
10         TABLE ACCESS FULL TRAN_CODE Parent: 2 Position: 2
11       TABLE ACCESS BY INDEX ROWID SERIAL Parent: 1 Position: 2
12         INDEX UNIQUE SCAN UX_SERIAL_SRLNB_RCTYP Parent: 11

Position: 1

Q_PLAN WITH NEW INDEXES AND ESTIMATE STATS - NO SAMPLE SIZE

---

0 SELECT STATEMENT Parent: Position: 13625

1     NESTED LOOPS OUTER  Parent: 0 Position: 1
2       HASH JOIN   Parent: 1 Position: 1
3         NESTED LOOPS   Parent: 2 Position: 1
4           NESTED LOOPS   Parent: 3 Position: 1
5             INDEX UNIQUE SCAN SYS_C002215 Parent: 4 Position: 1
6             TABLE ACCESS BY INDEX ROWID ACCOUNT Parent: 4 Position:
2
7               INDEX UNIQUE SCAN SYS_C00817 Parent: 6 Position: 1
8           TABLE ACCESS BY INDEX ROWID ITEM Parent: 3 Position: 2
9             INDEX RANGE SCAN IX_ITEM_SRLNMBR Parent: 8 Position: 1
10         TABLE ACCESS FULL TRAN_CODE Parent: 2 Position: 2
11       TABLE ACCESS BY INDEX ROWID SERIAL Parent: 1 Position: 2
12         INDEX UNIQUE SCAN UX_SERIAL_SRLNB_RCTYP Parent: 11

Position: 1

Q_PLAN With New Indexes, FULL COMPUTE STATS

---

0 SELECT STATEMENT Parent: Position: 8316

1     NESTED LOOPS OUTER  Parent: 0 Position: 1
2       HASH JOIN   Parent: 1 Position: 1
3         TABLE ACCESS BY INDEX ROWID ITEM Parent: 2 Position: 1
4           INDEX RANGE SCAN IX_ITEM_SRLNMBR Parent: 3 Position: 1
5         NESTED LOOPS   Parent: 2 Position: 2
6           NESTED LOOPS   Parent: 5 Position: 1
7             INDEX UNIQUE SCAN SYS_C002215 Parent: 6 Position: 1
8             TABLE ACCESS BY INDEX ROWID ACCOUNT Parent: 6 Position:
2
9               INDEX UNIQUE SCAN SYS_C00817 Parent: 8 Position: 1
10           TABLE ACCESS FULL TRAN_CODE Parent: 5 Position: 2
11       TABLE ACCESS BY INDEX ROWID SERIAL Parent: 1 Position: 2
12         INDEX UNIQUE SCAN UX_SERIAL_SRLNB_RCTYP Parent: 11

Position: 1

And if you're interested here's the query. It is querying off of a view.

SELECT out_item.item_objectid, out_item.item_amount,

out_item.item_trandate,   out_item.item_source, out_item.item_state,
out_item.item_dbcrtype,      out_item.item_serialnumber,
out_item.item_rectype, out_item.item_acceptdate,
out_item.item_trancode, out_item.item_account,
out_item.item_jdaexcept, out_item.item_userfiel1,
out_item.item_userfiel2, out_item.item_userfiel3,
out_item.item_userfiel4, out_item.item_userfiel5,
out_item.item_userfiel6, out_item.item_userfiel7,
out_item.item_userfiel8, out_item.item_userfiel9,  
out_item.item_userfiel10, out_item.item_glextrac,
out_item.item_historextrac, out_item.item_achextrac,
out_item.account_accounumbe,         out_item.trancode_trancode,
out_item.serial_usefie1, out_item.serial_usefie2,
out_item.serial_usefie3, out_item.serial_usefie4,
out_item.serial_usefie5, out_item.serial_usefie6,
out_item.serial_usefie7, out_item.serial_usefie8,
out_item.serial_usefie9, out_item.serial_usefie10,
out_item.view_blankcol

FROM out_item out_item
WHERE out_item.item_rectype = '100000153'

AND out_item.item_account = '100000912'
AND out_item.item_serialnumber >= '00000'
AND out_item.item_serialnumber <= '09999'
AND out_item.item_state = 'O'
AND out_item.item_trandate >= '04/01/2001'
AND out_item.item_trandate <= '04/28/2001';

This query is producing a report(crystal report!) the displays items that have not been reconciled. Item is the biggest table in the system

I can post the view if anyone wants to see it. We haven't even started to touch the view to tune it. I'm sure that is next!

Thanks for the help! Its been quite interesting.

Elizabeth Received on Thu Jun 21 2001 - 10:04:55 CDT