【转】CBO hint:noITeye - 凯时娱乐

【转】CBO hint:noITeye

2019-01-11 15:57:49 | 作者: 耘豪 | 标签: 查询,咱们,假如 | 浏览: 1860

原博客地址:

 

常常有人把这三个hint搞混,首要是由于对三种重写原理不清楚。特总结如下。(实验环境为10204)

1. no_unnest, unnest

unnest咱们称为对子查询打开,望文生义,便是别让子查询孑立地嵌套(nest)在里面。

所以un_unnest两层否定代表必定,即让子查询不打开,让它嵌套(nest)在里面。

现做一个简略的实验:

create table hao1 as select * from dba_objects;
create table hao2 as select * from dba_objects;

analyze table hao1 compute statistics;

analyze table hao2 compute statistics;

SQL select hao1.object_id from hao1 where exists
  2  (select 1 from hao2 where hao1.object_id=hao2.object_id*10);

1038 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2662903432

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     8 |    80   (3)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |      |     1 |     8 |    80   (3)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| HAO1 | 10662 | 42648 |    40   (3)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| HAO2 | 10663 | 42652 |    40   (3)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("HAO1"."OBJECT_ID"="HAO2"."OBJECT_ID"*10)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        352  consistent gets
          0  physical reads
          0  redo size
      18715  bytes sent via SQL*Net to client
       1251  bytes received via SQL*Net from client
         71  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1038  rows processed


这儿子查询主动打开(unnest),即HAO2和HAO1 hash join在一起。

接下来假如咱们不期望HAO2打开,想先让它独自的履行完,然后再来和外部查询进行一种叫做FILTER的操作。

那么咱们参加hint no_unnest:

SQL select hao1.object_id from hao1 where exists
  2  (select /*+no_unnest*/ 1 from hao2 where hao1.object_id=hao2.object_id*10);

1038 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2565749733

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     4 | 10750   (1)| 00:01:48 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| HAO1 | 10662 | 42648 |    40   (3)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| HAO2 |     1 |     4 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "HAO2" "HAO2"
              WHERE "HAO2"."OBJECT_ID"*10=:B1))
   3 - filter("HAO2"."OBJECT_ID"*10=:B1)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
    1369157  consistent gets
          0  physical reads
          0  redo size
      18715  bytes sent via SQL*Net to client
       1251  bytes received via SQL*Net from client
         71  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1038  rows processed

这儿HAO1和HAO2进行了一种FILTER操作,这个操作在《Cost Based Oracle Fundamental》此书第九章有介绍。他其实很像咱们了解的neested loop,但它的共同之处在于会保护一个hash table。

举例,假如HAO1里取出object_id=1,那么关于HAO2来说即select 1 from hao2 where hao2.object_id*10=1,假如条件满意,那么关于子查询,输入输出对,即为(1(HAO1.object_id),1(常量))。

他存储在hash table里,而且由于条件满意,HAO1.object_id=1被放入成果集。

然后接着从HAO1取出object_id=2,假如子查询仍旧条件满意,那么子查询发生另一个输入和输出,即(2,1),被放入hash table里;而且HAO1.object_id=2被放入成果集。

接着假定HAO1里有重复的object_id,例如咱们第三次从HAO1取出的object_id=2,那么由于咱们关于子查询来说,已经有输入输出对(2,1)在hash table里了,所以就不用去再次全表扫描HAO2了,ORACLE十分聪明地知道object_id=2是成果集。这儿,filter和neested loop比较,省去了一次全表扫描HAO2。

这个hash table是有巨细约束的,当被占满的时分,后续新的HAO1.object_id的FILTER就相似neested loop了。

由此可见,从buffer gets层面上来看,FILTER是应该优于neested loop的,特别当外部查询需求传递给子查询的输入(此例中为HAO1.object_id)的distinct value十分小时,FILTER就会显得更优。

即便在我这个比如中,HAO1.object_id的distinct value上万,我比照了一下neested loop,FILTER依然略优:

