 |
|
|
The previous chapter introduced the concept and role of the Automatic Workload Repository (AWR) feature in Oracle10g, and our next step is to learn more about key views which form the AWR repository.
As a quick review, Oracle stores the AWR internal data structures in schema sys which is physically located in the tablespace sysaux. The DBA can, however, access any AWR historical data directly by using the AWR internal tables prefixed wrx_.
Oracle provides DBAs with a set of data dictionary views prefixed with dba_hist to be used to access AWR information via queries. The Workload Interface Statistical Engine (Ion) tool uses the dba_hist dictionary views intensively in order to generate chart reports for AWR data, and Ion is a handy way to realize your trends.
This chapter will present an overview of various categories of dba_hist views and types of statistics collected. The following points will be covered: § Access paths to AWR data.
§
dba_hist data dictionary views. Access Paths to AWR data Typically, Oracle DBAs can access AWR historical data using the Oracle Enterprise Manager (EM), AWR standard report, or by using Ion tool reports.
Oracle OEM 10g allows access to AWR data through a web browser interface. The Oracle documentation can be referenced for details on using OEM to generate reports for AWR historical data.
An AWR standard report can be generated using the awrrpt.sql script located in $ORACLE_HOME/rdbms catalog. This script generates its report in either HTML or TEXT formats, depending on user specified parameters. Users must have a DBA role granted in order to successfully run this script. Furthermore, a script called awrrpt1.sql, which is located in $ORACLE_HOME/rdbms catalog, asks for a database identifier dbid and instance number and generates the AWR report for the specified database.
The Ion tool was designed to provide a convenient graphical interface for Oracle STATSPACK and AWR data.
The Ion tool’s role has been extended to
support work with the Oracle10g AWR by providing an advanced set of useful
features. The Ion tool provides users with a convenient GUI framework that
allows quick and easy navigation through AWR snapshots. It also allows the
retrieval of historical data in text or chart formats for any snapshot ranges
specified. The main advantages of Ion tool when compared to OEM are: § The ability to quickly build a historical or trend chart for any AWR statistic. § The ability to automate chart report generation on a schedule basis. § The ability to easily write custom AWR reports.
Oracle gives users the option of accessing AWR metadata or historical data manually using any ad-hoc query tool like SQL*Plus by performing queries against the following database objects: § The dba_hist dictionary views provide access to historical statistical data stored in the AWR. The AWR stores cumulative values available through dynamic performance views like v$sysstat. Instance restart resets these cumulative values in v$ memory views. However, the AWR keeps cumulative statistics as well as delta values that show the change of statistics over time. § The v$ metric views provide metric statistics which show users the rate of change of some particular statistic. The metrics in Oracle can be measured against such units as time, transaction number, database or user calls, etc. One specific example would be the parse number per second. The metric views are organized by Oracle in groups as system, session, file, event, tablespace, etc. These metric v$ views are presented in a later chapter in this book. § The v$active_session_history view provides access to a sampled data. The sampled data shows the activity status of all active database sessions at the given moment. The content of v$active_session_history view is also preserved by the AWR. Next, let’s explore the intervals of the dba_hist tables so we can write our own custom reports.
|
|
|
