ASH Analysis

-
===============================================
ANALYSING  v$active_session_history
===============================================
start time : 28/MAR/2015 12:47:21
end time : 28/MAR/2015 12:50:50


ash.ash_table_bkp has contents of v$active_session_history


-----------------------------------------------------------------------------------------------
STEP 1 : Confirm we have V$active_session_history data covering issue time
-----------------------------------------------------------------------------------------------
sqlplus / as sysdba
ALTER SESSION SET nls_date_format="DD/MON/YYYY HH24:MI:SS";
alter session set nls_timestamp_format="DD/MON/YYYY HH24:MI:SS";


SQL> select min(sample_time),max(sample_time) from ash.ash_table_bkp;


-----------------------------------------------------------------------------------------------
STEP 2 : GATHER WAITCLASS , WAIT EVENT details
-----------------------------------------------------------------------------------------------
set lines 170 pages 100
select wait_class_id, wait_class, sum(time_waited) "Total Time" from ash.ash_table_bkp where sample_time between
    to_date('&&start_time','DD/MON/YYYY HH24:MI:SS')
    and
    to_date('&&end_time','DD/MON/YYYY HH24:MI:SS')
and session_state='WAITING'
group by wait_class_id, wait_class order by 3;
set lines 170 pages 100
select EVENT,sum(time_waited) from ash.ash_table_bkp where sample_time between
    to_date('&&start_time','DD/MON/YYYY HH24:MI:SS')
    and
    to_date('&&end_time','DD/MON/YYYY HH24:MI:SS')
and session_state='WAITING'
group by event ORDER BY 2 DESC ;


-----------------------------------------------------------------------------------------------
STEP 3 : SQL's responsible for WAIT EVENT
-----------------------------------------------------------------------------------------------
select sql_id, count(*) cnt from ash.ash_table_bkp where sample_time between
   to_date('&&start_time','DD/MON/YYYY HH24:MI:SS')
    and
    to_date('&&end_time','DD/MON/YYYY HH24:MI:SS')
and event='&event' group by sql_id order by 2;


select sql_id,sql_text from v$sql where sql_id='&sql_id';
-----------------------------------------------------------------------------------------------
STEP 4 : Objects corresponding to WAIT EVENT
-----------------------------------------------------------------------------------------------
select CURRENT_OBJ#,CURRENT_FILE#,CURRENT_BLOCK#,CURRENT_ROW#,count(*) cnt from ash.ash_table_bkp where sample_time between
   to_date('&&start_time','DD/MON/YYYY HH24:MI:SS')
    and
    to_date('&&end_time','DD/MON/YYYY HH24:MI:SS')
and event='&event' group by CURRENT_OBJ#,CURRENT_FILE#,CURRENT_BLOCK#,CURRENT_ROW# order by 2;
select owner,object_name,object_type from dba_objects where object_id='&object_id';


-----------------------------------------------------------------------------------------------
STEP 5 : Sessions corresponding to WAIT event
-----------------------------------------------------------------------------------------------
select SESSION_ID,SESSION_SERIAL#,SESSION_TYPE,USER_ID,SQL_ID,count(*) cnt from ash.ash_table_bkp where sample_time between
   to_date('&&start_time','DD/MON/YYYY HH24:MI:SS')
    and
    to_date('&&end_time','DD/MON/YYYY HH24:MI:SS')
and event='&event' group by SESSION_ID,SESSION_SERIAL#,SESSION_TYPE,USER_ID,SQL_ID order by 1,2;
Select user_id,username from dba_users where user_id='&user_id';


-----------------------------------------------------------------------------------------------
STEP 6 : BLOCKING SESSION DETAILS
-----------------------------------------------------------------------------------------------
SELECT BLOCKING_INST_ID,BLOCKING_SESSION,BLOCKING_SESSION_SERIAL#,BLOCKING_SESSION_STATUS,COUNT(*) cnt from ash.ash_table_bkp where sample_time between
   to_date('&&start_time','DD/MON/YYYY HH24:MI:SS')
    and
    to_date('&&end_time','DD/MON/YYYY HH24:MI:SS')
and event='&event' group by BLOCKING_INST_ID,BLOCKING_SESSION,BLOCKING_SESSION_SERIAL#,BLOCKING_SESSION_STATUS order by 5;


