[20200211]檢視v$db_object_cache的CHILD_LATCH欄位.txt

[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

--//看不出來。