AI - Oracle Performance Tuning

 Oracle Database performance tuning is a continuous process of optimizing the database and its environment to ensure efficient and timely execution of operations. It's about getting the most out of your hardware and software resources, leading to faster application response times and higher transaction throughput.

It's a complex discipline that involves understanding various layers: the application, SQL queries, database instance parameters, storage, and the operating system.

The Performance Tuning Roadmap:

A systematic approach to performance tuning usually involves these steps:

1. Understand Performance Objectives: What are the user's expectations? What is the acceptable response time? What is the required throughput? Define clear, measurable goals.

2. Measure Current Performance (Baselines): Establish a baseline of normal operation. Collect metrics on CPU usage, I/O, memory, network, and application response times during various load periods (peak, off-peak). This allows you to identify deviations and assess the impact of tuning changes.

3. Identify Bottlenecks: This is the diagnostic phase. Where is the system spending most of its time? Is it waiting for I/O, CPU, locks, network, or something else?

4. Minimize the Impact of Bottlenecks: Implement specific tuning techniques to alleviate the identified bottlenecks.

5. Verify and Monitor: After making changes, measure performance again to confirm improvements and ensure no new bottlenecks have been introduced. Continue monitoring to sustain optimal performance.

Key Areas of Oracle Database Performance Tuning:

Oracle performance tuning can be broadly categorized into several areas:

1. SQL Tuning (Most Impactful):

• Identify High-Cost Queries: Use tools like AWR (Automatic Workload Repository), ADDM (Automatic Database Diagnostic Monitor), SQL Monitoring, and V$SQL views to find SQL statements consuming the most resources (CPU, I/O, elapsed time).

• Analyze Execution Plans: Use EXPLAIN PLAN or DBMS_XPLAN.DISPLAY_AWR to understand how Oracle is executing a query. Look for full table scans on large tables when indexes are expected, complex join methods, or excessive I/O.

• Indexing Strategy:

  • Create appropriate B-tree indexes on columns frequently used in WHERE clauses, JOIN conditions, ORDER BY, and GROUP BY clauses.

  • Consider composite indexes for multi-column queries.

  • Use bitmap indexes for low-cardinality columns (few distinct values) in data warehousing environments.

  • Avoid over-indexing, as indexes consume space and slow down DML operations (INSERT, UPDATE, DELETE).

• Rewrite Inefficient SQL:

  • Select only necessary columns: Avoid SELECT *.

  • Use bind variables: Prevents hard parsing, improves cursor sharing.

  • Avoid functions on indexed columns in WHERE clauses: WHERE TRUNC(date_col) = TRUNC(SYSDATE) will prevent index use. Instead, use range conditions like WHERE date_col BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE) + 0.99999.

  • Optimize JOINs: Ensure correct join order and types (nested loops, hash joins, sort-merge joins).

  • Consider UNION ALL over UNION if duplicates are not an issue for better performance.

  • Use EXISTS vs. IN depending on selectivity.

• Optimizer Statistics: Ensure optimizer statistics (collected by DBMS_STATS package) are up-to-date and accurate. The optimizer relies on these statistics to choose the best execution plan.

• Partitioning: For very large tables, partitioning can significantly improve query performance by reducing the amount of data scanned and improving manageability.

2. Instance Tuning (Memory and CPU):

• SGA (System Global Area) Tuning: The SGA is shared memory for all database processes.

  • Shared Pool: Caches parsed SQL statements, PL/SQL code, data dictionary information. Insufficient size can lead to excessive parsing (hard parsing) and latch contention.

  • Buffer Cache: Caches data blocks from data files. A large buffer cache reduces physical I/O.

  • Redo Log Buffer: Caches redo entries.

  • Large Pool, Java Pool: For specific features.

  • Automatic Memory Management (AMM/ASMM): Oracle can automatically manage SGA and PGA components (or the entire instance memory) to optimize performance.

• PGA (Program Global Area) Tuning: Private memory for each server process. Used for sorting, hashing, and cursor states.

  • Insufficient PGA can lead to excessive disk sorts and hash operations.

  • Automatic PGA Memory Management: Recommended, Oracle automatically tunes PGA.

