[20201117]使用DBMS_SHARED_POOL.MARKHOT與sql語句6.txt

lfree發表於2020-11-17

[20201117]使用DBMS_SHARED_POOL.MARKHOT與sql語句6.txt

--//前幾天我看了連結:https://blog.pythian.com/reducing-contention-on-hot-cursor-objects-cursor-pin-s/
--//裡面提到使用DBMS_SHARED_POOL.MARKHOT標識sql語句,減少Cursor: Pin S的情況,不過對方不同的地方是在函式中使用。
--//我以前的測試不在函式里面反而更慢,不建議使用,出現大量的"library cache: mutex X".
--//正好裡面有例子,我直接拿來測試看看。

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


2.建立測試環境:

create table job_times (sid number, sessionid number,time_ela number,method varchar2(20));
create table code_table (code_name char(1), low_value number, high_value number);

create unique index pk_code_table on scott.code_table(code_name);
alter table code_table add constraint pk_code_table  primary key (code_name);
--//作者的測試不建立主鍵索引,我的測試建立它。

declare
  letters char(26) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
  v_num number := 1;
begin
  for i in 1..26 LOOP
    insert into code_table values (substr(letters,i,1), i*v_num, i*(v_num+1000));
    v_num := v_num + 1001;
  end loop;
  commit;
end;
/

create or replace function fx_num (v_name varchar) return number is
   v_low number;
   v_high number;
begin
   select low_value, high_value into v_low, v_high from code_table where code_name=v_name;
    return( v_high-v_low);
--//return(DBMS_RANDOM.value(low => v_low, high => v_high));
end;
/
--//注:我發現作者寫的例子不是很好,呼叫DBMS_RANDOM函式。導致函式本身執行時間有點"長"。

--//建立測試指令碼m3.txt:
$ cat m3.txt
set verify off
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 /*+ &3 */ fx_num(substr(to_char(sysdate,'MON'),1,1)) into v_id from  dual;
    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;

$ cat cc.txt
SELECT owner
      ,name
      ,hash_value
      ,full_hash_value
      ,namespace
      ,child_latch
      ,property hot_flag
      ,executions
      ,invalidations
  FROM v$db_object_cache
 WHERE name = 'SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1 '
 order by executions desc ;
--//注意/:B1後面有1個空格。

$ cat cd.txt
column NAMESPACE format a20
column HOT_FLAG  format a20
column name format a66
SELECT owner
      ,name
      ,hash_value
      ,full_hash_value
      ,namespace
      ,child_latch
      ,property hot_flag
      ,executions
      ,invalidations
  FROM v$db_object_cache
 WHERE  name='FX_NUM'
 order by executions desc ;

 
