Identify hot blocks using AWR

First set the start and end snapshot ID as variables. 

Use this script if you want; http://bluefrog-oracle.blogspot.com/2011/11/set-start-and-end-snapshot-id-for-awr.html

To get a general idea of where the majority of WAITS's occur,
run the following SQL statement to view the counts of wait classes
in descending order;
---------------------------------------------------------

select   d.wait_class_id                as Wait_Class_ID
        ,d.wait_class                   as Wait_Class
        ,count(*)                       as Cnt
from     dba_hist_active_sess_history   d
where    d.nap_id between :p_Start_Snap_ID and :p_End_Snap_ID
group by d.wait_class_id
        ,d.wait_class
order by 3 desc;


Next, List a breakdown of Events per Wait class identified
in the previous result set;
---------------------------------------------------------
select   d.wait_class_id                as Wait_Class_id
        ,d.wait_class                   as Wait_Class_Name
        ,e.Name                         as Event_Name
        ,count(*)                       as Cnt
from     dba_hist_active_sess_history   d
        ,v$Event_Name                   e
where    d.snap_id between :p_Start_Snap_ID and :p_End_Snap_ID
and      d.Event_ID                     = e.Event_ID
group by d.wait_class_id
        ,d.wait_class
        ,e.Name
order by 4 desc;


Now attempt to identify which users are responsible for the
waits (broken down per event type).

select   d.wait_class_id                as Wait_Class_ID
        ,d.wait_class                   as Wait_Class_Name
        ,u.Username                     as User_Name
        ,e.Name                         as Event_Name
        ,count(*)                       as Cnt
from     dba_hist_active_sess_history   d
        ,v$Event_Name                   e
        ,all_users                      u
where    d.snap_id between :p_Start_Snap_ID and :p_End_Snap_ID
and      d.Event_ID         = e.Event_ID
and      d.User_id          = u.User_ID
group by u.Username
        ,d.wait_class_id
        ,d.wait_class
        ,e.Name
order by 4, 5 desc;


You may want to exclude WAITS's for SYS and focus only on the
application specific schemas, in which case, add
the additional predicate "u.Username != 'SYS'"

Also, you would probably want to exclude SQL*Net related WAIT's,
therefore add "e.Name not like 'SQL*Net%'" as a predicate.

select   d.wait_class                   as Wait_Class_Name
        ,u.Username                     as User_Name
        ,e.Name                         as Event_Name
        ,count(*)                       as Cnt
from     dba_hist_active_sess_history   d
        ,v$Event_Name                   e
        ,all_users                      u
where    d.snap_id between  :p_Start_Snap_ID and :p_End_Snap_ID
and      d.Event_ID         =     e.Event_ID
and      d.User_id          =     u.User_ID
and      u.Username         !=    'SYS'
and      e.Name not         like  'SQL*Net%'
group by u.Username
        ,d.wait_class
        ,e.Name
order by 4, 5 desc;


To drill down on hot blocks, the WAIT class to target would be;
"User I/O".
Therefore add an additional predicate;
      "d.Wait_Class       like  'User I/O'".

select   d.wait_class                   as Wait_Class_Name
        ,u.Username                     as User_Name
        ,e.Name                         as Event_Name
        ,count(*)                       as Cnt
from     dba_hist_active_sess_history   d
        ,v$Event_Name                   e
        ,all_users                      u
where    d.snap_id between  :p_Start_Snap_ID and :p_End_Snap_ID
and      d.Event_ID         =     e.Event_ID
and      d.User_id          =     u.User_ID
and      u.Username         !=    'SYS'
and      e.Name not         like  'SQL*Net%'
and      d.Wait_Class       like  'User I/O'
group by u.Username
        ,d.wait_class
        ,e.Name
order by 1, 4 desc;


To drill down on which Objects the hot blocks occur in,
join to the all_Objects dictionary view.

Remove the Event Name from the grouping and select list
since we know longer want to focus on individual reasons
for the general "User I/O" (of which there are several).

select   d.wait_class                   as Wait_Class_Name
        ,u.Username                     as User_Name
        ,a.Object_Name                  as Object_Name
        ,count(*)                       as Cnt
from     dba_hist_active_sess_history   d
        ,all_users                      u
        ,all_objects                    a
        ,v$Event_Name                   e
where    d.snap_id between  :p_Start_Snap_ID and :p_End_Snap_ID
and      d.Event_ID         =     e.Event_ID
and      d.User_id          =     u.User_ID
and      u.Username         !=    'SYS'
and      e.Name not         like  'SQL*Net%'
and      d.Wait_Class       like  'User I/O'
and      d.Current_Obj#     =     a.Object_ID
and      a.Object_Type      =     'TABLE'
group by u.Username
        ,d.wait_class
        ,a.Object_Name
