Monday, July 30, 2012

How to read AWR Report oracle DB

Oracle AWR Report Analysis: Key Sections for Performance Monitoring

The Automatic Workload Repository (AWR) report is a powerful diagnostic tool in Oracle Database that provides a snapshot of database performance over a specified time interval. Analyzing different sections of the AWR report helps DBAs identify performance bottlenecks and trends.

This document outlines several key sections of an AWR report and what information they provide.

Report Header

This is the introductory section of the AWR report.

  • Content: Provides fundamental information about the database and the AWR snapshot interval, including:

    • Database Name and ID

    • Instance Name (if RAC)

    • Platform Information

    • Snap Interval (the duration of the snapshot)

Load Profile

The Load Profile section provides a high-level summary of database activity during the snapshot interval, normalized to per-second rates.

  • DB time(s): The total amount of time Oracle has spent performing database user calls. This does not include background processes.

  • DB CPU(s): The amount of CPU time spent on user calls. Similar to DB time, it does not include background processes. The value is typically in microseconds.

  • Redo size: The average amount of redo data generated per second by transactions.

  • Logical reads: Represents Consistent Gets + DB Block Gets. This is the total number of blocks read from the buffer cache (logical I/O).

  • Block Changes: The number of data blocks modified during the sample interval.

  • Physical reads: The number of block requests that caused a physical I/O operation (i.e., had to be read from disk).

  • Physical writes: The number of physical writes performed to datafiles.

  • User calls: The number of user queries/calls generated per second.

  • Parses: The total number of parse operations (both hard and soft) per second.

  • Hard Parses: The number of parses requiring a completely new parse of the SQL statement. These consume significant resources (latches and shared pool area).

  • Soft Parses: (Derived by subtracting hard parses from total parses). A soft parse reuses a previously hard-parsed SQL statement, consuming significantly fewer resources.

  • Sorts: The number of sort operations performed per second.

  • Logons: The number of new user logons during the interval.

  • Executes: The number of SQL executions per second.

  • Transactions: The number of user transactions per second.

Shared Pool Statistics

This section provides insights into the utilization and efficiency of the Shared Pool, a critical SGA component.

  • Begin/End Memory Usage %: Shows the percentage of the Shared Pool used at the beginning and end of the snapshot.

  • SQL with executions > 1 %: Percentage of SQL statements in the Shared Pool that have been executed more than once.

  • Memory for SQL w/exec > 1 %: Percentage of Shared Pool memory consumed by SQL statements executed more than once.

Interpretation:

  • Ideally, memory usage should not be excessively high (e.g., consistently beyond 90%), as this could indicate contention in the shared pool and suggest it is too small.

  • If the percentage is very low (e.g., around 50%), it might indicate that the shared pool is too large for the current workload, leading to wasted memory.

Instance Efficiency Percentages (Target 100%)

These percentages indicate how efficiently the database instance is operating. Values close to 100% are desirable.

  • Buffer Nowait %: Percentage of buffer gets that did not have to wait.

  • Redo NoWait %: Percentage of redo entries that did not have to wait for space in the redo buffer.

  • Buffer Hit %: Percentage of logical reads that found the block already in the buffer cache (avoiding physical I/O).

  • In-memory Sort %: Percentage of sort operations performed entirely in memory (not spilling to disk).

  • Library Hit %: Percentage of parse calls that found the SQL statement already in the library cache (soft parse).

  • Soft Parse %: Percentage of total parse calls that were soft parses.

  • Execute to Parse %: Percentage of executions relative to parses. A high value indicates good SQL reuse.

  • Latch Hit %: Percentage of latch gets that were successful on the first attempt.

  • Parse CPU to Parse Elapsd %: Ratio of CPU time spent parsing to total elapsed time during parsing. A low value might indicate parsing contention.

  • % Non-Parse CPU: Percentage of CPU time spent on actual execution rather than parsing.

Top 5 Timed Foreground Events

This crucial section identifies the top 5 events that foreground processes spent the most time waiting for. "Time" is a very important component here, as it directly points to where the database is spending its time waiting. Analyzing these events is often the starting point for performance tuning.

RAC Statistics

This section is present in AWR reports generated for Oracle Real Application Clusters (RAC) environments. It provides metrics specific to inter-instance communication and resource management, such as Global Cache (GC) related wait events (e.g., gc current block request, gc cr block request).

Time Model Statistics

This section breaks down the total DB time into various components, showing how much time was spent on different database activities (e.g., SQL execution, parsing, PL/SQL execution, Java execution). This provides a granular view of where the database time is actually being consumed.

SQL Statistics

