When you execute a query in an Oracle database, Oracle has to decide how to retrieve that data. That’s where the query optimizer steps in. The query optimizer makes decisions like which indexes to use, weather to perform a full table scan etc. based on the tables, columns, calculations and joins in a query. Oracle can do this quite efficiently with the cost-based optimizer, but it is important that there be accurate statistics available for it to use.
A short history
Originally the query optimizer worked on a static set of around 20 rules. These rules would be applied regardless of the size and type of data in a table.
Oracle 7 introduced the cost-based optimizer which can make more intelligent optimization decisions. By analyzing pre-gathered statistics on database objects the cost-based optimizer estimates the “cost” of processing several possible execution plans. The cost-based optimizer then chooses the cheapest execution plan and the database executes the plan.
In Oracle Database 7 through 9i either the cost-based or rule-based optimizer could be used. The rule-based optimizer is no longer included in Oracle 10g.
How can I tell which optimizer mode I am using
If you are running Database 10g or later, you are using the cost-based optimizer.
If you are running Database 7 through 9i you should check the
SQL> show parameter optimizer_mode
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string CHOOSE
RULE means your database is using the old rule-based optimizer. The good news is you do not have to gather statistics on your data. The bad news is your queries probably aren’t running as well as they could.
CHOOSE was introduced as a stop-gap between rule- and cost-based optimizers. If there are no statistics available, the rule-based optimizer will be used; however if statistics are available the query optimizer will default to cost-based mode.
ALL_ROWS will force your database to use the cost-based optimizer regardless of statistics, so you’d better gather them.
How can I gather statistics for my index, table, schema, database, etc.?
The DBMS_STATS package is used to gather statistics for the cost based optimizer. Historically the ANALYZE command would perform similar operations
Here are a few popular examples. Of course you should always consult the documentation for your Oracle distribution before using a new command.
These can be run through SQL*Plus, but you will probably want to automate them for more active databases. There are more options that I have chosen to show here, but these should be a good start.
This will analyze statistics for your entire database. It is likely to take quite a while (hours) and generally should not be necessary, but if you want to analyze the whole database this will do it with one command.
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'JEMMONS')
This will analyze statistics for everything owned by the user ‘JEMMONS’. It is important to put the username in single quotes and all capitol letters. You can exclude the parameter and parentheses to analyze the current user’s statistics.
EXECUTE DBMS_STATS.GATHER_TABLE_STATS(ownname => 'JEMMONS', tabname => 'ENROLLMENT_DATA')
This will gather statistics for a specific table and all its indexes. This may be a good idea on tables which change drastically on a regular basis.
EXECUTE DBMS_STATS.GATHER_INDEX_STATS(ownname => 'JEMMONS', indname => 'ENROLLMENT_CRSE_NUMB')
This will gather statistics on a specific index. If there is a need to drop and rebuild an index you could use this to re-analyze the index after rebuild.
The GATHER AUTO option can and should be added to the commands above after initial analysis. This will cause only objects with missing stats or more than 10% changed since last analysis (via insert, update or delete) to be analyzed. The resulting command should look something like the following:
EXECUTE DBMS_STATS.GATHER_DATABASE_STATS(options => 'GATHER AUTO')
How often should I gather statistics?
This is a question I cannot answer. I have schemas which do not change often that I may analyze once a month, others that I will gather new statistics on once a day. The GATHER AUTO option should be used to automatically gather missing and stale statistics rather than re-analyzing everything; however, sometimes there may be an advantage to re-analyzing an entire schema or database.
Imagine having a quick-and-dirty overview of network health on a worldwide basis. That’s exactly what the Internet Traffic Report offers.
When working at WebCT I frequently used this site to help diagnose why University A in City B could not reach our servers while other people could. Now as Plymouth State University starts reaching a wider audience, both nationally and globally, I can see this becoming a useful tool here as well.
Like any other tool, Internet Traffic Report is not going to solve your network problems. It should be considered just another tool in the toolbox. From their FAQ:
Q: How does this relate to me?
A: Your Internet surfing safari may be smooth today, but perhaps you can’t reach Yahoo or a few web sites in Europe. This web site will tell you if those regions of the Internet are currently slowed down. By checking the Internet Traffic Report, you can determine if your problems are global or local.
Inspired by Animaniacs, I now present you with Good Idea, Bad Idea. Today’s topic: Internet advertising.
The Million Dollar Homepage: This is a very fresh idea with an interesting result. Alex Tew of Wiltshire England came up with the idea of selling one million pixels on www.milliondollarhomepage.com for one dollar each so he could put himself through school. He reached his goal and then some in January 2006. Way to go Alex!
There will be plenty of copy-cat sites, but I will always hold the original as a great example of out-of-the-box thinking.
buyitoff.com: Not only a rip-off, but somewhat degrading. It’s a fresh new type of prostitution, but its still prostitution. The site features a moderately attractive woman who, as people buy advertising, will be undressed, one painfully small panel at a time.
Anyone who buys advertising here is a moron. For starters, there’s better porn out there. More importantly, the ads only show up when you mouse-over the body part purchased, and nobody who is looking for pictures of a naked woman is going to mouse over her best parts just to find out who sponsored them.
The only thing that could somewhat redeem this site is if it had a “Crying Game” surprise ending. This guy is such an ass clown I won’t even link to him. He’ll get a little press, but will be recognized for the hack he is.
In no particular order, here are some of the other folks blogging good stuff about Oracle. Most are more up on current topics than I am since I mostly write how-to stuff so I can find it when I need it.
Rittman.net – Mark Rittman’s Oracle Weblog is a fantastic resource with a focus on data warehousing and business intelligence. One of the best sites by an individual.
PeteFinnigan.com – Pete Finnigan has a lot of security related info. A great resource!
AskTom.Oracle.com – While Ask Tom is not really a blog, there is a wealth of information here on most Oracle topics.
OracleDoug.Blogspot.com – Doug has a bit more casual site and, like me, talks about tech and non-tech topics.
OracleToday.Blogspot.com – Not a frequent poster, but some great nuts-and-bolts SQL and PL/SQL stuff.
ThinkOracle.Blogspot.com – Another blog with a potpouri of Oracle topics.
TKyte.Blogspot.com – The Tom Kyte Blog. Tom works for Oracle (see AskTom.Oracle.com above) and some of his Oracle stuff and peripheral interests and opinions spill over into here.
More are out there. Feel free to comment with your favorites and also check out some of my favorite Oracle web resources