SQL select /*+use_nl(hao1 hao2)*/ hao1.object_id from hao1,hao2 where hao1.object_id=hao2.object_id*10;


1038 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 251947914

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 10663 | 85304 |   404K  (2)| 01:07:23 |
|   1 |  NESTED LOOPS      |      | 10663 | 85304 |   404K  (2)| 01:07:23 |
|   2 |   TABLE ACCESS FULL| HAO1 | 10662 | 42648 |    40   (3)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| HAO2 |     1 |     4 |    38   (3)| 00:00:01 |
---------------------------------------------------------------------------

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

   3 - filter("HAO1"."OBJECT_ID"="HAO2"."OBJECT_ID"*10)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
    1503621  consistent gets
          0  physical reads
          0  redo size
      18715  bytes sent via SQL*Net to client
       1251  bytes received via SQL*Net from client
         71  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1038  rows processed

FILTER的consistent gets是1369157,neested loop的consistent gets是1503621。

假如咱们期望验证我前面的定论,咱们能够用distinct value较小的object_type来做个相似的比照实验。

SQL select hao1.object_id from hao1 where exists
  2  (select /*+no_unnest*/ 1 from hao2 where hao1.object_type=hao2.object_type);

10662 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2565749733

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   288 |  3168 |   114   (1)| 00:00:02 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| HAO1 | 10662 |   114K|    40   (3)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| HAO2 |     2 |    14 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "HAO2" "HAO2"
              WHERE "HAO2"."OBJECT_TYPE"=:B1))
   3 - filter("HAO2"."OBJECT_TYPE"=:B1)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      17012  consistent gets
          0  physical reads
          0  redo size
     187491  bytes sent via SQL*Net to client
       8302  bytes received via SQL*Net from client
        712  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10662  rows processed

可见,同样是HAO1和HAO2的全表扫描后的FILTER操作,却由于传给子查询的输入的distinct value的不同,两者相差的consistent gets却如此巨大,这跟neested loop是彻底不一样的。

当然,关于如此的两个全表扫描的成果集,运用hash join是最佳办法。

SQL select hao1.object_id from hao1 where exists
  2  (select 1 from hao2 where hao1.object_type=hao2.object_type);

10662 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3371915275

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      | 10662 |   187K|    81   (4)| 00:00:01 |
|*  1 |  HASH JOIN RIGHT SEMI|      | 10662 |   187K|    81   (4)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | HAO2 | 10663 | 74641 |    40   (3)| 00:00:01 |
|   3 |   TABLE ACCESS FULL  | HAO1 | 10662 |   114K|    40   (3)| 00:00:01 |
-----------------------------------------------------------------------------

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

   1 - access("HAO1"."OBJECT_TYPE"="HAO2"."OBJECT_TYPE")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        985  consistent gets
          0  physical reads
          0  redo size
     187491  bytes sent via SQL*Net to client
       8302  bytes received via SQL*Net from client
        712  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10662  rows processed

所以,什么时分该用no_unnest使得子查询能够独立的履行结束之后再跟外围的查询做FILTER?

首要,子查询的回来成果集应该较小,然后外围查询的输入的distinct value也应该较小(例如object_type)。

 

2.push_subq

假如说no_unnest是为了让子查询不打开,独立的完结,那么push_subq便是为了让子查询最早进行join。

所以,这个hint其实是操控的join的次序。

例如某次在出产库中遇到的一个SQL,简化一下然后模仿一下:

create table hao1 as select * from dba_objects;
create table hao2 as select * from dba_objects;
create table hao3 as select * from dba_objects;
create table hao4 as select * from dba_objects;

create index hao3idx on hao3(object_id);

(analyze all tables。)

select hao1.object_name from 
hao1,hao2,hao4
where hao1.object_name like %a%
and hao1.object_id+hao2.object_id 50
and hao4.object_type=hao1.object_type
and 11 in
(SELECT  hao3.object_id FROM hao3 WHERE hao1.object_id = hao3.object_id);

关于如上的SQL,其间hao3和hao1在子查询中join,

很明显,假如先让hao1和hao3经过join,成果集估量只要一行,或许没有。

