Re: Query works fine in 8i and not in 9i
- From: SJHussain@xxxxxxxxxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 11 Aug 2004 18:08:50 +0300
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@xxxxxxxx To
m> <oracle-l@xxxxxxxxxxxxx>
Sent by: cc
oracle-l-bounce@f
reelists.org Subject
Query works fine in 8i and not in
9i
08/11/2004 06:01
PM
Please respond to
oracle-l@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
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.
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
GROUP BY A.LEDGER,A.CURRENCY_CD,
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
----------------------------------------------------------------
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
-----------------------------------------------------------------
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 ======
----------------------------------------------------------------
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:
