occasionally slow performance of delete which uses function based index
Hi
We are using 11.2.0.4 db on AIX platform. We had nightly purge job which deletes data from tables. We had performance issue with one of delete which use functional based index and also had FK releation with another table using cascade. Sometimes delete complete withing 10min and sometime it runs for more than 45 min. Table size is 3.5gb Table with FK has size 13gb. We done some checks and observed that issue is coming when execution plan has wrong cardinality.
==== Execution plan when delete runs faster with cardinatility - 334K ===
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------
SQL_ID 9vc2pdk09xd03, child number 4