E34：[JPOUG Presents] Oracle Database の隠されている様々な謎を解くセッション「なーんでだ？」再び @ db tech showcase 2015 Tokyo
661 Views
June 13, 15
スライド概要
Slideshare移行2023-08-10時点View : 7,113 views
個人事業主/Love SQL/Tuning/DBエンジニア/JPOUG/Oracle ACE Pro/ex-AWS BigData Consultant/ex-DEC/Oracle,PostgreSQL,Aurora,Redshift,Athena,SCT,DMS,Glue/偶にNowPlaying♪垂れ流し/
関連スライド
各ページのテキスト
〜 1
〜 2
3
👻 4
⬇ TABLE_NAME ----------------A B COLUMN_NAME -------------------ID DATA1 DATA2 DATA_TYPE -----------NUMBER VARCHAR2 VARCHAR2 INDEX_NAME COLUMN_NAME DESC -------------------- ------------ ---PK_A ID ASC NUM_ROWS ---------100 300 COLUMN_NAME -------------------ID BR# DATA1 DATA2 DATA_TYPE ---------------NUMBER NUMBER VARCHAR2 VARCHAR2 INDEX_NAME -------------------PK_B PK_B COLUMN_NAME --------------ID BR# 5 DESC ---ASC ASC
⬆ 〜 SELECT /*+ SELECT /*+ LEADING(a b) USE_NL(a b) LEADING(b a) USE_NL(b a) */ a.id ,b.br# ,SUBSTR(a.data1, 10) AS a_data1 ,SUBSTR(b.data1, 10) AS b_data1 ⏬ */ a.id ,b.br# ,SUBSTR(a.data1, 10) AS a_data1 ,SUBSTR(b.data1, 10) AS b_data1 FROM FROM a INNER JOIN b ON a.id = b.id WHERE a.id BETWEEN 1 AND 5 ORDER BY a.id ,b.br#; a INNER JOIN b ON a.id = b.id WHERE a.id BETWEEN 1 AND 5 ORDER BY a.id ,b.br#; 6 ？
SELECT a.id ,b.br# ,SUBSTR(a.data1, 10) AS a_data1 ,SUBSTR(b.data1, 10) AS b_data1 FROM a INNER JOIN b ON a.id = b.id WHERE a.id BETWEEN 1 AND 5 ORDER BY a.id ,b.br#; 7
SQL Plan Monitoring Details (Plan Hash Value=757367763) ============================================================================================================ | Id | Operation | Name |・| Time | Start | Execs | Rows | Mem |・| | | | |・| Active(s) | Active | | (Actual) | (Max) |・| ============================================================================================================= | 0 | SELECT STATEMENT | |・| 1 | +0 | 1 | 15 | |・| | 1 | SORT ORDER BY | |・| 1 | +0 | 1 | 15 | 129K |・| | 2 | NESTED LOOPS | |・| 1 | +0 | 1 | 15 | |・| | 3 | | |・| 1 | +0 | 1 | 15 | |・| | 4 | |・| 1 | +0 | 1 | 5 | |・| | 5 | | PK_A |・| 1 | +0 | 1 | 5 | |・| | 6 | | PK_B |・| 1 | +0 | 5 | 15 | |・| | 7 | | B 1 | +0 | 15 | 15 | |・| NESTED LOOPS TABLE ACCESS BY INDEX ROWID BATCHED | A INDEX RANGE SCAN INDEX RANGE SCAN TABLE ACCESS BY INDEX ROWID |・| ============================================================================================================= 8 ⬆
SQL Plan Monitoring Details (Plan Hash Value=757367763) ============================================================================================================ | Id | Operation | Name |・| Time | Start | Execs | Rows | Mem |・| | | | |・| Active(s) | Active | | (Actual) | (Max) |・| ============================================================================================================= | 0 | SELECT STATEMENT | |・| 1 | +0 | 1 | 15 | |・| | 1 | SORT ORDER BY | |・| 1 | +0 | 1 | 15 | 129K |・| | 2 | NESTED LOOPS | |・| 1 | +0 | 1 | 15 | |・| | 3 | | |・| 1 | +0 | 1 | 15 | |・| | 4 | |・| 1 | +0 | 1 | 5 | |・| | 5 | | PK_A |・| 1 | +0 | 1 | 5 | |・| | 6 | | PK_B |・| 1 | +0 | 5 | 15 | |・| | 7 | | B 1 | +0 | 15 | 15 | |・| NESTED LOOPS TABLE ACCESS BY INDEX ROWID BATCHED | A INDEX RANGE SCAN INDEX RANGE SCAN TABLE ACCESS BY INDEX ROWID |・| ============================================================================================================= 8 ⬆
SQL Plan Monitoring Details (Plan Hash Value=757367763) ============================================================================================================ | Id | Operation | Name |・| Time | Start | Execs | Rows | Mem |・| | | | |・| Active(s) | Active | | (Actual) | (Max) |・| ============================================================================================================= | 0 | SELECT STATEMENT | |・| 1 | +0 | 1 | 15 | |・| | 1 | SORT ORDER BY | |・| 1 | +0 | 1 | 15 | 129K |・| | 2 | NESTED LOOPS | |・| 1 | +0 | 1 | 15 | |・| | 3 | | |・| 1 | +0 | 1 | 15 | |・| | 4 | |・| 1 | +0 | 1 | 5 | |・| | 5 | | PK_A |・| 1 | +0 | 1 | 5 | |・| | 6 | | PK_B |・| 1 | +0 | 5 | 15 | |・| | 7 | | B 1 | +0 | 15 | 15 | |・| NESTED LOOPS TABLE ACCESS BY INDEX ROWID BATCHED | A INDEX RANGE SCAN INDEX RANGE SCAN TABLE ACCESS BY INDEX ROWID |・| ============================================================================================================= 8 ⬆
SQL Plan Monitoring Details (Plan Hash Value=757367763) ============================================================================================================ | Id | Operation | Name |・| Time | Start | Execs | Rows | Mem |・| | | | |・| Active(s) | Active | | (Actual) | (Max) |・| ============================================================================================================= | 0 | SELECT STATEMENT | |・| 1 | +0 | 1 | 15 | |・| | 1 | SORT ORDER BY | |・| 1 | +0 | 1 | 15 | 129K |・| | 2 | NESTED LOOPS | |・| 1 | +0 | 1 | 15 | |・| | 3 | | |・| 1 | +0 | 1 | 15 | |・| | 4 | |・| 1 | +0 | 1 | 5 | |・| | 5 | | PK_A |・| 1 | +0 | 1 | 5 | |・| | 6 | | PK_B |・| 1 | +0 | 5 | 15 | |・| | 7 | | B 1 | +0 | 15 | 15 | |・| NESTED LOOPS TABLE ACCESS BY INDEX ROWID BATCHED | A INDEX RANGE SCAN INDEX RANGE SCAN TABLE ACCESS BY INDEX ROWID |・| ============================================================================================================= 9 ⬆
SQL Plan Monitoring Details (Plan Hash Value=1248002027) ============================================================================================ | Id | Operation | Name |・| Time | Start | Execs | Rows |・| | | | |・| Active(s) | Active | | (Actual) |・| ============================================================================================ | 0 | SELECT STATEMENT | |・| 1 | +0 | 1 | 15 |・| | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | NESTED LOOPS | |・| 1 | +0 | 1 | 15 |・| | |・| 1 | +0 | 1 | 15 |・| |・| 1 | +0 | 1 | 15 |・| INDEX RANGE SCAN | PK_B |・| 1 | +0 | 1 | 15 |・| INDEX UNIQUE SCAN | PK_A |・| 1 | +0 | 15 | 15 |・| | A 1 | +0 | 15 | 15 |・| NESTED LOOPS TABLE ACCESS BY INDEX ROWID | B TABLE ACCESS BY INDEX ROWID |・| ============================================================================================ 10 ⬆
SQL Plan Monitoring Details (Plan Hash Value=1248002027) ============================================================================================ | Id | Operation | Name |・| Time | Start | Execs | Rows |・| | | | |・| Active(s) | Active | | (Actual) |・| ============================================================================================ | 0 | SELECT STATEMENT | |・| 1 | +0 | 1 | 15 |・| | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | NESTED LOOPS | |・| 1 | +0 | 1 | 15 |・| | |・| 1 | +0 | 1 | 15 |・| |・| 1 | +0 | 1 | 15 |・| INDEX RANGE SCAN | PK_B |・| 1 | +0 | 1 | 15 |・| INDEX UNIQUE SCAN | PK_A |・| 1 | +0 | 15 | 15 |・| | A 1 | +0 | 15 | 15 |・| NESTED LOOPS TABLE ACCESS BY INDEX ROWID | B TABLE ACCESS BY INDEX ROWID |・| ============================================================================================ 10 ⬆
SQL Plan Monitoring Details (Plan Hash Value=1248002027) ============================================================================================ | Id | Operation | Name |・| Time | Start | Execs | Rows |・| | | | |・| Active(s) | Active | | (Actual) |・| ============================================================================================ | 0 | SELECT STATEMENT | |・| 1 | +0 | 1 | 15 |・| | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | NESTED LOOPS | |・| 1 | +0 | 1 | 15 |・| | |・| 1 | +0 | 1 | 15 |・| |・| 1 | +0 | 1 | 15 |・| INDEX RANGE SCAN | PK_B |・| 1 | +0 | 1 | 15 |・| INDEX UNIQUE SCAN | PK_A |・| 1 | +0 | 15 | 15 |・| | A 1 | +0 | 15 | 15 |・| NESTED LOOPS TABLE ACCESS BY INDEX ROWID | B TABLE ACCESS BY INDEX ROWID |・| ============================================================================================ 10 ⬆
11 ⬆
Plan Statistics Stat Name Statement Per Execution % Snap ---------------------------------------- ---------- -------------- ------Elapsed Time (ms) 12,645 0.1 76.9 CPU Time (ms) 12,540 0.1 79.1 Executions 100,000 N/A N/A Buffer Gets 2,300,000 23.0 99.6 Disk Reads 0 0.0 0.0 Parse Calls 1 0.0 0.3 Rows 1,500,000 15.0 N/A User I/O Wait Time (ms) 0 N/A N/A Cluster Wait Time (ms) 0 N/A N/A Application Wait Time (ms) 0 N/A N/A Concurrency Wait Time (ms) 0 N/A N/A Invalidations 0 N/A N/A Version Count 1 N/A N/A Sharable Mem(KB) 27 N/A N/A ------------------------------------------------------------- 12 ⬆
Plan Statistics Stat Name Statement Per Execution % Snap ---------------------------------------- ---------- -------------- ------Elapsed Time (ms) 6,972 0.1 68.4 CPU Time (ms) 7,231 0.1 72.9 Executions 100,000 N/A N/A Buffer Gets 3,500,000 35.0 99.8 Disk Reads 0 0.0 0.0 Parse Calls 1 0.0 0.3 Rows 1,500,000 15.0 N/A User I/O Wait Time (ms) 0 N/A N/A Cluster Wait Time (ms) 0 N/A N/A Application Wait Time (ms) 0 N/A N/A Concurrency Wait Time (ms) 0 N/A N/A Invalidations 0 N/A N/A Version Count 1 N/A N/A Sharable Mem(KB) 27 N/A N/A ------------------------------------------------------------- 13 ⬆
Plan Statistics Stat Name Statement Per Execution % Snap ---------------------------------------- ---------- -------------- ------Elapsed Time (ms) 6,972 0.1 68.4 CPU Time (ms) 7,231 0.1 72.9 Executions 100,000 N/A N/A Buffer Gets 3,500,000 35.0 99.8 Disk Reads 0 0.0 0.0 Parse Calls 1 0.0 0.3 Rows 1,500,000 15.0 N/A User I/O Wait Time (ms) 0 N/A N/A Cluster Wait Time (ms) 0 N/A N/A Application Wait Time (ms) 0 N/A N/A Concurrency Wait Time (ms) 0 N/A N/A Invalidations 0 N/A N/A Version Count 1 N/A N/A Sharable Mem(KB) 27 N/A N/A ------------------------------------------------------------- 13 ⬆
👻 14
接続されました。 1 declare 2 cursor cs_foobar is 3 select 4 rowid 5 from 6 foobar 7 where 8 id between 1 and 100000 9 order by 10 dbms_rowid.rowid_block_number(rowid) 11 for update; 12 begin 13 for row in cs_foobar loop 14 update foobar 15 set 16 a = lpad('y',1795,'y') 17 where 18 rowid = row.rowid; 19 end loop; 20 commit; 21* end; PL/SQLプロシージャが正常に完了しました。 経過: 00:03:03.40 15
接続されました。 1 declare 2 cursor cs_foobar is 3 select 4 rowid 5 from 6 foobar 7 where 8 id between 1 and 100000 9 order by 10 dbms_rowid.rowid_block_number(rowid) 11 for update; 12 begin 13 for row in cs_foobar loop 14 update foobar 15 set 16 a = lpad('y',1795,'y') 17 where 18 rowid = row.rowid; 19 end loop; 20 commit; 21* end; PL/SQLプロシージャが正常に完了しました。 経過: 00:03:03.40 15
SQL ordered by Executions DB/Inst: NONPDB12/nonpdb12c Snaps: 21-31 -> End Executions Threshold: 100 Total Executions: 122,051 -> Captured SQL accounts for 16.1% of Total Executions -> SQL reported below exceeded 1.0% of Total Executions CPU per Elap per Old Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value ------------ --------------- ---------------- ----------- ---------- ---------6,107 5,129 0.8 0.00 0.00 4274598960 select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, tim estamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj# =:1 and intcol#=:2 2,656 41,093 15.5 0.00 0.00 select /*+ rule */ bucket, endpoint, col#, epvalue, epvalue_raw, ep_repeat_count from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket 343569411 1,858 2,747 1.5 0.00 0.00 select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where c on#=:1 2482976222 16
SQL ordered by Executions DB/Inst: NONPDB12/nonpdb12c Snaps: 21-31 -> End Executions Threshold: 100 Total Executions: 122,051 -> Captured SQL accounts for 16.1% of Total Executions CPU per Elap -> SQL reported below exceeded 1.0% of Total Executions per Old Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value ------------ --------------- -------------------------------------------CPU per Elap per Old Processed Rows per Exec Exec (s) Exec0.00 (s) Hash Value 0.00 100,000 Executions Rows 100,000 1.0 3389821460 ------------ --------------- ---------------- ----------- ---------- ---------Module: SQL*Plus 6,107 5,129 0.8 0.00 0.00 4274598960 UPDATE FOOBAR SET A = LPAD('y',1795,'y') WHERE ROWID select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, tim = :B1 estamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj# =:1 and intcol#=:2 2,656 41,093 15.5 0.00 0.00 select /*+ rule */ bucket, endpoint, col#, epvalue, epvalue_raw, ep_repeat_count from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket 343569411 1,858 2,747 1.5 0.00 0.00 select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where c on#=:1 2482976222 16
SQL ordered by Executions DB/Inst: NONPDB12/nonpdb12c Snaps: 21-31 -> End Executions Threshold: 100 Total Executions: 122,051 -> Captured SQL accounts for 16.1% of Total Executions -> SQL reported below exceeded 1.0% of Total Executions CPU per Elap per Old Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value ------------ --------------- ---------------- ----------- ---------- ---------6,107 5,129 0.8 0.00 0.00 4274598960 select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, tim estamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj# =:1 and intcol#=:2 2,656 41,093 15.5 0.00 0.00 select /*+ rule */ bucket, endpoint, col#, epvalue, epvalue_raw, ep_repeat_count from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket 343569411 1,858 2,747 1.5 0.00 0.00 select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where c on#=:1 2482976222 16
〜 👻 17
18
STATSPACK report for Load Profile Instance Efficiency Indicators Top 5 Timed Event Instance CPU Memory Statistics Statistic Foreground Wait Events Background Wait Events Wait Events (fg and bg) Wait Event Histogram SQL SQL SQL SQL ordered ordered ordered ordered by by by by CPU Gets Executions Parse Calls Instance Activity Stats OS Statistics IO Stat by Function - summary/detail Tablespace IO Stats File IO Stats File Read Histogram Stats IO Stats Function Detail Instance Recovery Stats Memory Dynamic Components Memory Resize Operations Buffer Pool Advisory Buffer Pool Statistics PGA Aggr Target Stats PGA Aggr Target Histogram PGA Memory Advisory Process Memory Summary Stats Shared Pool Advisory Cache Size Changes SGA Target Advisory SGA breakdown difference init.ora Parameters ⏩ 19
STATSPACK report for Database DB Id Instance Inst Num Startup Time Release RAC ~~~~~~~~ ----------- ------------ -------- --------------- ----------- --4044466261 nonpdb12c 1 31-5月 -15 22:0 12.1.0.2.0 NO 3 Host Name Platform CPUs Cores Sockets Memory (G) ~~~~ ---------------- ---------------------- ----- ----- ------- -----------foobar Linux x86 64-bit 4 4 1 7.9 Snapshot Snap Id Snap Time Sessions Curs/Sess Comment ~~~~~~~~ ---------- ------------------ -------- --------- -----------------Begin Snap: 83 31-5月 -15 22:05:54 32 .9 End Snap: 93 31-5月 -15 22:08:53 Elapsed: DB time: 2.98 (mins) Av Act Sess: 2.95 (mins) DB CPU: 20 31 .9 1.0 0.96 (mins) ⬆
Cache Sizes Begin End ~~~~~~~~~~~ ---------- ---------Buffer Cache: 532M 524M Shared Pool: 172M 180M Load Profile Per Second ~~~~~~~~~~~~ -----------------DB time(s): 1.0 DB CPU(s): 0.3 Redo size: 2,380,617.8 Logical reads: 3,113.7 Block changes: 2,952.4 Physical reads: 2,037.4 Physical writes: 1,575.3 User calls: 0.3 Parses: 9.3 Hard parses: 2.3 W/A MB processed: 0.3 Logons: 0.1 Executes: 611.5 Rollbacks: 0.0 Transactions: 0.0 Std Block Size: Log Buffer: 8K 7,432K Per Transaction Per Exec Per Call ----------------- ----------- ----------22.1 0.00 3.28 7.2 0.00 1.06 53,266,323.0 69,668.9 66,059.1 45,586.9 35,247.9 6.8 208.8 50.9 6.2 1.1 13,682.0 0.0 21 ⬆
Instance Efficiency Indicators ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 100.00 Buffer Hit %: 34.57 Library Hit %: 97.70 Execute to Parse %: 98.47 Parse CPU to Parse Elapsd %: 98.61 Shared Pool Statistics Memory Usage %: % SQL with executions>1: % Memory for SQL w/exec>1: Begin -----89.03 77.86 75.17 Redo NoWait %: Optimal W/A Exec %: Soft Parse %: Latch Hit %: % Non-Parse CPU: 100.00 100.00 75.63 99.99 97.60 End -----91.18 79.00 78.51 ⬆ 22
Top 5 Timed Events Avg %Total ~~~~~~~~~~~~~~~~~~ wait Call Event Waits Time (s) (ms) Time ----------------------------------------- ------------ ----------- ------ -----LGWR worker group idle 432 478 1105 35.1 lreg timer 63 180 2860 13.2 AQPC idle 6 180 30005 13.2 heartbeat redo informer 179 179 1000 13.2 db file sequential read 200,834 135 1 9.9 ------------------------------------------------------------Host CPU (CPUs: 4 Cores: 4 Sockets: 1) ~~~~~~~~ Load Average Begin End User System Idle WIO WCPU ------- ------------- ------- ------- ------- -------0.10 1.02 6.68 2.71 90.44 14.26 Note: There is a 6% discrepancy between the OS Stat total CPU time and the total CPU time estimated by Statspack OS Stat CPU time: 675(s) (BUSY_TIME + IDLE_TIME) Statspack CPU time: 716(s) (Elapsed time * num CPUs in end snap) 23 ⬆
Instance CPU ~~~~~~~~~~~~ Host: Total time (s): Host: Busy CPU time (s): % of time Host is Busy: Instance: Total CPU time (s): % of Busy CPU used for Instance: Instance: Total Database time (s): %DB time waiting for CPU (Resource Mgr): % Time (seconds) -------- -------------675.4 64.6 9.6 64.0 99.1 204.2 0.0 Memory Statistics ~~~~~~~~~~~~~~~~~ Begin End ------------ -----------Host Mem (MB): 8,040.8 8,040.8 SGA use (MB): 768.0 768.0 PGA use (MB): 88.2 90.2 % Host Mem used for SGA+PGA: 10.6 10.7 ------------------------------------------------------------- 24 ⬆
Statistic Time (s) % DB time ----------------------------------- -------------------- --------sql execute elapsed time 177.0 100.0 DB CPU 57.4 32.4 PL/SQL execution elapsed time 3.5 2.0 parse time elapsed 1.9 1.1 hard parse elapsed time 1.9 1.1 connection management call elapsed 0.1 .1 repeated bind elapsed time 0.0 .0 PL/SQL compilation elapsed time 0.0 .0 sequence load elapsed time 0.0 .0 DB time 177.1 background elapsed time 27.1 background cpu time 6.6 ------------------------------------------------------------- ⬆ 25
Foreground Wait Events -> Only events with Total Wait Time (s) >= .001 are shown -> ordered by Total Wait Time desc, Waits desc (idle events last) Avg %Total %Tim Total Wait wait Waits Call Event Waits out Time (s) (ms) /txn Time ---------------------------- ------------ ---- ---------- ------ -------- -----db file sequential read 200,794 0 135 1 ######## 9.9 db file scattered read 1,516 0 8 5 189.5 .6 log file switch completion 4 0 0 24 0.5 .0 control file sequential read 266 0 0 0 33.3 .0 log file switch (private str 2 0 0 23 0.3 .0 log file sync 3 0 0 2 0.4 .0 reliable message 10 0 0 0 1.3 .0 Disk file operations I/O 8 0 0 0 1.0 .0 SQL*Net message from client 26 0 0 1 3.3 ------------------------------------------------------------- ⬆ 26
Background Wait Events -> Only events with Total Wait Time (s) >= .001 are shown -> ordered by Total Wait Time desc, Waits desc (idle events last) Event Waits ---------------------------- -----------LGWR worker group idle 432 lreg timer 63 AQPC idle 6 heartbeat redo informer 179 Streams AQ: load balancer id 1 db file parallel write 2,179 log file parallel write 430 db file async I/O submit 1,119 control file parallel write 151 control file sequential read 453 oracle thread bootstrap 7 Parameter File I/O 16 db file sequential read 40 target log write size 2 direct path write 6 log file single write 10 os thread creation 7 direct path read 5 Disk file operations I/O 27 log file sequential read 10 LGWR all worker groups 5 %Tim Total Wait out Time (s) ---- ---------0 478 95 180 100 180 0 179 100 120 0 16 0 4 0 1 0 0 0 0 0 0 0 0 0 0 100 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Avg %Total wait Waits Call (ms) /txn Time ------ -------- -----1105 54.0 35.1 2860 7.9 13.2 30005 0.8 13.2 1000 22.4 13.2 ###### 0.1 8.8 7 272.4 1.2 9 53.8 .3 1 139.9 .1 3 18.9 .0 1 56.6 .0 18 0.9 .0 3 2.0 .0 1 5.0 .0 10 0.3 .0 2 0.8 .0 1 1.3 .0 1 0.9 .0 1 0.6 .0 0 3.4 .0 0 1.3 .0 0 0.6 .0 27 ⬆
Wait Events (fg and bg) -> s - second, cs - centisecond, ms - millisecond, us - microsecond -> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0 -> Only events with Total Wait Time (s) >= .001 are shown -> ordered by Total Wait Time desc, Waits desc (idle events last) Event Waits ---------------------------- -----------LGWR worker group idle 432 lreg timer 63 AQPC idle 6 heartbeat redo informer 179 db file sequential read 200,834 Streams AQ: load balancer id 1 db file parallel write 2,179 db file scattered read 1,516 log file parallel write 430 db file async I/O submit 1,119 control file parallel write 151 control file sequential read 719 oracle thread bootstrap 7 log file switch completion 4 log file switch (private str 2 Parameter File I/O 16 target log write size 2 direct path write 6 log file single write 10 os thread creation 7 direct path read 5 Disk file operations I/O 35 log file sync 3 log file sequential read 10 reliable message 10 LGWR all worker groups 5 Avg %Total %Tim Total Wait wait Waits Call out Time (s) (ms) /txn Time ---- ---------- ------ -------- -----0 478 1105 54.0 35.1 95 180 2860 7.9 13.2 100 180 30005 0.8 13.2 0 179 1000 22.4 13.2 0 135 1 ######## 9.9 100 120 ###### 0.1 8.8 0 16 7 272.4 1.2 0 8 5 189.5 .6 0 4 9 53.8 .3 0 1 1 139.9 .1 0 0 3 18.9 .0 0 0 0 89.9 .0 0 0 18 0.9 .0 0 0 24 0.5 .0 0 0 23 0.3 .0 0 0 3 2.0 .0 100 0 10 0.3 .0 0 0 2 0.8 .0 0 0 1 1.3 .0 0 0 1 0.9 .0 0 0 1 0.6 .0 0 0 0 4.4 .0 0 0 2 0.4 .0 0 0 0 1.3 .0 0 0 0 1.3 .0 0 0 0 0.6 .0 28 ⬆
Wait Event Histogram -> Total Waits - units: K is 1000, M is 1000000, G is 1000000000 -> % of Waits - column heading: <=1s is truly <1024ms, >1s is truly >=1024ms -> % of Waits - value: .0 indicates value was <.05%, null is truly 0 -> Ordered by Event (idle events last) Event -------------------------AQPC idle Disk file operations I/O LGWR all worker groups LGWR worker group idle Parameter File I/O Streams AQ: load balancer asynch descriptor resize control file parallel writ control file sequential re db file async I/O submit db file parallel write db file scattered read db file sequential read direct path read direct path write heartbeat redo informer latch free latch: cache buffers lru c latch: shared pool log file parallel write log file sequential read log file single write log file switch (private s log file switch completion log file sync lreg timer oracle thread bootstrap SQL*Net message to client Total Waits ----6 35 5 432 16 1 19 151 719 1119 2179 1516 200K 5 6 179 1 1 5 430 10 10 2 4 3 63 7 32 ----------------- % of Waits -----------------<1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s ----- ----- ----- ----- ----- ----- ----- ----100.0 97.1 2.9 100.0 .5 90.7 8.8 62.5 12.5 6.3 18.8 100.0 100.0 46.4 46.4 2.0 5.3 96.1 2.1 .8 .1 .7 .1 78.9 16.5 3.1 .7 .5 .1 .1 42.5 2.5 3.3 3.6 37.4 10.7 34.4 14.7 1.6 28.4 11.9 9.0 94.4 4.7 .4 .2 .3 .0 60.0 40.0 50.0 50.0 100.0 100.0 100.0 100.0 1.4 .7 41.6 53.7 2.6 100.0 70.0 30.0 100.0 100.0 33.3 66.7 4.8 95.2 14.3 85.7 100.0 29 ⬆
SQL ordered by CPU -> Total DB CPU (s): -> Captured SQL accounts for -> SQL reported below exceeded 57 3.1% of Total DB CPU 1.0% of Total DB CPU CPU CPU per Elapsd Old Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash Value ---------- ------------ ---------- ------ ---------- --------------- ---------1.02 1 1.02 1.8 1.10 13,200 4269626113 Module: SQL*Plus BEGIN :snap# := statspack.snap(i_snap_level=>7); END; ⬆ 30
SQL ordered by Gets -> End Buffer Gets Threshold: -> Captured SQL accounts for -> SQL reported below exceeded 10000 Total Buffer Gets: 5.8% of Total Buffer Gets 1.0% of Total Buffer Gets 557,351 CPU Elapsd Old Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------13,200 1 13,200.0 2.4 1.02 1.10 4269626113 Module: SQL*Plus BEGIN :snap# := statspack.snap(i_snap_level=>7); END; 7,130 2,399 3.0 1.3 0.12 0.16 4274598960 select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, tim estamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj# =:1 and intcol#=:2 ⬆ 31
SQL ordered by Executions -> End Executions Threshold: -> Captured SQL accounts for -> SQL reported below exceeded 100 Total Executions: 7.7% of Total Executions 1.0% of Total Executions 109,456 CPU per Elap per Old Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value ------------ --------------- ---------------- ----------- ---------- ---------2,399 2,327 1.0 0.00 0.00 4274598960 select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, tim estamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj# =:1 and intcol#=:2 ⬆ 32
SQL ordered by Parse Calls -> End Parse Calls Threshold: 1000 Total Parse Calls: -> Captured SQL accounts for 69.0% of Total Parse Calls -> SQL reported below exceeded 1.0% of Total Parse Calls 1,670 % Total Old Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------210 210 12.57 2018736380 select timestamp, flags from fixed_obj$ where obj#=:1 139 139 8.32 1198893840 select order#,columns,types from access$ where d_obj#=:1 139 139 8.32 2824870641 select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_ obj#, nvl(property,0),subname,type#,flags,d_attrs from dependenc y$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order# 117 117 7.01 4118814203 select decode(u.type#, 2, u.ext_username, u.name), o.name, t.update$, t.insert$, t.delete$, t.enabled, decode(bita nd(t.property, 8192),8192, 1, 0), decode(bitand(t.propert y, 65536), 65536, 1, 0), decode(bitand(t.property, 131072) 112 112 6.71 1512023764 select obj#, name, stab#, sobj#, sobjd#, ttab#, tobj#, tobjd#, m flags from rmtab$ where (stab# = :1 or ttab# = :1) order by obj# 33 ⬆
Instance Activity Stats Statistic Total per Second per Trans --------------------------------- ------------------ -------------- -----------rollback changes - undo records a 0 0.0 0.0 rollbacks only - consistent read 0 0.0 0.0 rows fetched via callback 678 3.8 84.8 session connect time 180 1.0 22.5 session cursor cache hits 43,551 243.3 5,443.9 session logical reads 557,351 3,113.7 69,668.9 session pga memory 2,362,616 13,199.0 295,327.0 session pga memory max 1,576,184 8,805.5 197,023.0 session uga memory 400,216 2,235.8 50,027.0 session uga memory max 21,289,200 118,934.1 2,661,150.0 shared hash latch upgrades - no w 160 0.9 20.0 shared io pool buffer get success 0 0.0 0.0 sorts (memory) 2,984 16.7 373.0 sorts (rows) 127,277 711.0 15,909.6 sql area evicted 218 1.2 27.3 sql area purged 0 0.0 0.0 summed dirty queue length 415,865 2,323.3 51,983.1 switch current to new buffer 3 0.0 0.4 34 ⬆
OS Statistics -> ordered by statistic type (CPU use, Virtual Memory, Hardware Config), Name Statistic Total ------------------------- ---------------------SWAP_FREE_BYTES 2,113,925,120 BUSY_TIME 6,460 IDLE_TIME 61,078 IOWAIT_TIME 9,632 SYS_TIME 1,830 USER_TIME 4,511 PHYSICAL_MEMORY_BYTES 8,431,374,336 NUM_CPUS 4 NUM_CPU_CORES 4 NUM_CPU_SOCKETS 1 GLOBAL_RECEIVE_SIZE_MAX 4,194,304 GLOBAL_SEND_SIZE_MAX 1,048,576 TCP_RECEIVE_SIZE_DEFAULT 87,380 TCP_RECEIVE_SIZE_MAX 6,291,456 TCP_RECEIVE_SIZE_MIN 4,096 TCP_SEND_SIZE_DEFAULT 16,384 TCP_SEND_SIZE_MAX 4,194,304 TCP_SEND_SIZE_MIN 4,096 OS Statistics - detail Snap Snapshot Id Day Time Load %Busy %User %System %WIO %WCPU ------ --------------- ------ ------ ------ ------- ------ -----83 日 31 22:05:54 .1 93 日 31 22:08:53 1.0 9.6 6.7 2.7 14.3 35 ⬆
IO Stat by Function - summary ->Data Volume values suffixed with M,G,T,P are in multiples of 1024, other values suffixed with K,M,G,T,P are in multiples of 1000 ->ordered by Data Volume (Read+Write) desc ---------- Read --------- --------- Write -------- --- Wait ---Data Requests Data Data Requests Data Avg Function Volume /sec Vol/sec Volume /sec Vol/sec Count Tm(ms) --------------- ------ -------- -------- ------ -------- -------- ------ -----Buffer Cache Re 2849M 1130.3 15.9M 202K 0.0 LGWR 1M .3 .0M 423M 4.7 2.4M 552 0.0 Others 10M 3.8 .1M 3M 1.2 .0M 789 0.0 Direct Reads 1M .1 .0M .0 0.0 Direct Writes .0 .1 0.0 ------------------------------------------------------------IO Stat by Function - detail ->ordered by Data Volume (Read+Write) desc ----------- Read ---------- ----------- Write --------Small Large Small Large Small Large Small Large Read Read Data Data Write Write Data Data Function Reqs Reqs Read Read Reqs Reqs Writn Writn ------------------ ------ ------ ------ ------ ------ ------ ------ -----Buffer Cache Reads 201K 1436 1575M 1274M LGWR 62 1M 332 513 5M 418M Others 677 10M 214 3M Direct Reads 19 1M 2 Direct Writes 2 19 ------------------------------------------------------------- 36 ⬆
Tablespace IO Stats ->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 201,520 1,126 0.7 1.8 18,970 106 0 0.0 UNDOTBS1 9 0 1.1 1.0 1,107 6 0 0.0 SYSAUX 340 2 0.3 1.0 196 1 0 0.0 SYSTEM 478 3 0.4 1.0 28 0 0 0.0 EXAMPLE 3 0 0.0 1.0 3 0 0 0.0 —————————————————————————————— 37 ⬆
File IO Stats ->Mx Rd Bkt: Max bucket time for single block read ->ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------Av Mx Av Av Rd Rd Av Av Buffer BufWt Reads Reads/s (ms) Bkt Blks/Rd Writes Writes/s Waits (ms) -------------- ------- ----- --- ------- ------------ -------- ---------- -----EXAMPLE /u01/oradata/nonpdb12c/example01.dbf 3 0 0.0 1.0 3 0 0 SYSAUX 340 2 /u01/oradata/nonpdb12c/sysaux01.dbf 0.3 1 1.0 196 1 0 3 /u01/oradata/nonpdb12c/system01.dbf 0.4 1 1.0 28 0 0 0 /u01/oradata/nonpdb12c/undotbs01.dbf 1.1 1 1.0 1,107 6 0 SYSTEM 478 UNDOTBS1 9 USERS /u01/oradata/nonpdb12c/users01.dbf 201,520 1,126 0.7 1 1.8 18,970 106 0 ------------------------------------------------------------- 38 ⬆
File Read Histogram Stats ->Number of single block reads in each time range ->Tempfiles are not included ->ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------0 - 2 ms 2 - 4 ms 4 - 8 ms 8 - 16 ms 16 - 32 ms 32+ ms ------------ ------------ ------------ ------------ ------------ -----------SYSAUX /u01/oradata/nonpdb12c/sysaux01.dbf 333 0 0 0 0 0 SYSTEM 459 UNDOTBS1 6 USERS 188,707 /u01/oradata/nonpdb12c/system01.dbf 0 0 0 0 0 /u01/oradata/nonpdb12c/undotbs01.dbf 0 0 0 0 0 /u01/oradata/nonpdb12c/users01.dbf 0 0 0 0 0 ⬆ 39
IO Stats Function Detail Function Name -----------DBWR DBWR LGWR LGWR LGWR LGWR Streams AQ Buffer Cache Buffer Cache Direct Reads Others(14) Others(14) Others(14) Others(14) Others(14) FileType Name -----------Control File Data File Control File Data File Log File Other(8) Data File Data File Other(8) Data File Control File Data File Other(8) Parameter Fi External Tab Small Small Large Large Read Write Read Write Wait Time (MB) (MB) (MB) (MB) Waits (ms) ------- ------- ------- ------- --------- ----------0 0 0 0 0 0 0 84 0 2117 0 0 1 2 0 0 52 26 0 0 0 0 0 0 0 3 0 418 20 15 0 0 0 0 480 3903 0 0 0 0 0 0 1575 0 1274 0 202331 147785 0 0 0 0 0 0 1 0 0 0 0 0 10 3 0 0 671 272 0 0 0 0 0 0 0 0 0 0 102 296 0 0 0 0 16 42 0 0 0 0 0 0 0 0 0 0 0 0 40 ⬆
Instance Recovery Stats -> 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 8 424 3469 331776 331776 E 0 45 44244 287011 331776 331776 ------------------------------------------------------------- ⬆ 41
Memory Dynamic Components -> Op - memory resize Operation -> Cache: D: Default, K: Keep, R: Recycle -> Mode: DEF: DEFerred mode, IMM: IMMediate mode Begin Snap End Snap Op Last Op Cache Size (M) Size (M) Count Type/Mode Last Op Time ---------------------- ---------- -------- ------- ---------- --------------D:buffer cache 532 524 1 SHRINK/DEF 31-5月 22:05:5 7 PGA Target SGA Target Shared IO Pool 768 768 28 0 STATIC 0 STATIC 0 GROW/IMM 31-5月 22:03:5 5 java pool large pool shared pool 4 20 0 STATIC 0 SHRINK/DEF 31-5月 22:04:2 172 7 31-5月 22:05:5 180 1 GROW/DEF 7 42 ⬆
Memory Resize Operations -> Status: ERR: ERRor, COM: COMplete, PEN: PENding -> Delta - Target change in size (MB) -> Num Ops - number of identical Operations initiated concurrently Elap Init Delta(M) Final Num Start Time (s) Cache Size(M) & Mode Size(M) Sta Ops ------------- ----- -------------- -------- --------- -------- --- ----0531 22:05:57 0 D:buffer cache 532 -8 DEF 524 COM 1 0531 22:05:57 0 shared pool 172 +8 DEF 180 COM 1 ------------------------------------------------------------- ⬆ 43
Buffer Pool Advisory -> Only rows with estimated physical reads >0 are displayed -> ordered by Pool, Block Size, Buffers For Estimate Size for Size Buffers P Est (M) Factr (thousands) --- -------- ----- -----------D 52 .1 6 D 104 .2 13 D 156 .3 19 D 208 .4 25 D 260 .5 32 D 312 .6 38 D 364 .7 45 D 416 .8 51 D 468 .9 57 D 520 1.0 64 D 524 1.0 64 D 572 1.1 70 D 624 1.2 76 D 676 1.3 83 D 728 1.4 89 D 780 1.5 96 D 832 1.6 102 D 884 1.7 108 D 936 1.8 115 D 988 1.9 121 D 1,040 2.0 127 Est Phys Estimated Est Read Phys Reads Est Phys % dbtime Factr (thousands) Read Time for Rds ------ -------------- ------------ -------1.0 375 148 48.8 1.0 374 147 48.5 1.0 374 147 48.5 1.0 374 147 48.5 1.0 374 147 48.5 1.0 374 147 48.5 1.0 374 147 48.5 1.0 374 147 48.5 1.0 374 147 48.5 1.0 374 147 48.5 1.0 374 147 48.5 1.0 374 147 48.5 1.0 374 147 48.5 1.0 374 147 48.5 1.0 374 147 48.5 1.0 374 147 48.5 1.0 374 147 48.5 1.0 357 135 44.6 0.9 333 119 39.3 0.8 306 101 33.3 0.8 284 86 28.4 44 ⬆
Buffer Pool Statistics -> Standard block size Pools D: default, K: keep, R: recycle -> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k -> Buffers: the number of buffers. Units of K, M, G are divided by 1000 Free Writ Buffer Pool Buffer Physical Physical Buffer Comp Busy P Buffers Hit% Gets Reads Writes Waits Wait Waits --- ------- ---- -------------- ------------ ----------- ------- ---- ---------D 64K 35 558,026 364,662 281,962 0 0 0 ------------------------------------------------------------- ⬆ 45
PGA Aggr Target Stats -> B: Begin snap E: End snap (rows identified with B or E contain data which is absolute i.e. not diffed over the interval) -> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory -> 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 Cache Hit % W/A MB Processed Extra W/A MB Read/Written --------------- ---------------- ------------------------100.0 49 0 %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 768 647 88.2 0.0 .0 .0 .0 102,400 E 768 646 90.2 0.0 .0 .0 .0 102,400 ------------------------------------------------------------- 46 ⬆
PGA Aggr Target Histogram -> Optimal Executions are purely in-memory operations Low High Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs ------- ------- -------------- ------------- ------------ -----------2K 4K 1,539 1,539 0 0 64K 128K 8 8 0 0 128K 256K 3 3 0 0 256K 512K 2 2 0 0 512K 1024K 27 27 0 0 1M 2M 10 10 0 0 2M 4M 1 1 0 0 4M 8M 2 2 0 0 ------------------------------------------------------------- ⬆ 47
PGA Memory Advisory -> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value where Estd PGA Overalloc Count is 0 PGA Aggr Target Size W/A MB Est (MB) Factr Processed ---------- ------ -------------96 0.1 110 192 0.3 110 384 0.5 110 576 0.8 110 768 1.0 110 922 1.2 110 1,075 1.4 110 1,229 1.6 110 1,382 1.8 110 1,536 2.0 110 2,304 3.0 110 3,072 4.0 110 4,608 6.0 110 Estd Extra Estd W/A MB Estd Time PGA Estd PGA Read/Written to Process Cache Overalloc to Disk Bytes (s) Hit % Count -------------- ---------- ------ ---------0 0.1 100.0 0 0 0.1 100.0 0 0 0.1 100.0 0 0 0.1 100.0 0 0 0.1 100.0 0 0 0.1 100.0 0 0 0.1 100.0 0 0 0.1 100.0 0 0 0.1 100.0 0 0 0.1 100.0 0 0 0.1 100.0 0 0 0.1 100.0 0 0 0.1 100.0 0 48 ⬆
Process Memory Summary Stats -> 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 -> Num Procs or Allocs: For Begin/End snapshot lines, it is the number of processes. For Category lines, it is the number of allocations -> ordered by Begin/End snapshot, Alloc (MB) desc Hist Num Avg Std Dev Max Max Procs Alloc Used Freeabl Alloc Alloc Alloc Alloc or Category (MB) (MB) (MB) (MB) (MB) (MB) (MB) Allocs - -------- --------- --------- -------- -------- ------- ------- ------ -----B -------88.2 49.8 19.9 1.8 2.6 15 15 50 Other 66.5 1.3 1.7 11 11 50 Freeable 20.0 .0 1.5 1.9 7 13 SQL .9 .1 .1 .1 0 4 12 PL/SQL .8 .7 .0 .1 0 0 32 E -------90.3 50.9 19.8 1.8 2.8 15 15 49 Other 68.7 1.4 1.9 11 11 49 Freeable 19.8 .0 1.5 1.8 7 13 SQL .9 .2 .1 .1 0 3 11 PL/SQL .8 .7 .0 .1 0 0 31 49 ⬆
Shared Pool Advisory -> SP: Shared Pool Est LC: Estimated Library Cache Factr: Factor -> Note there is often a 1:Many correlation between a single logical object in the Library Cache, and the physical number of memory objects associated with it. Therefore comparing the number of Lib Cache objects (e.g. in v$librarycache), with the number of Lib Cache Memory Objects is invalid Shared SP Est LC Pool Size Size Est LC Size (M) Factr (M) Mem Obj ---------- ----- -------- -----------180 1.0 15 992 184 1.0 19 1,200 188 1.0 23 1,406 192 1.1 26 1,639 196 1.1 29 1,828 200 1.1 32 2,040 204 1.1 36 2,129 208 1.2 40 2,245 212 1.2 44 2,413 216 1.2 48 2,595 220 1.2 52 2,791 240 1.3 64 3,474 260 1.4 64 3,474 280 1.6 64 3,474 300 1.7 64 3,474 320 1.8 64 3,474 340 1.9 64 3,474 360 2.0 64 3,474 Est LC Est LC Est LC Est LC Time Time Load Load Est LC Saved Saved Time Time Mem (s) Factr (s) Factr Obj Hits ------- ------ ------- ------ ----------187 1.0 5 1.0 40,197 187 1.0 5 1.0 40,313 187 1.0 5 1.0 40,585 187 1.0 5 1.0 40,809 187 1.0 5 1.0 40,881 187 1.0 5 1.0 40,905 187 1.0 5 1.0 40,920 187 1.0 5 1.0 40,939 187 1.0 5 1.0 40,963 187 1.0 5 1.0 40,976 187 1.0 5 1.0 40,985 187 1.0 5 1.0 40,996 187 1.0 5 1.0 40,996 187 1.0 5 1.0 40,996 187 1.0 5 1.0 40,996 187 1.0 5 1.0 40,996 187 1.0 5 1.0 40,996 187 1.0 5 1.0 40,996 50 ⬆
Cache Size Changes -> Not all cache size changes may be captured. evident at snapshot time are shown Only cache changes which are Snap Prior New Difference Id Cache Size (MB) Size (MB) (MB) ------ ------------ --------- --------- ---------93 Buffer Cache 532 524 -8 Shared Pool 172 180 8 —————————————————————————————— SGA Target Advisory SGA Target SGA Size Est DB Est DB Est Physical Size (M) Factor Time (s) Time Factor Reads ---------- -------- -------- ----------- -------------192 .3 169 .6 344,392 384 .5 303 1.0 344,426 576 .8 303 1.0 344,426 768 1.0 303 1.0 344,392 960 1.3 303 1.0 344,392 1,152 1.5 276 .9 303,099 1,344 1.8 258 .9 276,788 1,536 2.0 258 .9 276,788 51 ⬆
SGA breakdown difference -> Top 35 rows by size, ordered by Pool, Name (note rows with null values for Pool column, or Names showing free memory are always shown) -> Null value for Begin MB or End MB indicates the size of that Pool/Name was insignificant, or zero in that snapshot Pool -----shared shared shared shared shared shared shared shared shared shared shared shared shared shared shared shared shared Name Begin MB End MB % Diff ------------------------------ -------------- -------------- -------ASH buffers 8.0 8.0 0.00 KCB Table Scan Buffer 3.8 3.8 0.00 KGLH0 3.6 5.7 56.14 KGLS 4.1 ######## KGLSG 5.0 5.0 0.00 KQR L PO 2.1 ######## KSFD SGA I/O b 3.8 3.8 0.00 KTI-UNDO 3.4 3.4 0.00 Multiblock Index SO 3.7 3.7 0.00 SGA - SWRF Metric CHBs 2.5 2.5 0.00 SQLA 2.1 4.9 133.58 db_block_hash_buckets 2.8 2.8 0.00 event statistics per sess 6.7 6.7 0.00 free memory 18.9 15.9 -15.84 imc buf hdl 2.2 2.2 0.00 row cache 8.2 8.2 0.00 write state object 2.7 2.7 0.00 buffer_cache 532.0 524.0 -1.50 52 ⬆
init.ora Parameters Parameter Name ----------------------------audit_file_dest audit_trail compatible control_files db_block_size db_domain db_name db_recovery_file_dest db_recovery_file_dest_size db_unique_name diagnostic_dest dispatchers End value Begin value (if different) --------------------------------- -------------/u01/admin/nonpdb12c/adump NONE 12.1.0.2.0 /u01/oradata/nonpdb12c/control01. ctl, /u01/fast_recovery_area/nonp db12c/control02.ctl 8192 nonpdb12 /u01/fast_recovery_area 4781506560 nonpdb12c /u01 (PROTOCOL=TCP) (SERVICE=nonpdb12c XDB) filesystemio_options SETALL inmemory_size 0 local_listener LISTENER_NONPDB12C log_buffer 7610368 memory_max_target 0 memory_target 0 nls_language JAPANESE nls_territory JAPAN open_cursors 300 pga_aggregate_target 805306368 processes 300 remote_login_passwordfile EXCLUSIVE resource_limit FALSE resource_manager_plan SCHEDULER[0x4449]:DEFAULT_MAINTEN ANCE_PLAN sga_max_size 805306368 sga_target 805306368 shared_pool_size 0 undo_tablespace UNDOTBS1 ------------------------------------------------------------- ⬆ 53
CPU per Elap per Old Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value ------------ --------------- ---------------- ----------- ---------- ---------6,107 5,129 0.8 0.00 0.00 4274598960 select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, tim estamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj# =:1 and intcol#=:2 54
CPU per Elap per Old Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value ------------ --------------- ---------------- ----------- ---------- ---------6,107 5,129 0.8 0.00 0.00 4274598960 select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, tim estamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj# =:1 and intcol#=:2 54
Event Waits ---------------------------- -----------db file sequential read 200,359 db file scattered read 1,710 free buffer waits 12 log file switch completion 4 control file sequential read 231 kksfbc child completion 1 reliable message 80 Disk file operations I/O 24 library cache load lock 2 latch: shared pool 50 55 %Tim Total Wait out Time (s) ---- ---------0 139 0 9 0 0 0 0 0 0 100 0 0 0 0 0 0 0 0 0 Avg %Total wait Waits Call (ms) /txn Time ------ -------- -----1 ######## 8.2 5 171.0 .5 9 1.2 .0 25 0.4 .0 0 23.1 .0 50 0.1 .0 0 8.0 .0 1 2.4 .0 14 0.2 .0 0 5.0 .0
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment ~~~~~~~~ ---------- ------------------ -------- --------- -----------------Begin Snap: 83 31-5月 -15 22:05:54 32 .9 End Snap: 93 31-5月 -15 22:08:53 Elapsed: DB time: 2.98 (mins) Av Act Sess: 2.95 (mins) DB CPU: 56 31 .9 1.0 0.96 (mins)
PERFSTAT> select snap_id from STATS$SNAPSHOT order by snap_id; SNAP_ID ---------11 21 31 57
PERFSTAT> select snap_id from STATS$SNAPSHOT order by snap_id; SNAP_ID ---------11 21 31 PERFSTAT> select sequence_name,cache_size from user_sequences; SEQUENCE_NAME CACHE_SIZE ------------------------------ ---------STATS$SNAPSHOT_ID 10 57
PERFSTAT> select snap_id from STATS$SNAPSHOT order by snap_id; SNAP_ID ---------11 21 31 PERFSTAT> select sequence_name,cache_size from user_sequences; SEQUENCE_NAME CACHE_SIZE ------------------------------ ---------STATS$SNAPSHOT_ID 10 57
58
Instance Activity Stats Statistic Total per Second per Trans --------------------------------- ------------------ -------------- -----------・・・略・・・ CCursor + sql area evicted ・・・略・・・ 682 3.7 68.2 sql area evicted 831 4.5 83.1 59
Instance Activity Stats Statistic Total per Second per Trans --------------------------------- ------------------ -------------- -----------・・・略・・・ CCursor + sql area evicted ・・・略・・・ 682 3.7 68.2 sql area evicted 831 4.5 83.1 59
Instance Activity Stats Statistic Total per Second per Trans --------------------------------- ------------------ -------------- -----------・・・略・・・ CCursor + sql area evicted ・・・略・・・ 682 3.7 68.2 sql area evicted 831 4.5 83.1 60
Instance Activity Stats Statistic Total per Second per Trans --------------------------------- ------------------ -------------- -----------・・・略・・・ CCursor + sql area evicted ・・・略・・・ 682 3.7 68.2 sql area evicted 831 4.5 83.1 61
Instance Activity Stats Statistic Total per Second per Trans --------------------------------- ------------------ -------------- -----------・・・略・・・ CCursor + sql area evicted ・・・略・・・ 682 3.7 68.2 sql area evicted 831 4.5 83.1 62
Shared Pool Advisory Shared SP Est LC Pool Size Size Est LC Size (M) Factr (M) Mem Obj ---------- ----- -------- -----------204 1.0 18 1,102 208 1.0 21 1,284 212 1.0 25 1,494 216 1.1 28 1,671 220 1.1 31 1,839 224 1.1 34 1,995 228 1.1 37 2,207 232 1.1 40 2,384 236 1.2 44 2,617 240 1.2 45 2,713 252 1.2 55 3,248 276 1.4 76 4,110 300 1.5 99 5,146 324 1.6 108 5,717 348 1.7 108 5,717 Est LC Est LC Est LC Est LC Time Time Load Load Est LC Saved Saved Time Time Mem (s) Factr (s) Factr Obj Hits ------- ------ ------- ------ ----------3,502 1.0 17 1.0 219,030 3,505 1.0 14 .8 219,611 3,506 1.0 13 .8 220,078 3,508 1.0 11 .6 220,696 3,509 1.0 10 .6 221,113 3,510 1.0 9 .5 221,613 3,511 1.0 8 .5 221,944 3,511 1.0 8 .5 222,101 3,511 1.0 8 .5 222,322 3,512 1.0 7 .4 222,489 3,513 1.0 6 .4 222,759 3,514 1.0 5 .3 223,040 3,514 1.0 5 .3 223,133 3,514 1.0 5 .3 223,134 3,514 1.0 5 .3 223,134 63
Cache Sizes Begin End ~~~~~~~~~~~ ---------- ---------Buffer Cache: 460M 452M Shared Pool: 196M 204M 64 Std Block Size: Log Buffer: 8K 7,432K
〜 65
66
SQL ordered by CPU DB/Inst: NONPDB12/nonpdb12c Snaps: 103-104 -> Total DB CPU (s): 59 -> Captured SQL accounts for 191.7% of Total DB CPU -> SQL reported below exceeded 1.0% of Total DB CPU CPU CPU per Elapsd Old Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash Value ---------- ------------ ---------- ------ ---------- --------------- ---------57.11 1 57.11 96.0 183.84 520,249 985249325 Module: SQL*Plus declare cursor cs_foobar is select rowid from foobar where id be tween 1 and 100000 order by 27.03 100,000 0.00 45.4 84.33 Module: SQL*Plus UPDATE FOOBAR SET A = LPAD('y',1795,'y') WHERE ROWID = :B1 153,711 3389821460 25.32 1 25.32 42.6 90.92 366,391 Module: SQL*Plus SELECT ROWID FROM FOOBAR WHERE ID BETWEEN 1 AND 100000 ORDER BY DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) FOR UPDATE 67 741139910
👻 Shared Pool Library Cache Reserved Pool Shared SQL Area Data Dictionary Cache Server Result Cache 68 Other
👻 Shared Pool Library Cache Reserved Pool Shared SQL Area Data Dictionary Cache Server Result Cache 68 Other
👻 69
70
71
72
〜 72
Top 10 Foreground Events by Total Wait Time Enqueue Activity Segments by Row Lock Waits Top SQL with Top Events Foreground Wait Events Wait Event Histogram Wait Event Histogram Detail (64 msec to 2 sec) Wait Event Histogram Detail (4 sec to 2 min) ⏬ 73
Top 10 Foreground Events by Total Wait Time ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Event Waits ------------------------------ ----------DB CPU enq: TX - row lock contention 5,763 resmgr:cpu quantum 420 latch free 177 latch: enqueue hash chains 176 log file switch (checkpoint in 37 log file sync 67 latch: shared pool 27 Disk file operations I/O 45 cursor: pin S 35 ・・・・・ Total Wait Wait % DB Wait Time (sec) Avg(ms) time Class ---------- ---------- ------ -------80.2 48.2 51.6 8.96 31.1 Applicat 1.8 4.38 1.1 Schedule 1.1 6.41 .7 Other .7 3.92 .4 Other .7 17.88 .4 Configur .4 6.65 .3 Commit .2 7.72 .1 Concurre .1 2.78 .1 User I/O .1 2.30 .0 Concurre ⬆ 74
Enqueue Activity -> only enqueues with requests are shown -> Enqueue stats gathered prior to 10g should not be compared with 10g data -> ordered by Wait Time desc, Waits desc, Requests desc Enqueue Type (Request Reason) -----------------------------------------------------------------------------Requests Succ Gets Failed Gets Waits Wt Time (s) Av Wt Time(ms) ------------ ------------ ----------- ----------- ------------ -------------TX-Transaction (row lock contention) 5,758 5,765 0 5,763 53 9.16 HW-Segment High Water Mark 64 64 0 2 0 5.00 TM-DML 76,240 76,240 0 0 0 N/A ⬆ 75
Segments by Row Lock Waits -> % of Capture shows % of row lock waits for each top segment compared -> with total row lock waits for all segments captured by the Snapshot -> When ** MISSING ** occurs, some of the object attributes may not be available Tablespace Owner Name -------------------- ---------Row Subobject Obj. Lock % of Object Name Name Type Obj# Dataobj# Waits Capture -------------------- ---------- ----- ---------- ---------- ------------ ------SCOTT DBTS2014 LOCK_TABLE TABLE 96743 96743 5,748 100.00 ------------------------------------------------------ ⬆ 76
Top SQL with Top Events -> Top SQL statements by DB Time along with the top events by DB Time for those SQLs. -> % Activity is the percentage of DB Time due to the SQL. -> % Event is the percentage of DB Time due to the event that the SQL is waiting on. -> % Row Source is the percentage of DB Time due to the row source for the SQL waiting on the event. -> Executions is the number of executions of the SQL that were sampled in ASH. SQL ID Plan Hash Executions % Activity ----------------------- -------------------- -------------------- -------------% Row Event % Event Top Row Source Source ------------------------------ ------- --------------------------------- ------btxfn6y1htqsd 2069818805 6 61.54 enq: TX - row lock contention 38.46 UPDATE 38.46 UPDATE LOCK_TABLE SET STATUS=1 WHERE ID = :B1 CPU + Wait for CPU ・・・・ 23.08 UPDATE STATEMENT 15.38 ⬆ 77
Foreground Wait Events -> s - second, ms - millisecond 1000th of a second -> Only events with Total Wait Time (s) >= .001 are shown -> ordered by wait time desc, waits desc (idle events last) -> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0 Total %Time Wait Avg Waits % DB Event Waits -outs Time (s) wait (ms) /txn time -------------------------- ----------- ----- -------- ---------- -------- -----enq: TX - row lock content 5,763 52 8.96 0.1 31.1 resmgr:cpu quantum 420 2 4.38 0.0 1.1 latch free 177 1 6.41 0.0 .7 latch: enqueue hash chains 176 1 3.92 0.0 .4 ・・・・・ ⬆ 78
Wait Event Histogram
-> Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
-> % of Waits: value of .0 indicates value was <.05%; value of null is truly 0
-> % of Waits: column heading of <=1s is truly <1024ms, >1s is truly >=1024ms
-> Ordered by Event (idle events last)
% of Waits
----------------------------------------------Total
Event
Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s
>1s
------------------------- ------ ----- ----- ----- ----- ----- ----- ----- ----・・・・・
buffer busy waits
cursor: pin S
db file async I/O submit
db file parallel write
db file sequential read
enq: TX - row lock conten
・・・・・
37
35
17
24
183
5763
64.9
17.6
45.8
76.0
26.4
13.5
71.4
29.4
16.7
14.8
22.6
13.5
14.3
4.2
8.7
23.4
5.4
11.4
8.3
.5
17.4
2.7
2.9
5.9
8.3
47.1
16.7
8.0
2.0
.2
.0
⬆
79
Wait Event Histogram Detail (64 msec to 2 sec)
-> Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
-> Units for % of Total Waits:
ms is milliseconds
s is 1024 milliseconds (approximately 1 second)
-> % of Total Waits: total waits for all wait classes, including Idle
-> % of Total Waits: value of .0 indicates value was <.05%;
value of null is truly 0
-> Ordered by Event (only non-idle events are displayed)
% of Total Waits
----------------------------------------------Waits
64ms
Event
to 2s <32ms <64ms <1/8s <1/4s <1/2s
<1s
<2s >=2s
------------------------- ------ ----- ----- ----- ----- ----- ----- ----- ----db file async I/O submit
1 94.1
5.9
enq: TX - row lock conten
12 99.8
.2
.0
.0
latch free
1 99.5
.5
log file parallel write
4 99.9
.1
log file sync
1 98.5
1.5
oracle thread bootstrap
3 50.0 16.7 16.7 16.7
------------------------------------------------------
⬆
80
Wait Event Histogram Detail (4 sec to 2 min)
-> Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
-> Units for % of Total Waits:
s is 1024 milliseconds (approximately 1 second)
m is 64*1024 milliseconds (approximately 67 seconds or 1.1 minutes)
-> % of Total Waits: total waits for all wait classes, including Idle
-> % of Total Waits: value of .0 indicates value was <.05%;
value of null is truly 0
-> Ordered by Event (only non-idle events are displayed)
% of Total Waits
----------------------------------------------Waits
4s
Event
to 2m
<2s
<4s
<8s <16s <32s < 1m < 2m >=2m
------------------------- ------ ----- ----- ----- ----- ----- ----- ----- ----enq: TX - row lock conten
1 100.0
.0
------------------------------------------------------
⬆
81
Top 10 Foreground Events by Total Wait Time ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Total Wait Wait % DB Wait Event Waits Time (sec) Avg(ms) time Class ------------------------------ ----------- ---------- ---------- ------ -------DB CPU 80.2 48.2 enq: TX - row lock contention 5,763 51.6 8.96 31.1 Applicat resmgr:cpu quantum 420 1.8 4.38 1.1 Schedule latch free 177 1.1 6.41 .7 Other latch: enqueue hash chains 176 .7 3.92 .4 Other log file switch (checkpoint in 37 17.88 Configur Total .7 Wait Wait .4 % DB Wait logEvent file sync 67 .4 6.65 .3 Commit Waits Time (sec) Avg(ms) time Class latch: shared pool 27 .2 7.72 .1 Concurre ---------------------------------------------------------------------Disk 45 .1 2.78 .1 User I/O DB file CPU operations I/O 116.2 52.1 cursor: pin- Srow lock contention 35 .1 2.30 Concurre enq: TX 9,707 35.5 3.65 .0 15.9 Applicat ・・・・・ resmgr:cpu quantum 3,051 16.7 5.46 7.5 Schedule cursor: pin S wait on X db file sequential read cursor: pin S library cache load lock ・・・・・ 92 1,210 148 125 82 7.8 1.3 1.2 1.2 85.04 1.04 8.43 9.86 3.5 .6 .6 .6 Concurre User I/O Concurre Concurre
Top 10 Foreground Events by Total Wait Time ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Total Wait Wait % DB Wait Event Waits Time (sec) Avg(ms) time Class ------------------------------ ----------- ---------- ---------- ------ -------DB CPU 80.2 48.2 enq: TX - row lock contention 5,763 51.6 8.96 31.1 Applicat resmgr:cpu quantum 420 1.8 4.38 1.1 Schedule latch free 177 1.1 6.41 .7 Other latch: enqueue hash chains 176 .7 3.92 .4 Other log file switch (checkpoint in 37 17.88 Configur Total .7 Wait Wait .4 % DB Wait logEvent file sync 67 .4 6.65 .3 Commit Waits Time (sec) Avg(ms) time Class latch: shared pool 27 .2 7.72 .1 Concurre ---------------------------------------------------------------------Disk 45 .1 2.78 .1 User I/O DB file CPU operations I/O 116.2 52.1 cursor: pin- Srow lock contention 35 .1 2.30 Concurre enq: TX 9,707 35.5 3.65 .0 15.9 Applicat ・・・・・ resmgr:cpu quantum 3,051 16.7 5.46 7.5 Schedule cursor: pin S wait on X db file sequential read cursor: pin S library cache load lock ・・・・・ 92 1,210 148 125 82 7.8 1.3 1.2 1.2 85.04 1.04 8.43 9.86 3.5 .6 .6 .6 Concurre User I/O Concurre Concurre
Enqueue Activity -> only enqueues with requests are shown -> Enqueue stats gathered prior to 10g should not be compared with 10g data -> ordered by Wait Time desc, Waits desc, Requests desc Enqueue Type (Request Reason) -----------------------------------------------------------------------------Requests Succ Gets Failed Gets Waits Wt Time (s) Av Wt Time(ms) ------------ ------------ ----------- ----------- ------------ -------------TX-Transaction (row lock contention) 5,758 5,765 0 5,763 53 9.16 HW-Segment High Water Mark 64 64 0 2 0 5.00 TM-DML 76,240 76,240 0 0 0 N/A Enqueue Type (Request Reason) -----------------------------------------------------------------------------Requests Succ Gets Failed Gets Waits Wt Time (s) Av Wt Time(ms) ------------ ------------ ----------- ----------- ------------ -------------TX-Transaction (row lock contention) 9,704 9,714 0 9,706 37 3.82 SQ-Sequence Cache 3 3 0 2 0 5.00 83
Segments by Row Lock Waits -> % of Capture shows % of row lock waits for each top segment compared -> with total row lock waits for all segments captured by the Snapshot -> When ** MISSING ** occurs, some of the object attributes may not be available Tablespace Owner Name -------------------- ---------Row Subobject Obj. Lock % of Object Name Name Type Obj# Dataobj# Waits Capture -------------------- ---------- ----- ---------- ---------- ------------ ------SCOTT DBTS2014 LOCK_TABLE TABLE 96743 96743 5,748 100.00 ------------------------------------------------------ 84
Top SQL with Top Events -> Top SQL statements by DB Time along with the top events by DB Time for those SQLs. -> % Activity is the percentage of DB Time due to the SQL. -> % Event is the percentage of DB Time due to the event that the SQL is waiting on. -> % Row Source is the percentage of DB Time due to the row source for the SQL waiting on the event. -> Executions is the number of executions of the SQL that were sampled in ASH. SQL ID Plan Hash Executions % Activity ----------------------- -------------------- -------------------- -------------% Row Event % Event Top Row Source Source ------------------------------ ------- --------------------------------- ------btxfn6y1htqsd 2069818805 6 61.54 enq: TX - row lock contention 38.46 UPDATE 38.46 UPDATE LOCK_TABLE SET STATUS=1 WHERE ID = :B1 CPU + Wait for CPU ・・・・ 23.08 UPDATE STATEMENT 85 15.38
Foreground Wait Events -> s - second, ms - millisecond 1000th of a second -> Only events with Total Wait Time (s) >= .001 are shown -> ordered by wait time desc, waits desc (idle events last) -> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0 Total %Time Wait Avg Waits % DB Event Waits -outs Time (s) wait (ms) /txn time -------------------------- ----------- ----- -------- ---------- -------- -----enq: TX - row lock content 5,763 52 8.96 0.1 31.1 resmgr:cpu quantum 420 2 4.38 0.0 1.1 latch free 177 1 6.41 0.0 .7 Total latch: enqueue hash chains 176 1 3.92 0.0 .4 ・・・・・ %Time Wait Avg Waits % DB Event Waits -outs Time (s) wait (ms) /txn time -------------------------- ----------- ----- -------- ---------- -------- -----enq: TX - row lock content 9,707 35 3.65 0.1 15.9 resmgr:cpu quantum 3,051 17 5.46 0.0 7.5 cursor: pin S wait on X 92 8 85.04 0.0 3.5 db file sequential read 1,210 1 1.04 0.0 .6 cursor: pin S 148 1 8.43 0.0 .6 86
Foreground Wait Events -> s - second, ms - millisecond 1000th of a second -> Only events with Total Wait Time (s) >= .001 are shown -> ordered by wait time desc, waits desc (idle events last) -> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0 Total %Time Wait Avg Waits % DB Event Waits -outs Time (s) wait (ms) /txn time -------------------------- ----------- ----- -------- ---------- -------- -----enq: TX - row lock content 5,763 52 8.96 0.1 31.1 resmgr:cpu quantum 420 2 4.38 0.0 1.1 latch free 177 1 6.41 0.0 .7 Total latch: enqueue hash chains 176 1 3.92 0.0 .4 ・・・・・ %Time Wait Avg Waits % DB Event Waits -outs Time (s) wait (ms) /txn time -------------------------- ----------- ----- -------- ---------- -------- -----enq: TX - row lock content 9,707 35 3.65 0.1 15.9 resmgr:cpu quantum 3,051 17 5.46 0.0 7.5 cursor: pin S wait on X 92 8 85.04 0.0 3.5 db file sequential read 1,210 1 1.04 0.0 .6 cursor: pin S 148 1 8.43 0.0 .6 86
Wait Event Histogram
-> Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
-> % of Waits: value of .0 indicates value was <.05%; value of null is truly 0
-> % of Waits: column heading of <=1s is truly <1024ms, >1s is truly >=1024ms
-> Ordered by Event (idle events last)
% of Waits
----------------------------------------------Total
Event
Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s
>1s
------------------------- ------ ----- ----- ----- ----- ----- ----- ----- ----・・・・・
db file parallel write
db file sequential read
enq: TX - row lock conten
・・・・・
24
183
5763
45.8
76.0
26.4
16.7
14.8
22.6
4.2
8.7
23.4
8.3
.5
17.4
8.3
16.7
8.0
2.0
.2
.0
Event
Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s
>1s
------------------------- ------ ----- ----- ----- ----- ----- ----- ----- ----・・・・・
db file scattered read
db file sequential read
enq: TX - row lock conten
・・・・・
51
1463
9707
9.8
64.0
25.5
87
31.4
14.3
22.2
27.5
11.3
23.9
15.7
7.2
18.0
15.7
2.9
8.2
.3
2.0
.2
Wait Event Histogram
-> Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
-> % of Waits: value of .0 indicates value was <.05%; value of null is truly 0
-> % of Waits: column heading of <=1s is truly <1024ms, >1s is truly >=1024ms
-> Ordered by Event (idle events last)
% of Waits
----------------------------------------------Total
Event
Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s
>1s
------------------------- ------ ----- ----- ----- ----- ----- ----- ----- ----・・・・・
db file parallel write
db file sequential read
enq: TX - row lock conten
・・・・・
24
183
5763
45.8
76.0
26.4
16.7
14.8
22.6
4.2
8.7
23.4
8.3
.5
17.4
8.3
16.7
8.0
2.0
.2
.0
Event
Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s
>1s
------------------------- ------ ----- ----- ----- ----- ----- ----- ----- ----・・・・・
db file scattered read
db file sequential read
enq: TX - row lock conten
・・・・・
51
1463
9707
9.8
64.0
25.5
87
31.4
14.3
22.2
27.5
11.3
23.9
15.7
7.2
18.0
15.7
2.9
8.2
.3
2.0
.2
Wait Event Histogram Detail (64 msec to 2 sec)
-> Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
-> Units for % of Total Waits:
ms is milliseconds
s is 1024 milliseconds (approximately 1 second)
-> % of Total Waits: total waits for all wait classes, including Idle
-> % of Total Waits: value of .0 indicates value was <.05%;
value of null is truly 0
-> Ordered by Event (only non-idle events are displayed)
% of Total Waits
----------------------------------------------Waits
64ms
Event
to 2s <32ms <64ms <1/8s <1/4s <1/2s
<1s
<2s >=2s
------------------------- ------ ----- ----- ----- ----- ----- ----- ----- ----db file async I/O submit
1 94.1
5.9
enq: TX - row lock conten
12 99.8
.2
.0
.0
latch free
1 99.5
.5
log file parallel write
4 99.9
.1
log file sync
1 98.5
1.5
Eventthread bootstrap
to3 2s50.0
<32ms16.7
<64ms16.7
<1/8s16.7
<1/4s <1/2s
<1s
<2s >=2s
oracle
------------------------------ ----- ----- ----- ----- ----- ----- ----- ---------------------------------------------------------cursor: pin S wait on X
48 47.8 12.0 21.7
3.3 15.2
enq: TX - row lock conten
22 99.8
.2
.0
library cache load lock
2 98.4
1.6
88
Wait Event Histogram Detail (64 msec to 2 sec)
-> Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
-> Units for % of Total Waits:
ms is milliseconds
s is 1024 milliseconds (approximately 1 second)
-> % of Total Waits: total waits for all wait classes, including Idle
-> % of Total Waits: value of .0 indicates value was <.05%;
value of null is truly 0
-> Ordered by Event (only non-idle events are displayed)
% of Total Waits
----------------------------------------------Waits
64ms
Event
to 2s <32ms <64ms <1/8s <1/4s <1/2s
<1s
<2s >=2s
------------------------- ------ ----- ----- ----- ----- ----- ----- ----- ----db file async I/O submit
1 94.1
5.9
enq: TX - row lock conten
12 99.8
.2
.0
.0
latch free
1 99.5
.5
log file parallel write
4 99.9
.1
log file sync
1 98.5
1.5
Eventthread bootstrap
to3 2s50.0
<32ms16.7
<64ms16.7
<1/8s16.7
<1/4s <1/2s
<1s
<2s >=2s
oracle
------------------------------ ----- ----- ----- ----- ----- ----- ----- ---------------------------------------------------------cursor: pin S wait on X
48 47.8 12.0 21.7
3.3 15.2
enq: TX - row lock conten
22 99.8
.2
.0
library cache load lock
2 98.4
1.6
88
Wait Event Histogram Detail (4 sec to 2 min)
-> Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
-> Units for % of Total Waits:
s is 1024 milliseconds (approximately 1 second)
m is 64*1024 milliseconds (approximately 67 seconds or 1.1 minutes)
-> % of Total Waits: total waits for all wait classes, including Idle
-> % of Total Waits: value of .0 indicates value was <.05%;
value of null is truly 0
-> Ordered by Event (only non-idle events are displayed)
% of Total Waits
----------------------------------------------Waits
4s
Event
to 2m
<2s
<4s
<8s <16s <32s < 1m < 2m >=2m
------------------------- ------ ----- ----- ----- ----- ----- ----- ----- ----enq: TX - row lock conten
1 100.0
.0
------------------------------------------------------
Wait Event Histogram Detail (4 sec to 2 min)
No data exists for this section of the report.
------------------------------------------------------
89
Wait Event Histogram Detail (4 sec to 2 min)
-> Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
-> Units for % of Total Waits:
s is 1024 milliseconds (approximately 1 second)
m is 64*1024 milliseconds (approximately 67 seconds or 1.1 minutes)
-> % of Total Waits: total waits for all wait classes, including Idle
-> % of Total Waits: value of .0 indicates value was <.05%;
value of null is truly 0
-> Ordered by Event (only non-idle events are displayed)
% of Total Waits
----------------------------------------------Waits
4s
Event
to 2m
<2s
<4s
<8s <16s <32s < 1m < 2m >=2m
------------------------- ------ ----- ----- ----- ----- ----- ----- ----- ----enq: TX - row lock conten
1 100.0
.0
------------------------------------------------------
Wait Event Histogram Detail (4 sec to 2 min)
No data exists for this section of the report.
------------------------------------------------------
89
<1ms <2ms <4ms <8ms <16ms <32ms <64ms <125ms <32000ms <1ms <2ms <4ms <8ms <16ms <32ms <64ms <125ms 90
<1ms <2ms <4ms <8ms <16ms <32ms <64ms <125ms <32000ms 91 <1ms <2ms <4ms <8ms <16ms <32ms <64ms <125ms
<1ms <2ms <4ms <8ms <16ms <32ms <64ms <125ms <32000ms <32000ms 91 <1ms <2ms <4ms <8ms <16ms <32ms <64ms <125ms
<1ms <2ms <4ms <8ms <16ms <32ms <64ms <125ms <32000ms <32000ms 91 <1ms <2ms <4ms <8ms <16ms <32ms <64ms <125ms
92
http://www.jpoug.org 93
http://www.jpoug.org ✌ 93