Wrong Rows estimation with dynamic partition pruning
Hi all,
The database version 11.1. Rac 4 nodes. I encountered one sql that runs slowly because the wrong ROWS estimation. Here is the its exectuion plan.
Please note the plan is just a part of the real exectuion plan. And I have collected statistics for all involved tables, so I think the statistics must be up to date.
Please see below. CBO estimates that only 640 rows for step 5 and totall 115 for this SQL. But the real result has nearly 14K rows.
I don't know whether CBO made this mistake because of dynamic partition pruning?
The database version 11.1. Rac 4 nodes. I encountered one sql that runs slowly because the wrong ROWS estimation. Here is the its exectuion plan.
Please note the plan is just a part of the real exectuion plan. And I have collected statistics for all involved tables, so I think the statistics must be up to date.
Please see below. CBO estimates that only 640 rows for step 5 and totall 115 for this SQL. But the real result has nearly 14K rows.
I don't know whether CBO made this mistake because of dynamic partition pruning?
PLAN_TABLE_OUTPUT
Plan hash value: 2984909170
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | 0