[20171031]markhot.txt

[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.那位了解这点的始末,欢迎指点^_^.

相关文章