[20180914]oracle 12c 表 full_hash_value如何計算.txt

[20180914]oracle 12c 表 full_hash_value如何計算.txt

--//昨天在12c下看表full_hash_value與11g的full_hash_value不同,不過12c使用pdb,猜測跟PDB有關.
--//通過測試說明問題.

1.環境:
SCOTT@book> @ &r/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

SCOTT@book> select owner,name,namespace,type,hash_value,full_hash_value from V$DB_OBJECT_CACHE where owner='SCOTT' and name='EMP';
OWNER  NAME NAMESPACE       TYPE  HASH_VALUE FULL_HASH_VALUE
------ ---- --------------- ----- ---------- --------------------------------
SCOTT  EMP  TABLE/PROCEDURE TABLE 3800164305 684ea11e3eab602b778e1dd1e281e7d1

--//以上11g的結果.
$ echo -e -n "EMP.SCOTT\01\0\0\0" | md5sum |sed 's/  -//' | xxd -r -p | od -t x4 | sed  -n  -e 's/^0000000 //' -e 's/ //gp'
684ea11e3eab602b778e1dd1e281e7d1

2.而在12c下:

SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

SCOTT@test01p> select owner,name,namespace,type,hash_value,full_hash_value,con_id,con_name from V$DB_OBJECT_CACHE where owner='SCOTT' and name='EMP';
OWNER                NAME                 NAMESPACE            TYPE                 HASH_VALUE FULL_HASH_VALUE                      CON_ID CON_NAME
-------------------- -------------------- -------------------- -------------------- ---------- -------------------------------- ---------- --------------------
SCOTT                EMP                  TABLE/PROCEDURE      TABLE                1676251406 5675b61ea54d0cd0370c43ab63e9910e          3 TEST01P

--//對比前面可以發現FULL_HASH_VALUE不一樣,也很容易猜測12c 的FULL_HASH_VALUE計算加入CON_NAME的內容.
D:\tools\rlwrap>D:\tools\linux\usr\local\wbin\echo -e -n "EMP.SCOTT.TEST01P\01\0\0\0" | md5sum |sed "s/  -//" | D:\tools\Vim\vim80\xxd -r -p | od -t x4 | sed  -n  -e "s/^0000000 //" -e "s/ //gp"
5675b61ea54d0cd0370c43ab63e9910e

--//OK,能對上.
--//註:windows要安裝unxutil包.echo不能使用windows下的echo.必須使用ubxutil包的echo(該命令支持-n -e參數)
--//sed 命令格式要使用雙引號.
--//我個人還使用vim自帶的xxd.

--//可以發現12c計算表的full_hash_value是 計算table_name.owner_name.con_name\01\0\0\0的md5sum值.

3.從以上測試可以聯想到的問題就是後面都是補"\01\0\0\0",為什麼呢?
--//很容易聯想到namespace

SYS@test> select distinct kglhdnsp,kglhdnsd,kglobtyd from x$kglob order by 1;
KGLHDNSP KGLHDNSD                       KGLOBTYD
-------- ------------------------------ -------------------
       0 SQL AREA                       CURSOR
       1 TABLE/PROCEDURE                CURSOR
       1 TABLE/PROCEDURE                FUNCTION
       1 TABLE/PROCEDURE                LIBRARY
       1 TABLE/PROCEDURE                OPERATOR
       1 TABLE/PROCEDURE                PACKAGE
       1 TABLE/PROCEDURE                PROCEDURE
       1 TABLE/PROCEDURE                SCHEDULER CLASS
       1 TABLE/PROCEDURE                SCHEDULER JOB
       1 TABLE/PROCEDURE                SCHEDULER PROGRAM
       1 TABLE/PROCEDURE                SCHEDULER SCHEDULE
       1 TABLE/PROCEDURE                SCHEDULER WINDOW
       1 TABLE/PROCEDURE                SEQUENCE
       1 TABLE/PROCEDURE                SYNONYM
       1 TABLE/PROCEDURE                TABLE
       1 TABLE/PROCEDURE                TYPE
       1 TABLE/PROCEDURE                VIEW
       2 BODY                           CURSOR
       2 BODY                           PACKAGE BODY
       3 TRIGGER                        TRIGGER
       4 INDEX                          INDEX
       5 CLUSTER                        CLUSTER
       5 CLUSTER                        CURSOR
      10 QUEUE                          QUEUE
      18 PUB SUB INTERNAL INFORMATION   PUB SUB INTERNAL INFORMATION
      23 RULESET                        RULESET
      24 RESOURCE MANAGER               RESOURCE MANAGER CONSUMER GROUP
      24 RESOURCE MANAGER               RESOURCE MANAGER PLAN
      28 SUBSCRIPTION                   SUBSCRIPTION
      38 RULE EVALUATION CONTEXT        RULE EVALUATION CONTEXT
      45 MULTI-VERSION OBJECT FOR TABLE CURSOR
      45 MULTI-VERSION OBJECT FOR TABLE MULTI-VERSIONED OBJECT
      48 MULTI-VERSION OBJECT FOR INDEX CURSOR
      48 MULTI-VERSION OBJECT FOR INDEX MULTI-VERSIONED OBJECT
      51 SCHEDULER GLOBAL ATTRIBUTE     CURSOR
      51 SCHEDULER GLOBAL ATTRIBUTE     SCHEDULER GLOBAL ATTRIBUTE
      52 RESOURCE MANAGER CDB           RESOURCE MANAGER CDB PLAN
      64 EDITION                        EDITION
      69 DBLINK                         CURSOR
      72 OBJECT ID                      OBJECT ID
      73 SCHEMA                         CURSOR
      73 SCHEMA                         NONE
      74 DBINSTANCE                     CURSOR
      75 SQL AREA STATS                 CURSOR STATS
      79 ACCOUNT_STATUS                 NONE
      82 SQL AREA BUILD                 CURSOR
      88 PDB                            CURSOR
      88 PDB                            PDB
      93 AUDIT POLICY                   AUDIT POLICY
     103 OPTIMIZER FINDING              Optimizer Finding
     104 OPTIMIZER DIRECTIVE OWNER      CURSOR
     104 OPTIMIZER DIRECTIVE OWNER      Optimizer Directive Owner
     113 GTT SESSION PRIVATE STATS      CURSOR
     125 PDBOPER                        CURSOR
54 rows selected.

--//比如你就不能建立emp的sequence.
SCOTT@test01p> create sequence emp cache 100;
create sequence emp cache 100
                *
ERROR at line 1:
ORA-00955: name is already used by an existing object

4.SQL語句的full_hash_value(sql_id)計算還是和以前一樣計算 sql文本\0(不包括分號) md5sum值.
--//這樣帶來一個問題,就是如果不同pdb下的語句如果一樣,由於文本內容一樣,這樣計算的sql_id一樣,導致出現大量子游標.