[20200211]使用DBMS_SHARED_POOL.MARKHOT與sql語句2.txt

[20200211]使用DBMS_SHARED_POOL.MARKHOT與sql語句2.txt

--//上午測試使用DBMS_SHARED_POOL.MARKHOT標識熱sql語句，發現實際上更慢。
--//是否是我測試連線會話太少，加大測試量看看。
--//連結：http://blog.itpub.net/267265/viewspace-2675118/=>[20200126]使用DBMS_SHARED_POOL.MARKHOT與sql語句.txt

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

2.測試1：
--//先測試使用DBMS_SHARED_POOL.MARKHOT的情況：
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 $a $a $a $a $a| tr  ', ' '\n\n' | xargs -I{} -P 84 sqlplus -s -l scott/book @m2.txt 4e5 MARKHOT {}
--//注意tr ', '逗號後面有1個空格。

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   1054289511 767295bfca293b63b6f7cb623ed72e67 SQL AREA         77415 HOTCOPY4      7889752             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1    879431168 935723b8e76ce7f46c9524b7346b0e00 SQL AREA         69120 HOTCOPY12     6197136             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1    580344650 b698c5f01e065315c8c1042b22975b4a SQL AREA         88906 HOTCOPY11     6102859             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   3708080158 2b2d6a42e3f5dc2950f01eb8dd04d01e SQL AREA         53278 HOTCOPY8      5842227             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   1362767092 d8667d27feee3a14db493b1b513a2cf4 SQL AREA         11508 HOTCOPY9      5838937             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   4251304214 88e9b0a03994a83e3b1dd47ffd65c116 SQL AREA        114966 HOTCOPY2      5829690             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   3034301772 c94e7bc94f070bb139e74338b4dbc54c SQL AREA        116044 HOTCOPY3      5741466             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2727091266 c1c613120d5bb52788b12853a28c1c42 SQL AREA          7234 HOTCOPY7      5603564             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2346932844 aaec082428ac7dc31bed7f958be35a6c SQL AREA         88684 HOTCOPY6      5379941             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2683232668 b8b4a75c26ca8ed13d9653df9feee19c SQL AREA         57756 HOTCOPY5      5366078             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   1051678227 0cbac329d8cbd103bdf0b37e3eaf5613 SQL AREA         87571 HOTCOPY10     5245620             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2582763541 c88682c52f8f2765a84d377399f1d815 SQL AREA        120853 HOTCOPY1      4561987             0
12 rows selected.
--//僅僅生成12個sql_id.注意沒有HOT_FLAG=HOT,可以被刷出共享池了。
--//奇怪欄位HOT_FLAG顯示的HOTCOPYXX,後面的數字最大12.或者應該是 mod(sid,cpu_count/2)+1,看來前面的測試有誤。

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)
-------------------- ---------- ---------------------- -------------
MARKHOT                      84                   5860        492215

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

SYS@book> exec dbms_shared_pool.unmarkhot( hash=>'6ddb0702c4c177cb27ee292f05202b84', namespace=>0, global=>true);
PL/SQL procedure successfully completed.

$ echo $a $a $a $a $a $a| tr  ', ' '\n\n' | xargs -I{} -P 84 sqlplus -s -l scott/book @m2.txt 4e5 NOMARKHOT {}

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)
-------------------- ---------- ---------------------- -------------
NOMARKHOT                    84                   5375        451458
MARKHOT                      84                   5860        492215

--//還是不使用DBMS_SHARED_POOL.MARKHOT效果更好。
--//看來要使用DBMS_SHARED_POOL.MARKHOT必須有更多的CPU才可行。

4.繼續我發現有1個隱含引數可以控制hot數量。
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 TRUE          0             0            FALSE FALSE

SYS@book> alter system set "_kgl_hot_object_copies"=23 scope=spfile ;
System altered.

--//必須重啟才生效。
SYS@book> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@book> startup
ORACLE instance started.
Total System Global Area  643084288 bytes
Fixed Size                  2255872 bytes
Variable Size             205521920 bytes
Database Buffers          427819008 bytes
Redo Buffers                7487488 bytes
Database mounted.
Database opened.

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         23            23           FALSE FALSE

SYS@book> exec dbms_shared_pool.markhot( hash=>'6ddb0702c4c177cb27ee292f05202b84', namespace=>0, global=>true);
PL/SQL procedure successfully completed.
--//先要執行'SELECT ENAME FROM EMP WHERE ROWID =:B1 '語句獲得hash值，不然報錯，找不到物件。