select distinct BLOCKING_INST_ID,BLOCKING_SESSION,BLOCKING_SESSION_SERIAL#,BLOCKING_SESSION_STATUS from ash.ash_table_bkp where sample_time between
   to_date('&&start_time','DD/MON/YYYY HH24:MI:SS')
    and
    to_date('&&end_time','DD/MON/YYYY HH24:MI:SS')
and session_id=&session_id and SESSION_SERIAL#=&serial;
-----------------------------------------------------------------------------------------------
STEP 7 : FINDING MORE DETAILS ABOUT SESSION
-----------------------------------------------------------------------------------------------
select sample_time,event,sql_id,PROGRAM,MODULE,wait_time,time_waited,blocking_session,BLOCKING_SESSION_SERIAL#,BLOCKING_INST_ID
from ash.ash_table_bkp
where session_id=&session_id
 and session_serial#=&session_serial
and sample_time between
   to_date('&&start_time','DD/MON/YYYY HH24:MI:SS')
    and
    to_date('&&end_time','DD/MON/YYYY HH24:MI:SS')
order by sample_time;












++++++++++++++++++++++++++++++++++++


SQL> set lines 170 pages 100
select wait_class_id, wait_class, sum(time_waited) "Total Time" from ash.ash_table_bkp where sample_time between
    to_date('&&start_time','DD/MON/YYYY HH24:MI:SS')
    and
    to_date('&&end_time','DD/MON/YYYY HH24:MI:SS')
        and session_state='WAITING'
        group by wait_class_id, wait_class order by 3;SQL>   2    3    4    5    6
Enter value for start_time: 28/MAR/2015 12:47:21
old   2:     to_date('&&start_time','DD/MON/YYYY HH24:MI:SS')
new   2:     to_date('28/MAR/2015 12:47:21','DD/MON/YYYY HH24:MI:SS')
Enter value for end_time: 28/MAR/2015 12:50:50
old   4:     to_date('&&end_time','DD/MON/YYYY HH24:MI:SS')
new   4:     to_date('28/MAR/2015 12:50:50','DD/MON/YYYY HH24:MI:SS')


WAIT_CLASS_ID WAIT_CLASS                                                       Total Time
------------- ---------------------------------------------------------------- ----------
   3875070507 Concurrency                                                         3312687
   3290255840 Configuration                                                       3890907
   4108307767 System I/O                                                          5238966
   4217450380 Application                                                       129973310


 
SQL> set lines 170 pages 100
select EVENT,sum(time_waited) from ash.ash_table_bkp where sample_time between
    to_date('&&start_time','DD/MON/YYYY HH24:MI:SS')
    and
    to_date('&&end_time','DD/MON/YYYY HH24:MI:SS')
        and session_state='WAITING'
        group by event ORDER BY 2 DESC ;        SQL>   2    3    4    5    6
old   2:     to_date('&&start_time','DD/MON/YYYY HH24:MI:SS')
new   2:     to_date('28/MAR/2015 12:47:21','DD/MON/YYYY HH24:MI:SS')
old   4:     to_date('&&end_time','DD/MON/YYYY HH24:MI:SS')
new   4:     to_date('28/MAR/2015 12:50:50','DD/MON/YYYY HH24:MI:SS')


EVENT                                                            SUM(TIME_WAITED)
---------------------------------------------------------------- ----------------
enq: TX - row lock contention                                           129973310
log file parallel write                                                   4697643
log buffer space                                                          3617319
os thread startup                                                         3312687
control file parallel write                                                541323
log file switch completion                                                 273588


6 rows selected.




SQL> select sql_id, count(*) cnt from ash.ash_table_bkp where sample_time between
   to_date('&&start_time','DD/MON/YYYY HH24:MI:SS')
    and
    to_date('&&end_time','DD/MON/YYYY HH24:MI:SS')
        and event='&event' group by sql_id order by 2;  2    3    4    5