3.測試:
--//執行:
SYS@book> @ tpt/snapper ash 65 1  "select inst_id,sid from gv$session where username='SCOTT' and program like 'sqlplus%' and inst_id=1"
Sampling SID select inst_id,sid from gv$session where username='SCOTT' and program like 'sqlplus%' and inst_id=1 with interval 65 seconds, taking 1 snapshots...
-- Session Snapper v4.11 BETA - by Tanel Poder ( http://blog.tanelpoder.com ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)
----------------------------------------------------------------------------------------------------
Active% | INST | SQL_ID          | SQL_CHILD | EVENT                               | WAIT_CLASS
----------------------------------------------------------------------------------------------------
   993% |    1 |                 |           | library cache: mutex X              | Concurrency
   517% |    1 | 7tr4jwnamtmsr   | 0         | ON CPU                              | ON CPU
   255% |    1 |                 |           | ON CPU                              | ON CPU
    42% |    1 | ctyugp3su3k91   | 0         | library cache: mutex X              | Concurrency
    42% |    1 | 1zvmjk9k3sp2m   | 0         | library cache: mutex X              | Concurrency
    42% |    1 | 158y7v912v8fb   | 0         | library cache: mutex X              | Concurrency
    41% |    1 | 3mbun480b7ccy   | 0         | library cache: mutex X              | Concurrency
    40% |    1 | 4r4k676qa6v2d   | 0         | library cache: mutex X              | Concurrency
    39% |    1 | 3k7xkc2t9fwf6   | 0         | library cache: mutex X              | Concurrency
    39% |    1 | 8t7czzgnugs5x   | 0         | library cache: mutex X              | Concurrency
--  End of ASH snap 1, end=2020-11-17 15:57:47, seconds=65, samples_taken=99
PL/SQL procedure successfully completed.

--//馬上開啟另外會話執行:
$ zzdate ;seq 50 | xargs -I{} -P 50 sqlplus -s -l scott/book @m3.txt 2e5 unmarkhot_p=50 {} >/dev/null ;zzdate
trunc(sysdate)+15/24+56/1440+40/86400 == 2020/11/17 15:56:40
trunc(sysdate)+15/24+57/1440+42/86400 == 2020/11/17 15:57:42

SYS@book> @ tpt/ash/ash_wait_chains username||':'||program2||event2 module='SQL*Plus' trunc(sysdate)+15/24+56/1440+40/86400 trunc(sysdate)+15/24+57/1440+42/86400
-- Display ASH Wait Chain Signatures script v0.2 BETA by Tanel Poder ( http://blog.tanelpoder.com )
%This     SECONDS        AAS WAIT_CHAIN
------ ---------- ---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  41%        1249       20.1 -> SCOTT:(sqlplus) ON CPU
  32%         984       15.9 -> SCOTT:(sqlplus) library cache: mutex X  -> SCOTT:(sqlplus) ON CPU
  22%         675       10.9 -> SCOTT:(sqlplus) library cache: mutex X
   3%          87        1.4 -> SCOTT:(sqlplus) library cache: mutex X  -> SCOTT:(sqlplus) library cache: mutex X  -> SCOTT:(sqlplus) ON CPU
   3%          80        1.3 -> SCOTT:(sqlplus) library cache: mutex X  -> SCOTT:(sqlplus) library cache: mutex X
   0%           2          0 -> SCOTT:(sqlplus) library cache: mutex X  -> SCOTT:(sqlplus) library cache: mutex X  -> SCOTT:(sqlplus) library cache: mutex X  -> SCOTT:(sqlplus) ON CPU
   0%           1          0 -> SCOTT:(sqlplus) library cache: mutex X  -> SCOTT:(sqlplus) library cache: mutex X  -> SCOTT:(sqlplus) library cache: mutex X
7 rows selected.
--//從等待事件看出現大量的library cache: mutex X。實際上在做到這裡時就懷疑作者的測試,因為sql語句出現等待集中在cursor: pin S。
--//而測試出現library cache: mutex X,說明主要還是集中在包或者函式上。我估計標識sql語句後效果不會太好。

4.標識熱物件SQL語句:
SYS@book> @ cc.txt
OWNER  NAME                                                               HASH_VALUE FULL_HASH_VALUE                  NAMESPACE            CHILD_LATCH HOT_FLAG             EXECUTIONS INVALIDATIONS
------ ------------------------------------------------------------------ ---------- -------------------------------- -------------------- ----------- -------------------- ---------- -------------
       SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1    356306711 eb4cdceda1c495cd7cdc91e5153ccf17 SQL AREA                   53015                        37338848             0
       SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1    356306711 eb4cdceda1c495cd7cdc91e5153ccf17 SQL AREA                       0                        36943000             0
--//CHILD_LATCH是FULL_HASH_VALUE的後17位 0xcf17 = 53015。

SYS@book> exec dbms_shared_pool.markhot(hash => 'eb4cdceda1c495cd7cdc91e5153ccf17', namespace => 0, global => true);
PL/SQL procedure successfully completed.
       
5.繼續測試:
SYS@book> @ tpt/snapper ash 65 1  "select inst_id,sid from gv$session where username='SCOTT' and program like 'sqlplus%' and inst_id=1"
Sampling SID select inst_id,sid from gv$session where username='SCOTT' and program like 'sqlplus%' and inst_id=1 with interval 65 seconds, taking 1 snapshots...
-- Session Snapper v4.11 BETA - by Tanel Poder ( http://blog.tanelpoder.com ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)
----------------------------------------------------------------------------------------------------
Active% | INST | SQL_ID          | SQL_CHILD | EVENT                               | WAIT_CLASS
----------------------------------------------------------------------------------------------------
  1130% |    1 |                 |           | library cache: mutex X              | Concurrency
   234% |    1 |                 |           | ON CPU                              | ON CPU
    76% |    1 | dc8x5k16dsk82   | 0         | ON CPU                              | ON CPU
    76% |    1 | 9pyrc45h3tgg9   | 0         | ON CPU                              | ON CPU
    65% |    1 | 0u9hxt3azayv5   | 0         | ON CPU                              | ON CPU
    62% |    1 | arg5wx08suqm0   | 0         | ON CPU                              | ON CPU
    55% |    1 | cqtdnxg9apfza   | 0         | ON CPU                              | ON CPU
    49% |    1 | 3k7xkc2t9fwf6   | 0         | library cache: mutex X              | Concurrency
    46% |    1 | 61m38g7ygpfvz   | 0         | ON CPU                              | ON CPU
    44% |    1 | 2ht2a25wq37kg   | 0         | library cache: mutex X              | Concurrency
--  End of ASH snap 1, end=2020-11-17 16:03:21, seconds=65, samples_taken=98
PL/SQL procedure successfully completed.


$ zzdate ;seq 50 | xargs -I{} -P 50 sqlplus -s -l scott/book @m3.txt 2e5 markhot_p=50 {} >/dev/null ;zzdate
trunc(sysdate)+16/24+02/1440+17/86400 == 2020/11/17 16:02:17
trunc(sysdate)+16/24+03/1440+18/86400 == 2020/11/17 16:03:18
--//從時間上看並沒有體現使用DBMS_SHARED_POOL.MARKHOT的好處。

SYS@book> @ tpt/ash/ash_wait_chains username||':'||program2||event2 module='SQL*Plus' trunc(sysdate)+16/24+02/1440+17/86400 trunc(sysdate)+16/24+03/1440+18/86400
-- Display ASH Wait Chain Signatures script v0.2 BETA by Tanel Poder ( http://blog.tanelpoder.com )
%This     SECONDS        AAS WAIT_CHAIN
------ ---------- ---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  43%        1289       21.1 -> SCOTT:(sqlplus) ON CPU
  33%         994       16.3 -> SCOTT:(sqlplus) library cache: mutex X  -> SCOTT:(sqlplus) ON CPU
  19%         570        9.3 -> SCOTT:(sqlplus) library cache: mutex X
   2%          57         .9 -> SCOTT:(sqlplus) library cache: mutex X  -> SCOTT:(sqlplus) library cache: mutex X
   2%          54         .9 -> SCOTT:(sqlplus) library cache: mutex X  -> SCOTT:(sqlplus) library cache: mutex X  -> SCOTT:(sqlplus) ON CPU
   0%           5         .1 -> SCOTT:(sqlplus) cursor: pin S
   0%           1          0 -> SCOTT:(sqlplus) library cache: mutex X  -> SCOTT:(sqlplus) library cache: mutex X  -> SCOTT:(sqlplus) library cache: mutex X  -> SCOTT:(sqlplus) library cache: mutex X  -> SCOTT:(sqlplus) library cache: mutex X
   0%           1          0 -> SCOTT:(sqlplus) cursor: pin S  -> SCOTT:(sqlplus) ON CPU
   0%           1          0 -> SCOTT:(sqlplus) library cache: mutex X  -> SCOTT:(sqlplus) library cache: mutex X  -> SCOTT:(sqlplus) library cache: mutex X
   0%           1          0 -> SCOTT:(sqlplus) library cache: mutex X  -> SCOTT:(sqlplus) cursor: pin S
   0%           1          0 -> SCOTT:(sqlplus) library cache: mutex X  -> SCOTT:(sqlplus) library cache: mutex X  -> SCOTT:(sqlplus) library cache: mutex X  -> SCOTT:(sqlplus) library cache: mutex X
11 rows selected.

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_p=50                 50                   5989        299426
unmarkhot_p=50               50                   6118        305892

--//根本無法體會dbms_shared_pool.markhot的好處。

6.標識函式為熱物件呢?
SCOTT@book> @ cd.txt
OWNER  NAME    HASH_VALUE FULL_HASH_VALUE                  NAMESPACE            CHILD_LATCH HOT_FLAG             EXECUTIONS INVALIDATIONS
------ ------- ---------- -------------------------------- -------------------- ----------- -------------------- ---------- -------------
SCOTT  FX_NUM  3934649448 e946e63d9c1e58dd062f52adea85fc68 TABLE/PROCEDURE           130152                        50000000             0

SYS@book> exec dbms_shared_pool.markhot(hash => 'e946e63d9c1e58dd062f52adea85fc68', namespace => 1, global => true);
PL/SQL procedure successfully completed.

SYS@book> @ tpt/snapper ash 65 1  "select inst_id,sid from gv$session where username='SCOTT' and program like 'sqlplus%' and inst_id=1"
Sampling SID select inst_id,sid from gv$session where username='SCOTT' and program like 'sqlplus%' and inst_id=1 with interval 65 seconds, taking 1 snapshots...
-- Session Snapper v4.11 BETA - by Tanel Poder ( http://blog.tanelpoder.com ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)
----------------------------------------------------------------------------------------------------
Active% | INST | SQL_ID          | SQL_CHILD | EVENT                               | WAIT_CLASS
----------------------------------------------------------------------------------------------------
   309% |    1 |                 |           | ON CPU                              | ON CPU
   114% |    1 |                 |           | library cache: mutex X              | Concurrency
   106% |    1 | 0u9hxt3azayv5   | 0         | ON CPU                              | ON CPU
   101% |    1 | dc8x5k16dsk82   | 0         | ON CPU                              | ON CPU
    97% |    1 | arg5wx08suqm0   | 0         | ON CPU                              | ON CPU
    95% |    1 | cqtdnxg9apfza   | 0         | ON CPU                              | ON CPU
    88% |    1 | 9pyrc45h3tgg9   | 0         | ON CPU                              | ON CPU
    64% |    1 | 5bacfy8mwthrj   | 0         | ON CPU                              | ON CPU
    54% |    1 | 61m38g7ygpfvz   | 0         | ON CPU                              | ON CPU
    36% |    1 | 0srq6bz2rvktx   | 0         | ON CPU                              | ON CPU
--  End of ASH snap 1, end=2020-11-17 16:10:06, seconds=65, samples_taken=99
PL/SQL procedure successfully completed.

$ zzdate ;seq 50 | xargs -I{} -P 50 sqlplus -s -l scott/book @m3.txt 2e5 markhot_pfs=50 {} >/dev/null ;zzdate
trunc(sysdate)+16/24+09/1440+02/86400 == 2020/11/17 16:09:02
trunc(sysdate)+16/24+09/1440+45/86400 == 2020/11/17 16:09:45

SYS@book> @ tpt/ash/ash_wait_chains username||':'||program2||event2 module='SQL*Plus' trunc(sysdate)+16/24+09/1440+02/86400 trunc(sysdate)+16/24+09/1440+45/86400
-- Display ASH Wait Chain Signatures script v0.2 BETA by Tanel Poder ( http://blog.tanelpoder.com )
%This     SECONDS        AAS WAIT_CHAIN
------ ---------- ---------- -------------------------------------------------------------------------------------
  84%        1767       41.1 -> SCOTT:(sqlplus) ON CPU
  11%         234        5.4 -> SCOTT:(sqlplus) library cache: mutex X
   4%          77        1.8 -> SCOTT:(sqlplus) library cache: mutex X  -> SCOTT:(sqlplus) ON CPU
   1%          15         .3 -> SCOTT:(sqlplus) cursor: pin S  -> SCOTT:(sqlplus) ON CPU
   0%           4         .1 -> SCOTT:(sqlplus) library cache: mutex X  -> SCOTT:(sqlplus) library cache: mutex X
   0%           3         .1 -> SCOTT:(sqlplus) cursor: pin S
6 rows selected.
--//依舊存在少量library cache: mutex X。
SCOTT@book> @ cd.txt
OWNER  NAME   HASH_VALUE FULL_HASH_VALUE                  NAMESPACE            CHILD_LATCH HOT_FLAG             EXECUTIONS INVALIDATIONS
------ ------ ---------- -------------------------------- -------------------- ----------- -------------------- ---------- -------------
SCOTT  FX_NUM 3934649448 e946e63d9c1e58dd062f52adea85fc68 TABLE/PROCEDURE           130152 HOT                    50000000             0
SCOTT  FX_NUM 2604013040 acab658d7761882204bcbd0b9b3615f0 TABLE/PROCEDURE             5616 HOTCOPY1                1400000             0
SCOTT  FX_NUM 1147571161 5bc0190cffaf491d2cc98bda44668bd9 TABLE/PROCEDURE            35801 HOTCOPY6                1400000             0
SCOTT  FX_NUM 2514462761 4046d059eca2403b2f1031c395dfa829 TABLE/PROCEDURE           108585 HOTCOPY11               1400000             0
SCOTT  FX_NUM 2255211854 685056ffeb2dead33b91453e866bcd4e TABLE/PROCEDURE           118094 HOTCOPY9                1200000             0
SCOTT  FX_NUM 1128717707 5e9cc5f89e3a3b6fa14956904346dd8b TABLE/PROCEDURE            56715 HOTCOPY4                1200000             0
SCOTT  FX_NUM 3773218966 44b816a2374a4ecfd1c80ca4e0e6c096 TABLE/PROCEDURE            49302 HOTCOPY7                 800000             0
SCOTT  FX_NUM 1509772016 5aa610021f96b19db8b56a4459fd4af0 TABLE/PROCEDURE            84720 HOTCOPY2                 800000             0
SCOTT  FX_NUM  385266949 97923d5d4cfc8cfd6175dca316f6b505 TABLE/PROCEDURE            46341 HOTCOPY3                 600000             0
SCOTT  FX_NUM 2652599427 ef67af593682efa5dbecd4ee9e1b7483 TABLE/PROCEDURE            95363 HOTCOPY8                 400000             0
SCOTT  FX_NUM 1752733049 2a5adce93042ee1fd233ef8668789579 TABLE/PROCEDURE            38265 HOTCOPY12                400000             0
SCOTT  FX_NUM 2357479237 9b6f379c93c9312cab9a22be8c844745 TABLE/PROCEDURE            18245 HOTCOPY5                 400000             0
12 rows selected.
--//我並行50個會話,僅僅開啟11個hotcopy,依舊存在爭用,從EXECUTIONS次數也可以看出來。

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_pfs=50               50                   4190        209483
markhot_p=50                 50                   5989        299426
unmarkhot_p=50               50                   6118        305892

7.總結:
從測試可以看出,使用dbms_shared_pool.markhot標識sql語句帶來的效果不是很大,主要遇到http://blog.itpub.net/267265/viewspace-2675362/的bug。
利益還是在於連結http://blog.itpub.net/267265/viewspace-2675377/的總結,再次轉抄一遍(原始連結有1個地方寫錯):

1.使用DBMS_SHARED_POOL.MARKHOT標識熱sql語句,反而效能更慢。我估計可能遇到bug。
參考連結:http://blog.itpub.net/267265/viewspace-2675362/=>[20200212]使用DBMS_SHARED_POOL.MARKHOT與檢視v$open_cursor.txt

2.使用DBMS_SHARED_POOL.MARKHOT標識熱物件,效果不錯,如果頻繁呼叫可以嘗試使用。
--//這裡原始連結有錯。

3.關於FULL_HASH_VALUE的計算:
--// sql語句 在原來的基礎上加入 . mod(sid,cpu_count/2)+1數字的字串 參與運算。
--// 如果設定隱含引數 _kgl_hot_object_copies, sql語句 在原來的基礎上加入 . mod(sid,_kgl_hot_object_copies)+1數字的字串。
--// 物件    object_name.owner.HOTCOPYNN中的NN \0X\0\0\0 (其中X標識namespace)。
--//另外 namespace可以查詢  select distinct kglhdnsp,kglhdnsd,kglobtyd from x$kglob order by 1;

測試參考連結:

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2734737/,如需轉載,請註明出處,否則將追究法律責任。

相關文章