～

1

～

2

3

👻

4

5

COLUMN_NAME DATA_TYPE-------------------- ------------ID NUMBERDATA1 VARCHAR2DATA2 VARCHAR2

INDEX_NAME COLUMN_NAME DESC-------------------- ------------ ----PK_A ID ASC

COLUMN_NAME DATA_TYPE-------------------- ----------------ID NUMBERBR# NUMBERDATA1 VARCHAR2DATA2 VARCHAR2

INDEX_NAME COLUMN_NAME DESC-------------------- --------------- ----PK_B ID ASCPK_B BR# ASC

TABLE_NAME NUM_ROWS-----------------　----------A 100B 300

⬇

～

6

SELECT/*+

LEADING(a b)USE_NL(a b)

*/a.id,b.br#,SUBSTR(a.data1, 10) AS a_data1,SUBSTR(b.data1, 10) AS b_data1

FROMa INNER JOIN bON

a.id = b.idWHERE

a.id BETWEEN 1 AND 5ORDER BY

a.id,b.br#;

SELECT/*+

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

FROMa INNER JOIN bON

a.id = b.idWHERE

a.id BETWEEN 1 AND 5ORDER BY

a.id,b.br#;

⬆

？⏬

SELECTa.id,b.br#,SUBSTR(a.data1, 10) AS a_data1,SUBSTR(b.data1, 10) AS b_data1

FROMa INNER JOIN bON

a.id = b.idWHERE

a.id BETWEEN 1 AND 5ORDER 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 | NESTED LOOPS | |・| 1 | +0 | 1 | 15 | |・|| 4 | TABLE ACCESS BY INDEX ROWID BATCHED | A |・| 1 | +0 | 1 | 5 | |・|| 5 | INDEX RANGE SCAN | PK_A |・| 1 | +0 | 1 | 5 | |・|| 6 | INDEX RANGE SCAN | PK_B |・| 1 | +0 | 5 | 15 | |・|| 7 | TABLE ACCESS BY INDEX ROWID | B |・| 1 | +0 | 15 | 15 | |・|=============================================================================================================

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 | NESTED LOOPS | |・| 1 | +0 | 1 | 15 | |・|| 4 | TABLE ACCESS BY INDEX ROWID BATCHED | A |・| 1 | +0 | 1 | 5 | |・|| 5 | INDEX RANGE SCAN | PK_A |・| 1 | +0 | 1 | 5 | |・|| 6 | INDEX RANGE SCAN | PK_B |・| 1 | +0 | 5 | 15 | |・|| 7 | TABLE ACCESS BY INDEX ROWID | B |・| 1 | +0 | 15 | 15 | |・|=============================================================================================================

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 | NESTED LOOPS | |・| 1 | +0 | 1 | 15 | |・|| 4 | TABLE ACCESS BY INDEX ROWID BATCHED | A |・| 1 | +0 | 1 | 5 | |・|| 5 | INDEX RANGE SCAN | PK_A |・| 1 | +0 | 1 | 5 | |・|| 6 | INDEX RANGE SCAN | PK_B |・| 1 | +0 | 5 | 15 | |・|| 7 | TABLE ACCESS BY INDEX ROWID | B |・| 1 | +0 | 15 | 15 | |・|=============================================================================================================

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 | NESTED LOOPS | |・| 1 | +0 | 1 | 15 | |・|| 4 | TABLE ACCESS BY INDEX ROWID BATCHED | A |・| 1 | +0 | 1 | 5 | |・|| 5 | INDEX RANGE SCAN | PK_A |・| 1 | +0 | 1 | 5 | |・|| 6 | INDEX RANGE SCAN | PK_B |・| 1 | +0 | 5 | 15 | |・|| 7 | TABLE ACCESS BY INDEX ROWID | B |・| 1 | +0 | 15 | 15 | |・|=============================================================================================================

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 | NESTED LOOPS | |・| 1 | +0 | 1 | 15 |・|| 2 | NESTED LOOPS | |・| 1 | +0 | 1 | 15 |・|| 3 | TABLE ACCESS BY INDEX ROWID | B |・| 1 | +0 | 1 | 15 |・|| 4 | INDEX RANGE SCAN | PK_B |・| 1 | +0 | 1 | 15 |・|| 5 | INDEX UNIQUE SCAN | PK_A |・| 1 | +0 | 15 | 15 |・|| 6 | TABLE ACCESS BY INDEX ROWID | A |・| 1 | +0 | 15 | 15 |・|============================================================================================

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 | NESTED LOOPS | |・| 1 | +0 | 1 | 15 |・|| 2 | NESTED LOOPS | |・| 1 | +0 | 1 | 15 |・|| 3 | TABLE ACCESS BY INDEX ROWID | B |・| 1 | +0 | 1 | 15 |・|| 4 | INDEX RANGE SCAN | PK_B |・| 1 | +0 | 1 | 15 |・|| 5 | INDEX UNIQUE SCAN | PK_A |・| 1 | +0 | 15 | 15 |・|| 6 | TABLE ACCESS BY INDEX ROWID | A |・| 1 | +0 | 15 | 15 |・|============================================================================================

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 | NESTED LOOPS | |・| 1 | +0 | 1 | 15 |・|| 2 | NESTED LOOPS | |・| 1 | +0 | 1 | 15 |・|| 3 | TABLE ACCESS BY INDEX ROWID | B |・| 1 | +0 | 1 | 15 |・|| 4 | INDEX RANGE SCAN | PK_B |・| 1 | +0 | 1 | 15 |・|| 5 | INDEX UNIQUE SCAN | PK_A |・| 1 | +0 | 15 | 15 |・|| 6 | TABLE ACCESS BY INDEX ROWID | A |・| 1 | +0 | 15 | 15 |・|============================================================================================

10 ⬆

11 ⬆

12

Plan Statistics

Stat Name Statement Per Execution % Snap---------------------------------------- ---------- -------------- -------Elapsed Time (ms) 12,645 0.1 76.9CPU Time (ms) 12,540 0.1 79.1Executions 100,000 N/A N/ABuffer Gets 2,300,000 23.0 99.6Disk Reads 0 0.0 0.0Parse Calls 1 0.0 0.3Rows 1,500,000 15.0 N/AUser I/O Wait Time (ms) 0 N/A N/ACluster Wait Time (ms) 0 N/A N/AApplication Wait Time (ms) 0 N/A N/AConcurrency Wait Time (ms) 0 N/A N/AInvalidations 0 N/A N/AVersion Count 1 N/A N/ASharable Mem(KB) 27 N/A N/A -------------------------------------------------------------

⬆

13

Plan Statistics

Stat Name Statement Per Execution % Snap---------------------------------------- ---------- -------------- -------Elapsed Time (ms) 6,972 0.1 68.4CPU Time (ms) 7,231 0.1 72.9Executions 100,000 N/A N/ABuffer Gets 3,500,000 35.0 99.8Disk Reads 0 0.0 0.0Parse Calls 1 0.0 0.3Rows 1,500,000 15.0 N/AUser I/O Wait Time (ms) 0 N/A N/ACluster Wait Time (ms) 0 N/A N/AApplication Wait Time (ms) 0 N/A N/AConcurrency Wait Time (ms) 0 N/A N/AInvalidations 0 N/A N/AVersion Count 1 N/A N/ASharable Mem(KB) 27 N/A N/A -------------------------------------------------------------

⬆

13

Plan Statistics

Stat Name Statement Per Execution % Snap---------------------------------------- ---------- -------------- -------Elapsed Time (ms) 6,972 0.1 68.4CPU Time (ms) 7,231 0.1 72.9Executions 100,000 N/A N/ABuffer Gets 3,500,000 35.0 99.8Disk Reads 0 0.0 0.0Parse Calls 1 0.0 0.3Rows 1,500,000 15.0 N/AUser I/O Wait Time (ms) 0 N/A N/ACluster Wait Time (ms) 0 N/A N/AApplication Wait Time (ms) 0 N/A N/AConcurrency Wait Time (ms) 0 N/A N/AInvalidations 0 N/A N/AVersion Count 1 N/A N/ASharable Mem(KB) 27 N/A N/A -------------------------------------------------------------

⬆

👻

14

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

接続されました。 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

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 4274598960select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, 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 343569411select /*+ rule */ bucket, endpoint, col#, epvalue, epvalue_raw, ep_repeat_count from histgrm$ where obj#=:1 and intcol#=:2 androw#=:3 order by bucket