old   2:    to_date('&&start_time','DD/MON/YYYY HH24:MI:SS')
new   2:    to_date('28/MAR/2015 12:47:21','DD/MON/YYYY HH24:MI:SS')
old   4:     to_date('&&end_time','DD/MON/YYYY HH24:MI:SS')
new   4:     to_date('28/MAR/2015 12:50:50','DD/MON/YYYY HH24:MI:SS')
Enter value for event: enq: TX - row lock contention
old   5:        and event='&event' group by sql_id order by 2
new   5:        and event='enq: TX - row lock contention' group by sql_id order by 2


SQL_ID               CNT
------------- ----------
2prqxvzqv3srg         50
bm7twt5vhwcms         79




SQL> select sql_id,sql_text from v$sql where sql_id='&sql_id';
Enter value for sql_id: 2prqxvzqv3srg
old   1: select sql_id,sql_text from v$sql where sql_id='&sql_id'
new   1: select sql_id,sql_text from v$sql where sql_id='2prqxvzqv3srg'


SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2prqxvzqv3srg
update t1 set name='rakesh' where userid=2




SQL> /
Enter value for sql_id: bm7twt5vhwcms
old   1: select sql_id,sql_text from v$sql where sql_id='&sql_id'
new   1: select sql_id,sql_text from v$sql where sql_id='bm7twt5vhwcms'


SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
bm7twt5vhwcms
update t1 set name='Suresh' where userid=1




SQL> select CURRENT_OBJ#,CURRENT_FILE#,CURRENT_BLOCK#,CURRENT_ROW#,count(*) cnt from ash.ash_table_bkp where sample_time between
   to_date('&&start_time','DD/MON/YYYY HH24:MI:SS')
    and
    to_date('&&end_time','DD/MON/YYYY HH24:MI:SS')
        and event='&event' group by CURRENT_OBJ#,CURRENT_FILE#,CURRENT_BLOCK#,CURRENT_ROW# order by 2;  2    3    4    5
old   2:    to_date('&&start_time','DD/MON/YYYY HH24:MI:SS')
new   2:    to_date('28/MAR/2015 12:47:21','DD/MON/YYYY HH24:MI:SS')
old   4:     to_date('&&end_time','DD/MON/YYYY HH24:MI:SS')
new   4:     to_date('28/MAR/2015 12:50:50','DD/MON/YYYY HH24:MI:SS')
Enter value for event: enq: TX - row lock contention
old   5:        and event='&event' group by CURRENT_OBJ#,CURRENT_FILE#,CURRENT_BLOCK#,CURRENT_ROW# order by 2
new   5:        and event='enq: TX - row lock contention' group by CURRENT_OBJ#,CURRENT_FILE#,CURRENT_BLOCK#,CURRENT_ROW# order by 2


CURRENT_OBJ# CURRENT_FILE# CURRENT_BLOCK# CURRENT_ROW#        CNT
------------ ------------- -------------- ------------ ----------
       76883             4            527            0         79
       76883             4            527            1         50




SQL> select owner,object_name,object_type from dba_objects where object_id='&object_id';
Enter value for object_id: 76883
old   1: select owner,object_name,object_type from dba_objects where object_id='&object_id'
new   1: select owner,object_name,object_type from dba_objects where object_id='76883'


OWNER                          OBJECT_NAME
------------------------------ --------------------------------------------------------------------------------------------------------------------------------
OBJECT_TYPE
-------------------
ASH                            T1
TABLE




SQL> select SESSION_ID,SESSION_SERIAL#,SESSION_TYPE,USER_ID,SQL_ID,count(*) cnt from ash.ash_table_bkp where sample_time between
   to_date('&&start_time','DD/MON/YYYY HH24:MI:SS')
    and
    to_date('&&end_time','DD/MON/YYYY HH24:MI:SS')
        and event='&event' group by SESSION_ID,SESSION_SERIAL#,SESSION_TYPE,USER_ID,SQL_ID order by 1,2;  2    3    4    5
old   2:    to_date('&&start_time','DD/MON/YYYY HH24:MI:SS')
new   2:    to_date('28/MAR/2015 12:47:21','DD/MON/YYYY HH24:MI:SS')
old   4:     to_date('&&end_time','DD/MON/YYYY HH24:MI:SS')
new   4:     to_date('28/MAR/2015 12:50:50','DD/MON/YYYY HH24:MI:SS')
Enter value for event: enq: TX - row lock contention
old   5:        and event='&event' group by SESSION_ID,SESSION_SERIAL#,SESSION_TYPE,USER_ID,SQL_ID order by 1,2
new   5:        and event='enq: TX - row lock contention' group by SESSION_ID,SESSION_SERIAL#,SESSION_TYPE,USER_ID,SQL_ID order by 1,2


