Oracle 数据偏斜和连接基数估计

你好，克里斯/康纳，

我正在尝试解决特定sql的性能问题，而我正在努力解决的一件事是在存在数据偏斜的情况下估计连接基数。我不得不承认，我在提出一个可复制的测试用例方面取得的成功有限，但是如果你能忍受我并帮助我解决我的问题，那就太好了。
以下是测试用例设置

drop table t_parent purge ;
drop table t_child purge ;

create table t_parent (tc_id, tp_id, tc_padding) as select level, mod(level, 357), dbms_random.string('a',100) from dual connect by level <= 400 ;

delete from t_parent where tp_id = 0;

commit ;

alter table t_parent add constraint t_parent_pk primary key (tc_id) ;


CREATE TABLE t_child 
    AS
        SELECT
            ROWNUM + 100 tch_id,
            tc_id,
            dbms_random.string('a',100) tch_padding
        FROM
            t_parent,TABLE ( cast( MULTISET (
    SELECT
        level
    FROM
        dual
    CONNECT BY
        level <= decode(mod(t_parent.tc_id, 357), 3, exp(10), t_parent.tc_id)
) as sys.odcinumberlist ) );


alter table t_child add constraint t_child_pk primary key (tch_id) ;
复制

T_id和T_PARENT表中的TC_ID列具有父子关系。T_CHILD引用T_PARENT表 (虽然没有定义显式FK)，我试图在T_CHILD表中为TC_ID列创建数据倾斜，其中该列具有254多个不同的值 (导致在其上创建高度平衡直方图，以便使其类似于原始sql中的表)。

select tc_id, count(*) from t_child  where tc_id in (3,7) group by tc_id ;

TC_ID COUNT(*)
7 7
3 22026

exec dbms_stats.gather_table_stats(user,'T_PARENT') ;
exec dbms_stats.gather_table_stats(user,'T_CHILD') ;
复制

下面的简单sql产生足够接近的估计。

select /*+ GATHER_PLAN_STATISTICS */ count(*) from t_child where tc_id = 3 ;

COUNT(*)
22026

SQL_ID  fc7qd7fgvkp0a, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ count(*) from t_child where tc_id 
= :"SYS_B_0"
 
Plan hash value: 1228457128
 
-----------------------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |      1 |        |  1884 (100)|      1 |00:00:00.05 |    1988 |
|   1 |  SORT AGGREGATE    |         |      1 |      1 |            |      1 |00:00:00.05 |    1988 |
|*  2 |   TABLE ACCESS FULL| T_CHILD |      1 |  22372 |  1884   (1)|  22026 |00:00:00.04 |    1988 |
-----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("TC_ID"=:SYS_B_0)
复制

但是，当我加入它时，估计值还远远不够接近 (161 vs 7或161 vs 22026)。
在原始sql中，这2个表被连接到更多的表，并且此连接充当驱动行源。
原始sql在将此rowsource (T_PARENT和T_CHILD的join) 与后续表连接时始终使用嵌套循环连接。当此rowsource产生较少的行数 (最多50个) 时，sql的性能很好，但是对于特定的极值，它会产生大量的行 (超过2000个)，并且性能会受到影响。对于特定情况，原始sql估计T_CHILD将产生3行与实际2000。

只有在11级强制进行动态采样，才能为极端情况提供更好的估计。
有没有更好的方法来影响optimizer为这种偏斜数据提出更好的估计？

select /*+ GATHER_PLAN_STATISTICS */ count(*) from t_parent tp, t_child tc
where tp.tp_id = tc.tc_id
and tp.tc_id = 3 ;

COUNT(*)
22026

SQL_ID  d1qtam989vqp2, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ count(*) from t_parent tp, t_child 
tc where tp.tp_id = tc.tc_id and tp.tc_id = :"SYS_B_0"
 