• CPU Utilization: If the database server is CPU-bound, analyze which sessions or SQL statements are consuming the most CPU. This often points back to inefficient SQL.

3. I/O Tuning:

• Disk Subsystem Optimization: Ensure your storage is fast enough (SSDs are often preferred for high-performance databases).

• File Placement: Distribute data files, redo logs, and archive logs across different disks or disk groups to balance I/O.

• RAID Configuration: Choose appropriate RAID levels for performance and redundancy (e.g., RAID 1+0 for high I/O workloads).

• ASMLib/ASM (Automatic Storage Management): Oracle's recommended volume manager for database files, simplifying storage management and optimizing I/O.

4. Database Design and Schema Optimization:

• Normalization vs. Denormalization: Choose the appropriate level of normalization. While normalization reduces data redundancy, denormalization (with controlled redundancy) can sometimes improve query performance for reporting or specific applications.

• Appropriate Data Types: Use the most efficient data types for your columns (e.g., NUMBER for numbers, VARCHAR2 for variable-length strings, avoiding unnecessary large precision/scale).

• Table Compression: Can reduce storage space and I/O, improving query performance (especially with Hybrid Columnar Compression for data warehouses).

5. Concurrency and Contention:

• Locks and Latches: Identify and resolve contention for database resources (e.g., row locks, table locks, library cache latches, buffer cache latches). Often caused by inefficient DML or SQL.

• Blocking Sessions: Identify sessions that are blocking other sessions and the root cause (e.g., long-running transactions, uncommitted work).

6. Application-Level Tuning:

• Connection Pooling: Reusing database connections instead of opening/closing for each transaction significantly reduces overhead.

• Batch Processing: For large data loads or updates, use batch processing to reduce transaction overhead.

• Client-side Optimization: Efficient data retrieval and processing on the client side.

Oracle's Automatic Tuning Features (Self-Tuning Database):

Modern Oracle versions (especially 10g and later with the Oracle Diagnostics Pack and Tuning Pack licenses) provide powerful automatic features:

• AWR (Automatic Workload Repository): Collects, processes, and maintains performance statistics within the database. It forms the foundation for other automatic tools.

• ADDM (Automatic Database Diagnostic Monitor): Analyzes AWR data to identify performance bottlenecks and provides recommendations.

• SQL Tuning Advisor: Analyzes high-load SQL statements and recommends solutions (e.g., new indexes, SQL rewrites, profile creation).

• SQL Access Advisor: Recommends partitioning and materialized views for query optimization.

• Memory Advisors: Provide advice on optimal sizing for SGA and PGA components.

• Automatic Shared Memory Management (ASMM) / Automatic Memory Management (AMM): Automatically adjusts SGA and PGA sizes.

• Automatic Segment Space Management (ASSM): Manages free space within segments, improving concurrency.

When to Tune:

• Proactively: Regular monitoring, baseline establishment, and proactive adjustments based on trends.

• Reactively: When performance issues are reported or detected (e.g., slow queries, high CPU, application timeouts).

Performance tuning is an iterative process. You diagnose, implement changes, monitor, and repeat until the performance objectives are met. It requires a deep understanding of Oracle architecture, SQL, and the specific application workload.

High Water Mark
=============

Oracle's Recovery Manager (RMAN) is a powerful command-line utility used for backing up, restoring, and recovering Oracle databases. Here's a breakdown of common RMAN commands, categorized by their primary function:

Essential RMAN Commands

