[20220308]查詢x$ksmmem遇到的疑問.txt

lfree發表於2022-03-08

[20220308]查詢x$ksmmem遇到的疑問.txt

--//前一段時間探究library cache mutex的定位問題,使用了x$ksmmem檢視,當然也可以使用oradebug peek顯示相關資訊。
--//主要顯示的方便,但是我在使用x$ksmmem時遇到一個奇怪的問題,就是顯示500條記錄時明顯感覺出現一個小小的停頓,不知道為什
--//麼,當時主要精力放在探究library cache mutex的問題,沒有在意,今天仔細探究看看。

1.環境:
SYS@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

SYS@book> show array
arraysize 100

SYS@book> show release
release 1102000400
--//sqlplus版本是11.2.0.4、

2.測試:
SELECT rownum-1 rn , x$ksmmem.* FROM X$KSMMEM WHERE addr between hextoraw('00000000807827D8') and hextoraw('00000000807837D0');

--//結果不再貼出就是在顯示rn=499時,出現短暫的停頓,然後顯示剩下的12行。使用10046跟蹤看看。

@ 10046on 12
SELECT rownum-1 rn , x$ksmmem.* FROM X$KSMMEM WHERE addr between hextoraw('00000000807827D8') and hextoraw('00000000807837D0');
@ 10046off

$ grep -i fetch  /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_62869.trc
FETCH #140613649120704:c=10535398,e=10559083,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=2860074530,tim=1646702498610993
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
FETCH #140613649120704:c=0,e=81,p=0,cr=0,cu=0,mis=0,r=100,dep=0,og=1,plh=2860074530,tim=1646702498611444
FETCH #140613649120704:c=0,e=77,p=0,cr=0,cu=0,mis=0,r=100,dep=0,og=1,plh=2860074530,tim=1646702498613184
FETCH #140613649120704:c=0,e=91,p=0,cr=0,cu=0,mis=0,r=100,dep=0,og=1,plh=2860074530,tim=1646702498639698
FETCH #140613649120704:c=0,e=92,p=0,cr=0,cu=0,mis=0,r=100,dep=0,og=1,plh=2860074530,tim=1646702498641822
FETCH #140613649120704:c=0,e=85,p=0,cr=0,cu=0,mis=0,r=100,dep=0,og=1,plh=2860074530,tim=1646702498671499
FETCH #140613649120704:c=2384638,e=2390121,p=0,cr=0,cu=0,mis=0,r=11,dep=0,og=1,plh=2860074530,tim=1646702501090505
--//注意看e=的資訊,你可以最後一行e=2390121,差不多2秒多,而前面第一行的fetch也有10秒多的時間e=10559083。
--//為什麼出現這樣的情況呢?

SYS@book> @ dpc '' ''  ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  bh259hdarv55h, child number 0
-------------------------------------
SELECT rownum-1 rn , x$ksmmem.* FROM X$KSMMEM WHERE addr between
hextoraw('00000000807827D8') and hextoraw('00000000807837D0')
Plan hash value: 2860074530
--------------------------------------------------------------------
| Id  | Operation         | Name     | E-Rows |E-Bytes| Cost (%CPU)|
--------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |        |       |     1 (100)|
|   1 |  COUNT            |          |        |       |            |
|*  2 |   FIXED TABLE FULL| X$KSMMEM |      1 |    38 |     0   (0)|
--------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / X$KSMMEM@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("ADDR">=HEXTORAW('00000000807827D8')  AND
              "ADDR"<=HEXTORAW('00000000807837D0') ))
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level
--//仔細看執行計劃可以發現沒有使用索引。id=2很奇怪operdation=count , 不理解。

SYS@book> select * from V$INDEXED_FIXED_COLUMN where table_name='X$KSMMEM' order by 2 ;
TABLE_NAME           INDEX_NUMBER COLUMN_NAME          COLUMN_POSITION
-------------------- ------------ -------------------- ---------------
X$KSMMEM                        1 ADDR                               0
X$KSMMEM                        2 INDX                               0

--//很奇怪oracle並不使用內部的"索引"查詢,我測試如果使用addr=HEXTORAW('00000000807827D8')是可以使用索
--//引的。為什麼使用between不行呢?而且為什麼出現id2=count operation呢?

SYS@book> select /*+ full(emp) */ * from scott.emp where empno between 7000 and 8000 ;
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20
...
      7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10
