SQL*Plus: Release 10.2.0.5.0 - Production on Tue Apr 23 11:43:16 2013 Copyright (c) 1982, 2010, Oracle. All Rights Reserved. @> Connected. SYS@dwh11> SYS@dwh11> SYS@dwh11> SYS@dwh11> SYS@dwh11> SYS@dwh11> SYS@dwh11> SYS@dwh11> SYS@dwh11> SYS@dwh11> SYS@dwh11> 2 3 4 PL/SQL procedure successfully completed. SYS@dwh11> SYS@dwh11> 2 3 4 PL/SQL procedure successfully completed. SYS@dwh11> SYS@dwh11> SYS@dwh11> Session altered. SYS@dwh11> 2 3 4 PL/SQL procedure successfully completed. SYS@dwh11> 2 3 4 PL/SQL procedure successfully completed. SYS@dwh11> SYS@dwh11> SYS@dwh11> SYS@dwh11> SYS@dwh11> SYS@dwh11> SYS@dwh11> SYS@dwh11> SYS@dwh11> SYS@dwh11> SYS@dwh11> SYS@dwh11> SYS@dwh11> SYS@dwh11> SYS@dwh11> SYS@dwh11> SYS@dwh11> SYS@dwh11> SYS@dwh11> SYS@dwh11> SYS@dwh11> SYS@dwh11> SYS@dwh11> SYS@dwh11> SYS@dwh11> 2 3 4 5 6 7 8 PL/SQL procedure successfully completed. SYS@dwh11> SYS@dwh11> 2 3 4 5 6 7 8 9 PL/SQL procedure successfully completed. SYS@dwh11> SYS@dwh11> 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@dwh11> SYS@dwh11> SYS@dwh11> SYS@dwh11> SYS@dwh11> SYS@dwh11> 2 DETAILED ADDM REPORT FOR TASK 'TASK_177064' WITH ID 177064 ---------------------------------------------------------- Analysis Period: from 22-APR-2013 00:00 to 23-APR-2013 00:00 Database ID/Instance: 3987792604/1 Database/Instance Names: DWH1/dwh11 Host Name: prod-rac-dwh01.wp-hmi.cl.datapipe.net Database Version: 10.2.0.5.0 Snapshot Range: from 52436 to 52460 Database Time: 229558 seconds Average Database Load: 2.7 active sessions ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ FINDING 1: 100% impact (229558 seconds) --------------------------------------- Significant virtual memory paging was detected on the host operating system. RECOMMENDATION 1: Host Configuration, 100% benefit (229558 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. FINDING 2: 37% impact (85954 seconds) ------------------------------------- Individual database segments responsible for significant user I/O wait were found. RECOMMENDATION 1: Segment Tuning, 24% benefit (54410 seconds) ACTION: Run "Segment Advisor" on TABLE "DATA_ANALYSIS.DS_DA_ACTIVITY" with object id 1785692. RELEVANT OBJECT: database object with id 1785692 ACTION: Investigate application logic involving I/O on TABLE "DATA_ANALYSIS.DS_DA_ACTIVITY" with object id 1785692. RELEVANT OBJECT: database object with id 1785692 RATIONALE: The I/O usage statistics for the object are: 24 full object scans, 5330390 physical reads, 7846 physical writes and 0 direct reads. RATIONALE: The SQL statement with SQL_ID "8xjduhnwackh3" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID 8xjduhnwackh3 select PARTICIPANTID,EPISODEID,PUBLICATIONDT,PIECECD,'BL' FLAG FROM data_analysis.DS_DA_ACTIVITY B WHERE DISTCD='AETN' AND projectcd='aess' and PUBLICATIONDT>=TO_DATE('01/01/2010','MM/DD/YYYY') AND CUSTOMQ2 is not null AND POPTIN IN ('Forced','Yes') union select DISTINCT PARTICIPANTID,EPISODEID,trunc(EVAL1DATE) EVAL1DATE,PIECECD,'E1' FLAG FROM DS_DA_ACTIVITY WHERE DISTCD='AETN' and PROJECTCD='aess' AND PUBLICATIONDT>=TO_DATE('01/01/2010','MM/DD/YYYY') AND EVAL1DATE IS NOT NULL AND POPTIN IN ('Forced','Yes') UNION select DISTINCT PARTICIPANTID,EPISODEID,trunc(EVAL2DATE) EVAL2DATE,PIECECD,'E2' FLAG FROM DS_DA_ACTIVITY WHERE DISTCD='AETN' and PROJECTCD='aess' AND PUBLICATIONDT>=TO_DATE('01/01/2010','MM/DD/YYYY') AND EVAL2DATE IS NOT NULL AND POPTIN IN ('Forced','Yes') UNION select DISTINCT PARTICIPANTID,EPISODEID,trunc(EVAL3DATE) EVAL3DATE,PIECECD,'E3' FLAG FROM DS_DA_ACTIVITY WHERE DISTCD='AETN' and PROJECTCD='aess' AND PUBLICATIONDT>=TO_DATE('01/01/2010','MM/DD/YYYY') AND EVAL3DATE IS NOT NULL AND POPTIN IN ('Forced','Yes') RATIONALE: The SQL statement with SQL_ID "6nbcd8rtwyqb9" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID 6nbcd8rtwyqb9 (SELECT DISTINCT 'Succeed',PARTICIPANTID,EPISODEID FROM HBA_ANALYSIS1 where instrumentcd IN ('hba','chba') MINUS SELECT DISTINCT 'Succeed' as Program,PARTICIPANTID,EPISODEID FROM DS_DA_ACTIVITY where instrumentcd IN ('hba','chba') AND PUBLICATIONDT IS NOT NULL) union (SELECT DISTINCT 'Balance',PARTICIPANTID,EPISODEID FROM BLNA_ANALYSIS where instrumentcd='blna' minus SELECT DISTINCT 'Balance' as Program,PARTICIPANTID,EPISODEID FROM DS_DA_ACTIVITY where instrumentcd ='blna' AND PUBLICATIONDT IS NOT NULL) union (SELECT DISTINCT 'Breathe',PARTICIPANTID,EPISODEID FROM BREA_ANALYSIS1 where instrumentcd ='brea' minus SELECT DISTINCT 'Breathe' as Program,PARTICIPANTID,EPISODEID FROM DS_DA_ACTIVITY where instrumentcd='brea' AND PUBLICATIONDT IS NOT NULL) union (SELECT DISTINCT 'Relax',PARTICIPANTID,EPISODEID FROM RLXA_ANALYSIS1 where instrumentcd ='rlxa' minus SELECT DISTINCT 'Relax' as Program,PARTICIPANTID,EPISODEID FROM DS_DA_ACTIVITY where instrumentcd ='rlxa' AND PUBLICATIONDT IS NOT NULL) union ( SELECT DISTINCT 'Care for Diabetes',PARTICIPANTID,EPISODEID FROM CADB_ANALYSIS1 where instrumentcd='cadb' minus SELECT DISTINCT 'Care for Diabetes' as Program,PARTICIPANTID,EPISODEID FROM DS_DA_ACTIVITY where instrumentcd ='cadb' AND PUBLICATIONDT IS NOT NULL) union (SELECT DISTINCT 'Nourish',PARTICIPANTID,EPISODEID FROM NOUA_ANALYSIS1 where instrumentcd='noua' minus SELECT DISTINCT 'Nourish' as Program,PARTICIPANTID,EPISODEID FROM DS_DA_ACTIVITY where instrumentcd ='noua' AND PUBLICATIONDT IS NOT NULL) union (SELECT DISTINCT 'Care for Back',PARTICIPANTID,EPISODEID FROM CABA_ANALYSIS where instrumentcd='caba' minus SELECT DISTINCT 'Care for Back' as Program,PARTICIPANTID,EPISODEID FROM DS_DA_ACTIVITY where instrumentcd ='caba' AND PUBLICATIONDT IS NOT NULL) union (SELECT DISTINCT 'Care for Health',PARTICIPANTID,EPISODEID FROM CAHA_ANALYSIS where instrumentcd='caha' minus SELECT DISTINCT 'Care for Health' as Program,PARTICIPANTID,EPISODEID FROM DS_DA_ACTIVITY where instrumentcd ='caha' AND PUBLICATIONDT IS NOT NULL) union (SELECT DISTINCT 'Care for Pain',PARTICIPANTID,EPISODEID FROM CAPA_ANALYSIS1 where instrumentcd='capa' minus SELECT DISTINCT 'Care for Pain' as Program,PARTICIPANTID,EPISODEID FROM DS_DA_ACTIVITY where instrumentcd ='capa' AND PUBLICATIONDT IS NOT NULL) UNION (SELECT DISTINCT 'Binge',PARTICIPANTID,EPISODEID FROM BING_ANALYSIS1 where instrumentcd='bing' minus SELECT DISTINCT 'Binge' as Program,PARTICIPANTID,EPISODEID FROM DS_DA_ACTIVITY where instrumentcd ='bing' AND PUBLICATIONDT IS NOT NULL) RATIONALE: The SQL statement with SQL_ID "4x8jgn9uzsndd" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID 4x8jgn9uzsndd (SELECT DISTINCT 'Succeed' as Program,PARTICIPANTID,EPISODEID FROM DS_DA_ACTIVITY where instrumentcd IN ('hba','chba') AND PUBLICATIONDT IS NOT NULL and TRUNC(PUBLICATIONDT)<=TRUNC(SYSDATE-1) MINUS SELECT DISTINCT 'Succeed',PARTICIPANTID,EPISODEID FROM HBA_ANALYSIS1 where instrumentcd IN ('hba','chba')) union (SELECT DISTINCT 'Balance' as Program,PARTICIPANTID,EPISODEID FROM DS_DA_ACTIVITY where instrumentcd ='blna' AND PUBLICATIONDT IS NOT NULL and TRUNC(PUBLICATIONDT)<=TRUNC(SYSDATE-1) MINUS SELECT DISTINCT 'Balance',PARTICIPANTID,EPISODEID FROM BLNA_ANALYSIS where instrumentcd='blna') UNION (SELECT DISTINCT 'Breathe' as Program,PARTICIPANTID,EPISODEID FROM DS_DA_ACTIVITY where instrumentcd='brea' AND PUBLICATIONDT IS NOT NULL and TRUNC(PUBLICATIONDT)<=TRUNC(SYSDATE-1) MINUS SELECT DISTINCT 'Breathe',PARTICIPANTID,EPISODEID FROM BREA_ANALYSIS1 where instrumentcd ='brea') UNION (SELECT DISTINCT 'Relax' as Program,PARTICIPANTID,EPISODEID FROM DS_DA_ACTIVITY where instrumentcd ='rlxa' AND PUBLICATIONDT IS NOT NULL and TRUNC(PUBLICATIONDT)<=TRUNC(SYSDATE-1) MINUS SELECT DISTINCT 'Relax',PARTICIPANTID,EPISODEID FROM RLXA_ANALYSIS1 where instrumentcd ='rlxa') UNION (SELECT DISTINCT 'Care for Diabetes' as Program,PARTICIPANTID,EPISODEID FROM DS_DA_ACTIVITY where instrumentcd ='cadb' AND PUBLICATIONDT IS NOT NULL and TRUNC(PUBLICATIONDT)<=TRUNC(SYSDATE-1) MINUS SELECT DISTINCT 'Care for Diabetes',PARTICIPANTID,EPISODEID FROM CADB_ANALYSIS1 where instrumentcd='cadb') union (SELECT DISTINCT 'Nourish' as Program,PARTICIPANTID,EPISODEID FROM DS_DA_ACTIVITY where instrumentcd ='noua' AND PUBLICATIONDT IS NOT NULL and TRUNC(PUBLICATIONDT)<=TRUNC(SYSDATE-1) MINUS SELECT DISTINCT 'Nourish',PARTICIPANTID,EPISODEID FROM NOUA_ANALYSIS1 where instrumentcd='noua') UNION (SELECT DISTINCT 'Care for Back' as Program,PARTICIPANTID,EPISODEID FROM DS_DA_ACTIVITY where instrumentcd ='caba' AND PUBLICATIONDT IS NOT NULL and TRUNC(PUBLICATIONDT)<=TRUNC(SYSDATE-1) MINUS SELECT DISTINCT 'Care for Back',PARTICIPANTID,EPISODEID FROM CAba_ANALYSIS where instrumentcd='caba') union (SELECT DISTINCT 'Care for Health' as Program,PARTICIPANTID,EPISODEID FROM DS_DA_ACTIVITY where instrumentcd ='caha' AND PUBLICATIONDT IS NOT NULL and TRUNC(PUBLICATIONDT)<=TRUNC(SYSDATE-1) MINUS SELECT DISTINCT 'Care for Health',PARTICIPANTID,EPISODEID FROM CAHA_ANALYSIS where instrumentcd='caha') UNION (SELECT DISTINCT 'Binge' as Program,PARTICIPANTID,EPISODEID FROM DS_DA_ACTIVITY where instrumentcd ='bing' AND PUBLICATIONDT IS NOT NULL and TRUNC(PUBLICATIONDT)<=TRUNC(SYSDATE-1) MINUS SELECT DISTINCT 'Binge',PARTICIPANTID,EPISODEID FROM BING_ANALYSIS1 where instrumentcd='bing') union (SELECT DISTINCT 'Care for Pain' as Program,PARTICIPANTID,EPISODEID FROM DS_DA_ACTIVITY where instrumentcd ='capa' AND PUBLICATIONDT IS NOT NULL and TRUNC(PUBLICATIONDT)<=TRUNC(SYSDATE-1) MINUS SELECT DISTINCT 'Care for Pain',PARTICIPANTID,EPISODEID FROM CAPA_ANALYSIS1 where instrumentcd='capa') RATIONALE: The SQL statement with SQL_ID "dasa2vb1zqs6q" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID dasa2vb1zqs6q update DATA_ANALYSIS.DS_DA_ACTIVITY set CUSTOMQ6=null WHERE DISTCD='KPPL' AND POPTIN='Forced' and UPPER(CUSTOMQ6)='NO' and TRUNC(PUBLICATIONDT)>=TO_DATE('09/27/2011','MM/DD/YYYY') RATIONALE: The SQL statement with SQL_ID "bdsw0u9jruxst" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID bdsw0u9jruxst update DATA_ANALYSIS.DS_DA_ACTIVITY set PAUTHCD='KPPRINTAUTHCD' WHERE DISTCD='KPPL' AND PAUTHCD IS NULL RECOMMENDATION 2: Segment Tuning, 7.4% benefit (17017 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: 24 full object scans, 8626943 physical reads, 298 physical writes and 0 direct reads. RATIONALE: The SQL statement with SQL_ID "78jjjk004kg9q" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID 78jjjk004kg9q SELECT a1.participantid, a1.episodeid , a1.publicationdt FROM data_analysis.hba_analysis1@hlt1 a1 --minus where not exists ( select '1' from data_analysis.hba_analysis1 b1 where a1.participantid = b1.participantid and a1.episodeid =b1.episodeid and a1.publicationdt =b1.publicationdt ) RATIONALE: The SQL statement with SQL_ID "0zb3452hna6nj" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID 0zb3452hna6nj CREATE TABLE DSPAPTEMP AS select t1.participantid,t1.episodeid,T1.distcd,T1.projectcd,PUBLICATI ONDT,INITIALTAILORDT,firstname,lastname,gendercd,CASE WHEN gendercd = 'Female' AND t2.hscrpap = 'Yes' THEN 1 WHEN gendercd = 'Female' AND t13.hscrpapv2 = 'LessThan1Year' THEN 1 ELSE 0 END scr_pap, CASE WHEN gendercd = 'Female' AND age >= 40 AND t2.hscrmammogram = 'Yes' THEN 1 WHEN gendercd = 'Female' AND age >= 40 AND t13.hscrmammogramv2 = 'LessThan1Year' THEN 1 ELSE 0 END scr_mammogram, CASE WHEN t2.hscrbp12months = 'Yes' THEN 1 WHEN t13.hscrbpv2 = 'LessThan1Year' THEN 1 ELSE 0 END scr_bp12mos FROM data_analysis.hba_analysis1 t1, data_analysis.hba_analysis2 t2, data_analysis.hba_analysis3 t3, data_analysis.hba_analysis4 t4, data_analysis.hba_analysis5 t5, data_analysis.hba_analysis6 t11, data_analysis.hba_healthrisk_esr t6, data_analysis.hba_analysis7 t10, data_analysis.hba_analysis11 t13, data_analysis.hba_analysis9 t12, data_mart.v_t1t2_customertx t7, data_mart.customer_dimension t8 WHERE t1.participantid = t2.participantid(+) AND t1.episodeid = t2.episodeid(+) AND t1.participantid = t3.participantid(+) AND t1.episodeid = t3.episodeid(+) AND t1.participantid = t4.participantid(+) AND t1.episodeid = t4.episodeid(+) AND t1.participantid = t5.participantid(+) AND t1.episodeid = t5.episodeid(+) AND t1.participantid = t6.participantid(+) AND t1.episodeid = t6.episodeid(+) AND t1.participantid = t11.participantid(+) AND t1.episodeid = t11.episodeid(+) AND t1.participantid = t10.participantid(+) AND t1.episodeid = t10.episodeid(+) AND t1.participantid = t7.participantid AND t1.episodeid = t7.episodeid AND t1.participantid = t12.participantid(+) AND t1.episodeid = t12.episodeid(+) AND t1.participantid = t13.participantid(+) AND t1.episodeid = t13.episodeid(+) AND t7.sitecd = t8.sitecd AND t7.auth_code = t8.auth_code AND t7.LOCATION = t8.LOCATION and trunc(publicationdt)>=TRUNC(SYSDATE-4) RATIONALE: The SQL statement with SQL_ID "5gqfbh477b91g" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID 5gqfbh477b91g RATIONALE: The SQL statement with SQL_ID "fxscuv2hv8z5b" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID fxscuv2hv8z5b SELECT distinct S1.PARTICIPANTID,S1.EPISODEID,S1.FIRSTNAME,S1.LASTNAM E,S1.UMEMBERID,S1.PROJECTX,'Succeed' as Program, s1.publicationdt,e.lastmodifiedt FROM DATAWAREHOUSE.DWH_EPISODEDATA E, DATA_ANALYSIS.HBA_ANALYSIS1 S1 WHERE S1.PARTICIPANTID=E.PARTICIPANTID AND S1.EPISODEID=E.EPISODEID AND E.INSTRUMENTCD='deft' AND S1.TESTDATA IS NULL union SELECT distinct S1.PARTICIPANTID,S1.EPISODEID,S1.FIRSTNAME,S1.LASTNAM E,S1.UMEMBERID,S1.PROJECTX,'Balance' as Program, s1.publicationdt,e.lastmodifiedt FROM DATAWAREHOUSE.DWH_EPISODEDATA E, DATA_ANALYSIS.BLNA_ANALYSIS S1 WHERE S1.PARTICIPANTID=E.PARTICIPANTID AND S1.EPISODEID=E.EPISODEID AND E.INSTRUMENTCD='deft' AND S1.TESTDATA IS NULL union SELECT distinct S1.PARTICIPANTID,S1.EPISODEID,S1.FIRSTNAME,S1.LASTNAM E,S1.UMEMBERID,S1.PROJECTX,'Breathe' as Program, s1.publicationdt,e.lastmodifiedt FROM DATAWAREHOUSE.DWH_EPISODEDATA E, DATA_ANALYSIS.BREA_ANALYSIS1 S1 WHERE S1.PARTICIPANTID=E.PARTICIPANTID AND S1.EPISODEID=E.EPISODEID AND E.INSTRUMENTCD='deft' AND S1.TESTDATA IS NULL UNION SELECT distinct S1.PARTICIPANTID,S1.EPISODEID,S1.FIRSTNAME,S1.LASTNAM E,S1.UMEMBERID,S1.PROJECTX,'Relax' as Program, s1.publicationdt,e.lastmodifiedt FROM DATAWAREHOUSE.DWH_EPISODEDATA E, DATA_ANALYSIS.RLXA_ANALYSIS1 S1 WHERE S1.PARTICIPANTID=E.PARTICIPANTID AND S1.EPISODEID=E.EPISODEID AND E.INSTRUMENTCD='deft' AND S1.TESTDATA IS NULL union SELECT distinct S1.PARTICIPANTID,S1.EPISODEID,S1.FIRSTNAME,S1.LASTNAM E,S1.UMEMBERID,S1.PROJECTX,'Nourish' as Program, s1.publicationdt,e.lastmodifiedt FROM DATAWAREHOUSE.DWH_EPISODEDATA E, DATA_ANALYSIS.NOUA_ANALYSIS1 S1 WHERE S1.PARTICIPANTID=E.PARTICIPANTID AND S1.EPISODEID=E.EPISODEID AND E.INSTRUMENTCD='deft' AND S1.TESTDATA IS NULL union SELECT distinct S1.PARTICIPANTID,S1.EPISODEID,S1.FIRSTNAME,S1.LASTNAM E,S1.UMEMBERID,S1.PROJECTX,'Care for Diabetes' as Program, s1.publicationdt,e.lastmodifiedt FROM DATAWAREHOUSE.DWH_EPISODEDATA E, DATA_ANALYSIS.CADB_ANALYSIS1 S1 WHERE S1.PARTICIPANTID=E.PARTICIPANTID AND S1.EPISODEID=E.EPISODEID AND E.INSTRUMENTCD='deft' AND S1.TESTDATA IS NULL union SELECT distinct S1.PARTICIPANTID,S1.EPISODEID,S1.FIRSTNAME,S1.LASTNAM E,S1.UMEMBERID,S1.PROJECTX,'Care for Health' as Program, s1.publicationdt,e.lastmodifiedt FROM DATAWAREHOUSE.DWH_EPISODEDATA E, DATA_ANALYSIS.CAHA_ANALYSIS S1 WHERE S1.PARTICIPANTID=E.PARTICIPANTID AND S1.EPISODEID=E.EPISODEID AND E.INSTRUMENTCD='deft' AND S1.TESTDATA IS NULL union SELECT distinct S1.PARTICIPANTID,S1.EPISODEID,S1.FIRSTNAME,S1.LASTNAM E,S1.UMEMBERID,S1.PROJECTX,'Care for Back' as Program, s1.publicationdt,e.lastmodifiedt FROM DATAWAREHOUSE.DWH_EPISODEDATA E, DATA_ANALYSIS.CABA_ANALYSIS S1 WHERE S1.PARTICIPANTID=E.PARTICIPANTID AND S1.EPISODEID=E.EPISODEID AND E.INSTRUMENTCD='deft' AND S1.TESTDATA IS NULL union SELECT distinct S1.PARTICIPANTID,S1.EPISODEID,S1.FIRSTNAME,S1.LASTNAM E,S1.UMEMBERID,S1.PROJECTX,'Depression' as Program, s1.publicationdt,e.lastmodifiedt FROM DATAWAREHOUSE.DWH_EPISODEDATA E, DATA_ANALYSIS.DEPN_ANALYSIS1 S1 WHERE S1.PARTICIPANTID=E.PARTICIPANTID AND S1.EPISODEID=E.EPISODEID AND E.INSTRUMENTCD='deft' AND S1.TESTDATA IS NULL UNION SELECT distinct S1.PARTICIPANTID,S1.EPISODEID,S1.FIRSTNAME,S1.LASTNAM E,S1.UMEMBERID,S1.PROJECTX,'Insomnia' as Program, s1.publicationdt,e.lastmodifiedt FROM DATAWAREHOUSE.DWH_EPISODEDATA E, DATA_ANALYSIS.INSM_ANALYSIS1 S1 WHERE S1.PARTICIPANTID=E.PARTICIPANTID AND S1.EPISODEID=E.EPISODEID AND E.INSTRUMENTCD='deft' AND S1.TESTDATA IS NULL RATIONALE: The SQL statement with SQL_ID "025rbn51j5u2a" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID 025rbn51j5u2a select distinct pc.FIRSTNAME||chr(9)|| pc.LASTNAME||chr(9)|| pc.umemberid||chr(9)|| pc.customq1||chr(9)|| pc.BIRTHDATE||chr(9)|| pc.ADDRESS1||chr(9)|| pc.ADDRESS2||chr(9)|| pc.CITY||chr(9)|| pc.STATE||chr(9)|| pc.ZIP||chr(9)|| pc.EMAILADDRESS||chr(9)|| pc.emailnew||chr(9)|| pc.PLOCATION||chr(9)|| pc.participantid||chr(9)|| (SELECT DISTINCT MAX(s.publicationdt) from data_analysis.ds_da_activity s WHERE s.participantid = pc.participantid AND s.INSTRUMENTCD in ('chba','chbs') AND lower(s.piececd) in ('chbamas','mas'))||chr(9)|| null||chr(9)|| pc.xid||chr(9)|| pc.customq4||chr(9)|| pc.xgroup||chr(9)|| pc.xclient||chr(9)|| pc.gendercd||chr(9)|| pc.enrollmethodcd||chr(9)|| pc.projectcd||chr(9)|| pc.pauthcd||chr(9)|| pc.languagecd||chr(9)|| case when HSCRSBP IS NOT NULL AND HSCRSBP<>'-2' THEN 1 WHEN (HSCRBPV2='DontKnow' or HSCRBPDONTKNOW in ('true','DontKnow')) THEN 3 WHEN (HSCRBPDONTKNOW='false' or HSCRBPDONTKNOW IS NULL) AND HSCRSBP IS NULL THEN 4 END||chr(9)|| case when HSCRDBP IS NOT NULL AND HSCRDBP<>'-2' THEN 1 WHEN (HSCRBPV2='DontKnow' or HSCRBPDONTKNOW in ('true','DontKnow')) THEN 3 WHEN (HSCRBPDONTKNOW='false' or HSCRBPDONTKNOW IS NULL) AND HSCRDBP IS NULL THEN 4 END||chr(9)|| case when HSCRCHOLESTEROL IS NOT NULL AND HSCRCHOLESTEROL<>'-2' THEN 1 WHEN HScrCholesterol5Yrs='No' THEN 2 WHEN HSCRCHOLESTEROLDONTKNOW in ('DontKnow','true') THEN 3 WHEN (HSCRCHOLESTEROL5YRS in ('Yes','DontKnow') or HSCRCHOLESTEROL5YRS is null) and (HSCRCHOLESTEROLDONTKNOW ='false' or HSCRCHOLESTEROLDONTKNOW IS NULL) AND HSCRCHOLESTEROL IS NULL THEN 4 END||chr(9)|| case when HSCRHDL IS NOT NULL AND HSCRHDL<>'-2' THEN 1 WHEN HScrCholesterol5Yrs='No' THEN 2 WHEN HSCRHDLDONTKNOW in ('true','DontKnow') THEN 3 WHEN (HSCRCHOLESTEROL5YRS in ('Yes','DontKnow') or HSCRCHOLESTEROL5YRS is null) and (HSCRHDLDONTKNOW ='false' or HSCRHDLDONTKNOW IS NULL) AND HSCRHDL IS NULL THEN 4 END||chr(9)|| case when HSCRLDL IS NOT NULL AND HSCRLDL<>'-2' THEN 1 WHEN HScrCholesterol5Yrs='No' THEN 2 WHEN HSCRLDLDONTKNOW IN ('DontKnow','true') THEN 3 WHEN (HSCRCHOLESTEROL5YRS in ('Yes','DontKnow') or HSCRCHOLESTEROL5YRS is null) and (HSCRLDLDONTKNOW ='false' or HSCRLDLDONTKNOW IS NULL) AND HSCRLDL IS NULL THEN 4 END||chr(9)|| case when HSCRTRIGLYCERIDEV5 IS NOT NULL AND HSCRTRIGLYCERIDEV5<>'-2' THEN 1 WHEN HScrCholesterol5Yrs='No' THEN 2 WHEN HScrTriglycerideDontKnowV5 in ('true','DontKnow') THEN 3 WHEN (HSCRCHOLESTEROL5YRS in ('Yes','DontKnow') or HSCRCHOLESTEROL5YRS is null) and (HScrTriglycerideDontKnowV5 ='false' or HScrTriglycerideDontKnowV5 IS NULL) AND HSCRTRIGLYCERIDEV5 IS NULL THEN 4 END||chr(9)|| case when WTHEIGHTFEET IS NOT NULL AND WTHEIGHTFEET<>'-2' THEN 1 WHEN WOMPREGNANCY IN ('Pregnant','BirthLastThreeMos') THEN 2 WHEN (WOMPREGNANCY NOT IN ('Pregnant','BirthLastThreeMos') or WOMPREGNANCY IS NULL) and WTHEIGHTFEET IS NULL THEN 4 END||chr(9)|| case when WTHEIGHTINCHES IS NOT NULL AND WTHEIGHTINCHES<>'-2' THEN 1 WHEN WOMPREGNANCY IN ('Pregnant','BirthLastThreeMos') THEN 2 WHEN (WOMPREGNANCY NOT IN ('Pregnant','BirthLastThreeMos') or WOMPREGNANCY IS NULL) and WTHEIGHTINCHES IS NULL THEN 4 END||chr(9)|| case when WTWEIGHT IS NOT NULL AND WTWEIGHT<>'-2' THEN 1 WHEN WOMPREGNANCY IN ('Pregnant','BirthLastThreeMos') THEN 2 WHEN (WOMPREGNANCY NOT IN ('Pregnant','BirthLastThreeMos') or WOMPREGNANCY IS NULL) and WTWEIGHT IS NULL THEN 4 END||chr(9)|| case when WTWAIST IS NOT NULL AND WTWAIST<>'-2' THEN 1 WHEN WOMPREGNANCY IN ('Pregnant','BirthLastThreeMos') THEN 2 WHEN (WOMPREGNANCY NOT IN ('Pregnant','BirthLastThreeMos') or WOMPREGNANCY IS NULL) and WTWAIST IS NULL THEN 4 END||chr(9)|| case when HSCRBLOODGLUCOSE IS NOT NULL AND HSCRBLOODGLUCOSE<>'-2' THEN 1 WHEN HSCRBLOODGLUCOSE3YRS='No' then 2 WHEN HSCRBLOODGLUCOSEDONTKNOW in ('true','DontKnow') THEN 3 WHEN (HSCRBLOODGLUCOSE3YRS in ('Yes','DontKnow') or HSCRBLOODGLUCOSE3YRS IS NULL) and (HSCRBLOODGLUCOSEDONTKNOW='false' or HSCRBLOODGLUCOSEDONTKNOW is null) and HSCRBLOODGLUCOSE IS NULL THEN 4 END FROM DATA_ANALYSIS.HBA_ANALYSIS1 PC, datawarehouse.dwh_CHBABIONUTPHYWOM S2, datawarehouse.dwh_CHBASCRSEXDRUGCAN S3, data_analysis.ds_da_activity DA WHERE PC.PARTICIPANTID=DA.PARTICIPANTID AND PC.EPISODEID=DA.EPISODEID AND DA.PIECECD IN ('chbamas','mas') AND PC.INSTRUMENTCD='chba' AND PC.CUSTOMERCD in ('KPPL','KPPLS') AND PC.PARTICIPANTID=S2.PARTICIPANTID AND PC.EPISODEID=S2.EPISODEID AND PC.PARTICIPANTID=S3.PARTICIPANTID AND PC.EPISODEID=S3.EPISODEID AND (pc.CUSTOMQ6 in ('Yes','yes','YES') OR (pc.POPTIN='Forced' AND pc.CUSTOMQ6 is null)) and TO_CHAR(pc.publicationdt,'YYYY')=to_char(sysdate-1,'YYYY') AND (NOT ((pc.customq4='01' and trim(pc.plocation) in ('8','10','12','600453','603020','39300','603019')) OR (pc.customq4='02' and trim(pc.plocation) in ('100200','100201','100199','227351','230284','230283','226159')) OR (pc.customq4='16' and trim(pc.plocation) in ('849','2','1923','2608','1919','8809','28837')) OR (pc.customq4='28' and trim(pc.plocation) in ('2','1100','1','2211','2627','2694','1104','1105','1106')) OR (pc.customq4='12' and trim(pc.plocation) in ('36836','137','32717')) OR (pc.customq4='30' and trim(pc.plocation) in ('4727','9000','13642','13644','13818','19965','19966','19967','13700 ')) OR (pc.customq4='10' and trim(pc.plocation) in ('2503','2504','5000','5001','5002','5004','5006','5007','6000','5003 ','15963','9424')) OR (pc.customq4='14' and trim(pc.plocation) in ('1340','1301','1306')) or pc.PAUTHCD IN ('KPHWFNM','KPHWFNMS') ) OR ((pc.enrollmethodcd='Telephone' and pc.projectcd in ('kpti','kptis'))) ) and pc.publicationdt = (select max(publicationdt) from DATA_ANALYSIS.DS_DA_ACTIVITY S21 WHERE S21.PARTICIPANTID=Pc.PARTICIPANTID AND S21.INSTRUMENTCD='chba' and S21.piececd is not null) RECOMMENDATION 3: Segment Tuning, 6.3% benefit (14528 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: 17 full object scans, 810697 physical reads, 9552 physical writes and 0 direct reads. RATIONALE: The SQL statement with SQL_ID "abxhrs7rbwd92" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID abxhrs7rbwd92 CREATE TABLE data_analysis.huma_participantids tablespace users AS SELECT p.PARTICIPANTID, e.EPISODEID FROM PARTICIPANT p, EPISODES e, PIECESTATUS ps, DISTRIBUTOR d, CUSTOMER c, PROJECT pj, SITE s WHERE p.PARTICIPANTID = e.PARTICIPANTID AND e.PARTICIPANTID = ps.PARTICIPANTID AND e.EPISODEID = ps.EPISODEID AND p.SITECD = s.SITECD AND s.PROJECTCD = pj.PROJECTCD AND pj.CUSTOMERCD = c.CUSTOMERCD AND c.DISTCD = d.DISTCD AND e.INSTRUMENTCD = 'huma' AND upper(PIECECD) like '%MAS' AND p.TESTDATA IS NULL AND trunc(PUBLICATIONDT) <= trunc(SYSDATE-1) minus (SELECT PARTICIPANTID, EPISODEID from data_analysis.humaanalysis) RATIONALE: The SQL statement with SQL_ID "fxscuv2hv8z5b" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID fxscuv2hv8z5b SELECT distinct S1.PARTICIPANTID,S1.EPISODEID,S1.FIRSTNAME,S1.LASTNAM E,S1.UMEMBERID,S1.PROJECTX,'Succeed' as Program, s1.publicationdt,e.lastmodifiedt FROM DATAWAREHOUSE.DWH_EPISODEDATA E, DATA_ANALYSIS.HBA_ANALYSIS1 S1 WHERE S1.PARTICIPANTID=E.PARTICIPANTID AND S1.EPISODEID=E.EPISODEID AND E.INSTRUMENTCD='deft' AND S1.TESTDATA IS NULL union SELECT distinct S1.PARTICIPANTID,S1.EPISODEID,S1.FIRSTNAME,S1.LASTNAM E,S1.UMEMBERID,S1.PROJECTX,'Balance' as Program, s1.publicationdt,e.lastmodifiedt FROM DATAWAREHOUSE.DWH_EPISODEDATA E, DATA_ANALYSIS.BLNA_ANALYSIS S1 WHERE S1.PARTICIPANTID=E.PARTICIPANTID AND S1.EPISODEID=E.EPISODEID AND E.INSTRUMENTCD='deft' AND S1.TESTDATA IS NULL union SELECT distinct S1.PARTICIPANTID,S1.EPISODEID,S1.FIRSTNAME,S1.LASTNAM E,S1.UMEMBERID,S1.PROJECTX,'Breathe' as Program, s1.publicationdt,e.lastmodifiedt FROM DATAWAREHOUSE.DWH_EPISODEDATA E, DATA_ANALYSIS.BREA_ANALYSIS1 S1 WHERE S1.PARTICIPANTID=E.PARTICIPANTID AND S1.EPISODEID=E.EPISODEID AND E.INSTRUMENTCD='deft' AND S1.TESTDATA IS NULL UNION SELECT distinct S1.PARTICIPANTID,S1.EPISODEID,S1.FIRSTNAME,S1.LASTNAM E,S1.UMEMBERID,S1.PROJECTX,'Relax' as Program, s1.publicationdt,e.lastmodifiedt FROM DATAWAREHOUSE.DWH_EPISODEDATA E, DATA_ANALYSIS.RLXA_ANALYSIS1 S1 WHERE S1.PARTICIPANTID=E.PARTICIPANTID AND S1.EPISODEID=E.EPISODEID AND E.INSTRUMENTCD='deft' AND S1.TESTDATA IS NULL union SELECT distinct S1.PARTICIPANTID,S1.EPISODEID,S1.FIRSTNAME,S1.LASTNAM E,S1.UMEMBERID,S1.PROJECTX,'Nourish' as Program, s1.publicationdt,e.lastmodifiedt FROM DATAWAREHOUSE.DWH_EPISODEDATA E, DATA_ANALYSIS.NOUA_ANALYSIS1 S1 WHERE S1.PARTICIPANTID=E.PARTICIPANTID AND S1.EPISODEID=E.EPISODEID AND E.INSTRUMENTCD='deft' AND S1.TESTDATA IS NULL union SELECT distinct S1.PARTICIPANTID,S1.EPISODEID,S1.FIRSTNAME,S1.LASTNAM E,S1.UMEMBERID,S1.PROJECTX,'Care for Diabetes' as Program, s1.publicationdt,e.lastmodifiedt FROM DATAWAREHOUSE.DWH_EPISODEDATA E, DATA_ANALYSIS.CADB_ANALYSIS1 S1 WHERE S1.PARTICIPANTID=E.PARTICIPANTID AND S1.EPISODEID=E.EPISODEID AND E.INSTRUMENTCD='deft' AND S1.TESTDATA IS NULL union SELECT distinct S1.PARTICIPANTID,S1.EPISODEID,S1.FIRSTNAME,S1.LASTNAM E,S1.UMEMBERID,S1.PROJECTX,'Care for Health' as Program, s1.publicationdt,e.lastmodifiedt FROM DATAWAREHOUSE.DWH_EPISODEDATA E, DATA_ANALYSIS.CAHA_ANALYSIS S1 WHERE S1.PARTICIPANTID=E.PARTICIPANTID AND S1.EPISODEID=E.EPISODEID AND E.INSTRUMENTCD='deft' AND S1.TESTDATA IS NULL union SELECT distinct S1.PARTICIPANTID,S1.EPISODEID,S1.FIRSTNAME,S1.LASTNAM E,S1.UMEMBERID,S1.PROJECTX,'Care for Back' as Program, s1.publicationdt,e.lastmodifiedt FROM DATAWAREHOUSE.DWH_EPISODEDATA E, DATA_ANALYSIS.CABA_ANALYSIS S1 WHERE S1.PARTICIPANTID=E.PARTICIPANTID AND S1.EPISODEID=E.EPISODEID AND E.INSTRUMENTCD='deft' AND S1.TESTDATA IS NULL union SELECT distinct S1.PARTICIPANTID,S1.EPISODEID,S1.FIRSTNAME,S1.LASTNAM E,S1.UMEMBERID,S1.PROJECTX,'Depression' as Program, s1.publicationdt,e.lastmodifiedt FROM DATAWAREHOUSE.DWH_EPISODEDATA E, DATA_ANALYSIS.DEPN_ANALYSIS1 S1 WHERE S1.PARTICIPANTID=E.PARTICIPANTID AND S1.EPISODEID=E.EPISODEID AND E.INSTRUMENTCD='deft' AND S1.TESTDATA IS NULL UNION SELECT distinct S1.PARTICIPANTID,S1.EPISODEID,S1.FIRSTNAME,S1.LASTNAM E,S1.UMEMBERID,S1.PROJECTX,'Insomnia' as Program, s1.publicationdt,e.lastmodifiedt FROM DATAWAREHOUSE.DWH_EPISODEDATA E, DATA_ANALYSIS.INSM_ANALYSIS1 S1 WHERE S1.PARTICIPANTID=E.PARTICIPANTID AND S1.EPISODEID=E.EPISODEID AND E.INSTRUMENTCD='deft' AND S1.TESTDATA IS NULL RATIONALE: The SQL statement with SQL_ID "6rt8nh49stf3a" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID 6rt8nh49stf3a CREATE TABLE SNAIR25.TEMP_BOB_DATA1 AS SELECT DISTINCT P.PARTICIPANTID, E.EPISODEID, MEDHISTPERS_HA, MEDHISTPERS_ANGINA, MEDHISTPERS_HBP, MEDHISTPERS_CHD, MEDHISTPERS_CHF, MEDHISTPERS_OHD, MEDHISTPERS_STROKE, MEDHISTPERS_TIA, MEDHISTPERS_PVD, MEDHISTPERS_EMPHYSEMA, MEDHISTPERS_CHRONBRON, MEDHISTPERS_ASTHMA, MEDHISTPERS_COLORECTAL, MEDHISTPERS_SKIN, MEDHISTPERS_BREAST, MEDHISTPERS_CERVICAL, MEDHISTPERS_PROSTATE, MEDHISTPERS_HICHOL, MEDHISTPERS_HIBLOODSUGAR, MEDHISTPERS_HEPATITISB, MEDHISTPERS_STD, MEDHISTPERS_DIABETES, MEDHISTPERS_METABOLIC, MEDHISTPERS_OBESITY, MEDHISTPERS_OSTEO, MEDHISTPERS_CHRONPAIN, MEDHISTPERS_BACKPAIN, MEDHISTPERS_DEPRESSION, MEDHISTPERS_INSOMNIA, MEDHISTFAM_HAMALE, MEDHISTFAM_HAFEMALE, MEDHISTFAM_HBP, MEDHISTFAM_STROKE, MEDHISTFAM_BREAST, MEDHISTFAM_COLORECTAL, MEDHISTFAM_PROSTATE, MEDHISTFAM_HICHOL, MEDHISTFAM_DIABETES, MEDHISTFAM_OBESITY, MEDHISTFAM_OSTEO, MEDHISTFAM_DONTKNOW, WTHEIGHTFEET, WTHEIGHTINCHES, WTWEIGHT, WTWAIST, PHYSACTMODERATEDAYSPERWKV2, SMKSTAGE, MENSTRESSSTRESSED, HSCRCHOLESTEROL, HSCRSBP, HSCRDBP, HSCRBLOODGLUCOSE 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 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 PJ.PROJECTCD NOT IN ('fep1','fep1s') AND PC.PUBLICATIONDT >= TO_DATE('4/1/2012','MM/DD/YYYY') AND PC.PUBLICATIONDT <= TO_DATE('3/31/2013','MM/DD/YYYY') AND P.TESTDATA IS NULL AND E.ENROLLMETHODCD ='Web' RATIONALE: The SQL statement with SQL_ID "5qgnpdrsxv4yf" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID 5qgnpdrsxv4yf create table binge_participantids as SELECT DISTINCT p.participantid, e.episodeid, pc.publicationdt FROM DATAWAREHOUSE.MV_participant p, DATAWAREHOUSE.MV_episodes e, DATAWAREHOUSE.MV_piecestatus pc WHERE p.participantid = e.participantid AND pc.participantid = e.participantid AND pc.episodeid = e.episodeid AND p.testdata IS NULL and e.instrumentcd='bing' AND UPPER (pc.piececd) LIKE '%MAS%' AND pc.publicationdt IS NOT NULL and trunc(pc.publicationdt)<=trunc(sysdate-1) minus select distinct participantid,episodeid,publicationdt from bing_analysis1 RATIONALE: The SQL statement with SQL_ID "8zmtgu98x19sc" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID 8zmtgu98x19sc SELECT DISTINCT UMEMBERID||'|'||LASTNAME||'|'||FIRSTNAME||'|'|| ADDRESS1||'|'||ADDRESS2||'|'||CITY||'|'||STATE||'|'||ZIP||'|'||TO_CHA R(BIRTHDATE,'DDMONYYYY')||'|'|| PLOCATION||'|'||WEBID||'|'||XGROUP||'|'||GENDERCD||'|'|| (select distinct MARITALSTATUS FROM DATAWAREHOUSE.MV_EPISODES EP WHERE maritalstatus is not null and EP.PARTICIPANTID=E.PARTICIPANTID AND EP.ENROLLDATE=(SELECT MAX(EV.ENROLLDATE) FROM DATAWAREHOUSE.MV_EPISODES EV WHERE EV.PARTICIPANTID=EP.PARTICIPANTID AND EV.EPISODEID>0))||'|'|| (SELECT MAX(HV.EPISODEID) FROM DATA_ANALYSIS.HBA_ANALYSIS1 HV WHERE HV.PARTICIPANTID=P.PARTICIPANTID)||'|'|| (SELECT DISTINCT TO_CHAR(HBA.PUBLICATIONDT, 'YYYY/MM/DD') FROM DATA_ANALYSIS.HBA_ANALYSIS1 HBA WHERE HBA.PARTICIPANTID=P.PARTICIPANTID AND HBA.PUBLICATIONDT=(SELECT MAX(HV.PUBLICATIONDT) FROM DATA_ANALYSIS.HBA_ANALYSIS1 HV WHERE HV.PARTICIPANTID=HBA.PARTICIPANTID))||'|'|| (SELECT MAX(HV.EPISODEID) FROM DATA_ANALYSIS.BLNA_ANALYSIS HV WHERE HV.PARTICIPANTID=P.PARTICIPANTID) ||'|'|| (SELECT DISTINCT TO_CHAR(HBA.PUBLICATIONDT, 'YYYY/MM/DD') FROM DATA_ANALYSIS.BLNA_ANALYSIS HBA WHERE HBA.PARTICIPANTID=P.PARTICIPANTID AND HBA.PUBLICATIONDT=(SELECT MAX(HV.PUBLICATIONDT) FROM DATA_ANALYSIS.BLNA_ANALYSIS HV WHERE HV.PARTICIPANTID=HBA.PARTICIPANTID))||'|'|| (SELECT MAX(HV.EPISODEID) FROM DATA_ANALYSIS.BREA_ANALYSIS1 HV WHERE HV.PARTICIPANTID=P.PARTICIPANTID) ||'|'|| (SELECT DISTINCT TO_CHAR(HBA.PUBLICATIONDT, 'YYYY/MM/DD') FROM DATA_ANALYSIS.BREA_ANALYSIS1 HBA WHERE HBA.PARTICIPANTID=P.PARTICIPANTID AND HBA.PUBLICATIONDT=(SELECT MAX(HV.PUBLICATIONDT) FROM DATA_ANALYSIS.BREA_ANALYSIS1 HV WHERE HV.PARTICIPANTID=HBA.PARTICIPANTID))||'|'|| (SELECT MAX(HV.EPISODEID) FROM DATA_ANALYSIS.RLXA_ANALYSIS1 HV WHERE HV.PARTICIPANTID=P.PARTICIPANTID) ||'|'|| (SELECT DISTINCT TO_CHAR(HBA.PUBLICATIONDT, 'YYYY/MM/DD') FROM DATA_ANALYSIS.RLXA_ANALYSIS1 HBA WHERE HBA.PARTICIPANTID=P.PARTICIPANTID AND HBA.PUBLICATIONDT=(SELECT MAX(HV.PUBLICATIONDT) FROM DATA_ANALYSIS.RLXA_ANALYSIS1 HV WHERE HV.PARTICIPANTID=HBA.PARTICIPANTID))||'|'|| (SELECT MAX(HV.EPISODEID) FROM DATA_ANALYSIS.NOUA_ANALYSIS1 HV WHERE HV.PARTICIPANTID=P.PARTICIPANTID) ||'|'|| (SELECT DISTINCT TO_CHAR(HBA.PUBLICATIONDT, 'YYYY/MM/DD') FROM DATA_ANALYSIS.NOUA_ANALYSIS1 HBA WHERE HBA.PARTICIPANTID=P.PARTICIPANTID AND HBA.PUBLICATIONDT=(SELECT MAX(HV.PUBLICATIONDT) FROM DATA_ANALYSIS.NOUA_ANALYSIS1 HV WHERE HV.PARTICIPANTID=HBA.PARTICIPANTID))||'|'|| (SELECT MAX(HV.EPISODEID) FROM DATA_ANALYSIS.CABA_ANALYSIS HV WHERE HV.PARTICIPANTID=P.PARTICIPANTID) ||'|'|| (SELECT DISTINCT TO_CHAR(HBA.PUBLICATIONDT, 'YYYY/MM/DD') FROM DATA_ANALYSIS.CABA_ANALYSIS HBA WHERE HBA.PARTICIPANTID=P.PARTICIPANTID AND HBA.PUBLICATIONDT=(SELECT MAX(HV.PUBLICATIONDT) FROM DATA_ANALYSIS.CABA_ANALYSIS HV WHERE HV.PARTICIPANTID=HBA.PARTICIPANTID))||'|'|| (SELECT MAX(HV.EPISODEID) FROM DATA_ANALYSIS.CAHA_ANALYSIS HV WHERE HV.PARTICIPANTID=P.PARTICIPANTID) ||'|'|| (SELECT DISTINCT TO_CHAR(HBA.PUBLICATIONDT, 'YYYY/MM/DD') FROM DATA_ANALYSIS.CAHA_ANALYSIS HBA WHERE HBA.PARTICIPANTID=P.PARTICIPANTID AND HBA.PUBLICATIONDT=(SELECT MAX(HV.PUBLICATIONDT) FROM DATA_ANALYSIS.CAHA_ANALYSIS HV WHERE HV.PARTICIPANTID=HBA.PARTICIPANTID))||'|'|| (SELECT MAX(HV.EPISODEID) FROM DATA_ANALYSIS.CADB_ANALYSIS1 HV WHERE HV.PARTICIPANTID=P.PARTICIPANTID) ||'|'|| (SELECT DISTINCT TO_CHAR(HBA.PUBLICATIONDT, 'YYYY/MM/DD') FROM DATA_ANALYSIS.CADB_ANALYSIS1 HBA WHERE HBA.PARTICIPANTID=P.PARTICIPANTID AND HBA.PUBLICATIONDT=(SELECT MAX(HV.PUBLICATIONDT) FROM DATA_ANALYSIS.CADB_ANALYSIS1 HV WHERE HV.PARTICIPANTID=HBA.PARTICIPANTID))||'|'|| E.ENROLLMETHODCD||'|'|| (SELECT MAX(HV.EPISODEID) FROM DATA_ANALYSIS.CACH_ANALYSIS1 HV WHERE HV.PARTICIPANTID=P.PARTICIPANTID) ||'|'|| (SELECT DISTINCT TO_CHAR(HBA.PUBLICATIONDT, 'YYYY/MM/DD') FROM DATA_ANALYSIS.CACH_ANALYSIS1 HBA WHERE HBA.PARTICIPANTID=P.PARTICIPANTID AND HBA.PUBLICATIONDT=(SELECT MAX(HV.PUBLICATIONDT) FROM DATA_ANALYSIS.CACH_ANALYSIS1 HV WHERE HV.PARTICIPANTID=HBA.PARTICIPANTID))||'|'|| (SELECT MAX(HV.EPISODEID) FROM DATA_ANALYSIS.CAHT_ANALYSIS1 HV WHERE HV.PARTICIPANTID=P.PARTICIPANTID) ||'|'|| (SELECT DISTINCT TO_CHAR(HBA.PUBLICATIONDT, 'YYYY/MM/DD') FROM DATA_ANALYSIS.CAHT_ANALYSIS1 HBA WHERE HBA.PARTICIPANTID=P.PARTICIPANTID AND HBA.PUBLICATIONDT=(SELECT MAX(HV.PUBLICATIONDT) FROM DATA_ANALYSIS.CAHT_ANALYSIS1 HV WHERE HV.PARTICIPANTID=HBA.PARTICIPANTID))||'|'|| (SELECT MAX(HV.EPISODEID) FROM DATA_ANALYSIS.CAPA_ANALYSIS1 HV WHERE HV.PARTICIPANTID=P.PARTICIPANTID) ||'|'|| (SELECT DISTINCT TO_CHAR(HBA.PUBLICATIONDT, 'YYYY/MM/DD') FROM DATA_ANALYSIS.CAPA_ANALYSIS1 HBA WHERE HBA.PARTICIPANTID=P.PARTICIPANTID AND HBA.PUBLICATIONDT=(SELECT MAX(HV.PUBLICATIONDT) FROM DATA_ANALYSIS.CAPA_ANALYSIS1 HV WHERE HV.PARTICIPANTID=HBA.PARTICIPANTID))||'|'|| (SELECT MAX(HV.EPISODEID) FROM DATA_ANALYSIS.MOVE_ANALYSIS1 HV WHERE HV.PARTICIPANTID=P.PARTICIPANTID) ||'|'|| (SELECT DISTINCT TO_CHAR(HBA.PUBLICATIONDT, 'YYYY/MM/DD') FROM DATA_ANALYSIS.MOVE_ANALYSIS1 HBA WHERE HBA.PARTICIPANTID=P.PARTICIPANTID AND HBA.PUBLICATIONDT=(SELECT MAX(HV.PUBLICATIONDT) FROM DATA_ANALYSIS.MOVE_ANALYSIS1 HV WHERE HV.PARTICIPANTID=HBA.PARTICIPANTID)) FROM datawarehouse.mv_PARTICIPANT P, datawarehouse.mv_EPISODES E, DATAWAREHOUSE.MV_PIECESTATUS PC WHERE P.PPROJECTCD='hap2' and p.testdata is null AND P.PARTICIPANTID=E.PARTICIPANTID AND E.INSTRUMENTCD in ('hba','blna','brea','rlxa','noua','caba','caha','cadb','chba','cach' ,'caht','capa','move') AND PC.PARTICIPANTID=E.PARTICIPANTID AND PC.EPISODEID=E.EPISODEID AND UPPER(PC.PIECECD) LIKE '%MAS%' AND TRUNC(PC.PUBLICATIONDT) in (SELECT EXTRACTDATE FROM DATA_ANALYSIS.EXTRACTLOADDATE) SYMPTOMS THAT LED TO THE FINDING: SYMPTOM: Wait class "User I/O" was consuming significant database time. (68% impact [155934 seconds]) FINDING 3: 25% impact (56288 seconds) ------------------------------------- The PGA was inadequately sized, causing additional I/O to temporary tablespaces to consume significant database time. RECOMMENDATION 1: DB Configuration, 2% benefit (4503 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. (68% impact [155934 seconds]) FINDING 4: 21% impact (49068 seconds) ------------------------------------- The throughput of the I/O subsystem was significantly lower than expected. RECOMMENDATION 1: Host Configuration, 21% benefit (49068 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 2.6 M per second for reads and 651 K per second for writes. The average response time for single block reads was 16 milliseconds. RECOMMENDATION 2: Host Configuration, 4.8% benefit (10923 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 32 milliseconds. RECOMMENDATION 3: Host Configuration, 3.6% benefit (8256 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 29 milliseconds. RECOMMENDATION 4: Host Configuration, 2.3% benefit (5175 seconds) ACTION: The performance of file +ORAFILES/dwh1/data_analysislarge6.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_analysislarge6.dbf" RATIONALE: The average response time for single block reads for this file was 17 milliseconds. RECOMMENDATION 5: Host Configuration, 2.1% benefit (4806 seconds) ACTION: The performance of file +ORAFILES/dwh1/data_analysislarge4.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_analysislarge4.dbf" RATIONALE: The average response time for single block reads for this file was 17 milliseconds. SYMPTOMS THAT LED TO THE FINDING: SYMPTOM: Wait class "User I/O" was consuming significant database time. (68% impact [155934 seconds]) FINDING 5: 15% impact (35201 seconds) ------------------------------------- SQL statements consuming significant database time were found. RECOMMENDATION 1: SQL Tuning, 8% benefit (18413 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 54 times and had an average elapsed time of 85 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 65% 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 31% 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 1% of the database time spent in processing the SQL statement with SQL_ID "bzc9n0r0ywcq1". RECOMMENDATION 2: SQL Tuning, 6.6% benefit (15078 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 5 times and had an average elapsed time of 3015 seconds. RECOMMENDATION 3: SQL Tuning, 4.8% benefit (10928 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "8xjduhnwackh3". RELEVANT OBJECT: SQL statement with SQL_ID 8xjduhnwackh3 and PLAN_HASH 2617412812 select PARTICIPANTID,EPISODEID,PUBLICATIONDT,PIECECD,'BL' FLAG FROM data_analysis.DS_DA_ACTIVITY B WHERE DISTCD='AETN' AND projectcd='aess' and PUBLICATIONDT>=TO_DATE('01/01/2010','MM/DD/YYYY') AND CUSTOMQ2 is not null AND POPTIN IN ('Forced','Yes') union select DISTINCT PARTICIPANTID,EPISODEID,trunc(EVAL1DATE) EVAL1DATE,PIECECD,'E1' FLAG FROM DS_DA_ACTIVITY WHERE DISTCD='AETN' and PROJECTCD='aess' AND PUBLICATIONDT>=TO_DATE('01/01/2010','MM/DD/YYYY') AND EVAL1DATE IS NOT NULL AND POPTIN IN ('Forced','Yes') UNION select DISTINCT PARTICIPANTID,EPISODEID,trunc(EVAL2DATE) EVAL2DATE,PIECECD,'E2' FLAG FROM DS_DA_ACTIVITY WHERE DISTCD='AETN' and PROJECTCD='aess' AND PUBLICATIONDT>=TO_DATE('01/01/2010','MM/DD/YYYY') AND EVAL2DATE IS NOT NULL AND POPTIN IN ('Forced','Yes') UNION select DISTINCT PARTICIPANTID,EPISODEID,trunc(EVAL3DATE) EVAL3DATE,PIECECD,'E3' FLAG FROM DS_DA_ACTIVITY WHERE DISTCD='AETN' and PROJECTCD='aess' AND PUBLICATIONDT>=TO_DATE('01/01/2010','MM/DD/YYYY') AND EVAL3DATE IS NOT NULL AND POPTIN IN ('Forced','Yes') ACTION: Investigate the SQL statement with SQL_ID "8xjduhnwackh3" for possible performance improvements. RELEVANT OBJECT: SQL statement with SQL_ID 8xjduhnwackh3 and PLAN_HASH 2617412812 select PARTICIPANTID,EPISODEID,PUBLICATIONDT,PIECECD,'BL' FLAG FROM data_analysis.DS_DA_ACTIVITY B WHERE DISTCD='AETN' AND projectcd='aess' and PUBLICATIONDT>=TO_DATE('01/01/2010','MM/DD/YYYY') AND CUSTOMQ2 is not null AND POPTIN IN ('Forced','Yes') union select DISTINCT PARTICIPANTID,EPISODEID,trunc(EVAL1DATE) EVAL1DATE,PIECECD,'E1' FLAG FROM DS_DA_ACTIVITY WHERE DISTCD='AETN' and PROJECTCD='aess' AND PUBLICATIONDT>=TO_DATE('01/01/2010','MM/DD/YYYY') AND EVAL1DATE IS NOT NULL AND POPTIN IN ('Forced','Yes') UNION select DISTINCT PARTICIPANTID,EPISODEID,trunc(EVAL2DATE) EVAL2DATE,PIECECD,'E2' FLAG FROM DS_DA_ACTIVITY WHERE DISTCD='AETN' and PROJECTCD='aess' AND PUBLICATIONDT>=TO_DATE('01/01/2010','MM/DD/YYYY') AND EVAL2DATE IS NOT NULL AND POPTIN IN ('Forced','Yes') UNION select DISTINCT PARTICIPANTID,EPISODEID,trunc(EVAL3DATE) EVAL3DATE,PIECECD,'E3' FLAG FROM DS_DA_ACTIVITY WHERE DISTCD='AETN' and PROJECTCD='aess' AND PUBLICATIONDT>=TO_DATE('01/01/2010','MM/DD/YYYY') AND EVAL3DATE IS NOT NULL AND POPTIN IN ('Forced','Yes') RATIONALE: SQL statement with SQL_ID "8xjduhnwackh3" was executed 4 times and had an average elapsed time of 1431 seconds. RATIONALE: Waiting for event "db file sequential read" in wait class "User I/O" accounted for 48% of the database time spent in processing the SQL statement with SQL_ID "8xjduhnwackh3". RATIONALE: Waiting for event "db file parallel read" in wait class "User I/O" accounted for 34% of the database time spent in processing the SQL statement with SQL_ID "8xjduhnwackh3". RATIONALE: Waiting for event "read by other session" in wait class "User I/O" accounted for 12% of the database time spent in processing the SQL statement with SQL_ID "8xjduhnwackh3". RECOMMENDATION 4: SQL Tuning, 2.2% benefit (5125 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "4x8jgn9uzsndd". RELEVANT OBJECT: SQL statement with SQL_ID 4x8jgn9uzsndd and PLAN_HASH 4092877126 (SELECT DISTINCT 'Succeed' as Program,PARTICIPANTID,EPISODEID FROM DS_DA_ACTIVITY where instrumentcd IN ('hba','chba') AND PUBLICATIONDT IS NOT NULL and TRUNC(PUBLICATIONDT)<=TRUNC(SYSDATE-1) MINUS SELECT DISTINCT 'Succeed',PARTICIPANTID,EPISODEID FROM HBA_ANALYSIS1 where instrumentcd IN ('hba','chba')) union (SELECT DISTINCT 'Balance' as Program,PARTICIPANTID,EPISODEID FROM DS_DA_ACTIVITY where instrumentcd ='blna' AND PUBLICATIONDT IS NOT NULL and TRUNC(PUBLICATIONDT)<=TRUNC(SYSDATE-1) MINUS SELECT DISTINCT 'Balance',PARTICIPANTID,EPISODEID FROM BLNA_ANALYSIS where instrumentcd='blna') UNION (SELECT DISTINCT 'Breathe' as Program,PARTICIPANTID,EPISODEID FROM DS_DA_ACTIVITY where instrumentcd='brea' AND PUBLICATIONDT IS NOT NULL and TRUNC(PUBLICATIONDT)<=TRUNC(SYSDATE-1) MINUS SELECT DISTINCT 'Breathe',PARTICIPANTID,EPISODEID FROM BREA_ANALYSIS1 where instrumentcd ='brea') UNION (SELECT DISTINCT 'Relax' as Program,PARTICIPANTID,EPISODEID FROM DS_DA_ACTIVITY where instrumentcd ='rlxa' AND PUBLICATIONDT IS NOT NULL and TRUNC(PUBLICATIONDT)<=TRUNC(SYSDATE-1) MINUS SELECT DISTINCT 'Relax',PARTICIPANTID,EPISODEID FROM RLXA_ANALYSIS1 where instrumentcd ='rlxa') UNION (SELECT DISTINCT 'Care for Diabetes' as Program,PARTICIPANTID,EPISODEID FROM DS_DA_ACTIVITY where instrumentcd ='cadb' AND PUBLICATIONDT IS NOT NULL and TRUNC(PUBLICATIONDT)<=TRUNC(SYSDATE-1) MINUS SELECT DISTINCT 'Care for Diabetes',PARTICIPANTID,EPISODEID FROM CADB_ANALYSIS1 where instrumentcd='cadb') union (SELECT DISTINCT 'Nourish' as Program,PARTICIPANTID,EPISODEID FROM DS_DA_ACTIVITY where instrumentcd ='noua' AND PUBLICATIONDT IS NOT NULL and TRUNC(PUBLICATIONDT)<=TRUNC(SYSDATE-1) MINUS SELECT DISTINCT 'Nourish',PARTICIPANTID,EPISODEID FROM NOUA_ANALYSIS1 where instrumentcd='noua') UNION (SELECT DISTINCT 'Care for Back' as Program,PARTICIPANTID,EPISODEID FROM DS_DA_ACTIVITY where instrumentcd ='caba' AND PUBLICATIONDT IS NOT NULL and TRUNC(PUBLICATIONDT)<=TRUNC(SYSDATE-1) MINUS SELECT DISTINCT 'Care for Back',PARTICIPANTID,EPISODEID FROM CAba_ANALYSIS where instrumentcd='caba') union (SELECT DISTINCT 'Care for Health' as Program,PARTICIPANTID,EPISODEID FROM DS_DA_ACTIVITY where instrumentcd ='caha' AND PUBLICATIONDT IS NOT NULL and TRUNC(PUBLICATIONDT)<=TRUNC(SYSDATE-1) MINUS SELECT DISTINCT 'Care for Health',PARTICIPANTID,EPISODEID FROM CAHA_ANALYSIS where instrumentcd='caha') UNION (SELECT DISTINCT 'Binge' as Program,PARTICIPANTID,EPISODEID FROM DS_DA_ACTIVITY where instrumentcd ='bing' AND PUBLICATIONDT IS NOT NULL and TRUNC(PUBLICATIONDT)<=TRUNC(SYSDATE-1) MINUS SELECT DISTINCT 'Binge',PARTICIPANTID,EPISODEID FROM BING_ANALYSIS1 where instrumentcd='bing') union (SELECT DISTINCT 'Care for Pain' as Program,PARTICIPANTID,EPISODEID FROM DS_DA_ACTIVITY where instrumentcd ='capa' AND PUBLICATIONDT IS NOT NULL and TRUNC(PUBLICATIONDT)<=TRUNC(SYSDATE-1) MINUS SELECT DISTINCT 'Care for Pain',PARTICIPANTID,EPISODEID FROM CAPA_ANALYSIS1 where instrumentcd='capa') RATIONALE: SQL statement with SQL_ID "4x8jgn9uzsndd" was executed 1 times and had an average elapsed time of 4970 seconds. RECOMMENDATION 5: SQL Tuning, 2.1% benefit (4880 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "cx28zdj0snrju". RELEVANT OBJECT: SQL statement with SQL_ID cx28zdj0snrju and PLAN_HASH 1908425287 INSERT INTO ESR_STAGE_TAB (BEHAVIORCD,BEHAVIOR_DX,VALUE,VALUE_DX,PCT) SELECT A.BEHAVIORCD,A.BEHAVIOR_DX,A.VALUE,A.VALUE_DX, ROUND(SUM(NUM)*100/(SELECT SUM(NUMD) FROM ESR_BEHAVIORS_HMI AB WHERE AB.DISTRIBUTOR=:B3 AND AB.LOCATION IN (SELECT DISTINCT PLOCATION FROM KPPLOCATION) AND AB.TRANS_DATE BETWEEN TO_DATE(:B2 ,'YYYY/MM/DD') AND TO_DATE(:B1 ,'YYYY/MM/DD') AND PARAMETERCD=1 AND AB.BEHAVIORCD=A.BEHAVIORCD),2) FROM ESR_STAGE_HMI A WHERE A.LOCATION IN (SELECT DISTINCT PLOCATION FROM KPPLOCATION) AND A.DISTRIBUTOR=:B3 AND A.TRANS_DATE BETWEEN TO_DATE(:B2 ,'YYYY/MM/DD') AND TO_DATE(:B1 ,'YYYY/MM/DD') GROUP BY A.BEHAVIORCD,A.BEHAVIOR_DX,A.VALUE,A.VALUE_DX RATIONALE: SQL statement with SQL_ID "cx28zdj0snrju" was executed 3 times and had an average elapsed time of 1609 seconds. FINDING 6: 12% impact (27544 seconds) ------------------------------------- The SGA was inadequately sized, causing additional I/O or hard parses. RECOMMENDATION 1: DB Configuration, 5.8% benefit (13252 seconds) ACTION: Increase the size of the SGA by setting the parameter "sga_target" to 30720 M. ADDITIONAL INFORMATION: The value of parameter "sga_target" was "24576 M" during the analysis period. SYMPTOMS THAT LED TO THE FINDING: SYMPTOM: Wait class "User I/O" was consuming significant database time. (68% impact [155934 seconds]) FINDING 7: 8.9% impact (20492 seconds) -------------------------------------- Buffer cache writes due to setting of the obsolete parameters "fast_start_io_target", "log_checkpoint_interval" and "log_checkpoint_timeout" were consuming significant database time. RECOMMENDATION 1: DB Configuration, 8.9% benefit (20492 seconds) ACTION: Oracle's recommended solution is to control MTTR setting using the "fast_start_mttr_target" parameter instead of the "fast_start_io_target", "log_checkpoint_interval" and "log_checkpoint_timeout" parameters. SYMPTOMS THAT LED TO THE FINDING: SYMPTOM: The throughput of the I/O subsystem was significantly lower than expected. (21% impact [49068 seconds]) SYMPTOM: Wait class "User I/O" was consuming significant database time. (68% impact [155934 seconds]) FINDING 8: 7.2% impact (16562 seconds) -------------------------------------- Wait event "IPC send completion sync" in wait class "Other" was consuming significant database time. RECOMMENDATION 1: Application Analysis, 7.2% benefit (16562 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 "14kxshn1vmjw0" was found waiting for "IPC send completion sync" wait event. RELEVANT OBJECT: SQL statement with SQL_ID 14kxshn1vmjw0 select "CAHA_ANALYSIS_GRAPHS"."ITEMCD" "C0", 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") "C1", 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")/nullif("Diff_Sleeping_Graph_Total"." Total_no_of_Participants", 0) "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(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 "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 not "CAHA_ANALYSIS_GRAPHS"."ITEMCD" is null RATIONALE: The SQL statement with SQL_ID "0bxwpdc2bvfta" was found waiting for "IPC send completion sync" wait event. RELEVANT OBJECT: SQL statement with SQL_ID 0bxwpdc2bvfta select "Union31"."DiscussMeds" "level0key", "Union31"."Sort_Order" "Sort_Order", "Union31"."Level4" "Level0", "Union31"."Header" "levelkey", sum("Union31"."Percent") "Percent" from (select distinct "Union24"."CYHPHREL_DISCUSSMEDS" "CYHPHREL_DISCUSSMEDS", "Union24"."Total_no_of_Participants" "Total_no_of_Participants", "Union24"."Percent" "Percent", "Union24"."Level4" "Level4", "Union24"."Header" "Header", "Union24"."DiscussMeds" "DiscussMeds", case when '3 Column'='2 Column' and "Union24"."Level4"=2 then 'No' else 'Yes' end "C_2_Column_Filter", "Union24"."Sort_Order" "Sort_Order" from (select "T0"."C0" "CYHPHREL_DISCUSSMEDS", 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" "DiscussMeds", "T0"."C2" "Sort_Order" from (select "CAHA_ANALYSIS"."CYHPHREL_DISCUSSMEDS" "C0", count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPISODE ID") "C1", 1 "C2", 'PLK Test' "C3", case when "CAHA_ANALYSIS"."CYHPHREL_DISCUSSMEDS"='true' then 'Discuss Medication with Pharmacist' 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"."CYHPHREL_DISCUSSMEDS", case when "CAHA_ANALYSIS"."CYHPHREL_DISCUSSMEDS"='true' then 'Discuss Medication with Pharmacist' else NULL end ) "T0" union select "T0"."C0" "CYHPHREL_DISCUSSMEDS", 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" "DiscussMeds", "T0"."C5" "Sort_Order" from (select "CAHA_ANALYSIS"."CYHPHREL_DISCUSSMEDS" "C0", count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPISODEID") "C1", 2 "C2", "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR" "C3", case when "CAHA_ANALYSIS"."CYHPHREL_DISCUSSMEDS"='true' then 'Discuss Medication with Pharmacist' else NULL end "C4", 1 "C5" 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"."CYHPHREL_DISCUSSMEDS", "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR", case when "CAHA_ANALYSIS"."CYHPHREL_DISCUSSMEDS"='true' then 'Discuss Medication with Pharmacist' else NULL end ) "T0" union select "T0"."C0" "CYHPHREL_DISCUSSMEDS", 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" "DiscussMeds", "T0"."C5" "Sort_Order" from (select "CAHA_ANALYSIS"."CYHPHREL_DISCUSSMEDS" "C0", count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPISODEID") "C1", 3 "C2", 'HealthMedia ' "C3", case when "CAHA_ANALYSIS"."CYHPHREL_DISCUSSMEDS"='true' then 'Discuss Medication with Pharmacist' else NULL end "C4", 1 "C5" 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"."CYHPHREL_DISCUSSMEDS", case when "CAHA_ANALYSIS"."CYHPHREL_DISCUSSMEDS"='true' then 'Discuss Medication with Pharmacist' else NULL end ) "T0") "Union24" where case when ('3 Column'='2 Column' and "Union24"."Level4"=2) then 'No' else 'Yes' end ='Yes' and not "Union24"."DiscussMeds" is null union all select distinct "Union25"."CYHPHREL_PHARMUDATE" "CYHPHREL_DISCUSSMEDS", "Union25"."Total_no_of_Participants" "Total_no_of_Participants", "Union25"."Percent" "Percent", "Union25"."Level4" "Level4", "Union25"."Header" "Header", "Union25"."PharmUDate" "DiscussMeds", case when '3 Column'='2 Column' and "Union25"."Level4"=2 then 'No' else 'Yes' end "C_2_Column_Filter", "Union25"."Sort_Order" "Sort_Order" from (select "T0"."C0" "CYHPHREL_PHARMUDATE", 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" "PharmUDate", "T0"."C5" "Sort_Order" from (select "CAHA_ANALYSIS"."CYHPHREL_PHARMUDATE" "C0", count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPISODEID") "C1", 1 "C2", 'PLK Test' "C3", case when "CAHA_ANALYSIS"."CYHPHREL_PHARMUDATE"='true' then 'Keep Pharmacist Up to Date' else NULL end "C4", 2 "C5" 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"."CYHPHREL_PHARMUDATE", case when "CAHA_ANALYSIS"."CYHPHREL_PHARMUDATE"='true' then 'Keep Pharmacist Up to Date' else NULL end ) "T0" union select "T0"."C0" "CYHPHREL_PHARMUDATE", 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" "PharmUDate", "T0"."C2" "Sort_Order" from (select "CAHA_ANALYSIS"."CYHPHREL_PHARMUDATE" "C0", count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPISODEID") "C1", 2 "C2", "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR" "C3", case when "CAHA_ANALYSIS"."CYHPHREL_PHARMUDATE"='true' then 'Keep Pharmacist Up to Date' 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"."CYHPHREL_PHARMUDATE", "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR", case when "CAHA_ANALYSIS"."CYHPHREL_PHARMUDATE"='true' then 'Keep Pharmacist Up to Date' else NULL end ) "T0" union select "T0"."C0" "CYHPHREL_PHARMUDATE", 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" "PharmUDate", "T0"."C5" "Sort_Order" from (select "CAHA_ANALYSIS"."CYHPHREL_PHARMUDATE" "C0", count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPISODEID") "C1", 3 "C2", 'HealthMedia ' "C3", case when "CAHA_ANALYSIS"."CYHPHREL_PHARMUDATE"='true' then 'Keep Pharmacist Up to Date' else NULL end "C4", 2 "C5" 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"."CYHPHREL_PHARMUDATE", case when "CAHA_ANALYSIS"."CYHPHREL_PHARMUDATE"='true' then 'Keep Pharmacist Up to Date' else NULL end ) "T0") "Union25" where case when ('3 Column'='2 Column' and "Union25"."Level4"=2) then 'No' else 'Yes' end ='Yes' and not "Union25"."PharmUDate" is null union all select distinct "Union26"."CYHPHREL_PHARMHERBALS" "CYHPHREL_DISCUSSMEDS", "Union26"."Total_no_of_Participants" "Total_no_of_Participants", "Union26"."Percent" "Percent", "Union26"."Level4" "Level4", "Union26"."Header" "Header", "Union26"."PharmHerbals" "DiscussMeds", case when '3 Column'='2 Column' and "Union26"."Level4"=2 then 'No' else 'Yes' end "C_2_Column_Filter", "Union26"."Sort_Order" "Sort_Order" from (select "T0"."C0" "CYHPHREL_PHARMHERBALS", 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" "PharmHerbals", "T0"."C5" "Sort_Order" from (select "CAHA_ANALYSIS"."CYHPHREL_PHARMHERBALS" "C0", count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPISODE ID") "C1", 1 "C2", 'PLK Test' "C3", case when "CAHA_ANALYSIS"."CYHPHREL_PHARMHERBALS"='true' then 'Tell Pharmacist About Suppl & Herbal' else NULL end "C4", 3 "C5" 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"."CYHPHREL_PHARMHERBALS", case when "CAHA_ANALYSIS"."CYHPHREL_PHARMHERBALS"='true' then 'Tell Pharmacist About Suppl & Herbal' else NULL end ) "T0" union select "T0"."C0" "CYHPHREL_PHARMHERBALS", 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" "PharmHerbals", "T0"."C5" "Sort_Order" from (select "CAHA_ANALYSIS"."CYHPHREL_PHARMHERBALS" "C0", count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPISODEID") "C1", 2 "C2", "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR" "C3", case when "CAHA_ANALYSIS"."CYHPHREL_PHARMHERBALS"='true' then 'Tell Pharmacist About Suppl & Herbal' else NULL end "C4", 3 "C5" 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"."CYHPHREL_PHARMHERBALS", "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR", case when "CAHA_ANALYSIS"."CYHPHREL_PHARMHERBALS"='true' then 'Tell Pharmacist About Suppl & Herbal' else NULL end ) "T0" union select "T0"."C0" "CYHPHREL_PHARMHERBALS", 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" "PharmHerbals", "T0"."C2" "Sort_Order" from (select "CAHA_ANALYSIS"."CYHPHREL_PHARMHERBALS" "C0", count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPISODEID") "C1", 3 "C2", 'HealthMedia ' "C3", case when "CAHA_ANALYSIS"."CYHPHREL_PHARMHERBALS"='true' then 'Tell Pharmacist About Suppl & Herbal' 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"."CYHPHREL_PHARMHERBALS", case when "CAHA_ANALYSIS"."CYHPHREL_PHARMHERBALS"='true' then 'Tell Pharmacist About Suppl & Herbal' else NULL end ) "T0") "Union26" where case when ('3 Column'='2 Column' and "Union26"."Level4"=2) then 'No' else 'Yes' end ='Yes' and not "Union26"."PharmHerbals" is null union all select distinct "Union27"."CYHPHREL_PHARMUCMEDS" "CYHPHREL_DISCUSSMEDS", "Union27"."Total_no_of_Participants" "Total_no_of_Participants", "Union27"."Percent" "Percent", "Union27"."Level4" "Level4", "Union27"."Header" "Header", "Union27"."PharmUCMeds" "DiscussMeds", case when '3 Column'='2 Column' and "Union27"."Level4"=2 then 'No' else 'Yes' end "C_2_Column_Filter", "Union27"."Sort_Order" "Sort_Order" from (select "T0"."C0" "CYHPHREL_PHARMUCMEDS", 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" "PharmUCMeds", "T0"."C5" "Sort_Order" from (select "CAHA_ANALYSIS"."CYHPHREL_PHARMUCMEDS" "C0", count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPISODEID") "C1", 1 "C2", 'PLK Test' "C3", case when "CAHA_ANALYSIS"."CYHPHREL_PHARMUCMEDS"='true' then 'Tell Pharmacist About OTC Medications' else NULL end "C4", 4 "C5" 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"."CYHPHREL_PHARMUCMEDS", case when "CAHA_ANALYSIS"."CYHPHREL_PHARMUCMEDS"='true' then 'Tell Pharmacist About OTC Medications' else NULL end ) "T0" union select "T0"."C0" "CYHPHREL_PHARMUCMEDS", 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" "PharmUCMeds", "T0"."C5" "Sort_Order" from (select "CAHA_ANALYSIS"."CYHPHREL_PHARMUCMEDS" "C0", count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPISODEID") "C1", 2 "C2", "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR" "C3", case when "CAHA_ANALYSIS"."CYHPHREL_PHARMUCMEDS"='true' then 'Tell Pharmacist About OTC Medications' else NULL end "C4", 4 "C5" 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"."CYHPHREL_PHARMUCMEDS", "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR", case when "CAHA_ANALYSIS"."CYHPHREL_PHARMUCMEDS"='true' then 'Tell Pharmacist About OTC Medications' else NULL end ) "T0" union select "T0"."C0" "CYHPHREL_PHARMUCMEDS", 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" "PharmUCMeds", "T0"."C5" "Sort_Order" from (select "CAHA_ANALYSIS"."CYHPHREL_PHARMUCMEDS" "C0", count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPISODEID") "C1", 3 "C2", 'HealthMedia ' "C3", case when "CAHA_ANALYSIS"."CYHPHREL_PHARMUCMEDS"='true' then 'Tell Pharmacist About OTC Medications' else NULL end "C4", 4 "C5" 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"."CYHPHREL_PHARMUCMEDS", case when "CAHA_ANALYSIS"."CYHPHREL_PHARMUCMEDS"='true' then 'Tell Pharmacist About OTC Medications' else NULL end ) "T0") "Union27" where case when ('3 Column'='2 Column' and "Union27"."Level4"=2) then 'No' else 'Yes' end ='Yes' and not "Union27"."PharmUCMeds" is null union all select distinct "Union28"."CYHPHREL_PHARMALLERGIES" "CYHPHREL_DISCUSSMEDS", "Union28"."Total_no_of_Participants" "Total_no_of_Participants", "Union28"."Percent" "Percent", "Union28"."Level4" "Level4", "Union28"."Header" "Header", "Union28"."PharmAllergies" "DiscussMeds", case when '3 Column'='2 Column' and "Union28"."Level4"=2 then 'No' else 'Yes' end "C_2_Column_Filter", "Union28"."Sort_Order" "Sort_Order" from (select "T0"."C0" "CYHPHREL_PHARMALLERGIES", 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" "PharmAllergies", "T0"."C5" "Sort_Order" from (select "CAHA_ANALYSIS"."CYHPHREL_PHARMALLERGIES" "C0", count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPISODEID") "C1", 1 "C2", 'PLK Test' "C3", case when "CAHA_ANALYSIS"."CYHPHREL_PHARMALLERGIES"='true' then 'Tell Pharmacist About Allergies' else NULL end "C4", 5 "C5" 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"."CYHPHREL_PHARMALLERGIES", case when "CAHA_ANALYSIS"."CYHPHREL_PHARMALLERGIES"='true' then 'Tell Pharmacist About Allergies' else NULL end ) "T0" union select "T0"."C0" "CYHPHREL_PHARMALLERGIES", 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" "PharmAllergies", "T0"."C5" "Sort_Order" from (select "CAHA_ANALYSIS"."CYHPHREL_PHARMALLERGIES" "C0", count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPISODEID") "C1", 2 "C2", "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR" "C3", case when "CAHA_ANALYSIS"."CYHPHREL_PHARMALLERGIES"='true' then 'Tell Pharmacist About Allergies' else NULL end "C4", 5 "C5" 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"."CYHPHREL_PHARMALLERGIES", "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR", case when "CAHA_ANALYSIS"."CYHPHREL_PHARMALLERGIES"='true' then 'Tell Pharmacist About Allergies' else NULL end ) "T0" union select "T0"."C0" "CYHPHREL_PHARMALLERGIES", 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" "PharmAllergies", "T0"."C5" "Sort_Order" from (select "CAHA_ANALYSIS"."CYHPHREL_PHARMALLERGIES" "C0", count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPISODEID") "C1", 3 "C2", 'HealthMedia ' "C3", case when "CAHA_ANALYSIS"."CYHPHREL_PHARMALLERGIES"='true' then 'Tell Pharmacist About Allergies' else NULL end "C4", 5 "C5" 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"."CYHPHREL_PHARMALLERGIES", case when "CAHA_ANALYSIS"."CYHPHREL_PHARMALLERGIES"='true' then 'Tell Pharmacist About Allergies' else NULL end ) "T0") "Union28" where case when ('3 Column'='2 Column' and "Union28"."Level4"=2) then 'No' else 'Yes' end ='Yes' and not "Union28"."PharmAllergies" is null union all select distinct "Union29"."CYHPHREL_PHARMANOTHER" "CYHPHREL_DISCUSSMEDS", "Union29"."Total_no_of_Participants" "Total_no_of_Participants", "Union29"."Percent" "Percent", "Union29"."Level4" "Level4", "Union29"."Header" "Header", "Union29"."PharmAnother" "DiscussMeds", case when '3 Column'='2 Column' and "Union29"."Level4"=2 then 'No' else 'Yes' end "C_2_Column_Filter", "Union29"."Sort_Order" "Sort_Order" from (select "T0"."C0" "CYHPHREL_PHARMANOTHER", 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" "PharmAnother", "T0"."C5" "Sort_Order" from (select "CAHA_ANALYSIS"."CYHPHREL_PHARMANOTHER" "C0", count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPISODEID") "C1", 1 "C2", 'PLK Test' "C3", case when "CAHA_ANALYSIS"."CYHPHREL_PHARMANOTHER"='true' then 'Tell Pharmacist About Another Pharmacy' else NULL end "C4", 6 "C5" 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"."CYHPHREL_PHARMANOTHER", case when "CAHA_ANALYSIS"."CYHPHREL_PHARMANOTHER"='true' then 'Tell Pharmacist About Another Pharmacy' else NULL end ) "T0" union select "T0"."C0" "CYHPHREL_PHARMANOTHER", 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" "PharmAnother", "T0"."C5" "Sort_Order" from (select "CAHA_ANALYSIS"."CYHPHREL_PHARMANOTHER" "C0", count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPISODEID") "C1", 2 "C2", "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR" "C3", case when "CAHA_ANALYSIS"."CYHPHREL_PHARMANOTHER"='true' then 'Tell Pharmacist About Another Pharmacy' else NULL end "C4", 6 "C5" 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"."CYHPHREL_PHARMANOTHER", "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR", case when "CAHA_ANALYSIS"."CYHPHREL_PHARMANOTHER"='true' then 'Tell Pharmacist About Another Pharmacy' else NULL end ) "T0" union select "T0"."C0" "CYHPHREL_PHARMANOTHER", 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" "PharmAnother", "T0"."C5" "Sort_Order" from (select "CAHA_ANALYSIS"."CYHPHREL_PHARMANOTHER" "C0", count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPISODEID") "C1", 3 "C2", 'HealthMedia ' "C3", case when "CAHA_ANALYSIS"."CYHPHREL_PHARMANOTHER"='true' then 'Tell Pharmacist About Another Pharmacy' else NULL end "C4", 6 "C5" 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"."CYHPHREL_PHARMANOTHER", case when "CAHA_ANALYSIS"."CYHPHREL_PHARMANOTHER"='true' then 'Tell Pharmacist About Another Pharmacy' else NULL end ) "T0") "Union29" where case when ('3 Column'='2 Column' and "Union29"."Level4"=2) then 'No' else 'Yes' end ='Yes' and not "Union29"."PharmAnother" is null union all select distinct "Union30"."CYHPHREL_UDATEDOSES" "CYHPHREL_DISCUSSMEDS", "Union30"."Total_no_of_Participants" "Total_no_of_Participants", "Union30"."Percent" "Percent", "Union30"."Level4" "Level4", "Union30"."Header" "Header", "Union30"."UDateDoses" "DiscussMeds", case when '3 Column'='2 Column' and "Union30"."Level4"=2 then 'No' else 'Yes' end "C_2_Column_Filter", "Union30"."Sort_Order" "Sort_Order" from (select "T0"."C0" "CYHPHREL_UDATEDOSES", 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" "UDateDoses", "T0"."C5" "Sort_Order" from (select "CAHA_ANALYSIS"."CYHPHREL_UDATEDOSES" "C0", count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPISODEID") "C1", 1 "C2", 'PLK Test' "C3", case when "CAHA_ANALYSIS"."CYHPHREL_UDATEDOSES"='true' then 'Keep Up-To-Date List of Medications' else NULL end "C4", 7 "C5" 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"."CYHPHREL_UDATEDOSES", case when "CAHA_ANALYSIS"."CYHPHREL_UDATEDOSES"='true' then 'Keep Up-To-Date List of Medications' else NULL end ) "T0" union select "T0"."C0" "CYHPHREL_UDATEDOSES", 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" "UDateDoses", "T0"."C5" "Sort_Order" from (select "CAHA_ANALYSIS"."CYHPHREL_UDATEDOSES" "C0", count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPISODEID") "C1", 2 "C2", "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR" "C3", case when "CAHA_ANALYSIS"."CYHPHREL_UDATEDOSES"='true' then 'Keep Up-To-Date List of Medications' else NULL end "C4", 7 "C5" 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"."CYHPHREL_UDATEDOSES", "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR", case when "CAHA_ANALYSIS"."CYHPHREL_UDATEDOSES"='true' then 'Keep Up-To-Date List of Medications' else NULL end ) "T0" union select "T0"."C0" "CYHPHREL_UDATEDOSES", 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" "UDateDoses", "T0"."C5" "Sort_Order" from (select "CAHA_ANALYSIS"."CYHPHREL_UDATEDOSES" "C0", count("CAHA_ANALYSIS"."PARTICIPANTID"||"CAHA_ANALYSIS"."EPISODEID") "C1", 3 "C2", 'HealthMedia ' "C3", case when "CAHA_ANALYSIS"."CYHPHREL_UDATEDOSES"='true' then 'Keep Up-To-Date List of Medications' else NULL end "C4", 7 "C5" 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"."CYHPHREL_UDATEDOSES", case when "CAHA_ANALYSIS"."CYHPHREL_UDATEDOSES"='true' then 'Keep Up-To-Date List of Medications' else NULL end ) "T0") "Union30" where case when ('3 Column'='2 Column' and "Union30"."Level4"=2) then 'No' else 'Yes' end ='Yes' and not "Union30"."UDateDoses" is null) "Union31" where "Union31"."C_2_Column_Filter"='Yes' group by "Union31"."DiscussMeds", "Union31"."Sort_Order", "Union31"."Level4", "Union31"."Header" RATIONALE: The SQL statement with SQL_ID "c1yb69bss1806" was found waiting for "IPC send completion sync" wait event. RELEVANT OBJECT: SQL statement with SQL_ID c1yb69bss1806 select "T0"."C0" "Dist", "T0"."C1" "No__of_Conditions", "T0"."C2" "Work_Missed_Due_to_Health", "T0"."C3" "c4", "T0"."C4" "Overall_Impairment", "T0"."C0" "Dist6" from (select 'HAP' "C0", case when "CAHA_DIAGNOSES_TOTAL"."DIAGNOSES_VALUE"<5 then to_char("CAHA_DIAGNOSES_TOTAL"."DIAGNOSES_VALUE") else '5+' end "C1", avg("CAHA_PR_MALL"."PR_M1") "C2", avg("CAHA_PR_MALL"."PR_M2") "C3", avg("CAHA_PR_MALL"."PR_M3") "C4" 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" where not case when ("CAHA_DIAGNOSES_TOTAL"."DIAGNOSES_VALUE"<5) then to_char("CAHA_DIAGNOSES_TOTAL"."DIAGNOSES_VALUE") else '5+' end is null and to_char("CAHA_ANALYSIS"."PUBLICATIONDT", 'yyyy/mm/dd') between '2012/01/01' and '2012/12/31' and "CAHA_CUSTOMER_DIMENSION"."DISTRIBUTOR"='HAP' group by case when "CAHA_DIAGNOSES_TOTAL"."DIAGNOSES_VALUE"<5 then to_char("CAHA_DIAGNOSES_TOTAL"."DIAGNOSES_VALUE") else '5+' end ) "T0" order by "No__of_Conditions" asc nulls last RECOMMENDATION 2: Application Analysis, 7.2% benefit (16544 seconds) ACTION: Investigate the cause for high "IPC send completion sync" waits in Service "dwh1". RECOMMENDATION 3: Application Analysis, 7.2% benefit (16525 seconds) ACTION: Investigate the cause for high "IPC send completion sync" waits in Module "BIBusTKServerMain.exe". RECOMMENDATION 4: Application Analysis, 7% benefit (15967 seconds) ACTION: Investigate the cause for high "IPC send completion sync" waits with P1 ("send count") value "1". SYMPTOMS THAT LED TO THE FINDING: SYMPTOM: Wait class "Other" was consuming significant database time. (40% impact [92208 seconds]) FINDING 9: 4.5% impact (10303 seconds) -------------------------------------- Individual SQL statements responsible for significant user I/O wait were found. RECOMMENDATION 1: SQL Tuning, 4.8% benefit (10928 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "8xjduhnwackh3". RELEVANT OBJECT: SQL statement with SQL_ID 8xjduhnwackh3 and PLAN_HASH 2617412812 select PARTICIPANTID,EPISODEID,PUBLICATIONDT,PIECECD,'BL' FLAG FROM data_analysis.DS_DA_ACTIVITY B WHERE DISTCD='AETN' AND projectcd='aess' and PUBLICATIONDT>=TO_DATE('01/01/2010','MM/DD/YYYY') AND CUSTOMQ2 is not null AND POPTIN IN ('Forced','Yes') union select DISTINCT PARTICIPANTID,EPISODEID,trunc(EVAL1DATE) EVAL1DATE,PIECECD,'E1' FLAG FROM DS_DA_ACTIVITY WHERE DISTCD='AETN' and PROJECTCD='aess' AND PUBLICATIONDT>=TO_DATE('01/01/2010','MM/DD/YYYY') AND EVAL1DATE IS NOT NULL AND POPTIN IN ('Forced','Yes') UNION select DISTINCT PARTICIPANTID,EPISODEID,trunc(EVAL2DATE) EVAL2DATE,PIECECD,'E2' FLAG FROM DS_DA_ACTIVITY WHERE DISTCD='AETN' and PROJECTCD='aess' AND PUBLICATIONDT>=TO_DATE('01/01/2010','MM/DD/YYYY') AND EVAL2DATE IS NOT NULL AND POPTIN IN ('Forced','Yes') UNION select DISTINCT PARTICIPANTID,EPISODEID,trunc(EVAL3DATE) EVAL3DATE,PIECECD,'E3' FLAG FROM DS_DA_ACTIVITY WHERE DISTCD='AETN' and PROJECTCD='aess' AND PUBLICATIONDT>=TO_DATE('01/01/2010','MM/DD/YYYY') AND EVAL3DATE IS NOT NULL AND POPTIN IN ('Forced','Yes') ACTION: Investigate the SQL statement with SQL_ID "8xjduhnwackh3" for possible performance improvements. RELEVANT OBJECT: SQL statement with SQL_ID 8xjduhnwackh3 and PLAN_HASH 2617412812 select PARTICIPANTID,EPISODEID,PUBLICATIONDT,PIECECD,'BL' FLAG FROM data_analysis.DS_DA_ACTIVITY B WHERE DISTCD='AETN' AND projectcd='aess' and PUBLICATIONDT>=TO_DATE('01/01/2010','MM/DD/YYYY') AND CUSTOMQ2 is not null AND POPTIN IN ('Forced','Yes') union select DISTINCT PARTICIPANTID,EPISODEID,trunc(EVAL1DATE) EVAL1DATE,PIECECD,'E1' FLAG FROM DS_DA_ACTIVITY WHERE DISTCD='AETN' and PROJECTCD='aess' AND PUBLICATIONDT>=TO_DATE('01/01/2010','MM/DD/YYYY') AND EVAL1DATE IS NOT NULL AND POPTIN IN ('Forced','Yes') UNION select DISTINCT PARTICIPANTID,EPISODEID,trunc(EVAL2DATE) EVAL2DATE,PIECECD,'E2' FLAG FROM DS_DA_ACTIVITY WHERE DISTCD='AETN' and PROJECTCD='aess' AND PUBLICATIONDT>=TO_DATE('01/01/2010','MM/DD/YYYY') AND EVAL2DATE IS NOT NULL AND POPTIN IN ('Forced','Yes') UNION select DISTINCT PARTICIPANTID,EPISODEID,trunc(EVAL3DATE) EVAL3DATE,PIECECD,'E3' FLAG FROM DS_DA_ACTIVITY WHERE DISTCD='AETN' and PROJECTCD='aess' AND PUBLICATIONDT>=TO_DATE('01/01/2010','MM/DD/YYYY') AND EVAL3DATE IS NOT NULL AND POPTIN IN ('Forced','Yes') RATIONALE: SQL statement with SQL_ID "8xjduhnwackh3" was executed 4 times and had an average elapsed time of 1431 seconds. RATIONALE: Waiting for event "db file sequential read" in wait class "User I/O" accounted for 48% of the database time spent in processing the SQL statement with SQL_ID "8xjduhnwackh3". RATIONALE: Waiting for event "db file parallel read" in wait class "User I/O" accounted for 34% of the database time spent in processing the SQL statement with SQL_ID "8xjduhnwackh3". RATIONALE: Waiting for event "read by other session" in wait class "User I/O" accounted for 12% of the database time spent in processing the SQL statement with SQL_ID "8xjduhnwackh3". RATIONALE: Average time spent in User I/O wait events per execution was 1352 seconds. RECOMMENDATION 2: SQL Tuning, 2.2% benefit (5125 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "4x8jgn9uzsndd". RELEVANT OBJECT: SQL statement with SQL_ID 4x8jgn9uzsndd and PLAN_HASH 4092877126 (SELECT DISTINCT 'Succeed' as Program,PARTICIPANTID,EPISODEID FROM DS_DA_ACTIVITY where instrumentcd IN ('hba','chba') AND PUBLICATIONDT IS NOT NULL and TRUNC(PUBLICATIONDT)<=TRUNC(SYSDATE-1) MINUS SELECT DISTINCT 'Succeed',PARTICIPANTID,EPISODEID FROM HBA_ANALYSIS1 where instrumentcd IN ('hba','chba')) union (SELECT DISTINCT 'Balance' as Program,PARTICIPANTID,EPISODEID FROM DS_DA_ACTIVITY where instrumentcd ='blna' AND PUBLICATIONDT IS NOT NULL and TRUNC(PUBLICATIONDT)<=TRUNC(SYSDATE-1) MINUS SELECT DISTINCT 'Balance',PARTICIPANTID,EPISODEID FROM BLNA_ANALYSIS where instrumentcd='blna') UNION (SELECT DISTINCT 'Breathe' as Program,PARTICIPANTID,EPISODEID FROM DS_DA_ACTIVITY where instrumentcd='brea' AND PUBLICATIONDT IS NOT NULL and TRUNC(PUBLICATIONDT)<=TRUNC(SYSDATE-1) MINUS SELECT DISTINCT 'Breathe',PARTICIPANTID,EPISODEID FROM BREA_ANALYSIS1 where instrumentcd ='brea') UNION (SELECT DISTINCT 'Relax' as Program,PARTICIPANTID,EPISODEID FROM DS_DA_ACTIVITY where instrumentcd ='rlxa' AND PUBLICATIONDT IS NOT NULL and TRUNC(PUBLICATIONDT)<=TRUNC(SYSDATE-1) MINUS SELECT DISTINCT 'Relax',PARTICIPANTID,EPISODEID FROM RLXA_ANALYSIS1 where instrumentcd ='rlxa') UNION (SELECT DISTINCT 'Care for Diabetes' as Program,PARTICIPANTID,EPISODEID FROM DS_DA_ACTIVITY where instrumentcd ='cadb' AND PUBLICATIONDT IS NOT NULL and TRUNC(PUBLICATIONDT)<=TRUNC(SYSDATE-1) MINUS SELECT DISTINCT 'Care for Diabetes',PARTICIPANTID,EPISODEID FROM CADB_ANALYSIS1 where instrumentcd='cadb') union (SELECT DISTINCT 'Nourish' as Program,PARTICIPANTID,EPISODEID FROM DS_DA_ACTIVITY where instrumentcd ='noua' AND PUBLICATIONDT IS NOT NULL and TRUNC(PUBLICATIONDT)<=TRUNC(SYSDATE-1) MINUS SELECT DISTINCT 'Nourish',PARTICIPANTID,EPISODEID FROM NOUA_ANALYSIS1 where instrumentcd='noua') UNION (SELECT DISTINCT 'Care for Back' as Program,PARTICIPANTID,EPISODEID FROM DS_DA_ACTIVITY where instrumentcd ='caba' AND PUBLICATIONDT IS NOT NULL and TRUNC(PUBLICATIONDT)<=TRUNC(SYSDATE-1) MINUS SELECT DISTINCT 'Care for Back',PARTICIPANTID,EPISODEID FROM CAba_ANALYSIS where instrumentcd='caba') union (SELECT DISTINCT 'Care for Health' as Program,PARTICIPANTID,EPISODEID FROM DS_DA_ACTIVITY where instrumentcd ='caha' AND PUBLICATIONDT IS NOT NULL and TRUNC(PUBLICATIONDT)<=TRUNC(SYSDATE-1) MINUS SELECT DISTINCT 'Care for Health',PARTICIPANTID,EPISODEID FROM CAHA_ANALYSIS where instrumentcd='caha') UNION (SELECT DISTINCT 'Binge' as Program,PARTICIPANTID,EPISODEID FROM DS_DA_ACTIVITY where instrumentcd ='bing' AND PUBLICATIONDT IS NOT NULL and TRUNC(PUBLICATIONDT)<=TRUNC(SYSDATE-1) MINUS SELECT DISTINCT 'Binge',PARTICIPANTID,EPISODEID FROM BING_ANALYSIS1 where instrumentcd='bing') union (SELECT DISTINCT 'Care for Pain' as Program,PARTICIPANTID,EPISODEID FROM DS_DA_ACTIVITY where instrumentcd ='capa' AND PUBLICATIONDT IS NOT NULL and TRUNC(PUBLICATIONDT)<=TRUNC(SYSDATE-1) MINUS SELECT DISTINCT 'Care for Pain',PARTICIPANTID,EPISODEID FROM CAPA_ANALYSIS1 where instrumentcd='capa') RATIONALE: SQL statement with SQL_ID "4x8jgn9uzsndd" was executed 1 times and had an average elapsed time of 4970 seconds. RATIONALE: Average time spent in User I/O wait events per execution was 4893 seconds. SYMPTOMS THAT LED TO THE FINDING: SYMPTOM: Wait class "User I/O" was consuming significant database time. (68% impact [155934 seconds]) FINDING 10: 3.6% impact (8346 seconds) -------------------------------------- Wait event "SQL*Net message from dblink" in wait class "Network" was consuming significant database time. RECOMMENDATION 1: Application Analysis, 3.6% benefit (8346 seconds) ACTION: Investigate the cause for high "SQL*Net message from dblink" 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 "5qnft3k8vw5vy" was found waiting for "SQL*Net message from dblink" wait event. RELEVANT OBJECT: SQL statement with SQL_ID 5qnft3k8vw5vy (SELECT DISTINCT UMEMBERID||chr(124)|| LASTNAME||chr(124)|| FIRSTNAME||chr(124)|| TO_CHAR(P.BIRTHDATE, 'MM/DD/YYYY')||chr(124)|| P.PARTICIPANTID||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)|| replace(GENDERCD,'U',NULL)||chr(124)|| ETHNICITYCD||chr(124)|| TO_CHAR(REGISTDATE, 'MM/DD/YYYY hh:mi:ss PM')||chr(124)|| PDISTCD||chr(124)|| CUSTOMERCD||chr(124)|| PPROJECTCD||chr(124)|| SITECD||chr(124)|| PAUTHCD||chr(124)|| XCLIENT||chr(124)|| XMEMBER||chr(124)|| PLOCATION||chr(124)|| POPTIN||chr(124)|| CUSTOMQ1||chr(124)|| CUSTOMQ2||chr(124)|| CUSTOMQ3||chr(124)|| CUSTOMQ4||chr(124)|| CUSTOMQ5||chr(124)|| CUSTOMQ31||chr(124)|| CUSTOMQ32||chr(124)|| CUSTOMQ33||chr(124)|| CUSTOMQ34||chr(124)|| CUSTOMQ35||chr(124)|| XID||chr(124)|| XGROUP||chr(124)|| BIOUNIQUEID FROM vhba.PARTICIPANT@hlt1 P WHERE testdata is null AND P.PDISTCD in ('BCMI','BCMIS') AND UPPER(P.POPTIN)='FORCED' AND TRUNC(REGISTDATE) >= TO_DATE('05/28/2009','MM/DD/YYYY') AND TRUNC(REGISTDATE) <= TRUNC(SYSDATE-1) --AND (REGISTDATE) >= TO_DATE('05/28/2009','MM/DD/YYYY') --AND (REGISTDATE) <= TRUNC(SYSDATE-1) --AND (REGISTDATE) BETWEEN TO_DATE('05/28/2009','MM/DD/YYYY') and SYSDATE-1 minus SELECT DISTINCT UMEMBERID||chr(124)|| LASTNAME||chr(124)|| FIRSTNAME||chr(124)|| TO_CHAR(P.BIRTHDATE, 'MM/DD/YYYY')||chr(124)|| P.PARTICIPANTID||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)|| replace(GENDERCD,'U',NULL)||chr(124)|| ETHNICITYCD||chr(124)|| TO_CHAR(REGISTDATE, 'MM/DD/YYYY hh:mi:ss PM')||chr(124)|| PDISTCD||chr(124)|| CUSTOMERCD||chr(124)|| PPROJECTCD||chr(124)|| SITECD||chr(124)|| PAUTHCD||chr(124)|| XCLIENT||chr(124)|| XMEMBER||chr(124)|| PLOCATION||chr(124)|| POPTIN||chr(124)|| CUSTOMQ1||chr(124)|| CUSTOMQ2||chr(124)|| CUSTOMQ3||chr(124)|| CUSTOMQ4||chr(124)|| CUSTOMQ5||chr(124)|| CUSTOMQ31||chr(124)|| CUSTOMQ32||chr(124)|| CUSTOMQ33||chr(124)|| CUSTOMQ34||chr(124)|| CUSTOMQ35||chr(124)|| XID||chr(124)|| XGROUP||chr(124)|| BIOUNIQUEID FROM vhba.PARTICIPANT@hlt1 P WHERE testdata is null AND P.PDISTCD in ('BCMI','BCMIS') AND UPPER(P.POPTIN)='FORCED' --AND TRUNC(REGISTDATE) >= TO_DATE('05/28/2009','MM/DD/YYYY') --AND TRUNC(REGISTDATE) <= TRUNC(SYSDATE-1) --AND (REGISTDATE) >= TO_DATE('05/28/2009','MM/DD/YYYY') --AND (REGISTDATE) <= TRUNC(SYSDATE-1)) AND (REGISTDATE) BETWEEN TO_DATE('05/28/2009','MM/DD/YYYY') and trunc(SYSDATE-1)) UNION (SELECT DISTINCT UMEMBERID||chr(124)|| LASTNAME||chr(124)|| FIRSTNAME||chr(124)|| TO_CHAR(P.BIRTHDATE, 'MM/DD/YYYY')||chr(124)|| P.PARTICIPANTID||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)|| replace(GENDERCD,'U',NULL)||chr(124)|| ETHNICITYCD||chr(124)|| TO_CHAR(REGISTDATE, 'MM/DD/YYYY hh:mi:ss PM')||chr(124)|| PDISTCD||chr(124)|| CUSTOMERCD||chr(124)|| PPROJECTCD||chr(124)|| SITECD||chr(124)|| PAUTHCD||chr(124)|| XCLIENT||chr(124)|| XMEMBER||chr(124)|| PLOCATION||chr(124)|| POPTIN||chr(124)|| CUSTOMQ1||chr(124)|| CUSTOMQ2||chr(124)|| CUSTOMQ3||chr(124)|| CUSTOMQ4||chr(124)|| CUSTOMQ5||chr(124)|| CUSTOMQ31||chr(124)|| CUSTOMQ32||chr(124)|| CUSTOMQ33||chr(124)|| CUSTOMQ34||chr(124)|| CUSTOMQ35||chr(124)|| XID||chr(124)|| XGROUP||chr(124)|| BIOUNIQUEID FROM vhba.PARTICIPANT@hlt1 P WHERE testdata is null AND P.PDISTCD in ('BCMI','BCMIS') AND UPPER(P.POPTIN)='FORCED' --AND TRUNC(REGISTDATE) >= TO_DATE('05/28/2009','MM/DD/YYYY') --AND TRUNC(REGISTDATE) <= TRUNC(SYSDATE-1) --AND (REGISTDATE) >= TO_DATE('05/28/2009','MM/DD/YYYY') --AND (REGISTDATE) <= TRUNC(SYSDATE-1) AND (REGISTDATE) BETWEEN TO_DATE('05/28/2009','MM/DD/YYYY') and trunc(SYSDATE-1) MINUS SELECT DISTINCT UMEMBERID||chr(124)|| LASTNAME||chr(124)|| FIRSTNAME||chr(124)|| TO_CHAR(P.BIRTHDATE, 'MM/DD/YYYY')||chr(124)|| P.PARTICIPANTID||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)|| replace(GENDERCD,'U',NULL)||chr(124)|| ETHNICITYCD||chr(124)|| TO_CHAR(REGISTDATE, 'MM/DD/YYYY hh:mi:ss PM')||chr(124)|| PDISTCD||chr(124)|| CUSTOMERCD||chr(124)|| PPROJECTCD||chr(124)|| SITECD||chr(124)|| PAUTHCD||chr(124)|| XCLIENT||chr(124)|| XMEMBER||chr(124)|| PLOCATION||chr(124)|| POPTIN||chr(124)|| CUSTOMQ1||chr(124)|| CUSTOMQ2||chr(124)|| CUSTOMQ3||chr(124)|| CUSTOMQ4||chr(124)|| CUSTOMQ5||chr(124)|| CUSTOMQ31||chr(124)|| CUSTOMQ32||chr(124)|| CUSTOMQ33||chr(124)|| CUSTOMQ34||chr(124)|| CUSTOMQ35||chr(124)|| XID||chr(124)|| XGROUP||chr(124)|| BIOUNIQUEID FROM vhba.PARTICIPANT@hlt1 P WHERE testdata is null AND P.PDISTCD in ('BCMI','BCMIS') AND UPPER(P.POPTIN)='FORCED' AND TRUNC(REGISTDATE) >= TO_DATE('05/28/2009','MM/DD/YYYY') AND TRUNC(REGISTDATE) <= TRUNC(SYSDATE-1)) --AND (REGISTDATE) >= TO_DATE('05/28/2009','MM/DD/YYYY') --AND (REGISTDATE) <= TRUNC(SYSDATE-1) --AND (REGISTDATE) BETWEEN TO_DATE('05/28/2009','MM/DD/YYYY') and SYSDATE-1) RATIONALE: The SQL statement with SQL_ID "cng3kjug4pkpm" was found waiting for "SQL*Net message from dblink" wait event. RELEVANT OBJECT: SQL statement with SQL_ID cng3kjug4pkpm (SELECT DISTINCT UMEMBERID||chr(124)|| LASTNAME||chr(124)|| FIRSTNAME||chr(124)|| TO_CHAR(P.BIRTHDATE, 'MM/DD/YYYY')||chr(124)|| P.PARTICIPANTID||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)|| replace(GENDERCD,'U',NULL)||chr(124)|| ETHNICITYCD||chr(124)|| TO_CHAR(REGISTDATE, 'MM/DD/YYYY hh:mi:ss PM')||chr(124)|| PDISTCD||chr(124)|| CUSTOMERCD||chr(124)|| PPROJECTCD||chr(124)|| SITECD||chr(124)|| PAUTHCD||chr(124)|| XCLIENT||chr(124)|| XMEMBER||chr(124)|| PLOCATION||chr(124)|| POPTIN||chr(124)|| CUSTOMQ1||chr(124)|| CUSTOMQ2||chr(124)|| CUSTOMQ3||chr(124)|| CUSTOMQ4||chr(124)|| CUSTOMQ5||chr(124)|| CUSTOMQ31||chr(124)|| CUSTOMQ32||chr(124)|| CUSTOMQ33||chr(124)|| CUSTOMQ34||chr(124)|| CUSTOMQ35||chr(124)|| XID||chr(124)|| XGROUP||chr(124)|| BIOUNIQUEID FROM vhba.PARTICIPANT@hlt1 P WHERE testdata is null AND P.PDISTCD in ('BCMI','BCMIS') AND UPPER(P.POPTIN)='FORCED' AND TRUNC(REGISTDATE) >= TO_DATE('05/28/2009','MM/DD/YYYY') AND TRUNC(REGISTDATE) <= TRUNC(SYSDATE-1) --AND (REGISTDATE) >= TO_DATE('05/28/2009','MM/DD/YYYY') --AND (REGISTDATE) <= TRUNC(SYSDATE-1) --AND (REGISTDATE) BETWEEN TO_DATE('05/28/2009','MM/DD/YYYY') and SYSDATE-1 minus SELECT DISTINCT UMEMBERID||chr(124)|| LASTNAME||chr(124)|| FIRSTNAME||chr(124)|| TO_CHAR(P.BIRTHDATE, 'MM/DD/YYYY')||chr(124)|| P.PARTICIPANTID||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)|| replace(GENDERCD,'U',NULL)||chr(124)|| ETHNICITYCD||chr(124)|| TO_CHAR(REGISTDATE, 'MM/DD/YYYY hh:mi:ss PM')||chr(124)|| PDISTCD||chr(124)|| CUSTOMERCD||chr(124)|| PPROJECTCD||chr(124)|| SITECD||chr(124)|| PAUTHCD||chr(124)|| XCLIENT||chr(124)|| XMEMBER||chr(124)|| PLOCATION||chr(124)|| POPTIN||chr(124)|| CUSTOMQ1||chr(124)|| CUSTOMQ2||chr(124)|| CUSTOMQ3||chr(124)|| CUSTOMQ4||chr(124)|| CUSTOMQ5||chr(124)|| CUSTOMQ31||chr(124)|| CUSTOMQ32||chr(124)|| CUSTOMQ33||chr(124)|| CUSTOMQ34||chr(124)|| CUSTOMQ35||chr(124)|| XID||chr(124)|| XGROUP||chr(124)|| BIOUNIQUEID FROM vhba.PARTICIPANT@hlt1 P WHERE testdata is null AND P.PDISTCD in ('BCMI','BCMIS') AND UPPER(P.POPTIN)='FORCED' --AND TRUNC(REGISTDATE) >= TO_DATE('05/28/2009','MM/DD/YYYY') --AND TRUNC(REGISTDATE) <= TRUNC(SYSDATE-1) AND (REGISTDATE) >= TO_DATE('05/28/2009','MM/DD/YYYY') AND (REGISTDATE) <= TRUNC(SYSDATE-1)) --AND (REGISTDATE) BETWEEN TO_DATE('05/28/2009','MM/DD/YYYY') and SYSDATE-1) UNION (SELECT DISTINCT UMEMBERID||chr(124)|| LASTNAME||chr(124)|| FIRSTNAME||chr(124)|| TO_CHAR(P.BIRTHDATE, 'MM/DD/YYYY')||chr(124)|| P.PARTICIPANTID||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)|| replace(GENDERCD,'U',NULL)||chr(124)|| ETHNICITYCD||chr(124)|| TO_CHAR(REGISTDATE, 'MM/DD/YYYY hh:mi:ss PM')||chr(124)|| PDISTCD||chr(124)|| CUSTOMERCD||chr(124)|| PPROJECTCD||chr(124)|| SITECD||chr(124)|| PAUTHCD||chr(124)|| XCLIENT||chr(124)|| XMEMBER||chr(124)|| PLOCATION||chr(124)|| POPTIN||chr(124)|| CUSTOMQ1||chr(124)|| CUSTOMQ2||chr(124)|| CUSTOMQ3||chr(124)|| CUSTOMQ4||chr(124)|| CUSTOMQ5||chr(124)|| CUSTOMQ31||chr(124)|| CUSTOMQ32||chr(124)|| CUSTOMQ33||chr(124)|| CUSTOMQ34||chr(124)|| CUSTOMQ35||chr(124)|| XID||chr(124)|| XGROUP||chr(124)|| BIOUNIQUEID FROM vhba.PARTICIPANT@hlt1 P WHERE testdata is null AND P.PDISTCD in ('BCMI','BCMIS') AND UPPER(P.POPTIN)='FORCED' --AND TRUNC(REGISTDATE) >= TO_DATE('05/28/2009','MM/DD/YYYY') --AND TRUNC(REGISTDATE) <= TRUNC(SYSDATE-1) AND (REGISTDATE) >= TO_DATE('05/28/2009','MM/DD/YYYY') AND (REGISTDATE) <= TRUNC(SYSDATE-1) --AND (REGISTDATE) BETWEEN TO_DATE('05/28/2009','MM/DD/YYYY') and SYSDATE-1 MINUS SELECT DISTINCT UMEMBERID||chr(124)|| LASTNAME||chr(124)|| FIRSTNAME||chr(124)|| TO_CHAR(P.BIRTHDATE, 'MM/DD/YYYY')||chr(124)|| P.PARTICIPANTID||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)|| replace(GENDERCD,'U',NULL)||chr(124)|| ETHNICITYCD||chr(124)|| TO_CHAR(REGISTDATE, 'MM/DD/YYYY hh:mi:ss PM')||chr(124)|| PDISTCD||chr(124)|| CUSTOMERCD||chr(124)|| PPROJECTCD||chr(124)|| SITECD||chr(124)|| PAUTHCD||chr(124)|| XCLIENT||chr(124)|| XMEMBER||chr(124)|| PLOCATION||chr(124)|| POPTIN||chr(124)|| CUSTOMQ1||chr(124)|| CUSTOMQ2||chr(124)|| CUSTOMQ3||chr(124)|| CUSTOMQ4||chr(124)|| CUSTOMQ5||chr(124)|| CUSTOMQ31||chr(124)|| CUSTOMQ32||chr(124)|| CUSTOMQ33||chr(124)|| CUSTOMQ34||chr(124)|| CUSTOMQ35||chr(124)|| XID||chr(124)|| XGROUP||chr(124)|| BIOUNIQUEID FROM vhba.PARTICIPANT@hlt1 P WHERE testdata is null AND P.PDISTCD in ('BCMI','BCMIS') AND UPPER(P.POPTIN)='FORCED' AND TRUNC(REGISTDATE) >= TO_DATE('05/28/2009','MM/DD/YYYY') AND TRUNC(REGISTDATE) <= TRUNC(SYSDATE-1)) --AND (REGISTDATE) >= TO_DATE('05/28/2009','MM/DD/YYYY') --AND (REGISTDATE) <= TRUNC(SYSDATE-1) --AND (REGISTDATE) BETWEEN TO_DATE('05/28/2009','MM/DD/YYYY') and SYSDATE-1) RATIONALE: The SQL statement with SQL_ID "art0rvxaxd7mx" was found waiting for "SQL*Net message from dblink" wait event. RELEVANT OBJECT: SQL statement with SQL_ID art0rvxaxd7mx select count(*) from ((SELECT DISTINCT UMEMBERID||chr(124)|| LASTNAME||chr(124)|| FIRSTNAME||chr(124)|| TO_CHAR(P.BIRTHDATE, 'MM/DD/YYYY')||chr(124)|| P.PARTICIPANTID||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)|| replace(GENDERCD,'U',NULL)||chr(124)|| ETHNICITYCD||chr(124)|| TO_CHAR(REGISTDATE, 'MM/DD/YYYY hh:mi:ss PM')||chr(124)|| PDISTCD||chr(124)|| CUSTOMERCD||chr(124)|| PPROJECTCD||chr(124)|| SITECD||chr(124)|| PAUTHCD||chr(124)|| XCLIENT||chr(124)|| XMEMBER||chr(124)|| PLOCATION||chr(124)|| POPTIN||chr(124)|| CUSTOMQ1||chr(124)|| CUSTOMQ2||chr(124)|| CUSTOMQ3||chr(124)|| CUSTOMQ4||chr(124)|| CUSTOMQ5||chr(124)|| CUSTOMQ31||chr(124)|| CUSTOMQ32||chr(124)|| CUSTOMQ33||chr(124)|| CUSTOMQ34||chr(124)|| CUSTOMQ35||chr(124)|| XID||chr(124)|| XGROUP||chr(124)|| BIOUNIQUEID FROM vhba.PARTICIPANT@hlt1 P WHERE testdata is null AND P.PDISTCD in ('BCMI','BCMIS') AND UPPER(P.POPTIN)='FORCED' --AND TRUNC(REGISTDATE) >= TO_DATE('05/28/2009','MM/DD/YYYY') --AND TRUNC(REGISTDATE) <= TRUNC(SYSDATE-1) AND (REGISTDATE) >= TO_DATE('05/28/2009','MM/DD/YYYY') AND (REGISTDATE) <= TRUNC(SYSDATE-1) --AND (REGISTDATE) BETWEEN TO_DATE('05/28/2009','MM/DD/YYYY') and trunc(SYSDATE-1) MINUS SELECT DISTINCT UMEMBERID||chr(124)|| LASTNAME||chr(124)|| FIRSTNAME||chr(124)|| TO_CHAR(P.BIRTHDATE, 'MM/DD/YYYY')||chr(124)|| P.PARTICIPANTID||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)|| replace(GENDERCD,'U',NULL)||chr(124)|| ETHNICITYCD||chr(124)|| TO_CHAR(REGISTDATE, 'MM/DD/YYYY hh:mi:ss PM')||chr(124)|| PDISTCD||chr(124)|| CUSTOMERCD||chr(124)|| PPROJECTCD||chr(124)|| SITECD||chr(124)|| PAUTHCD||chr(124)|| XCLIENT||chr(124)|| XMEMBER||chr(124)|| PLOCATION||chr(124)|| POPTIN||chr(124)|| CUSTOMQ1||chr(124)|| CUSTOMQ2||chr(124)|| CUSTOMQ3||chr(124)|| CUSTOMQ4||chr(124)|| CUSTOMQ5||chr(124)|| CUSTOMQ31||chr(124)|| CUSTOMQ32||chr(124)|| CUSTOMQ33||chr(124)|| CUSTOMQ34||chr(124)|| CUSTOMQ35||chr(124)|| XID||chr(124)|| XGROUP||chr(124)|| BIOUNIQUEID FROM vhba.PARTICIPANT@hlt1 P WHERE testdata is null AND P.PDISTCD in ('BCMI','BCMIS') AND UPPER(P.POPTIN)='FORCED' AND TRUNC(REGISTDATE) >= TO_DATE('05/28/2009','MM/DD/YYYY') AND TRUNC(REGISTDATE) <= TRUNC(SYSDATE-1)) --AND (REGISTDATE) >= TO_DATE('05/28/2009','MM/DD/YYYY') --AND (REGISTDATE) <= TRUNC(SYSDATE-1) --AND (REGISTDATE) BETWEEN TO_DATE('05/28/2009','MM/DD/YYYY') and trunc(SYSDATE-1) UNION (SELECT DISTINCT UMEMBERID||chr(124)|| LASTNAME||chr(124)|| FIRSTNAME||chr(124)|| TO_CHAR(P.BIRTHDATE, 'MM/DD/YYYY')||chr(124)|| P.PARTICIPANTID||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)|| replace(GENDERCD,'U',NULL)||chr(124)|| ETHNICITYCD||chr(124)|| TO_CHAR(REGISTDATE, 'MM/DD/YYYY hh:mi:ss PM')||chr(124)|| PDISTCD||chr(124)|| CUSTOMERCD||chr(124)|| PPROJECTCD||chr(124)|| SITECD||chr(124)|| PAUTHCD||chr(124)|| XCLIENT||chr(124)|| XMEMBER||chr(124)|| PLOCATION||chr(124)|| POPTIN||chr(124)|| CUSTOMQ1||chr(124)|| CUSTOMQ2||chr(124)|| CUSTOMQ3||chr(124)|| CUSTOMQ4||chr(124)|| CUSTOMQ5||chr(124)|| CUSTOMQ31||chr(124)|| CUSTOMQ32||chr(124)|| CUSTOMQ33||chr(124)|| CUSTOMQ34||chr(124)|| CUSTOMQ35||chr(124)|| XID||chr(124)|| XGROUP||chr(124)|| BIOUNIQUEID FROM vhba.PARTICIPANT@hlt1 P WHERE testdata is null AND P.PDISTCD in ('BCMI','BCMIS') AND UPPER(P.POPTIN)='FORCED' AND TRUNC(REGISTDATE) >= TO_DATE('05/28/2009','MM/DD/YYYY') AND TRUNC(REGISTDATE) <= TRUNC(SYSDATE-1) --AND (REGISTDATE) >= TO_DATE('05/28/2009','MM/DD/YYYY') --AND (REGISTDATE) <= TRUNC(SYSDATE-1) --AND (REGISTDATE) BETWEEN TO_DATE('05/28/2009','MM/DD/YYYY') and trunc(SYSDATE-1) MINUS SELECT DISTINCT UMEMBERID||chr(124)|| LASTNAME||chr(124)|| FIRSTNAME||chr(124)|| TO_CHAR(P.BIRTHDATE, 'MM/DD/YYYY')||chr(124)|| P.PARTICIPANTID||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)|| replace(GENDERCD,'U',NULL)||chr(124)|| ETHNICITYCD||chr(124)|| TO_CHAR(REGISTDATE, 'MM/DD/YYYY hh:mi:ss PM')||chr(124)|| PDISTCD||chr(124)|| CUSTOMERCD||chr(124)|| PPROJECTCD||chr(124)|| SITECD||chr(124)|| PAUTHCD||chr(124)|| XCLIENT||chr(124)|| XMEMBER||chr(124)|| PLOCATION||chr(124)|| POPTIN||chr(124)|| CUSTOMQ1||chr(124)|| CUSTOMQ2||chr(124)|| CUSTOMQ3||chr(124)|| CUSTOMQ4||chr(124)|| CUSTOMQ5||chr(124)|| CUSTOMQ31||chr(124)|| CUSTOMQ32||chr(124)|| CUSTOMQ33||chr(124)|| CUSTOMQ34||chr(124)|| CUSTOMQ35||chr(124)|| XID||chr(124)|| XGROUP||chr(124)|| BIOUNIQUEID FROM vhba.PARTICIPANT@hlt1 P WHERE testdata is null AND P.PDISTCD in ('BCMI','BCMIS') AND UPPER(P.POPTIN)='FORCED' --AND TRUNC(REGISTDATE) >= TO_DATE('05/28/2009','MM/DD/YYYY') --AND TRUNC(REGISTDATE) <= TRUNC(SYSDATE-1) AND (REGISTDATE) >= TO_DATE('05/28/2009','MM/DD/YYYY') AND (REGISTDATE) <= TRUNC(SYSDATE-1)) --AND (REGISTDATE) BETWEEN TO_DATE('05/28/2009','MM/DD/YYYY') and trunc(SYSDATE-1) ) RATIONALE: The SQL statement with SQL_ID "05qz58gqztd4q" was found waiting for "SQL*Net message from dblink" wait event. RELEVANT OBJECT: SQL statement with SQL_ID 05qz58gqztd4q (SELECT DISTINCT UMEMBERID||chr(124)|| LASTNAME||chr(124)|| FIRSTNAME||chr(124)|| TO_CHAR(P.BIRTHDATE, 'MM/DD/YYYY')||chr(124)|| P.PARTICIPANTID||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)|| replace(GENDERCD,'U',NULL)||chr(124)|| ETHNICITYCD||chr(124)|| TO_CHAR(REGISTDATE, 'MM/DD/YYYY hh:mi:ss PM')||chr(124)|| PDISTCD||chr(124)|| CUSTOMERCD||chr(124)|| PPROJECTCD||chr(124)|| SITECD||chr(124)|| PAUTHCD||chr(124)|| XCLIENT||chr(124)|| XMEMBER||chr(124)|| PLOCATION||chr(124)|| POPTIN||chr(124)|| CUSTOMQ1||chr(124)|| CUSTOMQ2||chr(124)|| CUSTOMQ3||chr(124)|| CUSTOMQ4||chr(124)|| CUSTOMQ5||chr(124)|| CUSTOMQ31||chr(124)|| CUSTOMQ32||chr(124)|| CUSTOMQ33||chr(124)|| CUSTOMQ34||chr(124)|| CUSTOMQ35||chr(124)|| XID||chr(124)|| XGROUP||chr(124)|| BIOUNIQUEID FROM vhba.PARTICIPANT@hlt1 P WHERE testdata is null AND P.PDISTCD in ('BCMI','BCMIS') AND UPPER(P.POPTIN)='FORCED' AND TRUNC(REGISTDATE) >= TO_DATE('05/28/2009','MM/DD/YYYY') AND TRUNC(REGISTDATE) <= TRUNC(SYSDATE-1) --AND (REGISTDATE) >= TO_DATE('05/28/2009','MM/DD/YYYY') --AND (REGISTDATE) <= TRUNC(SYSDATE-1) --AND (REGISTDATE) BETWEEN TO_DATE('05/28/2009','MM/DD/YYYY') and SYSDATE-1 minus SELECT DISTINCT UMEMBERID||chr(124)|| LASTNAME||chr(124)|| FIRSTNAME||chr(124)|| TO_CHAR(P.BIRTHDATE, 'MM/DD/YYYY')||chr(124)|| P.PARTICIPANTID||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)|| replace(GENDERCD,'U',NULL)||chr(124)|| ETHNICITYCD||chr(124)|| TO_CHAR(REGISTDATE, 'MM/DD/YYYY hh:mi:ss PM')||chr(124)|| PDISTCD||chr(124)|| CUSTOMERCD||chr(124)|| PPROJECTCD||chr(124)|| SITECD||chr(124)|| PAUTHCD||chr(124)|| XCLIENT||chr(124)|| XMEMBER||chr(124)|| PLOCATION||chr(124)|| POPTIN||chr(124)|| CUSTOMQ1||chr(124)|| CUSTOMQ2||chr(124)|| CUSTOMQ3||chr(124)|| CUSTOMQ4||chr(124)|| CUSTOMQ5||chr(124)|| CUSTOMQ31||chr(124)|| CUSTOMQ32||chr(124)|| CUSTOMQ33||chr(124)|| CUSTOMQ34||chr(124)|| CUSTOMQ35||chr(124)|| XID||chr(124)|| XGROUP||chr(124)|| BIOUNIQUEID FROM vhba.PARTICIPANT@hlt1 P WHERE testdata is null AND P.PDISTCD in ('BCMI','BCMIS') AND UPPER(P.POPTIN)='FORCED' --AND TRUNC(REGISTDATE) >= TO_DATE('05/28/2009','MM/DD/YYYY') --AND TRUNC(REGISTDATE) <= TRUNC(SYSDATE-1) --AND (REGISTDATE) >= TO_DATE('05/28/2009','MM/DD/YYYY') --AND (REGISTDATE) <= TRUNC(SYSDATE-1) AND (REGISTDATE) BETWEEN TO_DATE('05/28/2009','MM/DD/YYYY') and SYSDATE-1) UNION (SELECT DISTINCT UMEMBERID||chr(124)|| LASTNAME||chr(124)|| FIRSTNAME||chr(124)|| TO_CHAR(P.BIRTHDATE, 'MM/DD/YYYY')||chr(124)|| P.PARTICIPANTID||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)|| replace(GENDERCD,'U',NULL)||chr(124)|| ETHNICITYCD||chr(124)|| TO_CHAR(REGISTDATE, 'MM/DD/YYYY hh:mi:ss PM')||chr(124)|| PDISTCD||chr(124)|| CUSTOMERCD||chr(124)|| PPROJECTCD||chr(124)|| SITECD||chr(124)|| PAUTHCD||chr(124)|| XCLIENT||chr(124)|| XMEMBER||chr(124)|| PLOCATION||chr(124)|| POPTIN||chr(124)|| CUSTOMQ1||chr(124)|| CUSTOMQ2||chr(124)|| CUSTOMQ3||chr(124)|| CUSTOMQ4||chr(124)|| CUSTOMQ5||chr(124)|| CUSTOMQ31||chr(124)|| CUSTOMQ32||chr(124)|| CUSTOMQ33||chr(124)|| CUSTOMQ34||chr(124)|| CUSTOMQ35||chr(124)|| XID||chr(124)|| XGROUP||chr(124)|| BIOUNIQUEID FROM vhba.PARTICIPANT@hlt1 P WHERE testdata is null AND P.PDISTCD in ('BCMI','BCMIS') AND UPPER(P.POPTIN)='FORCED' --AND TRUNC(REGISTDATE) >= TO_DATE('05/28/2009','MM/DD/YYYY') --AND TRUNC(REGISTDATE) <= TRUNC(SYSDATE-1) --AND (REGISTDATE) >= TO_DATE('05/28/2009','MM/DD/YYYY') --AND (REGISTDATE) <= TRUNC(SYSDATE-1) AND (REGISTDATE) BETWEEN TO_DATE('05/28/2009','MM/DD/YYYY') and SYSDATE-1 MINUS SELECT DISTINCT UMEMBERID||chr(124)|| LASTNAME||chr(124)|| FIRSTNAME||chr(124)|| TO_CHAR(P.BIRTHDATE, 'MM/DD/YYYY')||chr(124)|| P.PARTICIPANTID||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)|| replace(GENDERCD,'U',NULL)||chr(124)|| ETHNICITYCD||chr(124)|| TO_CHAR(REGISTDATE, 'MM/DD/YYYY hh:mi:ss PM')||chr(124)|| PDISTCD||chr(124)|| CUSTOMERCD||chr(124)|| PPROJECTCD||chr(124)|| SITECD||chr(124)|| PAUTHCD||chr(124)|| XCLIENT||chr(124)|| XMEMBER||chr(124)|| PLOCATION||chr(124)|| POPTIN||chr(124)|| CUSTOMQ1||chr(124)|| CUSTOMQ2||chr(124)|| CUSTOMQ3||chr(124)|| CUSTOMQ4||chr(124)|| CUSTOMQ5||chr(124)|| CUSTOMQ31||chr(124)|| CUSTOMQ32||chr(124)|| CUSTOMQ33||chr(124)|| CUSTOMQ34||chr(124)|| CUSTOMQ35||chr(124)|| XID||chr(124)|| XGROUP||chr(124)|| BIOUNIQUEID FROM vhba.PARTICIPANT@hlt1 P WHERE testdata is null AND P.PDISTCD in ('BCMI','BCMIS') AND UPPER(P.POPTIN)='FORCED' AND TRUNC(REGISTDATE) >= TO_DATE('05/28/2009','MM/DD/YYYY') AND TRUNC(REGISTDATE) <= TRUNC(SYSDATE-1)) --AND (REGISTDATE) >= TO_DATE('05/28/2009','MM/DD/YYYY') --AND (REGISTDATE) <= TRUNC(SYSDATE-1) --AND (REGISTDATE) BETWEEN TO_DATE('05/28/2009','MM/DD/YYYY') and SYSDATE-1) RATIONALE: The SQL statement with SQL_ID "bqsc1bh9hyhzj" was found waiting for "SQL*Net message from dblink" wait event. RELEVANT OBJECT: SQL statement with SQL_ID bqsc1bh9hyhzj begin sys.dbms_snapshot_utl.set_up@"HLT1.WORLD" (:mown_col, :mas_col, :rollseg_col, :flag_col, :outflag_col, :snaptime_col, :loadertime_col, :num_sid, :snapid_tab, :max_tab_scn_col, :max_dl_scn_col, :max_pmop_scn_col, :trunc_scn_col, :max_ins_tim_col, :max_upd_tim_col, :max_del_tim_col, :max_ins_scn_col, :max_upd_scn_col, :max_del_scn_col, :hdl_col, :hdl_indx_col, :rscn); end; RECOMMENDATION 2: Application Analysis, 2.9% benefit (6683 seconds) ACTION: Investigate the cause for high "SQL*Net message from dblink" waits with P1 ("driver id") value "1413697536" and P2 ("#bytes") value "1". RECOMMENDATION 3: Application Analysis, 2.9% benefit (6634 seconds) ACTION: Investigate the cause for high "SQL*Net message from dblink" waits in Service "dwh1". RECOMMENDATION 4: Application Analysis, 2.3% benefit (5293 seconds) ACTION: Investigate the cause for high "SQL*Net message from dblink" waits in Module "TOAD 9.7.2.5". RECOMMENDATION 5: Application Analysis, 0.75% benefit (1712 seconds) ACTION: Investigate the cause for high "SQL*Net message from dblink" waits in Service "SYS$USERS". RECOMMENDATION 6: Application Analysis, 0.58% benefit (1340 seconds) ACTION: Investigate the cause for high "SQL*Net message from dblink" waits in Module "SQL*Plus". SYMPTOMS THAT LED TO THE FINDING: SYMPTOM: Wait class "Network" was consuming significant database time. (4.1% impact [9438 seconds]) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ADDITIONAL INFORMATION ---------------------- Wait class "Application" was not consuming significant database time. Wait class "Cluster" was not consuming significant database time. Wait class "Commit" was not consuming significant database time. Wait class "Concurrency" was not consuming significant database time. Wait class "Configuration" was not consuming significant database time. CPU was not a bottleneck for the instance. 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@dwh11> 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