1,858 2,747 1.5 0.00 0.00 2482976222select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where con#=:1

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 4274598960select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, 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 343569411select /*+ rule */ bucket, endpoint, col#, epvalue, epvalue_raw, ep_repeat_count from histgrm$ where obj#=:1 and intcol#=:2 androw#=:3 order by bucket

1,858 2,747 1.5 0.00 0.00 2482976222select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where con#=:1

CPU per Elap per Old Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value------------ --------------- ---------------- ----------- ---------- ---------- 100,000 100,000 1.0 0.00 0.00 3389821460Module: SQL*PlusUPDATE FOOBAR SET A = LPAD('y',1795,'y') WHERE ROWID = :B1

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 4274598960select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, 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 343569411select /*+ rule */ bucket, endpoint, col#, epvalue, epvalue_raw, ep_repeat_count from histgrm$ where obj#=:1 and intcol#=:2 androw#=:3 order by bucket

1,858 2,747 1.5 0.00 0.00 2482976222select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where con#=:1

～

17

👻

18

19

⏩

STATSPACK report forLoad ProfileInstance Efficiency Indicators

Top 5 Timed EventInstance CPU

Memory StatisticsStatistic

Foreground Wait Events

Background Wait EventsWait Events (fg and bg)

Wait Event Histogram

SQL ordered by CPUSQL ordered by Gets SQL ordered by ExecutionsSQL ordered by Parse Calls

Instance Activity Stats OS StatisticsIO Stat by Function - summary/detailTablespace IO StatsFile IO StatsFile Read Histogram StatsIO Stats Function DetailInstance Recovery Stats

Memory Dynamic ComponentsMemory Resize OperationsBuffer Pool AdvisoryBuffer Pool StatisticsPGA Aggr Target StatsPGA Aggr Target HistogramPGA Memory AdvisoryProcess Memory Summary StatsShared Pool Advisory Cache Size Changes SGA Target AdvisorySGA breakdown difference

init.ora Parameters

20

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 31 .9

Elapsed: 2.98 (mins) Av Act Sess: 1.0 DB time: 2.95 (mins) DB CPU: 0.96 (mins) ⬆

21

Cache Sizes Begin End~~~~~~~~~~~ ---------- ---------- Buffer Cache: 532M 524M Std Block Size: 8K Shared Pool: 172M 180M Log Buffer: 7,432K

Load Profile Per Second Per Transaction Per Exec Per Call~~~~~~~~~~~~ ------------------ ----------------- ----------- ----------- DB time(s): 1.0 22.1 0.00 3.28 DB CPU(s): 0.3 7.2 0.00 1.06 Redo size: 2,380,617.8 53,266,323.0 Logical reads: 3,113.7 69,668.9 Block changes: 2,952.4 66,059.1 Physical reads: 2,037.4 45,586.9 Physical writes: 1,575.3 35,247.9 User calls: 0.3 6.8 Parses: 9.3 208.8 Hard parses: 2.3 50.9W/A MB processed: 0.3 6.2 Logons: 0.1 1.1 Executes: 611.5 13,682.0 Rollbacks: 0.0 0.0 Transactions: 0.0

⬆

22

Instance Efficiency Indicators~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 100.00 Redo NoWait %: 100.00 Buffer Hit %: 34.57 Optimal W/A Exec %: 100.00 Library Hit %: 97.70 Soft Parse %: 75.63 Execute to Parse %: 98.47 Latch Hit %: 99.99Parse CPU to Parse Elapsd %: 98.61 % Non-Parse CPU: 97.60

Shared Pool Statistics Begin End ------ ------ Memory Usage %: 89.03 91.18 % SQL with executions>1: 77.86 79.00 % Memory for SQL w/exec>1: 75.17 78.51

⬆

23

Top 5 Timed Events Avg %Total~~~~~~~~~~~~~~~~~~ wait CallEvent Waits Time (s) (ms) Time----------------------------------------- ------------ ----------- ------ ------LGWR worker group idle 432 478 1105 35.1lreg timer 63 180 2860 13.2AQPC idle 6 180 30005 13.2heartbeat redo informer 179 179 1000 13.2db 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)