可是,此刻CBO做出的履行计划为:

--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         | 89077 |  3131K|  2070M  (1)|999:59:59 |
|*  1 |  FILTER              |         |       |       |            |          |
|*  2 |   HASH JOIN          |         |  3234M|   108G|   289K (24)| 00:48:17 |
|   3 |    TABLE ACCESS FULL | HAO4    | 36309 |   212K|   126   (3)| 00:00:02 |
|   4 |    NESTED LOOPS      |         |  3296K|    94M|   224K  (2)| 00:37:28 |
|*  5 |     TABLE ACCESS FULL| HAO1    |  1816 | 47216 |   126   (3)| 00:00:02 |
|*  6 |     TABLE ACCESS FULL| HAO2    |  1815 |  7260 |   124   (2)| 00:00:02 |
|*  7 |   FILTER             |         |       |       |            |          |
|*  8 |    INDEX RANGE SCAN  | HAO3IDX |     1 |     4 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT /*+ */ 0 FROM "HAO3" "HAO3" WHERE 11=:B1
              AND "HAO3"."OBJECT_ID"=11))
   2 - access("HAO4"."OBJECT_TYPE"="HAO1"."OBJECT_TYPE")
   5 - filter("HAO1"."OBJECT_NAME" LIKE %a%)
   6 - filter("HAO1"."OBJECT_ID"+"HAO2"."OBJECT_ID" 50)
   7 - filter(11=:B1)
   8 - access("HAO3"."OBJECT_ID"=11)

由上可见,hao1和hao2,hao4先进行无穷无尽的join之后,最终才跟hao3 join,这是十分坏的plan。

所以,咱们期望hao1和hao3地点子查询先join,能够选用push_subq:

select /*+push_subq(@tmp)*/ hao1.object_name from 
hao1,hao2,hao4
where hao1.object_name like %a%
and hao1.object_id+hao2.object_id 50
and hao4.object_type=hao1.object_type
and 11 in
(SELECT /*+QB_Name(tmp)*/ hao3.object_id FROM hao3 WHERE hao1.object_id = hao3.object_id);

--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |   161M|  5552M| 14749  (24)| 00:02:28 |
|*  1 |  HASH JOIN           |         |   161M|  5552M| 14748  (24)| 00:02:28 |
|   2 |   TABLE ACCESS FULL  | HAO4    | 36309 |   212K|   126   (3)| 00:00:02 |
|   3 |   NESTED LOOPS       |         |   164K|  4828K| 11386   (2)| 00:01:54 |
|*  4 |    TABLE ACCESS FULL | HAO1    |    91 |  2366 |   126   (3)| 00:00:02 |
|*  5 |     FILTER           |         |       |       |            |          |
|*  6 |      INDEX RANGE SCAN| HAO3IDX |     1 |     4 |     1   (0)| 00:00:01 |
|*  7 |    TABLE ACCESS FULL | HAO2    |  1815 |  7260 |   124   (2)| 00:00:02 |
--------------------------------------------------------------------------------

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

   1 - access("HAO4"."OBJECT_TYPE"="HAO1"."OBJECT_TYPE")
   4 - filter("HAO1"."OBJECT_NAME" LIKE %a% AND  EXISTS (SELECT /*+
              PUSH_SUBQ QB_NAME ("TMP") */ 0 FROM "HAO3" "HAO3" WHERE 11=:B1 AND
              "HAO3"."OBJECT_ID"=11))
   5 - filter(11=:B1)
   6 - access("HAO3"."OBJECT_ID"=11)
   7 - filter("HAO1"."OBJECT_ID"+"HAO2"."OBJECT_ID" 50)

加上hint后,SQL会在1秒以内完结。

 

3.push_pred

在谈到push_pred这个hint时,首要要搞清楚mergeable view和unmergeable view的差异。

这个在concept上有清晰解说:

Mergeable and Unmergeable Views

The optimizer can merge a view into a referencing query block when the view has one or more base tables, provided the view does not contain:

版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表凯时娱乐立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章