[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測試。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2734695/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20201117]使用DBMS_SHARED_POOL.MARKHOT與sql語句6.txtSQL
- [20200126]使用DBMS_SHARED_POOL.MARKHOT與sql語句.txtSQL
- [20200212]使用DBMS_SHARED_POOL.MARKHOT與sql語句3.txtSQL
- [20200211]使用DBMS_SHARED_POOL.MARKHOT與sql語句2.txtSQL
- 使用sql語句查詢平均值,使用sql語句查詢資料總條數, not in 篩選語句的使用SQL
- Java中如何解析SQL語句、格式化SQL語句、生成SQL語句？JavaSQL
- SQL語句SQL
- jsqlparser使用記錄---生成sql語句JSSQL
- Fastapi sqlalchemy DBApi 直接使用sql語句ASTAPISQL
- 【SQL】Oracle SQL join on語句and和where使用區別SQLOracle
- 【SQL】9 SQL INSERT INTO 語句SQL
- 【SQL】10 SQL UPDATE 語句SQL
- 【SQL】11 SQL DELETE 語句SQLdelete
- SQL SELECT 語句SQL
- sql常用語句SQL
- [20240320]空格與sqlpus的sql語句.txtSQL
- [20200212]使用DBMS_SHARED_POOL.MARKHOT與sql的計算2.txtSQL
- [20200211]使用DBMS_SHARED_POOL.MARKHOT與sql_id的計算.txtSQL
- 4.3.2 關於使用SQL語句建立CDBSQL
- Oracle執行語句跟蹤 使用sql trace實現語句追蹤OracleSQL
- 日期與字串的互相轉換SQL語句字串SQL
- 1.3. SQL 語句SQL
- Oracle基本SQL語句OracleSQL
- Sql語句小整理SQL
- SQL語句優化SQL優化
- SQL語句IN的用法SQL
- SQL 語句學習SQL
- flask之控制語句 if 語句與for語句Flask
- [20211009]使用bash計算sql語句的sql_id.txtSQL
- Oracle SQL精妙SQL語句講解OracleSQL
- 18 與Oracle Data Guard 相關的SQL語句OracleSQL
- SQL語句優化的原則與方法QOSQL優化
- 【SQL】14 UNION 操作符、SELECT INTO 語句、INSERT INTO SELECT 語句、CREATE DATABASE 語句、CREATE TABLE 語句SQLDatabase
- SQL語言基礎(SELECT語句)SQL
- MYSQL SQL語句優化MySql優化
- springboot 中列印 sql 語句Spring BootSQL
- 【資料庫】SQL語句資料庫SQL
- sql查詢語句流程SQL