$ echo $a $a $a $a $a $a| tr  ', ' '\n\n' | xargs -I{} -P 84 sqlplus -s -l scott/book @m2.txt 4e5 MODIFY {}

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)
-------------------- ---------- ---------------------- -------------
NOMARKHOT                    84                   5375        451458
MARKHOT                      84                   5860        492215
MODIFY                       84                   5866        492761
--//問題照舊。

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   2683232668 b8b4a75c26ca8ed13d9653df9feee19c SQL AREA         57756 HOTCOPY5      2376593             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   1054289511 767295bfca293b63b6f7cb623ed72e67 SQL AREA         77415 HOTCOPY4      1989065             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   3233207171 eb871620fcbe3434c79e2665c0b6d383 SQL AREA         54147 HOTCOPY16     1985272             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2582763541 c88682c52f8f2765a84d377399f1d815 SQL AREA        120853 HOTCOPY1      1981125             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2346932844 aaec082428ac7dc31bed7f958be35a6c SQL AREA         88684 HOTCOPY6      1976634             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   3492648563 8d651d1b5e6152636190413bd02d9673 SQL AREA        104051 HOTCOPY17     1972980             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2159114629 bd2a9fd75309aaef9ce808dd80b17985 SQL AREA         96645 HOTCOPY15     1596766             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   3034301772 c94e7bc94f070bb139e74338b4dbc54c SQL AREA        116044 HOTCOPY3      1595704             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1    293343694 8b69d2d58627e62a20a5434b117c11ce SQL AREA          4558 HOTCOPY19     1594337             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2727091266 c1c613120d5bb52788b12853a28c1c42 SQL AREA          7234 HOTCOPY7      1593345             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2373487536 4b725dd36b3084a43d0fc69a8d788bb0 SQL AREA         35760 HOTCOPY21     1593193             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2496819678 14d02282ba345f71d7396b5694d271de SQL AREA         29150 HOTCOPY14     1593099             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   1724855008 95db377359aad060a01cca1266cf32e0 SQL AREA         78560 HOTCOPY18     1591348             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   4206576078 8381913ebc5dbe17bbe2ddbafabb41ce SQL AREA         82382 HOTCOPY20     1583056             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2264846016 3d03c18a8227f757cf0e6a5886fecec0 SQL AREA         52928 HOTCOPY23     1198483             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   1522006932 cc360b48be5bd0ce4e8dd54a5ab7fb94 SQL AREA        129940 HOTCOPY22     1198435             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   1051678227 0cbac329d8cbd103bdf0b37e3eaf5613 SQL AREA         87571 HOTCOPY10     1197761             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   3650006106 3ad214cdf323271020dd88eed98eac5a SQL AREA         44122 HOTCOPY13     1196945             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   4251304214 88e9b0a03994a83e3b1dd47ffd65c116 SQL AREA        114966 HOTCOPY2       799795             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   1362767092 d8667d27feee3a14db493b1b513a2cf4 SQL AREA         11508 HOTCOPY9       799705             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1    580344650 b698c5f01e065315c8c1042b22975b4a SQL AREA         88906 HOTCOPY11      799363             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1    879431168 935723b8e76ce7f46c9524b7346b0e00 SQL AREA         69120 HOTCOPY12      799176             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   3708080158 2b2d6a42e3f5dc2950f01eb8dd04d01e SQL AREA         53278 HOTCOPY8       400000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1     85994372 6ddb0702c4c177cb27ee292f05202b84 SQL AREA         11140 HOT                84             0
24 rows selected.

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

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)
-------------------- ---------- ---------------------- -------------
MODIFY14                     14                   2971         41591
NOMARKHOT                    84                   5375        451458
MARKHOT                      84                   5860        492215
MODIFY                       84                   5866        492761

--//我前面的測試結果，可以看出基本沒效果。
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)
-------------------- ---------- ---------------------- -------------
NOMARKHOT                    14                   2600         36404
MARKHOT                      14                   3099         43391


SCOTT@book> select mod(sid,23) ,count(*) from  job_times where method='MODIFY14' group by mod(sid,23);
MOD(SID,23)   COUNT(*)
----------- ----------
          1          1
          6          1
         13          2
          5          2
         18          1
          0          2
         16          1
         19          1
         15          2
         10          1
10 rows selected.
--//還是存在衝突。

4.總結:
--//透過測試可以發現使用DBMS_SHARED_POOL.MARKHOT標識熱sql語句,效果不大，建議不要使用。