Oracle DBMS_WORKLOAD_REPOSITORY

HOW to generate ASH REPORT ( ACTIVE SESSION HISTORY)

ASH_REPORT_HTML

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> SELECT dbid FROM v$database;

      DBID
----------
1378393633

SQL> SELECT inst_id FROM gv$instance;

   INST_ID
----------
         1

SQL> SELECT sample_time
  2  FROM gv$active_session_history
  3  ORDER BY 1;
  
  ...................................................
  ..................................................
10-JUL-14 07.29.24.494 PM
10-JUL-14 07.30.08.625 PM

517 rows selected.

SQL> set pagesize 0
SQL> set linesize 121
SQL> spool d:\report\ash_rpt.html
SQL> SELECT * FROM TABLE(dbms_workload_repository.ash_report_html(1378393633, 1, SYSDATE-30/1440, SYSDATE-1/1440));

....................
....................
....................

</table><p />
<br /><a class="awr" href="#top">Back to Top</a><p />
<p />
End of Report
</body></html>

453 rows selected.

SQL>spool off

-- After that please see in D:\report directory  ash_rpt.html has been generated.

*********************************************************************************
Alternative ASH Text Report

SQL> spool d:\report\ash_rpt.txt
SQL> SELECT * FROM TABLE(dbms_workload_repository.ash_report_text(1378393633, 1, SYSDATE-30/1440, SYSDATE-1/1440));

..........................................
..........................................
♀Activity Over Time                DB/Inst: ORCL/orcl  (Jul 10 19:26 to 19:55)
-> Analysis period is divided into smaller time slots
-> Top 3 events are reported in each of those slots
-> 'Slot Count' shows the number of ASH samples in that slot
-> 'Event Count' shows the number of ASH samples waiting for
   that event in that slot
-> '% Event' is 'Event Count' over all ASH samples in the analysis period

                         Slot                                   Event
Slot Time (Duration)    Count Event                             Count % Event
-------------------- -------- ------------------------------ -------- -------
19:26:09   (51 secs)        9 CPU + Wait for CPU                    9   40.91
19:27:00   (3.0 min)        5 control file sequential read          3   13.64
                              CPU + Wait for CPU                    1    4.55
                              Disk file operations I/O              1    4.55
19:30:00   (3.0 min)        1 CPU + Wait for CPU                    1    4.55
19:36:00   (3.0 min)        1 CPU + Wait for CPU                    1    4.55
19:42:00   (3.0 min)        2 CPU + Wait for CPU                    2    9.09
19:45:00   (3.0 min)        1 Disk file operations I/O              1    4.55
19:48:00   (3.0 min)        2 CPU + Wait for CPU                    2    9.09
19:51:00   (3.0 min)        1 CPU + Wait for CPU                    1    4.55
          -------------------------------------------------------------

End of Report

309 rows selected.

SQL>spool off

-- Now try to open ash_rpt.txt in notepad++ or edit plus , the text allignment will look ok.

***************************************************************************

Automatic Workload Repository (AWR)

AWR_SQL_REPORT_HTML

SQL> SELECT dbid FROM v$database;

      DBID
----------
1378393633


SQL> SELECT inst_id FROM gv$instance;

   INST_ID
----------
         1

SQL> SELECT TO_CHAR(s.startup_time,' DD MON "at" HH24:MI:SS') INSTART_FMT,
di.instance_name INST_NAME, di.db_name DB_NAME, s.snap_id SNAP_ID,
TO_CHAR(s.end_interval_time,'DD MON YYYY HH24:MI') SNAPDAT,
s.snap_level LVL
FROM dba_hist_snapshot s, dba_hist_database_instance di
WHERE di.dbid = s.dbid
AND di.instance_number = s.instance_number
AND di.startup_time = s.startup_time
ORDER BY snap_id;

SQL>SELECT sql_id
FROM gv$active_session_history
WHERE TRUNC(sql_exec_start) = TRUNC(SYSDATE);

5hfunyv38vwfp
6zmjb9mc87m93
1qvq5p7wjsbdr
a8j39qb13tqkr
1yzr1rwpsaqh4
..............
..............
165 rows selected.


SQL> spool d:\report\awr_sql_rpt.html

SQL> SELECT * FROM TABLE(dbms_workload_repository.awr_sql_report_html(1378393633, 1, 31, 32, 'a8j39qb13tqkr'));

SQL>spool off

************************************

PERFORMANCE TUNING WITH AWR & ASH IN #EM12C

Oracle monitoring and performance tuning has grown over the years.  With every release of the Oracle database the monitoring capabilities that have been provided have evolved.  In Oracle11g, monitoring with statpack has given way to Automatic Workload Repository (AWR) and Active Session History (ASH) for near real time monitoring of items within the Oracle database.  Adding AWR and ASH to your performance tool kit can yield huge savings of time to tuning endeavors.

Note: AWR and ASH were also in Oracle10g, but not as robust as it currently is in Oracle11g.

Note: If you are interested in how to run AWR and ASH from the command line check out this post by a good friend of mine: here

