Siebel query performing bad when using recommended stats collection using coe script by oracle
We are using recommended method by oracle to collect stats on the S_ORG_EXT table using coe script as :--
:EXEC DBMS_STATS.GATHER_TABLE_STATS('"SIEBEL"', '"S_ORG_EXT"', estimate_percent => 10, method_opt => 'FOR ALL INDEXED COLUMNS SIZE 254', degree =>
DBMS_STATS.DEFAULT_DEGREE, cascade => TRUE);
But one of the query executing on the table is doing "full index scan" on S_ORG_EXT_M57 index which is a bad plan.
When collecting 100% stats for the table then it is using good plan which don't use the S_ORG_EXT_M57 index
Bad plan