Scribd
Upload
554 views54 pages

003.ASH Report Analysis - How To Read ASH Report

The document discusses how to generate and interpret ASH (Active Session History) reports. It explains that ASH reports provide sampled, detailed activity data about active database sessions over a period of time. It describes how to generate an ASH report using scripts from the Oracle home directory and how ASH data is stored both in memory and on disk in the DBA_HIST_ACTIVE_SESS_HISTORY view. It also gives examples of how to read and analyze the data in an ASH report.

Uploaded by

Irfan Ahmad
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
554 views54 pages

003.ASH Report Analysis - How To Read ASH Report

The document discusses how to generate and interpret ASH (Active Session History) reports. It explains that ASH reports provide sampled, detailed activity data about active database sessions over a period of time. It describes how to generate an ASH report using scripts from the Oracle home directory and how ASH data is stored both in memory and on disk in the DBA_HIST_ACTIVE_SESS_HISTORY view. It also gives examples of how to read and analyze the data in an ASH report.

Uploaded by

Irfan Ahmad
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
You are on page 1/ 54

ASH Report - How different than AWR report

How to generate ASH Report?


How to read and interpret ASH report?

Mallik

You can search mallik034 on YouTube and Google


AWR How to identify bad SQL?
Where to look at AWR report or ASH report?

AWR:
--- Gives you only TOP SQLs
--- Other SQLs are executed at the same time which will not be captured in AWR

Example:
--- 1 standard program used to run in 10 second which taking 1 mins.

--- As per Application or business perspective SLA is 10 sec, At any cost this standard SQL has to
complete in 10 sec

--- Since this executed within 1 min so it never appeared in AWR report.

Example is Banking transactions

If application team is coming up and complaining out these type of issues and if you want to have a
look on these SQLs and issue best way to look at them is ASH report (Active Sessions History)

You can search mallik034 on YouTube and Google


ASH

AWR

V$SESSIONS – Dynamic Perf View


Composition with AWR --- Info about what sessions are doing at
--- AWR has timeline and in which we have taken my DB at a given time (Any/All sessions)
different snapshots

--- We will give begin snap and end snap while


generating the report Active In Active

--- We will try to find out what happened at my DB 09 AM - Session 1 doing update
during that time period 10 AM - Session 2 doing delete
11 AM - Session 3 doing select
--- But which will give me only TOP Issues 11:20 AM – Session 1 is Idel
Application team are coming and complaining at 5:00 PM that morning 10 AM my query was not running properly.

You can search mallik034 on YouTube and Google


Application team are coming and complaining at 5:00 PM that morning 10 AM my query was not running properly.

Option1 - You can not check V$SESSION


Option2 - Next option is you may generate AWR and check for the query but no guarantee you may get that SQL
Option3 – ASH comes into picture

V$ACTIVE_SESSION_HISTORY --- In memory view

V$SESSIONS – Dynamic Perf View

Every 1 Sec Activ In Active


e

V$ACTIVE_SESSION_HISTORY 1
2
Since this In memory view, It will be having data about last 60 mins
1 in 10 sec only Next question comes what happens beyond 60 mins?

DBA_HIST_ACTIVE_SESS_HISTORY

You can search mallik034 on YouTube and Google


Application team are coming and complaining at 5:00 PM
that morning 10 AM my query was not running properly.

Question: Is that query took more than 10 seconds?


If YES then you get it from DBA_HIST_ACTIVE_SESS_HISTORY
Else you will not find it
Application team are coming and complaining at 5:00 PM
that 30 mins back my query was not running properly.

you get it from V$ACTIVE_SESSION_HISTORY

You can search mallik034 on YouTube and Google


What is ASH? What is ASH?
Sampled, detailed, non-intrusive activity data Samples ‘Active’ sessions every second
–Like doing “select * from v$session_wait” w/o SQL
•Part of Oracle 10g
•On by default Writes into ASH buffer in SGA memory
•Licensed as part of the Diagnostic pack –2MB per CPU, ≤5% shared_pool, 2%sga_target

‘Active’ == Non-idle sessions


–Waiting on non-idle event or on CPU

Data volume based on activity


–10,000 sessions => 200 active sessions
–Design goal: one hour activity held in memory

You can search mallik034 on YouTube and Google


SQL> select * from v$sgastat where name like 'ASH buffers';
POOL NAME BYTES
------------------------------------------------
shared pool ASH buffers 65011712

SQL> select min(sample_time), max(sample_time) from v$active_session_history;


MIN(SAMPLE_TIME) MAX(SAMPLE_TIME)
------------------------------ ------------------------------------
20-FEB-05 10.31.38.615 PM 21-FEB-05 02.39.28.950 AM

You can search mallik034 on YouTube and Google


ASH on Disk
Captured as part of AWR snapshots
–DBA_HIST_ACTIVE_SESS_HISTORY

Takes samples from in


-memory ASH–10 second samples

On-demand flush if required


–Whenever circular buffer is 66% full
–No missed data

Seven days history by default


–Table is partitioned for easy purging

You can search mallik034 on YouTube and Google


ASH on Disk

You can search mallik034 on YouTube and Google


ASH on Disk
Session

Waits
–Event, P1, P2, P3

SQL
–Sql_id, Opcode, Plan_hash

Objects
–Object#, File#, Block#

Application
–Program, Module, Action, Client_id, Service