These commands are fundamental for interacting with RMAN and performing basic operations.

 * CONNECT: Establishes a connection to a target database, auxiliary database, or recovery catalog.

   * Example: CONNECT TARGET / (connects to the target database as SYSDBA)

   * Example: CONNECT CATALOG rman/password@catdb (connects to a recovery catalog)

 * RUN: Executes a block of RMAN commands. Many operations, especially those involving channel allocation, are enclosed within a RUN block.

   * Example:

     RUN {

  BACKUP DATABASE;

}

 * ALLOCATE CHANNEL: Defines a connection between RMAN and the database instance for I/O operations (e.g., disk, tape).

   * Example: ALLOCATE CHANNEL d1 DEVICE TYPE DISK;

 * RELEASE CHANNEL: Deallocates a previously allocated channel.

 * CONFIGURE: Configures persistent RMAN settings, which remain in effect across RMAN sessions.

   * Example: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

   * Example: CONFIGURE DEFAULT DEVICE TYPE TO DISK;

   * Example: CONFIGURE CONTROLFILE AUTOBACKUP ON;

 * SHOW: Displays current RMAN configuration settings.

   * Example: SHOW ALL;

   * Example: SHOW RETENTION POLICY;

 * REPORT: Provides various reports about the database, backups, and recovery needs.

   * Example: REPORT SCHEMA; (shows the database schema)

   * Example: REPORT OBSOLETE; (lists backups no longer needed based on retention policy)

   * Example: REPORT NEED BACKUP; (lists datafiles that require backup)

Backup Commands

These commands are used to create backups of your database.

 * BACKUP DATABASE: Backs up the entire database.

   * Example: BACKUP DATABASE;

   * Example with archived redo logs: BACKUP DATABASE PLUS ARCHIVELOG;

 * BACKUP TABLESPACE: Backs up specific tablespaces.

   * Example: BACKUP TABLESPACE users, hr;

 * BACKUP DATAFILE: Backs up individual datafiles.

   * Example: BACKUP DATAFILE 1, 2;

 * BACKUP ARCHIVELOG ALL: Backs up all archived redo logs.

 * BACKUP CURRENT CONTROLFILE: Backs up the current control file.

 * BACKUP SPFILE: Backs up the server parameter file.

 * BACKUP AS COPY: Creates image copies (exact duplicates) of datafiles or the control file, rather than backup sets.

   * Example: BACKUP AS COPY DATABASE;

 * BACKUP VALIDATE: Checks the validity of backups without actually performing the backup operation. Useful for testing.

   * Example: BACKUP VALIDATE DATABASE;

Recovery Commands

These commands are crucial for restoring and recovering your database from backups.

 * RESTORE DATABASE: Restores the entire database from backup.

   * Example: RESTORE DATABASE;

 * RESTORE TABLESPACE: Restores specific tablespaces.

   * Example: RESTORE TABLESPACE users;

 * RESTORE DATAFILE: Restores individual datafiles.

   * Example: RESTORE DATAFILE 3;

 * RESTORE CONTROLFILE: Restores the control file.

   * Example: RESTORE CONTROLFILE FROM AUTOBACKUP; (restores from an autobackup)

 * RECOVER DATABASE: Applies archived redo logs and/or incremental backups to roll forward the database to a consistent state.

   * Example: RECOVER DATABASE;

 * RECOVER TABLESPACE: Recovers specific tablespaces.

 * RECOVER DATAFILE: Recovers individual datafiles.

 * BLOCKRECOVER: Recovers individual corrupt data blocks.

Duplication Commands

RMAN's DUPLICATE command is used to create a copy of a target database, often for testing, standby creation, or development environments.

 * DUPLICATE TARGET DATABASE TO new_db_name: Creates a duplicate database.

   * Example from active database: DUPLICATE TARGET DATABASE TO clone_db FROM ACTIVE DATABASE NOFILENAMECHECK;

   * Example using backups:

     RUN {

  SET UNTIL TIME 'SYSDATE - 1/24'; # To duplicate to a point in time

  DUPLICATE TARGET DATABASE TO test_db;

}

Maintenance and Catalog Commands

