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

[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;