Combinations of the above, CUBEs, ROLLUPs, …

You can search mallik034 on YouTube and Google


You can search mallik034 on YouTube and Google
How to generate the ASH report?

To generate an ASH report on the local database instance using the command-line interface:
@$ORACLE_HOME/rdbms/admin/ashrpt.sql

To generate an ASH report on a specific database instance using the command-line interface:
@$ORACLE_HOME/rdbms/admin/ashrpti.sql

Generating an ASH Report for Oracle RAC


@$ORACLE_HOME/rdbms/admin/ashrpti.sql

https://docs.oracle.com/database/121/TGDBA/sampling.htm#TGDBA279

You can search mallik034 on YouTube and Google


ASH report

You can search mallik034 on YouTube and Google


ASH report

You can search mallik034 on YouTube and Google


ASH report

You can search mallik034 on YouTube and Google


ASH report

You can search mallik034 on YouTube and Google


You can search mallik034 on YouTube and Google
You can search mallik034 on YouTube and Google
You can search mallik034 on YouTube and Google
You can search mallik034 on YouTube and Google
You can search mallik034 on YouTube and Google
You can search mallik034 on YouTube and Google
You can search mallik034 on YouTube and Google
You can search mallik034 on YouTube and Google
You can search mallik034 on YouTube and Google
You can search mallik034 on YouTube and Google
You can search mallik034 on YouTube and Google
You can search mallik034 on YouTube and Google
You can search mallik034 on YouTube and Google
You can search mallik034 on YouTube and Google
You can search mallik034 on YouTube and Google
You can search mallik034 on YouTube and Google
You can search mallik034 on YouTube and Google
You can search mallik034 on YouTube and Google
You can search mallik034 on YouTube and Google
How to generate the ASH report?

To generate an ASH report on the local database instance using the command-line interface:
@$ORACLE_HOME/rdbms/admin/ashrpt.sql

To generate an ASH report on a specific database instance using the command-line interface:
@$ORACLE_HOME/rdbms/admin/ashrpti.sql

Generating an ASH Report for Oracle RAC


@$ORACLE_HOME/rdbms/admin/ashrpti.sql

https://docs.oracle.com/database/121/TGDBA/sampling.htm#TGDBA279

You can search mallik034 on YouTube and Google


ASH report

You can search mallik034 on YouTube and Google


ASH report

You can search mallik034 on YouTube and Google


ASH report

You can search mallik034 on YouTube and Google


ASH report

You can search mallik034 on YouTube and Google


dba_hist_active_sess_history
ACTION PLAN
-------------------
1)Create table test_a as
select * from dba_hist_active_sess_history
where sample_time>=to_date('2016-3-26 00:05:00','yyyy-mm-dd
hh24:mi:ss')
and sample_time<=to_date('2016-3-26 00:19:00','yyyy-mm-dd hh24:mi:ss');

2)Please export the table test_a


You can expdp the tables with these steps:
…

You can search mallik034 on YouTube and Google


dba_hist_active_sess_history
select
session_id,instance_number,session_state,BLOCKING_SESSION,BLOCKING_INST
_ID,program,action,sql_id,event,p1,p2,p3,xid,current_obj#,current_file#,current_block
#,in_parse,in_hard_parse
from (select * from oracle.test_a
where to_char(sample_time,'yyyy-mm-dd hh24:mi:ss')='2016-03-26 00:05:28')
start with session_id=6339
connect by prior SESSION_ID=BLOCKING_SESSION;

6339 1 WAITING gtm@ns-acctmgr-02 (TNS V1-V3) 585drf2upjmt4 gc cr multi block request 163 194409 1
09B0000A00821E17 423 1 119155 N N
《 ==========blocking session 6339

520 1 WAITING 6339 1 JDBC Thin Client row cache lock 8 0 3 090A001D00A09584 275907 375 1141177 N N
546 1 WAITING 6339 1 JDBC Thin Client row cache lock 8 0 3 09DC000E0062B0F3 237852 124 2548434 N N
1425 1 WAITING 6339 1 JDBC Thin Client row cache lock 8 0 3 0918000200A3F6BF 275907 375 1389959 N N
1546 1 WAITING 6339 1 JDBC Thin Client row cache lock 8 0 3 0941000700ABE91A 275907 375 1130175 N N
1569 1 WAITING 6339 1 JDBC Thin Client row cache lock 8 0 3 09DE001100601B10 275907 308 3226885 N N
1730 1 WAITING 6339 1 JDBC Thin Client row cache lock 8 0 3 09F100150061865C -1 0 0 N N
…

You can search mallik034 on YouTube and Google


dba_hist_active_sess_history
select to_char(sample_time,'yyyy-mm-dd
hh24:mi:ss'),sql_id,sql_opname,top_level_sql_id,to_char(sql_exec_start,'yyyy-mm-dd
hh24:mi:ss'),event from oracle.test_a
where session_id=6339
order by to_char(sample_time,'yyyy-mm-dd hh24:mi:ss');

2016-03-26 00:05:07 585drf2upjmt4 ALTER TABLE 3766qwdbcbx9u 2016-03-26 00:05:04 db file scattered read

<==================ALTER TABLE
=====Top level sql id 3766qwdbcbx9u

You can search mallik034 on YouTube and Google


You can search mallik034 on YouTube and Google

You might also like