Plan hash value: 2770800883
 
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |      1 |        |  1884 (100)|      1 |00:00:00.04 |    1990 |
|   1 |  SORT AGGREGATE               |             |      1 |      1 |            |      1 |00:00:00.04 |    1990 |
|   2 |   NESTED LOOPS                |             |      1 |    161 |  1884   (1)|  22026 |00:00:00.02 |    1990 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T_PARENT    |      1 |      1 |     1   (0)|      1 |00:00:00.01 |       2 |
|*  4 |     INDEX UNIQUE SCAN         | T_PARENT_PK |      1 |      1 |     0   (0)|      1 |00:00:00.01 |       1 |
|*  5 |    TABLE ACCESS FULL          | T_CHILD     |      1 |    161 |  1883   (1)|  22026 |00:00:00.01 |    1988 |
--------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("TP"."TC_ID"=:SYS_B_0)
   5 - filter("TP"."TP_ID"="TC"."TC_ID")

select /*+ GATHER_PLAN_STATISTICS */ count(*) from t_parent tp, t_child tc
where tp.tp_id = tc.tc_id
and tp.tc_id = 7 ;

COUNT(*)
7

SQL_ID  1z03mw1a33sfz, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ count(*) from t_parent tp, t_child 
tc where tp.tp_id = tc.tc_id and tp.tc_id = 7
 
Plan hash value: 2770800883
 
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |      1 |        |  1884 (100)|      1 |00:00:00.02 |    1990 |
|   1 |  SORT AGGREGATE               |             |      1 |      1 |            |      1 |00:00:00.02 |    1990 |
|   2 |   NESTED LOOPS                |             |      1 |    161 |  1884   (1)|      7 |00:00:00.01 |    1990 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T_PARENT    |      1 |      1 |     1   (0)|      1 |00:00:00.01 |       2 |
|*  4 |     INDEX UNIQUE SCAN         | T_PARENT_PK |      1 |      1 |     0   (0)|      1 |00:00:00.01 |       1 |
|*  5 |    TABLE ACCESS FULL          | T_CHILD     |      1 |    161 |  1883   (1)|      7 |00:00:00.01 |    1988 |
--------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("TP"."TC_ID"=7)
   5 - filter("TP"."TP_ID"="TC"."TC_ID")


SQL_ID  911tc3mn58zv3, child number 0
-------------------------------------
select /*+ DYNAMIC_SAMPLING(11) GATHER_PLAN_STATISTICS */ count(*) from 
t_parent tp, t_child tc where tp.tp_id = tc.tc_id and tp.tc_id = 
:"SYS_B_0"
 
Plan hash value: 2770800883
 
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |      1 |        |  1884 (100)|      1 |00:00:00.02 |    1990 |
|   1 |  SORT AGGREGATE               |             |      1 |      1 |            |      1 |00:00:00.02 |    1990 |
|   2 |   NESTED LOOPS                |             |      1 |  21904 |  1884   (1)|  22026 |00:00:00.02 |    1990 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T_PARENT    |      1 |      1 |     1   (0)|      1 |00:00:00.01 |       2 |
|*  4 |     INDEX UNIQUE SCAN         | T_PARENT_PK |      1 |      1 |     0   (0)|      1 |00:00:00.01 |       1 |
|*  5 |    TABLE ACCESS FULL          | T_CHILD     |      1 |  21904 |  1883   (1)|  22026 |00:00:00.01 |    1988 |
--------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("TP"."TC_ID"=:SYS_B_0)
   5 - filter("TP"."TP_ID"="TC"."TC_ID")
 
Note
-----
   - dynamic sampling used for this statement (level=0)

复制

问题是你在t_parent的一列上过滤，但在另一列上加入。

所以数据库直到运行时才知道它从t_child中获取哪个值。这使得获得正确的基数变得棘手!

在此特定查询中，在

t_child (tc_id)

应该会有所帮助，无论行估计如何，因为数据库可以扫描索引以获取计数。

在一般情况下，增加动态统计级别应该会有所帮助。

另一种解决方案是将联接拆分为单独的查询。一般来说，这是个坏主意。但是，如果您从父级获得最多一行，则子级的改进行估计可能会抵消这一点。