14 rows selected.

--//檢視其執行計劃,你可以發現並沒有出現count operation
---------------------------------------------------------------------------
| Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |        |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMP  |     14 |   546 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / EMP@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(("EMPNO">=7000 AND "EMPNO"<=8000))

--//如果你將前面的between修改小點點,剩下的部分還是存在一個小小的停頓。
SELECT /*+ index(X$KSMMEM) */ rownum-1 rn , x$ksmmem.* FROM X$KSMMEM WHERE addr between hextoraw('00000000807827D8') and hextoraw('00000000807831D0');
SELECT /*+ index_asc(X$KSMMEM) */ rownum-1 rn , x$ksmmem.* FROM X$KSMMEM WHERE addr between hextoraw('00000000807827D8') and hextoraw('00000000807831D0');

--//而且執行計劃中一定出現count操作,可以看出慢我估計與count操作有關。
SYS@book> set timing on
SYS@book> @ sl all
alter session set statistics_level = all;
Session altered.
Elapsed: 00:00:00.00

SYS@book> SELECT /*+ INDEX_ASC( x$ksmmem ) */ rownum-1 rn , x$ksmmem.* FROM X$KSMMEM WHERE addr >= hextoraw('00000000807827D8') and addr<=hextoraw('00000000807827E0');
        RN ADDR                   INDX    INST_ID KSMMMVAL
---------- ---------------- ---------- ---------- ----------------
         0 00000000807827D8   68093179          1 00000000807837D8
         1 00000000807827E0   68093180          1 0000000080785FD8
Elapsed: 00:00:12.85

SYS@book> SELECT rownum-1 rn , KSMMMVAL FROM X$KSMMEM WHERE addr >= hextoraw('00000000807827D8') and addr<=hextoraw('00000000807827E0');
        RN KSMMMVAL
---------- ----------------
         0 00000000807837D8
         1 0000000080785FD8
Elapsed: 00:00:05.19

SYS@book> SELECT rownum-1 rn , addr,KSMMMVAL FROM X$KSMMEM WHERE addr >= hextoraw('00000000807827D8') and addr<=hextoraw('00000000807827E0');
        RN ADDR             KSMMMVAL
---------- ---------------- ----------------
         0 00000000807827D8 00000000807837D8
         1 00000000807827E0 0000000080785FD8
Elapsed: 00:00:05.24

SYS@book> SELECT rownum-1 rn , addr,inst_id,KSMMMVAL FROM X$KSMMEM WHERE addr >= hextoraw('00000000807827D8') and addr<=hextoraw('00000000807827E0');
        RN ADDR                INST_ID KSMMMVAL
---------- ---------------- ---------- ----------------
         0 00000000807827D8          1 00000000807837D8
         1 00000000807827E0          1 0000000080785FD8
Elapsed: 00:00:07.40
--//可以發現加入inst_id的查詢就增加2秒。

SYS@book> SELECT /*+ INDEX_ASC( x$ksmmem ) */ rownum-1 rn , addr,indx,inst_id,KSMMMVAL FROM X$KSMMEM WHERE addr >= hextoraw('00000000807827D8') and addr<=hextoraw('00000000807827E0');
        RN ADDR                   INDX    INST_ID KSMMMVAL
---------- ---------------- ---------- ---------- ----------------
         0 00000000807827D8   68093179          1 00000000807837D8
         1 00000000807827E0   68093180          1 0000000080785FD8
Elapsed: 00:00:12.80
--//加入indx列查詢時間增加5秒。也就是查詢X$KSMMEM的內部表不能按照我們平時普通表的訪問方式來理解,要想加快訪問改寫如下:

SYS@book> select vsize(addr)*2 addrlen from x$dual;
   ADDRLEN
----------
        16

WITH a
     AS (    SELECT /*+ MATERIALIZE qb_name(a1)*/
                   HEXTORAW
                    (
                       TO_CHAR
                       (
                            TO_NUMBER ('00000000807827D8', 'xxxxxxxxxxxxxxxx')
                          + (LEVEL - 1) * 8
                         ,'FM0XXXXXXXXXXXXXXX'
                       )
                    )
                       c30
               FROM DUAL
         CONNECT BY LEVEL <= 2)
SELECT /*+  USE1_NL(x$lsmemm) */
      *
  FROM X$KSMMEM, a
 WHERE X$KSMMEM.addr = a.c30;        

