« Going Production With 2005.2 Upgrade | Main | Parts Upgraded »
April 20, 2006
Oracle Database Tuning
Today Dale and I finished up the Oracle DB Tuning course offered by VTC. VTC is something I read about on Joe's blog and it has some useful material for us to use. This particular course/area is pretty dense, and there was no way to obtain the scripts used in the course, so Dale and I started taking screen shots of the most important scripts for later use.
We covered three important areas today. First, was dbms_stats. This is a built-in Oracle package that creates statistics. This package can be used for such things as exporting statistics from a production database to a test database which will give pretty good results on tuning even if all the data isn't in the test instance. This points out the main reason for collecting statistics in the first place - the Cost Based Optimizer (CBO) uses them to create the explain plan (that is, to decide how a particular query will be processed). The more statistics, the better.
A second way of creating statistics is by using the Analyze command. This is a way of controlling the collection of statistics from the command line, and is generally pretty straight forward to use.
The final thing we studied today was Oracle statspack. This is a way to collect statistics about the actual performance of a running database. The idea is to take a snapshot of the database, then take another one some time later (15 minutes, say), then use statspack to compare the two snapshots. This one gets pretty involved. I've run it on Aero a few times, but digesting the results is quite a challenge. In typical Oracle fashion there is more information there than you can sift through in a reasonable amount of time.
Next, we plan to implement these different items, and Oracle Enterprise Manager as well, to see if we can identify poorly tuned SQL queries or other bottlenecks on a database.
Posted by rossm at April 20, 2006 3:02 PM
Comments
Post a comment
Thanks for signing in, . Now you can comment. (sign out)
(If you haven't left a comment here before, you may need to be approved by the site owner before your comment will appear. Until then, it won't appear on the entry. Thanks for waiting.)