This section provides detailed performance metrics for individual SQL statements. It typically includes sub-sections that order SQL statements by:

  • SQL ordered by Elapsed Time: Top SQL statements consuming the most total time (CPU + Wait).

  • SQL ordered by CPU Time: Top SQL statements consuming the most CPU time.

  • SQL ordered by Gets: Top SQL statements performing the most logical reads (buffer gets).

  • SQL ordered by Reads: Top SQL statements performing the most physical reads.

I/O Stats Section

This section provides detailed statistics about I/O operations.

  • Av Rd(ms) (Average Read time in milliseconds): This metric is a key indicator of I/O latency. Ideally, this value should not cross beyond 10ms. A value consistently above this threshold is considered an I/O bottleneck, indicating that the storage subsystem is struggling to deliver data quickly.

Advisory Statistics

Advisory sections provide recommendations for optimizing various SGA and PGA memory components.

  • Buffer Pool Advisory: Recommends optimal DB_CACHE_SIZE based on workload simulation.

  • PGA Aggr Summary: Summary of PGA memory usage.

  • PGA Aggr Target Stats: Statistics related to the PGA Aggregate Target.

  • PGA Aggr Target Histogram: Distribution of PGA memory usage.

  • PGA Memory Advisory: Recommends optimal PGA_AGGREGATE_TARGET based on workload.

  • Shared Pool Advisory: Recommends optimal SHARED_POOL_SIZE.

  • SGA Target Advisory: Recommends optimal SGA_TARGET (if using Automatic Shared Memory Management).

  • Streams Pool Advisory: Recommends optimal STREAMS_POOL_SIZE (if using Oracle Streams).

  • Java Pool Advisory: Recommends optimal JAVA_POOL_SIZE (if using Java in the database).

init.ora Parameters

This section lists the values of all initialization parameters that were set at the instance level during the AWR snapshot interval. This is useful for understanding the database's configuration.

By thoroughly reviewing these sections in an AWR report, DBAs can gain a comprehensive understanding of database performance, identify bottlenecks, and make informed decisions for tuning and optimization.


=====================================================================


Here's how you can derive DB IOPS (Input/Output Operations Per Second) and Throughput (data transferred per second) from the sections of an AWR report you've described:

To understand your database's I/O performance, you'll primarily look at the "Load Profile" and potentially infer from the "I/O Stats" section.

Deriving DB IOPS (Input/Output Operations Per Second)

IOPS represents the number of individual read or write operations performed per second.

  1. Identify Key Metrics in "Load Profile":

    • Physical reads: This is the total number of data blocks that Oracle had to read from disk into memory during the AWR snapshot interval. Each of these counts as an I/O operation.
    • Physical writes: This is the total number of data blocks that Oracle wrote from memory to disk during the AWR snapshot interval. Each of these also counts as an I/O operation.
  2. Find the AWR Snapshot Interval:

    • Look at the "Report Header" section. It will specify the start and end times of the AWR snapshot, from which you can calculate the elapsed_seconds.
      • AWR Interval (seconds) = END_TIME - START_TIME
  3. Calculate IOPS:

    • Total IOPS = (Physical reads + Physical writes) / AWR Interval (seconds)

    Example: If Physical reads = 1,000,000, Physical writes = 500,000, and AWR Interval = 3600 seconds (1 hour): Total IOPS = (1,000,000 + 500,000) / 3600 = 1,500,000 / 3600 ≈ 416.67 IOPS

Deriving DB Throughput (Data Transferred per Second)

Throughput represents the total volume of data (in MB/GB) transferred to or from disk per second.

  1. Identify Key Metrics in "Load Profile":

    • Again, use Physical reads and Physical writes.
  2. Determine Database Block Size:

    • The AWR report itself usually lists the database block size (e.g., 8KB, 16KB) in the "Report Header" or "init.ora Parameters" section. If not, you can query show parameter db_block_size in SQL*Plus.
    • Convert the block size to bytes (e.g., 8KB = 8 * 1024 bytes = 8192 bytes).
  3. Find the AWR Snapshot Interval:

    • As before, from the "Report Header".
  4. Calculate Throughput:

    • Total Throughput (Bytes/sec) = ((Physical reads + Physical writes) * Database Block Size in Bytes) / AWR Interval (seconds)
    • To convert to Megabytes per second (MB/sec), divide the result by (1024 * 1024).
    • To convert to Gigabytes per second (GB/sec), divide the result by (1024 * 1024 * 1024).

    Example: Using the above IOPS example, with a Database Block Size = 8192 bytes: Total Throughput (Bytes/sec) = (1,500,000 * 8192) / 3600 = 12,288,000,000 / 3600 ≈ 3,413,333 Bytes/sec Total Throughput (MB/sec) = 3,413,333 / (1024 * 1024) ≈ 3.25 MB/sec

