1. 首页
  2. IT资讯

[20200126]使用DBMS_SHARED_POOL.MARKHOT与sql语句.txt

[20200126]使用DBMS_SHARED_POOL.MARKHOT与sql语句.txt

–//以前的测试:http://blog.itpub.net/267265/viewspace-2146632/=>[20171031]markhot.txt
–//当时自己对于使用DBMS_SHARED_POOL.MARKHOT太不理解,实际上就是减少争用,我当时的测试是实际上反而更慢。
–//设置MARKHOT后,出现library cache: mutex X. (P1=  11140)实际上我自己没有注意看一些细节。
–//emp表有14条记录,而查询
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%';
–//输出是10条记录,也就是生成的hotcopy还不够多,这样反而更慢,从executions的次数也可以看出问题。
–//重新测试看看:

1.环境:
SCOTT@book> @ 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

create table job_times (sid number, sessionid number,time_ela number,method varchar2(20));

–//建立测试脚本m2.txt:
$ cat m2.txt
set verify off
–//host sleep $(echo &&3/50 | bc -l )
insert into job_times values ( sys_context ('userenv', 'sid') ,sys_context ('userenv', 'sessionid'),dbms_utility.get_time ,'&&2') ;
commit ;
declare
v_id number;
v_d date;
m_rowid varchar2(20);
m_data varchar2(32);
begin
    m_rowid := '&3';
    for i in 1 .. &&1 loop
        select ename into m_data from emp where rowid =m_rowid ;
    end loop;
end ;
/
update job_times set time_ela = dbms_utility.get_time – time_ela where sid=sys_context ('userenv', 'sid') and sessionid=sys_context ('userenv', 'sessionid') and method='&&2';
commit;
quit

SCOTT@book> select listagg(rowid,',') WITHIN GROUP (order  by rowid ) c100 from emp ;

C100
—————————————————————————————————-
AAAVREAAEAAAACXAAA,AAAVREAAEAAAACXAAB,AAAVREAAEAAAACXAAC,AAAVREAAEAAAACXAAD,AAAVREAAEAAAACXAAE,AAAVR
EAAEAAAACXAAF,AAAVREAAEAAAACXAAG,AAAVREAAEAAAACXAAH,AAAVREAAEAAAACXAAI,AAAVREAAEAAAACXAAJ,AAAVREAAEA
AAACXAAK,AAAVREAAEAAAACXAAL,AAAVREAAEAAAACXAAM,AAAVREAAEAAAACXAAN

2.测试1:
–//不使用DBMS_SHARED_POOL.MARKHOT的情况:
a='AAAVREAAEAAAACXAAA,AAAVREAAEAAAACXAAB,AAAVREAAEAAAACXAAC,AAAVREAAEAAAACXAAD,AAAVREAAEAAAACXAAE,AAAVREAAEAAAACXAAF,AAAVREAAEAAAACXAAG,AAAVREAAEAAAACXAAH,AAAVREAAEAAAACXAAI,AAAVREAAEAAAACXAAJ,AAAVREAAEAAAACXAAK,AAAVREAAEAAAACXAAL,AAAVREAAEAAAACXAAM,AAAVREAAEAAAACXAAN'

$ echo $a | tr  ',' 'n' | xargs -I{} -P 14 sqlplus -s -l scott/book @m2.txt 1e6 NOMARKHOT {}

SCOTT@book> select * from job_times order by TIME_ELA;

       SID  SESSIONID   TIME_ELA METHOD
———- ———- ———- ——————–
       325   20921041       2350 NOMARKHOT
       170   20921030       2361 NOMARKHOT
       255   20921036       2369 NOMARKHOT
       184   20921031       2401 NOMARKHOT
       281   20921040       2528 NOMARKHOT
       128   20921028       2534 NOMARKHOT
       241   20921035       2576 NOMARKHOT
       198   20921033       2603 NOMARKHOT
       297   20921038       2624 NOMARKHOT
       212   20921032       2649 NOMARKHOT
       142   20921029       2665 NOMARKHOT
       226   20921034       2739 NOMARKHOT
       267   20921037       2996 NOMARKHOT
       310   20921039       3009 NOMARKHOT
14 rows selected.
–//最快的与最慢的相差7秒之多。

