[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语句,效果不大，建议不要使用。

本文章url：https://www.kukuyl.com/new/30000162.html