[20230216]奇怪的高邏輯讀3.txt
[20230216]奇怪的高邏輯讀3.txt
--//昨天在生產系統看到一條邏輯讀很高的sql語句,檢查發現謂詞使用了DBMS_LOB.SUBSTR函式.
--//開發真奇葩,我無論如何也想不出開發為什麼這些寫程式碼,當時腦子短路了,使用trunc還好理解一點.
--//我開始以為主要原因是lob型別導致fetch記錄是1行1行提取,邏輯讀提高,但是實測的效果要高出許多倍.
--//在測試環境模擬探究看看:
1.環境:
SCOTT@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
2.建立測試例子:
create table t as select * from all_objects <=100;
@ gts t '' ''
SCOTT@book> @ desc_proc sys dbms_lob substr
INPUT OWNER PACKAGE_NAME OBJECT_NAME
sample : @desc_proc sys dbms_stats gather_%_stats
OWNER PACKAGE_NAME OBJECT_NAME SEQUENCE ARGUMENT_NAME DATA_TYPE IN_OUT DEFAULTED
---------- ------------ ----------- -------- ------------- --------- --------- ----------
SYS DBMS_LOB SUBSTR 1 RAW OUT N
2 LOB_LOC BLOB IN N
3 AMOUNT NUMBER IN Y
4 OFFSET NUMBER IN Y
1 VARCHAR2 OUT N
2 LOB_LOC CLOB IN N
3 AMOUNT NUMBER IN Y
4 OFFSET NUMBER IN Y
1 RAW OUT N
2 FILE_LOC BFILE IN N
3 AMOUNT NUMBER IN Y
4 OFFSET NUMBER IN Y
12 rows selected.
--//dbms_lob.substr函式引數與substr不一樣,第2個引數AMOUNT表示取字串的數量,第3個引數OFFSET表示字串的偏移量.
--//注意最後一列DEFAULTED表明可以不輸入第2,3引數(Y),不知道預設怎麼值.可以根據下面的執行推斷offset預設值=1.
3.測試:
SCOTT@book> @ sl all
alter session set statistics_level = all;
Session altered.
SCOTT@book> select * from t where dbms_lob.substr(object_name)='DEPT';
no rows selected
--//第1次執行:
Plan hash value: 1601196873
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 0 |00:00:00.07 | 3081 | 8 |
|* 1 | TABLE ACCESS FULL| T | 1 | 1 | 75 | 3 (0)| 00:00:01 | 0 |00:00:00.07 | 3081 | 8 |
-----------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DBMS_LOB"."SUBSTR"(INTERNAL_FUNCTION("OBJECT_NAME"))='DEPT')
--//第1次執行不穩定buffers=3081,估計存在一些遞規,以後執行都是1298.
--//第2次執行:
Plan hash value: 1601196873
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 0 |00:00:00.02 | 1298 |
|* 1 | TABLE ACCESS FULL| T | 1 | 1 | 75 | 3 (0)| 00:00:01 | 0 |00:00:00.02 | 1298 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DBMS_LOB"."SUBSTR"(INTERNAL_FUNCTION("OBJECT_NAME"))='DEPT')
select * from t where dbms_lob.substr(object_name,30,1)='DEPT';
select * from t where dbms_lob.substr(object_name,30.0,1.0)='DEPT';
Plan hash value: 1601196873
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 0 |00:00:00.02 | 1298 |
|* 1 | TABLE ACCESS FULL| T | 1 | 1 | 75 | 3 (0)| 00:00:01 | 0 |00:00:00.02 | 1298 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DBMS_LOB"."SUBSTR"(INTERNAL_FUNCTION("OBJECT_NAME"),30,1)='DEPT')
--//如果執行如下:
select t.*,dbms_lob.substr(object_name) from t
Plan hash value: 1601196873
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 100 |00:00:00.01 | 4 |
| 1 | TABLE ACCESS FULL| T | 1 | 100 | 7500 | 3 (0)| 00:00:01 | 100 |00:00:00.01 | 4 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
--//僅僅4個邏輯讀.
SCOTT@book> select rowid from t where rownum<=1;
ROWID
------------------
AABSzYAAEAAAAijAAA
SCOTT@book> @ rowid AABSzYAAEAAAAijAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
339160 4 2211 0 0x10008A3 4,2211 alter system dump datafile 4 block 2211
--//記錄資料塊的dba地址,後面有用!!
4.開啟跟蹤:
SCOTT@book> alter system set "_trace_pin_time"=1 scope=spfile;
System altered.
--//重啟資料庫略.
SCOTT@book> select * from t where dbms_lob.substr(object_name)='DEPT';
no rows selected
--//講跟蹤檔案內容複製到20230217.txt
$ awk '{print $2}' 20230217.txt | sort | uniq -c | sort -rn
1210 release
301 ktswh133:
201 ktswh102:
101 ktswh100:
100 ktswh134:
100 ktswh123:
100 ktswh111:
100 ktswh110:
100 ktswh108:
100 kdlwh01:
100 kdlwh00:
2 kdswh11: --//訪問表
1 ktswh130:
1 ktswh129:
1 ktswh115:
1 ktswh114:
1 ktswh107:
1 ktswh105:
1 ktswh101:
1 ktewh26: --//訪問表
$ grep 0x10008a 20230217.txt
pin ktewh26: kteinpscan dba 0x10008a2:4 time 2251552903
pin kdswh11: kdst_fetch dba 0x10008a3:1 time 2251553064
pin kdswh11: kdst_fetch dba 0x10008a4:1 time 2251602100
--//掃描表對應塊僅僅3次.
--//問題在於ktswhNNN,取出pin release行中dba值,執行如下:
$ grep "pin release" 20230217.txt | awk '{print $7}' | sort | uniq -c
605 0x404b00:4
203 0x404b01:12
302 0x404b02:11
50 0x404b03:1
50 0x404b04:1
--//605+203+302+50+50 = 1210
--//0x404b00 = set dba 1,19200 = alter system dump datafile 1 block 19200 = 4213504
--//0x404b04 = set dba 1,19204 = alter system dump datafile 1 block 19204 = 4213508
--//也就是反覆訪問dba = 1,19200 1,19201 1,19202 1,19203 1,19204
--//dba冒號後面的數字表示怎麼不知道?
SCOTT@book> @ which_obj 1 19200
SCOTT@book> @ pr
==============================
OWNER : SYS
SEGMENT_NAME : ARGUMENT$
PARTITION_NAME :
SEGMENT_TYPE : TABLE
TABLESPACE_NAME : SYSTEM
EXTENT_ID : 18
FILE_ID : 1
BLOCK_ID : 19200
BYTES : 1048576
BLOCKS : 128
RELATIVE_FNO : 1
PL/SQL procedure successfully completed.
--//也就是反覆查詢SYS.ARGUMENT$獲取對應支援需要那些引數嗎?
SELECT distinct DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid)
FROM sys.argument$ a
WHERE procedure$='SUBSTR'
AND
argument IN ('LOB_LOC', 'AMOUNT', 'OFFSET');
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
10247
--//塊對不上!!
SELECT DISTINCT OBJ#, PROCEDURE$
FROM sys.argument$
WHERE ROWID BETWEEN 'AAAADfAABAAAEsAAAA' AND 'AAAADfAABAAAEsEDDD';
OBJ# PROCEDURE$
---- -------------
9148 FS_GETXATTR
9148 FS_READDIR
9148 FS_RELEASEDIR
9148 FS_TRUNCATE
9148 FS_MOVE
9148 FS_OPENDIR
9148 FS_CHMOD
9148 FS_CHOWN
9148 FS_REMOVEXATTR
9148 FS_SETXATTR
9148 FS_MKDIR
9148 FS_MKNOD
9148 FS_OPEN
9148 FS_UTIME
9148 FS_WRITE
9148 FS_FSYNC
9148 FS_SYMLINK
9148 FS_DESTROY
9148 FS_RELEASE
9148 FS_STATFS
9148 FS_READ
9148 FS_FLUSH
9148 FS_FSYNCDIR
9148 FS_CREAT
9148 FS_INIT
9148 FS_LINK
9148 FS_RENAME
9148 FS_RMDIR
9148 FS_LISTXATTR
9148 FS_UNLINK
9148 FS_ACCESS
31 rows selected.
--//似乎是底層的一些函式.
SCOTT@book> @ sl all
alter session set statistics_level = all;
Session altered.
SCOTT@book> select dbms_lob.substr(object_name) ,t.* from t ;
SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 031na24n0u8y7, child number 1
-------------------------------------
select dbms_lob.substr(object_name) ,t.* from t
Plan hash value: 1601196873
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 100 |00:00:00.01 | 4 |
| 1 | TABLE ACCESS FULL| T | 1 | 100 | 7500 | 3 (0)| 00:00:01 | 100 |00:00:00.01 | 4 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
--//這樣查詢僅僅4個邏輯讀.
SCOTT@book> select * from (select dbms_lob.substr(object_name) xx,t.* from t) where xx='DEPT';
no rows selected
SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 9vwbhftau3207, child number 0
-------------------------------------
select * from (select dbms_lob.substr(object_name) xx,t.* from t) where
xx='DEPT'
Plan hash value: 1601196873
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 0 |00:00:00.05 | 1298 |
|* 1 | TABLE ACCESS FULL| T | 1 | 1 | 75 | 3 (0)| 00:00:01 | 0 |00:00:00.05 | 1298 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$F5BB74E1 / T@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DBMS_LOB"."SUBSTR"(INTERNAL_FUNCTION("OBJECT_NAME"))='DEPT')
--//放棄探究!!總之不知道開發不知道為什麼這樣寫程式碼.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2936071/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20231124]奇怪的高邏輯讀4.txt
- 漏洞小總結：瀏覽器裡那些奇怪的邏輯瀏覽器
- [20180425]為什麼走索引邏輯讀反而高.txt索引
- oracle邏輯讀過程Oracle
- 《底層邏輯》讀後感
- sql生成可讀性邏輯圖SQL
- 在Oracle中，什麼是物理讀和邏輯讀？Oracle
- 適當調大arraysize減少邏輯讀
- [20210224]fetch r=0算邏輯讀嗎.txt
- [20210220]gdb跟蹤邏輯讀2.txt
- 邏輯STANDBY負載高，應用緩慢的解決負載
- 深度解讀GaussDB邏輯解碼技術原理
- MySQL調優篇 | 邏輯架構解讀（1）MySql架構
- [20210301]為什麼邏輯讀這麼多.txt
- [20210219]全表掃描邏輯讀問題.txt
- 一次調整arraysize減少邏輯讀
- [20210220]全索引掃描快速索引掃描的邏輯讀.txt索引
- 邏輯題
- 讀人工智慧全傳06邏輯程式設計人工智慧程式設計
- 幾道經典邏輯推理題，提高你的邏輯思考能力
- 理解ProcessFunction的Timer邏輯Function
- 掌握 Promise 的邏輯方法Promise
- 基於UNIX系統，邏輯故障的資料災難解讀
- 賽靈思（Xilinx）BlockRam（Bram）的結構與讀邏輯解釋BloC
- 《金子塔原理》讀書筆記之解決問題的邏輯筆記
- 邏輯運算子
- 邏輯推理20201028
- 量子邏輯閘
- 邏輯迴歸邏輯迴歸
- java邏輯控制Java
- [20180410]為什麼2個邏輯讀不一樣.txt
- SAP Spartacus home 頁面讀取 product 資料的請求的 population 邏輯
- 從《英雄聯盟》手遊成績看解讀資料的邏輯
- Python中的邏輯表示式Python
- kubebuilder operator的執行邏輯UI
- 認清 React 的useState邏輯React
- JS 中的邏輯運算子JS
- 3.2.2的Model的快取邏輯快取