Instead of focusing on the command line to run AWR and ASH, lets take a look at how these tools are used through Oracle Enterprise Manager 12c. 

Note: The AWR and ASH tools are covered under the Diagnostic and Tuning Management Packs for the database and OEM.  Before using these tools, make sure you have access to these management packs.

Automatic Workload Repository (AWR)

The Automatic Workload Repository (AWR) is used to automate database statistic gathering by collecting, processing, and maintaining performance statistics for database problem identification and self-tuning.  Part of the AWR is snapshots.  AWR Snapshots are gathered by default every hour and queried from memory.  Snapshots can be used to identify performance problems over time.

In order to use AWR, there are two initialization parameters that are relevant:

• STATISTICS_LEVEL – set to TYPICAL (default) or AL, enables statistics gathering for AWR. setting it to BASIC will disable statistics gathering.
• CONTROL_MANAGEMENT_PACK_ACCESS – set to DIAGNOSTIC+TUNING(default) or DIAGNOSTIC to enable diagnostic monitoring.  Setting to NONE will disable many database features including ADDM (not covered in this post).

Automatic Workload Repository (AWR) Report

With the AWR enabled for the database, an AWR report can be ran from within OEM.  While on a database home page within OEM, AWR report can be accessed from the Performance menu (Performance –> AWR –> AWR Report) (Image 1). 

Image 1:

Before you can run an AWR Report, OEM will ask you to log in to the database.  Like with anything in OEM, you can used Named Credentials or create a new one (Image 2). 

Image 2:

After logging in you will be asked if you want to use “By Baseline” or “By Snapshot”.  Most of the time, “By Snapshot” will be used (Image 3).  This step corresponds to the command line version of AWR Report when it asks what snapshots you would like to use.  Then click the Generate Report button to build the report.

Tip: Use the magnifine glass to lookup the snapshots.  This will open a separate window.

Image 3:

Once the report is generated; the report can be read in the OEM interface or saved to file for later reading or emailing to others.

Reading AWR Report

One thing to notice, the difference between the AWR Report from command line and OEM interface is that at the command line, text based report can be chosen.  When running the AWR Report from OEM, the only option is the report is automatically saved in HTML format.  Although there is a difference in report formats; the report is read the same way no matter what the format is.

The header of the report (Image 4) provides you with general information about the environment where there report was gathered from.  It also provides information about the snapshots used to generate the report and the time differences between the snapshots.

Image 4:

The next section in the report is the Report Summary.  In the Report Summary, there are a few items in interest.  The first of which is the “Instance Efficiency Percentage” section (Image 5).  In this section, you can get a sense of how well the instance is managing it memory operations.  Ideally, the target should be 100%.

Note: The only time I’m concern about this area is if any of the values are below 90%. Often this is an indication that the SGA may be to small.

Image 5:

In the same section, I also look at the “Top 5 Time Foreground Event” section (Image 6).  This section of the report provides you with a list of the current wait events that are taking the most time waiting and what wait class they belong to.  At this time, I can see what I should be tuning the environment to prevent.  In the image provided, I can see that I have a wait dealing with high water mark contention (enq: HW – contention). 

From looking at these waits, it appears that I have an object that is possibly having a problem with extents be allocated.  What I need to do next is see if I can find the object that is causing the issue.

Image 6:

Since I know that I have a high water mark contention issue, this is telling me that I need to look at what objects are being written to in the database.  In the “Segments Statistics” section of the report, I want to look at the “Segments by Physical Writes” sub-section (Image 7).  From looking at this section, I can see that the problem is on a LOB object names SYS_LOB0000253792C00002$$.

Image 7:

From looking at the AWR Report, I can quickly see the object that is causing the problem with the wait.  I can make an attempt to correct the problem and remove the wait by affecting the LOB object in an appropriate way.  In this case, that may be adding extents, looking at moving the LOB to a less accessed tablespace, etc. 

Active Session History (ASH)

Although I have identified the object that is causing the wait, I want to find the session that is causing this wait.  This is where I can use the Active Session History (ASH) Report. Before diving into the ASH Report, lets take a look at what Active Session History (ASH) actually is.

Active Session History provides detailed history of session activity by sampling database activity by sessions every second.  These samplings are taken from memory and then stored in persistent storage (tables).  The sampling that ASH does is done at the session level rather than the instance level.  The statistics that are captured is only for active sessions and is directly related to the work be performed by the sessions rather than the entire database.

Active Session History (ASH) Reports are used to analyzed transient performance problems within the database during specific time. 

Let’s take a look at how the ASH Report is ran and what it provides.

Active Session History (ASH) Report

In Oracle Enterprise Manager 12c (OEM), the ASH Report is accessed through the “Top Activity” menu (Performance –> Top Activity) (Image 8) for that database. 

Image 8:

Once on the “Top Activity” page, below the graph that is displayed there is a button labeled “Run ASH Report” (Image 9). 

Image 9:

