[20200211]视图v$db_object_cache的CHILD_LATCH字段.txt

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

[20200211]视图v$db_object_cache的CHILD_LATCH字段.txt --//上午测试使用DBMS_SHARED_POOL.MARKHOT标识热sql语句，我发现查询v$db_object_cache视图的CHILD_LATCH字段，视乎与 --//FULL_HASH_VALUE存在某种关联,测试看看。 1.环境： SCOTT@book> @ ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production create table job_times (sid number, sessionid number,time_ela number,method varchar2(20)); --//建立测试脚本m2.txt: $ cat m2.txt set verify off --//host sleep $(echo &&3/50 | bc -l ) insert into job_times values ( sys_context ('userenv', 'sid') ,sys_context ('userenv', 'sessionid'),dbms_utility.get_time ,'&&2') ; commit ; declare v_id number; v_d date; m_rowid varchar2(20); m_data varchar2(32); begin m_rowid := '&3'; for i in 1 .. &&1 loop select ename into m_data from emp where rowid =m_rowid ; end loop; end ; / update job_times set time_ela = dbms_utility.get_time - time_ela where sid=sys_context ('userenv', 'sid') and sessionid=sys_context ('userenv', 'sessionid') and method='&&2'; commit; quit SCOTT@book> select listagg(rowid,',') WITHIN GROUP (order by rowid ) c100 from emp ; C100 ---------------------------------------------------------------------------------------------------- AAAVREAAEAAAACXAAA,AAAVREAAEAAAACXAAB,AAAVREAAEAAAACXAAC,AAAVREAAEAAAACXAAD,AAAVREAAEAAAACXAAE,AAAVR EAAEAAAACXAAF,AAAVREAAEAAAACXAAG,AAAVREAAEAAAACXAAH,AAAVREAAEAAAACXAAI,AAAVREAAEAAAACXAAJ,AAAVREAAEA AAACXAAK,AAAVREAAEAAAACXAAL,AAAVREAAEAAAACXAAM,AAAVREAAEAAAACXAAN $ a='AAAVREAAEAAAACXAAA,AAAVREAAEAAAACXAAB,AAAVREAAEAAAACXAAC,AAAVREAAEAAAACXAAD,AAAVREAAEAAAACXAAE,AAAVREAAEAAAACXAAF,AAAVREAAEAAAACXAAG,AAAVREAAEAAAACXAAH,AAAVREAAEAAAACXAAI,AAAVREAAEAAAACXAAJ,AAAVREAAEAAAACXAAK,AAAVREAAEAAAACXAAL,AAAVREAAEAAAACXAAM,AAAVREAAEAAAACXAAN' SYS@book> exec dbms_shared_pool.markhot( hash=>'6ddb0702c4c177cb27ee292f05202b84', namespace=>0, global=>true); PL/SQL procedure successfully completed. $ echo $a | tr ',' '\n' | xargs -I{} -P 14 sqlplus -s -l scott/book @m2.txt 1e6 MARKHOT {} 2.测试结果： SELECT owner ,name ,hash_value ,full_hash_value ,namespace ,child_latch ,property hot_flag ,executions ,invalidations FROM v$db_object_cache WHERE name = 'SELECT ENAME FROM EMP WHERE ROWID =:B1 ' order by executions desc ; OWNER NAME HASH_VALUE FULL_HASH_VALUE NAMESPACE CHILD_LATCH HOT_FLAG EXECUTIONS INVALIDATIONS ------ ---------------------------------------- ---------- -------------------------------- ---------- ----------- ---------- ---------- ------------- SELECT ENAME FROM EMP WHERE ROWID =:B1 85994372 6ddb0702c4c177cb27ee292f05202b84 SQL AREA 11140 HOT 39941116 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 85994372 6ddb0702c4c177cb27ee292f05202b84 SQL AREA 0 HOT 39532761 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2727091266 c1c613120d5bb52788b12853a28c1c42 SQL AREA 7234 HOTCOPY7 2971797 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2727091266 c1c613120d5bb52788b12853a28c1c42 SQL AREA 0 HOTCOPY7 2964992 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 580344650 b698c5f01e065315c8c1042b22975b4a SQL AREA 88906 HOTCOPY11 1997489 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 580344650 b698c5f01e065315c8c1042b22975b4a SQL AREA 0 HOTCOPY11 1997360 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2582763541 c88682c52f8f2765a84d377399f1d815 SQL AREA 120853 HOTCOPY1 1996477 0 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ SELECT ENAME FROM EMP WHERE ROWID =:B1 2582763541 c88682c52f8f2765a84d377399f1d815 SQL AREA 0 HOTCOPY1 1995152 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 3034301772 c94e7bc94f070bb139e74338b4dbc54c SQL AREA 116044 HOTCOPY3 1975972 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 3034301772 c94e7bc94f070bb139e74338b4dbc54c SQL AREA 0 HOTCOPY3 1886386 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2346932844 aaec082428ac7dc31bed7f958be35a6c SQL AREA 0 HOTCOPY6 1000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2346932844 aaec082428ac7dc31bed7f958be35a6c SQL AREA 88684 HOTCOPY6 1000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 3708080158 2b2d6a42e3f5dc2950f01eb8dd04d01e SQL AREA 0 HOTCOPY8 1000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 3708080158 2b2d6a42e3f5dc2950f01eb8dd04d01e SQL AREA 53278 HOTCOPY8 1000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 4251304214 88e9b0a03994a83e3b1dd47ffd65c116 SQL AREA 0 HOTCOPY2 1000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 4251304214 88e9b0a03994a83e3b1dd47ffd65c116 SQL AREA 114966 HOTCOPY2 1000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 1362767092 d8667d27feee3a14db493b1b513a2cf4 SQL AREA 0 HOTCOPY9 1000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2683232668 b8b4a75c26ca8ed13d9653df9feee19c SQL AREA 57756 HOTCOPY5 1000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2683232668 b8b4a75c26ca8ed13d9653df9feee19c SQL AREA 0 HOTCOPY5 1000000 0 SELECT ENAME FROM EMP WHERE ROWID =:B1 1362767092 d8667d27feee3a14db493b1b513a2cf4 SQL AREA 11508 HOTCOPY9 1000000 0 20 rows selected. --//注不看CHILD_LATCH=0的情况，我估计那个对应父游标。 --//取FULL_HASH_VALUE的后4个17进制字符，0x2b84 = 11140 --//拿下划线那行分析，FULL_HASH_VALUE=c88682c52f8f2765a84d377399f1d815,后4位 0x d815 = 55317,不对。 --//前面补1个1再计算 0x1d815 = 120853，正好对上，也就是CHILD_LATCH实际上取FULL_HASH_VALUE的二进制位17位计算的10进制值就 --//是对应的CHILD_LATCH。 3.探测视图看看。 /* Formatted on 2020/2/11 10:05:15 (QP5 v5.269.14213.34769) */ 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 ~~~~~~=> 对应CHILD_LATCH ,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) ,kglobt23 ,kglobt24 ,kglobprop ,kglnahsv FROM x$kglob WHERE kglnaobj IS NOT NULL --//看不出来。