Home » SQL & PL/SQL » SQL & PL/SQL » Table Analysis (oracle 10g)
Table Analysis [message #362175] Mon, 01 December 2008 04:40 Go to next message
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
Senior Member
Hi,

when i run the below given query it will take 43 minutes to execute. But if i run table analysis script it will take few sec to execute. Can any one please explain the behaviour.

SELECT FCT_MIT.MIS_DATE,FCT_MIT.V_ID,to_char(FCT_MIT.N_ISSUER_SKEY),to_char(FCT_MIT.N_RESIDUAL_MATURITY),
to_char(FCT_MIT.N_MIPE_SKEY),FCT_MIT.V_CCY_CODE,to_char(FCT_MIT.N_MITIGANT_TYPE_SKEY),
to_char(FCT_MIT.N_MI_SKEY),FCT_MIT.F_DISTRESS_RESTRUCTURING,to_char(FCT_MIT.SKEY),
to_char(FCT_SAM.N_RESIDUAL_MATURITY),to_char(FCT_SAM.N_ORIGINAL_MATURITY),to_char(FCT_SAM.N_POST_VOL_HC_EAD_AMT),
to_char(FCT_SAM.N_POST_VOL_HC_EAD_AMT_RCY),to_char(FCT_SAM.N_ACCT_SKEY),to_char(FCT_SAM.N_PRE_MITIGATION_RW_UL),
to_char(FCT_MIT.N_BASEL_RATING),FCT_SAM.V_CCY_CODE,to_char(FCT_MIT.N_BASEL_ISSUER_TYPE_SKEY),
to_char(FCT_MIT.N_COLLATERAL_ORIG_AMOUNT_RCY),COALESCE(FCT_MIT.F_ELIGIBILITY_FLAG,'Y'),
(CASE WHEN DIM_MITIGANT_TYPE.V_MITIGANT_TYPE ='COL' THEN '1'  ELSE '2'  END),to_char(FCT_MIT.N_FX_RATE),
to_char(FCT_SAM.N_FX_RATE),to_char(FCT_MIT.N_HLDG_PERIOD_FOR_HAIRCUT),to_char(FCT_MIT.N_MIN_HOLDING_PERIOD),
to_char(FCT_MIT.N_REMARGIN_FREQUENCY),to_char(FCT_SAM.N_GAAP_SKEY),FCT_MIT.F_TRADING_BOOK_GTY,0,FCT_SAM.V_DATA_ORIGIN,
(CASE WHEN DIM_MITIGANT_TYPE.V_MITIGANT_TYPE ='COL'  and DIM_TAB1.V_MITI_SUB_CODE='101030'  
and DIM_GAAP.V_GAAP_CODE = 'MA' THEN (1-coalesce( FCT_MIT.N_RISK_WEIGHT,0))* FCT_MIT.N_MITIGANT_VALUE_NCY 
ELSE FCT_MIT.N_MITIGANT_VALUE_NCY END),(CASE WHEN DIM_MITIGANT_TYPE.V_MITIGANT_TYPE ='COL'  
and DIM_TAB1.V_MITI_SUB_CODE='101030'  and DIM_GAAP.V_GAAP_CODE = 'MA' 
THEN (1-coalesce( FCT_MIT.N_RISK_WEIGHT,0))* FCT_MIT.N_MITIGANT_VALUE ELSE FCT_MIT.N_MITIGANT_VALUE END),
CASE WHEN DIM_GAAP.V_GAAP_CODE='MA' AND DIM_TAB1.V_MITI_SUB_CODE ='101030' 
THEN 0 ELSE FCT_MIT.N_RISK_WEIGHT END	 
FROM MASTER.DIM_GAAP,MASTER.FCT_MIT,MASTER.FCT_SAM,MASTER.DIM_MITIGANT_TYPE,MASTER.DIM_TAB1,MASTER.EXP_MIT where (( 1=1 ) AND (CASE WHEN Dim_G.f_latest_record_indicator ='Y' THEN Dim_G.v_g_code END = 'PL') AND ( 1=1 ) AND ( 1=1 )) AND EXP_MIT.MIS_DATE = FCT_MIT.MIS_DATE
AND FCT_MIT.MIS_DATE= FCT_SAM.MIS_DATE
AND FCT_MIT.V_ID = EXP_MIT.V_ID
AND FCT_SAM.V_ID=FCT_MIT.V_ID
AND FCT_SAM.N_GAAP_SKEY=DIM_GAAP.N_GAAP_SKEY
AND EXP_MIT.N_MI_SKEY=FCT_MIT.N_MI_SKEY
AND FCT_MIT.N_MITIGANT_TYPE_SKEY= DIM_MITIGANT_TYPE.N_MITIGANT_TYPE_SKEY 
AND FCT_SAM.F_OTC_IND='N'
AND EXP_MIT.V_LINKAGE_ID = FCT_SAM.V_INSTRUMENT_ID
AND EXP_MIT.V_LINKAGE_TYPE = 'IN'
AND FCT_MIT.SKEY=DIM_TAB1.SKEY
 and FCT_MIT.MIS_DATE= to_date('20081127','YYYYMMDD') AND FCT_MIT.V_ID = '1188923812038'  AND FCT_SAM.N_POST_VOL_HC_EAD_AMT_RCY > 0


Thanks -ravi
Re: Table Analysis [message #362177 is a reply to message #362175] Mon, 01 December 2008 04:46 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Oracle might be able to explain it.

Why don't you run an explain plan for the query?

And why don't you check all the other things mentioned in the sticky post in performance tuning forum first?

Re: Table Analysis [message #362182 is a reply to message #362175] Mon, 01 December 2008 04:54 Go to previous messageGo to next message
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
Senior Member
Thanks for replying.

Ok how can i check the statics of a given table.
Re: Table Analysis [message #362184 is a reply to message #362182] Mon, 01 December 2008 04:58 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Read the sticky post of the performance tuning forum.

Also have a look Performance Tuning Guide, especially the Using EXPLAIN PLAN section.
Previous Topic: using the SYS_GUID() function
Next Topic: Alternative syntax for Self joining of a table 3 times
Goto Forum:
  


Current Time: Wed Dec 07 04:51:47 CST 2016

Total time taken to generate the page: 0.24977 seconds