[20240516]关于v$db_object_cache视图.txt

来源：这里教程网时间：2026-03-03 20:00:51 作者：

[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.