Thanks for the question, Narendra.

Asked: July 13, 2020 - 3:42 pm UTC

Last updated: July 15, 2020 - 9:36 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Hello Chris/Connor,

I am trying to troubleshoot a performance issue with a specific sql and one of the things I am struggling with is estimate of join cardinality in the presence of data skew. I have to admit that I had limited success in coming up with a reproducible test case but would be great if you can bear with me and help me with my question.
Below is the test case setup

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) ;

TP_ID and TC_ID columns in T_PARENT table have parent-child relationship. T_CHILD references T_PARENT table (although an explicit FK is not defined) and I have tried to create data skew in T_CHILD table for TC_ID column where the column has more than 254 distinct values (causing a HEIGHT BALANCED histogram being created on it in order to make this similar to tables in original 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') ;

The below simple sql produces close-enough estimates.

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)

However, when I join it, the estimates are far from close enough (161 vs 7 or 161 vs 22026).
In original sql, these 2 tables are being joined to more tables and this join is acting as a driving rowsource.
The original sql is always using NESTED LOOP join when joining this rowsource (join of T_PARENT and T_CHILD) with subsequent table. When this rowsource produces less number of rows (up to 50), the sql performs well but for specific extreme values it produces large number of rows (more than 2000) and performance suffers. Original sql estimates T_CHILD will produce 3 rows vs. actual 2000 for specific cases.

Only forcing dynamic sampling at level 11, provides better estimates for extreme cases.
Is there a better way to influence optimizer to come up with better estimates for such skewed data?

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)

and Chris said...

The problem is you're filtering on one column of t_parent, but joining on another.

So the database doesn't know until runtime which value it's fetching from t_child. Which makes it tricky to get the right cardinality!

In this specific query, creating an index on

t_child ( tc_id )

should help, regardless of the row estimates because the database can scan the index to get the count.

In the general case, increasing the dynamic statistics level should help as you've found.

Another solution is to split the join into separate queries. Generally this is a bad idea. But provided you get at most one row from the parent the improved row estimates for the child may offset this.

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Got it...but

Narendra, July 14, 2020 - 3:40 pm UTC

Hello,

Thank you for your time and input.
While the index would help reduce the expensive full table scan to more efficient index scan, it does not help with the estimated cardinality.
Dynamic Sampling helps but only at level 11, which kind of makes it not viable to use in the main sql.
I had (wrongly) assumed that because T_PARENT has a primary key constraint defined on TC_ID, the optimizers should know that the access to T_PARENT will produce only one record and hence a single value for TP_ID column, which is used to join T_PARENT to T_CHILD tables.
Am I missing something obvious or is the above reasoning too complicated for optimizer?
Would you be kind of enough to suggest how I can split the sql to achieve better cardinality estimates?

July 15, 2020 - 9:34 am UTC

The optimizer does know it's getting one row from t_parent - it estimates one row from this in all the queries.

The problem is it doesn't know what value TP_ID its fetching for a given TC_ID. When you have:

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

We know TC_ID = 7; but the value for TP_ID is... ¯\_(ツ)_/¯. So the optimizer doesn't know which value of TC_ID it's fetching from t_child.

Splitting the join into two queries like this:

select tp.tp_id into parent_value 
from t_parent tp
where tp.tc_id = 7 ;

select count(*) from t_child tc
where tc.tc_id = parent_value;

And the second query is like this example from your test case:

select /*+ GATHER_PLAN_STATISTICS */ count(*) from t_child where tc_id 
= :"SYS_B_0"

cursor_sharing = force

Rajeshwaran, Jeyabal, July 15, 2020 - 6:02 am UTC

....
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"
....

given this sql, the literals are getting replaced into auto generate binds - is that due to the cursor_sharing=force (either at session/system level) ? - will that have any impact to this cardinality estimates?

July 15, 2020 - 9:36 am UTC

Adaptive cursor sharing enables the optimizer to get different row estimates for different bind values; though when you use new values it may still get the wrong estimate on first execution.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Got it...but

cursor_sharing = force

More to Explore

SQL

Data skew and join cardinality estimates

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Got it...but

cursor_sharing = force

More to Explore

SQL