ASH Report For DWH1/dwh11 DB Name DB Id Instance Inst Num Release RAC Host ------------ ----------- ------------ -------- ----------- --- ------------ DWH1 3987792604 dwh11 1 10.2.0.5.0 YES prod-rac-dwh CPUs SGA Size Buffer Cache Shared Pool ASH Buffer Size ---- ------------------ ------------------ ------------------ ------------------ 16 24,576M (100%) 22,240M (90.5%) 2,045M (8.3%) 29.0M (0.1%) Analysis Begin Time: 16-Apr-13 12:07:15 Analysis End Time: 23-Apr-13 12:07:15 Elapsed Time: 10,080.0 (mins) Sample Count: 187,984 Average Active Sessions: 3.11 Avg. Active Session per CPU: 0.19 Report Target: None specified Top User Events DB/Inst: DWH1/dwh11 (Apr 16 12:07 to 12:07) Avg Active Event Event Class % Activity Sessions ----------------------------------- --------------- ---------- ---------- db file sequential read User I/O 30.45 0.95 PX Deq Credit: send blkd Other 20.97 0.65 db file scattered read User I/O 15.62 0.49 CPU + Wait for CPU CPU 8.54 0.27 PX Deq: reap credit Other 6.14 0.19 ------------------------------------------------------------- Top Background Events DB/Inst: DWH1/dwh11 (Apr 16 12:07 to 12:07) Avg Active Event Event Class % Activity Sessions ----------------------------------- --------------- ---------- ---------- CPU + Wait for CPU CPU 3.41 0.11 ------------------------------------------------------------- Top Event P1/P2/P3 Values DB/Inst: DWH1/dwh11 (Apr 16 12:07 to 12:07) Event % Event P1 Value, P2 Value, P3 Value % Activity ------------------------------ ------- ----------------------------- ---------- Parameter 1 Parameter 2 Parameter 3 -------------------------- -------------------------- -------------------------- db file sequential read 30.56 "37","1","1" 0.00 file# block# blocks PX Deq Credit: send blkd 20.97 "268566527","1","0" 1.62 sleeptime/senderid passes qref db file scattered read 15.62 "12","3547147","112" 0.00 file# block# blocks db file parallel read 3.97 "1","127","127" 0.23 files blocks requests IPC send completion sync 2.60 "1","0","0" 2.46 send count NOT DEFINED NOT DEFINED ------------------------------------------------------------- Top Service/Module DB/Inst: DWH1/dwh11 (Apr 16 12:07 to 12:07) Service Module % Activity Action % Action -------------- ------------------------ ---------- ------------------ ---------- dwh1 SQL*Plus 39.47 UNNAMED 39.47 SYS$USERS JDBC Thin Client 21.20 UNNAMED 21.20 dwh1 BIBusTKServerMain.exe 16.35 UNNAMED 16.35 SYS$BACKGROUND UNNAMED 4.97 UNNAMED 4.97 SYS$USERS DBMS_SCHEDULER 4.90 GATHER_STATS_JOB 2.73 AUTO_SPACE_ADVISOR 2.16 ------------------------------------------------------------- Top Client IDs DB/Inst: DWH1/dwh11 (Apr 16 12:07 to 12:07) No data exists for this section of the report. ------------------------------------------------------------- Top SQL Command Types DB/Inst: DWH1/dwh11 (Apr 16 12:07 to 12:07) -> 'Distinct SQLIDs' is the count of the distinct number of SQLIDs with the given SQL Command Type found over all the ASH samples in the analysis period Distinct Avg Active SQL Command Type SQLIDs % Activity Sessions ---------------------------------------- ---------- ---------- ---------- SELECT 1,629 47.89 1.49 INSERT 603 16.09 0.50 CREATE TABLE 415 12.98 0.40 UPDATE 556 10.12 0.31 DELETE 294 4.40 0.14 ------------------------------------------------------------- Top SQL Statements DB/Inst: DWH1/dwh11 (Apr 16 12:07 to 12:07) SQL ID Planhash % Activity Event % Event ------------- ----------- ---------- ------------------------------ ---------- duynr67t45w2r 3790413933 17.18 PX Deq Credit: send blkd 16.72 select /*+ parallel(T1,16) */ T1."PARTICIPANTID" as c1, T1."EPISODEID " as c2, CASE T1."GENDERCD" WHEN 'Male' THEN 'Male' WHEN 'Female' THEN ' Female' ELSE null END as c3, CASE T1."ETHNICITYCD" WHEN 'NativeAmerican' THEN 'Native American' ELSE (T1."ETHNICITYCD") END as c4, T1."AGE" as c 8szmwam7fysa3 2976124318 2.01 db file sequential read 1.97 insert into wri$_adv_objspace_trend_data select timepoint, space_usage, space_a lloc, quality from table(dbms_space.object_growth_trend(:1, :2, :3, :4, NULL, N ULL, NULL, 'FALSE', :5, 'FALSE')) 8xjduhnwackh3 2617412812 1.50 db file sequential read 1.08 select PARTICIPANTID,EPISODEID,PUBLICATIONDT,PIECECD,'BL' FLAG FROM data_analy sis.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 ('F orced','Yes') union select DISTINCT PARTICIPANTID,EPISODEID,trunc(EVAL1DATE) 922qdarm4cyjn 3320027328 1.15 read by other session 0.54 INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "DATA_ANALYSIS"."MV_BLNA_CUSTOMER_DIME NSION" select distinct e.participantid, e. episodeid, case when upper(d.disttx) like 'TAKEDA%' then 'Takeda' when d.distcd = 'ABBT' then 'Abbott' when d.distcd = 'ATNA' then 'Aetna' when d.distcd = cf077pjuzgpjk 200623408 1.14 db file sequential read 0.92 INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "DATA_ANALYSIS"."MV_CAHA_CUSTOMER_DIME NSION" select distinct e.participantid, e. episodeid, case when upper(d.disttx) like 'TAKEDA%' then 'Takeda' when d.distcd = 'ABBT' then 'Abbott' when d.distcd = 'ATNA' then 'Aetna' when d.distcd = ------------------------------------------------------------- Top SQL using literals DB/Inst: DWH1/dwh11 (Apr 16 12:07 to 12:07) # of Sampled Plan Hash % Activity SQL Versions ----------- ---------- ------------ Example SQL 1 -------------------- Example SQL 2 -------------------- 548495273 2.47 4652 0y78qv9a5b9kd select "Union10"."DiscussMeds" "level0key", "Union10"."CYHPHREL_DISCUSSMEDS" "CY HPHREL_DISCUSSMEDS", "Union10"."Header" "levelkey", "Union10"."Level4" "Level4", sum("Union10"."Percent") "Percent" from (select "T0"."C0" "CYHPHREL_DISCUSSMEDS ", sum("T0"."C1") over () "Total_no_of_Participants", "T0"."C1"/nullif(sum("T0". gxn6vw8xrqdqu select "Union6"."Header" "levelkey", "Union6"."Level4" "Level2", "Union6"."UAdvi ce" "level0key", "Union6"."CYHMDREL_UADVICE" "CYHMDREL_UADVICE", sum("Union6"."P ercent") "Percent" from (select "T0"."C0" "CYHMDREL_UADVICE", sum("T0"."C1") ove r () "Total_no_of_Participants", "T0"."C1"/nullif(sum("T0"."C1") over (), 0) "Pe 1923653143 1.49 2792 01j0g45623uj3 UPDATE DATA_ANALYSIS.BLNA_ANALYSIS2 SET GENMAINJOBV2='Sales' where (participanti d,episodeid) in ( SELECT DISTINCT S1.PARTICIPANTID,S1.EPISODEID FROM DATA_ANALYS IS.BLNA_ANALYSIS S1, DATA_ANALYSIS.BLNA_ANALYSIS2 S2 WHERE S1.PARTICIPANTID=S2.P ARTICIPANTID AND S1.EPISODEID=S2.EPISODEID AND PREMPTYPE='Sales') AND GENMAINJOB gvdkj13skm9hs UPDATE DATA_ANALYSIS.BLNA_ANALYSIS2 SET NUTMODPHY='None' where (participantid,ep isodeid) in ( SELECT DISTINCT S1.PARTICIPANTID,S1.EPISODEID FROM DATA_ANALYSIS.B LNA_ANALYSIS S1, DATA_ANALYSIS.BLNA_ANALYSIS2 S2 WHERE S1.PARTICIPANTID=S2.PARTI CIPANTID AND S1.EPISODEID=S2.EPISODEID AND BALCARDIOEXERCISEV2='RarelyNever') AN ------------------------------------------------------------- Top PL/SQL Procedures DB/Inst: DWH1/dwh11 (Apr 16 12:07 to 12:07) -> 'PL/SQL entry subprogram' represents the application's top-level entry-point(procedure, function, trigger, package initialization or RPC call) into PL/SQL. -> 'PL/SQL current subprogram' is the pl/sql subprogram being executed at the point of sampling . If the value is 'SQL', it represents the percentage of time spent executing SQL for the particular plsql entry subprogram PLSQL Entry Subprogram % Activity ----------------------------------------------------------------- ---------- PLSQL Current Subprogram % Current ----------------------------------------------------------------- ---------- SYS.DBMS_REFRESH.REFRESH 3.39 SQL 3.17 DATA_MART.PROC_CONCURRENCY_NAVISTAR 2.90 DATA_MART.PROC_CONCURRENCY_NAVISTAR 1.70 SQL 1.20 SYS.DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC 2.53 SQL 1.85 SYS.DBMS_SPACE.AUTO_SPACE_ADVISOR_JOB_PROC 2.16 SYS.DBMS_SPACE.OBJECT_SPACE_USAGE 2.15 SYS.DBMS_BACKUP_RESTORE.BACKUPPIECECREATE#10 2.11 SYS.DBMS_BACKUP_RESTORE.BACKUPPIECECREATE#10 2.11 ------------------------------------------------------------- Top Sessions DB/Inst: DWH1/dwh11 (Apr 16 12:07 to 12:07) -> '# Samples Active' shows the number of ASH samples in which the session was found waiting for that particular event. The percentage shown in this column is calculated with respect to wall clock time and not total database activity. -> 'XIDs' shows the number of distinct transaction IDs sampled in ASH when the session was waiting for that particular event -> For sessions running Parallel Queries, this section will NOT aggregate the PQ slave activity into the session issuing the PQ. Refer to the 'Top Sessions running PQs' section for such statistics. Sid, Serial# % Activity Event % Event --------------- ---------- ------------------------------ ---------- User Program # Samples Active XIDs -------------------- ------------------------------ ------------------ -------- 423,57252 1.85 PX Deq Credit: send blkd 1.84 DATA_ANALYSIS oracle@prod-ra....datapipe.net 3,465/60K [ 6%] 0 208, 4806 1.80 PX Deq Credit: send blkd 1.79 DATA_ANALYSIS oracle@prod-ra....datapipe.net 3,360/60K [ 6%] 0 215,20225 1.71 PX Deq Credit: send blkd 1.69 DATA_ANALYSIS oracle@prod-ra....datapipe.net 3,186/60K [ 5%] 0 273,42752 1.63 PX Deq Credit: send blkd 1.62 DATA_ANALYSIS oracle@prod-ra....datapipe.net 3,044/60K [ 5%] 0 512, 1113 1.60 db file sequential read 0.82 DATA_MART sqlplus@batch01 (TNS V1-V3) 1,547/60K [ 3%] 9 ------------------------------------------------------------- Top Blocking Sessions DB/Inst: DWH1/dwh11 (Apr 16 12:07 to 12:07) No data exists for this section of the report. ------------------------------------------------------------- Top Sessions running PQs DB/Inst: DWH1/dwh11 (Apr 16 12:07 to 12:07) -> This section aggregates all the work done by the PQ slaves into the session issuing the parallel query. Sid,Srl# (Inst) % Activity SQL ID Event % Event --------------- ---------- ------------- ----------------------------- -------- User Program -------------------- ------------------------------ 190,12034(1) 17.18 duynr67t45w2r PX Deq Credit: send blkd 16.72 DATA_ANALYSIS JDBC Thin Client select /*+ parallel(T1,16) */ T1."PARTICIPANTID" as c1, T1."EPISODEID " as c2, CASE T1."GENDERCD" WHEN 'Male' THEN 'Male' WHEN 'Female' THEN ' Female' ELSE null END as c3, CASE T1."ETHNICITYCD" WHEN 'NativeAmerican' THEN 'Native American' ELSE (T1."ETHNICITYCD") END as c4, T1."AGE" as c 89,UNKNOWN(2) 1.04 bzc9n0r0ywcq1 PX Deq: reap credit 0.07 UserID: select "Union13"."Header" "Header", "Union13"."Level1" "Level2", sum("Union13"." CYHRXMOT") "total_Union13_CYHRXMOT_auto__0", count("Union13"."CYHRXMOT") "c4", s um("Union13"."CYHRXCONF") "c5", count("Union13"."CYHRXCONF") "c6", sum("Union13" ."Managing_NormalActs") "c7", count("Union13"."Managing_NormalActs") "c8", "Unio 274,UNKNOWN(2) 1.00 86zzu2d0dcgfq IPC send completion sync 0.10 UserID: select distinct "Union13"."Header" "Header", "Union13"."Level1" "Level2", min(ca se 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"."CY ------------------------------------------------------------- Top DB Objects DB/Inst: DWH1/dwh11 (Apr 16 12:07 to 12:07) -> With respect to Application, Cluster, User I/O and buffer busy waits only. Object ID % Activity Event % Event --------------- ---------- ------------------------------ ---------- Object Name (Type) Tablespace ----------------------------------------------------- ------------------------- 1785692 7.40 db file sequential read 4.79 DATA_ANALYSIS.DS_DA_ACTIVITY (TABLE) DATA_ANALYSISLARGE db file scattered read 1.43 db file parallel read 1.07 1682202 5.38 db file sequential read 4.47 DATAWAREHOUSE.DWH_EPISODEDATA (TABLE) DATAWAREHOUSESMALL 35087 4.60 db file sequential read 2.20 DATA_ANALYSIS.BLNA_ANALYSIS (TABLE) DATA_ANALYSISLARGE db file scattered read 1.84 1423115 4.53 db file scattered read 1.78 DATA_ANALYSIS.HBA_ANALYSIS1 (TABLE) DATA_ANALYSISLARGE db file sequential read 1.50 db file parallel read 1.15 1680856 4.27 db file sequential read 2.72 DATAWAREHOUSE.DWH_PARTICIPANTDATA (TABLE) DATAWAREHOUSESMALL ------------------------------------------------------------- Top DB Files DB/Inst: DWH1/dwh11 (Apr 16 12:07 to 12:07) -> With respect to Cluster and User I/O events only. File ID % Activity Event % Event --------------- ---------- ------------------------------ ---------- File Name Tablespace ----------------------------------------------------- ------------------------- 37 6.57 db file sequential read 4.82 +ORAFILES/dwh1/data_warehouse_small1.dbf DATAWAREHOUSESMALL 18 5.52 db file sequential read 4.06 +ORAFILES/dwh1/data_warehouse_small02.dbf DATAWAREHOUSESMALL 33 5.23 db file sequential read 2.85 +ORAFILES/dwh1/data_analysislarge1.dbf DATA_ANALYSISLARGE db file scattered read 1.72 12 4.89 db file scattered read 2.14 +ORAFILES/dwh1/data_analysislarge2.dbf DATA_ANALYSISLARGE db file sequential read 2.04 19 4.51 db file sequential read 2.46 +ORAFILES/dwh1/data_analysislarge6.dbf DATA_ANALYSISLARGE db file scattered read 1.55 ------------------------------------------------------------- Top Latches DB/Inst: DWH1/dwh11 (Apr 16 12:07 to 12:07) No data exists for this section of the report. ------------------------------------------------------------- Activity Over Time DB/Inst: DWH1/dwh11 (Apr 16 12:07 to 12:07) -> Analysis period is divided into smaller time slots -> Top 3 events are reported in each of those slots -> 'Slot Count' shows the number of ASH samples in that slot -> 'Event Count' shows the number of ASH samples waiting for that event in that slot -> '% Event' is 'Event Count' over all ASH samples in the analysis period Slot Event Slot Time (Duration) Count Event Count % Event -------------------- -------- ------------------------------ -------- ------- 12:07:15 (872.8 min) 11,128 db file sequential read 4,658 2.48 db file scattered read 2,444 1.30 CPU + Wait for CPU 923 0.49 02:40:00 (1,010.0 mi 19,507 db file sequential read 5,026 2.67 db file scattered read 4,390 2.34 PX Deq: reap credit 2,470 1.31 19:30:00 (1,010.0 mi 21,594 db file sequential read 9,048 4.81 db file scattered read 4,959 2.64 CPU + Wait for CPU 2,027 1.08 12:20:00 (1,010.0 mi 9,392 db file sequential read 3,710 1.97 db file scattered read 2,456 1.31 CPU + Wait for CPU 1,153 0.61 05:10:00 (1,010.0 mi 11,375 db file sequential read 5,630 2.99 db file scattered read 2,164 1.15 CPU + Wait for CPU 1,233 0.66 22:00:00 (1,010.0 mi 38,794 PX Deq Credit: send blkd 18,839 10.02 db file sequential read 9,400 5.00 db file scattered read 5,007 2.66 14:50:00 (1,010.0 mi 21,397 PX Deq Credit: send blkd 12,733 6.77 CPU + Wait for CPU 4,409 2.35 db file sequential read 1,567 0.83 07:40:00 (1,010.0 mi 13,636 db file sequential read 5,117 2.72 CPU + Wait for CPU 3,417 1.82 db file scattered read 2,863 1.52 00:30:00 (1,010.0 mi 22,918 db file sequential read 5,877 3.13 PX Deq: reap credit 4,362 2.32 CPU + Wait for CPU 2,930 1.56 17:20:00 (1,010.0 mi 14,379 db file sequential read 6,999 3.72 db file scattered read 2,037 1.08 CPU + Wait for CPU 1,984 1.06 10:10:00 (117.3 min) 3,850 PX Deq: reap credit 1,693 0.90 PX Deq Credit: send blkd 1,063 0.57 db file sequential read 416 0.22 ------------------------------------------------------------- End of Report