[20171031]markhot.txt

--//昨天看了https://jonathanlewis.wordpress.com/2017/10/02/markhot/,測試看看這樣時候可以減少争用.

1.環境:

SCOTT@book> @ &r/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

SCOTT@book> select rowid,empno,'sqlplus -s scott/book @h3 2e6 '||rowid c60 from emp ;

ROWID EMPNO C60

------------------ ---------- ------------------------------------------------------------

AAAVREAAEAAAACXAAA 7369 sqlplus -s scott/book @h3 2e6 AAAVREAAEAAAACXAAA

AAAVREAAEAAAACXAAB 7499 sqlplus -s scott/book @h3 2e6 AAAVREAAEAAAACXAAB

AAAVREAAEAAAACXAAC 7521 sqlplus -s scott/book @h3 2e6 AAAVREAAEAAAACXAAC

AAAVREAAEAAAACXAAD 7566 sqlplus -s scott/book @h3 2e6 AAAVREAAEAAAACXAAD

AAAVREAAEAAAACXAAE 7654 sqlplus -s scott/book @h3 2e6 AAAVREAAEAAAACXAAE

AAAVREAAEAAAACXAAF 7698 sqlplus -s scott/book @h3 2e6 AAAVREAAEAAAACXAAF

AAAVREAAEAAAACXAAG 7782 sqlplus -s scott/book @h3 2e6 AAAVREAAEAAAACXAAG

AAAVREAAEAAAACXAAH 7788 sqlplus -s scott/book @h3 2e6 AAAVREAAEAAAACXAAH

AAAVREAAEAAAACXAAI 7839 sqlplus -s scott/book @h3 2e6 AAAVREAAEAAAACXAAI

AAAVREAAEAAAACXAAJ 7844 sqlplus -s scott/book @h3 2e6 AAAVREAAEAAAACXAAJ

AAAVREAAEAAAACXAAK 7876 sqlplus -s scott/book @h3 2e6 AAAVREAAEAAAACXAAK

AAAVREAAEAAAACXAAL 7900 sqlplus -s scott/book @h3 2e6 AAAVREAAEAAAACXAAL

AAAVREAAEAAAACXAAM 7902 sqlplus -s scott/book @h3 2e6 AAAVREAAEAAAACXAAM

AAAVREAAEAAAACXAAN 7934 sqlplus -s scott/book @h3 2e6 AAAVREAAEAAAACXAAN

14 rows selected.

--//建立腳本:(注上次忘記補上&).

$ cat bbb.sh

sqlplus -s scott/book @h3 2e6 AAAVREAAEAAAACXAAA &

sqlplus -s scott/book @h3 2e6 AAAVREAAEAAAACXAAB &

sqlplus -s scott/book @h3 2e6 AAAVREAAEAAAACXAAC &

sqlplus -s scott/book @h3 2e6 AAAVREAAEAAAACXAAD &

sqlplus -s scott/book @h3 2e6 AAAVREAAEAAAACXAAE &

sqlplus -s scott/book @h3 2e6 AAAVREAAEAAAACXAAF &

sqlplus -s scott/book @h3 2e6 AAAVREAAEAAAACXAAG &

sqlplus -s scott/book @h3 2e6 AAAVREAAEAAAACXAAH &

sqlplus -s scott/book @h3 2e6 AAAVREAAEAAAACXAAI &

sqlplus -s scott/book @h3 2e6 AAAVREAAEAAAACXAAJ &

sqlplus -s scott/book @h3 2e6 AAAVREAAEAAAACXAAK &

sqlplus -s scott/book @h3 2e6 AAAVREAAEAAAACXAAL &

sqlplus -s scott/book @h3 2e6 AAAVREAAEAAAACXAAM &

sqlplus -s scott/book @h3 2e6 AAAVREAAEAAAACXAAN &

--//建立測試腳本使用綁定變量:

$ cat h3.sql

set verify off

column t1 format a20 new_value t1

column t2 format a20 new_value t2

select sysdate t1 from dual ;

declare

m_id number;

m_rowid varchar2(20);

m_data varchar2(200);

begin

m_rowid := '&2';

for i in 1 .. &&1 loop

-- select ename into m_data from emp where rowid='&&2';

select ename into m_data from emp where rowid =m_rowid ;

--select /*+ &2 */ ename into m_data from emp where rowid =m_rowid ;

--select /*+ &2 */ ename into m_data from emp where rowid =m_rowid for update;

end loop;

end ;

select sysdate t2 from dual ;

spool /tmp/aa append