⬆

24

Instance CPU~~~~~~~~~~~~ % Time (seconds) -------- -------------- Host: Total time (s): 675.4 Host: Busy CPU time (s): 64.6 % of time Host is Busy: 9.6 Instance: Total CPU time (s): 64.0 % of Busy CPU used for Instance: 99.1 Instance: Total Database time (s): 204.2 %DB time waiting for CPU (Resource Mgr): 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 ------------------------------------------------------------- ⬆

25

Statistic Time (s) % DB time----------------------------------- -------------------- ---------sql execute elapsed time 177.0 100.0DB CPU 57.4 32.4PL/SQL execution elapsed time 3.5 2.0parse time elapsed 1.9 1.1hard parse elapsed time 1.9 1.1connection management call elapsed 0.1 .1repeated bind elapsed time 0.0 .0PL/SQL compilation elapsed time 0.0 .0sequence load elapsed time 0.0 .0DB time 177.1background elapsed time 27.1background cpu time 6.6 -------------------------------------------------------------

⬆

26

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 CallEvent Waits out Time (s) (ms) /txn Time---------------------------- ------------ ---- ---------- ------ -------- ------db file sequential read 200,794 0 135 1 ######## 9.9db file scattered read 1,516 0 8 5 189.5 .6log file switch completion 4 0 0 24 0.5 .0control file sequential read 266 0 0 0 33.3 .0log file switch (private str 2 0 0 23 0.3 .0log file sync 3 0 0 2 0.4 .0reliable message 10 0 0 0 1.3 .0Disk file operations I/O 8 0 0 0 1.0 .0SQL*Net message from client 26 0 0 1 3.3 ------------------------------------------------------------- ⬆

27

Background 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 CallEvent Waits out Time (s) (ms) /txn Time---------------------------- ------------ ---- ---------- ------ -------- ------LGWR worker group idle 432 0 478 1105 54.0 35.1lreg timer 63 95 180 2860 7.9 13.2AQPC idle 6 100 180 30005 0.8 13.2heartbeat redo informer 179 0 179 1000 22.4 13.2Streams AQ: load balancer id 1 100 120 ###### 0.1 8.8db file parallel write 2,179 0 16 7 272.4 1.2log file parallel write 430 0 4 9 53.8 .3db file async I/O submit 1,119 0 1 1 139.9 .1control file parallel write 151 0 0 3 18.9 .0control file sequential read 453 0 0 1 56.6 .0oracle thread bootstrap 7 0 0 18 0.9 .0Parameter File I/O 16 0 0 3 2.0 .0db file sequential read 40 0 0 1 5.0 .0target log write size 2 100 0 10 0.3 .0direct path write 6 0 0 2 0.8 .0log file single write 10 0 0 1 1.3 .0os thread creation 7 0 0 1 0.9 .0direct path read 5 0 0 1 0.6 .0Disk file operations I/O 27 0 0 0 3.4 .0log file sequential read 10 0 0 0 1.3 .0LGWR all worker groups 5 0 0 0 0.6 .0 ⬆

28

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)

Avg %Total %Tim Total Wait wait Waits CallEvent Waits out Time (s) (ms) /txn Time---------------------------- ------------ ---- ---------- ------ -------- ------LGWR worker group idle 432 0 478 1105 54.0 35.1lreg timer 63 95 180 2860 7.9 13.2AQPC idle 6 100 180 30005 0.8 13.2heartbeat redo informer 179 0 179 1000 22.4 13.2db file sequential read 200,834 0 135 1 ######## 9.9Streams AQ: load balancer id 1 100 120 ###### 0.1 8.8db file parallel write 2,179 0 16 7 272.4 1.2db file scattered read 1,516 0 8 5 189.5 .6log file parallel write 430 0 4 9 53.8 .3db file async I/O submit 1,119 0 1 1 139.9 .1control file parallel write 151 0 0 3 18.9 .0control file sequential read 719 0 0 0 89.9 .0oracle thread bootstrap 7 0 0 18 0.9 .0log file switch completion 4 0 0 24 0.5 .0log file switch (private str 2 0 0 23 0.3 .0Parameter File I/O 16 0 0 3 2.0 .0target log write size 2 100 0 10 0.3 .0direct path write 6 0 0 2 0.8 .0log file single write 10 0 0 1 1.3 .0os thread creation 7 0 0 1 0.9 .0direct path read 5 0 0 1 0.6 .0Disk file operations I/O 35 0 0 0 4.4 .0log file sync 3 0 0 2 0.4 .0log file sequential read 10 0 0 0 1.3 .0reliable message 10 0 0 0 1.3 .0LGWR all worker groups 5 0 0 0 0.6 .0

⬆

29

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)