3.测试2:
–//确定sql_id,查询检索共享池很容易获得:
–//sql_id='2gvj95w2k0aw4',hash_value=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            53247117             0
  85994372 6ddb0702c4c177cb27ee292f05202b84 SQL AREA        11140            53799285             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

SYS@book> exec dbms_shared_pool.markhot( hash=>'6ddb0702c4c177cb27ee292f05202b84', namespace=>0, global=>true);
PL/SQL procedure successfully completed.

$ echo $a | tr  ',' 'n' | xargs -I{} -P 14 sqlplus -s -l scott/book @m2.txt 1e6 MARKHOT {}

SCOTT@book> select * from job_times where METHOD ='MARKHOT' order by TIME_ELA;
       SID  SESSIONID   TIME_ELA METHOD
———- ———- ———- ——————–
       281   20921054       2797 MARKHOT
       255   20921050       2800 MARKHOT
       267   20921053       2814 MARKHOT
       170   20921044       2876 MARKHOT
       325   20921052       2878 MARKHOT
       184   20921046       2902 MARKHOT
       297   20921051       2954 MARKHOT
       241   20921048       2990 MARKHOT
       212   20921047       3052 MARKHOT
       226   20921049       3171 MARKHOT
       310   20921055       3268 MARKHOT
       142   20921043       3459 MARKHOT
        17   20921056       3714 MARKHOT
       198   20921045       3716 MARKHOT
14 rows selected.

4.对比:
SCOTT@book> Select method,count(*),round(avg(TIME_ELA),0),sum(TIME_ELA) from job_times group by method order by 3 ;
METHOD                 COUNT(*) ROUND(AVG(TIME_ELA),0) SUM(TIME_ELA)
——————– ———- ———————- ————-
NOMARKHOT                    14                   2600         36404
MARKHOT                      14                   3099         43391
–//可以发现标记MARKHOT反而执行时间过长。也就是反而不执行dbms_shared_pool.markhot效果更好。

SELECT owner
      ,name
      ,hash_value
      ,full_hash_value
      ,namespace
      ,child_latch
      ,property hot_flag
      ,executions
      ,invalidations
  FROM v$db_object_cache
 WHERE name = 'SELECT ENAME FROM EMP WHERE ROWID =:B1 '
 order by executions desc ;
–//注意B1后面有1个空格。

OWNER  NAME                                     HASH_VALUE FULL_HASH_VALUE                  NAMESPACE  CHILD_LATCH HOT_FLAG   EXECUTIONS INVALIDATIONS
—— —————————————- ———- ——————————– ———- ———– ———- ———- ————-
       SELECT ENAME FROM EMP WHERE ROWID =:B1     85994372 6ddb0702c4c177cb27ee292f05202b84 SQL AREA         11140 HOT          67246853             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1     85994372 6ddb0702c4c177cb27ee292f05202b84 SQL AREA             0 HOT          66572231             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1    580344650 b698c5f01e065315c8c1042b22975b4a SQL AREA         88906 HOTCOPY11     2993565             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1    580344650 b698c5f01e065315c8c1042b22975b4a SQL AREA             0 HOTCOPY11     2993382             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   1054289511 767295bfca293b63b6f7cb623ed72e67 SQL AREA         77415 HOTCOPY4      1999784             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   1054289511 767295bfca293b63b6f7cb623ed72e67 SQL AREA             0 HOTCOPY4      1999784             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   4251304214 88e9b0a03994a83e3b1dd47ffd65c116 SQL AREA        114966 HOTCOPY2      1999158             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   4251304214 88e9b0a03994a83e3b1dd47ffd65c116 SQL AREA             0 HOTCOPY2      1999114             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2346932844 aaec082428ac7dc31bed7f958be35a6c SQL AREA             0 HOTCOPY6      1998950             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2346932844 aaec082428ac7dc31bed7f958be35a6c SQL AREA         88684 HOTCOPY6      1998940             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   3034301772 c94e7bc94f070bb139e74338b4dbc54c SQL AREA             0 HOTCOPY3      1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   1051678227 0cbac329d8cbd103bdf0b37e3eaf5613 SQL AREA         87571 HOTCOPY10     1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   1051678227 0cbac329d8cbd103bdf0b37e3eaf5613 SQL AREA             0 HOTCOPY10     1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2683232668 b8b4a75c26ca8ed13d9653df9feee19c SQL AREA         57756 HOTCOPY5      1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2683232668 b8b4a75c26ca8ed13d9653df9feee19c SQL AREA             0 HOTCOPY5      1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   1362767092 d8667d27feee3a14db493b1b513a2cf4 SQL AREA         11508 HOTCOPY9      1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   1362767092 d8667d27feee3a14db493b1b513a2cf4 SQL AREA             0 HOTCOPY9      1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   3034301772 c94e7bc94f070bb139e74338b4dbc54c SQL AREA        116044 HOTCOPY3      1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2727091266 c1c613120d5bb52788b12853a28c1c42 SQL AREA             0 HOTCOPY7      1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2727091266 c1c613120d5bb52788b12853a28c1c42 SQL AREA          7234 HOTCOPY7      1000000             0
