​[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));