Home » RDBMS Server » Performance Tuning » COMPLEX SQL,NESTED
| COMPLEX SQL,NESTED [message #154272] |
Fri, 06 January 2006 09:41  |
feroze
Messages: 144
Registered: September 2005
Location: chicago
|
Senior Member |
|
|
Hi
I have these SQL in a cursor of a procedure, variables are declared in procedures,
These SQL are taking time when called in reports,
STATS are updated one,
Please suggest any modifications to these SQL code from performance point of view or better approach,
/* I tried to attach .txt but not attaching so i have pasted SQL and PLAN also.*/
// FIRST SQL **/
SELECT b.bus_ut_long_nm bu_name
, ar.bus_ut_ky bus_key
, a.account_ky acc_ky
, a.account_id acc_id
, a.account_long_nm acc_nm
, ar.end_market_value_am
, ar.date_ky rel_ky
, c.calendar_dt acct_rel_date
, ar.rate_of_returns_pt
, sec.industry_hierarchy_level_ky sec_ky
, sec.industry_hierarchy_level_no ind_lvl_no
, sec.industry_hierarchy_level1_nm sector1
, sec.industry_hierarchy_level2_nm sector2
, sec.industry_hierarchy_level3_nm sector3
, sec.industry_hierarchy_level1_id ind_lvl1_id
, sec.industry_hierarchy_level2_id ind_lvl2_id
, sec.industry_hierarchy_level3_id ind_lvl3_id
, sec.industry_hierarchy_level4_id ind_lvl4_id
, ctry.country_ky ctry_ky
, ctry.country_nm COUNTRY
, rgn.region_ky regn_ky
, rgn.client_region_nm regn_nm
, rgn.client_region_id regionid
, inmt.inmt_type_hierarchy_level_ky int_ky
, inmt.inmt_type_hierarchy_level_no lvl_no
, inmt.inmt_type_hierarchy_level1_nm level1_nm
, inmt.inmt_type_hierarchy_level2_nm level2_nm
, inmt.inmt_type_hierarchy_level3_nm level3_nm
, inmt.inmt_type_hierarchy_level4_nm level4_nm
, inmt.inmt_type_hierarchy_level5_nm level5_nm
, inmt.inmt_type_hierarchy_level6_nm level6_nm
, inmt.inmt_type_hierarchy_level1_id lvl1_id
, inmt.inmt_type_hierarchy_level2_id lvl2_id
, inmt.inmt_type_hierarchy_level3_id lvl3_id
, inmt.inmt_type_hierarchy_level4_id lvl4_id
, inmt.inmt_type_hierarchy_level5_id lvl5_id
, inmt.inmt_type_hierarchy_level6_id lvl6_id
, ctry.country_id countryid
, ccy.currency_ky currky
, sec.industry_hierarchy_level1_id sectorid
, 'DLY' freq_type
, fct.fact_type_id
, ar.currency_code_type_ky cctyky
, ct.code_type_id cdtypeid
FROM ACCOUNT a
, BUSINESS_UNIT b
, CALENDAR_DATE c
, FACT_TYPE fct
, CODE_TYPE ct
, COUNTRY ctry
, REGION rgn
, CURRENCY ccy
, INDUSTRY_HIERARCHY sec
, INSTRUMENT_TYPE_HIERARCHY inmt
, INT_REPORT_TEMPLATE_DET rep
, DAILY_ACCOUNT_RETURNS ar
WHERE a.account_ky = ar.account_ky
AND c.date_ky = ar.date_ky
AND ctry.country_ky = ar.country_ky
AND rgn.region_ky = ar.region_ky
AND ccy.currency_ky = ar.reporting_currency_ky
AND ct.code_type_ky = ar.currency_code_type_ky
--AND ccy.currency_id = rep_currency
AND sec.industry_hierarchy_level_ky =
ar.industry_hierarchy_level_ky
AND inmt.inmt_type_hierarchy_level_ky =
ar.inst_type_hierarchy_level_ky
AND ar.bus_ut_ky = bu
AND b.bus_ut_ky = ar.bus_ut_ky
AND fct.fact_type_ky = ar.fact_type_ky
AND a.account_id = rep.report_item_id
AND rep.report_entity_id = 'ACT'
AND rep.template_id = temp_id
AND NOT (ar.END_MARKET_VALUE_AM = 0 AND ar.RATE_OF_RETURNS_PT = 0)
-- AND ar.end_market_value_am <> 0
-- AND ar.rate_of_returns_pt<>0
AND rep.current_rec_in = 'Y'
AND rep.status_in = 'A'
AND b.current_rec_in = 'Y'
-- AND ar.date_ky = rel_dt
AND (
/*ar.date_ky >= cutoff_start_dt
AND ar.date_ky <= cutoff_end_dt*/
ar.date_ky >= mindateky
AND ar.date_ky <= maxdateky
AND c.CALENDAR_DT >= a.ACCOUNT_OPEN_DT
)
AND ct.code_type_id IN ( 'PRIMRY', 'N/A' )
AND (
(
( grp = 'mgr' )
AND ar.country_ky = 0
-- AND ar.CURRENCY_CODE_TYPE_KY = 0 /* commented for now */
AND ccy.currency_id = rep_currency
AND ar.region_ky = 0
AND ar.industry_hierarchy_level_ky = 0
AND (
(
( asset_count = 0 )
AND ar.inst_type_hierarchy_level_ky = 0
AND ar.fact_type_ky = fct.fact_type_ky
AND fct.fact_type_id = tot_ror_type
)
OR (
( asset_count > 0 )
AND fct.fact_type_id = 'N/A'
AND (
inmt.inmt_type_hierarchy_level_no
, inmt.inmt_type_hierarchy_scheme_id
, inmt.inmt_type_hierarchy_level1_id
, inmt.inmt_type_hierarchy_level2_id
, inmt.inmt_type_hierarchy_level3_id
, inmt.inmt_type_hierarchy_level4_id
, inmt.inmt_type_hierarchy_level5_id
, inmt.inmt_type_hierarchy_level6_id
) IN (
SELECT hier_lvl.instrument_hier_level_no
, hier_lvl.instrument_type_hier_scheme_id
, hier_lvl.instrument_hier_level1_id
, hier_lvl.instrument_hier_level2_id
, hier_lvl.instrument_hier_level3_id
, hier_lvl.instrument_hier_level4_id
, hier_lvl.instrument_hier_level5_id
, hier_lvl.instrument_hier_level6_id
FROM INT_INSTRUMENT_HIER_LVL hier_lvl
, INT_REP_TEMP_DIM_COL_DET dim_col
WHERE hier_lvl.instrument_hier_level_id =
dim_col.entity_id
AND hier_lvl.instrument_hier_level_no =
dim_col.level_no
AND dim_col.entity_ty = 'AC'
AND dim_col.template_id = temp_id
AND dim_col.current_rec_in = 'Y'
AND dim_col.status_in = 'A' )
)
OR (
ar.inst_type_hierarchy_level_ky = 0
AND ar.fact_type_ky = fct.fact_type_ky
AND fct.fact_type_id = tot_ror_type
)
)
)
OR (
( grp = 'cty' )
AND (
(
ar.industry_hierarchy_level_ky = 0
-- AND ar.CURRENCY_CODE_TYPE_KY = 0 /* commented for now */
AND ar.region_ky = 0
AND (
(
( asset_count = 0 )
AND inmt.inmt_type_hierarchy_level_ky =
0
AND fct.fact_type_id = 'TOTFUND'
)
OR (
( asset_count > 0 )
AND (
fct.fact_type_id = 'N/A'
AND (
inmt.inmt_type_hierarchy_level_no
, inmt.inmt_type_hierarchy_scheme_id
, inmt.inmt_type_hierarchy_level1_id
, inmt.inmt_type_hierarchy_level2_id
, inmt.inmt_type_hierarchy_level3_id
, inmt.inmt_type_hierarchy_level4_id
, inmt.inmt_type_hierarchy_level5_id
, inmt.inmt_type_hierarchy_level6_id
) IN (
SELECT hier_lvl.instrument_hier_level_no
, hier_lvl.instrument_type_hier_scheme_id
, hier_lvl.instrument_hier_level1_id
, hier_lvl.instrument_hier_level2_id
, hier_lvl.instrument_hier_level3_id
, hier_lvl.instrument_hier_level4_id
, hier_lvl.instrument_hier_level5_id
, hier_lvl.instrument_hier_level6_id
FROM INT_INSTRUMENT_HIER_LVL hier_lvl
, INT_REP_TEMP_DIM_COL_DET dim_col
WHERE hier_lvl.instrument_hier_level_id =
dim_col.entity_id
AND hier_lvl.instrument_hier_level_no =
dim_col.level_no
AND dim_col.entity_ty =
'AC'
AND dim_col.template_id =
temp_id
AND dim_col.current_rec_in =
'Y'
AND dim_col.status_in =
'A' )
OR (
inmt.inmt_type_hierarchy_level_ky =
0
AND fct.fact_type_id =
tot_ror_type
)
)
)
)
)
OR (
ar.country_ky = 0
-- AND ar.CURRENCY_CODE_TYPE_KY = 0 --commented for now
AND ar.region_ky = 0
AND ar.industry_hierarchy_level_ky = 0
AND ar.inst_type_hierarchy_level_ky = 0
AND ar.fact_type_ky = fct.fact_type_ky
AND fct.fact_type_id = tot_ror_type
)
)
)
OR (
( grp = 'rgn' )
AND ar.industry_hierarchy_level_ky = 0
AND ar.country_ky = 0
AND ccy.currency_id = rep_currency
AND ar.fact_type_ky = fct.fact_type_ky
--AND fct.fact_type_id IN ('N/A', 'TOTFUND')
AND (
(
( asset_count = 0 )
AND ar.inst_type_hierarchy_level_ky = 0
AND fct.fact_type_id IN (tot_ror_type, 'TOTFUND')
)
OR (
( asset_count > 0 )
AND (
(
inmt.inmt_type_hierarchy_level_no
, inmt.inmt_type_hierarchy_scheme_id
, inmt.inmt_type_hierarchy_level1_id
, inmt.inmt_type_hierarchy_level2_id
, inmt.inmt_type_hierarchy_level3_id
, inmt.inmt_type_hierarchy_level4_id
, inmt.inmt_type_hierarchy_level5_id
, inmt.inmt_type_hierarchy_level6_id
) IN (
SELECT hier_lvl.instrument_hier_level_no
, hier_lvl.instrument_type_hier_scheme_id
, hier_lvl.instrument_hier_level1_id
, hier_lvl.instrument_hier_level2_id
, hier_lvl.instrument_hier_level3_id
, hier_lvl.instrument_hier_level4_id
, hier_lvl.instrument_hier_level5_id
, hier_lvl.instrument_hier_level6_id
FROM INT_INSTRUMENT_HIER_LVL hier_lvl
, INT_REP_TEMP_DIM_COL_DET dim_col
WHERE hier_lvl.instrument_hier_level_id =
dim_col.entity_id
AND hier_lvl.instrument_hier_level_no =
dim_col.level_no
AND dim_col.entity_ty = 'AC'
AND dim_col.template_id =
temp_id
AND dim_col.current_rec_in = 'Y'
AND dim_col.status_in = 'A' )
OR (
ar.inst_type_hierarchy_level_ky =
0
AND fct.fact_type_id = tot_ror_type
)
)
)
)
)
OR (
( grp = 'sec' )
AND ar.country_ky = 0
AND ar.region_ky = 0
AND ccy.currency_id = rep_currency
AND ar.fact_type_ky = fct.fact_type_ky
AND (
(
sec.industry_hierarchy_scheme_id
, sec.industry_hierarchy_level_no
) IN (
SELECT industry_hier_scheme_id
, industry_hierarchy_level_no
FROM INT_REPORT_ATTRIBUTE_LVL
WHERE template_id = temp_id )
OR sec.industry_hierarchy_level_ky = 0
)
AND (
(
( asset_count = 0 )
AND ar.inst_type_hierarchy_level_ky = 0
AND fct.fact_type_id IN(tot_ror_type, 'TOTFUND')
)
OR (
( asset_count > 0 )
AND (
(
(
inmt.inmt_type_hierarchy_level_no
, inmt.inmt_type_hierarchy_scheme_id
, inmt.inmt_type_hierarchy_level1_id
, inmt.inmt_type_hierarchy_level2_id
, inmt.inmt_type_hierarchy_level3_id
, inmt.inmt_type_hierarchy_level4_id
, inmt.inmt_type_hierarchy_level5_id
, inmt.inmt_type_hierarchy_level6_id
) IN (
SELECT hier_lvl.instrument_hier_level_no
, hier_lvl.instrument_type_hier_scheme_id
, hier_lvl.instrument_hier_level1_id
, hier_lvl.instrument_hier_level2_id
, hier_lvl.instrument_hier_level3_id
, hier_lvl.instrument_hier_level4_id
, hier_lvl.instrument_hier_level5_id
, hier_lvl.instrument_hier_level6_id
FROM INT_INSTRUMENT_HIER_LVL hier_lvl
, INT_REP_TEMP_DIM_COL_DET dim_col
WHERE hier_lvl.instrument_hier_level_id =
dim_col.entity_id
AND hier_lvl.instrument_hier_level_no =
dim_col.level_no
AND dim_col.entity_ty =
'AC'
AND dim_col.template_id =
temp_id
AND dim_col.current_rec_in =
'Y'
AND dim_col.status_in = 'A' )
AND fct.fact_type_id = 'N/A'
)
OR (
ar.inst_type_hierarchy_level_ky =
0
AND fct.fact_type_id = tot_ror_type
)
)
)
)
)
OR (
( grp = 'asset_cls' )
AND (
ar.country_ky = 0
-- AND ar.CURRENCY_CODE_TYPE_KY = 0 /* commented for now */
AND ccy.currency_id = rep_currency
AND ar.industry_hierarchy_level_ky = 0
AND ar.region_ky = 0
AND ar.fact_type_ky = fct.fact_type_ky
AND (
inmt.inmt_type_hierarchy_scheme_id
, inmt.inmt_type_hierarchy_level_no
) IN (
SELECT instrument_type_hier_scheme_id
, inmt_ty_hierarchy_level_no
FROM INT_BUS_UT_INMT_TY_VALID
WHERE current_rec_in = 'Y'
AND inmt_ty_hierarchy_level_in = 'Y'
AND business_unit_id = buid )
AND inmt.inmt_type_hierarchy_level_no IN (
SELECT instrument_hier_level_no
FROM INT_INSTRUMENT_HIER_LVL )
AND (
( asset_count = 0 )
OR (
( asset_count > 0 )
AND (
inmt.inmt_type_hierarchy_level_no
, inmt.inmt_type_hierarchy_scheme_id
, inmt.inmt_type_hierarchy_level1_id
, inmt.inmt_type_hierarchy_level2_id
, inmt.inmt_type_hierarchy_level3_id
, inmt.inmt_type_hierarchy_level4_id
, inmt.inmt_type_hierarchy_level5_id
, inmt.inmt_type_hierarchy_level6_id
) IN (
SELECT hier_lvl.instrument_hier_level_no
, hier_lvl.instrument_type_hier_scheme_id
, hier_lvl.instrument_hier_level1_id
, hier_lvl.instrument_hier_level2_id
, hier_lvl.instrument_hier_level3_id
, hier_lvl.instrument_hier_level4_id
, hier_lvl.instrument_hier_level5_id
, hier_lvl.instrument_hier_level6_id
FROM INT_INSTRUMENT_HIER_LVL hier_lvl
, INT_REP_TEMP_DIM_COL_DET dim_col
WHERE hier_lvl.instrument_hier_level_id =
dim_col.entity_id
AND hier_lvl.instrument_hier_level_no =
dim_col.level_no
AND dim_col.entity_ty = 'AC'
AND dim_col.template_id =
temp_id
AND dim_col.current_rec_in =
'Y'
AND dim_col.status_in = 'A' )
)
)
OR (
ar.country_ky = 0
-- AND ar.CURRENCY_CODE_TYPE_KY = 0 --commented for now
AND ar.industry_hierarchy_level_ky = 0
AND ar.inst_type_hierarchy_level_ky = 0
AND ar.fact_type_ky = fct.fact_type_ky
AND fct.fact_type_id = tot_ror_type
AND ar.region_ky = 0
)
)
)
)
UNION ALL
SELECT 'TEST BU' bu_name
, 99999.99 bus_key
, 9999999999.99 acc_ky
, '9999' acc_id
, 'TEST ACC' acc_nm
, 99999
, 9999 rel_ky
, SYSDATE acct_rel_date
, 99999.99
, 99999.99 sec_ky
, '1' ind_lvl_no
, 'SECTOR' sector1
, 'SECTOR1' sector2
, 'SECTOR1' sector3
, 'ind_lvl1_id' ind_lvl1_id
, 'ind_lvl2_id' ind_lvl2_id
, 'ind_lvl3_id' ind_lvl3_id
, 'ind_lvl4_id' ind_lvl4_id
, 99999.99 ctry_ky
, 'COUNTRY' COUNTRY
, 99999.99 regn_ky
, 'CLIENTREGIONNM' regn_nm
, 'REGIONID' regionid
, 99999.99 int_ky
, 1 lvl_no
, /* '' Asset_label,*/
'lvl1' lvl1_nm
, 'lvl2' lvl2_nm
, 'lvl3' lvl3_nm
, 'lvl4' lvl4_nm
, 'lvl5' lvl5_nm
, 'lvl6' lvl6_nm
, 'N' countryid
, '99999.99' lvl1_id
, '99999.99' lvl2_id
, '99999.99' lvl3_id
, '99999.99' lvl4_id
, '99999.99' lvl4_id
, '99999.99' lvl6_id
, 89
, 'N' sectorid
, 'None' freq_type
, 'ZZZZ'
, 99999.99 cctyky
, '99999.99' cdtypeid
FROM DUAL
ORDER BY acc_ky
, int_ky DESC
, ctry_ky DESC
, sec_ky DESC
, regn_ky DESC
, cctyky DESC
, rel_ky DESC;
/* PLAN FOR THIS SQL*/
SELECT STATEMENT, GOAL = CHOOSE Cost=255 Cardinality=8169 Bytes=393
SORT ORDER BY Cost=239 Cardinality=8169 Bytes=393
UNION-ALL
FILTER
FILTER
NESTED LOOPS Cost=231 Cardinality=1 Bytes=393
NESTED LOOPS Cost=230 Cardinality=1 Bytes=386
NESTED LOOPS Cost=229 Cardinality=1 Bytes=309
NESTED LOOPS Cost=228 Cardinality=1 Bytes=292
NESTED LOOPS Cost=227 Cardinality=1 Bytes=170
NESTED LOOPS Cost=226 Cardinality=1 Bytes=145
NESTED LOOPS Cost=224 Cardinality=2 Bytes=268
NESTED LOOPS Cost=222 Cardinality=2 Bytes=254
NESTED LOOPS Cost=219 Cardinality=3 Bytes=351
NESTED LOOPS Cost=43 Cardinality=11 Bytes=902
NESTED LOOPS Cost=3 Cardinality=10 Bytes=410
TABLE ACCESS BY INDEX ROWID Object owner=PM_DBA Object name=BUSINESS_UNIT Cost=1 Cardinality=1 Bytes=22
INDEX UNIQUE SCAN Object owner=PM_DBA Object name=BUSINESS_UNIT_PK Cardinality=220
TABLE ACCESS BY INDEX ROWID Object owner=PM_DBA Object name=INT_REPORT_TEMPLATE_DET Cost=2 Cardinality=10 Bytes=190
INDEX RANGE SCAN Object owner=PM_DBA Object name=INT_REPORT_TEMPLATE_DET_PK Cost=1 Cardinality=10
TABLE ACCESS BY INDEX ROWID Object owner=PM_DBA Object name=ACCOUNT Cost=4 Cardinality=1 Bytes=41
INDEX RANGE SCAN Object owner=PM_DBA Object name=ACCOUNT_IDX_NK01 Cost=1 Cardinality=1
TABLE ACCESS BY INDEX ROWID Object owner=PM_DBA Object name=DAILY_ACCOUNT_RETURNS Cost=16 Cardinality=359 Bytes=12565
INDEX RANGE SCAN Object owner=PM_DBA Object name=DAILY_ACCOUNT_RETURNS_PK Cost=13 Cardinality=4
TABLE ACCESS BY INDEX ROWID Object owner=PM_DBA Object name=CODE_TYPE Cost=1 Cardinality=1 Bytes=10
INDEX UNIQUE SCAN Object owner=PM_DBA Object name=CODE_TYPE_PK Cardinality=70
TABLE ACCESS BY INDEX ROWID Object owner=PM_DBA Object name=FACT_TYPE Cost=1 Cardinality=5 Bytes=35
INDEX UNIQUE SCAN Object owner=PM_DBA Object name=FACT_TYPE_PK Cardinality=1
TABLE ACCESS BY INDEX ROWID Object owner=PM_DBA Object name=CALENDAR_DATE Cost=1 Cardinality=1 Bytes=11
INDEX UNIQUE SCAN Object owner=PM_DBA Object name=CALENDAR_DATE_PK Cardinality=1
TABLE ACCESS BY INDEX ROWID Object owner=PM_DBA Object name=REGION Cost=1 Cardinality=1 Bytes=25
INDEX UNIQUE SCAN Object owner=PM_DBA Object name=REGION_PK Cardinality=1
TABLE ACCESS BY INDEX ROWID Object owner=PM_DBA Object name=INSTRUMENT_TYPE_HIERARCHY Cost=1 Cardinality=1 Bytes=122
INDEX UNIQUE SCAN Object owner=PM_DBA Object name=INSTRUMENT_TYPE_HIERARCHY_PK Cardinality=1
TABLE ACCESS BY INDEX ROWID Object owner=PM_DBA Object name=COUNTRY Cost=1 Cardinality=1 Bytes=17
INDEX UNIQUE SCAN Object owner=PM_DBA Object name=COUNTRY_PK Cardinality=1
TABLE ACCESS BY INDEX ROWID Object owner=PM_DBA Object name=INDUSTRY_HIERARCHY Cost=1 Cardinality=1 Bytes=77
INDEX UNIQUE SCAN Object owner=PM_DBA Object name=INDUSTRY_HIERARCHY_PK Cardinality=1
TABLE ACCESS BY INDEX ROWID Object owner=PM_DBA Object name=CURRENCY Cost=1 Cardinality=1 Bytes=7
INDEX UNIQUE SCAN Object owner=PM_DBA Object name=CURRENCY_PK Cardinality=1
TABLE ACCESS BY INDEX ROWID Object owner=PM_DBA Object name=INT_REP_TEMP_DIM_COL_DET Cost=2 Cardinality=1 Bytes=13
NESTED LOOPS Cost=4 Cardinality=1 Bytes=49
TABLE ACCESS FULL Object owner=PM_DBA Object name=INT_INSTRUMENT_HIER_LVL Cost=2 Cardinality=1 Bytes=36
INDEX RANGE SCAN Object owner=PM_DBA Object name=INT_REP_TEMP_DIM_COL_DET_PK Cost=1 Cardinality=1
TABLE ACCESS BY INDEX ROWID Object owner=PM_DBA Object name=INT_REP_TEMP_DIM_COL_DET Cost=2 Cardinality=1 Bytes=13
NESTED LOOPS Cost=4 Cardinality=1 Bytes=49
TABLE ACCESS FULL Object owner=PM_DBA Object name=INT_INSTRUMENT_HIER_LVL Cost=2 Cardinality=1 Bytes=36
INDEX RANGE SCAN Object owner=PM_DBA Object name=INT_REP_TEMP_DIM_COL_DET_PK Cost=1 Cardinality=1
TABLE ACCESS BY INDEX ROWID Object owner=PM_DBA Object name=INT_REP_TEMP_DIM_COL_DET Cost=2 Cardinality=1 Bytes=13
NESTED LOOPS Cost=4 Cardinality=1 Bytes=49
TABLE ACCESS FULL Object owner=PM_DBA Object name=INT_INSTRUMENT_HIER_LVL Cost=2 Cardinality=1 Bytes=36
INDEX RANGE SCAN Object owner=PM_DBA Object name=INT_REP_TEMP_DIM_COL_DET_PK Cost=1 Cardinality=1
TABLE ACCESS BY INDEX ROWID Object owner=PM_DBA Object name=INT_REPORT_ATTRIBUTE_LVL Cost=6 Cardinality=1 Bytes=8
INDEX RANGE SCAN Object owner=PM_DBA Object name=INT_REPORT_ATTRIBUTE_LVL_PK Cost=2 Cardinality=1
TABLE ACCESS BY INDEX ROWID Object owner=PM_DBA Object name=INT_REP_TEMP_DIM_COL_DET Cost=2 Cardinality=1 Bytes=13
NESTED LOOPS Cost=4 Cardinality=1 Bytes=49
TABLE ACCESS FULL Object owner=PM_DBA Object name=INT_INSTRUMENT_HIER_LVL Cost=2 Cardinality=1 Bytes=36
INDEX RANGE SCAN Object owner=PM_DBA Object name=INT_REP_TEMP_DIM_COL_DET_PK Cost=1 Cardinality=1
TABLE ACCESS BY INDEX ROWID Object owner=PM_DBA Object name=INT_BUS_UT_INMT_TY_VALID Cost=8 Cardinality=1 Bytes=17
INDEX RANGE SCAN Object owner=PM_DBA Object name=INT_BUS_UT_INMT_TY_VALID_PK Cost=2 Cardinality=1
INDEX FULL SCAN Object owner=PM_DBA Object name=INT_INST_HIER_LVL_IDX_NK01 Cost=1 Cardinality=65 Bytes=65
TABLE ACCESS BY INDEX ROWID Object owner=PM_DBA Object name=INT_REP_TEMP_DIM_COL_DET Cost=2 Cardinality=1 Bytes=13
NESTED LOOPS Cost=4 Cardinality=1 Bytes=49
TABLE ACCESS FULL Object owner=PM_DBA Object name=INT_INSTRUMENT_HIER_LVL Cost=2 Cardinality=1 Bytes=36
INDEX RANGE SCAN Object owner=PM_DBA Object name=INT_REP_TEMP_DIM_COL_DET_PK Cost=1 Cardinality=1
TABLE ACCESS FULL Object owner=SYS Object name=DUAL Cost=8 Cardinality=8168
/* 2 SQL */
SELECT sty.security_nm security_nm
, msr.security_ky sec_ky
, msr.date_ky sec_rel_ky
, c.calendar_dt sec_rel_date
, sty.security_id sec_id
, msr.end_market_value_am endmarketvalue
, msr.rate_of_returns_pt ror
, msr.risk_country_ky riskctryky
--, sty.origin_country_nm orgctrynm
, ctry.country_nm orgctrynm
, sty.trade_country_nm tractrynm
, msr.region_ky secrgn_ky
, msr.inmt_type_hierarchy_level_ky secinst_ky
, msr.industry_hierarchy_level_ky secindu_ky
, NVL ( msr.end_unit_price_am, 0 ) endunitpriceam
, NVL ( msr.closing_units_held_no, 0 ) closingunitsheldno
, msr.SECURITY_COST_VL Cost
, sty.origin_currency_nm orgcurrnm
, sty.trade_currency_nm tracurrnm
, cct.currency_code_ty currcodety
, sec.industry_hierarchy_level1_nm sector1
, sec.industry_hierarchy_level2_nm sector2
, sec.industry_hierarchy_level3_nm sector3
, sec.industry_hierarchy_level4_nm sector4
, inmt.inmt_type_hierarchy_level1_nm level1_nm
, inmt.inmt_type_hierarchy_level2_nm level2_nm
, inmt.inmt_type_hierarchy_level3_nm level3_nm
, INMT.inmt_type_hierarchy_level4_nm level4_nm
, INMT.inmt_type_hierarchy_level5_nm level5_nm
, INMT.inmt_type_hierarchy_level6_nm level6_nm
, INMT.INMT_TYPE_HIERARCHY_LEVEL_NO SecLvl
, msr.country_currency_type_ky cctky
, 'DLY' freq_type
FROM SECURITY sty
, CALENDAR_DATE c
, COUNTRY_CURRENCY_TYPE cct
, COUNTRY ctry
, INDUSTRY_HIERARCHY sec
, INSTRUMENT_TYPE_HIERARCHY inmt
, DAILY_SECURITY_RETURNS msr
WHERE msr.bus_ut_ky = refbuskey
AND msr.account_ky = refaccky
AND msr.security_ky > 0
AND (
msr.date_ky >= mindateky
AND msr.date_ky <= maxdateky
)
AND sty.security_ky = msr.security_ky
AND ctry.COUNTRY_KY=msr.RISK_COUNTRY_KY
AND sec.industry_hierarchy_level_ky =
msr.industry_hierarchy_level_ky
AND inmt.inmt_type_hierarchy_level_ky =
msr.inmt_type_hierarchy_level_ky
AND c.date_ky = msr.date_ky
AND cct.currency_code_ty IN ( 'PRI', 'TRD' )
AND cct.country_currency_type_ky = msr.country_currency_type_ky
AND (
(
( grp = 'mgr' )
AND (
( asset_count = 0 )
OR (
( asset_count > 0 )
AND (
(
reflvl_no = 1
AND inmt.inmt_type_hierarchy_level1_id =
reflvl1_id
)
OR (
reflvl_no = 2
AND inmt.inmt_type_hierarchy_level1_id =
reflvl1_id
AND inmt.inmt_type_hierarchy_level2_id =
reflvl2_id
)
OR (
reflvl_no = 3
AND inmt.inmt_type_hierarchy_level1_id =
reflvl1_id
AND inmt.inmt_type_hierarchy_level2_id =
reflvl2_id
AND inmt.inmt_type_hierarchy_level3_id =
reflvl3_id
)
OR (
reflvl_no = 4
AND inmt.inmt_type_hierarchy_level1_id =
reflvl1_id
AND inmt.inmt_type_hierarchy_level2_id =
reflvl2_id
AND inmt.inmt_type_hierarchy_level3_id =
reflvl3_id
AND inmt.inmt_type_hierarchy_level4_id =
reflvl4_id
)
OR (
reflvl_no = 5
AND inmt.inmt_type_hierarchy_level1_id =
reflvl1_id
AND inmt.inmt_type_hierarchy_level2_id =
reflvl2_id
AND inmt.inmt_type_hierarchy_level3_id =
reflvl3_id
AND inmt.inmt_type_hierarchy_level4_id =
reflvl4_id
AND inmt.inmt_type_hierarchy_level5_id =
reflvl5_id
)
OR (
reflvl_no = 6
AND inmt.inmt_type_hierarchy_level1_id =
reflvl1_id
AND inmt.inmt_type_hierarchy_level2_id =
reflvl2_id
AND inmt.inmt_type_hierarchy_level3_id =
reflvl3_id
AND inmt.inmt_type_hierarchy_level4_id =
reflvl4_id
AND inmt.inmt_type_hierarchy_level5_id =
reflvl5_id
AND inmt.inmt_type_hierarchy_level6_id =
reflvl6_id
)
)
)
)
)
OR (
( grp = 'cty' )
AND risk_country_ky = refcounkey
AND (
( asset_count = 0 )
OR (
( asset_count > 0 )
AND (
(
reflvl_no = 1
AND inmt.inmt_type_hierarchy_level1_id =
reflvl1_id
)
OR (
reflvl_no = 0
AND inmt.inmt_type_hierarchy_level1_id =
reflvl1_id
)
OR (
reflvl_no = 2
AND inmt.inmt_type_hierarchy_level1_id =
reflvl1_id
AND inmt.inmt_type_hierarchy_level2_id =
reflvl2_id
)
OR (
reflvl_no = 3
AND inmt.inmt_type_hierarchy_level1_id =
reflvl1_id
AND inmt.inmt_type_hierarchy_level2_id =
reflvl2_id
AND inmt.inmt_type_hierarchy_level3_id =
reflvl3_id
)
OR (
reflvl_no = 4
AND inmt.inmt_type_hierarchy_level1_id =
reflvl1_id
AND inmt.inmt_type_hierarchy_level2_id =
reflvl2_id
AND inmt.inmt_type_hierarchy_level3_id =
reflvl3_id
AND inmt.inmt_type_hierarchy_level4_id =
reflvl4_id
)
OR (
reflvl_no = 5
AND inmt.inmt_type_hierarchy_level1_id =
reflvl1_id
AND inmt.inmt_type_hierarchy_level2_id =
reflvl2_id
AND inmt.inmt_type_hierarchy_level3_id =
reflvl3_id
AND inmt.inmt_type_hierarchy_level4_id =
reflvl4_id
AND inmt.inmt_type_hierarchy_level5_id =
reflvl5_id
)
OR (
reflvl_no = 6
AND inmt.inmt_type_hierarchy_level1_id =
reflvl1_id
AND inmt.inmt_type_hierarchy_level2_id =
reflvl2_id
AND inmt.inmt_type_hierarchy_level3_id =
reflvl3_id
AND inmt.inmt_type_hierarchy_level4_id =
reflvl4_id
AND inmt.inmt_type_hierarchy_level5_id =
reflvl5_id
AND inmt.inmt_type_hierarchy_level6_id =
reflvl6_id
)
)
)
)
)
OR (
( grp = 'rgn' )
AND region_ky = refregionkey
AND (
( asset_count = 0 )
OR (
( asset_count > 0 )
AND (
(
reflvl_no = 1
AND inmt.inmt_type_hierarchy_level1_id =
reflvl1_id
)
OR (
reflvl_no = 0
AND inmt.inmt_type_hierarchy_level1_id =
reflvl1_id
)
OR (
reflvl_no = 2
AND inmt.inmt_type_hierarchy_level1_id =
reflvl1_id
AND inmt.inmt_type_hierarchy_level2_id =
reflvl2_id
)
OR (
reflvl_no = 3
AND inmt.inmt_type_hierarchy_level1_id =
reflvl1_id
AND inmt.inmt_type_hierarchy_level2_id =
reflvl2_id
AND inmt.inmt_type_hierarchy_level3_id =
reflvl3_id
)
OR (
reflvl_no = 4
AND inmt.inmt_type_hierarchy_level1_id =
reflvl1_id
AND inmt.inmt_type_hierarchy_level2_id =
reflvl2_id
AND inmt.inmt_type_hierarchy_level3_id =
reflvl3_id
AND inmt.inmt_type_hierarchy_level4_id =
reflvl4_id
)
OR (
reflvl_no = 5
AND inmt.inmt_type_hierarchy_level1_id =
reflvl1_id
AND inmt.inmt_type_hierarchy_level2_id =
reflvl2_id
AND inmt.inmt_type_hierarchy_level3_id =
reflvl3_id
AND inmt.inmt_type_hierarchy_level4_id =
reflvl4_id
AND inmt.inmt_type_hierarchy_level5_id =
reflvl5_id
)
OR (
reflvl_no = 6
AND inmt.inmt_type_hierarchy_level1_id =
reflvl1_id
AND inmt.inmt_type_hierarchy_level2_id =
reflvl2_id
AND inmt.inmt_type_hierarchy_level3_id =
reflvl3_id
AND inmt.inmt_type_hierarchy_level4_id =
reflvl4_id
AND inmt.inmt_type_hierarchy_level5_id =
reflvl5_id
AND inmt.inmt_type_hierarchy_level6_id =
reflvl6_id
)
)
)
)
)
OR (
( grp = 'asset_cls' )
AND (
(
reflvl_no = 1
AND inmt.inmt_type_hierarchy_level1_id =
reflvl1_id
)
OR (
reflvl_no = 0
AND inmt.inmt_type_hierarchy_level1_id =
reflvl1_id
)
OR (
reflvl_no = 2
AND inmt.inmt_type_hierarchy_level1_id =
reflvl1_id
AND inmt.inmt_type_hierarchy_level2_id =
reflvl2_id
)
OR (
reflvl_no = 3
AND inmt.inmt_type_hierarchy_level1_id =
reflvl1_id
AND inmt.inmt_type_hierarchy_level2_id =
reflvl2_id
AND inmt.inmt_type_hierarchy_level3_id =
reflvl3_id
)
OR (
reflvl_no = 4
AND inmt.inmt_type_hierarchy_level1_id =
reflvl1_id
AND inmt.inmt_type_hierarchy_level2_id =
reflvl2_id
AND inmt.inmt_type_hierarchy_level3_id =
reflvl3_id
AND inmt.inmt_type_hierarchy_level4_id =
reflvl4_id
)
OR (
reflvl_no = 5
AND inmt.inmt_type_hierarchy_level1_id =
reflvl1_id
AND inmt.inmt_type_hierarchy_level2_id =
reflvl2_id
AND inmt.inmt_type_hierarchy_level3_id =
reflvl3_id
AND inmt.inmt_type_hierarchy_level4_id =
reflvl4_id
AND inmt.inmt_type_hierarchy_level5_id =
reflvl5_id
)
OR (
reflvl_no = 6
AND inmt.inmt_type_hierarchy_level1_id =
reflvl1_id
AND inmt.inmt_type_hierarchy_level2_id =
reflvl2_id
AND inmt.inmt_type_hierarchy_level3_id =
reflvl3_id
AND inmt.inmt_type_hierarchy_level4_id =
reflvl4_id
AND inmt.inmt_type_hierarchy_level5_id =
reflvl5_id
AND inmt.inmt_type_hierarchy_level6_id =
reflvl6_id
)
)
)
OR
(
( grp = 'sec' )
AND (
(
refind_lvl_no = 1
AND sec.industry_hierarchy_level1_id =
refind_lvl1_id
)
OR (
refind_lvl_no = 2
AND sec.industry_hierarchy_level2_id =
refind_lvl2_id
)
OR (
refind_lvl_no = 3
AND sec.industry_hierarchy_level3_id =
refind_lvl3_id
)
OR (
refind_lvl_no = 4
AND sec.industry_hierarchy_level4_id =
refind_lvl4_id
)
)
AND (
( asset_count = 0 )
OR (
( asset_count > 0 )
AND (
(
reflvl_no = 1
AND inmt.inmt_type_hierarchy_level1_id =
reflvl1_id
)
OR (
reflvl_no = 0
AND inmt.inmt_type_hierarchy_level1_id =
reflvl1_id
)
OR (
reflvl_no = 2
AND inmt.inmt_type_hierarchy_level1_id =
reflvl1_id
AND inmt.inmt_type_hierarchy_level2_id =
reflvl2_id
)
OR (
reflvl_no = 3
AND inmt.inmt_type_hierarchy_level1_id =
reflvl1_id
AND inmt.inmt_type_hierarchy_level2_id =
reflvl2_id
AND inmt.inmt_type_hierarchy_level3_id =
reflvl3_id
)
OR (
reflvl_no = 4
AND inmt.inmt_type_hierarchy_level1_id =
reflvl1_id
AND inmt.inmt_type_hierarchy_level2_id =
reflvl2_id
AND inmt.inmt_type_hierarchy_level3_id =
reflvl3_id
AND inmt.inmt_type_hierarchy_level4_id =
reflvl4_id
)
OR (
reflvl_no = 5
AND inmt.inmt_type_hierarchy_level1_id =
reflvl1_id
AND inmt.inmt_type_hierarchy_level2_id =
reflvl2_id
AND inmt.inmt_type_hierarchy_level3_id =
reflvl3_id
AND inmt.inmt_type_hierarchy_level4_id =
reflvl4_id
AND inmt.inmt_type_hierarchy_level5_id =
reflvl5_id
)
OR (
reflvl_no = 6
AND inmt.inmt_type_hierarchy_level1_id =
reflvl1_id
AND inmt.inmt_type_hierarchy_level2_id =
reflvl2_id
AND inmt.inmt_type_hierarchy_level3_id =
reflvl3_id
AND inmt.inmt_type_hierarchy_level4_id =
reflvl4_id
AND inmt.inmt_type_hierarchy_level5_id =
reflvl5_id
AND inmt.inmt_type_hierarchy_level6_id =
reflvl6_id
)
)
)
)
)
)
AND NOT (msr.END_MARKET_VALUE_AM = 0 AND msr.RATE_OF_RETURNS_PT = 0)
UNION ALL
SELECT 'PERFBYSECURITYNAMEZZZZZ'
, 9999999999.99
, 99999.99 sec_rel_ky
, SYSDATE sec_rel_date
, 'SECURITYID'
, 999999999
, 999999999
, 999999999
, 'AAA'
, 'AAA'
, 99999.99 secrgn_ky
, 99999.99 secinst_ky
, 99999.99 secindu_ky
, 99999.99
, 99999.99
, 99999.99 Cost
, 'A'
, 'A'
, 'PRI'
, 'SECTOR1' sector1
, 'SECTOR2' sector2
, 'SECTOR3' sector3
, 'SECTOR4' sector4
, 'LEVEL1_NM' level1_nm
, 'LEVEL2_NM' level2_nm
, 'LEVEL3_nm' level3_nm
, 'LEVEL4_nm' level4_nm
, 'LEVEL5_nm' level5_nm
, 'LEVEL6_nm' level6_nm
, 99999.99 SecLvl
, 99999.99 cctky
, 'None' freq_type
FROM DUAL
ORDER BY sec_ky
, cctky
, sec_rel_ky DESC;
PLEASE SUGEEST ANY CHANGES IN SQL FOR BETTER PERFORMANCE.
Thanks
|
|
|
|
| Re: COMPLEX SQL,NESTED [message #154298 is a reply to message #154272] |
Fri, 06 January 2006 11:36  |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
First thing that caught my eyes was this
| Quote: |
TABLE ACCESS FULL Object owner=SYS Object name=DUAL Cost=8 Cardinality=8168
|
A cardinality of 8168 on dual?
Sounds strange; can someone shed a light on that?
secondly: how many rows does table INT_INSTRUMENT_HIER_LVL contain?
|
|
|
|
|
|
|
|
| Re: COMPLEX SQL,NESTED [message #154315 is a reply to message #154272] |
Fri, 06 January 2006 13:59 |
mchadder
Messages: 224
Registered: May 2005
Location: UK
|
Senior Member |
|
|
First thoughts,
looking at the query, I bet we could factor out the
SELECT hier_lvl.instrument_hier_level_no
, hier_lvl.instrument_type_hier_scheme_id
, hier_lvl.instrument_hier_level1_id
, hier_lvl.instrument_hier_level2_id
, hier_lvl.instrument_hier_level3_id
, hier_lvl.instrument_hier_level4_id
, hier_lvl.instrument_hier_level5_id
, hier_lvl.instrument_hier_level6_id
FROM INT_INSTRUMENT_HIER_LVL hier_lvl
, INT_REP_TEMP_DIM_COL_DET dim_col
WHERE hier_lvl.instrument_hier_level_id =
dim_col.entity_id
AND hier_lvl.instrument_hier_level_no =
dim_col.level_no
AND dim_col.entity_ty = 'AC'
AND dim_col.template_id = temp_id
AND dim_col.current_rec_in = 'Y'
AND dim_col.status_in = 'A'
Which seems to be repeated quite a few times. The WITH clause allows us to do this once and reuse,
i.e.
WITH x AS ( SELECT hier_lvl.instrument_hier_level_no
, hier_lvl.instrument_type_hier_scheme_id
, hier_lvl.instrument_hier_level1_id
, hier_lvl.instrument_hier_level2_id
, hier_lvl.instrument_hier_level3_id
, hier_lvl.instrument_hier_level4_id
, hier_lvl.instrument_hier_level5_id
, hier_lvl.instrument_hier_level6_id
FROM INT_INSTRUMENT_HIER_LVL hier_lvl
, INT_REP_TEMP_DIM_COL_DET dim_col
WHERE hier_lvl.instrument_hier_level_id =
dim_col.entity_id
AND hier_lvl.instrument_hier_level_no =
dim_col.level_no
AND dim_col.entity_ty = 'AC'
AND dim_col.template_id = temp_id
AND dim_col.current_rec_in = 'Y'
AND dim_col.status_in = 'A' )
SELECT ...
..
WHERE
(
... inmt.inmt_type_hierarchy_level_no
, inmt.inmt_type_hierarchy_scheme_id
, inmt.inmt_type_hierarchy_level1_id
, inmt.inmt_type_hierarchy_level2_id
, inmt.inmt_type_hierarchy_level3_id
, inmt.inmt_type_hierarchy_level4_id
, inmt.inmt_type_hierarchy_level5_id
, inmt.inmt_type_hierarchy_level6_id
) IN (
SELECT * FROM x )
....
Although, I can't see where "temp_id" comes from, is this a column or a bound variable?
Rgds
|
|
|
|
|
|
|
|
| Re: COMPLEX SQL,NESTED [message #154321 is a reply to message #154320] |
Fri, 06 January 2006 17:30 |
feroze
Messages: 144
Registered: September 2005
Location: chicago
|
Senior Member |
|
|
Thanks Mchadder
Thanks for responding,
Yes I learned a new WITH clause today,
I will try with it, but i think some difference is there in the where clause of the repeated inner query will look again into it ,
If you find any other flaw or any suggestion please come up.
Thanks
[Updated on: Fri, 06 January 2006 17:35] Report message to a moderator |
|
|
|
| Re: COMPLEX SQL,NESTED [message #154330 is a reply to message #154321] |
Fri, 06 January 2006 20:27 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
1st SQL
It's a big SQL, so I can't say that I've studied it closely, but...
I have two concerns with this plan:
1. The lines containing INDEX RANGE SCAN: An index range scan means that Oracle is reading some but not necessarily all of the index. eg. If the table (and index) has 100 million rows, then that step of the plan may be reading anywhere from 1 to 100 million rows. It's a bit iffy.
If EACH range scan is reading 1-10 rows, I wouldn't be concerned, but it's very hard to tell without tracing the SQL.
2. The plan uses all Nested Loops joins, indicating that Oracle thinks it is a low-volume SQL. But with those RANGE SCANs, I'm not so sure. Look at the join order: single row of BUSINESS_UNIT, then range scan of INT_REPORT_TEMPLATE_DET, then range scan of ACCOUNT, then range scan of DAILY_ACCOUNT_RETURNS.
Say (for instance) that one Business Unit had 10 INT_REPORT_TEMPLATE_DET, each of which had 10 accounts, each of which had 365 DAILY_ACCOUNT_RETURNS. That means 10x10x365 = 36500 rows. That's not such a huge number, but then you have to lookup another 8 tables 36500 times; it starts to add up.
The question is: are there any predicates in the SQL that are MORE constraining that the one on BUSINESS_UNIT? If so, the the SQL should be driving off a different table.
To get further information on both of these points, you need to trace the SQL and run it through TK*Prof. Lookup TKPROF in the Oracle Utilities manual.
Feel free to post the TKPROF results here if you want further advice.
_____________
Ross Leishman
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Apr 20 08:45:05 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
)). It's the old