Skip to Main Content
  • Questions
  • Bad cardinality in join with column with skewed data

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Paul.

Asked: November 23, 2016 - 6:09 pm UTC

Last updated: January 12, 2022 - 2:27 pm UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Hi guys.

I have a problem with the estimation of the cardinality of a skewed column

The distribution of the data is as follows:

select m.m_pricelist_id, count(*)
from m_pricelist_version m
group by m.m_pricelist_id   2    3  ;


M_PRICELIST_ID COUNT(*)
-------------- ----------
1000000 1
1000003 2624686
1000001 1
1000002 33375
1000005 1
102 2
1000004 1
101 2
103 1


I have an index on that column, in addition to histograms.

Select *
From USER_TAB_HISTOGRAMS uh
where uh.TABLE_NAME= 'M_PRICELIST_VERSION'
and uh.COLUMN_NAME='M_PRICELIST_ID';


TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE
------------------------------ ------------------------------ --------------- -------------- ------------------------------
M_PRICELIST_VERSION M_PRICELIST_ID 1 101
M_PRICELIST_VERSION M_PRICELIST_ID 65 1000002
M_PRICELIST_VERSION M_PRICELIST_ID 5560 1000003
M_PRICELIST_VERSION M_PRICELIST_ID 5561 1000005

The cardinality is correctly estimated if the filter is entered with literals (2626K)

SQL>  select /*+ gather_plan_statistics */ count(*)
from m_pricelist_version m where m.m_pricelist_id = 1000003  2  ;


COUNT(*)
----------
2624745

Transcurrido: 00:00:00.28
SQL> select * from table(dbms_xplan.display_cursor( format=> 'allstats last'));


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID b8fbc0xn66v9n, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from m_pricelist_version
m where m.m_pricelist_id = 1000003

Plan hash value: 3128031140

-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.28 | 9597 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.28 | 9597 |
|* 2 | INDEX FAST FULL SCAN| M_PRICELIST_VERSION_VALIDFROM | 1 | 2626K| 2624K|00:00:01.30 | 9597 |
-----------------------------------------------------------------------------------------------------------------

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

2 - filter("M"."M_PRICELIST_ID"=1000003)


20 filas seleccionadas.


However, if the filter is pre-calculated from a query:

SQL>select p.m_pricelist_id
from m_pricelist p where p.name='Precios';

M_PRICELIST_ID
--------------
1000003

SQL> select /*+ gather_plan_statistics */ count(*)
from m_pricelist_version m where m.m_pricelist_id =
(select p.m_pricelist_id
from m_pricelist p where p.name='Precios')
2 3 4 5 ;

COUNT(*)
----------
2624745

