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

From: Daniel W. Fink <Daniel.Fink_at_Sun.COM>
Date: Wed, 11 Aug 2004 09:57:55 -0600
Message-id: <411A4203.4070703@sun.com>

Abraham,

If you hint the 9.2 query to use a nested_loop, what is the performance?

There are some significant differences in the costs and rows expected between the plans. This tells me that there are some significant differences in the parameters, statistics and/or nature/layout (# of data rows is the same) of the data. For example, if the number of leaf blocks and the # of distinct keys are different, the number of i/o operations to retrieve the index entries will be different (reflected in the cost). If the clustering factor of the index is different, the number of i/os to retrieve the table records will be different.

Regards,
Daniel Fink

Guerra, Abraham J wrote:

>Hello list members,
>
>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.
>
>
>

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 - 10:54:07 CDT