Part B : Collecting Data


Tools:






Questions or comments ? Send a note to Kyle Hailey


I will recommend other tools beside the ones above later in this presentation. The above tools were chosen as the minimal set to easily tune a system.

The first The scripts "bstat/estat" has been around since v6 and is easy to use and dependible. There is a new similiar tool, "stats pack" that is much more powerful but available only in v8.1.6, thus the more limited discussion. Oracle also has of course OEM which again is limited to certain versions, but I refrain from discussing it at all because I think it is a badly written behemoth of a beast.

The next script sqldmp.sh is pretty necessary in order to read v$sqlarea with any ease. Oracle doesn't provide any scripts that I know if to facitilate this. What sqldmp.sh does is pretty simple, it just dumps the sqlarea to a flat file and sorts the statments by buffer hits and disk hits and buffer hits per execute. This is the simple information that is needed

Collecting data is complicated by:

Collecting data on an Oracle database is sort of like being caught between Murphy's law and the Heisenburg uncertainty principle. Like in the Heisenbuurg uncertainty principle, the more you find out about how the database is running the more you influence the way the database is running. Like in Murphy's law you'll probably need to find out the information that has a large impact on the database. In most cases running any of the following prograns would not have any affect on the database, but just when the database is heavily loaded this programs can have an important impact on the database and or not even run themselves, thus the need for light weight tools.