应用反应说系统慢,时间不固定,现象不知道,就是慢。无奈只好登陆系统查查看了。
查看系统上现有的快照信息
SQL> col mintime for a30
SQL> col maxtime for a30
SQL>
SQL> select min(snap_id) minid, max(snap_id) maxid,
2 to_char(min(begin_interval_time),'yyyy-mm-dd hh34:mi:ss') mintime,
3 to_char(max(end_interval_time),'yyyy-mm-dd hh34:mi:ss') maxtime
4 from dba_hist_snapshot;
根据快照信息,我们来查看一下对应的等待事件分类情况
SQL> 1 select wait_class_id,wait_class, count(*) cnt
2 from dba_hist_active_sess_history
3 where snap_id between 78303 and 78472
4 group by wait_class_id, wait_class
5* order by 3
WAIT_CLASS_ID WAIT_CLASS CNT
------------- -------------------- ----------
2723168908 Idle 2
4166625743 Administrative 6
2000153315 Network 538
4217450380 Application 829
3290255840 Configuration 4128
4108307767 System I/O 9234
1893977003 Other 11043
3386400367 Commit 26802
1740759767 User I/O 28076
375421
3875070507 Concurrency 888984
11 rows selected.
查看具体的等待事件情况
SQL> select event_id, event, count(*) cnt
2 from dba_hist_active_sess_history
3 where snap_id between 78303 and 78472
4 and wait_class_id=3875070507
5 group by event_id, event
6 order by 3;
EVENT_ID EVENT CNT
---------- ---------------------------------------------------------------- ----------
877525844 cursor: mutex X 1
86156091 os thread startup 6
1242501677 latch: library cache pin 7
1714089451 row cache lock 7
2952162927 library cache load lock 10
2802704141 library cache pin 22
2032051689 latch: library cache lock 45
1117386924 latch: row cache objects 60
1394127552 latch: In memory undo latch 68
2779959231 latch: cache buffers chains 873
2161531084 buffer busy waits 4286
916468430 library cache lock 4549
2696347763 latch: shared pool 12360
589947255 latch: library cache 12718
1729366244 cursor: pin S wait on X 853972
查找出pin S wait on X对应的SQL
SQL> select sql_id, count(*) cnt
from dba_hist_active_sess_histo 2 ry
where snap_id between 78303 and 3 78472
4 and event_id in (1729366244)
5 group by sql_id
having count(*)> 6 100
order by 7 2 desc;
SQL_ID CNT
------------- ----------
0nuvj12m3ryvy 853880
接着上面的查询我们可以从awr历史信息里找到这些sql语句主要在等待那些对象:
SQL> select owner,current_obj#,object_name,object_type, count(*) cnt
2 from dba_hist_active_sess_history a, dba_objects b
3 where snap_id between 78303 and 78472
4 and event_id in (1729366244)
5 and sql_id in ('0nuvj12m3ryvy')
6 and a.current_obj#=b.object_id
7 group by owner,current_obj#,object_name,object_type
8 having count(*) > 10
9 order by 5 desc;
OWNER CURRENT_OBJ# OBJECT_NAME OBJECT_TYPE CNT
---------- ------------ ------------------------------ ------------------- ----------
SETTLE 49326 T_OPERATE_LOG TABLE 654899
SYS 73541 LOG$INFORMATION TABLE 16337
SETTLE 48117 G_MENU_RIGHT TABLE 9684
SETTLE 141993 CONFIG_UNIX INDEX 9567
SETTLE 136520 T_MANAGE_WARN_CONFIG TABLE 9565
SETTLE 51955 T_BILL_LOG TABLE 9520
SETTLE 48128 G_ROLE TABLE 9458
下面确认等待的数据库是否过于集中,也就是确认是否存在热块儿问题:
SQL> select current_file#,current_block#, count(*) cnt
2 from dba_hist_active_sess_history
3 where snap_id between 78303 and 78472
4 and event_id in (1729366244)
and sql_id in ('0nuvj12m3ryv 5 y')
6 and current_obj# in (49326,48117,141993,136520,51955,48128)
7 group by current_file#, current_block#
8 having count(*)>50
9 order by 3;
CURRENT_FILE# CURRENT_BLOCK# CNT
------------- -------------- ----------
9 4436 9458
276 839623 9500
246 857417 9520
276 839495 9521
2 532140 9565
55 1153960 9567
276 840134 9648
25 739537 9684
275 906620 9687
276 838125 15128
276 843388 15131
275 906533 15138
275 904906 15180
275 904851 15186
275 902677 15210
276 845366 15210
275 909383 15216
275 902396 15220
275 905990 15333
275 909920 15422
276 840809 15427
276 845296 15451
275 906837 15454
276 843996 15777
276 837403 15778
275 908047 15784
275 906933 15813
275 909489 15813
275 903374 15814
276 844903 15886
276 841993 15925
275 907463 15942
276 839733 15944
275 905797 15944
275 908458 15944
276 838802 15948
276 843290 15950
275 905767 16209
275 909728 16213
275 904723 16262
275 908888 16263
276 844986 16275
276 844862 16347
275 906325 16394
275 904842 16403
275 908197 24737
276 841357 25472
47 rows selected.
网上也有另外一种思路,是根据MOS上面的
How to Determine the Blocking Session for Event: 'cursor: pin S wait on X' (Doc ID 786507.1)
Cursor: pin S wait on X
Cursor: pin S wait on X
当一个session为一个与pin相关的共享操作(such as executing a cursor)请求一个mutex时,该session会有Cursor: pin S wait on X等待事件。
但是该mutex不能被授权,因为该mutex正在被其他session以排他模式持有(比如 parsing the cursor)
v$session or v$session_wait中的p2raw列 给出了 cursor: pin S wait on X等待事件的阻塞者session(持有者session)
按照MOS文档方法,我们查看一下
SQL> select p2raw from v$session where event = 'cursor: pin S wait on X';
P2RAW
----------------
000001B200000000
000001B200000000
000001B200000000
000001B200000000
000001B200000000
000001B200000000
000001B200000000
000001B200000000
000001B200000000
000001B200000000
000001B200000000
000001B200000000
000001B200000000
000001B200000000
000001B200000000
000001B200000000
000001B200000000
--参数说明
免责声明:
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
软考中级精品资料免费领
- 历年真题答案解析
- 备考技巧名师总结
- 高频考点精准押题
- 资料下载
- 历年真题
193.9 KB下载数265
191.63 KB下载数245
143.91 KB下载数1148
183.71 KB下载数642
644.84 KB下载数2756