SESSION_ID SESSION_SERIAL# SESSION_TY    USER_ID SQL_ID               CNT
---------- --------------- ---------- ---------- ------------- ----------
        35               7 FOREGROUND         97 2prqxvzqv3srg         50
        36               1 FOREGROUND         97 bm7twt5vhwcms         79




SQL> Select user_id,username from dba_users where user_id='&user_id';
Enter value for user_id: 97
old   1: Select user_id,username from dba_users where user_id='&user_id'
new   1: Select user_id,username from dba_users where user_id='97'


   USER_ID USERNAME
---------- ------------------------------
        97 ASH




SQL> SELECT BLOCKING_INST_ID,BLOCKING_SESSION,BLOCKING_SESSION_SERIAL#,BLOCKING_SESSION_STATUS,COUNT(*) cnt from ash.ash_table_bkp where sample_time between
   to_date('&&start_time','DD/MON/YYYY HH24:MI:SS')
    and
    to_date('&&end_time','DD/MON/YYYY HH24:MI:SS')
        and event='&event' group by BLOCKING_INST_ID,BLOCKING_SESSION,BLOCKING_SESSION_SERIAL#,BLOCKING_SESSION_STATUS order by 5;  2    3    4    5
old   2:    to_date('&&start_time','DD/MON/YYYY HH24:MI:SS')
new   2:    to_date('28/MAR/2015 12:47:21','DD/MON/YYYY HH24:MI:SS')
old   4:     to_date('&&end_time','DD/MON/YYYY HH24:MI:SS')
new   4:     to_date('28/MAR/2015 12:50:50','DD/MON/YYYY HH24:MI:SS')
Enter value for event: enq: TX - row lock contention
old   5:        and event='&event' group by BLOCKING_INST_ID,BLOCKING_SESSION,BLOCKING_SESSION_SERIAL#,BLOCKING_SESSION_STATUS order by 5
new   5:        and event='enq: TX - row lock contention' group by BLOCKING_INST_ID,BLOCKING_SESSION,BLOCKING_SESSION_SERIAL#,BLOCKING_SESSION_STATUS order by 5


BLOCKING_INST_ID BLOCKING_SESSION BLOCKING_SESSION_SERIAL# BLOCKING_SE        CNT
---------------- ---------------- ------------------------ ----------- ----------
               1               33                       19 VALID              129




 
SQL> select distinct BLOCKING_INST_ID,BLOCKING_SESSION,BLOCKING_SESSION_SERIAL#,BLOCKING_SESSION_STATUS from ash.ash_table_bkp where sample_time between
   to_date('&&start_time','DD/MON/YYYY HH24:MI:SS')
    and
    to_date('&&end_time','DD/MON/YYYY HH24:MI:SS')
        and session_id=&session_id and SESSION_SERIAL#=&serial;  2    3    4    5
old   2:    to_date('&&start_time','DD/MON/YYYY HH24:MI:SS')
new   2:    to_date('28/MAR/2015 12:47:21','DD/MON/YYYY HH24:MI:SS')
old   4:     to_date('&&end_time','DD/MON/YYYY HH24:MI:SS')
new   4:     to_date('28/MAR/2015 12:50:50','DD/MON/YYYY HH24:MI:SS')
Enter value for session_id: 35
Enter value for serial: 7
old   5:        and session_id=&session_id and SESSION_SERIAL#=&serial
new   5:        and session_id=35 and SESSION_SERIAL#=7


BLOCKING_INST_ID BLOCKING_SESSION BLOCKING_SESSION_SERIAL# BLOCKING_SE
---------------- ---------------- ------------------------ -----------
               1               33                       19 VALID


