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.