WORKLOAD REPOSITORY report for DB Name DB Id Instance Inst Num Release RAC Host ------------ ----------- ------------ -------- ----------- --- ------------ RACDB 705202569 racdb1 1 10.2.0.1.0 YES hpevo1 Snap Id Snap Time Sessions Curs/Sess --------- ------------------- -------- --------- Begin Snap: 4 28-Feb-10 13:00:49 35 4.9 End Snap: 7 28-Feb-10 16:00:18 35 4.9 Elapsed: 179.49 (mins) DB Time: 1.59 (mins) Cache Sizes ~~~~~~~~~~~ Begin End ---------- ---------- Buffer Cache: 168M 168M Std Block Size: 8K Shared Pool Size: 92M 92M Log Buffer: 2,904K Load Profile ~~~~~~~~~~~~ Per Second Per Transaction --------------- --------------- Redo size: 788.39 1,754.56 Logical reads: 37.36 83.15 Block changes: 4.00 8.91 Physical reads: 0.07 0.16 Physical writes: 0.30 0.66 User calls: 2.42 5.38 Parses: 2.14 4.77 Hard parses: 0.10 0.23 Sorts: 1.17 2.61 Logons: 0.04 0.10 Executes: 6.18 13.76 Transactions: 0.45 % Blocks changed per Read: 10.71 Recursive Call %: 92.30 Rollback per transaction %: 44.84 Rows per Sort: 11.57 Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 100.00 Redo NoWait %: 100.00 Buffer Hit %: 99.81 In-memory Sort %: 100.00 Library Hit %: 96.94 Soft Parse %: 95.28 Execute to Parse %: 65.34 Latch Hit %: 100.00 Parse CPU to Parse Elapsd %: 69.79 % Non-Parse CPU: 86.89 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 90.58 95.85 % SQL with executions>1: 83.10 84.03 % Memory for SQL w/exec>1: 91.09 87.73 Top 5 Timed Events Avg %Total ~~~~~~~~~~~~~~~~~~ wait Call Event Waits Time (s) (ms) Time Wait Class ------------------------------ ------------ ----------- ------ ------ ---------- control file sequential read 18,425 144 8 151.5 System I/O CPU time 61 64.4 Streams AQ: qmn coordinator wa 13 57 4368 59.7 Other control file parallel write 3,576 37 10 38.6 System I/O log file parallel write 3,403 25 7 26.7 System I/O ------------------------------------------------------------- RAC Statistics DB/Inst: RACDB/racdb1 Snaps: 4-7 Begin End ----- ----- Number of Instances: 2 2 Global Cache Load Profile ~~~~~~~~~~~~~~~~~~~~~~~~~ Per Second Per Transaction --------------- --------------- Global Cache blocks received: 0.37 0.82 Global Cache blocks served: 0.30 0.67 GCS/GES messages received: 2.16 4.81 GCS/GES messages sent: 2.32 5.16 DBWR Fusion writes: 0.04 0.09 Estd Interconnect traffic (KB) 6.23 Global Cache Efficiency Percentages (Target local+remote 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer access - local cache %: 98.82 Buffer access - remote cache %: 0.99 Buffer access - disk %: 0.19 Global Cache and Enqueue Services - Workload Characteristics ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Avg global enqueue get time (ms): 0.1 Avg global cache cr block receive time (ms): 1.7 Avg global cache current block receive time (ms): 1.1 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 %: 1.4 Avg global cache cr block flush time (ms): 15.9 Avg global cache current block pin time (ms): 0.0 Avg global cache current block send time (ms): 0.1 Global cache log flushes for current blocks served %: 1.9 Avg global cache current block flush time (ms): 18.5 Global Cache and Enqueue Services - Messaging Statistics ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Avg message sent queue time (ms): 0.7 Avg message sent queue time on ksxp (ms): 0.6 Avg message received queue time (ms): 7.9 Avg GCS message process time (ms): 0.1 Avg GES message process time (ms): 0.0 % of direct sent messages: 40.87 % of indirect sent messages: 27.35 % of flow controlled messages: 31.78 ------------------------------------------------------------- Time Model Statistics DB/Inst: RACDB/racdb1 Snaps: 4-7 -> Total time in database user-calls (DB Time): 95.1s -> 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 Statistic Name Time (s) % of DB Time ------------------------------------------ ------------------ ------------ sql execute elapsed time 92.6 97.3 DB CPU 61.3 64.4 PL/SQL execution elapsed time 10.0 10.5 parse time elapsed 6.2 6.5 hard parse elapsed time 3.7 3.9 connection management call elapsed time 0.6 .7 hard parse (sharing criteria) elapsed time 0.3 .4 PL/SQL compilation elapsed time 0.3 .3 sequence load elapsed time 0.1 .1 hard parse (bind mismatch) elapsed time 0.0 .0 repeated bind elapsed time 0.0 .0 DB time 95.1 N/A background elapsed time 377.0 N/A background cpu time 78.3 N/A ------------------------------------------------------------- Wait Class DB/Inst: RACDB/racdb1 Snaps: 4-7 -> s - second -> cs - centisecond - 100th of a second -> ms - millisecond - 1000th of a second -> us - microsecond - 1000000th of a second -> ordered by wait time desc, waits desc Avg %Time Total Wait wait Waits Wait Class Waits -outs Time (s) (ms) /txn -------------------- ---------------- ------ ---------------- ------- --------- System I/O 27,749 .0 216 8 5.7 Other 1,181,522 99.1 97 0 244.2 Commit 1,091 .0 10 9 0.2 User I/O 577 .0 8 13 0.1 Cluster 5,542 .0 7 1 1.1 Concurrency 3,820 .0 4 1 0.8 Application 2,939 .0 1 0 0.6 Network 27,111 .0 0 0 5.6 Configuration 11 18.2 0 19 0.0 ------------------------------------------------------------- Wait Events DB/Inst: RACDB/racdb1 Snaps: 4-7 -> s - second -> cs - centisecond - 100th of a second -> ms - millisecond - 1000th of a second -> us - microsecond - 1000000th of a second -> ordered by wait time desc, waits desc (idle events last) Avg %Time Total Wait wait Waits Event Waits -outs Time (s) (ms) /txn ---------------------------- -------------- ------ ----------- ------- --------- control file sequential read 18,425 .0 144 8 3.8 Streams AQ: qmn coordinator 13 84.6 57 4368 0.0 control file parallel write 3,576 .0 37 10 0.7 log file parallel write 3,403 .0 25 7 0.7 PX qref latch 1,040,534 100.0 16 0 215.0 reliable message 1,078 .8 11 10 0.2 db file parallel write 2,345 .0 10 4 0.5 log file sync 1,091 .0 10 9 0.2 db file sequential read 460 .0 6 13 0.1 name-service call wait 31 .0 2 70 0.0 CGS wait for IPC msg 105,700 100.0 2 0 21.8 gc cr block busy 198 .0 2 10 0.0 PX Deq: reap credit 13,284 93.2 2 0 2.7 wait for scn ack 1,077 .0 2 2 0.2 os thread startup 31 .0 2 57 0.0 row cache lock 1,761 .0 2 1 0.4 gc current multi block reque 398 .0 2 4 0.1 db file scattered read 99 .0 2 16 0.0 gc cr block 2-way 1,811 .0 1 1 0.4 gcs log flush sync 196 34.7 1 5 0.0 lms flush message acks 56 .0 1 15 0.0 library cache lock 1,753 .0 1 0 0.4 KJC: Wait for msg sends to c 4 50.0 1 147 0.0 gc current grant busy 791 .0 1 1 0.2 gc current block 2-way 1,124 .0 1 1 0.2 gc cr multi block request 836 .0 1 1 0.2 DFS lock handle 979 .0 1 1 0.2 SQL*Net break/reset to clien 2,864 .0 1 0 0.6 gc current block busy 55 .0 1 9 0.0 PX Deq: Signal ACK 201 25.9 1 3 0.0 enq: PS - contention 610 .0 0 0 0.1 ksxr poll remote instances 12,004 76.8 0 0 2.5 PX Deq Credit: send blkd 431 25.3 0 1 0.1 enq: US - contention 1,214 .0 0 0 0.3 undo segment extension 2 100.0 0 94 0.0 rdbms ipc reply 483 .0 0 0 0.1 library cache pin 265 .0 0 1 0.1 SQL*Net more data from clien 3,261 .0 0 0 0.7 SQL*Net message to client 23,850 .0 0 0 4.9 IPC send completion sync 3,333 100.0 0 0 0.7 gc cr grant 2-way 164 .0 0 0 0.0 gc current grant 2-way 161 .0 0 0 0.0 LGWR wait for redo copy 47 .0 0 1 0.0 latch free 32 .0 0 1 0.0 enq: FU - contention 1 .0 0 33 0.0 enq: TM - contention 69 .0 0 0 0.0 enq: TA - contention 41 .0 0 1 0.0 enq: HW - contention 7 .0 0 3 0.0 latch: library cache 4 .0 0 4 0.0 enq: WF - contention 25 .0 0 1 0.0 enq: TT - contention 28 .0 0 0 0.0 db file parallel read 1 .0 0 9 0.0 latch: shared pool 3 .0 0 3 0.0 enq: TD - KTF dump entries 40 .0 0 0 0.0 latch: ges resource hash lis 27 .0 0 0 0.0 enq: DR - contention 12 .0 0 0 0.0 enq: PI - contention 2 .0 0 2 0.0 enq: RS - prevent aging list 8 .0 0 0 0.0 enq: JS - job run lock - syn 8 .0 0 0 0.0 enq: UL - contention 6 .0 0 0 0.0 Wait Events DB/Inst: RACDB/racdb1 Snaps: 4-7 -> s - second -> cs - centisecond - 100th of a second -> ms - millisecond - 1000th of a second -> us - microsecond - 1000000th of a second -> ordered by wait time desc, waits desc (idle events last) Avg %Time Total Wait wait Waits Event Waits -outs Time (s) (ms) /txn ---------------------------- -------------- ------ ----------- ------- --------- gc current retry 2 .0 0 0 0.0 enq: TX - allocate ITL entry 2 .0 0 0 0.0 enq: TX - index contention 2 .0 0 0 0.0 enq: AF - task serialization 2 .0 0 0 0.0 latch: enqueue hash chains 4 .0 0 0 0.0 gc buffer busy 2 .0 0 0 0.0 enq: PE - contention 3 .0 0 0 0.0 lock escalate retry 2 100.0 0 0 0.0 enq: PG - contention 2 .0 0 0 0.0 latch: session allocation 4 .0 0 0 0.0 latch: redo allocation 4 .0 0 0 0.0 buffer busy waits 1 .0 0 0 0.0 latch: gcs resource hash 2 .0 0 0 0.0 direct path write 17 .0 0 0 0.0 SQL*Net message from client 23,850 .0 41,161 1726 4.9 Streams AQ: waiting for mess 3,393 91.4 21,002 6190 0.7 PX Idle Wait 8,733 97.2 20,978 2402 1.8 Streams AQ: qmn slave idle w 442 2.9 11,471 25952 0.1 class slave wait 98 92.9 10,981 112054 0.0 DIAG idle wait 53,759 .0 10,511 196 11.1 wait for unread message on b 10,912 97.9 10,509 963 2.3 ges remote message 142,745 98.4 10,508 74 29.5 ASM background timer 2,163 .0 10,504 4856 0.4 gcs remote message 368,260 99.0 10,498 29 76.1 jobq slave wait 4,515 47.4 10,487 2323 0.9 Streams AQ: waiting for time 570 41.1 10,486 18396 0.1 virtual circuit status 359 100.0 10,460 29137 0.1 Streams AQ: qmn coordinator 764 50.3 10,416 13634 0.2 PX Deq: Execution Msg 828 5.0 16 20 0.2 PX Deq: Execute Reply 236 14.4 2 8 0.0 PX Deq: Join ACK 362 45.6 1 2 0.1 PX Deq: Parse Reply 359 44.0 1 2 0.1 KSV master wait 8 .0 0 10 0.0 PX Deq: Msg Fragment 297 5.1 0 0 0.1 SGA: MMAN sleep for componen 5 80.0 0 9 0.0 Streams AQ: RAC qmn coordina 779 100.0 0 0 0.2 PX Deq Credit: need buffer 3 66.7 0 0 0.0 ------------------------------------------------------------- Background Wait Events DB/Inst: RACDB/racdb1 Snaps: 4-7 -> ordered by wait time desc, waits desc (idle events last) Avg %Time Total Wait wait Waits Event Waits -outs Time (s) (ms) /txn ---------------------------- -------------- ------ ----------- ------- --------- control file sequential read 15,344 .0 131 9 3.2 events in waitclass Other 126,546 93.9 64 1 26.2 control file parallel write 3,576 .0 37 10 0.7 log file parallel write 3,402 .0 25 7 0.7 db file parallel write 2,345 .0 10 4 0.5 os thread startup 21 .0 1 35 0.0 gc current block 2-way 385 .0 0 0 0.1 gc cr block 2-way 263 .0 0 0 0.1 gc current multi block reque 65 .0 0 1 0.0 gc current grant busy 242 .0 0 0 0.1 latch: shared pool 2 .0 0 2 0.0 library cache lock 64 .0 0 0 0.0 enq: HW - contention 3 .0 0 1 0.0 buffer busy waits 1 .0 0 0 0.0 gc cr multi block request 3 .0 -0 -2 0.0 rdbms ipc message 160,329 92.6 145,977 910 33.1 DIAG idle wait 53,758 .0 10,511 196 11.1 ges remote message 142,735 98.4 10,508 74 29.5 pmon timer 3,677 100.0 10,506 2857 0.8 ASM background timer 2,163 .0 10,504 4856 0.4 gcs remote message 368,255 99.0 10,498 29 76.1 Streams AQ: waiting for time 570 41.1 10,486 18396 0.1 Streams AQ: qmn coordinator 764 50.3 10,416 13634 0.2 smon timer 40 42.5 10,261 256535 0.0 class slave wait 20 305.0 6,935 346758 0.0 Streams AQ: qmn slave idle w 54 .0 1,415 26210 0.0 PX Deq: Join ACK 67 46.3 0 2 0.0 KSV master wait 8 .0 0 10 0.0 SGA: MMAN sleep for componen 5 80.0 0 9 0.0 PX Deq: Parse Reply 69 47.8 0 0 0.0 Streams AQ: RAC qmn coordina 779 100.0 0 0 0.2 PX Deq: Execute Reply 54 33.3 0 0 0.0 ------------------------------------------------------------- Operating System Statistics DB/Inst: RACDB/racdb1 Snaps: 4-7 Statistic Total -------------------------------- -------------------- BUSY_TIME 110,834 IDLE_TIME 863,441 NICE_TIME 241 SYS_TIME 51,695 USER_TIME 58,898 LOAD 1 RSRC_MGR_CPU_WAIT_TIME 0 PHYSICAL_MEMORY_BYTES 27,528 NUM_CPUS 1 ------------------------------------------------------------- Service Statistics DB/Inst: RACDB/racdb1 Snaps: 4-7 -> ordered by DB Time Physical Logical Service Name DB Time (s) DB CPU (s) Reads Reads -------------------------------- ------------ ------------ ---------- ---------- SYS$USERS 76.7 44.9 16 217,383 racdb.idevelopment.info 16.7 14.7 0 59,535 SYS$BACKGROUND 0.2 0.2 868 126,552 racdbXDB 0.0 0.0 0 0 racdb_taf 0.0 0.0 0 0 ------------------------------------------------------------- Service Wait Class Stats DB/Inst: RACDB/racdb1 Snaps: 4-7 -> 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 centisecond (100th of a second) Service Name ---------------------------------------------------------------- User I/O User I/O Concurcy Concurcy Admin Admin Network Network Total Wts Wt Time Total Wts Wt Time Total Wts Wt Time Total Wts Wt Time --------- --------- --------- --------- --------- --------- --------- --------- SYS$USERS 13 45 507 126 0 0 12527 16 racdb.idevelopment.info 0 0 53 2 0 0 14323 7 SYS$BACKGROUND 569 727 3278 307 0 0 0 0 ------------------------------------------------------------- SQL ordered by Elapsed Time DB/Inst: RACDB/racdb1 Snaps: 4-7 -> 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 Elapsed CPU Elap per % Total Time (s) Time (s) Executions Exec (s) DB Time SQL Id ---------- ---------- ------------ ---------- ------- ------------- 26 15 36 0.7 26.9 26zpagbuyygf9 Module: emagent@hpevo1 (TNS V1-V3) /* 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 )) block 18 11 166 0.1 19.1 6gvch1xu9ca3g DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN : = FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := next_date ; IF broken THEN :b := 1; ELSE :b := 0; END IF; END; 9 0 3 3.1 9.6 7vgmvmy8vvb9s insert into wrh$_tempstatxs (snap_id, dbid, instance_number, file#, creation_c hange#, phyrds, phywrts, singleblkrds, readtim, writetim, singleblkrdtim, phy blkrd, phyblkwrt, wait_count, time) select :snap_id, :dbid, :instance_num ber, tf.tfnum, to_number(tf.tfcrc_scn) creation_change#, ts.kcftiopyr, ts. 8 8 6,444 0.0 8.5 cb75rw3w1tt0s Module: OEM.SystemPool begin MGMT_JOB_ENGINE.get_scheduled_steps(:1, :2, :3, :4); end; 7 2 36 0.2 6.9 26hpjhhc3kacr Module: emagent@hpevo1 (TNS V1-V3) /* OracleOEM */ SELECT up.count, total.total FROM (SELECT COUNT(*) count FRO M gv$instance WHERE status='OPEN') up, (SELECT COUNT(DISTINCT thread#) total FR OM v$log) total 4 3 17 0.3 4.6 1gc7umngbqyc1 SELECT /*+ ORDERED */ A.READY FROM USER_QUEUES B, GV$AQ A WHERE B.NAME = 'MGMT_N OTIFY_Q' AND A.QID = B.QID 4 1 122 0.0 4.1 abtp0uqvdb1d3 CALL MGMT_ADMIN_DATA.EVALUATE_MGMT_METRICS(:tguid, :mguid, :result) 3 3 3 1.1 3.6 bunssq950snhf insert into wrh$_sga_target_advice (snap_id, dbid, instance_number, SGA_SIZ E, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_READS) select :snap_id, :dbi d, :instance_number, SGA_SIZE, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_R EADS from v$sga_target_advice 3 1 1 3.3 3.4 b84gb6u21n480 insert into WRI$_DBU_CPU_USAGE(dbid, version, timestamp, cpu_count, cpu_core_count, cpu_socket_count) select :dbid, :version, :bind1, gv.gv_cpu_count, gv.gv_cpu_core_count, gv.gv_cpu_socket_count from (selec t sum(cpu_count_current) as gv_cpu_count, sum(cpu_core_count_cu 3 3 355 0.0 2.8 2b064ybzkwf1y Module: OEM.SystemPool BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END; 2 1 17 0.1 2.0 6mvfay19q3v4n SELECT COUNT(CLIENT_INFO) FROM GV$SESSION WHERE USERNAME=:B2 AND CLIENT_INFO = : B1 GROUP BY CLIENT_INFO 1 0 160 0.0 1.5 2txg6sm725mn6 INSERT INTO MGMT_METRICS_RAW (TARGET_GUID, COLLECTION_TIMESTAMP, METRIC_GUID, KE Y_VALUE, VALUE, STRING_VALUE) VALUES (:B5 , :B4 , :B3 , :B2 , :B1 , NULL) 1 1 6,444 0.0 1.2 6gh8gj9n09vr7 SQL ordered by Elapsed Time DB/Inst: RACDB/racdb1 Snaps: 4-7 -> 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 Elapsed CPU Elap per % Total Time (s) Time (s) Executions Exec (s) DB Time SQL Id ---------- ---------- ------------ ---------- ------- ------------- Module: OEM.SystemPool SELECT JOB_ID, EXECUTION_ID, STEP_ID, STEP_NAME, STEP_TYPE, ITERATE_PARAM, ITERA TE_PARAM_INDEX, COMMAND_TYPE, TIMEZONE_REGION FROM MGMT_JOB_EXECUTION J WHERE ST EP_TYPE IN (:B7 , :B6 , :B5 ) AND STEP_STATUS = :B4 AND COMMAND_TYPE = :B3 AND S TEP_NAME LIKE :B2 AND START_TIME <= SYS_EXTRACT_UTC(SYSTIMESTAMP) AND ROWNUM <= 1 1 179 0.0 1.0 cydnuss99swtd Module: OEM.SystemPool BEGIN EM_PING.RECORD_BATCH_HEARTBEAT(:1, :2, :3); END; 1 0 53 0.0 1.0 39m4sx9k63ba2 select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece from idl_ub2$ wher e obj#=:1 and part=:2 and version=:3 order by piece# ------------------------------------------------------------- SQL ordered by CPU Time DB/Inst: RACDB/racdb1 Snaps: 4-7 -> 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 CPU Elapsed CPU per % Total Time (s) Time (s) Executions Exec (s) DB Time SQL Id ---------- ---------- ------------ ----------- ------- ------------- 15 26 36 0.43 26.9 26zpagbuyygf9 Module: emagent@hpevo1 (TNS V1-V3) /* 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 )) block 11 18 166 0.07 19.1 6gvch1xu9ca3g DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN : = FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := next_date ; IF broken THEN :b := 1; ELSE :b := 0; END IF; END; 8 8 6,444 0.00 8.5 cb75rw3w1tt0s Module: OEM.SystemPool begin MGMT_JOB_ENGINE.get_scheduled_steps(:1, :2, :3, :4); end; 3 3 3 1.09 3.6 bunssq950snhf insert into wrh$_sga_target_advice (snap_id, dbid, instance_number, SGA_SIZ E, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_READS) select :snap_id, :dbi d, :instance_number, SGA_SIZE, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_R EADS from v$sga_target_advice 3 4 17 0.16 4.6 1gc7umngbqyc1 SELECT /*+ ORDERED */ A.READY FROM USER_QUEUES B, GV$AQ A WHERE B.NAME = 'MGMT_N OTIFY_Q' AND A.QID = B.QID 3 3 355 0.01 2.8 2b064ybzkwf1y Module: OEM.SystemPool BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END; 2 7 36 0.07 6.9 26hpjhhc3kacr Module: emagent@hpevo1 (TNS V1-V3) /* OracleOEM */ SELECT up.count, total.total FROM (SELECT COUNT(*) count FRO M gv$instance WHERE status='OPEN') up, (SELECT COUNT(DISTINCT thread#) total FR OM v$log) total 1 3 1 1.48 3.4 b84gb6u21n480 insert into WRI$_DBU_CPU_USAGE(dbid, version, timestamp, cpu_count, cpu_core_count, cpu_socket_count) select :dbid, :version, :bind1, gv.gv_cpu_count, gv.gv_cpu_core_count, gv.gv_cpu_socket_count from (selec t sum(cpu_count_current) as gv_cpu_count, sum(cpu_core_count_cu 1 4 122 0.01 4.1 abtp0uqvdb1d3 CALL MGMT_ADMIN_DATA.EVALUATE_MGMT_METRICS(:tguid, :mguid, :result) 1 1 6,444 0.00 1.2 6gh8gj9n09vr7 Module: OEM.SystemPool SELECT JOB_ID, EXECUTION_ID, STEP_ID, STEP_NAME, STEP_TYPE, ITERATE_PARAM, ITERA TE_PARAM_INDEX, COMMAND_TYPE, TIMEZONE_REGION FROM MGMT_JOB_EXECUTION J WHERE ST EP_TYPE IN (:B7 , :B6 , :B5 ) AND STEP_STATUS = :B4 AND COMMAND_TYPE = :B3 AND S TEP_NAME LIKE :B2 AND START_TIME <= SYS_EXTRACT_UTC(SYSTIMESTAMP) AND ROWNUM <= 1 1 179 0.01 1.0 cydnuss99swtd Module: OEM.SystemPool BEGIN EM_PING.RECORD_BATCH_HEARTBEAT(:1, :2, :3); END; 1 2 17 0.06 2.0 6mvfay19q3v4n SELECT COUNT(CLIENT_INFO) FROM GV$SESSION WHERE USERNAME=:B2 AND CLIENT_INFO = : B1 GROUP BY CLIENT_INFO SQL ordered by CPU Time DB/Inst: RACDB/racdb1 Snaps: 4-7 -> 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 CPU Elapsed CPU per % Total Time (s) Time (s) Executions Exec (s) DB Time SQL Id ---------- ---------- ------------ ----------- ------- ------------- 0 9 3 0.12 9.6 7vgmvmy8vvb9s insert into wrh$_tempstatxs (snap_id, dbid, instance_number, file#, creation_c hange#, phyrds, phywrts, singleblkrds, readtim, writetim, singleblkrdtim, phy blkrd, phyblkwrt, wait_count, time) select :snap_id, :dbid, :instance_num ber, tf.tfnum, to_number(tf.tfcrc_scn) creation_change#, ts.kcftiopyr, ts. 0 1 160 0.00 1.5 2txg6sm725mn6 INSERT INTO MGMT_METRICS_RAW (TARGET_GUID, COLLECTION_TIMESTAMP, METRIC_GUID, KE Y_VALUE, VALUE, STRING_VALUE) VALUES (:B5 , :B4 , :B3 , :B2 , :B1 , NULL) 0 1 53 0.00 1.0 39m4sx9k63ba2 select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece from idl_ub2$ wher e obj#=:1 and part=:2 and version=:3 order by piece# ------------------------------------------------------------- SQL ordered by Gets DB/Inst: RACDB/racdb1 Snaps: 4-7 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> Total Buffer Gets: 402,342 -> Captured SQL account for 59.3% of Total Gets CPU Elapsed Buffer Gets Executions per Exec %Total Time (s) Time (s) SQL Id -------------- ------------ ------------ ------ -------- --------- ------------- 145,422 166 876.0 36.1 11.39 18.17 6gvch1xu9ca3g DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN : = FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := next_date ; IF broken THEN :b := 1; ELSE :b := 0; END IF; END; 66,140 122 542.1 16.4 1.27 3.92 abtp0uqvdb1d3 CALL MGMT_ADMIN_DATA.EVALUATE_MGMT_METRICS(:tguid, :mguid, :result) 53,483 17 3,146.1 13.3 2.78 4.36 1gc7umngbqyc1 SELECT /*+ ORDERED */ A.READY FROM USER_QUEUES B, GV$AQ A WHERE B.NAME = 'MGMT_N OTIFY_Q' AND A.QID = B.QID 38,664 6,444 6.0 9.6 8.06 8.06 cb75rw3w1tt0s Module: OEM.SystemPool begin MGMT_JOB_ENGINE.get_scheduled_steps(:1, :2, :3, :4); end; 20,315 24 846.5 5.0 0.19 0.21 fca1gqzj52va2 select sum(SYSDATE - cast(NVL(qt.delay, qt.enq_time) as date)) from "SYS"."AL ERT_QT" qt, "SYS"."AQ$_ALERT_QT_I" iot where ((iot.subscriber# != 0 and iot.subscriber# in (select subscriber_id from "SYS"."AQ$_ALERT_QT_S" where queue_name = :1 an 19,332 6,444 3.0 4.8 1.16 1.16 6gh8gj9n09vr7 Module: OEM.SystemPool SELECT JOB_ID, EXECUTION_ID, STEP_ID, STEP_NAME, STEP_TYPE, ITERATE_PARAM, ITERA TE_PARAM_INDEX, COMMAND_TYPE, TIMEZONE_REGION FROM MGMT_JOB_EXECUTION J WHERE ST EP_TYPE IN (:B7 , :B6 , :B5 ) AND STEP_STATUS = :B4 AND COMMAND_TYPE = :B3 AND S TEP_NAME LIKE :B2 AND START_TIME <= SYS_EXTRACT_UTC(SYSTIMESTAMP) AND ROWNUM <= 10,347 355 29.1 2.6 2.68 2.69 2b064ybzkwf1y Module: OEM.SystemPool BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END; 8,250 179 46.1 2.1 0.98 0.99 cydnuss99swtd Module: OEM.SystemPool BEGIN EM_PING.RECORD_BATCH_HEARTBEAT(:1, :2, :3); END; 8,162 330 24.7 2.0 0.31 0.31 gnjnsxu3gv5cv Module: SEVERITY EVALUATION SELECT DISTINCT EVAL_FUNC FROM MGMT_METRICS M, MGMT_TARGETS T WHERE METRIC_GUID = :B1 AND M.TYPE_META_VER = T.TYPE_META_VER 5,976 617 9.7 1.5 0.23 0.23 0h6b2sajwb74n select privilege#,level from sysauth$ connect by grantee#=prior privilege# and p rivilege#>0 start with grantee#=:1 and privilege#>0 5,117 1,512 3.4 1.3 0.76 0.76 3c1kubcdjnppq update sys.col_usage$ set equality_preds = equality_preds + decode(bitan d(:flag,1),0,0,1), equijoin_preds = equijoin_preds + decode(bitand(:flag ,2),0,0,1), nonequijoin_preds = nonequijoin_preds + decode(bitand(:flag,4),0,0 ,1), range_preds = range_preds + decode(bitand(:flag,8),0,0,1), 4,963 821 6.0 1.2 0.68 0.75 18naypzfmabd6 INSERT INTO MGMT_SYSTEM_PERFORMANCE_LOG (JOB_NAME, TIME, DURATION, MODULE, ACTIO N, IS_TOTAL, NAME, VALUE, CLIENT_DATA, HOST_URL) VALUES (:B9 , SYSDATE, :B8 , SU BSTR(:B7 , 1, 512), SUBSTR(:B6 ,1,32), :B5 , SUBSTR(:B4 ,1,128), SUBSTR(:B3 ,1,1 28), SUBSTR(:B2 ,1,128), SUBSTR(:B1 ,1,256)) 4,636 2,152 2.2 1.2 0.43 0.43 803b7z0t84sq7 select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= next_date) and (n SQL ordered by Gets DB/Inst: RACDB/racdb1 Snaps: 4-7 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> Total Buffer Gets: 402,342 -> Captured SQL account for 59.3% of Total Gets CPU Elapsed Buffer Gets Executions per Exec %Total Time (s) Time (s) SQL Id -------------- ------------ ------------ ------ -------- --------- ------------- ext_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, jo b ------------------------------------------------------------- SQL ordered by Reads DB/Inst: RACDB/racdb1 Snaps: 4-7 -> Total Disk Reads: 772 -> Captured SQL account for 13.6% of Total Reads CPU Elapsed Physical Reads Executions per Exec %Total Time (s) Time (s) SQL Id -------------- ----------- ------------- ------ -------- --------- ------------- 21 234 0.1 2.7 0.11 0.55 cqgv56fmuj63x select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(proper ty,0),subname,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj# (+) order by order# 17 234 0.1 2.2 0.06 0.33 8swypbbr0m372 select order#,columns,types from access$ where d_obj#=:1 16 166 0.1 2.1 11.39 18.17 6gvch1xu9ca3g DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN : = FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := next_date ; IF broken THEN :b := 1; ELSE :b := 0; END IF; END; 11 188 0.1 1.4 0.05 0.27 96g93hntrzjtr select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_ size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, a vgcln from hist_head$ where obj#=:1 and intcol#=:2 9 821 0.0 1.2 0.68 0.75 18naypzfmabd6 INSERT INTO MGMT_SYSTEM_PERFORMANCE_LOG (JOB_NAME, TIME, DURATION, MODULE, ACTIO N, IS_TOTAL, NAME, VALUE, CLIENT_DATA, HOST_URL) VALUES (:B9 , SYSDATE, :B8 , SU BSTR(:B7 , 1, 512), SUBSTR(:B6 ,1,32), :B5 , SUBSTR(:B4 ,1,128), SUBSTR(:B3 ,1,1 28), SUBSTR(:B2 ,1,128), SUBSTR(:B1 ,1,256)) 9 1 9.0 1.2 1.48 3.26 b84gb6u21n480 insert into WRI$_DBU_CPU_USAGE(dbid, version, timestamp, cpu_count, cpu_core_count, cpu_socket_count) select :dbid, :version, :bind1, gv.gv_cpu_count, gv.gv_cpu_core_count, gv.gv_cpu_socket_count from (selec t sum(cpu_count_current) as gv_cpu_count, sum(cpu_core_count_cu 7 3 2.3 0.9 0.35 9.18 7vgmvmy8vvb9s insert into wrh$_tempstatxs (snap_id, dbid, instance_number, file#, creation_c hange#, phyrds, phywrts, singleblkrds, readtim, writetim, singleblkrdtim, phy blkrd, phyblkwrt, wait_count, time) select :snap_id, :dbid, :instance_num ber, tf.tfnum, to_number(tf.tfcrc_scn) creation_change#, ts.kcftiopyr, ts. 7 3 2.3 0.9 3.27 3.42 bunssq950snhf insert into wrh$_sga_target_advice (snap_id, dbid, instance_number, SGA_SIZ E, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_READS) select :snap_id, :dbi d, :instance_number, SGA_SIZE, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_R EADS from v$sga_target_advice 6 53 0.1 0.8 0.01 0.98 39m4sx9k63ba2 select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece from idl_ub2$ wher e obj#=:1 and part=:2 and version=:3 order by piece# 6 1 6.0 0.8 0.12 0.18 fmysjzxwxjuwj Module: MMON_SLAVE BEGIN DBMS_FEATURE_PARTITION_SYSTEM(:feature_boolean, :aux_cnt, :feature_info); END; ------------------------------------------------------------- SQL ordered by Executions DB/Inst: RACDB/racdb1 Snaps: 4-7 -> Total Executions: 66,581 -> Captured SQL account for 59.2% of Total CPU per Elap per Executions Rows Processed Rows per Exec Exec (s) Exec (s) SQL Id ------------ --------------- -------------- ---------- ----------- ------------- 6,610 6,610 1.0 0.00 0.00 089dbukv1aanh Module: EM_PING SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) FROM DUAL 6,444 0 0.0 0.00 0.00 6gh8gj9n09vr7 Module: OEM.SystemPool SELECT JOB_ID, EXECUTION_ID, STEP_ID, STEP_NAME, STEP_TYPE, ITERATE_PARAM, ITERA TE_PARAM_INDEX, COMMAND_TYPE, TIMEZONE_REGION FROM MGMT_JOB_EXECUTION J WHERE ST EP_TYPE IN (:B7 , :B6 , :B5 ) AND STEP_STATUS = :B4 AND COMMAND_TYPE = :B3 AND S TEP_NAME LIKE :B2 AND START_TIME <= SYS_EXTRACT_UTC(SYSTIMESTAMP) AND ROWNUM <= 6,444 6,444 1.0 0.00 0.00 cb75rw3w1tt0s Module: OEM.SystemPool begin MGMT_JOB_ENGINE.get_scheduled_steps(:1, :2, :3, :4); end; 2,152 166 0.1 0.00 0.00 803b7z0t84sq7 select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= next_date) and (n ext_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, jo b 1,512 1,469 1.0 0.00 0.00 3c1kubcdjnppq update sys.col_usage$ set equality_preds = equality_preds + decode(bitan d(:flag,1),0,0,1), equijoin_preds = equijoin_preds + decode(bitand(:flag ,2),0,0,1), nonequijoin_preds = nonequijoin_preds + decode(bitand(:flag,4),0,0 ,1), range_preds = range_preds + decode(bitand(:flag,8),0,0,1), 1,325 1,325 1.0 0.00 0.00 b0320g1xasbx7 Module: SEVERITY EVALUATION SELECT TIMEZONE_REGION FROM MGMT_TARGETS WHERE TARGET_GUID = :B1 1,322 1,322 1.0 0.00 0.00 g2wr3u7s1gtf3 select count(*) from sys.job$ where (next_date > sysdate) and (next_date < (sysd ate+5/86400)) 821 821 1.0 0.00 0.00 18naypzfmabd6 INSERT INTO MGMT_SYSTEM_PERFORMANCE_LOG (JOB_NAME, TIME, DURATION, MODULE, ACTIO N, IS_TOTAL, NAME, VALUE, CLIENT_DATA, HOST_URL) VALUES (:B9 , SYSDATE, :B8 , SU BSTR(:B7 , 1, 512), SUBSTR(:B6 ,1,32), :B5 , SUBSTR(:B4 ,1,128), SUBSTR(:B3 ,1,1 28), SUBSTR(:B2 ,1,128), SUBSTR(:B1 ,1,256)) 767 767 1.0 0.00 0.00 5kyb5bvnu3s04 SELECT DISTINCT METRIC_GUID FROM MGMT_METRICS WHERE TARGET_TYPE = :B3 AND METRIC _NAME = :B2 AND METRIC_COLUMN = :B1 698 698 1.0 0.00 0.00 661tvbvnac891 Module: SEVERITY EVALUATION SELECT CURRENT_STATUS FROM MGMT_CURRENT_AVAILABILITY WHERE TARGET_GUID = :B1 ------------------------------------------------------------- SQL ordered by Parse Calls DB/Inst: RACDB/racdb1 Snaps: 4-7 -> Total Parse Calls: 23,076 -> Captured SQL account for 45.8% of Total % Total Parse Calls Executions Parses SQL Id ------------ ------------ --------- ------------- 617 617 2.67 0h6b2sajwb74n select privilege#,level from sysauth$ connect by grantee#=prior privilege# and p rivilege#>0 start with grantee#=:1 and privilege#>0 608 1,512 2.63 3c1kubcdjnppq update sys.col_usage$ set equality_preds = equality_preds + decode(bitan d(:flag,1),0,0,1), equijoin_preds = equijoin_preds + decode(bitand(:flag ,2),0,0,1), nonequijoin_preds = nonequijoin_preds + decode(bitand(:flag,4),0,0 ,1), range_preds = range_preds + decode(bitand(:flag,8),0,0,1), 608 43 2.63 53btfq0dt9bs9 insert into sys.col_usage$ values ( :objn, :coln, decode(bitand(:flag,1),0,0 ,1), decode(bitand(:flag,2),0,0,1), decode(bitand(:flag,4),0,0,1), decode( bitand(:flag,8),0,0,1), decode(bitand(:flag,16),0,0,1), decode(bitand(:flag, 32),0,0,1), :time) 608 608 2.63 b2gnxm5z6r51n lock table sys.col_usage$ in exclusive mode nowait 377 377 1.63 350f5yrnnmshs lock table sys.mon_mods$ in exclusive mode nowait 377 377 1.63 g00cj285jmgsw update sys.mon_mods$ set inserts = inserts + :ins, updates = updates + :upd, del etes = deletes + :del, flags = (decode(bitand(flags, :flag), :flag, flags, flags + :flag)), drop_segments = drop_segments + :dropseg, timestamp = :time where ob j# = :objn 355 355 1.54 2b064ybzkwf1y Module: OEM.SystemPool BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END; 332 664 1.44 c3hhngpbpvvmk Module: SEVERITY EVALUATION SELECT MARKER_TIMESTAMP FROM MGMT_AVAILABILITY_MARKER WHERE TARGET_GUID = :B1 330 509 1.43 24dkx03u3rj6k Module: SELECT COUNT(*) FROM MGMT_PARAMETERS WHERE PARAMETER_NAME=:B1 AND UPPER(PARAMETE R_VALUE)='TRUE' 330 698 1.43 661tvbvnac891 Module: SEVERITY EVALUATION SELECT CURRENT_STATUS FROM MGMT_CURRENT_AVAILABILITY WHERE TARGET_GUID = :B1 289 289 1.25 0sppkag5n7mk6 select owner_instance from sys.aq$_queue_table_affinities aft, system.aq$_queue s q where aft.table_objno = q.table_objno and q.eventid = :1 265 265 1.15 0k8522rmdzg4k select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege# >0 234 234 1.01 8swypbbr0m372 select order#,columns,types from access$ where d_obj#=:1 234 234 1.01 cqgv56fmuj63x select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(proper ty,0),subname,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj# (+) order by order# SQL ordered by Parse Calls DB/Inst: RACDB/racdb1 Snaps: 4-7 -> Total Parse Calls: 23,076 -> Captured SQL account for 45.8% of Total % Total Parse Calls Executions Parses SQL Id ------------ ------------ --------- ------------- ------------------------------------------------------------- SQL ordered by Sharable Memory DB/Inst: RACDB/racdb1 Snaps: 4-7 No data exists for this section of the report. ------------------------------------------------------------- SQL ordered by Version Count DB/Inst: RACDB/racdb1 Snaps: 4-7 No data exists for this section of the report. ------------------------------------------------------------- SQL ordered by Cluster Wait Time DB/Inst: RACDB/racdb1 Snaps: 4-7 Cluster CWT % of Elapsed CPU Wait Time (s) Elapsd Tim Time(s) Time(s) Executions SQL Id ------------- ---------- ----------- ----------- -------------- ------------- 2.05 11.3 18.17 11.39 166 6gvch1xu9ca3g DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN : = FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := next_date ; IF broken THEN :b := 1; ELSE :b := 0; END IF; END; 1.24 87.3 1.42 0.18 160 2txg6sm725mn6 INSERT INTO MGMT_METRICS_RAW (TARGET_GUID, COLLECTION_TIMESTAMP, METRIC_GUID, KE Y_VALUE, VALUE, STRING_VALUE) VALUES (:B5 , :B4 , :B3 , :B2 , :B1 , NULL) .55 68.4 0.81 0.54 349 a1u69wxgrjqmq select tab.rowid, tab.msgid, tab.corrid, tab.priority, tab.delay, tab.expirat ion, tab.retry_count, tab.exception_qschema, tab.exception_queue, tab.chain_no , tab.local_order_no, tab.enq_time, tab.time_manager_info, tab.state, tab.enq_ tid, tab.step_no, tab.sender_name, tab.sender_address, tab.sender_protocol, .19 69.5 0.27 0.17 448 c45crtmadjf65 Module: SEVERITY EVALUATION SELECT SEVERITY_CODE FROM (SELECT SEVERITY_CODE FROM MGMT_SEVERITY WHERE TARGET_ GUID = :B4 AND METRIC_GUID = :B3 AND KEY_VALUE = :B2 AND COLLECTION_TIMESTAMP <= :B1 ORDER BY COLLECTION_TIMESTAMP DESC) WHERE ROWNUM = 1 .14 37.4 0.38 0.37 166 47a50dvdgnxc2 update sys.job$ set failures=0, this_date=null, flag=:1, last_date=:2, next_dat e = greatest(:3, sysdate), total=total+(sysdate-nvl(this_date,sysdate)) where j ob=:4 .11 2.8 3.92 1.27 122 abtp0uqvdb1d3 CALL MGMT_ADMIN_DATA.EVALUATE_MGMT_METRICS(:tguid, :mguid, :result) .10 2.2 4.36 2.78 17 1gc7umngbqyc1 SELECT /*+ ORDERED */ A.READY FROM USER_QUEUES B, GV$AQ A WHERE B.NAME = 'MGMT_N OTIFY_Q' AND A.QID = B.QID .08 15.1 0.52 0.14 69 1dubbbfqnqvh9 SELECT ORA_TQ_BASE$.NEXTVAL FROM DUAL .06 39.1 0.15 0.10 218 8djm33auk1d0j Module: SEVERITY EVALUATION SELECT SEVERITY_CODE, COLLECTION_TIMESTAMP FROM (SELECT SEVERITY_CODE, COLLECTIO N_TIMESTAMP FROM MGMT_SEVERITY WHERE TARGET_GUID = :B8 AND METRIC_GUID = :B7 AND KEY_VALUE = :B6 AND COLLECTION_TIMESTAMP <= :B5 AND SEVERITY_CODE != :B4 ORDER BY COLLECTION_TIMESTAMP DESC, DECODE(SEVERITY_CODE, :B3 , 1, :B2 , 2, :B1 , 3, 9 .05 41.6 0.13 0.13 202 c4nhd1ntptxq7 select message_level, sequence_id, time_suggested from WRI$_ALERT_OUTSTANDING wh ere reason_id = :1 and object_id = :2 and subobject_id = :3 and internal_instanc e_number = :4 .03 4.3 0.76 0.76 1,512 3c1kubcdjnppq update sys.col_usage$ set equality_preds = equality_preds + decode(bitan d(:flag,1),0,0,1), equijoin_preds = equijoin_preds + decode(bitand(:flag ,2),0,0,1), nonequijoin_preds = nonequijoin_preds + decode(bitand(:flag,4),0,0 ,1), range_preds = range_preds + decode(bitand(:flag,8),0,0,1), .03 15.5 0.21 0.19 24 fca1gqzj52va2 select sum(SYSDATE - cast(NVL(qt.delay, qt.enq_time) as date)) from "SYS"."AL ERT_QT" qt, "SYS"."AQ$_ALERT_QT_I" iot where ((iot.subscriber# != 0 and iot.subscriber# in (select subscriber_id from "SYS"."AQ$_ALERT_QT_S" where queue_name = :1 an SQL ordered by Cluster Wait Time DB/Inst: RACDB/racdb1 Snaps: 4-7 Cluster CWT % of Elapsed CPU Wait Time (s) Elapsd Tim Time(s) Time(s) Executions SQL Id ------------- ---------- ----------- ----------- -------------- ------------- .02 3.3 0.75 0.68 821 18naypzfmabd6 INSERT INTO MGMT_SYSTEM_PERFORMANCE_LOG (JOB_NAME, TIME, DURATION, MODULE, ACTIO N, IS_TOTAL, NAME, VALUE, CLIENT_DATA, HOST_URL) VALUES (:B9 , SYSDATE, :B8 , SU BSTR(:B7 , 1, 512), SUBSTR(:B6 ,1,32), :B5 , SUBSTR(:B4 ,1,128), SUBSTR(:B3 ,1,1 28), SUBSTR(:B2 ,1,128), SUBSTR(:B1 ,1,256)) .02 23.0 0.10 0.10 49 3qsmy8ybvwt3n insert into WRI$_ALERT_OUTSTANDING (reason_id, object_id, subobject_id, internal _instance_number, owner, object_name, subobject_name, sequence_id, reason_argume nt_1, reason_argument_2, reason_argument_3, reason_argument_4, reason_argument_5 , time_suggested, creation_time, action_argument_1, action_argument_2, action_ar .02 21.7 0.10 0.09 98 c0agatqzq2jzr insert into "SYS"."ALERT_QT" (q_name, msgid, corrid, priority, state, delay, ex piration, time_manager_info, local_order_no, chain_no, enq_time, step_no, enq_ uid, enq_tid, retry_count, exception_qschema, exception_queue, recipient_key, dequeue_msgid, user_data, sender_name, sender_address, sender_protocol, user .02 2.2 0.99 0.98 179 cydnuss99swtd Module: OEM.SystemPool BEGIN EM_PING.RECORD_BATCH_HEARTBEAT(:1, :2, :3); END; .02 3.7 0.54 0.29 187 6d64jpfzqc9rv INSERT INTO MGMT_METRICS_RAW (TARGET_GUID, COLLECTION_TIMESTAMP, METRIC_GUID, KE Y_VALUE, VALUE, STRING_VALUE) VALUES (:B5 , :B4 , :B3 , :B2 , NULL, :B1 ) .02 9.7 0.18 0.18 1,325 b0320g1xasbx7 Module: SEVERITY EVALUATION SELECT TIMEZONE_REGION FROM MGMT_TARGETS WHERE TARGET_GUID = :B1 .02 25.3 0.06 0.06 34 6bzv9ph7d1hvj select count(*) from "SYS"."AQ$_ALERT_QT_I" where (subscriber# != 0 and subscriber# in (select subscriber_id from "SYS" ."AQ$_ALERT_QT_S" where queue_name = :1)) or ( subscriber# = 0 and queue# = :2) .01 0.4 3.42 3.27 3 bunssq950snhf insert into wrh$_sga_target_advice (snap_id, dbid, instance_number, SGA_SIZ E, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_READS) select :snap_id, :dbi d, :instance_number, SGA_SIZE, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_R EADS from v$sga_target_advice .01 15.5 0.09 0.09 49 19rkm1wsf9axx insert into WRI$_ALERT_HISTORY (sequence_id, reason_id, owner, object_name, subo bject_name, reason_argument_1, reason_argument_2, reason_argument_3, reason_argu ment_4, reason_argument_5, time_suggested, creation_time, action_argument_1, act ion_argument_2, action_argument_3, action_argument_4, action_argument_5, message .01 7.3 0.18 0.12 1 fmysjzxwxjuwj Module: MMON_SLAVE BEGIN DBMS_FEATURE_PARTITION_SYSTEM(:feature_boolean, :aux_cnt, :feature_info); END; .01 2.4 0.55 0.11 234 cqgv56fmuj63x select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(proper ty,0),subname,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj# (+) order by order# .01 0.4 3.26 1.48 1 b84gb6u21n480 insert into WRI$_DBU_CPU_USAGE(dbid, version, timestamp, SQL ordered by Cluster Wait Time DB/Inst: RACDB/racdb1 Snaps: 4-7 Cluster CWT % of Elapsed CPU Wait Time (s) Elapsd Tim Time(s) Time(s) Executions SQL Id ------------- ---------- ----------- ----------- -------------- ------------- cpu_count, cpu_core_count, cpu_socket_count) select :dbid, :version, :bind1, gv.gv_cpu_count, gv.gv_cpu_core_count, gv.gv_cpu_socket_count from (selec t sum(cpu_count_current) as gv_cpu_count, sum(cpu_core_count_cu .01 3.6 0.35 0.35 1 cz22nn47dtvrj INSERT INTO MGMT_SEVERITY (TARGET_GUID, KEY_VALUE, METRIC_GUID, SEVERITY_GUID, C OLLECTION_TIMESTAMP, SEVERITY_CODE, SEVERITY_TYPE, ANNOTATED_FLAG, NOTIFICATION_ STATUS, MESSAGE, MESSAGE_NLSID, MESSAGE_PARAMS) VALUES (:B11 , :B10 , :B9 , :B8 , :B7 , :B6 , :B5 , :B4 , 1, :B3 , :B2 , :B1 ) .01 30.4 0.04 0.04 112 6vrbpjz1mhf87 Module: SEVERITY EVALUATION SELECT TARGET_TYPE, TARGET_NAME, TIMEZONE_REGION FROM MGMT_TARGETS WHERE TARGET_ GUID = :B1 .01 20.7 0.05 0.05 71 2ym6hhaq30r73 select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(lis ts,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0),NVL(scanhint,0) fro m seg$ where ts#=:1 and file#=:2 and block#=:3 .01 3.3 0.26 0.26 377 g00cj285jmgsw update sys.mon_mods$ set inserts = inserts + :ins, updates = updates + :upd, del etes = deletes + :del, flags = (decode(bitand(flags, :flag), :flag, flags, flags + :flag)), drop_segments = drop_segments + :dropseg, timestamp = :time where ob j# = :objn .01 2.4 0.31 0.31 330 gnjnsxu3gv5cv Module: SEVERITY EVALUATION SELECT DISTINCT EVAL_FUNC FROM MGMT_METRICS M, MGMT_TARGETS T WHERE METRIC_GUID = :B1 AND M.TYPE_META_VER = T.TYPE_META_VER .01 10.9 0.07 0.07 11 9qgtwh66xg6nz update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,e xtpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decod e(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,: 17),scanhint=:18 where ts#=:1 and file#=:2 and block#=:3 .01 10.9 0.06 0.06 12 bqfx5q2jas08u Module: emagent@hpevo1 (TNS V1-V3) SELECT TASK_LIST.TASK_ID FROM (SELECT /*+ NO_MERGE(T) ORDERED */ T.TASK_ID FROM (SELECT * FROM DBA_ADVISOR_TASKS ORDER BY TASK_ID DESC) T, DBA_ADVISOR_PARAMETER S_PROJ P1, DBA_ADVISOR_PARAMETERS_PROJ P2 WHERE T.ADVISOR_NAME='ADDM' AND T.STAT US = 'COMPLETED' AND T.EXECUTION_START >= (SYSDATE - 1) AND T.HOW_CREATED = 'AUT .01 5.6 0.09 0.05 0 akh9zqqkx3wj7 select 1, max(id) from sys.wri$_adv_objects where task_id = :1 union all select 2, max(id) from sys.wri$_adv_recommendations where task_id = :1 union all sele ct 3, max(id) from sys.wri$_adv_actions where task_id = :1 union all select 4, max(id) from sys.wri$_adv_findings where task_id = :1 union all select 5, max(i ------------------------------------------------------------- Instance Activity Stats DB/Inst: RACDB/racdb1 Snaps: 4-7 Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- CPU used by this session 8,375 0.8 1.7 CPU used when call started 7,271 0.7 1.5 CR blocks created 357 0.0 0.1 DB time 4,311,021 400.3 890.9 DBWR checkpoint buffers written 2,812 0.3 0.6 DBWR fusion writes 429 0.0 0.1 DBWR transaction table writes 334 0.0 0.1 DBWR undo block writes 793 0.1 0.2 DFO trees parallelized 183 0.0 0.0 IPC CPU used by this session 1,660 0.2 0.3 PX local messages recv'd 1,040 0.1 0.2 PX local messages sent 1,040 0.1 0.2 PX remote messages recv'd 474 0.0 0.1 PX remote messages sent 836 0.1 0.2 Parallel operations downgraded t 0 0.0 0.0 Parallel operations not downgrad 183 0.0 0.0 RowCR - row contention 170 0.0 0.0 RowCR attempts 724 0.1 0.2 RowCR hits 554 0.1 0.1 SMON posted for instance recover 0 0.0 0.0 SMON posted for undo segment shr 3 0.0 0.0 SQL*Net roundtrips to/from clien 23,580 2.2 4.9 active txn count during cleanout 130 0.0 0.0 application wait time 58 0.0 0.0 background timeouts 46,940 4.4 9.7 buffer is not pinned count 196,769 18.3 40.7 buffer is pinned count 164,926 15.3 34.1 bytes received via SQL*Net from 8,745,171 812.1 1,807.2 bytes sent via SQL*Net to client 2,671,510 248.1 552.1 calls to get snapshot scn: kcmgs 100,641 9.4 20.8 calls to kcmgas 9,408 0.9 1.9 calls to kcmgcs 156 0.0 0.0 change write time 193 0.0 0.0 cleanout - number of ktugct call 245 0.0 0.1 cleanouts and rollbacks - consis 9 0.0 0.0 cleanouts only - consistent read 34 0.0 0.0 cluster key scan block gets 16,108 1.5 3.3 cluster key scans 13,206 1.2 2.7 cluster wait time 707 0.1 0.2 commit batch performed 0 0.0 0.0 commit batch requested 0 0.0 0.0 commit batch/immediate performed 0 0.0 0.0 commit batch/immediate requested 0 0.0 0.0 commit cleanout failures: block 43 0.0 0.0 commit cleanout failures: callba 14 0.0 0.0 commit cleanouts 9,051 0.8 1.9 commit cleanouts successfully co 8,994 0.8 1.9 commit immediate performed 0 0.0 0.0 commit immediate requested 0 0.0 0.0 commit txn count during cleanout 161 0.0 0.0 concurrency wait time 412 0.0 0.1 consistent changes 1,436 0.1 0.3 consistent gets 364,250 33.8 75.3 consistent gets - examination 146,308 13.6 30.2 consistent gets from cache 362,160 33.6 74.8 cursor authentications 223 0.0 0.1 data blocks consistent reads - u 357 0.0 0.1 db block changes 43,105 4.0 8.9 db block gets 38,092 3.5 7.9 db block gets direct 6 0.0 0.0 Instance Activity Stats DB/Inst: RACDB/racdb1 Snaps: 4-7 Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- db block gets from cache 37,778 3.5 7.8 deferred (CURRENT) block cleanou 5,095 0.5 1.1 enqueue conversions 8,308 0.8 1.7 enqueue releases 389,943 36.2 80.6 enqueue requests 390,016 36.2 80.6 enqueue timeouts 73 0.0 0.0 enqueue waits 1,108 0.1 0.2 execute count 66,581 6.2 13.8 free buffer requested 11,466 1.1 2.4 gc CPU used by this session 520 0.1 0.1 gc cr block build time 6 0.0 0.0 gc cr block flush time 35 0.0 0.0 gc cr block receive time 396 0.0 0.1 gc cr block send time 8 0.0 0.0 gc cr blocks received 2,370 0.2 0.5 gc cr blocks served 1,539 0.1 0.3 gc current block flush time 61 0.0 0.0 gc current block pin time 4 0.0 0.0 gc current block receive time 173 0.0 0.0 gc current block send time 10 0.0 0.0 gc current blocks received 1,600 0.2 0.3 gc current blocks served 1,704 0.2 0.4 gcs messages sent 15,849 1.5 3.3 ges messages sent 9,137 0.9 1.9 global enqueue CPU used by this 589 0.1 0.1 global enqueue get time 711 0.1 0.2 global enqueue gets async 2,115 0.2 0.4 global enqueue gets sync 88,664 8.2 18.3 global enqueue releases 87,620 8.1 18.1 heap block compress 36 0.0 0.0 immediate (CR) block cleanout ap 43 0.0 0.0 immediate (CURRENT) block cleano 1,377 0.1 0.3 index fast full scans (full) 0 0.0 0.0 index fetch by key 92,752 8.6 19.2 index scans kdiixs1 78,905 7.3 16.3 leaf node 90-10 splits 21 0.0 0.0 leaf node splits 57 0.0 0.0 lob reads 367 0.0 0.1 lob writes 557 0.1 0.1 lob writes unaligned 557 0.1 0.1 logons cumulative 482 0.0 0.1 messages received 8,292 0.8 1.7 messages sent 8,292 0.8 1.7 no buffer to keep pinned count 0 0.0 0.0 no work - consistent read gets 139,936 13.0 28.9 opened cursors cumulative 26,766 2.5 5.5 parse count (hard) 1,089 0.1 0.2 parse count (total) 23,076 2.1 4.8 parse time cpu 804 0.1 0.2 parse time elapsed 1,152 0.1 0.2 physical read IO requests 469 0.0 0.1 physical read bytes 6,324,224 587.3 1,306.9 physical read total IO requests 18,919 1.8 3.9 physical read total bytes 308,303,872 28,628.4 63,712.3 physical read total multi block 104 0.0 0.0 physical reads 772 0.1 0.2 physical reads cache 772 0.1 0.2 physical reads cache prefetch 309 0.0 0.1 physical reads direct 0 0.0 0.0 physical reads direct temporary 0 0.0 0.0 Instance Activity Stats DB/Inst: RACDB/racdb1 Snaps: 4-7 Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- physical reads prefetch warmup 297 0.0 0.1 physical write IO requests 2,467 0.2 0.5 physical write bytes 26,181,632 2,431.2 5,410.6 physical write total IO requests 16,444 1.5 3.4 physical write total bytes 162,916,352 15,128.0 33,667.4 physical write total multi block 6,177 0.6 1.3 physical writes 3,196 0.3 0.7 physical writes direct 6 0.0 0.0 physical writes direct (lob) 6 0.0 0.0 physical writes from cache 3,190 0.3 0.7 physical writes non checkpoint 1,423 0.1 0.3 process last non-idle time 0 0.0 0.0 queries parallelized 147 0.0 0.0 recovery blocks read 0 0.0 0.0 recursive calls 312,518 29.0 64.6 recursive cpu usage 5,194 0.5 1.1 redo blocks read for recovery 0 0.0 0.0 redo blocks written 19,102 1.8 4.0 redo entries 24,997 2.3 5.2 redo ordering marks 402 0.0 0.1 redo size 8,490,300 788.4 1,754.6 redo synch time 1,012 0.1 0.2 redo synch writes 16,083 1.5 3.3 redo wastage 976,788 90.7 201.9 redo write time 2,670 0.3 0.6 redo writer latching time 6 0.0 0.0 redo writes 3,402 0.3 0.7 rollback changes - undo records 0 0.0 0.0 rollbacks only - consistent read 345 0.0 0.1 rows fetched via callback 41,032 3.8 8.5 session connect time 0 0.0 0.0 session cursor cache hits 12,223 1.1 2.5 session logical reads 402,342 37.4 83.2 session pga memory 618,883,024 57,468.0 127,894.8 session pga memory max 1,159,284,452 107,648.4 239,571.1 session uga memory 738,747,040,552 68,598,282.0 ############# session uga memory max 269,023,444 24,980.9 55,594.8 shared hash latch upgrades - no 65,667 6.1 13.6 sorts (memory) 12,647 1.2 2.6 sorts (rows) 146,277 13.6 30.2 sql area purged 0 0.0 0.0 switch current to new buffer 315 0.0 0.1 table fetch by rowid 109,774 10.2 22.7 table fetch continued row 264 0.0 0.1 table scan blocks gotten 26,226 2.4 5.4 table scan rows gotten 529,759 49.2 109.5 table scans (long tables) 4 0.0 0.0 table scans (short tables) 3,497 0.3 0.7 total number of times SMON poste 23 0.0 0.0 transaction lock background gets 0 0.0 0.0 transaction lock foreground requ 0 0.0 0.0 transaction lock foreground wait 0 0.0 0.0 transaction rollbacks 0 0.0 0.0 undo change vector size 2,611,416 242.5 539.7 user I/O wait time 684 0.1 0.1 user calls 26,055 2.4 5.4 user commits 2,669 0.3 0.6 user rollbacks 2,170 0.2 0.5 workarea executions - optimal 7,481 0.7 1.6 ------------------------------------------------------------- Instance Activity Stats - Absolute Values DB/Inst: RACDB/racdb1 Snaps: 4-7 -> Statistics with absolute values (should not be diffed) Statistic Begin Value End Value -------------------------------- --------------- --------------- session cursor cache count 1,441 5,544 opened cursors current 170 170 logons current 35 35 ------------------------------------------------------------- Instance Activity Stats - Thread Activity DB/Inst: RACDB/racdb1 Snaps: 4-7 -> Statistics identified by '(derived)' come from sources other than SYSSTAT Statistic Total per Hour -------------------------------- ------------------ --------- log switches (derived) 0 .00 ------------------------------------------------------------- Tablespace IO Stats DB/Inst: RACDB/racdb1 Snaps: 4-7 -> ordered by IOs (Reads + Writes) desc Tablespace ------------------------------ Av Av Av Av Buffer Av Buf Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms) -------------- ------- ------ ------- ------------ -------- ---------- ------ SYSAUX 375 0 11.3 1.8 1,519 0 0 0.0 UNDOTBS1 1 0 40.0 1.0 697 0 1 0.0 SYSTEM 199 0 16.6 1.0 251 0 0 0.0 ------------------------------------------------------------- File IO Stats DB/Inst: RACDB/racdb1 Snaps: 4-7 -> ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------- Av Av Av Av Buffer Av Buf Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms) -------------- ------- ------ ------- ------------ -------- ---------- ------ SYSAUX +RACDB_DATA1/racdb/datafile/sysaux.261.707585893 375 0 11.3 1.8 1,519 0 0 0.0 SYSTEM +RACDB_DATA1/racdb/datafile/system.259.707585839 199 0 16.6 1.0 251 0 0 0.0 UNDOTBS1 +RACDB_DATA1/racdb/datafile/undotbs1.260.707585877 1 0 40.0 1.0 697 0 1 0.0 ------------------------------------------------------------- Buffer Pool Statistics DB/Inst: RACDB/racdb1 Snaps: 4-7 -> Standard block size Pools D: default, K: keep, R: recycle -> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k Free Writ Buffer Number of Pool Buffer Physical Physical Buff Comp Busy P Buffers Hit% Gets Reads Writes Wait Wait Waits --- ---------- ---- -------------- ------------ ----------- ---- ---- ---------- D 20,496 100 399,316 806 3,190 0 0 1 ------------------------------------------------------------- Instance Recovery Stats DB/Inst: RACDB/racdb1 Snaps: 4-7 -> B: Begin snapshot, E: End snapshot Targt Estd Log File Log Ckpt Log Ckpt MTTR MTTR Recovery Actual Target Size Timeout Interval (s) (s) Estd IOs Redo Blks Redo Blks Redo Blks Redo Blks Redo Blks - ----- ----- ---------- --------- --------- ---------- --------- ------------ B 0 31 357 4737 9838 92160 9838 N/A E 0 30 230 691 2105 92160 2105 N/A ------------------------------------------------------------- Buffer Pool Advisory DB/Inst: RACDB/racdb1 Snap: 7 -> Only rows with estimated physical reads >0 are displayed -> ordered by Block Size, Buffers For Estimate Est Phys Size for Size Buffers for Read Estimated P Est (M) Factor Estimate Factor Physical Reads --- -------- ------ ---------------- ------ ------------------ D 16 .1 1,952 1.7 15,491 D 32 .2 3,904 1.3 11,420 D 48 .3 5,856 1.1 9,502 D 64 .4 7,808 1.0 9,023 D 80 .5 9,760 1.0 8,915 D 96 .6 11,712 1.0 8,915 D 112 .7 13,664 1.0 8,915 D 128 .8 15,616 1.0 8,915 D 144 .9 17,568 1.0 8,915 D 160 1.0 19,520 1.0 8,915 D 168 1.0 20,496 1.0 8,915 D 176 1.0 21,472 1.0 8,915 D 192 1.1 23,424 1.0 8,915 D 208 1.2 25,376 1.0 8,915 D 224 1.3 27,328 1.0 8,915 D 240 1.4 29,280 1.0 8,915 D 256 1.5 31,232 1.0 8,915 D 272 1.6 33,184 1.0 8,915 D 288 1.7 35,136 1.0 8,915 D 304 1.8 37,088 1.0 8,915 D 320 1.9 39,040 0.8 7,486 ------------------------------------------------------------- PGA Aggr Summary DB/Inst: RACDB/racdb1 Snaps: 4-7 -> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written --------------- ------------------ -------------------------- 100.0 205 0 ------------------------------------------------------------- PGA Aggr Target Stats DB/Inst: RACDB/racdb1 Snaps: 4-7 -> 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 %PGA %Auto %Man PGA Aggr Auto PGA PGA Mem W/A PGA W/A W/A W/A Global Mem Target(M) Target(M) Alloc(M) Used(M) Mem Mem Mem Bound(K) - ---------- ---------- ---------- ---------- ------ ------ ------ ---------- B 90 10 181.7 0.0 .0 .0 .0 18,432 E 90 9 172.0 0.0 .0 .0 .0 18,432 ------------------------------------------------------------- PGA Aggr Target Histogram DB/Inst: RACDB/racdb1 Snaps: 4-7 -> Optimal Executions are purely in-memory operations Low High Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs ------- ------- -------------- -------------- ------------ ------------ 2K 4K 7,223 7,223 0 0 64K 128K 3 3 0 0 512K 1024K 252 252 0 0 1M 2M 3 3 0 0 ------------------------------------------------------------- PGA Memory Advisory DB/Inst: RACDB/racdb1 Snap: 7 -> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value where Estd PGA Overalloc Count is 0 Estd Extra Estd PGA Estd PGA PGA Target Size W/A MB W/A MB Read/ Cache Overalloc Est (MB) Factr Processed Written to Disk Hit % Count ---------- ------- ---------------- ---------------- -------- ---------- 11 0.1 285.3 24.3 92.0 7 23 0.3 285.3 24.3 92.0 7 45 0.5 285.3 24.3 92.0 7 68 0.8 285.3 24.3 92.0 7 90 1.0 285.3 0.0 100.0 1 108 1.2 285.3 0.0 100.0 0 126 1.4 285.3 0.0 100.0 0 144 1.6 285.3 0.0 100.0 0 162 1.8 285.3 0.0 100.0 0 180 2.0 285.3 0.0 100.0 0 270 3.0 285.3 0.0 100.0 0 360 4.0 285.3 0.0 100.0 0 540 6.0 285.3 0.0 100.0 0 720 8.0 285.3 0.0 100.0 0 ------------------------------------------------------------- Shared Pool Advisory DB/Inst: RACDB/racdb1 Snap: 7 -> 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. Est LC Est LC Est LC Est LC Shared SP Est LC Time Time Load Load Est LC Pool Size Size Est LC Saved Saved Time Time Mem Size(M) Factr (M) Mem Obj (s) Factr (s) Factr Obj Hits ---------- ----- -------- ------------ ------- ------ ------- ------ ----------- 80 .9 12 1,744 5,412 1.0 78 1.6 479,449 92 1.0 23 3,101 5,440 1.0 50 1.0 483,442 104 1.1 35 4,537 5,445 1.0 45 .9 484,141 116 1.3 46 6,104 5,446 1.0 44 .9 484,366 128 1.4 57 7,616 5,446 1.0 44 .9 484,394 140 1.5 66 8,834 5,446 1.0 44 .9 484,395 152 1.7 66 8,834 5,446 1.0 44 .9 484,395 164 1.8 66 8,834 5,446 1.0 44 .9 484,395 176 1.9 66 8,834 5,446 1.0 44 .9 484,395 188 2.0 66 8,834 5,446 1.0 44 .9 484,395 ------------------------------------------------------------- SGA Target Advisory DB/Inst: RACDB/racdb1 Snap: 7 SGA Target SGA Size Est DB Est Physical Size (M) Factor Time (s) Reads ---------- ---------- ------------ ---------------- 136 0.5 533 11,426 204 0.8 505 9,023 272 1.0 504 8,915 340 1.3 504 8,915 408 1.5 504 8,915 476 1.8 491 7,483 544 2.0 491 7,483 ------------------------------------------------------------- Streams Pool Advisory DB/Inst: RACDB/racdb1 Snap: 7 No data exists for this section of the report. ------------------------------------------------------------- Java Pool Advisory DB/Inst: RACDB/racdb1 Snap: 7 No data exists for this section of the report. ------------------------------------------------------------- Buffer Wait Statistics DB/Inst: RACDB/racdb1 Snaps: 4-7 -> ordered by wait time desc, waits desc Class Waits Total Wait Time (s) Avg Time (ms) ------------------ ----------- ------------------- -------------- undo header 1 0 0 ------------------------------------------------------------- Enqueue Activity DB/Inst: RACDB/racdb1 Snaps: 4-7 -> 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 Enqueue Type (Request Reason) ------------------------------------------------------------------------------ Requests Succ Gets Failed Gets Waits Wt Time (s) Av Wt Time(ms) ------------ ------------ ----------- ----------- ------------ -------------- US-Undo Segment 724 724 0 607 0 .58 PS-PX Process Reservation 1,328 1,255 73 361 0 .83 FU-DBFUS 2 2 0 1 0 60.00 TM-DML 34,807 34,807 0 36 0 .83 TA-Instance Undo 36 36 0 29 0 1.03 WF-AWR Flush 85 85 0 14 0 2.14 HW-Segment High Water Mark 243 243 0 5 0 4.00 TD-KTF map table enqueue (KTF dump entries) 20 20 0 20 0 .50 TT-Tablespace 196 196 0 16 0 .63 DR-Distributed Recovery 6 6 0 6 0 1.67 RS-Reclaimable Space (prevent aging list update) 12 12 0 4 0 2.50 JS-Job Scheduler (job run lock - synchronize) 6 6 0 4 0 .00 UL-User-defined 24 24 0 3 0 .00 PI-Remote PX Process Spawn Status 4 4 0 2 0 .00 AF-Advisor Framework (task serialization) 14 14 0 1 0 .00 PE-Parameter 496 496 0 1 0 .00 PG-Global Parameter 4 4 0 1 0 .00 TX-Transaction (allocate ITL entry) 1 1 0 1 0 .00 TX-Transaction (index contention) 2 2 0 1 0 .00 ------------------------------------------------------------- Undo Segment Summary DB/Inst: RACDB/racdb1 Snaps: 4-7 -> Min/Max TR (mins) - Min and Max Tuned Retention (minutes) -> STO - Snapshot Too Old count, OOS - Out of Space count -> Undo segment block stats: -> uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed -> eS - expired Stolen, eR - expired Released, eU - expired reUsed Undo Num Undo Number of Max Qry Max Tx Min/Max STO/ uS/uR/uU/ TS# Blocks (K) Transactions Len (s) Concurcy TR (mins) OOS eS/eR/eU ---- ---------- --------------- -------- -------- --------- ----- -------------- 1 .6 4,412 0 3 15/15 0/0 0/0/0/0/0/0 ------------------------------------------------------------- Undo Segment Stats DB/Inst: RACDB/racdb1 Snaps: 4-7 -> Most recent 35 Undostat rows, ordered by Time desc Num Undo Number of Max Qry Max Tx Tun Ret STO/ uS/uR/uU/ End Time Blocks Transactions Len (s) Concy (mins) OOS eS/eR/eU ------------ ----------- ------------ ------- ------- ------- ----- ------------ 28-Feb 15:52 13 160 0 2 15 0/0 0/0/0/0/0/0 28-Feb 15:42 13 179 0 3 15 0/0 0/0/0/0/0/0 28-Feb 15:32 9 152 0 1 15 0/0 0/0/0/0/0/0 28-Feb 15:22 16 355 0 2 15 0/0 0/0/0/0/0/0 28-Feb 15:12 8 150 0 3 15 0/0 0/0/0/0/0/0 28-Feb 15:02 65 304 0 3 15 0/0 0/0/0/0/0/0 28-Feb 14:52 15 175 0 3 15 0/0 0/0/0/0/0/0 28-Feb 14:42 10 204 0 2 15 0/0 0/0/0/0/0/0 28-Feb 14:32 13 143 0 3 15 0/0 0/0/0/0/0/0 28-Feb 14:22 16 419 0 1 15 0/0 0/0/0/0/0/0 28-Feb 14:12 8 169 0 1 15 0/0 0/0/0/0/0/0 28-Feb 14:02 74 322 0 3 15 0/0 0/0/0/0/0/0 28-Feb 13:52 8 157 0 1 15 0/0 0/0/0/0/0/0 28-Feb 13:42 14 299 0 1 15 0/0 0/0/0/0/0/0 28-Feb 13:32 8 175 0 1 15 0/0 0/0/0/0/0/0 28-Feb 13:22 19 360 0 2 15 0/0 0/0/0/0/0/0 28-Feb 13:12 10 172 0 2 15 0/0 0/0/0/0/0/0 28-Feb 13:02 292 517 0 3 15 0/0 0/0/0/0/0/0 ------------------------------------------------------------- Latch Activity DB/Inst: RACDB/racdb1 Snaps: 4-7 -> "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 Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Latch Name Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ ASM allocation 52 0.0 N/A 0 0 N/A ASM map headers 24 0.0 N/A 0 0 N/A ASM map load waiting lis 4 0.0 N/A 0 0 N/A ASM map operation freeli 95 0.0 N/A 0 0 N/A ASM map operation hash t 126,744 0.0 N/A 0 0 N/A ASM network background l 4,333 0.0 N/A 0 0 N/A AWR Alerted Metric Eleme 60,101 0.0 N/A 0 0 N/A Consistent RBA 3,402 0.0 N/A 0 0 N/A FAL request queue 216 0.0 N/A 0 0 N/A FAL subheap alocation 216 0.0 N/A 0 0 N/A FIB s.o chain latch 8 0.0 N/A 0 0 N/A FOB s.o list latch 146 0.0 N/A 0 0 N/A JS broadcast add buf lat 7,903 0.0 N/A 0 0 N/A JS broadcast drop buf la 7,906 0.0 N/A 0 0 N/A JS broadcast load blnc l 7,595 0.0 N/A 0 0 N/A JS mem alloc latch 17 0.0 N/A 0 2 0.0 JS queue access latch 17 0.0 N/A 0 0 N/A JS queue state obj latch 319,104 0.0 N/A 0 0 N/A JS slv state obj latch 30 0.0 N/A 0 0 N/A KCL gc element parent la 36,286 0.0 N/A 0 1,723 0.0 KFK SGA context latch 7,263 0.0 N/A 0 0 N/A KFMD SGA 56 0.0 N/A 0 0 N/A KJC destination ctx free 0 N/A N/A 0 2 0.0 KJC message pool free li 6,819 0.0 N/A 0 792 0.0 KJCT flow control latch 38,184 0.0 N/A 0 0 N/A KMG MMAN ready and start 3,594 0.0 N/A 0 0 N/A KMG resize request state 4 0.0 N/A 0 0 N/A KSXR large replies 724 0.0 N/A 0 0 N/A KTF sga latch 40 0.0 N/A 0 3,037 0.0 KWQMN job cache list lat 2,166 0.3 1.0 0 0 N/A MQL Tracking Latch 0 N/A N/A 0 215 0.0 Memory Management Latch 60 1.7 1.0 0 3,594 0.0 OS process 549 0.0 N/A 0 0 N/A OS process allocation 3,955 0.0 N/A 0 0 N/A OS process: request allo 238 0.0 N/A 0 0 N/A PL/SQL warning settings 2,111 0.0 N/A 0 0 N/A SGA IO buffer pool latch 5 0.0 N/A 0 5 0.0 SQL memory manager latch 27 0.0 N/A 0 3,576 0.0 SQL memory manager worka 246,691 0.0 N/A 0 0 N/A Shared B-Tree 398 0.0 N/A 0 0 N/A active checkpoint queue 6,183 0.0 N/A 0 0 N/A active service list 76,055 0.0 N/A 0 3,677 0.0 archive control 407 0.0 N/A 0 0 N/A archive process latch 3,792 0.0 N/A 0 0 N/A begin backup scn array 6 0.0 N/A 0 0 N/A buffer pool 4 0.0 N/A 0 0 N/A business card 366 0.0 N/A 0 0 N/A cache buffer handles 145 0.0 N/A 0 0 N/A cache buffers chains 771,722 0.0 N/A 0 27,566 0.0 cache buffers lru chain 37,662 0.0 N/A 0 20,698 0.0 cache table scan latch 0 N/A N/A 0 3 0.0 channel handle pool latc 250 0.0 N/A 0 0 N/A channel operations paren 508,146 0.0 N/A 0 0 N/A checkpoint queue latch 84,662 0.0 N/A 0 3,792 0.0 client/application info 6,169 0.0 N/A 0 0 N/A commit callback allocati 894 0.0 N/A 0 0 N/A compile environment latc 12,546 0.0 N/A 0 0 N/A dml lock allocation 69,789 0.0 1.0 0 0 N/A dummy allocation 984 0.0 N/A 0 0 N/A enqueue hash chains 864,244 0.0 1.0 0 233 0.0 Latch Activity DB/Inst: RACDB/racdb1 Snaps: 4-7 -> "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 Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Latch Name Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ enqueues 713,917 0.0 N/A 0 0 N/A error message lists 843 0.0 N/A 0 0 N/A event group latch 119 0.0 N/A 0 0 N/A file cache latch 610 0.0 N/A 0 0 N/A gcs drop object freelist 156 0.0 N/A 0 0 N/A gcs opaque info freelist 5,084 0.0 N/A 0 0 N/A gcs partitioned table ha 55,943 0.0 N/A 0 0 N/A gcs remastering latch 32 0.0 N/A 0 0 N/A gcs resource freelist 858 0.0 N/A 0 0 N/A gcs resource hash 30,570 0.0 1.0 0 6 0.0 gcs resource scan list 12 0.0 N/A 0 0 N/A gcs shadows freelist 3,916 0.0 N/A 0 0 N/A ges caches resource list 74,466 0.0 N/A 0 111,536 0.0 ges deadlock list 1,874 0.0 N/A 0 0 N/A ges domain table 183,468 0.0 N/A 0 0 N/A ges enqueue table freeli 187,847 0.0 N/A 0 0 N/A ges group table 260,442 0.0 N/A 0 0 N/A ges process hash list 6,048 0.0 N/A 0 0 N/A ges process parent latch 373,352 0.0 N/A 0 0 N/A ges process table freeli 238 0.0 N/A 0 0 N/A ges resource hash list 260,303 0.0 1.0 0 7,946 0.0 ges resource scan list 68 0.0 N/A 0 0 N/A ges resource table freel 10,876 0.0 1.0 0 1 0.0 ges timeout list 3,466 0.0 N/A 0 3,051 0.0 global KZLD latch for me 87 0.0 N/A 0 0 N/A hash table column usage 1,261 0.0 N/A 0 27,430 0.0 hash table modification 188 0.0 N/A 0 0 N/A job workq parent latch 0 N/A N/A 0 344 0.0 job_queue_processes free 0 N/A N/A 0 2 0.0 job_queue_processes para 352 0.0 N/A 0 0 N/A kks stats 3,185 0.0 N/A 0 0 N/A ksuosstats global area 724 0.0 N/A 0 0 N/A ktm global data 46 0.0 N/A 0 0 N/A kwqbsn:qsga 398 0.0 N/A 0 0 N/A lgwr LWN SCN 5,635 0.0 N/A 0 0 N/A library cache 553,025 0.0 1.0 0 1,249 0.0 library cache load lock 5,178 0.0 N/A 0 0 N/A library cache lock 244,342 0.0 N/A 0 0 N/A library cache lock alloc 6,863 0.0 N/A 0 0 N/A library cache pin 360,969 0.0 N/A 0 2 0.0 library cache pin alloca 3,286 0.0 N/A 0 0 N/A list of block allocation 443 0.0 N/A 0 0 N/A loader state object free 20 0.0 N/A 0 0 N/A message pool operations 3,370 0.0 N/A 0 0 N/A messages 333,800 0.0 N/A 0 0 N/A mostly latch-free SCN 5,635 0.0 N/A 0 0 N/A msg queue 8 0.0 N/A 0 8 0.0 multiblock read objects 5,012 0.0 N/A 0 0 N/A name-service memory obje 1,964 0.0 N/A 0 0 N/A name-service namespace b 108,225 0.0 N/A 0 0 N/A name-service pending que 1,202 0.0 N/A 0 0 N/A name-service request 60 0.0 N/A 0 0 N/A name-service request que 107,274 0.0 N/A 0 0 N/A ncodef allocation latch 175 0.0 N/A 0 0 N/A object queue header heap 78 0.0 N/A 0 1 0.0 object queue header oper 38,005 0.0 N/A 0 0 N/A object stats modificatio 85 0.0 N/A 0 2 0.0 parallel query alloc buf 5,112 0.0 N/A 0 0 N/A parallel query stats 714 0.0 N/A 0 0 N/A parallel txn reco latch 720 0.0 N/A 0 0 N/A Latch Activity DB/Inst: RACDB/racdb1 Snaps: 4-7 -> "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 Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Latch Name Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ parameter table allocati 502 0.0 N/A 0 0 N/A post/wait queue 2,315 0.0 N/A 0 1,338 0.0 process allocation 105,844 0.0 N/A 0 119 0.0 process group creation 238 0.0 N/A 0 0 N/A process queue 2,003 0.0 N/A 0 0 N/A process queue reference 30,983 0.0 N/A 0 3,420 ###### qmn state object latch 26 0.0 N/A 0 0 N/A qmn task queue latch 1,643 1.3 1.0 0 0 N/A query server freelists 1,667 0.0 N/A 0 0 N/A query server process 10 0.0 N/A 0 9 11.1 queued dump request 53,757 0.0 N/A 0 0 N/A redo allocation 9,037 0.0 1.0 0 25,007 0.0 redo copy 0 N/A N/A 0 25,007 0.2 redo writing 23,938 0.0 N/A 0 0 N/A resmgr group change latc 1,249 0.0 N/A 0 0 N/A resmgr:actses active lis 943 0.0 N/A 0 0 N/A resmgr:actses change gro 460 0.0 N/A 0 0 N/A resmgr:free threads list 940 0.0 N/A 0 0 N/A resmgr:schema config 3 0.0 N/A 0 0 N/A row cache objects 324,006 0.0 N/A 0 213 0.0 rules engine aggregate s 98 0.0 N/A 0 0 N/A rules engine rule set st 196 0.0 N/A 0 0 N/A segmented array pool 8 0.0 N/A 0 0 N/A sequence cache 786 0.0 N/A 0 0 N/A session allocation 303,709 0.0 1.0 0 0 N/A session idle bit 64,202 0.0 N/A 0 0 N/A session state list latch 898 0.0 N/A 0 0 N/A session switching 175 0.0 N/A 0 0 N/A session timer 3,677 0.0 N/A 0 0 N/A shared pool 130,155 0.0 1.0 0 0 N/A simulator hash latch 22,811 0.0 N/A 0 0 N/A simulator lru latch 21,968 0.0 N/A 0 554 0.0 slave class 46 0.0 N/A 0 0 N/A slave class create 103 1.0 1.0 0 0 N/A sort extent pool 329 0.0 N/A 0 0 N/A state object free list 8 0.0 N/A 0 0 N/A statistics aggregation 504 0.0 N/A 0 0 N/A temp lob duration state 2 0.0 N/A 0 0 N/A threshold alerts latch 740 0.0 N/A 0 0 N/A transaction allocation 86,408 0.0 N/A 0 0 N/A transaction branch alloc 175 0.0 N/A 0 0 N/A undo global data 15,048 0.0 N/A 0 0 N/A user lock 698 0.0 N/A 0 0 N/A ------------------------------------------------------------- Latch Sleep Breakdown DB/Inst: RACDB/racdb1 Snaps: 4-7 -> ordered by misses desc Latch Name ---------------------------------------- Get Requests Misses Sleeps Spin Gets Sleep1 Sleep2 Sleep3 -------------- ----------- ----------- ---------- -------- -------- -------- ges resource hash list 260,303 27 27 0 0 0 0 qmn task queue latch 1,643 21 22 0 0 0 0 KWQMN job cache list latch 2,166 6 6 0 0 0 0 enqueue hash chains 864,244 4 4 0 0 0 0 library cache 553,025 4 4 0 0 0 0 redo allocation 9,037 4 4 0 0 0 0 session allocation 303,709 4 4 0 0 0 0 shared pool 130,155 3 3 0 0 0 0 gcs resource hash 30,570 2 2 0 0 0 0 Memory Management Latch 60 1 1 0 0 0 0 dml lock allocation 69,789 1 1 0 0 0 0 ges resource table freelist 10,876 1 1 0 0 0 0 slave class create 103 1 1 0 0 0 0 ------------------------------------------------------------- Latch Miss Sources DB/Inst: RACDB/racdb1 Snaps: 4-7 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- -------- KWQMN job cache list lat kwqmnuji: update job item 0 6 0 Memory Management Latch kmgs_perform_parameter_upd 0 1 1 dml lock allocation ktaidm 0 1 0 enqueue hash chains ksqgtl3 0 3 2 enqueue hash chains ksqrcl 0 1 2 event range base latch No latch 0 22 22 gcs resource hash kjbcrcomplete 0 1 0 gcs resource hash kjbcropen:affinity 0 1 0 ges resource hash list kjrmas1: lookup master nod 0 24 0 ges resource hash list kjlrlr: remove lock from r 0 2 7 ges resource hash list kjucvl: lock convert reque 0 1 21 ges resource table freel kjralc: resource allocatio 0 1 0 library cache kglobpn: child: 0 2 3 library cache kglpndl: child: after proc 0 1 0 redo allocation kcrfw_redo_gen: redo alloc 0 4 0 session allocation ksudlc 0 1 0 session allocation ksuprc 0 1 2 session allocation ksuxds: KSUSFCLC not set 0 1 0 session allocation ksuxds: not user session 0 1 1 shared pool kghupr1 0 2 1 shared pool kghalo 0 1 2 shared pool kghfrunp: clatch: nowait 0 1 0 slave class create ksvcreate 0 1 0 ------------------------------------------------------------- Parent Latch Statistics DB/Inst: RACDB/racdb1 Snaps: 4-7 No data exists for this section of the report. ------------------------------------------------------------- Child Latch Statistics DB/Inst: RACDB/racdb1 Snaps: 4-7 No data exists for this section of the report. ------------------------------------------------------------- Segments by Logical Reads DB/Inst: RACDB/racdb1 Snaps: 4-7 -> Total Logical Reads: 402,342 -> Captured Segments account for 74.4% of Total Tablespace Subobject Obj. Logical Owner Name Object Name Name Type Reads %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- SYS SYSAUX SYS_IOT_TOP_8803 INDEX 42,240 10.50 SYS SYSTEM I_OBJ# INDEX 26,656 6.63 SYSMAN SYSAUX MGMT_JOB_EXEC_IDX01 INDEX 20,672 5.14 SYS SYSAUX ALERT_QT TABLE 16,608 4.13 SYS SYSTEM SYS_C00648 INDEX 16,032 3.98 ------------------------------------------------------------- Segments by Physical Reads DB/Inst: RACDB/racdb1 Snaps: 4-7 -> Total Physical Reads: 772 -> Captured Segments account for 40.4% of Total Tablespace Subobject Obj. Physical Owner Name Object Name Name Type Reads %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- SYS SYSAUX WRH$_SQL_PLAN_PK INDEX 24 3.11 SYS SYSTEM IDL_UB1$ TABLE 19 2.46 SYS SYSAUX WRH$_LATCH_PK 05202569_0 INDEX 16 2.07 SYS SYSAUX WRH$_PARAMETER 05202569_0 TABLE 16 2.07 SYS SYSAUX WRH$_SEG_STAT 05202569_0 TABLE 16 2.07 ------------------------------------------------------------- Segments by Row Lock Waits DB/Inst: RACDB/racdb1 Snaps: 4-7 -> % of Capture shows % of row lock waits for each top segment compared -> with total row lock waits for all segments captured by the Snapshot Row Tablespace Subobject Obj. Lock % of Owner Name Object Name Name Type Waits Capture ---------- ---------- -------------------- ---------- ----- ------------ ------- SYS SYSTEM SMON_SCN_TIME TABLE 8 80.00 SYSMAN SYSAUX MGMT_SYSTEM_PERF_LOG INDEX 2 20.00 ------------------------------------------------------------- Segments by ITL Waits DB/Inst: RACDB/racdb1 Snaps: 4-7 -> % of Capture shows % of ITL waits for each top segment compared -> with total ITL waits for all segments captured by the Snapshot Tablespace Subobject Obj. ITL % of Owner Name Object Name Name Type Waits Capture ---------- ---------- -------------------- ---------- ----- ------------ ------- SYS SYSAUX SYS_IOT_TOP_8798 INDEX 1 100.00 ------------------------------------------------------------- Segments by Buffer Busy Waits DB/Inst: RACDB/racdb1 Snaps: 4-7 No data exists for this section of the report. ------------------------------------------------------------- Segments by Global Cache Buffer Busy DB/Inst: RACDB/racdb1 Snaps: 4-7 No data exists for this section of the report. ------------------------------------------------------------- Segments by CR Blocks Received DB/Inst: RACDB/racdb1 Snaps: 4-7 -> Total CR Blocks Received: 2,370 -> Captured Segments account for 91.1% of Total CR Tablespace Subobject Obj. Blocks Owner Name Object Name Name Type Received %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- SYS SYSAUX ALERT_QT TABLE 323 13.63 SYS SYSAUX SYS_C002066 INDEX 257 10.84 SYS SYSAUX SYS_IOT_TOP_8803 INDEX 251 10.59 SYSMAN SYSAUX SEVERITY_PRIMARY_KEY INDEX 250 10.55 SYSMAN SYSAUX MGMT_METRICS_RAW_PK INDEX 191 8.06 ------------------------------------------------------------- Segments by Current Blocks Received DB/Inst: RACDB/racdb1 Snaps: 4-7 -> Total Current Blocks Received: 1,600 -> Captured Segments account for 83.7% of Total Current Tablespace Subobject Obj. Blocks Owner Name Object Name Name Type Received %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- SYS SYSAUX SYS_IOT_TOP_8803 INDEX 250 15.63 SYS SYSAUX SYS_IOT_TOP_8798 INDEX 153 9.56 SYS SYSTEM SEG$ TABLE 84 5.25 SYS SYSAUX ALERT_QT TABLE 78 4.88 SYS SYSAUX SYS_IOT_TOP_8796 INDEX 78 4.88 ------------------------------------------------------------- Dictionary Cache Stats DB/Inst: RACDB/racdb1 Snaps: 4-7 -> "Pct Misses" should be very low (< 2% in most cases) -> "Final Usage" is the number of cache entries being used Get Pct Scan Pct Mod Final Cache Requests Miss Reqs Miss Reqs Usage ------------------------- ------------ ------ ------- ----- -------- ---------- dc_awr_control 206 0.0 0 N/A 6 1 dc_files 10 0.0 0 N/A 0 5 dc_global_oids 16,878 0.0 0 N/A 0 60 dc_histogram_data 1,902 3.7 0 N/A 0 408 dc_histogram_defs 11,576 7.4 0 N/A 0 3,596 dc_object_grants 84 0.0 0 N/A 0 72 dc_object_ids 24,213 0.5 0 N/A 0 1,067 dc_objects 3,845 3.9 0 N/A 15 1,202 dc_profiles 265 0.0 0 N/A 0 1 dc_rollback_segments 1,975 0.0 0 N/A 0 22 dc_segments 2,073 5.8 0 N/A 15 782 dc_sequences 165 9.1 0 N/A 165 5 dc_tablespace_quotas 1 100.0 0 N/A 0 1 dc_tablespaces 9,621 0.0 0 N/A 0 6 dc_usernames 1,062 0.2 0 N/A 0 10 dc_users 30,714 0.0 0 N/A 0 36 outstanding_alerts 337 59.9 0 N/A 98 5 ------------------------------------------------------------- Dictionary Cache Stats (RAC) DB/Inst: RACDB/racdb1 Snaps: 4-7 GES GES GES Cache Requests Conflicts Releases ------------------------- ------------ ------------ ------------ dc_awr_control 12 0 0 dc_global_oids 8 0 0 dc_histogram_defs 861 0 151 dc_object_ids 124 0 6 dc_objects 180 0 131 dc_segments 148 29 15 dc_sequences 328 15 0 dc_tablespace_quotas 2 1 0 dc_usernames 2 0 0 dc_users 2 0 0 outstanding_alerts 588 153 0 ------------------------------------------------------------- Library Cache Activity DB/Inst: RACDB/racdb1 Snaps: 4-7 -> "Pct Misses" should be very low Get Pct Pin Pct Invali- Namespace Requests Miss Requests Miss Reloads dations --------------- ------------ ------ -------------- ------ ---------- -------- BODY 2,724 0.3 18,090 0.2 31 0 CLUSTER 65 0.0 121 0.8 1 0 INDEX 65 18.5 67 94.0 51 0 SQL AREA 1,795 13.8 77,844 2.1 806 0 TABLE/PROCEDURE 4,101 4.5 37,820 6.3 1,230 0 TRIGGER 40 0.0 559 0.7 4 0 ------------------------------------------------------------- Library Cache Activity (RAC) DB/Inst: RACDB/racdb1 Snaps: 4-7 GES Lock GES Pin GES Pin GES Inval GES Invali- Namespace Requests Requests Releases Requests dations --------------- ------------ ------------ ------------ ----------- ----------- CLUSTER 121 0 0 0 0 INDEX 67 12 31 12 0 TABLE/PROCEDURE 13,535 225 58 116 0 ------------------------------------------------------------- Process Memory Summary DB/Inst: RACDB/racdb1 Snaps: 4-7 -> B: Begin snap E: End snap -> All rows below contain absolute values (i.e. not diffed over the interval) -> Max Alloc is Maximum PGA Allocation size at snapshot time -> Hist Max Alloc is the Historical Max Allocation for still-connected processes -> ordered by Begin/End snapshot, Alloc (MB) desc Hist Avg Std Dev Max Max Alloc Used Alloc Alloc Alloc Alloc Num Num Category (MB) (MB) (MB) (MB) (MB) (MB) Proc Alloc - -------- --------- --------- -------- -------- ------- ------- ------ ------ B Other 170.4 N/A 4.4 6.3 24 24 39 39 Freeable 10.0 .0 .6 .4 1 N/A 18 18 SQL 1.0 .4 .0 .0 0 2 21 17 PL/SQL .4 .2 .0 .0 0 0 37 36 E Other 161.2 N/A 4.1 6.4 24 24 39 39 Freeable 9.4 .0 .5 .3 1 N/A 18 18 SQL 1.0 .4 .0 .0 0 2 21 17 PL/SQL .4 .2 .0 .0 0 0 37 36 ------------------------------------------------------------- SGA Memory Summary DB/Inst: RACDB/racdb1 Snaps: 4-7 End Size (Bytes) SGA regions Begin Size (Bytes) (if different) ------------------------------ ------------------- ------------------- Database Buffers 176,160,768 Fixed Size 1,218,992 Redo Buffers 2,973,696 Variable Size 104,859,216 ------------------- sum 285,212,672 ------------------------------------------------------------- SGA breakdown difference DB/Inst: RACDB/racdb1 Snaps: 4-7 -> ordered by Pool, Name -> N/A value for Begin MB or End MB indicates the size of that Pool/Name was insignificant, or zero in that snapshot Pool Name Begin MB End MB % Diff ------ ------------------------------ -------------- -------------- ------- java free memory 4.0 4.0 0.00 large ASM map operations hashta .2 .2 0.00 large PX msg pool .3 .3 0.00 large free memory 3.5 3.5 0.00 shared ASH buffers 2.0 2.0 0.00 shared CCursor 2.0 1.8 -7.75 shared Heap0: KGL 1.4 1.3 -7.94 shared KCB Table Scan Buffer 3.8 3.8 0.00 shared KGLS heap 1.7 1.7 2.75 shared KQR M PO 3.4 4.0 16.60 shared KSFD SGA I/O b 3.8 3.8 0.00 shared KSXR receive buffers 1.0 1.0 0.00 shared PCursor 1.4 1.4 -4.56 shared PL/SQL DIANA 1.7 1.1 -34.65 shared PL/SQL MPCODE 1.8 2.2 22.73 shared dbwriter coalesce buffer 1.0 1.0 0.00 shared event statistics per sess 1.3 1.3 0.00 shared free memory 8.7 3.8 -55.98 shared gcs resources 2.2 2.2 0.00 shared gcs shadows 1.6 1.6 0.00 shared ges big msg buffers 3.8 3.8 0.00 shared ges enqueues 1.5 1.7 13.94 shared ges reserved msg buffers 1.2 1.2 0.00 shared ges resource 1.7 1.9 12.22 shared kglsim hash table bkts 2.0 2.0 0.00 shared kks stbkt N/A .9 N/A shared library cache 6.1 6.3 3.09 shared row cache 3.6 3.6 0.00 shared sql area 8.4 8.1 -4.29 buffer_cache 168.0 168.0 0.00 fixed_sga 1.2 1.2 0.00 log_buffer 2.8 2.8 0.00 ------------------------------------------------------------- Streams CPU/IO Usage DB/Inst: RACDB/racdb1 Snaps: 4-7 No data exists for this section of the report. ------------------------------------------------------------- Streams Capture DB/Inst: RACDB/racdb1 Snaps: 4-7 No data exists for this section of the report. ------------------------------------------------------------- Streams Apply DB/Inst: RACDB/racdb1 Snaps: 4-7 No data exists for this section of the report. ------------------------------------------------------------- Buffered Queues DB/Inst: RACDB/racdb1 Snaps: 4-7 No data exists for this section of the report. ------------------------------------------------------------- Buffered Subscribers DB/Inst: RACDB/racdb1 Snaps: 4-7 No data exists for this section of the report. ------------------------------------------------------------- Rule Set DB/Inst: RACDB/racdb1 Snaps: 4-7 No data exists for this section of the report. ------------------------------------------------------------- Resource Limit Stats DB/Inst: RACDB/racdb1 Snap: 7 No data exists for this section of the report. ------------------------------------------------------------- init.ora Parameters DB/Inst: RACDB/racdb1 Snaps: 4-7 End value Parameter Name Begin value (if different) ----------------------------- --------------------------------- -------------- audit_file_dest /u01/app/oracle/admin/racdb/adump background_dump_dest /u01/app/oracle/admin/racdb/bdump cluster_database TRUE cluster_database_instances 2 compatible 10.2.0.1.0 control_files +RACDB_DATA1/racdb/controlfile/cu core_dump_dest /u01/app/oracle/admin/racdb/cdump db_block_size 8192 db_create_file_dest +RACDB_DATA1 db_domain idevelopment.info db_file_multiblock_read_count 16 db_name racdb db_recovery_file_dest +FLASH_RECOVERY_AREA db_recovery_file_dest_size 8589934592 dispatchers (PROTOCOL=TCP) (SERVICE=racdbXDB) instance_number 1 job_queue_processes 10 open_cursors 300 pga_aggregate_target 94371840 processes 150 remote_listener LISTENERS_RACDB remote_login_passwordfile EXCLUSIVE service_names racdb.idevelopment.info, racdb_ta sga_target 285212672 spfile +RACDB_DATA1/racdb/spfileracdb.or thread 1 undo_management AUTO undo_tablespace UNDOTBS1 user_dump_dest /u01/app/oracle/admin/racdb/udump ------------------------------------------------------------- Global Enqueue Statistics DB/Inst: RACDB/racdb1 Snaps: 4-7 Statistic Total per Second per Trans --------------------------------- ---------------- ------------ ------------ acks for commit broadcast(actual) 1,840 0.2 0.4 acks for commit broadcast(logical 1,890 0.2 0.4 broadcast msgs on commit(actual) 3,401 0.3 0.7 broadcast msgs on commit(logical) 3,402 0.3 0.7 broadcast msgs on commit(wasted) 4 0.0 0.0 dynamically allocated gcs resourc 0 0.0 0.0 dynamically allocated gcs shadows 0 0.0 0.0 false posts waiting for scn acks 2 0.0 0.0 flow control messages received 0 0.0 0.0 flow control messages sent 0 0.0 0.0 gcs assume cvt 0 0.0 0.0 gcs assume no cvt 1,183 0.1 0.2 gcs ast xid 0 0.0 0.0 gcs blocked converts 2,341 0.2 0.5 gcs blocked cr converts 2,361 0.2 0.5 gcs compatible basts 26 0.0 0.0 gcs compatible cr basts (global) 281 0.0 0.1 gcs compatible cr basts (local) 248 0.0 0.1 gcs cr basts to PIs 0 0.0 0.0 gcs cr serve without current lock 0 0.0 0.0 gcs dbwr flush pi msgs 383 0.0 0.1 gcs dbwr write request msgs 177 0.0 0.0 gcs error msgs 0 0.0 0.0 gcs forward cr to pinged instance 0 0.0 0.0 gcs immediate (compatible) conver 159 0.0 0.0 gcs immediate (null) converts 599 0.1 0.1 gcs immediate cr (compatible) con 17 0.0 0.0 gcs immediate cr (null) converts 194 0.0 0.0 gcs indirect ast 689 0.1 0.1 gcs lms flush pi msgs 0 0.0 0.0 gcs lms write request msgs 279 0.0 0.1 gcs msgs process time(ms) 771 0.1 0.2 gcs msgs received 14,767 1.4 3.1 gcs out-of-order msgs 0 0.0 0.0 gcs pings refused 18 0.0 0.0 gcs queued converts 0 0.0 0.0 gcs recovery claim msgs 0 0.0 0.0 gcs refuse xid 0 0.0 0.0 gcs regular cr 0 0.0 0.0 gcs retry convert request 1,219 0.1 0.3 gcs side channel msgs actual 379 0.0 0.1 gcs side channel msgs logical 4,395 0.4 0.9 gcs undo cr 15 0.0 0.0 gcs write notification msgs 50 0.0 0.0 gcs writes refused 8 0.0 0.0 ges msgs process time(ms) 270 0.0 0.1 ges msgs received 8,507 0.8 1.8 global posts dropped 0 0.0 0.0 global posts queue time 507 0.0 0.1 global posts queued 98 0.0 0.0 global posts requested 98 0.0 0.0 global posts sent 98 0.0 0.0 implicit batch messages received 562 0.1 0.1 implicit batch messages sent 388 0.0 0.1 lmd msg send time(ms) 0 0.0 0.0 lms(s) msg send time(ms) 0 0.0 0.0 messages flow controlled 4,886 0.5 1.0 messages queue sent actual 10,733 1.0 2.2 messages queue sent logical 11,535 1.1 2.4 messages received actual 19,177 1.8 4.0 Global Enqueue Statistics DB/Inst: RACDB/racdb1 Snaps: 4-7 Statistic Total per Second per Trans --------------------------------- ---------------- ------------ ------------ messages received logical 23,274 2.2 4.8 messages sent directly 6,283 0.6 1.3 messages sent indirectly 4,204 0.4 0.9 messages sent not implicit batche 10,345 1.0 2.1 messages sent pbatched 11,686 1.1 2.4 msgs causing lmd to send msgs 3,395 0.3 0.7 msgs causing lms(s) to send msgs 3,148 0.3 0.7 msgs received queue time (ms) 183,983 17.1 38.0 msgs received queued 23,274 2.2 4.8 msgs sent queue time (ms) 7,800 0.7 1.6 msgs sent queue time on ksxp (ms) 12,151 1.1 2.5 msgs sent queued 11,423 1.1 2.4 msgs sent queued on ksxp 20,310 1.9 4.2 process batch messages received 7,929 0.7 1.6 process batch messages sent 7,777 0.7 1.6 ------------------------------------------------------------- Global CR Served Stats DB/Inst: RACDB/racdb1 Snaps: 4-7 Statistic Total ------------------------------ ------------------ CR Block Requests 1,384 CURRENT Block Requests 153 Data Block Requests 1,384 Undo Block Requests 0 TX Block Requests 153 Current Results 1,537 Private results 0 Zero Results 0 Disk Read Results 0 Fail Results 0 Fairness Down Converts 273 Fairness Clears 0 Free GC Elements 0 Flushes 22 Flushes Queued 0 Flush Queue Full 0 Flush Max Time (us) 0 Light Works 0 Errors 0 ------------------------------------------------------------- Global CURRENT Served Stats DB/Inst: RACDB/racdb1 Snaps: 4-7 -> Pins = CURRENT Block Pin Operations -> Flushes = Redo Flush before CURRENT Block Served Operations -> Writes = CURRENT Block Fusion Write Operations Statistic Total % <1ms % <10ms % <100ms % <1s % <10s ---------- ------------ -------- -------- -------- -------- -------- Pins 1,691 100.00 0.00 0.00 0.00 0.00 Flushes 33 9.09 75.76 12.12 3.03 0.00 Writes 429 0.00 91.38 8.16 0.47 0.00 ------------------------------------------------------------- Global Cache Transfer Stats DB/Inst: RACDB/racdb1 Snaps: 4-7 -> Immediate (Immed) - Block Transfer NOT impacted by Remote Processing Delays -> Busy (Busy) - Block Transfer impacted by Remote Contention -> Congested (Congst) - Block Transfer impacted by Remote System Load -> ordered by CR + Current Blocks Received desc CR Current ----------------------------- ----------------------------- Inst Block Blocks % % % Blocks % % % No Class Received Immed Busy Congst Received Immed Busy Congst ---- ----------- -------- ------ ------ ------ -------- ------ ------ ------ 2 data block 2,091 89.1 10.9 .0 1,474 96.1 3.9 .0 2 Others 265 100.0 .0 .0 141 97.9 2.1 .0 2 undo header 18 94.4 5.6 .0 2 100.0 .0 .0 2 undo block 7 85.7 14.3 .0 0 N/A N/A N/A ------------------------------------------------------------- End of Report