select ( to_date('&t2','yyyy-mm-dd hh24:mi:ss') - to_date('&t1','yyyy-mm-dd hh24:mi:ss'))*86400 n,'&&2' c20 from dual ;

spool off

quit

2.擷取sql語句的full_hash_value:

--//執行2次如下語句,擷取full_hash_value,.

sqlplus -s scott/book @h3 2 AAAVREAAEAAAACXAAA &

--//sql_id='2gvj95w2k0aw4',hash_value=85994372

select hash_value, full_hash_value, namespace, child_latch, property hot_flag, executions, invalidations from v$db_object_cache where hash_values=85994372;

SCOTT@book> select hash_value, full_hash_value, namespace, child_latch, property hot_flag, executions, invalidations from v$db_object_cache where hash_value=85994372;

HASH_VALUE FULL_HASH_VALUE NAMESPACE CHILD_LATCH HOT_FLAG EXECUTIONS INVALIDATIONS

---------- -------------------------------- --------- ----------- -------- ---------- -------------

85994372 6ddb0702c4c177cb27ee292f05202b84 SQL AREA 0 2 0

85994372 6ddb0702c4c177cb27ee292f05202b84 SQL AREA 11140 2 0

--//FULL_HASH_VALUE= 6ddb0702c4c177cb27ee292f05202b84.

SCOTT@book> select distinct name from v$db_object_cache where hash_value=85994372;

NAME

--------------------------------------

SELECT ENAME FROM EMP WHERE ROWID =:B1

--//exec dbms_shared_pool.markhot( hash=>'6ddb0702c4c177cb27ee292f05202b84', namespace=>0, global=>true);

select sysdate from dual;

3.執行bbb.sh腳本測試:

--//alter system flush shared_pool;

alter system flush buffercache;

select * from emp;

--//分别測試不調用dbms_shared_pool.markhot以及調用的情況下測試如下:

--//注在每次測試前執行以上3條語句,排除其他情況影響.(注:我測試重新整理與不重新整理共享池的情況).

exec dbms_shared_pool.markhot( hash=>'6ddb0702c4c177cb27ee292f05202b84', namespace=>0, global=>true);

SYS@book> select hash_value, full_hash_value, namespace, child_latch, property hot_flag, executions, invalidations from v$db_object_cache where hash_value=85994372;

85994372 6ddb0702c4c177cb27ee292f05202b84 SQL AREA 0 HOT 25530299 0

85994372 6ddb0702c4c177cb27ee292f05202b84 SQL AREA 11140 HOT 25209596 0

--//HOT_FLAG=HOT.

SYS@book> alter system flush shared_pool;

System altered.

85994372 6ddb0702c4c177cb27ee292f05202b84 SQL AREA 11140 HOT 25209596 1

$ grep "AAA" /tmp/aa.lst

--------> 以下不設定了markhot的情況

48 AAAVREAAEAAAACXAAJ

48 AAAVREAAEAAAACXAAE

50 AAAVREAAEAAAACXAAN

50 AAAVREAAEAAAACXAAD

51 AAAVREAAEAAAACXAAA

51 AAAVREAAEAAAACXAAC

52 AAAVREAAEAAAACXAAB

53 AAAVREAAEAAAACXAAM

54 AAAVREAAEAAAACXAAF

54 AAAVREAAEAAAACXAAL

56 AAAVREAAEAAAACXAAG

59 AAAVREAAEAAAACXAAK

62 AAAVREAAEAAAACXAAI

63 AAAVREAAEAAAACXAAH

--------> 以下設定了markhot,并且alter system flush shared_pool.

57 AAAVREAAEAAAACXAAC

58 AAAVREAAEAAAACXAAN

58 AAAVREAAEAAAACXAAD

58 AAAVREAAEAAAACXAAB

58 AAAVREAAEAAAACXAAJ

57 AAAVREAAEAAAACXAAG

60 AAAVREAAEAAAACXAAM

64 AAAVREAAEAAAACXAAI

65 AAAVREAAEAAAACXAAA

65 AAAVREAAEAAAACXAAF

65 AAAVREAAEAAAACXAAE

67 AAAVREAAEAAAACXAAH

68 AAAVREAAEAAAACXAAL

73 AAAVREAAEAAAACXAAK

--------> 以下設定了我重新開機資料庫,sqlplus -s scott/book @h3 2 AAAVREAAEAAAACXAAA & 再執行markhot,

55 AAAVREAAEAAAACXAAD

57 AAAVREAAEAAAACXAAA

57 AAAVREAAEAAAACXAAI

