[20171031]markhot.txt

来源:http://www.cnblogs.com/lfree/archive/2017/10/31/7761834.html
-Advertisement-
Play Games

[20171031]markhot.txt--//昨天看了https://jonathanlewis.wordpress.com/2017/10/02/markhot/,測試看看這樣時候可以減少爭用.1.環境:SCOTT@book> @ &r/ver1PORT_STRING VERSION BANN ...


[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.那位瞭解這方面的內容,歡迎指點^_^.


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • iOS 11更新後以及iPhone X推出後工程中遇到的問題及適配
    1.UITableView滑動時右側的滑動條忽長忽短的亂跳以及MJRefresh上拉刷新死迴圈 這是因為tableView在iOS11預設使用Self-Sizing，tableView的estimatedRowHeight、estimatedSectionHeaderHeight、estimated ...
  • Pandas 常見的基本方法
    說明：文章所有內容均截選自實驗樓教程 "【Pandas 使用教程】" ，想要查看教程完整內容，點擊教程即可~ 前言： Pandas 是非常著名的開源數據處理工具，我們可以通過它對數據集進行快速讀取、轉換、過濾、分析等一系列操作。除此之外，Pandas 擁有強大的缺失數據處理與數據透視功能，可謂是數據 ...
  • c3p0
    c3p0 c3p0 編輯 C3P0是一個開源的JDBC連接池，它實現了數據源和JNDI綁定，支持JDBC3規範和JDBC2的標準擴展。目前使用它的開源項目有Hibernate，Spring等。 C3P0是一個開源的JDBC連接池，它實現了數據源和JNDI綁定，支持JDBC3規範和JDBC2的標準擴展 ...
  • [20171101]修改oracle口令安全問題.txt
    [20171101]修改oracle口令安全問題.txt--//等保的問題,做一些關於修改oracle口令方面的測試.1.oracle修改口令一般如下方式:alter user scott identified by oracle;password scott第三方工具,通常也是執行以上類似的命令. ...
  • Win10 系統安裝Sql Server2008 R2 資料庫遇到的問題及解決辦法總結！
    1.開始安裝時，提示要先安裝 “.NET Framework 3.5(包括.NET 2.0和3.0)”，之前已經下載好.NET Framework 3.5 sp1，安裝時還是提示要先安裝 “.NET Framework 3.5(包括.NET 2.0和3.0)”，結果還是要去網上下載.NET Fram ...
  • oracle ORA-12899錯誤的解決方法
    在使用ORACLE的過程中,會出現各種各樣的問題,各種各樣的錯誤,其中ORA-12899就是前段時間我在將數據導入到我本地機器上的時候一直出現的問題.不過還好已經解決了這個問題,現在分享一下,解決方案;出現ORA-12899,是字元集引起的,中文在UTF-8中占3個位元組,ZHS16GBK中占2個位元組 ...
  • 修改MySQL數據文件預設路徑
    原文鏈接： http://www.aichengxu.com/database/8499581.htm 一.同一主機下位置的轉移 在mysql安裝完成後，要修改資料庫存儲的位置，比如從安裝目錄下的C:\Program Files\MySQL\MySQL Server 5.0\Data文件夾轉移到D: ...
  • [20171031]rman xxx Failure.txt
    [20171031]rman xxx Failure.txt--//簡單測試 List Failure, Advise Failure and Repair Failure命令在11g下,也許以後工作需要.--//雖然我自己很少使用這個命令,感覺這個有點傻瓜化.1.環境:SYS@book> @ &r ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類