[20200212]使用DBMS_SHARED_POOL.MARKHOT與sql語句3.txt

[20200212]使用DBMS_SHARED_POOL.MARKHOT與sql語句3.txt

--//測試設定_kgl_hot_object_copies＝101,看看避免衝突的情況下,使用DBMS_SHARED_POOL.MARKHOT效果如何。

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

SYS@book> alter system set "_kgl_hot_object_copies"=101 scope=spfile ;
System altered.
--//必須重啟才生效，重啟資料庫略。

SYS@book> @ hide _kgl_hot_object_copies
NAME                   DESCRIPTION                         DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
---------------------- ----------------------------------- ------------- ------------- ------------ ----- ---------
_kgl_hot_object_copies Number of copies for the hot object FALSE         101           101          FALSE FALSE

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

2.測試1：
--//測試不使用DBMS_SHARED_POOL.MARKHOT的情況：
a='AAAVREAAEAAAACXAAA,AAAVREAAEAAAACXAAB,AAAVREAAEAAAACXAAC,AAAVREAAEAAAACXAAD,AAAVREAAEAAAACXAAE,AAAVREAAEAAAACXAAF,AAAVREAAEAAAACXAAG,AAAVREAAEAAAACXAAH,AAAVREAAEAAAACXAAI,AAAVREAAEAAAACXAAJ,AAAVREAAEAAAACXAAK,AAAVREAAEAAAACXAAL,AAAVREAAEAAAACXAAM,AAAVREAAEAAAACXAAN'

$ echo $a | tr  ',' '\n' | xargs -I{} -P 14 sqlplus -s -l scott/book @m2.txt 1e6 NOMARKHOTx {}

SCOTT@book> Select method,count(*),round(avg(TIME_ELA),0),sum(TIME_ELA) from job_times group by method order by 3 ;
METHOD                 COUNT(*) ROUND(AVG(TIME_ELA),0) SUM(TIME_ELA)
-------------------- ---------- ---------------------- -------------
NOMARKHOTx                   14                   2629         36811

3.測試2：
--//測試使用DBMS_SHARED_POOL.MARKHOT的情況：
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 ' and CHILD_LATCH<>0
 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              13071757             0

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 MARKHOTx {}

