| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Query works fine in 8i and not in 9i
I have faced the same problem when we migrated from 8i to 9i. Queries that were talking minuts were taking ages to finish in 9i. I even tried setting optimizer_features_enable=8.1.7 but no result. I had a similar problem where 8i was using nested loop and 9i was using hash join, what I did was is just add a hint to USE_NL(tablename) and it did the trick for me.
Best Regards,
Syed Jaffar Hussain
"Guerra, Abraham
J"
<AGUERRA_at_amfam.co To
m> <oracle-l_at_freelists.org>
Sent by: cc
oracle-l-bounce_at_f
reelists.org Subject
Query works fine in 8i and not in
9i
08/11/2004 06:01
PM
Please respond to
oracle-l_at_freelist
s.org
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
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
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.
This is the culprit query ( I know it is not pretty!):
INSERT INTO PS_CLO_LEDG_TMP001
(ACCOUNT,ACCOUNTING_PERIOD,AFFILIATE,AF_GEOMKT,AF_SLSMKT,
AF_SUBACCT,ALTACCT,BASE_CURRENCY,BUSINESS_UNIT,CURRENCY_CD,DEPTID,DTTM_S
TAMP_SEC,
FISCAL_YEAR,LEDGER,OPERATING_UNIT,POSTED_BASE_AMT,POSTED_TOTAL_AMT,POSTE
D_TRAN_AMT,
PROCESS_INSTANCE,PRODUCT,PROJECT_ID,STATISTICS_CODE,CF_GROUP_NBR,CLOS_PR
OC_FLG,
CURRENCY_CD1,FOREIGN_AMOUNT,FOREIGN_CURRENCY,MONETARY_AMOUNT,SCENARIO,SE
QNUM)=20
SELECT A.ACCOUNT,
0,A.AFFILIATE,A.AF_GEOMKT,A.AF_SLSMKT,A.AF_SUBACCT,A.ALTACCT,
A.BASE_CURRENCY,'XXXX',A.CURRENCY_CD,A.DEPTID,
TO_DATE(SUBSTR('2004-08-10-13.53.06.780000', 0, 19), 'YYYY-MM-DD
HH24:MI:SS'),2004,
A.LEDGER,A.OPERATING_UNIT, -SUM(A.POSTED_BASE_AMT),
-SUM(A.POSTED_TOTAL_AMT),=20
-SUM(A.POSTED_TRAN_AMT),0009999999,A.PRODUCT,
A.PROJECT_ID,A.STATISTICS_CODE, 0, 0,' ', 0.0,' ', 0,' ', 0=20
FROM PS_LEDGER A,PS_CLO_ACCT_TMP001 C=20
WHERE A.BUSINESS_UNIT=3D'XXXX'=20
AND A.LEDGER IN ('NON-CASH')=20
AND A.FISCAL_YEAR=3D2004=20
AND A.ACCOUNTING_PERIOD>=3D1=20
AND A.ACCOUNTING_PERIOD<=3D998=20
AND A.ACCOUNT=3DC.ACCOUNT=20
AND C.BALANCE_FWD_SW=3D'N'=20
AND C.STATISTICS_ACCOUNT=3D'N'=20
A.ACCOUNT,A.ALTACCT,A.OPERATING_UNIT,A.DEPTID,A.PRODUCT, A.PROJECT_ID,A.AF_GEOMKT,A.AF_SLSMKT,A.AF_SUBACCT,A.AFFILIATE, A.STATISTICS_CODE,A.BASE_CURRENCY;
Any help is appreciated.
Abraham Guerra
Oracle DBA
American Family Insurance
Visit us on www.alfransi.com.sa
====== Banque Saudi Fransi - Privacy Notice ======
This message is intended only for the person or entity to which it is
addressed and may contain confidential and/or privileged material. Any
use of this information by persons or entities other than the intended
recipient is prohibited. If you have received this in error, please
contact the sender and delete the material from your computer. Any
opinions and other information contained in this message that do not
relate to the official business of Banque Saudi Fransi shall be
understood as neither given or endorsed by it. Although precautions
have been taken to ensure no viruses are present in this email, BSF
cannot accept responsibility for any loss or damage arising from the
use of this email or attachments.
====== Banque Saudi Fransi - Privacy Notice ======
 |
 |