20 rows selected.

–//可以看出我仅仅建立有10个HOTCOPY,从EXECUTIONS次数也可以看出不平衡。奇怪没有HOT_FLAG='HOTCOPY1',遇到有几次有的情况。
–//从EXECUTIONS可以看出出现争用的情况。

5.继续测试:
echo $a | tr  ',' 'n' | xargs -I{} -P 10 sqlplus -s -l scott/book @m2.txt 1e6 MARKHOT10 {}

SCOTT@book> select * from job_times where METHOD ='MARKHOT10' order by TIME_ELA;
       SID  SESSIONID   TIME_ELA METHOD
———- ———- ———- ——————–
       198   20921068       2563 MARKHOT10
       281   20921070       2610 MARKHOT10
       226   20921069       2630 MARKHOT10
       142   20921071       2647 MARKHOT10
       198   20921060       2660 MARKHOT10
       226   20921064       2706 MARKHOT10
       281   20921066       2708 MARKHOT10
       142   20921059       2713 MARKHOT10
       170   20921058       2717 MARKHOT10
       184   20921061       2722 MARKHOT10
       212   20921062       2729 MARKHOT10
       241   20921063       2753 MARKHOT10
       255   20921065       2768 MARKHOT10
       267   20921067       2769 MARKHOT10
14 rows selected.

SCOTT@book> Select method,count(*),round(avg(TIME_ELA),0),sum(TIME_ELA) from job_times group by method order by 3 ;
METHOD                 COUNT(*) ROUND(AVG(TIME_ELA),0) SUM(TIME_ELA)
——————– ———- ———————- ————-
NOMARKHOT                    14                   2600         36404
MARKHOT10                    14                   2693         37695
MARKHOT                      14                   3099         43391

SELECT owner
      ,name
      ,hash_value
      ,full_hash_value
      ,namespace
      ,child_latch
      ,property hot_flag
      ,executions
      ,invalidations
  FROM v$db_object_cache
 WHERE name = 'SELECT ENAME FROM EMP WHERE ROWID =:B1 '
 order by executions desc ;
