[20171031]markhot.txt

时间:2023-12-10 15:25:26

[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.那位了解这方面的内容,欢迎指点^_^.