DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: SQL Queries Taking longer time to retrieve

  1. 04-17-2008, 05:20 AM #1
    Ibbs
    Ibbs is offline Junior Member
    Join Date
    Apr 2008
    Posts
    2

    SQL Queries Taking longer time to retrieve

    Hello,

    I need urgent help on Query Tuning as I am having critical situation.

    I have SQL query, which is taking longer time to execute, The explain plan is good, with no Full table scan happening, but the SQL query contains lot of joins and it is also using Materialized views.

    The query takes more than 8 minutes to retrieve 16500 records (prod, stage and dev) environment. In Toad the query retrieves in 10 secs but when you do the record count it takes 8 minutes or If I run the query from SQL PLUS on database server it takes more than 8 minutes to display all the information.


    The query and it's execution plan is listed below.

    Query:-
    Code:
    SELECT 
  ACCTG_METHD_DIM.ACCTG_METHD_CD,
  trunc(DT_DIM.ACCTG_MONTH_END_DT),
  MTHLY_SECURTY_MV.ALTERNT_AMORTZN_DT,
  MTHLY_SECURTY_MV.DEAL_TICKER_CD,
  UPPER(PRTFLIO_MV.BIZ_CD),
  MTHLY_SECURTY_MV.CALL_IND,
  MTHLY_SECURTY_MV.RGSTRD_AS_RULE144A_IND,
  PRTFLIO_MV.ACCTG_SYSTM_LEGAL_ENTITY_CD,
  UPPER(PRTFLIO_MV.LEGAL_ENTITY_NAME),
  MTHLY_SECURTY_MV.COLATRL_DESC,
  MTHLY_SECURTY_MV.CONVRTBL_IND,
  MTHLY_ISSUR_MV.DOMICIL_CNTRY_NAME,
  MTHLY_SECURTY_MV.DFLT_DT,
  MTHLY_SECURTY_MV.COUPN_TYPE_DESC,
  UPPER(MTHLY_SECURTY_MV.GL_EUROPE_GRP_NAME),
  MTHLY_SECURTY_MV.IN_DFLT_IND,
  MTHLY_SECURTY_MV.LEHMAN_INDSTRY_LVL_1_NAME,
  MTHLY_SECURTY_MV.LEHMAN_INDSTRY_LVL_2_NAME,
  MTHLY_SECURTY_MV.INCM_CRRNCY_CD,
  MTHLY_SECURTY_MV.CALLBL_WITH_MAKE_WHOLE_IND,
  MTHLY_SECURTY_MV.ACCTG_SYSTM_MATURTY_DT,
  MTHLY_SECURTY_MV.FUNDNG_SRC_CD,
  MTHLY_SECURTY_MV.NAIC_RATING_CD,
  MTHLY_SECURTY_MV.NAIC_RATING_EQV_NUMRC_QTY,
  MTHLY_POSITN_SNPSHT_FACT.ACQSTN_SETLMNT_DT,
  MTHLY_ISSUR_MV.PARENT_ISSUR_LONG_NAME,
  MTHLY_SECURTY_MV.PERPETL_IND,
  MTHLY_SECURTY_MV.ACCTG_SYSTM_PRIMRY_SECURTY_ID,
  MTHLY_SECURTY_MV.PRV_PLCMNT_IND,
  MTHLY_SECURTY_MV.SECURTY_CLSFCN_CLSS_DESC,
  MTHLY_SECURTY_MV.SECURTY_CLSFCN_GRP_DESC,
  MTHLY_SECURTY_MV.SECURTY_CLSFCN_SUB_TYPE_DESC,
  MTHLY_SECURTY_MV.SECURTY_CLSFCN_TYPE_DESC,
  MTHLY_SECURTY_MV.SINK_FUND_IND,
  decode(MTHLY_SECURTY_MV.COUPN_TYPE_DESC,'STEP','Y','N'),
  MTHLY_SECURTY_MV.BOND_STRUCTR_TXT,
  MTHLY_POSITN_SNPSHT_FACT.ACCTG_SYSTM_LOT_NBR,
  MTHLY_SECURTY_MV.TRNCHE_CD,
  UPPER(MTHLY_SECURTY_MV.GL_USA_GRP_NAME),
  MTHLY_SECURTY_MV.SECURTY_DSGNTD_STRUCTR_DESC,
  MTHLY_POSITN_SNPSHT_FACT.RESTRCTN_IND,
  MTHLY_POSITN_SNPSHT_FACT.RESTRCTN_DESC,
  case when 