ADDR                   INDX    INST_ID KSMMMVAL         C30
---------------- ---------- ---------- ---------------- ------------------------------
00000000807827D8   68093179          1 00000000807837D8 00000000807827D8
00000000807827E0   68093180          1 0000000080785FD8 00000000807827E0
Elapsed: 00:00:00.00

--//執行計劃如下:
Plan hash value: 1568942201
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                        | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                             |      1 |        |       |     4 (100)|          |      2 |00:00:00.01 |      16 |      1 |      1 |       |       |          |
|   1 |  TEMP TABLE TRANSFORMATION     |                             |      1 |        |       |            |          |      2 |00:00:00.01 |      16 |      1 |      1 |       |       |          |
|   2 |   LOAD AS SELECT               |                             |      1 |        |       |            |          |      0 |00:00:00.01 |       4 |      0 |      1 |   270K|   270K|  270K (0)|
|   3 |    CONNECT BY WITHOUT FILTERING|                             |      1 |        |       |            |          |      2 |00:00:00.01 |       0 |      0 |      0 |       |       |          |
|   4 |     FAST DUAL                  |                             |      1 |      1 |       |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       0 |      0 |      0 |       |       |          |
|   5 |   NESTED LOOPS                 |                             |      1 |    100 |  4400 |     2   (0)| 00:00:01 |      2 |00:00:00.01 |       6 |      1 |      0 |       |       |          |
|   6 |    VIEW                        |                             |      1 |      1 |     6 |     2   (0)| 00:00:01 |      2 |00:00:00.01 |       6 |      1 |      0 |       |       |          |
|   7 |     TABLE ACCESS FULL          | SYS_TEMP_0FD9D6634_1EE7C710 |      1 |      1 |     6 |     2   (0)| 00:00:01 |      2 |00:00:00.01 |       6 |      1 |      0 |       |       |          |
|*  8 |    FIXED TABLE FIXED INDEX     | X$KSMMEM (ind:1)            |      2 |    100 |  3800 |     0   (0)|          |      2 |00:00:00.01 |       0 |      0 |      0 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - A1
   4 - A1           / DUAL@A1
   6 - SEL$847CB826 / A@SEL$1
   7 - SEL$847CB826 / T1@SEL$847CB826
   8 - SEL$1        / X$KSMMEM@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   8 - filter("X$KSMMEM"."ADDR"="A"."C30")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - "A"."C30"[RAW,9], "X$KSMMEM"."ADDR"[RAW,8], "X$KSMMEM"."INDX"[NUMBER,22], "X$KSMMEM"."INST_ID"[NUMBER,22],
       "X$KSMMEM"."KSMMMVAL"[RAW,8]
   2 - SYSDEF[4], SYSDEF[0], SYSDEF[1], SYSDEF[96], SYSDEF[0]
   3 - LEVEL[4]
   5 - "A"."C30"[RAW,9], "X$KSMMEM"."ADDR"[RAW,8], "X$KSMMEM"."INDX"[NUMBER,22], "X$KSMMEM"."INST_ID"[NUMBER,22],
       "X$KSMMEM"."KSMMMVAL"[RAW,8]
   6 - "A"."C30"[RAW,9]
   7 - "C0"[RAW,9]
   8 - "X$KSMMEM"."ADDR"[RAW,8], "X$KSMMEM"."INDX"[NUMBER,22], "X$KSMMEM"."INST_ID"[NUMBER,22], "X$KSMMEM"."KSMMMVAL"[RAW,8]
   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--//注意下劃線,與普通的索引訪問不同。

3.繼續:
--//至於全表掃描為什麼出現這樣的情況呢?
SYS@book> @ memalloc
MIN(BASEADDR)    MAX(BASEADDR)      GRANULES         MB  GRANFLAGS COMPONENT                        GRANSTATE
---------------- ---------------- ---------- ---------- ---------- -------------------------------- ----------------
0000000060C00000 000000007A000000        102        408          4 DEFAULT buffer cache             ALLOC
000000007A400000 000000007AC00000          3         12          4 java pool                        ALLOC
000000007B000000 000000007B800000          3         12          4 large pool                       ALLOC
000000007BC00000 0000000086400000         43        172          4 shared pool                      ALLOC

