Script categories

Wednesday, 16 November 2011

Report historical wait times per event for a specific Table

I always try and name my constraint and index names
with the same prefix as my table name and then suffix it with
a few characters to indicate what type of constraint or index it is.

For Example;

For a table such as TRADE_DEAL;

create table Trade_Deal
(Deal_ID      number          not null
,Deal_Type    varchar2(10)
,Deal_version integer
,Created      timestamp default current_timestamp
,Trade_Risk   integer
,GBP_Amnt     number          not null
,USD_Amnt     number);

The primary key would be named as follows;

alter table trade_deal 
add constraint TRADE_DEAL_PK primary key (deal_id);

A check constraint might be named as follows;

alter table trade_deal add constraint
TRADE_DEAL_CK_RISK check (trade_risk in (1,2,3,4,5,6,7,8,9));

The main reason for naming my objects using the above convention
is that it simplifies running queries to asses performance
stats on a particular set of related objects.

For example, the AWR query below will output the time waited
per wait event for a set of related table and indexes.

First set the start and end snapshot ID as variables before
attempting to run the query below. Use this script if you want;
http://bluefrog-oracle.blogspot.com/2011/11/set-start-and-end-snapshot-id-for-awr.html

variable p_Schema varchar2(30);
exec :p_Schema := sys_context('USERENV','CURRENT_SCHEMA');

col Object_Name format a32
col Object_Type format a10
col Event_Name  format a40
select
  /*+ all_rows */
   ds.Instance        as Instance_Number
  ,ao.Object_Name     as Object_Name
  ,ao.Object_Type     as Object_Type
  ,ds.Event           as Event_Name
  ,sum(ds.Cnt)        as Event_Wait_Cnt
  ,sum(Time_Waited)   as Time_Waited
from
  (
   select    /*+ all_rows */
             au.Username
            ,e.Name          as Event
            ,count(*)        as Cnt
            ,sum(Wait_Time)  as Time_Waited
            ,da.Current_Obj# as Object_ID
            ,Instance_Number as Instance
   from
             dba_hist_active_sess_history da
            ,v$Event_Name e
            ,all_users   au
   where     da.Event_ID = e.Event_ID
   and       da.User_ID  = au.User_ID
   and       da.Snap_ID  between :p_Start_Snap_ID and :p_End_Snap_ID
   and       au.Username = :p_Schema
   group by  au.Username
            ,e.Name
            ,da.Current_Obj#
            ,da.Instance_Number
   order by 3 desc
  )                     ds
  ,dba_Objects          ao
where
          ao.Object_ID          = ds.Object_ID
and       ao.Object_name        like  '&Object%'
and       ao.Object_Type        in ('TABLE','INDEX')
-- Exclude SQL Client type of waits
and       ds.Event              not like 'SQL*Net%'
group by  ao.Object_Name
         ,ao.Object_Type
         ,ds.Event
         ,ds.Instance
order by 5 desc, 4
/

No comments:

Post a Comment