Total ----------------- % of Waits ------------------Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----AQPC idle 6 100.0Disk file operations I/O 35 97.1 2.9LGWR all worker groups 5 100.0LGWR worker group idle 432 .5 90.7 8.8Parameter File I/O 16 62.5 12.5 6.3 18.8Streams AQ: load balancer 1 100.0asynch descriptor resize 19 100.0control file parallel writ 151 46.4 46.4 2.0 5.3control file sequential re 719 96.1 2.1 .8 .1 .7 .1db file async I/O submit 1119 78.9 16.5 3.1 .7 .5 .1 .1db file parallel write 2179 42.5 2.5 3.3 3.6 37.4 10.7db file scattered read 1516 34.4 14.7 1.6 28.4 11.9 9.0db file sequential read 200K 94.4 4.7 .4 .2 .3 .0direct path read 5 60.0 40.0direct path write 6 50.0 50.0heartbeat redo informer 179 100.0latch free 1 100.0latch: cache buffers lru c 1 100.0latch: shared pool 5 100.0log file parallel write 430 1.4 .7 41.6 53.7 2.6log file sequential read 10 100.0log file single write 10 70.0 30.0log file switch (private s 2 100.0log file switch completion 4 100.0log file sync 3 33.3 66.7lreg timer 63 4.8 95.2oracle thread bootstrap 7 14.3 85.7SQL*Net message to client 32 100.0

⬆

30

SQL ordered by CPU -> Total DB CPU (s): 57-> Captured SQL accounts for 3.1% 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---------- ------------ ---------- ------ ---------- --------------- ---------- 1.02 1 1.02 1.8 1.10 13,200 4269626113Module: SQL*PlusBEGIN :snap# := statspack.snap(i_snap_level=>7); END;

⬆

31

SQL ordered by Gets -> End Buffer Gets Threshold: 10000 Total Buffer Gets: 557,351-> Captured SQL accounts for 5.8% of Total Buffer Gets-> SQL reported below exceeded 1.0% of Total Buffer Gets

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 4269626113Module: SQL*PlusBEGIN :snap# := statspack.snap(i_snap_level=>7); END;

7,130 2,399 3.0 1.3 0.12 0.16 4274598960select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, 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 Executions -> End Executions Threshold: 100 Total Executions: 109,456-> Captured SQL accounts for 7.7% 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------------ --------------- ---------------- ----------- ---------- ---------- 2,399 2,327 1.0 0.00 0.00 4274598960select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival,density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2

⬆

33

SQL ordered by Parse Calls -> End Parse Calls Threshold: 1000 Total Parse Calls: 1,670-> Captured SQL accounts for 69.0% of Total Parse Calls-> SQL reported below exceeded 1.0% of Total Parse Calls

% Total Old Parse Calls Executions Parses Hash Value------------ ------------ -------- ---------- 210 210 12.57 2018736380select timestamp, flags from fixed_obj$ where obj#=:1

139 139 8.32 1198893840select order#,columns,types from access$ where d_obj#=:1

139 139 8.32 2824870641select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(property,0),subname,type#,flags,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order#

117 117 7.01 4118814203select decode(u.type#, 2, u.ext_username, u.name), o.name, t.update$, t.insert$, t.delete$, t.enabled, decode(bitand(t.property, 8192),8192, 1, 0), decode(bitand(t.property, 65536), 65536, 1, 0), decode(bitand(t.property, 131072)

112 112 6.71 1512023764select obj#, name, stab#, sobj#, sobjd#, ttab#, tobj#, tobjd#, mflags from rmtab$ where (stab# = :1 or ttab# = :1) order by obj# ⬆

34

Instance Activity Stats

Statistic Total per Second per Trans--------------------------------- ------------------ -------------- ------------rollback changes - undo records a 0 0.0 0.0rollbacks only - consistent read 0 0.0 0.0rows fetched via callback 678 3.8 84.8session connect time 180 1.0 22.5session cursor cache hits 43,551 243.3 5,443.9session logical reads 557,351 3,113.7 69,668.9session pga memory 2,362,616 13,199.0 295,327.0session pga memory max 1,576,184 8,805.5 197,023.0session uga memory 400,216 2,235.8 50,027.0session uga memory max 21,289,200 118,934.1 2,661,150.0shared hash latch upgrades - no w 160 0.9 20.0shared io pool buffer get success 0 0.0 0.0sorts (memory) 2,984 16.7 373.0sorts (rows) 127,277 711.0 15,909.6sql area evicted 218 1.2 27.3sql area purged 0 0.0 0.0summed dirty queue length 415,865 2,323.3 51,983.1switch current to new buffer 3 0.0 0.4

⬆

35

OS Statistics -> ordered by statistic type (CPU use, Virtual Memory, Hardware Config), NameStatistic Total------------------------- ----------------------SWAP_FREE_BYTES 2,113,925,120BUSY_TIME 6,460IDLE_TIME 61,078IOWAIT_TIME 9,632SYS_TIME 1,830USER_TIME 4,511PHYSICAL_MEMORY_BYTES 8,431,374,336NUM_CPUS 4NUM_CPU_CORES 4NUM_CPU_SOCKETS 1GLOBAL_RECEIVE_SIZE_MAX 4,194,304GLOBAL_SEND_SIZE_MAX 1,048,576TCP_RECEIVE_SIZE_DEFAULT 87,380TCP_RECEIVE_SIZE_MAX 6,291,456TCP_RECEIVE_SIZE_MIN 4,096TCP_SEND_SIZE_DEFAULT 16,384TCP_SEND_SIZE_MAX 4,194,304TCP_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 ⬆

36

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 AvgFunction Volume /sec Vol/sec Volume /sec Vol/sec Count Tm(ms)--------------- ------ -------- -------- ------ -------- -------- ------ ------Buffer Cache Re 2849M 1130.3 15.9M 202K 0.0LGWR 1M .3 .0M 423M 4.7 2.4M 552 0.0Others 10M 3.8 .1M 3M 1.2 .0M 789 0.0Direct Reads 1M .1 .0M .0 0.0Direct 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 DataFunction Reqs Reqs Read Read Reqs Reqs Writn Writn------------------ ------ ------ ------ ------ ------ ------ ------ ------Buffer Cache Reads 201K 1436 1575M 1274MLGWR 62 1M 332 513 5M 418MOthers 677 10M 214 3MDirect Reads 19 1M 2Direct Writes 2 19 ------------------------------------------------------------- ⬆

37

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.0UNDOTBS1 9 0 1.1 1.0 1,107 6 0 0.0SYSAUX 340 2 0.3 1.0 196 1 0 0.0SYSTEM 478 3 0.4 1.0 28 0 0 0.0EXAMPLE 3 0 0.0 1.0 3 0 0 0.0 ——————————————————————————————

⬆

38

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 /u01/oradata/nonpdb12c/sysaux01.dbf 340 2 0.3 1 1.0 196 1 0

SYSTEM /u01/oradata/nonpdb12c/system01.dbf 478 3 0.4 1 1.0 28 0 0

UNDOTBS1 /u01/oradata/nonpdb12c/undotbs01.dbf 9 0 1.1 1 1.0 1,107 6 0

USERS /u01/oradata/nonpdb12c/users01.dbf 201,520 1,126 0.7 1 1.8 18,970 106 0 -------------------------------------------------------------

⬆

39

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 /u01/oradata/nonpdb12c/system01.dbf 459 0 0 0 0 0

UNDOTBS1 /u01/oradata/nonpdb12c/undotbs01.dbf 6 0 0 0 0 0

USERS /u01/oradata/nonpdb12c/users01.dbf 188,707 0 0 0 0 0

⬆

40

IO Stats Function Detail Small Small Large LargeFunction FileType Read Write Read Write Wait TimeName Name (MB) (MB) (MB) (MB) Waits (ms)------------ ------------ ------- ------- ------- ------- --------- -----------DBWR Control File 0 0 0 0 0 0DBWR Data File 0 84 0 2117 0 0LGWR Control File 1 2 0 0 52 26LGWR Data File 0 0 0 0 0 0LGWR Log File 0 3 0 418 20 15LGWR Other(8) 0 0 0 0 480 3903Streams AQ Data File 0 0 0 0 0 0Buffer Cache Data File 1575 0 1274 0 202331 147785Buffer Cache Other(8) 0 0 0 0 0 0Direct Reads Data File 1 0 0 0 0 0Others(14) Control File 10 3 0 0 671 272Others(14) Data File 0 0 0 0 0 0Others(14) Other(8) 0 0 0 0 102 296Others(14) Parameter Fi 0 0 0 0 16 42Others(14) External Tab 0 0 0 0 0 0 0 0 0 0 0 0 ⬆

41

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 331776E 0 45 44244 287011 331776 331776 -------------------------------------------------------------

⬆

42

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 OpCache Size (M) Size (M) Count Type/Mode Last Op Time---------------------- ---------- -------- ------- ---------- ---------------D:buffer cache 532 524 1 SHRINK/DEF 31-5月 22:05:5 7PGA Target 768 0 STATICSGA Target 768 0 STATICShared IO Pool 28 0 GROW/IMM 31-5月 22:03:5 5java pool 4 0 STATIClarge pool 20 0 SHRINK/DEF 31-5月 22:04:2 7shared pool 172 180 1 GROW/DEF 31-5月 22:05:5 7 ⬆

43

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 NumStart Time (s) Cache Size(M) & Mode Size(M) Sta Ops------------- ----- -------------- -------- --------- -------- --- -----0531 22:05:57 0 D:buffer cache 532 -8 DEF 524 COM 10531 22:05:57 0 shared pool 172 +8 DEF 180 COM 1 -------------------------------------------------------------

⬆

44

Buffer Pool Advisory -> Only rows with estimated physical reads >0 are displayed-> ordered by Pool, Block Size, Buffers For Estimate

Est Phys Estimated Est Size for Size Buffers Read Phys Reads Est Phys % dbtimeP Est (M) Factr (thousands) Factr (thousands) Read Time for Rds--- -------- ----- ------------ ------ -------------- ------------ --------D 52 .1 6 1.0 375 148 48.8D 104 .2 13 1.0 374 147 48.5D 156 .3 19 1.0 374 147 48.5D 208 .4 25 1.0 374 147 48.5D 260 .5 32 1.0 374 147 48.5D 312 .6 38 1.0 374 147 48.5D 364 .7 45 1.0 374 147 48.5D 416 .8 51 1.0 374 147 48.5D 468 .9 57 1.0 374 147 48.5D 520 1.0 64 1.0 374 147 48.5D 524 1.0 64 1.0 374 147 48.5D 572 1.1 70 1.0 374 147 48.5D 624 1.2 76 1.0 374 147 48.5D 676 1.3 83 1.0 374 147 48.5D 728 1.4 89 1.0 374 147 48.5D 780 1.5 96 1.0 374 147 48.5D 832 1.6 102 1.0 374 147 48.5D 884 1.7 108 1.0 357 135 44.6D 936 1.8 115 0.9 333 119 39.3D 988 1.9 121 0.8 306 101 33.3D 1,040 2.0 127 0.8 284 86 28.4 ⬆

45

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 BusyP Buffers Hit% Gets Reads Writes Waits Wait Waits--- ------- ---- -------------- ------------ ----------- ------- ---- ----------D 64K 35 558,026 364,662 281,962 0 0 0 -------------------------------------------------------------

⬆

46

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,400E 768 646 90.2 0.0 .0 .0 .0 102,400 ------------------------------------------------------------- ⬆

47

PGA Aggr Target Histogram

-> Optimal Executions are purely in-memory operations

Low HighOptimal 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 -------------------------------------------------------------

⬆

48

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

Estd Extra Estd PGA Aggr W/A MB Estd Time PGA Estd PGA Target Size W/A MB Read/Written to Process Cache Overalloc Est (MB) Factr Processed to Disk Bytes (s) Hit % Count---------- ------ -------------- -------------- ---------- ------ ---------- 96 0.1 110 0 0.1 100.0 0 192 0.3 110 0 0.1 100.0 0 384 0.5 110 0 0.1 100.0 0 576 0.8 110 0 0.1 100.0 0 768 1.0 110 0 0.1 100.0 0 922 1.2 110 0 0.1 100.0 0 1,075 1.4 110 0 0.1 100.0 0 1,229 1.6 110 0 0.1 100.0 0 1,382 1.8 110 0 0.1 100.0 0 1,536 2.0 110 0 0.1 100.0 0 2,304 3.0 110 0 0.1 100.0 0 3,072 4.0 110 0 0.1 100.0 0 4,608 6.0 110 0 0.1 100.0 0

⬆

49

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 32E -------- 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 ⬆

50

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

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---------- ----- -------- ------------ ------- ------ ------- ------ ----------- 180 1.0 15 992 187 1.0 5 1.0 40,197 184 1.0 19 1,200 187 1.0 5 1.0 40,313 188 1.0 23 1,406 187 1.0 5 1.0 40,585 192 1.1 26 1,639 187 1.0 5 1.0 40,809 196 1.1 29 1,828 187 1.0 5 1.0 40,881 200 1.1 32 2,040 187 1.0 5 1.0 40,905 204 1.1 36 2,129 187 1.0 5 1.0 40,920 208 1.2 40 2,245 187 1.0 5 1.0 40,939 212 1.2 44 2,413 187 1.0 5 1.0 40,963 216 1.2 48 2,595 187 1.0 5 1.0 40,976 220 1.2 52 2,791 187 1.0 5 1.0 40,985 240 1.3 64 3,474 187 1.0 5 1.0 40,996 260 1.4 64 3,474 187 1.0 5 1.0 40,996 280 1.6 64 3,474 187 1.0 5 1.0 40,996 300 1.7 64 3,474 187 1.0 5 1.0 40,996 320 1.8 64 3,474 187 1.0 5 1.0 40,996 340 1.9 64 3,474 187 1.0 5 1.0 40,996 360 2.0 64 3,474 187 1.0 5 1.0 40,996 ⬆

51

Cache Size Changes

-> Not all cache size changes may be captured. Only cache changes which are evident at snapshot time are shown

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 ⬆

52

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 Name Begin MB End MB % Diff------ ------------------------------ -------------- -------------- --------shared ASH buffers 8.0 8.0 0.00shared KCB Table Scan Buffer 3.8 3.8 0.00shared KGLH0 3.6 5.7 56.14shared KGLS 4.1 ########shared KGLSG 5.0 5.0 0.00shared KQR L PO 2.1 ########shared KSFD SGA I/O b 3.8 3.8 0.00shared KTI-UNDO 3.4 3.4 0.00shared Multiblock Index SO 3.7 3.7 0.00shared SGA - SWRF Metric CHBs 2.5 2.5 0.00shared SQLA 2.1 4.9 133.58shared db_block_hash_buckets 2.8 2.8 0.00shared event statistics per sess 6.7 6.7 0.00shared free memory 18.9 15.9 -15.84shared imc buf hdl 2.2 2.2 0.00shared row cache 8.2 8.2 0.00shared write state object 2.7 2.7 0.00 buffer_cache 532.0 524.0 -1.50 ⬆

53

init.ora Parameters End valueParameter Name Begin value (if different)----------------------------- --------------------------------- --------------audit_file_dest /u01/admin/nonpdb12c/adumpaudit_trail NONEcompatible 12.1.0.2.0control_files /u01/oradata/nonpdb12c/control01. ctl, /u01/fast_recovery_area/nonp db12c/control02.ctldb_block_size 8192db_domaindb_name nonpdb12db_recovery_file_dest /u01/fast_recovery_areadb_recovery_file_dest_size 4781506560db_unique_name nonpdb12cdiagnostic_dest /u01dispatchers (PROTOCOL=TCP) (SERVICE=nonpdb12c XDB)filesystemio_options SETALLinmemory_size 0local_listener LISTENER_NONPDB12Clog_buffer 7610368memory_max_target 0memory_target 0nls_language JAPANESEnls_territory JAPANopen_cursors 300pga_aggregate_target 805306368processes 300remote_login_passwordfile EXCLUSIVEresource_limit FALSEresource_manager_plan SCHEDULER[0x4449]:DEFAULT_MAINTEN ANCE_PLANsga_max_size 805306368sga_target 805306368shared_pool_size 0undo_tablespace UNDOTBS1 -------------------------------------------------------------

⬆

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 4274598960select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, 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 4274598960select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival,density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2

55

Avg %Total %Tim Total Wait wait Waits CallEvent Waits out Time (s) (ms) /txn Time---------------------------- ------------ ---- ---------- ------ -------- ------db file sequential read 200,359 0 139 1 ######## 8.2db file scattered read 1,710 0 9 5 171.0 .5free buffer waits 12 0 0 9 1.2 .0log file switch completion 4 0 0 25 0.4 .0control file sequential read 231 0 0 0 23.1 .0kksfbc child completion 1 100 0 50 0.1 .0reliable message 80 0 0 0 8.0 .0Disk file operations I/O 24 0 0 1 2.4 .0library cache load lock 2 0 0 14 0.2 .0latch: shared pool 50 0 0 0 5.0 .0

56

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 31 .9

Elapsed: 2.98 (mins) Av Act Sess: 1.0 DB time: 2.95 (mins) DB CPU: 0.96 (mins)

57

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

58

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

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

63

Shared Pool Advisory 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---------- ----- -------- ------------ ------- ------ ------- ------ ----------- 204 1.0 18 1,102 3,502 1.0 17 1.0 219,030 208 1.0 21 1,284 3,505 1.0 14 .8 219,611 212 1.0 25 1,494 3,506 1.0 13 .8 220,078 216 1.1 28 1,671 3,508 1.0 11 .6 220,696 220 1.1 31 1,839 3,509 1.0 10 .6 221,113 224 1.1 34 1,995 3,510 1.0 9 .5 221,613 228 1.1 37 2,207 3,511 1.0 8 .5 221,944 232 1.1 40 2,384 3,511 1.0 8 .5 222,101 236 1.2 44 2,617 3,511 1.0 8 .5 222,322 240 1.2 45 2,713 3,512 1.0 7 .4 222,489 252 1.2 55 3,248 3,513 1.0 6 .4 222,759 276 1.4 76 4,110 3,514 1.0 5 .3 223,040 300 1.5 99 5,146 3,514 1.0 5 .3 223,133 324 1.6 108 5,717 3,514 1.0 5 .3 223,134 348 1.7 108 5,717 3,514 1.0 5 .3 223,134

64

Cache Sizes Begin End~~~~~~~~~~~ ---------- ---------- Buffer Cache: 460M 452M Std Block Size: 8K Shared Pool: 196M 204M Log Buffer: 7,432K

65

～

66

67

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 985249325Module: SQL*Plusdeclare cursor cs_foobar is select rowid from foobar where id between 1 and 100000 order by

27.03 100,000 0.00 45.4 84.33 153,711 3389821460Module: SQL*PlusUPDATE FOOBAR SET A = LPAD('y',1795,'y') WHERE ROWID = :B1

25.32 1 25.32 42.6 90.92 366,391 741139910Module: SQL*PlusSELECT ROWID FROM FOOBAR WHERE ID BETWEEN 1 AND 100000 ORDER BYDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) FOR UPDATE

👻

68

Data Dictionary Cache

Server ResultCache

Other

Library Cache

Shared SQL AreaReserved

Pool

Shared Pool

👻

68

Data Dictionary Cache

Server ResultCache

Other

Library Cache

Shared SQL AreaReserved

Pool

Shared Pool

👻

69

70

71

72

72

～

73

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)

