关于library cache lock的P3参数解析的疑问

枕霜卧雪

在分析hanganlyze的报告输出中有如下信息：
is waiting for 'library cache lock' with wait info:
   {
                     p1: 'handle address'=0x9a7eb1820
                     p2: 'lock address'=0x9aa780190
                     p3: '100*mode+namespace'=0x2dd16500010003   
其中p3的解释为'100*mode+namespace'。
那么要如何换算出namespace和lockmod呢？

Maclean Liu(刘相兵

至少给出版本信息。。。

枕霜卧雪

Maclean Liu(刘相兵 发表于 2015-5-6 11:23
至少给出版本信息。。。

$ORACLE_HOME/OPatch/opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.6
Copyright (c) 2013, Oracle Corporation. All rights reserved.


Oracle Home : /orabnm/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /orabnm/app/oraInventory
from : /orabnm/app/oracle/product/11.2.0/dbhome_1/oraInst.loc
OPatch version : 11.2.0.3.6
OUI version : 11.2.0.4.0
Log file location : /orabnm/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2015-05-05_14-27-47PM_1.log

Lsinventory Output file location : /orabnm/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2015-05-05_14-27-47PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Database 11g 11.2.0.4.0
There are 1 product(s) installed in this Oracle Home.


Interim patches (3) :

Patch 17551261 : applied on Thu Oct 30 18:30:59 CST 2014
Unique Patch ID: 17521045
Created on 10 Apr 2014, 10:16:30 hrs PST8PDT
Bugs fixed:
17551261

Patch 16188701 : applied on Thu Oct 30 18:28:26 CST 2014
Unique Patch ID: 17521048
Created on 10 Apr 2014, 10:13:31 hrs PST8PDT
Bugs fixed:
16188701

Patch 18522509 : applied on Thu Oct 30 18:26:08 CST 2014
Unique Patch ID: 17652812
Patch description: "Database Patch Set Update : 11.2.0.4.3 (18522509)"
Created on 1 Jul 2014, 23:47:17 hrs PST8PDT
Sub-patch 18031668; "Database Patch Set Update : 11.2.0.4.2 (18031668)"
Sub-patch 17478514; "Database Patch Set Update : 11.2.0.4.1 (17478514)"
Bugs fixed:
17752995, 17288409, 16392068, 17205719, 17811429, 17767676, 17614227
17040764, 17381384, 17754782, 17726838, 13364795, 17311728, 17389192
17006570, 17612828, 17284817, 17441661, 13853126, 17721717, 13645875
18203837, 17390431, 16542886, 16992075, 16043574, 17446237, 16863422
14565184, 17071721, 17610798, 17468141, 17786518, 17375354, 17397545
18203838, 16956380, 17478145, 16360112, 17235750, 17394950, 13866822
17478514, 17027426, 12905058, 14338435, 16268425, 13944971, 18247991
14458214, 16929165, 17265217, 13498382, 17786278, 17227277, 17546973
14054676, 17088068, 16314254, 17016369, 14602788, 17443671, 16228604
16837842, 17332800, 17393683, 13951456, 16315398, 18744139, 17186905
16850630, 17437634, 19049453, 17883081, 15861775, 17296856, 18277454
16399083, 16855292, 18018515, 10136473, 16472716, 17050888, 17865671
17325413, 14010183, 18554871, 17080436, 16613964, 17761775, 16721594
17588480, 17551709, 17344412, 18681862, 15979965, 13609098, 18139690
17501491, 17239687, 17752121, 17602269, 18203835, 17297939, 17313525
16731148, 17811456, 14133975, 17600719, 17385178, 17571306, 16450169
17655634, 18094246, 17892268, 17165204, 17011832, 17648596, 16785708
17477958, 16180763, 16220077, 17465741, 17174582, 18522509, 16069901
16285691, 17323222, 18180390, 17393915, 16875449, 18096714, 17238511
17596908, 17811438, 17811447, 18031668, 16912439, 18061914, 17622427
17545847, 16943711, 17082359, 17346671, 18996843, 14852021, 17783588
16618694, 17672719, 17614134, 17341326, 17546761, 17716305



--------------------------------------------------------------------------------

OPatch succeeded.

Maclean Liu(刘相兵

odm finding:
Also how can we check the namespace in 11g from p3 parameters. In 10g it was 100*namespace+mode.

In 11g i see this in my p3(272876452249603). I believe I can still look at the last digit for mode (here its 3).



在11g以后 p3 应当不再是简单的100*mode+namespace，但由于 100*mode+namespace是一个代码提示，应当是开发的人员没有对此做修改。

Maclean Liu(刘相兵

more odm finding:

Individual Waits: Parameters:
P1 = Handle address
P2 = Pin address
P3 = Encoded Mode & Namespace
Handle address
Use P1RAWrather than P1
This is the handle of the library cache object which the waiting session wants to acquire a pin on. The actual object being waited on can be found using
SELECT kglnaown "Owner", kglnaobj "Object" FROM x$kglob
WHERE kglhdadr='&P1RAW' ;
Pin address
Use P2RAWrather than P2
This is the address of the PIN itself.
Encoded Mode & Namespace
In Oracle 7.0 - 8.1.7 inclusive the value is 10*Mode+Namespace.
In Oracle 9.0 - 11.1 inclusive the value is 100*Mode+Namespace.
Where:
Modeis the mode in which the pin is wanted. This is a number thus: 2 - Share mode
3 - Exclusive mode
Namespaceis just the namespace number of the namespace in the library cache in which the required object lives: 0 SQL Area
1 Table / Procedure / Function / Package Header 2 Package Body
3 Trigger
4 Index
5 Cluster
6 Object
7 Pipe
13 Java Source 14 Java Resource 32 Java Data

枕霜卧雪

Maclean Liu(刘相兵 发表于 2015-5-6 11:29
more odm finding:

Individual Waits: Parameters:

按照您的反馈信息，11g之后没法直接看出lock mode了吗？
因为我的疑问是从一个sr中看到的，sr上原厂工程师直接从输出信息中判断，语句持有的mode 3的lock。

枕霜卧雪

下述是SR中针对该问题的回复，仅供参考：
该值没有一个固定的计算方法，10g和11.1里，我们知道一个比较统一的算法是'100*mode+namespace' ;
但是11.2 之后每个版本上的这个值都不太一样...
我们也确实基于经验总结出来的:
首先需要获得P3的16进制的值，如： 0x2dd16500010003
然后截取后边的8位，前边的四位，转换成10进制后代表namespace ，后边的四位转换成10进制后代表mode；
您的例子中
0001代表的是 name space= cursor
而0003代表的是mode=3 exclusive