SQL Performance (MOSC)

MOSC Banner

Siebel query performing bad when using recommended stats collection using coe script by oracle

edited Jul 21, 2016 5:01AM in SQL Performance (MOSC) 8 commentsAnswered

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

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center