WORKLOAD REPOSITORY report for DB Name DB Id Instance Inst Num Release RAC Host ------------ ----------- ------------ -------- ----------- --- ------------ ORCL1 1263261112 orcl1 1 10.2.0.4.0 NO TS-RAC Snap Id Snap Time Sessions Curs/Sess --------- ------------------- -------- --------- Begin Snap: 1614 21-May-15 12:10:07 22 7.1 End Snap: 1620 21-May-15 13:10:18 22 7.1 Elapsed: 60.18 (mins) DB Time: 0.20 (mins) Cache Sizes ~~~~~~~~~~~ Begin End ---------- ---------- Buffer Cache: 1,328M 1,328M Std Block Size: 8K Shared Pool Size: 192M 192M Log Buffer: 14,364K Load Profile ~~~~~~~~~~~~ Per Second Per Transaction --------------- --------------- Redo size: 1,682.79 14,747.68 Logical reads: 131.66 1,153.86 Block changes: 16.79 147.14 Physical reads: 1.25 10.95 Physical writes: 0.78 6.83 User calls: 0.16 1.41 Parses: 3.00 26.27 Hard parses: 0.14 1.27 Sorts: 1.27 11.16 Logons: 0.03 0.29 Executes: 5.33 46.67 Transactions: 0.11 % Blocks changed per Read: 12.75 Recursive Call %: 99.77 Rollback per transaction %: 0.00 Rows per Sort: 42.91 Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 99.99 Redo NoWait %: 100.00 Buffer Hit %: 99.09 In-memory Sort %: 100.00 Library Hit %: 93.67 Soft Parse %: 95.18 Execute to Parse %: 43.71 Latch Hit %: 100.00 Parse CPU to Parse Elapsd %: 86.52 % Non-Parse CPU: 80.03 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 60.94 61.71 % SQL with executions>1: 75.69 74.90 % Memory for SQL w/exec>1: 68.35 63.70 Top 5 Timed Events Avg %Total ~~~~~~~~~~~~~~~~~~ wait Call Event Waits Time (s) (ms) Time Wait Class ------------------------------ ------------ ----------- ------ ------ ---------- db file sequential read 4,566 8 2 67.5 User I/O CPU time 4 32.4 control file sequential read 5,498 2 0 15.0 System I/O control file parallel write 1,200 1 1 10.7 System I/O db file parallel write 1,028 1 1 6.5 System I/O ------------------------------------------------------------- Time Model Statistics DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 -> Total time in database user-calls (DB Time): 11.9s -> 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 11.9 100.3 DB CPU 3.9 32.4 parse time elapsed 2.1 17.9 hard parse elapsed time 2.0 16.7 PL/SQL execution elapsed time 1.1 9.4 hard parse (bind mismatch) elapsed time 0.1 .8 hard parse (sharing criteria) elapsed time 0.1 .8 PL/SQL compilation elapsed time 0.1 .7 repeated bind elapsed time 0.0 .2 sequence load elapsed time 0.0 .0 DB time 11.9 N/A background elapsed time 18.4 N/A background cpu time 7.4 N/A ------------------------------------------------------------- Wait Class DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 -> 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 -------------------- ---------------- ------ ---------------- ------- --------- User I/O 4,668 .0 8 2 11.3 System I/O 8,258 .0 4 0 20.0 Commit 76 .0 1 8 0.2 Concurrency 119 .0 0 2 0.3 Other 104 .0 0 0 0.3 Network 272 .0 0 0 0.7 Configuration 655 99.1 0 0 1.6 ------------------------------------------------------------- Wait Events DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 -> 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 ---------------------------- -------------- ------ ----------- ------- --------- db file sequential read 4,566 .0 8 2 11.1 control file sequential read 5,498 .0 2 0 13.3 control file parallel write 1,200 .0 1 1 2.9 db file parallel write 1,028 .0 1 1 2.5 log file sync 76 .0 1 8 0.2 os thread startup 58 .0 0 4 0.1 log file parallel write 532 .0 0 0 1.3 direct path read 95 .0 0 1 0.2 db file scattered read 7 .0 0 3 0.0 rdbms ipc reply 87 .0 0 0 0.2 latch free 1 .0 0 3 0.0 SQL*Net more data to client 72 .0 0 0 0.2 undo segment extension 655 99.1 0 0 1.6 buffer busy waits 58 .0 0 0 0.1 LGWR wait for redo copy 16 .0 0 0 0.0 SQL*Net message to client 200 .0 0 0 0.5 latch: library cache 1 .0 0 0 0.0 latch: cache buffers chains 2 .0 0 0 0.0 jobq slave wait 1,207 98.6 3,607 2989 2.9 Streams AQ: qmn slave idle w 258 .0 3,604 13969 0.6 Streams AQ: qmn coordinator 389 33.9 3,604 9265 0.9 virtual circuit status 120 100.0 3,579 29821 0.3 Streams AQ: waiting for time 57 70.2 3,194 56030 0.1 SQL*Net message from client 200 .0 2,345 11725 0.5 class slave wait 13 .0 0 0 0.0 ------------------------------------------------------------- Background Wait Events DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 -> 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 parallel write 1,200 .0 1 1 2.9 db file parallel write 1,028 .0 1 1 2.5 control file sequential read 1,356 .0 1 1 3.3 db file sequential read 22 .0 0 17 0.1 os thread startup 58 .0 0 4 0.1 log file parallel write 531 .0 0 0 1.3 events in waitclass Other 103 .0 0 0 0.3 buffer busy waits 23 .0 0 0 0.1 rdbms ipc message 23,432 98.1 38,634 1649 56.9 pmon timer 1,221 100.0 3,610 2956 3.0 Streams AQ: qmn slave idle w 258 .0 3,604 13969 0.6 Streams AQ: qmn coordinator 389 33.9 3,604 9265 0.9 smon timer 13 92.3 3,388 260653 0.0 Streams AQ: waiting for time 57 70.2 3,194 56030 0.1 ------------------------------------------------------------- Operating System Statistics DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 Statistic Total -------------------------------- -------------------- AVG_BUSY_TIME 856 AVG_IDLE_TIME 359,993 AVG_SYS_TIME #################### AVG_USER_TIME 355 BUSY_TIME 7,716 IDLE_TIME 2,880,802 SYS_TIME 3,988 USER_TIME 3,728 RSRC_MGR_CPU_WAIT_TIME 0 VM_IN_BYTES #################### VM_OUT_BYTES #################### PHYSICAL_MEMORY_BYTES 4,177,530,880 NUM_CPUS 8 NUM_CPU_CORES 4 ------------------------------------------------------------- Service Statistics DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 -> ordered by DB Time Physical Logical Service Name DB Time (s) DB CPU (s) Reads Reads -------------------------------- ------------ ------------ ---------- ---------- orcl1 10.2 3.2 4,361 395,306 SYS$USERS 1.7 0.6 0 21,133 SYS$BACKGROUND 0.0 0.0 155 59,101 orcl1XDB 0.0 0.0 0 0 ------------------------------------------------------------- Service Wait Class Stats DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 -> 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 --------- --------- --------- --------- --------- --------- --------- --------- orcl1 4349 602 0 0 0 0 272 0 SYS$USERS 0 0 0 0 0 0 0 0 SYS$BACKGROUND 319 213 119 24 0 0 0 0 ------------------------------------------------------------- SQL ordered by Elapsed Time DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 -> 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 ---------- ---------- ------------ ---------- ------- ------------- 7 2 1 6.9 57.8 d3h466f2ga5wr Module: TOAD 11.6.0.43 select a.table_name, b.blocks, b.num_rows, (b.blocks*4)/1024 as table_storage_mb, a.index_name, sum(ue.BYTES)/(1024*1024) as index_stora ge_mb, a.clustering_factor, a.BLEVEL, a.LEAF_BLOCKS from user_in dexes a inner join user_tables b on a.table_name=b.table_name inner join 3 2 1 3.0 24.9 572fbaj0fdw2b Module: sqlplus.exe select output from table(dbms_workload_repository.awr_report_html( :dbid, :inst_num, :bid, :eid, :rpt_options )) 2 2 6 0.3 14.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 1 0 6 0.2 9.7 350myuyx0t1d6 insert into wrh$_tablespace_stat (snap_id, dbid, instance_number, ts#, tsname , contents, status, segment_space_management, extent_management, is_back up) select :snap_id, :dbid, :instance_number, ts.ts#, ts.name as tsname, decode(ts.contents$, 0, (decode(bitand(ts.flags, 16), 16, 'UNDO', ' 1 0 59 0.0 8.2 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 0 6 0.1 6.3 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. 1 0 6 0.1 4.4 g6wf9na8zs5hb insert into wrh$_sysmetric_summary (snap_id, dbid, instance_number, beg in_time, end_time, intsize, group_id, metric_id, num_interval, maxval, minv al, average, standard_deviation) select :snap_id, :dbid, :instance_number, begtime, endtime, intsize_csec, groupid, metricid, numintv, max, min, 0 0 6 0.1 2.6 32mk33ry1g665 INSERT INTO wrh$_datafile (snap_id, dbid, file#, creation_change#, filename, ts#, tsname, block_size) SELECT /*+ ordered index(f) index(ts) */ :lah_snap _id lah, :dbid dbid, f.file# file#, f.crscnbas + (f.crscnwrp * power(2,32)) c reation_change#, v.name filename, ts.ts# ts#, ts.name tsname, ts.blocksize bl 0 0 6 0.0 2.5 84qubbrsr0kfn insert into wrh$_latch (snap_id, dbid, instance_number, latch_hash, level#, ge ts, misses, sleeps, immediate_gets, immediate_misses, spin_gets, sleep1, s leep2, sleep3, sleep4, wait_time) select :snap_id, :dbid, :instance_number, hash, level#, gets, misses, sleeps, immediate_gets, immediate_misses, spin_ge 0 0 6 0.0 2.0 d92h3rjp0y217 begin prvt_hdm.auto_execute( :db_id, :inst_id, :end_snap ); end; 0 0 1,182 0.0 1.8 db78fxqxwxt7r select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 a SQL ordered by Elapsed Time DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 -> 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 ---------- ---------- ------------ ---------- ------- ------------- nd intcol#=:2 and row#=:3 order by bucket 0 0 49 0.0 1.6 48sdn419b6p2q UPDATE MGMT_METRIC_COLLECTIONS SET STATUS_MESSAGE = :B6 || ' returned ' || :B5 | | ' values', LAST_COLLECTED_TIMESTAMP = :B4 WHERE TARGET_GUID = :B3 AND METRIC_G UID = :B2 AND COLL_NAME = :B1 0 0 90 0.0 1.6 91h2x42zqagcm UPDATE MGMT_CURRENT_METRICS SET COLLECTION_TIMESTAMP = :B3 , VALUE = :B2 , STRIN G_VALUE = :B1 WHERE TARGET_GUID = :B6 AND METRIC_GUID = :B5 AND KEY_VALUE = :B4 AND COLLECTION_TIMESTAMP < :B3 0 0 1 0.2 1.5 65f1skutyg1wr Module: sqlplus.exe SELECT DECODE(:B10 , 0, TO_NUMBER(NULL), 100*SUM(CASE SQT.COMMAND_TYPE WHEN 47 T HEN 0 ELSE SQT.BGET END)/:B10 ), DECODE(:B9 , 0, TO_NUMBER(NULL), 100*SUM(CASE S QT.COMMAND_TYPE WHEN 47 THEN 0 ELSE SQT.DSKR END)/:B9 ), DECODE(:B8 , 0, TO_NUMB ER(NULL), 100 * (SUM(SQT.EXEC) / :B8 )), DECODE(:B7 , 0, TO_NUMBER(NULL), 100 * 0 0 4,364 0.0 1.4 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 0 0 2 0.1 1.1 cz85u35yy7gcx Module: sqlplus.exe SELECT SUM(WAIT_COUNT) FROM DBA_HIST_WAITSTAT WHERE SNAP_ID = :B3 AND DBID = :B2 AND INSTANCE_NUMBER = :B1 0 0 36 0.0 1.1 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 ) 0 0 54 0.0 1.1 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 0 6 0.0 1.1 8xkz6ya4pyyw0 INSERT INTO WRH$_ACTIVE_SESSION_HISTORY ( snap_id, dbid, instance_number, sample _id, sample_time, session_id, session_serial#, user_id, sql_id, sql_child_ number, sql_plan_hash_value, force_matching_signature, service_hash, sessi on_type, flags , sql_opcode, plsql_entry_object_id, plsql_entry_subprog ------------------------------------------------------------- SQL ordered by CPU Time DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 -> 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 ---------- ---------- ------------ ----------- ------- ------------- 2 2 6 0.28 14.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 2 3 1 1.63 24.9 572fbaj0fdw2b Module: sqlplus.exe select output from table(dbms_workload_repository.awr_report_html( :dbid, :inst_num, :bid, :eid, :rpt_options )) 2 7 1 1.53 57.8 d3h466f2ga5wr Module: TOAD 11.6.0.43 select a.table_name, b.blocks, b.num_rows, (b.blocks*4)/1024 as table_storage_mb, a.index_name, sum(ue.BYTES)/(1024*1024) as index_stora ge_mb, a.clustering_factor, a.BLEVEL, a.LEAF_BLOCKS from user_in dexes a inner join user_tables b on a.table_name=b.table_name inner join 0 0 6 0.04 2.5 84qubbrsr0kfn insert into wrh$_latch (snap_id, dbid, instance_number, latch_hash, level#, ge ts, misses, sleeps, immediate_gets, immediate_misses, spin_gets, sleep1, s leep2, sleep3, sleep4, wait_time) select :snap_id, :dbid, :instance_number, hash, level#, gets, misses, sleeps, immediate_gets, immediate_misses, spin_ge 0 1 59 0.00 8.2 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; 0 0 4,364 0.00 1.4 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 0 1 6 0.02 6.3 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 0 1,182 0.00 1.8 db78fxqxwxt7r select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 a nd intcol#=:2 and row#=:3 order by bucket 0 0 49 0.00 0.9 abtp0uqvdb1d3 CALL MGMT_ADMIN_DATA.EVALUATE_MGMT_METRICS(:tguid, :mguid, :result) 0 0 721 0.00 0.7 6ssrk2dqj7jbx 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, j ob 0 0 2 0.04 1.1 cz85u35yy7gcx Module: sqlplus.exe SELECT SUM(WAIT_COUNT) FROM DBA_HIST_WAITSTAT WHERE SNAP_ID = :B3 AND DBID = :B2 AND INSTANCE_NUMBER = :B1 SQL ordered by CPU Time DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 -> 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 0 6 0.01 1.1 8xkz6ya4pyyw0 INSERT INTO WRH$_ACTIVE_SESSION_HISTORY ( snap_id, dbid, instance_number, sample _id, sample_time, session_id, session_serial#, user_id, sql_id, sql_child_ number, sql_plan_hash_value, force_matching_signature, service_hash, sessi on_type, flags , sql_opcode, plsql_entry_object_id, plsql_entry_subprog 0 0 36 0.00 1.1 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 ) 0 0 6 0.00 2.0 d92h3rjp0y217 begin prvt_hdm.auto_execute( :db_id, :inst_id, :end_snap ); end; 0 0 6 0.00 2.6 32mk33ry1g665 INSERT INTO wrh$_datafile (snap_id, dbid, file#, creation_change#, filename, ts#, tsname, block_size) SELECT /*+ ordered index(f) index(ts) */ :lah_snap _id lah, :dbid dbid, f.file# file#, f.crscnbas + (f.crscnwrp * power(2,32)) c reation_change#, v.name filename, ts.ts# ts#, ts.name tsname, ts.blocksize bl 0 1 6 0.00 4.4 g6wf9na8zs5hb insert into wrh$_sysmetric_summary (snap_id, dbid, instance_number, beg in_time, end_time, intsize, group_id, metric_id, num_interval, maxval, minv al, average, standard_deviation) select :snap_id, :dbid, :instance_number, begtime, endtime, intsize_csec, groupid, metricid, numintv, max, min, 0 0 54 0.00 1.1 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 0 90 0.00 1.6 91h2x42zqagcm UPDATE MGMT_CURRENT_METRICS SET COLLECTION_TIMESTAMP = :B3 , VALUE = :B2 , STRIN G_VALUE = :B1 WHERE TARGET_GUID = :B6 AND METRIC_GUID = :B5 AND KEY_VALUE = :B4 AND COLLECTION_TIMESTAMP < :B3 0 0 49 0.00 1.6 48sdn419b6p2q UPDATE MGMT_METRIC_COLLECTIONS SET STATUS_MESSAGE = :B6 || ' returned ' || :B5 | | ' values', LAST_COLLECTED_TIMESTAMP = :B4 WHERE TARGET_GUID = :B3 AND METRIC_G UID = :B2 AND COLL_NAME = :B1 0 0 1 0.00 1.5 65f1skutyg1wr Module: sqlplus.exe SELECT DECODE(:B10 , 0, TO_NUMBER(NULL), 100*SUM(CASE SQT.COMMAND_TYPE WHEN 47 T HEN 0 ELSE SQT.BGET END)/:B10 ), DECODE(:B9 , 0, TO_NUMBER(NULL), 100*SUM(CASE S QT.COMMAND_TYPE WHEN 47 THEN 0 ELSE SQT.DSKR END)/:B9 ), DECODE(:B8 , 0, TO_NUMB ER(NULL), 100 * (SUM(SQT.EXEC) / :B8 )), DECODE(:B7 , 0, TO_NUMBER(NULL), 100 * 0 1 6 0.00 9.7 350myuyx0t1d6 insert into wrh$_tablespace_stat (snap_id, dbid, instance_number, ts#, tsname , contents, status, segment_space_management, extent_management, is_back up) select :snap_id, :dbid, :instance_number, ts.ts#, ts.name as tsname, decode(ts.contents$, 0, (decode(bitand(ts.flags, 16), 16, 'UNDO', ' ------------------------------------------------------------- SQL ordered by Gets DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> Total Buffer Gets: 475,390 -> Captured SQL account for 113.9% of Total Gets CPU Elapsed Buffer Gets Executions per Exec %Total Time (s) Time (s) SQL Id -------------- ------------ ------------ ------ -------- --------- ------------- 208,301 1 208,301.0 43.8 1.53 6.87 d3h466f2ga5wr Module: TOAD 11.6.0.43 select a.table_name, b.blocks, b.num_rows, (b.blocks*4)/1024 as table_storage_mb, a.index_name, sum(ue.BYTES)/(1024*1024) as index_stora ge_mb, a.clustering_factor, a.BLEVEL, a.LEAF_BLOCKS from user_in dexes a inner join user_tables b on a.table_name=b.table_name inner join 186,422 1 186,422.0 39.2 1.63 2.96 572fbaj0fdw2b Module: sqlplus.exe select output from table(dbms_workload_repository.awr_report_html( :dbid, :inst_num, :bid, :eid, :rpt_options )) 28,492 84 339.2 6.0 0.05 0.05 gs2z9175jqgdf Module: sqlplus.exe SELECT VALUE FROM DBA_HIST_SYSSTAT WHERE SNAP_ID = :B4 AND DBID = :B3 AND INSTAN CE_NUMBER = :B2 AND STAT_NAME = :B1 13,539 59 229.5 2.8 0.25 0.98 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; 13,176 4,364 3.0 2.8 0.16 0.16 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 7,984 6 1,330.7 1.7 0.02 0.24 d92h3rjp0y217 begin prvt_hdm.auto_execute( :db_id, :inst_id, :end_snap ); end; 5,159 1,790 2.9 1.1 0.03 0.10 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 4,761 1 4,761.0 1.0 0.00 0.18 65f1skutyg1wr Module: sqlplus.exe SELECT DECODE(:B10 , 0, TO_NUMBER(NULL), 100*SUM(CASE SQT.COMMAND_TYPE WHEN 47 T HEN 0 ELSE SQT.BGET END)/:B10 ), DECODE(:B9 , 0, TO_NUMBER(NULL), 100*SUM(CASE S QT.COMMAND_TYPE WHEN 47 THEN 0 ELSE SQT.DSKR END)/:B9 ), DECODE(:B8 , 0, TO_NUMB ER(NULL), 100 * (SUM(SQT.EXEC) / :B8 )), DECODE(:B7 , 0, TO_NUMBER(NULL), 100 * 4,174 12 347.8 0.9 0.03 0.03 a1y2ba3awndas Module: sqlplus.exe SELECT VALUE FROM DBA_HIST_PARAMETER WHERE SNAP_ID = :B4 AND DBID = :B3 AND INST ANCE_NUMBER = :B2 AND PARAMETER_NAME = :B1 3,595 1,182 3.0 0.8 0.11 0.21 db78fxqxwxt7r select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 a nd intcol#=:2 and row#=:3 order by bucket ------------------------------------------------------------- SQL ordered by Reads DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 -> Total Disk Reads: 4,511 -> Captured SQL account for 104.3% of Total Reads CPU Elapsed Physical Reads Executions per Exec %Total Time (s) Time (s) SQL Id -------------- ----------- ------------- ------ -------- --------- ------------- 3,956 1 3,956.0 87.7 1.53 6.87 d3h466f2ga5wr Module: TOAD 11.6.0.43 select a.table_name, b.blocks, b.num_rows, (b.blocks*4)/1024 as table_storage_mb, a.index_name, sum(ue.BYTES)/(1024*1024) as index_stora ge_mb, a.clustering_factor, a.BLEVEL, a.LEAF_BLOCKS from user_in dexes a inner join user_tables b on a.table_name=b.table_name inner join 401 1 401.0 8.9 1.63 2.96 572fbaj0fdw2b Module: sqlplus.exe select output from table(dbms_workload_repository.awr_report_html( :dbid, :inst_num, :bid, :eid, :rpt_options )) 147 72 2.0 3.3 0.04 0.05 cvn54b7yz0s8u select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece from idl_ub1$ wher e obj#=:1 and part=:2 and version=:3 order by piece# 29 1,182 0.0 0.6 0.11 0.21 db78fxqxwxt7r select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 a nd intcol#=:2 and row#=:3 order by bucket 19 1 19.0 0.4 0.00 0.18 65f1skutyg1wr Module: sqlplus.exe SELECT DECODE(:B10 , 0, TO_NUMBER(NULL), 100*SUM(CASE SQT.COMMAND_TYPE WHEN 47 T HEN 0 ELSE SQT.BGET END)/:B10 ), DECODE(:B9 , 0, TO_NUMBER(NULL), 100*SUM(CASE S QT.COMMAND_TYPE WHEN 47 THEN 0 ELSE SQT.DSKR END)/:B9 ), DECODE(:B8 , 0, TO_NUMB ER(NULL), 100 * (SUM(SQT.EXEC) / :B8 )), DECODE(:B7 , 0, TO_NUMBER(NULL), 100 * 12 12 1.0 0.3 0.03 0.03 a1y2ba3awndas Module: sqlplus.exe SELECT VALUE FROM DBA_HIST_PARAMETER WHERE SNAP_ID = :B4 AND DBID = :B3 AND INST ANCE_NUMBER = :B2 AND PARAMETER_NAME = :B1 9 9 1.0 0.2 0.00 0.10 3505vtqmvvf40 insert into wrh$_waitclassmetric_history (snap_id, dbid, instance_number, wa it_class_id, begin_time, end_time, intsize, group_id, average_waiter_c ount, dbtime_in_wait, time_waited, wait_count) select :snap_id, :dbid , :instance_number, wait_id, begtime, endtime, intsize_csec, groupid, 9 84 0.1 0.2 0.05 0.05 gs2z9175jqgdf Module: sqlplus.exe SELECT VALUE FROM DBA_HIST_SYSSTAT WHERE SNAP_ID = :B4 AND DBID = :B3 AND INSTAN CE_NUMBER = :B2 AND STAT_NAME = :B1 8 6 1.3 0.2 0.02 0.52 g6wf9na8zs5hb insert into wrh$_sysmetric_summary (snap_id, dbid, instance_number, beg in_time, end_time, intsize, group_id, metric_id, num_interval, maxval, minv al, average, standard_deviation) select :snap_id, :dbid, :instance_number, begtime, endtime, intsize_csec, groupid, metricid, numintv, max, min, 7 60 0.1 0.2 0.00 0.03 g3wrkmxkxzhf2 select cols,audit$,textlength,intcols,property,flags,rowid from view$ where obj# =:1 ------------------------------------------------------------- SQL ordered by Executions DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 -> Total Executions: 19,230 -> Captured SQL account for 96.0% of Total CPU per Elap per Executions Rows Processed Rows per Exec Exec (s) Exec (s) SQL Id ------------ --------------- -------------- ---------- ----------- ------------- 4,364 4,364 1.0 0.00 0.00 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 1,790 1,565 0.9 0.00 0.00 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 1,182 18,393 15.6 0.00 0.00 db78fxqxwxt7r select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 a nd intcol#=:2 and row#=:3 order by bucket 721 60 0.1 0.00 0.00 6ssrk2dqj7jbx 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, j ob 718 714 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), 660 660 1.0 0.00 0.00 g2wr3u7s1gtf3 select count(*) from sys.job$ where (next_date > sysdate) and (next_date < (sysd ate+5/86400)) 413 413 1.0 0.00 0.00 grwydz59pu6mc select text from view$ where rowid=:1 384 382 1.0 0.00 0.00 04xtrk7uyhknh select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, spare2 f rom obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null an d linkname is null and subname is null 382 464 1.2 0.00 0.00 53saa2zkr6wc3 select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where con#=:1 318 318 1.0 0.00 0.00 5h7w8ykwtb2xt INSERT INTO SYS.WRI$_ADV_PARAMETERS (TASK_ID,NAME,DATATYPE,VALUE,FLAGS,DESCRIPTI ON) VALUES (:B6 , :B5 , :B4 , :B3 , :B2 , :B1 ) 297 297 1.0 0.00 0.00 5ngzsfstg8tmy select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,o.dataobj# ,o.flags from obj$ o where o.obj#=:1 265 695 2.6 0.00 0.00 6769wyy3yf66f select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where obj#=:1 262 3,177 12.1 0.00 0.00 83taa7kaw59c1 select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,nvl(sc ale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,scale,183,s cale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,rowid,col#,proper ty, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0) from col$ wh 234 0 0.0 0.00 0.00 b2gnxm5z6r51n lock table sys.col_usage$ in exclusive mode nowait SQL ordered by Executions DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 -> Total Executions: 19,230 -> Captured SQL account for 96.0% of Total CPU per Elap per Executions Rows Processed Rows per Exec Exec (s) Exec (s) SQL Id ------------ --------------- -------------- ---------- ----------- ------------- 221 0 0.0 0.00 0.00 350f5yrnnmshs lock table sys.mon_mods$ in exclusive mode nowait 221 220 1.0 0.00 0.00 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 198 265 1.3 0.00 0.00 7ng34ruy5awxq select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,i.pctf ree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,i.dblkkey,i.cl ufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,nvl(i.degree,1),nvl(i.instance s,1),i.rowcnt,mod(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl( ------------------------------------------------------------- SQL ordered by Parse Calls DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 -> Total Parse Calls: 10,824 -> Captured SQL account for 91.9% of Total % Total Parse Calls Executions Parses SQL Id ------------ ------------ --------- ------------- 4,364 4,364 40.32 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 413 413 3.82 grwydz59pu6mc select text from view$ where rowid=:1 234 4 2.16 0v3dvmc22qnam insert into sys.col_usage$ (obj#, intcol#, equality_preds, equijoin_preds, noneq uijoin_preds, range_preds, like_preds, null_preds, timestamp) values ( :objn, :coln, decode(bitand(:flag,1),0,0,1), decode(bitand(:flag,2),0,0,1), decod e(bitand(:flag,4),0,0,1), decode(bitand(:flag,8),0,0,1), decode(bitand(:flag 234 718 2.16 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), 234 234 2.16 b2gnxm5z6r51n lock table sys.col_usage$ in exclusive mode nowait 221 221 2.04 350f5yrnnmshs lock table sys.mon_mods$ in exclusive mode nowait 221 221 2.04 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 190 190 1.76 0h6b2sajwb74n select privilege#,level from sysauth$ connect by grantee#=prior privilege# and p rivilege#>0 start with grantee#=:1 and privilege#>0 122 122 1.13 8swypbbr0m372 select order#,columns,types from access$ where d_obj#=:1 122 122 1.13 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 Sharable Memory DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 No data exists for this section of the report. ------------------------------------------------------------- SQL ordered by Version Count DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 No data exists for this section of the report. ------------------------------------------------------------- Instance Activity Stats DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- CPU used by this session 689 0.2 1.7 CPU used when call started 497 0.1 1.2 CR blocks created 42 0.0 0.1 Cached Commit SCN referenced 0 0.0 0.0 Commit SCN cached 0 0.0 0.0 DB time 361,173 100.0 876.6 DBWR checkpoint buffers written 2,816 0.8 6.8 DBWR checkpoints 0 0.0 0.0 DBWR object drop buffers written 0 0.0 0.0 DBWR revisited being-written buf 0 0.0 0.0 DBWR tablespace checkpoint buffe 0 0.0 0.0 DBWR thread checkpoint buffers w 0 0.0 0.0 DBWR transaction table writes 93 0.0 0.2 DBWR undo block writes 451 0.1 1.1 IMU CR rollbacks 15 0.0 0.0 IMU Flushes 74 0.0 0.2 IMU Redo allocation size 368,456 102.1 894.3 IMU commits 355 0.1 0.9 IMU contention 0 0.0 0.0 IMU ktichg flush 0 0.0 0.0 IMU pool not allocated 0 0.0 0.0 IMU recursive-transaction flush 1 0.0 0.0 IMU undo allocation size 1,643,304 455.1 3,988.6 IMU- failed to get a private str 0 0.0 0.0 Misses for writing mapping 0 0.0 0.0 SMON posted for undo segment shr 1 0.0 0.0 SQL*Net roundtrips to/from clien 200 0.1 0.5 SQL*Net roundtrips to/from dblin 0 0.0 0.0 active txn count during cleanout 195 0.1 0.5 application wait time 0 0.0 0.0 background checkpoints completed 0 0.0 0.0 background checkpoints started 0 0.0 0.0 background timeouts 13,925 3.9 33.8 branch node splits 0 0.0 0.0 buffer is not pinned count 192,588 53.3 467.5 buffer is pinned count 238,266 66.0 578.3 bytes received via SQL*Net from 24,124 6.7 58.6 bytes received via SQL*Net from 0 0.0 0.0 bytes sent via SQL*Net to client 308,092 85.3 747.8 bytes sent via SQL*Net to dblink 0 0.0 0.0 calls to get snapshot scn: kcmgs 75,932 21.0 184.3 calls to kcmgas 5,505 1.5 13.4 calls to kcmgcs 259 0.1 0.6 change write time 5 0.0 0.0 cleanout - number of ktugct call 211 0.1 0.5 cleanouts and rollbacks - consis 1 0.0 0.0 cleanouts only - consistent read 9 0.0 0.0 cluster key scan block gets 69,033 19.1 167.6 cluster key scans 44,644 12.4 108.4 commit batch performed 0 0.0 0.0 commit batch requested 0 0.0 0.0 commit batch/immediate performed 2 0.0 0.0 commit batch/immediate requested 2 0.0 0.0 commit cleanout failures: block 0 0.0 0.0 commit cleanout failures: buffer 0 0.0 0.0 commit cleanout failures: callba 22 0.0 0.1 commit cleanout failures: cannot 0 0.0 0.0 commit cleanouts 3,475 1.0 8.4 commit cleanouts successfully co 3,453 1.0 8.4 commit immediate performed 2 0.0 0.0 Instance Activity Stats DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- commit immediate requested 2 0.0 0.0 commit txn count during cleanout 110 0.0 0.3 concurrency wait time 24 0.0 0.1 consistent changes 37,961 10.5 92.1 consistent gets 338,868 93.9 822.5 consistent gets - examination 194,410 53.8 471.9 consistent gets direct 95 0.0 0.2 consistent gets from cache 338,773 93.8 822.3 cursor authentications 211 0.1 0.5 data blocks consistent reads - u 40 0.0 0.1 db block changes 60,622 16.8 147.1 db block gets 136,522 37.8 331.4 db block gets direct 0 0.0 0.0 db block gets from cache 136,522 37.8 331.4 deferred (CURRENT) block cleanou 2,304 0.6 5.6 dirty buffers inspected 0 0.0 0.0 enqueue conversions 743 0.2 1.8 enqueue releases 38,170 10.6 92.7 enqueue requests 38,170 10.6 92.7 enqueue timeouts 0 0.0 0.0 enqueue waits 0 0.0 0.0 exchange deadlocks 0 0.0 0.0 execute count 19,230 5.3 46.7 failed probes on index block rec 0 0.0 0.0 free buffer inspected 3,107 0.9 7.5 free buffer requested 14,373 4.0 34.9 heap block compress 49 0.0 0.1 hot buffers moved to head of LRU 201 0.1 0.5 immediate (CR) block cleanout ap 10 0.0 0.0 immediate (CURRENT) block cleano 497 0.1 1.2 index crx upgrade (found) 0 0.0 0.0 index crx upgrade (positioned) 1,846 0.5 4.5 index fast full scans (full) 3 0.0 0.0 index fetch by key 166,776 46.2 404.8 index scans kdiixs1 8,403 2.3 20.4 leaf node 90-10 splits 50 0.0 0.1 leaf node splits 83 0.0 0.2 lob reads 4,062 1.1 9.9 lob writes 9,764 2.7 23.7 lob writes unaligned 9,764 2.7 23.7 logons cumulative 120 0.0 0.3 messages received 1,561 0.4 3.8 messages sent 1,561 0.4 3.8 no buffer to keep pinned count 0 0.0 0.0 no work - consistent read gets 126,063 34.9 306.0 opened cursors cumulative 15,531 4.3 37.7 parse count (failures) 0 0.0 0.0 parse count (hard) 522 0.1 1.3 parse count (total) 10,824 3.0 26.3 parse time cpu 77 0.0 0.2 parse time elapsed 89 0.0 0.2 physical read IO requests 4,495 1.2 10.9 physical read bytes 36,954,112 10,234.7 89,694.5 physical read total IO requests 10,161 2.8 24.7 physical read total bytes 128,409,600 35,563.8 311,673.8 physical read total multi block 7 0.0 0.0 physical reads 4,511 1.3 11.0 physical reads cache 4,416 1.2 10.7 physical reads cache prefetch 16 0.0 0.0 physical reads direct 95 0.0 0.2 Instance Activity Stats DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- physical reads direct (lob) 95 0.0 0.2 physical reads direct temporary 0 0.0 0.0 physical reads prefetch warmup 0 0.0 0.0 physical write IO requests 1,840 0.5 4.5 physical write bytes 23,068,672 6,389.0 55,991.9 physical write total IO requests 5,973 1.7 14.5 physical write total bytes 88,534,528 24,520.1 214,889.6 physical write total multi block 726 0.2 1.8 physical writes 2,816 0.8 6.8 physical writes direct 0 0.0 0.0 physical writes direct (lob) 0 0.0 0.0 physical writes direct temporary 0 0.0 0.0 physical writes from cache 2,816 0.8 6.8 physical writes non checkpoint 691 0.2 1.7 pinned buffers inspected 0 0.0 0.0 prefetch warmup blocks aged out 0 0.0 0.0 prefetch warmup blocks flushed o 0 0.0 0.0 prefetched blocks aged out befor 0 0.0 0.0 process last non-idle time 0 0.0 0.0 recursive calls 247,451 68.5 600.6 recursive cpu usage 533 0.2 1.3 redo blocks written 12,663 3.5 30.7 redo buffer allocation retries 0 0.0 0.0 redo entries 11,546 3.2 28.0 redo log space requests 0 0.0 0.0 redo log space wait time 0 0.0 0.0 redo ordering marks 350 0.1 0.9 redo size 6,076,044 1,682.8 14,747.7 redo subscn max counts 946 0.3 2.3 redo synch time 68 0.0 0.2 redo synch writes 75 0.0 0.2 redo wastage 158,808 44.0 385.5 redo write time 9 0.0 0.0 redo writer latching time 0 0.0 0.0 redo writes 531 0.2 1.3 rollback changes - undo records 3 0.0 0.0 rollbacks only - consistent read 39 0.0 0.1 rows fetched via callback 31,785 8.8 77.2 session connect time 0 0.0 0.0 session cursor cache hits 12,773 3.5 31.0 session logical reads 475,390 131.7 1,153.9 session pga memory 91,924,208 25,458.9 223,117.0 session pga memory max 107,718,384 29,833.2 261,452.4 session uga memory 266,291,430,008 73,750,883.5 ############# session uga memory max 109,517,392 30,331.5 265,818.9 shared hash latch upgrades - no 2,138 0.6 5.2 shared hash latch upgrades - wai 0 0.0 0.0 sorts (disk) 0 0.0 0.0 sorts (memory) 4,597 1.3 11.2 sorts (rows) 197,263 54.6 478.8 sql area evicted 418 0.1 1.0 sql area purged 0 0.0 0.0 summed dirty queue length 0 0.0 0.0 switch current to new buffer 39 0.0 0.1 table fetch by rowid 91,953 25.5 223.2 table fetch continued row 251 0.1 0.6 table scan blocks gotten 13,149 3.6 31.9 table scan rows gotten 357,285 99.0 867.2 table scans (direct read) 0 0.0 0.0 table scans (long tables) 0 0.0 0.0 Instance Activity Stats DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- table scans (rowid ranges) 0 0.0 0.0 table scans (short tables) 1,363 0.4 3.3 total number of times SMON poste 1 0.0 0.0 transaction rollbacks 2 0.0 0.0 transaction tables consistent re 0 0.0 0.0 transaction tables consistent re 0 0.0 0.0 undo change vector size 2,057,140 569.7 4,993.1 user I/O wait time 812 0.2 2.0 user calls 582 0.2 1.4 user commits 412 0.1 1.0 user rollbacks 0 0.0 0.0 workarea executions - onepass 0 0.0 0.0 workarea executions - optimal 2,722 0.8 6.6 write clones created in backgrou 0 0.0 0.0 write clones created in foregrou 0 0.0 0.0 ------------------------------------------------------------- Instance Activity Stats - Absolute ValuesDB/Inst: ORCL1/orcl1 Snaps: 1614-16 -> Statistics with absolute values (should not be diffed) Statistic Begin Value End Value -------------------------------- --------------- --------------- session cursor cache count 97,831 99,153 opened cursors current 156 157 logons current 22 22 ------------------------------------------------------------- Instance Activity Stats - Thread ActivityDB/Inst: ORCL1/orcl1 Snaps: 1614-162 -> Statistics identified by '(derived)' come from sources other than SYSSTAT Statistic Total per Hour -------------------------------- ------------------ --------- log switches (derived) 0 .00 ------------------------------------------------------------- Tablespace IO Stats DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 -> 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) -------------- ------- ------ ------- ------------ -------- ---------- ------ USERS 3,893 1 1.3 1.0 0 0 0 0.0 SYSAUX 300 0 5.2 1.0 1,508 0 0 0.0 SYSTEM 206 0 1.7 1.0 114 0 0 0.0 UNDOTBS1 0 0 0.0 .0 218 0 58 0.0 TEMP 58 0 0.5 1.0 0 0 0 0.0 REG02 2 0 10.0 1.0 0 0 0 0.0 REG03 2 0 10.0 1.0 0 0 0 0.0 REG04 2 0 5.0 1.0 0 0 0 0.0 REG05 2 0 10.0 1.0 0 0 0 0.0 TS_REG01 2 0 15.0 1.0 0 0 0 0.0 YEAR_1999 2 0 10.0 1.0 0 0 0 0.0 YEAR_2000 2 0 0.0 1.0 0 0 0 0.0 YEAR_2001 2 0 0.0 1.0 0 0 0 0.0 YEAR_2002 2 0 5.0 1.0 0 0 0 0.0 YEAR_2003 2 0 5.0 1.0 0 0 0 0.0 YEAR_2004 2 0 0.0 1.0 0 0 0 0.0 YEAR_2005 2 0 5.0 1.0 0 0 0 0.0 YEAR_2006 2 0 0.0 1.0 0 0 0 0.0 YEAR_2007 2 0 5.0 1.0 0 0 0 0.0 YEAR_2008 2 0 5.0 1.0 0 0 0 0.0 YEAR_2009 2 0 5.0 1.0 0 0 0 0.0 YEAR_2010 2 0 5.0 1.0 0 0 0 0.0 YEAR_2011 2 0 0.0 1.0 0 0 0 0.0 YEAR_2012 2 0 5.0 1.0 0 0 0 0.0 YEAR_2013 2 0 5.0 1.0 0 0 0 0.0 YEAR_2014 2 0 5.0 1.0 0 0 0 0.0 GIDC 1 0 10.0 1.0 0 0 0 0.0 ------------------------------------------------------------- File IO Stats DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 -> 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) -------------- ------- ------ ------- ------------ -------- ---------- ------ GIDC D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\GIDC.DBF 1 0 10.0 1.0 0 0 0 0.0 REG02 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REG02.DBF 2 0 10.0 1.0 0 0 0 0.0 REG03 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REG03.DBF 2 0 10.0 1.0 0 0 0 0.0 REG04 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REG04.DBF 2 0 5.0 1.0 0 0 0 0.0 REG05 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REG05.DBF 2 0 10.0 1.0 0 0 0 0.0 SYSAUX D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSAUX01.DBF 300 0 5.2 1.0 1,508 0 0 0.0 SYSTEM D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSTEM01.DBF 206 0 1.7 1.0 114 0 0 0.0 TEMP D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\TEMP01.DBF 58 0 0.5 1.0 0 0 0 N/A TS_REG01 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REG01.DBF 2 0 15.0 1.0 0 0 0 0.0 UNDOTBS1 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\UNDOTBS01.DBF 0 0 N/A N/A 218 0 58 0.0 USERS D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\USERS01.DBF 3,891 1 1.3 1.0 0 0 0 0.0 USERS D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\USERS02.DBF 2 0 15.0 1.0 0 0 0 0.0 YEAR_1999 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\YEAR_1999.DBF 2 0 10.0 1.0 0 0 0 0.0 YEAR_2000 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\YEAR_2000.DBF 2 0 0.0 1.0 0 0 0 0.0 YEAR_2001 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\YEAR_2001.DBF 2 0 0.0 1.0 0 0 0 0.0 YEAR_2002 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\YEAR_2002.DBF 2 0 5.0 1.0 0 0 0 0.0 YEAR_2003 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\YEAR_2003.DBF 2 0 5.0 1.0 0 0 0 0.0 YEAR_2004 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\YEAR_2004.DBF 2 0 0.0 1.0 0 0 0 0.0 YEAR_2005 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\YEAR_2005.DBF 2 0 5.0 1.0 0 0 0 0.0 YEAR_2006 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\YEAR_2006.DBF 2 0 0.0 1.0 0 0 0 0.0 YEAR_2007 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\YEAR_2007.DBF 2 0 5.0 1.0 0 0 0 0.0 YEAR_2008 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\YEAR_2008.DBF 2 0 5.0 1.0 0 0 0 0.0 YEAR_2009 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\YEAR_2009.DBF 2 0 5.0 1.0 0 0 0 0.0 YEAR_2010 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\YEAR_2010.DBF 2 0 5.0 1.0 0 0 0 0.0 YEAR_2011 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\YEAR_2011.DBF 2 0 0.0 1.0 0 0 0 0.0 YEAR_2012 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\YEAR_2012.DBF 2 0 5.0 1.0 0 0 0 0.0 YEAR_2013 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\YEAR_2013.DBF 2 0 5.0 1.0 0 0 0 0.0 YEAR_2014 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\YEAR_2014.DBF 2 0 5.0 1.0 0 0 0 0.0 ------------------------------------------------------------- Buffer Pool Statistics DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 -> 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 163,194 99 475,334 4,416 2,816 0 0 58 ------------------------------------------------------------- Instance Recovery Stats DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 -> 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 57 24 209 538 7881 276480 7881 N/A E 57 24 180 547 5983 276480 5983 N/A ------------------------------------------------------------- Buffer Pool Advisory DB/Inst: ORCL1/orcl1 Snap: 1620 -> 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 128 .1 15,832 1.2 10,423,206 D 256 .2 31,664 1.1 10,050,397 D 384 .3 47,496 1.1 9,906,607 D 512 .4 63,328 1.1 9,754,859 D 640 .5 79,160 1.1 9,541,953 D 768 .6 94,992 1.1 9,308,396 D 896 .7 110,824 1.0 9,147,574 D 1,024 .8 126,656 1.0 9,030,894 D 1,152 .9 142,488 1.0 8,928,009 D 1,280 1.0 158,320 1.0 8,839,656 D 1,344 1.0 166,236 1.0 8,805,838 D 1,408 1.0 174,152 1.0 8,772,661 D 1,536 1.1 189,984 1.0 8,718,489 D 1,664 1.2 205,816 1.0 8,650,227 D 1,792 1.3 221,648 1.0 8,610,161 D 1,920 1.4 237,480 1.0 8,581,669 D 2,048 1.5 253,312 1.0 8,564,653 D 2,176 1.6 269,144 1.0 8,552,274 D 2,304 1.7 284,976 1.0 8,540,502 D 2,432 1.8 300,808 1.0 8,525,804 D 2,560 1.9 316,640 0.9 8,314,426 ------------------------------------------------------------- PGA Aggr Summary DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 -> 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 136 0 ------------------------------------------------------------- PGA Aggr Target Stats DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 -> 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 388 328 59.1 0.0 .0 .0 .0 79,462 E 388 328 60.0 0.0 .0 .0 .0 79,462 ------------------------------------------------------------- PGA Aggr Target Histogram DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 -> Optimal Executions are purely in-memory operations Low High Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs ------- ------- -------------- -------------- ------------ ------------ 2K 4K 2,562 2,562 0 0 64K 128K 12 12 0 0 512K 1024K 129 129 0 0 1M 2M 19 19 0 0 ------------------------------------------------------------- PGA Memory Advisory DB/Inst: ORCL1/orcl1 Snap: 1620 -> 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 ---------- ------- ---------------- ---------------- -------- ---------- 49 0.1 100,317.1 37,502.4 73.0 132 97 0.3 100,317.1 20,310.3 83.0 6 194 0.5 100,317.1 9,830.9 91.0 0 291 0.8 100,317.1 9,398.9 91.0 0 388 1.0 100,317.1 8,348.5 92.0 0 466 1.2 100,317.1 5,481.7 95.0 0 543 1.4 100,317.1 5,481.7 95.0 0 621 1.6 100,317.1 5,481.7 95.0 0 698 1.8 100,317.1 5,481.7 95.0 0 776 2.0 100,317.1 5,481.7 95.0 0 1,164 3.0 100,317.1 5,481.7 95.0 0 1,552 4.0 100,317.1 5,481.7 95.0 0 2,328 6.0 100,317.1 5,481.7 95.0 0 3,104 8.0 100,317.1 5,481.7 95.0 0 ------------------------------------------------------------- Shared Pool Advisory DB/Inst: ORCL1/orcl1 Snap: 1620 -> 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 ---------- ----- -------- ------------ ------- ------ ------- ------ ----------- 128 .7 36 2,048 ####### .9 ####### 141.3 9,870,779 160 .8 65 3,519 ####### 1.0 67,013 48.8 9,934,771 192 1.0 96 6,826 ####### 1.0 1,373 1.0 9,959,500 224 1.2 127 11,110 ####### 1.0 1 .0 9,973,113 256 1.3 158 12,880 ####### 1.1 1 .0 9,980,965 288 1.5 189 14,782 ####### 1.1 1 .0 9,985,854 320 1.7 220 16,005 ####### 1.1 1 .0 9,988,920 352 1.8 251 16,753 ####### 1.1 1 .0 9,991,288 384 2.0 280 17,265 ####### 1.1 1 .0 9,993,677 ------------------------------------------------------------- SGA Target Advisory DB/Inst: ORCL1/orcl1 Snap: 1620 SGA Target SGA Size Est DB Est Physical Size (M) Factor Time (s) Reads ---------- ---------- ------------ ---------------- 400 0.3 62,431 10,423,467 800 0.5 58,929 9,755,104 1,200 0.8 55,750 9,147,501 1,600 1.0 53,959 8,805,835 2,000 1.3 52,934 8,610,345 2,400 1.5 52,632 8,552,227 2,800 1.8 51,385 8,314,469 3,200 2.0 51,385 8,314,469 ------------------------------------------------------------- Streams Pool Advisory DB/Inst: ORCL1/orcl1 Snap: 1620 Size for Size Est Spill Est Spill Est Unspill Est Unspill Est (MB) Factor Count Time (s) Count Time (s) ---------- --------- ----------- ----------- ----------- ----------- 16 0.5 0 0 0 0 32 1.0 0 0 0 0 48 1.5 0 0 0 0 64 2.0 0 0 0 0 80 2.5 0 0 0 0 96 3.0 0 0 0 0 112 3.5 0 0 0 0 128 4.0 0 0 0 0 144 4.5 0 0 0 0 160 5.0 0 0 0 0 176 5.5 0 0 0 0 192 6.0 0 0 0 0 208 6.5 0 0 0 0 224 7.0 0 0 0 0 240 7.5 0 0 0 0 256 8.0 0 0 0 0 272 8.5 0 0 0 0 288 9.0 0 0 0 0 304 9.5 0 0 0 0 320 10.0 0 0 0 0 ------------------------------------------------------------- Java Pool Advisory DB/Inst: ORCL1/orcl1 Snap: 1620 No data exists for this section of the report. ------------------------------------------------------------- Buffer Wait Statistics DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 -> ordered by wait time desc, waits desc Class Waits Total Wait Time (s) Avg Time (ms) ------------------ ----------- ------------------- -------------- undo header 58 0 0 ------------------------------------------------------------- Enqueue Activity DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 No data exists for this section of the report. ------------------------------------------------------------- Undo Segment Summary DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 -> 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 .3 1,562 0 3 15/15 0/0 0/0/0/0/0/0 ------------------------------------------------------------- Undo Segment Stats DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 -> 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 ------------ ----------- ------------ ------- ------- ------- ----- ------------ 21-May 13:05 63 238 0 3 15 0/0 0/0/0/0/0/0 21-May 12:55 47 102 0 3 15 0/0 0/0/0/0/0/0 21-May 12:45 63 513 0 3 15 0/0 0/0/0/0/0/0 21-May 12:35 53 364 0 3 15 0/0 0/0/0/0/0/0 21-May 12:25 66 99 0 3 15 0/0 0/0/0/0/0/0 21-May 12:15 51 246 0 3 15 0/0 0/0/0/0/0/0 ------------------------------------------------------------- Latch Activity DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 -> "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 db client latch 3,360 0.0 N/A 0 0 N/A AWR Alerted Metric Eleme 14,119 0.0 N/A 0 0 N/A Consistent RBA 531 0.0 N/A 0 0 N/A FOB s.o list latch 412 0.0 N/A 0 0 N/A In memory undo latch 5,281 0.0 N/A 0 1,582 0.0 JS mem alloc latch 3 0.0 N/A 0 0 N/A JS queue access latch 3 0.0 N/A 0 0 N/A JS queue state obj latch 26,062 0.0 N/A 0 0 N/A JS slv state obj latch 145 0.0 N/A 0 0 N/A KMG MMAN ready and start 1,200 0.0 N/A 0 0 N/A KTF sga latch 8 0.0 N/A 0 1,092 0.0 KWQMN job cache list lat 145 0.0 N/A 0 0 N/A KWQP Prop Status 12 0.0 N/A 0 0 N/A MQL Tracking Latch 0 N/A N/A 0 72 0.0 Memory Management Latch 0 N/A N/A 0 1,200 0.0 OS process 522 0.0 N/A 0 0 N/A OS process allocation 1,548 0.0 N/A 0 0 N/A OS process: request allo 116 0.0 N/A 0 0 N/A PL/SQL warning settings 300 0.0 N/A 0 0 N/A SGA IO buffer pool latch 7 0.0 N/A 0 7 0.0 SQL memory manager latch 6 0.0 N/A 0 1,200 0.0 SQL memory manager worka 82,092 0.0 N/A 0 0 N/A Shared B-Tree 147 0.0 N/A 0 0 N/A active checkpoint queue 4,628 0.0 N/A 0 0 N/A active service list 7,645 0.0 N/A 0 1,221 0.0 archive control 10 0.0 N/A 0 0 N/A cache buffer handles 194 0.0 N/A 0 0 N/A cache buffers chains 931,684 0.0 0.1 0 13,611 0.0 cache buffers lru chain 19,054 0.0 N/A 0 19,243 0.0 cache table scan latch 0 N/A N/A 0 7 0.0 channel handle pool latc 116 0.0 N/A 0 0 N/A channel operations paren 23,921 0.0 N/A 0 0 N/A checkpoint queue latch 104,705 0.0 N/A 0 2,699 0.0 client/application info 1,570 0.0 N/A 0 0 N/A commit callback allocati 140 0.0 N/A 0 0 N/A compile environment latc 120 0.0 N/A 0 0 N/A dml lock allocation 4,318 0.0 N/A 0 0 N/A dummy allocation 240 0.0 N/A 0 0 N/A enqueue hash chains 77,178 0.0 N/A 0 67 0.0 enqueues 69,960 0.0 N/A 0 0 N/A event group latch 58 0.0 N/A 0 0 N/A file cache latch 485 0.0 N/A 0 0 N/A hash table column usage 477 0.0 N/A 0 80,708 0.0 hash table modification 13 0.0 N/A 0 0 N/A job workq parent latch 0 N/A N/A 0 124 0.0 job_queue_processes para 122 0.0 N/A 0 0 N/A kks stats 2,210 0.0 N/A 0 0 N/A kokc descriptor allocati 72 0.0 N/A 0 0 N/A ksuosstats global area 253 0.0 N/A 0 0 N/A ktm global data 15 0.0 N/A 0 0 N/A kwqbsn:qsga 141 0.0 N/A 0 0 N/A lgwr LWN SCN 1,461 0.0 N/A 0 0 N/A library cache 62,134 0.0 1.0 0 1,302 0.0 library cache load lock 1,632 0.0 N/A 0 0 N/A library cache lock 32,352 0.0 N/A 0 0 N/A library cache lock alloc 1,635 0.0 N/A 0 0 N/A library cache pin 14,701 0.0 N/A 0 0 N/A library cache pin alloca 543 0.0 N/A 0 0 N/A list of block allocation 267 0.0 N/A 0 0 N/A loader state object free 156 0.0 N/A 0 0 N/A Latch Activity DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 -> "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 ------------------------ -------------- ------ ------ ------ ------------ ------ logminer context allocat 6 0.0 N/A 0 0 N/A messages 49,964 0.0 0.0 0 0 N/A mostly latch-free SCN 1,461 0.0 N/A 0 0 N/A multiblock read objects 18 0.0 N/A 0 0 N/A ncodef allocation latch 58 0.0 N/A 0 0 N/A object queue header heap 10,319 0.0 N/A 0 16 0.0 object queue header oper 48,238 0.0 N/A 0 0 N/A object stats modificatio 6 0.0 N/A 0 0 N/A parallel query alloc buf 480 0.0 N/A 0 0 N/A parameter list 34 0.0 N/A 0 0 N/A parameter table allocati 122 0.0 N/A 0 0 N/A post/wait queue 114 0.0 N/A 0 76 0.0 process allocation 116 0.0 N/A 0 58 0.0 process group creation 116 0.0 N/A 0 0 N/A qmn task queue latch 903 0.0 N/A 0 0 N/A redo allocation 6,620 0.0 0.0 0 11,598 0.0 redo copy 0 N/A N/A 0 11,599 0.1 redo writing 8,749 0.0 N/A 0 0 N/A resmgr group change latc 314 0.0 N/A 0 0 N/A resmgr:active threads 220 0.0 N/A 0 0 N/A resmgr:actses change gro 62 0.0 N/A 0 0 N/A resmgr:free threads list 214 0.0 N/A 0 0 N/A resmgr:schema config 6 0.0 N/A 0 0 N/A row cache objects 207,973 0.0 N/A 0 1,038 0.0 rules engine aggregate s 53 0.0 N/A 0 0 N/A rules engine rule set st 706 0.0 N/A 0 0 N/A sequence cache 317 0.0 N/A 0 0 N/A session allocation 211,236 0.0 0.0 0 0 N/A session idle bit 1,354 0.0 N/A 0 0 N/A session state list latch 272 0.0 N/A 0 0 N/A session switching 58 0.0 N/A 0 0 N/A session timer 1,221 0.0 N/A 0 0 N/A shared pool 52,026 0.0 N/A 0 0 N/A shared pool simulator 45,968 0.0 N/A 0 0 N/A sim partition latch 6 0.0 N/A 0 3 0.0 simulator hash latch 17,318 0.0 N/A 0 0 N/A simulator lru latch 2,816 0.0 N/A 0 13,740 0.0 slave class 13 0.0 N/A 0 0 N/A slave class create 52 1.9 1.0 0 0 N/A sort extent pool 89 0.0 N/A 0 0 N/A state object free list 48 0.0 N/A 0 0 N/A statistics aggregation 3,360 0.0 N/A 0 0 N/A threshold alerts latch 820 0.0 N/A 0 0 N/A transaction allocation 209 0.0 N/A 0 0 N/A transaction branch alloc 1,221 0.0 N/A 0 0 N/A undo global data 8,030 0.0 0.0 0 0 N/A user lock 124 0.0 N/A 0 0 N/A ------------------------------------------------------------- Latch Sleep Breakdown DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 -> ordered by misses desc Latch Name ---------------------------------------- Get Requests Misses Sleeps Spin Gets Sleep1 Sleep2 Sleep3 -------------- ----------- ----------- ---------- -------- -------- -------- cache buffers chains 931,684 38 2 36 0 0 0 library cache 62,134 1 1 0 0 0 0 slave class create 52 1 1 0 0 0 0 ------------------------------------------------------------- Latch Miss Sources DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- -------- cache buffers chains kcbchg: kslbegin: bufs not 0 1 0 cache buffers chains kcbgtcr: kslbegin excl 0 1 0 library cache kglScanDependency 0 1 0 slave class create ksvcreate 0 1 0 ------------------------------------------------------------- Parent Latch Statistics DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 No data exists for this section of the report. ------------------------------------------------------------- Child Latch Statistics DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 No data exists for this section of the report. ------------------------------------------------------------- Segments by Logical Reads DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 -> Total Logical Reads: 475,390 -> Captured Segments account for 66.6% of Total Tablespace Subobject Obj. Logical Owner Name Object Name Name Type Reads %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- SYS SYSTEM I_IND1 INDEX 53,280 11.21 SYS SYSTEM I_OBJ# INDEX 31,184 6.56 SYS SYSTEM SEG$ TABLE 23,392 4.92 SYS SYSTEM I_OBJ1 INDEX 23,360 4.91 SYS SYSTEM TAB$ TABLE 17,760 3.74 ------------------------------------------------------------- Segments by Physical Reads DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 -> Total Physical Reads: 4,511 -> Captured Segments account for 7.7% of Total Tablespace Subobject Obj. Physical Owner Name Object Name Name Type Reads %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- SYS SYSTEM IDL_UB1$ TABLE 147 3.26 SYS SYSAUX WRH$_SQL_PLAN TABLE 33 .73 SYS SYSTEM HISTGRM$ TABLE 25 .55 SYS SYSAUX WRH$_SQLSTAT 61112_1577 TABLE 21 .47 SYS SYSAUX WRH$_PARAMETER_NAME_ INDEX 9 .20 ------------------------------------------------------------- Segments by Row Lock Waits DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 -> % 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 SYSAUX WRH$_SYSSTAT_PK 61112_1577 INDEX 4 23.53 SYS SYSAUX WRH$_LATCH_MISSES_SU 61112_1577 INDEX 2 11.76 SYS SYSAUX WRH$_LATCH_PK 61112_1577 INDEX 2 11.76 SYS SYSAUX WRH$_SERVICE_STAT_PK 61112_1577 INDEX 2 11.76 SYS SYSAUX WRH$_SQLSTAT_INDEX 61112_1577 INDEX 2 11.76 ------------------------------------------------------------- Segments by ITL Waits DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 No data exists for this section of the report. ------------------------------------------------------------- Segments by Buffer Busy Waits DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 No data exists for this section of the report. ------------------------------------------------------------- Dictionary Cache Stats DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 -> "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 84 0.0 0 N/A 12 1 dc_database_links 2 50.0 0 N/A 0 1 dc_global_oids 394 4.6 0 N/A 0 30 dc_histogram_data 12,032 9.8 0 N/A 0 1,736 dc_histogram_defs 13,925 13.0 0 N/A 0 1,674 dc_object_ids 7,040 4.2 0 N/A 0 512 dc_objects 3,232 12.6 0 N/A 5 555 dc_profiles 64 0.0 0 N/A 0 1 dc_rollback_segments 456 0.0 0 N/A 0 22 dc_segments 2,353 13.1 0 N/A 16 293 dc_sequences 4 100.0 0 N/A 4 4 dc_tablespaces 20,533 0.0 0 N/A 0 28 dc_usernames 570 6.0 0 N/A 0 38 dc_users 14,964 0.2 0 N/A 0 44 outstanding_alerts 402 6.5 0 N/A 53 56 ------------------------------------------------------------- Library Cache Activity DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 -> "Pct Misses" should be very low Get Pct Pin Pct Invali- Namespace Requests Miss Requests Miss Reloads dations --------------- ------------ ------ -------------- ------ ---------- -------- BODY 625 0.5 1,151 2.3 23 0 CLUSTER 38 2.6 99 2.0 1 0 INDEX 36 8.3 104 20.2 18 0 SQL AREA 4,039 14.0 28,603 5.1 351 109 TABLE/PROCEDURE 1,220 17.3 5,486 13.8 366 0 TRIGGER 36 0.0 123 1.6 2 0 ------------------------------------------------------------- Process Memory Summary DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 -> 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 38.0 N/A 1.6 1.9 9 11 24 24 Freeable 19.8 .0 1.7 1.2 5 N/A 12 12 SQL 1.0 .5 .1 .1 0 150 14 12 PL/SQL .2 .1 .0 .0 0 14 22 22 E Other 37.1 N/A 1.5 1.9 9 11 24 24 Freeable 21.7 .0 1.7 1.1 5 N/A 13 13 SQL 1.0 .5 .1 .1 0 150 14 12 PL/SQL .2 .1 .0 .0 0 14 22 22 ------------------------------------------------------------- SGA Memory Summary DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 End Size (Bytes) SGA regions Begin Size (Bytes) (if different) ------------------------------ ------------------- ------------------- Database Buffers 1,392,508,928 Fixed Size 2,066,240 Redo Buffers 14,708,736 Variable Size 268,437,696 ------------------- sum 1,677,721,600 ------------------------------------------------------------- SGA breakdown difference DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 -> 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 16.0 16.0 0.00 large PX msg pool 1.0 1.0 0.00 large free memory 15.0 15.0 0.00 shared ASH buffers 15.5 15.5 0.00 shared CCursor 2.6 2.8 10.39 shared KCB Table Scan Buffer 4.0 4.0 0.00 shared KGH: NO ACCESS 39.8 39.8 0.00 shared KGLS heap 2.5 N/A -100.00 shared KSFD SGA I/O b 4.0 4.0 0.00 shared PL/SQL MPCODE N/A 3.3 N/A shared XDB Schema Cac 7.6 7.6 0.00 shared db_block_hash_buckets 11.3 11.3 0.00 shared dbwriter coalesce buffer 3.0 3.0 0.00 shared free memory 75.0 73.5 -1.97 shared kglsim hash table bkts 4.0 4.0 0.00 shared library cache 8.5 8.6 0.69 shared row cache 7.2 7.2 0.00 shared sql area 9.5 8.8 -7.52 stream KGH: NO ACCESS 16.0 16.0 0.00 stream free memory 32.0 32.0 0.00 buffer_cache 1,328.0 1,328.0 0.00 fixed_sga 2.0 2.0 0.00 log_buffer 14.0 14.0 0.00 ------------------------------------------------------------- Streams CPU/IO Usage DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 -> Streams processes ordered by CPU usage -> CPU and I/O Time in micro seconds Session Type CPU Time User I/O Time Sys I/O Time ------------------------- -------------- -------------- -------------- QMON Slaves 80,581 129,722 0 QMON Coordinator 9,503 0 0 ------------------------------------------------------------- Streams Capture DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 No data exists for this section of the report. ------------------------------------------------------------- Streams Apply DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 No data exists for this section of the report. ------------------------------------------------------------- Buffered Queues DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 No data exists for this section of the report. ------------------------------------------------------------- Buffered Subscribers DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 No data exists for this section of the report. ------------------------------------------------------------- Rule Set DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 -> Rule Sets ordered by Evaluations Fast SQL CPU Elapsed Ruleset Name Evals Evals Execs Time Time ------------------------- -------- -------- -------- -------- -------- SYS.ALERT_QUE_R 53 0 0 2 2 ------------------------------------------------------------- Resource Limit Stats DB/Inst: ORCL1/orcl1 Snap: 1620 No data exists for this section of the report. ------------------------------------------------------------- init.ora Parameters DB/Inst: ORCL1/orcl1 Snaps: 1614-1620 End value Parameter Name Begin value (if different) ----------------------------- --------------------------------- -------------- archive_lag_target 0 audit_file_dest D:\ORACLE\PRODUCT\10.2.0\ADMIN\OR background_dump_dest D:\ORACLE\PRODUCT\10.2.0\ADMIN\OR compatible 10.2.0.3.0 control_files D:\ORACLE\PRODUCT\10.2.0\ORADATA\ core_dump_dest D:\ORACLE\PRODUCT\10.2.0\ADMIN\OR db_block_size 8192 db_domain db_file_multiblock_read_count 16 db_name orcl1 db_recovery_file_dest D:\oracle\product\10.2.0\flash_re db_recovery_file_dest_size 2147483648 db_writer_processes 3 dispatchers (PROTOCOL=TCP) (SERVICE=orcl1XDB) fast_start_mttr_target 1800 job_queue_processes 10 open_cursors 300 pga_aggregate_target 406847488 processes 150 remote_login_passwordfile EXCLUSIVE sga_max_size 1677721600 sga_target 1677721600 undo_management AUTO undo_tablespace UNDOTBS1 user_dump_dest D:\ORACLE\PRODUCT\10.2.0\ADMIN\OR ------------------------------------------------------------- End of Report