How to run ASHRPT.SQL script
To generate a text report of ASH information, run the ashrpt.sql script at the SQL prompt:
@$ORACLE_HOME/rdbms/admin/ashrpt.sql
First, you need to specify whether you want an HTML or a text report.
Enter value for report_type: text
Specify the time frame to collect ASH information by first specifying the begin time in minutes prior to the system date.
Enter value for begin_time: -10
Note: here you have to just put number in minutes eg: 10 for 10 minutes
Next, enter the duration in minutes that the report for which you want to capture ASH information from the begin time. The default duration of system date minus begin time is accepted in the following example:
Enter value for duration:
Note: left blank for default value. Default value is SYSDATE
The report in this example will gather ASH information beginning from 10 minutes before the current time and ending at the current time. Next, accept the default report name or enter a report name. The default name is accepted in the following example:
Enter value for report_name:
Using the report name ashrpt_1_0310_0131.txt
Note: Left it blank for default value.
The session history report is generated.
Purpose of ASH
ASH is way of sampling the state of sessions connected to an Oracle database in order to monitory database load as well as drill down into any performance issues that may arise. Any session that is connected to the database and is waiting for an event that does not belong to the Idle wait class is considered as an active session.ASH maintains a fixed sized circular buffer in the database System Global Area (SGA). The fixed
sized circular buffer will be allocated during database start-up time. ASH by default on an Oracle database, samples once every second and logs some 30 to 60 (depending on version) pieces of information on any session that is active at the time of sampling.Each session sample is a set of rows and the V$ACTIVE_SESSION_HISTORY view returns one row for each active session per sample, returning the latest session sample rows first. Because the active session samples are stored in a Fixed Sized circular buffer in SGA, the greater the system activity, the smaller the number of seconds of session activity that can be stored in the Fixed Sized circular buffer. This means that the duration for which a session sample appears in the V$ view, or the number of seconds of session activity that is displayed in the V$ view, is completely dependent on the database activity.
ASH Analysis
1.Online Analysis
2.Offline Analysis
Let start with ONLINE analysis
I have open two session. first one (SID 16) is running the DML like
SQL> delete test where rowid=’AAAKB9AAEAAAAAiAAA’;
From second session (SID 15) run the same DML, and it is obvious that second
session will wait for first session to commit.
Lets check the info in V$ACTIVE_SESSION_HISTORY.
Run the following script.
SQL> select SESSION_ID,NAME,P1,P2,P3,WAIT_TIME,CURRENT_OBJ#,CURRENT_FILE#,CURRENT_BLOCK#
from v$active_session_history ash, v$event_name enm
where ash.event#=enm.event# and SESSION_ID=&SID and SAMPLE_TIME>=(sysdate-&minute/(2460)); Input is Enter value for sid: 15 Enter value for minute: 1 / How many minutes activity you want to see */
output is 59 lines as it is wiatting more than 1 minute more than 1 minute
SESSION_ID NAME P1 P2 P3 WAIT_TIME CURRENT_OBJ# CURRENT_FILE# CURRENT_BLOCK#
15 enq: TX - row lock contention 1415053318 589825 143 0 41085 4 34
15 enq: TX - row lock contention 1415053318 589825 143 0 41085 4 34
15 enq: TX - row lock contention 1415053318 589825 143 0 41085 4 34 continue ………….
15 enq: TX – row lock contention 1415053318 589825 143 0 41085 4 34
15 enq: TX – row lock contention 1415053318 589825 143 0 41085 4 34
15 enq: TX – row lock contention 1415053318 589825 143 0 41085 4 34
So you have object details with problem info.
Lets do the OFFLINE analysis of ASH
So if your are not able to find the problem online, you can dump the ASH to a trace.
Command would be like below: where level means minute. lets dump for 10 minutes history
- SQL> alter session set events ‘immediate trace name ashdump level 10’;
or - SQL> alter system set events ‘immediate trace name ashdump level 10’;
or - SQL> oradebug setmypid
SQL> oradebug dump ashdump 10;
So you will get the trace file in udump.
ASH Contains
- SQL identifier of SQL statement(SQL_ID-SQL identifier of the SQL statement that the session was executing at the time of sampling)
- Object number, file number, and block number(CURRENT_OBJ#-Object ID of the object that the session is referencing,CURRENT_FILE#-File number of the file containing the block that the session is referencing,CURRENT_BLOCK#-ID of the block that the session is referencing)
- Wait event identifier (EVENT_ID-Identifier of the resource or event for which the session is waiting or for which the session last waited)
- Session identifier and session serial number(sid,serial#-Session serial number (used to uniquely identify a session’s objects))
- Module and action name(module-name of executing module when sampled,action-name of executing module when sampled)
- Client identifier of the session(client id)
- Service hash identifier (service_hash-hash that identifies the service)
