AUTOMATIC WORKLOAD REPOSITORY

 AUTOMATIC WORKLOAD REPOSITORY

 

What are the different AWR reports can be generated?

awrrpt.sql -> Normal AWR report on standalone DB.

awrGrpt.sql -> RAC AWR report for entire RAC node.

AwrDDrpt.sql -> AWR difference period report

awrSQrpt.sql ->AWR specific SQL_ID report for analysis

awrGDrpt-> AWR Global Diff report

 

Do the quick analysis of AWR report

 

->In the AWR Header Report, check Elapsed Time (time between two snapshots) and DB Time (Total activity on the DB), if DB TIME is greater than Elapsed Time then it means database has high workload.

->Load Profile: It provides an at-a-glance look at some specific operational statistics. You can compare these statistics with a baseline snapshot to determine if DB activity is different.

->Instance Efficiency %:  Buffer Nowait, Buffer Hit, Library Hit, Redo No Wait, In-memory Sort, Soft Parse, Latch Hit, Non-Parse CPU.

The above ratios should be above 90% in a DB.

Check hard parsing , not using bind variables.

->Top 5 Timed Foreground Events:

This section provides insight into what events the Oracle DB is spending most of its time on. In Top 5 Timed Event, use the Highest Wait Times to guide the investigation.

Wait Events are:

DB File Scattered Read: That generally happens a Full Table Scan or Fast Full Index Scans. As FTS’s are pulled into memory ,they are rarely fall into contiguous buffers but instead are scattered throughout the buffer cache. A large number here indicates that your table may have missing indexes, statistics are not updated or your indexes are not used. You can use the report to help identify the query in question and fix it.

DB File Sequential Read: This is the wait times come from physical side of the DB. It is related to memory starvation and non-selective index. Sequential read is an index read followed by table read because it is doing index lookups which tells exactly which block to go to. This could indicate poor joining order of tables or un-selective indexes in your SQL or waiting for writes to TEMP space. Need to tune PGA_AGGREGATE_TARGET. When DB File Sequential Read and DB File Scattered Read occurred in conjunction in the Top 5 Wait Events section first you should examine the SQL Ordered Physical Reads section of the report.

CPU TIME: This is the sum of the CPU used by the session. If the CPU time event is the biggest event , find the SQL statements that access the most blocks (Buffer gets and Logical I/O). The report lists such SQL statements in section SQL ordered by Gets.

Latch Free: Latches are low-level mechanism used to protect shared memory structures in the SGA. Latches are like locks on the memory that are quickly obtained and released. Latches are used to prevent concurrent access to a shared memory structure. If the latch is not available, latch free miss is recorded. Most latch problems are related to the failure to use bind variables (Library Cache Latch), redo generation issue (Redo Allocation Latch), buffer cache contention issue( Cache Buffer LRU Chain) and hot blocks in the buffer cache (Cache Buffer Chain). When Latch Miss Ration is >0.5% you should investigate the issue.

Enqueue: It is a lock that protects a shared resources. Lock protect shared resources, such  as data in record, to prevent two people updating the same data at the same time application.

->Time Model Statistics: If the SQL Time > DB CPU Time then probably have IO issue.

->SQL Information Section:

Any SQL statements appears in the TOP 5 statements in two or mode areas below , then it is prime candidate for tuning.

SQL Ordered By Elapsed Time: IO waits

SQL Ordered by CPU Time: Sorting/ Hashing

SQL Ordered by Buffer Gets: High Logical IO

SQL Ordered by Disk Reads: High physical IO

SQL Order by Executions: May indicate Loop issues

SQL Order by Parse Calls: Memory issues.

SQL Ordered by Version Count: May indicates unsafe bind variables

->I/O Stats Section: From this report you can determine if the TBS or Datafiles are suffering from sub-standard performance in term of IO response from the disk sub-system.

->Advisory Statistics Section