⏬

Top 10 Foreground Events by Total Wait Time~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Total Wait Wait % DB WaitEvent Waits Time (sec) Avg(ms) time Class------------------------------ ----------- ---------- ---------- ------ --------DB CPU 80.2 48.2enq: TX - row lock contention 5,763 51.6 8.96 31.1 Applicatresmgr:cpu quantum 420 1.8 4.38 1.1 Schedulelatch free 177 1.1 6.41 .7 Otherlatch: enqueue hash chains 176 .7 3.92 .4 Otherlog file switch (checkpoint in 37 .7 17.88 .4 Configurlog file sync 67 .4 6.65 .3 Commitlatch: shared pool 27 .2 7.72 .1 ConcurreDisk file operations I/O 45 .1 2.78 .1 User I/Ocursor: pin S 35 .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.16HW-Segment High Water Mark 64 64 0 2 0 5.00TM-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

TablespaceOwner Name-------------------- ---------- Row Subobject Obj. Lock % ofObject 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----------------------- -------------------- -------------------- -------------- % RowEvent % Event Top Row Source Source------------------------------ ------- --------------------------------- ------- btxfn6y1htqsd 2069818805 6 61.54enq: TX - row lock contention 38.46 UPDATE 38.46UPDATE 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 % DBEvent Waits -outs Time (s) wait (ms) /txn time-------------------------- ----------- ----- -------- ---------- -------- ------enq: TX - row lock content 5,763 52 8.96 0.1 31.1resmgr:cpu quantum 420 2 4.38 0.0 1.1latch free 177 1 6.41 0.0 .7latch: 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 ----------------------------------------------- TotalEvent Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s------------------------- ------ ----- ----- ----- ----- ----- ----- ----- -----・・・・・buffer busy waits 37 64.9 13.5 13.5 5.4 2.7cursor: pin S 35 71.4 14.3 11.4 2.9db file async I/O submit 17 17.6 29.4 47.1 5.9db file parallel write 24 45.8 16.7 4.2 8.3 8.3 16.7db file sequential read 183 76.0 14.8 8.7 .5enq: TX - row lock conten 5763 26.4 22.6 23.4 17.4 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 64msEvent to 2s <32ms <64ms <1/8s <1/4s <1/2s <1s <2s >=2s------------------------- ------ ----- ----- ----- ----- ----- ----- ----- -----db file async I/O submit 1 94.1 5.9enq: TX - row lock conten 12 99.8 .2 .0 .0latch free 1 99.5 .5log file parallel write 4 99.9 .1log file sync 1 98.5 1.5oracle 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 4sEvent to 2m <2s <4s <8s <16s <32s < 1m < 2m >=2m------------------------- ------ ----- ----- ----- ----- ----- ----- ----- -----enq: TX - row lock conten 1 100.0 .0　　　　　　　　　　　 ------------------------------------------------------

81

⬆

82

Top 10 Foreground Events by Total Wait Time~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Total Wait Wait % DB WaitEvent Waits Time (sec) Avg(ms) time Class------------------------------ ----------- ---------- ---------- ------ --------DB CPU 80.2 48.2enq: TX - row lock contention 5,763 51.6 8.96 31.1 Applicatresmgr:cpu quantum 420 1.8 4.38 1.1 Schedulelatch free 177 1.1 6.41 .7 Otherlatch: enqueue hash chains 176 .7 3.92 .4 Otherlog file switch (checkpoint in 37 .7 17.88 .4 Configurlog file sync 67 .4 6.65 .3 Commitlatch: shared pool 27 .2 7.72 .1 ConcurreDisk file operations I/O 45 .1 2.78 .1 User I/Ocursor: pin S 35 .1 2.30 .0 Concurre・・・・・

Total Wait Wait % DB WaitEvent Waits Time (sec) Avg(ms) time Class------------------------------ ----------- ---------- ---------- ------ --------DB CPU 116.2 52.1enq: TX - row lock contention 9,707 35.5 3.65 15.9 Applicatresmgr:cpu quantum 3,051 16.7 5.46 7.5 Schedulecursor: pin S wait on X 92 7.8 85.04 3.5 Concurredb file sequential read 1,210 1.3 1.04 .6 User I/Ocursor: pin S 148 1.2 8.43 .6 Concurrelibrary cache load lock 125 1.2 9.86 .6 Concurre・・・・・

82

Top 10 Foreground Events by Total Wait Time~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Total Wait Wait % DB WaitEvent Waits Time (sec) Avg(ms) time Class------------------------------ ----------- ---------- ---------- ------ --------DB CPU 80.2 48.2enq: TX - row lock contention 5,763 51.6 8.96 31.1 Applicatresmgr:cpu quantum 420 1.8 4.38 1.1 Schedulelatch free 177 1.1 6.41 .7 Otherlatch: enqueue hash chains 176 .7 3.92 .4 Otherlog file switch (checkpoint in 37 .7 17.88 .4 Configurlog file sync 67 .4 6.65 .3 Commitlatch: shared pool 27 .2 7.72 .1 ConcurreDisk file operations I/O 45 .1 2.78 .1 User I/Ocursor: pin S 35 .1 2.30 .0 Concurre・・・・・

Total Wait Wait % DB WaitEvent Waits Time (sec) Avg(ms) time Class------------------------------ ----------- ---------- ---------- ------ --------DB CPU 116.2 52.1enq: TX - row lock contention 9,707 35.5 3.65 15.9 Applicatresmgr:cpu quantum 3,051 16.7 5.46 7.5 Schedulecursor: pin S wait on X 92 7.8 85.04 3.5 Concurredb file sequential read 1,210 1.3 1.04 .6 User I/Ocursor: pin S 148 1.2 8.43 .6 Concurrelibrary cache load lock 125 1.2 9.86 .6 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.16HW-Segment High Water Mark 64 64 0 2 0 5.00TM-DML 76,240 76,240 0 0 0 N/A

83

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.82SQ-Sequence Cache 3 3 0 2 0 5.00

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

TablespaceOwner Name-------------------- ---------- Row Subobject Obj. Lock % ofObject 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----------------------- -------------------- -------------------- -------------- % RowEvent % Event Top Row Source Source------------------------------ ------- --------------------------------- ------- btxfn6y1htqsd 2069818805 6 61.54enq: TX - row lock contention 38.46 UPDATE 38.46UPDATE LOCK_TABLE SET STATUS=1 WHERE ID = :B1　　　　　　　　　　　　　

CPU + Wait for CPU 23.08 UPDATE STATEMENT 15.38・・・・

85

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 % DBEvent Waits -outs Time (s) wait (ms) /txn time-------------------------- ----------- ----- -------- ---------- -------- ------enq: TX - row lock content 5,763 52 8.96 0.1 31.1resmgr:cpu quantum 420 2 4.38 0.0 1.1latch free 177 1 6.41 0.0 .7latch: enqueue hash chains 176 1 3.92 0.0 .4・・・・・

86

Total %Time Wait Avg Waits % DBEvent Waits -outs Time (s) wait (ms) /txn time-------------------------- ----------- ----- -------- ---------- -------- ------enq: TX - row lock content 9,707 35 3.65 0.1 15.9resmgr:cpu quantum 3,051 17 5.46 0.0 7.5cursor: pin S wait on X 92 8 85.04 0.0 3.5db file sequential read 1,210 1 1.04 0.0 .6cursor: pin S 148 1 8.43 0.0 .6

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 % DBEvent Waits -outs Time (s) wait (ms) /txn time-------------------------- ----------- ----- -------- ---------- -------- ------enq: TX - row lock content 5,763 52 8.96 0.1 31.1resmgr:cpu quantum 420 2 4.38 0.0 1.1latch free 177 1 6.41 0.0 .7latch: enqueue hash chains 176 1 3.92 0.0 .4・・・・・

86

Total %Time Wait Avg Waits % DBEvent Waits -outs Time (s) wait (ms) /txn time-------------------------- ----------- ----- -------- ---------- -------- ------enq: TX - row lock content 9,707 35 3.65 0.1 15.9resmgr:cpu quantum 3,051 17 5.46 0.0 7.5cursor: pin S wait on X 92 8 85.04 0.0 3.5db file sequential read 1,210 1 1.04 0.0 .6cursor: pin S 148 1 8.43 0.0 .6

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 ----------------------------------------------- TotalEvent Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s------------------------- ------ ----- ----- ----- ----- ----- ----- ----- -----・・・・・db file parallel write 24 45.8 16.7 4.2 8.3 8.3 16.7db file sequential read 183 76.0 14.8 8.7 .5enq: TX - row lock conten 5763 26.4 22.6 23.4 17.4 8.0 2.0 .2 .0・・・・・

87

Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s------------------------- ------ ----- ----- ----- ----- ----- ----- ----- -----・・・・・db file scattered read 51 9.8 31.4 27.5 15.7 15.7db file sequential read 1463 64.0 14.3 11.3 7.2 2.9 .3enq: TX - row lock conten 9707 25.5 22.2 23.9 18.0 8.2 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 ----------------------------------------------- TotalEvent Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s------------------------- ------ ----- ----- ----- ----- ----- ----- ----- -----・・・・・db file parallel write 24 45.8 16.7 4.2 8.3 8.3 16.7db file sequential read 183 76.0 14.8 8.7 .5enq: TX - row lock conten 5763 26.4 22.6 23.4 17.4 8.0 2.0 .2 .0・・・・・

87

Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s------------------------- ------ ----- ----- ----- ----- ----- ----- ----- -----・・・・・db file scattered read 51 9.8 31.4 27.5 15.7 15.7db file sequential read 1463 64.0 14.3 11.3 7.2 2.9 .3enq: TX - row lock conten 9707 25.5 22.2 23.9 18.0 8.2 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 64msEvent to 2s <32ms <64ms <1/8s <1/4s <1/2s <1s <2s >=2s------------------------- ------ ----- ----- ----- ----- ----- ----- ----- -----db file async I/O submit 1 94.1 5.9enq: TX - row lock conten 12 99.8 .2 .0 .0latch free 1 99.5 .5log file parallel write 4 99.9 .1log file sync 1 98.5 1.5oracle thread bootstrap 3 50.0 16.7 16.7 16.7 ------------------------------------------------------

88

Event to 2s <32ms <64ms <1/8s <1/4s <1/2s <1s <2s >=2s------------------------- ------ ----- ----- ----- ----- ----- ----- ----- -----cursor: pin S wait on X 48 47.8 12.0 21.7 3.3 15.2enq: TX - row lock conten 22 99.8 .2 .0 library cache load lock 2 98.4 1.6

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 64msEvent to 2s <32ms <64ms <1/8s <1/4s <1/2s <1s <2s >=2s------------------------- ------ ----- ----- ----- ----- ----- ----- ----- -----db file async I/O submit 1 94.1 5.9enq: TX - row lock conten 12 99.8 .2 .0 .0latch free 1 99.5 .5log file parallel write 4 99.9 .1log file sync 1 98.5 1.5oracle thread bootstrap 3 50.0 16.7 16.7 16.7 ------------------------------------------------------

88

Event to 2s <32ms <64ms <1/8s <1/4s <1/2s <1s <2s >=2s------------------------- ------ ----- ----- ----- ----- ----- ----- ----- -----cursor: pin S wait on X 48 47.8 12.0 21.7 3.3 15.2enq: TX - row lock conten 22 99.8 .2 .0 library cache load lock 2 98.4 1.6

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 4sEvent to 2m <2s <4s <8s <16s <32s < 1m < 2m >=2m------------------------- ------ ----- ----- ----- ----- ----- ----- ----- -----enq: TX - row lock conten 1 100.0 .0　　　　　　　　　　　 ------------------------------------------------------

89

Wait Event Histogram Detail (4 sec to 2 min)

No data exists for this section of the report. ------------------------------------------------------

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 4sEvent to 2m <2s <4s <8s <16s <32s < 1m < 2m >=2m------------------------- ------ ----- ----- ----- ----- ----- ----- ----- -----enq: TX - row lock conten 1 100.0 .0　　　　　　　　　　　 ------------------------------------------------------

89

Wait Event Histogram Detail (4 sec to 2 min)

No data exists for this section of the report. ------------------------------------------------------

90

<1ms<2ms<4ms<8ms<16ms<32ms<64ms<125ms

<1ms<2ms<4ms<8ms<16ms<32ms<64ms<125ms<32000ms

91

<1ms<2ms<4ms<8ms<16ms<32ms<64ms<125ms<32000ms

<1ms<2ms<4ms<8ms<16ms<32ms<64ms<125ms

91

<1ms<2ms<4ms<8ms<16ms<32ms<64ms<125ms<32000ms

<1ms<2ms<4ms<8ms<16ms<32ms<64ms<125ms

<32000ms

91

<1ms<2ms<4ms<8ms<16ms<32ms<64ms<125ms<32000ms

<1ms<2ms<4ms<8ms<16ms<32ms<64ms<125ms

<32000ms

92

http://www.jpoug.org

93

http://www.jpoug.org

✌

93