cardinality hint

cardinality hint可以通过强制的方式告诉Oracle优化器会返回多少数据,在某些场景可以通过这个hint来改变优化器的关联顺序和join方式。但是它也是把双刃剑,在使用的时候请慎重。
实验脚本

env 12.1.0.2

drop table t1_object purge;
drop table t2_object purge;

create table t1_object(id1 number,name varchar2(50));
create table t2_object(id2 number,name varchar2(50));

insert into t1_object select rownum,object_name from dba_objects;
insert into t2_object select rownum + 10000,object_name from dba_objects where rownum <= 500;
commit; 
begin 
    dbms_stats.gather_table_stats(                              
        ownname         => user,
        tabname         => 't1_object',
        cascade         => true,
        estimate_percent    => null, 
        method_opt      =>'for all columns size 1'
    );
end;
/

begin
    dbms_stats.gather_table_stats(
        ownname         => user,
        tabname         => 't2_object',
        cascade         => true,
        estimate_percent    => null, 
        method_opt      =>'for all columns size 1'
    );
end;
/

set autotrace trace explain

select t1.id1,t1.name,t2.id2,t2.name from t1_object t1,t2_object t2 where t1.id1 = t2.id2;

select /*+ cardinality(t2 1000000) */ t1.id1,t1.name from t1_object t1,t2_object t2 where t1.id1 = t2.id2;

实验的执行计划输出如下
–no hint–

SQL> select t1.id1,t1.name,t2.id2,t2.name from t1_object t1,t2_object t2 where t1.id1 = t2.id2;

Execution Plan
----------------------------------------------------------
Plan hash value: 3226144231

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |   500 | 24500 |   124   (2)| 00:00:01 |
|*  1 |  HASH JOIN         |           |   500 | 24500 |   124   (2)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T2_OBJECT |   500 |  9500 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T1_OBJECT | 91064 |  2667K|   121   (1)| 00:00:01 |
--------------------------------------------------------------------------------

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

   1 - access("T1"."ID1"="T2"."ID2")

— add hint–

SQL> select /*+ cardinality(t2 1000000) */ t1.id1,t1.name from t1_object t1,t2_object t2 where t1.id1 = t2.id2;

Execution Plan
----------------------------------------------------------
Plan hash value: 2994827088

----------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |  1000K|    33M|       |  1125   (2)| 00:00:01 |
|*  1 |  HASH JOIN         |           |  1000K|    33M|  3736K|  1125   (2)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1_OBJECT | 91064 |  2667K|       |   121   (1)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2_OBJECT |  1000K|  4882K|       |     3  (34)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   1 - access("T1"."ID1"="T2"."ID2")

通过cardinality hint的加入,强制告诉Oracle优化器t2_object返回100w行记录,所以hash join将T2_OBJECT从build table改为probe table来有利于hash join。

10053 trace

Access path analysis for T2_OBJECT            --cardinality hint
***************************************
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for T2_OBJECT[T2] 
  SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
  Table: T2_OBJECT  Alias: T2
    Card: Original: 500.000000    >> Single Tab Card adjusted from 500.000000 to 1000000.000000 due to opt_estimate hint
  Rounded: 1000000  Computed: 1000000.000000  Non Adjusted: 500.000000
  Scan IO  Cost (Disk) =   2.000000
  Scan CPU Cost (Disk) =   20079242.880000
  Total Scan IO  Cost  =   2.000000 (scan (Disk))
                       =   2.000000
  Total Scan CPU  Cost =   20079242.880000 (scan (Disk))
                       =   20079242.880000
  Access Path: TableScan
    Cost:  3.396720  Resp: 3.396720  Degree: 0
      Cost_io: 2.000000  Cost_cpu: 20079243
      Resp_io: 2.000000  Resp_cpu: 20079243
  Best:: AccessPath: TableScan
         Cost: 3.396720  Degree: 1  Resp: 3.396720  Card: 1000000.000000  Bytes: 0.000000
此条目发表在Hint, Uncategorized分类目录。将固定链接加入收藏夹。

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s