order by 4 desc,  2, 3;


And finally, to identify the most read ROWs relative to a Top-N
number passed in as a parameter.

select User_Name
      ,Object_Name
      ,Hot_Row_ID
      ,Cnt
from
  (
  select   u.Username                     as User_Name
          ,a.Object_Name                  as Object_Name
          ,dbms_rowid.rowid_create(1, d.Current_Obj#
                                     ,d.Current_File#
                                     ,d.Current_Block#
                                     ,d.Current_Row#) as Hot_Row_ID
          ,count(*)                       as Cnt
  from     dba_hist_active_sess_history   d
          ,all_users                      u
          ,all_objects                    a
          ,v$Event_Name                   e
  where    d.snap_id between  :p_Start_Snap_ID and :p_End_Snap_ID
  and      d.Event_ID         =     e.Event_ID
  and      d.User_id          =     u.User_ID
  and      u.Username         !=    'SYS'
  and      e.Name not         like  'SQL*Net%'
  and      d.Wait_Class       like  'User I/O'
  and      d.Current_Obj#     =     a.Object_ID
  and      a.Object_Type      =     'TABLE'
  group by u.Username
          ,a.Object_Name
          ,dbms_rowid.rowid_create(1, d.Current_Obj#
                                     ,d.Current_File#
                                     ,d.Current_Block#
                                     ,d.Current_Row#)
  order by 4 desc,  2
  )
where rownum < &top_n;

Set Start and End Snapshot ID for AWR scripts

variable p_Start_Snap_ID  number
variable p_End_Snap_ID    number

alter session set nls_date_format = 'dd/mm/yyyy hh24:mi'
/
alter session set nls_timestamp_format = 'dd/mm/yyyy hh24:mi'
/

col Begin_interval_time format a20
col End_interval_time   format a20

set linesize 400
set pagesize 4000

select Snap_ID, Instance_Number, begin_interval_time, end_interval_time
from (
      select snap_id
            ,Instance_Number
            ,begin_interval_time
            ,end_interval_time
      from   dba_hist_snapshot
      order by snap_id desc
     )
where rownum < &&Rownum_Limit
/

prompt Get Start and End snapshot ID
begin

  -- One must use an anonymous block when setting a Bind Variable
  select min(Snap_ID)
        ,max (Snap_ID)
  into  :p_Start_Snap_ID
       ,:p_End_Snap_ID
  from
  (select snap_id
         ,Instance_Number
         ,begin_interval_time
         ,end_interval_time
   from dba_hist_snapshot
   order by snap_id desc)
  where rownum < &&Rownum_Limit;

end;
/

Split PL/SQL VARCHAR2 variable into chunks of 4000 each

The procedure below splits a varchar2 variable, declared
in PL/SQL, into several 4000 varchar2 chunks, which can then
be inserted into a table, since the maximum length of a
varchar2 column is 4000 characters whereas the maximum
length of a PL/SQL varchar2 variable is 32767 characters.

First create a test table;
create table xdq (str varchar2(4000), chunk_id number);

Procedure to split the string into 4000 character chunks;
create or replace procedure xdq_Ins (p_Str in varchar2) as

STR_EMPTY   exception;
l_Clob      clob        := to_clob(p_Str);  -- convert to a clob
l_Len       pls_integer := dbms_lob.getlength(l_Clob); --str length
l_Chunk_Cnt pls_integer := ceil(l_Len/4000); -- number of chunks

begin
  if (l_Len < 1) then
    raise STR_EMPTY;
  end if;

  insert into xdq
   (str
   ,Chunk_ID)
   (
    select dbms_lob.substr(l_Clob, 4000, ((level-1) * 4000) + 1)
          ,level
    from   dual
    connect by level <= l_Chunk_Cnt
   );
  
  exception
    when STR_EMPTY then
      raise_application_error('No string to insert', -20088);
end xdq_Ins;
/


Now the test

SQL> begin
  2    xdq_Ins(rpad('x',5001,'x'));
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select length(str) str_len, chunk_id
  2  from xdq;

   STR_LEN   CHUNK_ID
---------- ----------
      4000          1
      1001          2

Using SYS_CONTEXT in a view

create or replace context App_Context using Set_Global_Var;

create or replace package Set_Global_Var as

  procedure set_ctx (p_Name       in varchar2
                    ,p_Value      in varchar2
                    ,p_ID         in number default null);

  -- Only use this procedure if you access attributes against a
  -- namespace globally across different schema sessions
  procedure Set_Id  (p_ID         in number);

end Set_Global_Var;
/

create or replace package body Set_Global_Var as

  -- object name represented by literal string is defined
  -- by the CREATE OR REPLACE CONTEXT DDL COMMAND
  v_CTX_NAME constant All_Context.Namespace%type := 'APP_CONTEXT';

  procedure Set_Id  (p_ID in number) as
    begin
      dbms_session.set_identifier(p_ID);
  end Set_ID;

  -- p_ID will only be offered when an identifier has
  -- been set using dbms_session.set_identifier so as to
  -- read an attribute from a namespace using the identifier
  procedure set_ctx (p_Name       in varchar2
                    ,p_Value      in varchar2
                    ,p_ID         in number default null) as
    begin

      dbms_session.set_context (namespace => v_CTX_NAME
                               ,attribute => p_Name
                               ,value     => p_Value
                           --,client_ID  => p_ID -- only set this
                           -- when .set_identifier is used
                               );

  end set_ctx;

end Set_Global_Var;
/

-- Test, which adds 2 years to the existing date
declare
  v_Value varchar2(10) := to_char(add_months(sysdate, 24), 'YYYY');
begin

  Set_Global_Var.set_ctx('DEFAULT_YEAR', v_Value);

  exception
    when others then
      dbms_output.put_line('Error : ' || sqlerrm);
end;
/

create or replace view Year_View as
-- The sys_context variable can occur anywhere in the view
select sys_context('APP_CONTEXT', 'DEFAULT_YEAR')  as Default_Year
      ,to_char(sysdate, 'YYYY')                    as Actual_Year
from dual
/

col Actual_year format 9999
col Default_year format 9999
-- Now test if the current year and the context year differ
select *
from Year_View
/

Identify excessively read blocks (hot blocks)

Use the following query to identify which files the hot blocks occur in;

select p1 "file#"
      ,p2 "block#"
      ,p3 "class#"
from   v$session_wait
where  event = 'read by other session'
/

Once the above query has been run several times over, a pattern may emerge which highlights a specific range of blocks.

Then run the following SQL statement to identify which object(s) the blocks are associated with;

select relative_fno
      ,owner
      ,segment_name
      ,segment_type
from   dba_extents

-- the file parameter comes from the result set above
where  file_id      = &file

-- the block parameter comes from the result set above
and    &block       between block_id and block_id + blocks - 1
/

INSERT ALL with single sequence

If you want to perform an insert into two or more tables using an INSERT ALL
and also set the primary key to a unique number for each table based
on a sequence, then the following is a method that could be used.

For example;

Create some test tables first;

create table t1 (a number primary key, b number);
create table t2 (a number primary key, b number);

Create the sequence next.

The trick is to create the sequence with an increment value equal to
the number of tables being inserted into. So if you have three tables
then the increment value would be three. In the example we have two tables
so the increment value is 2.

create sequence test_seq increment by 2 minvalue 0;

Now for the test;

insert all
when 1 = 1 then
  into t1 (a, b) values (test_seq.nextval, 1)
when 1 = 1 then
  into t2 (a, b) values (test_seq.nextval  - 1, 1)
select 1, 1 from dual;

SQL> select * from t1
  2  union all
  3  select * from t2
  4  order by 1;

         A          B
---------- ----------
         3          1
         4          1

One might want to wrap the insert in a procedure, since although the
trick works in versions 10g and 11g, future versions might not output
the same results. The INSERT ALL within the procedure would refer to
a number (which would be passed as a parameter) instead of the
sequence.

For example;

create or replace procedure p_Test (p_Number in number) as
  begin

    insert all
      when 1 = 1 then
      into t1 (a, b) values (p_Number, 1)
    when 1 = 1 then
      into t2 (a, b) values (p_Number  - 1, 1)
    select 1, 1 from dual;

end p_Test;
/

Now test using the procedure;

SQL> exec p_Test(test_seq.nextval);

PL/SQL procedure successfully completed.

SQL> select * from t1
  2  union all
  3  select * from t2
  4  order by 1;

         A          B
---------- ----------
         3          1
         4          1
         5          1
         6          1

View waits per Segment for a specific session

set timing on
col event format a30
col owner format a10
col segment_type format a20
col segment_name format a32
set linesize 400
set pagesize 2000

select distinct
       a.sql_id           as SQL_ID
      ,a.segment_name     as Segment_Name
      ,a.owner            as Owner
      ,a.segment_type     as Segment_Type
      ,a.event            as Event
      ,a.count            as Count
from
(
  select /*+ all_rows */
          de.Owner
         ,de.Segment_Name
         ,de.Segment_Type
         ,ash.Event
         ,ash.SQL_ID
         ,round(sum(ash.time_waited)/100000)    as Total_Time
         ,count(*)                              as Cnt
         ,round( (sum(ash.time_waited)/100000)
          / count(*), 2)                        as Avg_wait
  from
          v$active_session_history  ash
         ,dba_extents               de
  where
          ash.p1           = de.file_id
  and     ash.time_waited  > 0
  and     ash.p2           between de.block_id
                           and (de.block_id+de.blocks-1)
  and     ash.session_id   = &sid
  and     de.owner         not in ('SYS', 'SYSTEM')
  group by de.owner
          ,de.segment_name
          ,de.segment_type
          ,ash.event
          ,ash.sql_id
  order by 6 desc
) a
where rownum <= 30 -- Only look at worst performing
order by 6 desc, 4 asc
/

Useful way to improve peformance of bulk DELETE

delete from table1
where
rowid in ( select rowid
           from table1 group by rowid, col_id
           minus
           select max(rowid)
           from table1 group by col_id);
 

View SQL that run more than one hour from AWR views

select  sql_text
from    dba_hist_sqltext
where   sql_id in 
  (select   sql_id
   from     dba_hist_sqlstat
   where    snap_id between &Start_SnapID and &End_SnapID
   and      elapsed_time_total > 7200000000 -- number for 1 hour
)
/

What to query when SQL_ID is NULL

Sometimes the SQL_ID in V$SESSION will be NULL even though a long running SQL statement appears to be executing. Reason being that something other than the SQL is in fact executing. It could be PL/SQL code, latching activity in the shared pool (which is common for very long running SQL statements) or a COMMIT is currently underway.

One can query the V$ACTIVE_SESSION_HISTORY dictionary view to identify the current elapsed time per wait event for the session;

select    /*+ all_rows */
          count(*)                  as Wait_Event_Count
         ,sum(ah.time_waited)       as Total_Time_Waited
         ,ah.session_id             as SID
         ,ev.name                   as Event_Name
         ,sql_id                    as SQL_ID
         ,ah.session_type           as Session_Type
from     
          v$active_session_history  ah
         ,v$event_name              ev
where     ah.sample_time between sysdate - 30/(24*60) and sysdate
and       ah.event#      = ev.event#(+)
and       ah.session_id  = &sid
group by 
          ev.name
         ,ah.session_id
         ,ah.sql_id
         ,ah.session_type
/

The following statement indicates the sessions that have caused the most load as a percentage of all foreground sessions;

select ah.session_id
      ,ah.session_type
      ,nvl(ah.sql_id,'xx')                     as SQL_ID
      ,count(*)                                as Session_Cnt
      ,round(count(*)/sum(count(*)) over(), 2) as Percent_Load
from
       v$active_session_history ah
where
       ah.sample_time >to_date('31-OCT-11 11:40','dd-MON-yy hh24:mi')
and    ah.session_type='FOREGROUND'
group by
       ah.session_id
      ,ah.session_type
      ,ah.sql_id
order by count(*) desc

/

The following SQL shows information on the PL/SQL units, which might be relevant if PL/SQL is busy executing and the SQL_ID is null;


select    ah.session_id               as SID
         ,ah.session_state            as State
         ,event                       as Event_name
         ,ah.sql_id                   as SQL_ID
         ,ah.top_level_sql_id
         ,ah.plsql_entry_object_id
         ,ah.plsql_entry_subprogram_id
         ,ah.plsql_object_id
         ,ah.plsql_subprogram_id
         ,count(*)                    as Wait_Event_Count
from
          v$active_session_history    ah
where
          ah.sample_time     between sysdate - 60/(24*60) and sysdate
and       ah.session_state   in ('ON CPU', 'WAITING')
and       ah.session_id      = &sid
group by  ah.session_id
         ,ah.session_state
         ,ah.event
         ,ah.sql_id
         ,ah.top_level_sql_id
         ,ah.plsql_entry_object_id
         ,ah.plsql_entry_subprogram_id
         ,ah.plsql_object_id
         ,ah.plsql_subprogram_id
order by
          ah.session_id
/

The following SQL shows operations that are currently executing specific to a session, which will give you an indication of what the wait is for if the SQL_ID is null.


alter session set nls_date_format = 'dd/mm/yyyy hh24:mi';
col target format a25
col opname format a40
select sid
      ,opname
      ,target
      ,round(sofar/totalwork*100,2)   as percent_done
      ,start_time
      ,last_update_time
      ,time_remaining
from 
       v$session_longops
where
       sid = &sid
/