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