|
实施反馈系统有20分钟不可用，然后又自动恢复了。先查看alert日志，看到打开文件数不够，系统已经运行几年了，怎么可能呢。
Non critical error ORA-48180 caught while writing to trace file "/u01/app/ora/diag/rdbms/nwzcdb/nwzcdb2/trace/nwzcdb2_ora_195339.trc"
Error message: Linux-x86_64 Error: 23: Too many open files in system
检查数据库服务器的配置，ulimit -a ，发现oracle hard nofile 65536，应该是足够大的。
查看问题时段的数据库报告，发现数据库过载了。
|
|
Snap Id |
Snap Time |
Sessions |
Cursors/Session |
Instances |
Begin Snap:
13158
24-3月 -17 09:00:27
1531
7.5
2
End Snap:
13159
24-3月 -17 10:00:38
1810
10.0
2
Elapsed:
60.18 (mins)
DB Time:
32,066.54 (mins)
11g开始gc buffer busy分为gc buffer busy acquire和gc buffer busy release:
gc buffer busy acquire是当session#1尝试请求访问远程实例(remote instance) buffer，但是在session#1之前已经有相同实例上另外一个session#2请求访问了相同的buffer，并且没有完成，那么session#1等待gc buffer busy acquire。
gc buffer busy release是在session#1之前已经有远程实例的session#2请求访问了相同的buffer，并且没有完成，那么session#1等待gc buffer busy release。
Event |
Waits |
Total Wait Time (sec) |
Wait Avg(ms) |
% DB time |
Wait Class |
gc buffer busy acquire
288,206
568.5K
1972
30.5
Cluster
enq: TX - index contention
11,889
325K
27335
17.5
Concurrency
buffer busy waits
54,302
217.9K
4012
11.7
Concurrency
gc buffer busy release
228,772
200.8K
878
10.8
Cluster
gc current grant busy
251,301
73.6K
293
4.0
Cluster
gc current block congested
109,356
71.2K
651
3.8
Cluster
gc cr block congested
25,922
69.2K
2669
3.7
Cluster
gc cr grant congested
30,967
42.1K
1360
2.3
Cluster
我认为可能是两个原因造成的：
1. 低效SQL，逻辑读过大，且访问频繁，造成争用严重。
2. 数据库IO资源紧张，导致一些频繁访问的SQL语句响应慢，造成gc buffer busy acquire,gc buffer busy release等待事件。
定位是否是原因1的问题，就找Segments by Global Cache Buffer Busy。然后根据对象的名称去找对应的SQL，然后查看SQL的执行计划定位问题。
Segments by Global Cache Buffer Busy
- % of Capture shows % of GC Buffer Busy for each top segment compared
- with GC Buffer Busy for all segments captured by the Snapshot
Owner |
Tablespace Name |
Object Name |
Subobject Name |
Obj. Type |
GC Buffer Busy |
% of Capture |
LCSC
LCSC_DATA
INDEX_LOG_UO_OPERATE_TIME
INDEX
266,048
36.39
LCSC
LCSC_DATA
SS_SECURITY_RESPONSIBILITY
TABLE
112,425
15.38
DIS_TRANSFER
P_0501
TABLE PARTITION
62,460
8.54
LCSC_DATA
INDEX_LOG_FUN_OPER_DATE
INDEX
27,816
3.80
P_0507
27,775
3.80
定位是否是问题2造成，先查看数据库IO的整体情况，如果是RAC，多个节点都要看，因为RAC是共享存储，消耗IO总量是多个节点之和。如果如下图所示，相比数据库正常的时刻是非常大的。
如何判断是否是问题2影响了问题1，就查看问题1找到的SQL是否有消耗IO，如果有，则有影响。
IOStat by Function summary
- 'Data' columns suffixed with M,G,T,P are in multiples of 1024 other columns suffixed with K,M,P are in multiples of 1000
- ordered by (Data Read + Write) desc
Function Name |
Reads: Data |
Reqs per sec |
Data per sec |
Writes: Data |
Waits: Count |
Avg Tm(ms) |
Direct Reads
475.2G
209.64
134.921
1.8G
3.97
.503M
0
Buffer Cache Reads
34G
327.39
9.649M
0M
0.00
0M
616.6K
9.91
Direct Writes
5.8G
1.72
1.646M
22.6G
33.85
6.418M
Others
5G
7.07
1.407M
5.1G
11.22
1.441M
26.1K
2.24
DBWR
0M
0.01
0M
8.6G
217.96
2.44M
20
34.20
LGWR
153M
2.73
.042M
4.5G
214.76
1.292M
679.9K
1.03
TOTAL:
520.1G
548.55
147.665
42.6G
481.76
12.094M
1322.6K
5.20
问题2的定位是通过segments by physical reads来找到相应的SQL。
Segments by Physical Reads
- Total Physical Reads: 67,312,485
- Captured Segments account for 81.6% of Total
Physical Reads |
%Total |
LCSC
LCSC_DATA
FUNCTION_LOCATION
P_DEFAULT_SUB_P_0502
TABLE SUBPARTITION
12,612,706
18.74
LCSC
LCSC_DATA
FUNCTION_LOCATION
P_DEFAULT_SUB_P_0501
TABLE SUBPARTITION
7,703,111
11.44
P_DEFAULT_SUB_P_0505
7,258,626
10.78
LCSC
LCSC_DATA
FUNCTION_LOCATION
P_DEFAULT_SUB_P_0503
TABLE SUBPARTITION
6,005,657
8.92
P_DEFAULT_SUB_P_0507
4,008,989
5.96
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
