This discussion is archived
1 Reply Latest reply: Jan 22, 2013 11:01 AM by udayjampani RSS

bad CBO statistics

udayjampani Newbie
Currently Being Moderated
Hi All ,

I've below questions regarding the statistics gathering.Could you please try to answer.

If the queries performance were acceptable , then the underlying table's stats should not be gathered ( as it may go either ways i.e improve or decrease the performance of the queries,lock_table_stats is a proof for the same) ?.Do you agree?

How to confirm the queries are performing slow because of the bad CBO statistics.?

Could you please elaborate the "Test with the RULE hint" from [Burleson's post|http://www.dba-oracle.com/t_sql_tuning_tricks.htm] ?

Thanks in advance,
Uday
  • 1. Re: bad CBO statistics
    damorgan Oracle ACE Director
    Currently Being Moderated
    The last thing I would recommend you read about any Oracle topic is something from dba-oracle.com. To better understand this point google the following:
    "Kyte" and "Burleson"
    My generic advice, because in Oracle there are very few absolutes, is that before you make decisions with respect to stats and stats collection you determine how Oracle is using the stats. Not collecting stats works well right up until the point-in-time when the table changes enough that the plan it is generating because a problem rather than a solution. Collecting stats always works provided you collect them properly and don't hit a bug.

    The only persons whose advice I would recommend you take on this question, Exadata or not, is that of Jonathan Lewis, Christian Antognini, Tanel Poder, and a few other members of the Oak Table.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points