SYS@book> @ wait
P1RAW            P2RAW            P3RAW                    P1                P2         P3        SID    SERIAL#       SEQ# EVENT                  STATUS   STATE               WAIT_TIME_MICRO SECONDS_IN_WAIT WAIT_CLASS
---------------- ---------------- ---------------- ---------- ----------------- ---------- ---------- ---------- ---------- ---------------------- -------- ------------------- --------------- --------------- --------------------
0000000000002B84 00               000000000000003E      11140                 0         62         58         23         69 library cache: mutex X ACTIVE   WAITED SHORT TIME                 2               0 Concurrency
0000000000002B84 00               000000000000003E      11140                 0         62         72         13         69 library cache: mutex X ACTIVE   WAITED SHORT TIME                 2               2 Concurrency
0000000000002B84 0000003A00000000 000000000000003E      11140      249108103168         62         86          3         46 library cache: mutex X ACTIVE   WAITED SHORT TIME                 3               4 Concurrency
0000000000002B84 00               000000000000003E      11140                 0         62        101          3         70 library cache: mutex X ACTIVE   WAITED SHORT TIME                 3               1 Concurrency
0000000000002B84 00               000000000000003E      11140                 0         62        114          3         66 library cache: mutex X ACTIVE   WAITED SHORT TIME                 2               1 Concurrency
0000000000002B84 00               000000000000003E      11140                 0         62        128          3         45 library cache: mutex X ACTIVE   WAITED SHORT TIME                 3               1 Concurrency
0000000000002B84 0000009C00000000 000000000000003E      11140      670014898176         62        142          3         55 library cache: mutex X ACTIVE   WAITED KNOWN TIME             10997               4 Concurrency
0000000000002B84 00               000000000000003E      11140                 0         62        156          3         61 library cache: mutex X ACTIVE   WAITED SHORT TIME                 2               0 Concurrency
0000000000002B84 0000005600000000 000000000000003E      11140      369367187456         62        170          3         39 library cache: mutex X ACTIVE   WAITED SHORT TIME                 3               4 Concurrency
0000000000002B84 0000009C00000000 000000000000003E      11140      670014898176         62        184          3         41 library cache: mutex X ACTIVE   WAITED SHORT TIME                11               4 Concurrency
0000000000002B84 00               000000000000003E      11140                 0         62        198          3         59 library cache: mutex X ACTIVE   WAITED SHORT TIME                 2               0 Concurrency
0000000000002B84 00               000000000000003E      11140                 0         62        212         37         63 library cache: mutex X ACTIVE   WAITED SHORT TIME                 2               0 Concurrency
0000000000002B84 0000003A00000000 000000000000003E      11140      249108103168         62        226          7         53 library cache: mutex X ACTIVE   WAITED SHORT TIME                 2               1 Concurrency
0000000000002B84 00               000000000000003E      11140                 0         62        241          3         60 library cache: mutex X ACTIVE   WAITED SHORT TIME                 2               1 Concurrency
14 rows selected.
--//還是大量的library cache: mutex X等待事件。

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 ' and CHILD_LATCH<>0
 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          13071757             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1    285504892 7f0b59ec4b8535eec82fba911104757c SQL AREA         30076 HOTCOPY28     1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   4010836214 0cea2fadafa4dd35f7f8efcdef1080f6 SQL AREA         33014 HOTCOPY70     1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   1691525416 3d2f9eaeae30daafc5521e5c64d2a128 SQL AREA         41256 HOTCOPY84     1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   3448939648 33066bcd4389a8a8b78ccddacd92a480 SQL AREA         42112 HOTCOPY87     1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2118854785 c79e876e553b527d68e095217e4b2881 SQL AREA         75905 HOTCOPY59     1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   1626819103 fe3d83877f2c5663dd70044f60f74a1f SQL AREA         84511 HOTCOPY98     1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1    580344650 b698c5f01e065315c8c1042b22975b4a SQL AREA         88906 HOTCOPY11     1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2543673867 6dd887a7dad2ef66cb0141e3979d620b SQL AREA         90635 HOTCOPY56     1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2236576569 7ace1ec20937d7b7155be95f854f7339 SQL AREA         95033 HOTCOPY73     1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2648560675 127d6fcd213a6dd9c2d5d8139dddd423 SQL AREA        119843 HOTCOPY25     1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2582763541 c88682c52f8f2765a84d377399f1d815 SQL AREA        120853 HOTCOPY1      1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2302797450 ec2f55c6e5d2cd8da2e94ef78941e68a SQL AREA        124554 HOTCOPY42     1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2074737225 a3b70cec850d0f06245030157ba9fa49 SQL AREA        129609 HOTCOPY40     1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2496819678 14d02282ba345f71d7396b5694d271de SQL AREA         29150 HOTCOPY14     1000000             0
15 rows selected.
--//這次全部分散開了。生成14個HOTCOPYXX。

SCOTT@book> Select method,count(*),round(avg(TIME_ELA),0),sum(TIME_ELA) from job_times group by method order by 3 ;
METHOD                 COUNT(*) ROUND(AVG(TIME_ELA),0) SUM(TIME_ELA)
-------------------- ---------- ---------------------- -------------
NOMARKHOTx                   14                   2629         36811
MARKHOTx                     14                   3130         43825

--//效果一樣，再次驗證使用DBMS_SHARED_POOL.MARKHOT標識熱sql語句,效果不大，建議不要使用。

SCOTT@book> select mod(sid,101)+1 ,count(*) from  job_times where method='MARKHOTx' group by mod(sid,101) order by 1;
MOD(SID,101)+1   COUNT(*)
-------------- ----------
             1          1
            11          1
            14          1
            25          1
            28          1
            40          1
            42          1
            56          1
            59          1
            70          1
            73          1
            84          1
            87          1
            98          1
14 rows selected.

SCOTT@book> SELECT substr(property,8,2) FROM v$db_object_cache WHERE name = 'SELECT ENAME FROM EMP WHERE ROWID =:B1 ' and CHILD_LATCH<>0 order by 1 ;
SUBS
----
1
11
14
25
28
40
42
56
59
70
73
84
87
98
15 rows selected.

--//可以發現結果一致，再次驗證了設定隱含引數_kgl_hot_object_copies，sql_id語句計算sql_text變為原來sql文字的基礎上
--// 加上 . mod(sid,_kgl_hot_object_copies)+1數字的字串。

$ echo -e -n  'SELECT ENAME FROM EMP WHERE ROWID =:B1 \0.14' | md5sum | sed 's/  -//' | xxd -r -p | od -t x4 | sed  -n  -e 's/^0000000 //' -e 's/ //gp'
14d02282ba345f71d7396b5694d271de
--//FULL_HASH_VALUE與前面的輸出完全能對上。

4.收尾:
SYS@book> alter system reset "_kgl_hot_object_copies" ;
System altered.

--//重啟還原。