SQL*Plus: Release 10.2.0.5.0 - Production on Tue Apr 23 12:20:50 2013 Copyright (c) 1982, 2010, Oracle. All Rights Reserved. @> Connected. SYS@dwh12> SYS@dwh12> SYS@dwh12> SYS@dwh12> SYS@dwh12> SYS@dwh12> SYS@dwh12> SYS@dwh12> SYS@dwh12> SYS@dwh12> SYS@dwh12> 2 3 4 PL/SQL procedure successfully completed. SYS@dwh12> SYS@dwh12> 2 3 4 PL/SQL procedure successfully completed. SYS@dwh12> SYS@dwh12> SYS@dwh12> Session altered. SYS@dwh12> 2 3 4 PL/SQL procedure successfully completed. SYS@dwh12> 2 3 4 PL/SQL procedure successfully completed. SYS@dwh12> SYS@dwh12> SYS@dwh12> SYS@dwh12> SYS@dwh12> SYS@dwh12> SYS@dwh12> SYS@dwh12> SYS@dwh12> SYS@dwh12> SYS@dwh12> SYS@dwh12> SYS@dwh12> SYS@dwh12> SYS@dwh12> SYS@dwh12> SYS@dwh12> SYS@dwh12> SYS@dwh12> SYS@dwh12> SYS@dwh12> SYS@dwh12> SYS@dwh12> SYS@dwh12> SYS@dwh12> 2 3 4 5 6 7 8 PL/SQL procedure successfully completed. SYS@dwh12> SYS@dwh12> 2 3 4 5 6 7 8 9 PL/SQL procedure successfully completed. SYS@dwh12> SYS@dwh12> 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@dwh12> SYS@dwh12> SYS@dwh12> SYS@dwh12> SYS@dwh12> SYS@dwh12> 2 DETAILED ADDM REPORT FOR TASK 'TASK_177069' WITH ID 177069 ---------------------------------------------------------- Analysis Period: from 22-APR-2013 00:00 to 23-APR-2013 00:00 Database ID/Instance: 3987792604/2 Database/Instance Names: DWH1/dwh12 Host Name: prod-rac-dwh02.wp-hmi.cl.datapipe.net Database Version: 10.2.0.5.0 Snapshot Range: from 52436 to 52460 Database Time: 238739 seconds Average Database Load: 2.8 active sessions ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ FINDING 1: 100% impact (238739 seconds) --------------------------------------- Significant virtual memory paging was detected on the host operating system. RECOMMENDATION 1: Host Configuration, 100% benefit (238739 seconds) ACTION: Host operating system was experiencing significant paging but no particular root cause could be detected. Investigate processes that do not belong to this instance running on the host that are consuming significant amount of virtual memory. Also consider adding more physical memory to the host. RECOMMENDATION 2: DB Configuration, 100% benefit (238739 seconds) ACTION: Total PGA memory used was above the specified "pga_aggregate_target" value for 30% of the time. Consider increasing your current "pga_aggregate_target". Note that the sum of "pga_aggregate_target" and SGA size should be less than the physical memory available on the host. RATIONALE: The value of parameter "pga_aggregate_target" was "520 M" during the analysis period. FINDING 2: 30% impact (72309 seconds) ------------------------------------- Individual database segments responsible for significant user I/O wait were found. RECOMMENDATION 1: Segment Tuning, 8.9% benefit (21343 seconds) ACTION: Run "Segment Advisor" on TABLE "DATAWAREHOUSE.DWH_PARTICIPANTDATA" with object id 1680856. RELEVANT OBJECT: database object with id 1680856 ACTION: Investigate application logic involving I/O on TABLE "DATAWAREHOUSE.DWH_PARTICIPANTDATA" with object id 1680856. RELEVANT OBJECT: database object with id 1680856 RATIONALE: The I/O usage statistics for the object are: 31 full object scans, 3351644 physical reads, 6090 physical writes and 0 direct reads. RATIONALE: The SQL statement with SQL_ID "2hw4b9qgvx4tb" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID 2hw4b9qgvx4tb SELECT RPAD(DISTCD,6)||chr(9)||RPAD(PPROJECTCD,8)||chr(9)||RPAD(E.INS TRUMENTCD,11)||chr(9)||rpad(E.ENROLLMETHODCD,15)||chr(9)|| rpad(E.COMMTYPECD,10)||chr(9)||MIN(PUBLICATIONDT) FROM DATAWAREHOUSE.MV_CHBACOREDEMOPROD S1, PARTICIPANT P, EPISODES E, PIECESTATUS PC WHERE P.PARTICIPANTID=E.PARTICIPANTID AND E.INSTRUMENTCD='chba' and PC.PARTICIPANTID=E.PARTICIPANTID AND PC.EPISODEID=E.EPISODEID and PC.PARTICIPANTID=s1.PARTICIPANTID AND PC.EPISODEID=s1.EPISODEID AND TRUNC(PC.PUBLICATIONDT)>=TO_DATE('12/04/2009','MM/DD/YYYY') AND TRUNC(PC.INITIALTAILORDT)>=TO_DATE('12/04/2009','MM/DD/YYYY') AND P.TESTDATA IS NULL AND PC.PIECECD IN ('chbamas','shramas','mas') and (MEDHISTFAM_OSTEO|| MEDHISTFAM_OBESITY|| MEDHISTFAM_DIABETES|| MEDHISTFAM_HICHOL|| MEDHISTFAM_PROSTATE|| MEDHISTFAM_COLORECTAL|| MEDHISTFAM_BREAST|| MEDHISTFAM_STROKE|| MEDHISTFAM_HBP|| MEDHISTFAM_HAFEMALE|| MEDHISTFAM_HAMALE|| MEDHISTFAM_NONE) is not null group by DISTCD,PPROJECTCD,E.INSTRUMENTCD,E.ENROLLMETHODCD,E.COMMTYPE CD RATIONALE: The SQL statement with SQL_ID "cf077pjuzgpjk" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID cf077pjuzgpjk INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "DATA_ANALYSIS"."MV_CAHA_CUSTOMER_DIMENSION" select distinct e.participantid, e. episodeid, case when upper(d.disttx) like 'TAKEDA%' then 'Takeda' when d.distcd = 'ABBT' then 'Abbott' when d.distcd = 'ATNA' then 'Aetna' when d.distcd = 'HAHT' then 'Harris Health Trends' when d.distcd = 'MHN1' then 'MHN' when d.distcd = 'FLEXBEN' then 'FLEXBEN' when d.distcd = 'HSOL' then 'Health Solutions' when d.distcd = 'MEDI' then 'Medifit' when d.distcd = 'RWJS' then 'RWJS' when d.distcd = 'PROF' or pj.projectcd = 'spac' then 'Pro-Fitness' when d.distcd = 'WLCR' then 'Wellcare' when d.distcd in ('CORP','CORH') then 'LifeSynch' when pj.projectcd in ('bcbsmn','bcbsmnhba') then 'BCBS - MN' when pj.projectcd in ('bcri','hmpc') then 'BCBS - RI' when pj.projectcd = 'bcma' then 'BCBS - MA' when pj.projectcd = 'bcla' then 'BCBS - LA' when pj.projectcd in ('azhh','azek') then 'AstraZeneca' when pj.projectcd = 'hpac' then 'Hewlett-Packard' when pj.projectcd = 'hap2' then 'HAP' when pj.projectcd = 'hap1' then 'HAP1' when pj.projectcd in ('hibc', 'hibs', 'himk', 'hinm', 'hipt', 'hmem','hhic') then 'Highmark' when pj.projectcd = 'hmi4' then 'HealthMedia' when pj.projectcd = 'hum2' then 'Humana' when pj.projectcd in ('jjsn','spjj') then 'Johnson & Johnson' when pj.projectcd = 'kprc' then 'Kaiser Permanente' when pj.projectcd = 'clev' then 'Cleveland Clinic' when d.distcd = 'HUMA' then 'Humana' when d.distcd in ('KPPL','KPTE') then 'Kaiser Permanente' when p.customercd='KPPLS' then 'Kaiser Permanente' when d.distcd = 'SWH1' then 'Scott & White Health Plan' when pj.projectcd = 'tuft' then 'Tufts Health Plan' when pj.projectcd = 'ups1' then 'UPS' when d.disttx = 'Group and Pension Administrators, Inc' then 'GPA' when d.distcd='BCMI' then 'BCBSM' when d.distcd='BCMIS' then 'BCBSM Spanish' -- when pj.projectx = 'N/A' and d.distcd <> 'HMI' then d.disttx -- when pj.projectx = 'N/A' and pj.projectcd = 'vhba1' then 'Quickstart' -- when d.distcd = 'HMI' and c.customercd = 'FIND' then pj.projectx -- when d.distcd = 'HMI' and c.customercd <> 'HMI' then c.customertx -- when d.distcd = 'HMI' then pj.projectx -- when d.distcd <> 'HMI' then replace(d.disttx, ',', '') when d.distcd = 'HMI' then 'HealthMedia' else replace(d.disttx, ',', '') end distributor, case when pj.projectcd in ('hibc', 'hibs', 'himk', 'hinm', 'hipt', 'hmem','hhic') then 'HIGHMARK' when pj.projectcd = 'hap2' then 'HAP' when pj.projectcd = 'hap1' then 'HAP1' when pj.projectcd in ('azhh','azek') then 'ASTR' when pj.projectcd in ('bcbsmn','bcbsmnhba') then 'BCBSMN' when pj.projectcd in ('bcri','hmpc') then 'BCBSRI' when pj.projectcd = 'bcma' then 'BCBSMA' when pj.projectcd = 'bcla' then 'BCBSLA' when pj.projectcd = 'hpac' then 'HPAC' when pj.projectcd = 'hum2' then 'HUMA' when pj.projectcd in ('jjsn','spjj') then 'JSON' when pj.projectcd = 'kprc' then 'KPPL' when pj.projectcd = 'spac' then 'PROF' when d.distcd = 'SWH1' then 'SWHP' when pj.projectcd = 'tuft' then 'TUFT' when pj.projectcd = 'ups1' then 'UPS' when pj.projectcd = 'clev' then 'CLEV' when d.distcd='HLTX' then c.customercd else d.distcd end distcd, case when d.distcd = 'ATNA' then 'Aetna' when pj.projectcd in ('azhh','azek') then 'AstraZeneca MHM' when d.distcd = 'ASTR' then 'AstraZeneca' -- when d.distcd = 'AVEN' then 'Aventis' when c.customercd = 'OMS' then 'OMS' when c.customercd = 'ABBT' then 'Abbott' when c.customercd = 'HAHT' then 'Harris Health Trends' when c.customercd = 'SHPS' then 'CareWise/SHPS' when c.customercd = 'PNIC' then 'Parke Nicollet' when c.customercd = 'MARS' then 'Scarborough' when c.customercd = 'FIND' then 'FIND' when c.customercd = 'MEDI' then 'Medifit' when c.customercd = 'OPTE' then 'Optate' when c.customercd = 'HEMOUAT' then 'PPL UAT' when c.customercd = 'RWJS' then 'RWJS' when c.customercd = 'WLCR' then 'Wellcare' when pj.projectcd = 'ebay' then 'eBay' when pj.projectcd = 'clev' then 'Cleveland Clinic' when pj.projectcd in ('acsn','spac') then 'Acushnet' when pj.projectcd = 'boin' then 'Boehringer-Ingelheim' when pj.projectcd in ('bcbsmn','bcbsmnhba') then 'BCBS - MN' when pj.projectcd in ('bcri','hmpc') then 'BCBS - RI' when pj.projectcd = 'bcma' then 'BCBS - MA' when pj.projectcd = 'bcla' then 'BCBS - LA' when pj.projectcd = 'tuft' then 'Tufts Health Plan' when pj.projectcd = 'ups1' then 'UPS' when d.distcd = 'SWH1' then 'Scott & White Health Plan' when pj.projectcd = 'hap2' then 'HAP' when pj.projectcd = 'hap1' then 'HAP1' when pj.projectcd = 'hpac' then 'Hewlett-Packard' when d.distcd = 'HP' then d.disttx when d.distcd = 'FLEXBEN' then 'FLEXBEN' when d.distcd = 'HSOL' then 'Health Solutions' when pj.projectcd in ('spjj','jjpr') then 'Puerto Rico' when pj.projectcd in ('jjsn','spjj') or c.customercd = 'JSON' then 'Johnson & Johnson' when pj.projectcd in ('hibc','hibs','hipt','hmem') then 'Highmark Members' when pj.projectcd = 'hinm' then 'Highmark NonMembers' when pj.projectcd = 'himk' then 'Highmark Employees' when pj.projectcd = 'hum2' then 'Humana MHM' when upper(pj.projectx) like 'TAKEDA%' then 'Takeda Diabetes' when pj.projectcd = 'kpag' then 'KP PAG' when pj.projectcd = 'kprc' then 'KP RCT' when d.distcd = 'KPPL' then 'KP Private Label' when d.distcd = 'KPTE' then 'KP Teen' when pj.projectcd = 'hmi4' then 'HealthMedia' when d.distcd = 'MHN1' then c.customercd else (case when pj.projectx = 'N/A' and d.distcd <> 'HMI' then replace(d.disttx, ',','') when c.customertx = 'N/A' and d.distcd = 'HMI' then 'HealthMedia' else replace(c.customertx, ',','') end) end customer, case when pj.projectcd in ('hibc','hibs','hipt','hmem') then 'HMEM' when pj.projectcd = 'hinm' then 'HINM' when pj.projectcd = 'himk' then 'HIMK' when pj.projectcd = 'hap2' then 'HAP' when pj.projectcd = 'hap1' then 'HAP1' when pj.projectcd = 'clev' then 'CLEV' when pj.projectcd in ('azhh','azek') then 'ASTRMHM' when pj.projectcd in ('bcbsmn','bcbsmnhba') then 'BCBSMN' when pj.projectcd in ('bcri','hmpc') then 'BCBSRI' when pj.projectcd = 'bcma' then 'BCBSMA' when pj.projectcd = 'bcla' then 'BCBSLA' when pj.projectcd = 'hpac' then 'HPAC' when pj.projectcd = 'hum2' then 'HUMAMHM' when pj.projectcd = 'jjsn' then 'JSON' when pj.projectcd in ('jjpr','spjj') then 'JSONPR' when pj.projectcd = 'kprc' then 'KPRC' when pj.projectcd = 'kpag' then 'KPAG' when pj.projectcd = 'kpth' then 'KPPL' when pj.projectcd in ('spac', 'acsn') then 'ACSN' when pj.projectcd = 'boin' then 'BOIN' when d.distcd = 'SWH1' then 'SWHP' when pj.projectcd = 'tuft' then 'TUFT' when pj.projectcd = 'ups1' then 'UPS' when pj.projectcd = 'ebay' then 'EBAY' else c.customercd end customercd, case when pj.projectcd = 'beha' then 'Aetna BHA' when pj.projectcd = 'azeh' then 'eHypertension' when pj.projectcd = 'azst' then 'eHypertension Study' when pj.projectcd = 'azek' then 'EverydayKidz' when upper(pj.projectx) like 'TAKEDA%' then 'Takeda Diabetes' when pj.projectcd = 'bcma' then 'BCBS Massachusetts' when pj.projectcd in ('bcbsmn','bcbsmnhba') then 'BCBS Minnesota' when pj.projectcd in ('bcri','hmpc') then 'BCBS Rhode Island' when pj.projectcd = 'flexben' then 'Flexben Succeed' when pj.projectcd = 'hum2' then 'Humana MHM' when pj.projectcd = 'azhh' then 'Heart Horizons' when pj.projectcd = 'geis' then 'Geisinger' when pj.projectcd = 'hmi4' then 'HealthMedia' when pj.projectcd = 'macd' then 'MacDill AF Base' when pj.projectcd = 'msgn' then 'MedStat' when pj.projectcd = 'pinw' then 'Pinnacle West' when pj.projectcd = 'pshs' then 'PSHS' when pj.projectcd = 'sjm2' then 'SJMH - Macomb' when pj.projectcd = 'cmiu' then 'Central Michigan' when pj.projectcd = 'brem' then 'Bremer' when pj.projectcd = 'scar' then 'Scarborough' when pj.projectcd = 'jjpr' then 'J & J PR English' when pj.projectcd = 'spjj' then 'J & J PR Spanish' when pj.projectcd = 'kpp1' then 'KP Private Label' when pj.projectcd = 'qmed' then 'MediFit Corporate' when pj.projectcd in ('gtwn', 'gtw2') then 'Georgetown' when pj.projectcd = 'hmsa' then 'HMSA' when pj.projectcd = 'dtoc' then 'DTC' when pj.projectcd = 'hap1' then 'HAP Employees' when pj.projectcd = 'hap2' then 'HAP Members' when upper(pj.projectcd) like 'OIL%' then 'Owens Illinois' when pj.projectx = 'N/A' and pj.projectcd = 'vhba1' then 'Quickstart' when pj.projectcd in ('netd', 'neth') then 'Affinity' when pj.projectcd = 'msgn' then 'MedStat' when pj.projectcd in ('uhss', 'umhs') then 'University of Michigan' when pj.projectcd in ('mpin', 'mpl2', 'mplc', 'mpln', 'mpmc', 'mpnm') then 'M-Plan' when pj.projectcd in ('codb','cor2','cornell') then 'Cornell' when pj.projectcd in ('alb2','albv') then 'Albuquerque VA' when pj.projectx = 'N/A' and d.distcd <> 'HMI' then d.disttx when pj.projectx = 'N/A' and pj.projectcd = 'vhba1' then 'Quickstart' else replace(pj.projectx, ',', '') end project, case when pj.projectcd in ('mpin', 'mpl2', 'mplc', 'mpln', 'mpmc', 'mpnm') then 'mplan' when upper(pj.projectcd) like 'OIL%' then 'oil' when pj.projectcd in ('codb','cor2','cornell') then 'cornell' when pj.projectcd in ('gtwn', 'gtw2') then 'gtwn' when pj.projectcd in ('uhss', 'umhs') then 'uofm' else pj.projectcd end projectcd, case when s.sitecd = 'hap1' then 'HAP Employees' when s.sitecd = 'hap2' then 'HAP Members' when s.sitecd = 'clev' and p.customq1 is null then 'CLEVna' when s.sitecd = 'clev' then 'CLEV' || p.customq1 when pj.projectcd = 'beha' then 'Aetna BHA' when pj.projectcd = 'azeh' then 'eHypertension' when pj.projectcd = 'azst' then 'eHypertension Study' when pj.projectcd = 'azek' then 'EverydayKidz' when pj.projectcd = 'bcma' then 'BCBS Massachusetts' when pj.projectcd = 'clar' then 'Clarian' when upper(s.sitetx) like 'TAKEDA%' then 'Takeda Diabetes' when s.sitecd = 'kpp1' then 'KP Private Label' when s.sitecd = 'azhh' then 'Heart Horizons' when s.sitecd = 'brem' then 'Bremer' when s.sitecd = 'scar' then 'Scarborough' when s.sitecd = 'fort' then 'Fortis' when s.sitecd = 'geis' then 'Geisinger' when s.sitecd = 'hmi4' then 'HMI BE Campaign' when s.sitecd = 'hum2' then 'Humana MHM' when s.sitecd = 'pinw' then 'Pinnacle West - Nourish' when s.sitecd = 'sjm2' then 'St. Josephs Mercy Hospital - Macomb' when s.sitecd = 'undf' then pj.projectx when s.sitecd = 'boin' then pj.projectx when s.sitetx like 'N/A%' and pj.projectx = 'N/A' then (case when pj.projectx = 'N/A' and d.distcd <> 'HMI' then d.disttx when pj.projectx = 'N/A' and pj.projectcd = 'vhba1' then 'Quickstart' else pj.projectx end) when s.sitetx like 'N/A%' and pj.projectx <> 'N/A' then pj.projectx when s.sitetx is null then pj.projectx else replace(s.sitetx, ',','') end site, case when s.sitecd = 'clev' and p.customq1 is null then 'CLEVna' when s.sitecd = 'clev' then 'CLEV' || p.customq1 else s.sitecd end sitecd, case when s.sitecd = 'clev' and p.customq2 is null then 'CLEVna' when s.sitecd = 'clev' then 'CLEV' || p.customq2 when p.pauthcd is null then 'N/A' else p.pauthcd end auth_code, case when p.plocation is null then 'N/A' when (P.PDISTCD='KPPL' or P.CUSTOMERCD='KPPLS') AND p.plocation IS NOT NULL THEN P.CUSTOMQ4||LTRIM(RTRIM(p.plocation)) else (p.plocation) end location --,case when pj.projectcd in ('hibc', 'hibs', 'himk', 'hinm', 'hipt', 'hmem') then 1 -- else 0 -- end ESR_HBA_BEHAVIOR_10, --case when pj.projectcd in ('hibc', 'hibs', 'himk', 'hinm', 'hipt', 'hmem') then 1 -- else 0 -- end ESR_HBA_BEHAVIOR_7 --,case when pj.projectcd in ('hibc', 'hibs', 'himk', 'hinm', 'hipt', 'hmem') and p.customq1 is not null then --upper(replace(p.customq1, ',','')) -- when pj.projectcd in ('hibc', 'hibs', 'himk', 'hinm', 'hipt', 'hmem') and p.customq1 is null then 'N/A' -- else 'N/A' -- end highmark_location2 from datawarehouse.mv_participant p, datawarehouse.mv_site s, datawarehouse.mv_project pj, datawarehouse.mv_customer c, datawarehouse.mv_distributor d, data_analysis.caha_analysis e where p.sitecd = s.sitecd and s.projectcd = pj.projectcd and pj.customercd = c.customercd and c.distcd = d.distcd and e.participantid=p.participantid and p.testdata is null and upper(d.distcd) not like '%DEMO%' and upper(c.customercd) not like '%DEMO%' and upper(c.customertx) not like '%DEMO%' and upper(pj.projectx) not like '%DEMO%' and upper(pj.projectx) not like '%TRIAL%' and upper(pj.projectx) not like '%PILOT%' and pj.projectcd <> 'bln' RATIONALE: The SQL statement with SQL_ID "922qdarm4cyjn" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID 922qdarm4cyjn INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "DATA_ANALYSIS"."MV_BLNA_CUSTOMER_DIMENSION" select distinct e.participantid, e. episodeid, case when upper(d.disttx) like 'TAKEDA%' then 'Takeda' when d.distcd = 'ABBT' then 'Abbott' when d.distcd = 'ATNA' then 'Aetna' when d.distcd = 'HAHT' then 'Harris Health Trends' when d.distcd = 'MHN1' then 'MHN' when d.distcd = 'FLEXBEN' then 'FLEXBEN' when d.distcd = 'HSOL' then 'Health Solutions' when d.distcd = 'MEDI' then 'Medifit' when d.distcd = 'RWJS' then 'RWJS' when d.distcd = 'PROF' or pj.projectcd = 'spac' then 'Pro-Fitness' when d.distcd = 'WLCR' then 'Wellcare' when d.distcd in ('CORP','CORH') then 'LifeSynch' when pj.projectcd in ('bcbsmn','bcbsmnhba') then 'BCBS - MN' when pj.projectcd in ('bcri','hmpc') then 'BCBS - RI' when pj.projectcd = 'bcma' then 'BCBS - MA' when pj.projectcd = 'bcla' then 'BCBS - LA' when pj.projectcd in ('azhh','azek') then 'AstraZeneca' when pj.projectcd = 'hpac' then 'Hewlett-Packard' when pj.projectcd = 'hap2' then 'HAP' when pj.projectcd = 'hap1' then 'HAP1' when pj.projectcd in ('hibc', 'hibs', 'himk', 'hinm', 'hipt', 'hmem','hhic') then 'Highmark' when pj.projectcd = 'hmi4' then 'HealthMedia' when pj.projectcd = 'hum2' then 'Humana' when pj.projectcd in ('jjsn','spjj') then 'Johnson & Johnson' when pj.projectcd = 'kprc' then 'Kaiser Permanente' when pj.projectcd = 'clev' then 'Cleveland Clinic' when d.distcd = 'HUMA' then 'Humana' when d.distcd in ('KPPL','KPTE') then 'Kaiser Permanente' when p.customercd='KPPLS' then 'Kaiser Permanente' when d.distcd = 'SWH1' then 'Scott & White Health Plan' when pj.projectcd = 'tuft' then 'Tufts Health Plan' when pj.projectcd = 'ups1' then 'UPS' when d.disttx = 'Group and Pension Administrators, Inc' then 'GPA' when d.distcd='BCMI' then 'BCBSM' when d.distcd='BCMIS' then 'BCBSM Spanish' -- when pj.projectx = 'N/A' and d.distcd <> 'HMI' then d.disttx -- when pj.projectx = 'N/A' and pj.projectcd = 'vhba1' then 'Quickstart' -- when d.distcd = 'HMI' and c.customercd = 'FIND' then pj.projectx -- when d.distcd = 'HMI' and c.customercd <> 'HMI' then c.customertx -- when d.distcd = 'HMI' then pj.projectx -- when d.distcd <> 'HMI' then replace(d.disttx, ',', '') when d.distcd = 'HMI' then 'HealthMedia' else replace(d.disttx, ',', '') end distributor, case when pj.projectcd in ('hibc', 'hibs', 'himk', 'hinm', 'hipt', 'hmem','hhic') then 'HIGHMARK' when pj.projectcd = 'hap2' then 'HAP' when pj.projectcd = 'hap1' then 'HAP1' when pj.projectcd in ('azhh','azek') then 'ASTR' when pj.projectcd in ('bcbsmn','bcbsmnhba') then 'BCBSMN' when pj.projectcd in ('bcri','hmpc') then 'BCBSRI' when pj.projectcd = 'bcma' then 'BCBSMA' when pj.projectcd = 'bcla' then 'BCBSLA' when pj.projectcd = 'hpac' then 'HPAC' when pj.projectcd = 'hum2' then 'HUMA' when pj.projectcd in ('jjsn','spjj') then 'JSON' when pj.projectcd = 'kprc' then 'KPPL' when pj.projectcd = 'spac' then 'PROF' when d.distcd = 'SWH1' then 'SWHP' when pj.projectcd = 'tuft' then 'TUFT' when pj.projectcd = 'ups1' then 'UPS' when pj.projectcd = 'clev' then 'CLEV' when d.distcd='HLTX' then c.customercd else d.distcd end distcd, case when d.distcd = 'ATNA' then 'Aetna' when pj.projectcd in ('azhh','azek') then 'AstraZeneca MHM' when d.distcd = 'ASTR' then 'AstraZeneca' -- when d.distcd = 'AVEN' then 'Aventis' when c.customercd = 'OMS' then 'OMS' when c.customercd = 'ABBT' then 'Abbott' when c.customercd = 'HAHT' then 'Harris Health Trends' when c.customercd = 'SHPS' then 'CareWise/SHPS' when c.customercd = 'PNIC' then 'Parke Nicollet' when c.customercd = 'MARS' then 'Scarborough' when c.customercd = 'FIND' then 'FIND' when c.customercd = 'MEDI' then 'Medifit' when c.customercd = 'OPTE' then 'Optate' when c.customercd = 'HEMOUAT' then 'PPL UAT' when c.customercd = 'RWJS' then 'RWJS' when c.customercd = 'WLCR' then 'Wellcare' when pj.projectcd = 'ebay' then 'eBay' when pj.projectcd = 'clev' then 'Cleveland Clinic' when pj.projectcd in ('acsn','spac') then 'Acushnet' when pj.projectcd = 'boin' then 'Boehringer-Ingelheim' when pj.projectcd in ('bcbsmn','bcbsmnhba') then 'BCBS - MN' when pj.projectcd in ('bcri','hmpc') then 'BCBS - RI' when pj.projectcd = 'bcma' then 'BCBS - MA' when pj.projectcd = 'bcla' then 'BCBS - LA' when pj.projectcd = 'tuft' then 'Tufts Health Plan' when pj.projectcd = 'ups1' then 'UPS' when d.distcd = 'SWH1' then 'Scott & White Health Plan' when pj.projectcd = 'hap2' then 'HAP' when pj.projectcd = 'hap1' then 'HAP1' when pj.projectcd = 'hpac' then 'Hewlett-Packard' when d.distcd = 'HP' then d.disttx when d.distcd = 'FLEXBEN' then 'FLEXBEN' when d.distcd = 'HSOL' then 'Health Solutions' when pj.projectcd in ('spjj','jjpr') then 'Puerto Rico' when pj.projectcd in ('jjsn','spjj') or c.customercd = 'JSON' then 'Johnson & Johnson' when pj.projectcd in ('hibc','hibs','hipt','hmem') then 'Highmark Members' when pj.projectcd = 'hinm' then 'Highmark NonMembers' when pj.projectcd = 'himk' then 'Highmark Employees' when pj.projectcd = 'hum2' then 'Humana MHM' when upper(pj.projectx) like 'TAKEDA%' then 'Takeda Diabetes' when pj.projectcd = 'kpag' then 'KP PAG' when pj.projectcd = 'kprc' then 'KP RCT' when d.distcd = 'KPPL' then 'KP Private Label' when d.distcd = 'KPTE' then 'KP Teen' when pj.projectcd = 'hmi4' then 'HealthMedia' when d.distcd = 'MHN1' then c.customercd else (case when pj.projectx = 'N/A' and d.distcd <> 'HMI' then replace(d.disttx, ',','') when c.customertx = 'N/A' and d.distcd = 'HMI' then 'HealthMedia' else replace(c.customertx, ',','') end) end customer, case when pj.projectcd in ('hibc','hibs','hipt','hmem') then 'HMEM' when pj.projectcd = 'hinm' then 'HINM' when pj.projectcd = 'himk' then 'HIMK' when pj.projectcd = 'hap2' then 'HAP' when pj.projectcd = 'hap1' then 'HAP1' when pj.projectcd = 'clev' then 'CLEV' when pj.projectcd in ('azhh','azek') then 'ASTRMHM' when pj.projectcd in ('bcbsmn','bcbsmnhba') then 'BCBSMN' when pj.projectcd in ('bcri','hmpc') then 'BCBSRI' when pj.projectcd = 'bcma' then 'BCBSMA' when pj.projectcd = 'bcla' then 'BCBSLA' when pj.projectcd = 'hpac' then 'HPAC' when pj.projectcd = 'hum2' then 'HUMAMHM' when pj.projectcd = 'jjsn' then 'JSON' when pj.projectcd in ('jjpr','spjj') then 'JSONPR' when pj.projectcd = 'kprc' then 'KPRC' when pj.projectcd = 'kpag' then 'KPAG' when pj.projectcd = 'kpth' then 'KPPL' when pj.projectcd in ('spac', 'acsn') then 'ACSN' when pj.projectcd = 'boin' then 'BOIN' when d.distcd = 'SWH1' then 'SWHP' when pj.projectcd = 'tuft' then 'TUFT' when pj.projectcd = 'ups1' then 'UPS' when pj.projectcd = 'ebay' then 'EBAY' else c.customercd end customercd, case when pj.projectcd = 'beha' then 'Aetna BHA' when pj.projectcd = 'azeh' then 'eHypertension' when pj.projectcd = 'azst' then 'eHypertension Study' when pj.projectcd = 'azek' then 'EverydayKidz' when upper(pj.projectx) like 'TAKEDA%' then 'Takeda Diabetes' when pj.projectcd = 'bcma' then 'BCBS Massachusetts' when pj.projectcd in ('bcbsmn','bcbsmnhba') then 'BCBS Minnesota' when pj.projectcd in ('bcri','hmpc') then 'BCBS Rhode Island' when pj.projectcd = 'flexben' then 'Flexben Succeed' when pj.projectcd = 'hum2' then 'Humana MHM' when pj.projectcd = 'azhh' then 'Heart Horizons' when pj.projectcd = 'geis' then 'Geisinger' when pj.projectcd = 'hmi4' then 'HealthMedia' when pj.projectcd = 'macd' then 'MacDill AF Base' when pj.projectcd = 'msgn' then 'MedStat' when pj.projectcd = 'pinw' then 'Pinnacle West' when pj.projectcd = 'pshs' then 'PSHS' when pj.projectcd = 'sjm2' then 'SJMH - Macomb' when pj.projectcd = 'cmiu' then 'Central Michigan' when pj.projectcd = 'brem' then 'Bremer' when pj.projectcd = 'scar' then 'Scarborough' when pj.projectcd = 'jjpr' then 'J & J PR English' when pj.projectcd = 'spjj' then 'J & J PR Spanish' when pj.projectcd = 'kpp1' then 'KP Private Label' when pj.projectcd = 'qmed' then 'MediFit Corporate' when pj.projectcd in ('gtwn', 'gtw2') then 'Georgetown' when pj.projectcd = 'hmsa' then 'HMSA' when pj.projectcd = 'dtoc' then 'DTC' when pj.projectcd = 'hap1' then 'HAP Employees' when pj.projectcd = 'hap2' then 'HAP Members' when upper(pj.projectcd) like 'OIL%' then 'Owens Illinois' when pj.projectx = 'N/A' and pj.projectcd = 'vhba1' then 'Quickstart' when pj.projectcd in ('netd', 'neth') then 'Affinity' when pj.projectcd = 'msgn' then 'MedStat' when pj.projectcd in ('uhss', 'umhs') then 'University of Michigan' when pj.projectcd in ('mpin', 'mpl2', 'mplc', 'mpln', 'mpmc', 'mpnm') then 'M-Plan' when pj.projectcd in ('codb','cor2','cornell') then 'Cornell' when pj.projectcd in ('alb2','albv') then 'Albuquerque VA' when pj.projectx = 'N/A' and d.distcd <> 'HMI' then d.disttx when pj.projectx = 'N/A' and pj.projectcd = 'vhba1' then 'Quickstart' else replace(pj.projectx, ',', '') end project, case when pj.projectcd in ('mpin', 'mpl2', 'mplc', 'mpln', 'mpmc', 'mpnm') then 'mplan' when upper(pj.projectcd) like 'OIL%' then 'oil' when pj.projectcd in ('codb','cor2','cornell') then 'cornell' when pj.projectcd in ('gtwn', 'gtw2') then 'gtwn' when pj.projectcd in ('uhss', 'umhs') then 'uofm' else pj.projectcd end projectcd, case when s.sitecd = 'hap1' then 'HAP Employees' when s.sitecd = 'hap2' then 'HAP Members' when s.sitecd = 'clev' and p.customq1 is null then 'CLEVna' when s.sitecd = 'clev' then 'CLEV' || p.customq1 when pj.projectcd = 'beha' then 'Aetna BHA' when pj.projectcd = 'azeh' then 'eHypertension' when pj.projectcd = 'azst' then 'eHypertension Study' when pj.projectcd = 'azek' then 'EverydayKidz' when pj.projectcd = 'bcma' then 'BCBS Massachusetts' when pj.projectcd = 'clar' then 'Clarian' when upper(s.sitetx) like 'TAKEDA%' then 'Takeda Diabetes' when s.sitecd = 'kpp1' then 'KP Private Label' when s.sitecd = 'azhh' then 'Heart Horizons' when s.sitecd = 'brem' then 'Bremer' when s.sitecd = 'scar' then 'Scarborough' when s.sitecd = 'fort' then 'Fortis' when s.sitecd = 'geis' then 'Geisinger' when s.sitecd = 'hmi4' then 'HMI BE Campaign' when s.sitecd = 'hum2' then 'Humana MHM' when s.sitecd = 'pinw' then 'Pinnacle West - Nourish' when s.sitecd = 'sjm2' then 'St. Josephs Mercy Hospital - Macomb' when s.sitecd = 'undf' then pj.projectx when s.sitecd = 'boin' then pj.projectx when s.sitetx like 'N/A%' and pj.projectx = 'N/A' then (case when pj.projectx = 'N/A' and d.distcd <> 'HMI' then d.disttx when pj.projectx = 'N/A' and pj.projectcd = 'vhba1' then 'Quickstart' else pj.projectx end) when s.sitetx like 'N/A%' and pj.projectx <> 'N/A' then pj.projectx when s.sitetx is null then pj.projectx else replace(s.sitetx, ',','') end site, case when s.sitecd = 'clev' and p.customq1 is null then 'CLEVna' when s.sitecd = 'clev' then 'CLEV' || p.customq1 else s.sitecd end sitecd, case when s.sitecd = 'clev' and p.customq2 is null then 'CLEVna' when s.sitecd = 'clev' then 'CLEV' || p.customq2 when p.pauthcd is null then 'N/A' else p.pauthcd end auth_code, case when p.plocation is null then 'N/A' when (P.PDISTCD='KPPL' or P.CUSTOMERCD='KPPLS') AND p.plocation IS NOT NULL THEN P.CUSTOMQ4||LTRIM(RTRIM(p.plocation)) else (p.plocation) end location --,case when pj.projectcd in ('hibc', 'hibs', 'himk', 'hinm', 'hipt', 'hmem') then 1 -- else 0 -- end ESR_HBA_BEHAVIOR_10, --case when pj.projectcd in ('hibc', 'hibs', 'himk', 'hinm', 'hipt', 'hmem') then 1 -- else 0 -- end ESR_HBA_BEHAVIOR_7 --,case when pj.projectcd in ('hibc', 'hibs', 'himk', 'hinm', 'hipt', 'hmem') and p.customq1 is not null then --upper(replace(p.customq1, ',','')) -- when pj.projectcd in ('hibc', 'hibs', 'himk', 'hinm', 'hipt', 'hmem') and p.customq1 is null then 'N/A' -- else 'N/A' -- end highmark_location2 from participant p, datawarehouse.mv_site s, datawarehouse.mv_project pj, datawarehouse.mv_customer c, datawarehouse.mv_distributor d, blna_analysis e where p.sitecd = s.sitecd and s.projectcd = pj.projectcd and pj.customercd = c.customercd and c.distcd = d.distcd and e.participantid=p.participantid and p.testdata is null and upper(d.distcd) not like '%DEMO%' and upper(c.customercd) not like '%DEMO%' and upper(c.customertx) not like '%DEMO%' and upper(pj.projectx) not like '%DEMO%' and upper(pj.projectx) not like '%TRIAL%' and upper(pj.projectx) not like '%PILOT%' and pj.projectcd <> 'bln' RATIONALE: The SQL statement with SQL_ID "gsqux5mqn0nxm" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID gsqux5mqn0nxm select 'HMI'||chr(124)|| 'BCMI'||chr(124)|| 'HMI_BCMI_BALANCE_EVAL30_R_'||TO_CHAR(SYSDATE - 1, 'MMDDYYYY') ||'_v1.0.1.txt'||chr(124)|| 'Balance_Eval_30'||chr(124)|| 'v1.0.1'||chr(124)|| 'EN'||chr(124)|| to_char(sysdate, 'yyyymmdd')||chr(124)|| (select count(*) from datawarehouse.mv_episodes where (participantid, episodeid) in (select distinct n1.participantid, n1.episodeid FROM DATA_ANALYSIS.BLNA_EVAL1 N1,DATA_ANALYSIS.BLNA_ANALYSIS NA,DATAWAREHOUSE.MV_PARTICIPANT P WHERE NA.PARTICIPANTID=N1.PARTICIPANTID AND NA.EPISODEID=N1.EPISODEID AND P.PARTICIPANTID=NA.PARTICIPANTID AND P.TESTDATA IS NULL and to_number(na.instrumentversion) >= 3 AND P.PDISTCD='BCMI' AND UPPER(P.POPTIN)='FORCED' AND TRUNC(n1.E1ALL_STATDATE) in (SELECT EXTRACTDATE FROM DATA_ANALYSIS.EXTRACTLOADDATE))) from dual RATIONALE: The SQL statement with SQL_ID "bpjfb329vq2ns" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID bpjfb329vq2ns SELECT DISTINCT N1.PARTICIPANTID||chr(124)|| N1.EPISODEID||chr(124)|| RELAXEVALOVERALLE1||chr(124)|| RELAXEVALAGREE1E1||chr(124)|| RELAXEVALAGREE2E1||chr(124)|| RELAXEVALAGREE3E1||chr(124)|| RELAXEVALAGREE4E1||chr(124)|| RELAXEVALAGREE5E1||chr(124)|| RELAXEVALAGREE7E1||chr(124)|| RELAXEVALAGREE6E1||chr(124)|| RELAXEVALMANAGESTRESSE1||chr(124)|| RELAXEVALSTRESSSYMPTOMSE1||chr(124)|| RELAXEVALMOTIVATIONE1||chr(124)|| RELAXEVALCONFIDENCEE1||chr(124)|| PRRELEEMPTYPEE1||chr(124)|| PRRELEHEALTHMISSWORKE1||chr(124)|| PRRELEGENMISSWORKE1||chr(124)|| PRRELEDIDWORKE1||chr(124)|| PRRELEHEALTHAFFECTWORKE1||chr(124)|| PRRELEHEALTHAFFECTACTE1||chr(124)|| PRRELESICKDAYSE1||chr(124)|| E1ALL||chr(124)|| to_char(E1ALL_STATDATE, 'MM/DD/YYYY hh:mi:ss PM')||chr(124)|| P.UMEMBERID||chr(124)|| P.PLOCATION FROM DATA_ANALYSIS.RLXA_EVAL1 N1,DATA_ANALYSIS.RLXA_ANALYSIS1 NA,DATAWAREHOUSE.MV_PARTICIPANT P WHERE NA.PARTICIPANTID=N1.PARTICIPANTID AND NA.EPISODEID=N1.EPISODEID AND P.PARTICIPANTID=NA.PARTICIPANTID AND P.TESTDATA IS NULL AND P.PDISTCD='BCMI' AND UPPER(P.POPTIN)='FORCED' AND TRUNC(N1.E1ALL_STATDATE) in (SELECT EXTRACTDATE FROM DATA_ANALYSIS.EXTRACTLOADDATE) RECOMMENDATION 2: Segment Tuning, 8.6% benefit (20445 seconds) ACTION: Run "Segment Advisor" on TABLE "DATA_ANALYSIS.HBA_ANALYSIS1" with object id 1423115. RELEVANT OBJECT: database object with id 1423115 ACTION: Investigate application logic involving I/O on TABLE "DATA_ANALYSIS.HBA_ANALYSIS1" with object id 1423115. RELEVANT OBJECT: database object with id 1423115 RATIONALE: The I/O usage statistics for the object are: 16 full object scans, 7489430 physical reads, 349 physical writes and 0 direct reads. RATIONALE: The SQL statement with SQL_ID "78y6kuv670pss" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID 78y6kuv670pss CREATE TABLE DS_IR_ENROLLMENTS AS select DISTTX,CUSTOMERTX,PROJECTX,INSTRUMENTX,COUNT(DISTINCT PARTICIPANTID) NUMENROLL from DATA_ANALYSIS.V_DS_TAILORED_INSIGHTS WHERE ENROLLDATE IS NOT NULL and ENROLLDATE>EMAILSENTDATE GROUP BY DISTTX,CUSTOMERTX,PROJECTX,INSTRUMENTX RATIONALE: The SQL statement with SQL_ID "9sx27bdchr6xx" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID 9sx27bdchr6xx select distinct s1.UMEMBERID|| chr(9) || NULL|| chr(9) || NULL|| chr(9) || NULL||chr(9)|| s1.PARTICIPANTID|| chr(9) || s1.EPISODEID|| chr(9) || NULL|| chr(9) || NULL|| chr(9) || NULL|| chr(9) || NULL|| chr(9) || NULL|| chr(9) || NULL|| chr(9) || NULL|| chr(9) || NULL|| chr(9) || NULL|| chr(9) || NULL|| chr(9) || s1.CONTACTPERMISSCD|| chr(9) || s1.CONTACTPREFCD|| chr(9) || s1.STATUSCD|| chr(9) || s1.GENDERCD|| chr(9) || s1.ETHNICITYCD|| chr(9) || s1.MARITALSTATUS|| chr(9) || s1.EDUCATIONCD|| chr(9) || s1.AGE|| chr(9) || s1.ENROLLMETHODCD|| chr(9) || TO_CHAR(s1.REGISTDATE, 'MM/DD/YYYY hh:mi:ss PM')||chr(9)|| TO_CHAR(s1.ENROLLDATE, 'MM/DD/YYYY hh:mi:ss PM')||chr(9)|| TO_CHAR(s1.PUBLICATIONDT, 'MM/DD/YYYY hh:mi:ss PM')||chr(9)|| s1.DISTCD|| chr(9) || s1.DISTTX|| chr(9) || s1.CUSTOMERCD|| chr(9) || s1.CUSTOMERTX|| chr(9) || s1.PROJECTCD|| chr(9) || s1.PROJECTX|| chr(9) || s1.SITECD|| chr(9) || s1.SITETX|| chr(9) || s1.PAUTHCD|| chr(9) || s1.CUSTCD|| chr(9) || NULL|| chr(9) || s1.LMPTYPE|| chr(9) || CASE WHEN p.POPTIN = 'GYes' THEN 'Yes' WHEN p.POPTIN = 'GNo' THEN 'No' ELSE p.POPTIN END || chr(9) || s1.INSTRUMENTVERSION|| chr(9) || WTHEIGHTFEET|| chr(9) || WTHEIGHTINCHES|| chr(9) || WTWEIGHT|| chr(9) || REPLACE(to_char(BMI, 'FM990.000'),'-1.000', NULL)|| chr(9) || HSCRBP12MONTHS|| chr(9) || CASE WHEN HSCRBPDONTKNOW = 'A' THEN 'true' WHEN HSCRBPDONTKNOW = 'B' THEN 'false' ELSE HSCRBPDONTKNOW END || chr(9) || REPLACE(HSCRBLOODGLUCOSE, '-1', NULL)|| chr(9) || CASE WHEN HSCRBLOODGLUCOSEDONTKNOW = 'A' THEN 'true' WHEN HSCRBLOODGLUCOSEDONTKNOW = 'B' THEN 'false' ELSE HSCRBLOODGLUCOSEDONTKNOW END || chr(9) || HSCRBLOODGLUCOSEFASTING || chr(9) || HSCRCHICKENPOX|| chr(9) || REPLACE(HSCRCHOLESTEROL, '-1', NULL)|| chr(9) || HSCRCHOLESTEROL5YRS || chr(9) || CASE WHEN HSCRCHOLESTEROLDONTKNOW = 'A' THEN 'true' WHEN HSCRCHOLESTEROLDONTKNOW = 'B' THEN 'false' ELSE HSCRCHOLESTEROLDONTKNOW END || chr(9) || HSCRCLINICALBREASTEXAM || chr(9) || HSCRCLINICALSKINEXAM || chr(9) || REPLACE(HSCRDBP, '-1', NULL) || chr(9) || HSCRFOBT || chr(9) || REPLACE(HSCRHDL, '-1', NULL) || chr(9) || CASE WHEN HSCRHDLDONTKNOW = 'A' THEN 'true' WHEN HSCRHDLDONTKNOW = 'B' THEN 'false' ELSE HSCRHDLDONTKNOW END || chr(9) || HSCRHEPATITISB|| chr(9) || HSCRINFLUENZA || chr(9) || HSCRMMR || chr(9) || HSCRMAMMOGRAM || chr(9) || HSCRPSA || chr(9) || HSCRPAP || chr(9) || HSCRPNEUMONIA || chr(9) || REPLACE(HSCRSBP, '-1', NULL) || chr(9) || HSCRSIGMOID || chr(9) || HSCRTETANUS || chr(9) || PERSDISEASEHISTANGINA || chr(9) || PERSDISEASEHISTASTHMA || chr(9) || PERSDISEASEHISTBREAST || chr(9) || PERSDISEASEHISTCHD || chr(9) || PERSDISEASEHISTCHF || chr(9) || PERSDISEASEHISTCERVICAL || chr(9) || PERSDISEASEHISTCHRONBRON || chr(9) || PERSDISEASEHISTCOLORECTAL || chr(9) || PERSDISEASEHISTDIABETES || chr(9) || PERSDISEASEHISTEMPHYSEMA || chr(9) || PERSDISEASEHISTHA || chr(9) || PERSDISEASEHISTHBP || chr(9) || PERSDISEASEHISTHEPATITISB || chr(9) || PERSDISEASEHISTHIBLOODSUGAR || chr(9) || PERSDISEASEHISTHICHOL || chr(9) || PERSDISEASEHISTOHD || chr(9) || PERSDISEASEHISTOBESITY || chr(9) || PERSDISEASEHISTOSTEO || chr(9) || PERSDISEASEHISTPVD || chr(9) || PERSDISEASEHISTPROSTATE || chr(9) || PERSDISEASEHISTSTD || chr(9) || PERSDISEASEHISTSKIN || chr(9) || PERSDISEASEHISTSTROKE || chr(9) || PERSDISEASEHISTTIA || chr(9) || FAMDISEASEHISTOBREAST || chr(9) || FAMDISEASEHISTOCOLORECTAL || chr(9) || FAMDISEASEHISTODIABETES || chr(9) || FAMDISEASEHISTODONTKNOW || chr(9) || FAMDISEASEHISTOHAFEMALE || chr(9) || FAMDISEASEHISTOHAMALE || chr(9) || FAMDISEASEHISTOHBP || chr(9) || FAMDISEASEHISTOHICHOL || chr(9) || FAMDISEASEHISTOOBESITY || chr(9) || FAMDISEASEHISTOOSTEO || chr(9) || FAMDISEASEHISTOPROSTATE || chr(9) || FAMDISEASEHISTOSTROKE || chr(9) || GENHEALTHOVERALL || chr(9) || GENHEALTHQOFLIFEOVERALL || chr(9) || REPLACE(WTMOTIVATIONLIST, '-1', NULL) || chr(9) || WTSELLIST_WTSEANXIOUSANGRY || chr(9) || WTSELLIST_WTSEDONTEXERCISE || chr(9) || WTSELLIST_WTSEEATOUT || chr(9) || WTSELLIST_WTSEHOLIDAYVACATION || chr(9) || WTSELLIST_WTSELONELYBORED || chr(9) || WTSELLIST_WTSETIREDSTRESSED || chr(9) || WTSTAGE || chr(9) || WTWAIST|| chr(9) || NUTFOODSDONTEATBEANSPEAS || chr(9) || NUTFOODSDONTEATBEEF || chr(9) || NUTFOODSDONTEATDAIRY || chr(9) || NUTFOODSDONTEATEGGS || chr(9) || NUTFOODSDONTEATFISH || chr(9) || NUTFOODSDONTEATFRUITS || chr(9) || NUTFOODSDONTEATGRAINS || chr(9) || NUTFOODSDONTEATLAMB || chr(9) || NUTFOODSDONTEATNUTS || chr(9) || NUTFOODSDONTEATPORKHAM || chr(9) || NUTFOODSDONTEATPOULTRY || chr(9) || NUTFOODSDONTEATSEEDS || chr(9) || NUTFOODSDONTEATSHELLFISH || chr(9) || NUTFOODSDONTEATVEGETABLES || chr(9) || NUTADDEDFATS || chr(9) || NUTHIFATFOODS || chr(9) || REPLACE(NUTMOTIVATIONLIST, '-1', NULL) || chr(9) || NUTSELLIST_NUTSEAROUNDTEMPTING || chr(9) || NUTSELLIST_NUTSEEATOUTFREQUENT || chr(9) || NUTSELLIST_NUTSENEEDREWARDFOOD || chr(9) || NUTSELLIST_NUTSENOTIMESHOPGROC || chr(9) || NUTSELLIST_NUTSEOTHERSEATUNHEA || chr(9) || NUTSELLIST_NUTSETOOTIREDTOPREP || chr(9) || NUTSERVINGSDAIRY || chr(9) || NUTSERVINGSFLUID || chr(9) || NUTSERVINGSFRUITS || chr(9) || NUTSERVINGSGRAINS || chr(9) || NUTSERVINGSMEAT || chr(9) || NUTSERVINGSVEGETABLES || chr(9) || NUTSODIUM || chr(9) || NUTSTAGE || chr(9) || NUTSUGAR || chr(9) || PHYDOCTORNOENGAGE || chr(9) || PHYHIACTIVITIESHOURS || chr(9) || PHYHIACTIVITIESMINUTES || chr(9) || PHYLOACTIVITIESHOURS || chr(9) || PHYLOACTIVITIESMINUTES || chr(9) || PHYMODERATEACTIVITIESHOURS || chr(9) || PHYMODERATEACTIVITIESMINUTES || chr(9) || REPLACE(PHYMOTIVATIONLIST, '-1', NULL) || chr(9) || PHYREADINESS || chr(9) || PHYSELLIST_PHYSENOACTIVITYENJO || chr(9) || PHYSELLIST_PHYSENOEXERCISEREGU || chr(9) || PHYSELLIST_PHYSENOPARTNER || chr(9) || PHYSELLIST_PHYSESCHEDULE || chr(9) || PHYSELLIST_PHYSETIRED || chr(9) || PHYSELLIST_PHYSEWEATHERPOOR || chr(9) || PHYSTAGE || chr(9) || PHYSTRENGTH || chr(9) || PHYSTRETCH || chr(9) || PHYVERYHIACTIVITIESHOURS || chr(9) || PHYVERYHIACTIVITIESMINUTES || chr(9) || SMKTOBACCOTYPECHEWINGTOBACCO || chr(9) || SMKTOBACCOTYPECIGARETTES || chr(9) || SMKTOBACCOTYPECIGARS || chr(9) || SMKTOBACCOTYPEPIPE || chr(9) || SMKTOBACCOTYPESNUFF || chr(9) || SMKCIGSPERDAY || chr(9) || SMKFIRSTCIGDAY|| chr(9) || SMKINTERESTREDUCING || chr(9) || SMKLONGESTTIMEQUIT || chr(9) || REPLACE(SMKMOTIVATIONLIST, '-1', NULL) || chr(9) || SMKSELLIST_SMKSEANGRY || chr(9) || SMKSELLIST_SMKSEBORED || chr(9) || SMKSELLIST_SMKSECRAVING || chr(9) || SMKSELLIST_SMKSENEEDLIFT || chr(9) || SMKSELLIST_SMKSESTRESSED || chr(9) || SMKSELLIST_SMKSEWEIGHT || chr(9) || SMKSTAGE || chr(9) || MENDEPSELLIST_MENDEPSEBENEFITT || chr(9) || MENDEPSELLIST_MENDEPSECARINGPR || chr(9) || MENDEPSELLIST_MENDEPSEFOLLOWTR || chr(9) || MENDEPSELLIST_MENDEPSEGETHELP || chr(9) || MENDEPSELLIST_MENDEPSEHAVESUPP || chr(9) || MENDEPSELLIST_MENDEPSEKEPTCONF || chr(9) || MENSTRESSANGERED || chr(9) || MENSTRESSCONFIDENT || chr(9) || MENSTRESSCRYING || chr(9) || MENSTRESSDEPRESSED || chr(9) || MENSTRESSDIFFICULTIES || chr(9) || MENSTRESSDISLIKED || chr(9) || MENSTRESSHAPPY|| chr(9) || MENSTRESSHOPEFUL || chr(9) || REPLACE(MENSTRESSMOTIVATIONLIST, '-1', NULL) || chr(9) || MENSTRESSONTOP|| chr(9) || MENSTRESSSELLIST_MENSTRESSSEFI || chr(9) || MENSTRESSSELLIST_MENSTRESSSELE || chr(9) || MENSTRESSSELLIST_MENSTRESSSEPR || chr(9) || MENSTRESSSELLIST_MENSTRESSSERE || chr(9) || MENSTRESSSELLIST_MENSTRESSSESE || chr(9) || MENSTRESSSELLIST_MENSTRESSSETI || chr(9) || MENSTRESSSTAGE|| chr(9) || MENSTRESSSTRESSED || chr(9) || MENSTRESSTREATMENT || chr(9) || REPLACE(ALCDRINKSDAY, '-1', NULL) || chr(9) || ALCFIVEORMORE || chr(9) || ALCFREQUENCY || chr(9) || REPLACE(ALCMOTIVATIONLIST, '-1', NULL)|| chr(9) || ALCSELLIST_ALCSEALONE || chr(9) || ALCSELLIST_ALCSEANGRY || chr(9) || ALCSELLIST_ALCSEBARPARTY || chr(9) || ALCSELLIST_ALCSEDEPRESSED || chr(9) || ALCSELLIST_ALCSESOCIAL || chr(9) || ALCSELLIST_ALCSEVACATION || chr(9) || ALCSTAGE || chr(9) || WOMHRT || chr(9) || WOMMENSTRUAL || chr(9) || WOMMENSTRUALYRSAGO || chr(9) || WOMPREGNANCY || chr(9) || SKNCLOTHING || chr(9) || SKNHOWSENSITIVE || chr(9) || REPLACE(SKNMOTIVATIONLIST, '-1', NULL) || chr(9) || SKNNOPROTECTION || chr(9) || replace(SKNREPEATEDEXPOSURE, 'DonKnow', 'DontKnow') || chr(9) || SKNSELLIST_SKNSE10TO2 || chr(9) || SKNSELLIST_SKNSEDOCTORCHECK || chr(9) || SKNSELLIST_SKNSEPROTECTSKN || chr(9) || SKNSELLIST_SKNSESEEKSHADE || chr(9) || SKNSELLIST_SKNSESELFCHECK || chr(9) || SKNSELLIST_SKNSEUSESUNSCREEN || chr(9) || SKNSHADE || chr(9) || SKNSUNSCREEN || chr(9) || INJDRIVEDRUNK || chr(9) || INJGATINGQUESTION || chr(9) || INJGUNSHOME || chr(9) || INJGUNSLOCKED|| chr(9) || REPLACE(INJMOTIVATIONLIST, '-1', NULL) || chr(9) || INJRIDEDRUNKDRIVER || chr(9) || INJSELLIST_INJSEBELTEVERYTIME || chr(9) || INJSELLIST_INJSEDRINKAVOIDDRIV || chr(9) || INJSELLIST_INJSEDRINKAVOIDRIDE || chr(9) || INJSELLIST_INJSEDRIVEWITHINLIM || chr(9) || INJSELLIST_INJSEEDUCATEGUNSAFE || chr(9) || INJSELLIST_INJSESTOREGUNSUNLOA || chr(9) || INJSEATBELT || chr(9) || INJSPEEDLIMIT || chr(9) || SEXAFFAIR || chr(9) || SEXAFFAIRCONDOM || chr(9) || SEXGATINGQUESTION || chr(9) || REPLACE(SEXMOTIVATIONLIST, '-1', NULL)|| chr(9) || SEXNUMPARTNERS|| chr(9) || SEXRELCONDOM || chr(9) || SEXRELATIONSHIP || chr(9) || SEXSELLIST_SEXCONDOMPURCHASE || chr(9) || SEXSELLIST_SEXCONDOMSEAROUSED || chr(9) || SEXSELLIST_SEXCONDOMSEDISCUSSP || chr(9) || SEXSELLIST_SEXCONDOMSENORISK || chr(9) || SEXSELLIST_SEXCONDOMSEOTHERBCO || chr(9) || SEXSELLIST_SEXCONDOMSESUBSTANC || chr(9) || SEXSTAGE || chr(9) || SEXTYPEPARTNERS || chr(9) || REPLACE(to_char(LIFESTYLESCORE,'FM990.000'), '-1.000',NULL) || chr(9) || BEHAVIOR1NAME || chr(9) || BEHAVIOR2NAME || chr(9) || BEHAVIOR3NAME || chr(9) || BEHAVIOR4NAME || chr(9) || BEHAVIOR5NAME || chr(9) || BEHAVIOR6NAME || chr(9) || BEHAVIOR7NAME || chr(9) || BEHAVIOR8NAME || chr(9) || BEHAVIOR9NAME || chr(9) || REPLACE(to_char(ALCBEHSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(DEPBEHSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(INJBEHSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(MENBEHSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(NUTBEHSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(PHYBEHSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(SEXBEHSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(SKNBEHSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(SMKBEHSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(STRBEHSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(WTBEHSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(ALCMOTOSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(DEPMOTOSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(INJMOTOSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(MENMOTOSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(NUTMOTOSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(PHYMOTOSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(SEXMOTOSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(SKNMOTOSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(SMKMOTOSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(STRMOTOSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(WTMOTOSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(ALCSESCOREVALUE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(DEPSESCOREVALUE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(INJSESCOREVALUE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(MENSESCOREVALUE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(NUTSESCOREVALUE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(PHYSESCOREVALUE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(SEXSESCOREVALUE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(SKNSESCOREVALUE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(SMKSESCOREVALUE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(STRSESCOREVALUE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(WTSESCOREVALUE,'FM990.000'), '-1.000',NULL) || chr(9) || p.CUSTOMQ1|| chr(9) || p.CUSTOMQ2|| chr(9) || p.CUSTOMQ3|| chr(9) || p.CUSTOMQ4|| chr(9) || p.CUSTOMQ5|| chr(9) || s1.CUSTOMQ6|| chr(9) || s1.CUSTOMQ7|| chr(9) || s1.CUSTOMQ8|| chr(9) || s1.CUSTOMQ9|| chr(9) || s1.CUSTOMQ10|| chr(9) || s1.CUSTOMQ11|| chr(9) || s1.CUSTOMQ12|| chr(9) || s1.CUSTOMQ13|| chr(9) || s1.CUSTOMQ14|| chr(9) || s1.CUSTOMQ15|| chr(9) || p.CUSTOMQ31|| chr(9) || p.CUSTOMQ32|| chr(9) || p.CUSTOMQ33|| chr(9) || p.CUSTOMQ34|| chr(9) || p.CUSTOMQ35|| chr(9) || s6.WomHysterectomy|| chr(9) || DEMOHOUSEHOLDADULTS|| chr(9) || DEMOHOUSEHOLDALONE|| chr(9) || DEMOHOUSEHOLDCHILDREN|| chr(9) || DEMOHOUSEHOLDINFANTS|| chr(9) || GENDAYSWORKMISSEDV2 || chr(9) || GENMAINJOBV2 || chr(9) || replace(MENSTRESSHOURSSLEEPV2,'EigthHours','EightHours')|| chr(9) || PHYSACTHIGHDAYSPERWKV2 || chr(9) || PHYSACTHIGHMINPERDAYV2 || chr(9) || PHYSACTLOWDAYSPERWKV2 || chr(9) || PHYSACTLOWMINPERDAYV2 || chr(9) || PHYSACTMODERATEDAYSPERWKV2 || chr(9) || PHYSACTMODERATEMINPERDAYV2 || chr(9) || PHYSACTVHIGHDAYSPERWKV2 || chr(9) || PHYSACTVHIGHMINPERDAYV2 || chr(9) || HMISSHEALTHV3 || chr(9) || HMISSOTHERV3 || chr(9) || HWORKEDV3 || chr(9) || IMPACTOTHERV3 || chr(9) || IMPACTWORKV3|| chr(9) || null|| chr(9) || PRGENHEALTHMISSWORK|| chr(9) || PRGENMISSWORK|| chr(9) || PRGENHEALTHAFFECTWORK|| chr(9) || PRGENHEALTHAFFECTACT|| chr(9) || PRGENDIDWORK|| chr(9) || HSCRLDL || chr(9) || CASE WHEN HSCRLDLDONTKNOW = 'A' THEN 'true' WHEN HSCRLDLDONTKNOW = 'B' THEN 'false' ELSE HSCRLDLDONTKNOW END || chr(9) || HSCRTRIGLYCERIDEV5|| chr(9) || CASE WHEN HSCRTRIGLYCERIDEDONTKNOWV5='A' then 'true' WHEN HSCRTRIGLYCERIDEDONTKNOWV5='B' then 'false' ELSE HSCRTRIGLYCERIDEDONTKNOWV5 end|| chr(9) || PERSDISEASEHISTALLERGIES || chr(9) || PERSDISEASEHISTLUNG || chr(9) || replace(PERSDISEASEHISTBACKPAIN, 'LowBackPain', 'BackPain')|| chr(9) || PERSDISEASEHISTCOPD || chr(9) || CASE WHEN PERSDISEASEHISTDEPRESSION = 'Dep' THEN 'Depression' WHEN PERSDISEASEHISTDEPRESSION = 'Depressionression' THEN 'Depression' ELSE PERSDISEASEHISTDEPRESSION END||chr(9)|| PERSDISEASEHISTNONE || chr(9) || FAMDISEASEHISTOBLOODCLOT || chr(9) || FAMDISEASEHISTONONE || chr(9) || s1.LANGUAGECD|| chr(9) || p.XID|| chr(9) || p.XGROUP|| chr(9) || s1.SELFREPORTED|| chr(9) || s1.RETAILORED|| chr(9) || s1.RETAILOREDDT|| chr(9) || s1.XMEMBER|| chr(9) || HBAGENHEALTHCOMPARE|| chr(9) || HBAHSCRHPV|| chr(9) || HBAHSCRCOLON|| chr(9) || HBADRUGUSEPAST_TAKENMORE|| chr(9) || HBADRUGUSEPAST_USEDRECDRUGS|| chr(9) || HBADRUGUSEPAST_NA|| chr(9) || HBADRUGUSECURRENT_TAKENMORE|| chr(9) || HBADRUGUSECURRENT_USEDRECDRUGS|| chr(9) || HBADRUGUSECURRENT_NA|| chr(9) || HBADRUGUSERESULT_LOSTMARRIAGE|| chr(9) || HBADRUGUSERESULT_LOSTRELS|| chr(9) || HBADRUGUSERESULT_LOSTCUSTODY|| chr(9) || HBADRUGUSERESULT_LOSTJOB|| chr(9) || HBADRUGUSERESULT_LAWTROUBLE|| chr(9) || HBADRUGUSERESULT_FINDIFF|| chr(9) || HBADRUGUSERESULT_POORHEALTH|| chr(9) || HBADRUGUSERESULT_OTHER|| chr(9) || HBADRUGUSERESULT_NONE|| chr(9) || HBADRUGSTAGE|| chr(9) || HBADRUGMOTIVATIONLIST|| chr(9) || HBADRUGSETLIST|| chr(9) || HBADRUGUSERECPROF|| chr(9) || HBADRUGUSERXPROF|| chr(9) || HBALCINTERESTRISK|| chr(9) || HBALCSMKDYEARSQUIT|| chr(9) || HBALCSMKDCIGSPERDAY|| chr(9) || HBALCSMKDYEARSSMOKED|| chr(9) || HBALCSMKCIGSPERDAY|| chr(9) || HBALCSMKYEARSSMOKE|| chr(9) || HBALCASBESTOS|| chr(9) || HBABCINTERESTRISK|| chr(9) || HBABCGATING|| chr(9) || HBABCFIRSTPERIOD|| chr(9) || HBABCAGEFIRSTCHILD|| chr(9) || HBABCRELBREASTCANCER|| chr(9) || HBABCBIOPSY|| chr(9) || HBABCNUMBIOPSY|| chr(9) || HBABCBIOPSYAH|| chr(9) || REPLACE(to_char(DRUGBEHSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(DRUGSESCOREVALUE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(DRUGMOTOSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(LCPROBQUIT,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(LCPROBSMOKE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(CHDRISK,'FM990.000'), '-1', NULL) || chr(9) || REPLACE(to_char(CHDRISKPERC,'FM990.000'), '-1', NULL) || chr(9) || REPLACE(to_char(BCINDABSFIVEYRRISK,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(BCINDAVGFIVEYRRISK,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(BCINDABSLIFETIMERISK,'FM990.000'), '-1.000',NULL)|| chr(9) || REPLACE(to_char(BCINDAVGLIFETIMERISK,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(BCINDABSFIVEYRNORISK,'FM990.000'), '-1.000',NULL) || chr(9) || BEHAVIOR10NAME|| chr(9) || HBALCGATING||chr(9)|| CUSTOMQ16||chr(9)|| CUSTOMQ17||chr(9)|| CUSTOMQ18||chr(9)|| CUSTOMQ19||chr(9)|| CUSTOMQ20||chr(9)|| CUSTOMQ21||chr(9)|| CUSTOMQ22||chr(9)|| CUSTOMQ23||chr(9)|| CUSTOMQ24||chr(9)|| CUSTOMQ25||chr(9)|| CUSTOMQ26||chr(9)|| CUSTOMQ27||chr(9)|| CUSTOMQ28||chr(9)|| CUSTOMQ29||chr(9)|| CUSTOMQ30 from datawarehouse.mv_participant p, data_analysis.hba_analysis1 s1, data_analysis.hba_analysis2 s2, data_analysis.hba_analysis3 s3, data_analysis.hba_analysis4 s4,data_analysis.HBA_ANALYSIS_V2 V2, data_analysis.HBA_ANALYSIS_V3 V3, data_analysis.HBA_ANALYSIS6 s6,data_analysis.HBA_ANALYSIS_PR pr, data_analysis.HBA_ANALYSIS7 s7, data_analysis.HBA_ANALYSIS8 s8, data_analysis.hba_healthrisk hr, data_analysis.hba_healthrisk_values hl where s1.participantid = s2.participantid(+) and s1.episodeid = s2.episodeid(+) and s1.participantid = s3.participantid(+) and s1.episodeid = s3.episodeid(+) and s1.participantid = s4.participantid(+) and s1.episodeid = s4.episodeid(+) and s1.participantid = v2.participantid(+) and s1.episodeid = v2.episodeid(+) and s1.participantid = v3.participantid(+) and s1.episodeid = v3.episodeid(+) and s1.participantid = s6.participantid(+) and s1.episodeid = s6.episodeid(+) and s1.participantid = s8.participantid(+) and s1.episodeid = s8.episodeid(+) and s1.participantid = s7.participantid(+) and s1.episodeid = s7.episodeid(+) and s1.participantid = pr.participantid(+) and s1.episodeid = pr.episodeid(+) and p.participantid = s1.participantid and s1.participantid = hr.participantid(+) and s1.episodeid = hr.episodeid(+) and s1.participantid = hl.participantid(+) and s1.episodeid = hl.episodeid(+) and s1.instrumentcd='hba' --and s1.instrumentversion in ('03','04','06') --AND P.PDISTCD='ADHCS' and p.pdistcd in ('ADHC', 'ADHCS') and p.pprojectcd not in ('adep','adcm') AND P.POPTIN in ('Forced','Yes') and trunc(s1.publicationdt) in (SELECT EXTRACTDATE FROM DATA_ANALYSIS.EXTRACTLOADDATE) and s1.testdata is null union select distinct s1.UMEMBERID|| chr(9) || s1.LASTNAME|| chr(9) || s1.FIRSTNAME|| chr(9) || TO_CHAR(s1.BIRTHDATE, 'MM/DD/YYYY')||chr(9)|| s1.PARTICIPANTID|| chr(9) || s1.EPISODEID|| chr(9) || s1.DAYTIMEAREACD|| chr(9) || s1.DAYTIMEPHONE|| chr(9) || s1.ADDRESS1 || chr(9) || s1.ADDRESS2 || chr(9) || s1.city || chr(9) || s1.state|| chr(9) || s1.zip || chr(9) || p.EMAILADDRESS|| chr(9) || p.EMAILNEW|| chr(9) || s1.SSN|| chr(9) || s1.CONTACTPERMISSCD|| chr(9) || s1.CONTACTPREFCD|| chr(9) || s1.STATUSCD|| chr(9) || s1.GENDERCD|| chr(9) || s1.ETHNICITYCD|| chr(9) || s1.MARITALSTATUS|| chr(9) || s1.EDUCATIONCD|| chr(9) || s1.AGE|| chr(9) || s1.ENROLLMETHODCD|| chr(9) || TO_CHAR(s1.REGISTDATE, 'MM/DD/YYYY hh:mi:ss PM')||chr(9)|| TO_CHAR(s1.ENROLLDATE, 'MM/DD/YYYY hh:mi:ss PM')||chr(9)|| TO_CHAR(s1.PUBLICATIONDT, 'MM/DD/YYYY hh:mi:ss PM')||chr(9)|| s1.DISTCD|| chr(9) || s1.DISTTX|| chr(9) || s1.CUSTOMERCD|| chr(9) || s1.CUSTOMERTX|| chr(9) || s1.PROJECTCD|| chr(9) || s1.PROJECTX|| chr(9) || s1.SITECD|| chr(9) || s1.SITETX|| chr(9) || s1.PAUTHCD|| chr(9) || s1.CUSTCD|| chr(9) || s1.PLOCATION|| chr(9) || s1.LMPTYPE|| chr(9) || CASE WHEN p.POPTIN = 'GYes' THEN 'Yes' WHEN p.POPTIN = 'GNo' THEN 'No' ELSE p.POPTIN END || chr(9) || s1.INSTRUMENTVERSION|| chr(9) || WTHEIGHTFEET|| chr(9) || WTHEIGHTINCHES|| chr(9) || WTWEIGHT|| chr(9) || REPLACE(to_char(BMI, 'FM990.000'),'-1.000', NULL)|| chr(9) || HSCRBP12MONTHS|| chr(9) || CASE WHEN HSCRBPDONTKNOW = 'A' THEN 'true' WHEN HSCRBPDONTKNOW = 'B' THEN 'false' ELSE HSCRBPDONTKNOW END || chr(9) || REPLACE(HSCRBLOODGLUCOSE, '-1', NULL)|| chr(9) || CASE WHEN HSCRBLOODGLUCOSEDONTKNOW = 'A' THEN 'true' WHEN HSCRBLOODGLUCOSEDONTKNOW = 'B' THEN 'false' ELSE HSCRBLOODGLUCOSEDONTKNOW END || chr(9) || HSCRBLOODGLUCOSEFASTING || chr(9) || HSCRCHICKENPOX|| chr(9) || REPLACE(HSCRCHOLESTEROL, '-1', NULL)|| chr(9) || HSCRCHOLESTEROL5YRS || chr(9) || CASE WHEN HSCRCHOLESTEROLDONTKNOW = 'A' THEN 'true' WHEN HSCRCHOLESTEROLDONTKNOW = 'B' THEN 'false' ELSE HSCRCHOLESTEROLDONTKNOW END || chr(9) || HSCRCLINICALBREASTEXAM || chr(9) || HSCRCLINICALSKINEXAM || chr(9) || REPLACE(HSCRDBP, '-1', NULL) || chr(9) || HSCRFOBT || chr(9) || REPLACE(HSCRHDL, '-1', NULL) || chr(9) || CASE WHEN HSCRHDLDONTKNOW = 'A' THEN 'true' WHEN HSCRHDLDONTKNOW = 'B' THEN 'false' ELSE HSCRHDLDONTKNOW END || chr(9) || HSCRHEPATITISB|| chr(9) || HSCRINFLUENZA || chr(9) || HSCRMMR || chr(9) || HSCRMAMMOGRAM || chr(9) || HSCRPSA || chr(9) || HSCRPAP || chr(9) || HSCRPNEUMONIA || chr(9) || REPLACE(HSCRSBP, '-1', NULL) || chr(9) || HSCRSIGMOID || chr(9) || HSCRTETANUS || chr(9) || PERSDISEASEHISTANGINA || chr(9) || PERSDISEASEHISTASTHMA || chr(9) || PERSDISEASEHISTBREAST || chr(9) || PERSDISEASEHISTCHD || chr(9) || PERSDISEASEHISTCHF || chr(9) || PERSDISEASEHISTCERVICAL || chr(9) || PERSDISEASEHISTCHRONBRON || chr(9) || PERSDISEASEHISTCOLORECTAL || chr(9) || PERSDISEASEHISTDIABETES || chr(9) || PERSDISEASEHISTEMPHYSEMA || chr(9) || PERSDISEASEHISTHA || chr(9) || PERSDISEASEHISTHBP || chr(9) || PERSDISEASEHISTHEPATITISB || chr(9) || PERSDISEASEHISTHIBLOODSUGAR || chr(9) || PERSDISEASEHISTHICHOL || chr(9) || PERSDISEASEHISTOHD || chr(9) || PERSDISEASEHISTOBESITY || chr(9) || PERSDISEASEHISTOSTEO || chr(9) || PERSDISEASEHISTPVD || chr(9) || PERSDISEASEHISTPROSTATE || chr(9) || PERSDISEASEHISTSTD || chr(9) || PERSDISEASEHISTSKIN || chr(9) || PERSDISEASEHISTSTROKE || chr(9) || PERSDISEASEHISTTIA || chr(9) || FAMDISEASEHISTOBREAST || chr(9) || FAMDISEASEHISTOCOLORECTAL || chr(9) || FAMDISEASEHISTODIABETES || chr(9) || FAMDISEASEHISTODONTKNOW || chr(9) || FAMDISEASEHISTOHAFEMALE || chr(9) || FAMDISEASEHISTOHAMALE || chr(9) || FAMDISEASEHISTOHBP || chr(9) || FAMDISEASEHISTOHICHOL || chr(9) || FAMDISEASEHISTOOBESITY || chr(9) || FAMDISEASEHISTOOSTEO || chr(9) || FAMDISEASEHISTOPROSTATE || chr(9) || FAMDISEASEHISTOSTROKE || chr(9) || GENHEALTHOVERALL || chr(9) || GENHEALTHQOFLIFEOVERALL || chr(9) || REPLACE(WTMOTIVATIONLIST, '-1', NULL) || chr(9) || WTSELLIST_WTSEANXIOUSANGRY || chr(9) || WTSELLIST_WTSEDONTEXERCISE || chr(9) || WTSELLIST_WTSEEATOUT || chr(9) || WTSELLIST_WTSEHOLIDAYVACATION || chr(9) || WTSELLIST_WTSELONELYBORED || chr(9) || WTSELLIST_WTSETIREDSTRESSED || chr(9) || WTSTAGE || chr(9) || WTWAIST|| chr(9) || NUTFOODSDONTEATBEANSPEAS || chr(9) || NUTFOODSDONTEATBEEF || chr(9) || NUTFOODSDONTEATDAIRY || chr(9) || NUTFOODSDONTEATEGGS || chr(9) || NUTFOODSDONTEATFISH || chr(9) || NUTFOODSDONTEATFRUITS || chr(9) || NUTFOODSDONTEATGRAINS || chr(9) || NUTFOODSDONTEATLAMB || chr(9) || NUTFOODSDONTEATNUTS || chr(9) || NUTFOODSDONTEATPORKHAM || chr(9) || NUTFOODSDONTEATPOULTRY || chr(9) || NUTFOODSDONTEATSEEDS || chr(9) || NUTFOODSDONTEATSHELLFISH || chr(9) || NUTFOODSDONTEATVEGETABLES || chr(9) || NUTADDEDFATS || chr(9) || NUTHIFATFOODS || chr(9) || REPLACE(NUTMOTIVATIONLIST, '-1', NULL) || chr(9) || NUTSELLIST_NUTSEAROUNDTEMPTING || chr(9) || NUTSELLIST_NUTSEEATOUTFREQUENT || chr(9) || NUTSELLIST_NUTSENEEDREWARDFOOD || chr(9) || NUTSELLIST_NUTSENOTIMESHOPGROC || chr(9) || NUTSELLIST_NUTSEOTHERSEATUNHEA || chr(9) || NUTSELLIST_NUTSETOOTIREDTOPREP || chr(9) || NUTSERVINGSDAIRY || chr(9) || NUTSERVINGSFLUID || chr(9) || NUTSERVINGSFRUITS || chr(9) || NUTSERVINGSGRAINS || chr(9) || NUTSERVINGSMEAT || chr(9) || NUTSERVINGSVEGETABLES || chr(9) || NUTSODIUM || chr(9) || NUTSTAGE || chr(9) || NUTSUGAR || chr(9) || PHYDOCTORNOENGAGE || chr(9) || PHYHIACTIVITIESHOURS || chr(9) || PHYHIACTIVITIESMINUTES || chr(9) || PHYLOACTIVITIESHOURS || chr(9) || PHYLOACTIVITIESMINUTES || chr(9) || PHYMODERATEACTIVITIESHOURS || chr(9) || PHYMODERATEACTIVITIESMINUTES || chr(9) || REPLACE(PHYMOTIVATIONLIST, '-1', NULL) || chr(9) || PHYREADINESS || chr(9) || PHYSELLIST_PHYSENOACTIVITYENJO || chr(9) || PHYSELLIST_PHYSENOEXERCISEREGU || chr(9) || PHYSELLIST_PHYSENOPARTNER || chr(9) || PHYSELLIST_PHYSESCHEDULE || chr(9) || PHYSELLIST_PHYSETIRED || chr(9) || PHYSELLIST_PHYSEWEATHERPOOR || chr(9) || PHYSTAGE || chr(9) || PHYSTRENGTH || chr(9) || PHYSTRETCH || chr(9) || PHYVERYHIACTIVITIESHOURS || chr(9) || PHYVERYHIACTIVITIESMINUTES || chr(9) || SMKTOBACCOTYPECHEWINGTOBACCO || chr(9) || SMKTOBACCOTYPECIGARETTES || chr(9) || SMKTOBACCOTYPECIGARS || chr(9) || SMKTOBACCOTYPEPIPE || chr(9) || SMKTOBACCOTYPESNUFF || chr(9) || SMKCIGSPERDAY || chr(9) || SMKFIRSTCIGDAY|| chr(9) || SMKINTERESTREDUCING || chr(9) || SMKLONGESTTIMEQUIT || chr(9) || REPLACE(SMKMOTIVATIONLIST, '-1', NULL) || chr(9) || SMKSELLIST_SMKSEANGRY || chr(9) || SMKSELLIST_SMKSEBORED || chr(9) || SMKSELLIST_SMKSECRAVING || chr(9) || SMKSELLIST_SMKSENEEDLIFT || chr(9) || SMKSELLIST_SMKSESTRESSED || chr(9) || SMKSELLIST_SMKSEWEIGHT || chr(9) || SMKSTAGE || chr(9) || MENDEPSELLIST_MENDEPSEBENEFITT || chr(9) || MENDEPSELLIST_MENDEPSECARINGPR || chr(9) || MENDEPSELLIST_MENDEPSEFOLLOWTR || chr(9) || MENDEPSELLIST_MENDEPSEGETHELP || chr(9) || MENDEPSELLIST_MENDEPSEHAVESUPP || chr(9) || MENDEPSELLIST_MENDEPSEKEPTCONF || chr(9) || MENSTRESSANGERED || chr(9) || MENSTRESSCONFIDENT || chr(9) || MENSTRESSCRYING || chr(9) || MENSTRESSDEPRESSED || chr(9) || MENSTRESSDIFFICULTIES || chr(9) || MENSTRESSDISLIKED || chr(9) || MENSTRESSHAPPY|| chr(9) || MENSTRESSHOPEFUL || chr(9) || REPLACE(MENSTRESSMOTIVATIONLIST, '-1', NULL) || chr(9) || MENSTRESSONTOP|| chr(9) || MENSTRESSSELLIST_MENSTRESSSEFI || chr(9) || MENSTRESSSELLIST_MENSTRESSSELE || chr(9) || MENSTRESSSELLIST_MENSTRESSSEPR || chr(9) || MENSTRESSSELLIST_MENSTRESSSERE || chr(9) || MENSTRESSSELLIST_MENSTRESSSESE || chr(9) || MENSTRESSSELLIST_MENSTRESSSETI || chr(9) || MENSTRESSSTAGE|| chr(9) || MENSTRESSSTRESSED || chr(9) || MENSTRESSTREATMENT || chr(9) || REPLACE(ALCDRINKSDAY, '-1', NULL) || chr(9) || ALCFIVEORMORE || chr(9) || ALCFREQUENCY || chr(9) || REPLACE(ALCMOTIVATIONLIST, '-1', NULL)|| chr(9) || ALCSELLIST_ALCSEALONE || chr(9) || ALCSELLIST_ALCSEANGRY || chr(9) || ALCSELLIST_ALCSEBARPARTY || chr(9) || ALCSELLIST_ALCSEDEPRESSED || chr(9) || ALCSELLIST_ALCSESOCIAL || chr(9) || ALCSELLIST_ALCSEVACATION || chr(9) || ALCSTAGE || chr(9) || WOMHRT || chr(9) || WOMMENSTRUAL || chr(9) || WOMMENSTRUALYRSAGO || chr(9) || WOMPREGNANCY || chr(9) || SKNCLOTHING || chr(9) || SKNHOWSENSITIVE || chr(9) || REPLACE(SKNMOTIVATIONLIST, '-1', NULL) || chr(9) || SKNNOPROTECTION || chr(9) || replace(SKNREPEATEDEXPOSURE, 'DonKnow', 'DontKnow') || chr(9) || SKNSELLIST_SKNSE10TO2 || chr(9) || SKNSELLIST_SKNSEDOCTORCHECK || chr(9) || SKNSELLIST_SKNSEPROTECTSKN || chr(9) || SKNSELLIST_SKNSESEEKSHADE || chr(9) || SKNSELLIST_SKNSESELFCHECK || chr(9) || SKNSELLIST_SKNSEUSESUNSCREEN || chr(9) || SKNSHADE || chr(9) || SKNSUNSCREEN || chr(9) || INJDRIVEDRUNK || chr(9) || INJGATINGQUESTION || chr(9) || INJGUNSHOME || chr(9) || INJGUNSLOCKED|| chr(9) || REPLACE(INJMOTIVATIONLIST, '-1', NULL) || chr(9) || INJRIDEDRUNKDRIVER || chr(9) || INJSELLIST_INJSEBELTEVERYTIME || chr(9) || INJSELLIST_INJSEDRINKAVOIDDRIV || chr(9) || INJSELLIST_INJSEDRINKAVOIDRIDE || chr(9) || INJSELLIST_INJSEDRIVEWITHINLIM || chr(9) || INJSELLIST_INJSEEDUCATEGUNSAFE || chr(9) || INJSELLIST_INJSESTOREGUNSUNLOA || chr(9) || INJSEATBELT || chr(9) || INJSPEEDLIMIT || chr(9) || SEXAFFAIR || chr(9) || SEXAFFAIRCONDOM || chr(9) || SEXGATINGQUESTION || chr(9) || REPLACE(SEXMOTIVATIONLIST, '-1', NULL)|| chr(9) || SEXNUMPARTNERS|| chr(9) || SEXRELCONDOM || chr(9) || SEXRELATIONSHIP || chr(9) || SEXSELLIST_SEXCONDOMPURCHASE || chr(9) || SEXSELLIST_SEXCONDOMSEAROUSED || chr(9) || SEXSELLIST_SEXCONDOMSEDISCUSSP || chr(9) || SEXSELLIST_SEXCONDOMSENORISK || chr(9) || SEXSELLIST_SEXCONDOMSEOTHERBCO || chr(9) || SEXSELLIST_SEXCONDOMSESUBSTANC || chr(9) || SEXSTAGE || chr(9) || SEXTYPEPARTNERS || chr(9) || REPLACE(to_char(LIFESTYLESCORE,'FM990.000'), '-1.000',NULL) || chr(9) || BEHAVIOR1NAME || chr(9) || BEHAVIOR2NAME || chr(9) || BEHAVIOR3NAME || chr(9) || BEHAVIOR4NAME || chr(9) || BEHAVIOR5NAME || chr(9) || BEHAVIOR6NAME || chr(9) || BEHAVIOR7NAME || chr(9) || BEHAVIOR8NAME || chr(9) || BEHAVIOR9NAME || chr(9) || REPLACE(to_char(ALCBEHSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(DEPBEHSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(INJBEHSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(MENBEHSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(NUTBEHSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(PHYBEHSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(SEXBEHSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(SKNBEHSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(SMKBEHSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(STRBEHSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(WTBEHSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(ALCMOTOSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(DEPMOTOSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(INJMOTOSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(MENMOTOSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(NUTMOTOSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(PHYMOTOSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(SEXMOTOSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(SKNMOTOSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(SMKMOTOSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(STRMOTOSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(WTMOTOSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(ALCSESCOREVALUE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(DEPSESCOREVALUE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(INJSESCOREVALUE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(MENSESCOREVALUE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(NUTSESCOREVALUE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(PHYSESCOREVALUE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(SEXSESCOREVALUE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(SKNSESCOREVALUE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(SMKSESCOREVALUE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(STRSESCOREVALUE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(WTSESCOREVALUE,'FM990.000'), '-1.000',NULL) || chr(9) || p.CUSTOMQ1|| chr(9) || p.CUSTOMQ2|| chr(9) || p.CUSTOMQ3|| chr(9) || p.CUSTOMQ4|| chr(9) || p.CUSTOMQ5|| chr(9) || s1.CUSTOMQ6|| chr(9) || s1.CUSTOMQ7|| chr(9) || s1.CUSTOMQ8|| chr(9) || s1.CUSTOMQ9|| chr(9) || s1.CUSTOMQ10|| chr(9) || s1.CUSTOMQ11|| chr(9) || s1.CUSTOMQ12|| chr(9) || s1.CUSTOMQ13|| chr(9) || s1.CUSTOMQ14|| chr(9) || s1.CUSTOMQ15|| chr(9) || p.CUSTOMQ31|| chr(9) || p.CUSTOMQ32|| chr(9) || p.CUSTOMQ33|| chr(9) || p.CUSTOMQ34|| chr(9) || p.CUSTOMQ35|| chr(9) || s6.WomHysterectomy|| chr(9) || DEMOHOUSEHOLDADULTS|| chr(9) || DEMOHOUSEHOLDALONE|| chr(9) || DEMOHOUSEHOLDCHILDREN|| chr(9) || DEMOHOUSEHOLDINFANTS|| chr(9) || GENDAYSWORKMISSEDV2 || chr(9) || GENMAINJOBV2 || chr(9) || replace(MENSTRESSHOURSSLEEPV2,'EigthHours','EightHours')|| chr(9) || PHYSACTHIGHDAYSPERWKV2 || chr(9) || PHYSACTHIGHMINPERDAYV2 || chr(9) || PHYSACTLOWDAYSPERWKV2 || chr(9) || PHYSACTLOWMINPERDAYV2 || chr(9) || PHYSACTMODERATEDAYSPERWKV2 || chr(9) || PHYSACTMODERATEMINPERDAYV2 || chr(9) || PHYSACTVHIGHDAYSPERWKV2 || chr(9) || PHYSACTVHIGHMINPERDAYV2 || chr(9) || HMISSHEALTHV3 || chr(9) || HMISSOTHERV3 || chr(9) || HWORKEDV3 || chr(9) || IMPACTOTHERV3 || chr(9) || IMPACTWORKV3|| chr(9) || null|| chr(9) || PRGENHEALTHMISSWORK|| chr(9) || PRGENMISSWORK|| chr(9) || PRGENHEALTHAFFECTWORK|| chr(9) || PRGENHEALTHAFFECTACT|| chr(9) || PRGENDIDWORK|| chr(9) || HSCRLDL || chr(9) || CASE WHEN HSCRLDLDONTKNOW = 'A' THEN 'true' WHEN HSCRLDLDONTKNOW = 'B' THEN 'false' ELSE HSCRLDLDONTKNOW END || chr(9) || HSCRTRIGLYCERIDEV5|| chr(9) || CASE WHEN HSCRTRIGLYCERIDEDONTKNOWV5='A' then 'true' WHEN HSCRTRIGLYCERIDEDONTKNOWV5='B' then 'false' ELSE HSCRTRIGLYCERIDEDONTKNOWV5 end|| chr(9) || PERSDISEASEHISTALLERGIES || chr(9) || PERSDISEASEHISTLUNG || chr(9) || replace(PERSDISEASEHISTBACKPAIN, 'LowBackPain', 'BackPain')|| chr(9) || PERSDISEASEHISTCOPD || chr(9) || CASE WHEN PERSDISEASEHISTDEPRESSION = 'Dep' THEN 'Depression' WHEN PERSDISEASEHISTDEPRESSION = 'Depressionression' THEN 'Depression' ELSE PERSDISEASEHISTDEPRESSION END||chr(9)|| PERSDISEASEHISTNONE || chr(9) || FAMDISEASEHISTOBLOODCLOT || chr(9) || FAMDISEASEHISTONONE || chr(9) || s1.LANGUAGECD|| chr(9) || p.XID|| chr(9) || p.XGROUP|| chr(9) || s1.SELFREPORTED|| chr(9) || s1.RETAILORED|| chr(9) || s1.RETAILOREDDT|| chr(9) || s1.XMEMBER|| chr(9) || HBAGENHEALTHCOMPARE|| chr(9) || HBAHSCRHPV|| chr(9) || HBAHSCRCOLON|| chr(9) || HBADRUGUSEPAST_TAKENMORE|| chr(9) || HBADRUGUSEPAST_USEDRECDRUGS|| chr(9) || HBADRUGUSEPAST_NA|| chr(9) || HBADRUGUSECURRENT_TAKENMORE|| chr(9) || HBADRUGUSECURRENT_USEDRECDRUGS|| chr(9) || HBADRUGUSECURRENT_NA|| chr(9) || HBADRUGUSERESULT_LOSTMARRIAGE|| chr(9) || HBADRUGUSERESULT_LOSTRELS|| chr(9) || HBADRUGUSERESULT_LOSTCUSTODY|| chr(9) || HBADRUGUSERESULT_LOSTJOB|| chr(9) || HBADRUGUSERESULT_LAWTROUBLE|| chr(9) || HBADRUGUSERESULT_FINDIFF|| chr(9) || HBADRUGUSERESULT_POORHEALTH|| chr(9) || HBADRUGUSERESULT_OTHER|| chr(9) || HBADRUGUSERESULT_NONE|| chr(9) || HBADRUGSTAGE|| chr(9) || HBADRUGMOTIVATIONLIST|| chr(9) || HBADRUGSETLIST|| chr(9) || HBADRUGUSERECPROF|| chr(9) || HBADRUGUSERXPROF|| chr(9) || HBALCINTERESTRISK|| chr(9) || HBALCSMKDYEARSQUIT|| chr(9) || HBALCSMKDCIGSPERDAY|| chr(9) || HBALCSMKDYEARSSMOKED|| chr(9) || HBALCSMKCIGSPERDAY|| chr(9) || HBALCSMKYEARSSMOKE|| chr(9) || HBALCASBESTOS|| chr(9) || HBABCINTERESTRISK|| chr(9) || HBABCGATING|| chr(9) || HBABCFIRSTPERIOD|| chr(9) || HBABCAGEFIRSTCHILD|| chr(9) || HBABCRELBREASTCANCER|| chr(9) || HBABCBIOPSY|| chr(9) || HBABCNUMBIOPSY|| chr(9) || HBABCBIOPSYAH|| chr(9) || REPLACE(to_char(DRUGBEHSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(DRUGSESCOREVALUE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(DRUGMOTOSCORE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(LCPROBQUIT,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(LCPROBSMOKE,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(CHDRISK,'FM990.000'), '-1', NULL) || chr(9) || REPLACE(to_char(CHDRISKPERC,'FM990.000'), '-1', NULL) || chr(9) || REPLACE(to_char(BCINDABSFIVEYRRISK,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(BCINDAVGFIVEYRRISK,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(BCINDABSLIFETIMERISK,'FM990.000'), '-1.000',NULL)|| chr(9) || REPLACE(to_char(BCINDAVGLIFETIMERISK,'FM990.000'), '-1.000',NULL) || chr(9) || REPLACE(to_char(BCINDABSFIVEYRNORISK,'FM990.000'), '-1.000',NULL) || chr(9) || BEHAVIOR10NAME|| chr(9) || HBALCGATING||chr(9)|| CUSTOMQ16||chr(9)|| CUSTOMQ17||chr(9)|| CUSTOMQ18||chr(9)|| CUSTOMQ19||chr(9)|| CUSTOMQ20||chr(9)|| CUSTOMQ21||chr(9)|| CUSTOMQ22||chr(9)|| CUSTOMQ23||chr(9)|| CUSTOMQ24||chr(9)|| CUSTOMQ25||chr(9)|| CUSTOMQ26||chr(9)|| CUSTOMQ27||chr(9)|| CUSTOMQ28||chr(9)|| CUSTOMQ29||chr(9)|| CUSTOMQ30 from datawarehouse.mv_participant p, data_analysis.hba_analysis1 s1, data_analysis.hba_analysis2 s2, data_analysis.hba_analysis3 s3, data_analysis.hba_analysis4 s4,data_analysis.HBA_ANALYSIS_V2 V2, data_analysis.HBA_ANALYSIS_V3 V3, data_analysis.HBA_ANALYSIS6 s6,data_analysis.HBA_ANALYSIS_PR pr, data_analysis.HBA_ANALYSIS7 s7, data_analysis.HBA_ANALYSIS8 s8, data_analysis.hba_healthrisk hr, data_analysis.hba_healthrisk_values hl where s1.participantid = s2.participantid(+) and s1.episodeid = s2.episodeid(+) and s1.participantid = s3.participantid(+) and s1.episodeid = s3.episodeid(+) and s1.participantid = s4.participantid(+) and s1.episodeid = s4.episodeid(+) and s1.participantid = v2.participantid(+) and s1.episodeid = v2.episodeid(+) and s1.participantid = v3.participantid(+) and s1.episodeid = v3.episodeid(+) and s1.participantid = s6.participantid(+) and s1.episodeid = s6.episodeid(+) and s1.participantid = s8.participantid(+) and s1.episodeid = s8.episodeid(+) and s1.participantid = s7.participantid(+) and s1.episodeid = s7.episodeid(+) and s1.participantid = pr.participantid(+) and s1.episodeid = pr.episodeid(+) and p.participantid = s1.participantid and s1.participantid = hr.participantid(+) and s1.episodeid = hr.episodeid(+) and s1.participantid = hl.participantid(+) and s1.episodeid = hl.episodeid(+) and s1.instrumentcd='hba' --and s1.instrumentversion in ('03','04','06') --AND P.PDISTCD='ADHCS' and p.pdistcd in ('ADHC', 'ADHCS') and p.pprojectcd in ('adep','adcm') AND P.POPTIN in ('Forced','Yes') and trunc(s1.publicationdt) in (SELECT EXTRACTDATE FROM DATA_ANALYSIS.EXTRACTLOADDATE) and s1.testdata is null RATIONALE: The SQL statement with SQL_ID "326rbhu24z3a0" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID 326rbhu24z3a0 create table data_analysis.hba_participantids tablespace users as select participantid, episodeid, publicationdt FROM data_analysis.hba_analysis1@hlt1 minus select participantid, episodeid, publicationdt from data_analysis.hba_analysis1 RATIONALE: The SQL statement with SQL_ID "23mupud7nz065" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID 23mupud7nz065 update DATA_ANALYSIS.HBA_ANALYSIS1 SET customq6='No' where Customq6='no' and (DISTCD='KPPL' OR CUSTOMERCD='KPPLS') RATIONALE: The SQL statement with SQL_ID "66y93qp59axkh" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID 66y93qp59axkh select distinct s1.UMEMBERID|| chr(124) || s1.LASTNAME|| chr(124) || s1.FIRSTNAME|| chr(124) || TO_CHAR(s1.BIRTHDATE, 'MM/DD/YYYY')||chr(124)|| s1.PARTICIPANTID|| chr(124) || s1.EPISODEID|| chr(124) || s1.DAYTIMEAREACD|| chr(124) || s1.DAYTIMEPHONE|| chr(124) || s1.ADDRESS1 || chr(124) || s1.ADDRESS2 || chr(124) || s1.city || chr(124) || s1.state|| chr(124) || s1.zip || chr(124) || p.EMAILADDRESS|| chr(124) || p.EMAILNEW|| chr(124) || s1.SSN|| chr(124) || s1.CONTACTPERMISSCD|| chr(124) || s1.CONTACTPREFCD|| chr(124) || s1.STATUSCD|| chr(124) || s1.GENDERCD|| chr(124) || s1.ETHNICITYCD|| chr(124) || s1.MARITALSTATUS|| chr(124) || s1.EDUCATIONCD|| chr(124) || s1.AGE|| chr(124) || s1.ENROLLMETHODCD|| chr(124) || TO_CHAR(s1.REGISTDATE, 'MM/DD/YYYY hh:mi:ss PM')||chr(124)|| TO_CHAR(s1.ENROLLDATE, 'MM/DD/YYYY hh:mi:ss PM')||chr(124)|| TO_CHAR(s1.PUBLICATIONDT, 'MM/DD/YYYY hh:mi:ss PM')||chr(124)|| s1.DISTCD|| chr(124) || s1.DISTTX|| chr(124) || s1.CUSTOMERCD|| chr(124) || s1.CUSTOMERTX|| chr(124) || s1.PROJECTCD|| chr(124) || s1.PROJECTX|| chr(124) || s1.SITECD|| chr(124) || s1.SITETX|| chr(124) || s1.PAUTHCD|| chr(124) || s1.CUSTCD|| chr(124) || s1.PLOCATION|| chr(124) || s1.LMPTYPE|| chr(124) || CASE WHEN p.POPTIN = 'GYes' THEN 'Yes' WHEN p.POPTIN = 'GNo' THEN 'No' ELSE p.POPTIN END || chr(124) || s1.INSTRUMENTVERSION|| chr(124) || WTHEIGHTFEET|| chr(124) || WTHEIGHTINCHES|| chr(124) || WTWEIGHT|| chr(124) || REPLACE(to_char(BMI, 'FM990.000'),'-1.000', NULL)|| chr(124) || HSCRBP12MONTHS|| chr(124) || CASE WHEN HSCRBPDONTKNOW = 'A' THEN 'true' WHEN HSCRBPDONTKNOW = 'B' THEN 'false' ELSE HSCRBPDONTKNOW END || chr(124) || REPLACE(HSCRBLOODGLUCOSE, '-1', NULL)|| chr(124) || CASE WHEN HSCRBLOODGLUCOSEDONTKNOW = 'A' THEN 'true' WHEN HSCRBLOODGLUCOSEDONTKNOW = 'B' THEN 'false' ELSE HSCRBLOODGLUCOSEDONTKNOW END || chr(124) || HSCRBLOODGLUCOSEFASTING || chr(124) || HSCRCHICKENPOX|| chr(124) || REPLACE(HSCRCHOLESTEROL, '-1', NULL)|| chr(124) || HSCRCHOLESTEROL5YRS || chr(124) || CASE WHEN HSCRCHOLESTEROLDONTKNOW = 'A' THEN 'true' WHEN HSCRCHOLESTEROLDONTKNOW = 'B' THEN 'false' ELSE HSCRCHOLESTEROLDONTKNOW END || chr(124) || HSCRCLINICALBREASTEXAM || chr(124) || HSCRCLINICALSKINEXAM || chr(124) || REPLACE(HSCRDBP, '-1', NULL) || chr(124) || HSCRFOBT || chr(124) || REPLACE(HSCRHDL, '-1', NULL) || chr(124) || CASE WHEN HSCRHDLDONTKNOW = 'A' THEN 'true' WHEN HSCRHDLDONTKNOW = 'B' THEN 'false' ELSE HSCRHDLDONTKNOW END || chr(124) || HSCRHEPATITISB|| chr(124) || HSCRINFLUENZA || chr(124) || HSCRMMR || chr(124) || HSCRMAMMOGRAM || chr(124) || HSCRPSA || chr(124) || HSCRPAP || chr(124) || HSCRPNEUMONIA || chr(124) || REPLACE(HSCRSBP, '-1', NULL) || chr(124) || HSCRSIGMOID || chr(124) || HSCRTETANUS || chr(124) || PERSDISEASEHISTANGINA || chr(124) || PERSDISEASEHISTASTHMA || chr(124) || PERSDISEASEHISTBREAST || chr(124) || PERSDISEASEHISTCHD || chr(124) || PERSDISEASEHISTCHF || chr(124) || PERSDISEASEHISTCERVICAL || chr(124) || PERSDISEASEHISTCHRONBRON || chr(124) || PERSDISEASEHISTCOLORECTAL || chr(124) || PERSDISEASEHISTDIABETES || chr(124) || PERSDISEASEHISTEMPHYSEMA || chr(124) || PERSDISEASEHISTHA || chr(124) || PERSDISEASEHISTHBP || chr(124) || PERSDISEASEHISTHEPATITISB || chr(124) || PERSDISEASEHISTHIBLOODSUGAR || chr(124) || PERSDISEASEHISTHICHOL || chr(124) || PERSDISEASEHISTOHD || chr(124) || PERSDISEASEHISTOBESITY || chr(124) || PERSDISEASEHISTOSTEO || chr(124) || PERSDISEASEHISTPVD || chr(124) || PERSDISEASEHISTPROSTATE || chr(124) || PERSDISEASEHISTSTD || chr(124) || PERSDISEASEHISTSKIN || chr(124) || PERSDISEASEHISTSTROKE || chr(124) || PERSDISEASEHISTTIA || chr(124) || FAMDISEASEHISTOBREAST || chr(124) || FAMDISEASEHISTOCOLORECTAL || chr(124) || FAMDISEASEHISTODIABETES || chr(124) || FAMDISEASEHISTODONTKNOW || chr(124) || FAMDISEASEHISTOHAFEMALE || chr(124) || FAMDISEASEHISTOHAMALE || chr(124) || FAMDISEASEHISTOHBP || chr(124) || FAMDISEASEHISTOHICHOL || chr(124) || FAMDISEASEHISTOOBESITY || chr(124) || FAMDISEASEHISTOOSTEO || chr(124) || FAMDISEASEHISTOPROSTATE || chr(124) || FAMDISEASEHISTOSTROKE || chr(124) || GENHEALTHOVERALL || chr(124) || GENHEALTHQOFLIFEOVERALL || chr(124) || REPLACE(WTMOTIVATIONLIST, '-1', NULL) || chr(124) || WTSELLIST_WTSEANXIOUSANGRY || chr(124) || WTSELLIST_WTSEDONTEXERCISE || chr(124) || WTSELLIST_WTSEEATOUT || chr(124) || WTSELLIST_WTSEHOLIDAYVACATION || chr(124) || WTSELLIST_WTSELONELYBORED || chr(124) || WTSELLIST_WTSETIREDSTRESSED || chr(124) || WTSTAGE || chr(124) || WTWAIST|| chr(124) || NUTFOODSDONTEATBEANSPEAS || chr(124) || NUTFOODSDONTEATBEEF || chr(124) || NUTFOODSDONTEATDAIRY || chr(124) || NUTFOODSDONTEATEGGS || chr(124) || NUTFOODSDONTEATFISH || chr(124) || NUTFOODSDONTEATFRUITS || chr(124) || NUTFOODSDONTEATGRAINS || chr(124) || NUTFOODSDONTEATLAMB || chr(124) || NUTFOODSDONTEATNUTS || chr(124) || NUTFOODSDONTEATPORKHAM || chr(124) || NUTFOODSDONTEATPOULTRY || chr(124) || NUTFOODSDONTEATSEEDS || chr(124) || NUTFOODSDONTEATSHELLFISH || chr(124) || NUTFOODSDONTEATVEGETABLES || chr(124) || NUTADDEDFATS || chr(124) || NUTHIFATFOODS || chr(124) || REPLACE(NUTMOTIVATIONLIST, '-1', NULL) || chr(124) || NUTSELLIST_NUTSEAROUNDTEMPTING || chr(124) || NUTSELLIST_NUTSEEATOUTFREQUENT || chr(124) || NUTSELLIST_NUTSENEEDREWARDFOOD || chr(124) || NUTSELLIST_NUTSENOTIMESHOPGROC || chr(124) || NUTSELLIST_NUTSEOTHERSEATUNHEA || chr(124) || NUTSELLIST_NUTSETOOTIREDTOPREP || chr(124) || NUTSERVINGSDAIRY || chr(124) || NUTSERVINGSFLUID || chr(124) || NUTSERVINGSFRUITS || chr(124) || NUTSERVINGSGRAINS || chr(124) || NUTSERVINGSMEAT || chr(124) || NUTSERVINGSVEGETABLES || chr(124) || NUTSODIUM || chr(124) || NUTSTAGE || chr(124) || NUTSUGAR || chr(124) || PHYDOCTORNOENGAGE || chr(124) || PHYHIACTIVITIESHOURS || chr(124) || PHYHIACTIVITIESMINUTES || chr(124) || PHYLOACTIVITIESHOURS || chr(124) || PHYLOACTIVITIESMINUTES || chr(124) || PHYMODERATEACTIVITIESHOURS || chr(124) || PHYMODERATEACTIVITIESMINUTES || chr(124) || REPLACE(PHYMOTIVATIONLIST, '-1', NULL) || chr(124) || PHYREADINESS || chr(124) || PHYSELLIST_PHYSENOACTIVITYENJO || chr(124) || PHYSELLIST_PHYSENOEXERCISEREGU || chr(124) || PHYSELLIST_PHYSENOPARTNER || chr(124) || PHYSELLIST_PHYSESCHEDULE || chr(124) || PHYSELLIST_PHYSETIRED || chr(124) || PHYSELLIST_PHYSEWEATHERPOOR || chr(124) || PHYSTAGE || chr(124) || PHYSTRENGTH || chr(124) || PHYSTRETCH || chr(124) || PHYVERYHIACTIVITIESHOURS || chr(124) || PHYVERYHIACTIVITIESMINUTES || chr(124) || SMKTOBACCOTYPECHEWINGTOBACCO || chr(124) || SMKTOBACCOTYPECIGARETTES || chr(124) || SMKTOBACCOTYPECIGARS || chr(124) || SMKTOBACCOTYPEPIPE || chr(124) || SMKTOBACCOTYPESNUFF || chr(124) || SMKCIGSPERDAY || chr(124) || SMKFIRSTCIGDAY|| chr(124) || SMKINTERESTREDUCING || chr(124) || SMKLONGESTTIMEQUIT || chr(124) || REPLACE(SMKMOTIVATIONLIST, '-1', NULL) || chr(124) || SMKSELLIST_SMKSEANGRY || chr(124) || SMKSELLIST_SMKSEBORED || chr(124) || SMKSELLIST_SMKSECRAVING || chr(124) || SMKSELLIST_SMKSENEEDLIFT || chr(124) || SMKSELLIST_SMKSESTRESSED || chr(124) || SMKSELLIST_SMKSEWEIGHT || chr(124) || SMKSTAGE || chr(124) || MENDEPSELLIST_MENDEPSEBENEFITT || chr(124) || MENDEPSELLIST_MENDEPSECARINGPR || chr(124) || MENDEPSELLIST_MENDEPSEFOLLOWTR || chr(124) || MENDEPSELLIST_MENDEPSEGETHELP || chr(124) || MENDEPSELLIST_MENDEPSEHAVESUPP || chr(124) || MENDEPSELLIST_MENDEPSEKEPTCONF || chr(124) || MENSTRESSANGERED || chr(124) || MENSTRESSCONFIDENT || chr(124) || MENSTRESSCRYING || chr(124) || MENSTRESSDEPRESSED || chr(124) || MENSTRESSDIFFICULTIES || chr(124) || MENSTRESSDISLIKED || chr(124) || MENSTRESSHAPPY|| chr(124) || MENSTRESSHOPEFUL || chr(124) || REPLACE(MENSTRESSMOTIVATIONLIST, '-1', NULL) || chr(124) || MENSTRESSONTOP|| chr(124) || MENSTRESSSELLIST_MENSTRESSSEFI || chr(124) || MENSTRESSSELLIST_MENSTRESSSELE || chr(124) || MENSTRESSSELLIST_MENSTRESSSEPR || chr(124) || MENSTRESSSELLIST_MENSTRESSSERE || chr(124) || MENSTRESSSELLIST_MENSTRESSSESE || chr(124) || MENSTRESSSELLIST_MENSTRESSSETI || chr(124) || MENSTRESSSTAGE|| chr(124) || MENSTRESSSTRESSED || chr(124) || MENSTRESSTREATMENT || chr(124) || REPLACE(ALCDRINKSDAY, '-1', NULL) || chr(124) || ALCFIVEORMORE || chr(124) || ALCFREQUENCY || chr(124) || REPLACE(ALCMOTIVATIONLIST, '-1', NULL)|| chr(124) || ALCSELLIST_ALCSEALONE || chr(124) || ALCSELLIST_ALCSEANGRY || chr(124) || ALCSELLIST_ALCSEBARPARTY || chr(124) || ALCSELLIST_ALCSEDEPRESSED || chr(124) || ALCSELLIST_ALCSESOCIAL || chr(124) || ALCSELLIST_ALCSEVACATION || chr(124) || ALCSTAGE || chr(124) || WOMHRT || chr(124) || WOMMENSTRUAL || chr(124) || WOMMENSTRUALYRSAGO || chr(124) || WOMPREGNANCY || chr(124) || SKNCLOTHING || chr(124) || SKNHOWSENSITIVE || chr(124) || REPLACE(SKNMOTIVATIONLIST, '-1', NULL) || chr(124) || SKNNOPROTECTION || chr(124) || replace(SKNREPEATEDEXPOSURE, 'DonKnow', 'DontKnow') || chr(124) || SKNSELLIST_SKNSE10TO2 || chr(124) || SKNSELLIST_SKNSEDOCTORCHECK || chr(124) || SKNSELLIST_SKNSEPROTECTSKN || chr(124) || SKNSELLIST_SKNSESEEKSHADE || chr(124) || SKNSELLIST_SKNSESELFCHECK || chr(124) || SKNSELLIST_SKNSEUSESUNSCREEN || chr(124) || SKNSHADE || chr(124) || SKNSUNSCREEN || chr(124) || INJDRIVEDRUNK || chr(124) || INJGATINGQUESTION || chr(124) || INJGUNSHOME || chr(124) || INJGUNSLOCKED|| chr(124) || REPLACE(INJMOTIVATIONLIST, '-1', NULL) || chr(124) || INJRIDEDRUNKDRIVER || chr(124) || INJSELLIST_INJSEBELTEVERYTIME || chr(124) || INJSELLIST_INJSEDRINKAVOIDDRIV || chr(124) || INJSELLIST_INJSEDRINKAVOIDRIDE || chr(124) || INJSELLIST_INJSEDRIVEWITHINLIM || chr(124) || INJSELLIST_INJSEEDUCATEGUNSAFE || chr(124) || INJSELLIST_INJSESTOREGUNSUNLOA || chr(124) || INJSEATBELT || chr(124) || INJSPEEDLIMIT || chr(124) || SEXAFFAIR || chr(124) || SEXAFFAIRCONDOM || chr(124) || SEXGATINGQUESTION || chr(124) || REPLACE(SEXMOTIVATIONLIST, '-1', NULL)|| chr(124) || SEXNUMPARTNERS|| chr(124) || SEXRELCONDOM || chr(124) || SEXRELATIONSHIP || chr(124) || SEXSELLIST_SEXCONDOMPURCHASE || chr(124) || SEXSELLIST_SEXCONDOMSEAROUSED || chr(124) || SEXSELLIST_SEXCONDOMSEDISCUSSP || chr(124) || SEXSELLIST_SEXCONDOMSENORISK || chr(124) || SEXSELLIST_SEXCONDOMSEOTHERBCO || chr(124) || SEXSELLIST_SEXCONDOMSESUBSTANC || chr(124) || SEXSTAGE || chr(124) || SEXTYPEPARTNERS || chr(124) || REPLACE(to_char(LIFESTYLESCORE,'FM990.000'), '-1.000',NULL) || chr(124) || BEHAVIOR1NAME || chr(124) || BEHAVIOR2NAME || chr(124) || BEHAVIOR3NAME || chr(124) || BEHAVIOR4NAME || chr(124) || BEHAVIOR5NAME || chr(124) || BEHAVIOR6NAME || chr(124) || BEHAVIOR7NAME || chr(124) || BEHAVIOR8NAME || chr(124) || BEHAVIOR9NAME || chr(124) || REPLACE(to_char(ALCBEHSCORE,'FM990.000'), '-1.000',NULL) || chr(124) || REPLACE(to_char(DEPBEHSCORE,'FM990.000'), '-1.000',NULL) || chr(124) || REPLACE(to_char(INJBEHSCORE,'FM990.000'), '-1.000',NULL) || chr(124) || REPLACE(to_char(MENBEHSCORE,'FM990.000'), '-1.000',NULL) || chr(124) || REPLACE(to_char(NUTBEHSCORE,'FM990.000'), '-1.000',NULL) || chr(124) || REPLACE(to_char(PHYBEHSCORE,'FM990.000'), '-1.000',NULL) || chr(124) || REPLACE(to_char(SEXBEHSCORE,'FM990.000'), '-1.000',NULL) || chr(124) || REPLACE(to_char(SKNBEHSCORE,'FM990.000'), '-1.000',NULL) || chr(124) || REPLACE(to_char(SMKBEHSCORE,'FM990.000'), '-1.000',NULL) || chr(124) || REPLACE(to_char(STRBEHSCORE,'FM990.000'), '-1.000',NULL) || chr(124) || REPLACE(to_char(WTBEHSCORE,'FM990.000'), '-1.000',NULL) || chr(124) || REPLACE(to_char(ALCMOTOSCORE,'FM990.000'), '-1.000',NULL) || chr(124) || REPLACE(to_char(DEPMOTOSCORE,'FM990.000'), '-1.000',NULL) || chr(124) || REPLACE(to_char(INJMOTOSCORE,'FM990.000'), '-1.000',NULL) || chr(124) || REPLACE(to_char(MENMOTOSCORE,'FM990.000'), '-1.000',NULL) || chr(124) || REPLACE(to_char(NUTMOTOSCORE,'FM990.000'), '-1.000',NULL) || chr(124) || REPLACE(to_char(PHYMOTOSCORE,'FM990.000'), '-1.000',NULL) || chr(124) || REPLACE(to_char(SEXMOTOSCORE,'FM990.000'), '-1.000',NULL) || chr(124) || REPLACE(to_char(SKNMOTOSCORE,'FM990.000'), '-1.000',NULL) || chr(124) || REPLACE(to_char(SMKMOTOSCORE,'FM990.000'), '-1.000',NULL) || chr(124) || REPLACE(to_char(STRMOTOSCORE,'FM990.000'), '-1.000',NULL) || chr(124) || REPLACE(to_char(WTMOTOSCORE,'FM990.000'), '-1.000',NULL) || chr(124) || REPLACE(to_char(ALCSESCOREVALUE,'FM990.000'), '-1.000',NULL) || chr(124) || REPLACE(to_char(DEPSESCOREVALUE,'FM990.000'), '-1.000',NULL) || chr(124) || REPLACE(to_char(INJSESCOREVALUE,'FM990.000'), '-1.000',NULL) || chr(124) || REPLACE(to_char(MENSESCOREVALUE,'FM990.000'), '-1.000',NULL) || chr(124) || REPLACE(to_char(NUTSESCOREVALUE,'FM990.000'), '-1.000',NULL) || chr(124) || REPLACE(to_char(PHYSESCOREVALUE,'FM990.000'), '-1.000',NULL) || chr(124) || REPLACE(to_char(SEXSESCOREVALUE,'FM990.000'), '-1.000',NULL) || chr(124) || REPLACE(to_char(SKNSESCOREVALUE,'FM990.000'), '-1.000',NULL) || chr(124) || REPLACE(to_char(SMKSESCOREVALUE,'FM990.000'), '-1.000',NULL) || chr(124) || REPLACE(to_char(STRSESCOREVALUE,'FM990.000'), '-1.000',NULL) || chr(124) || REPLACE(to_char(WTSESCOREVALUE,'FM990.000'), '-1.000',NULL) || chr(124) || p.CUSTOMQ1|| chr(124) || p.CUSTOMQ2|| chr(124) || p.CUSTOMQ3|| chr(124) || p.CUSTOMQ4|| chr(124) || p.CUSTOMQ5|| chr(124) || s1.CUSTOMQ6|| chr(124) || s1.CUSTOMQ7|| chr(124) || s1.CUSTOMQ8|| chr(124) || s1.CUSTOMQ9|| chr(124) || s1.CUSTOMQ10|| chr(124) || s1.CUSTOMQ11|| chr(124) || s1.CUSTOMQ12|| chr(124) || s1.CUSTOMQ13|| chr(124) || s1.CUSTOMQ14|| chr(124) || s1.CUSTOMQ15|| chr(124) || p.CUSTOMQ31|| chr(124) || p.CUSTOMQ32|| chr(124) || p.CUSTOMQ33|| chr(124) || p.CUSTOMQ34|| chr(124) || p.CUSTOMQ35|| chr(124) || s6.WomHysterectomy|| chr(124) || DEMOHOUSEHOLDADULTS|| chr(124) || DEMOHOUSEHOLDALONE|| chr(124) || DEMOHOUSEHOLDCHILDREN|| chr(124) || DEMOHOUSEHOLDINFANTS|| chr(124) || GENDAYSWORKMISSEDV2 || chr(124) || GENMAINJOBV2 || chr(124) || replace(MENSTRESSHOURSSLEEPV2,'EigthHours','EightHours')|| chr(124) || PHYSACTHIGHDAYSPERWKV2 || chr(124) || PHYSACTHIGHMINPERDAYV2 || chr(124) || PHYSACTLOWDAYSPERWKV2 || chr(124) || PHYSACTLOWMINPERDAYV2 || chr(124) || PHYSACTMODERATEDAYSPERWKV2 || chr(124) || PHYSACTMODERATEMINPERDAYV2 || chr(124) || PHYSACTVHIGHDAYSPERWKV2 || chr(124) || PHYSACTVHIGHMINPERDAYV2 || chr(124) || HMISSHEALTHV3 || chr(124) || HMISSOTHERV3 || chr(124) || HWORKEDV3 || chr(124) || IMPACTOTHERV3 || chr(124) || IMPACTWORKV3|| chr(124) || null|| chr(124) || PRGENHEALTHMISSWORK|| chr(124) || PRGENMISSWORK|| chr(124) || PRGENHEALTHAFFECTWORK|| chr(124) || PRGENHEALTHAFFECTACT|| chr(124) || PRGENDIDWORK|| chr(124) || HSCRLDL || chr(124) || CASE WHEN HSCRLDLDONTKNOW = 'A' THEN 'true' WHEN HSCRLDLDONTKNOW = 'B' THEN 'false' ELSE HSCRLDLDONTKNOW END || chr(124) || HSCRTRIGLYCERIDEV5|| chr(124) || CASE WHEN HSCRTRIGLYCERIDEDONTKNOWV5='A' then 'true' WHEN HSCRTRIGLYCERIDEDONTKNOWV5='B' then 'false' ELSE HSCRTRIGLYCERIDEDONTKNOWV5 end|| chr(124) || PERSDISEASEHISTALLERGIES || chr(124) || PERSDISEASEHISTLUNG || chr(124) || replace(PERSDISEASEHISTBACKPAIN, 'LowBackPain', 'BackPain')|| chr(124) || PERSDISEASEHISTCOPD || chr(124) || CASE WHEN PERSDISEASEHISTDEPRESSION = 'Dep' THEN 'Depression' WHEN PERSDISEASEHISTDEPRESSION = 'Depressionression' THEN 'Depression' ELSE PERSDISEASEHISTDEPRESSION END||chr(124)|| PERSDISEASEHISTNONE || chr(124) || FAMDISEASEHISTOBLOODCLOT || chr(124) || FAMDISEASEHISTONONE || chr(124) || s1.LANGUAGECD|| chr(124) || p.XID|| chr(124) || p.XGROUP|| chr(124) || s1.SELFREPORTED|| chr(124) || s1.RETAILORED|| chr(124) || s1.RETAILOREDDT|| chr(124) || s1.XMEMBER|| chr(124) || HBAGENHEALTHCOMPARE|| chr(124) || HBAHSCRHPV|| chr(124) || HBAHSCRCOLON|| chr(124) || HBADRUGUSEPAST_TAKENMORE|| chr(124) || HBADRUGUSEPAST_USEDRECDRUGS|| chr(124) || HBADRUGUSEPAST_NA|| chr(124) || HBADRUGUSECURRENT_TAKENMORE|| chr(124) || HBADRUGUSECURRENT_USEDRECDRUGS|| chr(124) || HBADRUGUSECURRENT_NA|| chr(124) || HBADRUGUSERESULT_LOSTMARRIAGE|| chr(124) || HBADRUGUSERESULT_LOSTRELS|| chr(124) || HBADRUGUSERESULT_LOSTCUSTODY|| chr(124) || HBADRUGUSERESULT_LOSTJOB|| chr(124) || HBADRUGUSERESULT_LAWTROUBLE|| chr(124) || HBADRUGUSERESULT_FINDIFF|| chr(124) || HBADRUGUSERESULT_POORHEALTH|| chr(124) || HBADRUGUSERESULT_OTHER|| chr(124) || HBADRUGUSERESULT_NONE|| chr(124) || HBADRUGSTAGE|| chr(124) || HBADRUGMOTIVATIONLIST|| chr(124) || HBADRUGSETLIST|| chr(124) || HBADRUGUSERECPROF|| chr(124) || HBADRUGUSERXPROF|| chr(124) || HBALCINTERESTRISK|| chr(124) || HBALCSMKDYEARSQUIT|| chr(124) || HBALCSMKDCIGSPERDAY|| chr(124) || HBALCSMKDYEARSSMOKED|| chr(124) || HBALCSMKCIGSPERDAY|| chr(124) || HBALCSMKYEARSSMOKE|| chr(124) || HBALCASBESTOS|| chr(124) || HBABCINTERESTRISK|| chr(124) || HBABCGATING|| chr(124) || HBABCFIRSTPERIOD|| chr(124) || HBABCAGEFIRSTCHILD|| chr(124) || HBABCRELBREASTCANCER|| chr(124) || HBABCBIOPSY|| chr(124) || HBABCNUMBIOPSY|| chr(124) || HBABCBIOPSYAH|| chr(124) || REPLACE(to_char(DRUGBEHSCORE,'FM990.000'), '-1.000',NULL) || chr(124) || REPLACE(to_char(DRUGSESCOREVALUE,'FM990.000'), '-1.000',NULL) || chr(124) || REPLACE(to_char(DRUGMOTOSCORE,'FM990.000'), '-1.000',NULL) || chr(124) || REPLACE(to_char(LCPROBQUIT,'FM990.000'), '-1.000',NULL) || chr(124) || REPLACE(to_char(LCPROBSMOKE,'FM990.000'), '-1.000',NULL) || chr(124) || REPLACE(to_char(CHDRISK,'FM990.000'), '-1', NULL) || chr(124) || REPLACE(to_char(CHDRISKPERC,'FM990.000'), '-1', NULL) || chr(124) || REPLACE(to_char(BCINDABSFIVEYRRISK,'FM990.000'), '-1.000',NULL) || chr(124) || REPLACE(to_char(BCINDAVGFIVEYRRISK,'FM990.000'), '-1.000',NULL) || chr(124) || REPLACE(to_char(BCINDABSLIFETIMERISK,'FM990.000'), '-1.000',NULL)|| chr(124) || REPLACE(to_char(BCINDAVGLIFETIMERISK,'FM990.000'), '-1.000',NULL) || chr(124) || REPLACE(to_char(BCINDABSFIVEYRNORISK,'FM990.000'), '-1.000',NULL) || chr(124) || BEHAVIOR10NAME|| chr(124) || HBALCGATING||chr(124)|| CUSTOMQ16||chr(124)|| CUSTOMQ17||chr(124)|| CUSTOMQ18||chr(124)|| CUSTOMQ19||chr(124)|| CUSTOMQ20||chr(124)|| CUSTOMQ21||chr(124)|| CUSTOMQ22||chr(124)|| CUSTOMQ23||chr(124)|| CUSTOMQ24||chr(124)|| CUSTOMQ25||chr(124)|| CUSTOMQ26||chr(124)|| CUSTOMQ27||chr(124)|| CUSTOMQ28||chr(124)|| CUSTOMQ29||chr(124)|| CUSTOMQ30 from datawarehouse.mv_participant p, data_analysis.hba_analysis1 s1, data_analysis.hba_analysis2 s2, data_analysis.hba_analysis3 s3, data_analysis.hba_analysis4 s4,data_analysis.HBA_ANALYSIS_V2 V2, data_analysis.HBA_ANALYSIS_V3 V3, data_analysis.HBA_ANALYSIS6 s6,data_analysis.HBA_ANALYSIS_PR pr, data_analysis.HBA_ANALYSIS7 s7, data_analysis.HBA_ANALYSIS8 s8, data_analysis.hba_healthrisk hr, data_analysis.hba_healthrisk_values hl where s1.participantid = s2.participantid(+) and s1.episodeid = s2.episodeid(+) and s1.participantid = s3.participantid(+) and s1.episodeid = s3.episodeid(+) and s1.participantid = s4.participantid(+) and s1.episodeid = s4.episodeid(+) and s1.participantid = v2.participantid(+) and s1.episodeid = v2.episodeid(+) and s1.participantid = v3.participantid(+) and s1.episodeid = v3.episodeid(+) and s1.participantid = s6.participantid(+) and s1.episodeid = s6.episodeid(+) and s1.participantid = s8.participantid(+) and s1.episodeid = s8.episodeid(+) and s1.participantid = s7.participantid(+) and s1.episodeid = s7.episodeid(+) and s1.participantid = pr.participantid(+) and s1.episodeid = pr.episodeid(+) and p.participantid = s1.participantid and s1.participantid = hr.participantid(+) and s1.episodeid = hr.episodeid(+) and s1.participantid = hl.participantid(+) and s1.episodeid = hl.episodeid(+) and s1.instrumentcd='hba' and s1.instrumentversion in ('03','04','06') AND s1.DISTCD in ('BCMI','BCMIS') AND UPPER(s1.POPTIN)='FORCED' and s1.testdata is null and trunc(s1.publicationdt) in (SELECT EXTRACTDATE FROM DATA_ANALYSIS.EXTRACTLOADDATE) RECOMMENDATION 3: Segment Tuning, 5.5% benefit (13197 seconds) ACTION: Investigate application logic involving I/O on TABLE "DATAWAREHOUSE.DWH_EPISODEDATA" with object id 1682202. RELEVANT OBJECT: database object with id 1682202 RATIONALE: The I/O usage statistics for the object are: 57 full object scans, 1066254 physical reads, 29098 physical writes and 606704 direct reads. RATIONALE: The SQL statement with SQL_ID "2hw4b9qgvx4tb" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID 2hw4b9qgvx4tb SELECT RPAD(DISTCD,6)||chr(9)||RPAD(PPROJECTCD,8)||chr(9)||RPAD(E.INS TRUMENTCD,11)||chr(9)||rpad(E.ENROLLMETHODCD,15)||chr(9)|| rpad(E.COMMTYPECD,10)||chr(9)||MIN(PUBLICATIONDT) FROM DATAWAREHOUSE.MV_CHBACOREDEMOPROD S1, PARTICIPANT P, EPISODES E, PIECESTATUS PC WHERE P.PARTICIPANTID=E.PARTICIPANTID AND E.INSTRUMENTCD='chba' and PC.PARTICIPANTID=E.PARTICIPANTID AND PC.EPISODEID=E.EPISODEID and PC.PARTICIPANTID=s1.PARTICIPANTID AND PC.EPISODEID=s1.EPISODEID AND TRUNC(PC.PUBLICATIONDT)>=TO_DATE('12/04/2009','MM/DD/YYYY') AND TRUNC(PC.INITIALTAILORDT)>=TO_DATE('12/04/2009','MM/DD/YYYY') AND P.TESTDATA IS NULL AND PC.PIECECD IN ('chbamas','shramas','mas') and (MEDHISTFAM_OSTEO|| MEDHISTFAM_OBESITY|| MEDHISTFAM_DIABETES|| MEDHISTFAM_HICHOL|| MEDHISTFAM_PROSTATE|| MEDHISTFAM_COLORECTAL|| MEDHISTFAM_BREAST|| MEDHISTFAM_STROKE|| MEDHISTFAM_HBP|| MEDHISTFAM_HAFEMALE|| MEDHISTFAM_HAMALE|| MEDHISTFAM_NONE) is not null group by DISTCD,PPROJECTCD,E.INSTRUMENTCD,E.ENROLLMETHODCD,E.COMMTYPE CD RATIONALE: The SQL statement with SQL_ID "dtx3kt4v7njnf" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID dtx3kt4v7njnf SELECT DISTINCT UMEMBERID||chr(124)|| LASTNAME||chr(124)|| FIRSTNAME||chr(124)|| TO_CHAR(P.BIRTHDATE, 'MM/DD/YYYY')||chr(124)|| P.PARTICIPANTID||chr(124)|| E.EPISODEID||chr(124)|| P.DAYTIMEAREACD||chr(124)|| DAYTIMEPHONE||chr(124)|| replace(replace(replace(P.ADDRESS1,chr(13)||chr(10),''),chr(10),''),c hr(9),'') || chr(124) || replace(replace(replace(P.ADDRESS2,chr(13)||chr(10),''),chr(10),''),c hr(9),'') || chr(124) || replace(replace(replace(P.city,chr(13)||chr(10),''),chr(10),''),chr(9 ),'') || chr(124) || replace(replace(replace(P.state,chr(13)||chr(10),''),chr(10),''),chr( 9),'')|| chr(124) || replace(replace(replace(P.zip,chr(13)||chr(10),''),chr(10),''),chr(9) ,'')|| chr(124) || replace(replace(replace(P.EMAILADDRESS,chr(13)||chr(10),''),chr(10),' '),chr(9),'')|| chr(124) || replace(replace(replace(P.EMAILNEW,chr(13)||chr(10),''),chr(10),''),c hr(9),'')|| chr(124) || SSN||chr(124)|| CONTACTPERMISSCD||chr(124)|| CONTACTPREFCD||chr(124)|| STATUSCD||chr(124)|| replace(GENDERCD,'U',NULL)||chr(124)|| ETHNICITYCD||chr(124)|| MARITALSTATUS||chr(124)|| EDUCATIONCD||chr(124)|| AGE||chr(124)|| E.ENROLLMETHODCD||chr(124)|| TO_CHAR(REGISTDATE, 'MM/DD/YYYY hh:mi:ss PM')||chr(124)|| TO_CHAR(PUBLICATIONDT, 'MM/DD/YYYY hh:mi:ss PM')||chr(124)|| LANGUAGECD||chr(124)|| D.DISTCD||chr(124)|| DISTTX||chr(124)|| P.CUSTOMERCD||chr(124)|| CUSTOMERTX||chr(124)|| PJ.PROJECTCD||chr(124)|| PROJECTX||chr(124)|| P.SITECD||chr(124)|| SITETX||chr(124)|| PAUTHCD||chr(124)|| CUSTCD||chr(124)|| XCLIENT||chr(124)|| XMEMBER||chr(124)|| LMPTYPE||chr(124)|| PLOCATION||chr(124)|| POPTIN||chr(124)|| E.INSTRUMENTVERSION||chr(124)|| CUSTOMQ1||chr(124)|| CUSTOMQ2||chr(124)|| CUSTOMQ3||chr(124)|| CUSTOMQ4||chr(124)|| CUSTOMQ5||chr(124)|| CUSTOMQ6||chr(124)|| CUSTOMQ7||chr(124)|| CUSTOMQ8||chr(124)|| CUSTOMQ9||chr(124)|| CUSTOMQ10||chr(124)|| CUSTOMQ11||chr(124)|| CUSTOMQ12||chr(124)|| CUSTOMQ13||chr(124)|| CUSTOMQ14||chr(124)|| CUSTOMQ15||chr(124)|| CUSTOMQ16||chr(124)|| CUSTOMQ17||chr(124)|| CUSTOMQ18||chr(124)|| CUSTOMQ19||chr(124)|| CUSTOMQ20||chr(124)|| CUSTOMQ21||chr(124)|| CUSTOMQ22||chr(124)|| CUSTOMQ23||chr(124)|| CUSTOMQ24||chr(124)|| CUSTOMQ25||chr(124)|| CUSTOMQ26||chr(124)|| CUSTOMQ27||chr(124)|| CUSTOMQ28||chr(124)|| CUSTOMQ29||chr(124)|| CUSTOMQ30||chr(124)|| CUSTOMQ31||chr(124)|| CUSTOMQ32||chr(124)|| CUSTOMQ33||chr(124)|| CUSTOMQ34||chr(124)|| CUSTOMQ35||chr(124)|| XID||chr(124)|| XGROUP||chr(124)|| MEDHISTPERS_HA||chr(124)|| MEDHISTPERS_ANGINA||chr(124)|| MEDHISTPERS_HBP||chr(124)|| MEDHISTPERS_CHD||chr(124)|| MEDHISTPERS_CHF||chr(124)|| MEDHISTPERS_OHD||chr(124)|| MEDHISTPERS_STROKE||chr(124)|| MEDHISTPERS_TIA||chr(124)|| MEDHISTPERS_PVD||chr(124)|| MEDHISTPERS_EMPHYSEMA||chr(124)|| MEDHISTPERS_CHRONBRON||chr(124)|| MEDHISTPERS_ASTHMA||chr(124)|| MEDHISTPERS_COLORECTAL||chr(124)|| MEDHISTPERS_SKIN||chr(124)|| MEDHISTPERS_BREAST||chr(124)|| MEDHISTPERS_CERVICAL||chr(124)|| MEDHISTPERS_PROSTATE||chr(124)|| MEDHISTPERS_HICHOL||chr(124)|| MEDHISTPERS_HIBLOODSUGAR||chr(124)|| MEDHISTPERS_HEPATITISB||chr(124)|| MEDHISTPERS_STD||chr(124)|| MEDHISTPERS_DIABETES||chr(124)|| MEDHISTPERS_METABOLIC||chr(124)|| MEDHISTPERS_OBESITY||chr(124)|| MEDHISTPERS_OSTEO||chr(124)|| MEDHISTPERS_CHRONPAIN||chr(124)|| MEDHISTPERS_BACKPAIN||chr(124)|| MEDHISTPERS_DEPRESSION||chr(124)|| MEDHISTPERS_INSOMNIA||chr(124)|| MEDHISTFAM_HAMALE||chr(124)|| MEDHISTFAM_HAFEMALE||chr(124)|| MEDHISTFAM_HBP||chr(124)|| MEDHISTFAM_STROKE||chr(124)|| MEDHISTFAM_BREAST||chr(124)|| MEDHISTFAM_COLORECTAL||chr(124)|| MEDHISTFAM_PROSTATE||chr(124)|| MEDHISTFAM_HICHOL||chr(124)|| MEDHISTFAM_DIABETES||chr(124)|| MEDHISTFAM_OBESITY||chr(124)|| MEDHISTFAM_OSTEO||chr(124)|| MEDHISTFAM_DONTKNOW||chr(124)|| WOMPREGNANCY||chr(124)|| DEMOHOUSEHOLD_CHILDUNDER2||chr(124)|| DEMOHOUSEHOLD_CHILD2TO12||chr(124)|| DEMOHOUSEHOLD_TEEN13TO18||chr(124)|| DEMOHOUSEHOLD_ADULTS||chr(124)|| DEMOHOUSEHOLD_ALONE||chr(124)|| case when GENMAINJOBV2='TechincalSupport' then 'TechnicalSupport' else GENMAINJOBV2 end||chr(124)|| GENHEALTHQOFLIFEOVERALL||chr(124)|| GENHEALTHOVERALL||chr(124)|| HBAGENHEALTHCOMPARE||chr(124)|| PAINLASTTHREEMOS||chr(124)|| GENDAYSWORKMISSEDV2||chr(124)|| PRGENHEALTHMISSWORK||chr(124)|| PRGENMISSWORK||chr(124)|| PRGENDIDWORK||chr(124)|| PRGENHEALTHAFFECTWORK||chr(124)|| PRGENHEALTHAFFECTACT||chr(124)|| WOMHRT||chr(124)|| WOMHYSTERECTOMYV2||chr(124)|| TRIM(WTHEIGHTFEET)||chr(124)|| CASE WHEN WTHEIGHTINCHES<>'00' THEN LTRIM(WTHEIGHTINCHES, '0') WHEN WTHEIGHTINCHES='00' THEN '0' ELSE WTHEIGHTINCHES END||chr(124)|| WTWEIGHT||chr(124)|| WTWAIST||chr(124)|| WTSTAGE||chr(124)|| PHYDOCTORNOENGAGE||chr(124)|| PHYREADINESS||chr(124)|| PHYSACTLOWDAYSPERWKV2||chr(124)|| PHYSACTLOWMINPERDAYV2||chr(124)|| PHYSACTMODERATEDAYSPERWKV2||chr(124)|| PHYSACTMODERATEMINPERDAYV2||chr(124)|| PHYSACTHIGHDAYSPERWKV2||chr(124)|| PHYSACTHIGHMINPERDAYV2||chr(124)|| PHYSACTVHIGHDAYSPERWKV2||chr(124)|| PHYSACTVHIGHMINPERDAYV2||chr(124)|| PHYSTRENGTH||chr(124)|| PHYSTRETCH||chr(124)|| PHYSTAGE||chr(124)|| NUTFOODSDONTEAT_DAIRY||chr(124)|| NUTFOODSDONTEAT_EGGS||chr(124)|| NUTFOODSDONTEAT_POULTRY||chr(124)|| NUTFOODSDONTEAT_BEEF||chr(124)|| NUTFOODSDONTEAT_PORKHAM||chr(124)|| NUTFOODSDONTEAT_LAMB||chr(124)|| NUTFOODSDONTEAT_FISH||chr(124)|| NUTFOODSDONTEAT_SHELLFISH||chr(124)|| NUTFOODSDONTEAT_FRUITS||chr(124)|| NUTFOODSDONTEAT_VEGETABLES||chr(124)|| NUTFOODSDONTEAT_BEANSPEAS||chr(124)|| NUTFOODSDONTEAT_GRAINS||chr(124)|| NUTFOODSDONTEAT_NUTS||chr(124)|| NUTFOODSDONTEAT_SEEDS||chr(124)|| NUTSERVINGSGRAINSV2||chr(124)|| NUTMODSERVINGSWHOLEGRAINSV2||chr(124)|| NUTSERVINGSPROTEINMEATV2||chr(124)|| NUTSERVINGSPROTEINNONMEATV2||chr(124)|| NUTSERVINGSVEGETABLESV2||chr(124)|| NUTSERVINGSFRUITSV2||chr(124)|| NUTSERVINGSCALCIUMV2||chr(124)|| NUTSODIUM||chr(124)|| NUTMODSERVINGSOILSV2||chr(124)|| NUTMODSERVINGSFATSV2||chr(124)|| NUTSTAGE||chr(124)|| NUTMODPHY||chr(124)|| SMKSTAGE||chr(124)|| SMKTOBACCOTYPE_CIGARS||chr(124)|| SMKTOBACCOTYPE_PIPE||chr(124)|| SMKTOBACCOTYPE_CHEWTOBACCO||chr(124)|| SMKTOBACCOTYPE_SNUFF||chr(124)|| SMKTOBACCOTYPE_NOTUSE||chr(124)|| HBALCSMKCIGSPERDAY||chr(124)|| HBALCSMKYEARSSMOKE||chr(124)|| SMKFIRSTCIGDAY||chr(124)|| SMKLONGESTTIMEQUIT||chr(124)|| HBALCASBESTOS||chr(124)|| MENSTRESSSTRESSED||chr(124)|| MENSTRESSANGERED||chr(124)|| MENSTRESSDIFFICULTIES||chr(124)|| MENSTRESSCONFIDENT||chr(124)|| MENSTRESSONTOP||chr(124)|| MENSTRESSSTAGE||chr(124)|| MENSTRESSTREATMENT||chr(124)|| MENSTRESSDEPRESSED||chr(124)|| DEPDISLIKED||chr(124)|| DEPHAPPY||chr(124)|| DEPFELTEFFORT||chr(124)|| DEPRESTLESS||chr(124)|| DEPFELTLONELY||chr(124)|| DEPOTHERSUNFRIENDLY||chr(124)|| DEPENJOYEDLIFE||chr(124)|| DEPFELTSAD||chr(124)|| DEPNOTGETGOING||chr(124)|| INSSLEEPHOURS||chr(124)|| INSSLEEPQUALITY||chr(124)|| INSSLEEPDESC_HARDFALLASLEEP||chr(124)|| INSSLEEPDESC_HARDSTAYASLEEP||chr(124)|| INSSLEEPDESC_WAKETOOEARLY||chr(124)|| INSSLEEPDESC_STILLTIRED||chr(124)|| ANXFEAREPISODES||chr(124)|| ANXFEARSYM||chr(124)|| ANXWORRYCONTROL||chr(124)|| ANXMORESYM_RESTLESS||chr(124)|| ANXMORESYM_FATIGUED||chr(124)|| ANXMORESYM_CONCENTRATE||chr(124)|| ANXMORESYM_IRRITABLE||chr(124)|| ANXMORESYM_MUSCTENSION||chr(124)|| ANXMORESYM_DIFFSLEEP||chr(124)|| ANXTRAUMATICEVENT||chr(124)|| ANXDREAMSUPSETTING||chr(124)|| ANXFOLLOWSYM_DIFFSLEEP||chr(124)|| ANXFOLLOWSYM_IRRITABLE||chr(124)|| ANXFOLLOWSYM_CONCENTRATE||chr(124)|| ANXFOLLOWSYM_ONGUARD||chr(124)|| ANXFOLLOWSYM_JUMPY||chr(124)|| ANXRECURRINGIMAGES||chr(124)|| ANXREPETITIVE||chr(124)|| ANXSOCIALSIT_SPEAKING||chr(124)|| ANXSOCIALSIT_TESTS||chr(124)|| ANXSOCIALSIT_PUBLICACTS||chr(124)|| ANXSOCIALSIT_ATTENTION||chr(124)|| ANXSOCIALSIT_ASKDATES||chr(124)|| ANXPARTICIPATION||chr(124)|| ANXFEAROBJECT||chr(124)|| ALCFREQUENCY||chr(124)|| ALCDRINKSDAY||chr(124)|| ALCBINGE||chr(124)|| ALCSTAGE||chr(124)|| DRUGUSEPAST_TAKENMORE||chr(124)|| DRUGUSEPAST_USEDRECDRUGS||chr(124)|| DRUGUSEPAST_NA||chr(124)|| DRUGUSECURRENT_TAKENMORE||chr(124)|| DRUGUSECURRENT_USEDRECDRUGS||chr(124)|| DRUGUSECURRENT_NA||chr(124)|| DRUGUSERESULT_LOSTMARRIAGE||chr(124)|| DRUGUSERESULT_LOSTRELS||chr(124)|| DRUGUSERESULT_LOSTCUSTODY||chr(124)|| DRUGUSERESULT_LOSTJOB||chr(124)|| DRUGUSERESULT_LAWTROUBLE||chr(124)|| DRUGUSERESULT_FINDIFF||chr(124)|| DRUGUSERESULT_POORHEALTH||chr(124)|| DRUGUSERESULT_OTHER||chr(124)|| DRUGUSERESULT_NONE||chr(124)|| HBADRUGSTAGE||chr(124)|| HBADRUGUSERXPROF||chr(124)|| HBADRUGUSERECPROF||chr(124)|| SKNCLOTHING||chr(124)|| SKNSHADE||chr(124)|| SKNSUNSCREEN||chr(124)|| SKNNOPROTECTION||chr(124)|| SKNREPEATEDEXPOSURE||chr(124)|| SKNHOWSENSITIVE||chr(124)|| INJGATINGQUESTIONNFA||chr(124)|| INJRIDEDRUNKDRIVER||chr(124)|| INJDRIVEDRUNK||chr(124)|| replace(INJSEATBELT, 'Nearly always', 'NearlyAlways')||chr(124)|| INJSPEEDLIMIT||chr(124)|| INJGATINGQUESTION||chr(124)|| INJGUNSHOME||chr(124)|| INJGUNSLOCKED||chr(124)|| INJGUNSCOURSE||chr(124)|| SEXGATINGQUESTION||chr(124)|| SEXNUMPARTNERS||chr(124)|| SEXTYPEPARTNERS||chr(124)|| SEXRELATIONSHIP||chr(124)|| SEXRELCONDOM||chr(124)|| SEXAFFAIR||chr(124)|| SEXAFFAIRCONDOM||chr(124)|| SEXSTAGE||chr(124)|| HSCRMMR||chr(124)|| HSCRHEPATITISB||chr(124)|| HSCRPNEUMONIA||chr(124)|| HSCRTETANUS||chr(124)|| HSCRINFLUENZA||chr(124)|| HSCRSHINGLES||chr(124)|| HSCRCHICKENPOX||chr(124)|| HSCRFOBT||chr(124)|| HBAHSCRCOLON||chr(124)|| HSCRSIGMOID||chr(124)|| HSCRPSA||chr(124)|| HSCRPAP||chr(124)|| HBAHSCRHPV||chr(124)|| HSCRCLINICALBREASTEXAM||chr(124)|| HSCRMAMMOGRAM||chr(124)|| HSCRBP12MONTHS||chr(124)|| HSCRBPDONTKNOW||chr(124)|| HSCRSBP||chr(124)|| HSCRDBP||chr(124)|| HSCRCHOLESTEROL5YRS||chr(124)|| HSCRBLOODGLUCOSE3YRS||chr(124)|| HSCRCHOLESTEROLDONTKNOW||chr(124)|| HSCRCHOLESTEROL||chr(124)|| HSCRHDLDONTKNOW||chr(124)|| HSCRHDL||chr(124)|| HSCRLDLDONTKNOW||chr(124)|| HSCRLDL||chr(124)|| HSCRTRIGLYCERIDEDONTKNOWV5||chr(124)|| HSCRTRIGLYCERIDEV5||chr(124)|| HSCRBLOODGLUCOSEDONTKNOW||chr(124)|| HSCRBLOODGLUCOSE||chr(124)|| HSCRBLOODGLUCOSEFASTING||chr(124)|| HBABCINTERESTRISK||chr(124)|| HBABCGATING||chr(124)|| HBABCFIRSTPERIOD||chr(124)|| HBABCAGEFIRSTCHILD||chr(124)|| HBABCRELBREASTCANCER||chr(124)|| HBABCBIOPSY||chr(124)|| HBABCNUMBIOPSY||chr(124)|| HBABCBIOPSYAH||chr(124)|| PAINMOTIVATIONLIST||chr(124)|| WTMOTIVATIONLIST||chr(124)|| PHYMOTIVATIONLIST||chr(124)|| NUTMOTIVATIONLIST||chr(124)|| SMKMOTIVATIONLIST||chr(124)|| MENSTRESSMOTIVATIONLIST||chr(124)|| INSMOTIVATIONLIST||chr(124)|| ANXMOTIVATIONLIST||chr(124)|| ALCMOTIVATIONLIST||chr(124)|| HBADRUGMOTIVATIONLIST||chr(124)|| SKNMOTIVATIONLIST||chr(124)|| INJMOTIVATIONLIST||chr(124)|| SEXMOTIVATIONLIST||chr(124)|| PAINCONFIDENCE||chr(124)|| WTCONFIDENCE||chr(124)|| PHYCONFIDENCE||chr(124)|| NUTCONFIDENCE||chr(124)|| SMKCONFIDENCE||chr(124)|| MENSTRESSCONFIDENCE||chr(124)|| INSCONFIDENCE||chr(124)|| ANXCONFIDENCE||chr(124)|| ALCCONFIDENCE||chr(124)|| DRUGCONFIDENCE||chr(124)|| SKNCONFIDENCE||chr(124)|| INJCONFIDENCE||chr(124)|| SEXCONFIDENCE||chr(124)|| WTSE_ANXIOUSANGRY||chr(124)|| WTSE_HOLIDAYVACATION||chr(124)|| WTSE_EATOUT||chr(124)|| WTSE_TIREDSTRESSED||chr(124)|| WTSE_DONTEXERCISE||chr(124)|| WTSE_LONELYBORED||chr(124)|| PHYSE_NOPARTNER||chr(124)|| PHYSE_TIRED||chr(124)|| PHYSE_NOEXERCISEREGULARLY||chr(124)|| PHYSE_NOACTIVITYENJOY||chr(124)|| PHYSE_WEATHERPOOR||chr(124)|| PHYSE_SCHEDULE||chr(124)|| NUTSE_OTHERSEATUNHEALTHY||chr(124)|| NUTSE_TOOTIREDTOPREPARE||chr(124)|| NUTSE_EATOUTFREQUENTLY||chr(124)|| NUTSE_NEEDREWARDFOOD||chr(124)|| NUTSE_AROUNDTEMPTINGFOODS||chr(124)|| NUTSE_NOTIMESHOPGROCERIES||chr(124)|| SMKSE_NEEDLIFT||chr(124)|| SMKSE_STRESSED||chr(124)|| SMKSE_ANGRY||chr(124)|| SMKSE_BORED||chr(124)|| SMKSE_CRAVING||chr(124)|| SMKSE_WEIGHT||chr(124)|| MENSTRESSSE_REDUCESTRESS||chr(124)|| MENSTRESSSE_PRACTECHNIQUES||chr(124)|| MENSTRESSSE_TIMEDEMANDS||chr(124)|| MENSTRESSSE_FINDSUPPORT||chr(124)|| MENSTRESSSE_SETLIMITS||chr(124)|| MENSTRESSSE_LEAVESITUATIONS||chr(124)|| MENDEPSE_GETHELP||chr(124)|| MENDEPSE_CARINGPROFESS||chr(124)|| MENDEPSE_KEPTCONFIDENTIAL||chr(124)|| MENDEPSE_FOLLOWTREATMENT||chr(124)|| MENDEPSE_BENEFITTREATMENT||chr(124)|| MENDEPSE_HAVESUPPORT||chr(124)|| ALCSE_DEPRESSED||chr(124)|| ALCSE_VACATION||chr(124)|| ALCSE_ANGRY||chr(124)|| ALCSE_SOCIAL||chr(124)|| ALCSE_BARPARTY||chr(124)|| ALCSE_ALONE||chr(124)|| SKNSE_USESUNSCREEN||chr(124)|| SKNSE_PROTECTSKN||chr(124)|| SKNSE_SEEKSHADE||chr(124)|| SKNSE_10TO2||chr(124)|| SKNSE_SELFCHECK||chr(124)|| SKNSE_DOCTORCHECK||chr(124)|| INJSE_BELTEVERYTIME||chr(124)|| INJSE_DRINKAVOIDDRIVE||chr(124)|| INJSE_DRIVEWITHINLIMIT||chr(124)|| INJSE_DRINKAVOIDRIDE||chr(124)|| INJSE_STOREGUNSUNLOADED||chr(124)|| INJSE_EDUCATEGUNSAFETY||chr(124)|| SEXSE_CONDOMSUBSTANCE||chr(124)|| SEXSE_CONDOMNORISK||chr(124)|| SEXSE_CONDOMDISCUSSPARTNER||chr(124)|| SEXSE_CONDOMOTHERBCONTROL||chr(124)|| SEXSE_CONDOMAROUSED||chr(124)|| SEXSE_CONDOMPURCHASE||chr(124)|| REPLACE(to_char(ALCBEHSCORE,'FM990.000'), '-1.000',NULL) ||chr(124) || REPLACE(to_char(ALCMOTOSCORE,'FM990.000'), '-1.000',NULL)||chr(124) || REPLACE(to_char(ALCSESCOREVALUE,'FM990.000'), '-1.000',NULL) ||chr(124) || REPLACE(to_char(BCINDABSFIVEYRNORISK,'FM990.000'), '-1.000',NULL) ||chr(124) || REPLACE(to_char(BCINDABSFIVEYRRISK,'FM990.000'), '-1.000',NULL) ||chr(124) || REPLACE(to_char(BCINDABSLIFETIMERISK,'FM990.000'), '-1.000',NULL) ||chr(124) || REPLACE(to_char(BCINDAVGFIVEYRRISK,'FM990.000'), '-1.000',NULL) ||chr(124) || REPLACE(to_char(BCINDAVGLIFETIMERISK,'FM990.000'), '-1.000',NULL) ||chr(124) || BEHAVIOR1NAME||chr(124)|| BEHAVIOR2NAME||chr(124)|| BEHAVIOR3NAME||chr(124)|| BEHAVIOR4NAME||chr(124)|| BEHAVIOR5NAME||chr(124)|| BEHAVIOR6NAME||chr(124)|| BEHAVIOR7NAME||chr(124)|| BEHAVIOR8NAME||chr(124)|| BEHAVIOR9NAME||chr(124)|| BEHAVIOR10NAME||chr(124)|| BMI||chr(124)|| replace(CHDRISK,'-1',null)||chr(124)|| CHDRISKPERC||chr(124)|| replace(DEPBEHSCORE,'-1.000',NULL)||chr(124)|| REPLACE(DEPMOTOSCORE,'-1.000',NULL)||chr(124)|| replace(DEPSESCOREVALUE,'-1.000',NULL)||chr(124)|| replace(DRUGBEHSCORE,'-1.000',NULL)||chr(124)|| replace(DRUGMOTOSCORE,'-1.000',NULL)||chr(124)|| replace(DRUGSESCOREVALUE,'-1.000',NULL)||chr(124)|| replace(INJBEHSCORE,'-1.000',NULL)||chr(124)|| replace(INJMOTOSCORE,'-1.000',null)||chr(124)|| replace(INJSESCOREVALUE,'-1.000',null)||chr(124)|| replace(LCPROBQUIT,'-1.000',null)||chr(124)|| replace(LCPROBSMOKE,'-1.000',null)||chr(124)|| LIFESTYLESCORE||chr(124)|| replace(MENBEHSCORE,'-1.000',null)||chr(124)|| replace(MENMOTOSCORE,'-1.000',null)||chr(124)|| replace(MENSESCOREVALUE,'-1.000',null)||chr(124)|| replace(NUTBEHSCORE,'-1.000',null)||chr(124)|| replace(NUTMOTOSCORE,'-1.000',null)||chr(124)|| replace(NUTSESCOREVALUE,'-1.000',null)||chr(124)|| replace(PHYBEHSCORE,'-1.000',null)||chr(124)|| replace(PHYMOTOSCORE,'-1.000',null)||chr(124)|| replace(PHYSESCOREVALUE,'-1.000',null)||chr(124)|| replace(SEXBEHSCORE,'-1.000',null)||chr(124)|| replace(SEXMOTOSCORE,'-1.000',null)||chr(124)|| replace(SEXSESCOREVALUE,'-1.000',null)||chr(124)|| replace(SKNBEHSCORE,'-1.000',null)||chr(124)|| replace(SKNMOTOSCORE,'-1.000',null)||chr(124)|| replace(SKNSESCOREVALUE,'-1.000',null)||chr(124)|| replace(SMKBEHSCORE,'-1.000',null)||chr(124)|| replace(SMKMOTOSCORE,'-1.000',null)||chr(124)|| replace(SMKSESCOREVALUE,'-1.000',null)||chr(124)|| replace(STRBEHSCORE,'-1.000',null)||chr(124)|| replace(STRMOTOSCORE,'-1.000',null)||chr(124)|| replace(STRSESCOREVALUE,'-1.000',null)||chr(124)|| replace(WTBEHSCORE,'-1.000',null)||chr(124)|| replace(WTMOTOSCORE,'-1.000',null)||chr(124)|| replace(WTSESCOREVALUE,'-1.000',null)||chr(124)|| CALLEVEL||chr(124)|| DISRISKAVAIL||chr(124)|| e.COUNSELOROPTIN||chr(124)|| CUSTOMQ36||chr(124)|| CUSTOMQ37||chr(124)|| CUSTOMQ38||chr(124)|| CUSTOMQ39||chr(124)|| CUSTOMQ40||chr(124)|| CUSTOMQ41||chr(124)|| CUSTOMQ42||chr(124)|| CUSTOMQ43||chr(124)|| CUSTOMQ44||chr(124)|| CUSTOMQ45||chr(124)|| BINGEEVEROVEREAT||chr(124)|| BINGEEVEROVEREATNUM||chr(124)|| BINGELOSTCONTROL||chr(124)|| BINGELOSTCONTROLNUM||chr(124)|| TO_CHAR(ENROLLDATE, 'MM/DD/YYYY hh:mi:ss PM')||chr(124)|| MEDHISTPERS_NONE||chr(124)|| MEDHISTFAM_NONE||chr(124)|| WOMPREGNANCYEDD||chr(124)|| WOMBREASTFEEDING||chr(124)|| INSSLEEPDESC_NONE||chr(124)|| NUTFOODSDONTEAT_GLUTEN||chr(124)|| NUTFOODSDONTEAT_NONE||chr(124)|| SKNSE_10TO4||chr(124)|| HSCRBARIUMENEMA||chr(124)|| HSCRPAPV2||chr(124)|| HSCRBONEDENSITY||chr(124)|| HSCRMAMMOGRAMV2||chr(124)|| HSCRBPV2 FROM DATAWAREHOUSE.MV_PARTICIPANT P, DATAWAREHOUSE.MV_EPISODES E, DATAWAREHOUSE.MV_PIECESTATUS PC, DATAWAREHOUSE.MV_CHBAALCSKINMOTORIMM S1, DATAWAREHOUSE.MV_CHBABIONUTPHYWOM S2, DATAWAREHOUSE.MV_CHBACOREDEMOPROD S3, DATAWAREHOUSE.MV_CHBAPAINANXINSLUN S4, DATAWAREHOUSE.MV_CHBAPARAMETERS S5, DATAWAREHOUSE.MV_CHBASCRSEXDRUGCAN S6, DATAWAREHOUSE.MV_CHBATOBDEPSTRESS S7, DATAWAREHOUSE.MV_SITE S, DATAWAREHOUSE.MV_DISTRIBUTOR D, DATAWAREHOUSE.MV_CUSTOMER C, DATAWAREHOUSE.MV_PROJECT PJ, DATAWAREHOUSE.MV_CORE CR WHERE P.PARTICIPANTID=E.PARTICIPANTID AND E.PARTICIPANTID=PC.PARTICIPANTID AND E.EPISODEID=PC.EPISODEID AND UPPER(PC.PIECECD) IN ('CHBAMAS','MAS') AND E.INSTRUMENTCD='chba' and PC.PARTICIPANTID=S1.PARTICIPANTID(+) AND PC.EPISODEID=S1.EPISODEID(+) AND PC.PARTICIPANTID=S3.PARTICIPANTID(+) AND PC.EPISODEID=S3.EPISODEID(+) AND PC.PARTICIPANTID=S4.PARTICIPANTID(+) AND PC.EPISODEID=S4.EPISODEID(+) AND PC.PARTICIPANTID=S5.PARTICIPANTID(+) AND PC.EPISODEID=S5.EPISODEID(+) AND PC.PARTICIPANTID=S6.PARTICIPANTID(+) AND PC.EPISODEID=S6.EPISODEID(+) AND PC.PARTICIPANTID=S7.PARTICIPANTID(+) AND PC.EPISODEID=S7.EPISODEID(+) AND PC.PARTICIPANTID=S2.PARTICIPANTID(+) AND PC.EPISODEID=S2.EPISODEID(+) AND S.SITECD=P.SITECD AND D.DISTCD=P.PDISTCD AND C.CUSTOMERCD=P.CUSTOMERCD AND PJ.PROJECTCD=P.PPROJECTCD AND PC.PARTICIPANTID=CR.PARTICIPANTID(+) AND PC.EPISODEID=CR.EPISODEID(+) AND p.customercd in ('NAVI','SYMR','FLHO','ALPHA','HALB','LTST','HES1 ','CBTF','GORE','MIGA','JDUS') --and p.pauthcd in ('NAVEAUTHCD','NAVSAUTHCD','NAVEPRINTAUTHCD','NAVS PRINTAUTHCD','SYMRAUTHCD','SYMRBIOAUTHCD','FLHOBIOAUTHCD','FLHOAUTHCD ','ALPHABIO','ALPHAPRINTCD','ALPHAPRODCD','HALBAUTHCD','HALBAUTHBIOCD ','HALBTHPRODCD','LTSTAUTHCD') and p.poptin = 'Forced' and p.testdata is NULL AND TRUNC(PC.PUBLICATIONDT) in (SELECT EXTRACTDATE FROM DATA_ANALYSIS.EXTRACTLOADDATE) RATIONALE: The SQL statement with SQL_ID "ahgdsjp96wvuk" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID ahgdsjp96wvuk SELECT DISTINCT UMEMBERID||chr(9)|| LASTNAME||chr(9)|| FIRSTNAME||chr(9)|| BIRTHDATE||chr(9)|| P.PARTICIPANTID||chr(9)|| E.EPISODEID||chr(9)|| P.DAYTIMEAREACD||chr(9)|| DAYTIMEPHONE||chr(9)|| ADDRESS1||chr(9)|| ADDRESS2||chr(9)|| CITY||chr(9)|| STATE||chr(9)|| ZIP||chr(9)|| EMAILADDRESS||chr(9)|| EMAILNEW||chr(9)|| SSN||chr(9)|| CONTACTPERMISSCD||chr(9)|| CONTACTPREFCD||chr(9)|| STATUSCD||chr(9)|| GENDERCD||chr(9)|| ETHNICITYCD||chr(9)|| MARITALSTATUS||chr(9)|| EDUCATIONCD||chr(9)|| AGE||chr(9)|| E.ENROLLMETHODCD||chr(9)|| REGISTDATE||chr(9)|| PUBLICATIONDT||chr(9)|| LANGUAGECD||chr(9)|| D.DISTCD||chr(9)|| DISTTX||chr(9)|| P.CUSTOMERCD||chr(9)|| CUSTOMERTX||chr(9)|| PJ.PROJECTCD||chr(9)|| PROJECTX||chr(9)|| P.SITECD||chr(9)|| SITETX||chr(9)|| PAUTHCD||chr(9)|| CUSTCD||chr(9)|| XCLIENT||chr(9)|| XMEMBER||chr(9)|| LMPTYPE||chr(9)|| PLOCATION||chr(9)|| POPTIN||chr(9)|| E.INSTRUMENTVERSION||chr(9)|| CUSTOMQ1||chr(9)|| CUSTOMQ2||chr(9)|| CUSTOMQ3||chr(9)|| CUSTOMQ4||chr(9)|| CUSTOMQ5||chr(9)|| CUSTOMQ6||chr(9)|| CUSTOMQ7||chr(9)|| CUSTOMQ8||chr(9)|| CUSTOMQ9||chr(9)|| CUSTOMQ10||chr(9)|| CUSTOMQ11||chr(9)|| CUSTOMQ12||chr(9)|| CUSTOMQ13||chr(9)|| CUSTOMQ14||chr(9)|| CUSTOMQ15||chr(9)|| CUSTOMQ16||chr(9)|| CUSTOMQ17||chr(9)|| CUSTOMQ18||chr(9)|| CUSTOMQ19||chr(9)|| CUSTOMQ20||chr(9)|| CUSTOMQ21||chr(9)|| CUSTOMQ22||chr(9)|| CUSTOMQ23||chr(9)|| CUSTOMQ24||chr(9)|| CUSTOMQ25||chr(9)|| CUSTOMQ26||chr(9)|| CUSTOMQ27||chr(9)|| CUSTOMQ28||chr(9)|| CUSTOMQ29||chr(9)|| CUSTOMQ30||chr(9)|| CUSTOMQ31||chr(9)|| CUSTOMQ32||chr(9)|| CUSTOMQ33||chr(9)|| CUSTOMQ34||chr(9)|| CUSTOMQ35||chr(9)|| XID||chr(9)|| XGROUP||chr(9)|| MEDHISTPERS_HA||chr(9)|| MEDHISTPERS_ANGINA||chr(9)|| MEDHISTPERS_HBP||chr(9)|| MEDHISTPERS_CHD||chr(9)|| MEDHISTPERS_CHF||chr(9)|| MEDHISTPERS_OHD||chr(9)|| MEDHISTPERS_STROKE||chr(9)|| MEDHISTPERS_TIA||chr(9)|| MEDHISTPERS_PVD||chr(9)|| MEDHISTPERS_EMPHYSEMA||chr(9)|| MEDHISTPERS_CHRONBRON||chr(9)|| MEDHISTPERS_ASTHMA||chr(9)|| MEDHISTPERS_COLORECTAL||chr(9)|| MEDHISTPERS_SKIN||chr(9)|| MEDHISTPERS_BREAST||chr(9)|| MEDHISTPERS_CERVICAL||chr(9)|| MEDHISTPERS_PROSTATE||chr(9)|| MEDHISTPERS_HICHOL||chr(9)|| MEDHISTPERS_HIBLOODSUGAR||chr(9)|| MEDHISTPERS_HEPATITISB||chr(9)|| MEDHISTPERS_STD||chr(9)|| MEDHISTPERS_DIABETES||chr(9)|| MEDHISTPERS_METABOLIC||chr(9)|| MEDHISTPERS_OBESITY||chr(9)|| MEDHISTPERS_OSTEO||chr(9)|| MEDHISTPERS_CHRONPAIN||chr(9)|| MEDHISTPERS_BACKPAIN||chr(9)|| MEDHISTPERS_DEPRESSION||chr(9)|| MEDHISTPERS_INSOMNIA||chr(9)|| MEDHISTFAM_HAMALE||chr(9)|| MEDHISTFAM_HAFEMALE||chr(9)|| MEDHISTFAM_HBP||chr(9)|| MEDHISTFAM_STROKE||chr(9)|| MEDHISTFAM_BREAST||chr(9)|| MEDHISTFAM_COLORECTAL||chr(9)|| MEDHISTFAM_PROSTATE||chr(9)|| MEDHISTFAM_HICHOL||chr(9)|| MEDHISTFAM_DIABETES||chr(9)|| MEDHISTFAM_OBESITY||chr(9)|| MEDHISTFAM_OSTEO||chr(9)|| MEDHISTFAM_DONTKNOW||chr(9)|| WOMPREGNANCY||chr(9)|| DEMOHOUSEHOLD_CHILDUNDER2||chr(9)|| DEMOHOUSEHOLD_CHILD2TO12||chr(9)|| DEMOHOUSEHOLD_TEEN13TO18||chr(9)|| DEMOHOUSEHOLD_ADULTS||chr(9)|| DEMOHOUSEHOLD_ALONE||chr(9)|| CASE WHEN GENMAINJOBV2='TechincalSupport' THEN 'TechnicalSupport' ELSE GENMAINJOBV2 END||chr(9)|| GENHEALTHQOFLIFEOVERALL||chr(9)|| GENHEALTHOVERALL||chr(9)|| HBAGENHEALTHCOMPARE||chr(9)|| PAINLASTTHREEMOS||chr(9)|| GENDAYSWORKMISSEDV2||chr(9)|| PRGENHEALTHMISSWORK||chr(9)|| PRGENMISSWORK||chr(9)|| PRGENDIDWORK||chr(9)|| PRGENHEALTHAFFECTWORK||chr(9)|| PRGENHEALTHAFFECTACT||chr(9)|| WOMHRT||chr(9)|| WOMHYSTERECTOMYV2||chr(9)|| WTHEIGHTFEET||chr(9)|| WTHEIGHTINCHES||chr(9)|| WTWEIGHT||chr(9)|| WTWAIST||chr(9)|| WTSTAGE||chr(9)|| PHYDOCTORNOENGAGE||chr(9)|| PHYREADINESS||chr(9)|| PHYSACTLOWDAYSPERWKV2||chr(9)|| PHYSACTLOWMINPERDAYV2||chr(9)|| PHYSACTMODERATEDAYSPERWKV2||chr(9)|| PHYSACTMODERATEMINPERDAYV2||chr(9)|| PHYSACTHIGHDAYSPERWKV2||chr(9)|| PHYSACTHIGHMINPERDAYV2||chr(9)|| PHYSACTVHIGHDAYSPERWKV2||chr(9)|| PHYSACTVHIGHMINPERDAYV2||chr(9)|| PHYSTRENGTH||chr(9)|| PHYSTRETCH||chr(9)|| PHYSTAGE||chr(9)|| NUTFOODSDONTEAT_DAIRY||chr(9)|| NUTFOODSDONTEAT_EGGS||chr(9)|| NUTFOODSDONTEAT_POULTRY||chr(9)|| NUTFOODSDONTEAT_BEEF||chr(9)|| NUTFOODSDONTEAT_PORKHAM||chr(9)|| NUTFOODSDONTEAT_LAMB||chr(9)|| NUTFOODSDONTEAT_FISH||chr(9)|| NUTFOODSDONTEAT_SHELLFISH||chr(9)|| NUTFOODSDONTEAT_FRUITS||chr(9)|| NUTFOODSDONTEAT_VEGETABLES||chr(9)|| NUTFOODSDONTEAT_BEANSPEAS||chr(9)|| NUTFOODSDONTEAT_GRAINS||chr(9)|| NUTFOODSDONTEAT_NUTS||chr(9)|| NUTFOODSDONTEAT_SEEDS||chr(9)|| NUTSERVINGSGRAINSV2||chr(9)|| NUTMODSERVINGSWHOLEGRAINSV2||chr(9)|| NUTSERVINGSPROTEINMEATV2||chr(9)|| NUTSERVINGSPROTEINNONMEATV2||chr(9)|| NUTSERVINGSVEGETABLESV2||chr(9)|| NUTSERVINGSFRUITSV2||chr(9)|| NUTSERVINGSCALCIUMV2||chr(9)|| NUTSODIUM||chr(9)|| NUTMODSERVINGSOILSV2||chr(9)|| NUTMODSERVINGSFATSV2||chr(9)|| NUTSTAGE||chr(9)|| NUTMODPHY||chr(9)|| SMKSTAGE||chr(9)|| SMKTOBACCOTYPE_CIGARS||chr(9)|| SMKTOBACCOTYPE_PIPE||chr(9)|| SMKTOBACCOTYPE_CHEWTOBACCO||chr(9)|| SMKTOBACCOTYPE_SNUFF||chr(9)|| SMKTOBACCOTYPE_NOTUSE||chr(9)|| HBALCSMKCIGSPERDAY||chr(9)|| HBALCSMKYEARSSMOKE||chr(9)|| SMKFIRSTCIGDAY||chr(9)|| SMKLONGESTTIMEQUIT||chr(9)|| HBALCASBESTOS||chr(9)|| MENSTRESSSTRESSED||chr(9)|| MENSTRESSANGERED||chr(9)|| MENSTRESSDIFFICULTIES||chr(9)|| MENSTRESSCONFIDENT||chr(9)|| MENSTRESSONTOP||chr(9)|| MENSTRESSSTAGE||chr(9)|| MENSTRESSTREATMENT||chr(9)|| MENSTRESSDEPRESSED||chr(9)|| DEPDISLIKED||chr(9)|| DEPHAPPY||chr(9)|| DEPFELTEFFORT||chr(9)|| DEPRESTLESS||chr(9)|| DEPFELTLONELY||chr(9)|| DEPOTHERSUNFRIENDLY||chr(9)|| DEPENJOYEDLIFE||chr(9)|| DEPFELTSAD||chr(9)|| DEPNOTGETGOING||chr(9)|| INSSLEEPHOURS||chr(9)|| INSSLEEPQUALITY||chr(9)|| INSSLEEPDESC_HARDFALLASLEEP||chr(9)|| INSSLEEPDESC_HARDSTAYASLEEP||chr(9)|| INSSLEEPDESC_WAKETOOEARLY||chr(9)|| INSSLEEPDESC_STILLTIRED||chr(9)|| ANXFEAREPISODES||chr(9)|| ANXFEARSYM||chr(9)|| ANXWORRYCONTROL||chr(9)|| ANXMORESYM_RESTLESS||chr(9)|| ANXMORESYM_FATIGUED||chr(9)|| ANXMORESYM_CONCENTRATE||chr(9)|| ANXMORESYM_IRRITABLE||chr(9)|| ANXMORESYM_MUSCTENSION||chr(9)|| ANXMORESYM_DIFFSLEEP||chr(9)|| ANXTRAUMATICEVENT||chr(9)|| ANXDREAMSUPSETTING||chr(9)|| ANXFOLLOWSYM_DIFFSLEEP||chr(9)|| ANXFOLLOWSYM_IRRITABLE||chr(9)|| ANXFOLLOWSYM_CONCENTRATE||chr(9)|| ANXFOLLOWSYM_ONGUARD||chr(9)|| ANXFOLLOWSYM_JUMPY||chr(9)|| ANXRECURRINGIMAGES||chr(9)|| ANXREPETITIVE||chr(9)|| ANXSOCIALSIT_SPEAKING||chr(9)|| ANXSOCIALSIT_TESTS||chr(9)|| ANXSOCIALSIT_PUBLICACTS||chr(9)|| ANXSOCIALSIT_ATTENTION||chr(9)|| ANXSOCIALSIT_ASKDATES||chr(9)|| ANXPARTICIPATION||chr(9)|| ANXFEAROBJECT||chr(9)|| ALCFREQUENCY||chr(9)|| ALCDRINKSDAY||chr(9)|| ALCBINGE||chr(9)|| ALCSTAGE||chr(9)|| DRUGUSEPAST_TAKENMORE||chr(9)|| DRUGUSEPAST_USEDRECDRUGS||chr(9)|| DRUGUSEPAST_NA||chr(9)|| DRUGUSECURRENT_TAKENMORE||chr(9)|| DRUGUSECURRENT_USEDRECDRUGS||chr(9)|| DRUGUSECURRENT_NA||chr(9)|| DRUGUSERESULT_LOSTMARRIAGE||chr(9)|| DRUGUSERESULT_LOSTRELS||chr(9)|| DRUGUSERESULT_LOSTCUSTODY||chr(9)|| DRUGUSERESULT_LOSTJOB||chr(9)|| DRUGUSERESULT_LAWTROUBLE||chr(9)|| DRUGUSERESULT_FINDIFF||chr(9)|| DRUGUSERESULT_POORHEALTH||chr(9)|| DRUGUSERESULT_OTHER||chr(9)|| DRUGUSERESULT_NONE||chr(9)|| HBADRUGSTAGE||chr(9)|| HBADRUGUSERXPROF||chr(9)|| HBADRUGUSERECPROF||chr(9)|| SKNCLOTHING||chr(9)|| SKNSHADE||chr(9)|| SKNSUNSCREEN||chr(9)|| SKNNOPROTECTION||chr(9)|| SKNREPEATEDEXPOSURE||chr(9)|| SKNHOWSENSITIVE||chr(9)|| INJGATINGQUESTIONNFA||chr(9)|| INJRIDEDRUNKDRIVER||chr(9)|| INJDRIVEDRUNK||chr(9)|| INJSEATBELT||chr(9)|| INJSPEEDLIMIT||chr(9)|| INJGATINGQUESTION||chr(9)|| INJGUNSHOME||chr(9)|| INJGUNSLOCKED||chr(9)|| INJGUNSCOURSE||chr(9)|| SEXGATINGQUESTION||chr(9)|| SEXNUMPARTNERS||chr(9)|| SEXTYPEPARTNERS||chr(9)|| SEXRELATIONSHIP||chr(9)|| SEXRELCONDOM||chr(9)|| SEXAFFAIR||chr(9)|| SEXAFFAIRCONDOM||chr(9)|| SEXSTAGE||chr(9)|| HSCRMMR||chr(9)|| HSCRHEPATITISB||chr(9)|| HSCRPNEUMONIA||chr(9)|| HSCRTETANUS||chr(9)|| HSCRINFLUENZA||chr(9)|| HSCRSHINGLES||chr(9)|| HSCRCHICKENPOX||chr(9)|| HSCRFOBT||chr(9)|| HBAHSCRCOLON||chr(9)|| HSCRSIGMOID||chr(9)|| HSCRPSA||chr(9)|| HSCRPAP||chr(9)|| HBAHSCRHPV||chr(9)|| HSCRCLINICALBREASTEXAM||chr(9)|| HSCRMAMMOGRAM||chr(9)|| HSCRBP12MONTHS||chr(9)|| HSCRBPDONTKNOW||chr(9)|| HSCRSBP||chr(9)|| HSCRDBP||chr(9)|| HSCRCHOLESTEROL5YRS||chr(9)|| HSCRBLOODGLUCOSE3YRS||chr(9)|| HSCRCHOLESTEROLDONTKNOW||chr(9)|| HSCRCHOLESTEROL||chr(9)|| HSCRHDLDONTKNOW||chr(9)|| HSCRHDL||chr(9)|| HSCRLDLDONTKNOW||chr(9)|| HSCRLDL||chr(9)|| HSCRTRIGLYCERIDEDONTKNOWV5||chr(9)|| HSCRTRIGLYCERIDEV5||chr(9)|| HSCRBLOODGLUCOSEDONTKNOW||chr(9)|| HSCRBLOODGLUCOSE||chr(9)|| HSCRBLOODGLUCOSEFASTING||chr(9)|| HBABCINTERESTRISK||chr(9)|| HBABCGATING||chr(9)|| HBABCFIRSTPERIOD||chr(9)|| HBABCAGEFIRSTCHILD||chr(9)|| HBABCRELBREASTCANCER||chr(9)|| HBABCBIOPSY||chr(9)|| HBABCNUMBIOPSY||chr(9)|| HBABCBIOPSYAH||chr(9)|| PAINMOTIVATIONLIST||chr(9)|| WTMOTIVATIONLIST||chr(9)|| PHYMOTIVATIONLIST||chr(9)|| NUTMOTIVATIONLIST||chr(9)|| SMKMOTIVATIONLIST||chr(9)|| MENSTRESSMOTIVATIONLIST||chr(9)|| INSMOTIVATIONLIST||chr(9)|| ANXMOTIVATIONLIST||chr(9)|| ALCMOTIVATIONLIST||chr(9)|| HBADRUGMOTIVATIONLIST||chr(9)|| SKNMOTIVATIONLIST||chr(9)|| INJMOTIVATIONLIST||chr(9)|| SEXMOTIVATIONLIST||chr(9)|| PAINCONFIDENCE||chr(9)|| WTCONFIDENCE||chr(9)|| PHYCONFIDENCE||chr(9)|| NUTCONFIDENCE||chr(9)|| SMKCONFIDENCE||chr(9)|| MENSTRESSCONFIDENCE||chr(9)|| INSCONFIDENCE||chr(9)|| ANXCONFIDENCE||chr(9)|| ALCCONFIDENCE||chr(9)|| DRUGCONFIDENCE||chr(9)|| SKNCONFIDENCE||chr(9)|| INJCONFIDENCE||chr(9)|| SEXCONFIDENCE||chr(9)|| WTSE_ANXIOUSANGRY||chr(9)|| WTSE_HOLIDAYVACATION||chr(9)|| WTSE_EATOUT||chr(9)|| WTSE_TIREDSTRESSED||chr(9)|| WTSE_DONTEXERCISE||chr(9)|| WTSE_LONELYBORED||chr(9)|| PHYSE_NOPARTNER||chr(9)|| PHYSE_TIRED||chr(9)|| PHYSE_NOEXERCISEREGULARLY||chr(9)|| PHYSE_NOACTIVITYENJOY||chr(9)|| PHYSE_WEATHERPOOR||chr(9)|| PHYSE_SCHEDULE||chr(9)|| NUTSE_OTHERSEATUNHEALTHY||chr(9)|| NUTSE_TOOTIREDTOPREPARE||chr(9)|| NUTSE_EATOUTFREQUENTLY||chr(9)|| NUTSE_NEEDREWARDFOOD||chr(9)|| NUTSE_AROUNDTEMPTINGFOODS||chr(9)|| NUTSE_NOTIMESHOPGROCERIES||chr(9)|| SMKSE_NEEDLIFT||chr(9)|| SMKSE_STRESSED||chr(9)|| SMKSE_ANGRY||chr(9)|| SMKSE_BORED||chr(9)|| SMKSE_CRAVING||chr(9)|| SMKSE_WEIGHT||chr(9)|| MENSTRESSSE_REDUCESTRESS||chr(9)|| MENSTRESSSE_PRACTECHNIQUES||chr(9)|| MENSTRESSSE_TIMEDEMANDS||chr(9)|| MENSTRESSSE_FINDSUPPORT||chr(9)|| MENSTRESSSE_SETLIMITS||chr(9)|| MENSTRESSSE_LEAVESITUATIONS||chr(9)|| MENDEPSE_GETHELP||chr(9)|| MENDEPSE_CARINGPROFESS||chr(9)|| MENDEPSE_KEPTCONFIDENTIAL||chr(9)|| MENDEPSE_FOLLOWTREATMENT||chr(9)|| MENDEPSE_BENEFITTREATMENT||chr(9)|| MENDEPSE_HAVESUPPORT||chr(9)|| ALCSE_DEPRESSED||chr(9)|| ALCSE_VACATION||chr(9)|| ALCSE_ANGRY||chr(9)|| ALCSE_SOCIAL||chr(9)|| ALCSE_BARPARTY||chr(9)|| ALCSE_ALONE||chr(9)|| SKNSE_USESUNSCREEN||chr(9)|| SKNSE_PROTECTSKN||chr(9)|| SKNSE_SEEKSHADE||chr(9)|| SKNSE_10TO2||chr(9)|| SKNSE_SELFCHECK||chr(9)|| SKNSE_DOCTORCHECK||chr(9)|| INJSE_BELTEVERYTIME||chr(9)|| INJSE_DRINKAVOIDDRIVE||chr(9)|| INJSE_DRIVEWITHINLIMIT||chr(9)|| INJSE_DRINKAVOIDRIDE||chr(9)|| INJSE_STOREGUNSUNLOADED||chr(9)|| INJSE_EDUCATEGUNSAFETY||chr(9)|| SEXSE_CONDOMSUBSTANCE||chr(9)|| SEXSE_CONDOMNORISK||chr(9)|| SEXSE_CONDOMDISCUSSPARTNER||chr(9)|| SEXSE_CONDOMOTHERBCONTROL||chr(9)|| SEXSE_CONDOMAROUSED||chr(9)|| SEXSE_CONDOMPURCHASE||chr(9)|| REPLACE(to_char(ALCBEHSCORE,'FM990.000'), '-1.000',NULL) ||chr(9) || REPLACE(to_char(ALCMOTOSCORE,'FM990.000'), '-1.000',NULL)||chr(9) || REPLACE(to_char(ALCSESCOREVALUE,'FM990.000'), '-1.000',NULL) ||chr(9) || REPLACE(to_char(BCINDABSFIVEYRNORISK,'FM990.000'), '-1.000',NULL) ||chr(9) || REPLACE(to_char(BCINDABSFIVEYRRISK,'FM990.000'), '-1.000',NULL) ||chr(9) || REPLACE(to_char(BCINDABSLIFETIMERISK,'FM990.000'), '-1.000',NULL) ||chr(9) || REPLACE(to_char(BCINDAVGFIVEYRRISK,'FM990.000'), '-1.000',NULL) ||chr(9) || REPLACE(to_char(BCINDAVGLIFETIMERISK,'FM990.000'), '-1.000',NULL) ||chr(9) || BEHAVIOR1NAME||chr(9)|| BEHAVIOR2NAME||chr(9)|| BEHAVIOR3NAME||chr(9)|| BEHAVIOR4NAME||chr(9)|| BEHAVIOR5NAME||chr(9)|| BEHAVIOR6NAME||chr(9)|| BEHAVIOR7NAME||chr(9)|| BEHAVIOR8NAME||chr(9)|| BEHAVIOR9NAME||chr(9)|| BEHAVIOR10NAME||chr(9)|| BMI||chr(9)|| replace(CHDRISK,'-1',null)||chr(9)|| CHDRISKPERC||chr(9)|| replace(DEPBEHSCORE,'-1.000',NULL)||chr(9)|| REPLACE(DEPMOTOSCORE,'-1.000',NULL)||chr(9)|| replace(DEPSESCOREVALUE,'-1.000',NULL)||chr(9)|| replace(DRUGBEHSCORE,'-1.000',NULL)||chr(9)|| replace(DRUGMOTOSCORE,'-1.000',NULL)||chr(9)|| replace(DRUGSESCOREVALUE,'-1.000',NULL)||chr(9)|| replace(INJBEHSCORE,'-1.000',NULL)||chr(9)|| replace(INJMOTOSCORE,'-1.000',null)||chr(9)|| replace(INJSESCOREVALUE,'-1.000',null)||chr(9)|| replace(LCPROBQUIT,'-1.000',null)||chr(9)|| replace(LCPROBSMOKE,'-1.000',null)||chr(9)|| LIFESTYLESCORE||chr(9)|| replace(MENBEHSCORE,'-1.000',null)||chr(9)|| replace(MENMOTOSCORE,'-1.000',null)||chr(9)|| replace(MENSESCOREVALUE,'-1.000',null)||chr(9)|| replace(NUTBEHSCORE,'-1.000',null)||chr(9)|| replace(NUTMOTOSCORE,'-1.000',null)||chr(9)|| replace(NUTSESCOREVALUE,'-1.000',null)||chr(9)|| replace(PHYBEHSCORE,'-1.000',null)||chr(9)|| replace(PHYMOTOSCORE,'-1.000',null)||chr(9)|| replace(PHYSESCOREVALUE,'-1.000',null)||chr(9)|| replace(SEXBEHSCORE,'-1.000',null)||chr(9)|| replace(SEXMOTOSCORE,'-1.000',null)||chr(9)|| replace(SEXSESCOREVALUE,'-1.000',null)||chr(9)|| replace(SKNBEHSCORE,'-1.000',null)||chr(9)|| replace(SKNMOTOSCORE,'-1.000',null)||chr(9)|| replace(SKNSESCOREVALUE,'-1.000',null)||chr(9)|| replace(SMKBEHSCORE,'-1.000',null)||chr(9)|| replace(SMKMOTOSCORE,'-1.000',null)||chr(9)|| replace(SMKSESCOREVALUE,'-1.000',null)||chr(9)|| replace(STRBEHSCORE,'-1.000',null)||chr(9)|| replace(STRMOTOSCORE,'-1.000',null)||chr(9)|| replace(STRSESCOREVALUE,'-1.000',null)||chr(9)|| replace(WTBEHSCORE,'-1.000',null)||chr(9)|| replace(WTMOTOSCORE,'-1.000',null)||chr(9)|| replace(WTSESCOREVALUE,'-1.000',null)||chr(9)|| CALLEVEL||chr(9)|| DISRISKAVAIL||chr(9)|| e.COUNSELOROPTIN||chr(9)|| CUSTOMQ36||chr(9)|| CUSTOMQ37||chr(9)|| CUSTOMQ38||chr(9)|| CUSTOMQ39||chr(9)|| CUSTOMQ40||chr(9)|| CUSTOMQ41||chr(9)|| CUSTOMQ42||chr(9)|| CUSTOMQ43||chr(9)|| CUSTOMQ44||chr(9)|| CUSTOMQ45||chr(9)|| BINGEEVEROVEREAT||chr(9)|| BINGEEVEROVEREATNUM||chr(9)|| BINGELOSTCONTROL||chr(9)|| BINGELOSTCONTROLNUM||chr(9)|| ENROLLDATE||chr(9)|| MEDHISTPERS_NONE||chr(9)|| MEDHISTFAM_NONE||chr(9)|| WOMPREGNANCYEDD||chr(9)|| WOMBREASTFEEDING||chr(9)|| INSSLEEPDESC_NONE||chr(9)|| NUTFOODSDONTEAT_GLUTEN||chr(9)|| NUTFOODSDONTEAT_NONE||chr(9)|| SKNSE_10TO4||chr(9)|| HSCRBARIUMENEMA||chr(9)|| HSCRPAPV2||chr(9)|| HSCRBONEDENSITY||chr(9)|| HSCRMAMMOGRAMV2||chr(9)|| HSCRBPV2 FROM DATAWAREHOUSE.MV_PARTICIPANT P, DATAWAREHOUSE.MV_EPISODES E, DATAWAREHOUSE.MV_PIECESTATUS PC, DATAWAREHOUSE.MV_CHBAALCSKINMOTORIMM S1, DATAWAREHOUSE.MV_CHBABIONUTPHYWOM S2, DATAWAREHOUSE.MV_CHBACOREDEMOPROD S3, DATAWAREHOUSE.MV_CHBAPAINANXINSLUN S4, DATAWAREHOUSE.MV_CHBAPARAMETERS S5, DATAWAREHOUSE.MV_CHBASCRSEXDRUGCAN S6, DATAWAREHOUSE.MV_CHBATOBDEPSTRESS S7, DATAWAREHOUSE.MV_SITE S, DATAWAREHOUSE.MV_DISTRIBUTOR D, DATAWAREHOUSE.MV_CUSTOMER C, DATAWAREHOUSE.MV_PROJECT PJ, DATAWAREHOUSE.MV_CORE CR WHERE P.PARTICIPANTID=E.PARTICIPANTID AND E.PARTICIPANTID=PC.PARTICIPANTID AND E.EPISODEID=PC.EPISODEID AND UPPER(PC.PIECECD) IN ('CHBAMAS','MAS') AND E.INSTRUMENTCD='chba' AND P.PDISTCD='ADHC' and P.PPROJECTCD not in ('adep','aded') AND TRUNC(PC.PUBLICATIONDT) in (SELECT EXTRACTDATE FROM DATA_ANALYSIS.EXTRACTLOADDATE) AND P.testdata is null and PC.PARTICIPANTID=S1.PARTICIPANTID(+) AND PC.EPISODEID=S1.EPISODEID(+) AND PC.PARTICIPANTID=S3.PARTICIPANTID(+) AND PC.EPISODEID=S3.EPISODEID(+) AND PC.PARTICIPANTID=S4.PARTICIPANTID(+) AND PC.EPISODEID=S4.EPISODEID(+) AND PC.PARTICIPANTID=S5.PARTICIPANTID(+) AND PC.EPISODEID=S5.EPISODEID(+) AND PC.PARTICIPANTID=S6.PARTICIPANTID(+) AND PC.EPISODEID=S6.EPISODEID(+) AND PC.PARTICIPANTID=S7.PARTICIPANTID(+) AND PC.EPISODEID=S7.EPISODEID(+) AND PC.PARTICIPANTID=S2.PARTICIPANTID(+) AND PC.EPISODEID=S2.EPISODEID(+) AND S.SITECD=P.SITECD AND D.DISTCD=P.PDISTCD AND C.CUSTOMERCD=P.CUSTOMERCD AND PJ.PROJECTCD=P.PPROJECTCD AND PC.PARTICIPANTID=CR.PARTICIPANTID(+) AND PC.EPISODEID=CR.EPISODEID(+) RATIONALE: The SQL statement with SQL_ID "13mn3gudbms19" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID 13mn3gudbms19 create table data_analysis.hba_E1participantids tablespace users as select unique(p.participantid), e.episodeid FROM DATAWAREHOUSE.MV_PARTICIPANT p, DATAWAREHOUSE.MV_EPISODES e ,DATAWAREHOUSE.MV_PIECESTATUS ps, DATAWAREHOUSE.MV_PROGRESS pg WHERE p.PARTICIPANTID = e.PARTICIPANTID and e.participantid = ps.participantid AND e.EPISODEID = ps.EPISODEID AND e.participantid = pg.participantid AND e.episodeid = pg.episodeid AND e.INSTRUMENTCD = 'hba' AND upper(piececd) like '%MAS%' AND SECTCD = 'E1ALL' AND SECTSTAT = '2' AND p.TESTDATA IS NULL AND TRUNC(STATDATE)<=trunc(sysdate-1) minus select participantid, episodeid from data_analysis.hba_eval where e1all = '2' RATIONALE: The SQL statement with SQL_ID "682vqr1fjhb17" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID 682vqr1fjhb17 SELECT firstname||','|| lastname||','|| TO_CHAR (birthdate, 'MM/DD/YYYY')||','|| TO_CHAR(publicationdt, 'MM/DD/YYYY HH:MI:SS PM')||','|| CASE WHEN ( wtheightfeet IS NOT NULL AND wtheightinches IS NOT NULL AND wtweight IS NOT NULL AND wtwaist IS NOT NULL AND ( hscrbloodglucose IS NOT NULL AND LOWER (hscrbloodglucosefasting) = 'fasting' ) AND hscrcholesterol IS NOT NULL AND hscrhdl IS NOT NULL AND hscrldl IS NOT NULL AND hscrtriglyceridev5 IS NOT NULL ) THEN 'Yes' WHEN ( hscrbloodglucose IS NULL OR LOWER (hscrbloodglucosefasting) != 'fasting' OR hscrcholesterol5yrs IN ('No', 'DontKnow') OR hscrbloodglucose3yrs IN ('No', 'DontKnow') OR hscrcholesteroldontknow = 'true' OR hscrhdldontknow = 'true' OR hscrldldontknow = 'true' OR hscrtriglyceridedontknowv5 = 'true' OR hscrbloodglucosedontknow = 'true' ) THEN 'NotCompleted' ELSE 'No' END||','|| CASE WHEN wompregnancy = 'Pregnant' THEN 'Yes' ELSE 'No' END||','|| CASE WHEN wtheightfeet IS NOT NULL THEN 'Answered' ELSE 'NotAnswered' END||','|| CASE WHEN wtheightinches IS NOT NULL THEN 'Answered' ELSE 'NotAnswered' END||','|| CASE WHEN wtweight IS NOT NULL THEN 'Answered' ELSE 'NotAnswered' END||','|| CASE WHEN wtwaist IS NOT NULL THEN 'Answered' ELSE 'NotAnswered' END||','|| CASE WHEN ( hscrbloodglucose IS NOT NULL AND LOWER (hscrbloodglucosefasting) = 'fasting' ) THEN 'Answered' WHEN ( hscrbloodglucose IS NOT NULL AND LOWER (hscrbloodglucosefasting) = 'nonfasting' ) THEN 'NotAnswered' ELSE 'NotAnswered' END||','|| CASE WHEN hscrcholesterol IS NOT NULL THEN 'Answered' ELSE 'NotAnswered' END||','|| CASE WHEN hscrhdl IS NOT NULL THEN 'Answered' ELSE 'NotAnswered' END||','|| CASE WHEN hscrldl IS NOT NULL THEN 'Answered' ELSE 'NotAnswered' END||','|| CASE WHEN hscrtriglyceridev5 IS NOT NULL THEN 'Answered' ELSE 'NotAnswered' END FROM datawarehouse.mv_participant p, datawarehouse.mv_episodes e, datawarehouse.mv_piecestatus ps, datawarehouse.mv_chbabionutphywom cbn WHERE p.participantid = e.participantid AND e.participantid = ps.participantid AND e.episodeid = ps.episodeid AND e.participantid = cbn.participantid AND e.episodeid = cbn.episodeid AND pprojectcd = 'frbd' AND UPPER (piececd) LIKE '%MAS%' AND instrumentcd = 'chba' AND poptin IN ('Forced', 'Yes') AND enrollmethodcd = 'Web' AND TRUNC (publicationdt) <= TRUNC(SYSDATE-1) AND TRUNC (publicationdt) >= TO_DATE ('01/01/2011', 'MM/DD/YYYY') and testdata is NULL RECOMMENDATION 4: Segment Tuning, 4.7% benefit (11094 seconds) ACTION: Run "Segment Advisor" on TABLE "DATA_ANALYSIS.HBA_ANALYSIS2" with object id 1423116. RELEVANT OBJECT: database object with id 1423116 ACTION: Investigate application logic involving I/O on TABLE "DATA_ANALYSIS.HBA_ANALYSIS2" with object id 1423116. RELEVANT OBJECT: database object with id 1423116 RATIONALE: The I/O usage statistics for the object are: 26 full object scans, 10425539 physical reads, 2 physical writes and 0 direct reads. RATIONALE: The SQL statement with SQL_ID "3xkvq473kgh32" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID 3xkvq473kgh32 UPDATE DATA_ANALYSIS.HBA_ANALYSIS2 SET hscrmammogram=NULL WHERE hscrmammogram<>'-2' AND (PARTICIPANTID,EPISODEID) IN ( SELECT S1.PARTICIPANTID,S1.EPISODEID FROM DATA_ANALYSIS.HBA_ANALYSIS11 s1,DATA_ANALYSIS.HBA_ANALYSIS2 S2 WHERE S2.PARTICIPANTID=S1.PARTICIPANTID AND S2.EPISODEID=S1.EPISODEID AND hscrmammogramV2<>hscrmammogram) RATIONALE: The SQL statement with SQL_ID "3m3hnmwzqjbsh" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID 3m3hnmwzqjbsh UPDATE data_analysis.hba_analysis2 SET PHYLOACTIVITIESMINUTES=null where data_analysis.IS_NUMBER(PHYLOACTIVITIESMINUTES) IS NULL RATIONALE: The SQL statement with SQL_ID "37zk8c15rq694" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID 37zk8c15rq694 UPDATE DATA_ANALYSIS.HBA_ANALYSIS2 SET HSCRBP12MONTHS=NULL WHERE HSCRBP12MONTHS<>'-2' AND (PARTICIPANTID,EPISODEID) IN ( SELECT S1.PARTICIPANTID,S1.EPISODEID FROM DATA_ANALYSIS.HBA_ANALYSIS11 s1,DATA_ANALYSIS.HBA_ANALYSIS2 S2 WHERE S2.PARTICIPANTID=S1.PARTICIPANTID AND S2.EPISODEID=S1.EPISODEID AND HSCRBPV2<>HSCRBP12MONTHS) RATIONALE: The SQL statement with SQL_ID "fvb9rkdffq45t" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID fvb9rkdffq45t UPDATE DATA_ANALYSIS.HBA_ANALYSIS2 SET HSCRBPDONTKNOW='false' where HSCRBPDONTKNOW='falso' RATIONALE: The SQL statement with SQL_ID "dj1ugu16psf20" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID dj1ugu16psf20 UPDATE data_analysis.hba_analysis2 SET PHYHIACTIVITIESHOURS=null where data_analysis.IS_NUMBER(PHYHIACTIVITIESHOURS) IS NULL RECOMMENDATION 5: Segment Tuning, 2.6% benefit (6230 seconds) ACTION: Run "Segment Advisor" on TABLE "DATA_ANALYSIS.ACTIVEHEALTHEXTRACT" with object id 1427082. RELEVANT OBJECT: database object with id 1427082 ACTION: Investigate application logic involving I/O on TABLE "DATA_ANALYSIS.ACTIVEHEALTHEXTRACT" with object id 1427082. RELEVANT OBJECT: database object with id 1427082 RATIONALE: The I/O usage statistics for the object are: 19 full object scans, 2191595 physical reads, 199 physical writes and 0 direct reads. RATIONALE: The SQL statement with SQL_ID "9hszfg1qpn247" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID 9hszfg1qpn247 DELETE FROM ACTIVEHEALTHEXTRACT WHERE SOURCESURVEYID='MOVEBASELINE' AND DATADESCRIPTION IN ( SELECT DATADESCRIPTION FROM ACTIVEHEALTHEXTRACT WHERE SOURCESURVEYID='MOVEBASELINE' MINUS SELECT UPPER(ITEMCD) FROM DD_MOVE_BASELINE_2_1) RATIONALE: The SQL statement with SQL_ID "0z4zf0813zpsm" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID 0z4zf0813zpsm DELETE FROM ACTIVEHEALTHEXTRACT WHERE SOURCESURVEYID='RELAXBASELINE' AND DATADESCRIPTION IN ( SELECT DATADESCRIPTION FROM ACTIVEHEALTHEXTRACT WHERE SOURCESURVEYID='RELAXBASELINE' MINUS SELECT UPPER(ITEMCD) FROM DD_RELAX_BASELINE_3_1) RATIONALE: The SQL statement with SQL_ID "awa4f6udm1517" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID awa4f6udm1517 DELETE FROM ACTIVEHEALTHEXTRACT WHERE SOURCESURVEYID='NOURISHBASELINE' AND DATADESCRIPTION IN ( SELECT DATADESCRIPTION FROM ACTIVEHEALTHEXTRACT WHERE SOURCESURVEYID='NOURISHBASELINE' MINUS SELECT UPPER(ITEMCD) FROM DD_NOURISH_BASELINE_3_1) RATIONALE: The SQL statement with SQL_ID "37nkcsgmcu70g" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID 37nkcsgmcu70g DELETE FROM ACTIVEHEALTHEXTRACT WHERE SOURCESURVEYID='BALANCEBASELINE' AND DATADESCRIPTION IN ( SELECT DATADESCRIPTION FROM ACTIVEHEALTHEXTRACT WHERE SOURCESURVEYID='BALANCEBASELINE' MINUS SELECT UPPER(ITEMCD) FROM DD_BALANCE_BASELINE_4_1) RATIONALE: The SQL statement with SQL_ID "3gajnm260t6tp" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID 3gajnm260t6tp SELECT DISTINCT 'D'||chr(124)|| umemberid||chr(124)|| datadescription||chr(124)|| CASE WHEN A.RESPONSECD NOT IN (SELECT dd.RESPONSECD FROM DD_ALL_ACTIVEHEALTH DD WHERE DD.SOURCESURVEYID = A.SOURCESURVEYID AND A.DATADESCRIPTION = DD.ITEMCD) THEN (SELECT DISTINCT ddd.RESPONSECD FROM DD_ALL_ACTIVEHEALTH DDD WHERE DDD.ITEMCD = A.DATADESCRIPTION AND DDD.SOURCESURVEYID = A.SOURCESURVEYID and ddd.itemcd=d.itemcd and ddd.sourcesurveyid=d.sourcesurveyid and ddd.responsecd=d.responsecd) ELSE a.RESPONSECD END||chr(124)|| a.responsecd||chr(124)|| a.SOURCESURVEYID||chr(124)|| 'HMI'||chr(124)|| TO_CHAR (RESPONSEDT, 'MM/DD/YYYY HH24:MI:SS')||chr(124)|| episodeid||chr(124) FROM ACTIVEHEALTHEXTRACT a, dd_all_activehealth d WHERE d.sourcesurveyid = a.sourcesurveyid AND d.itemcd = a.DATADESCRIPTION AND ((TRUNC (responsedt) in (SELECT EXTRACTDATE FROM DATA_ANALYSIS.EXTRACTLOADDATE)) OR (TRUNC(CREATEDT)=TRUNC(SYSDATE))) SYMPTOMS THAT LED TO THE FINDING: SYMPTOM: Wait class "User I/O" was consuming significant database time. (69% impact [165667 seconds]) FINDING 3: 18% impact (42551 seconds) ------------------------------------- The PGA was inadequately sized, causing additional I/O to temporary tablespaces to consume significant database time. RECOMMENDATION 1: DB Configuration, 5.5% benefit (13191 seconds) ACTION: Increase the size of the PGA by setting the value of parameter "pga_aggregate_target" to 728 M. ADDITIONAL INFORMATION: The value of parameter "pga_aggregate_target" was "520 M" during the analysis period. SYMPTOMS THAT LED TO THE FINDING: SYMPTOM: Wait class "User I/O" was consuming significant database time. (69% impact [165667 seconds]) FINDING 4: 18% impact (42046 seconds) ------------------------------------- The throughput of the I/O subsystem was significantly lower than expected. RECOMMENDATION 1: Host Configuration, 18% benefit (42046 seconds) ACTION: Consider increasing the throughput of the I/O subsystem. Oracle's recommended solution is to stripe all data file using the SAME methodology. You might also need to increase the number of disks for better performance. Alternatively, consider using Oracle's Automatic Storage Management solution. RATIONALE: During the analysis period, the average data files' I/O throughput was 3.9 M per second for reads and 242 K per second for writes. The average response time for single block reads was 12 milliseconds. RECOMMENDATION 2: Host Configuration, 3.8% benefit (8966 seconds) ACTION: The performance of file +ORAFILES/dwh1/data_warehouse_small1.dbf was significantly worse than other files. If striping all files using the SAME methodology is not possible, consider striping this file over multiple disks. RELEVANT OBJECT: database file "+ORAFILES/dwh1/data_warehouse_small1.dbf" RATIONALE: The average response time for single block reads for this file was 17 milliseconds. RECOMMENDATION 3: Host Configuration, 3% benefit (7153 seconds) ACTION: The performance of file +ORAFILES/dwh1/data_warehouse_small02.dbf was significantly worse than other files. If striping all files using the SAME methodology is not possible, consider striping this file over multiple disks. RELEVANT OBJECT: database file "+ORAFILES/dwh1/data_warehouse_small02.dbf" RATIONALE: The average response time for single block reads for this file was 16 milliseconds. RECOMMENDATION 4: Host Configuration, 2.2% benefit (5350 seconds) ACTION: The performance of file +ORAFILES/dwh1/data_warehouse_lrg1.dbf was significantly worse than other files. If striping all files using the SAME methodology is not possible, consider striping this file over multiple disks. RELEVANT OBJECT: database file "+ORAFILES/dwh1/data_warehouse_lrg1.dbf" RATIONALE: The average response time for single block reads for this file was 27 milliseconds. SYMPTOMS THAT LED TO THE FINDING: SYMPTOM: Wait class "User I/O" was consuming significant database time. (69% impact [165667 seconds]) FINDING 5: 13% impact (30169 seconds) ------------------------------------- SQL statements consuming significant database time were found. RECOMMENDATION 1: SQL Tuning, 6.5% benefit (15476 seconds) ACTION: Investigate the SQL statement with SQL_ID "bzc9n0r0ywcq1" for possible performance improvements. RELEVANT OBJECT: SQL statement with SQL_ID bzc9n0r0ywcq1 and PLAN_HASH 1732963614 select "Union13"."Header" "Header", "Union13"."Level1" "Level2", sum("Union13"."CYHRXMOT") "total_Union13_CYHRXMOT_auto__0", count("Union13"."CYHRXMOT") "c4", sum("Union13"."CYHRXCONF") "c5", count("Union13"."CYHRXCONF") "c6", sum("Union13"."Managing_NormalActs") "c7", count("Union13"."Managing_NormalActs") "c8", "Union13"."Accepscore_High" "Accepscore_High", sum("Union13"."Total_no_of_Participants") "Total_no_of_Participants", "Union13"."Accepscore_Low" "Accepscore_Low", "Union13"."Smoke__Yes" "Smoke__Yes", "Union13"."Smoke__No" "Smoke__No", sum("Union13"."CYHMDVISITS") "c14", count("Union13"."CYHMDVISITS") "c15", sum("Union13"."CYHERVISITS") "c16", count("Union13"."CYHERVISITS") "c17", sum("Union13"."CYHHOSPNIGHTS") "c18", count("Union13"."CYHHOSPNIGHTS") "c19", sum("Union13"."Managing_Medical_Needs") "c20", count("Union13"."Managing_Medical_Needs") "c21", sum("Union13"."Manage_Health_Motivation") "c22", count("Union13"."Manage_Health_Motivation") "c23", sum("Union13"."Manage_Health_Confidence") "c24", count("Union13"."Manage_Health_Confidence") "c25", sum("Union13"."CYHSMRATINGMNGEMOT") "c26", count("Union13"."CYHSMRATINGMNGEMOT") "c27", sum("Union13"."Accepscore") "c28", count("Union13"."Accepscore") "c29", sum("Union13"."No_of_Conditions") "c30", count("Union13"."No_of_Conditions") "c31", sum("Union13"."BMI_Average") "c32", count("Union13"."BMI_Average") "c33", sum("Union13"."Overall_Impairment") "c34", count("Union13"."Overall_Impairment") "c35", sum("Union13"."CYHPAIN") "total_Union13_CYHPAIN_auto__0", count("Union13"."CYHPAIN") "c37", sum("Union13"."CYHFATIGUE") "c38", count("Union13"."CYHFATIGUE") "c39", sum("Union13"."CYHSHORTBREATH") "c40", count("Union13"."CYHSHORTBREATH") "c41", sum("Union13"."PRSICKDAYS_NUM") "c42", count("Union13"."PRSICKDAYS_NUM") "c43", sum("Union13"."PRCYHHEALTHAFFECTACT") "c44", count("Union13"."PRCYHHEALTHAFFECTACT") "c45", sum("Union13"."DIAGNOSES_VALUE") "c46", count("Union13"."DIAGNOSES_VALUE") "c47", sum("Union13"."AGE") "total_Union13_AGE_auto__0", count("Union13"."AGE") "c49" from (select distinct 1 "Level1", 'PLK Test' "Header", avg("CAHA_ANALYSIS"."CYHRXMOT") over () "CYHRXMOT", avg("CAHA_ANALYSIS"."CYHRXCONF") over () "CYHRXCONF", avg("CAHA_ANALYSIS"."CYHSMRATINGNORMACTS") over () "Managing_NormalActs", sum(case when ("CAHA_ANALYSIS"."ACCEPTSCORE">=60) then 1 else 0 end ) over ()/nullif(count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPI SODEID") over (), 0) "Accepscore_High", count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPISODEID") over () "Total_no_of_Participants", sum(case when ("CAHA_ANALYSIS"."ACCEPTSCORE"<60) then 1 else 0 end ) over ()/nullif(count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPI SODEID") over (), 0) "Accepscore_Low", sum(case when ("CAHA_ANALYSIS"."CYHSMOKE"='Yes') then 1 else 0 end ) over ()/nullif(count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPI SODEID") over (), 0) "Smoke__Yes", sum(case when ("CAHA_ANALYSIS"."CYHSMOKE"='No') then 1 else 0 end ) over ()/nullif(count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPI SODEID") over (), 0) "Smoke__No", avg("CAHA_ANALYSIS"."CYHMDVISITS") over () "CYHMDVISITS", avg("CAHA_ANALYSIS"."CYHERVISITS") over () "CYHERVISITS", avg("CAHA_ANALYSIS"."CYHHOSPNIGHTS") over () "CYHHOSPNIGHTS", avg("CAHA_ANALYSIS"."CYHSMRATINGMEDNEEDS") over () "Managing_Medical_Needs", avg("CAHA_ANALYSIS"."CYHMOTIVATION") over () "Manage_Health_Motivation", avg("CAHA_ANALYSIS"."CYHSXMGMTSE_MANAGEACT") over () "Manage_Health_Confidence", avg("CAHA_ANALYSIS"."CYHSMRATINGMNGEMOT") over () "CYHSMRATINGMNGEMOT", avg("CAHA_ANALYSIS"."ACCEPTSCORE") over () "Accepscore", avg("CAHA_DIAGNOSES_TOTAL"."DIAGNOSES_VALUE") over () "No_of_Conditions", avg("CAHA_ANALYSIS"."BMI") over () "BMI_Average", avg("CAHA_PR_MALL"."PR_M3") over () "Overall_Impairment", avg("CAHA_ANALYSIS"."CYHPAIN") over () "CYHPAIN", avg("CAHA_ANALYSIS"."CYHFATIGUE") over () "CYHFATIGUE", avg("CAHA_ANALYSIS"."CYHSHORTBREATH") over () "CYHSHORTBREATH", avg("CAHA_PR_SICKDAYS"."PRSICKDAYS_NUM") over () "PRSICKDAYS_NUM", avg("CAHA_ANALYSIS"."PRCYHHEALTHAFFECTACT") over () "PRCYHHEALTHAFFECTACT", avg("CAHA_DIAGNOSES_TOTAL"."DIAGNOSES_VALUE") over () "DIAGNOSES_VALUE", avg("CAHA_ANALYSIS"."AGE") over () "AGE" from ((("DATA_ANALYSIS"."CAHA_ANALYSIS" "CAHA_ANALYSIS" INNER JOIN "DATA_ANALYSIS"."CAHA_DIAGNOSES_TOTAL" "CAHA_DIAGNOSES_TOTAL" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_DIAGNOSES_TOTAL"."PARTICIPANTID " and "CAHA_ANALYSIS"."EPISODEID"="CAHA_DIAGNOSES_TOTAL"."EPISODEID") INNER JOIN "DATA_ANALYSIS"."MV_CAHA_CUSTOMER_DIMENSION" "CAHA_CUSTOMER_DIMENSION" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_CUSTOMER_DIMENSION"."PARTICIPAN TID" and "CAHA_ANALYSIS"."EPISODEID"="CAHA_CUSTOMER_DIMENSION"."EPISO DEID") LEFT OUTER JOIN "DATA_ANALYSIS"."CAHA_PR_MALL" "CAHA_PR_MALL" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_PR_MALL"."PARTICIPANTID" and "CAHA_ANALYSIS"."EPISODEID"="CAHA_PR_MALL"."EPISODEID") LEFT OUTER JOIN "DATA_ANALYSIS"."CAHA_PR_SICKDAYS" "CAHA_PR_SICKDAYS" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_PR_SICKDAYS"."PARTICIPANTID" and "CAHA_ANALYSIS"."EPISODEID"="CAHA_PR_SICKDAYS"."EPISODEID" where to_char("CAHA_ANALYSIS"."PUBLICATIONDT", 'yyyy/mm/dd') between '2012/01/01' and '2012/12/31' and "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR"='HAP' and "CAHA_CUSTOMER_DIMENSION"."CUSTOMER" in ('HAP') and "CAHA_CUSTOMER_DIMENSION"."PROJECT" in ('HAP Members') union select "T0"."C0" "Level1", "T0"."C1" "Header", "T0"."C2"/nullif("T0"."C3", 0) "CYHRXMOT", "T0"."C4"/nullif("T0"."C5", 0) "CYHRXCONF", "T0"."C6"/nullif("T0"."C7", 0) "Managing_NormalActs", "T0"."C8"/nullif(sum("T0"."C9") over (), 0) "Accepscore_High", sum("T0"."C9") over () "Total_no_of_Participants", "T0"."C10"/nullif(sum("T0"."C9") over (), 0) "Accepscore_Low", "T0"."C11"/nullif(sum("T0"."C9") over (), 0) "Smoke__Yes", "T0"."C12"/nullif(sum("T0"."C9") over (), 0) "Smoke__No", "T0"."C13"/nullif("T0"."C14", 0) "CYHMDVISITS", "T0"."C15"/nullif("T0"."C16", 0) "CYHERVISITS", "T0"."C17"/nullif("T0"."C18", 0) "CYHHOSPNIGHTS", "T0"."C19"/nullif("T0"."C20", 0) "Managing_Medical_Needs", "T0"."C21"/nullif("T0"."C22", 0) "Manage_Health_Motivation", "T0"."C23"/nullif("T0"."C24", 0) "Manage_Health_Confidence", "T0"."C25"/nullif("T0"."C26", 0) "CYHSMRATINGMNGEMOT", "T0"."C27"/nullif("T0"."C28", 0) "Accepscore", "T0"."C29"/nullif("T0"."C30", 0) "No_of_Conditions", "T0"."C31"/nullif("T0"."C32", 0) "BMI_Average", "T0"."C33"/nullif("T0"."C34", 0) "Overall_Impairment", "T0"."C35"/nullif("T0"."C36", 0) "CYHPAIN", "T0"."C37"/nullif("T0"."C38", 0) "CYHFATIGUE", "T0"."C39"/nullif("T0"."C40", 0) "CYHSHORTBREATH", "T0"."C41"/nullif("T0"."C42", 0) "PRSICKDAYS_NUM", "T0"."C43"/nullif("T0"."C44", 0) "PRCYHHEALTHAFFECTACT", "T0"."C29"/nullif("T0"."C30", 0) "DIAGNOSES_VALUE", "T0"."C45"/nullif("T0"."C46", 0) "AGE" from (select 2 "C0", "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR" "C1", sum("CAHA_ANALYSIS"."CYHRXMOT") "C2", count("CAHA_ANALYSIS"."CYHRXMOT") "C3", sum("CAHA_ANALYSIS"."CYHRXCONF") "C4", count("CAHA_ANALYSIS"."CYHRXCONF") "C5", sum("CAHA_ANALYSIS"."CYHSMRATINGNORMACTS") "C6", count("CAHA_ANALYSIS"."CYHSMRATINGNORMACTS") "C7", sum(case when "CAHA_ANALYSIS"."ACCEPTSCORE">=60 then 1 else 0 end ) "C8", count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPISODEID") "C9", sum(case when "CAHA_ANALYSIS"."ACCEPTSCORE"<60 then 1 else 0 end ) "C10", sum(case when "CAHA_ANALYSIS"."CYHSMOKE"='Yes' then 1 else 0 end ) "C11", sum(case when "CAHA_ANALYSIS"."CYHSMOKE"='No' then 1 else 0 end ) "C12", sum("CAHA_ANALYSIS"."CYHMDVISITS") "C13", count("CAHA_ANALYSIS"."CYHMDVISITS") "C14", sum("CAHA_ANALYSIS"."CYHERVISITS") "C15", count("CAHA_ANALYSIS"."CYHERVISITS") "C16", sum("CAHA_ANALYSIS"."CYHHOSPNIGHTS") "C17", count("CAHA_ANALYSIS"."CYHHOSPNIGHTS") "C18", sum("CAHA_ANALYSIS"."CYHSMRATINGMEDNEEDS") "C19", count("CAHA_ANALYSIS"."CYHSMRATINGMEDNEEDS") "C20", sum("CAHA_ANALYSIS"."CYHMOTIVATION") "C21", count("CAHA_ANALYSIS"."CYHMOTIVATION") "C22", sum("CAHA_ANALYSIS"."CYHSXMGMTSE_MANAGEACT") "C23", count("CAHA_ANALYSIS"."CYHSXMGMTSE_MANAGEACT") "C24", sum("CAHA_ANALYSIS"."CYHSMRATINGMNGEMOT") "C25", count("CAHA_ANALYSIS"."CYHSMRATINGMNGEMOT") "C26", sum("CAHA_ANALYSIS"."ACCEPTSCORE") "C27", count("CAHA_ANALYSIS"."ACCEPTSCORE") "C28", sum("CAHA_DIAGNOSES_TOTAL"."DIAGNOSES_VALUE") "C29", count("CAHA_DIAGNOSES_TOTAL"."DIAGNOSES_VALUE") "C30", sum("CAHA_ANALYSIS"."BMI") "C31", count("CAHA_ANALYSIS"."BMI") "C32", sum("CAHA_PR_MALL"."PR_M3") "C33", count("CAHA_PR_MALL"."PR_M3") "C34", sum("CAHA_ANALYSIS"."CYHPAIN") "C35", count("CAHA_ANALYSIS"."CYHPAIN") "C36", sum("CAHA_ANALYSIS"."CYHFATIGUE") "C37", count("CAHA_ANALYSIS"."CYHFATIGUE") "C38", sum("CAHA_ANALYSIS"."CYHSHORTBREATH") "C39", count("CAHA_ANALYSIS"."CYHSHORTBREATH") "C40", sum("CAHA_PR_SICKDAYS"."PRSICKDAYS_NUM") "C41", count("CAHA_PR_SICKDAYS"."PRSICKDAYS_NUM") "C42", sum("CAHA_ANALYSIS"."PRCYHHEALTHAFFECTACT") "C43", count("CAHA_ANALYSIS"."PRCYHHEALTHAFFECTACT") "C44", sum("CAHA_ANALYSIS"."AGE") "C45", count("CAHA_ANALYSIS"."AGE") "C46" from ((("DATA_ANALYSIS"."CAHA_ANALYSIS" "CAHA_ANALYSIS" INNER JOIN "DATA_ANALYSIS"."CAHA_DIAGNOSES_TOTAL" "CAHA_DIAGNOSES_TOTAL" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_DIAGNOSES_TOTAL"."PARTICIPANTID " and "CAHA_ANALYSIS"."EPISODEID"="CAHA_DIAGNOSES_TOTAL"."EPISODEID") INNER JOIN "DATA_ANALYSIS"."MV_CAHA_CUSTOMER_DIMENSION" "CAHA_CUSTOMER_DIMENSION" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_CUSTOMER_DIMENSION"."PARTICIPAN TID" and "CAHA_ANALYSIS"."EPISODEID"="CAHA_CUSTOMER_DIMENSION"."EPISO DEID") LEFT OUTER JOIN "DATA_ANALYSIS"."CAHA_PR_MALL" "CAHA_PR_MALL" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_PR_MALL"."PARTICIPANTID" and "CAHA_ANALYSIS"."EPISODEID"="CAHA_PR_MALL"."EPISODEID") LEFT OUTER JOIN "DATA_ANALYSIS"."CAHA_PR_SICKDAYS" "CAHA_PR_SICKDAYS" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_PR_SICKDAYS"."PARTICIPANTID" and "CAHA_ANALYSIS"."EPISODEID"="CAHA_PR_SICKDAYS"."EPISODEID" where to_char("CAHA_ANALYSIS"."PUBLICATIONDT", 'yyyy/mm/dd') between '2012/01/01' and '2012/12/31' and "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR"='HAP' group by "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR") "T0" union select distinct 3 "Level1", 'HealthMedia ' "Header", avg("CAHA_ANALYSIS"."CYHRXMOT") over () "CYHRXMOT", avg("CAHA_ANALYSIS"."CYHRXCONF") over () "CYHRXCONF", avg("CAHA_ANALYSIS"."CYHSMRATINGNORMACTS") over () "Managing_NormalActs", sum(case when ("CAHA_ANALYSIS"."ACCEPTSCORE">=60) then 1 else 0 end ) over ()/nullif(count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPI SODEID") over (), 0) "Accepscore_High", count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPISODEID") over () "Total_no_of_Participants", sum(case when ("CAHA_ANALYSIS"."ACCEPTSCORE"<60) then 1 else 0 end ) over ()/nullif(count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPI SODEID") over (), 0) "Accepscore_Low", sum(case when ("CAHA_ANALYSIS"."CYHSMOKE"='Yes') then 1 else 0 end ) over ()/nullif(count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPI SODEID") over (), 0) "Smoke__Yes", sum(case when ("CAHA_ANALYSIS"."CYHSMOKE"='No') then 1 else 0 end ) over ()/nullif(count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPI SODEID") over (), 0) "Smoke__No", avg("CAHA_ANALYSIS"."CYHMDVISITS") over () "CYHMDVISITS", avg("CAHA_ANALYSIS"."CYHERVISITS") over () "CYHERVISITS", avg("CAHA_ANALYSIS"."CYHHOSPNIGHTS") over () "CYHHOSPNIGHTS", avg("CAHA_ANALYSIS"."CYHSMRATINGMEDNEEDS") over () "Managing_Medical_Needs", avg("CAHA_ANALYSIS"."CYHMOTIVATION") over () "Manage_Health_Motivation", avg("CAHA_ANALYSIS"."CYHSXMGMTSE_MANAGEACT") over () "Manage_Health_Confidence", avg("CAHA_ANALYSIS"."CYHSMRATINGMNGEMOT") over () "CYHSMRATINGMNGEMOT", avg("CAHA_ANALYSIS"."ACCEPTSCORE") over () "Accepscore", avg("CAHA_DIAGNOSES_TOTAL"."DIAGNOSES_VALUE") over () "No_of_Conditions", avg("CAHA_ANALYSIS"."BMI") over () "BMI_Average", avg("CAHA_PR_MALL"."PR_M3") over () "Overall_Impairment", avg("CAHA_ANALYSIS"."CYHPAIN") over () "CYHPAIN", avg("CAHA_ANALYSIS"."CYHFATIGUE") over () "CYHFATIGUE", avg("CAHA_ANALYSIS"."CYHSHORTBREATH") over () "CYHSHORTBREATH", avg("CAHA_PR_SICKDAYS"."PRSICKDAYS_NUM") over () "PRSICKDAYS_NUM", avg("CAHA_ANALYSIS"."PRCYHHEALTHAFFECTACT") over () "PRCYHHEALTHAFFECTACT", avg("CAHA_DIAGNOSES_TOTAL"."DIAGNOSES_VALUE") over () "DIAGNOSES_VALUE", avg("CAHA_ANALYSIS"."AGE") over () "AGE" from (("DATA_ANALYSIS"."CAHA_ANALYSIS" "CAHA_ANALYSIS" INNER JOIN "DATA_ANALYSIS"."CAHA_DIAGNOSES_TOTAL" "CAHA_DIAGNOSES_TOTAL" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_DIAGNOSES_TOTAL"."PARTICIPANTID " and "CAHA_ANALYSIS"."EPISODEID"="CAHA_DIAGNOSES_TOTAL"."EPISODEID") LEFT OUTER JOIN "DATA_ANALYSIS"."CAHA_PR_MALL" "CAHA_PR_MALL" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_PR_MALL"."PARTICIPANTID" and "CAHA_ANALYSIS"."EPISODEID"="CAHA_PR_MALL"."EPISODEID") LEFT OUTER JOIN "DATA_ANALYSIS"."CAHA_PR_SICKDAYS" "CAHA_PR_SICKDAYS" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_PR_SICKDAYS"."PARTICIPANTID" and "CAHA_ANALYSIS"."EPISODEID"="CAHA_PR_SICKDAYS"."EPISODEID" where to_char("CAHA_ANALYSIS"."PUBLICATIONDT", 'yyyy/mm/dd') between '2012/01/01' and '2012/12/31') "Union13" where case when ('3 Column'='2 Column' and "Union13"."Level1"=2) then 'No' else 'Yes' end ='Yes' group by "Union13"."Header", "Union13"."Level1", "Union13"."Accepscore_High", "Union13"."Accepscore_Low", "Union13"."Smoke__Yes", "Union13"."Smoke__No" RATIONALE: SQL statement with SQL_ID "bzc9n0r0ywcq1" was executed 50 times and had an average elapsed time of 100 seconds. RATIONALE: At least one execution of the statement ran in parallel. RATIONALE: Waiting for event "PX Deq: reap credit" in wait class "Other" accounted for 68% of the database time spent in processing the SQL statement with SQL_ID "bzc9n0r0ywcq1". RATIONALE: Waiting for event "IPC send completion sync" in wait class "Other" accounted for 17% of the database time spent in processing the SQL statement with SQL_ID "bzc9n0r0ywcq1". RATIONALE: Waiting for event "PX Deq Credit: send blkd" in wait class "Other" accounted for 9% of the database time spent in processing the SQL statement with SQL_ID "bzc9n0r0ywcq1". RECOMMENDATION 2: SQL Tuning, 5.8% benefit (13811 seconds) ACTION: Tune the PL/SQL block with SQL_ID "29cdzrv72amja". Refer to the "Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide and Reference" RELEVANT OBJECT: SQL statement with SQL_ID 29cdzrv72amja BEGIN "DATA_MART"."PROC_CONCURRENCY_NAVISTAR"( :HBA_CONC_CURSOR2, :P0, :P1, :P2, :P3, :P4, :P5, :P6, :P7, :P8, :P9, :P10 ); END; RATIONALE: SQL statement with SQL_ID "29cdzrv72amja" was executed 6 times and had an average elapsed time of 2301 seconds. RECOMMENDATION 3: SQL Tuning, 5.5% benefit (13232 seconds) ACTION: Investigate the SQL statement with SQL_ID "86zzu2d0dcgfq" for possible performance improvements. RELEVANT OBJECT: SQL statement with SQL_ID 86zzu2d0dcgfq and PLAN_HASH 3456060061 select distinct "Union13"."Header" "Header", "Union13"."Level1" "Level2", min(case when '3 Column'='2 Column' and "Union13"."Level1"=2 then 'No' else 'Yes' end ) over (partition by "Union13"."Header", "Union13"."Level1") "C_2_Column_Filter" from (select distinct 1 "Level1", 'PLK Test' "Header", avg("CAHA_ANALYSIS"."CYHRXMOT") over () "CYHRXMOT", avg("CAHA_ANALYSIS"."CYHRXCONF") over () "CYHRXCONF", avg("CAHA_ANALYSIS"."CYHSMRATINGNORMACTS") over () "Managing_NormalActs", sum(case when ("CAHA_ANALYSIS"."ACCEPTSCORE">=60) then 1 else 0 end ) over ()/nullif(count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPI SODEID") over (), 0) "Accepscore_High", count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPISODEID") over () "Total_no_of_Participants", sum(case when ("CAHA_ANALYSIS"."ACCEPTSCORE"<60) then 1 else 0 end ) over ()/nullif(count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPI SODEID") over (), 0) "Accepscore_Low", sum(case when ("CAHA_ANALYSIS"."CYHSMOKE"='Yes') then 1 else 0 end ) over ()/nullif(count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPI SODEID") over (), 0) "Smoke__Yes", sum(case when ("CAHA_ANALYSIS"."CYHSMOKE"='No') then 1 else 0 end ) over ()/nullif(count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPI SODEID") over (), 0) "Smoke__No", avg("CAHA_ANALYSIS"."CYHMDVISITS") over () "CYHMDVISITS", avg("CAHA_ANALYSIS"."CYHERVISITS") over () "CYHERVISITS", avg("CAHA_ANALYSIS"."CYHHOSPNIGHTS") over () "CYHHOSPNIGHTS", avg("CAHA_ANALYSIS"."CYHSMRATINGMEDNEEDS") over () "Managing_Medical_Needs", avg("CAHA_ANALYSIS"."CYHMOTIVATION") over () "Manage_Health_Motivation", avg("CAHA_ANALYSIS"."CYHSXMGMTSE_MANAGEACT") over () "Manage_Health_Confidence", avg("CAHA_ANALYSIS"."CYHSMRATINGMNGEMOT") over () "CYHSMRATINGMNGEMOT", avg("CAHA_ANALYSIS"."ACCEPTSCORE") over () "Accepscore", avg("CAHA_DIAGNOSES_TOTAL"."DIAGNOSES_VALUE") over () "No_of_Conditions", avg("CAHA_ANALYSIS"."BMI") over () "BMI_Average", avg("CAHA_PR_MALL"."PR_M3") over () "Overall_Impairment", avg("CAHA_ANALYSIS"."CYHPAIN") over () "CYHPAIN", avg("CAHA_ANALYSIS"."CYHFATIGUE") over () "CYHFATIGUE", avg("CAHA_ANALYSIS"."CYHSHORTBREATH") over () "CYHSHORTBREATH", avg("CAHA_PR_SICKDAYS"."PRSICKDAYS_NUM") over () "PRSICKDAYS_NUM", avg("CAHA_ANALYSIS"."PRCYHHEALTHAFFECTACT") over () "PRCYHHEALTHAFFECTACT", avg("CAHA_DIAGNOSES_TOTAL"."DIAGNOSES_VALUE") over () "DIAGNOSES_VALUE", avg("CAHA_ANALYSIS"."AGE") over () "AGE" from ((("DATA_ANALYSIS"."CAHA_ANALYSIS" "CAHA_ANALYSIS" INNER JOIN "DATA_ANALYSIS"."CAHA_DIAGNOSES_TOTAL" "CAHA_DIAGNOSES_TOTAL" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_DIAGNOSES_TOTAL"."PARTICIPANTID " and "CAHA_ANALYSIS"."EPISODEID"="CAHA_DIAGNOSES_TOTAL"."EPISODEID") INNER JOIN "DATA_ANALYSIS"."MV_CAHA_CUSTOMER_DIMENSION" "CAHA_CUSTOMER_DIMENSION" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_CUSTOMER_DIMENSION"."PARTICIPAN TID" and "CAHA_ANALYSIS"."EPISODEID"="CAHA_CUSTOMER_DIMENSION"."EPISO DEID") LEFT OUTER JOIN "DATA_ANALYSIS"."CAHA_PR_MALL" "CAHA_PR_MALL" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_PR_MALL"."PARTICIPANTID" and "CAHA_ANALYSIS"."EPISODEID"="CAHA_PR_MALL"."EPISODEID") LEFT OUTER JOIN "DATA_ANALYSIS"."CAHA_PR_SICKDAYS" "CAHA_PR_SICKDAYS" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_PR_SICKDAYS"."PARTICIPANTID" and "CAHA_ANALYSIS"."EPISODEID"="CAHA_PR_SICKDAYS"."EPISODEID" where to_char("CAHA_ANALYSIS"."PUBLICATIONDT", 'yyyy/mm/dd') between '2012/01/01' and '2012/12/31' and "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR"='HAP' and "CAHA_CUSTOMER_DIMENSION"."CUSTOMER" in ('HAP') and "CAHA_CUSTOMER_DIMENSION"."PROJECT" in ('HAP Members') union select "T0"."C0" "Level1", "T0"."C1" "Header", "T0"."C2"/nullif("T0"."C3", 0) "CYHRXMOT", "T0"."C4"/nullif("T0"."C5", 0) "CYHRXCONF", "T0"."C6"/nullif("T0"."C7", 0) "Managing_NormalActs", "T0"."C8"/nullif(sum("T0"."C9") over (), 0) "Accepscore_High", sum("T0"."C9") over () "Total_no_of_Participants", "T0"."C10"/nullif(sum("T0"."C9") over (), 0) "Accepscore_Low", "T0"."C11"/nullif(sum("T0"."C9") over (), 0) "Smoke__Yes", "T0"."C12"/nullif(sum("T0"."C9") over (), 0) "Smoke__No", "T0"."C13"/nullif("T0"."C14", 0) "CYHMDVISITS", "T0"."C15"/nullif("T0"."C16", 0) "CYHERVISITS", "T0"."C17"/nullif("T0"."C18", 0) "CYHHOSPNIGHTS", "T0"."C19"/nullif("T0"."C20", 0) "Managing_Medical_Needs", "T0"."C21"/nullif("T0"."C22", 0) "Manage_Health_Motivation", "T0"."C23"/nullif("T0"."C24", 0) "Manage_Health_Confidence", "T0"."C25"/nullif("T0"."C26", 0) "CYHSMRATINGMNGEMOT", "T0"."C27"/nullif("T0"."C28", 0) "Accepscore", "T0"."C29"/nullif("T0"."C30", 0) "No_of_Conditions", "T0"."C31"/nullif("T0"."C32", 0) "BMI_Average", "T0"."C33"/nullif("T0"."C34", 0) "Overall_Impairment", "T0"."C35"/nullif("T0"."C36", 0) "CYHPAIN", "T0"."C37"/nullif("T0"."C38", 0) "CYHFATIGUE", "T0"."C39"/nullif("T0"."C40", 0) "CYHSHORTBREATH", "T0"."C41"/nullif("T0"."C42", 0) "PRSICKDAYS_NUM", "T0"."C43"/nullif("T0"."C44", 0) "PRCYHHEALTHAFFECTACT", "T0"."C29"/nullif("T0"."C30", 0) "DIAGNOSES_VALUE", "T0"."C45"/nullif("T0"."C46", 0) "AGE" from (select 2 "C0", "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR" "C1", sum("CAHA_ANALYSIS"."CYHRXMOT") "C2", count("CAHA_ANALYSIS"."CYHRXMOT") "C3", sum("CAHA_ANALYSIS"."CYHRXCONF") "C4", count("CAHA_ANALYSIS"."CYHRXCONF") "C5", sum("CAHA_ANALYSIS"."CYHSMRATINGNORMACTS") "C6", count("CAHA_ANALYSIS"."CYHSMRATINGNORMACTS") "C7", sum(case when "CAHA_ANALYSIS"."ACCEPTSCORE">=60 then 1 else 0 end ) "C8", count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPISODEID") "C9", sum(case when "CAHA_ANALYSIS"."ACCEPTSCORE"<60 then 1 else 0 end ) "C10", sum(case when "CAHA_ANALYSIS"."CYHSMOKE"='Yes' then 1 else 0 end ) "C11", sum(case when "CAHA_ANALYSIS"."CYHSMOKE"='No' then 1 else 0 end ) "C12", sum("CAHA_ANALYSIS"."CYHMDVISITS") "C13", count("CAHA_ANALYSIS"."CYHMDVISITS") "C14", sum("CAHA_ANALYSIS"."CYHERVISITS") "C15", count("CAHA_ANALYSIS"."CYHERVISITS") "C16", sum("CAHA_ANALYSIS"."CYHHOSPNIGHTS") "C17", count("CAHA_ANALYSIS"."CYHHOSPNIGHTS") "C18", sum("CAHA_ANALYSIS"."CYHSMRATINGMEDNEEDS") "C19", count("CAHA_ANALYSIS"."CYHSMRATINGMEDNEEDS") "C20", sum("CAHA_ANALYSIS"."CYHMOTIVATION") "C21", count("CAHA_ANALYSIS"."CYHMOTIVATION") "C22", sum("CAHA_ANALYSIS"."CYHSXMGMTSE_MANAGEACT") "C23", count("CAHA_ANALYSIS"."CYHSXMGMTSE_MANAGEACT") "C24", sum("CAHA_ANALYSIS"."CYHSMRATINGMNGEMOT") "C25", count("CAHA_ANALYSIS"."CYHSMRATINGMNGEMOT") "C26", sum("CAHA_ANALYSIS"."ACCEPTSCORE") "C27", count("CAHA_ANALYSIS"."ACCEPTSCORE") "C28", sum("CAHA_DIAGNOSES_TOTAL"."DIAGNOSES_VALUE") "C29", count("CAHA_DIAGNOSES_TOTAL"."DIAGNOSES_VALUE") "C30", sum("CAHA_ANALYSIS"."BMI") "C31", count("CAHA_ANALYSIS"."BMI") "C32", sum("CAHA_PR_MALL"."PR_M3") "C33", count("CAHA_PR_MALL"."PR_M3") "C34", sum("CAHA_ANALYSIS"."CYHPAIN") "C35", count("CAHA_ANALYSIS"."CYHPAIN") "C36", sum("CAHA_ANALYSIS"."CYHFATIGUE") "C37", count("CAHA_ANALYSIS"."CYHFATIGUE") "C38", sum("CAHA_ANALYSIS"."CYHSHORTBREATH") "C39", count("CAHA_ANALYSIS"."CYHSHORTBREATH") "C40", sum("CAHA_PR_SICKDAYS"."PRSICKDAYS_NUM") "C41", count("CAHA_PR_SICKDAYS"."PRSICKDAYS_NUM") "C42", sum("CAHA_ANALYSIS"."PRCYHHEALTHAFFECTACT") "C43", count("CAHA_ANALYSIS"."PRCYHHEALTHAFFECTACT") "C44", sum("CAHA_ANALYSIS"."AGE") "C45", count("CAHA_ANALYSIS"."AGE") "C46" from ((("DATA_ANALYSIS"."CAHA_ANALYSIS" "CAHA_ANALYSIS" INNER JOIN "DATA_ANALYSIS"."CAHA_DIAGNOSES_TOTAL" "CAHA_DIAGNOSES_TOTAL" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_DIAGNOSES_TOTAL"."PARTICIPANTID " and "CAHA_ANALYSIS"."EPISODEID"="CAHA_DIAGNOSES_TOTAL"."EPISODEID") INNER JOIN "DATA_ANALYSIS"."MV_CAHA_CUSTOMER_DIMENSION" "CAHA_CUSTOMER_DIMENSION" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_CUSTOMER_DIMENSION"."PARTICIPAN TID" and "CAHA_ANALYSIS"."EPISODEID"="CAHA_CUSTOMER_DIMENSION"."EPISO DEID") LEFT OUTER JOIN "DATA_ANALYSIS"."CAHA_PR_MALL" "CAHA_PR_MALL" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_PR_MALL"."PARTICIPANTID" and "CAHA_ANALYSIS"."EPISODEID"="CAHA_PR_MALL"."EPISODEID") LEFT OUTER JOIN "DATA_ANALYSIS"."CAHA_PR_SICKDAYS" "CAHA_PR_SICKDAYS" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_PR_SICKDAYS"."PARTICIPANTID" and "CAHA_ANALYSIS"."EPISODEID"="CAHA_PR_SICKDAYS"."EPISODEID" where to_char("CAHA_ANALYSIS"."PUBLICATIONDT", 'yyyy/mm/dd') between '2012/01/01' and '2012/12/31' and "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR"='HAP' group by "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR") "T0" union select distinct 3 "Level1", 'HealthMedia ' "Header", avg("CAHA_ANALYSIS"."CYHRXMOT") over () "CYHRXMOT", avg("CAHA_ANALYSIS"."CYHRXCONF") over () "CYHRXCONF", avg("CAHA_ANALYSIS"."CYHSMRATINGNORMACTS") over () "Managing_NormalActs", sum(case when ("CAHA_ANALYSIS"."ACCEPTSCORE">=60) then 1 else 0 end ) over ()/nullif(count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPI SODEID") over (), 0) "Accepscore_High", count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPISODEID") over () "Total_no_of_Participants", sum(case when ("CAHA_ANALYSIS"."ACCEPTSCORE"<60) then 1 else 0 end ) over ()/nullif(count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPI SODEID") over (), 0) "Accepscore_Low", sum(case when ("CAHA_ANALYSIS"."CYHSMOKE"='Yes') then 1 else 0 end ) over ()/nullif(count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPI SODEID") over (), 0) "Smoke__Yes", sum(case when ("CAHA_ANALYSIS"."CYHSMOKE"='No') then 1 else 0 end ) over ()/nullif(count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPI SODEID") over (), 0) "Smoke__No", avg("CAHA_ANALYSIS"."CYHMDVISITS") over () "CYHMDVISITS", avg("CAHA_ANALYSIS"."CYHERVISITS") over () "CYHERVISITS", avg("CAHA_ANALYSIS"."CYHHOSPNIGHTS") over () "CYHHOSPNIGHTS", avg("CAHA_ANALYSIS"."CYHSMRATINGMEDNEEDS") over () "Managing_Medical_Needs", avg("CAHA_ANALYSIS"."CYHMOTIVATION") over () "Manage_Health_Motivation", avg("CAHA_ANALYSIS"."CYHSXMGMTSE_MANAGEACT") over () "Manage_Health_Confidence", avg("CAHA_ANALYSIS"."CYHSMRATINGMNGEMOT") over () "CYHSMRATINGMNGEMOT", avg("CAHA_ANALYSIS"."ACCEPTSCORE") over () "Accepscore", avg("CAHA_DIAGNOSES_TOTAL"."DIAGNOSES_VALUE") over () "No_of_Conditions", avg("CAHA_ANALYSIS"."BMI") over () "BMI_Average", avg("CAHA_PR_MALL"."PR_M3") over () "Overall_Impairment", avg("CAHA_ANALYSIS"."CYHPAIN") over () "CYHPAIN", avg("CAHA_ANALYSIS"."CYHFATIGUE") over () "CYHFATIGUE", avg("CAHA_ANALYSIS"."CYHSHORTBREATH") over () "CYHSHORTBREATH", avg("CAHA_PR_SICKDAYS"."PRSICKDAYS_NUM") over () "PRSICKDAYS_NUM", avg("CAHA_ANALYSIS"."PRCYHHEALTHAFFECTACT") over () "PRCYHHEALTHAFFECTACT", avg("CAHA_DIAGNOSES_TOTAL"."DIAGNOSES_VALUE") over () "DIAGNOSES_VALUE", avg("CAHA_ANALYSIS"."AGE") over () "AGE" from (("DATA_ANALYSIS"."CAHA_ANALYSIS" "CAHA_ANALYSIS" INNER JOIN "DATA_ANALYSIS"."CAHA_DIAGNOSES_TOTAL" "CAHA_DIAGNOSES_TOTAL" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_DIAGNOSES_TOTAL"."PARTICIPANTID " and "CAHA_ANALYSIS"."EPISODEID"="CAHA_DIAGNOSES_TOTAL"."EPISODEID") LEFT OUTER JOIN "DATA_ANALYSIS"."CAHA_PR_MALL" "CAHA_PR_MALL" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_PR_MALL"."PARTICIPANTID" and "CAHA_ANALYSIS"."EPISODEID"="CAHA_PR_MALL"."EPISODEID") LEFT OUTER JOIN "DATA_ANALYSIS"."CAHA_PR_SICKDAYS" "CAHA_PR_SICKDAYS" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_PR_SICKDAYS"."PARTICIPANTID" and "CAHA_ANALYSIS"."EPISODEID"="CAHA_PR_SICKDAYS"."EPISODEID" where to_char("CAHA_ANALYSIS"."PUBLICATIONDT", 'yyyy/mm/dd') between '2012/01/01' and '2012/12/31') "Union13" where case when ('3 Column'='2 Column' and "Union13"."Level1"=2) then 'No' else 'Yes' end ='Yes' order by "Level2" asc nulls last RATIONALE: SQL statement with SQL_ID "86zzu2d0dcgfq" was executed 50 times and had an average elapsed time of 101 seconds. RATIONALE: At least one execution of the statement ran in parallel. RATIONALE: Waiting for event "PX Deq: reap credit" in wait class "Other" accounted for 57% of the database time spent in processing the SQL statement with SQL_ID "86zzu2d0dcgfq". RATIONALE: Waiting for event "PX Deq Credit: send blkd" in wait class "Other" accounted for 14% of the database time spent in processing the SQL statement with SQL_ID "86zzu2d0dcgfq". RATIONALE: Waiting for event "IPC send completion sync" in wait class "Other" accounted for 11% of the database time spent in processing the SQL statement with SQL_ID "86zzu2d0dcgfq". RECOMMENDATION 4: SQL Tuning, 2.7% benefit (6417 seconds) ACTION: Investigate the SQL statement with SQL_ID "b6usrg82hwsa3" for possible performance improvements. RELEVANT OBJECT: SQL statement with SQL_ID b6usrg82hwsa3 call dbms_stats.gather_database_stats_job_proc ( ) RATIONALE: Waiting for event "db file sequential read" in wait class "User I/O" accounted for 1% of the database time spent in processing the SQL statement with SQL_ID "b6usrg82hwsa3". FINDING 6: 5.5% impact (13091 seconds) -------------------------------------- PL/SQL execution consumed significant database time. RECOMMENDATION 1: SQL Tuning, 3% benefit (7050 seconds) ACTION: Investigate the SQL statement with SQL_ID "6mcpb06rctk0x" for possible performance improvements. RELEVANT OBJECT: SQL statement with SQL_ID 6mcpb06rctk0x call dbms_space.auto_space_advisor_job_proc ( ) RECOMMENDATION 2: SQL Tuning, 2.6% benefit (6214 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "8szmwam7fysa3". RELEVANT OBJECT: SQL statement with SQL_ID 8szmwam7fysa3 and PLAN_HASH 2976124318 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 163 times and had an average elapsed time of 37 seconds. RATIONALE: Average time spent in PL/SQL execution was 37 seconds. FINDING 7: 3.9% impact (9222 seconds) ------------------------------------- Wait event "IPC send completion sync" in wait class "Other" was consuming significant database time. RECOMMENDATION 1: Application Analysis, 3.9% benefit (9222 seconds) ACTION: Investigate the cause for high "IPC send completion sync" waits. Refer to Oracle's "Database Reference" for the description of this wait event. Use given SQL for further investigation. RATIONALE: The SQL statement with SQL_ID "bzc9n0r0ywcq1" was found waiting for "IPC send completion sync" wait event. RELEVANT OBJECT: SQL statement with SQL_ID bzc9n0r0ywcq1 select "Union13"."Header" "Header", "Union13"."Level1" "Level2", sum("Union13"."CYHRXMOT") "total_Union13_CYHRXMOT_auto__0", count("Union13"."CYHRXMOT") "c4", sum("Union13"."CYHRXCONF") "c5", count("Union13"."CYHRXCONF") "c6", sum("Union13"."Managing_NormalActs") "c7", count("Union13"."Managing_NormalActs") "c8", "Union13"."Accepscore_High" "Accepscore_High", sum("Union13"."Total_no_of_Participants") "Total_no_of_Participants", "Union13"."Accepscore_Low" "Accepscore_Low", "Union13"."Smoke__Yes" "Smoke__Yes", "Union13"."Smoke__No" "Smoke__No", sum("Union13"."CYHMDVISITS") "c14", count("Union13"."CYHMDVISITS") "c15", sum("Union13"."CYHERVISITS") "c16", count("Union13"."CYHERVISITS") "c17", sum("Union13"."CYHHOSPNIGHTS") "c18", count("Union13"."CYHHOSPNIGHTS") "c19", sum("Union13"."Managing_Medical_Needs") "c20", count("Union13"."Managing_Medical_Needs") "c21", sum("Union13"."Manage_Health_Motivation") "c22", count("Union13"."Manage_Health_Motivation") "c23", sum("Union13"."Manage_Health_Confidence") "c24", count("Union13"."Manage_Health_Confidence") "c25", sum("Union13"."CYHSMRATINGMNGEMOT") "c26", count("Union13"."CYHSMRATINGMNGEMOT") "c27", sum("Union13"."Accepscore") "c28", count("Union13"."Accepscore") "c29", sum("Union13"."No_of_Conditions") "c30", count("Union13"."No_of_Conditions") "c31", sum("Union13"."BMI_Average") "c32", count("Union13"."BMI_Average") "c33", sum("Union13"."Overall_Impairment") "c34", count("Union13"."Overall_Impairment") "c35", sum("Union13"."CYHPAIN") "total_Union13_CYHPAIN_auto__0", count("Union13"."CYHPAIN") "c37", sum("Union13"."CYHFATIGUE") "c38", count("Union13"."CYHFATIGUE") "c39", sum("Union13"."CYHSHORTBREATH") "c40", count("Union13"."CYHSHORTBREATH") "c41", sum("Union13"."PRSICKDAYS_NUM") "c42", count("Union13"."PRSICKDAYS_NUM") "c43", sum("Union13"."PRCYHHEALTHAFFECTACT") "c44", count("Union13"."PRCYHHEALTHAFFECTACT") "c45", sum("Union13"."DIAGNOSES_VALUE") "c46", count("Union13"."DIAGNOSES_VALUE") "c47", sum("Union13"."AGE") "total_Union13_AGE_auto__0", count("Union13"."AGE") "c49" from (select distinct 1 "Level1", 'PLK Test' "Header", avg("CAHA_ANALYSIS"."CYHRXMOT") over () "CYHRXMOT", avg("CAHA_ANALYSIS"."CYHRXCONF") over () "CYHRXCONF", avg("CAHA_ANALYSIS"."CYHSMRATINGNORMACTS") over () "Managing_NormalActs", sum(case when ("CAHA_ANALYSIS"."ACCEPTSCORE">=60) then 1 else 0 end ) over ()/nullif(count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPI SODEID") over (), 0) "Accepscore_High", count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPISODEID") over () "Total_no_of_Participants", sum(case when ("CAHA_ANALYSIS"."ACCEPTSCORE"<60) then 1 else 0 end ) over ()/nullif(count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPI SODEID") over (), 0) "Accepscore_Low", sum(case when ("CAHA_ANALYSIS"."CYHSMOKE"='Yes') then 1 else 0 end ) over ()/nullif(count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPI SODEID") over (), 0) "Smoke__Yes", sum(case when ("CAHA_ANALYSIS"."CYHSMOKE"='No') then 1 else 0 end ) over ()/nullif(count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPI SODEID") over (), 0) "Smoke__No", avg("CAHA_ANALYSIS"."CYHMDVISITS") over () "CYHMDVISITS", avg("CAHA_ANALYSIS"."CYHERVISITS") over () "CYHERVISITS", avg("CAHA_ANALYSIS"."CYHHOSPNIGHTS") over () "CYHHOSPNIGHTS", avg("CAHA_ANALYSIS"."CYHSMRATINGMEDNEEDS") over () "Managing_Medical_Needs", avg("CAHA_ANALYSIS"."CYHMOTIVATION") over () "Manage_Health_Motivation", avg("CAHA_ANALYSIS"."CYHSXMGMTSE_MANAGEACT") over () "Manage_Health_Confidence", avg("CAHA_ANALYSIS"."CYHSMRATINGMNGEMOT") over () "CYHSMRATINGMNGEMOT", avg("CAHA_ANALYSIS"."ACCEPTSCORE") over () "Accepscore", avg("CAHA_DIAGNOSES_TOTAL"."DIAGNOSES_VALUE") over () "No_of_Conditions", avg("CAHA_ANALYSIS"."BMI") over () "BMI_Average", avg("CAHA_PR_MALL"."PR_M3") over () "Overall_Impairment", avg("CAHA_ANALYSIS"."CYHPAIN") over () "CYHPAIN", avg("CAHA_ANALYSIS"."CYHFATIGUE") over () "CYHFATIGUE", avg("CAHA_ANALYSIS"."CYHSHORTBREATH") over () "CYHSHORTBREATH", avg("CAHA_PR_SICKDAYS"."PRSICKDAYS_NUM") over () "PRSICKDAYS_NUM", avg("CAHA_ANALYSIS"."PRCYHHEALTHAFFECTACT") over () "PRCYHHEALTHAFFECTACT", avg("CAHA_DIAGNOSES_TOTAL"."DIAGNOSES_VALUE") over () "DIAGNOSES_VALUE", avg("CAHA_ANALYSIS"."AGE") over () "AGE" from ((("DATA_ANALYSIS"."CAHA_ANALYSIS" "CAHA_ANALYSIS" INNER JOIN "DATA_ANALYSIS"."CAHA_DIAGNOSES_TOTAL" "CAHA_DIAGNOSES_TOTAL" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_DIAGNOSES_TOTAL"."PARTICIPANTID " and "CAHA_ANALYSIS"."EPISODEID"="CAHA_DIAGNOSES_TOTAL"."EPISODEID") INNER JOIN "DATA_ANALYSIS"."MV_CAHA_CUSTOMER_DIMENSION" "CAHA_CUSTOMER_DIMENSION" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_CUSTOMER_DIMENSION"."PARTICIPAN TID" and "CAHA_ANALYSIS"."EPISODEID"="CAHA_CUSTOMER_DIMENSION"."EPISO DEID") LEFT OUTER JOIN "DATA_ANALYSIS"."CAHA_PR_MALL" "CAHA_PR_MALL" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_PR_MALL"."PARTICIPANTID" and "CAHA_ANALYSIS"."EPISODEID"="CAHA_PR_MALL"."EPISODEID") LEFT OUTER JOIN "DATA_ANALYSIS"."CAHA_PR_SICKDAYS" "CAHA_PR_SICKDAYS" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_PR_SICKDAYS"."PARTICIPANTID" and "CAHA_ANALYSIS"."EPISODEID"="CAHA_PR_SICKDAYS"."EPISODEID" where to_char("CAHA_ANALYSIS"."PUBLICATIONDT", 'yyyy/mm/dd') between '2012/01/01' and '2012/12/31' and "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR"='HAP' and "CAHA_CUSTOMER_DIMENSION"."CUSTOMER" in ('HAP') and "CAHA_CUSTOMER_DIMENSION"."PROJECT" in ('HAP Members') union select "T0"."C0" "Level1", "T0"."C1" "Header", "T0"."C2"/nullif("T0"."C3", 0) "CYHRXMOT", "T0"."C4"/nullif("T0"."C5", 0) "CYHRXCONF", "T0"."C6"/nullif("T0"."C7", 0) "Managing_NormalActs", "T0"."C8"/nullif(sum("T0"."C9") over (), 0) "Accepscore_High", sum("T0"."C9") over () "Total_no_of_Participants", "T0"."C10"/nullif(sum("T0"."C9") over (), 0) "Accepscore_Low", "T0"."C11"/nullif(sum("T0"."C9") over (), 0) "Smoke__Yes", "T0"."C12"/nullif(sum("T0"."C9") over (), 0) "Smoke__No", "T0"."C13"/nullif("T0"."C14", 0) "CYHMDVISITS", "T0"."C15"/nullif("T0"."C16", 0) "CYHERVISITS", "T0"."C17"/nullif("T0"."C18", 0) "CYHHOSPNIGHTS", "T0"."C19"/nullif("T0"."C20", 0) "Managing_Medical_Needs", "T0"."C21"/nullif("T0"."C22", 0) "Manage_Health_Motivation", "T0"."C23"/nullif("T0"."C24", 0) "Manage_Health_Confidence", "T0"."C25"/nullif("T0"."C26", 0) "CYHSMRATINGMNGEMOT", "T0"."C27"/nullif("T0"."C28", 0) "Accepscore", "T0"."C29"/nullif("T0"."C30", 0) "No_of_Conditions", "T0"."C31"/nullif("T0"."C32", 0) "BMI_Average", "T0"."C33"/nullif("T0"."C34", 0) "Overall_Impairment", "T0"."C35"/nullif("T0"."C36", 0) "CYHPAIN", "T0"."C37"/nullif("T0"."C38", 0) "CYHFATIGUE", "T0"."C39"/nullif("T0"."C40", 0) "CYHSHORTBREATH", "T0"."C41"/nullif("T0"."C42", 0) "PRSICKDAYS_NUM", "T0"."C43"/nullif("T0"."C44", 0) "PRCYHHEALTHAFFECTACT", "T0"."C29"/nullif("T0"."C30", 0) "DIAGNOSES_VALUE", "T0"."C45"/nullif("T0"."C46", 0) "AGE" from (select 2 "C0", "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR" "C1", sum("CAHA_ANALYSIS"."CYHRXMOT") "C2", count("CAHA_ANALYSIS"."CYHRXMOT") "C3", sum("CAHA_ANALYSIS"."CYHRXCONF") "C4", count("CAHA_ANALYSIS"."CYHRXCONF") "C5", sum("CAHA_ANALYSIS"."CYHSMRATINGNORMACTS") "C6", count("CAHA_ANALYSIS"."CYHSMRATINGNORMACTS") "C7", sum(case when "CAHA_ANALYSIS"."ACCEPTSCORE">=60 then 1 else 0 end ) "C8", count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPISODEID") "C9", sum(case when "CAHA_ANALYSIS"."ACCEPTSCORE"<60 then 1 else 0 end ) "C10", sum(case when "CAHA_ANALYSIS"."CYHSMOKE"='Yes' then 1 else 0 end ) "C11", sum(case when "CAHA_ANALYSIS"."CYHSMOKE"='No' then 1 else 0 end ) "C12", sum("CAHA_ANALYSIS"."CYHMDVISITS") "C13", count("CAHA_ANALYSIS"."CYHMDVISITS") "C14", sum("CAHA_ANALYSIS"."CYHERVISITS") "C15", count("CAHA_ANALYSIS"."CYHERVISITS") "C16", sum("CAHA_ANALYSIS"."CYHHOSPNIGHTS") "C17", count("CAHA_ANALYSIS"."CYHHOSPNIGHTS") "C18", sum("CAHA_ANALYSIS"."CYHSMRATINGMEDNEEDS") "C19", count("CAHA_ANALYSIS"."CYHSMRATINGMEDNEEDS") "C20", sum("CAHA_ANALYSIS"."CYHMOTIVATION") "C21", count("CAHA_ANALYSIS"."CYHMOTIVATION") "C22", sum("CAHA_ANALYSIS"."CYHSXMGMTSE_MANAGEACT") "C23", count("CAHA_ANALYSIS"."CYHSXMGMTSE_MANAGEACT") "C24", sum("CAHA_ANALYSIS"."CYHSMRATINGMNGEMOT") "C25", count("CAHA_ANALYSIS"."CYHSMRATINGMNGEMOT") "C26", sum("CAHA_ANALYSIS"."ACCEPTSCORE") "C27", count("CAHA_ANALYSIS"."ACCEPTSCORE") "C28", sum("CAHA_DIAGNOSES_TOTAL"."DIAGNOSES_VALUE") "C29", count("CAHA_DIAGNOSES_TOTAL"."DIAGNOSES_VALUE") "C30", sum("CAHA_ANALYSIS"."BMI") "C31", count("CAHA_ANALYSIS"."BMI") "C32", sum("CAHA_PR_MALL"."PR_M3") "C33", count("CAHA_PR_MALL"."PR_M3") "C34", sum("CAHA_ANALYSIS"."CYHPAIN") "C35", count("CAHA_ANALYSIS"."CYHPAIN") "C36", sum("CAHA_ANALYSIS"."CYHFATIGUE") "C37", count("CAHA_ANALYSIS"."CYHFATIGUE") "C38", sum("CAHA_ANALYSIS"."CYHSHORTBREATH") "C39", count("CAHA_ANALYSIS"."CYHSHORTBREATH") "C40", sum("CAHA_PR_SICKDAYS"."PRSICKDAYS_NUM") "C41", count("CAHA_PR_SICKDAYS"."PRSICKDAYS_NUM") "C42", sum("CAHA_ANALYSIS"."PRCYHHEALTHAFFECTACT") "C43", count("CAHA_ANALYSIS"."PRCYHHEALTHAFFECTACT") "C44", sum("CAHA_ANALYSIS"."AGE") "C45", count("CAHA_ANALYSIS"."AGE") "C46" from ((("DATA_ANALYSIS"."CAHA_ANALYSIS" "CAHA_ANALYSIS" INNER JOIN "DATA_ANALYSIS"."CAHA_DIAGNOSES_TOTAL" "CAHA_DIAGNOSES_TOTAL" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_DIAGNOSES_TOTAL"."PARTICIPANTID " and "CAHA_ANALYSIS"."EPISODEID"="CAHA_DIAGNOSES_TOTAL"."EPISODEID") INNER JOIN "DATA_ANALYSIS"."MV_CAHA_CUSTOMER_DIMENSION" "CAHA_CUSTOMER_DIMENSION" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_CUSTOMER_DIMENSION"."PARTICIPAN TID" and "CAHA_ANALYSIS"."EPISODEID"="CAHA_CUSTOMER_DIMENSION"."EPISO DEID") LEFT OUTER JOIN "DATA_ANALYSIS"."CAHA_PR_MALL" "CAHA_PR_MALL" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_PR_MALL"."PARTICIPANTID" and "CAHA_ANALYSIS"."EPISODEID"="CAHA_PR_MALL"."EPISODEID") LEFT OUTER JOIN "DATA_ANALYSIS"."CAHA_PR_SICKDAYS" "CAHA_PR_SICKDAYS" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_PR_SICKDAYS"."PARTICIPANTID" and "CAHA_ANALYSIS"."EPISODEID"="CAHA_PR_SICKDAYS"."EPISODEID" where to_char("CAHA_ANALYSIS"."PUBLICATIONDT", 'yyyy/mm/dd') between '2012/01/01' and '2012/12/31' and "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR"='HAP' group by "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR") "T0" union select distinct 3 "Level1", 'HealthMedia ' "Header", avg("CAHA_ANALYSIS"."CYHRXMOT") over () "CYHRXMOT", avg("CAHA_ANALYSIS"."CYHRXCONF") over () "CYHRXCONF", avg("CAHA_ANALYSIS"."CYHSMRATINGNORMACTS") over () "Managing_NormalActs", sum(case when ("CAHA_ANALYSIS"."ACCEPTSCORE">=60) then 1 else 0 end ) over ()/nullif(count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPI SODEID") over (), 0) "Accepscore_High", count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPISODEID") over () "Total_no_of_Participants", sum(case when ("CAHA_ANALYSIS"."ACCEPTSCORE"<60) then 1 else 0 end ) over ()/nullif(count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPI SODEID") over (), 0) "Accepscore_Low", sum(case when ("CAHA_ANALYSIS"."CYHSMOKE"='Yes') then 1 else 0 end ) over ()/nullif(count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPI SODEID") over (), 0) "Smoke__Yes", sum(case when ("CAHA_ANALYSIS"."CYHSMOKE"='No') then 1 else 0 end ) over ()/nullif(count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPI SODEID") over (), 0) "Smoke__No", avg("CAHA_ANALYSIS"."CYHMDVISITS") over () "CYHMDVISITS", avg("CAHA_ANALYSIS"."CYHERVISITS") over () "CYHERVISITS", avg("CAHA_ANALYSIS"."CYHHOSPNIGHTS") over () "CYHHOSPNIGHTS", avg("CAHA_ANALYSIS"."CYHSMRATINGMEDNEEDS") over () "Managing_Medical_Needs", avg("CAHA_ANALYSIS"."CYHMOTIVATION") over () "Manage_Health_Motivation", avg("CAHA_ANALYSIS"."CYHSXMGMTSE_MANAGEACT") over () "Manage_Health_Confidence", avg("CAHA_ANALYSIS"."CYHSMRATINGMNGEMOT") over () "CYHSMRATINGMNGEMOT", avg("CAHA_ANALYSIS"."ACCEPTSCORE") over () "Accepscore", avg("CAHA_DIAGNOSES_TOTAL"."DIAGNOSES_VALUE") over () "No_of_Conditions", avg("CAHA_ANALYSIS"."BMI") over () "BMI_Average", avg("CAHA_PR_MALL"."PR_M3") over () "Overall_Impairment", avg("CAHA_ANALYSIS"."CYHPAIN") over () "CYHPAIN", avg("CAHA_ANALYSIS"."CYHFATIGUE") over () "CYHFATIGUE", avg("CAHA_ANALYSIS"."CYHSHORTBREATH") over () "CYHSHORTBREATH", avg("CAHA_PR_SICKDAYS"."PRSICKDAYS_NUM") over () "PRSICKDAYS_NUM", avg("CAHA_ANALYSIS"."PRCYHHEALTHAFFECTACT") over () "PRCYHHEALTHAFFECTACT", avg("CAHA_DIAGNOSES_TOTAL"."DIAGNOSES_VALUE") over () "DIAGNOSES_VALUE", avg("CAHA_ANALYSIS"."AGE") over () "AGE" from (("DATA_ANALYSIS"."CAHA_ANALYSIS" "CAHA_ANALYSIS" INNER JOIN "DATA_ANALYSIS"."CAHA_DIAGNOSES_TOTAL" "CAHA_DIAGNOSES_TOTAL" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_DIAGNOSES_TOTAL"."PARTICIPANTID " and "CAHA_ANALYSIS"."EPISODEID"="CAHA_DIAGNOSES_TOTAL"."EPISODEID") LEFT OUTER JOIN "DATA_ANALYSIS"."CAHA_PR_MALL" "CAHA_PR_MALL" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_PR_MALL"."PARTICIPANTID" and "CAHA_ANALYSIS"."EPISODEID"="CAHA_PR_MALL"."EPISODEID") LEFT OUTER JOIN "DATA_ANALYSIS"."CAHA_PR_SICKDAYS" "CAHA_PR_SICKDAYS" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_PR_SICKDAYS"."PARTICIPANTID" and "CAHA_ANALYSIS"."EPISODEID"="CAHA_PR_SICKDAYS"."EPISODEID" where to_char("CAHA_ANALYSIS"."PUBLICATIONDT", 'yyyy/mm/dd') between '2012/01/01' and '2012/12/31') "Union13" where case when ('3 Column'='2 Column' and "Union13"."Level1"=2) then 'No' else 'Yes' end ='Yes' group by "Union13"."Header", "Union13"."Level1", "Union13"."Accepscore_High", "Union13"."Accepscore_Low", "Union13"."Smoke__Yes", "Union13"."Smoke__No" RATIONALE: The SQL statement with SQL_ID "86zzu2d0dcgfq" was found waiting for "IPC send completion sync" wait event. RELEVANT OBJECT: SQL statement with SQL_ID 86zzu2d0dcgfq select distinct "Union13"."Header" "Header", "Union13"."Level1" "Level2", min(case when '3 Column'='2 Column' and "Union13"."Level1"=2 then 'No' else 'Yes' end ) over (partition by "Union13"."Header", "Union13"."Level1") "C_2_Column_Filter" from (select distinct 1 "Level1", 'PLK Test' "Header", avg("CAHA_ANALYSIS"."CYHRXMOT") over () "CYHRXMOT", avg("CAHA_ANALYSIS"."CYHRXCONF") over () "CYHRXCONF", avg("CAHA_ANALYSIS"."CYHSMRATINGNORMACTS") over () "Managing_NormalActs", sum(case when ("CAHA_ANALYSIS"."ACCEPTSCORE">=60) then 1 else 0 end ) over ()/nullif(count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPI SODEID") over (), 0) "Accepscore_High", count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPISODEID") over () "Total_no_of_Participants", sum(case when ("CAHA_ANALYSIS"."ACCEPTSCORE"<60) then 1 else 0 end ) over ()/nullif(count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPI SODEID") over (), 0) "Accepscore_Low", sum(case when ("CAHA_ANALYSIS"."CYHSMOKE"='Yes') then 1 else 0 end ) over ()/nullif(count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPI SODEID") over (), 0) "Smoke__Yes", sum(case when ("CAHA_ANALYSIS"."CYHSMOKE"='No') then 1 else 0 end ) over ()/nullif(count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPI SODEID") over (), 0) "Smoke__No", avg("CAHA_ANALYSIS"."CYHMDVISITS") over () "CYHMDVISITS", avg("CAHA_ANALYSIS"."CYHERVISITS") over () "CYHERVISITS", avg("CAHA_ANALYSIS"."CYHHOSPNIGHTS") over () "CYHHOSPNIGHTS", avg("CAHA_ANALYSIS"."CYHSMRATINGMEDNEEDS") over () "Managing_Medical_Needs", avg("CAHA_ANALYSIS"."CYHMOTIVATION") over () "Manage_Health_Motivation", avg("CAHA_ANALYSIS"."CYHSXMGMTSE_MANAGEACT") over () "Manage_Health_Confidence", avg("CAHA_ANALYSIS"."CYHSMRATINGMNGEMOT") over () "CYHSMRATINGMNGEMOT", avg("CAHA_ANALYSIS"."ACCEPTSCORE") over () "Accepscore", avg("CAHA_DIAGNOSES_TOTAL"."DIAGNOSES_VALUE") over () "No_of_Conditions", avg("CAHA_ANALYSIS"."BMI") over () "BMI_Average", avg("CAHA_PR_MALL"."PR_M3") over () "Overall_Impairment", avg("CAHA_ANALYSIS"."CYHPAIN") over () "CYHPAIN", avg("CAHA_ANALYSIS"."CYHFATIGUE") over () "CYHFATIGUE", avg("CAHA_ANALYSIS"."CYHSHORTBREATH") over () "CYHSHORTBREATH", avg("CAHA_PR_SICKDAYS"."PRSICKDAYS_NUM") over () "PRSICKDAYS_NUM", avg("CAHA_ANALYSIS"."PRCYHHEALTHAFFECTACT") over () "PRCYHHEALTHAFFECTACT", avg("CAHA_DIAGNOSES_TOTAL"."DIAGNOSES_VALUE") over () "DIAGNOSES_VALUE", avg("CAHA_ANALYSIS"."AGE") over () "AGE" from ((("DATA_ANALYSIS"."CAHA_ANALYSIS" "CAHA_ANALYSIS" INNER JOIN "DATA_ANALYSIS"."CAHA_DIAGNOSES_TOTAL" "CAHA_DIAGNOSES_TOTAL" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_DIAGNOSES_TOTAL"."PARTICIPANTID " and "CAHA_ANALYSIS"."EPISODEID"="CAHA_DIAGNOSES_TOTAL"."EPISODEID") INNER JOIN "DATA_ANALYSIS"."MV_CAHA_CUSTOMER_DIMENSION" "CAHA_CUSTOMER_DIMENSION" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_CUSTOMER_DIMENSION"."PARTICIPAN TID" and "CAHA_ANALYSIS"."EPISODEID"="CAHA_CUSTOMER_DIMENSION"."EPISO DEID") LEFT OUTER JOIN "DATA_ANALYSIS"."CAHA_PR_MALL" "CAHA_PR_MALL" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_PR_MALL"."PARTICIPANTID" and "CAHA_ANALYSIS"."EPISODEID"="CAHA_PR_MALL"."EPISODEID") LEFT OUTER JOIN "DATA_ANALYSIS"."CAHA_PR_SICKDAYS" "CAHA_PR_SICKDAYS" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_PR_SICKDAYS"."PARTICIPANTID" and "CAHA_ANALYSIS"."EPISODEID"="CAHA_PR_SICKDAYS"."EPISODEID" where to_char("CAHA_ANALYSIS"."PUBLICATIONDT", 'yyyy/mm/dd') between '2012/01/01' and '2012/12/31' and "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR"='HAP' and "CAHA_CUSTOMER_DIMENSION"."CUSTOMER" in ('HAP') and "CAHA_CUSTOMER_DIMENSION"."PROJECT" in ('HAP Members') union select "T0"."C0" "Level1", "T0"."C1" "Header", "T0"."C2"/nullif("T0"."C3", 0) "CYHRXMOT", "T0"."C4"/nullif("T0"."C5", 0) "CYHRXCONF", "T0"."C6"/nullif("T0"."C7", 0) "Managing_NormalActs", "T0"."C8"/nullif(sum("T0"."C9") over (), 0) "Accepscore_High", sum("T0"."C9") over () "Total_no_of_Participants", "T0"."C10"/nullif(sum("T0"."C9") over (), 0) "Accepscore_Low", "T0"."C11"/nullif(sum("T0"."C9") over (), 0) "Smoke__Yes", "T0"."C12"/nullif(sum("T0"."C9") over (), 0) "Smoke__No", "T0"."C13"/nullif("T0"."C14", 0) "CYHMDVISITS", "T0"."C15"/nullif("T0"."C16", 0) "CYHERVISITS", "T0"."C17"/nullif("T0"."C18", 0) "CYHHOSPNIGHTS", "T0"."C19"/nullif("T0"."C20", 0) "Managing_Medical_Needs", "T0"."C21"/nullif("T0"."C22", 0) "Manage_Health_Motivation", "T0"."C23"/nullif("T0"."C24", 0) "Manage_Health_Confidence", "T0"."C25"/nullif("T0"."C26", 0) "CYHSMRATINGMNGEMOT", "T0"."C27"/nullif("T0"."C28", 0) "Accepscore", "T0"."C29"/nullif("T0"."C30", 0) "No_of_Conditions", "T0"."C31"/nullif("T0"."C32", 0) "BMI_Average", "T0"."C33"/nullif("T0"."C34", 0) "Overall_Impairment", "T0"."C35"/nullif("T0"."C36", 0) "CYHPAIN", "T0"."C37"/nullif("T0"."C38", 0) "CYHFATIGUE", "T0"."C39"/nullif("T0"."C40", 0) "CYHSHORTBREATH", "T0"."C41"/nullif("T0"."C42", 0) "PRSICKDAYS_NUM", "T0"."C43"/nullif("T0"."C44", 0) "PRCYHHEALTHAFFECTACT", "T0"."C29"/nullif("T0"."C30", 0) "DIAGNOSES_VALUE", "T0"."C45"/nullif("T0"."C46", 0) "AGE" from (select 2 "C0", "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR" "C1", sum("CAHA_ANALYSIS"."CYHRXMOT") "C2", count("CAHA_ANALYSIS"."CYHRXMOT") "C3", sum("CAHA_ANALYSIS"."CYHRXCONF") "C4", count("CAHA_ANALYSIS"."CYHRXCONF") "C5", sum("CAHA_ANALYSIS"."CYHSMRATINGNORMACTS") "C6", count("CAHA_ANALYSIS"."CYHSMRATINGNORMACTS") "C7", sum(case when "CAHA_ANALYSIS"."ACCEPTSCORE">=60 then 1 else 0 end ) "C8", count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPISODEID") "C9", sum(case when "CAHA_ANALYSIS"."ACCEPTSCORE"<60 then 1 else 0 end ) "C10", sum(case when "CAHA_ANALYSIS"."CYHSMOKE"='Yes' then 1 else 0 end ) "C11", sum(case when "CAHA_ANALYSIS"."CYHSMOKE"='No' then 1 else 0 end ) "C12", sum("CAHA_ANALYSIS"."CYHMDVISITS") "C13", count("CAHA_ANALYSIS"."CYHMDVISITS") "C14", sum("CAHA_ANALYSIS"."CYHERVISITS") "C15", count("CAHA_ANALYSIS"."CYHERVISITS") "C16", sum("CAHA_ANALYSIS"."CYHHOSPNIGHTS") "C17", count("CAHA_ANALYSIS"."CYHHOSPNIGHTS") "C18", sum("CAHA_ANALYSIS"."CYHSMRATINGMEDNEEDS") "C19", count("CAHA_ANALYSIS"."CYHSMRATINGMEDNEEDS") "C20", sum("CAHA_ANALYSIS"."CYHMOTIVATION") "C21", count("CAHA_ANALYSIS"."CYHMOTIVATION") "C22", sum("CAHA_ANALYSIS"."CYHSXMGMTSE_MANAGEACT") "C23", count("CAHA_ANALYSIS"."CYHSXMGMTSE_MANAGEACT") "C24", sum("CAHA_ANALYSIS"."CYHSMRATINGMNGEMOT") "C25", count("CAHA_ANALYSIS"."CYHSMRATINGMNGEMOT") "C26", sum("CAHA_ANALYSIS"."ACCEPTSCORE") "C27", count("CAHA_ANALYSIS"."ACCEPTSCORE") "C28", sum("CAHA_DIAGNOSES_TOTAL"."DIAGNOSES_VALUE") "C29", count("CAHA_DIAGNOSES_TOTAL"."DIAGNOSES_VALUE") "C30", sum("CAHA_ANALYSIS"."BMI") "C31", count("CAHA_ANALYSIS"."BMI") "C32", sum("CAHA_PR_MALL"."PR_M3") "C33", count("CAHA_PR_MALL"."PR_M3") "C34", sum("CAHA_ANALYSIS"."CYHPAIN") "C35", count("CAHA_ANALYSIS"."CYHPAIN") "C36", sum("CAHA_ANALYSIS"."CYHFATIGUE") "C37", count("CAHA_ANALYSIS"."CYHFATIGUE") "C38", sum("CAHA_ANALYSIS"."CYHSHORTBREATH") "C39", count("CAHA_ANALYSIS"."CYHSHORTBREATH") "C40", sum("CAHA_PR_SICKDAYS"."PRSICKDAYS_NUM") "C41", count("CAHA_PR_SICKDAYS"."PRSICKDAYS_NUM") "C42", sum("CAHA_ANALYSIS"."PRCYHHEALTHAFFECTACT") "C43", count("CAHA_ANALYSIS"."PRCYHHEALTHAFFECTACT") "C44", sum("CAHA_ANALYSIS"."AGE") "C45", count("CAHA_ANALYSIS"."AGE") "C46" from ((("DATA_ANALYSIS"."CAHA_ANALYSIS" "CAHA_ANALYSIS" INNER JOIN "DATA_ANALYSIS"."CAHA_DIAGNOSES_TOTAL" "CAHA_DIAGNOSES_TOTAL" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_DIAGNOSES_TOTAL"."PARTICIPANTID " and "CAHA_ANALYSIS"."EPISODEID"="CAHA_DIAGNOSES_TOTAL"."EPISODEID") INNER JOIN "DATA_ANALYSIS"."MV_CAHA_CUSTOMER_DIMENSION" "CAHA_CUSTOMER_DIMENSION" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_CUSTOMER_DIMENSION"."PARTICIPAN TID" and "CAHA_ANALYSIS"."EPISODEID"="CAHA_CUSTOMER_DIMENSION"."EPISO DEID") LEFT OUTER JOIN "DATA_ANALYSIS"."CAHA_PR_MALL" "CAHA_PR_MALL" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_PR_MALL"."PARTICIPANTID" and "CAHA_ANALYSIS"."EPISODEID"="CAHA_PR_MALL"."EPISODEID") LEFT OUTER JOIN "DATA_ANALYSIS"."CAHA_PR_SICKDAYS" "CAHA_PR_SICKDAYS" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_PR_SICKDAYS"."PARTICIPANTID" and "CAHA_ANALYSIS"."EPISODEID"="CAHA_PR_SICKDAYS"."EPISODEID" where to_char("CAHA_ANALYSIS"."PUBLICATIONDT", 'yyyy/mm/dd') between '2012/01/01' and '2012/12/31' and "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR"='HAP' group by "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR") "T0" union select distinct 3 "Level1", 'HealthMedia ' "Header", avg("CAHA_ANALYSIS"."CYHRXMOT") over () "CYHRXMOT", avg("CAHA_ANALYSIS"."CYHRXCONF") over () "CYHRXCONF", avg("CAHA_ANALYSIS"."CYHSMRATINGNORMACTS") over () "Managing_NormalActs", sum(case when ("CAHA_ANALYSIS"."ACCEPTSCORE">=60) then 1 else 0 end ) over ()/nullif(count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPI SODEID") over (), 0) "Accepscore_High", count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPISODEID") over () "Total_no_of_Participants", sum(case when ("CAHA_ANALYSIS"."ACCEPTSCORE"<60) then 1 else 0 end ) over ()/nullif(count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPI SODEID") over (), 0) "Accepscore_Low", sum(case when ("CAHA_ANALYSIS"."CYHSMOKE"='Yes') then 1 else 0 end ) over ()/nullif(count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPI SODEID") over (), 0) "Smoke__Yes", sum(case when ("CAHA_ANALYSIS"."CYHSMOKE"='No') then 1 else 0 end ) over ()/nullif(count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPI SODEID") over (), 0) "Smoke__No", avg("CAHA_ANALYSIS"."CYHMDVISITS") over () "CYHMDVISITS", avg("CAHA_ANALYSIS"."CYHERVISITS") over () "CYHERVISITS", avg("CAHA_ANALYSIS"."CYHHOSPNIGHTS") over () "CYHHOSPNIGHTS", avg("CAHA_ANALYSIS"."CYHSMRATINGMEDNEEDS") over () "Managing_Medical_Needs", avg("CAHA_ANALYSIS"."CYHMOTIVATION") over () "Manage_Health_Motivation", avg("CAHA_ANALYSIS"."CYHSXMGMTSE_MANAGEACT") over () "Manage_Health_Confidence", avg("CAHA_ANALYSIS"."CYHSMRATINGMNGEMOT") over () "CYHSMRATINGMNGEMOT", avg("CAHA_ANALYSIS"."ACCEPTSCORE") over () "Accepscore", avg("CAHA_DIAGNOSES_TOTAL"."DIAGNOSES_VALUE") over () "No_of_Conditions", avg("CAHA_ANALYSIS"."BMI") over () "BMI_Average", avg("CAHA_PR_MALL"."PR_M3") over () "Overall_Impairment", avg("CAHA_ANALYSIS"."CYHPAIN") over () "CYHPAIN", avg("CAHA_ANALYSIS"."CYHFATIGUE") over () "CYHFATIGUE", avg("CAHA_ANALYSIS"."CYHSHORTBREATH") over () "CYHSHORTBREATH", avg("CAHA_PR_SICKDAYS"."PRSICKDAYS_NUM") over () "PRSICKDAYS_NUM", avg("CAHA_ANALYSIS"."PRCYHHEALTHAFFECTACT") over () "PRCYHHEALTHAFFECTACT", avg("CAHA_DIAGNOSES_TOTAL"."DIAGNOSES_VALUE") over () "DIAGNOSES_VALUE", avg("CAHA_ANALYSIS"."AGE") over () "AGE" from (("DATA_ANALYSIS"."CAHA_ANALYSIS" "CAHA_ANALYSIS" INNER JOIN "DATA_ANALYSIS"."CAHA_DIAGNOSES_TOTAL" "CAHA_DIAGNOSES_TOTAL" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_DIAGNOSES_TOTAL"."PARTICIPANTID " and "CAHA_ANALYSIS"."EPISODEID"="CAHA_DIAGNOSES_TOTAL"."EPISODEID") LEFT OUTER JOIN "DATA_ANALYSIS"."CAHA_PR_MALL" "CAHA_PR_MALL" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_PR_MALL"."PARTICIPANTID" and "CAHA_ANALYSIS"."EPISODEID"="CAHA_PR_MALL"."EPISODEID") LEFT OUTER JOIN "DATA_ANALYSIS"."CAHA_PR_SICKDAYS" "CAHA_PR_SICKDAYS" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_PR_SICKDAYS"."PARTICIPANTID" and "CAHA_ANALYSIS"."EPISODEID"="CAHA_PR_SICKDAYS"."EPISODEID" where to_char("CAHA_ANALYSIS"."PUBLICATIONDT", 'yyyy/mm/dd') between '2012/01/01' and '2012/12/31') "Union13" where case when ('3 Column'='2 Column' and "Union13"."Level1"=2) then 'No' else 'Yes' end ='Yes' order by "Level2" asc nulls last RATIONALE: The SQL statement with SQL_ID "7k3471ng7v89k" was found waiting for "IPC send completion sync" wait event. RELEVANT OBJECT: SQL statement with SQL_ID 7k3471ng7v89k select "Union8"."Header" "levelkey", "Union8"."Level4" "Level2", "Union8"."TrustHCP" "level0key", "Union8"."CYHMDREL_TRUSTHCP" "CYHMDREL_TRUSTHCP", sum("Union8"."Percent") "Percent" from (select "T0"."C0" "CYHMDREL_TRUSTHCP", sum("T0"."C1") over () "Total_no_of_Participants", "T0"."C1"/nullif(sum("T0"."C1") over (), 0) "Percent", "T0"."C2" "Level4", "T0"."C3" "Header", "T0"."C4" "TrustHCP" from (select "CAHA_ANALYSIS"."CYHMDREL_TRUSTHCP" "C0", count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPISODEID") "C1", 1 "C2", 'PLK Test' "C3", case when "CAHA_ANALYSIS"."CYHMDREL_TRUSTHCP"='1' then 'Strongly Disagree' when "CAHA_ANALYSIS"."CYHMDREL_TRUSTHCP"='2' then 'Disagree' when "CAHA_ANALYSIS"."CYHMDREL_TRUSTHCP"='3' then 'Somewhat Agree' when "CAHA_ANALYSIS"."CYHMDREL_TRUSTHCP"='4' then 'Agree' when "CAHA_ANALYSIS"."CYHMDREL_TRUSTHCP"='5' then 'Strongly Agree' else NULL end "C4" from "DATA_ANALYSIS"."CAHA_ANALYSIS" "CAHA_ANALYSIS", "DATA_ANALYSIS"."MV_CAHA_CUSTOMER_DIMENSION" "CAHA_CUSTOMER_DIMENSION" where to_char("CAHA_ANALYSIS"."PUBLICATIONDT", 'yyyy/mm/dd') between '2012/01/01' and '2012/12/31' and "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR"='HAP' and "CAHA_CUSTOMER_DIMENSION"."CUSTOMER" in ('HAP') and "CAHA_CUSTOMER_DIMENSION"."PROJECT" in ('HAP Members') and "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_CUSTOMER_DIMENSION"."PARTICIPAN TID" and "CAHA_ANALYSIS"."EPISODEID"="CAHA_CUSTOMER_DIMENSION"."EPISO DEID" group by "CAHA_ANALYSIS"."CYHMDREL_TRUSTHCP", case when "CAHA_ANALYSIS"."CYHMDREL_TRUSTHCP"='1' then 'Strongly Disagree' when "CAHA_ANALYSIS"."CYHMDREL_TRUSTHCP"='2' then 'Disagree' when "CAHA_ANALYSIS"."CYHMDREL_TRUSTHCP"='3' then 'Somewhat Agree' when "CAHA_ANALYSIS"."CYHMDREL_TRUSTHCP"='4' then 'Agree' when "CAHA_ANALYSIS"."CYHMDREL_TRUSTHCP"='5' then 'Strongly Agree' else NULL end ) "T0" union select "T0"."C0" "CYHMDREL_TRUSTHCP", sum("T0"."C1") over () "Total_no_of_Participants", "T0"."C1"/nullif(sum("T0"."C1") over (), 0) "Percent", "T0"."C2" "Level4", "T0"."C3" "Header", "T0"."C4" "TrustHCP" from (select "CAHA_ANALYSIS"."CYHMDREL_TRUSTHCP" "C0", count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPISODEID") "C1", 2 "C2", "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR" "C3", case when "CAHA_ANALYSIS"."CYHMDREL_TRUSTHCP"='1' then 'Strongly Disagree' when "CAHA_ANALYSIS"."CYHMDREL_TRUSTHCP"='2' then 'Disagree' when "CAHA_ANALYSIS"."CYHMDREL_TRUSTHCP"='3' then 'Somewhat Agree' when "CAHA_ANALYSIS"."CYHMDREL_TRUSTHCP"='4' then 'Agree' when "CAHA_ANALYSIS"."CYHMDREL_TRUSTHCP"='5' then 'Strongly Agree' else NULL end "C4" from "DATA_ANALYSIS"."CAHA_ANALYSIS" "CAHA_ANALYSIS", "DATA_ANALYSIS"."MV_CAHA_CUSTOMER_DIMENSION" "CAHA_CUSTOMER_DIMENSION" where to_char("CAHA_ANALYSIS"."PUBLICATIONDT", 'yyyy/mm/dd') between '2012/01/01' and '2012/12/31' and "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR"='HAP' and "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_CUSTOMER_DIMENSION"."PARTICIPAN TID" and "CAHA_ANALYSIS"."EPISODEID"="CAHA_CUSTOMER_DIMENSION"."EPISO DEID" group by "CAHA_ANALYSIS"."CYHMDREL_TRUSTHCP", "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR", case when "CAHA_ANALYSIS"."CYHMDREL_TRUSTHCP"='1' then 'Strongly Disagree' when "CAHA_ANALYSIS"."CYHMDREL_TRUSTHCP"='2' then 'Disagree' when "CAHA_ANALYSIS"."CYHMDREL_TRUSTHCP"='3' then 'Somewhat Agree' when "CAHA_ANALYSIS"."CYHMDREL_TRUSTHCP"='4' then 'Agree' when "CAHA_ANALYSIS"."CYHMDREL_TRUSTHCP"='5' then 'Strongly Agree' else NULL end ) "T0" union select "T0"."C0" "CYHMDREL_TRUSTHCP", sum("T0"."C1") over () "Total_no_of_Participants", "T0"."C1"/nullif(sum("T0"."C1") over (), 0) "Percent", "T0"."C2" "Level4", "T0"."C3" "Header", "T0"."C4" "TrustHCP" from (select "CAHA_ANALYSIS"."CYHMDREL_TRUSTHCP" "C0", count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPISODEID") "C1", 3 "C2", 'HealthMedia ' "C3", case when "CAHA_ANALYSIS"."CYHMDREL_TRUSTHCP"='1' then 'Strongly Disagree' when "CAHA_ANALYSIS"."CYHMDREL_TRUSTHCP"='2' then 'Disagree' when "CAHA_ANALYSIS"."CYHMDREL_TRUSTHCP"='3' then 'Somewhat Agree' when "CAHA_ANALYSIS"."CYHMDREL_TRUSTHCP"='4' then 'Agree' when "CAHA_ANALYSIS"."CYHMDREL_TRUSTHCP"='5' then 'Strongly Agree' else NULL end "C4" from "DATA_ANALYSIS"."CAHA_ANALYSIS" "CAHA_ANALYSIS" where to_char("CAHA_ANALYSIS"."PUBLICATIONDT", 'yyyy/mm/dd') between '2012/01/01' and '2012/12/31' group by "CAHA_ANALYSIS"."CYHMDREL_TRUSTHCP", case when "CAHA_ANALYSIS"."CYHMDREL_TRUSTHCP"='1' then 'Strongly Disagree' when "CAHA_ANALYSIS"."CYHMDREL_TRUSTHCP"='2' then 'Disagree' when "CAHA_ANALYSIS"."CYHMDREL_TRUSTHCP"='3' then 'Somewhat Agree' when "CAHA_ANALYSIS"."CYHMDREL_TRUSTHCP"='4' then 'Agree' when "CAHA_ANALYSIS"."CYHMDREL_TRUSTHCP"='5' then 'Strongly Agree' else NULL end ) "T0") "Union8" where case when ('3 Column'='2 Column' and "Union8"."Level4"=2) then 'No' else 'Yes' end ='Yes' and not "Union8"."TrustHCP" is null group by "Union8"."Header", "Union8"."Level4", "Union8"."TrustHCP", "Union8"."CYHMDREL_TRUSTHCP" RATIONALE: The SQL statement with SQL_ID "069xbh3j0tpyf" was found waiting for "IPC send completion sync" wait event. RELEVANT OBJECT: SQL statement with SQL_ID 069xbh3j0tpyf select "T0"."C0" "ITEMCD", "T0"."C1" "c2", min("T0"."C2") over (partition by "T0"."C0") "Percent", "T0"."C3" "Count4", "T0"."C4" "Count1" from (select distinct "CAHA_ANALYSIS_GRAPHS"."ITEMCD" "C0", sum("Diff_Sleeping_Graph_Total"."Total_no_of_Participants") over (partition by "CAHA_ANALYSIS_GRAPHS"."ITEMCD") "C1", count(distinct CASE WHEN("CAHA_ANALYSIS_GRAPHS"."PARTICIPANTID" IS NULL) OR ("CAHA_ANALYSIS_GRAPHS"."EPISODEID" IS NULL) THEN NULL ELSE ("CAHA_ANALYSIS_GRAPHS"."PARTICIPANTID"||"CAHA_ANALYSIS_GRAPHS"."EPIS ODEID") END) over (partition by "CAHA_ANALYSIS_GRAPHS"."ITEMCD")/nullif("Diff_Sleeping_Graph_Total"." Total_no_of_Participants", 0) "C2", count(distinct CASE WHEN("CAHA_ANALYSIS_GRAPHS"."PARTICIPANTID" IS NULL) OR ("CAHA_ANALYSIS_GRAPHS"."EPISODEID" IS NULL) THEN NULL ELSE ("CAHA_ANALYSIS_GRAPHS"."PARTICIPANTID"||"CAHA_ANALYSIS_GRAPHS"."EPIS ODEID") END) over (partition by "CAHA_ANALYSIS_GRAPHS"."ITEMCD") "C3", count(distinct CASE WHEN("CAHA_ANALYSIS_GRAPHS"."PARTICIPANTID" IS NULL) OR ("CAHA_ANALYSIS_GRAPHS"."EPISODEID" IS NULL) THEN NULL ELSE ("CAHA_ANALYSIS_GRAPHS"."PARTICIPANTID"||"CAHA_ANALYSIS_GRAPHS". "EPISODEID") END) over () "C4" from ("DATA_ANALYSIS"."MV_CAHA_ANALYSIS_GRAPHS_NEW" "CAHA_ANALYSIS_GRAPHS" RIGHT OUTER JOIN "DATA_ANALYSIS"."CAHA_ANALYSIS" "CAHA_ANALYSIS" on "CAHA_ANALYSIS_GRAPHS"."EPISODEID"="CAHA_ANALYSIS"."EPISODEID" and "CAHA_ANALYSIS_GRAPHS"."PARTICIPANTID"="CAHA_ANALYSIS"."PARTICIPANTID ") INNER JOIN "DATA_ANALYSIS"."MV_CAHA_CUSTOMER_DIMENSION" "CAHA_CUSTOMER_DIMENSION" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_CUSTOMER_DIMENSION"."PARTICIPAN TID" and "CAHA_ANALYSIS"."EPISODEID"="CAHA_CUSTOMER_DIMENSION"."EPISO DEID", (select distinct 1 "Level1", count(distinct "CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPISODEID") over () "Total_no_of_Participants" from "DATA_ANALYSIS"."CAHA_ANALYSIS" "CAHA_ANALYSIS", "DATA_ANALYSIS"."MV_CAHA_CUSTOMER_DIMENSION" "CAHA_CUSTOMER_DIMENSION" where to_char("CAHA_ANALYSIS"."PUBLICATIONDT", 'yyyy/mm/dd') between '2012/01/01' and '2012/12/31' and "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR"='HAP' and "CAHA_CUSTOMER_DIMENSION"."CUSTOMER" in ('HAP') and "CAHA_CUSTOMER_DIMENSION"."PROJECT" in ('HAP Members') and "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_CUSTOMER_DIMENSION"."PARTICIPAN TID" and "CAHA_ANALYSIS"."EPISODEID"="CAHA_CUSTOMER_DIMENSION"."EPISO DEID") "Diff_Sleeping_Graph_Total" where to_char("CAHA_ANALYSIS"."PUBLICATIONDT", 'yyyy/mm/dd') between '2012/01/01' and '2012/12/31' and "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR"='HAP' and "CAHA_CUSTOMER_DIMENSION"."CUSTOMER" in ('HAP') and "CAHA_CUSTOMER_DIMENSION"."PROJECT" in ('HAP Members') and "CAHA_ANALYSIS_GRAPHS"."ITEMTAB"=2 and not "CAHA_ANALYSIS_GRAPHS"."ITEMCD" is null) "T0" RATIONALE: The SQL statement with SQL_ID "0gwbnt8bncu3k" was found waiting for "IPC send completion sync" wait event. RELEVANT OBJECT: SQL statement with SQL_ID 0gwbnt8bncu3k select distinct "Union1"."ITEMCD" "ITEMCD", "Disease_History_Rank"."Rank7" "Rank2", min(case when '3 Column'='2 Column' and "Union1"."Level5"=2 then 'No' else 'Yes' end ) over (partition by "Union1"."ITEMCD", "Disease_History_Rank"."Rank7") "C_2_Column_Filter", min("Union1"."ITEMSORT") over (partition by "Union1"."ITEMCD", "Disease_History_Rank"."Rank7") "ITEMSORT" from (select "T0"."C0" "ITEMCD", "T0"."C1" "ITEMSORT", min("T0"."C2") over (partition by "T0"."C0", "T0"."C1") "Percent", "T0"."C3" "Total_no_of_Participants", 1 "Level5", 'PLK Test' "Header" from (select distinct "CAHA_ANALYSIS_GRAPHS"."ITEMCD" "C0", "CAHA_ANALYSIS_GRAPHS"."ITEMSORT" "C1", count(distinct CASE WHEN("CAHA_ANALYSIS_GRAPHS"."PARTICIPANTID" IS NULL) OR ("CAHA_ANALYSIS_GRAPHS"."EPISODEID" IS NULL) THEN NULL ELSE ("CAHA_ANALYSIS_GRAPHS"."PARTICIPANTID"||"CAHA_ANALYSIS_GRAPHS"."EPIS ODEID") END) over (partition by "CAHA_ANALYSIS_GRAPHS"."ITEMCD", "CAHA_ANALYSIS_GRAPHS"."ITEMSORT")/nullif("PDH_Total_Q1"."Total_no_of _Participants", 0) "C2", min("PDH_Total_Q1"."Total_no_of_Participants") over (partition by "CAHA_ANALYSIS_GRAPHS"."ITEMCD", "CAHA_ANALYSIS_GRAPHS"."ITEMSORT") "C3" from ("DATA_ANALYSIS"."MV_CAHA_ANALYSIS_GRAPHS_NEW" "CAHA_ANALYSIS_GRAPHS" RIGHT OUTER JOIN "DATA_ANALYSIS"."CAHA_ANALYSIS" "CAHA_ANALYSIS" on "CAHA_ANALYSIS_GRAPHS"."EPISODEID"="CAHA_ANALYSIS"."EPISODEID" and "CAHA_ANALYSIS_GRAPHS"."PARTICIPANTID"="CAHA_ANALYSIS"."PARTICIPANTID ") INNER JOIN "DATA_ANALYSIS"."MV_CAHA_CUSTOMER_DIMENSION" "CAHA_CUSTOMER_DIMENSION" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_CUSTOMER_DIMENSION"."PARTICIPAN TID" and "CAHA_ANALYSIS"."EPISODEID"="CAHA_CUSTOMER_DIMENSION"."EPISO DEID", (select distinct 1 "Level1", count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPISODEID") over () "Total_no_of_Participants" from "DATA_ANALYSIS"."CAHA_ANALYSIS" "CAHA_ANALYSIS", "DATA_ANALYSIS"."MV_CAHA_CUSTOMER_DIMENSION" "CAHA_CUSTOMER_DIMENSION" where to_char("CAHA_ANALYSIS"."PUBLICATIONDT", 'yyyy/mm/dd') between '2012/01/01' and '2012/12/31' and "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR"='HAP' and "CAHA_CUSTOMER_DIMENSION"."CUSTOMER" in ('HAP') and "CAHA_CUSTOMER_DIMENSION"."PROJECT" in ('HAP Members') and "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_CUSTOMER_DIMENSION"."PARTICIPAN TID" and "CAHA_ANALYSIS"."EPISODEID"="CAHA_CUSTOMER_DIMENSION"."EPISO DEID") "PDH_Total_Q1" where "CAHA_ANALYSIS_GRAPHS"."ITEMTAB"=1 and to_char("CAHA_ANALYSIS"."PUBLICATIONDT", 'yyyy/mm/dd') between '2012/01/01' and '2012/12/31' and "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR"='HAP' and "CAHA_CUSTOMER_DIMENSION"."CUSTOMER" in ('HAP') and "CAHA_CUSTOMER_DIMENSION"."PROJECT" in ('HAP Members') and 1="PDH_Total_Q1"."Level1") "T0" union select "T0"."C0" "ITEMCD", "T0"."C1" "ITEMSORT", min("T0"."C2") over (partition by "T0"."C0", "T0"."C1", "T0"."C3") "Percent", "T0"."C4" "Total_no_of_Participants", 2 "Level5", "T0"."C3" "Header" from (select distinct "CAHA_ANALYSIS_GRAPHS"."ITEMCD" "C0", "CAHA_ANALYSIS_GRAPHS"."ITEMSORT" "C1", count(distinct CASE WHEN("CAHA_ANALYSIS_GRAPHS"."PARTICIPANTID" IS NULL) OR ("CAHA_ANALYSIS_GRAPHS"."EPISODEID" IS NULL) THEN NULL ELSE ("CAHA_ANALYSIS_GRAPHS"."PARTICIPANTID"||"CAHA_ANALYSIS_GRAPHS"."EPIS ODEID") END) over (partition by "CAHA_ANALYSIS_GRAPHS"."ITEMCD", "CAHA_ANALYSIS_GRAPHS"."ITEMSORT", "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR")/nullif("PDH_Total_Q2"."Total _no_of_Participants", 0) "C2", "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR" "C3", min("PDH_Total_Q2"."Total_no_of_Participants") over (partition by "CAHA_ANALYSIS_GRAPHS"."ITEMCD", "CAHA_ANALYSIS_GRAPHS"."ITEMSORT", "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR") "C4" from ("DATA_ANALYSIS"."MV_CAHA_ANALYSIS_GRAPHS_NEW" "CAHA_ANALYSIS_GRAPHS" RIGHT OUTER JOIN "DATA_ANALYSIS"."CAHA_ANALYSIS" "CAHA_ANALYSIS" on "CAHA_ANALYSIS_GRAPHS"."EPISODEID"="CAHA_ANALYSIS"."EPISODEID" and "CAHA_ANALYSIS_GRAPHS"."PARTICIPANTID"="CAHA_ANALYSIS"."PARTICIPANTID ") INNER JOIN "DATA_ANALYSIS"."MV_CAHA_CUSTOMER_DIMENSION" "CAHA_CUSTOMER_DIMENSION" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_CUSTOMER_DIMENSION"."PARTICIPAN TID" and "CAHA_ANALYSIS"."EPISODEID"="CAHA_CUSTOMER_DIMENSION"."EPISO DEID", (select distinct 2 "Level1", count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPISODEID") over () "Total_no_of_Participants" from "DATA_ANALYSIS"."CAHA_ANALYSIS" "CAHA_ANALYSIS", "DATA_ANALYSIS"."MV_CAHA_CUSTOMER_DIMENSION" "CAHA_CUSTOMER_DIMENSION" where to_char("CAHA_ANALYSIS"."PUBLICATIONDT", 'yyyy/mm/dd') between '2012/01/01' and '2012/12/31' and "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR"='HAP' and "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_CUSTOMER_DIMENSION"."PARTICIPAN TID" and "CAHA_ANALYSIS"."EPISODEID"="CAHA_CUSTOMER_DIMENSION"."EPISO DEID") "PDH_Total_Q2" where "CAHA_ANALYSIS_GRAPHS"."ITEMTAB"=1 and to_char("CAHA_ANALYSIS"."PUBLICATIONDT", 'yyyy/mm/dd') between '2012/01/01' and '2012/12/31' and "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR"='HAP' and 2="PDH_Total_Q2"."Level1") "T0" union select "T0"."C0" "ITEMCD", "T0"."C1" "ITEMSORT", min("T0"."C2") over (partition by "T0"."C0", "T0"."C1") "Percent", "T0"."C3" "Total_no_of_Participants", 3 "Level5", 'HealthMedia ' "Header" from (select distinct "CAHA_ANALYSIS_GRAPHS"."ITEMCD" "C0", "CAHA_ANALYSIS_GRAPHS"."ITEMSORT" "C1", count(distinct CASE WHEN("CAHA_ANALYSIS_GRAPHS"."PARTICIPANTID" IS NULL) OR ("CAHA_ANALYSIS_GRAPHS"."EPISODEID" IS NULL) THEN NULL ELSE ("CAHA_ANALYSIS_GRAPHS"."PARTICIPANTID"||"CAHA_ANALYSIS_GRAPHS"."EPIS ODEID") END) over (partition by "CAHA_ANALYSIS_GRAPHS"."ITEMCD", "CAHA_ANALYSIS_GRAPHS"."ITEMSORT")/nullif("PDH_Total_Q3"."Total_no_of _Participants", 0) "C2", min("PDH_Total_Q3"."Total_no_of_Participants") over (partition by "CAHA_ANALYSIS_GRAPHS"."ITEMCD", "CAHA_ANALYSIS_GRAPHS"."ITEMSORT") "C3" from "DATA_ANALYSIS"."MV_CAHA_ANALYSIS_GRAPHS_NEW" "CAHA_ANALYSIS_GRAPHS" RIGHT OUTER JOIN "DATA_ANALYSIS"."CAHA_ANALYSIS" "CAHA_ANALYSIS" on "CAHA_ANALYSIS_GRAPHS"."EPISODEID"="CAHA_ANALYSIS"."EPISODEID" and "CAHA_ANALYSIS_GRAPHS"."PARTICIPANTID"="CAHA_ANALYSIS"."PARTICIPANTID ", (select distinct 3 "Level1", count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPISODEID") over () "Total_no_of_Participants" from "DATA_ANALYSIS"."CAHA_ANALYSIS" "CAHA_ANALYSIS" where to_char("CAHA_ANALYSIS"."PUBLICATIONDT", 'yyyy/mm/dd') between '2012/01/01' and '2012/12/31') "PDH_Total_Q3" where "CAHA_ANALYSIS_GRAPHS"."ITEMTAB"=1 and to_char("CAHA_ANALYSIS"."PUBLICATIONDT", 'yyyy/mm/dd') between '2012/01/01' and '2012/12/31' and 3="PDH_Total_Q3"."Level1") "T0") "Union1", (select 1 "Level1", "T0"."C0" "ITEMCD", "T0"."C1" "Count3", "T0"."C2" "Total_Participation", min("T0"."C3") over (partition by "T0"."C0", "T0"."C4") "Percent", "T0"."C4" "ITEMSORT", rank() over ( order by "T0"."C3" desc nulls last) "Rank7" from (select distinct "CAHA_ANALYSIS_GRAPHS"."ITEMCD" "C0", count(CASE WHEN("CAHA_ANALYSIS_GRAPHS"."PARTICIPANTID" IS NULL) OR ("CAHA_ANALYSIS_GRAPHS"."EPISODEID" IS NULL) THEN NULL ELSE ("CAHA_ANALYSIS_GRAPHS"."PARTICIPANTID"||"CAHA_ANALYSIS_GRAPHS"."EPIS ODEID") END) over (partition by "CAHA_ANALYSIS_GRAPHS"."ITEMCD", "CAHA_ANALYSIS_GRAPHS"."ITEMSORT") "C1", min("Disease_History_Rank_Total"."Total_no_of_Participants") over (partition by "CAHA_ANALYSIS_GRAPHS"."ITEMCD", "CAHA_ANALYSIS_GRAPHS"."ITEMSORT") "C2", count(CASE WHEN("CAHA_ANALYSIS_GRAPHS"."PARTICIPANTID" IS NULL) OR ("CAHA_ANALYSIS_GRAPHS"."EPISODEID" IS NULL) THEN NULL ELSE ("CAHA_ANALYSIS_GRAPHS"."PARTICIPANTID"||"CAHA_ANALYSIS_GRAPHS"."EPIS ODEID") END) over (partition by "CAHA_ANALYSIS_GRAPHS"."ITEMCD", "CAHA_ANALYSIS_GRAPHS"."ITEMSORT")/nullif("Disease_History_Rank_Total "."Total_no_of_Participants", 0) "C3", "CAHA_ANALYSIS_GRAPHS"."ITEMSORT" "C4" from ("DATA_ANALYSIS"."MV_CAHA_ANALYSIS_GRAPHS_NEW" "CAHA_ANALYSIS_GRAPHS" RIGHT OUTER JOIN "DATA_ANALYSIS"."CAHA_ANALYSIS" "CAHA_ANALYSIS" on "CAHA_ANALYSIS_GRAPHS"."EPISODEID"="CAHA_ANALYSIS"."EPISODEID" and "CAHA_ANALYSIS_GRAPHS"."PARTICIPANTID"="CAHA_ANALYSIS"."PARTICIPANTID ") INNER JOIN "DATA_ANALYSIS"."MV_CAHA_CUSTOMER_DIMENSION" "CAHA_CUSTOMER_DIMENSION" on "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_CUSTOMER_DIMENSION"."PARTICIPAN TID" and "CAHA_ANALYSIS"."EPISODEID"="CAHA_CUSTOMER_DIMENSION"."EPISO DEID", (select distinct 1 "Level1", count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPISODEID") over () "Total_no_of_Participants" from "DATA_ANALYSIS"."CAHA_ANALYSIS" "CAHA_ANALYSIS", "DATA_ANALYSIS"."MV_CAHA_CUSTOMER_DIMENSION" "CAHA_CUSTOMER_DIMENSION" where to_char("CAHA_ANALYSIS"."PUBLICATIONDT", 'yyyy/mm/dd') between '2012/01/01' and '2012/12/31' and "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR"='HAP' and "CAHA_CUSTOMER_DIMENSION"."CUSTOMER" in ('HAP') and "CAHA_CUSTOMER_DIMENSION"."PROJECT" in ('HAP Members') and "CAHA_ANALYSIS"."PARTICIPANTID"="CAHA_CUSTOMER_DIMENSION"."PARTICIPAN TID" and "CAHA_ANALYSIS"."EPISODEID"="CAHA_CUSTOMER_DIMENSION"."EPISO DEID") "Disease_History_Rank_Total" where "CAHA_ANALYSIS_GRAPHS"."ITEMTAB"=1 and to_char("CAHA_ANALYSIS"."PUBLICATIONDT", 'yyyy/mm/dd') between '2012/01/01' and '2012/12/31' and "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR"='HAP' and "CAHA_CUSTOMER_DIMENSION"."CUSTOMER" in ('HAP') and "CAHA_CUSTOMER_DIMENSION"."PROJECT" in ('HAP Members') and 1="Disease_History_Rank_Total"."Level1") "T0") "Disease_History_Rank" where case when ('3 Column'='2 Column' and "Union1"."Level5"=2) then 'No' else 'Yes' end ='Yes' and not "Union1"."ITEMCD" is null and "Union1"."ITEMCD"="Disease_History_Rank"."ITEMCD" and "Disease_History_Rank"."Rank7"<=15 order by "Rank2" asc nulls last RECOMMENDATION 2: Application Analysis, 3.9% benefit (9222 seconds) ACTION: Investigate the cause for high "IPC send completion sync" waits in Service "dwh1". RECOMMENDATION 3: Application Analysis, 3.6% benefit (8689 seconds) ACTION: Investigate the cause for high "IPC send completion sync" waits with P1 ("send count") value "1". RECOMMENDATION 4: Application Analysis, 3.6% benefit (8627 seconds) ACTION: Investigate the cause for high "IPC send completion sync" waits in Module "BIBusTKServerMain.exe". SYMPTOMS THAT LED TO THE FINDING: SYMPTOM: Wait class "Other" was consuming significant database time. (37% impact [87658 seconds]) FINDING 8: 2.5% impact (6048 seconds) ------------------------------------- Individual SQL statements responsible for significant user I/O wait were found. RECOMMENDATION 1: SQL Tuning, 2.6% benefit (6214 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "8szmwam7fysa3". RELEVANT OBJECT: SQL statement with SQL_ID 8szmwam7fysa3 and PLAN_HASH 2976124318 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 163 times and had an average elapsed time of 37 seconds. RATIONALE: Average time spent in User I/O wait events per execution was 37 seconds. SYMPTOMS THAT LED TO THE FINDING: SYMPTOM: Wait class "User I/O" was consuming significant database time. (69% impact [165667 seconds]) FINDING 9: 2% impact (4856 seconds) ----------------------------------- Inter-instance messaging was consuming significant database time on this instance. NO RECOMMENDATIONS AVAILABLE ADDITIONAL INFORMATION: The network latency of the cluster interconnect was within acceptable limits of 1 milliseconds. Read and write contention on database blocks was not consuming significant database time in the cluster. Global Cache Service Processes (LMSn) in other instances were performing within acceptable limits of 1 milliseconds. Cluster communications that were retried due to lost blocks did not consume significant database time. Waits on "buffer busy" events were not consuming significant database time. SYMPTOMS THAT LED TO THE FINDING: SYMPTOM: Wait class "Cluster" was consuming significant database time. (2.1% impact [5007 seconds]) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ADDITIONAL INFORMATION ---------------------- Wait class "Application" 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. CPU was not a bottleneck for the instance. Wait class "Network" was not consuming significant database time. 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 8% of the analysis period. The analysis of I/O performance is based on the default assumption that the average read time for one database block is 10000 micro-seconds. An explanation of the terminology used in this report is available when you run the report with the 'ALL' level of detail. SYS@dwh12> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options