[20210520]11g shared pool latch与library cache mutex的简单探究.txt

[20210520]11g shared pool latch与library cache mutex的简单探究.txt --//前面测试library cache 的转储,发现11g不再使用latch library cache,而是使用library cache mutex代替. --//测试11g下这种使用情况. 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 SELECT addr         ,latch#         ,child#         ,level#         ,name         ,gets         ,sleeps         ,immediate_gets         ,immediate_misses         ,spin_gets     FROM V$LATCH_CHILDREN    WHERE name LIKE 'shared pool' ORDER BY addr; ADDR                 LATCH#     CHILD#     LEVEL# NAME              GETS     SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES  SPIN_GETS ---------------- ---------- ---------- ---------- ----------- ---------- ---------- -------------- ---------------- ---------- 000000006010D860        336          1          7 shared pool    6780053       1054              0                0      12871 000000006010D900        336          2          7 shared pool         11          0              0                0          0 000000006010D9A0        336          3          7 shared pool         11          0              0                0          0 000000006010DA40        336          4          7 shared pool         11          0              0                0          0 000000006010DAE0        336          5          7 shared pool         11          0              0                0          0 000000006010DB80        336          6          7 shared pool         11          0              0                0          0 000000006010DC20        336          7          7 shared pool         11          0              0                0          0 7 rows selected. --//注意仅仅一个shared pool latch的gets很大,其它实际上启动后不会使用. --//shared pool latch的数量与共享池内存大小,cpu数量有关.实际上取 共享池内存大小/512M(对于11G是这样,早期版本有一些除256M,128M) --//以及cpu数量/4的最小值.我设置sga才484M.仅仅1个shared pool latch. 2.测试： --//session 1: SCOTT@book> select * from dept where deptno=20;     DEPTNO DNAME          LOC ---------- -------------- -------------         20 RESEARCH       DALLAS --//执行多次。 SCOTT@book> @ hash HASH_VALUE SQL_ID        CHILD_NUMBER HASH_HEX ---------- ------------- ------------ ---------   95129850 80baj2c2ur47u            0   5ab90fa SYS@book> @ tix New tracefile_identifier =  /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_13734_0001.trc SYS@book> oradebug setmypid Statement processed. SYS@book> oradebug dump library_cache 26; Statement processed. --//这样可以获得堆大小信息。 2+8+16 = 26 --//alter session set events 'immediate trace name library_cache level N'; . Level=1，转储库缓存统计信息。 . Level=2，转储散列表概要。 . Level=4，转储库缓存对象，只包含基本信息。 . Level=8，转储库缓存对象，包含详细信息(包括 child references、pin waiters等)。 . Level=16，增加堆大小信息。 . Level=32，增加堆信息。 SYS@book> @ sharepool/shp4 80baj2c2ur47u 0 TEXT                  KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09 --------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- child handle address  000000007D949148 000000007C351C40 select * from dept where deptno=20                1          0          0 000000007C8CF828 000000007DCF55E0       4528      12144       3067     19739      19739   95129850 80baj2c2ur47u          0 parent handle address 000000007C351C40 000000007C351C40 select * from dept where deptno=20                1          0          0 000000007BE1E1F8 00                     4720          0          0      4720       4720   95129850 80baj2c2ur47u      65535 Bucket: #=102650 Mutex=0x80528f40(0, 22, 0, 6)   LibraryHandle:  Address=0x7c351c40 Hash=5ab90fa LockMode=N PinMode=0 LoadLockMode=0 Status=VALD     ObjectName:  Name=select * from dept where deptno=20       FullHashValue=e8ec445edab00042802d511305ab90fa Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=95129850 OwnerIdn=83     Statistics:  InvalidationCount=0 ExecutionCount=6 LoadCount=2 ActiveLocks=1 TotalLockCount=3 TotalPinCount=1     Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=1 Version=0 BucketInUse=2 HandleInUse=2 HandleReferenceCount=0     Concurrency:  DependencyMutex=0x7c351cf0(0, 1, 0, 0) Mutex=0x7c351d80(1, 30, 0, 6)     Flags=RON/PIN/TIM/PN0/DBN/[10012841]     WaitersLists:       Lock=0x7c351cd0[0x7c351cd0,0x7c351cd0]       Pin=0x7c351cb0[0x7c351cb0,0x7c351cb0]       LoadLock=0x7c351d28[0x7c351d28,0x7c351d28]     Timestamp:  Current=05-13-2021 10:04:12     HandleReference:  Address=0x7c351e10 Handle=(nil) Flags=[00]     ReferenceList:       Reference:  Address=0x7d9f70c0 Handle=0x7cf101a0 Flags=ROD[21]     LibraryObject:  Address=0x7dcf4f20 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]       DataBlocks:         Block:  #='0' name=KGLH0^5ab90fa pins=0 Change=NONE           Heap=0x7be1e1f8 Pointer=0x7dcf4fc0 Extent=0x7dcf4ea0 Flags=I/-/P/A/-/-           FreedLocation=0 Alloc=2.437500 Size=3.976562 LoadTime=12215214510       ChildTable:  size='16'         Child:  id='0' Table=0x7dcf5dd0 Reference=0x7dcf5810 Handle=0x7d949148       Children:         Child:  childNum='0'           LibraryHandle:  Address=0x7d949148 Hash=0 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD             Name:  Namespace=SQL AREA(00) Type=CURSOR(00)             Statistics:  InvalidationCount=0 ExecutionCount=6 LoadCount=1 ActiveLocks=1 TotalLockCount=3 TotalPinCount=7             Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=0 Version=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0             Concurrency:  DependencyMutex=0x7d9491f8(0, 0, 0, 0) Mutex=0x7c351d80(1, 30, 0, 6)             Flags=RON/PIN/PN0/EXP/CHD/[10012111]             WaitersLists:               Lock=0x7d9491d8[0x7d9491d8,0x7d9491d8]               Pin=0x7d9491b8[0x7d9491b8,0x7d9491b8]               LoadLock=0x7d949230[0x7d949230,0x7d949230]             ReferenceList:               Reference:  Address=0x7dcf5810 Handle=0x7c351c40 Flags=CHL[02]             LibraryObject:  Address=0x7c9b3210 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]               Dependencies:  count='2' size='16' table='0x7c9b4048'                 Dependency:  num='0'                   Reference=0x7c9b37c8 Position=0 Flags=DEP[0001]                   Handle=0x7c606dc0 Type=NONE(255) Parent=SCOTT                 Dependency:  num='1'                   Reference=0x7c9b3868 Position=14 Flags=DEP[0001]                   Handle=0x7d46f3a0 Type=TABLE(02) Parent=SCOTT.DEPT               ReadOnlyDependencies:  count='1' size='16'                 ReadDependency:  num='0' Table=0x7c9b40e0 Reference=0x7c9b36c8 Handle=0x7cf101a0 Flags=DEP/ROD/KPP[61]               Authorizations:  count='1' size='16' entryeize='16'               Accesses:  count='1' size='16'                 Dependency:  num='1' Type=0009               Translations:  count='1' size='16'                 Translation:  num='0' Original=0x7d46f3a0 Final=0x7d46f3a0               DataBlocks:                 Block:  #='0' name=KGLH0^5ab90fa pins=0 Change=NONE                   Heap=0x7c8cf828 Pointer=0x7c9b32b0 Extent=0x7c9b3190 Flags=I/-/P/A/-/-                   FreedLocation=0 Alloc=2.593750 Size=3.976562 LoadTime=12215214510                 Block:  #='6' name=SQLA^5ab90fa pins=0 Change=NONE                   Heap=0x7dcf55e0 Pointer=0x7dbe4500 Extent=0x7dbe38c0 Flags=I/-/-/A/-/E                   FreedLocation=0 Alloc=8.445312 Size=11.859375 LoadTime=0             NamespaceDump:               Child Cursor:  Heap0=0x7c9b32b0 Heap6=0x7dbe4500 Heap0 Load Time=05-13-2021 10:04:12 Heap6 Load Time=05-13-2021 10:04:12     NamespaceDump:       Parent Cursor:  sql_id=80baj2c2ur47u parent=0x7dcf4fc0 maxchild=1 plk=y ppn=n --//hash=5ab90fa = 95129850 --//95129850 % 131072 = 102650 --//知道这条语句一定使用Bucket: #=102650 Mutex=0x80528f40. --//BUCKET肯定不会变，我关闭重启数据库发现Mutex对应的地址也没有变，估计这个启动时就设定好,我前面测试这类mutex结构体占用 --//40字节。 SYS@book> oradebug peek 0x80528f40 40 [080528F40, 080528F68) = 00000000 00000000 00000018 00000000 000190FA 00000000 80528F58 00000000 80528F58 00000000 --//看不出什么信息， 0x000190FA = 102650,对应Bucket. --//退出回话,刷新共享池: alter system flush shared_pool; alter system flush shared_pool; alter system flush shared_pool; SYS@book> oradebug peek 0x80528f40 40 [080528F40, 080528F68) = 00000000 00000000 0000001B 00000000 000190FA 00000000 80528F58 00000000 80528F58 00000000 3.测试: --//再次登录，测试前执行命令多次.主要是避免递归sql语句以及测试时遇到硬解析时挂起. --//desc dept; --//Select * from dept where deptno=20; --//@ wait --//session 1: SCOTT@book> @ spid        SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50 ---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------          1       6585 6763                     DEDICATED 6764        24        155 alter system kill session '1,6585' immediate; --//记下SID=1. --//session 2: SYS@book> oradebug setmypid Statement processed. SYS@book> oradebug peek 0x000000006010D860 4 [06010D860, 06010D864) = 00000000 --//shared pool的地址 SYS@book> oradebug peek 0x0000000080528f40 4 [080528F40, 080528F44) = 00000000 --//对应bucket= 102650的mutext地址。 SYS@book> oradebug poke 0x000000006010D860 4 0x00000001 BEFORE: [06010D860, 06010D864) = 00000000 AFTER:  [06010D860, 06010D864) = 00000001 SYS@book> oradebug poke 0x0000000080528f40 4 0x00000001 BEFORE: [080528F40, 080528F44) = 00000000 AFTER:  [080528F40, 080528F44) = 00000001 --//session 1: --//该语句的第1次执行： SCOTT@book> select * from dept where deptno=20; --//注意第1个字符小写,该语句第1次执行,需要硬解析.挂起!! SYS@book> @ wait P1RAW            P2RAW            P3RAW                    P1         P2         P3        SID    SERIAL#       SEQ# EVENT                                    STATUS   STATE               WAIT_TIME_MICRO SECONDS_IN_WAIT WAIT_CLASS           CLIENT_INFO ---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- -------- ------------------- --------------- --------------- -------------------- -------------------- 00000000000190FA 0000000000000001 000000000000003E     102650          1         62          1       6585      13633 library cache: mutex X                   ACTIVE   WAITING                        1033               0 Concurrency 000000006010D860 0000000000000150 00               1611716704        336          0         15          3      56187 latch: shared pool                       ACTIVE   WAITING                    10829818              11 Concurrency --//持有library cache: mutex X,注意P1,P2.P3参数。这里P1标识library cache bucket，也就是hash的后21位(2进制)相当于hash%131072。 SYS@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 SYS@book> oradebug peek 0x80528f40 40 [080528F40, 080528F68) = 00000001 00000000 0000092B 00042180 000190FA 00000006 80528F58 00000000 80528F58 00000000 SYS@book> oradebug poke 0x0000000080528f40 4 0x00000000 BEFORE: [080528F40, 080528F44) = 00000001 AFTER:  [080528F40, 080528F44) = 00000000 SYS@book> @ wait P1RAW            P2RAW            P3RAW                    P1         P2         P3        SID    SERIAL#       SEQ# EVENT                                    STATUS   STATE               WAIT_TIME_MICRO SECONDS_IN_WAIT WAIT_CLASS           CLIENT_INFO ---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- -------- ------------------- --------------- --------------- -------------------- -------------------- 000000006010D860 0000000000000150 00               1611716704        336          0          1       6585      30899 latch: shared pool                       ACTIVE   WAITING                    11647911              12 Concurrency 000000006010D860 0000000000000150 00               1611716704        336          0         15          3      56187 latch: shared pool                       ACTIVE   WAITING                    81069438              81 Concurrency 000000006010D860 0000000000000150 00               1611716704        336          0        239          1      13363 latch: shared pool                       ACTIVE   WAITING                    64137792              64 Concurrency --//持有latch: shared pool. SYS@book> oradebug peek 0x80528f40 40 [080528F40, 080528F68) = 00000000 00000000 0000092C 00042180 000190FA 00000000 80528F58 00000000 80528F58 00000000 --//session 1: SCOTT@book> select * from dept where deptno=20; DEPTNO DNAME    LOC ------ -------- ------     20 RESEARCH DALLAS --//session 2: SYS@book> select * from v$mutex_sleep_history where mutex_identifier=102650 order by sleep_timestamp desc   2  @ prxx ============================== MUTEX_IDENTIFIER              : 102650 --//这里mutex_identifier=102650明显标识bucket。 SLEEP_TIMESTAMP               : 2021-05-20 09:44:51.172610 MUTEX_TYPE                    : Library Cache GETS                          : 2346 --// 2346 = 0x92a,也就是gets的数量。 SLEEPS                        : 29193716 --// 29193716 = 0x1bd75f4 REQUESTING_SESSION            : 1 BLOCKING_SESSION              : 0 LOCATION                      : kglhdgn1  62 --//mutex的位置，kglhdgn1标识什么不懂。与前面P3参数一致。 MUTEX_VALUE                   : 0000000000000001 P1                            : 0 P1RAW                         : 00 P2                            : 0 P3                            : 0 P4                            : 0 P5                            : 3.继续： --//session 2: SYS@book> oradebug poke 0x000000006010D860 4 0x00000001 BEFORE: [06010D860, 06010D864) = 00000000 AFTER:  [06010D860, 06010D864) = 00000001 SYS@book> oradebug poke 0x0000000080528f40 4 0x00000001 BEFORE: [080528F40, 080528F44) = 00000000 AFTER:  [080528F40, 080528F44) = 00000001 --//session 1: --//该语句的第2次执行： SCOTT@book> select * from dept where deptno=20; --//再次挂起。 --//session 2: SYS@book> @ wait P1RAW            P2RAW            P3RAW                    P1         P2         P3        SID    SERIAL#       SEQ# EVENT                                    STATUS   STATE               WAIT_TIME_MICRO SECONDS_IN_WAIT WAIT_CLASS           CLIENT_INFO ---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- -------- ------------------- --------------- --------------- -------------------- -------------------- 000000006010D860 0000000000000150 00               1611716704        336          0          1       6585      30902 latch: shared pool                       ACTIVE   WAITING                     2224269               2 Concurrency 000000006010D860 0000000000000150 00               1611716704        336          0         15          3      56297 latch: shared pool                       ACTIVE   WAITING                    15761568              16 Concurrency --//持有latch: shared pool SYS@book> oradebug poke 0x000000006010D860 4 0x00000000 BEFORE: [06010D860, 06010D864) = 00000001 AFTER:  [06010D860, 06010D864) = 00000000 SYS@book> @ wait P1RAW            P2RAW            P3RAW                    P1         P2         P3        SID    SERIAL#       SEQ# EVENT                                    STATUS   STATE               WAIT_TIME_MICRO SECONDS_IN_WAIT WAIT_CLASS           CLIENT_INFO ---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- -------- ------------------- --------------- --------------- -------------------- -------------------- 00000000000190FA 0000000000000001 000000000000003E     102650          1         62          1       6585      48167 library cache: mutex X                   ACTIVE   WAITING                         879               0 Concurrency --//持有library cache: mutex X. SYS@book> oradebug poke 0x0000000080528f40 4 0x00000000 BEFORE: [080528F40, 080528F44) = 00000001 AFTER:  [080528F40, 080528F44) = 00000000 --//session 1: SCOTT@book> select * from dept where deptno=20; DEPTNO DNAME    LOC ------ -------- ------     20 RESEARCH DALLAS --//session 2: SYS@book> select * from v$mutex_sleep_history where mutex_identifier=102650 order by sleep_timestamp desc   2  @ prxx ============================== MUTEX_IDENTIFIER              : 102650 SLEEP_TIMESTAMP               : 2021-05-20 09:53:25.089384 MUTEX_TYPE                    : Library Cache GETS                          : 2348 SLEEPS                        : 31094348 REQUESTING_SESSION            : 1 BLOCKING_SESSION              : 0 LOCATION                      : kglhdgn1  62 MUTEX_VALUE                   : 0000000000000001 P1                            : 0 P1RAW                         : 00 P2                            : 0 P3                            : 0 P4                            : 0 P5                            : ============================== SYS@book> oradebug peek 0x80528f40 40 [080528F40, 080528F68) = 00000000 00000000 0000092D 000421D8 000190FA 00000000 80528F58 00000000 80528F58 00000000 --// 92D = 2349,我估计保存gets的数量. --//重复测试： --//session 2: SYS@book> oradebug poke 0x000000006010D860 4 0x00000001 BEFORE: [06010D860, 06010D864) = 00000000 AFTER:  [06010D860, 06010D864) = 00000001 SYS@book> oradebug poke 0x0000000080528f40 4 0x00000001 BEFORE: [080528F40, 080528F44) = 00000000 AFTER:  [080528F40, 080528F44) = 00000001 --//session 1,该语句的第3次执行： SCOTT@book> select * from dept where deptno=20; --//再次挂起。 --//session 2: SYS@book> @ wait P1RAW            P2RAW            P3RAW                    P1         P2         P3        SID    SERIAL#       SEQ# EVENT                                    STATUS   STATE               WAIT_TIME_MICRO SECONDS_IN_WAIT WAIT_CLASS           CLIENT_INFO ---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- -------- ------------------- --------------- --------------- -------------------- -------------------- 000000006010D860 0000000000000150 00               1611716704        336          0          1       6585      31022 latch: shared pool                       ACTIVE   WAITING                     4849339               5 Concurrency ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 000000006010D860 0000000000000150 00               1611716704        336          0         15          3      56359 latch: shared pool                       ACTIVE   WAITING                    15238187              15 Concurrency 000000006010D860 0000000000000150 00               1611716704        336          0        211          1       5215 latch: shared pool                       ACTIVE   WAITING                    10285743              10 Concurrency --//持有latch: shared pool SYS@book> oradebug poke 0x000000006010D860 4 0x00000000 BEFORE: [06010D860, 06010D864) = 00000001 AFTER:  [06010D860, 06010D864) = 00000000 SYS@book> @ wait P1RAW            P2RAW            P3RAW                    P1         P2         P3        SID    SERIAL#       SEQ# EVENT                                    STATUS   STATE               WAIT_TIME_MICRO SECONDS_IN_WAIT WAIT_CLASS           CLIENT_INFO ---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- -------- ------------------- --------------- --------------- -------------------- -------------------- 00000000000190FA 0000000000000001 000000000000003E     102650          1         62          1       6585      12835 library cache: mutex X                   ACTIVE   WAITED SHORT TIME                 3               0 Concurrency --//持有library cache: mutex,注意一些细节,这里是library cache: mutex X. SYS@book> oradebug poke 0x0000000080528f40 4 0x00000000 BEFORE: [080528F40, 080528F44) = 00000001 AFTER:  [080528F40, 080528F44) = 00000000 SYS@book> @ wait no rows selected --//session 1: SCOTT@book> select * from dept where deptno=20; DEPTNO DNAME    LOC ------ -------- ------     20 RESEARCH DALLAS --//重复测试： --//session 3: SYS@book> oradebug poke 0x000000006010D860 4 0x00000001 BEFORE: [06010D860, 06010D864) = 00000000 AFTER:  [06010D860, 06010D864) = 00000001 SYS@book> oradebug poke 0x0000000080528f40 4 0x00000001 BEFORE: [080528F40, 080528F44) = 00000000 AFTER:  [080528F40, 080528F44) = 00000001 --//session 1,该语句的第4次执行： SCOTT@book> select * from dept where deptno=20; DEPTNO DNAME    LOC ------ -------- ------     20 RESEARCH DALLAS --//ok，这次不需要任何latch: shared pool以及library cache: mutex x或者library cache: mutex. --//以为第3次后光标已经缓存了。绕过了这两个等待事件。 总结： --//不小心又写的很长，我总喜欢把一些细节记录下来，这样我自己以后还能看懂。 --//很明显前面3次出现都需要遇到这两个等待事件， --//第1次library cache: mutex X，再持有latch: shared pool --//第2，3次先持有latch: shared pool，然后才是library cache: mutex X. --//另外从11g开始oracle大量使用mutex代替latch，muext无处不在，而v$mutex_sleep_history ，v$mutex_sleep中locate的缩写有时候真看不懂 --//一旦遇到muext问题，诊断会变得很困难。也许查询底层视图x$mutex_sleep_history，知道MUTEX_ADDR很关键。 --//另外这样测试有点繁琐，不小心导致挂起，看来给使用gdb来调式。 SYS@book> select * from x$mutex_sleep_history where mutex_identifier=102650 order by sleeps desc   2  @ prxx ============================== ADDR                          : 00007F0A6CA6D490 INDX                          : 244 INST_ID                       : 1 MUTEX_ADDR                    : 0000000080528F40 MUTEX_IDENTIFIER              : 102650 SLEEP_TIMESTAMP               : 2021-05-20 09:59:17.085644 MUTEX_TYPE                    : Library Cache MUTEX_TYPE_ID                 : 4 GETS                          : 2349 SLEEPS                        : 33710600 REQUESTING_SESSION            : 1 BLOCKING_SESSION              : 0 LOCATION_ID                   : 62 LOCATION                      : kglhdgn1  62 MUTEX_VALUE                   : 0000000000000001 P1                            : 0 P1RAW                         : 00 P2                            : 0 P3                            : 0 P4                            : 0 P5                            : PL/SQL procedure successfully completed.