Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Queries slow after Upgrade to Oracle 9i
Thanks to all you for postings. Oracle is upgraded to 9.2.0.5.0
Following I found using sql*plus's autotrace I looking at the underlying queries and they are badly written. The mystery is, it was working fine with 8i (8.1.7). It was taking less then a minute and now it takes almost an hour with 9i.
9737 rows selected.
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=406 Card=29 Bytes=3538)
1 0 NESTED LOOPS (OUTER) (Cost=406 Card=29 Bytes=3538)
2 1 NESTED LOOPS (Cost=348 Card=29 Bytes=3161) 3 2 FILTER 4 3 NESTED LOOPS (OUTER) 5 4 TABLE ACCESS (FULL) OF 'SALE_STAFF' (Cost=290Card=29 Bytes=2436)
6 4 VIEW PUSHED PREDICATE OF 'INT_DUPS' (Cost=1 Card=1 Bytes=7)
7 6 NESTED LOOPS (OUTER) (Cost=299 Card=1 Bytes=114) 8 7 NESTED LOOPS (OUTER) (Cost=297 Card=1Bytes=101)
9 8 NESTED LOOPS (Cost=295 Card=1 Bytes=88) 10 9 HASH JOIN (Cost=294 Card=1 Bytes=70) 11 10 NESTED LOOPS (Cost=3 Card=1 Bytes=44) 12 11 TABLE ACCESS (BY INDEX ROWID) OF
13 12 INDEX (RANGE SCAN) OF
14 11 TABLE ACCESS (BY INDEX ROWID) OF
15 14 INDEX (UNIQUE SCAN) OF
16 10 TABLE ACCESS (FULL) OF 'SALE_STAFF'(Cost=290 Card=585 Bytes=15210)
17 9 TABLE ACCESS (BY INDEX ROWID) OF
18 17 INDEX (UNIQUE SCAN) OF
19 8 TABLE ACCESS (BY INDEX ROWID) OF
20 19 INDEX (RANGE SCAN) OF 'MAP_GEO'(NON-UNIQUE) (Cost=1 Card=1)
21 7 TABLE ACCESS (BY INDEX ROWID) OF 'SALE_STAFF' (Cost=2 Card=1 Bytes=13)
22 21 INDEX (RANGE SCAN) OF 'MAP_GEO' (NON-UNIQUE)(Cost=1 Card=1)
23 2 TABLE ACCESS (BY INDEX ROWID) OF 'FIELD_MAP' (Cost=1 Card=1 Bytes=18)
24 23 INDEX (UNIQUE SCAN) OF 'FIELD_MAP_VR2' (UNIQUE) 25 1 TABLE ACCESS (BY INDEX ROWID) OF 'SALE_STAFF' (Cost=2Card=1 Bytes=13)
26 25 INDEX (RANGE SCAN) OF 'MAP_GEO' (NON-UNIQUE) (Cost=1 Card=1)
Statistics
112 recursive calls 0 db block gets 46198380 consistent gets 74637 physical reads 0 redo size 2322838 bytes sent via SQL*Net to client 43800 bytes received via SQL*Net from client 651 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 9737 rows processed
VPSL_at_sl_prod> Received on Wed Nov 10 2004 - 09:28:16 CST