[20220309]查询x$ksmmem遇到的疑问补充.txt

--//在昨天的查询x$ksmmem遇到的疑问,当天晚上仔细看了早上写的内容,可能我忽略查询的顺序,补充测试看看.

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.测试:
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

--//我没有贴出memalloc脚本,实际我的环境oracle内存手工设置,不会出现MIN(BASEADDR),MAX(BASEADDR)之间DEFAULT buffer cache出
--//现shared pool或者其他组件的情况.

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
--//注：开始查询内存组件的前面2条记录,可以发现需要15秒,比我前面的测试12.XX秒时间要长.
--//我开始的理解addr从低端向高端扫描,遇到满足条件时(也就是addr在查询范围时),再去从某种方式获取select字段信息,
--//这样获取INDX,INST_ID不需要太多时间,我仅仅返回2条记录,其他根本无法满足查询条件,我的理解应该查询更快才对,而实际的测试
--//结果更慢,这也是我感觉oracle的一些算法有问题的原因.

SYS@book> SELECT rownum ,inst_id,indx,addr, KSMMMVAL FROM X$KSMMEM WHERE addr <=hextoraw('0000000060C00008') and addr >= hextoraw('0000000060C00000');
    ROWNUM    INST_ID       INDX ADDR             KSMMMVAL
---------- ---------- ---------- ---------------- ----------------
         1          1    1572864 0000000060C00000 00
         2          1    1572865 0000000060C00008 00
Elapsed: 00:00:12.59
--//注意看我仅仅将后面的查询条件换了一个位置，addr <=hextoraw('0000000060C00008')写在前面，查询时间就少了将近3秒。

--//看看与select是否相关。
SYS@book> @ desc X$KSMMEM
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      ADDR                                     RAW(8)
    2      INDX                                     NUMBER
    3      INST_ID                                  NUMBER
    4      KSMMMVAL                                 RAW(8)
--//ADDR在前面。

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

SYS@book> SELECT rownum ,x$ksmmem.* FROM X$KSMMEM WHERE addr <=hextoraw('0000000060C00008') and addr >= hextoraw('0000000060C00000');
    ROWNUM ADDR                   INDX    INST_ID KSMMMVAL
---------- ---------------- ---------- ---------- ----------------
         1 0000000060C00000    1572864          1 00
         2 0000000060C00008    1572865          1 00
Elapsed: 00:00:12.70
--//无关，差异还是出现谓词条件上。换成between测试看看。

SYS@book> SELECT rownum ,x$ksmmem.* FROM X$KSMMEM WHERE addr between hextoraw('0000000060C00000') and hextoraw('0000000060C00008');
    ROWNUM ADDR                   INDX    INST_ID KSMMMVAL
---------- ---------------- ---------- ---------- ----------------
         1 0000000060C00000    1572864          1 00
         2 0000000060C00008    1572865          1 00
Elapsed: 00:00:15.42
--//15秒。回到前面的情况。

--//SELECT rownum ,x$ksmmem.* FROM X$KSMMEM WHERE addr <=hextoraw('0000000060C00008') and addr >= hextoraw('0000000060C00000');
--//也就是这样理解，oracle先看条件addr <=hextoraw('0000000060C00008')，然后取出字段，再看条件addr >= hextoraw('0000000060C00000')，
--//只有这样才能解析上面看到的现象。两者执行计划看到的filter不同。
2 - filter(("ADDR"<=HEXTORAW('0000000060C00008')  AND
       "ADDR">=HEXTORAW('0000000060C00000') ))

2 - filter(("ADDR">=HEXTORAW('0000000060C00000')  AND
    "ADDR"<=HEXTORAW('0000000060C00008') ))


3.附上memalloc.sql脚本:
$ cat memalloc.sql
col component format a32
select min(BASEADDR), max(BASEADDR), count(1) Granules, sum(a.gransize)/1048576 MB, a.GRANFLAGS, component, a.GRANSTATE
from x$ksmge a, x$kmgsct b
where a.grantype = b.grantype (+)
group by a.GRANFLAGS, component, a.GRANSTATE
order by 1,2;