[20191106]隱式轉換.txt
[20191106]隱式轉換.txt
--//生產系統出現效能問題,原來看的連結如下:
http://blog.itpub.net/267265/viewspace-2638863/=>[20190320]關於使用smem檢視記憶體使用的問題.txt
--//我在使用smem測試記憶體使用時認為,對於OLTP系統在使用HugePages的前提下.每個會話平均消耗PSS不會過5M.
--//看來這個前提要好加上一條裡面不能存在大量不良sql語句,才可能達到每個會話平均消耗PSS不會過5M.
--//上面的系統當時出現短暫的IO問題.我當時就隱約遇到這套系統以後會頻繁出現問題.果然不出所料,6.7,8月頻繁出現
--//執行緩慢,監聽中斷的情況.
--//採用HugePages後的情況如下:
# smem -tk -U oracle -P oraclepeis
PID User Command Swap USS PSS RSS
28605 oracle oraclepeis (LOCAL=NO) 4.2M 4.0K 19.0K 3.5M
3956 oracle oraclepeis (LOCAL=NO) 3.4M 232.0K 286.0K 14.1M
4971 oracle oraclepeis (LOCAL=NO) 3.0M 1.7M 1.9M 22.0M
8198 oracle oraclepeis (LOCAL=NO) 3.0M 1.9M 2.0M 6.1M
6554 oracle oraclepeis (LOCAL=NO) 1.6M 2.0M 2.0M 15.9M
4245 oracle oraclepeis (LOCAL=NO) 1.6M 2.0M 2.0M 15.9M
3906 oracle oraclepeis (LOCAL=NO) 2.9M 2.5M 2.6M 23.5M
4130 oracle oraclepeis (LOCAL=NO) 2.0M 2.8M 2.9M 22.4M
6180 oracle oraclepeis (LOCAL=NO) 320.0K 3.3M 3.3M 17.2M
5481 oracle oraclepeis (LOCAL=NO) 212.0K 3.4M 3.5M 17.3M
4973 oracle oraclepeis (LOCAL=NO) 124.0K 3.5M 3.5M 17.4M
8385 oracle oraclepeis (LOCAL=NO) 1.3M 3.5M 3.6M 11.5M
17092 oracle oraclepeis (LOCAL=NO) 0 3.6M 3.6M 17.4M
..
16205 oracle oraclepeis (LOCAL=NO) 0 5.6M 5.8M 27.8M
16032 oracle oraclepeis (LOCAL=NO) 0 5.4M 5.9M 16.4M
15304 oracle oraclepeis (LOCAL=NO) 0 6.4M 6.9M 19.1M
-------------------------------------------------------------------------------
238 1 27.9M 970.0M 993.0M 4.8G
--//PSS=993M,933/238 = 4.17M,swap才使用27.9M.當前系統相對空閒...
--//並且還打了p6139856_10204_Linux-x86-64.zip補丁,避免監聽中斷.
--//但是使用者還是抱怨執行緩慢,我叫同事看awr報表看看sql語句問題,可惜一直沒人看.今天正好看一下.實際上我現在登入伺服器都明顯
--//感覺反應遲鈍.一些os命令第一次執行也會很慢.
1.環境:
SYSTEM@192.168.22.222:1521/peis> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
2.發現問題:
SYSTEM@192.168.22.222:1521/peis> @ sql_id 5whcb1qwww6x1
SQL_ID SQLTEXT
------------- -------------------------------------------------------------------------------------------------------
5whcb1qwww6x1 select count ( *) from pe_visit where pe_level ='二樓體檢區' and pe_pre_date > :1 and pe_pre_date < :2
view dba_hist_sqltext
SQL_ID SQLTEXT
------------- -------------------------------------------------------------------------------------------------------
5whcb1qwww6x1 select count ( *) from pe_visit where pe_level ='二樓體檢區' and pe_pre_date > :1 and pe_pre_date < :2
--//發現這條語句很簡單,pe_pre_date的索引也存在.
SYSTEM@192.168.22.222:1521/peis> @ dpc 5whcb1qwww6x1 ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 5whcb1qwww6x1, child number 1
-------------------------------------
select count ( *) from pe_visit where pe_level ='二樓體檢區' and pe_pre_date
> :1 and pe_pre_date < :2
Plan hash value: 891755927
---------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 14 | | |
|* 2 | FILTER | | | | | |
|* 3 | TABLE ACCESS FULL| PE_VISIT | 1579 | 22106 | 13210 (1)| 00:02:39 |
---------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / PE_VISIT@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(:1<:2)
3 - filter(("PE_LEVEL"='二樓體檢區' AND
INTERNAL_FUNCTION("PE_PRE_DATE")>:1 AND
INTERNAL_FUNCTION("PE_PRE_DATE")<:2))
--//發現過濾條件很奇怪,怎麼是INTERNAL_FUNCTION("PE_PRE_DATE")>:1,真心講好久沒做最佳化,一時沒反應過來.實際代入引數是timestamp型別.
SYSTEM@192.168.22.222:1521/peis> @ bind_cap 5whcb1qwww6x1 ''
C200
---------------------------------------------------------------------------------------------------------------------------------------------
select count ( *) from pe_visit where pe_level ='二樓體檢區' and pe_pre_date > :1 and pe_pre_date < :2
SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING
------------- ------------ --- ---------------------------------------- ---------- ---------- ------------------- --------------- ------------
5whcb1qwww6x1 0 YES :1 1 11 2019-11-06 11:26:38 TIMESTAMP
YES :2 2 11 2019-11-06 11:26:38 TIMESTAMP
1 YES :1 1 11 2019-11-06 15:42:06 TIMESTAMP
YES :2 2 11 2019-11-06 15:42:06 TIMESTAMP
--//噢VALUE_STRING沒有顯示,原來帶入的引數型別是TIMESTAMP.導致出現隱式轉換.
--//如果型別是timestamp,相關的值也儲存在value_anydata中，如何抽取檢視連結:
--//http://blog.itpub.net/267265/viewspace-713215/=>v$sql_bind_capture與timestamp型別的繫結變數的數值
SELECT SQL_ID
,NAME
,POSITION
,datatype_string
,max_length
,value_string
,DUMP (value_anydata) c90
,ANYDATA.accesstimestamp (value_anydata) c30
FROM v$sql_bind_capture a
WHERE a.value_string IS NULL
AND a.value_anydata IS NOT NULL
AND datatype_string = 'TIMESTAMP'
AND sql_id = '5whcb1qwww6x1';
SQL_ID NAME POSITION DATATYPE_STRING MAX_LENGTH VALUE_STRING C90 C30
------------- --------- -------- --------------- ---------- ------------ ---------------------------------------------------------------------------------- ------------------------------
5whcb1qwww6x1 :1 1 TIMESTAMP 11 Typ=58 Len=21: 32,234,132,6,0,0,0,0,224,218,203,102,159,127,0,0,80,232,217,102,159 2019-11-12 00:00:00.000000000
:2 2 TIMESTAMP 11 Typ=58 Len=21: 32,234,132,6,0,0,0,0,224,218,203,102,159,127,0,0,80,232,217,102,159 2019-11-12 23:59:59.000000000
:1 1 TIMESTAMP 11 Typ=58 Len=21: 32,234,132,6,0,0,0,0,224,218,203,102,159,127,0,0,80,232,217,102,159 2019-11-07 00:00:00.000000000
:2 2 TIMESTAMP 11 Typ=58 Len=21: 32,234,132,6,0,0,0,0,224,218,203,102,159,127,0,0,80,232,217,102,159 2019-11-07 23:59:59.000000000
--//可以發現取值範圍是一天的,而型別是timestamp型別.語句存在隱式轉換.一般這樣的語句不應該僅僅1條.
select distinct sql_id,name from (
SELECT SQL_ID
,NAME
,POSITION
,datatype_string
,max_length
,value_string
,DUMP (value_anydata) c90
,ANYDATA.accesstimestamp (value_anydata) c30
FROM v$sql_bind_capture a
WHERE a.value_string IS NULL
AND a.value_anydata IS NOT NULL
AND datatype_string = 'TIMESTAMP');
SQL_ID NAME
------------- --------------------
44zxp8pzcs49d :8
gfj04ktqsyzrp :ADT_BEGIN
:ADT_END
44zxp8pzcs49d :20
5whcb1qwww6x1 :2
cbukx1bujy80k :1
dbumjx9u8334g :16
1v0rzurbdpywr :5
91z2qtwn4n267 :ADT_END_DATE
647znt9fgk0yu :20
8qndjkwt2gpcd :AD_REGISTER_DATE
45smuv2b4w44k :4
c3fxr45mrwymr :8
6hh7qt2c9xg75 :4
btgu31w205r5n :8
647znt9fgk0yu :8
91z2qtwn4n267 :ADT_BEGIN_DATE
5whcb1qwww6x1 :1
c3fxr45mrwymr :21
axxmmcxn819pr :5
97ywkkx99jbnc :1
btgu31w205r5n :19
0q0u3shrucuka :4
60dmvn8gazaxy :LDT_BEG
:LDT_END
25 rows selected.
--//昏有24條語句可能涉及這個問題.還好不是很多,我估計其中一位開發這樣設計程式.不過我查詢如下
SELECT DISTINCT sql_id, name
FROM (SELECT SQL_ID
,NAME
,POSITION
,datatype_string
,max_length
,value_string
,DUMP (value_anydata) c90
,ANYDATA.accesstimestamp (value_anydata) c30
FROM v$sql_bind_capture a
WHERE datatype_string = 'DATE');
--//也就是43條.先不管它.
3.建立索引:
CREATE INDEX PHYEXAM.I_PE_VISIT_PE_LEVEL_PE_PRE_D ON PHYEXAM.PE_VISIT
(PE_LEVEL, PE_PRE_DATE)
LOGGING
TABLESPACE TSP_PE
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL
COMPRESS 1;
--//檢視執行計劃:
SYSTEM@192.168.22.222:1521/peis> @ dpc 5whcb1qwww6x1 ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 5whcb1qwww6x1, child number 1
-------------------------------------
select count ( *) from pe_visit where pe_level ='二樓體檢區' and pe_pre_date > :1 and pe_pre_date <
:2
Plan hash value: 2272508911
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
--------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 15 | | |
|* 2 | FILTER | | | | | |
|* 3 | INDEX FAST FULL SCAN| I_PE_VISIT_PE_LEVEL_PE_PRE_D | 1640 | 24600 | 1041 (3)| 00:00:13 |
--------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / PE_VISIT@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(:1<:2)
3 - filter(("PE_LEVEL"='二樓體檢區' AND INTERNAL_FUNCTION("PE_PRE_DATE")>:1 AND
INTERNAL_FUNCTION("PE_PRE_DATE")<:2))
--//檢查發現PE_LEVEL建立有直方圖.刪除看看.
SYSTEM@192.168.22.222:1521/peis> execute dbms_stats.delete_column_stats(ownname=>'PHYEXAM',tabname=>'PE_VISIT',colname=>'PE_LEVEL');
PL/SQL procedure successfully completed.
222:1521/peis> @ dpc 5whcb1qwww6x1 ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 5whcb1qwww6x1, child number 0
-------------------------------------
select count ( *) from pe_visit where pe_level ='二樓體檢區' and pe_pre_date > :1 and
pe_pre_date < :2
Plan hash value: 3803594299
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
----------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 35 | | |
|* 2 | FILTER | | | | | |
|* 3 | INDEX RANGE SCAN| I_PE_VISIT_PE_LEVEL_PE_PRE_D | 43 | 1505 | 21 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / PE_VISIT@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(:1<:2)
3 - access("PE_LEVEL"='二樓體檢區')
filter((INTERNAL_FUNCTION("PE_PRE_DATE")>:1 AND INTERNAL_FUNCTION("PE_PRE_DATE")<:2))
SYSTEM@192.168.22.222:1521/peis> @ deltabuffer.sql 5whcb1qwww6x1 100
EXECUTIONS1 BUFFER_GETS1 ELAPSED_TIME1 ROWS_PROCESSED1
----------- ------------ ------------- ---------------
2 3396 424297 2
... sleep 100 , waiting ....
EXECUTIONS2 BUFFER_GETS2 ELAPSED_TIME2 ROWS_PROCESSED2
----------- ------------ ------------- ---------------
6 10188 1206310 6
總buffer_gets 每次buffer_gets 執行次數 總執行時間 每次執行時間 總處理記錄數 平均處理記錄數
------------- --------------- ---------- ---------- ------------ ------------ --------------
6792 1698 4 782013 195503.25 4 1
--//現在每次邏輯讀1698,1698*8192/1024/1024 = 13.26M.
--//總之不論是 INDEX FAST FULL SCAN還是INDEX RANGE SCAN 掃描都是治標,治本就是代入引數型別修改為date型別.
--//還有就是一種變態的最佳化模式透過schedule人為地修改條件pe_pre_date between trunc(sysdate)-20 and trunc(sysdate)-10的PE_LEVEL"='二樓體檢去'.
--//當然首先要設定pe_pre_date < trunc(sysdate)-20 的PE_LEVEL"='二樓體檢去'.這樣人為縮寫索引掃描範圍.當然這個操作必須與使用者與開發協商.
--//這樣的修改是否可能要討論確定.
--//還有1種可能修改欄位pe_pre_date的型別為timestamp，不知道這個是否可行。
BEGIN
SYS.DBMS_STATS.LOCK_TABLE_STATS (
OwnName => 'PHYEXAM'
,TabName => 'PE_VISIT');
END;
/
4.收尾:
--//重新分析表,讓執行計劃選擇INDEX FAST FULL SCAN.
--//又是一例豆腐渣工程...
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2663172/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20220811]奇怪的隱式轉換問題.txt
- [20201214]查詢隱式轉換的sql語句.txtSQL
- 20201214]查詢隱式轉換的sql語句.txtSQL
- js顯式轉換和隱式轉換JS
- java隱式轉換Java
- javascript 隱式轉換JavaScript
- sql隱式轉換SQL
- Scala Essentials: 隱式轉換
- JavaScript隱式型別轉換JavaScript型別
- 【C++】禁止隱式轉換C++
- mysql隱式轉換問題MySql
- Java資料型別的顯式轉換和隱式轉換Java資料型別
- JS隱式轉換--寬鬆相等（==）JS
- [] == ![]，走進==隱式轉換的世界
- MySQL索引失效之隱式轉換MySql索引
- [20220815]奇怪的隱式轉換問題(11g測試補充).txt
- [20220811]奇怪的隱式轉換問題(12c補充測試).txt
- Solidity語言學習筆記————11、隱式轉換和顯式轉換Solid筆記
- JavaScript 隱式資料型別轉換JavaScript資料型別
- oracle資料隱式轉換規則Oracle
- Spark中的三種隱式轉換Spark
- 如何實現隱式型別轉換型別
- [20191106]善用column格式化輸出.txt
- C語言的隱式型別轉換C語言型別
- 【關於Javascript】--- 隱式型別轉換篇JavaScript型別
- 建構函式定義的隱式型別轉換函式型別
- [20191106]12c DCD SQLNET.EXPIRE_TIME.txtSQL
- Cris 的 Scala 筆記整理（十）：隱式轉換筆記
- c++隱式型別轉換存在的陷阱C++型別
- 雜記四：scala 柯理化和隱式轉換
- 資料型別隱式轉換導致的阻塞資料型別
- 徹底理解c++的隱式型別轉換C++型別
- 看不懂的隱式轉換（上）--- 基礎鋪墊
- golang 快速入門 [8.4]-常量與隱式型別轉換Golang型別
- 好程式設計師大資料教程Scala系列之隱式轉換和隱式引數程式設計師大資料
- [轉帖]見識一下SQL Server隱式轉換處理的不同SQLServer
- 前端面試官必問系列之隱式型別轉換前端面試型別
- 評“MySQL 隱式轉換引起的執行結果錯誤”MySql