Active Session History
For performance issues, normally we will go with top-down approach.
Okay if we are coming to database level , from 11gR2 on-wards we could use the ASH efficiently and quickly to look the top events. Please note that ASH have added feature in 11GR2 than in 10g.
Most important thing is that we don’t have to depend on sql trace instead which we get all the information from dba_hist_active_session_history. (only thing is this needs license which sql trace doesn’t) but you know need to have manual work to capture all these things.
The ASH report will provide you the following thing which helps us to drill down quickly than AWR reports.
ASH Report
• Top Events
• Load Profile
• Top SQL
• Top PL/SQL
• Top Java
• Top Call Types
• Top Sessions
• Top Objects/Files/Latches
• Activity Over Time
When you invoke the ASH report it will give you the available min of reports for you. So you could give -1 (which will consider as sysdate -1 hour) or 0.5 (which will consider as sysdate – 0.5 hour).
Example
Oldest ASH sample available: 01-Feb-12 22:00:26 [ 12381 mins in the past]
Latest ASH sample available: 10-Feb-12 12:21:41 [ 0 mins in the past]
Specify the timeframe to generate the ASH report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter begin time for report:
- Valid input formats:
-- To specify absolute begin time:
-- [MM/DD[/YY]] HH24:MI[:SS]
-- Examples: 02/23/03 14:30:15
-- 02/23 14:30:15
-- 14:30:15
-- 14:30
-- To specify relative begin time: (start with '-' sign)
-- -[HH24:]MI
-- Examples: -1:15 (SYSDATE - 1 Hr 15 Mins)
-- -25 (SYSDATE - 25 Mins)
Example report
Views
• V$ACTIVE_SESSION_HISTORY
• DBA_HIST_ACTIVE_SESS_HISTORY
This view dba_hist_active_sess_history will give information like “ event 10046 trace”. This view will give lots of information so we need to trill down it.
Example we could use column EVENT to point in particual column
For example
Where event=’ buffer busy waits’ Or event like ‘%I/O%’
Likewise there are many useful ways to make this table to have good information.
If you combine the view dba_hist_snapshot and dba_hist_active_sess_history. You could able to find lots of information
Reports
@?/rdbms/admin/ashrpt.sql
@?/rdbms/admin/ashrpti.sql
For performance issues, normally we will go with top-down approach.
Okay if we are coming to database level , from 11gR2 on-wards we could use the ASH efficiently and quickly to look the top events. Please note that ASH have added feature in 11GR2 than in 10g.
Most important thing is that we don’t have to depend on sql trace instead which we get all the information from dba_hist_active_session_history. (only thing is this needs license which sql trace doesn’t) but you know need to have manual work to capture all these things.
The ASH report will provide you the following thing which helps us to drill down quickly than AWR reports.
ASH Report
• Top Events
• Load Profile
• Top SQL
• Top PL/SQL
• Top Java
• Top Call Types
• Top Sessions
• Top Objects/Files/Latches
• Activity Over Time
When you invoke the ASH report it will give you the available min of reports for you. So you could give -1 (which will consider as sysdate -1 hour) or 0.5 (which will consider as sysdate – 0.5 hour).
Example
Oldest ASH sample available: 01-Feb-12 22:00:26 [ 12381 mins in the past]
Latest ASH sample available: 10-Feb-12 12:21:41 [ 0 mins in the past]
Specify the timeframe to generate the ASH report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter begin time for report:
- Valid input formats:
-- To specify absolute begin time:
-- [MM/DD[/YY]] HH24:MI[:SS]
-- Examples: 02/23/03 14:30:15
-- 02/23 14:30:15
-- 14:30:15
-- 14:30
-- To specify relative begin time: (start with '-' sign)
-- -[HH24:]MI
-- Examples: -1:15 (SYSDATE - 1 Hr 15 Mins)
-- -25 (SYSDATE - 25 Mins)
Example report
Views
• V$ACTIVE_SESSION_HISTORY
• DBA_HIST_ACTIVE_SESS_HISTORY
This view dba_hist_active_sess_history will give information like “ event 10046 trace”. This view will give lots of information so we need to trill down it.
Example we could use column EVENT to point in particual column
For example
Where event=’ buffer busy waits’ Or event like ‘%I/O%’
Likewise there are many useful ways to make this table to have good information.
If you combine the view dba_hist_snapshot and dba_hist_active_sess_history. You could able to find lots of information
Reports
@?/rdbms/admin/ashrpt.sql
@?/rdbms/admin/ashrpti.sql