Transcurrido: 00:00:00.35
SQL> select * from table(dbms_xplan.display_cursor( format=> 'allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 6z2hy1fb3h0py, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from m_pricelist_version
m where m.m_pricelist_id = (select p.m_pricelist_id from m_pricelist p
where p.name='Precios')

Plan hash value: 2632919022

-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.35 | 9393 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.35 | 9393 |
|* 2 | INDEX RANGE SCAN | M_PRICELIST_VERSION_VALIDFROM | 1 | 295K| 2624K|00:00:01.47 | 9393 |
| 3 | TABLE ACCESS BY INDEX ROWID| M_PRICELIST | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 4 | INDEX SKIP SCAN | M_PRICELIST_NAME | 1 | 1 | 1 |00:00:00.01 | 1 |
-------------------------------------------------------------------------------------------------------------------------

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

2 - access("M"."M_PRICELIST_ID"=)
4 - access("P"."NAME"=U'Precios')
filter("P"."NAME"=U'Precios')

Cardinality differs significantly (295K).

Please any help will be apreciated. Thanks.

and Chris said...

The basic issue is that when you join the optimizer doesn't know which value of m_pricelist_id it will be matching on.

For example, I can reproduce findings similar to yours (on 11.2.0.4). Building a table based on dba_objects there are:

- 32,060 objects owned by PUBLIC
- 171 objects owned by CHRIS

If I use these values in the where clause, the estimates are accurate. But if I turn this into a join/subquery then both get 3,029 rows estimated:

create table t1 ( owner not null, object_name not null ) as
  select owner, object_name from dba_objects;

create table t2 ( owner not null, io not null )as
  select distinct owner, initcap(owner) io from dba_objects;
 
create index i on t1(owner);

exec dbms_stats.gather_table_stats(user, 't1', method_opt => 'for all columns size skewonly');
exec dbms_stats.gather_table_stats(user, 't2');

select table_name, column_name, histogram from user_tab_col_statistics
where  table_name in ('T1', 'T2');

TABLE_NAME  COLUMN_NAME  HISTOGRAM        
T1          OWNER        FREQUENCY        
T1          OBJECT_NAME  HEIGHT BALANCED  
T2          OWNER        NONE             
T2          IO           NONE 

select /*+ gather_plan_statistics */count(*) from t1
where  owner = 'PUBLIC';

COUNT(*)  
32,060  

select * from table(dbms_xplan.display_cursor(null, null, 'ROWSTATS LAST'));

PLAN_TABLE_OUTPUT                                                     
SQL_ID  6kfdkhhvtq4t7, child number 0                                 
-------------------------------------                                 
select /*+ gather_plan_statistics */count(*) from t1 where  owner =   
'PUBLIC'                                                              
                                                                      
Plan hash value: 3309376399                                           
                                                                      
-----------------------------------------------------------------     
| Id  | Operation             | Name | Starts | E-Rows | A-Rows |     
-----------------------------------------------------------------     
|   0 | SELECT STATEMENT      |      |      1 |        |      1 |     
|   1 |  SORT AGGREGATE       |      |      1 |      1 |      1 |     
|*  2 |   INDEX FAST FULL SCAN| I    |      1 |  32056 |  32060 |     
-----------------------------------------------------------------     
                                                                      
Predicate Information (identified by operation id):                   
---------------------------------------------------                   
                                                                      
   2 - filter("OWNER"='PUBLIC')

select /*+ gather_plan_statistics */count(*) from t1
where  owner = 'CHRIS';

COUNT(*)  
171  

select * from table(dbms_xplan.display_cursor(null, null, 'ROWSTATS LAST'));

PLAN_TABLE_OUTPUT                                                     
SQL_ID  901gsm616gw15, child number 0                                 
-------------------------------------                                 
select /*+ gather_plan_statistics */count(*) from t1 where  owner =   
'CHRIS'                                                               
                                                                      
Plan hash value: 163676535                                            
                                                                      
-------------------------------------------------------------         
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |         
-------------------------------------------------------------         
|   0 | SELECT STATEMENT  |      |      1 |        |      1 |         
|   1 |  SORT AGGREGATE   |      |      1 |      1 |      1 |         
|*  2 |   INDEX RANGE SCAN| I    |      1 |    163 |    171 |         
-------------------------------------------------------------         
                                                                      
Predicate Information (identified by operation id):                   
---------------------------------------------------                   
                                                                      
   2 - access("OWNER"='CHRIS') 

select /*+ gather_plan_statistics */count(*) from t1
where  owner in (select t2.owner from t2 where io = 'Public');

COUNT(*)  
32,060  

select * from table(dbms_xplan.display_cursor(null, null, 'ROWSTATS LAST'));

PLAN_TABLE_OUTPUT                                                      
SQL_ID  9f74t08nb6qh8, child number 0                                  
-------------------------------------                                  
select /*+ gather_plan_statistics */count(*) from t1 where  owner in   
(select t2.owner from t2 where io = 'Public')                          
                                                                       
Plan hash value: 2896049957                                            
                                                                       
----------------------------------------------------------------       
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |       
----------------------------------------------------------------       
|   0 | SELECT STATEMENT     |      |      1 |        |      1 |       
|   1 |  SORT AGGREGATE      |      |      1 |      1 |      1 |       
|   2 |   NESTED LOOPS       |      |      1 |   3029 |  32060 |       
|   3 |    SORT UNIQUE       |      |      1 |      1 |      1 |       
|*  4 |     TABLE ACCESS FULL| T2   |      1 |      1 |      1 |       
|*  5 |    INDEX RANGE SCAN  | I    |      1 |   3029 |  32060 |       
----------------------------------------------------------------       
                                                                       
Predicate Information (identified by operation id):                    
---------------------------------------------------                    
                                                                       
   4 - filter("IO"='Public')                                           
   5 - access("OWNER"="T2"."OWNER")  

select /*+ gather_plan_statistics */count(*) from t1
where  owner in (select t2.owner from t2 where io = 'Chris');

COUNT(*)  
171 

select * from table(dbms_xplan.display_cursor(null, null, 'ROWSTATS LAST'));

PLAN_TABLE_OUTPUT                                                      
SQL_ID  06220jxs1bf4c, child number 0                                  
-------------------------------------                                  
select /*+ gather_plan_statistics */count(*) from t1 where  owner in   
(select t2.owner from t2 where io = 'Chris')                           
                                                                       
Plan hash value: 2896049957                                            
                                                                       
----------------------------------------------------------------       
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |       
----------------------------------------------------------------       
|   0 | SELECT STATEMENT     |      |      1 |        |      1 |       
|   1 |  SORT AGGREGATE      |      |      1 |      1 |      1 |       
|   2 |   NESTED LOOPS       |      |      1 |   3029 |    171 |       
|   3 |    SORT UNIQUE       |      |      1 |      1 |      1 |       
|*  4 |     TABLE ACCESS FULL| T2   |      1 |      1 |      1 |       
|*  5 |    INDEX RANGE SCAN  | I    |      1 |   3029 |    171 |       
----------------------------------------------------------------       
                                                                       
Predicate Information (identified by operation id):                    
---------------------------------------------------                    
                                                                       
   4 - filter("IO"='Chris')                                            
   5 - access("OWNER"="T2"."OWNER")  

With the subquery the optimizer doesn't know at parse time whether it's comparing owner to PUBLIC or CHRIS.

But before you obsess too much about making the estimates match the cardinality, it's worth asking:

What problem is this actually causing you?

Having estimated rows very different from actual rows is an indicator that the plan might be "wrong". But it doesn't mean it is!

For example, using the example above Oracle will choose the same plan whether there's 1 row in t1 or billions:

select /*+ gather_plan_statistics cardinality (t1, 1) */count(*) from t1
where  owner in (select t2.owner from t2 where io = 'Chris');

select * from table(dbms_xplan.display_cursor(null, null, 'ROWSTATS LAST'));

PLAN_TABLE_OUTPUT                                                       
SQL_ID  9kd044nw9vs49, child number 0                                   
-------------------------------------                                   
select /*+ gather_plan_statistics cardinality (t1, 1) */count(*) from   
t1 where  owner in (select t2.owner from t2 where io = 'Chris')         
                                                                        
Plan hash value: 2896049957                                             
                                                                        
----------------------------------------------------------------        
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |        
----------------------------------------------------------------        
|   0 | SELECT STATEMENT     |      |      1 |        |      1 |        
|   1 |  SORT AGGREGATE      |      |      1 |      1 |      1 |        
|   2 |   NESTED LOOPS       |      |      1 |      1 |    171 |        
|   3 |    SORT UNIQUE       |      |      1 |      1 |      1 |        
|*  4 |     TABLE ACCESS FULL| T2   |      1 |      1 |      1 |        
|*  5 |    INDEX RANGE SCAN  | I    |      1 |      1 |    171 |        
----------------------------------------------------------------        
                                                                        
Predicate Information (identified by operation id):                     
---------------------------------------------------                     
                                                                        
   4 - filter("IO"='Chris')                                             
   5 - access("OWNER"="T2"."OWNER")  

select /*+ gather_plan_statistics cardinality (t1, 1000000000) */count(*) from t1
where  owner in (select t2.owner from t2 where io = 'Chris');

select * from table(dbms_xplan.display_cursor(null, null, 'ROWSTATS LAST'));

PLAN_TABLE_OUTPUT                                                        
SQL_ID  8kghbrj5gtsm3, child number 0                                    
-------------------------------------                                    
select /*+ gather_plan_statistics cardinality (t1, 1000000000)           
*/count(*) from t1 where  owner in (select t2.owner from t2 where io =   
'Chris')                                                                 
                                                                         
Plan hash value: 2896049957                                              
                                                                         
----------------------------------------------------------------         
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |         
----------------------------------------------------------------         
|   0 | SELECT STATEMENT     |      |      1 |        |      1 |         
|   1 |  SORT AGGREGATE      |      |      1 |      1 |      1 |         
|   2 |   NESTED LOOPS       |      |      1 |     40M|    171 |         
|   3 |    SORT UNIQUE       |      |      1 |      1 |      1 |         
|*  4 |     TABLE ACCESS FULL| T2   |      1 |      1 |      1 |         
|*  5 |    INDEX RANGE SCAN  | I    |      1 |     40M|    171 |         
----------------------------------------------------------------         
                                                                         
Predicate Information (identified by operation id):                      
---------------------------------------------------                      
                                                                         
   4 - filter("IO"='Chris')                                              
   5 - access("OWNER"="T2"."OWNER")  

Note: the cardinality hint is undocumented, use with caution!

So the real question you should be asking is:

Is the optimizer choosing a full tablescan when it should use an index (or vice versa)?

If it is causing you performance problems, you could look into using SQL profiles and/or baselines to tweak the plan:

http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-sql-plan-management-11gr2-133099.pdf

Rating

  (4 ratings)

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

Comments

Cardinality hint not appropriate for test case

Andrew Sayer, November 26, 2016 - 4:31 pm UTC

Chris,
Where you've done your demo to prove that when the join returns a billion rows the same plan is used, the cardinality hint is not being used to calculate the cost of the join. The original cardinality estimate would be used to calculate the cost, and then the cardinality hint will be used to replace the cardinality of that row source. The hint will only affect the plan if there are subsequent things to do with that row source.

You can show the affect on cost by including the cost in your dbms_xplan output, I use the format 'allstats last typical' so that the default output (mainly cost and e-time) is still included.

You could use the (also undocumented) opt_estimate hint to finely specify where the cardinalities should change, the costs (and therefore optimizer decisions) will change to suit. Or you could directly modify the stats of the table to see the affect more fairly.

This question was also raised on the OTN https://community.oracle.com/thread/3994241 - Jonathan Lewis and I have shared other solutions there.
Chris Saxon
November 28, 2016 - 10:45 am UTC

Good point Andrew, thanks for sharing.

Thanks and sorry...

Paul Marin%CC%83o, November 28, 2016 - 7:40 pm UTC

Thanks Chris and again Andrew.

Sorry for post the same question. Personally, I thought my question had not been considered, it had been a few days since i posted it ... Sorry, it was my first question in both asktom and OTN.

I appreciate your help, thanks again

Underestimates do matter

Andrew Markiewicz, December 17, 2021 - 8:02 pm UTC

"What problem is this actually causing you?

Having estimated rows very different from actual rows is an indicator that the plan might be "wrong". But it doesn't mean it is!
"

My experience is this invariably causes problems when there is a severe underestimation. We have many instances where a query like the one from the OP is just one step of a much larger query. The CBO underestimates the cardinality on a skewed column in a join which leads to NL joins, push predicates, and thousands of executed subqueries where it should have been a HJ and one subquery.

You typically will see where it matters when an underestimate is trying to process several orders of magnitude higher actual rows in an execution plan meant for a low number of rows.
The opposite, an overestimation, is rarely an issue.
I can easily move a car load of stuff or a truck load of stuff using a truck, but I cannot move a truck load of stuff in a car unless I do many trips. It's just inefficient.

SQL Profiles (deprecated) and SQL baselines seem to be tools created as a workaround to fix deficiencies in the optimizer. I would prefer seeing the optimizer scaling up the cardinality estimates of skewed joined columns to accommodate values with larger sets. It would be a better general solution. Currently it favors an optimistic approach that the user will be querying the smaller data set, but the larger data set user is out of luck. Then we just get complaints that the system is slow. These differences can be a query that should be 0.5sec but is instead several minutes. Not trivial.
Connor McDonald
January 11, 2022 - 3:44 am UTC

The opposite, an overestimation, is rarely an issue.


Not sure I agree with that one. "Severe" mis-estimate in any direction generally means bad news for the optimizer. I would agree on the point that the smaller the numbers, the greater the order of magnitude the differences are, eg, estimating 5 instead of 10 rows is only 5 rows wrong, but is a 200% swing, whereas estimating 1000 instead of 1100 rows is 100(!!!) rows but only a 10% swing.


SQL Profiles (deprecated) and SQL baselines seem to be tools created as a workaround to fix deficiencies in the optimizer.

I disagree with this statement because it suggests a theoretical optimizer could exist that gets every plan correct. The only way to do that would be to have an infinite number of statistics which obviously is impossible.

Dynamic Sampling level = 11

Rajeshwaran, Jeyabal, January 12, 2022 - 6:05 am UTC

Team,

How about Dynamic sampling level = 11 ? it helps the optimizer for join cardinality mismatches, but available only in 12c and above.

https://docs.oracle.com/en/database/oracle/oracle-database/21/tgsql/query-optimizer-concepts.html#GUID-2D218B35-A443-41CB-855C-CF41ADB802EE

repeating the above with dynamic sampling level =11, produces like this

demo@XEPDB1> select owner,count(*)
  2  from all_objects
  3  group by owner
  4  order by 2 desc;

OWNER                            COUNT(*)
------------------------------ ----------
SYS                                 49802
PUBLIC                              12020
MDSYS                                4342
ORDSYS                                556
XDB                                   460
SYSTEM                                459
DVSYS                                 406
CTXSYS                                398
WMSYS                                 380
ORDDATA                               257
LBACSYS                               243
GSMADMIN_INTERNAL                     213
DEMO                                   70
DBSNMP                                 59
AUDSYS                                 52
OJVMSYS                                31
OLAPSYS                                25
DVF                                    22
REMOTE_SCHEDULER_AGENT                 13
DBSFWUSER                               8
OUTLN                                   8
SI_INFORMTN_SCHEMA                      8
ORDPLUGINS                              6
ORACLE_OCM                              6
APPQOSSYS                               6
SCOTT                                   4

26 rows selected.

demo@XEPDB1> create table t1 ( owner not null, object_name not null ) as
  2    select owner, object_name from dba_objects;

Table created.

demo@XEPDB1> create table t2 ( owner not null, io not null )as
  2    select distinct owner, initcap(owner) io from dba_objects;

Table created.

demo@XEPDB1> create index i on t1(owner);

Index created.

demo@XEPDB1> exec dbms_stats.gather_table_stats(user, 't1', method_opt => 'for all columns size skewonly');

PL/SQL procedure successfully completed.

demo@XEPDB1> set serveroutput off
demo@XEPDB1> select /*+ gather_plan_statistics */ count(*) from t1 where owner ='PUBLIC';

  COUNT(*)
----------
     12021

demo@XEPDB1> select * from table( dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9hfyrrmfahn47, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from t1 where owner
='PUBLIC'

Plan hash value: 2079104444

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |      1 |00:00:00.01 |      32 |
|   1 |  SORT AGGREGATE   |      |      1 |      1 |      1 |00:00:00.01 |      32 |
|*  2 |   INDEX RANGE SCAN| I    |      1 |  12021 |  12021 |00:00:00.01 |      32 |
------------------------------------------------------------------------------------

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

   2 - access("OWNER"='PUBLIC')


20 rows selected.

demo@XEPDB1> select /*+ gather_plan_statistics */ count(*) from t1 where owner ='DEMO';

  COUNT(*)
----------
        77

demo@XEPDB1> select * from table( dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  64tcauf11xhhg, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from t1 where owner
='DEMO'

Plan hash value: 2079104444

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |      1 |00:00:00.01 |       2 |
|   1 |  SORT AGGREGATE   |      |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  2 |   INDEX RANGE SCAN| I    |      1 |     77 |     77 |00:00:00.01 |       2 |
------------------------------------------------------------------------------------

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

   2 - access("OWNER"='DEMO')


20 rows selected.

demo@XEPDB1> select /*+ gather_plan_statistics */ count(*) from t1
  2  where owner in ( select t2.owner
  3     from t2
  4     where t2.io ='Public');

  COUNT(*)
----------
     12021

demo@XEPDB1> select * from table( dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  d1vw3cmkc15yg, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from t1 where owner in (
select t2.owner  from t2  where t2.io ='Public')

Plan hash value: 1258047248

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |      1 |00:00:00.02 |      34 |      5 |       |       |          |
|   1 |  SORT AGGREGATE      |      |      1 |      1 |      1 |00:00:00.02 |      34 |      5 |       |       |          |
|   2 |   NESTED LOOPS       |      |      1 |   2897 |  12021 |00:00:00.02 |      34 |      5 |       |       |          |
|   3 |    SORT UNIQUE       |      |      1 |      1 |      1 |00:00:00.01 |       2 |      5 |  2048 |  2048 | 2048  (0)|
|*  4 |     TABLE ACCESS FULL| T2   |      1 |      1 |      1 |00:00:00.01 |       2 |      5 |       |       |          |
|*  5 |    INDEX RANGE SCAN  | I    |      1 |   2897 |  12021 |00:00:00.01 |      32 |      0 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------

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

   4 - filter("T2"."IO"='Public')
   5 - access("OWNER"="T2"."OWNER")

Note
-----
   - this is an adaptive plan


28 rows selected.

demo@XEPDB1> select /*+ gather_plan_statistics */ count(*) from t1
  2  where owner in ( select t2.owner
  3     from t2
  4     where t2.io ='Demo');

  COUNT(*)
----------
        77

demo@XEPDB1> select * from table( dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  7t11163pdggja, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from t1 where owner in (
select t2.owner  from t2  where t2.io ='Demo')

Plan hash value: 1258047248

------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |      1 |00:00:00.01 |       4 |       |       |          |
|   1 |  SORT AGGREGATE      |      |      1 |      1 |      1 |00:00:00.01 |       4 |       |       |          |
|   2 |   NESTED LOOPS       |      |      1 |   2897 |     77 |00:00:00.01 |       4 |       |       |          |
|   3 |    SORT UNIQUE       |      |      1 |      1 |      1 |00:00:00.01 |       2 |  2048 |  2048 | 2048  (0)|
|*  4 |     TABLE ACCESS FULL| T2   |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  5 |    INDEX RANGE SCAN  | I    |      1 |   2897 |     77 |00:00:00.01 |       2 |       |       |          |
------------------------------------------------------------------------------------------------------------------

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

   4 - filter("T2"."IO"='Demo')
   5 - access("OWNER"="T2"."OWNER")

Note
-----
   - this is an adaptive plan


28 rows selected.

demo@XEPDB1> select /*+ gather_plan_statistics dynamic_sampling(11) */ count(*) from t1
  2  where owner in ( select t2.owner
  3     from t2
  4     where t2.io ='Public');

  COUNT(*)
----------
     12021

demo@XEPDB1> select * from table( dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  2tap1dsjwqu5r, child number 0
-------------------------------------
select /*+ gather_plan_statistics dynamic_sampling(11) */ count(*) from
t1 where owner in ( select t2.owner  from t2  where t2.io ='Public')

Plan hash value: 1258047248

------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |      1 |00:00:00.01 |      34 |       |       |          |
|   1 |  SORT AGGREGATE      |      |      1 |      1 |      1 |00:00:00.01 |      34 |       |       |          |
|   2 |   NESTED LOOPS       |      |      1 |  12021 |  12021 |00:00:00.01 |      34 |       |       |          |
|   3 |    SORT UNIQUE       |      |      1 |      1 |      1 |00:00:00.01 |       2 |  2048 |  2048 | 2048  (0)|
|*  4 |     TABLE ACCESS FULL| T2   |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  5 |    INDEX RANGE SCAN  | I    |      1 |  12021 |  12021 |00:00:00.01 |      32 |       |       |          |
------------------------------------------------------------------------------------------------------------------

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

   4 - filter("T2"."IO"='Public')
   5 - access("OWNER"="T2"."OWNER")

Note
-----
   - dynamic statistics used: dynamic sampling (level=AUTO)
   - this is an adaptive plan


29 rows selected.

demo@XEPDB1> select /*+ gather_plan_statistics dynamic_sampling(11) */ count(*) from t1
  2  where owner in ( select t2.owner
  3     from t2
  4     where t2.io ='Demo');

  COUNT(*)
----------
        77

demo@XEPDB1> select * from table( dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  avu6pftcg1w4q, child number 0
-------------------------------------
select /*+ gather_plan_statistics dynamic_sampling(11) */ count(*) from
t1 where owner in ( select t2.owner  from t2  where t2.io ='Demo')

Plan hash value: 1258047248

------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |      1 |00:00:00.01 |       4 |       |       |          |
|   1 |  SORT AGGREGATE      |      |      1 |      1 |      1 |00:00:00.01 |       4 |       |       |          |
|   2 |   NESTED LOOPS       |      |      1 |     77 |     77 |00:00:00.01 |       4 |       |       |          |
|   3 |    SORT UNIQUE       |      |      1 |      1 |      1 |00:00:00.01 |       2 |  2048 |  2048 | 2048  (0)|
|*  4 |     TABLE ACCESS FULL| T2   |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  5 |    INDEX RANGE SCAN  | I    |      1 |     77 |     77 |00:00:00.01 |       2 |       |       |          |
------------------------------------------------------------------------------------------------------------------

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

   4 - filter("T2"."IO"='Demo')
   5 - access("OWNER"="T2"."OWNER")

Note
-----
   - dynamic statistics used: dynamic sampling (level=AUTO)
   - this is an adaptive plan


29 rows selected.

demo@XEPDB1>

Chris Saxon
January 12, 2022 - 2:27 pm UTC

That can be worth investigating, yes.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.