57 AAAVREAAEAAAACXAAH

57 AAAVREAAEAAAACXAAE

58 AAAVREAAEAAAACXAAL

59 AAAVREAAEAAAACXAAJ

60 AAAVREAAEAAAACXAAF

70 AAAVREAAEAAAACXAAK

71 AAAVREAAEAAAACXAAC

76 AAAVREAAEAAAACXAAM

77 AAAVREAAEAAAACXAAN

--//我的測試實際上比不設定反而更快.不知道為什麼?

85994372 6ddb0702c4c177cb27ee292f05202b84 SQL AREA 0 HOT 2 0

85994372 6ddb0702c4c177cb27ee292f05202b84 SQL AREA 11140 HOT 2 0

--//一個奇怪的現象,EXECUTIONS=2.我可執行了2e6次*14次.

SCOTT@book> select sql_id,sql_text,executions,length(sql_text) from v$sqlarea where sql_text like '%SELECT ENAME FROM EMP WHERE ROWID =:B1%'and sql_text not like '%sqlarea%';

SQL_ID SQL_TEXT EXECUTIONS LENGTH(SQL_TEXT)

------------- ------------------------------------------------------------ ---------- ----------------

8jc98afj8s722 SELECT ENAME FROM EMP WHERE ROWID =:B1 3998991 39

dqk9v3d8mnb7n SELECT ENAME FROM EMP WHERE ROWID =:B1 3999192 39

51w0yr3fh9n0y SELECT ENAME FROM EMP WHERE ROWID =:B1 2000000 39

3v5kmvygyxscw SELECT ENAME FROM EMP WHERE ROWID =:B1 2000000 39

6t594qwu6q3h0 SELECT ENAME FROM EMP WHERE ROWID =:B1 2000000 39

bdxybc8zdfbm7 SELECT ENAME FROM EMP WHERE ROWID =:B1 2000000 39

1rvbzkq5y6qmc SELECT ENAME FROM EMP WHERE ROWID =:B1 2000000 39

cjh845cj9fqua SELECT ENAME FROM EMP WHERE ROWID =:B1 3999666 39

3mtu372udrjac SELECT ENAME FROM EMP WHERE ROWID =:B1 2000000 39

ahm9rffcz3q0p SELECT ENAME FROM EMP WHERE ROWID =:B1 3999027 39

10 rows selected.

--//length長度一樣.sql_text的文本沒有變化.而sql_id發生了變化.why??

SELECT hash_value

,full_hash_value

,namespace

,child_latch

,property hot_flag

,executions

,invalidations

FROM v$db_object_cache

WHERE hash_value = 85994372

OR name LIKE 'SELECT ENAME FROM EMP WHERE ROWID =:B1%';

2727091266 c1c613120d5bb52788b12853a28c1c42 SQL AREA 0 HOTCOPY7 3997328 0

2727091266 c1c613120d5bb52788b12853a28c1c42 SQL AREA 7234 HOTCOPY7 5997715 0

1362767092 d8667d27feee3a14db493b1b513a2cf4 SQL AREA 0 HOTCOPY9 3998383 0

1362767092 d8667d27feee3a14db493b1b513a2cf4 SQL AREA 11508 HOTCOPY9 5997882 0

3708080158 2b2d6a42e3f5dc2950f01eb8dd04d01e SQL AREA 0 HOTCOPY8 2000000 0

3708080158 2b2d6a42e3f5dc2950f01eb8dd04d01e SQL AREA 53278 HOTCOPY8 4000000 0

2683232668 b8b4a75c26ca8ed13d9653df9feee19c SQL AREA 0 HOTCOPY5 2000000 0

2683232668 b8b4a75c26ca8ed13d9653df9feee19c SQL AREA 57756 HOTCOPY5 5999619 0

879431168 935723b8e76ce7f46c9524b7346b0e00 SQL AREA 0 HOTCOPY1 2000000 0

879431168 935723b8e76ce7f46c9524b7346b0e00 SQL AREA 69120 HOTCOPY1 4000000 0

1054289511 767295bfca293b63b6f7cb623ed72e67 SQL AREA 0 HOTCOPY4 2000000 0

1054289511 767295bfca293b63b6f7cb623ed72e67 SQL AREA 77415 HOTCOPY4 4000000 0

2346932844 aaec082428ac7dc31bed7f958be35a6c SQL AREA 0 HOTCOPY6 2000000 0

2346932844 aaec082428ac7dc31bed7f958be35a6c SQL AREA 88684 HOTCOPY6 4000000 0