(UPPER(MTHLY_SECURTY_MV.GL_USA_GRP_NAME)<>'FAS115 TRADING' or UPPER(MTHLY_SECURTY_MV.GL_EUROPE_GRP_NAME)<>'FAS115 TRADING')
then 
MTHLY_POSITN_SNPSHT_FACT.LOCL_BOOK_VALUE_AMT * LOCL_CRRNCY_EXCHNG_RATE_FACT.MTHLY_SPOT_CRRNCY_EXCHNG_RATE
else
MTHLY_POSITN_SNPSHT_FACT.FNCTL_BOOK_VALUE_AMT * CRRNCY_EXCHNG_RATE_FACT.MTHLY_SPOT_CRRNCY_EXCHNG_RATE
end,
  case when MTHLY_POSITN_SNPSHT_FACT.FNCTL_ENDNG_UNREALZD_MKT_AMT >= 0 then MTHLY_POSITN_SNPSHT_FACT.FNCTL_ENDNG_UNREALZD_MKT_AMT * CRRNCY_EXCHNG_RATE_FACT.MTHLY_SPOT_CRRNCY_EXCHNG_RATE else 0 end,
  case when MTHLY_POSITN_SNPSHT_FACT.FNCTL_ENDNG_UNREALZD_MKT_AMT < 0 then MTHLY_POSITN_SNPSHT_FACT.FNCTL_ENDNG_UNREALZD_MKT_AMT * CRRNCY_EXCHNG_RATE_FACT.MTHLY_SPOT_CRRNCY_EXCHNG_RATE else 0 end,
  MTHLY_POSITN_SNPSHT_FACT.FNCTL_MKT_VALUE_AMT * CRRNCY_EXCHNG_RATE_FACT.MTHLY_SPOT_CRRNCY_EXCHNG_RATE,
  MTHLY_POSITN_SNPSHT_FACT.FNCTL_CARRY_VALUE_AMT *  CRRNCY_EXCHNG_RATE_FACT.MTHLY_SPOT_CRRNCY_EXCHNG_RATE,
  MTHLY_POSITN_SNPSHT_FACT.POSITN_QTY,
  MTHLY_SECURTY_MV.EFF_MATURTY_DT,
  PRTFLIO_MV.PRTFLIO_BASE_CRRNCY_DESC,
  MTHLY_SECURTY_MV.ISSUR_CNTRY_NAME,
  MTHLY_SECURTY_MV.LEHMAN_INDSTRY_LVL_3_NAME,
  MTHLY_SECURTY_MV.PRNCPL_CRRNCY_CD,
  PRTFLIO_MV.OLD_PRTFLIO_SHORT_NAME,
  MTHLY_POSITN_SNPSHT_FACT.MGR_DESC,
  PRTFLIO_MV.OLD_PRTFLIO_LONG_NAME,
  PRTFLIO_MV.PRTFLIO_SHORT_NAME,
  PRTFLIO_MV.PRTFLIO_LONG_NAME,
  MTHLY_SECURTY_MV.AGNCY_CD,
  MTHLY_SECURTY_MV.BRS_INTRNL_RATING_EQV_QTY,
  MTHLY_SECURTY_MV.BRS_MOODY_RATING_CD,
  MTHLY_SECURTY_MV.BRS_MOODY_RATING_EQV_NUMRC_QTY,
  MTHLY_SECURTY_MV.BRS_HIGH_RATING_EQV_NUMRC_QTY,
  MTHLY_SECURTY_MV.BRS_LOW_RATING_EQV_NUMRC_QTY,
  MTHLY_SECURTY_MV.BRS_SP_RATING_CD,
  MTHLY_SECURTY_MV.BRS_SP_RATING_EQV_NUMRC_QTY,
  MTHLY_SECURTY_MV.INSTRUMNT_TYPE_DESC,
  MTHLY_SECURTY_MV.BRS_SECURTY_CLSFCN_GRP_DESC,
  MTHLY_SECURTY_MV.BRS_SECURTY_CLSFCN_TYPE_DESC,
  MTHLY_ISSUR_MV.ISSUR_LONG_NAME,
  MTHLY_POSITN_SNPSHT_FACT.BOOK_YIELD_RATE,
  MTHLY_SECURTY_MV.FLOAT_RATE_IND,
  MTHLY_ISSUR_MV.ULTMT_PARENT_ISSUR_LONG_NAME,
  MTHLY_SECURTY_MV.GUARNTR_LONG_NAME
