Re: Queries slow after Upgrade to Oracle 9i

From: DB Dude <DBdude_From_NY_at_yahoo.com>
Date: 10 Nov 2004 07:28:16 -0800
Message-ID: <f65280c4.0411100728.2b29dd21@posting.google.com>

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=290

Card=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=1

Bytes=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

'SALE_STAFF' (Cost=2 Card=1 Bytes=26)

  13   12                         INDEX (RANGE SCAN) OF

'EMPL_INFO_ID'(NON-UNIQUE) (Cost=1 Card=1)

  14   11                       TABLE ACCESS (BY INDEX ROWID) OF

'FIELD_MAP' (Cost=1 Card=1 Bytes=18)

  15   14                         INDEX (UNIQUE SCAN) OF

'FIELD_MAP_VR2' (UNIQUE)

  16   10                     TABLE ACCESS (FULL) OF 'SALE_STAFF'

(Cost=290 Card=585 Bytes=15210)

  17    9                   TABLE ACCESS (BY INDEX ROWID) OF

'FIELD_MAP' (Cost=1 Card=1 Bytes=18)

  18   17                     INDEX (UNIQUE SCAN) OF

'FIELD_MAP_VR2'(UNIQUE)

  19    8                 TABLE ACCESS (BY INDEX ROWID) OF

'SALE_STAFF' (Cost=2 Card=1 Bytes=13)

  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=2

Card=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