[20210412]分析會話佔用的共享記憶體段.txt

lfree發表於2021-04-12

[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 ( )

 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 ( )

  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

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2767599/,如需轉載,請註明出處,否則將追究法律責任。

相關文章