[20230204]執行很慢的分析.txt
[20230204]執行很慢的分析.txt
--//連結http://www.itpub.net/thread-2148452-1-1.html,執行計劃一樣,效率卻相差甚遠的SQL.
--//遇到一個SQL,生產環境與測試環境執行計劃一致,生產環境需要31秒,測試環境秒出。執行效果相差較大。求指教
db version:11.2.0.1
生產與測試版本一致。
表DLE_LABEL生產環境數量:18955 測試環境數量:18402
SQL:
SELECT *
FROM (SELECT NAME
FROM (SELECT DISTINCT REGEXP_SUBSTR(t.ISMI, '[^,]+', 1, level) NAME
FROM (SELECT ISMI
FROM DLE_LABEL
WHERE ISMI IS NOT NULL
GROUP BY ISMI) t
CONNECT BY LEVEL <= regexp_count(t.ISMI, ',') + 1) TAB1
GROUP BY NAME
ORDER BY NAME DESC) TAB2
WHERE ROWNUM <= 2;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 28.95 28.98 0 135 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 28.96 28.99 0 135 0 2
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
2 COUNT STOPKEY (cr=135 pr=0 pw=0 time=0 us)
2 VIEW (cr=135 pr=0 pw=0 time=0 us cost=5 size=104 card=2)
2 SORT GROUP BY STOPKEY (cr=135 pr=0 pw=0 time=0 us cost=5 size=104 card=2)
5 VIEW (cr=135 pr=0 pw=0 time=28 us cost=4 size=104 card=2)
5 HASH UNIQUE (cr=135 pr=0 pw=0 time=24 us cost=4 size=82 card=2)
2546597 CONNECT BY WITHOUT FILTERING (UNIQUE) (cr=135 pr=0 pw=0 time=17937528 us)
--//問題在這裡需要17秒.而不是作者的31秒.
53 VIEW (cr=135 pr=0 pw=0 time=104 us cost=3 size=82 card=2)
53 HASH GROUP BY (cr=135 pr=0 pw=0 time=52 us cost=3 size=82 card=2)
14815 INDEX FULL SCAN IDX_IDML_ISMI (cr=135 pr=0 pw=0 time=8811 us cost=3 size=607415 card=14815)(object id 357829)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
asynch descriptor resize 3 0.00 0.00
SQL*Net message from client 2 8.53 8.53
1.我的分析:
--//我仔細看了一下, 根據上面的提示問題主要圍繞connect by執行問題.我測試如下.
--//環境:
SCOTT@test01p> @ver1
PORT_STRING VERSION BANNER CON_ID
-------------------- ---------- ---------------------------------------------------------------------------- ------
IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
WITH t1 AS (SELECT 'aaa,bbb,ccc' a FROM dual UNION ALL SELECT 'ddd,bbb,ccc' a FROM dual )
SELECT REGEXP_SUBSTR(t1.a, '[^,]+', 1, level) c20
FROM t1
CONNECT BY LEVEL <=regexp_count(t1.a,',')+1;
WITH t1 AS (SELECT 'aaa,bbb,ccc' a FROM dual )
SELECT REGEXP_SUBSTR(t1.a, '[^,]+', 1, level) c20
FROM t1
CONNECT BY REGEXP_SUBSTR(t1.a, '[^,]+', 1, level) is not null;
C20
--------------------
aaa
bbb
ccc
--//輸出3行.
WITH t1 AS (SELECT 'aaa,bbb,ccc' a FROM dual UNION ALL SELECT 'ddd,bbb,ccc' a FROM dual )
SELECT REGEXP_SUBSTR(t1.a, '[^,]+', 1, level) c20
FROM t1
CONNECT BY REGEXP_SUBSTR(t1.a, '[^,]+', 1, level) is not null;
C20
--------------------
aaa
bbb
ccc
ccc
bbb
ccc
ccc
ddd
bbb
ccc
ccc
bbb
ccc
ccc
14 rows selected.
--//使用regexp_count 類似.第2個執行我開始以為是6條記錄,而實際的情況是14條.可以看出如果按照這樣執行返回會有許多行.
--//也就是connect by導致大量的遞迴操作.
WITH t1 AS (SELECT 'aaa,bbb,ccc' a FROM dual UNION ALL SELECT 'ddd,bbb,ccc' a FROM dual union all SELECT '333,222,111' a FROM dual)
SELECT REGEXP_SUBSTR(t1.a, '[^,]+', 1, level) c20
FROM t1
CONNECT BY REGEXP_SUBSTR(t1.a, '[^,]+', 1, level) is not null;
--//3個結果集合,輸出略39行!!
--//如果對應的表僅僅1行,如果多行問題就來了,這樣執行自然很慢.
2.如何解決呢?
--//想到以前分開字串的split函式,我看了一下以前的工作筆記,定義函式如下:
create or replace function split (i_str in varchar2, i_delimiter in varchar2 default ',')
return sys.odcivarchar2list
pipelined
as
l_current_string varchar2(4000) := i_str;
l_pos binary_integer;
begin
if i_str is null then
return;
end if;
loop
l_pos := nullif(instr(l_current_string, i_delimiter), 0);
pipe row(substr(l_current_string, 1, nvl(l_pos - 1, length(l_current_string))));
exit when l_pos is null;
l_current_string := substr(l_current_string, l_pos + length(i_delimiter));
end loop;
end split;
/
WITH t1 AS (SELECT 'aaa,bbb,ccc' a FROM dual UNION ALL SELECT 'ddd,bbb,ccc' a FROM dual union all SELECT '333,222,111' a FROM dual)
SELECT distinct column_value
FROM t1,table(split(a));
COLUMN_VALUE
----------------------
aaa
bbb
ccc
ddd
bbb
ccc
333
222
111
9 rows selected.
--//OK,這樣可以滿足作者的需求,是否很慢我不是很清楚.畢竟每一行都要呼叫1次split函式.
--//隨便貼一下分離數字串的函式:
create or replace function splitnum (i_str in varchar2, i_delimiter in varchar2 default ',')
return sys.odcinumberlist
pipelined
as
l_current_string varchar2(4000) := i_str;
l_pos binary_integer;
begin
if i_str is null then
return;
end if;
loop
l_pos := nullif(instr(l_current_string, i_delimiter), 0);
pipe row(substr(l_current_string, 1, nvl(l_pos - 1, length(l_current_string))));
exit when l_pos is null;
l_current_string := substr(l_current_string, l_pos + length(i_delimiter));
end loop;
end splitnum;
/
--//其它相似指令碼:
--//使用str2numlist,str2varlist函式,原始碼在網上很容易找到.原始指令碼沒有定義分隔符號.
CREATE OR REPLACE TYPE numtabletype AS TABLE OF NUMBER
/
CREATE OR REPLACE FUNCTION str2numlist (p_string IN VARCHAR2)
RETURN numtabletype
AS
v_str LONG DEFAULT p_string || ',';
v_n NUMBER;
v_data numtabletype := numtabletype ();
BEGIN
LOOP
v_n := TO_NUMBER (INSTR (v_str, ','));
EXIT WHEN (NVL (v_n, 0) = 0);
v_data.EXTEND;
v_data (v_data.COUNT) := LTRIM (RTRIM (SUBSTR (v_str, 1, v_n - 1)));
v_str := SUBSTR (v_str, v_n + 1);
END LOOP;
RETURN v_data;
END;
/
CREATE OR REPLACE TYPE vartabletype AS TABLE OF VARCHAR2 (4000)
/
CREATE OR REPLACE FUNCTION str2varlist (p_string IN VARCHAR2)
RETURN vartabletype
AS
v_str LONG DEFAULT p_string || ',';
v_n VARCHAR2 (2000);
v_data vartabletype := vartabletype ();
BEGIN
LOOP
v_n := INSTR (v_str, ',');
EXIT WHEN (NVL (v_n, 0) = 0);
v_data.EXTEND;
v_data (v_data.COUNT) := LTRIM (RTRIM (SUBSTR (v_str, 1, v_n - 1)));
v_str := SUBSTR (v_str, v_n + 1);
END LOOP;
RETURN v_data;
END;
/
select * from table (cast(STR2VARLIST(:st2) as vartabletype));
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2934658/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- truncate table執行很慢的原因分析
- 執行起來很慢呀。
- [20170703]SQL語句分析執行過程.txtSQL
- 隨身碟啟動執行很慢怎麼辦?
- 執行SQL語句很慢,可能是什麼原因SQL
- XP系統在啟動執行捲軸時速度很慢
- [20181119]sql語句執行緩慢分析.txtSQL
- Java 執行緒池執行原理分析Java執行緒
- 透過DNS TXT記錄執行powershellDNS
- Java的執行機制分析!Java
- HiveSQL的執行過程分析HiveSQL
- JAVA執行緒dump的分析Java執行緒
- browserify執行原理分析
- Locust 執行原理分析
- [20210804]oracle rac執行命令crs_stat -t -v緩慢的分析.txtOracle
- 分析執行計劃優化SQLORACLE的執行計劃(轉)優化SQLOracle
- Oracle檢視正在執行的SQL以及執行計劃分析OracleSQL
- 實驗-shell執行資料庫命令.TXT資料庫
- win10遊戲執行緩慢如何解決_win10系統執行遊戲速度很慢怎麼辦Win10遊戲
- SqlServer的執行計劃如何分析?SQLServer
- [20221018]本地執行與遠端執行.txt
- Java多執行緒-程式執行堆疊分析Java執行緒
- 分析執行計劃最佳化SQLORACLE的執行計劃(轉)SQLOracle
- 20151111rman執行list backupset很慢的問題
- webrtc執行緒模型分析Web執行緒模型
- explain執行計劃分析AI
- JavaScript執行順序分析JavaScript
- RxJava 執行緒模型分析RxJava執行緒模型
- strerror執行緒安全分析Error執行緒
- Job不能執行原因分析
- sql執行過程分析SQL
- SQL執行計劃分析SQL
- 執行流程原始碼分析原始碼
- [20171219]指令碼執行的安全性.txt指令碼
- 關於redis單執行緒的分析Redis執行緒
- 用 Explain 命令分析 MySQL 的 SQL 執行AIMySql
- Flutter框架分析(四)-- Flutter框架的執行Flutter框架
- 原始碼分析OKHttp的執行過程原始碼HTTP