580344650 b698c5f01e065315c8c1042b22975b4a SQL AREA 0 HOTCOPY1 3994969 0

580344650 b698c5f01e065315c8c1042b22975b4a SQL AREA 88906 HOTCOPY1 9985924 0

3034301772 c94e7bc94f070bb139e74338b4dbc54c SQL AREA 0 HOTCOPY3 2000000 0

3034301772 c94e7bc94f070bb139e74338b4dbc54c SQL AREA 116044 HOTCOPY3 4000000 0

2582763541 c88682c52f8f2765a84d377399f1d815 SQL AREA 0 HOTCOPY1 3998183 0

2582763541 c88682c52f8f2765a84d377399f1d815 SQL AREA 120853 HOTCOPY1 7996755 0

22 rows selected.

--//大家可以猜測出為什麼這樣了吧,實際上就是通過将标記hot的分散開來(或者叫hotcopy也許更合适一些),建立多個父子光标.減少争用.

4.深入分析:

--//使用10053跟蹤看看:

SCOTT@book> alter system flush shared_pool;

SCOTT@book> variable B1 varchar2(20);

SCOTT@book> exec :B1 := 'AAAVREAAEAAAACXAAA';

PL/SQL procedure successfully completed.

SCOTT@book> @ &r/10053on 12

old 1: alter session set events '10053 trace name context forever, level &1'

new 1: alter session set events '10053 trace name context forever, level 12'

Session altered.

SCOTT@book> SELECT ENAME FROM EMP WHERE ROWID =:B1 ;

ENAME

----------

SMITH

SCOTT@book> @ &r/dpc '' ''

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID 8jc98afj8s722, child number 0

Plan hash value: 1116584662

------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 1 (100)| |

| 1 | TABLE ACCESS BY USER ROWID| EMP | 1 | 18 | 1 (0)| 00:00:01 |

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

1 - SEL$1 / EMP@SEL$1

--//注意sql_id的值..

SCOTT@book> @ &r/10053off

--//仔細檢查沒有發現線索...我重複測試在設定markhot:

SCOTT@book> @ &r/wait

P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATE WAIT_TIME_MICRO SECONDS_IN_WAIT

---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------- ------------------- --------------- ---------------

0000000099F1D815 0000008400000001 0000000300000000 2582763541 5.6694E+11 1.2885E+10 144 9 165 cursor: pin S WAITED SHORT TIME 6 0

0000000022975B4A 00 0000000300000000 580344650 0 1.2885E+10 106 7 363 cursor: pin S WAITED SHORT TIME 1 0

0000000022975B4A 00 0000000300000000 580344650 0 1.2885E+10 94 7 684 cursor: pin S WAITED SHORT TIME 3 0

0000000000002B84 0000008400000000 000000000000003E 11140 5.6694E+11 62 224 5 51 library cache: mutex X WAITED SHORT TIME 5 0

0000000000002B84 0000008400000000 000000000000003E 11140 5.6694E+11 62 119 7 69 library cache: mutex X WAITED KNOWN TIME 10947 0

0000000099F1D815 0000009000000000 000000000000006A 2582763541 6.1848E+11 106 132 7 47 library cache: mutex X WAITED KNOWN TIME 11003 7

0000000000002B84 00 000000000000003E 11140 0 62 237 5 62 library cache: mutex X WAITED SHORT TIME 2 0

0000000000002B84 0000008400000000 000000000000003E 11140 5.6694E+11 62 158 7 67 library cache: mutex X WAITED SHORT TIME 6 0

0000000000002B84 00 000000000000003E 11140 0 62 67 29 75 library cache: mutex X WAITED SHORT TIME 2 1

0000000000002B84 0000005E00000000 000000000000003E 11140 4.0373E+11 62 184 7 45 library cache: mutex X WAITED SHORT TIME 3 3

0000000000002B84 0000008400000000 000000000000003E 11140 5.6694E+11 62 197 55 72 library cache: mutex X WAITED SHORT TIME 4 0

0000000000002B84 0000008400000000 000000000000003E 11140 5.6694E+11 62 210 9 67 library cache: mutex X WAITED SHORT TIME 4 0

0000000000002B84 000000D200000000 000000000000003E 11140 9.0194E+11 62 80 17 86 library cache: mutex X WAITED SHORT TIME 2 1

0000000000002B84 0000009E00000000 000000000000003E 11140 6.7860E+11 62 171 7 58 library cache: mutex X WAITED SHORT TIME 2 0

--//發現這樣存在大量library cache: mutex X等待事件.

