SQL*Plus: Release 11.1.0.7.0 - Production on Tue Apr 23 12:44:12 2013 Copyright (c) 1982, 2008, Oracle. All rights reserved. @> Connected. SYS@PRD012> SYS@PRD012> SYS@PRD012> SYS@PRD012> SYS@PRD012> SYS@PRD012> SYS@PRD012> SYS@PRD012> SYS@PRD012> SYS@PRD012> SYS@PRD012> 2 3 4 PL/SQL procedure successfully completed. SYS@PRD012> SYS@PRD012> 2 3 4 PL/SQL procedure successfully completed. SYS@PRD012> SYS@PRD012> SYS@PRD012> Session altered. SYS@PRD012> 2 3 4 PL/SQL procedure successfully completed. SYS@PRD012> 2 3 4 PL/SQL procedure successfully completed. SYS@PRD012> SYS@PRD012> SYS@PRD012> SYS@PRD012> SYS@PRD012> SYS@PRD012> SYS@PRD012> SYS@PRD012> SYS@PRD012> SYS@PRD012> SYS@PRD012> SYS@PRD012> SYS@PRD012> SYS@PRD012> SYS@PRD012> SYS@PRD012> SYS@PRD012> SYS@PRD012> SYS@PRD012> SYS@PRD012> SYS@PRD012> SYS@PRD012> SYS@PRD012> SYS@PRD012> SYS@PRD012> 2 3 4 5 6 7 8 PL/SQL procedure successfully completed. SYS@PRD012> SYS@PRD012> 2 3 4 5 6 7 8 9 PL/SQL procedure successfully completed. SYS@PRD012> SYS@PRD012> 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@PRD012> SYS@PRD012> SYS@PRD012> SYS@PRD012> SYS@PRD012> SYS@PRD012> 2 ADDM Report for Task 'TASK_42687' --------------------------------- 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 PRD012, numbered 2 and hosted at prod-rac-dwh02.wp-hmi.cl.datapipe.net. Activity During the Analysis Period ----------------------------------- Total database time was 7189 seconds. The average number of active sessions was .08. Summary of Findings ------------------- Description Active Sessions Recommendations Percent of Activity ---------------------------------- ------------------- --------------- 1 Top SQL by DB Time .05 | 63.3 5 2 CPU Usage .03 | 30.93 3 3 Hard Parse Due to Parse Errors .02 | 22.62 1 4 Hard Parse Due to Sharing Criteria .01 | 10.45 1 5 Top SQL By I/O .01 | 7.17 2 6 Top Segments by I/O 0 | 5.07 1 7 Unusual "Other" Wait Event 0 | 4.58 5 8 PL/SQL Execution 0 | 2.28 1 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Findings and Recommendations ---------------------------- Finding 1: Top SQL by DB Time Impact is .05 active sessions, 63.3% of total activity. ------------------------------------------------------- SQL statements consuming significant database time were found. Recommendation 1: SQL Tuning Estimated benefit is .03 active sessions, 37.79% of total activity. ------------------------------------------------------------------- Action Tune the PL/SQL block with SQL_ID "6jbrg916bjmqc". 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 6jbrg916bjmqc. DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :mydate; broken BOOLEAN := FALSE; job_name VARCHAR2(30) := :job_name; job_subname VARCHAR2(30) := :job_subname; job_owner VARCHAR2(30) := :job_owner; job_start TIMESTAMP WITH TIME ZONE := :job_start; job_scheduled_start TIMESTAMP WITH TIME ZONE := :job_scheduled_start; window_start TIMESTAMP WITH TIME ZONE := :window_start; window_end TIMESTAMP WITH TIME ZONE := :window_end; BEGIN DECLARE ename VARCHAR2(30); BEGIN ename := dbms_sqltune.execute_tuning_task( 'SYS_AUTO_SQL_TUNING_TASK'); END; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END; Rationale SQL statement with SQL_ID "6jbrg916bjmqc" was executed 1 times and had an average elapsed time of 2727 seconds. Recommendation 2: SQL Tuning Estimated benefit is .01 active sessions, 16.4% of total activity. ------------------------------------------------------------------ Action Run SQL Tuning Advisor on the SQL statement with SQL_ID "b7jz2gj2b1q09". Related Object SQL statement with SQL_ID b7jz2gj2b1q09. /* SQL Analyze(1056,1) */ 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 "b7jz2gj2b1q09" was executed 17 times and had an average elapsed time of 68 seconds. Recommendation 3: SQL Tuning Estimated benefit is 0 active sessions, 4.55% of total activity. ---------------------------------------------------------------- Action Run SQL Tuning Advisor on the SQL statement with SQL_ID "99x75t3hnjjj1". Related Object SQL statement with SQL_ID 99x75t3hnjjj1. /* SQL Analyze(1056,1) */ select "T0"."C0" "Biometrics", "T0"."C1" "sort", "T0"."C2" "PCT1", "T0"."C3" "PCT2", "T0"."C4" "REPORT_TOTAL", sum("T0"."C2") over (partition by "T0"."C0")/nullif(sum("T0"."C5") over (partition by "T0"."C0"), 0) "Time_1", sum("T0"."C3") over (partition by "T0"."C0")/nullif(sum("T0"."C5") over (partition by "T0"."C0"), 0) "Time_2", "T0"."C6" "COMPANY_LABEL_1", "T0"."C7" "COMPANY_LABEL_2", "T0"."C8" "COMPANY_LABEL_3", "T0"."C9" "COMPANY_LABEL_4", "T0"."C10" "COMPANY_LABEL_5", "T0"."C11" "COMPANY_LABEL_6", "T0"."C12" "COMPANY_LABEL_7", "T0"."C13" "COMPANY_LABEL_8", "T0"."C14" "COMPANY_LABEL_9", "T0"."C15" "COMPANY_LABEL_10" from (select "SQL6"."Biometrics" "C0", case when "SQL6"."Biometrics"='Fasting Blood Sugar >=126' then 1 when "SQL6"."Biometrics"='Non-Fasting Blood Sugar >=200' then 2 when "SQL6"."Biometrics"='BMI >30' then 3 when "SQL6"."Biometrics"='BMI >=25 and <30' then 4 when "SQL6"."Biometrics"='HDL Cholesterol <40' then 5 when "SQL6"."Biometrics"='Total Cholesterol >=240' then 6 when "SQL6"."Biometrics"='Systolic >=120 and <140 or Diastolic >=80 and <90' then 7 when "SQL6"."Biometrics"='Systolic >=140 or Diastolic >=90' then 8 end "C1", sum("SQL6"."PCT1") "C2", sum("SQL6"."PCT2") "C3", sum("SQL6"."REPORT_TOTAL") "C4", sum("SQL6"."N_COUNT") "C5", "SQL6"."COMPANY_LABEL_1" "C6", "SQL6"."COMPANY_LABEL_2" "C7", "SQL6"."COMPANY_LABEL_3" "C8", "SQL6"."COMPANY_LABEL_4" "C9", "SQL6"."COMPANY_LABEL_5" "C10", "SQL6"."COMPANY_LABEL_6" "C11", "SQL6"."COMPANY_LABEL_7" "C12", "SQL6"."COMPANY_LABEL_8" "C13", "SQL6"."COMPANY_LABEL_9" "C14", "SQL6"."COMPANY_LABEL_10" "C15" from (select count(a.wnp_user_id) N_count, "Biometrics", to_number(Sort) Sort, a.PCT1, a.PCT2, sum(a.report_total) report_total, 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 from (select a.wnp_user_id, a.company_key, 'Fasting Blood Sugar >=126' as "Biometrics" , 1 as Sort, sum(a.FS) PCT1,sum(b.FS) PCT2 , sum(a.total) report_total from (select distinct wnp_user_id, company_key, 'Time1' t1,'cp' t2, sum(FSTNG_BLD_SGR) FS, sum(PRTCPNT_CNT) total from ptr_vw where 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' and FSTNG_BLD_SGR is not null group by company_key, 'Time1',wnp_user_id) a, (select distinct wnp_user_id,company_key, 'Time2' t1,'cp' t2,sum(FSTNG_BLD_SGR) FS, sum(PRTCPNT_CNT) total from ptr_vw where 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 FSTNG_BLD_SGR is not null group by company_key,'Time2',wnp_user_id) b where a.t2 = b.t2 and a.wnp_user_id = b.wnp_user_id group by a.wnp_user_id, a.company_key, 'Fasting Blood Sugar >=126' UNION ALL select a.wnp_user_id, a.company_key, 'Non-Fasting Blood Sugar >=200' as "Biometrics" , 2 as Sort, sum(a.NFS) PCT1,sum(b.NFS) PCT2 , sum(a.total) report_total from (select distinct wnp_user_id,company_key, 'Time1' t1,'cp' t2,sum(NON_FSTNG_BLD_SGR) NFS, sum(PRTCPNT_CNT) total from ptr_vw where 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' and NON_FSTNG_BLD_SGR is not null group by company_key, 'Time1',wnp_user_id) a, (select distinct wnp_user_id,company_key, 'Time2' t1,'cp' t2,sum(NON_FSTNG_BLD_SGR) NFS, sum(PRTCPNT_CNT) total from ptr_vw where 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 NON_FSTNG_BLD_SGR is not null group by company_key,'Time2',wnp_user_id) b where a.t2 = b.t2 and a.wnp_user_id = b.wnp_user_id group by a.wnp_user_id, a.company_key, 'Non-Fasting Blood Sugar >=200' UNION ALL select a.wnp_user_id, a.company_key, 'BMI >=25 and <30' as "Biometrics" ,4 as Sort, sum(a.BMI_25_30) PCT1, sum(b.BMI_25_30) PCT2 , sum(a.total) report_total from (select distinct wnp_user_id,company_key, 'Time1' t1,'cp' t2,sum(BMI_25_30) BMI_25_30, sum(PRTCPNT_CNT) total from ptr_vw where 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' and BMI_25_30 is not null group by company_key, 'Time1',wnp_user_id) a, (select distinct wnp_user_id,company_key, 'Time2' t1,'cp' t2,sum(BMI_25_30) BMI_25_30, sum(PRTCPNT_CNT) total from ptr_vw where 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 BMI_25_30 is not null group by company_key,'Time2',wnp_user_id) b where a.t2 = b.t2 and a.wnp_user_id = b.wnp_user_id group by a.wnp_user_id, a.company_key, 'BMI >=25 and <30' UNION ALL select a.wnp_user_id, a.company_key, 'BMI >=30' as "Biometrics" ,3 as Sort, sum(a.BMI_GT30) PCT1, sum(b.BMI_GT30) PCT2 , sum(a.total) report_total from (select distinct wnp_user_id,company_key, 'Time1' t1,'cp' t2,sum(BMI_GT30) BMI_GT30, sum(PRTCPNT_CNT) total from ptr_vw where 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' and BMI_GT30 is not null group by company_key, 'Time1',wnp_user_id) a, (select distinct wnp_user_id,company_key, 'Time2' t1,'cp' t2,sum(BMI_GT30) BMI_GT30, sum(PRTCPNT_CNT) total from ptr_vw where 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 BMI_GT30 is not null group by company_key,'Time2',wnp_user_id) b where a.t2 = b.t2 and a.wnp_user_id = b.wnp_user_id group by a.wnp_user_id, a.company_key, 'BMI >=30' UNION ALL select a.wnp_user_id, a.company_key, 'HDL Cholesterol <40' as "Biometrics" ,5 as Sort, sum(a.HDL_CHLSTRL) PCT1, sum(b.HDL_CHLSTRL) PCT2 , sum(a.total) report_total from (select distinct wnp_user_id,company_key, 'Time1' t1,'cp' t2,sum(HDL_CHLSTRL) HDL_CHLSTRL, sum(PRTCPNT_CNT) total from ptr_vw where 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' and HDL_CHLSTRL is not null group by company_key, 'Time1',wnp_user_id) a, (select distinct wnp_user_id,company_key, 'Time2' t1,'cp' t2,sum(HDL_CHLSTRL) HDL_CHLSTRL, sum(PRTCPNT_CNT) total from ptr_vw where 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 HDL_CHLSTRL is not null group by company_key,'Time2',wnp_user_id) b where a.t2 = b.t2 and a.wnp_user_id = b.wnp_user_id group by a.wnp_user_id, a.company_key, 'HDL Cholesterol <40' UNION ALL select a.wnp_user_id, a.company_key, 'Total Cholesterol >=240' as "Biometrics" ,6 as Sort, sum(a.TOT_CHLSTRL) PCT1, sum(b.TOT_CHLSTRL) PCT2 , sum(a.total) report_total from (select distinct wnp_user_id,company_key, 'Time1' t1,'cp' t2,sum(TOT_CHLSTRL) TOT_CHLSTRL, sum(PRTCPNT_CNT) total from ptr_vw where 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' and TOT_CHLSTRL is not null group by company_key, 'Time1',wnp_user_id) a, (select distinct wnp_user_id,company_key, 'Time2' t1,'cp' t2,sum(TOT_CHLSTRL) TOT_CHLSTRL, sum(PRTCPNT_CNT) total from ptr_vw where 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 TOT_CHLSTRL is not null group by company_key,'Time2',wnp_user_id) b where a.t2 = b.t2 and a.wnp_user_id = b.wnp_user_id group by a.wnp_user_id, a.company_key, 'Total Cholesterol >=240' UNION ALL select a.wnp_user_id, a.company_key, 'Systolic >=120 and <140 or Diastolic >=80 and <90' as "Biometrics" , 7 as Sort, sum(a.BP_RANGE1) PCT1,sum(b.BP_RANGE1) PCT2 , sum(a.total) report_total from (select distinct wnp_user_id,company_key, 'Time1' t1,'cp' t2,sum(BP_RANGE1) BP_RANGE1, sum(PRTCPNT_CNT) total from ptr_vw where 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' and BP_RANGE1 is not null group by company_key, 'Time1',wnp_user_id) a, (select distinct wnp_user_id,company_key, 'Time2' t1,'cp' t2,sum(BP_RANGE1) BP_RANGE1, sum(PRTCPNT_CNT) total from ptr_vw where 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 BP_RANGE1 is not null group by company_key,'Time2',wnp_user_id) b where a.t2 = b.t2 and a.wnp_user_id = b.wnp_user_id group by a.wnp_user_id, a.company_key, 'Systolic >=120 and <140 or Diastolic >=80 and <90' UNION ALL select a.wnp_user_id, a.company_key, 'Systolic >=140 or Diastolic >=90' as "Biometrics" ,8 as Sort, sum(a.BP_RANGE2) PCT1, sum(b.BP_RANGE2) PCT2 , sum(a.total) report_total from (select distinct wnp_user_id,company_key, 'Time1' t1,'cp' t2,sum(BP_RANGE2) BP_RANGE2, sum(PRTCPNT_CNT) total from ptr_vw where 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' and BP_RANGE2 is not null group by company_key, 'Time1',wnp_user_id) a, (select distinct wnp_user_id,company_key, 'Time2' t1,'cp' t2,sum(BP_RANGE2) BP_RANGE2, sum(PRTCPNT_CNT) total from ptr_vw where 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 BP_RANGE2 is not null group by company_key,'Time2',wnp_user_id) b where a.t2 = b.t2 and a.wnp_user_id = b.wnp_user_id group by a.wnp_user_id, a.company_key, 'Systolic >=140 or Diastolic >=90' ) a, rpt_se.dim_company_hist_vw b where a.company_key = b.company_key group by "Biometrics", Sort, a.PCT1, a.PCT2, 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 ) "SQL6" where "SQL6"."COMPANY_LABEL_1" in ('Coventry--6') group by "SQL6"."Biometrics", case when "SQL6"."Biometrics"='Fasting Blood Sugar >=126' then 1 when "SQL6"."Biometrics"='Non-Fasting Blood Sugar >=200' then 2 when "SQL6"."Biometrics"='BMI >30' then 3 when "SQL6"."Biometrics"='BMI >=25 and <30' then 4 when "SQL6"."Biometrics"='HDL Cholesterol <40' then 5 when "SQL6"."Biometrics"='Total Cholesterol >=240' then 6 when "SQL6"."Biometrics"='Systolic >=120 and <140 or Diastolic >=80 and <90' then 7 when "SQL6"."Biometrics"='Systolic >=140 or Diastolic >=90' then 8 end , "SQL6"."COMPANY_LABEL_1", "SQL6"."COMPANY_LABEL_2", "SQL6"."COMPANY_LABEL_3", "SQL6"."COMPANY_LABEL_4", "SQL6"."COMPANY_LABEL_5", "SQL6"."COMPANY_LABEL_6", "SQL6"."COMPANY_LABEL_7", "SQL6"."COMPANY_LABEL_8", "SQL6"."COMPANY_LABEL_9", "SQL6"."COMPANY_LABEL_10") "T0" Rationale SQL statement with SQL_ID "99x75t3hnjjj1" was executed 11 times and had an average elapsed time of 29 seconds. Recommendation 4: SQL Tuning Estimated benefit is 0 active sessions, 2.63% of total activity. ---------------------------------------------------------------- Action Investigate the SQL statement with SQL_ID "7frqszzdu8sgg" for possible performance improvements. Related Object SQL statement with SQL_ID 7frqszzdu8sgg. select DECODE(UPPER(d.OPEN_MODE),'READ WRITE','R','N') ,DECODE(UPPER(i.STATUS),'OPEN','O','N') into :b0,:b1 from v$database d ,gv$instance i where i.INSTANCE_NAME=:b2 Rationale SQL statement with SQL_ID "7frqszzdu8sgg" was executed 8611 times and had an average elapsed time of 0.021 seconds. Rationale At least one execution of the statement ran in parallel. Rationale Waiting for event "control file sequential read" in wait class "System I/O" accounted for 25% of the database time spent in processing the SQL statement with SQL_ID "7frqszzdu8sgg". Recommendation 5: SQL Tuning Estimated benefit is 0 active sessions, 2.17% of total activity. ---------------------------------------------------------------- Action Run SQL Tuning Advisor on the SQL statement with SQL_ID "4z5vk9ksuk4mh". Related Object SQL statement with SQL_ID 4z5vk9ksuk4mh. /* SQL Analyze(1056,1) */ with "Demographics" as (select "STG_DEMOGRAPHICS_V"."USER_DATA_HISTORY_SET_ID" "Set_ID", "STG_DEMOGRAPHICS_V"."LEVEL1_ID" "Company_ID_Level_1", "STG_DEMOGRAPHICS_V"."LEVEL1_NAME" "Company_Name_Level_1", "STG_DEMOGRAPHICS_V"."LEVEL2_ID" "Company_ID_Level_2", "STG_DEMOGRAPHICS_V"."LEVEL2_NAME" "Company_Name_Level_2", "STG_DEMOGRAPHICS_V"."LEVEL3_ID" "Company_ID_Level_3", "STG_DEMOGRAPHICS_V"."LEVEL3_NAME" "Company_Name_Level_3", "STG_DEMOGRAPHICS_V"."LEVEL4_ID" "Company_ID_Level_4", "STG_DEMOGRAPHICS_V"."LEVEL4_NAME" "Company_Name_Level_4", "STG_DEMOGRAPHICS_V"."LEVEL5_ID" "Company_ID_Level_5", "STG_DEMOGRAPHICS_V"."LEVEL5_NAME" "Company_Name_Level_5", "STG_DEMOGRAPHICS_V"."LEVEL6_ID" "Company_ID_Level_6", "STG_DEMOGRAPHICS_V"."LEVEL6_NAME" "Company_Name_Level_6", "STG_DEMOGRAPHICS_V"."WNP_USER_ID" "WNP_User_ID", "STG_DEMOGRAPHICS_V"."NAME" "Product_Name", "STG_DEMOGRAPHICS_V"."EVENT_DT" "Reporting_Date", "STG_DEMOGRAPHICS_V"."GENDER" "Gender", "STG_DEMOGRAPHICS_V"."ETHNCTY" "Ethnicity", "STG_DEMOGRAPHICS_V"."AG" "Age", "STG_DEMOGRAPHICS_V"."EDCTNLVL" "Education_Level", "STG_DEMOGRAPHICS_V"."RLTNSHPSTTS" "Relationship_Status", "STG_DEMOGRAPHICS_V"."LVSWTHYCHLDUNDR2" "Lives_With_Children_Under_2", "STG_DEMOGRAPHICS_V"."LVSWTHYCHLD2T12" "Lives_With_Children_2_to_12", "STG_DEMOGRAPHICS_V"."LVSWTHYTN13T18" "Lives_With_Teens_13_to_18", "STG_DEMOGRAPHICS_V"."LVSWTHYADLTS" "Lives_With_Adults", "STG_DEMOGRAPHICS_V"."LVSWTHYALN" "Lives_Alone", "STG_DEMOGRAPHICS_V"."MNWRKTYP" "Main_Work_Type", "STG_DEMOGRAPHICS_V"."MODALITY" "Modality", "STG_DEMOGRAPHICS_V"."PARTICIPANT_COUNT" "Participant_Count" from "RPT_SE"."STG_DEMOGRAPHICS_V" "STG_DEMOGRAPHICS_V"), "Substance_Use" as (select "STG_SUBSTANCE_USE"."ALCHLBNGFRQNCY" "Alcohol_Binge_Frequency", "STG_SUBSTANCE_USE"."ALCHLUSAMNT" "Alcohol_Use_Amount", "STG_SUBSTANCE_USE"."ALCHLUSSTGOFCHNG" "Alcohol_Use_Stage_Of_Change", "STG_SUBSTANCE_USE"."DRGUSSTGOFCHNG" "Drug_Use_Stage_Of_Change", "STG_SUBSTANCE_USE"."USER_DATA_HISTORY_SET_ID" "Set_ID" from "RPT_SE"."STG_SUBSTANCE_USE" "STG_SUBSTANCE_USE"), "Chronic_Condition_Mgmt" as (select "STG_CHRONIC_CNDTN_MGMT"."ACCPTSCR" "Accept_Score", "STG_CHRONIC_CNDTN_MGMT"."CNDTNINTRFRSWTHDYTDY" "c19", "STG_CHRONIC_CNDTN_MGMT"."CNDTNINTRFRSWTHHLTH" "c20", "STG_CHRONIC_CNDTN_MGMT"."CNDTNINTRFRSWTHRLTNSHPS" "c21", "STG_CHRONIC_CNDTN_MGMT"."CNDTNINTRFRSWTHSCHL" "c22", "STG_CHRONIC_CNDTN_MGMT"."CNDTNINTRFRSWTHSCL" "c23", "STG_CHRONIC_CNDTN_MGMT"."CNDTNINTRFRSWTHWRK" "Condition_Interferes_With_Work", "STG_CHRONIC_CNDTN_MGMT"."HLTHCNDTNMNGMNTHSTRY" "c62", "STG_CHRONIC_CNDTN_MGMT"."SLFMNGMNTSKLLSEMTNL" "c81", "STG_CHRONIC_CNDTN_MGMT"."SLFMNGMNTSKLLSLFSTYL" "c82", "STG_CHRONIC_CNDTN_MGMT"."SLFMNGMNTSKLLSMDCL" "Self_Management_Skills_Medical", "STG_CHRONIC_CNDTN_MGMT"."USER_DATA_HISTORY_SET_ID" "Set_ID" from "RPT_SE"."STG_CHRONIC_CNDTN_MGMT" "STG_CHRONIC_CNDTN_MGMT"), "Pain" as (select "STG_PAIN"."BCKPNAFFCTPRDCTVTYPSTMNTH" "c8", "STG_PAIN"."BCKPNCNCRNSCHRNC" "Back_Pain_Concerns_Chronic", "STG_PAIN"."BCKPNCRRNTMNCS" "Back_Pain_Current_Main_Cause", "STG_PAIN"."BCKPNCRRNTMNCSFBRMYLG" "c23", "STG_PAIN"."BCKPNCRRNTMNCSFRCTR" "c24", "STG_PAIN"."BCKPNCRRNTMNCSNNSPCFC" "c25", "STG_PAIN"."BCKPNCRRNTMNCSOSTPRSS" "c26", "STG_PAIN"."BCKPNCRRNTMNCSPRGNNCY" "c27", "STG_PAIN"."BCKPNCRRNTMNCSSCRLCDYSFNCTN" "c28", "STG_PAIN"."BCKPNCRRNTMNCSSCTC" "c29", "STG_PAIN"."BCKPNCRRNTMNCSSPNLSTNSS" "c30", "STG_PAIN"."BCKPNCRRNTMNCSSPRNSTRNSPSM" "c31", "STG_PAIN"."BCKPNEXPRNC" "Back_Pain_Experience", "STG_PAIN"."BCKPNPRVSMNCSARTHRTS" "c60", "STG_PAIN"."BCKPNPRVSMNCSDSC" "c61", "STG_PAIN"."BCKPNPRVSMNCSDNTKNW" "c62", "STG_PAIN"."BCKPNPRVSMNCSDSNTAPPLY" "c63", "STG_PAIN"."BCKPNPRVSMNCSEMTNLSTRSS" "c64", "STG_PAIN"."BCKPNPRVSMNCSFBRMYLG" "c65", "STG_PAIN"."BCKPNPRVSMNCSFRCTR" "c66", "STG_PAIN"."BCKPNPRVSMNCSNNSPCFC" "c67", "STG_PAIN"."BCKPNPRVSMNCSOSTPRSS" "c68", "STG_PAIN"."BCKPNPRVSMNCSPRGNNCY" "c69", "STG_PAIN"."BCKPNPRVSMNCSSCRLCDYSFNCTN" "c70", "STG_PAIN"."BCKPNPRVSMNCSSCTC" "c71", "STG_PAIN"."BCKPNPRVSMNCSSPNLSTNSS" "c72", "STG_PAIN"."BCKPNPRVSMNCSSPRNSTRNSPSM" "c73", "STG_PAIN"."JNTPN" "Joint_Pain", "STG_PAIN"."PNDGNSSCHRNCBCKPN" "c132", "STG_PAIN"."PNDGNSSCLSTRHDCH" "c133", "STG_PAIN"."PNDGNSSCNT" "Pain_Diagnosis_Count", "STG_PAIN"."PNDGNSSFBRMYLG" "Pain_Diagnosis_Fibromyalgia", "STG_PAIN"."PNDGNSSJNTPN" "Pain_Diagnosis_Joint_Pain", "STG_PAIN"."PNDGNSSMGRN" "Pain_Diagnosis_Migraine", "STG_PAIN"."PNDGNSSNCKPN" "Pain_Diagnosis_Neck_Pain", "STG_PAIN"."PNDGNSSNRPTHY" "Pain_Diagnosis_Neuropathy", "STG_PAIN"."PNDGNSSNDGNSS" "Pain_Diagnosis_No_Diagnosis", "STG_PAIN"."PNDGNSSOSTRTHRTS" "Pain_Diagnosis_Osteoarthritis", "STG_PAIN"."PNDGNSSOTHR" "Pain_Diagnosis_Other", "STG_PAIN"."PNDGNSSOTHRHDCH" "Pain_Diagnosis_Other_Headache", "STG_PAIN"."PNDGNSSRBNDHDCH" "c144", "STG_PAIN"."PNDGNSSRHMTDARTHRTS" "c145", "STG_PAIN"."PNDGNSSSNSHDCH" "Pain_Diagnosis_Sinus_Headache", "STG_PAIN"."PNDGNSSSLPPDDSC" "Pain_Diagnosis_Slipped_Disc", "STG_PAIN"."PNDGNSSSTRSSHDCH" "Pain_Diagnosis_Stress_Headache", "STG_PAIN"."PNDGNSSTMJDYSFNCTN" "Pain_Diagnosis_TMJ_Dysfunction", "STG_PAIN"."PNINTNSTYRTNG" "Pain_Intensity_Rating", "STG_PAIN"."PNINTRFRNC" "Pain_Interference", "STG_PAIN"."PNMRTHNTHRMNTHS" "Pain_More_Than_Three_Months", "STG_PAIN"."PNRTNG" "Pain_Rating", "STG_PAIN"."PNTRTMNTEFFCTVNSS" "Pain_Treatment_Effectiveness", "STG_PAIN"."PNUNPLSNTRTNG" "Pain_Unpleasant_Rating", "STG_PAIN"."PNWTHDPRSSN" "Pain_With_Depression", "STG_PAIN"."PRVNTORMNGBCKPN" "Prevent_Or_Manage_Back_Pain", "STG_PAIN"."STSFCTNWTHPNTRTMNTPLN" "c208", "STG_PAIN"."USER_DATA_HISTORY_SET_ID" "Set_ID" from "RPT_SE"."STG_PAIN" "STG_PAIN"), "Confidence" as (select "STG_CONFIDENCE"."CNFDNCETHLTHYARNDOTHRSTHTDNT" "c32", "STG_CONFIDENCE"."CNFDNCETHLTHYARNDTMPTNGFD" "c33", "STG_CONFIDENCE"."CNFDNCETHLTHYETOTFRQNTLY" "c34", "STG_CONFIDENCE"."CNFDNCETHLTHYNTMTGRCRYSHP" "c35", "STG_CONFIDENCE"."CNFDNCETHLTHYWNTRWRD" "c36", "STG_CONFIDENCE"."CNFDNCETHLTHYWHNTRD" "c37", "STG_CONFIDENCE"."CNFDNCFRTVGTBLSFRT" "c38", "STG_CONFIDENCE"."CNFDNCFRTVGTBLSVGGS" "c39", "STG_CONFIDENCE"."CNFDNCHNDLPRBLMSSTRSSD" "c40", "STG_CONFIDENCE"."CNFDNCSRSALCHLUS" "Confidence_Series_Alcohol_Use", "STG_CONFIDENCE"."CNFDNCSRSANXTYMNGMNT" "c84", "STG_CONFIDENCE"."CNFDNCSRSBCKPNMNG" "c85", "STG_CONFIDENCE"."CNFDNCSRSBCKPNPHYSCLTHRPYHLP" "c86", "STG_CONFIDENCE"."CNFDNCSRSBCKPNPRVNT" "c87", "STG_CONFIDENCE"."CNFDNCSRSBNGMNG" "Confidence_Series_Binge_Manage", "STG_CONFIDENCE"."CNFDNCSRSBLDPRSSRMNG" "c89", "STG_CONFIDENCE"."CNFDNCSRSCHLSTRLMNG" "c90", "STG_CONFIDENCE"."CNFDNCSRSDPRSSNMNG" "c91", "STG_CONFIDENCE"."CNFDNCSRSDBTSMNG" "c92", "STG_CONFIDENCE"."CNFDNCSRSDBTSMDCTNCNHLP" "c93", "STG_CONFIDENCE"."CNFDNCSRSDBTSTKINSLNASPRSCRBD" "c94", "STG_CONFIDENCE"."CONFSRSDBTSTKORLMEDASPRSCRBD" "c95", "STG_CONFIDENCE"."CNFDNCSRSDRGUS" "Confidence_Series_Drug_Use", "STG_CONFIDENCE"."CNFDNCSRSHLTHCNDTNMDCTNHLPS" "c97", "STG_CONFIDENCE"."CNFDNCSRSHLTHMNG" "c98", "STG_CONFIDENCE"."CNFDNCSRSHPMNG" "Confidence_Series_Hip_Manage", "STG_CONFIDENCE"."CNFDNCSRSINJRYPRVNTN" "c100", "STG_CONFIDENCE"."CNFDNCSRSINSMN" "Confidence_Series_Insomnia", "STG_CONFIDENCE"."CNFDNCSRSNTRTNIMPRV" "c102", "STG_CONFIDENCE"."CNFDNCSRSNTRTNMDRTFT" "c103", "STG_CONFIDENCE"."CNFDNCSRSNTRTNWHLGRNS" "c104", "STG_CONFIDENCE"."CNFDNCSRSPNMNGMNT" "c105", "STG_CONFIDENCE"."CNFDNCSRSPHYSCLACTVTY" "c106", "STG_CONFIDENCE"."CNFDNCSRSQTSMKNG" "Confidence_Series_Quit_Smoking", "STG_CONFIDENCE"."CNFDNCSRSSXLBHVR" "c108", "STG_CONFIDENCE"."CNFDNCSRSSKNPRTCTN" "c109", "STG_CONFIDENCE"."CNFDNCSRSSTRSSMNGMNT" "c110", "STG_CONFIDENCE"."CONFSRSSTRSSMGMTPRCTCTCHNQS" "c111", "STG_CONFIDENCE"."CNFDNCSRSWGHTMNGMNT" "c112", "STG_CONFIDENCE"."CNFDNCSYMPTMMNGMNTACTVTSTMNG" "c131", "STG_CONFIDENCE"."USER_DATA_HISTORY_SET_ID" "Set_ID" from "RPT_SE"."STG_CONFIDENCE" "STG_CONFIDENCE"), "Tobacco" as (select "STG_TBCCO_USE"."ATTMPTQTSMK" "Attempt_Quit_Smoke", "STG_TBCCO_USE"."CGRTTHRDGVUP" "Cigarette_Hard_Give_Up", "STG_TBCCO_USE"."DPRSSNQTSMKNG" "Depression_Quit_Smoking", "STG_TBCCO_USE"."DFFCLTYINNNSMKNGPLCS" "c10", "STG_TBCCO_USE"."LNGCNCRRSK" "Lung_Cancer_Risk", "STG_TBCCO_USE"."LNGCNCRRSKIFQT" "Lung_Cancer_Risk_If_Quit", "STG_TBCCO_USE"."QTSMKNGWGHTGNCNCRN" "c53", "STG_TBCCO_USE"."QTSMKNGWGHTGNRLPS" "c54", "STG_TBCCO_USE"."USER_DATA_HISTORY_SET_ID" "Set_ID", "STG_TBCCO_USE"."SMK" "Smoke", "STG_TBCCO_USE"."SMKFRSTCGRTTOFDY" "Smoke_First_Cigarette_Of_Day", "STG_TBCCO_USE"."SMKNMBRCGRTTSPRDY" "c72", "STG_TBCCO_USE"."SMKSTGOFCHNG" "Smoke_Stage_Of_Change", "STG_TBCCO_USE"."SMKWHNSCK" "Smoke_When_Sick", "STG_TBCCO_USE"."TBCCUSTYPCHWNGTBCC" "c84", "STG_TBCCO_USE"."TBCCUSTYPCGRTTS" "Tobacco_Use_Type_Cigarettes", "STG_TBCCO_USE"."TBCCUSTYPCGRS" "Tobacco_Use_Type_Cigars", "STG_TBCCO_USE"."TBCCUSTYPNN" "Tobacco_Use_Type_None", "STG_TBCCO_USE"."TBCCUSTYPPP" "Tobacco_Use_Type_Pipe", "STG_TBCCO_USE"."TBCCUSTYPSNFF" "Tobacco_Use_Type_Snuff", "STG_TBCCO_USE"."WNTTQTSMKNG" "Want_To_Quit_Smoking", "STG_TBCCO_USE"."YRSSMKD" "Years_Smoked" from "RPT_SE"."STG_TBCCO_USE" "STG_TBCCO_USE"), "Stress" as (select "STG_STRESS_MGMT"."FMLYSTRSSCRFRSCKRLTV" "c21", "STG_STRESS_MGMT"."FMLYSTRSSDTHOFLVDON" "c22", "STG_STRESS_MGMT"."FMLYSTRSSENDOFRLTNSHP" "c23", "STG_STRESS_MGMT"."FMLYSTRSSFRQNTTRVL" "Family_Stress_Frequent_Travel", "STG_STRESS_MGMT"."FMLYSTRSSLVDONINPRHLTH" "c25", "STG_STRESS_MGMT"."FMLYSTRSSMVD" "Family_Stress_Moved", "STG_STRESS_MGMT"."FMLYSTRSSNN" "Family_Stress_None", "STG_STRESS_MGMT"."FMLYSTRSSSNGLPRNT" "Family_Stress_Single_Parent", "STG_STRESS_MGMT"."HLTHSTRSSDGNSS" "Health_Stress_Diagnosis", "STG_STRESS_MGMT"."PRCVDSTRSSANGRD" "Perceived_Stress_Angered", "STG_STRESS_MGMT"."PRCVDSTRSSCNFDNT" "Perceived_Stress_Confident", "STG_STRESS_MGMT"."PRCVDSTRSSCNTRLIRRTTNS" "c53", "STG_STRESS_MGMT"."PRCVDSTRSSCPWTHTHNGS" "c54", "STG_STRESS_MGMT"."PRCVDSTRSSGNGYRWY" "c55", "STG_STRESS_MGMT"."PRCVDSTRSSNRVSSTRSSD" "c56", "STG_STRESS_MGMT"."PRCVDSTRSSNCNTRL" "Perceived_Stress_No_Control", "STG_STRESS_MGMT"."PRCVDSTRSSONTPOFTHNGS" "c58", "STG_STRESS_MGMT"."PRCVDSTRSSTMNYDFFCLTS" "c61", "STG_STRESS_MGMT"."PRCVDSTRSSUPSTUNXPCTD" "c62", "STG_STRESS_MGMT"."PRMRYSTRSSSRC" "Primary_Stress_Source", "STG_STRESS_MGMT"."USER_DATA_HISTORY_SET_ID" "Set_ID", "STG_STRESS_MGMT"."STRSSANGRYFRQNCY" "Stress_Angry_Frequency", "STG_STRESS_MGMT"."STRSSCPNGALCHL" "Stress_Coping_Alcohol", "STG_STRESS_MGMT"."STRSSCPNGASKFRHLP" "Stress_Coping_Ask_For_Help", "STG_STRESS_MGMT"."STRSSCPNGBRTHUNWND" "Stress_Coping_Breathe_Unwind", "STG_STRESS_MGMT"."STRSSCPNGCFFN" "Stress_Coping_Caffeine", "STG_STRESS_MGMT"."STRSSCPNGCNFRNTSTRSS" "Stress_Coping_Confront_Stress", "STG_STRESS_MGMT"."STRSSCPNGDSTRCTSLF" "Stress_Coping_Distract_Self", "STG_STRESS_MGMT"."STRSSCPNGETMR" "Stress_Coping_Eat_More", "STG_STRESS_MGMT"."STRSSCPNGEXRCS" "Stress_Coping_Exercise", "STG_STRESS_MGMT"."STRSSCPNGGSHPPNG" "Stress_Coping_Go_Shopping", "STG_STRESS_MGMT"."STRSSCPNGHBBS" "Stress_Coping_Hobbies", "STG_STRESS_MGMT"."STRSSCPNGIGNROWNNDS" "Stress_Coping_Ignore_Own_Needs", "STG_STRESS_MGMT"."STRSSCPNGIRRTBL" "Stress_Coping_Irritable", "STG_STRESS_MGMT"."STRSSCPNGJRNL" "Stress_Coping_Journal", "STG_STRESS_MGMT"."STRSSCPNGRLXTN" "Stress_Coping_Relaxation", "STG_STRESS_MGMT"."STRSSCPNGSKOTFRNDS" "Stress_Coping_Seek_Out_Friends", "STG_STRESS_MGMT"."STRSSCPNGSLP" "Stress_Coping_Sleep", "STG_STRESS_MGMT"."STRSSCPNGSMK" "Stress_Coping_Smoke", "STG_STRESS_MGMT"."STRSSCPNGSPRTL" "Stress_Coping_Spiritual", "STG_STRESS_MGMT"."STRSSCPNGTMWTHLVDONS" "c112", "STG_STRESS_MGMT"."STRSSCPNGUSTVINTRNT" "Stress_Coping_Use_TV_Internet", "STG_STRESS_MGMT"."STRSSCPNGVCTN" "Stress_Coping_Vacation", "STG_STRESS_MGMT"."STRSSCPNGWTHDRW" "Stress_Coping_Withdraw", "STG_STRESS_MGMT"."STRSSDFFCLTSFRQNCY" "Stress_Difficulties_Frequency", "STG_STRESS_MGMT"."STRSSFLONTPOFTHNGS" "Stress_Feel_On_Top_Of_Things", "STG_STRESS_MGMT"."STRSSMNGMNTSTGOFCHNG" "c118" from "RPT_SE"."STG_STRESS_MGMT" "STG_STRESS_MGMT"), "Weight_Management" as (select "STG_WT_MGMT"."LSWGHTSTGOFCHNG" "Lose_Weight_Stage_Of_Change", "STG_WT_MGMT"."MNTNWGHTSTGOFCHNG" "c32", "STG_WT_MGMT"."MNGWGHTSTGOFCHNG" "Manage_Weight_Stage_Of_Change", "STG_WT_MGMT"."USER_DATA_HISTORY_SET_ID" "Set_ID" from "RPT_SE"."STG_WT_MGMT" "STG_WT_MGMT"), "Motivation" as (select "STG_MOTIVATION"."MTVTNSRSALCHLUS" "Motivation_Series_Alcohol_Use", "STG_MOTIVATION"."MTVTNSRSANXTYMNGMNT" "c17", "STG_MOTIVATION"."MTVTNSRSBCKPNFLLWPHYCLTHRPYPLN" "c18", "STG_MOTIVATION"."MTVTNSRSBCKPNMNG" "c19", "STG_MOTIVATION"."MTVTNSRSBCKPNPRVNT" "c20", "STG_MOTIVATION"."MTVTNSRSBNGMNG" "Motivation_Series_Binge_Manage", "STG_MOTIVATION"."MTVTNSRSBLDPRSSRMNG" "c22", "STG_MOTIVATION"."MTVTNSRSCHLSTRLMNG" "c23", "STG_MOTIVATION"."MTVTNSRSDPRSSNMNG" "c24", "STG_MOTIVATION"."MTVTNSRSDBTSMNG" "c25", "STG_MOTIVATION"."MTVTNSRSDBTSTKINSLNASPRSCRBD" "c26", "STG_MOTIVATION"."MTVTNSRSDBTSTKORLMEDASPRSCRBD" "c27", "STG_MOTIVATION"."MTVTNSRSDRGUS" "Motivation_Series_Drug_Use", "STG_MOTIVATION"."MTVTNSRSHLTHBHVRS" "c29", "STG_MOTIVATION"."MTVTNSRSHLTHMNG" "c30", "STG_MOTIVATION"."MTVTNSRSHPMNG" "Motivation_Series_Hip_Manage", "STG_MOTIVATION"."MTVTNSRSINJRYPRVNTN" "c32", "STG_MOTIVATION"."MTVTNSRSINSMN" "Motivation_Series_Insomnia", "STG_MOTIVATION"."MTVTNSRSNTRTNFRT" "c34", "STG_MOTIVATION"."MTVTNSRSNTRTNIMPRV" "c35", "STG_MOTIVATION"."MTVTNSRSNTRTNMDRTFT" "c36", "STG_MOTIVATION"."MTVTNSRSNTRTNVGTBLS" "c37", "STG_MOTIVATION"."MTVTNSRSNTRTNWHLGRNS" "c38", "STG_MOTIVATION"."MTVTNSRSPNMNGMNT" "c39", "STG_MOTIVATION"."MTVTNSRSPHYSCLACTVTY" "c40", "STG_MOTIVATION"."MTVTNSRSQTSMKNG" "Motivation_Series_Quit_Smoking", "STG_MOTIVATION"."MTVTNSRSSXLBHVR" "c42", "STG_MOTIVATION"."MTVTNSRSSKNPRTCTN" "c43", "STG_MOTIVATION"."MTVTNSRSSTRSSMNGMNT" "c44", "STG_MOTIVATION"."MTVTNSRSSTRSSMNGMNTPRCTCTCHNQS" "c45", "STG_MOTIVATION"."MTVTNSRSTKMDCTNASPRSCRBD" "c46", "STG_MOTIVATION"."MTVTNSRSWGHTMNGMNT" "c47", "STG_MOTIVATION"."USER_DATA_HISTORY_SET_ID" "Set_ID", "STG_MOTIVATION"."VLORTRTCMMNTYMMBR" "c105", "STG_MOTIVATION"."VLORTRTDSCPLND" "Value_Or_Trait_Disciplined", "STG_MOTIVATION"."VLORTRTENRGTC" "Value_Or_Trait_Energetic", "STG_MOTIVATION"."VLORTRTGRNDPRNT" "Value_Or_Trait_Grandparent", "STG_MOTIVATION"."VLORTRTINCNTRL" "Value_Or_Trait_In_Control", "STG_MOTIVATION"."VLORTRTINDPNDNT" "Value_Or_Trait_Independent", "STG_MOTIVATION"."VLORTRTPRNT" "Value_Or_Trait_Parent", "STG_MOTIVATION"."VLORTRTRSPNSBL" "Value_Or_Trait_Responsible", "STG_MOTIVATION"."VLORTRTSPSPRTNR" "Value_Or_Trait_Spouse_Partner", "STG_MOTIVATION"."VLORTRTSTRNG" "Value_Or_Trait_Strong", "STG_MOTIVATION"."VLORTRTSCCSSFL" "Value_Or_Trait_Successful" from "RPT_SE"."STG_MOTIVATION" "STG_MOTIVATION"), "Nutrition" as (select "STG_NUTRITION"."CLCMSRVNGSPRDY" "Calcium_Servings_Per_Day", "STG_NUTRITION"."FTTYPADDD" "Fat_Type_Added", "STG_NUTRITION"."FTSSCR" "Fats_Score", "STG_NUTRITION"."FSHINTK" "Fish_Intake", "STG_NUTRITION"."FDFTLVLSCR" "Food_Fat_Level_Score", "STG_NUTRITION"."FRTSRVNGSPRDY" "Fruit_Servings_Per_Day", "STG_NUTRITION"."GRNSRVNGSPRDY" "Grain_Servings_Per_Day", "STG_NUTRITION"."IMPRVNTRTNBHVRPRRTYORDR" "c134", "STG_NUTRITION"."NTRTNSTGOFCHNG" "Nutrition_Stage_Of_Change", "STG_NUTRITION"."OLSRVNGSPRDY" "Oil_Servings_Per_Day", "STG_NUTRITION"."PRDCMTVTNANDCNFDNCCTGRY" "c163", "STG_NUTRITION"."PRDCSCR" "Produce_Score", "STG_NUTRITION"."PRTNSRVNGSPRDY" "Protein_Servings_Per_Day", "STG_NUTRITION"."STRTDTRNSFTSRVNGS" "Saturated_Trans_Fat_Servings", "STG_NUTRITION"."USER_DATA_HISTORY_SET_ID" "Set_ID", "STG_NUTRITION"."VGTBLSRVNGSPRDY" "Vegetable_Servings_Per_Day", "STG_NUTRITION"."WHLGRNAMNT" "Whole_Grain_Amount" from "RPT_SE"."STG_NUTRITION" "STG_NUTRITION"), "Physical_Activity" as (select "STG_PHYCL_ACTVTY"."MDRTPHYSCLACTVTYAMNTDYSPRWK" "c10", "STG_PHYCL_ACTVTY"."MDRTPHYSCLACTVTYAMNTMNTSPRDY" "c11", "STG_PHYCL_ACTVTY"."PHYCLACTTYLVL" "Physical_Activity_Level", "STG_PHYCL_ACTVTY"."PHYCLACTTYRSTRCTNS" "Physical_Activity_Restrictions", "STG_PHYCL_ACTVTY"."PHYSCLACTVTYRSTRCTNSLFTNG" "c126", "STG_PHYCL_ACTVTY"."PHYSCLACTVTYRSTRCTNSPARQ" "c127", "STG_PHYCL_ACTVTY"."PHYCLACTTYSLFASSSSAMNT" "c128", "STG_PHYCL_ACTVTY"."PHYCLACTTYSTGOFCHNG" "c129", "STG_PHYCL_ACTVTY"."PHYCLACTTYSTRNGTHFRQNCY" "c131", "STG_PHYCL_ACTVTY"."USER_DATA_HISTORY_SET_ID" "Set_ID", "STG_PHYCL_ACTVTY"."VGRSPHYCLACTTYAMNTDYSPRWK" "c193", "STG_PHYCL_ACTVTY"."VGRSPHYSCLACTVTYAMNTMNTSPRDY" "c194" from "RPT_SE"."STG_PHYCL_ACTVTY" "STG_PHYCL_ACTVTY"), "Sexual_Health" as (select "STG_SEXUAL_HLTH"."USER_DATA_HISTORY_SET_ID" "Set_ID", "STG_SEXUAL_HLTH"."SXLBHVRSTGOFCHNG" "c7" from "RPT_SE"."STG_SEXUAL_HLTH" "STG_SEXUAL_HLTH"), "Evaluations" as (select "STG_EVALUATIONS"."BCKPNMNGMNTHBTSATCHCKIN" "c2", "STG_EVALUATIONS"."DBTSEVLTNFLLWINSLNSCHDL" "c9", "STG_EVALUATIONS"."DBTSPRGRMRSLTBTTRMNGDBTS" "c10", "STG_EVALUATIONS"."DBTSPRGRMRSLTCOMMWTHHLTHPRVDR" "c11", "STG_EVALUATIONS"."NTRTNHBTSATCHCKIN" "Nutrition_Habits_At_Check_In", "STG_EVALUATIONS"."PRGRMSRTNG" "Programs_Rating", "STG_EVALUATIONS"."USER_DATA_HISTORY_SET_ID" "Set_ID" from "RPT_SE"."STG_EVALUATIONS" "STG_EVALUATIONS"), "Healthcare_Team" as (select "STG_HEALTH_CARE_TEAM"."BCKPNSNGHCP" "Back_Pain_Seeing_HCP", "STG_HEALTH_CARE_TEAM"."DPRSSNHLTHCRPRVDROTHR" "c12", "STG_HEALTH_CARE_TEAM"."DPRSSNHLTHCRPRVDRPCP" "c13", "STG_HEALTH_CARE_TEAM"."DPRSSNHLTHCRPRVDRPSYCHTRST" "c14", "STG_HEALTH_CARE_TEAM"."DPRSSNHLTHCRPRVDRPSYCHLGST" "c15", "STG_HEALTH_CARE_TEAM"."DCTRPTNTRLTNSHPASKQSTNS" "c32", "STG_HEALTH_CARE_TEAM"."DCTRPTNTRLTNSHPENGHTM" "c33", "STG_HEALTH_CARE_TEAM"."DCTRPTNTRLTNSHPOKYWTHRFRRLS" "c34", "STG_HEALTH_CARE_TEAM"."DCTRPTNTRLTNSHPRMMBRADVC" "c35", "STG_HEALTH_CARE_TEAM"."DCTRPTNTRLTNSHPTRST" "c36", "STG_HEALTH_CARE_TEAM"."DCTRPTNTRLTNSHPUNDRSTNDADVC" "c37", "STG_HEALTH_CARE_TEAM"."DCTRPTNTRLTNSHPUNDRSTNDSCNCRNS" "c38", "STG_HEALTH_CARE_TEAM"."HCPCMFRTASKNGQSTNS" "HCP_Comfort_Asking_Questions", "STG_HEALTH_CARE_TEAM"."HCPDBTSVSTFRQNCY" "HCP_Diabetes_Visit_Frequency", "STG_HEALTH_CARE_TEAM"."HCPQSTNLST" "HCP_Question_List", "STG_HEALTH_CARE_TEAM"."HCPTYPS" "HCP_Types", "STG_HEALTH_CARE_TEAM"."HLTHCRCRRNTDCSNMKNG" "c57", "STG_HEALTH_CARE_TEAM"."HLTHCRPRFRRDDCSNMKNG" "c59", "STG_HEALTH_CARE_TEAM"."PHRMCSTMDCTNQSTNS" "c60", "STG_HEALTH_CARE_TEAM"."PHRMCSTRLTNSHPDSCSSMDCTNPRBLMS" "c69", "STG_HEALTH_CARE_TEAM"."PHRMCSTRLTNSHPKPMDSLST" "c70", "STG_HEALTH_CARE_TEAM"."PHRMCSTRLTNSHPKPUPTDT" "c71", "STG_HEALTH_CARE_TEAM"."PHRMCSTRLTNSHPTLLABTOTCMDS" "c72", "STG_HEALTH_CARE_TEAM"."PHRMCSTRLTNSHPTLLANTHRPHRMCY" "c73", "STG_HEALTH_CARE_TEAM"."PHRMCSTRLTNSHPTLLDRGALLRGS" "c74", "STG_HEALTH_CARE_TEAM"."PHRMCTRLTNSHPTLLSPPLMNTHRBLMDS" "c75", "STG_HEALTH_CARE_TEAM"."STSFCTNWTHHCP" "Satisfaction_With_HCP", "STG_HEALTH_CARE_TEAM"."SNHCPFRCNDTN" "Seen_HCP_For_Condition", "STG_HEALTH_CARE_TEAM"."USER_DATA_HISTORY_SET_ID" "Set_ID", "STG_HEALTH_CARE_TEAM"."UNDRSTNDMDCLINFRMTN" "Understand_Medical_Information" from "RPT_SE"."STG_HEALTH_CARE_TEAM" "STG_HEALTH_CARE_TEAM"), "Disordered_Eating" as (select "STG_DISORDERED_EATING"."EXRCSTCNTRLPST4WKS" "c66", "STG_DISORDERED_EATING"."EXRCSTCNTRLWGHTSHP" "c67", "STG_DISORDERED_EATING"."LSCNTRLWHNETNG" "Lose_Control_When_Eating", "STG_DISORDERED_EATING"."LSCNTRLWHNETNGFRQNCY" "c107", "STG_DISORDERED_EATING"."OVRT" "Overeat", "STG_DISORDERED_EATING"."OVRTFRQNCY" "Overeat_Frequency", "STG_DISORDERED_EATING"."OVRTNGFRQNCYINDYS" "Overeating_Frequency_In_Days", "STG_DISORDERED_EATING"."USER_DATA_HISTORY_SET_ID" "Set_ID" from "RPT_SE"."STG_DISORDERED_EATING" "STG_DISORDERED_EATING"), "Medication" as (select "STG_MEDICATION"."ASPRNPRVNTHRTATTCKSTRK" "c1", "STG_MEDICATION"."CRRNTDPRSSNMDCTN" "Current_Depression_Medication", "STG_MEDICATION"."DBTSMDCTNPRSCRBDDNTKNW" "c16", "STG_MEDICATION"."DBTSMDCTNPRSCRBDINSLN" "c17", "STG_MEDICATION"."DBTSMDCTNPRSCRBDNNINSLNINJCTBL" "c18", "STG_MEDICATION"."DBTSMDCTNPRSCRBDNN" "c19", "STG_MEDICATION"."DBTSMDCTNPRSCRBDORL" "c20", "STG_MEDICATION"."DBTSMDCTNPRSCRBDOTHR" "c21", "STG_MEDICATION"."MDCTNTKASPRSCRBDFRQNCY" "c161", "STG_MEDICATION"."PSTETNGDSRDRMDCTNUS" "c165", "STG_MEDICATION"."USER_DATA_HISTORY_SET_ID" "Set_ID", "STG_MEDICATION"."SLPMDCTNHMPTHC" "Sleep_Medication_Homeopathic", "STG_MEDICATION"."SLPMDCTNNN" "Sleep_Medication_None", "STG_MEDICATION"."SLPMDCTNOTC" "Sleep_Medication_OTC", "STG_MEDICATION"."SLPMDCTNPRSCRPTN" "Sleep_Medication_Prescription", "STG_MEDICATION"."TKBLDPRSSRMDCTN" "Take_Blood_Pressure_Medication", "STG_MEDICATION"."TKCHLSTRLMDCTN" "Take_Cholesterol_Medication", "STG_MEDICATION"."TKSMDCTNFRETNGPRBLM" "c176" from "RPT_SE"."STG_MEDICATION" "STG_MEDICATION"), "Diabetes" as (select "STG_DIABETES"."A1CCHCKDNMBRTMSPRYRACTL" "c6", "STG_DIABETES"."A1CCHCKDNMBRTMSPRYRDNTKNW" "c7", "STG_DIABETES"."A1CNMBRACTLPRCNTG" "A1C_Number_Actual_Percentage", "STG_DIABETES"."A1CNMBRDNTKNW" "A1C_Number_Dont_Know", "STG_DIABETES"."BLDSGRCHCKACTLFRQNCY" "c11", "STG_DIABETES"."BLDSGRMNTR" "Blood_Sugar_Monitor", "STG_DIABETES"."BLDSGRNMBRFSTNGACTLMGDL" "c15", "STG_DIABETES"."BLDSGRNMBRGNRL" "Blood_Sugar_Number_General", "STG_DIABETES"."BLDSGRNMBRNNFSTNGACTLMGDL" "c17", "STG_DIABETES"."DBTSGTNGNNABV" "Diabetes_Gating_None_Above", "STG_DIABETES"."DBTSGTNGPRGNNT" "Diabetes_Gating_Pregnant", "STG_DIABETES"."DBTSGTNGTYP1" "Diabetes_Gating_Type_1", "STG_DIABETES"."DBTSGTNGTYP2" "Diabetes_Gating_Type_2", "STG_DIABETES"."DBTSHWOFTNFRGTORLMDCTN" "c84", "STG_DIABETES"."DBTSLNGTHDGNSS" "Diabetes_Length_Diagnosis", "STG_DIABETES"."HYPGLYCMFRQNCY" "Hypoglycemia_Frequency", "STG_DIABETES"."USER_DATA_HISTORY_SET_ID" "Set_ID", "STG_DIABETES"."UNDRSTNDDBTSMNGMNTA1C" "c152", "STG_DIABETES"."UNDRSTNDDBTSMNGMNTBLDSGRTSTNG" "c153", "STG_DIABETES"."UNDRSTNDDBTSMNGMNTDBTS" "c154", "STG_DIABETES"."UNDRSTNDDBTSMNGMNTDT" "c155", "STG_DIABETES"."UNDRSTNDDBTSMNGMNTPHYSCLACTVTY" "c156" from "RPT_SE"."STG_DIABETES" "STG_DIABETES"), "Blood_Pressure" as (select "STG_BLOOD_PRESSURE"."BLDPRSSRMNTR" "Blood_Pressure_Monitor", "STG_BLOOD_PRESSURE"."BPNMBRGNRL" "BP_Number_General", "STG_BLOOD_PRESSURE"."BPNMBRKNWNDSTLCMMHG" "c8", "STG_BLOOD_PRESSURE"."BPNMBRKNWNSYSTLCMMHG" "BP_Number_Known_Systolic_mm_Hg", "STG_BLOOD_PRESSURE"."USER_DATA_HISTORY_SET_ID" "Set_ID" from "RPT_SE"."STG_BLOOD_PRESSURE" "STG_BLOOD_PRESSURE"), "Cholesterol" as (select "STG_CHOLESTEROL"."CHLSTRLCHCKDRCNTLY" "Cholesterol_Checked_Recently", "STG_CHOLESTEROL"."CHLSTRLCHCKDYRLY" "Cholesterol_Checked_Yearly", "STG_CHOLESTEROL"."HDLCHLSTRLNMBREXCTMGDL" "c8", "STG_CHOLESTEROL"."HDLCHLSTRLNMBRGNRL" "HDL_Cholesterol_Number_General", "STG_CHOLESTEROL"."LDLCHLSTRLNMBREXCTMGDL" "c12", "STG_CHOLESTEROL"."LDLCHLSTRLNMBRGNRL" "LDL_Cholesterol_Number_General", "STG_CHOLESTEROL"."USER_DATA_HISTORY_SET_ID" "Set_ID", "STG_CHOLESTEROL"."TTLCHLSTRLNMBREXCTMGDL" "c16", "STG_CHOLESTEROL"."TTLCHLSTRLNMBRGNRL" "c17", "STG_CHOLESTEROL"."TRGLYCRDNMBREXCTMGDL" "Triglyceride_Number_Exact_mgdL", "STG_CHOLESTEROL"."TRGLYCRDNMBRGNRL" "Triglyceride_Number_General" from "RPT_SE"."STG_CHOLESTEROL" "STG_CHOLESTEROL"), "Health_Status" as (select "STG_HLTH_STATUS"."BHVR1" "Behavior_1", "STG_HLTH_STATUS"."BHVR10" "Behavior_10", "STG_HLTH_STATUS"."BHVR2" "Behavior_2", "STG_HLTH_STATUS"."BHVR3" "Behavior_3", "STG_HLTH_STATUS"."BHVR4" "Behavior_4", "STG_HLTH_STATUS"."BHVR5" "Behavior_5", "STG_HLTH_STATUS"."BHVR6" "Behavior_6", "STG_HLTH_STATUS"."BHVR7" "Behavior_7", "STG_HLTH_STATUS"."BHVR8" "Behavior_8", "STG_HLTH_STATUS"."BHVR9" "Behavior_9", "STG_HLTH_STATUS"."BHVRSCRALCHL" "Behavior_Score_Alcohol", "STG_HLTH_STATUS"."BHVRSCRALCHLSNPSHT" "c18", "STG_HLTH_STATUS"."BHVRSCRDPRSSN" "Behavior_Score_Depression", "STG_HLTH_STATUS"."BHVRSCRDPRSSNSNPSHT" "c20", "STG_HLTH_STATUS"."BHVRSCRDRGUS" "Behavior_Score_Drug_Use", "STG_HLTH_STATUS"."BHVRSCRINJRYPRVNTN" "c22", "STG_HLTH_STATUS"."BHVRSCRINJRYPRVNTNSNPSHT" "c23", "STG_HLTH_STATUS"."BHVRSCRMNTLSTRSS" "Behavior_Score_Mental_Stress", "STG_HLTH_STATUS"."BHVRSCRMNTLSTRSSSNPSHT" "c25", "STG_HLTH_STATUS"."BHVRSCRNTRTN" "Behavior_Score_Nutrition", "STG_HLTH_STATUS"."BHVRSCRNTRTNSNPSHT" "c27", "STG_HLTH_STATUS"."BHVRSCRPHYSCLACTVTY" "c28", "STG_HLTH_STATUS"."BHVRSCRPHYSCLACTVTYSNPSHT" "c29", "STG_HLTH_STATUS"."BHVRSCRSXLBHVR" "Behavior_Score_Sexual_Behavior", "STG_HLTH_STATUS"."BHVRSCRSKNPRTCTN" "Behavior_Score_Skin_Protection", "STG_HLTH_STATUS"."BHVRSCRSKNPRTCTNSNPSHT" "c32", "STG_HLTH_STATUS"."BHVRSCRSTRSS" "Behavior_Score_Stress", "STG_HLTH_STATUS"."BHVRSCRSTRSSSNPSHT" "Behavior_Score_Stress_Snapshot", "STG_HLTH_STATUS"."BHVRSCRTBCC" "Behavior_Score_Tobacco", "STG_HLTH_STATUS"."BHVRSCRTBCCSNPSHT" "c36", "STG_HLTH_STATUS"."BHVRSCRWGHTMNGMNT" "c38", "STG_HLTH_STATUS"."BDYMSSINDXNMBR" "Body_Mass_Index_Number", "STG_HLTH_STATUS"."CLCMSRVNGSPRDYSCR" "Calcium_Servings_Per_Day_Score", "STG_HLTH_STATUS"."ERVSTS" "ER_Visits", "STG_HLTH_STATUS"."FTGRTNG" "Fatigue_Rating", "STG_HLTH_STATUS"."FRTSRVNGSPRDYSCR" "Fruit_Servings_Per_Day_Score", "STG_HLTH_STATUS"."GNRLWLLBNGDLYACTVTS" "c68", "STG_HLTH_STATUS"."GNRLWLLBNGDPRSSD" "General_Wellbeing_Depressed", "STG_HLTH_STATUS"."GNRLWLLBNGENRGY" "General_Wellbeing_Energy", "STG_HLTH_STATUS"."GNRLWLLBNGLWESTM" "General_Wellbeing_Low_Esteem", "STG_HLTH_STATUS"."GNRLWLLBNGMDY" "General_Wellbeing_Moody", "STG_HLTH_STATUS"."GNRLWLLBNGNNABV" "General_Wellbeing_None_Above", "STG_HLTH_STATUS"."GNRLWLLBNGSLP" "General_Wellbeing_Sleep", "STG_HLTH_STATUS"."GNRLWLLBNGSTRSS" "General_Wellbeing_Stress", "STG_HLTH_STATUS"."GNRLWLLBNGUNHLTHY" "General_Wellbeing_Unhealthy", "STG_HLTH_STATUS"."GNRLWLLBNGWK" "General_Wellbeing_Weak", "STG_HLTH_STATUS"."GNRLWLLBNGWGHT" "General_Wellbeing_Weight", "STG_HLTH_STATUS"."HCPVSTFRQNCY" "HCP_Visit_Frequency", "STG_HLTH_STATUS"."HLTHLMTTNSCLMBNGONFLGHTSTRS" "c81", "STG_HLTH_STATUS"."HLTHLMTTNSCLMBNGSVRLFLGHTSSTRS" "c82", "STG_HLTH_STATUS"."HLTHLMTTNSLWINTNSTYACTVTS" "c83", "STG_HLTH_STATUS"."HLTHLMTTNSMDRTINTNSTYACTVTS" "c84", "STG_HLTH_STATUS"."HRTDSSRSKPRCNTG" "Heart_Disease_Risk_Percentage", "STG_HLTH_STATUS"."HRTDSSRSKSCRTTLSM" "c108", "STG_HLTH_STATUS"."HGHTFT" "Height_Feet", "STG_HLTH_STATUS"."HGHTINCHS" "Height_Inches", "STG_HLTH_STATUS"."LFSTYLSCRCMBND" "Lifestyle_Score_Combined", "STG_HLTH_STATUS"."MDCLHSTRYALLRGS" "Medical_History_Allergies", "STG_HLTH_STATUS"."MDCLHSTRYAMPTTN" "Medical_History_Amputation", "STG_HLTH_STATUS"."MDCLHSTRYANGN" "Medical_History_Angina", "STG_HLTH_STATUS"."MDCLHSTRYANXTYDSRDR" "c124", "STG_HLTH_STATUS"."MDCLHSTRYASTHM" "Medical_History_Asthma", "STG_HLTH_STATUS"."MDCLHSTRYBCKPN" "Medical_History_Back_Pain", "STG_HLTH_STATUS"."MEDHISTBCKSPCFCDIAGANKSPNDYTTS" "c127", "STG_HLTH_STATUS"."MEDHISTBCKSPCFCDGNSSNNOFTHABV" "c128", "STG_HLTH_STATUS"."MEDHISTBCKSPECDIAGSCRLCDYS" "c129", "STG_HLTH_STATUS"."MDCLHSTRYBCKSPCFCDGNSSSCTC" "c130", "STG_HLTH_STATUS"."MDCLHSTRYBCKSPCFCDGNSSSCLSS" "c131", "STG_HLTH_STATUS"."MEDHISTBCKSPCFCDGNSSSPNLSTNSS" "c132", "STG_HLTH_STATUS"."MDCLHSTRYBPLRDSRDR" "c133", "STG_HLTH_STATUS"."MDCLHSTRYBLDDRORKDNYCNCR" "c134", "STG_HLTH_STATUS"."MDCLHSTRYBLDDRURNRYPRBLMS" "c135", "STG_HLTH_STATUS"."MDCLHSTRYBRSTCNCR" "Medical_History_Breast_Cancer", "STG_HLTH_STATUS"."MDCLHSTRYCNCR" "Medical_History_Cancer", "STG_HLTH_STATUS"."MDCLHSTRYCRVCLCNCR" "c138", "STG_HLTH_STATUS"."MDCLHSTRYCHRNCFTGSYNDRM" "c139", "STG_HLTH_STATUS"."MDCLHSTRYCHRNCKDNYDSS" "c140", "STG_HLTH_STATUS"."MDCLHSTRYCHRNCPN" "Medical_History_Chronic_Pain", "STG_HLTH_STATUS"."MDCLHSTRYCLRCTLCNCR" "c142", "STG_HLTH_STATUS"."MDCLHSTRYCOPD" "Medical_History_COPD", "STG_HLTH_STATUS"."MDCLHSTRYCRNRYHRTDSS" "c144", "STG_HLTH_STATUS"."MDCLHSTRYDMNT" "Medical_History_Dementia", "STG_HLTH_STATUS"."MDCLHSTRYDPRSSN" "Medical_History_Depression", "STG_HLTH_STATUS"."MDCLHSTRYDBTS" "Medical_History_Diabetes", "STG_HLTH_STATUS"."MDCLHSTRYETNGDSRDR" "c149", "STG_HLTH_STATUS"."MDCLHSTRYEPLPSY" "Medical_History_Epilepsy", "STG_HLTH_STATUS"."MDCLHSTRYFBRMYLG" "Medical_History_Fibromyalgia", "STG_HLTH_STATUS"."MDCLHSTRYFTPRBLMS" "Medical_History_Foot_Problems", "STG_HLTH_STATUS"."MDCLHSTRYGLLBLDDRDSS" "c153", "STG_HLTH_STATUS"."MDCLHSTRYGERD" "Medical_History_GERD", "STG_HLTH_STATUS"."MDCLHSTRYHRTATTCK" "Medical_History_Heart_Attack", "STG_HLTH_STATUS"."MDCLHSTRYHRTFLR" "Medical_History_Heart_Failure", "STG_HLTH_STATUS"."MDCLHSTRYHPTTSB" "Medical_History_Hepatitis_B", "STG_HLTH_STATUS"."MDCLHSTRYHGHBLDPRSSR" "c158", "STG_HLTH_STATUS"."MDCLHSTRYHGHCHLSTRL" "c159", "STG_HLTH_STATUS"."MDCLHSTRYHGHTRGLYCRDS" "c160", "STG_HLTH_STATUS"."MDCLHSTRYHPPN" "Medical_History_Hip_Pain", "STG_HLTH_STATUS"."MDCLHSTRYHPSPCFCNNOFTHABV" "c162", "STG_HLTH_STATUS"."MDCLHSTRYHIVAIDS" "Medical_History_HIVAIDS", "STG_HLTH_STATUS"."MDCLHSTRYINSMN" "Medical_History_Insomnia", "STG_HLTH_STATUS"."MDCLHSTRYIRRTBLBWLSYNDRM" "c165", "STG_HLTH_STATUS"."MDCLHSTRYJNTPN" "Medical_History_Joint_Pain", "STG_HLTH_STATUS"."MDCLHSTRYLNGCNCR" "Medical_History_Lung_Cancer", "STG_HLTH_STATUS"."MDCLHSTRYMNPSLHTFLSHS" "c168", "STG_HLTH_STATUS"."MDCLHSTRYMTBLCSYNDRM" "c169", "STG_HLTH_STATUS"."MDCLHSTRYMGRN" "Medical_History_Migraine", "STG_HLTH_STATUS"."MDCLHSTRYMTHORTHRTCNCR" "c171", "STG_HLTH_STATUS"."MDCLHSTRYNRPTHY" "Medical_History_Neuropathy", "STG_HLTH_STATUS"."MDCLHSTRYNNACHV" "Medical_History_None_Achieve", "STG_HLTH_STATUS"."MDCLHSTRYNNBCKPN" "Medical_History_None_Back_Pain", "STG_HLTH_STATUS"."MDCLHSTRYNNBLNC" "Medical_History_None_Balance", "STG_HLTH_STATUS"."MDCLHSTRYNNBNG" "Medical_History_None_Binge", "STG_HLTH_STATUS"."MDCLHSTRYNNBRTH" "Medical_History_None_Breathe", "STG_HLTH_STATUS"."MDCLHSTRYNNCRPN" "Medical_History_None_Care_Pain", "STG_HLTH_STATUS"."MDCLHSTRYNNCNTRL" "Medical_History_None_Control", "STG_HLTH_STATUS"."MDCLHSTRYNNDPRSSN" "c180", "STG_HLTH_STATUS"."MDCLHSTRYNNDBTS" "Medical_History_None_Diabetes", "STG_HLTH_STATUS"."MDCLHSTRYNNINSMN" "Medical_History_None_Insomnia", "STG_HLTH_STATUS"."MDCLHSTRYNNMV" "Medical_History_None_Move", "STG_HLTH_STATUS"."MDCLHSTRYNNNRSH" "Medical_History_None_Nourish", "STG_HLTH_STATUS"."MDCLHSTRYNNRLX" "Medical_History_None_Relax", "STG_HLTH_STATUS"."MDCLHSTRYNNSNPSHT" "Medical_History_None_Snapshot", "STG_HLTH_STATUS"."MDCLHSTRYNNSTRD" "Medical_History_None_Stride", "STG_HLTH_STATUS"."MDCLHSTRYNNSCCD" "Medical_History_None_Succeed", "STG_HLTH_STATUS"."MDCLHSTRYOBSTY" "Medical_History_Obesity", "STG_HLTH_STATUS"."MDCLHSTRYOSTRTHRTS" "Medical_History_Osteoarthritis", "STG_HLTH_STATUS"."MDCLHSTRYOSTPRSS" "Medical_History_Osteoporosis", "STG_HLTH_STATUS"."MDCLHSTRYOTHR" "Medical_History_Other", "STG_HLTH_STATUS"."MDCLHSTRYOTHRHRTDSS" "c193", "STG_HLTH_STATUS"."MDCLHSTRYOTHRPSYCHTRCDSRDR" "c194", "STG_HLTH_STATUS"."MDCLHSTRYPRKNSNSDSS" "c195", "STG_HLTH_STATUS"."MDCLHSTRYPPTCULCRDSS" "c196", "STG_HLTH_STATUS"."MDCLHSTRYPRPHRLVSCLRDSS" "c197", "STG_HLTH_STATUS"."MDCLHSTRYPRDBTS" "Medical_History_Prediabetes", "STG_HLTH_STATUS"."MDCLHSTRYPRMNSTRLSYNDRM" "c199", "STG_HLTH_STATUS"."MDCLHSTRYPRSTTCNCR" "c200", "STG_HLTH_STATUS"."MDCLHSTRYRSTLSSLGSYNDRM" "c201", "STG_HLTH_STATUS"."MDCLHSTRYRHMTDARTHRTS" "c202", "STG_HLTH_STATUS"."MDCLHSTRYSXLLYTRNSMTTDDSS" "c203", "STG_HLTH_STATUS"."MDCLHSTRYSKNCNCR" "Medical_History_Skin_Cancer", "STG_HLTH_STATUS"."MDCLHSTRYSLPAPN" "Medical_History_Sleep_Apnea", "STG_HLTH_STATUS"."MDCLHSTRYSTMCHCNCR" "Medical_History_Stomach_Cancer", "STG_HLTH_STATUS"."MDCLHSTRYSTRKORTIA" "Medical_History_Stroke_Or_TIA", "STG_HLTH_STATUS"."MDCLHSTRYTRTMNTALLRGS" "c208", "STG_HLTH_STATUS"."MDCLHSTRYTRTMNTANGN" "c209", "STG_HLTH_STATUS"."MDCLHSTRYTRTMNTASTHM" "c210", "STG_HLTH_STATUS"."MDCLHSTRYTRTMNTBCKPN" "c211", "STG_HLTH_STATUS"."MDCLHSTRYTRTMNTBPLRDSRDR" "c212", "STG_HLTH_STATUS"."MDCLHSTRYTRTMNTCHRNCFTGSYNDRM" "c213", "STG_HLTH_STATUS"."MDCLHSTRYTRTMNTCHRNCKDNYDSS" "c214", "STG_HLTH_STATUS"."MDCLHSTRYTRTMNTCHRNCPN" "c215", "STG_HLTH_STATUS"."MDCLHSTRYTRTMNTCNGSTVHRTFLR" "c216", "STG_HLTH_STATUS"."MDCLHSTRYTRTMNTCOPD" "Medical_History_Treatment_COPD", "STG_HLTH_STATUS"."MDCLHSTRYTRTMNTCRNRYHRTDSS" "c218", "STG_HLTH_STATUS"."MDCLHSTRYTRTMNTDPRSSN" "c219", "STG_HLTH_STATUS"."MDCLHSTRYTRTMNTDBTS" "c220", "STG_HLTH_STATUS"."MDCLHSTRYTRTMNTFBRMYLG" "c221", "STG_HLTH_STATUS"."MDCLHSTRYTRTMNTGERD" "Medical_History_Treatment_GERD", "STG_HLTH_STATUS"."MDCLHSTRYTRTMNTHRTATTCK" "c223", "STG_HLTH_STATUS"."MDCLHSTRYTRTMNTHRTFLR" "c224", "STG_HLTH_STATUS"."MDCLHSTRYTRTMNTHGHBLDPRSSR" "c225", "STG_HLTH_STATUS"."MDCLHSTRYTRTMNTHGHCHLSTRL" "c226", "STG_HLTH_STATUS"."MDCLHSTRYTRTMNTHIVAIDS" "c227", "STG_HLTH_STATUS"."MDCLHSTRYTRTMNTIRRTBLBWLSYNDRM" "c228", "STG_HLTH_STATUS"."MDCLHSTRYTRTMNTMGRN" "c229", "STG_HLTH_STATUS"."MDCLHSTRYTRTMNTNN" "Medical_History_Treatment_None", "STG_HLTH_STATUS"."MDCLHSTRYTRTMNTOBSTY" "c231", "STG_HLTH_STATUS"."MDCLHSTRYTRTMNTOSTRTHRTS" "c232", "STG_HLTH_STATUS"."MDCLHSTRYTRTMNTOSTPRSS" "c233", "STG_HLTH_STATUS"."MDCLHSTRYTRTMNTOTHR" "c234", "STG_HLTH_STATUS"."MDCLHSTRYTRTMNTPRPHRLVSCLRDSS" "c235", "STG_HLTH_STATUS"."MDCLHSTRYTRTMNTRHMTDARTHRTS" "c236", "STG_HLTH_STATUS"."MDCLHSTRYTRTMNTSTRK" "c237", "STG_HLTH_STATUS"."NGHTSINHSPTL" "Nights_In_Hospital", "STG_HLTH_STATUS"."NMBRHLTHCRVSTSPST30DYSERVSTS" "c241", "STG_HLTH_STATUS"."NMBRHLTHCRVSTSPST30DYSHCPVSTS" "c242", "STG_HLTH_STATUS"."NBRHLTHCRVISPST30DYSHSPTLSTYS" "c243", "STG_HLTH_STATUS"."NBRHLTHCRVISPST30DYSTHRPYSSSNS" "c244", "STG_HLTH_STATUS"."PRRTYRNKALCHL" "Priority_Rank_Alcohol", "STG_HLTH_STATUS"."PRRTYRNKDPRSSN" "Priority_Rank_Depression", "STG_HLTH_STATUS"."PRRTYRNKINJRYPRVNTN" "c254", "STG_HLTH_STATUS"."PRRTYRNKNTRTN" "Priority_Rank_Nutrition", "STG_HLTH_STATUS"."PRRTYRNKPHYSCLACTVTY" "c256", "STG_HLTH_STATUS"."PRRTYRNKSXLBHVR" "Priority_Rank_Sexual_Behavior", "STG_HLTH_STATUS"."PRRTYRNKSKN" "Priority_Rank_Skin", "STG_HLTH_STATUS"."PRRTYRNKSTRSS" "Priority_Rank_Stress", "STG_HLTH_STATUS"."PRRTYRNKTBCC" "Priority_Rank_Tobacco", "STG_HLTH_STATUS"."PRRTYRNKWGHTMNGMNT" "c261", "STG_HLTH_STATUS"."QLTYOFHLTH" "Quality_Of_Health", "STG_HLTH_STATUS"."QLTYOFLF" "Quality_Of_Life", "STG_HLTH_STATUS"."USER_DATA_HISTORY_SET_ID" "Set_ID", "STG_HLTH_STATUS"."SHRTBRTHFRQNCY" "Short_Breath_Frequency", "STG_HLTH_STATUS"."VGTBLSRVNGSPRDYSCR" "c283", "STG_HLTH_STATUS"."WSTCRCMFRNCINCHS" "Waist_Circumference_Inches", "STG_HLTH_STATUS"."WGHTPNDS" "Weight_Pounds", "STG_HLTH_STATUS"."WHLGRNAMNTSCR" "Whole_Grain_Amount_Score", "STG_HLTH_STATUS"."WRKONBHVR" "Work_On_Behavior" from "RPT_SE"."STG_HLTH_STATUS" "STG_HLTH_STATUS"), "Screenings_and_Immunizations" as (select "STG_SCRNNGS"."IMMNZTNSCHCKNPX" "Immunizations_Chicken_Pox", "STG_SCRNNGS"."IMMNZTNSHPTTSB" "Immunizations_Hepatitis_B", "STG_SCRNNGS"."IMMNZTNSHPV" "Immunizations_HPV", "STG_SCRNNGS"."IMMNZTNSINFLNZ" "Immunizations_Influenza", "STG_SCRNNGS"."IMMNZTNSMMR" "Immunizations_MMR", "STG_SCRNNGS"."IMMNZTNSPNMN" "Immunizations_Pneumonia", "STG_SCRNNGS"."IMMNZTNSTTNS" "Immunizations_Tetanus", "STG_SCRNNGS"."SCRNNGBLDGLCS" "Screening_Blood_Glucose", "STG_SCRNNGS"."SCRNNGBLDPRSSR" "Screening_Blood_Pressure", "STG_SCRNNGS"."SCRNNGBNDNSTYSCN" "Screening_Bone_Density_Scan", "STG_SCRNNGS"."SCRNNGCLNCLBRSTEXM" "Screening_Clinical_Breast_Exam", "STG_SCRNNGS"."SCRNNGCRCBRMENM" "Screening_CRC_Barium_Enema", "STG_SCRNNGS"."SCRNNGCRCCLNSCPY" "Screening_CRC_Colonoscopy", "STG_SCRNNGS"."SCRNNGCRCFOBT" "Screening_CRC_FOBT", "STG_SCRNNGS"."SCRNNGCRCSGMDSCPY" "Screening_CRC_Sigmoidoscopy", "STG_SCRNNGS"."SCRNNGEYEXM" "Screening_Eye_Exam", "STG_SCRNNGS"."SCRNNGKDNYFNCTN" "Screening_Kidney_Function", "STG_SCRNNGS"."SCRNNGMMMGRM" "Screening_Mammogram", "STG_SCRNNGS"."SCRNNGPPTST" "Screening_Pap_Test", "STG_SCRNNGS"."SCRNNGPRSTTCNCR" "Screening_Prostate_Cancer", "STG_SCRNNGS"."USER_DATA_HISTORY_SET_ID" "Set_ID", "STG_SCRNNGS"."VCCNSHNGLS" "Vaccine_Shingles" from "RPT_SE"."STG_SCRNNGS" "STG_SCRNNGS"), "Womens_Health" as (select "STG_WOMENS_HLTH"."BRTHINPSTYR" "Birth_In_Past_Year", "STG_WOMENS_HLTH"."BRSTFDNG" "Breastfeeding", "STG_WOMENS_HLTH"."GSTTNLDBTS" "Gestational_Diabetes", "STG_WOMENS_HLTH"."HYSTRCTMY" "Hysterectomy", "STG_WOMENS_HLTH"."PRGNNCY" "Pregnancy", "STG_WOMENS_HLTH"."USER_DATA_HISTORY_SET_ID" "Set_ID" from "RPT_SE"."STG_WOMENS_HLTH" "STG_WOMENS_HLTH"), "Depression" as (select "STG_DEPRESSION"."CESDRSK10ITM" "CES_D_Risk10_Item", "STG_DEPRESSION"."CESDPRSSNCRYNGSPLLS" "CES_Depression_Crying_Spells", "STG_DEPRESSION"."CESDPRSSNDSLKD" "CES_Depression_Disliked", "STG_DEPRESSION"."CESDPRSSNENJYDLF" "CES_Depression_Enjoyed_Life", "STG_DEPRESSION"."CESDPRSSNFLTANXS" "CES_Depression_Felt_Anxious", "STG_DEPRESSION"."CESDPRSSNFLTDPRSSD" "CES_Depression_Felt_Depressed", "STG_DEPRESSION"."CESDPRSSNFLTEFFRT" "CES_Depression_Felt_Effort", "STG_DEPRESSION"."CESDPRSSNFLTLNLY" "CES_Depression_Felt_Lonely", "STG_DEPRESSION"."CESDPRSSNFLTSD" "CES_Depression_Felt_Sad", "STG_DEPRESSION"."CESDPRSSNFLTUSFL" "CES_Depression_Felt_Useful", "STG_DEPRESSION"."CESDPRSSNHPPY" "CES_Depression_Happy", "STG_DEPRESSION"."CESDPRSSNHPFLABTFTR" "c14", "STG_DEPRESSION"."CESDPRSSNNTGTGNG" "CES_Depression_Not_Get_Going", "STG_DEPRESSION"."CESDPRSSNOTHRSUNFRNDLY" "c16", "STG_DEPRESSION"."CESDPRSSNRSTLSSSLP" "CES_Depression_Restless_Sleep", "STG_DEPRESSION"."DPRSSNFRQNCY" "Depression_Frequency", "STG_DEPRESSION"."DPRSSNTRTMNT" "Depression_Treatment", "STG_DEPRESSION"."USER_DATA_HISTORY_SET_ID" "Set_ID" from "RPT_SE"."STG_DEPRESSION" "STG_DEPRESSION"), "Lifestyle" as (select "STG_LIFESTYLE"."HLTHHBTSACTV" "Health_Habits_Active", "STG_LIFESTYLE"."HLTHHBTSENGHSLP" "Health_Habits_Enough_Sleep", "STG_LIFESTYLE"."HLTHHBTSEXRCS" "Health_Habits_Exercise", "STG_LIFESTYLE"."HLTHHBTSFRTVGTBL" "Health_Habits_Fruit_Vegetable", "STG_LIFESTYLE"."HLTHHBTSRSTRCTFT" "Health_Habits_Restrict_Fat", "STG_LIFESTYLE"."USER_DATA_HISTORY_SET_ID" "Set_ID" from "RPT_SE"."STG_LIFESTYLE" "STG_LIFESTYLE"), "Injury_Prevention" as (select "STG_INJRY_PRVNTN"."FRRMSATHM" "Firearms_At_Home", "STG_INJRY_PRVNTN"."FRRMSLCKD" "Firearms_Locked", "STG_INJRY_PRVNTN"."FRRMSSFTYCRS" "Firearms_Safety_Course", "STG_INJRY_PRVNTN"."IMPRDDRVNGFRQNCY" "Impaired_Driving_Frequency", "STG_INJRY_PRVNTN"."RDWTHIMPRDDRVRFRQNCY" "c11", "STG_INJRY_PRVNTN"."STBLTUSFRQNCY" "Seatbelt_Use_Frequency", "STG_INJRY_PRVNTN"."USER_DATA_HISTORY_SET_ID" "Set_ID" from "RPT_SE"."STG_INJRY_PRVNTN" "STG_INJRY_PRVNTN"), "Sleep" as (select "STG_SLEEP"."AVRGSLPAMNTHRS" "Average_Sleep_Amount_Hours", "STG_SLEEP"."AVRGSLPAMNTMNTS" "Average_Sleep_Amount_Minutes", "STG_SLEEP"."FLLASLPDNGRSTTNDRVNG" "c14", "STG_SLEEP"."FLLASLPDNGRSTTNNNABV" "c15", "STG_SLEEP"."FLLASLPDNGRSTTNOTHR" "c16", "STG_SLEEP"."FLLASLPRTNG" "Fall_Asleep_Rating", "STG_SLEEP"."OVRLLSLPQLTYRTNG" "Overall_Sleep_Quality_Rating", "STG_SLEEP"."USER_DATA_HISTORY_SET_ID" "Set_ID", "STG_SLEEP"."WKTERLYRTNG" "Wake_Too_Early_Rating" from "RPT_SE"."STG_SLEEP" "STG_SLEEP"), "Productivity" as (select "STG_PRDCTVTY"."PRDCTVTYWPAIDYSWRKMSSDHLTH" "c1", "STG_PRDCTVTY"."PRDCTVTYWPAIHLTHAFFCTACTVTS" "c2", "STG_PRDCTVTY"."PRDCTVTYWPAIHLTHAFFCTWRK" "c3", "STG_PRDCTVTY"."PRDCTVTYWPAIHRSWRKMSSDGNRL" "c4", "STG_PRDCTVTY"."PRDCTVTYWPAIHRSWRKMSSDHLTH" "c5", "STG_PRDCTVTY"."PRDCTVTYWPAIHRSWRKD" "Productivity_WPAI_Hours_Worked", "STG_PRDCTVTY"."USER_DATA_HISTORY_SET_ID" "Set_ID" from "RPT_SE"."STG_PRDCTVTY" "STG_PRDCTVTY"), "Social_Support" as (select "STG_SOCIAL_SUPPORT"."AMNTOFSPPRT" "Amount_Of_Support", "STG_SOCIAL_SUPPORT"."USER_DATA_HISTORY_SET_ID" "Set_ID" from "RPT_SE"."STG_SOCIAL_SUPPORT" "STG_SOCIAL_SUPPORT"), "User_Population_Queries" as (select "Demographics"."Company_ID_Level_1" "Company_ID_Level_1", "Demographics"."Company_Name_Level_1" "Company_Name_Level_1", "Demographics"."Company_ID_Level_2" "Company_ID_Level_2", "Demographics"."Company_Name_Level_2" "Company_Name_Level_2", "Demographics"."Company_ID_Level_3" "Company_ID_Level_3", "Demographics"."Company_Name_Level_3" "Company_Name_Level_3", "Demographics"."Company_ID_Level_4" "Company_ID_Level_4", "Demographics"."Company_Name_Level_4" "Company_Name_Level_4", "Demographics"."Company_ID_Level_5" "Company_ID_Level_5", "Demographics"."Company_Name_Level_5" "Company_Name_Level_5", "Demographics"."Company_ID_Level_6" "Company_ID_Level_6", "Demographics"."Company_Name_Level_6" "Company_Name_Level_6" from "Demographics", "Substance_Use", "Chronic_Condition_Mgmt", "Pain", "Confidence", "Tobacco", "Stress", "Weight_Management", "Motivation", "Nutrition", "Physical_Activity", "Sexual_Health", "Evaluations", "Healthcare_Team", "Disordered_Eating", "Medication", "Diabetes", "Blood_Pressure", "Cholesterol", "Health_Status", "Screenings_and_Immunizations", "Womens_Health", "Depression", "Lifestyle", "Injury_Prevention", "Sleep", "Productivity", "Social_Support" where "Demographics"."Participant_Count"=1 and not "Demographics"."Participant_Count" is null and "Demographics"."Set_ID"="Blood_Pressure"."Set_ID" and "Demographics"."Set_ID"="Cholesterol"."Set_ID" and "Demographics"."Set_ID"="Chronic_Condition_Mgmt"."Set_ID" and "Demographics"."Set_ID"="Confidence"."Set_ID" and "Demographics"."Set_ID"="Depression"."Set_ID" and "Demographics"."Set_ID"="Diabetes"."Set_ID" and "Demographics"."Set_ID"="Disordered_Eating"."Set_ID" and "Demographics"."Set_ID"="Evaluations"."Set_ID" and "Demographics"."Set_ID"="Healthcare_Team"."Set_ID" and "Demographics"."Set_ID"="Health_Status"."Set_ID" and "Demographics"."Set_ID"="Injury_Prevention"."Set_ID" and "Demographics"."Set_ID"="Lifestyle"."Set_ID" and "Demographics"."Set_ID"="Medication"."Set_ID" and "Demographics"."Set_ID"="Motivation"."Set_ID" and "Demographics"."Set_ID"="Nutrition"."Set_ID" and "Demographics"."Set_ID"="Pain"."Set_ID" and "Demographics"."Set_ID"="Physical_Activity"."Set_ID" and "Demographics"."Set_ID"="Productivity"."Set_ID" and "Demographics"."Set_ID"="Screenings_and_Immunizations"."Set_ID" and "Demographics"."Set_ID"="Sexual_Health"."Set_ID" and "Demographics"."Set_ID"="Sleep"."Set_ID" and "Demographics"."Set_ID"="Social_Support"."Set_ID" and "Demographics"."Set_ID"="Stress"."Set_ID" and "Demographics"."Set_ID"="Substance_Use"."Set_ID" and "Demographics"."Set_ID"="Tobacco"."Set_ID" and "Demographics"."Set_ID"="Womens_Health"."Set_ID" and "Demographics"."Set_ID"="Weight_Management"."Set_ID") select distinct "User_Population_Queries"."Company_Name_Level_1" "Company_Name_Level_1", "User_Population_Queries"."Company_Name_Level_2" "Company_Name_Level_2", "User_Population_Queries"."Company_Name_Level_3" "Company_Name_Level_3", "User_Population_Queries"."Company_Name_Level_4" "Company_Name_Level_4", "User_Population_Queries"."Company_Name_Level_5" "Company_Name_Level_5", "User_Population_Queries"."Company_Name_Level_6" "Company_Name_Level_6", "User_Population_Queries"."Company_ID_Level_1" "Company_ID_Level_1", "User_Population_Queries"."Company_ID_Level_2" "Company_ID_Level_2", "User_Population_Queries"."Company_ID_Level_3" "Company_ID_Level_3", "User_Population_Queries"."Company_ID_Level_4" "Company_ID_Level_4", "User_Population_Queries"."Company_ID_Level_5" "Company_ID_Level_5", "User_Population_Queries"."Company_ID_Level_6" "Company_ID_Level_6" from "User_Population_Queries" order by "Company_Name_Level_1" asc nulls last, "Company_Name_Level_2" asc nulls last, "Company_Name_Level_3" asc nulls last, "Company_Name_Level_4" asc nulls last, "Company_Name_Level_5" asc nulls last, "Company_Name_Level_6" asc nulls last Rationale SQL statement with SQL_ID "4z5vk9ksuk4mh" was executed 9 times and had an average elapsed time of 17 seconds. Finding 2: CPU Usage Impact is .03 active sessions, 30.93% 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, 16.4% of total activity. ------------------------------------------------------------------ Action Run SQL Tuning Advisor on the SQL statement with SQL_ID "b7jz2gj2b1q09". Related Object SQL statement with SQL_ID b7jz2gj2b1q09. /* SQL Analyze(1056,1) */ 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 "b7jz2gj2b1q09" was executed 17 times and had an average elapsed time of 68 seconds. Rationale Average CPU used per execution was 48 seconds. Recommendation 2: Application Analysis Estimated benefit is .01 active sessions, 15.04% of total activity. ------------------------------------------------------------------- Action Parsing SQL statements were consuming significant CPU. Please refer to other findings in this task about parsing for further details. Recommendation 3: SQL Tuning Estimated benefit is 0 active sessions, 4.55% of total activity. ---------------------------------------------------------------- Action Run SQL Tuning Advisor on the SQL statement with SQL_ID "99x75t3hnjjj1". Related Object SQL statement with SQL_ID 99x75t3hnjjj1. /* SQL Analyze(1056,1) */ select "T0"."C0" "Biometrics", "T0"."C1" "sort", "T0"."C2" "PCT1", "T0"."C3" "PCT2", "T0"."C4" "REPORT_TOTAL", sum("T0"."C2") over (partition by "T0"."C0")/nullif(sum("T0"."C5") over (partition by "T0"."C0"), 0) "Time_1", sum("T0"."C3") over (partition by "T0"."C0")/nullif(sum("T0"."C5") over (partition by "T0"."C0"), 0) "Time_2", "T0"."C6" "COMPANY_LABEL_1", "T0"."C7" "COMPANY_LABEL_2", "T0"."C8" "COMPANY_LABEL_3", "T0"."C9" "COMPANY_LABEL_4", "T0"."C10" "COMPANY_LABEL_5", "T0"."C11" "COMPANY_LABEL_6", "T0"."C12" "COMPANY_LABEL_7", "T0"."C13" "COMPANY_LABEL_8", "T0"."C14" "COMPANY_LABEL_9", "T0"."C15" "COMPANY_LABEL_10" from (select "SQL6"."Biometrics" "C0", case when "SQL6"."Biometrics"='Fasting Blood Sugar >=126' then 1 when "SQL6"."Biometrics"='Non-Fasting Blood Sugar >=200' then 2 when "SQL6"."Biometrics"='BMI >30' then 3 when "SQL6"."Biometrics"='BMI >=25 and <30' then 4 when "SQL6"."Biometrics"='HDL Cholesterol <40' then 5 when "SQL6"."Biometrics"='Total Cholesterol >=240' then 6 when "SQL6"."Biometrics"='Systolic >=120 and <140 or Diastolic >=80 and <90' then 7 when "SQL6"."Biometrics"='Systolic >=140 or Diastolic >=90' then 8 end "C1", sum("SQL6"."PCT1") "C2", sum("SQL6"."PCT2") "C3", sum("SQL6"."REPORT_TOTAL") "C4", sum("SQL6"."N_COUNT") "C5", "SQL6"."COMPANY_LABEL_1" "C6", "SQL6"."COMPANY_LABEL_2" "C7", "SQL6"."COMPANY_LABEL_3" "C8", "SQL6"."COMPANY_LABEL_4" "C9", "SQL6"."COMPANY_LABEL_5" "C10", "SQL6"."COMPANY_LABEL_6" "C11", "SQL6"."COMPANY_LABEL_7" "C12", "SQL6"."COMPANY_LABEL_8" "C13", "SQL6"."COMPANY_LABEL_9" "C14", "SQL6"."COMPANY_LABEL_10" "C15" from (select count(a.wnp_user_id) N_count, "Biometrics", to_number(Sort) Sort, a.PCT1, a.PCT2, sum(a.report_total) report_total, 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 from (select a.wnp_user_id, a.company_key, 'Fasting Blood Sugar >=126' as "Biometrics" , 1 as Sort, sum(a.FS) PCT1,sum(b.FS) PCT2 , sum(a.total) report_total from (select distinct wnp_user_id, company_key, 'Time1' t1,'cp' t2, sum(FSTNG_BLD_SGR) FS, sum(PRTCPNT_CNT) total from ptr_vw where 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' and FSTNG_BLD_SGR is not null group by company_key, 'Time1',wnp_user_id) a, (select distinct wnp_user_id,company_key, 'Time2' t1,'cp' t2,sum(FSTNG_BLD_SGR) FS, sum(PRTCPNT_CNT) total from ptr_vw where 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 FSTNG_BLD_SGR is not null group by company_key,'Time2',wnp_user_id) b where a.t2 = b.t2 and a.wnp_user_id = b.wnp_user_id group by a.wnp_user_id, a.company_key, 'Fasting Blood Sugar >=126' UNION ALL select a.wnp_user_id, a.company_key, 'Non-Fasting Blood Sugar >=200' as "Biometrics" , 2 as Sort, sum(a.NFS) PCT1,sum(b.NFS) PCT2 , sum(a.total) report_total from (select distinct wnp_user_id,company_key, 'Time1' t1,'cp' t2,sum(NON_FSTNG_BLD_SGR) NFS, sum(PRTCPNT_CNT) total from ptr_vw where 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' and NON_FSTNG_BLD_SGR is not null group by company_key, 'Time1',wnp_user_id) a, (select distinct wnp_user_id,company_key, 'Time2' t1,'cp' t2,sum(NON_FSTNG_BLD_SGR) NFS, sum(PRTCPNT_CNT) total from ptr_vw where 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 NON_FSTNG_BLD_SGR is not null group by company_key,'Time2',wnp_user_id) b where a.t2 = b.t2 and a.wnp_user_id = b.wnp_user_id group by a.wnp_user_id, a.company_key, 'Non-Fasting Blood Sugar >=200' UNION ALL select a.wnp_user_id, a.company_key, 'BMI >=25 and <30' as "Biometrics" ,4 as Sort, sum(a.BMI_25_30) PCT1, sum(b.BMI_25_30) PCT2 , sum(a.total) report_total from (select distinct wnp_user_id,company_key, 'Time1' t1,'cp' t2,sum(BMI_25_30) BMI_25_30, sum(PRTCPNT_CNT) total from ptr_vw where 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' and BMI_25_30 is not null group by company_key, 'Time1',wnp_user_id) a, (select distinct wnp_user_id,company_key, 'Time2' t1,'cp' t2,sum(BMI_25_30) BMI_25_30, sum(PRTCPNT_CNT) total from ptr_vw where 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 BMI_25_30 is not null group by company_key,'Time2',wnp_user_id) b where a.t2 = b.t2 and a.wnp_user_id = b.wnp_user_id group by a.wnp_user_id, a.company_key, 'BMI >=25 and <30' UNION ALL select a.wnp_user_id, a.company_key, 'BMI >=30' as "Biometrics" ,3 as Sort, sum(a.BMI_GT30) PCT1, sum(b.BMI_GT30) PCT2 , sum(a.total) report_total from (select distinct wnp_user_id,company_key, 'Time1' t1,'cp' t2,sum(BMI_GT30) BMI_GT30, sum(PRTCPNT_CNT) total from ptr_vw where 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' and BMI_GT30 is not null group by company_key, 'Time1',wnp_user_id) a, (select distinct wnp_user_id,company_key, 'Time2' t1,'cp' t2,sum(BMI_GT30) BMI_GT30, sum(PRTCPNT_CNT) total from ptr_vw where 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 BMI_GT30 is not null group by company_key,'Time2',wnp_user_id) b where a.t2 = b.t2 and a.wnp_user_id = b.wnp_user_id group by a.wnp_user_id, a.company_key, 'BMI >=30' UNION ALL select a.wnp_user_id, a.company_key, 'HDL Cholesterol <40' as "Biometrics" ,5 as Sort, sum(a.HDL_CHLSTRL) PCT1, sum(b.HDL_CHLSTRL) PCT2 , sum(a.total) report_total from (select distinct wnp_user_id,company_key, 'Time1' t1,'cp' t2,sum(HDL_CHLSTRL) HDL_CHLSTRL, sum(PRTCPNT_CNT) total from ptr_vw where 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' and HDL_CHLSTRL is not null group by company_key, 'Time1',wnp_user_id) a, (select distinct wnp_user_id,company_key, 'Time2' t1,'cp' t2,sum(HDL_CHLSTRL) HDL_CHLSTRL, sum(PRTCPNT_CNT) total from ptr_vw where 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 HDL_CHLSTRL is not null group by company_key,'Time2',wnp_user_id) b where a.t2 = b.t2 and a.wnp_user_id = b.wnp_user_id group by a.wnp_user_id, a.company_key, 'HDL Cholesterol <40' UNION ALL select a.wnp_user_id, a.company_key, 'Total Cholesterol >=240' as "Biometrics" ,6 as Sort, sum(a.TOT_CHLSTRL) PCT1, sum(b.TOT_CHLSTRL) PCT2 , sum(a.total) report_total from (select distinct wnp_user_id,company_key, 'Time1' t1,'cp' t2,sum(TOT_CHLSTRL) TOT_CHLSTRL, sum(PRTCPNT_CNT) total from ptr_vw where 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' and TOT_CHLSTRL is not null group by company_key, 'Time1',wnp_user_id) a, (select distinct wnp_user_id,company_key, 'Time2' t1,'cp' t2,sum(TOT_CHLSTRL) TOT_CHLSTRL, sum(PRTCPNT_CNT) total from ptr_vw where 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 TOT_CHLSTRL is not null group by company_key,'Time2',wnp_user_id) b where a.t2 = b.t2 and a.wnp_user_id = b.wnp_user_id group by a.wnp_user_id, a.company_key, 'Total Cholesterol >=240' UNION ALL select a.wnp_user_id, a.company_key, 'Systolic >=120 and <140 or Diastolic >=80 and <90' as "Biometrics" , 7 as Sort, sum(a.BP_RANGE1) PCT1,sum(b.BP_RANGE1) PCT2 , sum(a.total) report_total from (select distinct wnp_user_id,company_key, 'Time1' t1,'cp' t2,sum(BP_RANGE1) BP_RANGE1, sum(PRTCPNT_CNT) total from ptr_vw where 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' and BP_RANGE1 is not null group by company_key, 'Time1',wnp_user_id) a, (select distinct wnp_user_id,company_key, 'Time2' t1,'cp' t2,sum(BP_RANGE1) BP_RANGE1, sum(PRTCPNT_CNT) total from ptr_vw where 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 BP_RANGE1 is not null group by company_key,'Time2',wnp_user_id) b where a.t2 = b.t2 and a.wnp_user_id = b.wnp_user_id group by a.wnp_user_id, a.company_key, 'Systolic >=120 and <140 or Diastolic >=80 and <90' UNION ALL select a.wnp_user_id, a.company_key, 'Systolic >=140 or Diastolic >=90' as "Biometrics" ,8 as Sort, sum(a.BP_RANGE2) PCT1, sum(b.BP_RANGE2) PCT2 , sum(a.total) report_total from (select distinct wnp_user_id,company_key, 'Time1' t1,'cp' t2,sum(BP_RANGE2) BP_RANGE2, sum(PRTCPNT_CNT) total from ptr_vw where 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' and BP_RANGE2 is not null group by company_key, 'Time1',wnp_user_id) a, (select distinct wnp_user_id,company_key, 'Time2' t1,'cp' t2,sum(BP_RANGE2) BP_RANGE2, sum(PRTCPNT_CNT) total from ptr_vw where 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 BP_RANGE2 is not null group by company_key,'Time2',wnp_user_id) b where a.t2 = b.t2 and a.wnp_user_id = b.wnp_user_id group by a.wnp_user_id, a.company_key, 'Systolic >=140 or Diastolic >=90' ) a, rpt_se.dim_company_hist_vw b where a.company_key = b.company_key group by "Biometrics", Sort, a.PCT1, a.PCT2, 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 ) "SQL6" where "SQL6"."COMPANY_LABEL_1" in ('Coventry--6') group by "SQL6"."Biometrics", case when "SQL6"."Biometrics"='Fasting Blood Sugar >=126' then 1 when "SQL6"."Biometrics"='Non-Fasting Blood Sugar >=200' then 2 when "SQL6"."Biometrics"='BMI >30' then 3 when "SQL6"."Biometrics"='BMI >=25 and <30' then 4 when "SQL6"."Biometrics"='HDL Cholesterol <40' then 5 when "SQL6"."Biometrics"='Total Cholesterol >=240' then 6 when "SQL6"."Biometrics"='Systolic >=120 and <140 or Diastolic >=80 and <90' then 7 when "SQL6"."Biometrics"='Systolic >=140 or Diastolic >=90' then 8 end , "SQL6"."COMPANY_LABEL_1", "SQL6"."COMPANY_LABEL_2", "SQL6"."COMPANY_LABEL_3", "SQL6"."COMPANY_LABEL_4", "SQL6"."COMPANY_LABEL_5", "SQL6"."COMPANY_LABEL_6", "SQL6"."COMPANY_LABEL_7", "SQL6"."COMPANY_LABEL_8", "SQL6"."COMPANY_LABEL_9", "SQL6"."COMPANY_LABEL_10") "T0" Rationale SQL statement with SQL_ID "99x75t3hnjjj1" was executed 11 times and had an average elapsed time of 29 seconds. Rationale Average CPU used per execution was 29 seconds. Finding 3: Hard Parse Due to Parse Errors Impact is .02 active sessions, 22.62% of total activity. -------------------------------------------------------- Hard parsing SQL statements that encountered parse errors was consuming significant database time. Recommendation 1: Application Analysis Estimated benefit is .02 active sessions, 22.62% of total activity. ------------------------------------------------------------------- Action Investigate application logic to eliminate parse errors. Symptoms That Led to the Finding: --------------------------------- Hard parsing of SQL statements was consuming significant database time. Impact is .03 active sessions, 32.25% of total activity. Finding 4: Hard Parse Due to Sharing Criteria Impact is .01 active sessions, 10.45% of total activity. -------------------------------------------------------- SQL statements with the same text were not shared because of cursor environment mismatch. This resulted in additional hard parses which were consuming significant database time. Common causes of environment mismatch are session NLS settings, SQL trace settings and optimizer parameters. Recommendation 1: Application Analysis Estimated benefit is .01 active sessions, 10.45% of total activity. ------------------------------------------------------------------- Action Look for top reason for cursor environment mismatch in V$SQL_SHARED_CURSOR. Symptoms That Led to the Finding: --------------------------------- Hard parsing of SQL statements was consuming significant database time. Impact is .03 active sessions, 32.25% of total activity. Finding 5: Top SQL By I/O Impact is .01 active sessions, 7.17% 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, 16.4% of total activity. ------------------------------------------------------------------ Action Run SQL Tuning Advisor on the SQL statement with SQL_ID "b7jz2gj2b1q09". Related Object SQL statement with SQL_ID b7jz2gj2b1q09. /* SQL Analyze(1056,1) */ 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 "b7jz2gj2b1q09" was executed 17 times and had an average elapsed time of 68 seconds. Rationale Average time spent in User I/O wait events per execution was 21 seconds. Recommendation 2: SQL Tuning Estimated benefit is 0 active sessions, 2.28% of total activity. ---------------------------------------------------------------- Action Run SQL Tuning Advisor on the SQL statement with SQL_ID "8szmwam7fysa3". Related Object SQL statement with SQL_ID 8szmwam7fysa3. insert into wri$_adv_objspace_trend_data select timepoint, space_usage, space_alloc, quality from table(dbms_space.object_growth_trend(:1, :2, :3, :4, NULL, NULL, NULL, 'FALSE', :5, 'FALSE')) Rationale SQL statement with SQL_ID "8szmwam7fysa3" was executed 22 times and had an average elapsed time of 7.4 seconds. Rationale Average time spent in User I/O wait events per execution was 7.1 seconds. Symptoms That Led to the Finding: --------------------------------- Wait class "User I/O" was consuming significant database time. Impact is .01 active sessions, 9.98% of total activity. Finding 6: Top Segments by I/O Impact is 0 active sessions, 5.07% of total activity. ----------------------------------------------------- Individual database segments responsible for significant user I/O wait were found. Recommendation 1: Segment Tuning Estimated benefit is 0 active sessions, 5.07% of total activity. ---------------------------------------------------------------- Action Investigate application logic involving I/O on INDEX "DWH.IDX_COMPA_PAID" with object ID 76845. Related Object Database object with ID 76845. Rationale The I/O usage statistics for the object are: 0 full object scans, 0 physical reads, 0 physical writes and 0 direct reads. Rationale The SQL statement with SQL_ID "b7jz2gj2b1q09" spent significant time waiting for User I/O on the hot object. Related Object SQL statement with SQL_ID b7jz2gj2b1q09. /* SQL Analyze(1056,1) */ 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 Symptoms That Led to the Finding: --------------------------------- Wait class "User I/O" was consuming significant database time. Impact is .01 active sessions, 9.98% of total activity. Finding 7: Unusual "Other" Wait Event Impact is 0 active sessions, 4.58% of total activity. ----------------------------------------------------- Wait event "reliable message" in wait class "Other" was consuming significant database time. Recommendation 1: Application Analysis Estimated benefit is 0 active sessions, 4.58% of total activity. ---------------------------------------------------------------- Action Investigate the cause for high "reliable message" waits. Refer to Oracle's "Database Reference" for the description of this wait event. Recommendation 2: Application Analysis Estimated benefit is 0 active sessions, 4.58% of total activity. ---------------------------------------------------------------- Action Investigate the cause for high "reliable message" waits in Service "SYS$USERS". Recommendation 3: Application Analysis Estimated benefit is 0 active sessions, 1.96% of total activity. ---------------------------------------------------------------- Action Investigate the cause for high "reliable message" waits in Module "perl@prod-rac-dwh02.wp-hmi.cl.datapipe.net (TNS". Recommendation 4: Application Analysis Estimated benefit is 0 active sessions, 1.7% of total activity. --------------------------------------------------------------- Action Investigate the cause for high "reliable message" waits in Module "emagent_SQL_rac_database". Recommendation 5: Application Analysis Estimated benefit is 0 active sessions, .92% of total activity. --------------------------------------------------------------- Action Investigate the cause for high "reliable message" waits in Module "racgimon@prod-rac-dwh02.wp-hmi.cl.datapipe.net ". Symptoms That Led to the Finding: --------------------------------- Wait class "Other" was consuming significant database time. Impact is .01 active sessions, 7.82% of total activity. Finding 8: PL/SQL Execution Impact is 0 active sessions, 2.28% of total activity. ----------------------------------------------------- PL/SQL execution consumed significant database time. Recommendation 1: SQL Tuning Estimated benefit is 0 active sessions, 2.28% of total activity. ---------------------------------------------------------------- Action Tune the entry point PL/SQL "SYS.DBMS_SPACE.AUTO_SPACE_ADVISOR_JOB_PROC" of type "PACKAGE" and ID 7262. Refer to the PL/SQL documentation for addition information. Rationale 164 seconds spent in executing PL/SQL "SYS.DBMS_SPACE.OBJECT_SPACE_USAGE" of type "PACKAGE" and ID 7262. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 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. The database's maintenance windows were active during 16% of the analysis period. SYS@PRD012> 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