[20240516]关于v$db_object_cache视图.txt
--//估计很少人使用这个视图,我记忆里知道使用因为查询full_hash_value.学习sql_id,hash_value计算.
--//实际上这个视图底层就是x$kglob,简单探究看看,主要x$kglob里面字段晦涩难懂.
--//对应看看,顺便建立一个脚本对照查询.
1.环境:
SCOTT@test01p> @ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
2.看看定义:
SYS@test> @ v v$db_object_cache
Show SQL text of views matching "%v$db_object_cache%"...
no rows selected
VIEW_NAME TEXT
------------------------------ ----------------------------------------------------------------------------------------------------
GV$DB_OBJECT_CACHE select inst_id,kglnaown,kglnaobj,kglnadlk,kglhdnsd,kglobtyd,
kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6,
kglhdldc,kglhdexc,kglhdlkc,kglobpc0,decode(kglhdkmk,0,'NO','YES'),kglhdclt, kglhdivc, kglnahsh,
decode(kglhdlmd, 0, 'NONE', 1, 'NULL',
2, 'SHARED', 3, 'EXCLUSIVE', 'UNKOWN'),
decode(kglhdpmd, 0, 'NONE', 1, 'NULL',
2, 'SHARED', 3, 'EXCLUSIVE', 'UNKOWN'),
decode(kglobsta, 1, 'VALID', 2, 'VALID_AUTH_ERROR',
3, 'VALID_COMPILE_ERROR', 4, 'VALID_UNAUTH', 5, 'INVALID_UNAUTH',
6, 'INVALID', 'UNKOWN'),
substr(to_char(kglnatim,'YYYY-MM-DD/HH24:MI:SS'),1,19),
substr(to_char(kglnaptm,'YYYY-MM-DD/HH24:MI:SS'),1,19), kgloblct, kglobpct, kglobprop, kglnahsv,
con_id, KGLNACON, kglhdadr, kglnaedn from x$kglob where kglnaobj is not null
V$DB_OBJECT_CACHE select OWNER , NAME , DB_LINK , NAMESPACE , TYPE , SHARABLE_MEM , LOADS , EXECUTIONS , LOCKS ,
PINS , KEPT , CHILD_LATCH , INVALIDATIONS, HASH_VALUE, LOCK_MODE, PIN_MODE, STATUS, TIMESTAMP,
PREVIOUS_TIMESTAMP, LOCKED_TOTAL, PINNED_TOTAL, PROPERTY, FULL_HASH_VALUE,
CON_ID, CON_NAME, ADDR, EDITION from GV$DB_OBJECT_CACHE where inst_id = USERENV('Instance')
--//建立一个对照表,方便学习查阅:
-----------------------------------------------------------------------------------------------------------------------------------------------------------
GV$DB_OBJECT_CACHE X$KGLOB
-----------------------------------------------------------------------------------------------------------------------------------------------------------
INST_ID inst_id
OWNER KGLNAOWN
NAME kglnaobj
DB_LINK kglnadlk
NAMESPACE kglhdnsd
TYPE kglobtyd
SHARABLE_MEM kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6
LOADS kglhdldc
EXECUTIONS kglhdexc
LOCKS kglhdlkc
PINS kglobpc0
KEPT decode(kglhdkmk,0,'NO','YES')
CHILD_LATCH kglhdclt
INVALIDATIONS kglhdivc
HASH_VALUE kglnahsh
LOCK_MODE decode(kglhdlmd,0, 'NONE',1, 'NULL', 2, 'SHARED',3, 'EXCLUSIVE','UNKOWN')
PIN_MODE decode(kglhdpmd,0, 'NONE',1, 'NULL', 2, 'SHARED',3, 'EXCLUSIVE','UNKOWN')
STATUS decode(kglobsta,1, 'VALID',2,'VALID_AUTH_ERROR',3,'VALID_COMPILE_ERROR',4,'VALID_UNAUTH',5,'INVALID_UNAUTH',6,'INVALID','UNKOWN')
TIMESTAMP substr(to_char(kglnatim,'YYYY-MM-DD/HH24:MI:SS'),1,19)
PREVIOUS_TIMESTAMP substr(to_char(kglnaptm,'YYYY-MM-DD/HH24:MI:SS'),1,19)
LOCKED_TOTAL kgloblct
PINNED_TOTAL kglobpct
PROPERTY kglobprop
FULL_HASH_VALUE kglnahsv
CON_ID con_id
CON_NAME KGLNACON
ADDR kglhdadr
EDITION kglnaedn
SQL_ID KGLOBT03 --//注:GV$DB_OBJECT_CACHE视图没有这个字段我加上的.
---------------------------------------------------------------------------------------------------------------------------------------------------------
3.建立脚本:
--//我感觉该视图定义缺少sql_id有点不合理,建立一个对照可以执行的脚本:
$ cat kblog.sql
select /*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1)) */
inst_id INST_ID
,KGLNAOWN OWNER
,kglnaobj NAME
,kglnadlk DB_LINK
,kglhdnsd NAMESPACE
,kglobtyd TYPE
,kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6 SHARABLE_MEM
,kglhdldc LOADS
,kglhdexc EXECUTIONS
,kglhdlkc LOCKS
,kglobpc0 PINS
,decode(kglhdkmk,0,'NO','YES') KEPT
,kglhdclt CHILD_LATCH
,kglhdivc INVALIDATIONS
,kglnahsh HASH_VALUE
,decode(kglhdlmd,0, 'NONE',1, 'NULL', 2, 'SHARED',3, 'EXCLUSIVE','UNKOWN') LOCK_MODE
,decode(kglhdpmd,0, 'NONE',1, 'NULL', 2, 'SHARED',3, 'EXCLUSIVE','UNKOWN') PIN_MODE
,decode(kglobsta,1, 'VALID',2,'VALID_AUTH_ERROR',3,'VALID_COMPILE_ERROR',4,'VALID_UNAUTH',5,'INVALID_UNAUTH',6,'INVALID','UNKOWN') STATUS
,substr(to_char(kglnatim,'YYYY-MM-DD/HH24:MI:SS'),1,19) TIMESTAMP
,substr(to_char(kglnaptm,'YYYY-MM-DD/HH24:MI:SS'),1,19) PREVIOUS_TIMESTAMP
,kgloblct LOCKED_TOTAL
,kglobpct PINNED_TOTAL
,kglobprop PROPERTY
,kglnahsv FULL_HASH_VALUE
,con_id CON_ID
,KGLNACON CON_NAME
,kglhdadr ADDR
,kglnaedn EDITION
,KGLOBT03 SQL_ID
from x$kglob
where kglnaobj is not null and (KGLOBT03 = lower('&1') or KGLNAHSH= &2);
-//简单测试看看:
select sysdate from dual;
select sysdate from dual;
select sysdate from dual;
SYS@test> @ hash
HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX SQL_EXEC_START SQL_EXEC_ID
----------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------
2343063137 7h35uxf5uhmm1 0 20065 1388734953 8ba84e61 2024-05-17 22:56:53 16777218
SYS@test> @ kglob 7h35uxf5uhmm1 0
SYS@test> @ prxx
==============================
INST_ID : 1
OWNER :
NAME : select sysdate from dual
DB_LINK :
NAMESPACE : SQL AREA
TYPE : CURSOR
SHARABLE_MEM : 12160
LOADS : 1
EXECUTIONS : 3
LOCKS : 1
PINS : 0
KEPT : NO
CHILD_LATCH : 0
INVALIDATIONS : 0
HASH_VALUE : 2343063137
LOCK_MODE : NULL
PIN_MODE : NONE
STATUS : VALID
TIMESTAMP : 2024-05-17/22:56:52
PREVIOUS_TIMESTAMP :
LOCKED_TOTAL : 3
PINNED_TOTAL : 4
PROPERTY :
FULL_HASH_VALUE : b3dbd4abf1156b09780cbaeb8ba84e61
CON_ID : 1
CON_NAME :
ADDR : 000007FF154B8C68
EDITION :
SQL_ID : 7h35uxf5uhmm1
==============================
INST_ID : 1
OWNER :
NAME : select sysdate from dual
DB_LINK :
NAMESPACE : SQL AREA
TYPE : CURSOR
SHARABLE_MEM : 4072
LOADS : 2
EXECUTIONS : 3
LOCKS : 1
PINS : 0
KEPT : NO
CHILD_LATCH : 20065
INVALIDATIONS : 0
HASH_VALUE : 2343063137
LOCK_MODE : NULL
PIN_MODE : NONE
STATUS : VALID
TIMESTAMP : 2024-05-17/22:56:52
PREVIOUS_TIMESTAMP :
LOCKED_TOTAL : 3
PINNED_TOTAL : 1
PROPERTY :
FULL_HASH_VALUE : b3dbd4abf1156b09780cbaeb8ba84e61
CON_ID : 1
CON_NAME :
ADDR : 000007FF007C34D0
EDITION :
SQL_ID : 7h35uxf5uhmm1
PL/SQL procedure successfully completed.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-3015895/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