5.取消MARKHOT:

SCOTT@book> @ &r/desc_proc sys dbms_shared_pool %markhot%

INPUT OWNER PACKAGE_NAME OBJECT_NAME

sample : @desc_proc sys dbms_stats gather_%_stats

OWNER PACKAGE_NAME OBJECT_NAME SEQUENCE ARGUMENT_NAME DATA_TYPE IN_OUT DATA_TYPE DEFAULTED

---------- -------------------- ------------------------------ ---------- -------------------- -------------------- --------- -------------------- ----------

SYS DBMS_SHARED_POOL UNMARKHOT 3 GLOBAL PL/SQL BOOLEAN IN PL/SQL BOOLEAN Y

2 NAMESPACE NUMBER IN NUMBER Y

1 HASH VARCHAR2 IN VARCHAR2 N

4 GLOBAL PL/SQL BOOLEAN IN PL/SQL BOOLEAN Y

3 NAMESPACE NUMBER IN NUMBER Y

2 OBJNAME VARCHAR2 IN VARCHAR2 N

1 SCHEMA VARCHAR2 IN VARCHAR2 N

MARKHOT 3 GLOBAL PL/SQL BOOLEAN IN PL/SQL BOOLEAN Y

SYS@book> exec dbms_shared_pool.unmarkhot( hash=>'6ddb0702c4c177cb27ee292f05202b84', namespace=>0, global=>true);

--//再次重複執行:

SQL_ID 2gvj95w2k0aw4, child number 0

--//注意看sql_id,現在是2gvj95w2k0aw4.

--//再次執行前面的測試,看到的等待事件是

P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATE WAIT_TIME_MICRO SECONDS_IN_WAIT

---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- ------------------- --------------- ---------------

0000000005202B84 0000007700000005 0000000300000000 85994372 5.1110E+11 1.2885E+10 54 53 33 cursor: pin S WAITED SHORT TIME 3 9

0000000005202B84 0000000000000004 0000000300000000 85994372 4 1.2885E+10 80 25 30 cursor: pin S WAITED SHORT TIME 3 9

0000000005202B84 0000000000000005 0000000300000000 85994372 5 1.2885E+10 94 13 30 cursor: pin S WAITED SHORT TIME 3 9

0000000005202B84 0000000000000007 0000000300000000 85994372 7 1.2885E+10 106 11 34 cursor: pin S WAITED SHORT TIME 3 9

0000000005202B84 0000000000000006 0000000300000000 85994372 6 1.2885E+10 184 11 30 cursor: pin S WAITED SHORT TIME 2 9

0000000005202B84 0000005000000007 0000000900000000 85994372 3.4360E+11 3.8655E+10 132 11 32 cursor: pin S WAITED SHORT TIME 3 9

0000000005202B84 0000007700000008 0000000300000000 85994372 5.1110E+11 1.2885E+10 144 13 31 cursor: pin S WAITED SHORT TIME 2 9

0000000005202B84 0000000000000007 0000000900000000 85994372 7 3.8655E+10 158 11 31 cursor: pin S WAITED SHORT TIME 3 9

0000000005202B84 0000000000000007 0000000300000000 85994372 7 1.2885E+10 119 11 30 cursor: pin S WAITED SHORT TIME 2 9

0000000005202B84 0000006A00000000 0000000500000000 85994372 4.5527E+11 2.1475E+10 171 11 29 cursor: pin S wait on X WAITED KNOWN TIME 10086 10

--//總結:

1.測試有點亂.思路不清楚,主要自己不了解這方面内容.

2.我的測試并不能變快,出現大量的library cache: mutex X.

3.從這個測試還可以發現sql文本一樣,sql_id可以出現不同的情況,oracle内部應該做了加了一些注解之類的東西....

4.那位了解這方面的内容,歡迎指點^_^.

[20171031]markhot.txt

繼續閱讀

JDBC連接配接資料庫（statement）

MyBatis-Plus 之AR模式

ASP程式設計經典例子

ASP程式設計中20個非常有用的例子

龍珠訓練營task04

Perl與網絡監控

阿裡雲天池龍珠計劃SQL訓練營打卡

阿裡雲天池龍珠計劃SQL訓練營day1

實驗樓sql進階之成績管理系統的資料操作(window)

Oracle的基本操作

SQL優化SQL語句優化的目的

JAVA高效程式設計指南

關于SQL語言

SQL語言基礎：常用的資料查詢語句

neo4j之cypher使用文檔

sqlServer根據經緯查距離