关于sql优化cardinality的hint的应用一例

原创杜伟 2023-07-19

1181

关于cardinality的hint的说明：

CARDINALITY是针对单个目标表的Hint，它可以用来设置对目标表执行扫描操作后返回结果集的Cardinality的值，它所能够设置的扫描类型包括对目标表的全表扫描、索引范围扫描、索引全扫描和索引快速全扫描。

/*+ cardinality(t 1000) */ ---针对t表设置其结果集是1000，在数据库优化器进行执行计划预估时，其结果集是10000。

示例如下：

select /*+ cardinality(emp 1000) */ * from emp；
 Plan Hash Value  : 3956160932 

--------------------------------------------------------------------
| Id | Operation           | Name | Rows | Bytes | Cost | Time     |
--------------------------------------------------------------------
|  0 | SELECT STATEMENT    |      | 1000 | 42000 |    3 | 00:00:01 |
|  1 |   TABLE ACCESS FULL | EMP  | 1000 | 42000 |    3 | 00:00:01 |

生产11.2.0.4上有个不怎么复杂的sql其执行时长近2000s，其语句如下：

select   * from dw_20230718 t where CREATED like '20220301%' AND object_TYPE ='TABLE'
and object_name not in 
(select object_name from dw_20230718 group by object_name,status,owner having count(1)>32 )
-----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Rows    | Bytes     | Cost  | Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |       1 |       111 | 11701 | 00:02:21 |
| * 1 |   FILTER                       |             |         |           |       |          |
| * 2 |    TABLE ACCESS BY INDEX ROWID | DW_20230718 |       1 |       111 |     5 | 00:00:01 |
| * 3 |     INDEX RANGE SCAN           | IDX_0718    |       1 |           |     3 | 00:00:01 |
| * 4 |    FILTER                      |             |         |           |       |          |
|   5 |     HASH GROUP BY              |             |      24 |       912 | 11696 | 00:02:21 |
|   6 |      TABLE ACCESS FULL         | DW_20230718 | 2761440 | 104934720 | 11619 | 00:02:20 |
-----------------------------------------------------------------------------------------------

该执行计划在最消耗性能的就是子查询执行多次，需要消除第四步的FILTER让执行计划走上hash join anti;

dw_20230718表是270w行的一个表，经过基于dba_objects多次插入，created是修改为varchar2(14)的存储时间数字到秒如 20230718120132；

CREATED like '20220301%'真实记录有1300多条，对应索引的信息如下：

可以肯定，CREATED like '20220301%'在预估行的时候，优化器预估错了，这种like不好正确预估真实的行

尝试收集字段的直方图还是不行，索引也是会自行收集直方图的。

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SCOTT',TABNAME=>'DW_20230718',METHOD_OPT=>'FOR COLUMNS SIZE AUTO CREATED',no_invalidate => FALSE);

尝试hint use_hash不行，因为子查询是经过分组查询的，相当于一个视图了。想起来之前看过一个cardinality的hint，今天就来试试吧！

select /*+ cardinality(t 1000) */  * from dw_20230718 t where CREATED like '20220301%' AND object_TYPE ='TABLE'
and object_name not in 
(select object_name from dw_20230718 group by object_name,status,owner having count(1)>32 );-----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Rows    | Bytes     | Cost  | Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |      10 |      1770 | 21611 | 00:04:20 |
| * 1 |   HASH JOIN ANTI NA            |             |      10 |      1770 | 21611 | 00:04:20 |
| * 2 |    TABLE ACCESS BY INDEX ROWID | DW_20230718 |    1000 |    111000 |     5 | 00:00:01 |
| * 3 |     INDEX RANGE SCAN           | IDX_0718    |       1 |           |     3 | 00:00:01 |
|   4 |    VIEW                        | VW_NSO_1    |   59515 |   3927990 | 21606 | 00:04:20 |
| * 5 |     FILTER                     |             |         |           |       |          |
|   6 |      HASH GROUP BY             |             |   59515 |   2261570 | 21606 | 00:04:20 |
|   7 |       TABLE ACCESS FULL        | DW_20230718 | 2761440 | 104934720 | 11619 | 00:02:20 |
-----------------------------------------------------------------------------------------------