Using "I/O Stats" Section

The "I/O Stats" section, particularly the Av Rd(ms) (Average Read time in milliseconds), is an indicator of I/O latency, not directly IOPS or throughput.

  • Av Rd(ms): This metric tells you how quickly your storage subsystem is responding to read requests.
    • A value consistently above 10ms (as you noted) indicates a potential I/O bottleneck, suggesting that your storage system might be struggling to keep up with the demand, which would directly impact your achievable IOPS and throughput. While it doesn't give you the raw numbers, it's a critical health check for your I/O subsystem.


Monday, July 16, 2012

Statistics Lock in Oracle10g

Oracle10g has one of the useful feature that we can lock the table statistics. When statistics on a table are locked, all the statistics depending on the table, including table statistics, column statistics, histograms and statistics on all dependent indexes, are considered to be locked.


We can use this feature in the following circumstances...

1. There are tables where you want to setup gathering statistics manually. You can stop gathering statistics during the regular schedule by locking the statistics.

2. Some cases, Queries works fine with old statistics. You can avoid gathering statistics at this situation.

3. Some time, tables are bigger and automatic gathering statistics might fail silently. In this scenario, we might need to lock the table and collect the statistics seperately. Refer these links Post1Post2,Post3

4. Sometime, gathering statistics, creating histograms takes very long time on bigger table and we can avoid such a bigger table while collecting statistics for all the tables in schema or DB level.

5. For some reason, if we want to use any specific parameter to gather statistics on particular table, then we can use this option to lock the statistics and gather statistics in different time.




SQL> execute dbms_stats.lock_table_stats('SCOTT','EMP');
PL/SQL procedure successfully completed.
SQL> execute dbms_stats.lock_schema_stats('SCOTT');
PL/SQL procedure successfully completed.


Tuesday, July 10, 2012

orainstRoot and ROOT.SH after the Installation


orainstRoot.sh needs to be run to change the Permissions and groupname to 770 and to dba.

Root.sh (ORACLE_HOME) location needs to be run to create a ORATAB in /etc/oratab or /opt/var/oratab in Solaris and to copy dbhome, oraenv and coraenv to /usr/local/bin.

orainstRoot.sh


[root@oracle11g ~]# /u01/app/oraInventory/orainstRoot.sh
Changing permissions of /u01/app/oraInventory to 770.
Changing groupname of /u01/app/oraInventory to dba.
The execution of the script is complete

root.sh

[root@oracle11g ~]# /u01/app/oracle/product/11.1.0/db_1/root.sh
Running Oracle 11g root.sh script...
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/app/oracle/product/11.1.0/db_1
Enter the full pathname of the local bin directory: [/usr/local/bin]:
Copying dbhome to /usr/local/bin ...
Copying oraenv to /usr/local/bin ...
Copying coraenv to /usr/local/bin ...
Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
Finished product-specific root actions.


10 G Background process

MMAN

Memory Manager (MMAN) coordinates the sizing of different memory components within SGA. MMAN keeps a track of sizes of components and allocates/de-allocates memory based on their usage. This process is used only when you enable Automatic Shared Memory Management.


RVWR


Process responsible for writing flashback logs which stores pre-image of data-blocks.
These logs can be used to flash back database to past point in time. It proves very handy specially in situation of logical data corruptions like an accidental drop/truncate of a table or commit of an unwanted transaction.


CTWR


Change Tracking Writer (CTWR) Process introduced in 10g to track changes to block to assist fast RMAN incremental backups.


MMNL


The Memory Monitor Light (MMNL) process introduced in 10g is responsible for writing out statistics buffer to disk as needed. It works with Automatic Workload Repository (AWR)


MMON
The memory monitor (MMON) process was introduced in 10g is responsible for gathering statistics for Automatic Workload Repository. This helps Oracle doing automatic problem detection and self tuning.



M000
MMON background slave (m000) processes.


RBAL
RBAL (Re-balancer) is the ASM related process that performs rebalancing of disk resources controlled by ASM.

ARBx
These processes are managed by the RBAL process and are used to do the actual rebalancing of ASM controlled disk resources. The number of ARBx processes invoked is directly influenced by the asm_power_limit parameter.

ASMB
The ASMB process is used to provide information to and from the Cluster Synchronization Services used by ASM to manage the disk resources. It is also used to update statistics and provide a heartbeat mechanism