SQL> /
old   2:    to_date('&&start_time','DD/MON/YYYY HH24:MI:SS')
new   2:    to_date('28/MAR/2015 12:47:21','DD/MON/YYYY HH24:MI:SS')
old   4:     to_date('&&end_time','DD/MON/YYYY HH24:MI:SS')
new   4:     to_date('28/MAR/2015 12:50:50','DD/MON/YYYY HH24:MI:SS')
Enter value for session_id: 36
Enter value for serial: 1
old   5:        and session_id=&session_id and SESSION_SERIAL#=&serial
new   5:        and session_id=36 and SESSION_SERIAL#=1


BLOCKING_INST_ID BLOCKING_SESSION BLOCKING_SESSION_SERIAL# BLOCKING_SE
---------------- ---------------- ------------------------ -----------
               1               33                       19 VALID


SQL> select sample_time,event,sql_id,PROGRAM,MODULE,wait_time,time_waited,blocking_session,BLOCKING_SESSION_SERIAL#,BLOCKING_INST_ID
from ash.ash_table_bkp
where session_id=&session_id
 and session_serial#=&session_serial
and sample_time between
   to_date('&&start_time','DD/MON/YYYY HH24:MI:SS')
    and
    to_date('&&end_time','DD/MON/YYYY HH24:MI:SS')
        order by sample_time;  2    3    4    5    6    7    8    9
Enter value for session_id: 33
old   3: where session_id=&session_id
new   3: where session_id=33
Enter value for session_serial: 19
old   4:  and session_serial#=&session_serial
new   4:  and session_serial#=19
old   6:    to_date('&&start_time','DD/MON/YYYY HH24:MI:SS')
new   6:    to_date('28/MAR/2015 12:47:21','DD/MON/YYYY HH24:MI:SS')
old   8:     to_date('&&end_time','DD/MON/YYYY HH24:MI:SS')
new   8:     to_date('28/MAR/2015 12:50:50','DD/MON/YYYY HH24:MI:SS')

Comments

Post a Comment

Popular posts from this blog

How to make CRS and ASM not to restart after server reboot

-
I have noticed that server rebooting is taking lot of time on a VM machine for every reboot, so I have decided to auto start of the crs and ASM service During this phase I have noticed an attribute called auto_start when I try to execute the below command crsctl stat res -p This AUTO_START attribute is there for several resources like ora.ASM ,ora.crs,ora.DATA_DG it has got 2 options restore(0) , never(2) and always (1) always (1)  Causes the resource to restart when the node restarts regardless of the resource’s state when the node stopped -restore (0)  Does not start the resource at restart time if it was in an offline state, such as STATE=OFFLINE, TARGET=OFFLINE, when the node stopped. The resource is restored to its state when the node went down. The resource is started only if it was online before and not otherwise. -never (2)  Oracle Clusterware never restarts the resource regardless of the resource’s state when the node is stopped. So I have dec...
Read more

How to repair ASM disk header

-
Image
What is ASM disk header corruption? ASM disk header corruption is a loss or damage to the disk header which contains the metadata essential for the operation and availability of an ASM disk group in Oracle. Imagine that you have an ASM disk corruption in any of the ASM diskgroups that have critical business data and the diskgroup has been configured as "EXTERNAL" redundancy, meaning that if one disk is inaccessible the whole diskgroup will be unavailable. Now what? Is your only option to restore it from backup by recreating the diskgroup or are there any other faster ways to recover the data? Here you need to find out whether it’s a complete loss of data in the ASM disk or is it just a header corruption. Hopefully for you, this is just a header corruption. Because, yes, there is a way out!  Below find the steps to restore from the ASM header corruption. Most of the data in the ASM disk header is of interest to that disk only. However, some information in the ASM disk header ...
Read more

Understanding Terraform

-
 Terraform is a tool used  for building, changing, and versioning infrastructure safely and efficiently. A Terraform can manage multiple cloud services using a provider plugin  Terraform work flow? Terraform work flow will be comprised of validate -> plan -> apply and finally destroy. what is the important file of terraform which records the actions of Terraform apply? For every Terraform execution the action will be recorded in a file called Terraform state file .  The terraform init command is used to initialise a working directory containing Terraform configuration files. During init, the configuration is searched for module blocks, and the source code for referenced modules is retrieved from the locations given in  their source arguments. The terraform plan command is used to create an execution plan. It will not modify things in infrastructure. The terraform apply command is used to apply the changes required to reach the desired state of the configur...
Read more