使用物化视图查询重写 优化对于 UNION ALL视图的CONNECT BY查询
使用物化视图查询重写 优化对于 UNION ALL视图的CONNECT BY查询
查询SQL如下
SQL> SELECT T.INTJOBID, T.STRJOBNAME, T.INTPRIORJOBID
2 FROM (SELECT INTJOBID, STRJOBNAME, INTPRIORJOBID, INTFLOWID
3 FROM JOB_BASEINFO
4 UNION ALL
5 SELECT INTJOBID, STRJOBNAME, INTPRIORJOBID, INTFLOWID
6 FROM JOB_BASEINFO_HIS) T
7 START WITH T.INTJOBID = &1
8 CONNECT BY T.INTJOBID = PRIOR T.INTPRIORJOBID
9 ORDER BY INTJOBID ;
Enter value for 1: 14381
old 7: START WITH T.INTJOBID = &1
new 7: START WITH T.INTJOBID = 14381
Execution Plan
----------------------------------------------------------
Plan hash value: 1526360741
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 127K| 64M| 2213 (1)| 00:00:27 |
| 1 | SORT ORDER BY | | 127K| 64M| 2213 (1)| 00:00:27 |
|* 2 | CONNECT BY WITH FILTERING | | | | | |
| 3 | VIEW | | 2 | 1160 | 4 (0)| 00:00:01 |
| 4 | UNION-ALL | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| JOB_BASEINFO | 1 | 40 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IDX_JOB_BASEINFO | 1 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| JOB_BASEINFO_HIS | 1 | 40 | 2 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | IDX_JOB_BASEINFO_HIS | 1 | | 1 (0)| 00:00:01 |
|* 9 | HASH JOIN | | | | | |
| 10 | CONNECT BY PUMP | | | | | |
| 11 | VIEW | | 127K| 64M| 2213 (1)| 00:00:27 |
| 12 | UNION-ALL | | | | | |
| 13 | TABLE ACCESS FULL | JOB_BASEINFO | 63890 | 2495K| 1106 (1)| 00:00:14 |
| 14 | TABLE ACCESS FULL | JOB_BASEINFO_HIS | 63890 | 2495K| 1106 (1)| 00:00:14 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."INTJOBID"=PRIOR "T"."INTPRIORJOBID")
6 - access("INTJOBID"=14381)
8 - access("INTJOBID"=14381)
9 - access("T"."INTJOBID"=PRIOR "T"."INTPRIORJOBID")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9970 consistent gets
0 physical reads
0 redo size
730 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
2 rows processed
数据库10204
这个只是测试数据，实际数据更多，一致性读超过10W，物理读也高达7W。
遇到大量会话同时执行该查询时，导致系统异常缓慢。
可能是10g CBO的限制，全表扫描无法避免，最多也就建立多列索引，将全表扫描转换为索引快速全扫描。
在11gR2中，受用于参数 _connect_by_use_union_all提供的新特性，其可以得到良好的执行计划。
相当了使用物化视图来重新查询。测试了一把，效果明显
**********************************************************************************************************
CREATE MATERIALIZED VIEW LOG ON JOB_BASEINFO WITH ROWID,SEQUENCE INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON JOB_BASEINFO_HIS WITH ROWID,SEQUENCE INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW mv_JOB
REFRESH FAST
ON COMMIT
-- ON DEMAND
WITH ROWID enable query rewrite AS
SELECT INTJOBID,INTFLOWID,STRJOBNAME,INTCREATORID,DTTBEGINTIME,DTTENDTIME,INTJOBTYPE,INTAPPLYTYPE,STRHOST,STRPATH,INTSTATE,STRJOBDESCRIPT,
INTDATASOURCEID,INTDATASOURCETYPE,INTBATCHID,STRURL,INTPRIORJOBID,INTBRANCHID,INTTREEID,STRSELECTSETTING,INTCAPTIONINDEX,INTRETURNVALUEINDEX,
INTACTIONTYPE,INTSAVETYPE,INTSAVETERM,INTSAVETERMTYPE,INTMINDATAROW,INTMAXDATAROW,1 MARKER,ROWID RD
FROM JOB_BASEINFO
UNION ALL
SELECT INTJOBID,INTFLOWID,STRJOBNAME,INTCREATORID,DTTBEGINTIME,DTTENDTIME,INTJOBTYPE,INTAPPLYTYPE,STRHOST,STRPATH,INTSTATE,STRJOBDESCRIPT,
INTDATASOURCEID,INTDATASOURCETYPE,INTBATCHID,STRURL,INTPRIORJOBID,INTBRANCHID,INTTREEID,STRSELECTSETTING,INTCAPTIONINDEX,INTRETURNVALUEINDEX,
INTACTIONTYPE,INTSAVETYPE,INTSAVETERM,INTSAVETERMTYPE,INTMINDATAROW,INTMAXDATAROW,2 MARKER,ROWID RD
FROM JOB_BASEINFO_HIS
CREATE INDEX idx_mv_job ON mv_job(INTJOBID);
exec dbms_stats.gather_table_stats(null,'mv_job');
**********************************************************************************************************
Execution Plan
----------------------------------------------------------
Plan hash value: 2052634769
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 80 | 3 (0)| 00:00:01 |
| 1 | SORT ORDER BY | | 2 | 80 | 3 (0)| 00:00:01 |
|* 2 | CONNECT BY WITH FILTERING | | | | | |
| 3 | MAT_VIEW REWRITE ACCESS BY INDEX ROWID | MV_JOB | 2 | 126 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_MV_JOB | 2 | | 1 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | | | | |
| 6 | CONNECT BY PUMP | | | | | |
| 7 | MAT_VIEW REWRITE ACCESS BY INDEX ROWID| MV_JOB | 2 | 80 | 3 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | IDX_MV_JOB | 2 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MV_JOB"."INTJOBID"=PRIOR "MV_JOB"."INTPRIORJOBID")
4 - access("MV_JOB"."INTJOBID"=14381)
8 - access("MV_JOB"."INTJOBID"=PRIOR "MV_JOB"."INTPRIORJOBID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
730 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
2 rows processed
查询SQL如下
SQL> SELECT T.INTJOBID, T.STRJOBNAME, T.INTPRIORJOBID
2 FROM (SELECT INTJOBID, STRJOBNAME, INTPRIORJOBID, INTFLOWID
3 FROM JOB_BASEINFO
4 UNION ALL
5 SELECT INTJOBID, STRJOBNAME, INTPRIORJOBID, INTFLOWID
6 FROM JOB_BASEINFO_HIS) T
7 START WITH T.INTJOBID = &1
8 CONNECT BY T.INTJOBID = PRIOR T.INTPRIORJOBID
9 ORDER BY INTJOBID ;
Enter value for 1: 14381
old 7: START WITH T.INTJOBID = &1
new 7: START WITH T.INTJOBID = 14381
Execution Plan
----------------------------------------------------------
Plan hash value: 1526360741
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 127K| 64M| 2213 (1)| 00:00:27 |
| 1 | SORT ORDER BY | | 127K| 64M| 2213 (1)| 00:00:27 |
|* 2 | CONNECT BY WITH FILTERING | | | | | |
| 3 | VIEW | | 2 | 1160 | 4 (0)| 00:00:01 |
| 4 | UNION-ALL | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| JOB_BASEINFO | 1 | 40 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IDX_JOB_BASEINFO | 1 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| JOB_BASEINFO_HIS | 1 | 40 | 2 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | IDX_JOB_BASEINFO_HIS | 1 | | 1 (0)| 00:00:01 |
|* 9 | HASH JOIN | | | | | |
| 10 | CONNECT BY PUMP | | | | | |
| 11 | VIEW | | 127K| 64M| 2213 (1)| 00:00:27 |
| 12 | UNION-ALL | | | | | |
| 13 | TABLE ACCESS FULL | JOB_BASEINFO | 63890 | 2495K| 1106 (1)| 00:00:14 |
| 14 | TABLE ACCESS FULL | JOB_BASEINFO_HIS | 63890 | 2495K| 1106 (1)| 00:00:14 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."INTJOBID"=PRIOR "T"."INTPRIORJOBID")
6 - access("INTJOBID"=14381)
8 - access("INTJOBID"=14381)
9 - access("T"."INTJOBID"=PRIOR "T"."INTPRIORJOBID")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9970 consistent gets
0 physical reads
0 redo size
730 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
2 rows processed
数据库10204
这个只是测试数据，实际数据更多，一致性读超过10W，物理读也高达7W。
遇到大量会话同时执行该查询时，导致系统异常缓慢。
可能是10g CBO的限制，全表扫描无法避免，最多也就建立多列索引，将全表扫描转换为索引快速全扫描。
在11gR2中，受用于参数 _connect_by_use_union_all提供的新特性，其可以得到良好的执行计划。
相当了使用物化视图来重新查询。测试了一把，效果明显
**********************************************************************************************************
CREATE MATERIALIZED VIEW LOG ON JOB_BASEINFO WITH ROWID,SEQUENCE INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON JOB_BASEINFO_HIS WITH ROWID,SEQUENCE INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW mv_JOB
REFRESH FAST
ON COMMIT
-- ON DEMAND
WITH ROWID enable query rewrite AS
SELECT INTJOBID,INTFLOWID,STRJOBNAME,INTCREATORID,DTTBEGINTIME,DTTENDTIME,INTJOBTYPE,INTAPPLYTYPE,STRHOST,STRPATH,INTSTATE,STRJOBDESCRIPT,
INTDATASOURCEID,INTDATASOURCETYPE,INTBATCHID,STRURL,INTPRIORJOBID,INTBRANCHID,INTTREEID,STRSELECTSETTING,INTCAPTIONINDEX,INTRETURNVALUEINDEX,
INTACTIONTYPE,INTSAVETYPE,INTSAVETERM,INTSAVETERMTYPE,INTMINDATAROW,INTMAXDATAROW,1 MARKER,ROWID RD
FROM JOB_BASEINFO
UNION ALL
SELECT INTJOBID,INTFLOWID,STRJOBNAME,INTCREATORID,DTTBEGINTIME,DTTENDTIME,INTJOBTYPE,INTAPPLYTYPE,STRHOST,STRPATH,INTSTATE,STRJOBDESCRIPT,
INTDATASOURCEID,INTDATASOURCETYPE,INTBATCHID,STRURL,INTPRIORJOBID,INTBRANCHID,INTTREEID,STRSELECTSETTING,INTCAPTIONINDEX,INTRETURNVALUEINDEX,
INTACTIONTYPE,INTSAVETYPE,INTSAVETERM,INTSAVETERMTYPE,INTMINDATAROW,INTMAXDATAROW,2 MARKER,ROWID RD
FROM JOB_BASEINFO_HIS
CREATE INDEX idx_mv_job ON mv_job(INTJOBID);
exec dbms_stats.gather_table_stats(null,'mv_job');
**********************************************************************************************************
Execution Plan
----------------------------------------------------------
Plan hash value: 2052634769
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 80 | 3 (0)| 00:00:01 |
| 1 | SORT ORDER BY | | 2 | 80 | 3 (0)| 00:00:01 |
|* 2 | CONNECT BY WITH FILTERING | | | | | |
| 3 | MAT_VIEW REWRITE ACCESS BY INDEX ROWID | MV_JOB | 2 | 126 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_MV_JOB | 2 | | 1 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | | | | |
| 6 | CONNECT BY PUMP | | | | | |
| 7 | MAT_VIEW REWRITE ACCESS BY INDEX ROWID| MV_JOB | 2 | 80 | 3 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | IDX_MV_JOB | 2 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MV_JOB"."INTJOBID"=PRIOR "MV_JOB"."INTPRIORJOBID")
4 - access("MV_JOB"."INTJOBID"=14381)
8 - access("MV_JOB"."INTJOBID"=PRIOR "MV_JOB"."INTPRIORJOBID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
730 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
2 rows processed
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/8242091/viewspace-754784/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
使用dtrace跟踪oracle函数调用
请登录后发表评论
登录
全部评论