OWNER  NAME                                     HASH_VALUE FULL_HASH_VALUE                  NAMESPACE  CHILD_LATCH HOT_FLAG   EXECUTIONS INVALIDATIONS
—— —————————————- ———- ——————————– ———- ———– ———- ———- ————-
       SELECT ENAME FROM EMP WHERE ROWID =:B1     85994372 6ddb0702c4c177cb27ee292f05202b84 SQL AREA         11140 HOT          67246853             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1     85994372 6ddb0702c4c177cb27ee292f05202b84 SQL AREA             0 HOT          66572231             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1    580344650 b698c5f01e065315c8c1042b22975b4a SQL AREA         88906 HOTCOPY11     6993500             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1    580344650 b698c5f01e065315c8c1042b22975b4a SQL AREA             0 HOTCOPY11     6993316             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   1054289511 767295bfca293b63b6f7cb623ed72e67 SQL AREA             0 HOTCOPY4      3999779             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   1054289511 767295bfca293b63b6f7cb623ed72e67 SQL AREA         77415 HOTCOPY4      3999779             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2346932844 aaec082428ac7dc31bed7f958be35a6c SQL AREA             0 HOTCOPY6      3998950             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2346932844 aaec082428ac7dc31bed7f958be35a6c SQL AREA         88684 HOTCOPY6      3998940             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2727091266 c1c613120d5bb52788b12853a28c1c42 SQL AREA             0 HOTCOPY7      3000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2727091266 c1c613120d5bb52788b12853a28c1c42 SQL AREA          7234 HOTCOPY7      3000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   4251304214 88e9b0a03994a83e3b1dd47ffd65c116 SQL AREA        114966 HOTCOPY2      2999158             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   4251304214 88e9b0a03994a83e3b1dd47ffd65c116 SQL AREA             0 HOTCOPY2      2999114             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2683232668 b8b4a75c26ca8ed13d9653df9feee19c SQL AREA         57756 HOTCOPY5      2000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   3034301772 c94e7bc94f070bb139e74338b4dbc54c SQL AREA        116044 HOTCOPY3      2000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   1362767092 d8667d27feee3a14db493b1b513a2cf4 SQL AREA         11508 HOTCOPY9      2000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2683232668 b8b4a75c26ca8ed13d9653df9feee19c SQL AREA             0 HOTCOPY5      2000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   1362767092 d8667d27feee3a14db493b1b513a2cf4 SQL AREA             0 HOTCOPY9      2000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   3034301772 c94e7bc94f070bb139e74338b4dbc54c SQL AREA             0 HOTCOPY3      2000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   1051678227 0cbac329d8cbd103bdf0b37e3eaf5613 SQL AREA             0 HOTCOPY10     1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   1051678227 0cbac329d8cbd103bdf0b37e3eaf5613 SQL AREA         87571 HOTCOPY10     1000000             0
20 rows selected.
–//还是出现分配不平均的情况, 从HOT_FLAG='HOTCOPY10'的EXECUTIONS=1000000就可以看出来。

$ echo $a | tr  ',' 'n' | xargs -I{} -P 7 sqlplus -s -l scott/book @m2.txt 1e6 MARKHOT7 {}
–//注:这样仅仅打开7个并行sqlplus,基本分2次执行。

SCOTT@book> select * from job_times where METHOD ='MARKHOT7' order by TIME_ELA;
       SID  SESSIONID   TIME_ELA METHOD
———- ———- ———- ——————–
       170   20921073       2647 MARKHOT7
       198   20921080       2650 MARKHOT7
       226   20921085       2653 MARKHOT7
       184   20921083       2654 MARKHOT7
       212   20921081       2655 MARKHOT7
       198   20921075       2657 MARKHOT7
       241   20921082       2662 MARKHOT7
       142   20921084       2663 MARKHOT7
       212   20921078       2668 MARKHOT7
       170   20921079       2672 MARKHOT7
       241   20921077       2716 MARKHOT7
       184   20921074       2728 MARKHOT7
       142   20921072       2761 MARKHOT7
       226   20921076       2787 MARKHOT7
14 rows selected.

SCOTT@book> Select method,count(*),round(avg(TIME_ELA),0),sum(TIME_ELA) from job_times group by method order by 3 ;
METHOD                 COUNT(*) ROUND(AVG(TIME_ELA),0) SUM(TIME_ELA)
——————– ———- ———————- ————-
NOMARKHOT                    14                   2600         36404
MARKHOT7                     14                   2684         37573
MARKHOT10                    14                   2693         37695
MARKHOT                      14                   3099         43391

6.总结:
–//不知道是否可以得出DBMS_SHARED_POOL.MARKHOT标识热的sql语句,并不能获得良好的性能效果,还不如不用。
–//这是我节前跟别人的讨论,我个人不建议使用这样方式解决这类问题。
–//首先问开发为什么要频繁执行这样的sql语句,是否可以通过注解等方式分散这样的sql语句。
–//我在重复看测试,为什么使用DBMS_SHARED_POOL.MARKHOT标识热的sql语句反而更慢,我减少并发数量一样没有快于不使用的情况。

