eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« Oracle Mutex 等待事件之: cursor: mutex S | Blog首页 | Oracle SQL和PL/SQL中字符串单引号的处理 »

Oracle Mutex 等待事件之: cursor mutex X

Cursor Mutex X 是当会话对某个 Cursor 请求排他模式锁,但是该Cursor 上已经有 S 或 X 模式的锁定,因而产生的等待。

The session requests the mutex for a cursor object in exclusive mode, and it must wait because the resource is busy. The mutex is busy because either the mutex is being held in exclusive mode by another session or the mutex is being held shared by one or more sessions. The existing mutex holder(s) must release the mutex before the mutex can be granted exclusively.

ParameterDescription

P1

Hash value of cursor

P2

Mutex value (top 2 bytes contain SID holding mutex in exclusive mode, and bottom two bytes usually hold the value 0)

P3

Mutex where (an internal code locator) OR'd with Mutex Sleeps

需要持有 Cursor Mutex X 的操作主要有:

  • 在父游标下创建新的子游标,但是这类操作在11.2之后被 Build Lock 替代,不再需要;
  • 捕获SQL中的绑定变量;
  • 更新、生成 SQL 统计信息,主要是 V$SQLSTATS 的内容;
  • Cursor Mutex X 主要指对于父游标的操作;
  • Building a new cursor under a parent
    • Although this operation is cheaper, building many cursors under a parent cursor is not recommended.
  • Capture SQL bind data
  • Build or Update statistics blocks
  • Mutex is in the parent cursor.

以下是在生产环境中观察到的实际现象,注意在获取 cursor:mutex X 的过程中,调用的底层函数是kkscsAddChildNode 这是增加子游标的操作,需要进一步的获取排他Mutex ,使用的函数是 kgxExclusive :

SQL> select sid,event,p1,p2,p3 from v$session where username='EYGLE' and sid=135;
       SID EVENT				      P1	 P2	  P3
---------- ---------------------------------- ---------- ---------- ----------
       135 cursor: mutex X		       978779761 1.6063E+12 1.4603E+11

SQL> oradebug short_stack;
ksedsts()+461<-ksdxfstk()+32<-ksdxcb()+1876<-sspuser()+112<-__sighandler()<-semtimedop()+10
<-skgpwwait()+160<-ksliwat()+1865<-kslwaitctx()+163<-ksfwaitctx()+14<-kgxWait()+650
<-kgxExclusive()+295<-kkscsAddChildNode()+337<-kkscscid_opt_eval()+1290<-kkscsCheckCriteria()+211
<-kkscsCheckCursor()+783<-kkscsSearchChildList()+1262<-kksfbc()+12105<-kkspsc0()+1173
<-kksParseCursor()+116<-opiosq0()+1588<-opipls()+11479<-opiodr()+916<-rpidrus()+211
<-skgmstack()+148<-rpiswu2()+638<-rpidrv()+1384<-psddr0()+473<-psdnal()+457<-pevm_EXIM()+308
<-pfrinstr_EXIM()+53<-pfrrun_no_tool()+63<-pfrrun()+627<-plsql_run()+649<-peicnt()+301
<-kkxexe()+525<-opiexe()+17785<-kpoal8()+2224<-opiodr()+916<-ttcpip()+2755<-opitsk()+1717
<-opiino()+966<-opiodr()+916<-opidrv()+570<-sou2o()+103<-opimai_real()+133
<-ssthrdmain()+252<-main()+201<-__libc_start_main()+253<-_start()+36

关于Mutex 的使用一直在变化之中。


历史上的今天...
    >> 2009-07-07文章:
    >> 2008-07-07文章:
           孩子改变世界
    >> 2005-07-07文章:
           瑞典游记-序幕I

无觅

By eygle on 2017-07-07 17:12 | Comments (0) | FAQ | 3259 |


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com