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

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Wed, 11 Aug 2004 10:26:37 -0600
Message-Id: <6.1.0.6.2.20040811093114.02d44760@pop.centrexcc.com>

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

              "Guerra, Abraham


>              J"

>              <AGUERRA_at_amfam.co                                          To

>              m>                        <oracle-l_at_freelists.org>

>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_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed Aug 11 2004 - 11:22:30 CDT