Clicking on the “Run ASH Report” button will bring up the page to provide specific information about the ASH Report that you would like to run (Image 10).  Since the ASH samples so much data, per second, you can select a beginning date, ending date and associated times that you would like to look at in the report. 

Note: Most times, when I use generate an ASH report, I try to keep to the same timeframe as the AWR report.  Provides some consistency to findings.

Image 10:

Once I have selected the timeframe I want to use, the report can be generated.  Just like the AWR Report, the ASH Report can be viewed inside of OEM or saved to a file for later reference or sharing with others.

Active Session History (ASH) Report

Let’s read the report now.  As you recall, I found an issue with a high-water mark contention wait.  What I will be looking for in the ASH Report is similar information and possibly who is behind the wait.

Just like the AWR Report, the ASH Report has a header section where I can find all the information related to the database, database instance and timing of the report (Image 11).  This section is general in nature, but it also provides you similar information on SGA usage just like the AWR Report does.

Image 11:

The next think I like to look at in the ASH Report is the “Top User Events” (Image 12).  If I have selected the same timeframe as the AWR Report, I expect to see similar wait events as I found earlier.  As you can see, the high-water contention wait is at the top of the list again.

Image 12:

Now that I have confirmed that the high-water contention wait is the top wait for the sessions during this timeframe, I want to confirm that it is the same object I found in the AWR Report.  Information on the object causing the wait can be found in the “Top Event P1/P2/P3 Values” (Image 13) section.

Image 13:

In order to confirm that it is the same object, I need to search DBA_EXTENTS with the values from P2 (data file) and P3 (block number).  Depending on how fast the system is, I can quickly identify the object being accessed that is causing the wait.

In any tuning that is being done, I want to find the SQL statement that the session is using.  This information can also be found in the ASH Report along with the top sessions and if there are any sessions being blocked.

Summary

Overall, these two reports (AWR and ASH) provide a wide range information for anyone to use in performance diagnosing and tuning.   These reports can be ran from the command line or from OEM depending on your comfort levels with the tools; however, I recommend that any DBA looking to keep a database in good working order and top performance learn how to use and read these reports.  II t will save a lot of time in resolving issues.

***********************************

AWR Reports Vs ASH Reports

Sometimes when you face performance issue On your database, Usually first thing you are doing is Generate One of the above reports . But What is the benefits of these reports ? What is the difference ? When Can i use them ?

Let Start 

I share in my blog earlier how to generate AWR reports . But Today we are talking About another topic .

Automatic Workload Repository Reports/(AWR):

this report appear in 10g Database, I heard that some people said "AWR used Instead Of Statpack" !! NO AWR is higher Version of statpack , statpack still exists but you have to enable it.

The AWR takes a snap shot of the database in specified intervals (default is one hour) and stores in Sysaux tablespace. and you can change this interval using the following :

BEGIN

  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(

    retention => 43200,        -- Minutes (= 30 Days). Current value retained if NULL.

    interval  => 30);          -- Minutes. Current value retained if NULL.

END;

/

The snap shots are taken automatically if the statistics_level parameter is set to typical/all. If it set to basic then statistics details are not gathered,The AWR contains the performance statistics and workload information on the database.

and you can take extra snapshot by :

EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

BEGIN

  DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (

    low_snap_id  => 22, 

    high_snap_id => 32);

END;

/

As i mention above you can enable it , disable by the following command :

Enable AWR :

alter system set  statistic_level = {typical | all} scope=spfile ;

Disable AWR :

alter system set   set statistic_level = {basic} scope=spfile

 Check Statistic if its been Gathered By Fire :

Select * from V$statistics_level;

The statistics are collected and stored in memory in SGA. The in memory statistics collection area is a circular buffer, where the old data is overwritten after flushing to disk.

The AWR statistics snap shot are owned by Sys schema. ,The AWR is used to collect performance statistics including:

Wait events used to identify performance problems.

Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.

Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view.

Some system and session statistics from the V$SYSSTAT and V$SESSTAT views.

Object usage statistics.

Resource intensive SQL statements.

Active Session History (ASH) :

 The Name of this reports describe what is the major difference between it and AWR , The ASH contains recent information on active sessions sampled every second. The AWR are taken every one hour and its one hour old will not help in diagnosing issues that are current on the database. Typically to resolve issues urgenly on the database, details information pertaining o last 5 to 10 mins is critical. Because recording session activity is expensive, ASH samples V$SESSION every second and records the event for which he session are waiting.

ASH information through V$active_session_history , you can can check this table from Oracle Documentation .

As appear in the Documentation the View contain :

It include 

* sql identifier of sql statement.

* object no., file no., and block no.

* wait event identifier & parameters.

* user identifier, Session identifier and Serial number.

* client identifier and name of the operating system program.

Conclusion :

ASH can help you when there's a sudden performance degradation of the database felt.

AWR - historic past snapshot intervals.

AWR, stores the session performance statistics for analysis later.

ASH - the storage is not persistent and as time progresses, the old entries are removed to accommodate new ones. They can be viewed using V$ACTIVE_SESSION_HISTORY.