树形查询SQL优化一例

mac2022-06-30 83

上周五一哥们发了条SQL，让我看看，代码如下：

SELECT COUNT(1) FROM (select m.sheet_id from cpm_main_sheet_history m, cpm_service_warn_config s where m.sheet_type_id in (select t.row_id from tbl_class_trees t start with t.row_id = s.sheet_type_id connect by t.parent_row_id = prior t.row_id) and m.service_type in (select tt.row_id from tbl_class_trees tt start with tt.row_id = s.business_type_id connect by tt.parent_row_id = prior tt.row_id) and m.accept_time >= TO_CHAR(SYSDATE - time_interval / 24, 'yyyy-mm-dd hh24:mi:ss') and s.row_id = 'AS170904165251' and m.no_area in ('0000')) c__ --执行计划 PLAN_TABLE_OUTPUT Plan hash value: 2710926849 ---------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 143 | 96246 (1)| 00:19:15 | |* 1 | FILTER | | | | | | | 2 | NESTED LOOPS | | 1681 | 234K| 746 (1)| 00:00:09 | | 3 | TABLE ACCESS BY INDEX ROWID | CPM_SERVICE_WARN_CONFIG | 1 | 56 | 1 (0)| 00:00:01 | |* 4 | INDEX UNIQUE SCAN | PK_CONFIG_ROW_ID | 1 | | 0 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID | CPM_MAIN_SHEET_HISTORY | 1681 | 142K| 745 (1)| 00:00:09 | |* 6 | INDEX RANGE SCAN | IDX_CPM_NO_AREA_TIME1 | 449 | | 62 (0)| 00:00:01 | |* 7 | FILTER | | | | | | |* 8 | CONNECT BY NO FILTERING WITH SW (UNIQUE)| | | | | | | 9 | TABLE ACCESS FULL | TBL_CLASS_TREES | 9527 | 493K| 113 (0)| 00:00:02 | |* 10 | FILTER | | | | | | |* 11 | CONNECT BY NO FILTERING WITH SW (UNIQUE)| | | | | | | 12 | TABLE ACCESS FULL | TBL_CLASS_TREES | 9527 | 493K| 113 (0)| 00:00:02 | ---------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( EXISTS (SELECT 0 FROM "TBL_CLASS_TREES" "T" WHERE "T"."ROW_ID"=:B1 START WITH "T"."ROW_ID"=:B2 CONNECT BY "T"."PARENT_ROW_ID"=PRIOR "T"."ROW_ID") AND EXISTS (SELECT 0 FROM "TBL_CLASS_TREES" "TT" WHERE "TT"."ROW_ID"=:B3 START WITH "TT"."ROW_ID"=:B4 CONNECT BY "TT"."PARENT_ROW_ID"=PRIOR "TT"."ROW_ID")) 4 - access("S"."ROW_ID"='AS170904165251') 6 - access("M"."ACCEPT_TIME">=TO_CHAR(SYSDATE@!-"TIME_INTERVAL"/24,'yyyy-mm-dd hh24:mi:ss') AND "M"."NO_AREA"='0000' AND "M"."ACCEPT_TIME" IS NOT NULL) filter("M"."NO_AREA"='0000') 7 - filter("T"."ROW_ID"=:B1) 8 - access("T"."PARENT_ROW_ID"=PRIOR "T"."ROW_ID") filter("T"."ROW_ID"=:B1) 10 - filter("TT"."ROW_ID"=:B1) 11 - access("TT"."PARENT_ROW_ID"=PRIOR "TT"."ROW_ID") filter("TT"."ROW_ID"=:B1)

SQL优化前：

耗时：20s count(1)返回: 147条数据

分析执行计划，执行计划中有filter关键字且有3个子级，这种sql是最容易引起性能问题的，所以第一时间是反应是sql有没有走索引，能不能改写。

尝试1：

建索引优化：

