IT Employment

Ask something
Discussion Locked

What is your Oracle SQL optimizer philosophy?

0 helpful
3 replies

by jack_swayze 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

3 replies

by jack_swayze

Thanks so much for this article! I have had a very tough time trying to convince DBAs I have worked with, over the years, that they need to set up a periodic process to ANALYZE the tables in the database. The consistent response I would get is that if you ever run an ANALYZE against a table, that immediately all your SQL against that table will run significantly slower. One person even told me that was one of the questions on the Oracle certified DBA exam!

This response has always seemed unfounded to me as both my common sense and my experience is that running an ANALYZE on a table gives the optimizer more information to work with and thereby allows it to work better, not worse.

How did this 'Urban Legend' ever get started?

by ora-dba

Many DBAs got burned by the early versions of CBO - ORACLE7 for example. If you have no statistics, the optimizer behaves like RULES. Analyze your tables, thus supplying statistics to the optimizer, and presto you are in cost-based. In V7 the CBO was terrible, things did indeed run slower, and DBAs swore off this optimzer method.

Now, once burned twice shy, DBAs still need to approach CBO with some caution. In my experience so far with 8i, the use of the /*+ RULE */ hint can actually create a better performing plan than CBO in many cases. Each piece of SQL must be optimized on its own terms.

by peace monger

if your data is fairly dynamic, then I'd suggest analyze tables and indexes frequently -- once a week or daily. Stale statistics can cause poor performance... This definitely isn't a set it and forget it situation.

Lately, I've found that histograms can help tremendously, especially if the value of your column isn't normally distributed such as in status fields. Once you're running Oracle 9i, data histograms are supposed to be generated automatically... but I'll bet some maintenance is required for that as well

This discussion is closed to new replies.