ASH Report For DWH1/dwh12 DB Name DB Id Instance Inst Num Release RAC Host ------------ ----------- ------------ -------- ----------- --- ------------ DWH1 3987792604 dwh12 2 10.2.0.5.0 YES prod-rac-dwh CPUs SGA Size Buffer Cache Shared Pool ASH Buffer Size ---- ------------------ ------------------ ------------------ ------------------ 20 24,576M (100%) 22,240M (90.5%) 2,003M (8.2%) 35.0M (0.1%) Analysis Begin Time: 16-Apr-13 12:30:59 Analysis End Time: 23-Apr-13 12:30:59 Elapsed Time: 10,080.0 (mins) Sample Count: 137,455 Average Active Sessions: 2.27 Avg. Active Session per CPU: 0.11 Report Target: None specified Top User Events DB/Inst: DWH1/dwh12 (Apr 16 12:30 to 12:30) Avg Active Event Event Class % Activity Sessions ----------------------------------- --------------- ---------- ---------- db file sequential read User I/O 38.83 0.88 db file scattered read User I/O 16.91 0.38 PX Deq: reap credit Other 9.29 0.21 CPU + Wait for CPU CPU 7.27 0.17 PX Deq Credit: send blkd Other 7.01 0.16 ------------------------------------------------------------- Top Background Events DB/Inst: DWH1/dwh12 (Apr 16 12:30 to 12:30) Avg Active Event Event Class % Activity Sessions ----------------------------------- --------------- ---------- ---------- CPU + Wait for CPU CPU 6.22 0.14 ------------------------------------------------------------- Top Event P1/P2/P3 Values DB/Inst: DWH1/dwh12 (Apr 16 12:30 to 12:30) Event % Event P1 Value, P2 Value, P3 Value % Activity ------------------------------ ------- ----------------------------- ---------- Parameter 1 Parameter 2 Parameter 3 -------------------------- -------------------------- -------------------------- db file sequential read 39.22 "11","1","1" 0.00 file# block# blocks db file scattered read 16.93 "12","782737","120" 0.01 file# block# blocks PX Deq Credit: send blkd 7.01 "268632063","1","0" 0.16 sleeptime/senderid passes qref db file parallel read 4.21 "1","127","127" 0.17 files blocks requests IPC send completion sync 1.87 "1","0","0" 1.77 send count NOT DEFINED NOT DEFINED ------------------------------------------------------------- Top Service/Module DB/Inst: DWH1/dwh12 (Apr 16 12:30 to 12:30) Service Module % Activity Action % Action -------------- ------------------------ ---------- ------------------ ---------- dwh1 SQL*Plus 54.07 UNNAMED 54.07 BIBusTKServerMain.exe 23.11 UNNAMED 23.11 SYS$BACKGROUND UNNAMED 8.18 UNNAMED 8.18 SYS$USERS DBMS_SCHEDULER 7.88 GATHER_STATS_JOB 5.21 AUTO_SPACE_ADVISOR 2.67 UNNAMED 3.21 UNNAMED 3.21 ------------------------------------------------------------- Top Client IDs DB/Inst: DWH1/dwh12 (Apr 16 12:30 to 12:30) No data exists for this section of the report. ------------------------------------------------------------- Top SQL Command Types DB/Inst: DWH1/dwh12 (Apr 16 12:30 to 12:30) -> '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,369 38.90 0.88 INSERT 525 17.92 0.41 CREATE TABLE 282 14.79 0.34 UPDATE 430 10.97 0.25 DELETE 231 7.04 0.16 ------------------------------------------------------------- Top SQL Statements DB/Inst: DWH1/dwh12 (Apr 16 12:30 to 12:30) SQL ID Planhash % Activity Event % Event ------------- ----------- ---------- ------------------------------ ---------- 8szmwam7fysa3 2976124318 2.38 db file sequential read 2.32 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')) abxhrs7rbwd92 491636827 1.74 db file sequential read 1.66 CREATE TABLE data_analysis.huma_participantids tablespace users AS SELECT p.PART ICIPANTID, e.EPISODEID FROM PARTICIPANT p, EPISODES e, PIECESTATUS ps, DISTRIBUT OR d, CUSTOMER c, PROJECT pj, SITE s WHERE p.PARTICIPANTID = e.PARTICIPANTID AN D e.PARTICIPANTID = ps.PARTICIPANTID AND e.EPISODEID = ps.EPISODEID AND p.SITEC 78y6kuv670pss 2731320786 1.54 db file sequential read 1.47 CREATE TABLE DS_IR_ENROLLMENTS AS select DISTTX,CUSTOMERTX,PROJECTX,INSTRUMENTX, COUNT(DISTINCT PARTICIPANTID) NUMENROLL from DATA_ANALYSIS.V_DS_TAILORED_INSIGHT S WHERE ENROLLDATE IS NOT NULL and ENROLLDATE>EMAILSENTDATE GROUP BY DISTTX,CUST OMERTX,PROJECTX,INSTRUMENTX 13mn3gudbms19 1817276503 1.26 db file sequential read 1.23 create table data_analysis.hba_E1participantids tablespace users as select uni que(p.participantid), e.episodeid FROM DATAWAREHOUSE.MV_PARTICIPANT p, DATAWAR EHOUSE.MV_EPISODES e ,DATAWAREHOUSE.MV_PIECESTATUS ps, DATAWAREHOUSE.MV_PROGRE SS pg WHERE p.PARTICIPANTID = e.PARTICIPANTID and e.participantid = ps.partic 86zzu2d0dcgfq 3560125532 1.11 PX Deq: reap credit 0.74 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 SQL using literals DB/Inst: DWH1/dwh12 (Apr 16 12:30 to 12:30) # of Sampled Plan Hash % Activity SQL Versions ----------- ---------- ------------ Example SQL 1 -------------------- Example SQL 2 -------------------- 548495273 2.97 4081 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.95 2675 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/dwh12 (Apr 16 12:30 to 12:30) -> '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 ----------------------------------------------------------------- ---------- DATA_MART.PROC_CONCURRENCY_NAVISTAR 4.77 SQL 2.74 DATA_MART.PROC_CONCURRENCY_NAVISTAR 2.02 SYS.DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC 4.66 SQL 3.21 SYS.DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC 1.41 SYS.DBMS_REFRESH.REFRESH 2.73 SQL 2.49 SYS.DBMS_SPACE.AUTO_SPACE_ADVISOR_JOB_PROC 2.67 SYS.DBMS_SPACE.OBJECT_SPACE_USAGE 2.65 ------------------------------------------------------------- Top Sessions DB/Inst: DWH1/dwh12 (Apr 16 12:30 to 12:30) -> '# 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 -------------------- ------------------------------ ------------------ -------- 240,23721 2.86 db file sequential read 1.34 SUPERUSER sqlplus@batch01 (TNS V1-V3) 1,842/60K [ 3%] 3 510,36235 2.30 db file sequential read 1.10 SUPERUSER sqlplus@batch01 (TNS V1-V3) 1,512/60K [ 3%] 2 200,46547 1.94 db file scattered read 1.34 SYS oracle@prod-ra....datapipe.net 1,840/60K [ 3%] 54 125,48761 1.90 db file sequential read 0.89 SUPERUSER sqlplus@batch01 (TNS V1-V3) 1,217/60K [ 2%] 10 202,54175 1.84 db file scattered read 1.11 SYS oracle@prod-ra....datapipe.net 1,525/60K [ 3%] 34 ------------------------------------------------------------- Top Blocking Sessions DB/Inst: DWH1/dwh12 (Apr 16 12:30 to 12:30) No data exists for this section of the report. ------------------------------------------------------------- Top Sessions running PQs DB/Inst: DWH1/dwh12 (Apr 16 12:30 to 12:30) -> 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 -------------------- ------------------------------ 343,UNKNOWN(1) 1.52 9tx2n2sqzvhm2 IPC send completion sync 0.08 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 323,UNKNOWN(2) 1.22 bzc9n0r0ywcq1 PX Deq: reap credit 0.12 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 470,UNKNOWN(1) 1.21 86zzu2d0dcgfq PX Deq: reap credit 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 215,UNKNOWN(1) 1.19 bzc9n0r0ywcq1 PX Deq: reap credit 0.13 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 471,UNKNOWN(2) 1.14 bh4upwh0z7cnj PX Deq: reap credit 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 HAP Test' "Header", avg("CAHA_ANALYSIS" ------------------------------------------------------------- Top DB Objects DB/Inst: DWH1/dwh12 (Apr 16 12:30 to 12:30) -> With respect to Application, Cluster, User I/O and buffer busy waits only. Object ID % Activity Event % Event --------------- ---------- ------------------------------ ---------- Object Name (Type) Tablespace ----------------------------------------------------- ------------------------- 1682202 8.07 db file sequential read 7.43 DATAWAREHOUSE.DWH_EPISODEDATA (TABLE) DATAWAREHOUSESMALL 1423115 5.88 db file sequential read 2.51 DATA_ANALYSIS.HBA_ANALYSIS1 (TABLE) DATA_ANALYSISLARGE db file scattered read 2.28 35087 5.79 db file sequential read 2.75 DATA_ANALYSIS.BLNA_ANALYSIS (TABLE) DATA_ANALYSISLARGE db file scattered read 1.71 db file parallel read 1.03 1680856 5.34 db file sequential read 4.07 DATAWAREHOUSE.DWH_PARTICIPANTDATA (TABLE) DATAWAREHOUSESMALL 1785692 4.31 db file sequential read 2.63 DATA_ANALYSIS.DS_DA_ACTIVITY (TABLE) DATA_ANALYSISLARGE ------------------------------------------------------------- Top DB Files DB/Inst: DWH1/dwh12 (Apr 16 12:30 to 12:30) -> With respect to Cluster and User I/O events only. File ID % Activity Event % Event --------------- ---------- ------------------------------ ---------- File Name Tablespace ----------------------------------------------------- ------------------------- 37 8.91 db file sequential read 7.54 +ORAFILES/dwh1/data_warehouse_small1.dbf DATAWAREHOUSESMALL 18 7.72 db file sequential read 6.56 +ORAFILES/dwh1/data_warehouse_small02.dbf DATAWAREHOUSESMALL 33 5.62 db file sequential read 3.03 +ORAFILES/dwh1/data_analysislarge1.dbf DATA_ANALYSISLARGE db file scattered read 1.86 12 4.96 db file sequential read 2.31 +ORAFILES/dwh1/data_analysislarge2.dbf DATA_ANALYSISLARGE db file scattered read 1.88 19 4.43 db file sequential read 2.42 +ORAFILES/dwh1/data_analysislarge6.dbf DATA_ANALYSISLARGE db file scattered read 1.35 ------------------------------------------------------------- Top Latches DB/Inst: DWH1/dwh12 (Apr 16 12:30 to 12:30) No data exists for this section of the report. ------------------------------------------------------------- Activity Over Time DB/Inst: DWH1/dwh12 (Apr 16 12:30 to 12:30) -> 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:30:59 (849.0 min) 5,325 db file sequential read 1,065 0.77 PX Deq: reap credit 943 0.69 CPU + Wait for CPU 914 0.66 02:40:00 (1,010.0 mi 20,299 db file sequential read 7,571 5.51 db file scattered read 3,071 2.23 PX Deq: reap credit 3,002 2.18 19:30:00 (1,010.0 mi 17,110 db file sequential read 6,307 4.59 CPU + Wait for CPU 2,851 2.07 db file scattered read 1,849 1.35 12:20:00 (1,010.0 mi 8,962 db file sequential read 4,294 3.12 db file scattered read 2,956 2.15 CPU + Wait for CPU 602 0.44 05:10:00 (1,010.0 mi 9,876 db file sequential read 3,296 2.40 db file scattered read 2,265 1.65 CPU + Wait for CPU 1,355 0.99 22:00:00 (1,010.0 mi 16,302 db file sequential read 8,281 6.02 db file scattered read 4,316 3.14 CPU + Wait for CPU 1,953 1.42 14:50:00 (1,010.0 mi 11,608 CPU + Wait for CPU 4,965 3.61 db file sequential read 4,354 3.17 db file scattered read 892 0.65 07:40:00 (1,010.0 mi 2,411 db file sequential read 1,173 0.85 CPU + Wait for CPU 437 0.32 db file scattered read 411 0.30 00:30:00 (1,010.0 mi 21,209 db file sequential read 6,494 4.72 PX Deq: reap credit 4,348 3.16 PX Deq Credit: send blkd 3,248 2.36 17:20:00 (1,010.0 mi 19,493 db file sequential read 10,388 7.56 db file scattered read 3,875 2.82 CPU + Wait for CPU 2,301 1.67 10:10:00 (141.0 min) 4,860 PX Deq: reap credit 1,504 1.09 PX Deq Credit: send blkd 1,175 0.85 IPC send completion sync 962 0.70 ------------------------------------------------------------- End of Report