--//你可以發現掃描的區域位於shared pool。這樣前面出現e很大就很正常了,oracle在顯示時11.2.0.4的版本是按照。
--//fetch 1,arraysize,,,,arraysize,剩下記錄來操作,
--//而顯示按照:  arraysize,,,,arraysize,剩下記錄來顯示。
--//即使上最後一個fetch很慢,主要原因是全表掃描剩下的記錄。
--//至於前面出現count操作,我認為查詢該表存在INDX有關,我的測試即使顯示不包含INDX欄位,前面的全表掃描也有count操作。
--//實際上可以猜測記憶體中不可能存在這樣形式的索引,INDX是查詢時構造出來的,甚至INST_ID也是這樣的情況。
--//這僅僅是我的猜測,不然無法解析如下的情況。

SYS@book> SELECT rownum-1 rn , KSMMMVAL FROM X$KSMMEM WHERE addr >= hextoraw('00000000807827D8') and addr<=hextoraw('00000000807827E0');
        RN KSMMMVAL
---------- ----------------
         0 00000000807837D8
         1 0000000080785FD8
Elapsed: 00:00:05.19

SYS@book> SELECT rownum-1 rn , addr,KSMMMVAL FROM X$KSMMEM WHERE addr >= hextoraw('00000000807827D8') and addr<=hextoraw('00000000807827E0');
        RN ADDR             KSMMMVAL
---------- ---------------- ----------------
         0 00000000807827D8 00000000807837D8
         1 00000000807827E0 0000000080785FD8
Elapsed: 00:00:05.24

SYS@book> SELECT rownum-1 rn , addr,inst_id,KSMMMVAL FROM X$KSMMEM WHERE addr >= hextoraw('00000000807827D8') and addr<=hextoraw('00000000807827E0');
        RN ADDR                INST_ID KSMMMVAL
---------- ---------------- ---------- ----------------
         0 00000000807827D8          1 00000000807837D8
         1 00000000807827E0          1 0000000080785FD8
Elapsed: 00:00:07.40
--//可以發現加入inst_id的查詢就增加2秒。

SYS@book> SELECT rownum-1 rn , addr,indx,inst_id,KSMMMVAL FROM X$KSMMEM WHERE addr >= hextoraw('00000000807827D8') and addr<=hextoraw('00000000807827E0');
        RN ADDR                   INDX    INST_ID KSMMMVAL
---------- ---------------- ---------- ---------- ----------------
         0 00000000807827D8   68093179          1 00000000807837D8
         1 00000000807827E0   68093180          1 0000000080785FD8
Elapsed: 00:00:12.80
--//加入indx列查詢時間增加5秒。

--//你也可以查詢最開始部分:
SYS@book> set timing on
SYS@book> SELECT rownum , KSMMMVAL FROM X$KSMMEM WHERE addr >= hextoraw('0000000060C00000') and addr<=hextoraw('0000000060C00008');
    ROWNUM KSMMMVAL
---------- ----------------
         1 00
         2 00
Elapsed: 00:00:07.83

SYS@book> SELECT rownum ,inst_id, KSMMMVAL FROM X$KSMMEM WHERE addr >= hextoraw('0000000060C00000') and addr<=hextoraw('0000000060C00008');
    ROWNUM    INST_ID KSMMMVAL
---------- ---------- ----------------
         1          1 00
         2          1 00
Elapsed: 00:00:10.01

SYS@book> SELECT rownum ,inst_id,indx, KSMMMVAL FROM X$KSMMEM WHERE addr >= hextoraw('0000000060C00000') and addr<=hextoraw('0000000060C00008');
    ROWNUM    INST_ID       INDX KSMMMVAL
---------- ---------- ---------- ----------------
         1          1    1572864 00
         2          1    1572865 00
Elapsed: 00:00:15.54

SYS@book> SELECT rownum ,inst_id,indx,addr, KSMMMVAL FROM X$KSMMEM WHERE addr >= hextoraw('0000000060C00000') and addr<=hextoraw('0000000060C00008');
    ROWNUM    INST_ID       INDX ADDR             KSMMMVAL
---------- ---------- ---------- ---------------- ----------------
         1          1    1572864 0000000060C00000 00
         2          1    1572865 0000000060C00008 00
Elapsed: 00:00:15.44

--//注:開始查詢前面部分這樣INDX,INST_ID全部都要構造出來,執行時間相對較長,感覺oracle的一些演算法有問題。

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

相關文章