About Active Session History (ASH) And ASH Analytics

The Active Session History(ASH) provide real-time diagnostic information. ASH Analytics is a feature of OEM Cloud Control 12c, which visualises ASH information,making it even simpler to diagnose to diagnose performance problems.

ASH requires the Diagnostics and Tuning option in Enterprise Edition.

Once you've navigated to a database target, the "ASH Analytics" menu will be available under the "Performance" section.

$ORACLE_HOME/rdbms/admin/ashrpt.sql

[root@MaxwellDBA ~]# 
[root@MaxwellDBA ~]# 
[root@MaxwellDBA ~]# su - oracle
Last login: Mon Feb 13 12:36:53 CST 2023 on pts/4
[oracle@MaxwellDBA ~]$ 
[oracle@MaxwellDBA ~]$ 
[oracle@MaxwellDBA ~]$ cd $ORACLE_HOME
[oracle@MaxwellDBA dbhome_1]$ pwd
/opt/oracle/product/19c/dbhome_1
[oracle@MaxwellDBA dbhome_1]$ cd rdbms/admin/
[oracle@MaxwellDBA admin]$ pwd
/opt/oracle/product/19c/dbhome_1/rdbms/admin
[oracle@MaxwellDBA admin]$ ls -ltr ashrpt*
-rw-r--r-- 1 oracle oinstall  7413 May 29  2017 ashrpt.sql
-rw-r--r-- 1 oracle oinstall  1572 May 29  2017 ashrptistd.sql
-rw-r--r-- 1 oracle oinstall  1516 May 29  2017 ashrptipdb.sql
-rw-r--r-- 1 oracle oinstall   841 May 29  2017 ashrptinoop.sql
-rw-r--r-- 1 oracle oinstall 31311 Jun 13  2018 ashrpti.sql
[oracle@MaxwellDBA admin]$ 

The script prompts you for the following details:

• Report Type: [ html | text ]
• Instance number: [all | n ] - On single instance databases, this defaults to "1". On RAC databases you can report on a specific instance number or "all" instances.
• Begin Time: The script describes the formats for this value. It can be an explicit date string or an offset of the current datetime. The default is -15 minutes.
• Duration: The number of minutes to report on. The default duration is (SYSDATE - begin_time).
• Report Name: A default name is provided. Change this if required.

Depending on the options selected, the ASH report scripts call one of several table functions from the DBMS_WORKLOAD_REPOSITORY package.

• ASH_REPORT_TEXT
• ASH_REPORT_HTML
• ASH_GLOBAL_REPORT_TEXT
• ASH_GLOBAL_REPORT_HTML

SQL Developer and ASH Reports

 ASH Viewer

The ASH Viewer tool gives a graphical view of active session history data within the Oracle instance. Interestingly, it is supports Oracle 8i onward. In releases prior to Oracle 10g, or if you don't have a Diagnostic and Tuning Pack license, you can connect using the "Standard" connection and the tool will mimic the functionality of ASH. If you have the necessary licenses, you can make "Enterprise" connections, which use ASH to provide the data.