Re: Query works fine in 8i and not in 9i
- From: "Daniel W. Fink" <Daniel.Fink@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 11 Aug 2004 09:57:55 -0600
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@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:
