[20210412]分析会话占用的共享内存段.txt

--//以前学习oracle，总是提到设置过大session_cached_cursors值会导致使用shared pool消耗过大。
--//总是想了解每个回话到底会消耗多少共享内存段，限于自己当时的能力，一直没做这方面的探究。
--//看了一些资料，做一些探究，首先说说明受限我的学习环境，对于许多东西完全是一知半解，一些可能仅仅是我的猜测。

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

--//建立执行脚本aa.sql
$ echo 'alter session set session_cached_cursors=200;' > aa.sql
$ echo set term off >> aa.sql
$ seq  200 | xargs -IQ echo 'select * from dept where deptno=Q;' >> aa.sql
$ echo set term on >> aa.sql
--//以scott用户登陆，不执行任何命令。我的测试环境很干净，很容易通过别的方式确定回话的SID=142。

2.分析：
--//首先每个共享内存段的使用在x$ksmsp上都有记录,而回话占用shared pool的chunk在x$ksmsp.KSMCHCOM标识为KKSSP^NNNN,其中的NNNN表示为
--//回话的SID。
--//以sys用户登陆打开新的session 2：
SYS@book> select * from x$ksmsp where ksmchcom='KKSSP^142' ;
ADDR                   INDX    INST_ID   KSMCHIDX   KSMCHDUR KSMCHCOM         KSMCHPTR           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007FECF9A579B8        930          1          1          1 KKSSP^142        000000007EA43E78       2136 freeabl           0 000000007CB7C610
00007FECF9A47388       1638          1          1          1 KKSSP^142        000000007E34ED88       2136 freeabl           0 000000007CB7C610
00007FECF9A33B30       3551          1          1          1 KKSSP^142        000000007DFBFA70       2136 freeabl           0 000000007CB7C610
00007FECF9A22750       4206          1          1          1 KKSSP^142        000000007DEA4568       2136 freeabl           0 000000007CB7C610
00007FECF99F5670       4987          1          1          1 KKSSP^142        000000007DD67DC0       2136 freeabl           0 000000007CB7C610
00007FECF99FD6D8       5356          1          1          1 KKSSP^142        000000007DCC7028       2136 freeabl           0 000000007CB7C610
00007FECF9A01BB0       5527          1          1          1 KKSSP^142        000000007DC89BD8       2136 freeabl           0 000000007CB7C610
00007FECF9A85408       5684          1          1          1 KKSSP^142        000000007DC5E1A8       2136 freeabl           0 000000007CB7C610
00007FECF9A853B0       5685          1          1          1 KKSSP^142        000000007DC5D950       2136 freeabl           0 000000007CB7C610
00007FECF9A8AB60       5894          1          1          1 KKSSP^142        000000007DBBEA70       2136 freeabl           0 000000007CB7C610
00007FECF9BFE5E8       9545          1          1          1 KKSSP^142        000000007D54F2B8       2136 freeabl           0 000000007CB7C610
00007FECF9BBE3D8      12311          1          1          1 KKSSP^142        000000007CF9FAE0       2136 freeabl           0 000000007CB7C610
00007FECF9BC0098      12413          1          1          1 KKSSP^142        000000007CF565A0       2136 freeabl           0 000000007CB7C610
00007FECF9B9DE68      12924          1          1          1 KKSSP^142        000000007CE26358       2136 freeabl           0 000000007CB7C610
00007FECF9B9E590      12996          1          1          1 KKSSP^142        000000007CE06170       2136 freeabl           0 000000007CB7C610
00007FECF9C994C0      13457          1          1          1 KKSSP^142        000000007CD269F0       2136 freeabl           0 000000007CB7C610
00007FECF9C9D418      13644          1          1          1 KKSSP^142        000000007CCD5A08       2136 recr           4095 000000007CB7C610
00007FECF9B41160      14527          1          1          1 KKSSP^142        000000007CAC1268       2136 freeabl           0 000000007CB7C610
00007FECF9AE1948      15562          1          1          1 KKSSP^142        000000007C8C8C80       2136 freeabl           0 000000007CB7C610
00007FECF9AE12C0      15581          1          1          1 KKSSP^142        000000007C8BAAC8       2136 freeabl           0 000000007CB7C610
00007FECF99F2200      17352          1          1          1 KKSSP^142        000000007C497630       2136 freeabl           0 000000007CB7C610
00007FECF9ACC710      18602          1          1          1 KKSSP^142        000000007C1F64B8       2136 freeabl           0 000000007CB7C610
00007FECF9ACC450      18610          1          1          1 KKSSP^142        000000007C1F2CA0       2136 freeabl           0 000000007CB7C610
00007FECF9B12DE8      19615          1          1          1 KKSSP^142        000000007C00B5E0      12352 R-freea           0 000000007CB7C610
00007FECF999BF00      20724          1          1          1 KKSSP^142        000000007BE3D5E0       2136 freeabl           0 000000007CB7C610
00007FECF9B02960      20990          1          1          1 KKSSP^142        000000007BDD26D0       2136 freeabl           0 000000007CB7C610
00007FECF99B9BE8      21460          1          1          1 KKSSP^142        000000007BCD0E38       2136 freeabl           0 000000007CB7C610
00007FECF99BBE28      21546          1          1          1 KKSSP^142        000000007BC95670       2136 freeabl           0 000000007CB7C610
00007FECF99A3DF0      21621          1          1          1 KKSSP^142        000000007BC6CF68       2136 freeabl           0 000000007CB7C610
29 rows selected.

--//你可以发现KSMCHPAR值都是一样的，相当于堆的描述符。通过它查询结果集也是一样的。
SYS@book> select count(*) from x$ksmsp where KSMCHPAR=hextoraw('000000007CB7C610');
  COUNT(*)
----------
        29

SYS@book> select sum(KSMCHSIZ) from x$ksmsp where ksmchcom='KKSSP^142' ;
SUM(KSMCHSIZ)
-------------
        72160
        
--//做一个转储分析：

SYS@book> @ tix
New tracefile_identifier=/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_6419_0001.trc

SYS@book> oradebug setmypid
Statement processed.
SYS@book> ORADEBUG DUMP HEAPDUMP_ADDR 1 0x000000007CB7C610
Statement processed.

SYS@book> ORADEBUG help DUMP
DUMP           <dump_name> <lvl> [addr]  Invoke named dump
--//简单说明ORADEBUG DUMP HEAPDUMP_ADDR后面的参数lvl=1,仅仅显示chunk，并不转储chunk内容。lvl=2,加上转储chunk的内容。

$ ./heapdump_analyzer /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_6419_0001.trc

 -- Heapdump Analyzer v1.00 by Tanel Poder ( http://www.tanelpoder.com )

 Total_size #Chunks  Chunk_size,        From_heap,       Chunk_type,  Alloc_reason
 ---------- ------- ------------ ----------------- ----------------- -----------------
      17408      68        256 ,        KKSSP^142,         freeable,  kgllk
      13944     249         56 ,        KKSSP^142,         freeable,  kglseshtSegs
      12312       1      12312 ,        KKSSP^142,         freeable,  kglseshtTable
       9472      37        256 ,        KKSSP^142,         freeable,  kglpn
       8064     252         32 ,        KKSSP^142,         freeable,  kglseshtSegs
       2560      10        256 ,        KKSSP^142,         freeable,  KQR ENQ
       1840       1       1840 ,        KKSSP^142,             free,
       1536       6        256 ,        KKSSP^142,         freeable,  kglll
       1064       7        152 ,        KKSSP^142,             free,
        760       1        760 ,        KKSSP^142,         freeable,  kglss
        504       7         72 ,        KKSSP^142,         freeable,  kglseshtSegs
        480      10         48 ,        KKSSP^142,             free,
        304       1        304 ,        KKSSP^142,             perm,  perm
        288       1        288 ,        KKSSP^142,         freeable,  kgllk
        160       4         40 ,        KKSSP^142,         freeable,  kglseshtSegs
         96       1         96 ,        KKSSP^142,             perm,  perm
         80       1         80 ,        KKSSP^142,             perm,  perm
         56       1         56 ,        KKSSP^142,             free,
         48       1         48 ,        KKSSP^142,         freeable,  kglsesht

3.继续：

--//session 1:
--//执行aa.sql脚步5次以上。
SCOTT@book> @ aa.sql
Session altered.

--//session 2：
SYS@book> select count(*) from x$ksmsp where KSMCHPAR=hextoraw('000000007CB7C610');
  COUNT(*)
----------
        65

SYS@book> select sum(KSMCHSIZ) from x$ksmsp where ksmchcom='KKSSP^142' ;
SUM(KSMCHSIZ)
-------------
       149080

SYS@book> @ tix
New tracefile_identifier=/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_6419_0002.trc

SYS@book> ORADEBUG DUMP HEAPDUMP_ADDR 1 0x000000007CB7C610
Statement processed.

 $ ./heapdump_analyzer /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_6419_0002.trc

  -- Heapdump Analyzer v1.00 by Tanel Poder ( http://www.tanelpoder.com )

  Total_size #Chunks  Chunk_size,        From_heap,       Chunk_type,  Alloc_reason
  ---------- ------- ------------ ----------------- ----------------- -----------------
       91392     357        256 ,        KKSSP^142,         freeable,  kgllk
       13944     249         56 ,        KKSSP^142,         freeable,  kglseshtSegs
       12312       1      12312 ,        KKSSP^142,         freeable,  kglseshtTable
        9472      37        256 ,        KKSSP^142,         freeable,  kglpn
        8064     252         32 ,        KKSSP^142,         freeable,  kglseshtSegs
        2560      10        256 ,        KKSSP^142,         freeable,  KQR ENQ
        2064      43         48 ,        KKSSP^142,             free,
        1584       1       1584 ,        KKSSP^142,             free,
        1536       6        256 ,        KKSSP^142,         freeable,  kglll
        1064       7        152 ,        KKSSP^142,             free,
         760       1        760 ,        KKSSP^142,         freeable,  kglss
         504       7         72 ,        KKSSP^142,         freeable,  kglseshtSegs
         304       1        304 ,        KKSSP^142,             perm,  perm
         288       1        288 ,        KKSSP^142,         freeable,  kgllk
         224       4         56 ,        KKSSP^142,             free,
         160       4         40 ,        KKSSP^142,         freeable,  kglseshtSegs
          96       1         96 ,        KKSSP^142,             perm,  perm
          80       1         80 ,        KKSSP^142,             perm,  perm
          48       1         48 ,        KKSSP^142,         freeable,  kglsesht

4.对比分析:

$ diff -Nur <(./heapdump_analyzer /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_6419_0001.trc) \
  <(./heapdump_analyzer /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_6419_0002.trc)

--- /dev/fd/63  2021-04-12 09:23:26.407242026 +0800
+++ /dev/fd/62  2021-04-12 09:23:26.407242026 +0800
@@ -3,23 +3,23 @@

   Total_size #Chunks  Chunk_size,        From_heap,       Chunk_type,  Alloc_reason
   ---------- ------- ------------ ----------------- ----------------- -----------------
-       17408      68        256 ,        KKSSP^142,         freeable,  kgllk
+       91392     357        256 ,        KKSSP^142,         freeable,  kgllk
        13944     249         56 ,        KKSSP^142,         freeable,  kglseshtSegs
        12312       1      12312 ,        KKSSP^142,         freeable,  kglseshtTable
         9472      37        256 ,        KKSSP^142,         freeable,  kglpn
         8064     252         32 ,        KKSSP^142,         freeable,  kglseshtSegs
         2560      10        256 ,        KKSSP^142,         freeable,  KQR ENQ
-        1840       1       1840 ,        KKSSP^142,             free,
+        2064      43         48 ,        KKSSP^142,             free,
+        1584       1       1584 ,        KKSSP^142,             free,
         1536       6        256 ,        KKSSP^142,         freeable,  kglll
         1064       7        152 ,        KKSSP^142,             free,
          760       1        760 ,        KKSSP^142,         freeable,  kglss
          504       7         72 ,        KKSSP^142,         freeable,  kglseshtSegs
-         480      10         48 ,        KKSSP^142,             free,
          304       1        304 ,        KKSSP^142,             perm,  perm
          288       1        288 ,        KKSSP^142,         freeable,  kgllk
+         224       4         56 ,        KKSSP^142,             free,
          160       4         40 ,        KKSSP^142,         freeable,  kglseshtSegs
           96       1         96 ,        KKSSP^142,             perm,  perm
           80       1         80 ,        KKSSP^142,             perm,  perm
-          56       1         56 ,        KKSSP^142,             free,
           48       1         48 ,        KKSSP^142,         freeable,  kglsesht

--//主要变化集中在kgllk。可以发现每个chunk大小256