–//今天上班重复测试:
–//不使用DBMS_SHARED_POOL.MARKHOT标识热的sql语句时,看到的等待事件是:
SCOTT@book> @ wait
P1RAW            P2RAW            P3RAW                    P1             P2           P3        SID    SERIAL#       SEQ# EVENT         STATUS   STATE               WAIT_TIME_MICRO SECONDS_IN_WAIT WAIT_CLASS
—————- —————- —————- ———- ————– ———— ———- ———- ———- ————- ——– ——————- ————— ————— ——————–
0000000005202B84 000000000000000B 0000000900000000   85994372             11  38654705664         30         11         78 cursor: pin S ACTIVE   WAITED SHORT TIME                 2               3 Concurrency
0000000005202B84 0000008000000007 0000000300000000   85994372   549755813895  12884901888         44         11         32 cursor: pin S ACTIVE   WAITED SHORT TIME                 3               5 Concurrency
0000000005202B84 000000000000000A 0000000900000000   85994372             10  38654705664         58         11         79 cursor: pin S ACTIVE   WAITED SHORT TIME                 2               0 Concurrency
0000000005202B84 000001270000000B 0000000300000000   85994372  1267015352331  12884901888         72          9         53 cursor: pin S ACTIVE   WAITED SHORT TIME                 1               3 Concurrency
0000000005202B84 0000000000000009 0000000300000000   85994372              9  12884901888         86          9         75 cursor: pin S ACTIVE   WAITED SHORT TIME                 9               2 Concurrency
0000000005202B84 0000000000000009 0000000300000000   85994372              9  12884901888        101          7         76 cursor: pin S ACTIVE   WAITED SHORT TIME                 2               1 Concurrency
0000000005202B84 0000008000000006 0000000300000000   85994372   549755813894  12884901888        114          5         78 cursor: pin S ACTIVE   WAITED SHORT TIME                 2               7 Concurrency
0000000005202B84 0000002C0000000C 0000000900000000   85994372   188978561036  38654705664        128          5         78 cursor: pin S ACTIVE   WAITED SHORT TIME                 2               5 Concurrency
0000000005202B84 0000003A00000008 0000000900000000   85994372   249108103176  38654705664        142          5         60 cursor: pin S ACTIVE   WAITED SHORT TIME                 3               4 Concurrency
0000000005202B84 0000003A00000008 0000000300000000   85994372   249108103176  12884901888        156          5         64 cursor: pin S ACTIVE   WAITED SHORT TIME                12               4 Concurrency
0000000005202B84 000000000000000D 0000000900000000   85994372             13  38654705664        170          5         78 cursor: pin S ACTIVE   WAITED SHORT TIME                 2               1 Concurrency
0000000005202B84 0000002C00000008 0000000300000000   85994372   188978561032  12884901888        184          5         47 cursor: pin S ACTIVE   WAITED SHORT TIME                 4              15 Concurrency
0000000005202B84 000000AA00000008 0000000900000000   85994372   730144440328  38654705664        198          5         70 cursor: pin S ACTIVE   WAITED KNOWN TIME             11008               7 Concurrency
0000000005202B84 0000000000000007 0000000300000000   85994372              7  12884901888        295          9         91 cursor: pin S ACTIVE   WAITED SHORT TIME                 1               2 Concurrency
14 rows selected.

–//不使用DBMS_SHARED_POOL.MARKHOT标识热的sql语句时,看到的等待事件是:
SYS@book> exec dbms_shared_pool.markhot( hash=>'6ddb0702c4c177cb27ee292f05202b84', namespace=>0, global=>true);
PL/SQL procedure successfully completed.