FROM
  OASYS_MTHLY_RDM_A01.LOCL_CRRNCY_EXCHNG_RATE_FACT,
  OASYS_MTHLY_RDM_A01.ACCTG_METHD_DIM,
  OASYS_MTHLY_RDM_A01.CRRNCY_EXCHNG_RATE_FACT,
  OASYS_MTHLY_RDM_A01.DT_DIM,
  OASYS_MTHLY_RDM_A01.MTHLY_SECURTY_MV,
  OASYS_MTHLY_RDM_A01.PRTFLIO_MV,
  OASYS_MTHLY_RDM_A01.MTHLY_ISSUR_MV,
 OASYS_MTHLY_RDM_A01.MTHLY_POSITN_SNPSHT_FACT
WHERE
  ( PRTFLIO_MV.PRTFLIO_DIM_ID=MTHLY_POSITN_SNPSHT_FACT.PRTFLIO_DIM_ID  )
  AND  ( MTHLY_POSITN_SNPSHT_FACT.SECURTY_ASSET_ODS_ID=MTHLY_SECURTY_MV.SECURTY_ASSET_ODS_ID AND
MTHLY_POSITN_SNPSHT_FACT.ACCTG_PERIOD_END_DT_DIM_ID=MTHLY_SECURTY_MV.ACCTG_DT_DIM_ID  )
  AND  ( MTHLY_POSITN_SNPSHT_FACT.ACCTG_METHD_DIM_ID=ACCTG_METHD_DIM.ACCTG_METHD_DIM_ID  )
  AND  ( DT_DIM.DT_DIM_ID=MTHLY_POSITN_SNPSHT_FACT.ACCTG_PERIOD_END_DT_DIM_ID  )
  AND  ( MTHLY_SECURTY_MV.MTHLY_REF_SNPSHT_ID=MTHLY_ISSUR_MV.MTHLY_REF_SNPSHT_ID(+)  )
  AND  ( MTHLY_POSITN_SNPSHT_FACT.ACCTG_PERIOD_END_DT_DIM_ID=CRRNCY_EXCHNG_RATE_FACT.CRRNCY_EXCHNG_DT_DIM_ID 
and MTHLY_POSITN_SNPSHT_FACT.FNCTL_CRRNCY_DIM_ID=CRRNCY_EXCHNG_RATE_FACT.FROM_CRRNCY_DIM_ID
AND CRRNCY_EXCHNG_RATE_FACT.TO_CRRNCY_CD ='USD' )
  AND  ( MTHLY_POSITN_SNPSHT_FACT.ACCTG_PERIOD_END_DT_DIM_ID=LOCL_CRRNCY_EXCHNG_RATE_FACT.CRRNCY_EXCHNG_DT_DIM_ID 
and MTHLY_POSITN_SNPSHT_FACT.LOCL_CRRNCY_DIM_ID=LOCL_CRRNCY_EXCHNG_RATE_FACT.FROM_CRRNCY_DIM_ID
AND LOCL_CRRNCY_EXCHNG_RATE_FACT.TO_CRRNCY_CD ='USD' )
  AND  (
  UPPER(PRTFLIO_MV.BIZ_CD)  =  'GNW - DOMESTIC LIFE'
  AND  ACCTG_METHD_DIM.ACCTG_METHD_CD  =  'GAAP'
  AND  UPPER(PRTFLIO_MV.LEGAL_ENTITY_NAME)  NOT IN  ('SPV6', 'SPV7', 'SPV8', 'SPV9')
  AND  ( (trunc(DT_DIM.ACCTG_DT) ='29-feb-2008'  
)  )
  )
    Execution Plan:-
    Code:
    Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS		1  	 	711  	 	      	             	 
  MAT_VIEW ACCESS BY INDEX ROWID	OASYS_ADM_S01.CRRNCY_EXCHNG_RATE_FACT_MV	1  	26  	172  	 	      	             	 
    NESTED LOOPS		1  	563  	711  	 	      	             	 
      NESTED LOOPS		1  	537  	539  	 	      	             	 
        NESTED LOOPS OUTER		1  	511  	368  	 	      	             	 
          NESTED LOOPS		1  	450  	366  	 	      	             	 
            NESTED LOOPS		1  	239  	361  	 	      	             	 
              NESTED LOOPS		1  	219  	360  	 	      	             	 
                NESTED LOOPS		2  	422  	358  	 	      	             	 
                  MAT_VIEW ACCESS FULL	OASYS_ADM_S01.PRTFLIO_MV	1  	124  	10  	 	      	             	 
                  MAT_VIEW ACCESS BY INDEX ROWID	OASYS_MTHLY_RDM_S01.MTHLY_POSITN_SNPSHT_FACT_MV	1 K	88 K	347  	 	      	             	 
                    INDEX RANGE SCAN	OASYS_MTHLY_RDM_S01.MTHLY_POS_SNPSHT_FACT_MV_XIE0	1 K	 	1  	 	      	             	 
                TABLE ACCESS BY INDEX ROWID	OASYS_ADM_S01.ACCTG_METHD_DIM	1  	8  	1  	 	      	             	 
                  INDEX RANGE SCAN	OASYS_ADM_S01.ACCTG_METHD_DIM_XIE1	1  	 	0  	 	      	             	 
              TABLE ACCESS BY INDEX ROWID	OASYS_ADM_S01.DT_DIM	1  	20  	1  	 	      	             	 
                INDEX UNIQUE SCAN	OASYS_ADM_S01.DT_DIM_XPK	1  	 	0  	 	      	             	 
            MAT_VIEW ACCESS BY INDEX ROWID	OASYS_MTHLY_RDM_S01.MTHLY_SECURTY_MV	1  	211  	5  	 	      	             	 
              INDEX RANGE SCAN	OASYS_MTHLY_RDM_S01.MTHLY_SECURTY_MV_XIE1	4  	 	1  	 	      	             	 
          MAT_VIEW ACCESS BY INDEX ROWID	OASYS_MTHLY_RDM_S01.MTHLY_ISSUR_MV	1  	61  	2  	 	      	             	 
            INDEX RANGE SCAN	OASYS_MTHLY_RDM_S01.MTHLY_ISSUR_MV_XIE1	1  	 	1  	 	      	             	 
        MAT_VIEW ACCESS BY INDEX ROWID	OASYS_ADM_S01.CRRNCY_EXCHNG_RATE_FACT_MV	1  	26  	172  	 	      	             	 
          INDEX RANGE SCAN	OASYS_ADM_S01.CRRNCY_EXC_RT_FACT_MV_XIE1	11 K	 	12  	 	      	             	 
      INDEX RANGE SCAN	OASYS_ADM_S01.CRRNCY_EXC_RT_FACT_MV_XIE1	11 K	 	12
    I am not able to understand why the query takes 8 minutes to retrieve just 16000 records.


    Thanks,
    Ibbs
    Last edited by davey23uk; 04-17-2008 at 08:40 AM.
    Reply With Quote Reply With Quote
  2. 04-17-2008, 03:52 PM #2
    monto1
    monto1 is offline Junior Member
    Join Date
    Dec 2007
    Posts
    55
    are Stats on the tables involved in the query are up to date?
    Reply With Quote Reply With Quote
  3. 04-18-2008, 03:07 AM #3
    Ibbs
    Ibbs is offline Junior Member
    Join Date
    Apr 2008
    Posts
    2
    yes stats for the query are up todate. Please find the attached trace output for the query.
    Reply With Quote Reply With Quote
  4. 04-18-2008, 03:08 AM #4
    PAVB's Avatar
    PAVB
    PAVB is offline Senior Advisor
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Trace it. TKprof it. Look for wait events.

    If needed, post relevant section of trace.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
    Reply With Quote Reply With Quote
  5. 04-18-2008, 04:44 PM #5
    LKBrwn_DBA's Avatar
    LKBrwn_DBA
    LKBrwn_DBA is offline Senior Advisor
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool

    Maybe you need another index for the MV:

    Code:
    ...
     MAT_VIEW ACCESS FULL	OASYS_ADM_S01.PRTFLIO_MV
...
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules

Click Here to Expand Forum to Full Width