[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;
HASH_VALUE FULL_HASH_VALUE NAMESPACE CHILD_LATCH HOT_FLAG EXECUTIONS INVALIDATIONS
---------- -------------------------------- --------- ----------- -------- ---------- -------------
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.
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;
HASH_VALUE FULL_HASH_VALUE NAMESPACE CHILD_LATCH HOT_FLAG EXECUTIONS INVALIDATIONS
---------- -------------------------------- --------- ----------- -------- ---------- -------------
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
56 AAAVREAAEAAAACXAAG
57 AAAVREAAEAAAACXAAA
57 AAAVREAAEAAAACXAAI
57 AAAVREAAEAAAACXAAH
57 AAAVREAAEAAAACXAAE
58 AAAVREAAEAAAACXAAL
58 AAAVREAAEAAAACXAAB
59 AAAVREAAEAAAACXAAJ
60 AAAVREAAEAAAACXAAF
70 AAAVREAAEAAAACXAAK
71 AAAVREAAEAAAACXAAC
76 AAAVREAAEAAAACXAAM
77 AAAVREAAEAAAACXAAN
--//我的测试实际上比不设置反而更快.不知道为什么?
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 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%';
HASH_VALUE FULL_HASH_VALUE NAMESPACE CHILD_LATCH HOT_FLAG EXECUTIONS INVALIDATIONS
---------- -------------------------------- --------- ----------- -------- ---------- -------------
2727091266 c1c613120d5bb52788b12853a28c1c42 SQL AREA 0 HOTCOPY7 3997328 0
2727091266 c1c613120d5bb52788b12853a28c1c42 SQL AREA 7234 HOTCOPY7 5997715 0
85994372 6ddb0702c4c177cb27ee292f05202b84 SQL AREA 0 HOT 2 0
85994372 6ddb0702c4c177cb27ee292f05202b84 SQL AREA 11140 HOT 2 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;
System altered.
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
-------------------------------------
SELECT ENAME FROM EMP WHERE ROWID =:B1
Plan hash value: 1116584662
------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
------------------------------------------------------------------------------------
| 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
Session altered.
--//仔细检查没有发现线索...我重复测试在设置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
14 rows selected.
--//发现这样存在大量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
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
14 rows selected.
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;
HASH_VALUE FULL_HASH_VALUE NAMESPACE CHILD_LATCH HOT_FLAG EXECUTIONS INVALIDATIONS
---------- -------------------------------- --------- ----------- -------- ---------- -------------
85994372 6ddb0702c4c177cb27ee292f05202b84 SQL AREA 11140 HOT 2 0
SYS@book> exec dbms_shared_pool.unmarkhot( hash=>'6ddb0702c4c177cb27ee292f05202b84', namespace=>0, global=>true);
PL/SQL procedure successfully completed.
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;
HASH_VALUE FULL_HASH_VALUE NAMESPACE CHILD_LATCH HOT_FLAG EXECUTIONS INVALIDATIONS
---------- -------------------------------- --------- ----------- -------- ---------- -------------
85994372 6ddb0702c4c177cb27ee292f05202b84 SQL AREA 11140 2 0
--//再次重复执行:
SCOTT@book> variable B1 varchar2(20);
SCOTT@book> exec :B1 := 'AAAVREAAEAAAACXAAA';
PL/SQL procedure successfully completed.
SCOTT@book> SELECT ENAME FROM EMP WHERE ROWID =:B1 ;
ENAME
----------
SMITH
SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 2gvj95w2k0aw4, child number 0
-------------------------------------
SELECT ENAME FROM EMP WHERE ROWID =:B1
Plan hash value: 1116584662
------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
------------------------------------------------------------------------------------
| 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,现在是2gvj95w2k0aw4.
--//再次执行前面的测试,看到的等待事件是
SCOTT@book> @ &r/wait
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
10 rows selected.
--//总结:
1.测试有点乱.思路不清楚,主要自己不了解这方面内容.
2.我的测试并不能变快,出现大量的library cache: mutex X.
3.从这个测试还可以发现sql文本一样,sql_id可以出现不同的情况,oracle内部应该做了加了一些注解之类的东西....
4.那位了解这方面的内容,欢迎指点^_^.