Oracle 11g Monitoring and Tuning Script Collection
Oracle Database 11g provides a comprehensive set of features and functions to monitor database health, identify performance problems, and implement corrective actions. This article introduces some useful scripts that can help Oracle database administrators (DBAs) to perform various monitoring and tuning tasks on Oracle 11g databases.
Oracle 11g Monitoring And Tuning Script Collection
Monitoring the Database
Oracle Database 11g monitors the vital signs (or metrics) related to database health and performance, analyzes the workload running against the database, and automatically identifies any issues that need attention as alerts and performance findings on the Database Home page. DBAs can also configure Oracle Enterprise Manager Database Control (Database Control) to notify them of issues by e-mail or perform some actions such as running a script.
Some of the scripts that can be used to monitor the database are:
access.sql: This script shows the number of sessions accessing each object in the database.
active_sessions.sql: This script shows the active sessions in the database along with their SQL statements, wait events, and resource consumption.
active_user_sessions.sql: This script shows the active user sessions in the database along with their SQL statements, wait events, and resource consumption.
cache_hit_ratio.sql: This script shows the cache hit ratio for the buffer cache and the library cache.
db_info.sql: This script shows some basic information about the database such as name, version, status, instance number, startup time, etc.
db_links.sql: This script shows the details of the database links in the database such as owner, name, username, host, etc.
health.sql: This script shows a summary of the health of the database such as alert log errors, invalid objects, locked objects, etc.
hidden_parameters.sql: This script shows the hidden parameters in the database along with their values and descriptions.
invalid_objects.sql: This script shows the invalid objects in the database along with their owners and types.
license.sql: This script shows the license information of the database such as product name, version, status, etc.
nls_params.sql: This script shows the national language support (NLS) parameters of the database such as character set, language, territory, etc.
options.sql: This script shows the options installed in the database such as partitioning, spatial, OLAP, etc.
parameters.sql: This script shows all the parameters in the database along with their values and descriptions.
parameters_non_default.sql: This script shows only the non-default parameters in the database along with their values and descriptions.
recovery_status.sql: This script shows the recovery status of the database such as flashback mode, archive mode, log switch frequency, etc.
registry_history.sql: This script shows the history of patches applied to the database such as patch name, version, date, etc.
sessions.sql: This script shows all the sessions in the database along with their status, username, machine name, program name, etc.
sessions_by_machine.sql: This script shows all the sessions in the database grouped by machine name along with their count and status.
sessions_by_sid.sql: This script shows all the sessions in the database grouped by session ID along with their status, username, machine name, program name, etc.
system_events.sql: This script shows all the system events in the database along with their total waits, time waited, average wait, etc.
system_parameters.sql: This script shows all the system parameters in the database along with their values and descriptions.
system_stats.sql: This script shows all the system statistics in the database such as CPU speed, IO seek time, IO transfer speed, etc.
users.sql: This script shows all the users in the database along with their default tablespace, temporary tablespace, profile, status, etc.
Tuning the Database
Oracle Database 11g includes a self-diagnostic engine called Automatic Database Diagnostic Monitor (ADDM) that makes it possible for Oracle Database to diagnose its own performance and determine how any identified problems can be resolved. ADDM periodically collects snapshots of the database state and workload and compares them to identify performance issues. ADDM also provides recommendations to resolve the issues along with their expected benefits.
Some of the scripts that can be used to tune the database are:
db_cache_advice.sql: This script shows the buffer cache advisory for different cache sizes and their impact on physical reads and read time.
high_water_mark.sql: This script shows the high water mark of each segment in the database and how much space can be reclaimed by reorganizing them.
index_monitoring_status.sql: This script shows the status of index monitoring for each index in the database and how many times they have been used by SQL statements.
index_usage.sql: This script shows the usage statistics of each index in the database such as leaf blocks, distinct keys, clustering factor, etc.
library_cache.sql: This script shows the library cache statistics for each namespace such as get requests, get hits, pin requests, pin hits, etc.
latch_hit_ratios.sql: This script shows the latch hit ratios for each latch in the database along with their misses and sleeps.
lru_latch_ratio.sql: This script shows the LRU latch ratio for each buffer pool in the database along with their gets and misses.
pga_target_advice.sql: This script shows the PGA target advisory for different PGA sizes and their impact on optimal executions, one-pass executions, multi-pass executions, etc.
sql_area.sql: This script shows the SQL area statistics for each SQL statement in the shared pool such as executions, parse calls, buffer gets, disk reads, etc.
sql_text.sql: This script shows the SQL text of each SQL statement in the shared pool along with their hash value and address.
tuning.sql: This script shows a summary of tuning recommendations from ADDM for each snapshot period along with their finding name, type, impact, and action.
The scripts mentioned in this article are only a sample of the many scripts that can be used to monitor and tune Oracle 11g databases. DBAs can also create their own scripts or modify existing ones to suit their needs and preferences. Oracle Database 11g provides a rich set of tools and features that make monitoring and tuning tasks easier and more effective.
References:
[Monitoring and Tuning the Database - 11g Release 2 (11.2) - Oracle]
[DBA Scripts for Oracle (12c, 11g, 10g, 9i, 8i)]
a7a7d27f09