Re: Query works fine in 8i and not in 9i

  • From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Wed, 11 Aug 2004 10:26:37 -0600

Hmm, I never received the original post. So I'll reply to the reply.

              "Guerra, Abraham
>              J"
>              <AGUERRA@xxxxxxxx                                          To
>              m>                        <oracle-l@xxxxxxxxxxxxx>
>I have a query that runs fast in SUN 8i and terrible in HP 9i.=20
>
>This is the execution plan in 8i (8.1.7.2)
>
>   INSERT STATEMENT     CHOOSE  Cost=3D20 Rows Expected=3D11
>     SORT  GROUP BY     Cost=3D20 Rows Expected=3D11
>       NESTED LOOPS       Cost=3D18 Rows Expected=3D11
>         TABLE ACCESS  FULL  SYSADM. PS_CLO_ACCT_TMP001 ANALYZED  =
>Cost=3D1
>Rows Exected=3D1
>         TABLE ACCESS  BY INDEX ROWID  SYSADM. PS_LEDGER ANALYZED
>Cost=3D17 Rows Expected=3D15779
>           INDEX  RANGE SCAN  SYSADM. PS_LEDGER ANALYZED  Cost=3D5 Rows
>Expected=3D1779
>
>This is the execution plan in 9i (9.2.0.5)
>
>   INSERT STATEMENT     CHOOSE  Cost=3D1951 Rows Expected=3D4978
>     SORT  GROUP BY     Cost=3D1951 Rows Expected=3D4978
>       HASH JOIN       Cost=3D1867 Rows Expected=3D4978
>         TABLE ACCESS  FULL  SYSADM. PS_CLO_ACCT_TMP001 ANALYZED  =
>Cost=3D2
>Rows Expected=3D499
>         TABLE ACCESS  BY INDEX ROWID  SYSADM. PS_LEDGER ANALYZED
>Cost=3D1863 Rows Expected=3D4233
>           INDEX  RANGE SCAN  SYSADM. PSCLEDGER ANALYZED  Cost=3D67 Rows
>Expected=3D10
>
>Both environments have the same amount of rows and have been analyzed.

If both environment have the same amount of rows, then how come the number 
of rows expected from table PS_CLO_ACCT_TMP001  in the 8.1.7 plan is 1, 
whereas in the 9.2 plan it is 499? The numbers of expected rows for ledger 
are different too, but it is a bit harder to compare because different 
indexes are being used.

Have a closer look at the statistics of your tables and perhaps run a 10053 
trace to find out why the 9.2 optimizer chooses a different path. But it 
seems to me that the statistics are quite different, particularly for the 
temp table. In the 8.1.7 case it looks as if the statistics were gathered 
when the temp table was empty and maybe in the 9.2 instance the temp table 
has no statistics at all?



Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com 

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: