[20220308]查詢x$ksmmem遇到的疑問.txt
[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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [20220309]查詢x$ksmmem遇到的疑問補充.txt
- [20220304]測試library cache mutex遇到的疑問.txtMutex
- [20231017]使用dbms_xplan.display_awr查詢遇到的問題.txt
- [20190312]關於增量檢查點的疑問(補充).txt
- [20190321]測試相同語句遇到導致cursor pin S的疑問.txt
- [20191209]降序索引疑問.txt索引
- [20190401]那個更快的疑問.txt
- Vue學習遇到疑問的總結Vue
- [20210208]lob欄位與查詢的問題.txt
- [20210418]查詢v$檢視問題.txt
- [20190411]linux stat 命令疑問.txtLinux
- [20211220]關於標量子查詢問題.txt
- [20190510]rman備份的疑問8.txt
- [20190510]rman備份的疑問7.txt
- [20190509]rman備份的疑問5.txt
- [20190423]那個更快的疑問3.txt
- [20191209]降序索引疑問2.txt索引
- [20200303]降序索引疑問5.txt索引
- [20191210]降序索引疑問3.txt索引
- [20191218]降序索引疑問4.txt索引
- [20221014]資料檔案2的小疑問.txt
- [20191213]不完全恢復疑問.txt
- [20190306]奇怪的查詢結果.txt
- [20211210]優化遇到的奇怪問題.txt優化
- [20221125]設定hugepages遇到的問題.txt
- [20230308]12c以上版本模糊查詢問題.txt
- [20180926]查詢相似索引.txt索引
- [20201207]關於ORACLE IMU的一些疑問.txtOracle
- [20210926]並行執行計劃疑問.txt並行
- [20211221]分析sql語句遇到的問題.txtSQL
- [20200325]慎用標量子查詢.txt
- [20190524]淺談模糊查詢.txt
- [20220406]使用那個shared pool latch的疑問1.txt
- MySQL like查詢字元轉義遇到的坑MySql字元
- 關於Room資料庫,拼寫模糊查詢語句遇到的問題OOM資料庫
- 同一欄位多個查詢條件時遇到的一個問題
- [20240309]在windwos下使用sed遇到的問題.txt
- [20220412]shared pool latch與使用sga heap的疑問2.txt