在TBL_CLASS_TREES表(row_id,parent_row_id)上建索引执行计划： PLAN_TABLE_OUTPUT Plan hash value: 135779572 ---------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 155 | 34147 (1)| 00:06:50 | |* 1 | FILTER | | | | | | | 2 | NESTED LOOPS | | 3021 | 457K| 816 (1)| 00:00:10 | | 3 | TABLE ACCESS BY INDEX ROWID | CPM_SERVICE_WARN_CONFIG | 1 | 62 | 1 (0)| 00:00:01 | |* 4 | INDEX UNIQUE SCAN | PK_CONFIG_ROW_ID | 1 | | 0 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID | CPM_MAIN_SHEET_HISTORY | 3021 | 274K| 815 (1)| 00:00:10 | |* 6 | INDEX RANGE SCAN | IDX_CPM_NO_AREA_TIME1 | 563 | | 136 (0)| 00:00:02 | |* 7 | FILTER | | | | | | |* 8 | CONNECT BY NO FILTERING WITH SW (UNIQUE)| | | | | | | 9 | INDEX FAST FULL SCAN | IDX_ROW_ID | 9527 | 493K| 20 (0)| 00:00:01 | |* 10 | FILTER | | | | | | |* 11 | CONNECT BY NO FILTERING WITH SW (UNIQUE)| | | | | | | 12 | INDEX FAST FULL SCAN | IDX_ROW_ID | 9527 | 493K| 20 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( EXISTS (SELECT 0 FROM "TBL_CLASS_TREES" "T" WHERE "T"."ROW_ID"=:B1 START WITH "T"."ROW_ID"=:B2 CONNECT BY "T"."PARENT_ROW_ID"=PRIOR "T"."ROW_ID") AND EXISTS (SELECT 0 FROM "TBL_CLASS_TREES" "TT" WHERE "TT"."ROW_ID"=:B3 START WITH "TT"."ROW_ID"=:B4 CONNECT BY "TT"."PARENT_ROW_ID"=PRIOR "TT"."ROW_ID")) 4 - access("S"."ROW_ID"='AS170904165251') 6 - access("M"."ACCEPT_TIME">=TO_CHAR(SYSDATE@!-"TIME_INTERVAL"/24,'yyyy-mm-dd hh24:mi:ss') AND "M"."NO_AREA"='0000' AND "M"."ACCEPT_TIME" IS NOT NULL) filter("M"."NO_AREA"='0000') 7 - filter("T"."ROW_ID"=:B1) 8 - access("T"."PARENT_ROW_ID"=PRIOR "T"."ROW_ID") filter("T"."ROW_ID"=:B1) 10 - filter("TT"."ROW_ID"=:B1) 11 - access("TT"."PARENT_ROW_ID"=PRIOR "TT"."ROW_ID") filter("TT"."ROW_ID"=:B1) --效果还是一样慢，此优化失败。

尝试2：

利用with改写sql优化

with t as (select /*+ materialize */ row_id,parent_row_id from tbl_class_trees) SELECT COUNT(1) FROM (select m.sheet_id from cpm_main_sheet_history m,cpm_service_warn_config s where m.sheet_type_id in (select t.row_id from t start with t.row_id = s.sheet_type_id connect by t.parent_row_id = prior t.row_id) and m.service_type in (select t.row_id from t start with t.row_id = s.business_type_id connect by t.parent_row_id = prior t.row_id) and m.accept_time >= TO_CHAR(SYSDATE - time_interval / 24, 'yyyy-mm-dd hh24:mi:ss') and s.row_id = 'AS170904165251' and m.no_area in ('0000')) c__ --效果还是一样慢，此优化失败。

再次分析原SQL执行计划：

