SQL*Plus: Release 11.1.0.7.0 - Production on Tue Apr 23 12:36:11 2013 Copyright (c) 1982, 2008, Oracle. All rights reserved. @> Connected. SYS@PRD011> SYS@PRD011> SYS@PRD011> SYS@PRD011> SYS@PRD011> SYS@PRD011> SYS@PRD011> SYS@PRD011> SYS@PRD011> SYS@PRD011> SYS@PRD011> 2 3 4 PL/SQL procedure successfully completed. SYS@PRD011> SYS@PRD011> 2 3 4 PL/SQL procedure successfully completed. SYS@PRD011> SYS@PRD011> SYS@PRD011> Session altered. SYS@PRD011> 2 3 4 PL/SQL procedure successfully completed. SYS@PRD011> 2 3 4 PL/SQL procedure successfully completed. SYS@PRD011> SYS@PRD011> SYS@PRD011> SYS@PRD011> SYS@PRD011> SYS@PRD011> SYS@PRD011> SYS@PRD011> SYS@PRD011> SYS@PRD011> SYS@PRD011> SYS@PRD011> SYS@PRD011> SYS@PRD011> SYS@PRD011> SYS@PRD011> SYS@PRD011> SYS@PRD011> SYS@PRD011> SYS@PRD011> SYS@PRD011> SYS@PRD011> SYS@PRD011> SYS@PRD011> SYS@PRD011> 2 3 4 5 6 7 8 PL/SQL procedure successfully completed. SYS@PRD011> SYS@PRD011> 2 3 4 5 6 7 8 9 PL/SQL procedure successfully completed. SYS@PRD011> SYS@PRD011> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 PL/SQL procedure successfully completed. SYS@PRD011> SYS@PRD011> SYS@PRD011> SYS@PRD011> SYS@PRD011> SYS@PRD011> 2 ADDM Report for Task 'TASK_42676' --------------------------------- Analysis Period --------------- AWR snapshot range from 13779 to 13803. Time period starts at 22-APR-13 12.00.01 AM Time period ends at 23-APR-13 12.00.20 AM Analysis Target --------------- Database 'PRD01' with DB ID 1874622897. Database version 11.1.0.7.0. ADDM performed an analysis of instance PRD011, numbered 1 and hosted at prod-rac-dwh01.wp-hmi.cl.datapipe.net. Activity During the Analysis Period ----------------------------------- Total database time was 17026 seconds. The average number of active sessions was .2. Summary of Findings ------------------- Description Active Sessions Recommendations Percent of Activity ------------------- ------------------- --------------- 1 Top SQL by DB Time .09 | 44.57 5 2 CPU Usage .03 | 17.42 3 3 Top Segments by I/O .02 | 7.65 2 4 Top SQL By I/O .01 | 3.01 1 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Findings and Recommendations ---------------------------- Finding 1: Top SQL by DB Time Impact is .09 active sessions, 44.57% of total activity. -------------------------------------------------------- SQL statements consuming significant database time were found. Recommendation 1: SQL Tuning Estimated benefit is .04 active sessions, 20.75% of total activity. ------------------------------------------------------------------- Action Tune the PL/SQL block with SQL_ID "70qxcmc5vf10w". Refer to the "Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide and Reference". Related Object SQL statement with SQL_ID 70qxcmc5vf10w. BEGIN "RPT_SE"."PROC_PHR_REPORT"( :P0, :P1, :P2, :P3, :P_PHR_REPORT_CURSOR, :P4, :P5, :P6, :P7, :P8, :P9, :P10, :P11, :P12, :P13, :P14, :P15, :P16, :P17, :P18, :P19, :P20, :P21, :P22, :P23, :P24, :P25, :P26, :P27, :P28, :P29, :P30, :P31, :P32, :P33 ); END; Rationale SQL statement with SQL_ID "70qxcmc5vf10w" was executed 6 times and had an average elapsed time of 588 seconds. Recommendation 2: SQL Tuning Estimated benefit is .01 active sessions, 7.47% of total activity. ------------------------------------------------------------------ Action Run SQL Tuning Advisor on the SQL statement with SQL_ID "2cdwk2yanzj9m". Related Object SQL statement with SQL_ID 2cdwk2yanzj9m. SELECT 1 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ID, b.ID PROD_REN_KEY FROM dwh.dw_product_renaming b, dwh.DW_COMPANY_HIER_V c WHERE B.COMPANY_ID = C.LEVEL1_ID UNION SELECT 2 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ID, b.ID PROD_REN_KEY FROM dwh.dw_product_renaming b, dwh.DW_COMPANY_HIER_V c WHERE B.COMPANY_ID = C.LEVEL2_ID UNION SELECT 3 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ID, b.ID PROD_REN_KEY FROM dwh.dw_product_renaming b, dwh.DW_COMPANY_HIER_V c WHERE B.COMPANY_ID = C.LEVEL3_ID UNION SELECT 4 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ID, b.ID PROD_REN_KEY FROM dwh.dw_product_renaming b, dwh.DW_COMPANY_HIER_V c WHERE B.COMPANY_ID = C.LEVEL4_ID UNION SELECT 5 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ID, b.ID PROD_REN_KEY FROM dwh.dw_product_renaming b, dwh.DW_COMPANY_HIER_V c WHERE B.COMPANY_ID = C.LEVEL5_ID UNION SELECT 6 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ID, b.ID PROD_REN_KEY FROM dwh.dw_product_renaming b, dwh.DW_COMPANY_HIER_V c WHERE B.COMPANY_ID = C.LEVEL6_ID UNION SELECT 7 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ID, b.ID PROD_REN_KEY FROM dwh.dw_product_renaming b, dwh.DW_COMPANY_HIER_V c WHERE B.COMPANY_ID = C.LEVEL7_ID UNION SELECT 8 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ID, b.ID PROD_REN_KEY FROM dwh.dw_product_renaming b, dwh.DW_COMPANY_HIER_V c WHERE B.COMPANY_ID = C.LEVEL8_ID UNION SELECT 9 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ID, b.ID PROD_REN_KEY FROM dwh.dw_product_renaming b, dwh.DW_COMPANY_HIER_V c WHERE B.COMPANY_ID = C.LEVEL9_ID UNION SELECT 10 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ID, b.ID PROD_REN_KEY FROM dwh.dw_product_renaming b, dwh.DW_COMPANY_HIER_V c WHERE B.COMPANY_ID = C.LEVEL10_ID Rationale SQL statement with SQL_ID "2cdwk2yanzj9m" was executed 3 times and had an average elapsed time of 413 seconds. Recommendation 3: SQL Tuning Estimated benefit is .01 active sessions, 7.11% of total activity. ------------------------------------------------------------------ Action Run SQL Tuning Advisor on the SQL statement with SQL_ID "6wuv2xshtqvc3". Related Object SQL statement with SQL_ID 6wuv2xshtqvc3. select "SQL2"."TIME_PERIOD" "levelkey", sum("SQL2"."AVG") "total_SQL2_AVG_auto__0", count("SQL2"."AVG") "c3" from (select distinct wnp_user_id, Company_Label_1, Company_Label_2, Company_Label_3, Company_Label_4, Company_Label_5, Company_Label_6, Company_Label_7, Company_Label_8, Company_Label_9, Company_Label_10, Lifestyle_score_Range, Time_Period,T1, T2, AVG( T2 ) OVER (PARTITION BY TIME_PERIOD,1)AVG from ( select a.wnp_user_id, B.LEVEL1_NAME || '--' || B.LEVEL1_ID Company_Label_1, B.LEVEL2_NAME || '--' || B.LEVEL2_ID Company_Label_2, B.LEVEL3_NAME || '--' || B.LEVEL3_ID Company_Label_3, B.LEVEL4_NAME || '--' || B.LEVEL4_ID Company_Label_4, B.LEVEL5_NAME || '--' || B.LEVEL5_ID Company_Label_5, B.LEVEL6_NAME || '--' || B.LEVEL6_ID Company_Label_6, B.LEVEL7_NAME || '--' || B.LEVEL7_ID Company_Label_7, B.LEVEL8_NAME || '--' || B.LEVEL8_ID Company_Label_8, B.LEVEL9_NAME || '--' || B.LEVEL9_ID Company_Label_9, B.LEVEL10_NAME || '--' || B.LEVEL10_ID Company_Label_10, LIFESTYLE_SCR_RNG Lifestyle_score_Range, 'Time 1' Time_Period,count(*) T1, LFSTYL_SCR_CMBND T2 from rpt_se.ptr_vw a, rpt_se.dim_company_hist_vw b where a.company_key = b.company_key and pdt_name = 'succeed' and LIFESTYLE_SCR_RNG is not null and a.wnp_user_id in ( select a.wnp_user_id from rpt_se.ptr_vw a, rpt_se.dim_company_hist_vw b where a.company_key = b.company_key and pdt_name = 'succeed' and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') >= '2011/11/01' and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') <= '2012/01/31' intersect select a.wnp_user_id from rpt_se.ptr_vw a, rpt_se.dim_company_hist_vw b where a.company_key = b.company_key and pdt_name = 'succeed' and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') >= '2012/02/01' and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') <= '2013/02/28' ) and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') >= '2011/11/01' and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') <= '2012/01/31' group by a.wnp_user_id, B.LEVEL1_NAME || '--' || B.LEVEL1_ID , B.LEVEL2_NAME || '--' || B.LEVEL2_ID , B.LEVEL3_NAME || '--' || B.LEVEL3_ID, B.LEVEL4_NAME || '--' || B.LEVEL4_ID , B.LEVEL5_NAME || '--' || B.LEVEL5_ID , B.LEVEL6_NAME || '--' || B.LEVEL6_ID, B.LEVEL7_NAME || '--' || B.LEVEL7_ID , B.LEVEL8_NAME || '--' || B.LEVEL8_ID , B.LEVEL9_NAME || '--' || B.LEVEL9_ID , B.LEVEL10_NAME || '--' || B.LEVEL10_ID , LIFESTYLE_SCR_RNG , LFSTYL_SCR_CMBND) UNION ALL select wnp_user_id, Company_Label_1, Company_Label_2, Company_Label_3, Company_Label_4, Company_Label_5, Company_Label_6, Company_Label_7, Company_Label_8, Company_Label_9, Company_Label_10, Lifestyle_score_Range, Time_Period,T1, T2, AVG( T2 ) OVER (PARTITION BY TIME_PERIOD,1)AVG from (select a.wnp_user_id, B.LEVEL1_NAME || '--' || B.LEVEL1_ID Company_Label_1, B.LEVEL2_NAME || '--' || B.LEVEL2_ID Company_Label_2, B.LEVEL3_NAME || '--' || B.LEVEL3_ID Company_Label_3, B.LEVEL4_NAME || '--' || B.LEVEL4_ID Company_Label_4, B.LEVEL5_NAME || '--' || B.LEVEL5_ID Company_Label_5, B.LEVEL6_NAME || '--' || B.LEVEL6_ID Company_Label_6, B.LEVEL7_NAME || '--' || B.LEVEL7_ID Company_Label_7, B.LEVEL8_NAME || '--' || B.LEVEL8_ID Company_Label_8, B.LEVEL9_NAME || '--' || B.LEVEL9_ID Company_Label_9, B.LEVEL10_NAME || '--' || B.LEVEL10_ID Company_Label_10, LIFESTYLE_SCR_RNG Lifestyle_score_Range, 'Time 2' Time_Period,count(*) T1, LFSTYL_SCR_CMBND T2 from rpt_se.ptr_vw a, rpt_se.dim_company_hist_vw b where a.company_key = b.company_key and pdt_name = 'succeed' and LIFESTYLE_SCR_RNG is not null and a.wnp_user_id in ( select a.wnp_user_id from rpt_se.ptr_vw a, rpt_se.dim_company_hist_vw b where a.company_key = b.company_key and pdt_name = 'succeed' and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') >= '2011/11/01' and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') <= '2012/01/31' intersect select a.wnp_user_id from rpt_se.ptr_vw a, rpt_se.dim_company_hist_vw b where a.company_key = b.company_key and pdt_name = 'succeed' and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') >= '2012/02/01' and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') <= '2013/02/28' ) and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') >= '2012/02/01' and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') <= '2013/02/28' group by a.wnp_user_id, B.LEVEL1_NAME || '--' || B.LEVEL1_ID, B.LEVEL2_NAME || '--' || B.LEVEL2_ID , B.LEVEL3_NAME || '--' || B.LEVEL3_ID , B.LEVEL4_NAME || '--' || B.LEVEL4_ID , B.LEVEL5_NAME || '--' || B.LEVEL5_ID , B.LEVEL6_NAME || '--' || B.LEVEL6_ID , B.LEVEL7_NAME || '--' || B.LEVEL7_ID , B.LEVEL8_NAME || '--' || B.LEVEL8_ID , B.LEVEL9_NAME || '--' || B.LEVEL9_ID , B.LEVEL10_NAME || '--' || B.LEVEL10_ID , LIFESTYLE_SCR_RNG , LFSTYL_SCR_CMBND)) "SQL2" where "SQL2"."COMPANY_LABEL_1" in ('Coventry--6') group by "SQL2"."TIME_PERIOD" Rationale SQL statement with SQL_ID "6wuv2xshtqvc3" was executed 5 times and had an average elapsed time of 242 seconds. Recommendation 4: SQL Tuning Estimated benefit is .01 active sessions, 5.89% of total activity. ------------------------------------------------------------------ Action Run SQL Tuning Advisor on the SQL statement with SQL_ID "5djb2yp935v58". Related Object SQL statement with SQL_ID 5djb2yp935v58. select "T0"."C0" "C0", "T0"."C1" "C1", "T0"."C2" "C2", "T0"."C3" "C3", first_value("T0"."C4") over (partition by "T0"."C0", "T0"."C1", "T0"."C2", "T0"."C3") "C4", "T0"."C5" "C5", "T0"."C6" "C6" from (select "T0"."C0" "C0", "T0"."C1" "C1", "T0"."C2" "C2", "T0"."C3" "C3", sum("T0"."C4") over (partition by "T0"."C2", "T0"."C0") "C4", "T0"."C4" "C5", sum("T0"."C4") over (partition by "T0"."C0") "C6" from (select "SQL2"."TIME_PERIOD" "C0", case when "SQL2"."LIFESTYLE_SCORE_RANGE"='90 - 100' then '(Ideal)' when "SQL2"."LIFESTYLE_SCORE_RANGE"='70 - 89' then '(Acceptable)' when "SQL2"."LIFESTYLE_SCORE_RANGE"='0 - 69' then '(Improvement Needed)' end "C1", "SQL2"."LIFESTYLE_SCORE_RANGE" "C2", case when "SQL2"."LIFESTYLE_SCORE_RANGE"='90 - 100' then 1 when "SQL2"."LIFESTYLE_SCORE_RANGE"='70 - 89' then 2 when "SQL2"."LIFESTYLE_SCORE_RANGE"='0 - 69' then 3 end "C3", sum("SQL2"."T1") "C4" from (select distinct wnp_user_id, Company_Label_1, Company_Label_2, Company_Label_3, Company_Label_4, Company_Label_5, Company_Label_6, Company_Label_7, Company_Label_8, Company_Label_9, Company_Label_10, Lifestyle_score_Range, Time_Period,T1, T2, AVG( T2 ) OVER (PARTITION BY TIME_PERIOD,1)AVG from ( select a.wnp_user_id, B.LEVEL1_NAME || '--' || B.LEVEL1_ID Company_Label_1, B.LEVEL2_NAME || '--' || B.LEVEL2_ID Company_Label_2, B.LEVEL3_NAME || '--' || B.LEVEL3_ID Company_Label_3, B.LEVEL4_NAME || '--' || B.LEVEL4_ID Company_Label_4, B.LEVEL5_NAME || '--' || B.LEVEL5_ID Company_Label_5, B.LEVEL6_NAME || '--' || B.LEVEL6_ID Company_Label_6, B.LEVEL7_NAME || '--' || B.LEVEL7_ID Company_Label_7, B.LEVEL8_NAME || '--' || B.LEVEL8_ID Company_Label_8, B.LEVEL9_NAME || '--' || B.LEVEL9_ID Company_Label_9, B.LEVEL10_NAME || '--' || B.LEVEL10_ID Company_Label_10, LIFESTYLE_SCR_RNG Lifestyle_score_Range, 'Time 1' Time_Period,count(*) T1, LFSTYL_SCR_CMBND T2 from rpt_se.ptr_vw a, rpt_se.dim_company_hist_vw b where a.company_key = b.company_key and pdt_name = 'succeed' and LIFESTYLE_SCR_RNG is not null and a.wnp_user_id in ( select a.wnp_user_id from rpt_se.ptr_vw a, rpt_se.dim_company_hist_vw b where a.company_key = b.company_key and pdt_name = 'succeed' and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') >= '2011/11/01' and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') <= '2012/01/31' intersect select a.wnp_user_id from rpt_se.ptr_vw a, rpt_se.dim_company_hist_vw b where a.company_key = b.company_key and pdt_name = 'succeed' and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') >= '2012/02/01' and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') <= '2013/02/28' ) and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') >= '2011/11/01' and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') <= '2012/01/31' group by a.wnp_user_id, B.LEVEL1_NAME || '--' || B.LEVEL1_ID , B.LEVEL2_NAME || '--' || B.LEVEL2_ID , B.LEVEL3_NAME || '--' || B.LEVEL3_ID, B.LEVEL4_NAME || '--' || B.LEVEL4_ID , B.LEVEL5_NAME || '--' || B.LEVEL5_ID , B.LEVEL6_NAME || '--' || B.LEVEL6_ID, B.LEVEL7_NAME || '--' || B.LEVEL7_ID , B.LEVEL8_NAME || '--' || B.LEVEL8_ID , B.LEVEL9_NAME || '--' || B.LEVEL9_ID , B.LEVEL10_NAME || '--' || B.LEVEL10_ID , LIFESTYLE_SCR_RNG , LFSTYL_SCR_CMBND) UNION ALL select wnp_user_id, Company_Label_1, Company_Label_2, Company_Label_3, Company_Label_4, Company_Label_5, Company_Label_6, Company_Label_7, Company_Label_8, Company_Label_9, Company_Label_10, Lifestyle_score_Range, Time_Period,T1, T2, AVG( T2 ) OVER (PARTITION BY TIME_PERIOD,1)AVG from (select a.wnp_user_id, B.LEVEL1_NAME || '--' || B.LEVEL1_ID Company_Label_1, B.LEVEL2_NAME || '--' || B.LEVEL2_ID Company_Label_2, B.LEVEL3_NAME || '--' || B.LEVEL3_ID Company_Label_3, B.LEVEL4_NAME || '--' || B.LEVEL4_ID Company_Label_4, B.LEVEL5_NAME || '--' || B.LEVEL5_ID Company_Label_5, B.LEVEL6_NAME || '--' || B.LEVEL6_ID Company_Label_6, B.LEVEL7_NAME || '--' || B.LEVEL7_ID Company_Label_7, B.LEVEL8_NAME || '--' || B.LEVEL8_ID Company_Label_8, B.LEVEL9_NAME || '--' || B.LEVEL9_ID Company_Label_9, B.LEVEL10_NAME || '--' || B.LEVEL10_ID Company_Label_10, LIFESTYLE_SCR_RNG Lifestyle_score_Range, 'Time 2' Time_Period,count(*) T1, LFSTYL_SCR_CMBND T2 from rpt_se.ptr_vw a, rpt_se.dim_company_hist_vw b where a.company_key = b.company_key and pdt_name = 'succeed' and LIFESTYLE_SCR_RNG is not null and a.wnp_user_id in ( select a.wnp_user_id from rpt_se.ptr_vw a, rpt_se.dim_company_hist_vw b where a.company_key = b.company_key and pdt_name = 'succeed' and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') >= '2011/11/01' and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') <= '2012/01/31' intersect select a.wnp_user_id from rpt_se.ptr_vw a, rpt_se.dim_company_hist_vw b where a.company_key = b.company_key and pdt_name = 'succeed' and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') >= '2012/02/01' and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') <= '2013/02/28' ) and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') >= '2012/02/01' and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') <= '2013/02/28' group by a.wnp_user_id, B.LEVEL1_NAME || '--' || B.LEVEL1_ID, B.LEVEL2_NAME || '--' || B.LEVEL2_ID , B.LEVEL3_NAME || '--' || B.LEVEL3_ID , B.LEVEL4_NAME || '--' || B.LEVEL4_ID , B.LEVEL5_NAME || '--' || B.LEVEL5_ID , B.LEVEL6_NAME || '--' || B.LEVEL6_ID , B.LEVEL7_NAME || '--' || B.LEVEL7_ID , B.LEVEL8_NAME || '--' || B.LEVEL8_ID , B.LEVEL9_NAME || '--' || B.LEVEL9_ID , B.LEVEL10_NAME || '--' || B.LEVEL10_ID , LIFESTYLE_SCR_RNG , LFSTYL_SCR_CMBND)) "SQL2" where "SQL2"."COMPANY_LABEL_1" in ('Coventry--6') group by "SQL2"."TIME_PERIOD", case when "SQL2"."LIFESTYLE_SCORE_RANGE"='90 - 100' then '(Ideal)' when "SQL2"."LIFESTYLE_SCORE_RANGE"='70 - 89' then '(Acceptable)' when "SQL2"."LIFESTYLE_SCORE_RANGE"='0 - 69' then '(Improvement Needed)' end , "SQL2"."LIFESTYLE_SCORE_RANGE", case when "SQL2"."LIFESTYLE_SCORE_RANGE"='90 - 100' then 1 when "SQL2"."LIFESTYLE_SCORE_RANGE"='70 - 89' then 2 when "SQL2"."LIFESTYLE_SCORE_RANGE"='0 - 69' then 3 end ) "T0") "T0" Rationale SQL statement with SQL_ID "5djb2yp935v58" was executed 4 times and had an average elapsed time of 250 seconds. Recommendation 5: SQL Tuning Estimated benefit is .01 active sessions, 3.31% of total activity. ------------------------------------------------------------------ Action Run SQL Tuning Advisor on the SQL statement with SQL_ID "8h76m78cym602". Related Object SQL statement with SQL_ID 8h76m78cym602. select A.user_data_history_set_id, EVENT_DT, NAME, LEVEL1_ID, level1_name, LEVEL2_ID, level2_name, LEVEL3_ID, level3_name, LEVEL4_ID, level4_name, LEVEL5_ID, level5_name, LEVEL6_ID, level6_name, column_value,den_cnt, num_cnt, 'Column1' from (SELECT USER_DATA_HISTORY_SET_ID, LEVEL1_ID, LEVEL2_ID, LEVEL3_ID, LEVEL4_ID, LEVEL5_ID, LEVEL6_ID, LEVEL1_NAME, LEVEL2_NAME, LEVEL3_NAME, LEVEL4_NAME, LEVEL5_NAME, LEVEL6_NAME, NAME, EVENT_DT, PARTICIPANT_COUNT FROM RPT_SE.STG_DEMOGRAPHICS_V) A , (select MB.user_data_history_set_id,MB.column_value, case when value <=10 then 1 ELSE 0 END den_cnt, case when value <10 then 1 ELSE 0 END num_cnt from (select * from etl_se.stg_hlth_status unpivot(value for column_value in ( BHVRSCRALCHL AS 'Alcohol' , --BHVRSCRDPRSSN AS 'Depressive Symptoms' , --BHVRSCRDRGUS AS 'Drug Use' , BHVRSCRINJRYPRVNTN AS 'Injury Prevention' , --BHVRSCRMNTLSTRSS AS 'Stress' , BHVRSCRNTRTN AS 'Nutrition' , BHVRSCRPHYSCLACTVTY AS 'Physical Activity' , BHVRSCRSKNPRTCTN AS 'Skin Protection' , --BHVRSCRSTRSS AS 'Stress Management' , --BHVRSCRSXLBHVR AS 'Sexual Behavior' , BHVRSCRTBCC AS 'Tobacco' , BHVRSCRWGHTMNGMNT AS 'Weight Management')))MB --GROUP BY MB.user_data_history_set_id,MB.column_value) )B WHERE A.USER_DATA_HISTORY_SET_ID = B.USER_DATA_HISTORY_SET_ID and name = 'Succeed' and participant_count = 1 Rationale SQL statement with SQL_ID "8h76m78cym602" was executed 38 times and had an average elapsed time of 15 seconds. Finding 2: CPU Usage Impact is .03 active sessions, 17.42% of total activity. -------------------------------------------------------- Time spent on the CPU by the instance was responsible for a substantial part of database time. Recommendation 1: SQL Tuning Estimated benefit is .01 active sessions, 7.47% of total activity. ------------------------------------------------------------------ Action Run SQL Tuning Advisor on the SQL statement with SQL_ID "2cdwk2yanzj9m". Related Object SQL statement with SQL_ID 2cdwk2yanzj9m. SELECT 1 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ID, b.ID PROD_REN_KEY FROM dwh.dw_product_renaming b, dwh.DW_COMPANY_HIER_V c WHERE B.COMPANY_ID = C.LEVEL1_ID UNION SELECT 2 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ID, b.ID PROD_REN_KEY FROM dwh.dw_product_renaming b, dwh.DW_COMPANY_HIER_V c WHERE B.COMPANY_ID = C.LEVEL2_ID UNION SELECT 3 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ID, b.ID PROD_REN_KEY FROM dwh.dw_product_renaming b, dwh.DW_COMPANY_HIER_V c WHERE B.COMPANY_ID = C.LEVEL3_ID UNION SELECT 4 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ID, b.ID PROD_REN_KEY FROM dwh.dw_product_renaming b, dwh.DW_COMPANY_HIER_V c WHERE B.COMPANY_ID = C.LEVEL4_ID UNION SELECT 5 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ID, b.ID PROD_REN_KEY FROM dwh.dw_product_renaming b, dwh.DW_COMPANY_HIER_V c WHERE B.COMPANY_ID = C.LEVEL5_ID UNION SELECT 6 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ID, b.ID PROD_REN_KEY FROM dwh.dw_product_renaming b, dwh.DW_COMPANY_HIER_V c WHERE B.COMPANY_ID = C.LEVEL6_ID UNION SELECT 7 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ID, b.ID PROD_REN_KEY FROM dwh.dw_product_renaming b, dwh.DW_COMPANY_HIER_V c WHERE B.COMPANY_ID = C.LEVEL7_ID UNION SELECT 8 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ID, b.ID PROD_REN_KEY FROM dwh.dw_product_renaming b, dwh.DW_COMPANY_HIER_V c WHERE B.COMPANY_ID = C.LEVEL8_ID UNION SELECT 9 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ID, b.ID PROD_REN_KEY FROM dwh.dw_product_renaming b, dwh.DW_COMPANY_HIER_V c WHERE B.COMPANY_ID = C.LEVEL9_ID UNION SELECT 10 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ID, b.ID PROD_REN_KEY FROM dwh.dw_product_renaming b, dwh.DW_COMPANY_HIER_V c WHERE B.COMPANY_ID = C.LEVEL10_ID Rationale SQL statement with SQL_ID "2cdwk2yanzj9m" was executed 3 times and had an average elapsed time of 413 seconds. Rationale Average CPU used per execution was 253 seconds. Recommendation 2: SQL Tuning Estimated benefit is .01 active sessions, 7.11% of total activity. ------------------------------------------------------------------ Action Run SQL Tuning Advisor on the SQL statement with SQL_ID "6wuv2xshtqvc3". Related Object SQL statement with SQL_ID 6wuv2xshtqvc3. select "SQL2"."TIME_PERIOD" "levelkey", sum("SQL2"."AVG") "total_SQL2_AVG_auto__0", count("SQL2"."AVG") "c3" from (select distinct wnp_user_id, Company_Label_1, Company_Label_2, Company_Label_3, Company_Label_4, Company_Label_5, Company_Label_6, Company_Label_7, Company_Label_8, Company_Label_9, Company_Label_10, Lifestyle_score_Range, Time_Period,T1, T2, AVG( T2 ) OVER (PARTITION BY TIME_PERIOD,1)AVG from ( select a.wnp_user_id, B.LEVEL1_NAME || '--' || B.LEVEL1_ID Company_Label_1, B.LEVEL2_NAME || '--' || B.LEVEL2_ID Company_Label_2, B.LEVEL3_NAME || '--' || B.LEVEL3_ID Company_Label_3, B.LEVEL4_NAME || '--' || B.LEVEL4_ID Company_Label_4, B.LEVEL5_NAME || '--' || B.LEVEL5_ID Company_Label_5, B.LEVEL6_NAME || '--' || B.LEVEL6_ID Company_Label_6, B.LEVEL7_NAME || '--' || B.LEVEL7_ID Company_Label_7, B.LEVEL8_NAME || '--' || B.LEVEL8_ID Company_Label_8, B.LEVEL9_NAME || '--' || B.LEVEL9_ID Company_Label_9, B.LEVEL10_NAME || '--' || B.LEVEL10_ID Company_Label_10, LIFESTYLE_SCR_RNG Lifestyle_score_Range, 'Time 1' Time_Period,count(*) T1, LFSTYL_SCR_CMBND T2 from rpt_se.ptr_vw a, rpt_se.dim_company_hist_vw b where a.company_key = b.company_key and pdt_name = 'succeed' and LIFESTYLE_SCR_RNG is not null and a.wnp_user_id in ( select a.wnp_user_id from rpt_se.ptr_vw a, rpt_se.dim_company_hist_vw b where a.company_key = b.company_key and pdt_name = 'succeed' and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') >= '2011/11/01' and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') <= '2012/01/31' intersect select a.wnp_user_id from rpt_se.ptr_vw a, rpt_se.dim_company_hist_vw b where a.company_key = b.company_key and pdt_name = 'succeed' and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') >= '2012/02/01' and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') <= '2013/02/28' ) and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') >= '2011/11/01' and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') <= '2012/01/31' group by a.wnp_user_id, B.LEVEL1_NAME || '--' || B.LEVEL1_ID , B.LEVEL2_NAME || '--' || B.LEVEL2_ID , B.LEVEL3_NAME || '--' || B.LEVEL3_ID, B.LEVEL4_NAME || '--' || B.LEVEL4_ID , B.LEVEL5_NAME || '--' || B.LEVEL5_ID , B.LEVEL6_NAME || '--' || B.LEVEL6_ID, B.LEVEL7_NAME || '--' || B.LEVEL7_ID , B.LEVEL8_NAME || '--' || B.LEVEL8_ID , B.LEVEL9_NAME || '--' || B.LEVEL9_ID , B.LEVEL10_NAME || '--' || B.LEVEL10_ID , LIFESTYLE_SCR_RNG , LFSTYL_SCR_CMBND) UNION ALL select wnp_user_id, Company_Label_1, Company_Label_2, Company_Label_3, Company_Label_4, Company_Label_5, Company_Label_6, Company_Label_7, Company_Label_8, Company_Label_9, Company_Label_10, Lifestyle_score_Range, Time_Period,T1, T2, AVG( T2 ) OVER (PARTITION BY TIME_PERIOD,1)AVG from (select a.wnp_user_id, B.LEVEL1_NAME || '--' || B.LEVEL1_ID Company_Label_1, B.LEVEL2_NAME || '--' || B.LEVEL2_ID Company_Label_2, B.LEVEL3_NAME || '--' || B.LEVEL3_ID Company_Label_3, B.LEVEL4_NAME || '--' || B.LEVEL4_ID Company_Label_4, B.LEVEL5_NAME || '--' || B.LEVEL5_ID Company_Label_5, B.LEVEL6_NAME || '--' || B.LEVEL6_ID Company_Label_6, B.LEVEL7_NAME || '--' || B.LEVEL7_ID Company_Label_7, B.LEVEL8_NAME || '--' || B.LEVEL8_ID Company_Label_8, B.LEVEL9_NAME || '--' || B.LEVEL9_ID Company_Label_9, B.LEVEL10_NAME || '--' || B.LEVEL10_ID Company_Label_10, LIFESTYLE_SCR_RNG Lifestyle_score_Range, 'Time 2' Time_Period,count(*) T1, LFSTYL_SCR_CMBND T2 from rpt_se.ptr_vw a, rpt_se.dim_company_hist_vw b where a.company_key = b.company_key and pdt_name = 'succeed' and LIFESTYLE_SCR_RNG is not null and a.wnp_user_id in ( select a.wnp_user_id from rpt_se.ptr_vw a, rpt_se.dim_company_hist_vw b where a.company_key = b.company_key and pdt_name = 'succeed' and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') >= '2011/11/01' and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') <= '2012/01/31' intersect select a.wnp_user_id from rpt_se.ptr_vw a, rpt_se.dim_company_hist_vw b where a.company_key = b.company_key and pdt_name = 'succeed' and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') >= '2012/02/01' and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') <= '2013/02/28' ) and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') >= '2012/02/01' and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') <= '2013/02/28' group by a.wnp_user_id, B.LEVEL1_NAME || '--' || B.LEVEL1_ID, B.LEVEL2_NAME || '--' || B.LEVEL2_ID , B.LEVEL3_NAME || '--' || B.LEVEL3_ID , B.LEVEL4_NAME || '--' || B.LEVEL4_ID , B.LEVEL5_NAME || '--' || B.LEVEL5_ID , B.LEVEL6_NAME || '--' || B.LEVEL6_ID , B.LEVEL7_NAME || '--' || B.LEVEL7_ID , B.LEVEL8_NAME || '--' || B.LEVEL8_ID , B.LEVEL9_NAME || '--' || B.LEVEL9_ID , B.LEVEL10_NAME || '--' || B.LEVEL10_ID , LIFESTYLE_SCR_RNG , LFSTYL_SCR_CMBND)) "SQL2" where "SQL2"."COMPANY_LABEL_1" in ('Coventry--6') group by "SQL2"."TIME_PERIOD" Rationale SQL statement with SQL_ID "6wuv2xshtqvc3" was executed 5 times and had an average elapsed time of 242 seconds. Rationale Average CPU used per execution was 241 seconds. Recommendation 3: SQL Tuning Estimated benefit is .01 active sessions, 5.89% of total activity. ------------------------------------------------------------------ Action Run SQL Tuning Advisor on the SQL statement with SQL_ID "5djb2yp935v58". Related Object SQL statement with SQL_ID 5djb2yp935v58. select "T0"."C0" "C0", "T0"."C1" "C1", "T0"."C2" "C2", "T0"."C3" "C3", first_value("T0"."C4") over (partition by "T0"."C0", "T0"."C1", "T0"."C2", "T0"."C3") "C4", "T0"."C5" "C5", "T0"."C6" "C6" from (select "T0"."C0" "C0", "T0"."C1" "C1", "T0"."C2" "C2", "T0"."C3" "C3", sum("T0"."C4") over (partition by "T0"."C2", "T0"."C0") "C4", "T0"."C4" "C5", sum("T0"."C4") over (partition by "T0"."C0") "C6" from (select "SQL2"."TIME_PERIOD" "C0", case when "SQL2"."LIFESTYLE_SCORE_RANGE"='90 - 100' then '(Ideal)' when "SQL2"."LIFESTYLE_SCORE_RANGE"='70 - 89' then '(Acceptable)' when "SQL2"."LIFESTYLE_SCORE_RANGE"='0 - 69' then '(Improvement Needed)' end "C1", "SQL2"."LIFESTYLE_SCORE_RANGE" "C2", case when "SQL2"."LIFESTYLE_SCORE_RANGE"='90 - 100' then 1 when "SQL2"."LIFESTYLE_SCORE_RANGE"='70 - 89' then 2 when "SQL2"."LIFESTYLE_SCORE_RANGE"='0 - 69' then 3 end "C3", sum("SQL2"."T1") "C4" from (select distinct wnp_user_id, Company_Label_1, Company_Label_2, Company_Label_3, Company_Label_4, Company_Label_5, Company_Label_6, Company_Label_7, Company_Label_8, Company_Label_9, Company_Label_10, Lifestyle_score_Range, Time_Period,T1, T2, AVG( T2 ) OVER (PARTITION BY TIME_PERIOD,1)AVG from ( select a.wnp_user_id, B.LEVEL1_NAME || '--' || B.LEVEL1_ID Company_Label_1, B.LEVEL2_NAME || '--' || B.LEVEL2_ID Company_Label_2, B.LEVEL3_NAME || '--' || B.LEVEL3_ID Company_Label_3, B.LEVEL4_NAME || '--' || B.LEVEL4_ID Company_Label_4, B.LEVEL5_NAME || '--' || B.LEVEL5_ID Company_Label_5, B.LEVEL6_NAME || '--' || B.LEVEL6_ID Company_Label_6, B.LEVEL7_NAME || '--' || B.LEVEL7_ID Company_Label_7, B.LEVEL8_NAME || '--' || B.LEVEL8_ID Company_Label_8, B.LEVEL9_NAME || '--' || B.LEVEL9_ID Company_Label_9, B.LEVEL10_NAME || '--' || B.LEVEL10_ID Company_Label_10, LIFESTYLE_SCR_RNG Lifestyle_score_Range, 'Time 1' Time_Period,count(*) T1, LFSTYL_SCR_CMBND T2 from rpt_se.ptr_vw a, rpt_se.dim_company_hist_vw b where a.company_key = b.company_key and pdt_name = 'succeed' and LIFESTYLE_SCR_RNG is not null and a.wnp_user_id in ( select a.wnp_user_id from rpt_se.ptr_vw a, rpt_se.dim_company_hist_vw b where a.company_key = b.company_key and pdt_name = 'succeed' and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') >= '2011/11/01' and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') <= '2012/01/31' intersect select a.wnp_user_id from rpt_se.ptr_vw a, rpt_se.dim_company_hist_vw b where a.company_key = b.company_key and pdt_name = 'succeed' and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') >= '2012/02/01' and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') <= '2013/02/28' ) and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') >= '2011/11/01' and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') <= '2012/01/31' group by a.wnp_user_id, B.LEVEL1_NAME || '--' || B.LEVEL1_ID , B.LEVEL2_NAME || '--' || B.LEVEL2_ID , B.LEVEL3_NAME || '--' || B.LEVEL3_ID, B.LEVEL4_NAME || '--' || B.LEVEL4_ID , B.LEVEL5_NAME || '--' || B.LEVEL5_ID , B.LEVEL6_NAME || '--' || B.LEVEL6_ID, B.LEVEL7_NAME || '--' || B.LEVEL7_ID , B.LEVEL8_NAME || '--' || B.LEVEL8_ID , B.LEVEL9_NAME || '--' || B.LEVEL9_ID , B.LEVEL10_NAME || '--' || B.LEVEL10_ID , LIFESTYLE_SCR_RNG , LFSTYL_SCR_CMBND) UNION ALL select wnp_user_id, Company_Label_1, Company_Label_2, Company_Label_3, Company_Label_4, Company_Label_5, Company_Label_6, Company_Label_7, Company_Label_8, Company_Label_9, Company_Label_10, Lifestyle_score_Range, Time_Period,T1, T2, AVG( T2 ) OVER (PARTITION BY TIME_PERIOD,1)AVG from (select a.wnp_user_id, B.LEVEL1_NAME || '--' || B.LEVEL1_ID Company_Label_1, B.LEVEL2_NAME || '--' || B.LEVEL2_ID Company_Label_2, B.LEVEL3_NAME || '--' || B.LEVEL3_ID Company_Label_3, B.LEVEL4_NAME || '--' || B.LEVEL4_ID Company_Label_4, B.LEVEL5_NAME || '--' || B.LEVEL5_ID Company_Label_5, B.LEVEL6_NAME || '--' || B.LEVEL6_ID Company_Label_6, B.LEVEL7_NAME || '--' || B.LEVEL7_ID Company_Label_7, B.LEVEL8_NAME || '--' || B.LEVEL8_ID Company_Label_8, B.LEVEL9_NAME || '--' || B.LEVEL9_ID Company_Label_9, B.LEVEL10_NAME || '--' || B.LEVEL10_ID Company_Label_10, LIFESTYLE_SCR_RNG Lifestyle_score_Range, 'Time 2' Time_Period,count(*) T1, LFSTYL_SCR_CMBND T2 from rpt_se.ptr_vw a, rpt_se.dim_company_hist_vw b where a.company_key = b.company_key and pdt_name = 'succeed' and LIFESTYLE_SCR_RNG is not null and a.wnp_user_id in ( select a.wnp_user_id from rpt_se.ptr_vw a, rpt_se.dim_company_hist_vw b where a.company_key = b.company_key and pdt_name = 'succeed' and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') >= '2011/11/01' and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') <= '2012/01/31' intersect select a.wnp_user_id from rpt_se.ptr_vw a, rpt_se.dim_company_hist_vw b where a.company_key = b.company_key and pdt_name = 'succeed' and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') >= '2012/02/01' and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') <= '2013/02/28' ) and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') >= '2012/02/01' and to_char(to_date(orig_event_dt), 'YYYY/MM/DD') <= '2013/02/28' group by a.wnp_user_id, B.LEVEL1_NAME || '--' || B.LEVEL1_ID, B.LEVEL2_NAME || '--' || B.LEVEL2_ID , B.LEVEL3_NAME || '--' || B.LEVEL3_ID , B.LEVEL4_NAME || '--' || B.LEVEL4_ID , B.LEVEL5_NAME || '--' || B.LEVEL5_ID , B.LEVEL6_NAME || '--' || B.LEVEL6_ID , B.LEVEL7_NAME || '--' || B.LEVEL7_ID , B.LEVEL8_NAME || '--' || B.LEVEL8_ID , B.LEVEL9_NAME || '--' || B.LEVEL9_ID , B.LEVEL10_NAME || '--' || B.LEVEL10_ID , LIFESTYLE_SCR_RNG , LFSTYL_SCR_CMBND)) "SQL2" where "SQL2"."COMPANY_LABEL_1" in ('Coventry--6') group by "SQL2"."TIME_PERIOD", case when "SQL2"."LIFESTYLE_SCORE_RANGE"='90 - 100' then '(Ideal)' when "SQL2"."LIFESTYLE_SCORE_RANGE"='70 - 89' then '(Acceptable)' when "SQL2"."LIFESTYLE_SCORE_RANGE"='0 - 69' then '(Improvement Needed)' end , "SQL2"."LIFESTYLE_SCORE_RANGE", case when "SQL2"."LIFESTYLE_SCORE_RANGE"='90 - 100' then 1 when "SQL2"."LIFESTYLE_SCORE_RANGE"='70 - 89' then 2 when "SQL2"."LIFESTYLE_SCORE_RANGE"='0 - 69' then 3 end ) "T0") "T0" Rationale SQL statement with SQL_ID "5djb2yp935v58" was executed 4 times and had an average elapsed time of 250 seconds. Rationale Average CPU used per execution was 249 seconds. Finding 3: Top Segments by I/O Impact is .02 active sessions, 7.65% of total activity. ------------------------------------------------------- Individual database segments responsible for significant user I/O wait were found. Recommendation 1: Segment Tuning Estimated benefit is .01 active sessions, 3.92% of total activity. ------------------------------------------------------------------ Action Investigate application logic involving I/O on TABLE "SYS.AUD$" with object ID 376. Related Object Database object with ID 376. Rationale The I/O usage statistics for the object are: 0 full object scans, 0 physical reads, 195 physical writes and 0 direct reads. Rationale The SQL statement with SQL_ID "2cdwk2yanzj9m" spent significant time waiting for User I/O on the hot object. Related Object SQL statement with SQL_ID 2cdwk2yanzj9m. SELECT 1 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ID, b.ID PROD_REN_KEY FROM dwh.dw_product_renaming b, dwh.DW_COMPANY_HIER_V c WHERE B.COMPANY_ID = C.LEVEL1_ID UNION SELECT 2 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ID, b.ID PROD_REN_KEY FROM dwh.dw_product_renaming b, dwh.DW_COMPANY_HIER_V c WHERE B.COMPANY_ID = C.LEVEL2_ID UNION SELECT 3 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ID, b.ID PROD_REN_KEY FROM dwh.dw_product_renaming b, dwh.DW_COMPANY_HIER_V c WHERE B.COMPANY_ID = C.LEVEL3_ID UNION SELECT 4 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ID, b.ID PROD_REN_KEY FROM dwh.dw_product_renaming b, dwh.DW_COMPANY_HIER_V c WHERE B.COMPANY_ID = C.LEVEL4_ID UNION SELECT 5 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ID, b.ID PROD_REN_KEY FROM dwh.dw_product_renaming b, dwh.DW_COMPANY_HIER_V c WHERE B.COMPANY_ID = C.LEVEL5_ID UNION SELECT 6 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ID, b.ID PROD_REN_KEY FROM dwh.dw_product_renaming b, dwh.DW_COMPANY_HIER_V c WHERE B.COMPANY_ID = C.LEVEL6_ID UNION SELECT 7 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ID, b.ID PROD_REN_KEY FROM dwh.dw_product_renaming b, dwh.DW_COMPANY_HIER_V c WHERE B.COMPANY_ID = C.LEVEL7_ID UNION SELECT 8 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ID, b.ID PROD_REN_KEY FROM dwh.dw_product_renaming b, dwh.DW_COMPANY_HIER_V c WHERE B.COMPANY_ID = C.LEVEL8_ID UNION SELECT 9 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ID, b.ID PROD_REN_KEY FROM dwh.dw_product_renaming b, dwh.DW_COMPANY_HIER_V c WHERE B.COMPANY_ID = C.LEVEL9_ID UNION SELECT 10 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ID, b.ID PROD_REN_KEY FROM dwh.dw_product_renaming b, dwh.DW_COMPANY_HIER_V c WHERE B.COMPANY_ID = C.LEVEL10_ID Rationale The SQL statement with SQL_ID "8h76m78cym602" spent significant time waiting for User I/O on the hot object. Related Object SQL statement with SQL_ID 8h76m78cym602. select A.user_data_history_set_id, EVENT_DT, NAME, LEVEL1_ID, level1_name, LEVEL2_ID, level2_name, LEVEL3_ID, level3_name, LEVEL4_ID, level4_name, LEVEL5_ID, level5_name, LEVEL6_ID, level6_name, column_value,den_cnt, num_cnt, 'Column1' from (SELECT USER_DATA_HISTORY_SET_ID, LEVEL1_ID, LEVEL2_ID, LEVEL3_ID, LEVEL4_ID, LEVEL5_ID, LEVEL6_ID, LEVEL1_NAME, LEVEL2_NAME, LEVEL3_NAME, LEVEL4_NAME, LEVEL5_NAME, LEVEL6_NAME, NAME, EVENT_DT, PARTICIPANT_COUNT FROM RPT_SE.STG_DEMOGRAPHICS_V) A , (select MB.user_data_history_set_id,MB.column_value, case when value <=10 then 1 ELSE 0 END den_cnt, case when value <10 then 1 ELSE 0 END num_cnt from (select * from etl_se.stg_hlth_status unpivot(value for column_value in ( BHVRSCRALCHL AS 'Alcohol' , --BHVRSCRDPRSSN AS 'Depressive Symptoms' , --BHVRSCRDRGUS AS 'Drug Use' , BHVRSCRINJRYPRVNTN AS 'Injury Prevention' , --BHVRSCRMNTLSTRSS AS 'Stress' , BHVRSCRNTRTN AS 'Nutrition' , BHVRSCRPHYSCLACTVTY AS 'Physical Activity' , BHVRSCRSKNPRTCTN AS 'Skin Protection' , --BHVRSCRSTRSS AS 'Stress Management' , --BHVRSCRSXLBHVR AS 'Sexual Behavior' , BHVRSCRTBCC AS 'Tobacco' , BHVRSCRWGHTMNGMNT AS 'Weight Management')))MB --GROUP BY MB.user_data_history_set_id,MB.column_value) )B WHERE A.USER_DATA_HISTORY_SET_ID = B.USER_DATA_HISTORY_SET_ID and name = 'Succeed' and participant_count = 1 Recommendation 2: Segment Tuning Estimated benefit is .01 active sessions, 3.74% of total activity. ------------------------------------------------------------------ Action Investigate application logic involving I/O on database object with ID 118277. Related Object Database object with ID 118277. Symptoms That Led to the Finding: --------------------------------- Wait class "User I/O" was consuming significant database time. Impact is .03 active sessions, 15.18% of total activity. Finding 4: Top SQL By I/O Impact is .01 active sessions, 3.01% of total activity. ------------------------------------------------------- Individual SQL statements responsible for significant user I/O wait were found. Recommendation 1: SQL Tuning Estimated benefit is .01 active sessions, 7.47% of total activity. ------------------------------------------------------------------ Action Run SQL Tuning Advisor on the SQL statement with SQL_ID "2cdwk2yanzj9m". Related Object SQL statement with SQL_ID 2cdwk2yanzj9m. SELECT 1 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ID, b.ID PROD_REN_KEY FROM dwh.dw_product_renaming b, dwh.DW_COMPANY_HIER_V c WHERE B.COMPANY_ID = C.LEVEL1_ID UNION SELECT 2 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ID, b.ID PROD_REN_KEY FROM dwh.dw_product_renaming b, dwh.DW_COMPANY_HIER_V c WHERE B.COMPANY_ID = C.LEVEL2_ID UNION SELECT 3 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ID, b.ID PROD_REN_KEY FROM dwh.dw_product_renaming b, dwh.DW_COMPANY_HIER_V c WHERE B.COMPANY_ID = C.LEVEL3_ID UNION SELECT 4 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ID, b.ID PROD_REN_KEY FROM dwh.dw_product_renaming b, dwh.DW_COMPANY_HIER_V c WHERE B.COMPANY_ID = C.LEVEL4_ID UNION SELECT 5 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ID, b.ID PROD_REN_KEY FROM dwh.dw_product_renaming b, dwh.DW_COMPANY_HIER_V c WHERE B.COMPANY_ID = C.LEVEL5_ID UNION SELECT 6 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ID, b.ID PROD_REN_KEY FROM dwh.dw_product_renaming b, dwh.DW_COMPANY_HIER_V c WHERE B.COMPANY_ID = C.LEVEL6_ID UNION SELECT 7 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ID, b.ID PROD_REN_KEY FROM dwh.dw_product_renaming b, dwh.DW_COMPANY_HIER_V c WHERE B.COMPANY_ID = C.LEVEL7_ID UNION SELECT 8 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ID, b.ID PROD_REN_KEY FROM dwh.dw_product_renaming b, dwh.DW_COMPANY_HIER_V c WHERE B.COMPANY_ID = C.LEVEL8_ID UNION SELECT 9 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ID, b.ID PROD_REN_KEY FROM dwh.dw_product_renaming b, dwh.DW_COMPANY_HIER_V c WHERE B.COMPANY_ID = C.LEVEL9_ID UNION SELECT 10 LEVEL_RANK, C.Level_ID COMPANY_ID, b.PRODUCT_ID, b.ID PROD_REN_KEY FROM dwh.dw_product_renaming b, dwh.DW_COMPANY_HIER_V c WHERE B.COMPANY_ID = C.LEVEL10_ID Rationale SQL statement with SQL_ID "2cdwk2yanzj9m" was executed 3 times and had an average elapsed time of 413 seconds. Rationale Average time spent in User I/O wait events per execution was 170 seconds. Symptoms That Led to the Finding: --------------------------------- Wait class "User I/O" was consuming significant database time. Impact is .03 active sessions, 15.18% of total activity. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Additional Information ---------------------- Miscellaneous Information ------------------------- Wait class "Application" was not consuming significant database time. Wait class "Cluster" was not consuming significant database time. Wait class "Commit" was not consuming significant database time. Wait class "Concurrency" was not consuming significant database time. Wait class "Configuration" was not consuming significant database time. Wait class "Network" was not consuming significant database time. The network latency of the cluster interconnect was within acceptable limits of 1 milliseconds. Session connect and disconnect calls were not consuming significant database time. Hard parsing of SQL statements was not consuming significant database time. The database's maintenance windows were active during 16% of the analysis period. SYS@PRD011> Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options