SCOTT@book> @ wait
P1RAW            P2RAW            P3RAW                    P1             P2           P3        SID    SERIAL#       SEQ# EVENT                  STATUS   STATE               WAIT_TIME_MICRO SECONDS_IN_WAIT WAIT_CLASS
—————- —————- —————- ———- ————– ———— ———- ———- ———- ———————- ——– ——————- ————— ————— ——————–
00000000A28C1C42 000000C600000000 0000000300000000 2727091266   850403524608  12884901888         30         17       1756 cursor: pin S          ACTIVE   WAITED SHORT TIME                 1               0 Concurrency
0000000099F1D815 00               0000000300000000 2582763541              0  12884901888         72         11       1452 cursor: pin S          ACTIVE   WAITED SHORT TIME                 1               0 Concurrency
00000000B4DBC54C 000000AA00000000 0000000300000000 3034301772   730144440320  12884901888         86         11        165 cursor: pin S          ACTIVE   WAITED SHORT TIME                 1               0 Concurrency
000000006010D860 0000000000000150 00               1611716704            336            0        101          9        148 latch: shared pool     ACTIVE   WAITED SHORT TIME                57               1 Concurrency
00000000A28C1C42 00               0000000300000000 2727091266              0  12884901888        114          7       1238 cursor: pin S          ACTIVE   WAITED SHORT TIME                 1               0 Concurrency
000000006010D860 0000000000000150 00               1611716704            336            0        128          7        189 latch: shared pool     ACTIVE   WAITED SHORT TIME                50               1 Concurrency
0000000099F1D815 00               0000000300000000 2582763541              0  12884901888        156          7        869 cursor: pin S          ACTIVE   WAITED SHORT TIME                 3               0 Concurrency
00000000B4DBC54C 00               0000000300000000 3034301772              0  12884901888        170          7       1941 cursor: pin S          ACTIVE   WAITED SHORT TIME                 2               0 Concurrency
0000000000002B84 0000003A00000000 000000000000003E      11140   249108103168           62        184          7        197 library cache: mutex X ACTIVE   WAITED SHORT TIME                 8               0 Concurrency
00000000A28C1C42 00               0000000300000000 2727091266              0  12884901888        198          7        950 cursor: pin S          ACTIVE   WAITED SHORT TIME                 1               0 Concurrency
0000000000002B84 0000006500000000 000000000000003E      11140   433791696896           62        241          1        204 library cache: mutex X ACTIVE   WAITED SHORT TIME                 1               1 Concurrency
0000000000002B84 00               000000000000003E      11140              0           62        295         21        171 library cache: mutex X ACTIVE   WAITED SHORT TIME                 2               1 Concurrency
12 rows selected.

SCOTT@book> @ wait
P1RAW            P2RAW            P3RAW                    P1             P2           P3        SID    SERIAL#       SEQ# EVENT                  STATUS   STATE               WAIT_TIME_MICRO SECONDS_IN_WAIT WAIT_CLASS
—————- —————- —————- ———- ————– ———— ———- ———- ———- ———————- ——– ——————- ————— ————— ——————–
00000000A28C1C42 000000C600000000 0000000300000000 2727091266   850403524608  12884901888         30         17        290 cursor: pin S          ACTIVE   WAITED SHORT TIME                 2               0 Concurrency
0000000000002B84 0000012700000000 000000000000003E      11140  1267015352320           62         58         13         41 library cache: mutex X ACTIVE   WAITED SHORT TIME                 4               1 Concurrency
0000000099F1D815 00               0000000300000000 2582763541              0  12884901888         72         11        487 cursor: pin S          ACTIVE   WAITED SHORT TIME                 2               0 Concurrency
0000000000002B84 0000006500000000 000000000000003E      11140   433791696896           62         86         11         57 library cache: mutex X ACTIVE   WAITED SHORT TIME                 2               1 Concurrency
0000000000002B84 00               000000000000003E      11140              0           62        101          9         35 library cache: mutex X ACTIVE   WAITED SHORT TIME                 3               2 Concurrency
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
00000000A28C1C42 0000000000000001 0000000300000000 2727091266              1  12884901888        114          7        233 cursor: pin S          ACTIVE   WAITED SHORT TIME                 3               0 Concurrency
0000000000002B84 00               000000000000003E      11140              0           62        128          7         47 library cache: mutex X ACTIVE   WAITED SHORT TIME                 3               1 Concurrency
0000000000002B84 00               000000000000003E      11140              0           62        142          7         41 library cache: mutex X ACTIVE   WAITED SHORT TIME                 2               0 Concurrency
0000000000002B84 00               000000000000003E      11140              0           62        156          7         97 library cache: mutex X ACTIVE   WAITED SHORT TIME                 1               2 Concurrency
0000000000002B84 0000006500000000 000000000000003E      11140   433791696896           62        170          7        146 library cache: mutex X ACTIVE   WAITED SHORT TIME                 4               1 Concurrency
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
0000000000002B84 0000012700000000 000000000000003E      11140  1267015352320           62        184          7         51 library cache: mutex X ACTIVE   WAITED SHORT TIME                 2               0 Concurrency
0000000000002B84 00               000000000000003E      11140              0           62        198          7         81 library cache: mutex X ACTIVE   WAITED SHORT TIME                 2               0 Concurrency
0000000000002B84 0000006500000000 000000000000003E      11140   433791696896           62        241          1         43 library cache: mutex X ACTIVE   WAITED SHORT TIME                 7               1 Concurrency
0000000000002B84 00               000000000000003E      11140              0           62        295         21         42 library cache: mutex X ACTIVE   WAITED SHORT TIME                 3               0 Concurrency
14 rows selected.
–//遇到大量的library cache: mutex X等待事件。

