Dear list

• Oracle Update Statistics Partitioned Table
• Oracle Update Statistics Performance Improvement
• Oracle Update Statistics
• Oracle Update Statistics Command
• Oracle Gather Table Statistics

SQL plan directives on partitioned tables with skewed data might cause worse cardinality estimates. Finally, gather statistics: EXEC DBMS_STATS.gather_table_stats('SYS', 'T'). Update: The Oracle Support filed a bug for the problem – Bug 22229118: WRONG CARDINALITY ESTIMATE WITH SQL PLAN DIRECTIVE.

I have a performance problem that I have narrowed to a statistics problem
where the CBO is not doing good estimations on a partitioned table
The culprit select looks like
select
a
,b

Oracle Update Statistics Partitioned Table

,c
,per_ind
from XXX_PER_YYY

Oracle Update Statistics Performance Improvement

where per_ind = 0;
XXX_PER_YYY is a table range partitioned by a date. There are 1493
partitions.
AND there is an index XXX_PER_IND on (per_ind) local (note that it is local
non prefixed)
The execution plan with Estimations and Actuals looks like
----------------------------------------------------------------------------------
A-Rows |
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):

Oracle Update Statistics

---------------------------------------------------
3 - access('PER_IND'=0)
And the execution plan showing the number of partition looks like
-------------------------------------------------------------------------------------------
Pstart| Pstop |
--------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access('PER_IND'=0)
There is only one distinct value of per_ind (per_ind =0)
select per_ind, count(1) from XXX_PER_YYY group by per_ind;
*per_ind cout(1)*
0 6,118,184
And I have the following partitions num_rows distribution
758 partitions with num_rows = 0;
60 partitions with num_rows <= 5000
295 partitions with num_rows between 5000 and 10000
315 partitions with num_rows > 10,000 and num_rows <50,000
65 partitions with num_rows > 100,000;

Oracle Update Statistics Command

Statistics are calculated at a global level

Oracle Gather Table Statistics

How to make the CBO having correct estimations and hence an optimal
execution plan?
Thanks in advance