These commands help manage RMAN's repository and maintain backups.

 * CROSSCHECK: Verifies the physical existence of RMAN backups and copies on disk or tape, and updates their status in the RMAN repository.

   * Example: CROSSCHECK BACKUP;

   * Example: CROSSCHECK ARCHIVELOG ALL;

 * DELETE: Deletes backups, copies, and archived logs, and removes their records from the RMAN repository.

   * Example: DELETE EXPIRED BACKUP; (deletes expired backups based on crosscheck)

   * Example: DELETE OBSOLETE; (deletes backups that are no longer needed based on the retention policy)

   * Example: DELETE NOPROMPT BACKUP OF DATABASE; (deletes all database backups without prompting)

 * CHANGE: Modifies the status or attributes of RMAN backups and copies.

   * Example: CHANGE BACKUPSET 123 UNAVAILABLE;

   * Example: CHANGE DATAFILECOPY '/path/to/datafile.dbf' AVAILABLE;

 * CATALOG: Adds information about user-managed backups or file copies to the RMAN repository.

   * Example: CATALOG START WITH '/u01/app/oracle/oradata/MYDB/datafile/';

 * REGISTER DATABASE: Registers a target database with a recovery catalog.

 * UNREGISTER DATABASE: Unregisters a target database from a recovery catalog.

 * CREATE CATALOG: Creates the schema for the recovery catalog.

 * DROP CATALOG: Removes the schema from the recovery catalog.

 * RESYNC CATALOG: Synchronizes the recovery catalog with the current control file of the target database.

 * UPGRADE CATALOG: Upgrades the recovery catalog schema to a newer version.

 * LIST: Displays information about backups, copies, and database schema.

   * Example: LIST BACKUP SUMMARY;

   * Example: LIST DATAFILE COPY;

This list covers many of the commonly used RMAN commands. For a complete and detailed reference, always consult the official Oracle documentation for your specific database version.

Active Sessions History

The Oracle Active Session History (ASH) report is an invaluable tool for Oracle Database performance tuning, particularly for diagnosing transient (short-lived) performance problems. While the Automatic Workload Repository (AWR) report provides a broader historical view over a longer period (typically hours or days), ASH focuses on what active sessions were doing every second.

What is Active Session History (ASH)?

ASH is an in-memory performance diagnostic feature introduced in Oracle 10g. It works by:

1. Sampling Active Sessions: Every second, the database samples the state of all active sessions. An "active session" is defined as a session that is either consuming CPU or waiting for a non-idle wait event. Inactive sessions are not sampled.

2. Storing in SGA: This sampled data is stored in a circular buffer within the System Global Area (SGA) in memory. This in-memory storage minimizes overhead on the production system.

3. Persistence to AWR: Periodically, a portion of the ASH data is offloaded from the SGA to disk and stored in the DBA_HIST_ACTIVE_SESS_HISTORY view as part of the AWR infrastructure. This allows for historical analysis even after the in-memory buffer is overwritten.

Why is the ASH Report Important for Performance Tuning?

The ASH report is crucial for:

• Diagnosing Transient Issues: Unlike AWR, which might average out short-lived spikes, ASH's one-second sampling frequency makes it excellent for pinpointing performance bottlenecks that last only a few minutes (e.g., a sudden slowdown that users reported).

• Targeted Analysis: It allows you to perform highly scoped analysis by various dimensions or their combinations, such as:

  • Time (e.g., analyze a specific 5-minute window)

  • Session ID (SID) and Serial#

  • SQL ID (the specific SQL statement causing the activity)

  • Wait Event (what the session was waiting for)

  • Module and Action (application context)

  • Client ID

  • Program

• Understanding "What was actually happening?": ASH provides a granular "CCTV feed" of database activity, showing exactly what was happening at a specific point in time, who was doing it, and what resources they were consuming or waiting on.

• Identifying Root Causes Quickly: It helps quickly identify:

  • The top SQL statements consuming resources (CPU, I/O) or experiencing the most waits.

  • The sessions (users/processes) that were most active or problematic.

  • The most prevalent wait events.

  • Blocking sessions and the resources they were holding.

  • Specific database objects (tables, indexes) involved in high activity or contention.

Generating an ASH Report:

You need the DBA role or specific ADVISOR and AWR privileges to generate an ASH report.

The most common methods are:

1. Using SQL*Plus (Command Line):

   • Connect to your database as a user with appropriate privileges (e.g., SYS AS SYSDBA).

   • Navigate to the $ORACLE_HOME/rdbms/admin directory.

   • Run the script:

     • For a single instance database: @ashrpt.sql

     • For a RAC (Real Application Clusters) database: @ashrpti.sql

   • The script will prompt you for:

     • Report type: html (recommended for readability) or text.

     • Begin time: Specify the start time in minutes before the current system date (e.g., -10 for 10 minutes ago, or a specific date/time like '2025-06-28 19:00:00').

     • Duration: The length of the report period in minutes from the begin time (e.g., 5 for a 5-minute report).

     • Report name: A file name for the generated report (e.g., my_ash_report.html).

     • (For RAC) dbid and inst_num: Database ID and instance number(s).

   • Example:

     SQL

     SQL> @$ORACLE_HOME/rdbms/admin/ashrpt.sql
     Enter value for report_type: html
     Enter value for begin_time: -10  -- Start 10 minutes ago
     Enter value for duration: 5     -- Report duration of 5 minutes
     Enter value for report_name: my_ash_report.html
     

   • The HTML report will be generated in the current working directory.

2. Using Oracle Enterprise Manager (OEM) Cloud Control / Database Management:

   • OEM provides a user-friendly graphical interface to generate and view ASH reports.

   • Navigate to the Performance Hub for your database.

   • You'll typically find a "Reports" or "Download Report" option, where you can select "Active Session History" and specify the time range.

Analyzing an ASH Report:

An ASH report (usually HTML) is structured into several sections that help pinpoint performance issues:

1. Report Summary / Load Profile:

   • Provides an overview of the workload, average active sessions, CPU usage, etc.

   • Average Active Sessions (AAS): This is a key metric. If AAS consistently exceeds the number of CPU cores available, it indicates a bottleneck (either CPU starvation or waiting on resources).

2. Top User Events:

   • Lists the most significant wait events by their percentage of total database time. This tells you what the database sessions were spending most of their time waiting for (e.g., db file sequential read for single block I/O, log file sync for committing transactions, CPU + CPU wait if CPU bound).

   • Focus on events with high percentages and their corresponding wait classes.

3. Top Background Events:

   • Similar to Top User Events, but for background processes (e.g., LGWR, DBWn, ARCH). Helps identify internal database contention.

4. Top SQL by DB Time:

   • Identifies the SQL statements that consumed the most database time (CPU + wait time).

   • This is often the most critical section. Look for SQL IDs with high percentages.

   • You can then investigate these SQL IDs further to check their execution plans, identify missing indexes, or rewrite the query.

5. Top Sessions:

   • Shows which individual sessions (user, program, module, action) contributed most to the overall activity.

   • Helps in identifying specific users or application processes that were causing the most load during the analyzed period.

6. Top Blocking Sessions:

   • Crucial for identifying lock contention. Shows which sessions were holding locks and preventing other sessions from progressing.

   • Provides the blocker's session ID, SQL ID, and the object being contended.

7. Top Objects:

   • Lists database objects (tables, indexes, LOBs) that were most actively accessed or involved in waits. A "hot" object might indicate missing indexes, poor design, or heavy contention.

8. Activity Over Time:

   • Provides a time-series graph showing average active sessions, broken down by wait class or other dimensions. This helps visualize trends and identify specific time points when performance degraded.

ASH vs. AWR:

Feature	AWR Report	ASH Report
Granularity	Snapshot-based (e.g., 30-min or 1-hour intervals)	1-second samples of active sessions
Focus	Overall database performance over time	Transient issues and what specific sessions were doing
Duration	Typically longer periods (hours to days)	Shorter, targeted periods (minutes to tens of minutes)
Use Case	Trend analysis, capacity planning, identifying recurring problems	Diagnosing sudden slowdowns, high-impact events, pinpointing root cause of specific incidents
Data Source	Aggregated statistics from V$ views, saved in DBA_HIST_*	Primarily V$ACTIVE_SESSION_HISTORY (in-memory buffer) and DBA_HIST_ACTIVE_SESS_HISTORY
Overhead	Minimal but continuous data collection	Very low overhead due to sampling and in-memory storage

In summary, the ASH report is a powerful, high-resolution diagnostic tool that complements the AWR report, enabling DBAs to quickly and accurately pinpoint the root causes of specific, often transient, performance issues in an Oracle database.