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

[20201117]使用DBMS_SHARED_POOL.MARKHOT與sql語句5.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".
--//正好裡面有例子，我直接拿來測試看看,首先看看標識熱sql語句後，看看v$open_cursor的情況。

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);

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(DBMS_RANDOM.value(low => v_low, high => v_high));
end;
/

--//建立測試指令碼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個空格。

3.測試:
SCOTT@book> @ spid
       SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
       295        695 56167                    DEDICATED 56168       21        250 alter system kill session '295,695' immediate;

SCOTT@book> @m3.txt 10 xx yy
1 row created.
Commit complete.
PL/SQL procedure successfully completed.
4 rows updated.
Commit complete.
--//確定sql_id='7tr4jwnamtmsr'.

SCOTT@book> select * from v$open_cursor where sid=295 and SQL_ID='7tr4jwnamtmsr'
  2  @ prxx
==============================
SADDR                         : 0000000085EC7D20
SID                           : 295
USER_NAME                     : SCOTT
ADDRESS                       : 000000007BE3D218
HASH_VALUE                    : 356306711
SQL_ID                        : 7tr4jwnamtmsr
SQL_TEXT                      : SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME
LAST_SQL_ACTIVE_TIME          :
SQL_EXEC_ID                   :
CURSOR_TYPE                   : PL/SQL CURSOR CACHED
PL/SQL procedure successfully completed.

SCOTT@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                              31             0
       SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1    356306711 eb4cdceda1c495cd7cdc91e5153ccf17 SQL AREA                       0                              31             0

SCOTT@book>  @prxx
==============================
OWNER                         :
NAME                          : SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
HASH_VALUE                    : 356306711
FULL_HASH_VALUE               : eb4cdceda1c495cd7cdc91e5153ccf17
NAMESPACE                     : SQL AREA
CHILD_LATCH                   : 53015
HOT_FLAG                      :
EXECUTIONS                    : 31
INVALIDATIONS                 : 0
==============================
OWNER                         :
NAME                          : SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
HASH_VALUE                    : 356306711
FULL_HASH_VALUE               : eb4cdceda1c495cd7cdc91e5153ccf17
NAMESPACE                     : SQL AREA
CHILD_LATCH                   : 0
HOT_FLAG                      :
EXECUTIONS                    : 31
INVALIDATIONS                 : 0
PL/SQL procedure successfully completed.
--//確定FULL_HASH_VALUE=eb4cdceda1c495cd7cdc91e5153ccf17.

4.標識熱物件：
--//以sys使用者執行：
SYS@book> exec dbms_shared_pool.markhot(hash => 'eb4cdceda1c495cd7cdc91e5153ccf17', namespace => 0, global => true);
PL/SQL procedure successfully completed.

--//退出重新登入：
SCOTT@book> @ spid

       SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
       295        697 56266                    DEDICATED 56267       21        251 alter system kill session '295,697' immediate;

SCOTT@book> @m3.txt 10 xx yy
1 row created.
Commit complete.
PL/SQL procedure successfully completed.
1 row updated.
Commit complete.

SCOTT@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                       0 HOT                          31             0
       SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1    356306711 eb4cdceda1c495cd7cdc91e5153ccf17 SQL AREA                   53015 HOT                          31             0
       SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1    939937426 432f8fa611a9e7d4d7a686f838064e92 SQL AREA                       0 HOTCOPY8                     10             0
       SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1    939937426 432f8fa611a9e7d4d7a686f838064e92 SQL AREA                   20114 HOTCOPY8                     10             0
--//HOT_FLAG=HOTCOPY8.參考http://blog.itpub.net/267265/viewspace-2675377/的總結：
--//mod(sid,cpu_count/2)+1 ,我的測試環境cpu_count=24.
$ echo 295%12+1 | bc
8

SCOTT@book> select * from v$open_cursor where sid=295 and sql_text='SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1 ';
no rows selected
--//奇怪沒有找到對應記錄。

SCOTT@book> select * from v$open_cursor where sid=295 and sql_text like 'SELECT LOW_VALUE, HIGH_VALUE%'
  2  @ prxx
==============================
SADDR                         : 0000000085EC7D20
SID                           : 295
USER_NAME                     : SCOTT
ADDRESS                       : 000000007CB1C9D0
HASH_VALUE                    : 939937426
SQL_ID                        : dg9n6z0w0cmnk
SQL_TEXT                      : SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME
LAST_SQL_ACTIVE_TIME          :
SQL_EXEC_ID                   :
CURSOR_TYPE                   : PL/SQL CURSOR CACHED
PL/SQL procedure successfully completed.
--//噢，原來sql_text僅僅儲存開頭60個字元長度。
--//可以發現很明顯，沒有出現在連結看到的情況
http://blog.itpub.net/267265/viewspace-2675362/
--//也就是如果在函式或者PL/SQL包裡面的sql語句透過DBMS_SHARED_POOL.MARKHOT是可以提高效能的。
--//限於偏於另外寫一篇blog測試。