About Me.

My self Adinarayana working as Implementation Application DBA with advanced technologies like RAC/PCP,OID/SSO,DMZ,Exadata and Fusion Middleware i.e Demantra,Application Server,SOA,FMW,BPEL and UPK. Created this blog to share the useful information related to DBA and Application DBA Your comments and suggestions are most welcome. Disclaimer: Please note all the views and opinions expressed in this site are my own. It's not recommend to use the fixes/suggestions provided in this site directly in production instance, please test them before implementing.

Monday, July 22, 2013

How To Generate ASH Report

How To Generate ASH Report:

Active Session History (ASH) is a very important feature to analyze sessions in Oracle to troubleshoot transient problems that last for just few minutes. ASH reports gives the details needed to analyze the sessions.
Below example shows how to generate ASH(Active Session History) reports from SQLPLUS. The same can be done using Enterprise Manager also.
Default Script Location : $ORACLE_HOME/rdbms/admin/
Scripts                             : ashrpt.sql (for singleinstance)

ashrpti.sql (for  RAC)

1. Running ashrpt.sql (for single instance)
SQL> @$ORACLE_HOME/rdbms/admin/ashrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id    DB Name      Inst Num Instance
———– ———— ——– ————
12345     TESTDB             1  TESTDB1

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Enter ‘html’ for an HTML report, or ‘text’ for plain text
Defaults to ‘html’
Enter value for report_type: html
..
..
Defaults to -15 mins
Enter value for begin_time: 09/15/11 00:00:00
Report begin time specified: 09/15/11 00:00:00
Enter value for duration: 5
Report duration specified:   5
Using 15-Sep-11 00:00:00 as report begin time
Using 15-Sep-11 00:05:00 as report end time
Enter value for report_name: ASH
Summary of All User Input
————————-
Format         : HTML
DB Id          : 12345
Inst num       : 1
Begin time     : 15-Sep-11 00:00:00
End time       : 15-Sep-11 00:05:00
Slot width     : Default
Report targets : 0
Report name    : ASH
End of Report
</body></html>
Report written to ASH

2. Running the ashrpti.sql (for RAC)
SQL> @$ORACLE_HOME/rdbms/admin/ashrpti.sql
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Enter ‘html’ for an HTML report, or ‘text’ for plain text
Defaults to ‘html’
Enter value for report_type: html
Type Specified:                                  html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id     Inst Num DB Name      Instance     Host
———— ——– ———— ———— ————
12345         1     TESTDB       TESTDB1      abcd123

Defaults to current database
Enter value for dbid: 12345
Using database id:
12345
Defaults to current instance
Enter value for inst_num: 1
Using instance number:
1
..
..
Defaults to -15 mins
Enter value for begin_time: 09/15/11 00:00:00
Report begin time specified: 09/15/11 00:00:00
Enter duration in minutes starting from begin time:
Defaults to SYSDATE – begin_time
Press Enter to analyze till current time
Enter value for duration: 5
Report duration specified:   5
Using 15-Sep-11 00:00:00 as report begin time
Using 15-Sep-11 00:05:00 as report end time
Enter value for slot_width: 1
(Here we get values for each minutes. So we get more precise values.)
Specify SESSION_ID (eg: from V$SESSION.SID) report target:
Defaults to NULL:
Enter value for target_session_id:
SESSION report target specified:
Specify SQL_ID (eg: from V$SQL.SQL_ID) report target:
Defaults to NULL: (% and _ wildcards allowed)
Enter value for target_sql_id:
SQL report target specified:
Specify WAIT_CLASS name (eg: from V$EVENT_NAME.WAIT_CLASS) report target:
[Enter 'CPU' to investigate CPU usage]
Defaults to NULL: (% and _ wildcards allowed)
Enter value for target_wait_class:
WAIT_CLASS report target specified:
Specify SERVICE_HASH (eg: from V$ACTIVE_SERVICES.NAME_HASH) report target:
Defaults to NULL:
Enter value for target_service_hash:
SERVICE report target specified:
Specify MODULE name (eg: from V$SESSION.MODULE) report target:
Defaults to NULL: (% and _ wildcards allowed)
Enter value for target_module_name:
MODULE report target specified:
Specify ACTION name (eg: from V$SESSION.ACTION) report target:
Defaults to NULL: (% and _ wildcards allowed)
Enter value for target_action_name:
ACTION report target specified:
Specify CLIENT_ID (eg: from V$SESSION.CLIENT_IDENTIFIER) report target:
Defaults to NULL: (% and _ wildcards allowed)
Enter value for target_client_id:
CLIENT_ID report target specified:
Specify PLSQL_ENTRY name (eg: “SYS.DBMS_LOB.*”) report target:
Defaults to NULL: (% and _ wildcards allowed)
Enter value for target_plsql_entry:
PLSQL_ENTRY report target specified:
End of Report
</body></html>
Report written to ashrpt_1_0915_0005.html

No comments:

Post a Comment