id=8,id=11的执行计划关键词是：CONNECT BY NO FILTERING WITH SW (UNIQUE)。这个为树形查询在11g中的新特性，尝试让sql不使用这个新特性。于是使用以下hint：/*+ connect_by_filtering */ 进行优化： SELECT COUNT(1) FROM (select m.sheet_id from cpm_main_sheet_history m, cpm_service_warn_config s where m.sheet_type_id in (select /*+ connect_by_filtering */ t.row_id from tbl_class_trees t start with t.row_id = s.sheet_type_id connect by t.parent_row_id = prior t.row_id) and m.service_type in (select /*+ connect_by_filtering */ tt.row_id from tbl_class_trees tt start with tt.row_id = s.business_type_id connect by tt.parent_row_id = prior tt.row_id) and m.accept_time >= TO_CHAR(SYSDATE - time_interval / 24, 'yyyy-mm-dd hh24:mi:ss') and s.row_id = 'AS170904165251' and m.no_area in ('0000')) c__ PLAN_TABLE_OUTPUT Plan hash value: 2824841339 ---------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 246 | 188K (1)| 00:37:47 | |* 1 | FILTER | | | | | | | 2 | NESTED LOOPS | | 3021 | 725K| 816 (1)| 00:00:10 | | 3 | TABLE ACCESS BY INDEX ROWID | CPM_SERVICE_WARN_CONFIG | 1 | 106 | 1 (0)| 00:00:01 | |* 4 | INDEX UNIQUE SCAN | PK_CONFIG_ROW_ID | 1 | | 0 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID | CPM_MAIN_SHEET_HISTORY | 3021 | 413K| 815 (1)| 00:00:10 | |* 6 | INDEX RANGE SCAN | IDX_CPM_NO_AREA_TIME1 | 563 | | 136 (0)| 00:00:02 | |* 7 | FILTER | | | | | | |* 8 | CONNECT BY WITH FILTERING | | | | | | | 9 | TABLE ACCESS BY INDEX ROWID| TBL_CLASS_TREES | 1 | 107 | 2 (0)| 00:00:01 | |* 10 | INDEX UNIQUE SCAN | PK_TBL_CLASS_TREESS | 1 | | 1 (0)| 00:00:01 | |* 11 | HASH JOIN | | | | | | | 12 | CONNECT BY PUMP | | | | | | | 13 | TABLE ACCESS FULL | TBL_CLASS_TREES | 6 | 318 | 113 (0)| 00:00:02 | |* 14 | FILTER | | | | | | |* 15 | CONNECT BY WITH FILTERING | | | | | | | 16 | TABLE ACCESS BY INDEX ROWID| TBL_CLASS_TREES | 1 | 107 | 2 (0)| 00:00:01 | |* 17 | INDEX UNIQUE SCAN | PK_TBL_CLASS_TREESS | 1 | | 1 (0)| 00:00:01 | |* 18 | HASH JOIN | | | | | | | 19 | CONNECT BY PUMP | | | | | | | 20 | TABLE ACCESS FULL | TBL_CLASS_TREES | 6 | 318 | 113 (0)| 00:00:02 | ---------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( EXISTS (SELECT /*+ CONNECT_BY_FILTERING */ 0 FROM "TBL_CLASS_TREES" "T" WHERE "T"."ROW_ID"=:B1 START WITH "T"."ROW_ID"=:B2) AND EXISTS (SELECT /*+ CONNECT_BY_FILTERING */ 0 FROM "TBL_CLASS_TREES" "TT" WHERE "TT"."ROW_ID"=:B3 START WITH "TT"."ROW_ID"=:B4)) 4 - access("S"."ROW_ID"='AS170904165251') 6 - access("M"."ACCEPT_TIME">=TO_CHAR(SYSDATE@!-"TIME_INTERVAL"/24,'yyyy-mm-dd hh24:mi:ss') AND "M"."NO_AREA"='0000' AND "M"."ACCEPT_TIME" IS NOT NULL) filter("M"."NO_AREA"='0000') 7 - filter("T"."ROW_ID"=:B1) 8 - access("T"."PARENT_ROW_ID"=PRIOR "T"."ROW_ID") 10 - access("T"."ROW_ID"=:B1) 11 - access("T"."PARENT_ROW_ID"=PRIOR "T"."ROW_ID") 14 - filter("TT"."ROW_ID"=:B1) 15 - access("TT"."PARENT_ROW_ID"=PRIOR "TT"."ROW_ID") 17 - access("TT"."ROW_ID"=:B1) 18 - access("TT"."PARENT_ROW_ID"=PRIOR "TT"."ROW_ID") --优化后，SQL能在5s返回结果

转载于:https://www.cnblogs.com/wanbin/p/9514718.html

最新回复(0)