[20220308]查询x$ksmmem遇到的疑问.txt

来源：这里教程网时间：2026-03-03 17:30:10 作者：

[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的一些算法有问题。

编辑推荐：

返回列表

[20220308]查询x$ksmmem遇到的疑问.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推