In this section we receive several recommendations on changing the size of different buffer pools from Advisories such as Buffer Pool Advisory, PGA Memory Advisory, Shared Pool Advisory and SGA Target Advisory  that we can perform and how that will affect the overall performance on the DB.

->Segments Statistics Sections:

This is a series of reports that let you identify objects that are heavily used such as segment by logical read and segment by physical read. These reports can help you find objects that are HOT objects in the database.

 

What you’ll check whenever user complains that his session/database is slow?

If the user is giving SID and query is still running, first of all we will check the sql_id, event ,last_call_et, seq# from v$session.

We will monitor the event and last_call_et to identify what’s query actually doing and what is the elasped_time  also we will check seq# is changing or not. If the seq# is not changing that means, there is a contention. After that I will investigate further to identify that which session is blocking. Blocking and waiting session can be identified by looking into v$session, v$process and v$lock

DBA_WAITERS View that retrieve information for each session waiting on a lock

DBA_BLOCKERS View that gives the blocking sessions  

To find out how long a session has been blocked by another session.

select sid, username, event, blocking_session,

seconds_in_wait, wait_time

from v$session where state in ('WAITING');

Check the database for blocking locks as well as latch contention.

select sid,serial#,status,event,seq#,username,machine,sql_id from v$session where sid in (588);

select sql_fulltext from v$sql where sql_id='4yv506w29vx57';

 

->Check the CPU , Memory Utilization, Paging and Swapping using top,prstat,topas, free ,vmstat to ensure the problem was not from Hardware Resource.

->Check the alert log file for any ORA error at a glance if it is hitting any bug or logical corruption.

->Check the no. of INACTIVE SESSIONs on the database for a long time holding resource using V$SESSION.LAST_CALL_ET.

->Check the blocking and waiting sessions on the database using V$SESSION,V$PROCESS and V$LOCK.

->Identify and retrieve the query if the SID is given by the user using V$SESSION and V$SQL.

->Find the TABLES in the query and identify the indexes on the tables.If the views are present in the query then extract the view metadata using DBA_VIEWS.TEXT and check the TABLES in the view.

->Check the OPTIMIZER STATISTICS are updated on both TABLES AND its INDEXES using DBA_TABLES.LAST_ANALYZED and DBA_INDEXES.LAST_ANALYZED    and check the STATUS of INDEXES it should not be UNUSABLE.

->Check the TABLES and INDEXES FRAGMENTATION using no. of rows in the table and avg_row_len of the TABLES and check the size of TABLES and INDEXES using DBA_SEGMENTS.BYTES and estimate the FRAGMENTATION %.

->Generate the Execution Plan for problematic SQL query using SET AUTOTRACE TRACEONLY EXPLAIN and enable the timing using SET TIMING ON command.

->Read the indented explain plan line by line and identify the highest COST and CPU % utilization and highest no. of rows retrieves and its size.

->Check the Access Method (FTS and Index Scan)

->Check the Joins on column (Sort Merge Join (SMJ),Nested Loops (NL),Hash Join, Cartesian Product)

->Check the indexes on the join column, if the function is using in the join or there is concatenated joins , ensure that there is function based index and composite indexes available on the joins. Also check the column name and column position using DBA_IND_COLUMNS view to ensure that the indexes are on the right column.

->Break up the SQL query into smaller part to confirm which query is causing performance degradation.

->Use the Hint or Optimizer Parameter to influence the OPTIMIZER to change the plan to get better execution plan.

->Ensure that query is not using SQL PROFILE if it is using try to disable SQL PROFILE for the query so OPTIMIZER use different execution plan.

 

Check system stats AUX_STATS$

FTS or Full Table Scan

Whole table is read upto high water mark

Uses multiblock I/O

Buffer from FTS operation is stored in LRU end of buffer cache

Index Unique Scan

Single block I/O

Index Fast Full Scan

Multi block I/O possible

Returned rows may not be in sorted order

Index Full Scan:

Single blockI/O

Returned rows generally will be in sorted order