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