做STATSPACK报告显示如下
STATSPACK report for
DB NameDB Id InstanceInst NumRelease OPS Host
---------------------------------------------------------------
ORA816 1558744441ora816
18.1.6.0.0 NOTJSERVER
Snap Length
Start IdEnd Id Start Time
End Time (Minutes)
-------------------------------------------------------------------
1 216-Nov-06 08:25:5316-Nov-06 09:16:24
50.52
Cache Sizes
~~~~~~~~~~~
db_block_buffers: 64000
db_block_size:8192
log_buffer: 32768
shared_pool_size: 104857600
Load Profile
~~~~~~~~~~~~
Per SecondPer Transaction
------------------------------
Redo size:
2,541.53
8,999.26
Logical reads:
10,712.50
37,931.74
Block changes:
16.92
59.92
Physical reads:
1,484.89
5,257.84
Physical writes:
4.62
16.35
User calls:
103.63
366.93
Parses:
28.99
102.65
Hard parses:
1.01
3.58
Sorts:
1.28
4.53
Transactions:
0.28
Rows per Sort:219.26
Pct Blocks changed / Read:0.16
Recursive Call Pct: 38.27
Rollback / transaction Pct:6.19
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait Ratio: 99.67
BufferHit Ratio: 86.14
Library Hit Ratio: 98.06
Redo NoWait Ratio: 99.97
In-memory Sort Ratio: 96.31
Soft Parse Ratio: 96.52
Latch Hit Ratio: 99.87
Top 5 Wait Events
~~~~~~~~~~~~~~~~~
Wait % Total
Event
WaitsTime (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
db file scattered read
595,327233,457 82.44
buffer busy waits
107,516 19,8387.00
direct path write
5,3939,8993.50
direct path read
6,9374,6061.63
db file sequential read
16,6123,5331.25
-------------------------------------------------------------
得出有些表可能索引没建或不好，
通过查询v$session_longops视图
，确定问题表
TARGET
OPNAME
USERNAMESQL_HASH_VALUE
------------------------------ -------------------- --------------- --------------
OUTPADM.CLINIC_BILL_ITEMSTable Scan ZM38
1209238697
OUTPADM.CLINIC_BILL_ITEMSTable Scan ZM332
4112651358
OUTPADM.CLINIC_BILL_ITEMSTable Scan ZM38
1209238697
OUTPADM.CLINIC_BILL_ITEMSTable Scan ZM181
3234940250
OUTPADM.CLINIC_BILL_ITEMSTable Scan ZM930
344388034
OUTPADM.CLINIC_BILL_ITEMSTable Scan ZM600
1209238697
OUTPADM.CLINIC_BILL_ITEMSTable Scan ZM181
2829783299
OUTPADM.CLINIC_BILL_ITEMSTable Scan ZM940
4051556578
OUTPADM.CLINIC_BILL_ITEMSTable Scan ZM2631
1087743228
OUTPADM.CLINIC_BILL_ITEMSTable Scan ZM181
1357963817
OUTPADM.CLINIC_BILL_ITEMSTable Scan ZM2631 |
