precompute_subquery hint的使用一

最新推荐文章于 2024-08-31 23:36:42 发布

cuiganglan0701

最新推荐文章于 2024-08-31 23:36:42 发布

阅读量167

点赞数

文章标签：数据库

前两天看到一个优化的文档，提到子查询和or一起使用执行起来速度很慢，说是ORACLE优化器的一个bug，还说到使用一个hint可以避免这个问题，查了下该Hint叫precompute_subquery，不过是从10.2.0.1开始使用的一个hint，其主要作用是让编译器预先计算子查询里面的值，从而提高运行的效率。这hint是有关Oracle 光标管理和SQL内部执行方面的知识，将一贴子翻译在此：

创建两个测试表： T1 and T2

SQL> create table t1 as select rownum a from dual connect by level < 10;

Table created.

SQL> create table t2 as select rownum+10 b from dual connect by level < 10;

Table created.

SQL> select * from t1;

----------

9 rows selected.

SQL> select * from t2;

----------

9 rows selected.

现在运行一个带有简单子查询的查询:

SQL> select a from t1 where a in (select b from t2);

no rows selected

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); dbms_xplan.display_cursor 获取执行过的sql的执行计划，上述语句就是不需要sqlid，简单获取执行计划。

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------

SQL_ID aucw6byq3d5q8, child number 0

-------------------------------------

select a from t1 where a in (select b from t2)

Plan hash value: 561629455

----------------------------------------------------------------------------

----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | | |

|* 1 | HASH JOIN SEMI | | 1 | 1066K| 1066K| 1056K (0)|

| 2 | TABLE ACCESS FULL | T1 | 9 | | | |

| 3 | VIEW | VW_NSO_1 | 9 | | | |

| 4 | TABLE ACCESS FULL| T2 | 9 | | | |

---------------------------------------------------------------------Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("A"="B")

注意执行了一个哈希半联结，将2个子行源用条件“A=B”联结起来。

现在，让我们在子查询里面使用PRECOMPUTE_SUBQUERY来运行同样的查询。

SQL> select a

from t1

where a in (select /*+ PRECOMPUTE_SUBQUERY */b from t2);

no rows selected

SQL>

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------

SQL_ID fvnqhjkcjnybx, child number 0

-------------------------------------

select a from t1 where a in (select /*+ PRECOMPUTE_SUBQUERY */b from

t2)

Plan hash value: 3617692013

-------------------------------------------

-------------------------------------------

| 0 | SELECT STATEMENT | | |

|* 1 | TABLE ACCESS FULL| T1 | 5 |

-------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(("A"=11 OR "A"=12 OR "A"=13 OR "A"=14 OR "A"=15 OR "A"=16

OR "A"=17 OR "A"=18 OR "A"=19))

看看发生了什么！连接没有了，似乎表T2根本没被访问（因为只有一个TABLE ACCESS 访问读取表T1的行源）。

还出现了一个filter 条件，其中罗列出了表T2中的所有值，这些值是如何检索来的呢?

一个简单的sql_trace 揭示了这一点：

PARSING IN CURSOR #1 len=97 dep=1 uid=0 ct=3 lid=0 tim=1232490329981197 hv=1703909501 ad='3175d2cc' sqlid='7b0jqyxksz63x'

SELECT /*+ BYPASS_RECURSIVE_CHECK */ DISTINCT * FROM (select /*+ PRECOMPUTE_SUBQUERY */b from t2)

END OF STMT

PARSE #1:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=393655594,tim=1232490329981197

EXEC #1:c=1000,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=393655594,tim=1232490329981197

FETCH #1:c=4000,e=0,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=1,plh=393655594,tim=1232490329981197

FETCH #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=1,plh=393655594,tim=1232490329981197

FETCH #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=393655594,tim=1232490329981197

STAT #1 id=1 cnt=9 pid=0 pos=1 bj=0 p='HASH UNIQUE (cr=3 pr=0 pw=0 time=0 us cost=3 size=27 card=9)'

STAT #1 id=2 cnt=9 pid=1 pos=1 bj=79810 p='TABLE ACCESS FULL T2 (cr=3 pr=0 pw=0 time=0 us cost=2 size=27 card=9)'

CLOSE #1:c=0,e=0,dep=1,type=0,tim=1232490329981197

PARSING IN CURSOR #2 len=75 dep=0 uid=0 ct=3 lid=0 tim=1232490330002221 hv=2568649085 ad='3175d5cc' sqlid='fvnqhjkcjnybx'

select a

from t1

where a in (select /*+ PRECOMPUTE_SUBQUERY */b from t2)

END OF STMT

PARSE #2:c=28996,e=41781,p=0,cr=3,cu=0,mis=1,r=0,dep=0,og=1,plh=3617692013,tim=1232490330002221

EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3617692013,tim=1232490330002221

FETCH #2:c=0,e=0,p=0,cr=3,cu=0,mis=0,r=0,dep=0,og=1,plh=3617692013,tim=1232490330002221

STAT #2 id=1 cnt=0 pid=0 pos=1 bj=79809 p='TABLE ACCESS FULL T1 (cr=3 pr=0 pw=0 time=0 us cost=2 size=15 card=5)'

CLOSE #2:c=0,e=0,dep=0,type=0,tim=1232490330002221

在sql_trace的输出里面，有两个查询，第二个查询就是我执行的（它的dep=0 因此它是一个“top-level”的查询）。但是，就在开始解析我的查询之前，还执行了另外一个查询（跟踪里面的第一个查询），dep=1，这意味着它是一个递归的查询。

这就是Oracle 怎样从表T2递归地获取唯一的值，存储这个结果集在内存里，并将这些值作为过滤的条件传给给我的查询。这允许 Oracle 只从表T1里面获取数据，然后将这些行与硬编码的过滤条件进行比较，而不必在两个行源（rowsources）之间做某种半联结。

一个 10053 跟踪也同样可以显示下面的行:

CSE: Considering common sub-expression elimination in query block SEL$2 (#0)

*************************

Common Subexpression elimination (CSE)

*************************

CSE: CSE not performed on query block SEL$2 (#0).

CSE: CSE not performed on query block SEL$1 (#0).

Copy query block qb# -1 () : SELECT /*+ BYPASS_RECURSIVE_CHECK */ DISTINCT * FROM (select /*+ PRECOMPUTE_SUBQUERY */b from t2)

当评估子查询的rewrite 选项时，optimizer 展开该子查询的文本到一个单独的cursor里面 (创建一个查询块 qb# -1).

未完待续.......

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7177735/viewspace-707948/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7177735/viewspace-707948/

cuiganglan0701

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
precompute_subquery hint的使用一

前两天看到一个优化的文档，提到子查询和or一起使用执行起来速度很慢，说是ORACLE优化器的一个bug，还说到使用一个hint可以避免这个问题，查了下该Hint叫precompute_subquery，不过是从10.2.0....
复制链接

扫一扫