SCOTT@book> @ ev_name 'library cache: mutex X'
    EVENT#   EVENT_ID NAME                                     PARAMETER1           PARAMETER2           PARAMETER3           WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
———- ———- —————————————- ——————– ——————– ——————– ————- ———– ——————–
       289 1646780882 library cache: mutex X                   idn                  value                where                   3875070507           4 Concurrency

–//参数P1,P2,P3表示什么呢?

SELECT owner
      ,name
      ,hash_value
      ,full_hash_value
      ,namespace
      ,child_latch
      ,property hot_flag
      ,executions
      ,invalidations
  FROM v$db_object_cache
 WHERE name = 'SELECT ENAME FROM EMP WHERE ROWID =:B1 '
 order by executions desc ;

OWNER  NAME                                     HASH_VALUE FULL_HASH_VALUE                  NAMESPACE  CHILD_LATCH HOT_FLAG   EXECUTIONS INVALIDATIONS
—— —————————————- ———- ——————————– ———- ———– ———- ———- ————-
       SELECT ENAME FROM EMP WHERE ROWID =:B1     85994372 6ddb0702c4c177cb27ee292f05202b84 SQL AREA         11140 HOT          39941116             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1     85994372 6ddb0702c4c177cb27ee292f05202b84 SQL AREA             0 HOT          39532761             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2727091266 c1c613120d5bb52788b12853a28c1c42 SQL AREA          7234 HOTCOPY7      2971797             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2727091266 c1c613120d5bb52788b12853a28c1c42 SQL AREA             0 HOTCOPY7      2964992             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1    580344650 b698c5f01e065315c8c1042b22975b4a SQL AREA         88906 HOTCOPY11     1997489             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1    580344650 b698c5f01e065315c8c1042b22975b4a SQL AREA             0 HOTCOPY11     1997360             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2582763541 c88682c52f8f2765a84d377399f1d815 SQL AREA        120853 HOTCOPY1      1996477             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2582763541 c88682c52f8f2765a84d377399f1d815 SQL AREA             0 HOTCOPY1      1995152             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   3034301772 c94e7bc94f070bb139e74338b4dbc54c SQL AREA        116044 HOTCOPY3      1975972             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   3034301772 c94e7bc94f070bb139e74338b4dbc54c SQL AREA             0 HOTCOPY3      1886386             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2346932844 aaec082428ac7dc31bed7f958be35a6c SQL AREA             0 HOTCOPY6      1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2346932844 aaec082428ac7dc31bed7f958be35a6c SQL AREA         88684 HOTCOPY6      1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   3708080158 2b2d6a42e3f5dc2950f01eb8dd04d01e SQL AREA             0 HOTCOPY8      1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   3708080158 2b2d6a42e3f5dc2950f01eb8dd04d01e SQL AREA         53278 HOTCOPY8      1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   4251304214 88e9b0a03994a83e3b1dd47ffd65c116 SQL AREA             0 HOTCOPY2      1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   4251304214 88e9b0a03994a83e3b1dd47ffd65c116 SQL AREA        114966 HOTCOPY2      1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   1362767092 d8667d27feee3a14db493b1b513a2cf4 SQL AREA             0 HOTCOPY9      1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2683232668 b8b4a75c26ca8ed13d9653df9feee19c SQL AREA         57756 HOTCOPY5      1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   2683232668 b8b4a75c26ca8ed13d9653df9feee19c SQL AREA             0 HOTCOPY5      1000000             0
       SELECT ENAME FROM EMP WHERE ROWID =:B1   1362767092 d8667d27feee3a14db493b1b513a2cf4 SQL AREA         11508 HOTCOPY9      1000000             0
