Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Query works fine in 8i and not in 9i

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

From: <SJHussain_at_alfransi.com.sa>
Date: Wed, 11 Aug 2004 18:08:50 +0300
Message-ID: <OFFA58D438.9878D5FC-ON43256EED.0052FB2A-43256EED.00533502@AlFransi.com.sa>

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
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_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

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_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:01:59 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US