Oracle Database 10g New Features
Oracle 10g Reference for Advanced Tuning & Administration By Mike Ault, Daniel Liu, and Madhu Tumma
Copyright ? 2003 by Rampant TechPress.
Table of Contents
Using the Online Code Depot
Conventions Used in this Book
Acknowledgments
Preface
Chapter 1 Database Management
New Features
SYSAUX Tablespace
What is Automated Storage Management?
Automated Storage Management Configuration
ASM Concepts
ASM Files
Failure Groups
ASM Instances
Database Instance ASM Background Processes
Installation of ASM
ASM Benefits
Viewing Information About Automated Storage Management
Automated Memory Management
Oracle Database 10g Automation Features
Automatic Workload Repository
Automatic Maintenance Tasks
Oracle Database 10g Automatic Memory Management (AMM)
How AMM Works
Adjusting the Oracle Database 10g data buffers
Viewing Information About the SGA
Adjusting the Oracle Database 10g Shared Pool
Conclusions about AMM
Automated RAC Services Configuration
Creating a Real Application Clusters Database with the DBCA
Simplified Upgrade for RAC and OPS Databases
Drop Database
Conclusion
Chapter 2 Database Tuning and Performance
Database Resource Manager
Components of DRM
Resource Plans and Plan Directives
Procedure to implement and manage
Automatic Mapping
Database Tuning Improvements
User initiated Buffer Cache Flushing
Automated Checkpoint Tuning
Easier Transaction Recovery Monitoring
Performance Overview Charts
Application Tuning Improvements
Optimizer Mode
Moving from RBO to the Cost-Based Optimizer
Dynamic Sampling
Sample Table Scans
trcsess utility
Self-Tuning Features
Automatic Shared Memory Management Automatic SQL Tuning Process
SQL Tuning Advisor
Wait Event Model improvements
Overview of Wait Event Model
Conclusion
Chapter 3 Tablespace Management Temporary Tablespace
Temporary Tablespace Group Overview
Temporary Tablespace Group Benefits
New Data Dictionary View
Examples
Rename Tablespace
Tablespace Rename Overview
Tablespace Rename Benefits
Bigfile Tablespace
Bigfile Tablespace Overview
Bigfile Tablespace Benefits
Maximum Database Size
Extended ROWID Format
Data Dictionary Views Enhancement
Examples
New Cross-Platform Transportable Tablespaces
Benefits
Supported Platforms and New Data Dictionary Views
Determine Platform Endianness
Convert Datafiles using RMAN
Conclusion
Chapter 4 Table and Index Features
Table and Index Partitions
Overview
Index-Organized Tables
LOB Column Support for IOT Partitions
Locally Partitioned Bitmap Indexes on an IOT Partition Managing Index Partitions
Skipping Unusable Indexes
Enhanced Partition Management in OEM
Hash-Partitioned Global Indexes
Sorted Hash-Clustered Tables
Overview of Hash Clusters
Creating Sorted Hash-Clustered Tables
Conclusion
Chapter 5 DML Features
Single-Set Aggregates in DML Returning Clause
Single-set Aggregates in the INSERT Statement
Single-set Aggregates in the UPDATE Statement
Single-set Aggregates in the DELETE Statement
Virtual Spreadsheets and Upsert Through SQL Interrow Calculations
Conclusion
Chapter 6 SELECT Features
Grouped Table Outer Join
Increased Number of Aggregates per Query
Remote Stored Functions in SELECT Statements
hs_call_name
Case-Insensitive and Accent-Insensitive Query and Sort
Enhanced CONNECT BY Support
Hierarchical Query Pseudo-columns
Oracle Expression Filter
The Expression Attribute Set
Expression Datatype and Expressions
The EVALUATE Operator
Example use of the EVALUATE Operator
Using CREATE INDEX for Expressions
SQL Regular Expressions
Changes to INSTR
Changes to LIKE
Changes to REPLACE
Changes to SUBSTR
Multilingual Regular Expression Syntax
Notes on the POSIX operators and Oracle enhancements:
Regular Expression Operator Multilingual Enhancements
Row Timestamp
Conclusion
Chapter 7 Improved Existing Features
Existing Features That Have Changed
Asynchronous Row Change Data Capture
Overview of Change Data Capture
Change Data Capture
Asynchronous Change Data Capture
Cross-Platform Transportable Tablespaces
Transporting Tablespaces Between Databases: A General Procedure
The RMAN CONVERT Clause
Restrictions and Usage Notes
Enhanced Table Functions
Returning Large Amounts of Data from a Function
Representing Dynamically Typed Data Using SYS.AnyData, SYS.AnyType, and SYS.AnyDataSet Types
External Tables Unload
Unloading Data Using External Tables and the oracle_datapump Access Driver The oracle_datapump Access Driver
LOGFILE | NOLOGFILE
Using LOGFILE clause for oracle_datapump
Unloading Data With the oracle_datapump Access Driver
PARALLEL UNLOAD
Supported Datatypes
Unsupported Datatypes
Reserved Words For the oracle_datapump Access Driver
Enhanced MERGE Functionality
Prerequisites
MERGE Syntax
Conclusion
Chapter 8 Initialization Parameters
Basic Parameters
Old Parameters
New Parameters
create_stored_outlines
db_flashback_retention_target
db_recovery_file_dest
db_recovery_file_dest_size
db_unique_name
ddl_wait_for_locks
ldap_directory_access
log_archive_config
instance_type
osm_diskgroups
osm_diskstring
osm_power_limit
plsql_code_type
plsql_debug
plsql_optimize_level
plsql_warnings
resumable_timeout
sga_target
skip_unusable_indexes
smtp_out_server
sqltune_category
streams_pool_size
Conclusion
Chapter 9 Manageability Features
Easy Management
Self-Managing Database
Overview
Automatic Workload Repository (AWR)
General Benefits
Physical Structures
Collection Process
Using and Managing AWR
Active Session History
Database Feature Usage Metrics
Advisory Framework - ADDM
Server Alert Mechanism
Pro-Active Space Management
Shared Server Configuration
Transaction Manageability
New Columns
Changes to v$session_longops
MAXTRANS and Maximum Concurrency
Statistics Collection
Dictionary Objects
Guidelines
Changes to dbms_stats package
DML Table Monitoring Changes
Audit Enhancements
Uniform Audit Trails
Fine-Grained Auditing (FGA)
Response File
Conclusion
Chapter 10 Utilities Improvements
Data Pump Utilities
Data Pump Overview
Data Pump Export
Data Pump Import
New Scheduler Utilities
Scheduler Components
Create, Enable, Disable, and Drop a Program
Create and Drop a Schedule
Create A Job Class
Data Dictionary Views
SQL*Plus Enhancements
New DEFINE Variables
SPOOL Command Enhancement
Conclusion
Chapter 11 Network Features
Enhanced Oracle Networking
Export Directory Naming Entries to Local Naming File (tnsnames.ora) Dynamic Connection Manager Configuration
Easy Connect Naming Method
Syntax Element Description
Configuring Easy Connect Naming to Use an Alias
Improved Network Outage Detection
Configuration of the Advanced Profile Information
Improved Connection Manager Access Rules
Automated Shared Server Configuration
Simplified Shared Server Configuration
Shared Server Initialization Parameters
Certificate Validation with Certificate Revocation Lists (CRLs)
Certificate Revocation Lists
Centralized CRL Management
Centralized User Management for Kerberos Users
What Exactly is a Trusted Database?
Access to Single Sign On Wallet Java Applications
Single Station Administration for Password Authentication
Overview of Password-Authenticated Enterprise User Database Login Information Security Protecting Database Password Verifiers With Directory ACLS
Transport Layer Security (TLS) Support
SSL and TLS in an Oracle Environment
Conclusion
Chapter 12 Backup and Recovery Features
Flashback and RMAN
Extended Flashback Functions
Flashback Database
Flashback Standby Database
Flashback Re-instantiation
Flashback Drop
Flashback Table
Flashback Row History
Flashback Transaction History
RMAN Enhancements
Automated Channel Failover for Backup and Restore
Automated File Creation During Recovery
Simplified Backups to Disk
Proxy Copy Backup of Archivelogs
Incrementally Updated Backups
Simplified Recovery Through Resetlogs
Full Database Begin Backup Command
Changes to the ALTER DATABASE END BACKUP command
Change-Aware Incremental Backups
Automated Disk-Based Backup and Recovery
RMAN Database Dropping and Deregistration
Automated TSPITR Instantiation
Simplified Recovery Manager Cataloging of Backup Files
RMAN and the new EM (Enterprise Manager)
Conclusion
Chapter 13 High Availability and Scalability
Avoiding Disruption
Online Redefinition
New Additions and Changes
Synonyms and Views
LOGMINER
Automatic Determination of Redo Log Files
Data Guard Environment
Overview of New Features
Supplemental Logging
Real Application Clusters
Service Registration
CRS – Cluster Ready Services
Rolling Upgrades
Conclusion
Chapter 14 Oracle Streams Enhancement Areas
Streams Overview
Configuration and Management
Streams Administrator
SYSAUX tablespace usage
Streams Pool in the SGA
Buffered Queues in the SGA
Purge Streams Queues
New and Modified Views Downstream Capture
Overview
Advantages of Downstream Capture
Downstream Capture Method
Monitoring Downstream Capture Processes Asynchronous Change Data Capture Rule Interface Improvements
Negative Rule Sets
Rule Based Transformation
Subset Rules
Replication Enhancements
Instantiating with RMAN
Instantiating with Transportable Tablespace Migration from Advanced Replication Messaging Enhancements
Streams Messaging Client
Simplified Configuration
Advance Queue Enhancements Conclusion
Chapter 15 Security Enhancements Virtual Private Database
Virtual Private Database Overview
Column-Level Privacy
Apply a Column-Level VPD Policy
New Types of VPD Policies
Audit Enhancements
Conclusion
Chapter 16 Business Intelligence New Features and Enhancements Materialized View Enhancements
Partition Tracking Change
MJV Enhancement
Nested MV enhancement
Query Rewriting
Generalized Multiple Table Instance Support Summary Management Improvements
Materialized View Column Aliases
Partition Maintenance Operations (PMOP)
Enhanced Explain Plan for Materialized Views
Materialized View Log
Describe and Validate Dimensions
SQLAccess Advisor
Overview
Overall Benefits
Using the SQLAccess Advisor
OLAP Enhancements
Hierarchy Handling Improvements
XML Support for Analytic Workspace (AW)
Useful Views
Support for Bioinformatics
Bioinformatics Overview
Data Mining for Analytical
Multi-User Access Control
Enhanced Adaptive Bayes Network (ABN)
Non-Negative Matrix Factorization
Text Mining
Conclusion
Chapter 17 Globalization Improvements
New Features
Globalization Development Kit Enhancements
Overview of the Globalization Development Kit and Its Components Overview of Designing a Global Internet Application
Getting Started with Oracle Globalization Services
Oracle Services in OGS
Internet Services in OGS
Enhanced Character Set Scanner and Converter
Character Set Scanner Reports
Universal Character Sets
Conclusion
Chapter 18 Oracle Enterprise Manager
Installation Of OEM
Starting and Navigating in OEM
The EM2GO Interface
Conclusion
Index
Manageability Features CHAPTER
9
Easy Management
This chapter focuses on the new features aimed at database management. Self-management, or easy management, has been the key word for Oracle 10g. The main areas of enhancements are:
Self-Managing Database –To aid self-management and auto tuning of the database, Oracle introduces a new persistent store called automatic workload repository (AWR), which collects memory statistics, and the automatic database diagnostic monitor (ADDM), which monitors and analyzes statistics and provides advisory services.
Simplified Configuration of Shared Servers–The configuration of shared servers and their associated dispatchers has been largely made dynamic. Transaction Manageability– Now, we can monitor normal transaction rollback or transactions recovered by SMON. In addition, we can view historical information about transaction recovery and transaction rollback.
Also, Oracle 10g pre-configures objects for maximum concurrency. Simplified Statistics Collection – Beginning with the 10g version, both the real and fixed dictionary tables need to be analyzed in order to get better performance. There are several new procedures that have been introduced to streamline and simplify the compute statistics operation of dictionary objects.
Extended Support for FGA (Fine Grain Audit) - There is now additional fine grain audit support for ‘insert’, ‘delete’, and ‘update’ statements. Response File Creation during database install –With Oracle Database 10g you now have true silent capability. When running OUI in silent mode on
a character mode console, you no longer need to specify an X-server or
set the DISPLAY environment variable on UNIX. No GUI classes are instantiated.
Self-Managing Database
Generally speaking, database administration primarily entails regular health checks, handling the performance issues, organizing periodic monitoring
activities, and locating the bottlenecks. Checking the space thresholds, creating indexes, allocating necessary resources, collecting statistics etc. are the other important activities.
The Oracle 10g database release takes over many of these tedious database tasks and tuning processes. The collection, storage, and analysis of status information about the database and the host has become relatively easy. Many new features have been added to aid self-management and self-tuning. This section focuses on database manageability features newly introduced and enhanced.
Overview
The Oracle 10g database introduces a new framework for managing many tuning tasks automatically, for producing real-time information about the database’s health, and for extending advisories to improve performance.
The new manageability infrastructure mainly focuses on four areas. They are as follows:
Automatic Workload Repository- The ability to automatically collect and store database information at regular intervals is crucial. This information should be persistent and accurate. Oracle introduces a new internal data store called Automatic Workload Repository (AWR) to collect and store data. AWR is central to the whole framework of self and automatic management. It works with internal Oracle database components to process, maintain, and access performance statistics for problem detection and self-tuning.
Automatic Database Diagnostic Monitor- The second key component is the advisory framework that provides expert recommendations to improve performance. The Automatic Database Diagnostic Monitor (ADDM) is a server-based performance expert in a box. It can perform real time root cause analysis of performance issues. It relies on the current statistics within the SGA and on the contents of the AWR. In addition, there are various advisory tools to help make tuning decisions.
Next, are the Automatic Routine Administration tasks. By using the newly introduced Scheduler, you can delegate to the Oracle database some of the repetitive tasks that need to be performed to keep the database up-to-date.
Server Generated Alerts- Oracle Database 10g is capable of automatically detecting many database alarm situations.
We have covered the topic of the Scheduler in Chapter 10, Utilities Improvements. We will examine the other features next.
Automatic Workload Repository (AWR)
AWR is the main infrastructure that collects in-memory statistics at regular intervals and makes them available to the internal and external services or clients. External clients, such as Oracle Enterprise Manager and SQL*plus sessions, can view the AWR information through the data dictionary views. Internal clients, such as the ADDM and other self-tuning components or advisories, make use of the contents in the AWR.
General Benefits
Advantages of the new workload repository include:
AWR is a record of all database in-memory statistics historically stored.
In the past, historical data could be obtained manually using the ‘statspack’ utility. AWR automatically collects more precise and granular information than past methods.
With a larger data sample, more informed decisions could be made. The self-tuning mechanism uses this information for trend analysis.
The statistics survive database reboots and crashes.
Another benefit is that AWR statistics are accessible to external users, who can build their own performance monitoring tools, routines, and scripts.
Oracle recommends that statspack users switch to the Workload Repository in Oracle Database 10g.
Physical Structures
AWR is stored in tables owned by ‘SYS’ but physically located on the SYSAUX tablespace. The Workload Repository contains two types of tables: Metadata Tables: These are used to control, process, and describe the Workload Repository tables. For example, Oracle uses the metadata tables to determine when to perform snapshots, and what to capture to disk.
Historical Statistics Tables: These tables store historical statistical information about the database in the form of snapshots. Each snapshot is a capture of the in–memory database statistics data at a certain point in time. All names of the AWR tables are prefixed with WRx$ with x specifying the kind of table:
WRM$ tables store metadata information for the Workload Repository.
WRH$ tables store historical data or snapshots.
WRI$ tables store data related to advisory functions.
The WRx$ tables are organized into the following categories: File Statistics, General System Statistics, Concurrency Statistics, Instance Tuning Statistics, SQL Statistics, Segment Statistics, Undo Statistics, Time– Model Statistics, Recovery Statistics, and RAC Statistics. Fig 9.1 shows a graphical view of the AWR table types.
Following is the list of WRM$ tables that control all repository operations. WRM$_BASELINE
WRM$_DATABASE_INSTANCE
WRM$_SNAPSHOT
WRM$_SNAP_ERROR
WRM$_WR_CONTROL
Figure 9.1 Automatic Workload Repository (AWR) Structures
Dictionary views are also provided, making the historical data available to users for query. Any view related to the historical information in the AWR has the dba_hist_ prefix. Figures 9.2 and 9.3 show the full list of the WR tables and the dba_hist* tables respectively.
Collection Process
The collection process involves the capture of in-memory statistics from the SGA and their transfer to the physical tables located in the workload repository. The new background process, MMON, does this. The frequency of the capture snapshot is 30 minutes by default, however it can be adjusted suitably.
You can control the interval and retention of snapshot generation by the dbms_workload_repository.modify_snapshot_settings procedure. For example:
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (43200, 15);
In this example, the retention period is specified as 30 days (43200 min) and the interval between each snapshot is 10 min.
Taking manual snapshots is also supported in conjunction with
the automatic snapshots that the system generates. For this,
the dbms_workload_repository.create_snapshot procedure is
used.
The snapshots are used for computing the rate of change of a statistic. This is mainly used for performance analysis. A snapshot sequence numer (snap_id) identifies each snapshot, which is unique within the Workload Repository. Figure 9.4 shows the relation of AWR to other components.
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SESSION_HISTORY_BL WRH$_BG_EVENT_SUMMARY
WRH$_BUFFER_POOL_STATISTICS WRH$_DATAFILE
WRH$_DB_CACHE_ADVICE
WRH$_DB_CACHE_ADVICE_BL
WRH$_DLM_MISC
WRH$_ENQUEUE_STAT
WRH$_ENQUEUE_STAT_BL
WRH$_EVCMETRIC_HISTORY
WRH$_EVENT_NAME
WRH$_FILEMETRIC_HISTORY
WRH$_FILESTATXS
WRH$_FILESTATXS_BL
WRH$_INSTANCE_RECOVERY
WRH$_JAVA_POOL_ADVICE
WRH$_LATCH
WRH$_LATCH_BL
WRH$_LATCH_CHILDREN
WRH$_LATCH_CHILDREN_BL
WRH$_LATCH_MISSES_SUMMARY WRH$_LATCH_MISSES_SUMMARY_BL WRH$_LATCH_NAME
WRH$_LATCH_PARENT
WRH$_LATCH_PARENT_BL
WRH$_LIBRARYCACHE
WRH$_LOG
WRH$_METRIC_NAME
WRH$_MTTR_TARGET_ADVICE
WRH$_UNDOSTAT
WRH$_WAITSTAT
WRH$_WAITSTAT_BL
WRH$_OPTIMIZER_ENV
WRH$_OSSTAT
WRH$_OSSTAT_BL
WRH$_OSSTAT_NAME
WRH$_PARAMETER
WRH$_PARAMETER_BL
WRH$_PARAMETER_NAME
WRH$_PGASTAT WRH$_PGASTAT_BL
WRH$_PGA_TARGET_ADVICE
WRH$_PGA_TARGET_ADVICE_BL WRH$_RECOVERY_FILE_DEST_STAT WRH$_RESOURCE_LIMIT
WRH$_RMAN_PERFORMANCE
WRH$_ROLLSTAT
WRH$_ROWCACHE_SUMMARY
WRH$_ROWCACHE_SUMMARY_BL WRH$_SEG_STAT
WRH$_SEG_STAT_BL
WRH$_SEG_STAT_OBJ
WRH$_SESSMETRIC_HISTORY
WRH$_SGA
WRH$_SGASTAT
WRH$_SGASTAT_BL
WRH$_SHARED_POOL_ADVICE
WRH$_SQLBIND
WRH$_SQLBIND_BL
WRH$_SQLSTAT
WRH$_SQLSTAT_BL
WRH$_SQLTEXT
WRH$_SQL_PLAN
WRH$_SQL_SUMMARY
WRH$_SQL_WORKAREA_HISTOGRAM WRH$_STAT_NAME
WRH$_SYSMETRIC_HISTORY
WRH$_SYSMETRIC_SUMMARY
WRH$_SYSSTAT
WRH$_SYSSTAT_BL
WRH$_SYSTEM_EVENT
WRH$_SYSTEM_EVENT_BL
WRH$_SYS_TIME_MODEL
WRH$_SYS_TIME_MODEL_BL
WRH$_TABLESPACE_SPACE_USAGE WRH$_TABLESPACE_STAT
WRH$_TABLESPACE_STAT_BL
WRH$_TEMPFILE
WRH$_TEMPSTATXS
WRH$_THREAD
Workload Repository Schema Tables
related to historical data / snapshots Figure 9.2 Workload Repository Tables
DBA_HIST_DATABASE_INSTANCE DBA_HIST_SNAPSHOT
DBA_HIST_SNAP_ERROR
DBA_HIST_BASELINE
DBA_HIST_WR_CONTROL
DBA_HIST_DATAFILE
DBA_HIST_FILESTATXS
DBA_HIST_TEMPFILE
DBA_HIST_TEMPSTATXS
DBA_HIST_SQLSTAT
DBA_HIST_SQLTEXT
DBA_HIST_SQL_SUMMARY
DBA_HIST_SQL_PLAN
DBA_HIST_SQLBIND
DBA_HIST_OPTIMIZER_ENV
DBA_HIST_EVENT_NAME
DBA_HIST_SYSTEM_EVENT
DBA_HIST_BG_EVENT_SUMMARY DBA_HIST_WAITSTAT
DBA_HIST_ENQUEUE_STAT
DBA_HIST_LATCH_NAME
DBA_HIST_LATCH
DBA_HIST_LATCH_CHILDREN
DBA_HIST_LATCH_PARENT
DBA_HIST_LATCH_MISSES_SUMMARY DBA_HIST_LIBRARYCACHE
DBA_HIST_DB_CACHE_ADVICE
DBA_HIST_BUFFER_POOL_STAT
DBA_HIST_ROWCACHE_SUMMARY DBA_HIST_SGA
DBA_HIST_SGASTAT
DBA_HIST_PGASTAT DBA_HIST_RESOURCE_LIMIT
DBA_HIST_SHARED_POOL_ADVICE DBA_HIST_SQL_WORKAREA_HSTGRM DBA_HIST_PGA_TARGET_ADVICE DBA_HIST_INSTANCE_RECOVERY DBA_HIST_JAVA_POOL_ADVICE
DBA_HIST_THREAD
DBA_HIST_STAT_NAME
DBA_HIST_SYSSTAT
DBA_HIST_SYS_TIME_MODEL
DBA_HIST_OSSTAT_NAME
DBA_HIST_OSSTAT
DBA_HIST_PARAMETER_NAME
DBA_HIST_PARAMETER
DBA_HIST_UNDOSTAT
DBA_HIST_ROLLSTAT
DBA_HIST_SEG_STAT
DBA_HIST_SEG_STAT_OBJ
DBA_HIST_METRIC_NAME
DBA_HIST_SYSMETRIC_HISTORY DBA_HIST_SYSMETRIC_SUMMARY DBA_HIST_SESSMETRIC_HISTORY DBA_HIST_FILEMETRIC_HISTORY DBA_HIST_EVCMETRIC_HISTORY DBA_HIST_DLM_MISC
DBA_HIST_RCVRY_FILE_DEST_STAT DBA_HIST_RMAN_PERFORMANCE DBA_HIST_ACTIVE_SESS_HISTORY DBA_HIST_TABLESPACE_STAT
DBA_HIST_LOG
DBA_HIST_MTTR_TARGET_ADVICE DBA_HIST_TBSPC_SPACE_USAGE
Dictionary Views to Query
the Histotrical Workload Repository Data
Figure 9.3 Dictionary Views exposing the historical workload repository
The statistics_level initialization parameter controls the type of statistics collected and stored. The parameter can take any one of these values.
basic: The computation of AWR statistics and metrics is turned off.
typical: Only a part of the statistics are collected. They are normally enough to monitor the Oracle database behavior.
all: All possible statistics are captured.
Figure 9.4 AWR and relation with components
Types of Data Collected
The collected statistics include:
New time model statistics that show the amount of time spent on database activities.
Object Statistics that determine both access and usage of the segments.
Some selected statistics collected in v$sysstat and v$sesstat.
Some of the optimizer statistics that include statistics for self-learning and tuning.
The ADDM Active Session History (ASH), which represents the history of the recent session’s activity.
These statistics can be broadly categorized into 5 groups based on their nature.
Base Statistics – This group represents raw data, which are generally values from the start of the database, e.g. total physical reads.
SQL Statistics – Important measurements regarding the SQL statement.
For example: Disk Read per SQL statement.
Metrics – These are the secondary statistics, and the most interesting ones from a tuning point of view. Metrics track the rates of change of activities in the database. For example, the average physical reads in the system in the last 30 min. is a metric.
Contents of Active Session History - For example, db file sequential read wait for SID of 16, file# 12, block# 1245, obj# 67, and time: 20000us.
Advisor Results – Results of the expert analysis by the advisor framework.
Note on Metrics
As we noted above, the Metrics are the statistics derived from base statistics. They represent the delta values between the snapshot periods. Metrics are used by internal components (clients) for system health monitoring, problem detection and self-tuning.
Metrics are a key component in tracking threshold violations and thus for generating alerts. Since Metrics are pre-computed internally, they are readily available for use. Metrics are very useful in understanding the load and the nature of activity between two specified time periods.
A unique number, which is referred to as a metric number, identifies metrics. Each metric is also associated with a metric name. You can query the view v$metricname to find the names of all the metrics. For example:
SQL> select METRIC_NAME, METRIC_UNIT from v$metricname;
Internally, MMON periodically updates metric data from the
corresponding base statistics.
There are about 183 metrics for which you can define thresholds. Examples of metrics:
Physical Reads Per Sec
User Commits Per Sec
SQL Service Response Time
DB Block Changes Per Txn
Redo Generated Per Sec
Physical Writes Per Sec
Tablespace Space Usage
Network Traffic Volume Per Sec
Logical Reads Per Sec
CR Blocks Created Per Sec
Using and Managing AWR
You can query base statistics and metrics from the various fixed views provided. You can optionally create your own snapshots and baselines using the dbms_workload_repository package.
dbms_workload_repository package procedures are as follows:
modify_snapshot_settings: Procedure to modify the snapshot settings.
drop_baseline: Procedure to drop a single baseline.
create_baseline: Procedure to create a single baseline.
drop_snapshot_range: Procedure to drop a range of snapshots.
create_snapshot: Procedure to create a manual snapshot immediately.
You can use the Oracle-provided SQL scripts to generate reports to view the contents of AWR.
swrfrpt.sql: This script generates a report showing information on the overall behavior of the system over a time period. The script generates a text file.
swrfrpth.sql: This script gives the same information as swrfrpt.sql, however, the generated output file uses HTML format.
Active Session History
The Active Session History (ASH) represents the history of a recent session’s activity. ASH facilitates the analysis of the system performance at the current time. ASH is composed of regular samples of information extracted from v$session. ASH is designed as a rolling buffer in memory, and earlier information is overwritten when needed. ASH uses the memory of the SGA.
It is also possible to access ASH through SQL*plus by
querying v$active_session_history. This view contains one row
for each active session per sample.
Database Feature Usage Metrics
AWR is also used to track database usage metrics. The usage metrics represent how you use the database features. The database usage metrics are divided into two categories:
The database feature usage statistics.
The High Water Mark (HWM) values of certain database attributes. Database Feature Usage Statistics include Advanced Replication, Oracle Streams, AQ, Virtual Private Database, Audit options etc.
High Water Mark Statistics include the size of the largest segment, the maximum number of sessions, the maximum number of tables, the maximum size of the database, the maximum number of data files, etc.
To view usage metric information, you can query the following:
dba_feature_usage_statistics - Lists the usage statistics of various database features.
dba_high_water_mark_statistics - Lists the database high water mark statistics.
Advisory Framework - ADDM
So far we have examined the details of the workload repository. Now let us focus on the advisory framework Oracle 10g introduces.
Advisors are the server components that provide you with useful feedback about resource utilization and performance. The most important of all advisors is the Automatic Database Diagnostic Monitor (ADDM). ADDM does analysis of the system, identifies problems and their potential causes, and comes up with recommendations for fixing the problems. It can call all other advisors also.
The main features of the ADDM are as follows:
ADDM runs automatically in the background process MMON whenever
a snapshot of in-memory statistics is taken. ADDM does analysis of the
statistics collected between two snapshots.
ADDM analysis results are written back to the workload repository for further use.
ADDM uses the new wait and time statistics model, where activities with high time consumption are analyzed on a priority basis. This is where the big impact lies.
ADDM can also be invoked manually.
The results of the proactive analysis performed by the ADDM module are posted to the workload repository and then they are available to users through the OEM console or by other SQL query means. ADDM analysis is also the driving point for Server Alerts whenever the threshold is exceeded for the specified metrics. The new OEM or Grid Control has many web pages devoted to displaying ADDM findings.
Other advisors include:
SQL Tuning Advisor: This advisor is responsible for providing tuning advice for a SQL statement. We have covered this topic in detail in Chapter 2, Database Tuning and Performance Improvements.
SQL Access Advisor: This provides expert advice on materialized views, indexes, and materialized view logs. A full detailed account of its utility and usage is covered in Chapter 16, Business Intelligence.
Segment Advisor: This advisor is responsible for space issues involving
a database object. It analyzes the growth trends. It can also extend advice
on the shrink possibility for the segments.
Undo Advisor: This advisor suggests parameter values, and how much additional space would be needed to support flashback for a specified time. However, most of the undo tuning (like undo retention) is automatic in Oracle Database 10g.
Redo Logfile Size Advisor: This determines the optimal smallest online redo log file size, based on the current fast_start_mttr_target setting and MTTR statistics.
Server Alert Mechanism
As we have noted, the Oracle 10g database collects and stores various statistics into the workload repository. Those statistics are then analyzed to produce various metrics.
Server-generated alerts pretty much depend on the derived ‘metrics’ available in the workload repository. MMON wakes up every minute to compute the metric values. In addition, for all the metrics that have thresholds defined,