Evolution of Oracle Native Diagnostic Tools

 
All the tools introduced here share the same foundation: a set of dynamic/dictionary views, such as v$sessionstat, v$sysstat, v$system_event, etc. 
 
However, all these views contain only the raw data: the statistics — either accumulative values of state parameters since the last time the instance started, or the exact values of state paramters at a moment, one cannot tell the change rate of a certain state parameter value over a time period to reveal if any certain change had occured which might have resulted in performance down-grade. In addition all the "raw" data are stored in memory and will all vanish once the instance is sutdown. In order words, checking the raw data directly is useless for the purpose of dignosing and not reliable either. We need some "delta" tool to reveal the change rate over a certain time period (also called a metric) by taking two "snapshots" — one at the beginning and the other at the end of a predefined period, and then to find the change rate over this time period. Oracle provides the follwoig:
 
Utilbstat.sql/utilestat.sql  — a pair of SQL scripts
Statspack — a package of tools, free of charge
AWR — optional, but the users will have to pay to use it.
 
Thes tools provided by Oracle evolute over time and versions.

Version

Tool Name

Report

Stored

Tablespace

Customizable

Tuning tool

7-8

Utlbstat.sql/

utlestat.sql

Once

None

None

None

Manual tuning, 3rd party

8i

Statspack

Multiple

Yes

User TS

Somewhat

Manual tuning, 3rd party

9i

Statepack

Multiple

Yes

User TS

Somewhat

Manual tuning, 3rd party

10g

AWR

As many

Yes

Sysaux

Highly

ADDM – Tuning advisers

11g

AWR

As many

Yes

Sysaux

Highly

ADDM – more adviser.

 
Statspack was introduced since 8i, as an extension of utlbstat.sql/utilestat.sql (begins and ends a stat snapshot) and remains abvailable through the latest 11g.
 
AWR (Automatic Workload Repository), introduced since 10g, is actually a graphical extention and enhancement of Statspack, with more management features, such as scheduler, historical data retention, baseline creation, comparison of periods bettween different snapshot pairs, etc, yet the core performance data and the interpretation of the data remain unchanged.
Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s