20 rows selected.

–//对比前面下划线内容。
–//P1=11140,正好对应HOT_FLAG='HOT'的CHILD_LATCH.
–//P2=433791696896, 433791696896 = 0x6500000000
–//P3=62 ,P2,p3不知道表示什么。猜测P2对应阻塞的会话,0x65 = 101,注意看++++的下划线内容。
–//而P2=1267015352320 = 0x12700000000, 能力有限无法推测。
–//CHILD_LATCH值似乎与FULL_HASH_VALUE有关。另外写一篇blog.

SELECT sql_id
      ,sql_text
      ,executions
      ,LENGTH (sql_text)
      ,ORA_HASH (sql_text)
      ,hash_value
  FROM v$sqlarea
 WHERE sql_text = 'SELECT ENAME FROM EMP WHERE ROWID =:B1 ';

SQL_ID        SQL_TEXT                               EXECUTIONS LENGTH(SQL_TEXT) ORA_HASH(SQL_TEXT) HASH_VALUE
————- ————————————– ———- —————- —————— ———-
8jc98afj8s722 SELECT ENAME FROM EMP WHERE ROWID =:B1    2992985               39         1145033045 2727091266
2gvj95w2k0aw4 SELECT ENAME FROM EMP WHERE ROWID =:B1   40873893               39         1145033045   85994372
dqk9v3d8mnb7n SELECT ENAME FROM EMP WHERE ROWID =:B1    1000000               39         1145033045 1362767092
51w0yr3fh9n0y SELECT ENAME FROM EMP WHERE ROWID =:B1    1000000               39         1145033045 3708080158
3v5kmvygyxscw SELECT ENAME FROM EMP WHERE ROWID =:B1    1000000               39         1145033045 2683232668
1rvbzkq5y6qmc SELECT ENAME FROM EMP WHERE ROWID =:B1    1000000               39         1145033045 2346932844
cjh845cj9fqua SELECT ENAME FROM EMP WHERE ROWID =:B1    1999111               39         1145033045  580344650
3q7fngzyqbh8q SELECT ENAME FROM EMP WHERE ROWID =:B1    1000000               39         1145033045 4251304214
3mtu372udrjac SELECT ENAME FROM EMP WHERE ROWID =:B1    1983886               39         1145033045 3034301772
ahm9rffcz3q0p SELECT ENAME FROM EMP WHERE ROWID =:B1    1997056               39         1145033045 2582763541
10 rows selected.

–//视乎如果使用DBMS_SHARED_POOL.MARKHOT设置后,如果生成的HOTCOPY达不到并发数量,就存在library cache: mutex X ACTIVE等待事件。
–//执行EXECUTIONS=1000000仅仅5条。其它大于1000000的(不算执行次数40873893),有4条(视乎计数不对,正常应该是后面都是0.占9个会话。),5+9=14.
–//也就是还是存在争用的情况。

–//有机会测试更多的会话是否效果更好一些,有点长,另外写一篇blog。

SELECT LISTAGG (value_string, ',') WITHIN GROUP (ORDER BY value_string) c200
  FROM (SELECT value_string
          FROM V$SQL_BIND_CAPTURE
         WHERE sql_id IN ('8jc98afj8s722'
                         ,'dqk9v3d8mnb7n'
                         ,'51w0yr3fh9n0y'
                         ,'3v5kmvygyxscw'
                         ,'1rvbzkq5y6qmc'
                         ,'cjh845cj9fqua'
                         ,'3q7fngzyqbh8q'
                         ,'3mtu372udrjac'
                         ,'ahm9rffcz3q0p'));
C200
————————————————————————————————————————————————————————–
AAAVREAAEAAAACXAAA,AAAVREAAEAAAACXAAB,AAAVREAAEAAAACXAAD,AAAVREAAEAAAACXAAF,AAAVREAAEAAAACXAAG,AAAVREAAEAAAACXAAH,AAAVREAAEAAAACXAAI,AAAVREAAEAAAACXAAJ,AAAVREAAEAAAACXAAK

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2675118/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:深沉的少年,转转请注明出处:http://www.cxybcw.com/183064.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code