Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Request in 9i slower than in 8i ...

Request in 9i slower than in 8i ...

From: zeb <spam_at_nowhere.com>
Date: Thu, 20 May 2004 08:57:24 +0200
Message-ID: <40ac5592$0$14063$626a14ce@news.free.fr>

Hi,

For a migration from 8i to 9i
a request is slower in 9i than 8i ( x5 ) the excution plans are different
why ? and how can I speed my request in 9i as it was in 8i ??

Thanks in advance...

Here is my request:
( env: oracle 8.1.7.4/AIX 4.3.3 , 9.2.0.5/AIX 5.1 )

select count(*) from dstmp.stock_ims a
  where exists

             (select 1 from dstmp.ods_stock_ims b
               where
                       a.company               = b.company
               and     a.sitinfo               = b.sitinfo
               and     a.fiscal_year           = b.fiscal_year
               and     a.period            8        = b.period
               )

8i
Execution Plan


   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=486 Card=1 Bytes=14)    1 0 SORT (AGGREGATE)

   2    1     FILTER
   3    2       INDEX (FAST FULL SCAN) OF 'STOCK_IMS_PK' (UNIQUE) (Cos
          t=486 Card=51086 Bytes=715204)

   4    2       INDEX (RANGE SCAN) OF 'ODS_STOCK_IMS_I1' (NON-UNIQUE)
          (Cost=442 Card=54989 Bytes=714857)


9i
Execution Plan


   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1952 Card=1 Bytes=34
          )

   1    0   SORT (AGGREGATE)
   2    1     HASH JOIN (SEMI) (Cost=1952 Card=48740 Bytes=1657160)
   3    2       INDEX (FAST FULL SCAN) OF 'STOCK_IMS_PK' (UNIQUE) (Cos
          t=811 Card=1108830 Bytes=18850110)

   4    2       INDEX (FAST FULL SCAN) OF 'ODS_STOCK_IMS_I1' (NON-UNIQ
          UE) (Cost=173 Card=229105 Bytes=3894785)
Received on Thu May 20 2004 - 01:57:24 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US