Oracle 'or exists/in'结合使用引起的filter执行计划 的优化
注释：
所以这对这个sql看下怎么优化.
环境：
|
AIX |
7.1 |
| >> | CPU 64 (16C) |
| >> | Mem 64 |
| Oracle | 11.2.0.3.0 |
|
>>
|
Mem 40G
|
表结构/数据情况
|
TABLE_NAME |
INDEX_NAME |
COLUMN_NAME |
BLEVEL |
LEAF_BLOCKS |
DISTINCT_KEYS |
CLUSTERING_FACTOR |
NUM_ROWS |
STATUS |
| CS_CHANGE_ACCE | PK_CHANGE_ORDER_ID | ORDER_ID | 1 | 392 | 91178 | 65010 | 91178 | VALID |
|
CS_ORDER_ITEM
|
IDX_COI_ORDER_ID
|
ORDER_ID
|
2
|
35082
|
638272
|
9077480
|
15016701
|
VALID
|
SQL> set autot on
SQL> set timing on
SQL> set linesize 1000
SQL> set pagesize 1000
SQL> set timing on
SQL> set linesize 1000
SQL> set pagesize 1000
SQL> select count(1) ---->篇幅原因，选择count(1)复现
2 from cs_order_item t3 where 1 = 1
4 AND (T.ORDER_ID = 1528168 OR
5 T.ORDER_ID IN
6 (SELECT CA.ORDER_ID
7 FROM CS_CHANGE_ACCE CA
8 WHERE CA.TAG IN (SELECT t.TAG
9 FROM CS_CHANGE_ACCE t
10 WHERE t.ORDER_ID = 1537775)))
11 and t.oi_source in (1, 2)
12 order by t.begin_date;
COUNT(1)
----------
12
已用时间: 00: 01: 13.28
执行计划
----------------------------------------------------------
Plan hash value: 1964544126
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU )| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 108 K (2)| 00:21 :44 |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
|* 2 | FILTER | | | | | |
|* 3 | TABLE ACCESS FULL | CS_ORDER_ITEM | 14M| 234M| 108 K (2)| 00:21 :44 |
| 4 | NESTED LOOPS | | 1 | 22 | 3 (0)| 00:00 :01 |
| 5 | TABLE ACCESS BY INDEX ROWID | CS_CHANGE_ACCE | 1 | 11 | 2 (0)| 00:00 :01 |
|* 6 | INDEX UNIQUE SCAN | PK_CHANGE_ORDER_ID | 1 | | 1 (0)| 00:00 :01 |
|* 7 | TABLE ACCESS BY INDEX ROWID | CS_CHANGE_ACCE | 1 | 11 | 1 (0)| 00:00 :01 |
|* 8 | INDEX UNIQUE SCAN | PK_CHANGE_ORDER_ID | 1 | | 0 (0)| 00:00 :01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T"."ORDER_ID"=1528168 OR EXISTS (SELECT 0 FROM "CS_CHANGE_ACCE"
"CA","CS_CHANGE_ACCE" "T" WHERE "T"."ORDER_ID"=1537775 AND "CA"."TAG"="T"."TAG" AND
"CA"."ORDER_ID"=:B1))
3 - filter("T"."OI_SOURCE"=1 OR "T"."OI_SOURCE"=2)
6 - access("CA"."ORDER_ID"=:B1)
7 - filter("CA"."TAG"="T"."TAG")
8 - access("T"."ORDER_ID"=1537775)
统计信息
----------------------------------------------------------
1 recursive calls
1 db block gets
19981341 consistent gets
392349 physical reads
0 redo size
217 bytes sent via SQL*Net to client
252 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
Filter/子查询非嵌套 知识点：
|
Subquery Unnesting(子查询非嵌套)： 如果SQL语句中的where条件后面有子查询，子查询前面有in,not in,exists,not exists,<,<=,=,&get;,&get;= 等等， CBO很可能会对该子查询进行等价改写，改写的过程其实就叫做子查询扩展。Oracle始终认为SQL语句进行改写之后， CBO能更好的优化，当然了，并不是所有的子查询都会被改写，子查询中有些限制条件会阻止CBO进行改写(因为改写之后不等价)。 说白了就是 将半连接与反连接的子查询语句转化成普通的连接,如果没有子查询非嵌套化,执行的基本的方法就是基于FILTER操作. 这是一个类似于nested loop的一种方法,和nest loop不同之处是要维护一个hash table 且filter的性能实际上跟列值distinct数有关， oracle执行时实际上做了很大优化，最坏情况下才会出现对外表每一行 执行一次filter操作，如果distinct值比较少，那执行效率还是非常高的，甚至有可能比nl更高. **** hint UNNEST – 子查询展开,不让子查询嵌套在里面,也就是这里说的子查询非嵌套化. NO_UNNEST – 子查询嵌套化. |
*** 查看生产环境子查询非嵌套_unnest_subquery value
SQL> select x.ksppinm name, y.ksppstvl value, y.ksppstdf isdefault
2 from sys.x$ksppi x, sys.x$ksppcv y
3 where x.inst_id = userenv('Instance')
4 and y.inst_id = userenv('Instance')
5 and x.indx = y.indx
6 and x.ksppinm like '_unnest%'
7 order by translate(x.ksppinm, ' _', ' ');
NAME VALUE ISDEFAULT
-----------------------------------------------------------------------------------------------------
_unnest_subquery TRUE TRUE
SQL>
2 from sys.x$ksppi x, sys.x$ksppcv y
3 where x.inst_id = userenv('Instance')
4 and y.inst_id = userenv('Instance')
5 and x.indx = y.indx
6 and x.ksppinm like '_unnest%'
7 order by translate(x.ksppinm, ' _', ' ');
NAME VALUE ISDEFAULT
-----------------------------------------------------------------------------------------------------
_unnest_subquery TRUE TRUE
SQL>
分析 ： cs_order_item.order_id选择性较高的索引，CBO 没做成子查询扩展，
导致cs_order_item 14M的驱动表和CS_CHANGE_ACCE 小表做循环
(驱动表(CS_ORDER_ITEM)行数等于(CS_CHANGE_ACCE)被驱动表扫描次数(14M) )
所以我们选择用union all形式手动子查询扩展(**注释用hint无作用)
SQL> SELECT COUNT(1)
2 FROM (select t.*
3 from cs_order_item t
4 where 1 = 1
5 AND (T.ORDER_ID = 1528168)
6 and t.oi_source in (1, 2)
7 union all
8 select t.*
9 from cs_order_item t
10 where 1 = 1
11 AND t.oi_source in (1, 2)
12 and T.ORDER_ID IN
13 (SELECT CA.ORDER_ID
14 FROM CS_CHANGE_ACCE CA
15 WHERE CA.TAG IN (SELECT t.TAG
16 FROM CS_CHANGE_ACCE t
17 WHERE t.ORDER_ID = 1537775)));
COUNT(1)
----------
12
已用时间: 00: 00: 00.02
执行计划
----------------------------------------------------------
Plan hash value: 3789129724
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU )| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 158 (2 )| 00: 00:02 |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | VIEW | | 69 | | 158 (2 )| 00: 00:02 |
| 3 | UNION-ALL | | | | | |
|* 4 | TABLE ACCESS BY INDEX ROWID | CS_ORDER_ITEM | 23 | 207 | 18 (0)| 00:00 :01 |
|* 5 | INDEX RANGE SCAN | INDEX_ORDER_ID | 23 | | 3 (0)| 00:00 :01 |
| 6 | NESTED LOOPS | | | | | |
| 7 | NESTED LOOPS | | 46 | 1426 | 140 (2)| 00:00 :02 |
| 8 | NESTED LOOPS | | 2 | 44 | 106 (2)| 00:00 :02 |
| 9 | TABLE ACCESS BY INDEX ROWID | CS_CHANGE_ACCE | 1 | 11 | 2 (0)| 00:00 :01 |
|* 10 | INDEX UNIQUE SCAN | PK_CHANGE_ORDER_ID | 1 | | 1 (0)| 00:00 :01 |
|* 11 | TABLE ACCESS FULL | CS_CHANGE_ACCE | 2 | 22 | 104 (2)| 00:00 :02 |
|* 12 | INDEX RANGE SCAN | INDEX_ORDER_ID | 23 | | 2 (0)| 00:00 :01 |
|* 13 | TABLE ACCESS BY INDEX ROWID | CS_ORDER_ITEM | 23 | 207 | 17 (0)| 00:00 :01 |
--------------------------------------------------------------------------------------------------------
Predicate Information ( identified by operation id):
---------------------------------------------------4 - filter("T"."OI_SOURCE"=1 OR "T"."OI_SOURCE"=2)
5 - access("T"."ORDER_ID"=1528168)
10 - access("T"."ORDER_ID"=1537775)
11 - filter("CA"."TAG"="T"."TAG")
12 - access("T"."ORDER_ID"="CA"."ORDER_ID")
13 - filter("T"."OI_SOURCE"=1 OR "T"."OI_SOURCE"=2)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
217 bytes sent via SQL*Net to client
252 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
总结：
看来CBO很多时候遇到一些写法也做不到我们想的那样，所以需要我们手动做子查询扩展；
最近发现好多sql的过滤条件 ( a.id=1 or exists/in ..子查询) 都选择了filter 执行计划，都选择了union all 做的解决。
【源于本人笔记】 若有书写错误，表达错误，请指正...
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/28602568/viewspace-1462937/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
最新文章
- Oracle 跟踪/告警/监听日志的清理脚本
- Oracle JAVA变量类型与字段类型不一致，分区表全扫(INTERNAL_FUNCTION隐含转换)的优化
- EXPDP dumpfile和parallel的关系
- Oracle not exist子查询全扫的优化
- Oracle 不知用户的密码时,如何获取dblink创建语句
- Oracle 快速将数据导出到CSV(Excel)文件的方法介绍
- Redis 基本搭建
- PLSQL Developer Profiler 使用 【存储过程调优 工具推荐】
- Oracle 针对子查询里有group by 表全扫的优化
- Oracle 使用sqlnet.ora/trigger限制/允许某IP或IP段访问指定用户
