WORKLOAD REPOSITORY report for

DB Name	DB Id	Instance	Inst num	Startup Time	Release	RAC
DEMO	3430975138	DEMO2	2	26-Jun-09 00:06	11.1.0.7.0	YES

Host Name	Platform	CPUs	Cores	Sockets	Memory (GB)
host102.us.oracle.com	Linux x86 64-bit	8	8	2	31.44

	Snap Id	Snap Time	Sessions	Cursors/Session
Begin Snap:	912	27-Jun-09 17:26:05	48	3.2
End Snap:	913	27-Jun-09 17:30:07	54	2.8
Elapsed:		4.03 (mins)
DB Time:		30.06 (mins)

Report Summary

Cache Sizes

	Begin	End
Buffer Cache:	4,928M	4,928M	Std Block Size:	8K
Shared Pool Size:	1,024M	1,024M	Log Buffer:	35,328K

Load Profile

	Per Second	Per Transaction	Per Exec	Per Call
DB Time(s):	7.5	21.2	0.57	0.81
DB CPU(s):	4.5	12.8	0.35	0.49
Redo size:	6,743.0	19,184.1
Logical reads:	15,257.4	43,407.8
Block changes:	45.9	130.5
Physical reads:	26,072.1	74,176.0
Physical writes:	10,960.5	31,183.1
User calls:	9.3	26.3
Parses:	5.5	15.6
Hard parses:	0.2	0.7
W/A MB processed:	63,088,758.2	179,489,743.8
Logons:	1.4	4.0
Executes:	13.0	37.1
Rollbacks:	0.2	0.6
Transactions:	0.4

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %:	47.13	Redo NoWait %:	100.00
Buffer Hit %:	99.98	In-memory Sort %:	100.00
Library Hit %:	98.05	Soft Parse %:	95.61
Execute to Parse %:	58.03	Latch Hit %:	99.99
Parse CPU to Parse Elapsd %:	0.00	% Non-Parse CPU:	99.99

Shared Pool Statistics

	Begin	End
Memory Usage %:	71.98	72.24
% SQL with executions>1:	93.67	93.50
% Memory for SQL w/exec>1:	86.27	85.72

Top 5 Timed Foreground Events

Host CPU (CPUs: 8 Cores: 8 Sockets: 2)

Load Average Begin	Load Average End	%User	%System	%WIO	%Idle
0.02	7.75	49.1	7.7	0.0	41.2

Instance CPU

%Total CPU	%Busy CPU	%DB time waiting for CPU (Resource Manager)
56.6	96.2	0.0

Memory Statistics

	Begin	End
Host Mem (MB):	32,189.6	32,189.6
SGA use (MB):	6,144.0	6,144.0
PGA use (MB):	419.0	486.6
% Host Mem used for SGA+PGA:	20.39	20.39

RAC Statistics

	Begin	End
Number of Instances:	8	8

Global Cache Load Profile

	Per Second	Per Transaction
Global Cache blocks received:	2.56	7.28
Global Cache blocks served:	4.10	11.67
GCS/GES messages received:	28.04	79.76
GCS/GES messages sent:	35.60	101.28
DBWR Fusion writes:	0.24	0.67
Estd Interconnect traffic (KB)	65.72

Global Cache Efficiency Percentages (Target local+remote 100%)

Buffer access - local cache %:	97.77
Buffer access - remote cache %:	2.21
Buffer access - disk %:	0.02

Global Cache and Enqueue Services - Workload Characteristics

Avg global enqueue get time (ms):	0.2
Avg global cache cr block receive time (ms):	0.2
Avg global cache current block receive time (ms):	0.4
Avg global cache cr block build time (ms):	0.0
Avg global cache cr block send time (ms):	0.1
Global cache log flushes for cr blocks served %:	3.2
Avg global cache cr block flush time (ms):	4.4
Avg global cache current block pin time (ms):	0.0
Avg global cache current block send time (ms):	0.0
Global cache log flushes for current blocks served %:	0.8
Avg global cache current block flush time (ms):	0.0

Global Cache and Enqueue Services - Messaging Statistics

Avg message sent queue time (ms):	1,294.2
Avg message sent queue time on ksxp (ms):	0.7
Avg message received queue time (ms):	0.0
Avg GCS message process time (ms):	0.0
Avg GES message process time (ms):	0.0
% of direct sent messages:	52.24
% of indirect sent messages:	37.02
% of flow controlled messages:	10.74

Cluster Interconnect

Main Report

• Report Summary
• Wait Events Statistics
• SQL Statistics
• Instance Activity Statistics
• IO Stats
• Buffer Pool Statistics
• Advisory Statistics
• Wait Statistics
• Undo Statistics
• Latch Statistics
• Segment Statistics
• Dictionary Cache Statistics
• Library Cache Statistics
• Memory Statistics
• Streams Statistics
• Resource Limit Statistics
• init.ora Parameters

More RAC Statistics

• RAC Report Summary
• Global Enqueue Statistics
• Global CR Served Stats
• Global CURRENT Served Stats
• Global Cache Transfer Stats
• Interconnect Stats

Wait Events Statistics

• Time Model Statistics
• Operating System Statistics
• Operating System Statistics - Detail
• Foreground Wait Class
• Foreground Wait Events
• Background Wait Events
• Wait Event Histogram
• Service Statistics
• Service Wait Class Stats

Time Model Statistics

• Total time in database user-calls (DB Time): 1803.6s
• Statistics including the word "background" measure background process time, and so do not contribute to the DB time statistic
• Ordered by % or DB time desc, Statistic name

Back to Wait Events Statistics
Back to Top

Operating System Statistics

• *TIME statistic values are diffed. All others display actual values. End Value is displayed if different
• ordered by statistic type (CPU Use, Virtual Memory, Hardware Config), Name

Back to Wait Events Statistics
Back to Top

Operating System Statistics - Detail

Back to Wait Events Statistics
Back to Top

Foreground Wait Class

• s - second, ms - millisecond - 1000th of a second
• ordered by wait time desc, waits desc
• %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
• Captured Time accounts for 98.5% of Total DB time 1,803.55 (s)
• Total FG Wait Time: 689.48 (s) DB CPU time: 1,086.98 (s)

Back to Wait Events Statistics
Back to Top

Foreground Wait Events

• s - second, ms - millisecond - 1000th of a second
• Only events with Total Wait Time (s) >= .001 are shown
• ordered by wait time desc, waits desc (idle events last)
• %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0

Back to Wait Events Statistics
Back to Top

Background Wait Events

• ordered by wait time desc, waits desc (idle events last)
• Only events with Total Wait Time (s) >= .001 are shown
• %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0

Back to Wait Events Statistics
Back to Top

Wait Event Histogram

• Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
• % of Waits: value of .0 indicates value was <.05%. Value of null is truly 0
• % of Waits: column heading of <=1s is truly <1024ms, >1s is truly >=1024ms
• Ordered by Event (idle events last)

Back to Wait Events Statistics
Back to Top

Service Statistics

• ordered by DB Time

Back to Wait Events Statistics
Back to Top

Service Wait Class Stats

• Wait Class info for services in the Service Statistics section.
• Total Waits and Time Waited displayed for the following wait classes: User I/O, Concurrency, Administrative, Network
• Time Waited (Wt Time) in seconds

Back to Wait Events Statistics
Back to Top

SQL Statistics

• SQL ordered by Elapsed Time
• SQL ordered by CPU Time
• SQL ordered by Gets
• SQL ordered by Reads
• SQL ordered by Executions
• SQL ordered by Parse Calls
• SQL ordered by Sharable Memory
• SQL ordered by Version Count
• SQL ordered by Cluster Wait Time
• Complete List of SQL Text

SQL ordered by Elapsed Time

• Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
• % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
• Total DB Time (s): 1,804
• Captured SQL account for 96.2% of Total
• Total DB Time (s): 1,804
• Captured PL/SQL account for 0.2% of Total

Back to SQL Statistics
Back to Top

SQL ordered by CPU Time

• Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
• % Total is the CPU Time divided into the Total CPU Time times 100
• Total CPU Time (s): 1,087
• Captured SQL account for 98.1% of Total
• Total CPU Time (s): 1,087
• Captured PL/SQL account for 0.2% of Total

Back to SQL Statistics
Back to Top

SQL ordered by Gets

• Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
• Total Buffer Gets: 3,689,663
• Captured SQL account for 98.4% of Total

Back to SQL Statistics
Back to Top

SQL ordered by Reads

• Total Disk Reads: 6,304,959
• Captured SQL account for 99.6% of Total

Back to SQL Statistics
Back to Top

SQL ordered by Executions

• Total Executions: 3,150
• Captured SQL account for 31.9% of Total

Back to SQL Statistics
Back to Top

SQL ordered by Parse Calls

• Total Parse Calls: 1,322
• Captured SQL account for 34.9% of Total

Back to SQL Statistics
Back to Top

SQL ordered by Sharable Memory

• Only Statements with Sharable Memory greater than 1048576 are displayed

Back to SQL Statistics
Back to Top

SQL ordered by Version Count

No data exists for this section of the report.

Back to SQL Statistics
Back to Top

SQL ordered by Cluster Wait Time

• %Ela is Cluster Wait Time as percentage of SQL Elapsed Time
• %Total is Cluster Wait Time as percentage of Total Cluster Wait Time
• Only SQL with Cluster Wait Time > .005 seconds is reported
• Total Cluster Wait Time (s): 0
• Captured Segments account for 47.2% of Total

Back to SQL Statistics
Back to Top

Complete List of SQL Text

