|
|||||
|
以下脚本可以用于诊断Oracle实例中的锁情况(Lock Status): REM SCRIPT: FULLY DECODED LOCKING set echo off set lines 200 set pagesize 66 break on Kill on sid on username on terminal column Kill heading ‘Kill String’ format a13 column res heading ‘Resource Type’ format 999 column id1 format 9999990 column id2 format 9999990 column locking heading ‘Lock Held/Lock Requested’ format a40 . . . → Read More: Script:Diagnostic Oracle Locks “Cross Instance call Enqueue”是一种在一个或多个instance实例间调用后台进程行为时用到的队列锁,具体调用的后台进程行为包括检查点checkpoint、日志切换logfile switch、shutdown实例、载入数据文件头等等。需要注意的是这种Enqueue Lock并不仅仅在RAC中使用，即便是单节点也会用到。CI锁的数量取决于并行执行Cross Instance Call调用的进程的总数。 SQL> col ksqsttyp for a20 SQL> col ksqstrsn for a20 SQL> col ksqstexpl for a80 SQL> set linesize 200 pagesize 2000; SQL> select ksqsttyp,ksqstrsn,ksqstexpl from x$ksqst where ksqsttyp=’CI’; KSQSTTYP KSQSTRSN KSQSTEXPL ——————– ——————– ——————————————————————————– CI contention Coordinates cross-instance function invocations SQL> show parameter cluster_database NAME TYPE VALUE . . . → Read More: Oracle等待事件Enqueue CI:Cross Instance Call Invocation 我们在查看10704 event trace(Print out information about what enqueues are being obtained)或deadlock detected trace死锁检测跟踪日志时，总是会看到名为”DID”的名词，影响”DID”这个名词被正确理解的一个原因是你很难通过search engine正确找到相关的正确解释(被误解)。 那么DID到底是什么东西呢？我们来看一下trace中的DID: =====================10704 enqueue trace======================== ksqgtl *** CU-913f5a28-00000000 mode=6 flags=0×10010 timeout=300 *** ksqgtl: no transaction ksqgtl: use existing ksusetxn DID ksqgtl: ksqlkdid: 0002-001E-00000026 *** 2011-05-09 23:44:15.210 *** ksudidTrace: ksqgtl ksusesdi: 0002-001E-00000025 ksusetxn: 0002-001E-00000026 ksqgtl: RETURNS 0 *** 2011-05-09 23:44:15.212 ksqrcl: CU,913f5a28,0 ksqrcl: . . . → Read More: Identify ksusetxn DID:An Deadlock ID 内部视图x$ksqst记录了以enqueue type分类的队列管理统计信息(Enqueue management statistics by type)，ksqstwat列反映了等待次数，而ksqstwtim列反映了累积等待时间，实际上v$enqueue_stat动态性能视图的CUM_WAIT_TIME列数据也来源于ksqstwtim: SQL> select * from v$fixed_view_definition where view_name=upper(‘gv$enqueue_stat’); VIEW_NAME —————————— VIEW_DEFINITION ——————————————————————————– GV$ENQUEUE_STAT select inst_id, ksqsttyp, sum(ksqstreq), sum(ksqstwat), sum(ksqstsgt), sum(ksqstfgt), sum(ksqstwtm) from X$KSQST group by inst_id, ksqsttyp having sum(ksqstreq) > 0 SQL> desc X$KSQST Name Null? Type —————————————– ——– —————————- ADDR RAW(8) INDX NUMBER INST_ID NUMBER KSQSTTYP VARCHAR2(2) KSQSTREQ NUMBER . . . → Read More: Oracle Enqueue Lock Type Reference including 11g new locks Single resource deadlock: blocking enqueue which blocks itself, f 0 Single resource deadlock: blocking enqueue which blocks itself, f 0 Granted global enqueue 0xd8578490 ———-enqueue 0xd8578490———————— lock version : 1 Owner inst : 2 grant_level : KJUSERCW req_level : KJUSERPW bast_level : KJUSERNL notify_func : 0x4fe4b6e resp : 0xd9c7ad50 procp : 0xd9971780 pid : 0 . . . → Read More: RAC Deadlock For Example Value Name(s) Table method (TM lock) 0 No lock n/a 1 Null lock (NL) Used during some parallel DML operations (e.g. update) by the pX slaves while the QC is holding an exclusive lock. 2 Sub-share (SS) Until 9.2.0.5/6 “select for update” Row-share (RS) Since 9.2.0.1/2 used at opposite end of RI during DML . . . → Read More: Know Oracle Lock Mode |
|
||||
|
Copyright © 2012 Oracle Clinic – Maclean Liu的个人技术博客 - All Rights Reserved |
|||||
最新评论