[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博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2867593/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