SQL Id	SQL Text
062savj8zgzut	UPDATE sys.wri$_adv_parameters SET datatype = :1, value = :2, flags = :3, description = :4 WHERE task_id = :5 AND name = :6
089dbukv1aanh	SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) FROM DUAL
0v6s91manuhz8	/* OracleOEM */ SELECT blocking_sid, num_blocked FROM ( SELECT blocking_sid, SUM(num_blocked) num_blocked FROM ( SELECT l.id1, l.id2, MAX(DECODE(l.block, 1, i.instance_name||'-'||l.sid, 2, i.instance_name||'-'||l.sid, 0 )) blocking_sid, SUM(DECODE(l.request, 0, 0, 1 )) num_blocked FROM gv$lock l, gv$instance i WHERE ( l.block!= 0 OR l.request > 0 ) AND l.inst_id = i.inst_id GROUP BY l.id1, l.id2) GROUP BY blocking_sid ORDER BY num_blocked DESC) WHERE num_blocked != 0 AND blocking_sid != '0'
14wnf35dahb7v	SELECT A.ID, A.TYPE FROM SYS.WRI$_ADV_DEFINITIONS A WHERE A.NAME = :B1
1cq3qr774cu45	insert into WRH$_IOSTAT_FILETYPE (snap_id, dbid, instance_number, filetype_id, small_read_megabytes, small_write_megabytes, large_read_megabytes, large_write_megabytes, small_read_reqs, small_write_reqs, small_sync_read_reqs, large_read_reqs, large_write_reqs, small_read_servicetime, small_write_servicetime, small_sync_read_latency, large_read_servicetime, large_write_servicetime, retries_on_error) (select :snap_id, :dbid, :instance_number, filetype_id, sum(small_read_megabytes) small_read_megabytes, sum(small_write_megabytes) small_write_megabytes, sum(large_read_megabytes) large_read_megabytes, sum(large_write_megabytes) large_write_megabytes, sum(small_read_reqs) small_read_reqs, sum(small_write_reqs) small_write_reqs, sum(small_sync_read_reqs) small_sync_read_reqs, sum(large_read_reqs) large_read_reqs, sum(large_write_reqs) large_write_reqs, sum(small_read_servicetime) small_read_servicetime, sum(small_write_servicetime) small_write_servicetime, sum(small_sync_read_latency) small_sync_read_latency, sum(large_read_servicetime) large_read_servicetime, sum(large_write_servicetime) large_write_servicetime, sum(retries_on_error) retries_on_error from v$iostat_file group by filetype_id)
1gu8t96d0bdmu	select t.ts#, t.file#, t.block#, nvl(t.bobj#, 0), nvl(t.tab#, 0), t.intcols, nvl(t.clucols, 0), t.audit$, t.flags, t.pctfree$, t.pctused$, t.initrans, t.maxtrans, t.rowcnt, t.blkcnt, t.empcnt, t.avgspc, t.chncnt, t.avgrln, t.analyzetime, t.samplesize, t.cols, t.property, nvl(t.degree, 1), nvl(t.instances, 1), t.avgspc_flb, t.flbcnt, t.kernelcols, nvl(t.trigflag, 0), nvl(t.spare1, 0), nvl(t.spare2, 0), t.spare4, t.spare6, ts.cachedblk, ts.cachehit, ts.logicalread from tab$ t, tab_stats$ ts where t.obj#= :1 and t.obj# = ts.obj# (+)
1mjhyad05m8pf	SELECT value FROM gv$osstat WHERE inst_id = :p1 and stat_name='NUM_CPU_CORES'
1uk5m5qbzj1vt	BEGIN dbms_workload_repository.create_snapshot; END;
2421xs4q7gjzh	UPDATE MGMT_NOTIFY_DEVICES SET STATUS = :B3 , CONTACT_TIMESTAMP = SYSDATE WHERE STATUS = :B2 AND CONTACT_TIMESTAMP < SYSDATE - ( :B1 /86400 )
2b064ybzkwf1y	BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END;
2n27cdhj8s6fb	SELECT JOB_ID, EXECUTION_ID, STEP_ID, STEP_NAME, STEP_TYPE, COMMAND_TYPE, TIMEZONE_REGION FROM MGMT_JOB_EXECUTION J WHERE STEP_TYPE IN (:B5 , :B4 ) AND COMMAND_TYPE = :B3 AND START_TIME <= :B2 AND ROWNUM <= :B1
350myuyx0t1d6	insert into wrh$_tablespace_stat (snap_id, dbid, instance_number, ts#, tsname, contents, status, segment_space_management, extent_management, is_backup) select :snap_id, :dbid, :instance_number, ts.ts#, ts.name as tsname, decode(ts.contents$, 0, (decode(bitand(ts.flags, 16), 16, 'UNDO', 'PERMANENT')), 1, 'TEMPORARY') as contents, decode(ts.online$, 1, 'ONLINE', 2, 'OFFLINE', 4, 'READ ONLY', 'UNDEFINED') as status, decode(bitand(ts.flags, 32), 32, 'AUTO', 'MANUAL') as segspace_mgmt, decode(ts.bitmapped, 0, 'DICTIONARY', 'LOCAL') as extent_management, (case when b.active_count > 0 then 'TRUE' else 'FALSE' end) as is_backup from sys.ts$ ts, (select dfile.ts#, sum( case when bkup.status = 'ACTIVE' then 1 else 0 end ) as active_count from v$backup bkup, file$ dfile where bkup.file# = dfile.file# and dfile.status$ = 2 group by dfile.ts#) b where ts.online$ != 3 and bitand(ts.flags, 2048) != 2048 and ts.ts# = b.ts#
54patfpds4pp3	select /* two */ /*+ parallel (t1, 16) parallel (t2, 16) */ min(t1.BSNS_UNIT_KEY + t2.BSNS_UNIT_KEY ) , max(t1.DAY_KEY + t2.DAY_KEY), avg(t1.DAY_KEY + t2.DAY_KEY), max(t1.BSNS_UNIT_TYP_CD ), max(t2.CURR_IND) , max(t1.LOAD_DT) from retail.DWB_RTL_TRX t1 , retail.DWB_RTL_TRX t2 where t1.TRX_NBR = t2.TRX_NBR
5ax5xu96u2ztd	insert into WRH$_EVENT_HISTOGRAM (snap_id, dbid, instance_number, event_id, wait_time_milli, wait_count) select :snap_id, :dbid, :instance_number, d.ksledhash, s.kslsesmaxdur, s.kslsesval from x$kslseshist s, x$ksled d where s.kslsesenum = d.indx and s.kslsesval > 0 order by d.ksledhash, s.kslsesmaxdur
5h7w8ykwtb2xt	INSERT INTO SYS.WRI$_ADV_PARAMETERS (TASK_ID, NAME, DATATYPE, VALUE, FLAGS, DESCRIPTION) VALUES (:B6 , :B5 , :B4 , :B3 , :B2 , :B1 )
5yv0nj5hwfz5d	select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= next_date) and (next_date <= :2)) or ((last_date is null) and (next_date < :3))) and (field1 = :4 or (field1 = 0 and 'Y' = :5)) and (this_date is null) order by next_date, job
61tn3mam0vq0b	SELECT source, (case when time_secs < 1 then 1 else time_secs end) as time_secs, operation FROM ( SELECT 1 as source, trunc((sysdate - cast(ll.log_date as date)) * 86400) as time_secs, decode(ll.operation, 'OPEN', 0 , 1 ) as operation, ll.log_id as log_id FROM DBA_SCHEDULER_WINDOW_LOG ll , ( SELECT max(l.log_id) as max_log_id FROM DBA_SCHEDULER_WINDOW_LOG l , DBA_SCHEDULER_WINGROUP_MEMBERS m WHERE l.window_name = m.window_name AND m.window_group_name = 'MAINTENANCE_WINDOW_GROUP' AND l.operation in ('OPEN', 'CLOSE') AND CAST(l.log_date AS DATE) < ( SELECT cast(min(end_interval_time) as date) as btime FROM WRM$_SNAPSHOT bsi WHERE bsi.dbid = :dbid AND bsi.snap_id = :bid ) ) max_log WHERE ll.log_id = max_log.max_log_id UNION ALL SELECT 2 as source, trunc((sysdate - cast(l.log_date as date)) * 86400) as time_secs, decode(l.operation, 'OPEN', 0 , 1 ) as operation, l.log_id as log_id FROM DBA_SCHEDULER_WINDOW_LOG l , DBA_SCHEDULER_WINGROUP_MEMB ERS m WHERE l.window_name = m.window_name AND m.window_group_name = 'MAINTENANCE_WINDOW_GROUP' AND l.operation in ('OPEN', 'CLOSE') AND CAST(l.log_date AS DATE) <= ( SELECT cast(max(end_interval_time) as date) as etime FROM WRM$_SNAPSHOT esi WHERE esi.dbid = :dbid AND esi.snap_id = :eid ) AND CAST(l.log_date AS DATE) >= ( SELECT cast(min(end_interval_time) as date) as btime FROM WRM$_SNAPSHOT bsi WHERE bsi.dbid = :dbid AND bsi.snap_id = :bid ) UNION ALL SELECT 0 as source, trunc((sysdate - et.etime) * 86400) as time_secs, 3 as operation, 0 as log_id FROM ( SELECT cast(max(end_interval_time) as date) as etime FROM WRM$_SNAPSHOT esi WHERE esi.dbid = :dbid AND esi.snap_id = :eid ) et UNION ALL SELECT 0 as source, trunc((sysdate - bt.btime) * 86400) as time_secs, 2 as operation, 0 as log_id FROM ( SELECT cast(min(end_interval_time) as date) as btime FROM WRM$_SNAPSHOT bsi WHERE bsi.dbid = :dbid AND bsi.snap_id = :bid ) bt ) WHERE time_secs >= 0 ORDER BY source ASC, time_secs DESC, log_id ASC
6ajkhukk78nsr	begin prvt_hdm.auto_execute( :dbid, :inst_num , :end_snap_id ); end;
6v7n0y2bq89n8	BEGIN EMDW_LOG.set_context(MGMT_JOB_ENGINE.MODULE_NAME, :1); MGMT_JOB_ENGINE.get_scheduled_steps(:2, :3, :4, :5); EMDW_LOG.set_context; END;
7frqszzdu8sgg	select DECODE(UPPER(d.OPEN_MODE), 'READ WRITE', 'R', 'N') , DECODE(UPPER(i.STATUS), 'OPEN', 'O', 'N') into :b0, :b1 from v$database d , gv$instance i where i.INSTANCE_NAME=:b2
7qjhf5dzmazsr	SELECT snap_id , OBJ#, DATAOBJ# FROM (SELECT /*+ ordered use_nl(t2) index(t2) */ t2.snap_id , t1.OBJN_KEWRSEG OBJ#, t1.OBJD_KEWRSEG DATAOBJ# FROM X$KEWRTSEGSTAT t1, WRH$_SEG_STAT_OBJ t2 WHERE t2.dbid(+) = :dbid AND t2.OBJ#(+) = t1.OBJN_KEWRSEG AND t2.DATAOBJ#(+) = t1.OBJD_KEWRSEG) WHERE nvl(snap_id, 0) < :snap_id
7vgmvmy8vvb9s	insert into wrh$_tempstatxs (snap_id, dbid, instance_number, file#, creation_change#, phyrds, phywrts, singleblkrds, readtim, writetim, singleblkrdtim, phyblkrd, phyblkwrt, wait_count, time) select :snap_id, :dbid, :instance_number, tf.tfnum, to_number(tf.tfcrc_scn) creation_change#, ts.kcftiopyr, ts.kcftiopyw, ts.kcftiosbr, ts.kcftioprt, ts.kcftiopwt, ts.kcftiosbt, ts.kcftiopbr, ts.kcftiopbw, fw.count, fw.time from x$kcftio ts, x$kcctf tf, x$kcbfwait fw where tf.tfdup != 0 and tf.tfnum = ts.kcftiofno and fw.indx+1 = (ts.kcftiofno + :db_files)
8j37z6cqf3s9h	SELECT value FROM gv$osstat WHERE inst_id = :p1 and stat_name='NUM_CPUS'
8t43xdhf4d9x2	SELECT CONTEXT_TYPE_ID, CONTEXT_TYPE, TRACE_LEVEL, NULL, NULL FROM EMDW_TRACE_CONFIG WHERE CONTEXT_TYPE = UPPER(:B1 )
8x0funf899dka	INSERT INTO wri$_adv_addm_inst ( TASK_ID, INSTANCE_NUMBER, INSTANCE_NAME, HOST_NAME, STATUS, DATABASE_TIME, ACTIVE_SESSIONS, PERC_ACTIVE_SESS, METER_LEVEL, LOCAL_TASK_ID) VALUES ( :tid, :inst_num, :inst_name, :host_name, :st, NULL, NULL, NULL, NULL, NULL)
9550qrptt8fu2	WITH MONITOR_DATA AS (SELECT INST_ID, KEY, STATUS, FIRST_REFRESH_TIME, LAST_REFRESH_TIME, REFRESH_COUNT, PROCESS_NAME, SID, SQL_ID, SQL_EXEC_START, SQL_EXEC_ID, SQL_PLAN_HASH_VALUE, SQL_CHILD_ADDRESS, SESSION_SERIAL#, PX_SERVER#, PX_SERVER_GROUP, PX_SERVER_SET, PX_QCINST_ID, PX_QCSID, MAX(ELAPSED_TIME) OVER() MAX_ELAPSED_TIME, MAX(NVL(DIRECT_WRITES, 0) + NVL(DISK_READS, 0)) OVER() MAX_IO_COUNT, MAX(NVL(BUFFER_GETS, 0)) OVER() MAX_BUFFER_GETS, CASE WHEN ELAPSED_TIME < (CPU_TIME+ APPLICATION_WAIT_TIME+ CONCURRENCY_WAIT_TIME+ CLUSTER_WAIT_TIME+ USER_IO_WAIT_TIME) THEN (CPU_TIME+ APPLICATION_WAIT_TIME+ CONCURRENCY_WAIT_TIME+ CLUSTER_WAIT_TIME+ USER_IO_WAIT_TIME) ELSE ELAPSED_TIME END ELAPSED_TIME, CPU_TIME, APPLICATION_WAIT_TIME, CONCURRENCY_WAIT_TIME, CLUSTER_WAIT_TIME, USER_IO_WAIT_TIME, CASE WHEN ELAPSED_TIME < (CPU_TIME+ APPLICATION_WAIT_TIME+ CONCURRENCY_WAIT_TIME+ CLUSTER_WAIT_TIME+ USER_IO_WAIT_TIME) THEN 0 ELSE ELAPSED_TIME - (CPU_TIME+ APPLICATION_WAIT_TIME+ CONCURRENCY_WAIT_TIME+ CLUSTER_WAIT_TIME+ USER_IO_WAIT_TIME) END OTHER_WAIT_TIME, PLSQL_EXEC_TIME, JAVA_EXEC_TIME, FETCHES, BUFFER_GETS, DISK_READS, DIRECT_WRITES, NVL(DISK_READS, 0)+NVL(DIRECT_WRITES, 0) IO_COUNT FROM GV$SQL_MONITOR MO1 WHERE MO1.INST_ID = NVL(:B8 , MO1.INST_ID) AND MO1.SQL_ID = :B7 AND MO1.SQL_EXEC_START = :B6 AND MO1.SQL_EXEC_ID = :B5 AND ((:B4 = 1 AND MO1.PX_QCSID IS NULL) OR (MO1.PX_SERVER_GROUP = NVL(:B3 , MO1.PX_SERVER_GROUP) AND MO1.PX_SERVER_SE T = NVL(:B2 , MO1.PX_SERVER_SET) AND MO1.PX_SERVER# = NVL(:B1 , MO1.PX_SERVER#)))), ASH_DATA AS (SELECT ASH2.BUCKET_NUM, ASH2.PLAN_LINE_ID, ASH2.ACTIVITY_TYPE, ASH2.EVENT_NAME, ASH2.INST_ID, ASH2.SESSION_ID, :B13 + ((:B13 - :B12 )/ :B11 ) * (ASH2.BUCKET_NUM-1) BUCKET_ACTIVITY_START, :B13 + (((:B12 - :B13 )/ :B11 ) * (ASH2.BUCKET_NUM)) - NUMTODSINTERVAL(1, 'SECOND') BUCKET_ACTIVITY_END, ASH2.ACTIVITY_START, ASH2.ACTIVITY_END, ASH2.ACTIVITY_COUNT, ASH2.WAIT_COUNT, ASH2.PX_SERVER_SET FROM (SELECT ASH1.BUCKET_NUM, ASH1.PLAN_LINE_ID, ASH1.ACTIVITY_TYPE, ASH1.EVENT_NAME, ASH1.INST_ID, ASH1.SESSION_ID, MIN(ASH1.PX_SERVER_SET) PX_SERVER_SET, MIN(ASH1.SAMPLE_TIME) ACTIVITY_START, MAX(ASH1.SAMPLE_TIME) ACTIVITY_END, COUNT(*) ACTIVITY_COUNT, COUNT(ASH1.WAIT_CLASS) WAIT_COUNT FROM ( SELECT /*+ leading(mo) use_hash(ash0) */ CAST(ASH0.SAMPLE_TIME AS DATE) SAMPLE_TIME, CASE WHEN :B11 > 1 THEN WIDTH_BUCKET(CAST(ASH0.SAMPLE_TIME AS DATE), :B13 , :B12 + NUMTODSINTERVAL(1, 'SECOND'), :B11 ) ELSE 1 END BUCKET_NUM, ASH0.WAIT_CLASS, NVL(ASH0.WAIT_CLASS, 'Cpu') ACTIVITY_TYPE, DECODE(:B10 , 1, ASH0.EVENT, NULL) EVENT_NAME, ASH0.INST_ID, ASH0.SESSION_ID, ASH0.SQL_PLAN_LINE_ID PLAN_LINE_ID, MO.PX_SERVER_SET FROM MONITOR_DATA MO, (SELECT SQL_ID, SQL_PLAN_LINE_ID, EVENT, WAIT_CLASS, SQL_EXEC_ID, INST_ID, SESSION_ID, FROM_TZ(SAMPLE_TIME, DBTIMEZONE) SAMPLE_TIME FROM GV$ACTIVE_SESSION_HISTORY) ASH0 WHERE ASH0.SQL_ID = :B7 AND (ASH0.WAIT_CLASS IS NULL O R ASH0.WAIT_CLASS != 'Queueing') AND ASH0.SAMPLE_TIME >= :B6 - NUMTODSINTERVAL(1, 'SECOND') AND ASH0.SAMPLE_TIME <= :B9 AND ASH0.SQL_EXEC_ID = :B5 AND ASH0.INST_ID = MO.INST_ID AND ASH0.SESSION_ID = MO.SID) ASH1 WHERE ASH1.BUCKET_NUM > 0 AND ASH1.BUCKET_NUM <= :B11 GROUP BY ASH1.BUCKET_NUM, ASH1.INST_ID, ASH1.SESSION_ID, ASH1.PLAN_LINE_ID, ASH1.ACTIVITY_TYPE, ASH1.EVENT_NAME) ASH2) SELECT /*+ no_monitor */ XMLELEMENT( "sql_monitor_report", NULL, XMLELEMENT( "report_parameters", NULL, XMLFOREST( :B30 AS "sql_id", :B29 AS "session_id", :B28 AS "session_serial", TO_CHAR(:B27 , :B17 ) AS "sql_exec_start", :B26 AS "sql_exec_id", :B11 AS "bucket_count", TO_CHAR(:B13 , :B17 ) AS "interval_start", TO_CHAR(:B12 , :B17 ) AS "interval_end", :B25 AS "auto_refresh"), CASE WHEN :B15 IS NOT NULL AND :B24 IS NOT NULL AND NOT (:B4 = 1 AND :B3 IS NULL AND :B2 IS NULL AND :B1 IS NULL) THEN XMLELEMENT( "parallel_filter", NULL, XMLFOREST( DECODE(:B4 , 1, 'yes', 'no') AS "qc", :B3 AS "server_group", :B2 AS "server_set", :B1 AS "server_number")) ELSE NULL END), XMLELEMENT( "target", XMLATTRIBUTES(:B23 AS "instance_id", :B22 AS "session_id", :B21 AS "session_serial", :B7 AS "sql_id", TO_CHAR(:B6 , :B17 ) AS "sql_exec_start", :B5 AS "sql_exec_id", :B20 AS "sql_plan_hash"), XMLFOREST(XMLCDATA((SELECT SUBSTR(SQL_FULLTEXT, 1, 3900) FROM GV$SQL SQ WHERE SQ.INST_ID = :B23 AND :B31 = 1 AND SQ.SQL_ID = :B7 AND ROWNUM = 1)) AS "sql_fulltext"), XMLELEMENT ( "status", NULL, :B19 ), XMLELEMENT( "refresh_count", NULL, :B18 ), XMLELEMENT( "first_refresh_time", NULL, TO_CHAR(:B16 , :B17 )), XMLELEMENT( "last_refresh_time", NULL, TO_CHAR(:B9 , :B17 )), XMLELEMENT( "duration", NULL, ROUND((:B9 - LEAST(:B6 , :B16 )) * 3600*24 + 1))), (SELECT XMLELEMENT( "stats", XMLATTRIBUTES('monitor' AS "type"), DECODE(NVL(SUM(ELAPSED_TIME), 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('elapsed_time' AS "name"), SUM(ELAPSED_TIME))), DECODE(NVL(SUM(CPU_TIME), 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('cpu_time' AS "name"), SUM(CPU_TIME))), DECODE(NVL(SUM(USER_IO_WAIT_TIME), 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('user_io_wait_time' AS "name"), SUM(USER_IO_WAIT_TIME))), DECODE(NVL(SUM(APPLICATION_WAIT_TIME), 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('application_wait_time' AS "name"), SUM(APPLICATION_WAIT_TIME))), DECODE(NVL(SUM(CONCURRENCY_WAIT_TIME), 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('concurrency_wait_time' AS "name"), SUM(CONCURRENCY_WAIT_TIME))), DECODE(NVL(SUM(CLUSTER_WAIT_TIME), 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('cluster_wait_time' AS "name"), SUM(CLUSTER_WAIT_TIME))), DECODE(NVL(SUM(PLSQL_EXEC_TIME), 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('plsql_exec_time' AS "name"), SUM(PLSQL_EXEC_TIME))), DECODE(NVL(SUM(JAVA_EXEC_TIME), 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('java_exec_time' AS "name"), SUM(JAVA_EXEC_TIME)) ), DECODE(NVL(SUM(OTHER_WAIT_TIME), 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('other_wait_time' AS "name"), SUM(OTHER_WAIT_TIME))), DECODE(NVL(SUM(FETCHES), 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('user_fetch_count' AS "name"), SUM(FETCHES))), DECODE(NVL(SUM(BUFFER_GETS), 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('buffer_gets' AS "name"), SUM(BUFFER_GETS))), DECODE(NVL(SUM(DISK_READS), 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('disk_reads' AS "name"), SUM(DISK_READS))), DECODE(NVL(SUM(DIRECT_WRITES), 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('direct_writes' AS "name"), SUM(DIRECT_WRITES)))) FROM MONITOR_DATA), NVL2(1, (SELECT CASE WHEN AT.ACTIVITY_COUNT > 0 THEN XMLELEMENT( "activity_sampled", XMLATTRIBUTES( TO_CHAR(AT.ACTIVITY_START, :B17 ) AS "start_time", TO_CHAR(AT.ACTIVITY_END, :B17 ) AS "end_time", ROUND((AT.ACTIVITY_END - AT.ACTIVITY_START) * 3600 *24)+1 AS "duration", AT.ACTIVITY_COUNT AS "activity_count", AT.WAIT_COUNT AS "wait_count", AT.ACTIVITY_COUNT - AT.WAIT_COUNT AS "cpu_count", AT.MOST_ACTIVE AS "most_active"), AT.ACTIVITY_TOTAL, DECODE(:B11 , 1, NULL, AH.ACTIVITY_HISTO)) ELSE NULL END FROM (SELECT MIN(AD1.ACTIVITY_START) ACTIVITY_START, MAX(AD1.ACTIVITY_END) ACTIVITY_END, SUM(AD1.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(AD1.WAIT_COUNT) WAIT_COUNT, SUBSTR(MAX(LPAD(AD1.ACTIVITY_COUNT, 10)|| AD1.ACTIVITY_TYPE), 11) MOST_ACTIVE, XMLAGG( XMLELEMENT( "activity", XMLATTRIBUTES( AD1.AC TIVITY_TYPE AS "class", AD1.EVENT_NAME AS "event"), AD1.ACTIVITY_COUNT) ORDER BY AD1.ACTIVITY_TYPE, AD1.EVENT_NAME) ACTIVITY_TOTAL FROM (SELECT AD0.ACTIVITY_TYPE, AD0.EVENT_NAME, MIN(AD0.ACTIVITY_START) ACTIVITY_START, MAX(AD0.ACTIVITY_END) ACTIVITY_END, SUM(AD0.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(AD0.WAIT_COUNT) WAIT_COUNT FROM ASH_DATA AD0 GROUP BY AD0.ACTIVITY_TYPE, AD0.EVENT_NAME) AD1) AT, (SELECT XMLELEMENT( "activity_histogram", XMLATTRIBUTES( MAX(AD2.BUCKET_NUM) AS "bucket_count"), XMLAGG( XMLELEMENT( "bucket", XMLATTRIBUTES( AD2.BUCKET_NUM AS "bucket_number", TO_CHAR(AD2.ACTIVITY_START, :B17 ) AS "start_time", TO_CHAR(AD2.ACTIVITY_END, :B17 ) AS "end_time", ROUND((AD2.ACTIVITY_END - AD2.ACTIVITY_START) * 3600 * 24)+1 AS "duration", AD2.ACTIVITY_COUNT AS "activity_count", AD2.ACTIVITY_COUNT - AD2.WAIT_COUNT AS "cpu_count", AD2.WAIT_COUNT AS "wait_count", AD2.MOST_ACTIVE AS "most_active"), ACTIVITY_BUCKET) ORDER BY AD2.BUCKET_NUM)) ACTIVITY_HISTO FROM (SELECT AD1.BUCKET_NUM, SUM(ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(WAIT_COUNT) WAIT_COUNT, MIN(AD1.ACTIVITY_START) ACTIVITY_START, MAX(AD1.ACTIVITY_END) ACTIVITY_END, SUBSTR(MAX(LPAD(AD1.ACTIVITY_COUNT, 10)|| AD1.ACTIVITY_TYPE), 11) MOST_ACTIVE, XMLAGG( XMLELEMENT( "activity", XMLATTRIBUTES( AD1.ACTIVITY_TYPE AS "class", AD1.EVENT_NAME AS "event"), AD1.ACTIVITY_COUNT) ORDER BY AD1.ACTIVITY_TYPE, AD1.EVENT_NAME) ACTIVITY_BUCKET FROM (SELECT AD0.BUCKET_NUM, AD0.ACTIVITY_TYPE, AD0.EVENT_NAME, MIN(AD0.ACTIVITY_START) ACTIVITY_START, MAX(AD0.ACTIVITY_END) ACTIVITY_END, SUM(AD0.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(AD0.WAIT_COUNT) WAIT_COUNT FROM ASH_DATA AD0 WHERE :B11 > 1 GROUP BY AD0.BUCKET_NUM, AD0.ACTIVITY_TYPE, AD0.EVENT_NAME) AD1 GROUP BY AD1.BUCKET_NUM) AD2) AH), NULL), CASE WHEN :B15 IS NOT NULL THEN (SELECT XMLELEMENT( "parallel_info", XMLATTRIBUTES( :B15 AS "qc_instance_id", :B39 AS "qc_session_id", :B38 AS "dop", :B35 AS "inst_count", :B37 AS "server_group_count", :B36 AS "server_set_count"), DECODE(:B34 , 1, PX_SESSIONS, NULL), DECODE(:B33 , 1, PX_SESSIONS, NULL), DECODE(:B32 , 1, PX_INSTANCES, NULL)) FROM (SELECT (SELECT XMLELEMENT( "sessions", XMLATTRIBUTES(MAX(PX_SESSION.ACTIVITY_COUNT) AS "max_activity_count", MAX(PX_SESSION.ACTIVITY_COUNT - PX_SESSION.WAIT_COUNT) AS "max_cpu_count", MAX(PX_SESSION.WAIT_COUNT) AS "max_wait_count", MAX(PX_SESSION.MAX_IO_COUNT) AS "max_io_count", MAX(PX_SESSION.MAX_BUFFER_GETS) AS "max_buffer_gets", MAX(PX_SESSION.MAX_ELAPSED_TIME) AS "max_elapsed_time"), XMLAGG(PX_SESSION.PX_SESSION_XML ORDER BY PX_SERVER_GROUP NULLS FIRST, PX_SERVER_SET, PX_SERVER#)) FROM (SELECT PX_SERVER_GROUP, PX_SERVER_SET, PX_SERVER#, MAX(PI.MAX_ELAPSED_TIME) MAX_ELAPSED_TIME, MAX(PI.MAX_IO_COUNT) MAX_IO_COUNT, MAX(PI.MAX_BUFFER_GETS) MAX_BUFFER_GETS, SUM(PI.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(PI.WAIT_COUNT) WAIT_COUNT, XMLELEMENT( "session", XMLATTRIBUTES( INST_ID AS "inst_id", PROCESS_NAME AS "proc ess_name", SID AS "session_id", SESSION_SERIAL# AS "session_serial", PX_SERVER_GROUP AS "server_group", PX_SERVER_SET AS "server_set", PX_SERVER# AS "server_num"), XMLELEMENT( "stats", XMLATTRIBUTES( 'monitor' AS "type"), NVL2(MAX(ELAPSED_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('elapsed_time' AS "name"), MAX(ELAPSED_TIME)), NULL), NVL2(MAX(CPU_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('cpu_time' AS "name"), MAX(CPU_TIME)), NULL), NVL2(MAX(USER_IO_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('user_io_wait_time' AS "name"), MAX(USER_IO_WAIT_TIME)), NULL), NVL2(MAX(APPLICATION_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('application_wait_time' AS "name"), MAX(APPLICATION_WAIT_TIME)), NULL), NVL2(MAX(CONCURRENCY_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('concurrency_wait_time' AS "name"), MAX(CONCURRENCY_WAIT_TIME)), NULL), NVL2(MAX(CLUSTER_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('cluster_wait_time' AS "name"), MAX(CLUSTER_WAIT_TIME)), NULL), NVL2(MAX(PLSQL_EXEC_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('plsql_exec_time' AS "name"), MAX(PLSQL_EXEC_TIME)), NULL), NVL2(MAX(JAVA_EXEC_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('java_exec_time' AS "name"), MAX(JAVA_EXEC_TIME)), NULL), NVL2(MAX(OTHER_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES( 'other_wait_time' AS "name"), MAX(OTHER_WAIT_TIME)), NULL), NVL2(MAX(FETCHES), XMLELEMENT( "stat", XMLATTRIBUTES('user_fetch_count' AS "name"), MAX(FETCHES)), NULL), NVL2(MAX( BUFFER_GETS), XMLELEMENT( "stat", XMLATTRIBUTES('buffer_gets' AS "name"), MAX(BUFFER_GETS)), NULL), NVL2(MAX(DISK_READS), XMLELEMENT( "stat", XMLATTRIBUTES('disk_reads' AS "name"), MAX(DISK_READS)), NULL), NVL2(MAX(DIRECT_WRITES), XMLELEMENT( "stat", XMLATTRIBUTES('direct_writes' AS "name"), MAX(DIRECT_WRITES)), NULL)), XMLELEMENT( "activity_sampled", XMLATTRIBUTES( TO_CHAR(MIN(PI.ACTIVITY_START), :B17 ) AS "start_time", TO_CHAR(MAX(PI.ACTIVITY_END), :B17 ) AS "end_time", ROUND((MAX(PI.ACTIVITY_END) - MIN(PI.ACTIVITY_START)) * 3600 *24)+1 AS "duration", SUM(PI.ACTIVITY_COUNT) AS "activity_count", SUM(PI.ACTIVITY_COUNT - PI.WAIT_COUNT) AS "cpu_count", SUM(PI.WAIT_COUNT) AS "wait_count"), XMLAGG( NVL2(ACTIVITY_TYPE, XMLELEMENT( "activity", XMLATTRIBUTES( PI.ACTIVITY_TYPE AS "class", PI.EVENT_NAME AS "event"), ACTIVITY_COUNT), NULL) ORDER BY PI.ACTIVITY_TYPE, PI.EVENT_NAME))) PX_SESSION_XML FROM (SELECT MO.INST_ID, DECODE(MO.PROCESS_NAME, 'ora', 'PX Coordinator', MO.PROCESS_NAME) PROCESS_NAME, MO.SID, MO.SESSION_SERIAL#, MO.PX_SERVER_GROUP, MO.PX_SERVER_SET, MO.PX_SERVER#, ASH0.ACTIVITY_TYPE, ASH0.EVENT_NAME, MAX(MO.IO_COUNT) MAX_IO_COUNT, MAX(MO.BUFFER_GETS) MAX_BUFFER_GETS, MAX(MO.MAX_ELAPSED_TIME) MAX_ELAPSED_TIME, SUM(DECODE(ASH0.ACTIVITY_TYPE, NULL, NULL, ASH0.ACTIVITY_COUNT)) ACTIVITY_COUNT, SUM(DECODE(ASH0.ACTIVITY_TYPE, NULL, NULL, ASH0.WAIT_COUNT)) WAIT_COUNT, MIN(ASH0.ACTIVITY_START) ACTIVITY_START, MAX(ASH0.ACTIVIT Y_END) ACTIVITY_END, MAX(DECODE(MO.ELAPSED_TIME, 0, NULL, MO.ELAPSED_TIME)) ELAPSED_TIME, MAX(DECODE(MO.CPU_TIME, 0, NULL, CPU_TIME)) CPU_TIME, MAX(DECODE(MO.FETCHES, 0, NULL, FETCHES)) FETCHES, MAX(DECODE(MO.BUFFER_GETS, 0, NULL, MO.BUFFER_GETS)) BUFFER_GETS, MAX(DECODE(MO.DISK_READS, 0, NULL, MO.DISK_READS)) DISK_READS, MAX(DECODE(MO.DIRECT_WRITES, 0, NULL, MO.DIRECT_WRITES)) DIRECT_WRITES, MAX(DECODE(MO.APPLICATION_WAIT_TIME, 0, NULL, MO.APPLICATION_WAIT_TIME)) APPLICATION_WAIT_TIME, MAX(DECODE(MO.CONCURRENCY_WAIT_TIME, 0, NULL, MO.CONCURRENCY_WAIT_TIME)) CONCURRENCY_WAIT_TIME, MAX(DECODE(MO.CLUSTER_WAIT_TIME, 0, NULL, MO.CLUSTER_WAIT_TIME)) CLUSTER_WAIT_TIME, MAX(DECODE(MO.USER_IO_WAIT_TIME, 0, NULL, MO.USER_IO_WAIT_TIME)) USER_IO_WAIT_TIME, MAX(DECODE(PLSQL_EXEC_TIME, 0, NULL, PLSQL_EXEC_TIME)) PLSQL_EXEC_TIME, MAX(DECODE(MO.JAVA_EXEC_TIME, 0, NULL, MO.JAVA_EXEC_TIME)) JAVA_EXEC_TIME, MAX(DECODE(MO.OTHER_WAIT_TIME, 0, NULL, MO.OTHER_WAIT_TIME)) OTHER_WAIT_TIME FROM MONITOR_DATA MO, ASH_DATA ASH0 WHERE MO.INST_ID = ASH0.INST_ID(+) AND MO.SID = ASH0.SESSION_ID(+) AND (:B34 = 1 OR :B33 = 1 OR :B32 = 1) GROUP BY MO.INST_ID, MO.PROCESS_NAME, MO.SID, MO.SESSION_SERIAL#, MO.PX_SERVER_GROUP, MO.PX_SERVER_SET, MO.PX_SERVER#, ASH0.ACTIVITY_TYPE, ASH0.EVENT_NAME) PI WHERE (:B34 = 1 OR :B33 = 1) GROUP BY PI.INST_ID, PI.SID, PI.PROCESS_NAME, PI.SESSION_SERIAL#, PI.PX_SERVER_GROUP, PI.PX_SERVER_SET, PI.PX_SERVER#) PX_S ESSION) PX_SESSIONS, (SELECT XMLELEMENT( "instances", XMLATTRIBUTES( MAX(PX_INSTANCE.ACTIVITY_COUNT) AS "max_activity_count", MAX(PX_INSTANCE.WAIT_COUNT - PX_INSTANCE.ACTIVITY_COUNT) AS "max_cpu_count", MAX(PX_INSTANCE.WAIT_COUNT) AS "max_wait_count", MAX(PX_INSTANCE.ELAPSED_TIME) AS "max_elapsed_time", MAX(PX_INSTANCE.BUFFER_GETS) AS "max_buffer_gets", MAX(PX_INSTANCE.IO_COUNT) AS "max_io_count"), XMLAGG(PX_INSTANCE.PX_INSTANCES_XML ORDER BY INST_ID)) FROM (SELECT PI.INST_ID, MAX(PI.ELAPSED_TIME) ELAPSED_TIME, MAX(PI.IO_COUNT) IO_COUNT, MAX(PI.BUFFER_GETS) BUFFER_GETS, SUM(PI.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(PI.WAIT_COUNT) WAIT_COUNT, XMLELEMENT( "instance", XMLATTRIBUTES( INST_ID AS "inst_id"), XMLELEMENT( "stats", XMLATTRIBUTES( 'monitor' AS "type"), NVL2(MAX(ELAPSED_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('elapsed_time' AS "name"), MAX(ELAPSED_TIME)), NULL), NVL2(MAX(CPU_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('cpu_time' AS "name"), MAX(CPU_TIME)), NULL), NVL2(MAX(USER_IO_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('user_io_wait_time' AS "name"), MAX(USER_IO_WAIT_TIME)), NULL), NVL2(MAX(APPLICATION_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('application_wait_time' AS "name"), MAX(APPLICATION_WAIT_TIME)), NULL), NVL2(MAX(CONCURRENCY_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('concurrency_wait_time' AS "name"), MAX(CONCURRENCY_WAIT_TIME)), NULL), NVL2(MAX(CLUSTER_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('cluster _wait_time' AS "name"), MAX(CLUSTER_WAIT_TIME)), NULL), NVL2(MAX(PLSQL_EXEC_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('plsql_exec_time' AS "name"), MAX(PLSQL_EXEC_TIME)), NULL), NVL2(MAX(JAVA_EXEC_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('java_exec_time' AS "name"), MAX(JAVA_EXEC_TIME)), NULL), NVL2(MAX(OTHER_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES( 'other_wait_time' AS "name"), MAX(OTHER_WAIT_TIME)), NULL), NVL2(MAX(FETCHES), XMLELEMENT( "stat", XMLATTRIBUTES('user_fetch_count' AS "name"), MAX(FETCHES)), NULL), NVL2(MAX(BUFFER_GETS), XMLELEMENT( "stat", XMLATTRIBUTES('buffer_gets' AS "name"), MAX(BUFFER_GETS)), NULL), NVL2(MAX(DISK_READS), XMLELEMENT( "stat", XMLATTRIBUTES('disk_reads' AS "name"), MAX(DISK_READS)), NULL), NVL2(MAX(DIRECT_WRITES), XMLELEMENT( "stat", XMLATTRIBUTES('direct_writes' AS "name"), MAX(DIRECT_WRITES)), NULL)), XMLELEMENT( "activity_sampled", XMLATTRIBUTES( TO_CHAR(MIN(PI.ACTIVITY_START), :B17 ) AS "start_time", TO_CHAR(MAX(PI.ACTIVITY_END), :B17 ) AS "end_time", ROUND((MAX(PI.ACTIVITY_END) - MIN(PI.ACTIVITY_START)) * 3600 *24)+1 AS "duration", SUM(PI.WAIT_COUNT) AS "wait_count", SUM(PI.ACTIVITY_COUNT - PI.WAIT_COUNT) AS "cpu_count", SUM(PI.ACTIVITY_COUNT) AS "activity_count"), XMLAGG( NVL2(ACTIVITY_TYPE, XMLELEMENT( "activity", XMLATTRIBUTES( PI.ACTIVITY_TYPE AS "class", PI.EVENT_NAME AS "event"), ACTIVITY_COUNT), NULL) ORDER BY PI.ACTIVITY_TYPE, PI.EVENT_NAME))) PX_INSTANCES_XML FROM (SELECT MO.INST_ID, ASH.ACTIVITY_TYPE, ASH.EVENT_NAME, ASH.ACTIVITY_COUNT, ASH.WAIT_COUNT, ASH.ACTIVITY_START, ASH.ACTIVITY_END, MO.ELAPSED_TIME, MO.CPU_TIME, MO.APPLICATION_WAIT_TIME, MO.CONCURRENCY_WAIT_TIME, MO.CLUSTER_WAIT_TIME, MO.USER_IO_WAIT_TIME, MO.PLSQL_EXEC_TIME, MO.JAVA_EXEC_TIME, MO.OTHER_WAIT_TIME, MO.FETCHES, MO.BUFFER_GETS, MO.DISK_READS, MO.DIRECT_WRITES, MO.IO_COUNT FROM (SELECT MO0.INST_ID, SUM(MO0.ELAPSED_TIME) ELAPSED_TIME, SUM(MO0.CPU_TIME) CPU_TIME, SUM(MO0.FETCHES) FETCHES, SUM(MO0.BUFFER_GETS) BUFFER_GETS, SUM(MO0.DISK_READS) DISK_READS, SUM(MO0.DIRECT_WRITES) DIRECT_WRITES, SUM(MO0.IO_COUNT) IO_COUNT, SUM(MO0.APPLICATION_WAIT_TIME) APPLICATION_WAIT_TIME, SUM(MO0.CONCURRENCY_WAIT_TIME) CONCURRENCY_WAIT_TIME, SUM(MO0.CLUSTER_WAIT_TIME) CLUSTER_WAIT_TIME, SUM(MO0.USER_IO_WAIT_TIME) USER_IO_WAIT_TIME, SUM(MO0.PLSQL_EXEC_TIME) PLSQL_EXEC_TIME, SUM(MO0.JAVA_EXEC_TIME) JAVA_EXEC_TIME, SUM(MO0.OTHER_WAIT_TIME) OTHER_WAIT_TIME FROM MONITOR_DATA MO0 GROUP BY MO0.INST_ID) MO, (SELECT ASH0.INST_ID, ASH0.ACTIVITY_TYPE, ASH0.EVENT_NAME, SUM(ASH0.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(ASH0.WAIT_COUNT) WAIT_COUNT, MIN(ASH0.ACTIVITY_START) ACTIVITY_START, MAX(ASH0.ACTIVITY_END) ACTIVITY_END FROM ASH_DATA ASH0 GROUP BY ASH0.INST_ID, ASH0.ACTIVITY_TYPE, ASH0.EVENT_NAME) ASH WHERE MO.INST_ID = ASH.INST_ID(+) AND :B32 = 1 AND :B35 > 1) PI GROUP BY PI.INST_ID)PX_INSTANCE) PX_INSTANCES FROM DUAL)) ELSE NULL END, CASE WHEN :B14 = 1 THEN (SELECT XMLELEMENT( "plan_monitor", XMLATTRIBUTES(MAX(MAX_LINE_ACTIVITY_COUNT) AS "max_activity_count", MAX(MAX_LINE_CPU_COUNT) AS "max_cpu_count", MAX(MAX_LINE_WAIT_COUNT) AS "max_wait_count"), XMLAGG( XMLELEMENT( "operation", XMLATTRIBUTES( PM.PLAN_LINE_ID AS "id", PL.PARENT_ID AS "parent_id", PM.OPERATION AS "name", PM.OPTIONS AS "options", PL.DEPTH AS "depth", PL.POSITION AS "position", CASE WHEN PM.PX_SERVER_SET IS NOT NULL THEN TO_CHAR(PM.PX_SERVER_SET) WHEN AH.PX_SERVER_SET IS NOT NULL THEN TO_CHAR(AH.PX_SERVER_SET) WHEN (:B15 IS NULL OR (PM.LAST_CHANGE_TIME IS NULL AND AT.LINE_ACTIVITY_END IS NULL)) THEN NULL ELSE 'QC' END AS "px_type"), NVL2(PL.OBJECT_NAME, XMLELEMENT( "object", XMLATTRIBUTES(PL.OBJECT_TYPE AS "type"), XMLFOREST(XMLCDATA(PL.OBJECT_OWNER) AS "owner"), XMLFOREST(XMLCDATA(PL.OBJECT_NAME) AS "name"), XMLFOREST(XMLCDATA(PL.OBJECT_ALIAS) AS "alias")), NULL), XMLFOREST(PL.PARTITION_START AS "partition_start", PL.PARTITION_STOP AS "partition_stop"), CASE WHEN PL.CARD IS NULL AND PL.BYTES IS NULL AND PL.COST IS NULL AND PL.TEMP_SPACE IS NULL AND PL.TIME IS NULL THEN NULL ELSE XMLELEMENT( "optimizer", NULL, NVL2(PL.CARD, XMLFOREST(PL.CARD AS "cardinality"), NULL), NVL2(PL.BYTES, XMLFOREST(PL.BYTES AS "bytes"), NULL), NVL2(PL.COST, XMLFOREST(PL.COST AS "cost"), NULL), NVL2(PL.CPU_COST, XMLFOREST(PL.CPU_COST AS "cpu_cost"), NULL), NVL2(PL.IO_COST, XMLFOREST(PL.IO_COST AS "io_cost"), NULL), NVL2(PL.TEMP_SPACE, XMLFOREST(PL.T EMP_SPACE AS "temp"), NULL), NVL2(PL.TIME, XMLFOREST(PL.TIME AS "time"), NULL)) END, XMLELEMENT( "stats", XMLATTRIBUTES('plan_monitor' AS "type"), CASE WHEN PM.FIRST_CHANGE_TIME IS NULL AND AT.LINE_ACTIVITY_START IS NULL THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('first_active' AS "name"), TO_CHAR( LEAST(NVL(AT.LINE_ACTIVITY_START, PM.FIRST_CHANGE_TIME), NVL(PM.FIRST_CHANGE_TIME, AT.LINE_ACTIVITY_START)), :B17 )) END, CASE WHEN PM.LAST_CHANGE_TIME IS NULL AND AT.LINE_ACTIVITY_END IS NULL THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('last_active' AS "name"), TO_CHAR( GREATEST(NVL(AT.LINE_ACTIVITY_END, PM.LAST_CHANGE_TIME), NVL(PM.LAST_CHANGE_TIME, AT.LINE_ACTIVITY_END)), :B17 )) END, CASE WHEN (PM.LAST_CHANGE_TIME IS NULL AND AT.LINE_ACTIVITY_END IS NULL) OR (PM.LAST_CHANGE_TIME IS NULL AND AT.LINE_ACTIVITY_END IS NULL) THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('duration' AS "name"), ROUND( (GREATEST(NVL(AT.LINE_ACTIVITY_END, PM.LAST_CHANGE_TIME), NVL(PM.LAST_CHANGE_TIME, AT.LINE_ACTIVITY_END)) - LEAST(NVL(AT.LINE_ACTIVITY_START, PM.FIRST_CHANGE_TIME), NVL(PM.FIRST_CHANGE_TIME, AT.LINE_ACTIVITY_START))) * 3600 * 24)+1) END, CASE WHEN (PM.OVERALL_LAST_CHANGE_TIME IS NULL AND AT.OVERALL_LINE_ACTIVITY_END IS NULL) OR (PM.LAST_CHANGE_TIME IS NULL AND AT.LINE_ACTIVITY_END IS NULL) THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('from_most_recent' AS "name"), ROUND( (GREATEST(NVL(AT.OVERALL_LINE_ACTIVITY_END, PM.OVERALL_LAST_CHANGE_ TIME), NVL(PM.OVERALL_LAST_CHANGE_TIME, AT.OVERALL_LINE_ACTIVITY_END)) - GREATEST(NVL(AT.LINE_ACTIVITY_END, PM.LAST_CHANGE_TIME), NVL(PM.LAST_CHANGE_TIME, AT.LINE_ACTIVITY_END))) * 3600 * 24)) END, CASE WHEN (PM.LAST_CHANGE_TIME IS NULL AND AT.LINE_ACTIVITY_END IS NULL) THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES( 'from_sql_exec_start' AS "name"), ROUND( (LEAST(NVL(AT.LINE_ACTIVITY_START, PM.FIRST_CHANGE_TIME), NVL(PM.FIRST_CHANGE_TIME, AT.LINE_ACTIVITY_START)) - :B6 ) * 3600 * 24)) END, NVL2(LO.PERCENT_COMPLETE, XMLELEMENT( "stat", XMLATTRIBUTES('percent_complete' AS "name"), LO.PERCENT_COMPLETE), NULL), NVL2(LO.TIME_REMAINING, XMLELEMENT( "stat", XMLATTRIBUTES('time_left' AS "name"), LO.TIME_REMAINING), NULL), CASE WHEN PM.STARTS IS NOT NULL AND PM.STARTS > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES('starts' AS "name"), PM.STARTS) ELSE NULL END, CASE WHEN PM.STARTS IS NOT NULL AND PM.STARTS > 0 AND PM.OUTPUT_ROWS IS NOT NULL THEN XMLELEMENT( "stat", XMLATTRIBUTES('cardinality' AS "name"), CASE WHEN AT.LINE_ACTIVITY_START IS NULL AND PM.FIRST_CHANGE_TIME IS NULL THEN NULL ELSE PM.OUTPUT_ROWS END) ELSE NULL END, CASE WHEN PM.MEM > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES('memory' AS "name"), PM.MEM) ELSE NULL END, CASE WHEN PM.MAX_MEM > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES('max_memory' AS "name"), PM.MAX_MEM) ELSE NULL END, CASE WHEN PM.TEMP > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES('temp' AS "name"), PM.TEMP) ELSE NU LL END, CASE WHEN PM.MAX_TEMP > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES('max_temp' AS "name"), PM.MAX_TEMP) ELSE NULL END), NVL2(AT.ACTIVITY_TOTAL, XMLELEMENT( "activity_sampled", XMLATTRIBUTES( TO_CHAR(AT.LINE_ACTIVITY_START, :B17 ) AS "start_time", TO_CHAR(AT.LINE_ACTIVITY_END, :B17 ) AS "end_time", ROUND((AT.LINE_ACTIVITY_END - AT.LINE_ACTIVITY_START) * 3600 *24)+1 AS "duration", AT.LINE_WAIT_COUNT AS "wait_count", AT.LINE_ACTIVITY_COUNT - AT.LINE_WAIT_COUNT AS "cpu_count", AT.LINE_ACTIVITY_COUNT AS "activity_count", AT.MOST_ACTIVE AS "most_active"), AT.ACTIVITY_TOTAL, DECODE(:B11 , 1, NULL, AH.ACTIVITY_HISTO)), NULL)) ORDER BY PM.PLAN_LINE_ID)) FROM (SELECT AT0.*, MAX(LINE_ACTIVITY_END) OVER() OVERALL_LINE_ACTIVITY_END, MAX(LINE_ACTIVITY_COUNT) OVER() MAX_LINE_ACTIVITY_COUNT, MAX(LINE_CPU_COUNT) OVER() MAX_LINE_CPU_COUNT, MAX(LINE_WAIT_COUNT) OVER() MAX_LINE_WAIT_COUNT FROM (SELECT AD1.PLAN_LINE_ID, MIN(AD1.LINE_ACTIVITY_START) LINE_ACTIVITY_START, MAX(AD1.LINE_ACTIVITY_END) LINE_ACTIVITY_END, SUM(AD1.ACTIVITY_COUNT) LINE_ACTIVITY_COUNT, SUM(AD1.WAIT_COUNT) LINE_WAIT_COUNT, SUM(AD1.ACTIVITY_COUNT - AD1.WAIT_COUNT) LINE_CPU_COUNT, SUBSTR(MAX(LPAD(AD1.ACTIVITY_COUNT, 10)|| AD1.ACTIVITY_TYPE), 11) MOST_ACTIVE, XMLAGG(XMLELEMENT( "activity", XMLATTRIBUTES( AD1.ACTIVITY_TYPE AS "class", AD1.EVENT_NAME AS "event"), AD1.ACTIVITY_COUNT) ORDER BY AD1.ACTIVITY_TYPE, AD1.EVENT_NAME) ACTIVITY_TOTAL FROM (SELECT AD0.PLAN_LINE_ID, AD0.ACTIVITY_T YPE, AD0.EVENT_NAME, MIN(ACTIVITY_START) LINE_ACTIVITY_START, MAX(ACTIVITY_END) LINE_ACTIVITY_END, SUM(ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(WAIT_COUNT) WAIT_COUNT FROM ASH_DATA AD0 GROUP BY AD0.PLAN_LINE_ID, AD0.ACTIVITY_TYPE, AD0.EVENT_NAME) AD1 GROUP BY AD1.PLAN_LINE_ID) AT0) AT, (SELECT AD2.PLAN_LINE_ID, MIN(AD2.PX_SERVER_SET) PX_SERVER_SET, MIN(AD2.LINE_ACTIVITY_START) LINE_ACTIVITY_START, MAX(AD2.LINE_ACTIVITY_END) LINE_ACTIVITY_END, SUM(AD2.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(AD2.WAIT_COUNT) WAIT_COUNT, XMLELEMENT( "activity_histogram", NULL, XMLAGG(XMLELEMENT( "bucket", XMLATTRIBUTES( AD2.BUCKET_NUM AS "bucket_number", AD2.WAIT_COUNT AS "wait_count", AD2.ACTIVITY_COUNT - AD2.WAIT_COUNT AS "cpu_count", AD2.ACTIVITY_COUNT AS "activity_count", AD2.MOST_ACTIVE AS "most_active"), AD2.ACTIVITY_BUCKET) ORDER BY AD2.BUCKET_NUM)) ACTIVITY_HISTO FROM (SELECT AD1.PLAN_LINE_ID, AD1.BUCKET_NUM, MIN(AD1.PX_SERVER_SET) PX_SERVER_SET, MIN(AD1.LINE_ACTIVITY_START) LINE_ACTIVITY_START, MAX(AD1.LINE_ACTIVITY_END) LINE_ACTIVITY_END, MIN(AD1.BUCKET_ACTIVITY_START) BUCKET_ACTIVITY_START, MAX(AD1.BUCKET_ACTIVITY_END) BUCKET_ACTIVITY_END, SUM(AD1.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(AD1.WAIT_COUNT) WAIT_COUNT, SUBSTR(MAX(LPAD(AD1.ACTIVITY_COUNT, 10)|| AD1.ACTIVITY_TYPE), 11) MOST_ACTIVE, XMLAGG(NVL2(AD1.ACTIVITY_TYPE, XMLELEMENT( "activity", XMLATTRIBUTES( AD1.ACTIVITY_TYPE AS "class", AD1.EVENT_NAME AS "event"), AD1.ACTIVITY_COUNT), NULL) ORDER BY AD1.ACTI VITY_TYPE, AD1.EVENT_NAME) ACTIVITY_BUCKET FROM (SELECT AD0.PLAN_LINE_ID, AD0.BUCKET_NUM, AD0.ACTIVITY_TYPE, AD0.EVENT_NAME, MIN(NVL2(AD0.ACTIVITY_START, AD0.PX_SERVER_SET, NULL)) PX_SERVER_SET, MIN(AD0.ACTIVITY_START) LINE_ACTIVITY_START, MAX(AD0.ACTIVITY_END) LINE_ACTIVITY_END, MIN(AD0.BUCKET_ACTIVITY_START) BUCKET_ACTIVITY_START, MIN(AD0.BUCKET_ACTIVITY_END) BUCKET_ACTIVITY_END, SUM(AD0.WAIT_COUNT) WAIT_COUNT, SUM(AD0.ACTIVITY_COUNT) ACTIVITY_COUNT FROM ASH_DATA AD0 WHERE :B11 > 1 GROUP BY AD0.PLAN_LINE_ID, AD0.BUCKET_NUM, AD0.ACTIVITY_TYPE, AD0.EVENT_NAME) AD1 GROUP BY AD1.PLAN_LINE_ID, AD1.BUCKET_NUM) AD2 GROUP BY AD2.PLAN_LINE_ID) AH, (SELECT PL.ID PLAN_LINE_ID, PL.PARENT_ID, PL.OPERATION, PL.OPTIONS, PL.OBJECT_OWNER, PL.OBJECT_NAME, PL.OBJECT_ALIAS, PL.OBJECT_TYPE, PL.POSITION, PL.DEPTH, PL.COST, PL.CARDINALITY CARD, PL.BYTES, PL.PARTITION_START, PL.PARTITION_STOP, PL.CPU_COST, PL.IO_COST, PL.TEMP_SPACE, PL.TIME FROM V$SQL_PLAN PL WHERE PL.SQL_ID = :B7 AND PL.PLAN_HASH_VALUE = :B20 AND PL.CHILD_ADDRESS = (SELECT MAX(SQL.CHILD_ADDRESS) FROM V$SQL SQL WHERE SQL.SQL_ID = :B7 AND SQL.PLAN_HASH_VALUE = :B20 AND SQL.LOADED_VERSIONS > 0)) PL, (SELECT LO.SQL_PLAN_LINE_ID PLAN_LINE_ID, DECODE(SUM(LO.TOTALWORK), 0, NULL, ROUND(SUM(LO.SOFAR)*100/SUM(LO.TOTALWORK))) PERCENT_COMPLETE, MAX(LO.TIME_REMAINING) TIME_REMAINING FROM GV$SESSION_LONGOPS LO, MONITOR_DATA MO WHERE LO.SQL_ID = :B7 AND LO.SQL_EXEC_START = :B6 AND LO.SQL_EXEC_ID = :B5 AND LO.INST_ID = MO.INST_ID AND LO.SID = MO.SID GROUP BY LO.SQL_PLAN_LINE_ID) LO, (SELECT PM0.*, MAX(LAST_CHANGE_TIME) OVER() OVERALL_LAST_CHANGE_TIME FROM (SELECT /*+ leading(md) use_hash(plm) */ PLM.PLAN_LINE_ID PLAN_LINE_ID, PLM.PLAN_OPERATION OPERATION, PLM.PLAN_OPTIONS OPTIONS, MIN(PLM.FIRST_CHANGE_TIME) FIRST_CHANGE_TIME, MAX(PLM.LAST_CHANGE_TIME) LAST_CHANGE_TIME, MIN(NVL2(PLM.FIRST_CHANGE_TIME, MO.PX_SERVER_SET, NULL)) PX_SERVER_SET, SUM(PLM.STARTS) STARTS, SUM(PLM.OUTPUT_ROWS) OUTPUT_ROWS, SUM(PLM.WORKAREA_MEM) MEM, SUM(PLM.WORKAREA_MAX_MEM) MAX_MEM, SUM(PLM.WORKAREA_TEMPSEG) TEMP, SUM(PLM.WORKAREA_MAX_TEMPSEG) MAX_TEMP FROM GV$SQL_PLAN_MONITOR PLM, MONITOR_DATA MO WHERE PLM.SQL_ID = :B7 AND PLM.SQL_EXEC_START = :B6 AND PLM.SQL_EXEC_ID = :B5 AND PLM.INST_ID = MO.INST_ID AND PLM.INST_ID = NVL( :B8 , PLM.INST_ID) AND PLM.KEY = MO.KEY GROUP BY PLM.PLAN_LINE_ID, PLM.PLAN_OPERATION, PLM.PLAN_OPTIONS) PM0) PM WHERE AH.PLAN_LINE_ID(+) = PM.PLAN_LINE_ID AND AT.PLAN_LINE_ID(+) = PM.PLAN_LINE_ID AND PL.PLAN_LINE_ID(+) = PM.PLAN_LINE_ID AND LO.PLAN_LINE_ID(+) = PM.PLAN_LINE_ID) ELSE NULL END) FROM DUAL
9tgj4g8y4rwy8	select type#, blocks, extents, minexts, maxexts, extsize, extpct, user#, iniexts, NVL(lists, 65535), NVL(groups, 65535), cachehint, hwmincr, NVL(spare1, 0), NVL(scanhint, 0), NVL(bitmapranges, 0) from seg$ where ts#=:1 and file#=:2 and block#=:3
aqb79nn78dgyy	SELECT inst_id, service_name, TO_CHAR(CAST(begin_time AS TIMESTAMP) AT TIME ZONE 'GMT', 'YYYY-MM-DD HH24:MI:SS TZD') time, TO_CHAR(CAST(end_time AS TIMESTAMP) AT TIME ZONE 'GMT', 'YYYY-MM-DD HH24:MI:SS TZD') time, intsize_csec, group_id, cpupercall, dbtimepercall, callspersec, dbtimepersec FROM gv$servicemetric_history WHERE service_name = :p1 AND end_time >= SYSDATE - 5/(60*24) AND group_id = 6 ORDER BY inst_id asc, end_time DESC
arx2rgss6k33m	SELECT s.inst_id, s.name, i.instance_name FROM gv$active_services s, gv$instance i WHERE s.name = :p1 AND s.inst_id = i.inst_id
bjsc9c4gg59jj	BEGIN EMDW_LOG.set_context(MGMT_JOB_ENGINE.MODULE_NAME, :1); BEGIN MGMT_JOB_ENGINE.update_step_status(:2, :3, :4, :5); END; EMDW_LOG.set_context; END;
bunssq950snhf	insert into wrh$_sga_target_advice (snap_id, dbid, instance_number, SGA_SIZE, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_READS) select :snap_id, :dbid, :instance_number, SGA_SIZE, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_READS from v$sga_target_advice
cydnuss99swtd	BEGIN EM_PING.RECORD_BATCH_HEARTBEAT(:1, :2, :3); END;
f318xdxdn0pdc	insert into wrh$_log (snap_id, dbid, instance_number, group#, thread#, sequence#, bytes, members, archived, status, first_change#, first_time) select :snap_id, :dbid, :instance_number, group#, thread#, sequence#, bytes, members, archived, status, first_change#, first_time from v$log
fgqt3gak6vv8x	SELECT instance_name FROM gv$instance WHERE inst_id = :p1
g4vrdfn14tp29	SELECT PROPERTY FROM SYS.WRI$_ADV_TASKS WHERE ID = :B1

Back to SQL Statistics
Back to Top

Instance Activity Statistics

• Instance Activity Stats
• Instance Activity Stats - Absolute Values
• Instance Activity Stats - Thread Activity

Instance Activity Stats

Back to Instance Activity Statistics
Back to Top

Instance Activity Stats - Absolute Values

• Statistics with absolute values (should not be diffed)

Back to Instance Activity Statistics
Back to Top

Instance Activity Stats - Thread Activity

• Statistics identified by '(derived)' come from sources other than SYSSTAT

Back to Instance Activity Statistics
Back to Top

IO Stats

• Tablespace IO Stats
• File IO Stats

Tablespace IO Stats

• ordered by IOs (Reads + Writes) desc

Back to IO Stats
Back to Top

File IO Stats

• ordered by Tablespace, File

Back to IO Stats
Back to Top

Buffer Pool Statistics

• Standard block size Pools D: default, K: keep, R: recycle
• Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k

Back to Top

Advisory Statistics

• Instance Recovery Stats
• Buffer Pool Advisory
• PGA Aggr Summary
• PGA Aggr Target Stats
• PGA Aggr Target Histogram
• PGA Memory Advisory
• Shared Pool Advisory
• SGA Target Advisory
• Streams Pool Advisory
• Java Pool Advisory

Instance Recovery Stats

• B: Begin snapshot, E: End snapshot

Back to Advisory Statistics
Back to Top

Buffer Pool Advisory

• Only rows with estimated physical reads >0 are displayed
• ordered by Block Size, Buffers For Estimate

Back to Advisory Statistics
Back to Top

PGA Aggr Summary

• PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory

Back to Advisory Statistics
Back to Top

PGA Aggr Target Stats

• B: Begin snap E: End snap (rows dentified with B or E contain data which is absolute i.e. not diffed over the interval)
• Auto PGA Target - actual workarea memory target
• W/A PGA Used - amount of memory used for all Workareas (manual + auto)
• %PGA W/A Mem - percentage of PGA memory allocated to workareas
• %Auto W/A Mem - percentage of workarea memory controlled by Auto Mem Mgmt
• %Man W/A Mem - percentage of workarea memory under manual control

Back to Advisory Statistics
Back to Top

PGA Aggr Target Histogram

• Optimal Executions are purely in-memory operations

Back to Advisory Statistics
Back to Top

PGA Memory Advisory

• When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value where Estd PGA Overalloc Count is 0

Back to Advisory Statistics
Back to Top

Shared Pool Advisory

• SP: Shared Pool Est LC: Estimated Library Cache Factr: Factor
• Note there is often a 1:Many correlation between a single logical object in the Library Cache, and the physical number of memory objects associated with it. Therefore comparing the number of Lib Cache objects (e.g. in v$librarycache), with the number of Lib Cache Memory Objects is invalid.

Back to Advisory Statistics
Back to Top

SGA Target Advisory

Back to Advisory Statistics
Back to Top

Streams Pool Advisory

No data exists for this section of the report.

Back to Advisory Statistics
Back to Top

Java Pool Advisory

No data exists for this section of the report.

Back to Advisory Statistics
Back to Top

Wait Statistics

• Buffer Wait Statistics
• Enqueue Activity

Buffer Wait Statistics

• ordered by wait time desc, waits desc

Back to Wait Statistics
Back to Top

Enqueue Activity

• only enqueues with waits are shown
• Enqueue stats gathered prior to 10g should not be compared with 10g data
• ordered by Wait Time desc, Waits desc

Back to Wait Statistics
Back to Top

Undo Statistics

• Undo Segment Summary
• Undo Segment Stats

Undo Segment Summary

No data exists for this section of the report.

Back to Undo Statistics
Back to Top

Undo Segment Stats

No data exists for this section of the report.

Back to Undo Statistics
Back to Top

Latch Statistics

• Latch Activity
• Latch Sleep Breakdown
• Latch Miss Sources
• Mutex Sleep Summary
• Parent Latch Statistics
• Child Latch Statistics

Latch Activity

• "Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests
• "NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
• "Pct Misses" for both should be very close to 0.0