[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
--//看不出來。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2675119/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- (轉)Oracle動態效能檢視學習之V$DB_OBJECT_CACHEOracleObject
- v$db_object_cacheObject
- sql server如何檢視欄位註釋?SQLServer
- [20120307]檢視v$session檢視的定義.txtSession
- [20171102]檢視v$session中process欄位含義Session
- sql2005 獲取表欄位資訊和檢視欄位資訊SQL
- 【ORACLE】物化檢視相關後設資料檢視欄位說明Oracle
- 關於v$db_object_cacheObject
- oracle檢視該使用者的所有表名字、表註釋、欄位名、欄位註釋、是否為空、欄位型別Oracle型別
- v$sql檢視和v$sqlarea檢視的構建SQL
- Hibernate對檢視對映,當檢視中有空欄位的時候如何解決?
- 【GP】透過資料字典檢視某個表的欄位
- sql server 修改欄位名,檢視指定表是否存在SQLServer
- [20210528]V$INDEXED_FIXED_COLUMN檢視.txtIndex
- EBS:物料搬運單檢視人限制(建立人欄位)
- 檢視oracle 欄位的資料佔用的位元組數函式Oracle函式
- v$process和v$session中欄位解釋Session
- [20210418]查詢v$檢視問題.txt
- Oracle常用的V$檢視Oracle
- v檢視的授權
- [20211214]檢視檢視V$ACTIVE_SESSION_HISTORY遇到奇怪問題.txtSession
- v$session 檢視Session
- MySQL如何檢視新增修改表以及欄位註釋資訊MySql
- Oracle系統檢視中address欄位長度與db位數的關係Oracle
- [20211019]V$DETACHED_SESSION檢視.txtSession
- Dynamics CRM 通過配置來設定查詢欄位的預設檢視
- 強制轉換檢視某個欄位為某個型別的sql型別SQL
- MongoDB檢視執行計劃方法及相關欄位說明MongoDB
- 欄位型別檢測指令碼型別指令碼
- v$session中command欄位的含義解析Session
- Oracle的V$檢視和DBA_檢視的參考提示Oracle
- 如何檢視型別為LRAW的SAP ABAP資料庫表欄位的內容型別資料庫
- SAP MM 物料主資料採購檢視中的欄位'Var. OUn'的作用?
- SAP QM 物料主資料QM檢視裡欄位MARC-INSMK的更新
- 檢視oracle資料庫中,哪些表的欄位是null值比較多Oracle資料庫Null
- (轉)Sql Server 快速檢視錶結構(表描述及欄位說明)SQLServer
- 幾個檢視 v$mystat v$systata v$sessionSession
- v$sqlarea,v$sql,v$sqltext三個檢視的區別SQL