SPM and Profiles -Part2

Converting baselines into profiles

In my last post, we have discussed various workarounds to fix the execution plan of the query if its not picking baseline.

Of the given 3 methods, I was interested in the 3rd one. What got me interested is the idea if we can create profiles from  baselines. 

The next obvious question is “why you need to do so”.

The 2 reasons are

1. Baselines not getting used. In this case you can create a profile out of baseline and then impose oracle to use this profile.

2. In 11g, we have ADG (active standby database). Guess what!!! Baselines doesn’t work in ADG. So if you fix query on primary, it won’t pick the plan in standby. So you better create a profile.  Also you may need to bounce your standby in order for profile to get used (because of another bug).

Before I talk about script to convert baselines into profiles, lets step back and think whats is that thing which is common in baselines and profiles(if at all there is) and what is that thing which makes them different.

Both baselines and profiles store data in form of hints.

Only difference is in case of baselines oracle tries to regenerate the same plan using hints and if it can’t then it will not use the baseline.

However, in case of profile, oracle will use the hints to generate the execution plan which it will use. Therefore, there is no guarantee that oracle will land on same plan again and again and hence plan flip can occur.

I will soon write about hints and how oracle stores them for a given plan.

Anyway, coming back to our point, oracle stores the execution plan in form of hints. Therefore, for each plan it captures or generates, it must store them somewhere. The idea is to get those hints and create the profile.

planprofile.sql:-

Session1>select plan_name from dba_sql_plan_baselines where rownum < 2;

PLAN_NAME
------------------------------
SYS_SQL_PLAN_086da2eaeec7bdef

1 row selected.

Session1>@planprofile
enter sql_id to be fixed:- dgh672sad89j4
baseline plan name:- SYS_SQL_PLAN_086da2eaeec7bdef
enter bad plan hash value to be tuned :- 12345678
'===================OUTPUT========================='

declare
ar_profile_hints sys.sqlprof_attr;
begin
 ar_profile_hints := sys.sqlprof_attr(
 'BEGIN_OUTLINE_DATA',
 'FULL(@"SEL$4" "MRH"@"SEL$4")',
 'NO_ACCESS(@"SEL$3" "from$_subquery$_007"@"SEL$3")',
 'FULL(@"SEL$6" "MRH"@"SEL$6")',
 'NO_ACCESS(@"SEL$5" "from$_subquery$_004"@"SEL$5")',
 'PQ_DISTRIBUTE(@"SEL$F5BB74E1" "RMS"@"SEL$2" NONE BROADCAST)',
 'USE_NL(@"SEL$F5BB74E1" "RMS"@"SEL$2")',
 'LEADING(@"SEL$F5BB74E1" "H"@"SEL$1" "RMS"@"SEL$2")',
 'INDEX_RS_ASC(@"SEL$F5BB74E1" "RMS"@"SEL$2" ("METRICS_SUMMARY95"."MVIEW_NAME" "METRICS_SUMMARY95"."OWNER"))',
 'NO_ACCESS(@"SEL$F5BB74E1" "H"@"SEL$1")',
 'PQ_DISTRIBUTE(@"MRG$1" "H"@"MRG$1" BROADCAST NONE)',
 'PQ_DISTRIBUTE(@"MRG$1" "RMS"@"MRG$1" NONE BROADCAST)',
 'USE_MERGE_CARTESIAN(@"MRG$1" "H"@"MRG$1")',
 'USE_MERGE_CARTESIAN(@"MRG$1" "RMS"@"MRG$1")',
 'LEADING(@"MRG$1" "from$_subquery$_010"@"MRG$1" "RMS"@"MRG$1" "H"@"MRG$1")',
 'NO_ACCESS(@"MRG$1" "H"@"MRG$1")',
 'FULL(@"MRG$1" "RMS"@"MRG$1")',
 'NO_ACCESS(@"MRG$1" "from$_subquery$_010"@"MRG$1")',
 'OUTLINE(@"SEL$2")',
 'OUTLINE(@"SEL$1")',
 'OUTLINE_LEAF(@"MRG$1")',
 'OUTLINE_LEAF(@"SEL$5")',
 'OUTLINE_LEAF(@"SEL$6")',
 'MERGE(@"SEL$2")',
 'OUTLINE_LEAF(@"SEL$F5BB74E1")',
 'OUTLINE_LEAF(@"SEL$3")',
 'OUTLINE_LEAF(@"SEL$4")',
 'OPT_PARAM(''optimizer_index_caching'' 80)',
 'OPT_PARAM(''optimizer_index_cost_adj'' 3)',
 'OPT_PARAM(''_optimizer_connect_by_cost_based'' ''false'')',
 'OPT_PARAM(''_optim_peek_user_binds'' ''false'')',
 'OPT_PARAM(''_b_tree_bitmap_plans'' ''false'')',
 'DB_VERSION(''11.1.0.7'')',
 'OPTIMIZER_FEATURES_ENABLE(''11.1.0.7'')',
 'IGNORE_OPTIM_EMBEDDED_HINTS',
 'END_OUTLINE_DATA'
);
for sql_rec in (
 select t.sql_id, t.sql_text
 from dba_hist_sqltext t, dba_hist_sql_plan p
 where t.sql_id = p.sql_id and p.sql_id = 'dgh672sad89j4' and p.plan_hash_value = 12345678 and p.parent_id is null
 ) loop
 DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
 sql_text => sql_rec.sql_text,profile => ar_profile_hints,name => 'PROFILE_'||sql_rec.sql_id
 );
 end loop;
end;
/

That was simple. Isn’t it??

But i was not satisfied. I wanted to take this a step further. Now i want oracle to use whatever plan I want.

In case oracle had changed plan , I want oracle to use previous plan. 

In one of my database, planflip caused cpu spikes. Buffer_gets/exec increased from thousands to millions. The idea was to force old to use previous plan .

oldprofile.sql

</pre>
Sesion1>@oldprofile
enter sql_id:- 9ddv4vbt2zrxy
enter old plan hash value:- 1236769290
enter bad plan hash value to be tuned :- 3891475932
'===================OUTPUT========================='

declare
ar_profile_hints sys.sqlprof_attr;
begin
 ar_profile_hints := sys.sqlprof_attr(
 'BEGIN_OUTLINE_DATA',
 'IGNORE_OPTIM_EMBEDDED_HINTS',
 'OPTIMIZER_FEATURES_ENABLE(''11.1.0.7'')',
 'DB_VERSION(''11.1.0.7'')',
 'OPT_PARAM(''_b_tree_bitmap_plans'' ''false'')',
 'OPT_PARAM(''_optim_peek_user_binds'' ''false'')',
 'OPT_PARAM(''_optimizer_connect_by_cost_based'' ''false'')',
 'OPT_PARAM(''optimizer_index_cost_adj'' 3)',
 'OPT_PARAM(''optimizer_index_caching'' 80)',
 'OUTLINE_LEAF(@"SEL$1")',
 'INDEX(@"SEL$1" "CCS"@"SEL$1" ("COMPLETED_CUSTOMER_SHIPMENTS"."CONDITION" "COMPLETED_CUSTOMER_SHIPMENTS"."LAST_UPDATED"))',
 'END_OUTLINE_DATA'
);
for sql_rec in (
 select t.sql_id, t.sql_text
 from dba_hist_sqltext t, dba_hist_sql_plan p
 where t.sql_id = p.sql_id and p.sql_id = '9ddv4vbt2zrxy' and p.plan_hash_value = 3891475932 and p.parent_id is null
 ) loop
 DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
 sql_text => sql_rec.sql_text,profile => ar_profile_hints,name => 'PROFILE_'||sql_rec.sql_id
 );
 end loop;
end;
/

Oldprofile.sql

OldProfile.sql:- This script can be used to force oracle to use the given plan by creating profile over top of it.

/*
Name :- Oldprofile.sql
Db Version :- 11.1.0.7
Author :- Sumit Bhatia
Version :- 1.1
Purpose :- Create a profile to force the old plan
Input :- sql_id, old plan hash value, bad plan hash value
*/
set feedback off
set echo off
set timing off
set pagesize 0
set heading off verify off
accept sql_id prompt ‘enter sql_id:- ‘
accept old_plan_hash_value prompt’enter old plan hash value:- ‘
accept plan_hash prompt’enter bad plan hash value to be tuned :- ‘
prompt ‘===================OUTPUT=========================’
select chr(10) from dual;

select ‘declare’ from dual;
select ‘ar_profile_hints sys.sqlprof_attr;’ from dual;
select ‘begin’ from dual;
select chr(9)||’ar_profile_hints := sys.sqlprof_attr(‘ from dual;
select chr(9)||chr(9)||”’BEGIN_OUTLINE_DATA”,’ from dual;

SELECT chr(9)||chr(9)||””||regexp_replace(extractvalue(value(d), ‘/hint’),””,”””)||”’,’
from
xmltable(‘/*/outline_data/hint’
passing (
select
xmltype(other_xml) as xmlval
from
dba_hist_sql_plan
where
sql_id like nvl(‘&sql_id’,sql_id)
and plan_hash_value=&old_plan_hash_value
and other_xml is not null
and rownum < 2
)
)
d;

select chr(9)||chr(9)||”’END_OUTLINE_DATA”’||chr(10)||’);’||chr(10)||’for sql_rec in (‘||chr(10)||chr(9)||’select t.sql_id, t.sql_text’||chr(10)||chr(9)||’ from dba_hist_sqltext t, dba_hist_sql_plan p’ ||chr(10)||chr(9)|| ‘where t.sql_id = p.sql_id and p.sql_id = ”&sql_id” and p.plan_hash_value = &plan_hash and p.parent_id is null’||chr(10)||chr(9)||chr(9)||’) loop’||chr(10)||chr(9)||’ DBMS_SQLTUNE.IMPORT_SQL_PROFILE(‘||chr(10)||chr(9)||chr(9)||’sql_text => sql_rec.sql_text,profile => ar_profile_hints,name => ”PROFILE_”||sql_rec.sql_id’||CHR(10)||chr(9)||chr(9)||chr(9)||chr(9)||chr(9)||’);’||chr(10)||chr(9)||’end loop;’||chr(10)||’end;’||chr(10)||’/’ FROM DUAL;
prompt ‘===================OUTPUT=========================’
set heading on
set pages 500
undef sql_id
undef old_plan_hash_value
undef plan_hash

planprofile.sql

Profile.sql:- This script can be used to convert baseline into profile. Output can be directly pasted to create a profile.

/*
Name :- PlanProfile.sql
Db Version :- 11.1.0.7
Author :- Sumit Bhatia
Version :- 1.1
Purpose :- Create a profile from baseline
Input :- sql_id, baseline plan name, bad plan hash value
*/
set feedback off
set echo off
set timing off
set pagesize 0 lines 500
set heading off verify off
accept sql_id prompt ‘enter sql_id to be fixed:- ‘
accept plan prompt’baseline plan name:- ‘
accept plan_hash prompt’enter bad plan hash value to be tuned :- ‘
prompt ‘===================OUTPUT=========================’
select chr(10) from dual;

select ‘declare’ from dual;

select ‘ar_profile_hints sys.sqlprof_attr;’ from dual;

select ‘begin’ from dual;
select chr(9)||’ar_profile_hints := sys.sqlprof_attr(‘ from dual;
select chr(9)||chr(9)||”’BEGIN_OUTLINE_DATA”,’ from dual;
SELECT chr(9)||chr(9)||””||regexp_replace(extractValue(value(h),’.'),””,”””)||”’,’ FROM sys.sqlobj$data od, sys.sqlobj$ so, table(xmlsequence(extract(xmltype(od.comp_data),’/outline_data/hint’))) h WHERE so.name = ‘&plan’ AND so.signature = od.signature AND so.category = od.category AND so.obj_type = od.obj_type AND so.plan_id = od.plan_id;

select chr(9)||chr(9)||”’END_OUTLINE_DATA”’||chr(10)||’);’||chr(10)||’for sql_rec in (‘||chr(10)||chr(9)||’select t.sql_id, t.sql_text’||chr(10)||chr(9)||’ from dba_hist_sqltext t, dba_hist_sql_plan p’ ||chr(10)||chr(9)|| ‘where t.sql_id = p.sql_id and p.sql_id = ”&sql_id” and p.plan_hash_value = &plan_hash and p.parent_id is null’||chr(10)||chr(9)||chr(9)||’) loop’||chr(10)||chr(9)||’ DBMS_SQLTUNE.IMPORT_SQL_PROFILE(‘||chr(10)||chr(9)||chr(9)||’sql_text => sql_rec.sql_text,profile => ar_profile_hints,name => ”PROFILE_”||sql_rec.sql_id’||CHR(10)||chr(9)||chr(9)||chr(9)||chr(9)||chr(9)||’);’||chr(10)||chr(9)||’end loop;’||chr(10)||’end;’||chr(10)||’/’ FROM DUAL;
prompt ‘===================OUTPUT=========================’
set heading on
set pages 500
undef sql_id
undef plan
undef plan_hash

Profile.sql

Profile.sql:- This script can be used to tune bad performing queries. Output can be directly paste to create a profile

/*
Name :- Profile.sql
Db Version :- 11.1.0.7
Author :- Sumit Bhatia
Version :- 1.1
Purpose :- Create a profile
Input :- Hinted sql_id, child number, original sql_id and original plan_hash_value
*/
set feedback off
set echo off
set timing off
set pagesize 0
set heading off verify off
accept sql_id prompt 'enter hinted sql_id:- '
accept child_no prompt'enter child number:- '
accept orig_sql_id prompt 'enter original sql_id:- '
accept plan_hash prompt'enter bad plan hash value to be tuned :- '
prompt '===================OUTPUT========================='
select chr(10) from dual;

select 'declare' from dual;
select 'ar_profile_hints sys.sqlprof_attr;' from dual;
select 'begin' from dual;
select chr(9)||'ar_profile_hints := sys.sqlprof_attr(' from dual;
select chr(9)||chr(9)||'''BEGIN_OUTLINE_DATA'',' from dual;

SELECT chr(9)||chr(9)||''''||regexp_replace(extractvalue(value(d), '/hint'),'''','''''')||''','
 from
 xmltable('/*/outline_data/hint'
 passing (
 select
 xmltype(other_xml) as xmlval
 from
 v$sql_plan
 where
 sql_id like nvl('&sql_id',sql_id)
 and child_number = &child_no
 and other_xml is not null
 )
 )
d;

select chr(9)||chr(9)||'''END_OUTLINE_DATA'''||chr(10)||');'||chr(10)||'for sql_rec in ('||chr(10)||chr(9)||'select t.sql_id, t.sql_text'||chr(10)||chr(9)||' from dba_hist_sqltext t, dba_hist_sql_plan p' ||chr(10)||chr(9)|| 'where t.sql_id = p.sql_id and p.sql_id = ''&orig_sql_id'' and p.plan_hash_value = &plan_hash and p.parent_id is null'||chr(10)||chr(9)||chr(9)||') loop'||chr(10)||chr(9)||' DBMS_SQLTUNE.IMPORT_SQL_PROFILE('||chr(10)||chr(9)||chr(9)||'sql_text => sql_rec.sql_text,profile => ar_profile_hints,name => ''PROFILE_''||sql_rec.sql_id'||CHR(10)||chr(9)||chr(9)||chr(9)||chr(9)||chr(9)||');'||chr(10)||chr(9)||'end loop;'||chr(10)||'end;'||chr(10)||'/' FROM DUAL;
prompt '===================OUTPUT========================='
set heading on
set pages 500
undef sql_id
undef child_no
undef plan_hash

ndx-cleanup.sql

This script must be executed as SYS and is used to cleanup the journal table.

declare

/* Name :- ndx-cleanup.sql */
/* DB Version :- 11.1.0.7 */
/* Author :- Sumit Bhatia */
/* Version :- 1.1
/* Purpose :- To cleanup the journal entries */
/* Input :- owner and index name */

/*declare variables*/
index_name dba_objects.object_name%type;
index_owner dba_objects.owner%type;
index_id dba_objects.object_id%type;
index_status dba_objects.status%type;
journal_name dba_objects.object_name%type;
retval boolean;
wait_for_lock binary_integer;

begin
/* INITIALIZE */
journal_name:='NONE_NONE';

/*check if index exists*/
 begin
select object_name,owner,object_id,status into index_name,index_owner,index_id,index_status from dba_objects where owner=upper('&owner') and object_name=upper('&index_name') and object_type='INDEX';
 exception
 WHEN NO_DATA_FOUND THEN
 dbms_output.put_line('INDEX NOT FOUND');
 raise;
 end;

/*check if index is stuck in journal*/
 begin
 select object_name into journal_name from dba_objects where object_name=concat('SYS_JOURNAL_',index_id);
 exception
 WHEN NO_DATA_FOUND THEN
 journal_name:='NONE_NONE';
 end;

dbms_output.put_line('OBJECT ID OF INDEX IS -----------> '||index_id);
if journal_name ='NONE_NONE'
then
 dbms_output.put_line('No journal table found. Index is clean');
else
 dbms_output.put_line('JOURNAL TABLE IS -----------> '||journal_name);
 dbms_output.put_line('CURRENT INDEX STATUS -----------> '||index_status);

 wait_for_lock := NULL;
 retval:=sys.dbms_repair.online_index_clean(index_id);
 case
 when retval then dbms_output.put_line('INDEX CLEANUP COMPLETED');
 when not retval then dbms_output.put_line('INDEX COULD NOT BE CLEANED');
 end case;

 commit;
end if;

/*confirm that journal entry has been cleared */
begin
 select object_name into journal_name from dba_objects where object_name=concat('SYS_JOURNAL_',index_id);
 exception
 WHEN NO_DATA_FOUND THEN
 dbms_output.put_line('NO JOURNAL TABLE FOUND');
 end;

end;
/

ndx-progress.sql

This script does not have any DDL/DML and it just queries V$ views to pull the data based on various if-else condition.It in indented to work with 11g

declare
/* Name       :-    ndx-progress.sql */
/* DB Version :-    11.1.0.7  */
/* Author     :-    Sumit Bhatia */
/* Version    :-    1.1
/* Purpose    :-    To track the progress of index and find blocking sessions */
/* Input      :-    Sid of the session runing index rebuild */
/*declare local variables */
/*capture the session information*/
 mysid                v$session.sid%type;
 myserial             v$session.serial#%type;
 mylastcall           v$session.last_call_et%type;
 mymodule             v$session.module%type;
 mymachine            v$session.machine%type;
 myusername           v$session.username%type;
 myosuser             v$session.osuser%type;
 v_command            v$session.command%type;
 v_sql_id             v$session.sql_id%type;
 v_event              v$session.event%type;
 v_sql_text           v$sql.sql_text%type;
 v_cpu_time           v$sql.cpu_time%type;
 v_elapsed_time       v$sql.elapsed_time%type;
 v_table_name         dba_objects.object_name%type;
 v_index_name         dba_objects.object_name%type; 
 v_journal_name       dba_objects.object_name%type;
 v_journal_index      dba_objects.object_name%type; 
 v_index_id           dba_objects.object_id%type; 
 v_opname             v$session_longops.opname%type;
 v_table_owner        dba_objects.owner%type;
 v_index_owner        dba_objects.owner%type;
 v_total_waits        v$session_event.total_waits%type;
 v_total_timeouts     v$session_event.total_timeouts%type;
 v_time_waited        v$session_event.time_waited%type;
 v_average_wait       v$session_event.average_wait%type;
 v_max_wait           v$session_event.max_wait%type;
 
 
/*find if rebuilding or cleaning is in progress*/
 operation_id         number; 
 
/* longops details */
    longops_count        number; --find if session is there in longops
 
 
/*capture the current state*/ 
 status_id            number;
 event_id             number;
 opname_id            number;
 
/*Description of current stage*/
 stage                varchar2(4000);
 progress             varchar2(4000);
    
 
/* Parallel and Slave process tracking */ 
 
 parallel_id          number;    --to find if parallelism is used or not
    parallel_check       number;    --number of parallel process spawned by this sid as per longops
 slave_count          number;    --number of slave spawned as per px_session
 
/*journal table details*/
 count_journal        number; --to find number of rows in journal table
 
 
/* declare type variables to find for blocking and parallel sessions */
 type type_sid is table of v$session.sid%type;
 type type_serial is table of v$session.serial#%type;
 type type_status is table of v$session.status%type;
 type type_last_call_et is table of v$session.last_call_et%type;
 type type_command is table of v$session.command%type;
 type type_module is table of v$session.module%type;
 type type_machine is table of v$session.machine%type;
 type type_sql_id is table of v$session.sql_id%type;
 type type_prev_sql_id is table of v$session.prev_sql_id%type; 
 type type_event is table of v$session_wait.event%type;
 type type_opname is table of v$session_longops.opname%type;
 type type_sofar is table of v$session_longops.sofar%type;
 type type_totalwork is table of v$session_longops.totalwork%type;
 type type_time_remaining is table of v$session_longops.time_remaining%type;
 type type_elapsed_seconds is table of v$session_longops.elapsed_seconds%type;
 type type_degree is table of v$px_session.degree%type;
 type type_req_degree is table of v$px_session.req_degree%type;
 
 
/* declare blocking variables*/
 blocking_sid type_sid;
 blocking_serial type_serial;
 blocking_status type_status;
 blocking_last_call_et type_last_call_et;
 blocking_command type_command;
 blocking_module type_module;
 blocking_machine type_machine;
 blocking_sql_id type_sql_id;
 blocking_prev_sql_id type_prev_sql_id; 
 
 
 
 /* Declare slave process */
 slave_sid type_sid;
 slave_serial type_serial;
    slave_event type_event;
 slave_totalwork type_totalwork;
 slave_sofar type_sofar;
 slave_opname type_opname;
 slave_degree type_degree;
 slave_req_degree type_req_degree;
 slave_time_remaining type_time_remaining;
 slave_elapsed_seconds type_elapsed_seconds;
begin
/* initialize */
/* operation_id =0 -->index build in progress
   operation_id =1 -->index cleanup in progress
*/
/*
 parallel_id =0 -->no parallel
 parallel_id =1 -->parallel 1 clause used
 parallel_id =2 --> parallel clause being used
*/ 
/* 
   status_id =0    -->waiting for first latch
   status_id =1    -->first latch acquired and sequential read going on to merge journal table entries before FTS.
   status_id =2    -->FTS going on
   status_id =3    -->Sequential Read going on after FTS to merge journal table entries.
   status_id =4    -->Direct path read temp going on
   status_id =10   -->waiting for ending latch   
   status_id =11   -->parallel processing going on
*/
/*
  event_id =0      -->enq: TX - row lock contention
  event_id =1      -->db file scattered read
  event_id =2      -->db file sequential read
  event_id =3      -->direct path read temp
  event_id =11     -->PX Deq: Execute Reply
  event_id =100    -->others 
*/ 

/*
  opname_id =0    -->Not started
  opname_id =1    -->FULL Scan
  opname_id =2    -->Sort output
  opname_id =3    -->Operation completed
  opname_id =4    -->Row id Scan
  opname_id =100  -->others
  
*/
/* GATHER SESSION INFO AND SQL INFO */
  select sid,serial#,last_call_et,sql_id ,event,command,module,machine,username,osuser into mysid,myserial,mylastcall,v_sql_id,v_event,v_command,mymodule,mymachine,myusername,myosuser from v$session where sid=&sid;
  select sql_text,cpu_time,elapsed_time into v_sql_text,v_cpu_time,v_elapsed_time from v$sql where sql_id=v_sql_id and rownum<2;
  select count(*) into parallel_check from v$sql where sql_id=v_sql_id and upper(sql_text) like '% PARALLEL %';
  
/* check if index cleanup is running or index rebuild in running */
 
 if v_event<>'index (re)build online cleanup'
 then
  operation_id:=0;
 else
  operation_id:=1;
 end if; 
 /*if index rebuild is in progress then....*/
 if operation_id=0 
 then
  
  /*extract object info */
  select object_name ,substr(object_name,13) into v_journal_name,v_index_id from v$locked_object lo,dba_objects do,v$lock l where lo.OBJECT_ID = do.OBJECT_ID AND l.SID = lo.SESSION_ID and l.sid=mysid and object_name like '%SYS_JOURNAL%' group by object_name,substr(object_name,13);
  select object_name into v_table_name from v$locked_object lo,dba_objects do,v$lock l where lo.OBJECT_ID = do.OBJECT_ID AND l.SID = lo.SESSION_ID and l.sid=mysid and object_name not like '%SYS_JOURNAL%' group by object_name;
  select object_name,owner into v_index_name,v_index_owner from dba_objects where object_id=v_index_id;
  
  /*check if parallel processing is going on */
  if parallel_check=0 
  then
   parallel_id:=0;
  else
   parallel_id:=2;
  end if;
  
  /*print object info*/
      dbms_output.put_line(CHR(9));
   dbms_output.put_line('SID              ---->'||mysid);
   dbms_output.put_line('SERIAL           ---->'||myserial);
   dbms_output.put_line('MODULE           ---->'||mymodule);
   dbms_output.put_line('MACHINE          ---->'||mymachine);
   dbms_output.put_line('USERNAME         ---->'||myusername);
   dbms_output.put_line('OSUSER           ---->'||myosuser);
   dbms_output.put_line('SQL TEXT         ---->'||v_sql_text);
   dbms_output.put_line('SQL ID           ---->'||v_sql_id);
   dbms_output.put_line('JOURNAL NAME     ---->'||v_journal_name);
   dbms_output.put_line('TABLE NAME       ---->'||v_table_name);
   dbms_output.put_line('EVENT_NAME       ---->'||v_event);
   dbms_output.put_line('IDLE SINCE       ---->'||mylastcall);
   dbms_output.put_line('CPU TIME         ---->'||v_cpu_time);
   dbms_output.put_line('ELAPSED TIME     ---->'||v_elapsed_time);
   
  if parallel_id=0 /*no parallel is used */
  then
/*SECTION FOR NO PARALLEL*/  
   /*check if session is present in longops */
   select nvl(count(*),0) into longops_count from v$session_longops where sid=mysid and serial#=myserial and sql_id=v_sql_id;
   
   /*if session is present in longops capture its last operation */
   if longops_count>0
   then
    select  opname into v_opname from (select opname  from v$session_longops where sid=mysid and serial#=myserial and sql_id=v_sql_id order by opname) where rownum <2;
   else
    v_opname:='INDEX REBUILD NOT STARTED YET';
   end if;
   
   /*check event info*/
   if v_event='enq: TX - row lock contention'
   then
    event_id:=0;
   elsif v_event='db file scattered read'
   then 
    event_id:=1;
   elsif v_event='db file sequential read'
   then
    event_id:=2;
   elsif   v_event='direct path read temp'
   then
    event_id:=3;   
   else
    event_id:=100;
   end if; 
   
   
   /*Compute the operation stage*/
   if v_opname ='Table Scan' and event_id <>0       --scattered read going on
   then
    opname_id:=1;
   elsif v_opname='Sort Output' and event_id <> 0   --sort output going on
   then
    opname_id:=2;
   elsif v_opname ='Sort Output' and event_id =0    --sort output completed and waiting for last latch
   then
    opname_id:=3;
   elsif v_opname='INDEX REBUILD NOT STARTED YET' and event_id=0       --first latch not acquired
   then
    opname_id:=0;
   else
    opname_id:=100; 
   end if;
   
   execute immediate 'select count(*) from '||v_index_owner||'.'||v_journal_name into count_journal;
      dbms_output.put_line('JOURNAL COUNT    ---->'||count_journal);
   
   /* compute status*/
   if event_id=0 and opname_id>0 /*enq Tx row contention and there is entry into longops then its last latch */
   then
    status_id:=10;
    stage:='Operation completed.Waiting for ending latch';
    progress:='Tx started now will not cause any blocking or entry into journal table';
   elsif event_id=0 and opname_id=0
   then
    status_id:=0; /*enq Tx row contention and no entry into longops then its waiting for first latch */
    stage:='Waiting for first latch.';
   elsif event_id=1
   then
    status_id:=2;
    stage:='First latch acquired. FTS going on';
   elsif event_id=2
   then
        /* Check if session has already perfomred scattered reador not */
    select count(*) into longops_count from v$session_longops where sid=mysid and serial#=myserial and sql_id=v_sql_id and opname='Table Scan';
    
    /*if there is no scattered read then journal table is getting merged */
    if longops_count =0
    then
     status_id:=1;
     stage:='first latch acquired and sequential read going on to merge journal table entries before FTS.';
    /*else FTS completed and sequential read to merge journal entries captured during scattered read*/
    else
     status_id:=3;
     stage:='Sequential Read going on after FTS to merge journal table entries.';
    end if;
   elsif event_id =3
   then
    status_id:=4;
    stage:='First latch acquired.FTS and sequential read completed.Direct path read temp going on.';  
   elsif event_id=100
   then
    status_id:=10;
       stage:='waiting for other wait event';
   end if;
   
   /*check what happened to Transaction during index rebuild */
   if status_id=0 and count_journal=0
   then
    progress:='No Tx started after index rebuild has commited yet. There are 0 rows in journal table';
   elsif status_id=0 and count_journal >0
   then
    progress:='Few Tx started after index rebuild command have been commited and there is/are '||count_journal||' rows into journal table';
   elsif status_id=1 and count_journal=0
   then
    progress:='Completed merging journal table entries before starting scattered read';
   elsif status_id=1 and count_journal>0
   then
    progress:='merging journal table entries before starting scattered read. There are still '||count_journal||' rows to be merged';
   elsif status_id=2 and count_journal=0
   then
    progress:='No Tx commited since Scattered read started. 0 rows in journal table';
   elsif status_id=2 and count_journal>0
   then
    progress:='Tx have committed while scattered read going on.There are still '||count_journal||' rows to be merged once FTS complete.';
   elsif status_id=3 and count_journal=0
   then
    progress:='Merging of journal entries just completed after FTS.';
   elsif status_id=3 and count_journal>0
   then
    progress:='Merging of journal entries after FTS is going on.There are still '||count_journal||' rows to be merged once FTS complete.';
   elsif status_id=4 and count_journal=0
   then
    progress:=' Sorting going on. This is last step and there are 0 rows in journal table';
   elsif status_id=4 and count_journal>0
   then
    progress:=' Sorting going on. Some transactions have again committed during this and again have to do sequential read. Currently '|| count_journal|| ' rows in journal';
   elsif status_id=10 and count_journal=0
   then
    progress:=' Waiting for Tx to complete. Entries wont go into journal table now.';
   elsif status_id=10 and count_journal>0
   then
    progress:=' Waiting for Tx to complete. Currently '|| count_journal|| ' rows in journal';
   end if;
   
   /*print status info */
   dbms_output.put_line('STAGE            ---->'||stage);
   dbms_output.put_line('LONGOPS          ---->'||v_opname);
   dbms_output.put_line('PROGRESS         ---->'||progress);
   
   /*if first latch is not acquired then what sesisons are blocking it */
   if status_id=0
   then
    select 
     s.sid,s.serial#,s.status,s.last_call_et,s.command,s.module,s.machine,nvl(s.sql_id,'NONE'),NVL(s.prev_sql_id,'NONE') 
    bulk collect into 
     blocking_sid,blocking_serial,blocking_status,blocking_last_call_et,blocking_command,blocking_module,blocking_machine,blocking_sql_id,blocking_prev_sql_id
    from
     v$transaction t,v$session s
    where 
     t.addr=s.taddr
    and
     s.sid in 
     (
      select 
       l.sid 
      from 
       v$locked_object lo,dba_objects do,v$lock l
      where 
             (l.type='TM' and l.lmode=3 )   
      and 
       lo.OBJECT_ID = do.OBJECT_ID
      and
       l.SID = lo.SESSION_ID
      and
       do.object_name=v_table_name
      INTERSECT   
      select
       l.sid
      from 
       v$locked_object lo,dba_objects do,v$lock l
      where   
       (l.type='TX' and l.lmode=6 )
      and 
       lo.OBJECT_ID = do.OBJECT_ID
      and
       l.SID = lo.SESSION_ID
      and
       do.object_name=v_table_name    
      INTERSECT
      select
       l.sid
      from  
       v$locked_object lo,dba_objects do,v$lock l
      where 
       (l.type not in ('OD','DL') and l.lmode IN (4,3))
      and
       lo.OBJECT_ID = do.OBJECT_ID
      and
       l.SID = lo.SESSION_ID
      and
       (do.object_name=v_table_name)  
     )
    and 
    t.start_time < (select start_time from v$transaction,v$session where sid  = mysid and serial#=myserial and addr=taddr);
    dbms_output.put_line(CHR(9));
    dbms_output.put_line(CHR(9));
    dbms_output.put_line('------------SESSIONS BLOCKING FIRST LATCH ARE-------------');
    dbms_output.put_line('SID'||CHR(9)||'SERIAL'||CHR(9)||'STATUS'||CHR(9)||CHR(9)||'LAST_CALL_ET'||CHR(9)||CHR(9)||'COMMAND'||CHR(9)||CHR(9)||'SQL_ID'||CHR(9)||CHR(9)||'PREV_SQL_ID'||CHR(9)||CHR(9)||'MODULE'||CHR(9)||CHR(9)||'MACHINE');
    for i in blocking_sid.first ..blocking_sid.last 
    loop
     dbms_output.put_line(blocking_sid(i)||CHR(9)||blocking_serial(i)||CHR(9)||blocking_status(i)||CHR(9)||CHR(9)||blocking_last_call_et(i)||CHR(9)||CHR(9)||blocking_command(i)||CHR(9)||CHR(9)||blocking_sql_id(i)||CHR(9)||CHR(9)||blocking_prev_sql_id(i)||CHR(9)||CHR(9)||blocking_module(i)||CHR(9)||blocking_machine(i));
    end loop;
    
    /* check potential blockers for ending latch */
    
    select 
     s.sid,s.serial#,s.status,s.last_call_et,s.command,s.module,s.machine,nvl(s.sql_id,'NONE'),NVL(s.prev_sql_id,'NONE') 
    bulk collect into 
     blocking_sid,blocking_serial,blocking_status,blocking_last_call_et,blocking_command,blocking_module,blocking_machine,blocking_sql_id,blocking_prev_sql_id
    from
     v$transaction t,v$session s
    where 
     t.addr=s.taddr
    and
     s.sid in 
     (
      select 
       l.sid 
      from 
       v$locked_object lo,dba_objects do,v$lock l
      where 
       (l.type='TM' and l.lmode=3 )   
      and 
       lo.OBJECT_ID = do.OBJECT_ID
      and 
       l.SID = lo.SESSION_ID
      and
       do.object_name=v_table_name
      INTERSECT   
      select
       l.sid
      from 
       v$locked_object lo,dba_objects do,v$lock l
      where   
       (l.type='TX' and l.lmode=6 )
      and 
       lo.OBJECT_ID = do.OBJECT_ID
      and
       l.SID = lo.SESSION_ID
      and
       do.object_name=v_table_name    
      INTERSECT
      select
       l.sid
      from  
       v$locked_object lo,dba_objects do,v$lock l
      where 
       (l.type not in ('OD','DL') and l.lmode IN (4,3))
      and
       lo.OBJECT_ID = do.OBJECT_ID
      and
       l.SID = lo.SESSION_ID
      and
       (do.object_name=v_table_name)  
     )
    and 
    t.start_time > (select start_time from v$transaction,v$session where sid  = mysid and serial#=myserial and addr=taddr)
    and s.last_call_et > 300;
     
     /*INNER BLOCK for exception handling */
     BEGIN
      dbms_output.put_line(CHR(9));
      dbms_output.put_line(CHR(9));
      dbms_output.put_line('------------SESSIONS THAT MAY BLOCK LAST LATCH ARE-------------');
      dbms_output.put_line('SID'||CHR(9)||'SERIAL'||CHR(9)||'STATUS'||CHR(9)||CHR(9)||'LAST_CALL_ET'||CHR(9)||CHR(9)||'COMMAND'||CHR(9)||CHR(9)||'SQL_ID'||CHR(9)||CHR(9)||'PREV_SQL_ID'||CHR(9)||CHR(9)||'MODULE'||CHR(9)||CHR(9)||'MACHINE');
      for i in blocking_sid.first ..blocking_sid.last 
      loop
       dbms_output.put_line(blocking_sid(i)||CHR(9)||blocking_serial(i)||CHR(9)||blocking_status(i)||CHR(9)||CHR(9)||blocking_last_call_et(i)||CHR(9)||CHR(9)||blocking_command(i)||CHR(9)||CHR(9)||blocking_sql_id(i)||CHR(9)||CHR(9)||blocking_prev_sql_id(i)||CHR(9)||CHR(9)||blocking_module(i)||CHR(9)||blocking_machine(i));
       dbms_output.put_line(CHR(9));
       dbms_output.put_line(CHR(9));
      end loop;
     EXCEPTION
     WHEN OTHERS THEN
         dbms_output.put_line(CHR(9));
      dbms_output.put_line('NO POTENTIAL BLOCKERS FOUND FOR THE LAST LATCH');
      dbms_output.put_line(CHR(9));
      dbms_output.put_line(CHR(9));
     END;                   
   elsif status_id=10
   then
    select 
     s.sid,s.serial#,s.status,s.last_call_et,s.command,s.module,s.machine,nvl(s.sql_id,'NONE'),NVL(s.prev_sql_id,'NONE') 
    bulk collect into 
     blocking_sid,blocking_serial,blocking_status,blocking_last_call_et,blocking_command,blocking_module,blocking_machine,blocking_sql_id,blocking_prev_sql_id
    from
     v$transaction t,v$session s
    where 
     t.addr=s.taddr
    and
     s.sid in 
     (
      select 
       l.sid 
      from 
       v$locked_object lo,dba_objects do,v$lock l
      where 
       (l.type='TM' and l.lmode=3 )   
      and 
       lo.OBJECT_ID = do.OBJECT_ID
      and 
       l.SID = lo.SESSION_ID
      and
       do.object_name=v_table_name
      INTERSECT   
      select
       l.sid
      from 
       v$locked_object lo,dba_objects do,v$lock l
      where   
       (l.type='TX' and l.lmode=6 )
      and 
       lo.OBJECT_ID = do.OBJECT_ID
      and
       l.SID = lo.SESSION_ID
      and
       do.object_name=v_table_name    
      INTERSECT
      select
       l.sid
      from  
       v$locked_object lo,dba_objects do,v$lock l
      where 
       (l.type not in ('OD','DL') and l.lmode IN (4,3))
      and
       lo.OBJECT_ID = do.OBJECT_ID
      and
       l.SID = lo.SESSION_ID
      and
       (do.object_name=v_table_name)  
     )
    and 
    t.start_time > (select start_time from v$transaction,v$session where sid  = mysid and serial#=myserial and addr=taddr);    
    dbms_output.put_line(CHR(9));
    dbms_output.put_line(CHR(9));
    dbms_output.put_line('------------SESSIONS BLOCKING LAST LATCH ARE-------------');
    dbms_output.put_line('SID'||CHR(9)||'SERIAL'||CHR(9)||'STATUS'||CHR(9)||CHR(9)||'LAST_CALL_ET'||CHR(9)||CHR(9)||'COMMAND'||CHR(9)||CHR(9)||'SQL_ID'||CHR(9)||CHR(9)||'PREV_SQL_ID'||CHR(9)||CHR(9)||'MODULE'||CHR(9)||CHR(9)||'MACHINE');
    for i in blocking_sid.first ..blocking_sid.last 
    loop
     dbms_output.put_line(blocking_sid(i)||CHR(9)||blocking_serial(i)||CHR(9)||blocking_status(i)||CHR(9)||CHR(9)||blocking_last_call_et(i)||CHR(9)||CHR(9)||blocking_command(i)||CHR(9)||CHR(9)||blocking_sql_id(i)||CHR(9)||CHR(9)||blocking_prev_sql_id(i)||CHR(9)||CHR(9)||blocking_module(i)||CHR(9)||blocking_machine(i));
    end loop;    
   end if;
/* NO PARALLEL SECTION ENDS HERE */   
  else
/* SECTION FOR PARALLEL PROCESSING STARTED  */  
   /*check event info of parallel process*/
   if v_event='enq: TX - row lock contention'
   then
    event_id:=0;
   elsif v_event='db file scattered read' /*this event will only occur if parallel 1 is used */
   then 
    event_id:=1;
   elsif v_event='db file sequential read'
   then
    event_id:=2;
   elsif   v_event='direct path read temp'
   then
    event_id:=3;   
   elsif   v_event='PX Deq: Execute Reply'
   then
    event_id:=11;   
   else
    event_id:=100;
   end if; 
   
   execute immediate 'select count(*) from '||v_index_owner||'.'||v_journal_name into count_journal;
   dbms_output.put_line('JOURNAL COUNT    ---->'||count_journal);
   
   if event_id=0  /*in case of enq Tx contention */
   then
    /*check if child process is present in in longops or not */
    select count(*) into longops_count from v$session_longops where qcsid=mysid and sql_id=v_sql_id;
    
    if longops_count > 0 /*if there were child slaves earlier then its waiting on last latch */
    then
     status_id:=10;
     stage:='Operation completed.Waiting for ending latch';
    else
     select count(*) into longops_count from v$session_longops where sid=mysid and serial#=myserial and sql_id=v_sql_id; /*check if paralel 1 clause being used */
      if longops_count > 0
      then
       status_id:=10;
       stage:='Operation completed.Waiting for ending latch';
      else
          status_id:=0;
       stage:='Waiting for first latch.';
      end if;
    end if;
   elsif event_id=1
   then
    status_id:=2;
    stage:='Parallel 1 clause being used. Scattered read going on';
   elsif event_id=2
   then
    select count(*) into longops_count from v$session_longops where qcsid=mysid and sql_id=v_sql_id; /*if there were child earlier it means its merging journal after FTS*/
    if longops_count > 0
    then
     status_id:=3;
     stage:='Sequential Read going on for merging journal table entries after FTS';
    else
     select count(*) into longops_count from v$session_longops where sid=mysid and serial#=myserial and sql_id=v_sql_id; 
     if longops_count > 0
      then
       status_id:=3;
       stage:='Parallel 1 clause used. Sequential Read going on to merge journal table entries after FTS';
     else
          status_id:=0;
       stage:='Sequential Read going on to merge journal entries before FTS.';
     end if;
    end if;
   elsif event_id=3
   then
    status_id:=4;
    stage:='Direct Path read temp going on after merging journal table entries';
   elsif event_id=11
   then
    status_id:=11;
    stage:='First Latch acquired.Parallel Processing going on'; 
   end if; 
   
   /* Check the progress*/
   if status_id=0 and count_journal=0
   then
    progress:='No Tx started after index rebuild has commited yet. There are 0 rows in journal table';
   elsif status_id=0 and count_journal >0
   then
    progress:='Few Tx started after index rebuild command have been commited and there is/are '||count_journal||' rows into journal table';
   elsif status_id=1 and count_journal=0
   then
    progress:='Completed merging journal table entries before starting scattered read';
   elsif status_id=1 and count_journal>0
   then
    progress:='merging journal table entries before starting scattered read. There are still '||count_journal||' rows to be merged';
   elsif status_id=2 and count_journal=0
   then
    progress:='No Tx commited since Scattered read started. 0 rows in journal table';
   elsif status_id=2 and count_journal>0
   then
    progress:='Tx have committed while scattered read going on.There are still '||count_journal||' rows to be merged once FTS complete.';
   elsif status_id=3 and count_journal=0
   then
    progress:='Merging of journal entries just completed after FTS';
   elsif status_id=3 and count_journal>0
   then
    progress:='Merging of journal entries after FTS is going on.There are still '||count_journal||' rows to be merged once FTS complete.';
   elsif status_id=4 and count_journal=0
   then
    progress:=' Sorting going on. This is last step and there are 0 rows in journal table';
   elsif status_id=4 and count_journal>0
   then
    progress:=' Sorting going on. Some transactions have again committed during this and again have to do sequential read. Currently '|| count_journal|| ' rows in journal';
   elsif status_id=10 and count_journal=0
   then
    progress:=' Waiting for Tx to complete. Entries wont go into journal table now.';
   elsif status_id=10 and count_journal>0
   then
    progress:=' Waiting for Tx to complete. Currently '|| count_journal|| ' rows in journal';
   elsif status_id=11 and count_journal=0
   then
    progress:=' check slave process opname for current status';
   elsif status_id=11 and count_journal>0
   then
    progress:=' check slave process opname for current status. Few Tx hhave again commited while parallel proceesing going on. Currently '|| count_journal|| ' rows in journal';
   end if;
   
   dbms_output.put_line('STAGE            ---->'||stage);
   dbms_output.put_line('PROGRESS         ---->'||progress);
   
   /* Check the locking */
   if status_id=0
   then
    select 
     s.sid,s.serial#,s.status,s.last_call_et,s.command,s.module,s.machine,nvl(s.sql_id,'NONE'),NVL(s.prev_sql_id,'NONE') 
    bulk collect into 
     blocking_sid,blocking_serial,blocking_status,blocking_last_call_et,blocking_command,blocking_module,blocking_machine,blocking_sql_id,blocking_prev_sql_id
    from
     v$transaction t,v$session s
    where 
     t.addr=s.taddr
    and
     s.sid in 
     (
      select 
       l.sid 
      from 
       v$locked_object lo,dba_objects do,v$lock l
      where 
             (l.type='TM' and l.lmode=3 )   
      and 
       lo.OBJECT_ID = do.OBJECT_ID
      and
       l.SID = lo.SESSION_ID
      and
       do.object_name=v_table_name
      INTERSECT   
      select
       l.sid
      from 
       v$locked_object lo,dba_objects do,v$lock l
      where   
       (l.type='TX' and l.lmode=6 )
      and 
       lo.OBJECT_ID = do.OBJECT_ID
      and
       l.SID = lo.SESSION_ID
      and
       do.object_name=v_table_name    
      INTERSECT
      select
       l.sid
      from  
       v$locked_object lo,dba_objects do,v$lock l
      where 
       (l.type not in ('OD','DL') and l.lmode IN (4,3))
      and
       lo.OBJECT_ID = do.OBJECT_ID
      and
       l.SID = lo.SESSION_ID
      and
       (do.object_name=v_table_name)  
     )
    and 
    t.start_time < (select start_time from v$transaction,v$session where sid  = mysid and serial#=myserial and addr=taddr);
    dbms_output.put_line(CHR(9));
    dbms_output.put_line(CHR(9));
    dbms_output.put_line('------------SESSIONS BLOCKING FIRST LATCH ARE-------------');
    dbms_output.put_line('SID'||CHR(9)||'SERIAL'||CHR(9)||'STATUS'||CHR(9)||CHR(9)||'LAST_CALL_ET'||CHR(9)||CHR(9)||'COMMAND'||CHR(9)||CHR(9)||'SQL_ID'||CHR(9)||CHR(9)||'PREV_SQL_ID'||CHR(9)||CHR(9)||'MODULE'||CHR(9)||CHR(9)||'MACHINE');
    for i in blocking_sid.first ..blocking_sid.last 
    loop
     dbms_output.put_line(blocking_sid(i)||CHR(9)||blocking_serial(i)||CHR(9)||blocking_status(i)||CHR(9)||CHR(9)||blocking_last_call_et(i)||CHR(9)||CHR(9)||blocking_command(i)||CHR(9)||CHR(9)||blocking_sql_id(i)||CHR(9)||CHR(9)||blocking_prev_sql_id(i)||CHR(9)||CHR(9)||blocking_module(i)||CHR(9)||blocking_machine(i));
    end loop;
    
    /* check potential blockers for ending latch */
    
    select 
     s.sid,s.serial#,s.status,s.last_call_et,s.command,s.module,s.machine,nvl(s.sql_id,'NONE'),NVL(s.prev_sql_id,'NONE') 
    bulk collect into 
     blocking_sid,blocking_serial,blocking_status,blocking_last_call_et,blocking_command,blocking_module,blocking_machine,blocking_sql_id,blocking_prev_sql_id
    from
     v$transaction t,v$session s
    where 
     t.addr=s.taddr
    and
     s.sid in 
     (
      select 
       l.sid 
      from 
       v$locked_object lo,dba_objects do,v$lock l
      where 
       (l.type='TM' and l.lmode=3 )   
      and 
       lo.OBJECT_ID = do.OBJECT_ID
      and 
       l.SID = lo.SESSION_ID
      and
       do.object_name=v_table_name
      INTERSECT   
      select
       l.sid
      from 
       v$locked_object lo,dba_objects do,v$lock l
      where   
       (l.type='TX' and l.lmode=6 )
      and 
       lo.OBJECT_ID = do.OBJECT_ID
      and
       l.SID = lo.SESSION_ID
      and
       do.object_name=v_table_name    
      INTERSECT
      select
       l.sid
      from  
       v$locked_object lo,dba_objects do,v$lock l
      where 
       (l.type not in ('OD','DL') and l.lmode IN (4,3))
      and
       lo.OBJECT_ID = do.OBJECT_ID
      and
       l.SID = lo.SESSION_ID
      and
       (do.object_name=v_table_name)  
     )
    and 
    t.start_time > (select start_time from v$transaction,v$session where sid  = mysid and serial#=myserial and addr=taddr)
    and s.last_call_et > 300;
     
     /*INNER BLOCK for exception handling */
     BEGIN
      dbms_output.put_line(CHR(9));
      dbms_output.put_line(CHR(9));
      dbms_output.put_line('------------SESSIONS THAT MAY BLOCK LAST LATCH ARE-------------');
      dbms_output.put_line('SID'||CHR(9)||'SERIAL'||CHR(9)||'STATUS'||CHR(9)||CHR(9)||'LAST_CALL_ET'||CHR(9)||CHR(9)||'COMMAND'||CHR(9)||CHR(9)||'SQL_ID'||CHR(9)||CHR(9)||'PREV_SQL_ID'||CHR(9)||CHR(9)||'MODULE'||CHR(9)||CHR(9)||'MACHINE');
      for i in blocking_sid.first ..blocking_sid.last 
      loop
       dbms_output.put_line(blocking_sid(i)||CHR(9)||blocking_serial(i)||CHR(9)||blocking_status(i)||CHR(9)||CHR(9)||blocking_last_call_et(i)||CHR(9)||CHR(9)||blocking_command(i)||CHR(9)||CHR(9)||blocking_sql_id(i)||CHR(9)||CHR(9)||blocking_prev_sql_id(i)||CHR(9)||CHR(9)||blocking_module(i)||CHR(9)||blocking_machine(i));
       dbms_output.put_line(CHR(9));
       dbms_output.put_line(CHR(9));
      end loop;
     EXCEPTION
     WHEN OTHERS THEN
         dbms_output.put_line(CHR(9));
      dbms_output.put_line('NO POTENTIAL BLOCKERS FOUND FOR THE LAST LATCH');
      dbms_output.put_line(CHR(9));
      dbms_output.put_line(CHR(9));
     END;
   elsif status_id=10
   then
    select 
     s.sid,s.serial#,s.status,s.last_call_et,s.command,s.module,s.machine,nvl(s.sql_id,'NONE'),NVL(s.prev_sql_id,'NONE') 
    bulk collect into 
     blocking_sid,blocking_serial,blocking_status,blocking_last_call_et,blocking_command,blocking_module,blocking_machine,blocking_sql_id,blocking_prev_sql_id
    from
     v$transaction t,v$session s
    where 
     t.addr=s.taddr
    and
     s.sid in 
     (
      select 
       l.sid 
      from 
       v$locked_object lo,dba_objects do,v$lock l
      where 
       (l.type='TM' and l.lmode=3 )   
      and 
       lo.OBJECT_ID = do.OBJECT_ID
      and 
       l.SID = lo.SESSION_ID
      and
       do.object_name=v_table_name
      INTERSECT   
      select
       l.sid
      from 
       v$locked_object lo,dba_objects do,v$lock l
      where   
       (l.type='TX' and l.lmode=6 )
      and 
       lo.OBJECT_ID = do.OBJECT_ID
      and
       l.SID = lo.SESSION_ID
      and
       do.object_name=v_table_name    
      INTERSECT
      select
       l.sid
      from  
       v$locked_object lo,dba_objects do,v$lock l
      where 
       (l.type not in ('OD','DL') and l.lmode IN (4,3))
      and
       lo.OBJECT_ID = do.OBJECT_ID
      and
       l.SID = lo.SESSION_ID
      and
       (do.object_name=v_table_name)  
     )
    and 
    t.start_time > (select start_time from v$transaction,v$session where sid  = mysid and serial#=myserial and addr=taddr);    
    dbms_output.put_line(CHR(9));
    dbms_output.put_line(CHR(9));
    dbms_output.put_line('------------SESSIONS BLOCKING LAST LATCH ARE-------------');
    dbms_output.put_line('SID'||CHR(9)||'SERIAL'||CHR(9)||'STATUS'||CHR(9)||CHR(9)||'LAST_CALL_ET'||CHR(9)||CHR(9)||'COMMAND'||CHR(9)||CHR(9)||'SQL_ID'||CHR(9)||CHR(9)||'PREV_SQL_ID'||CHR(9)||CHR(9)||'MODULE'||CHR(9)||CHR(9)||'MACHINE');
    for i in blocking_sid.first ..blocking_sid.last 
    loop
     dbms_output.put_line(blocking_sid(i)||CHR(9)||blocking_serial(i)||CHR(9)||blocking_status(i)||CHR(9)||CHR(9)||blocking_last_call_et(i)||CHR(9)||CHR(9)||blocking_command(i)||CHR(9)||CHR(9)||blocking_sql_id(i)||CHR(9)||CHR(9)||blocking_prev_sql_id(i)||CHR(9)||CHR(9)||blocking_module(i)||CHR(9)||blocking_machine(i));
    end loop;
   elsif status_id=11
   then
    select p.sid,p.serial#,p.degree,p.req_degree,s.event bulk collect into slave_sid,slave_serial,slave_degree,slave_req_degree,slave_event from v$px_session p,v$session s where p.qcsid=mysid and p.sid<>mysid and p.sid=s.sid and p.serial#=s.serial#;
    dbms_output.put_line(CHR(9));
    dbms_output.put_line(CHR(9));
    dbms_output.put_line('------------TOTAL SLAVES ARE-------------');
    dbms_output.put_line('SID'||CHR(9)||'SERIAL'||CHR(9)||'DEGREE'||CHR(9)||CHR(9)||'REQ_DEGREE'||CHR(9)||CHR(9)||'EVENT');
    
    for i in slave_sid.first ..slave_sid.last 
    loop
     dbms_output.put_line(slave_sid(i)||CHR(9)||slave_serial(i)||CHR(9)||slave_degree(i)||CHR(9)||CHR(9)||slave_req_degree(i)||CHR(9)||CHR(9)||slave_event(i));
    end loop;    
    
/*    select count(*) into parallel_count from v$session_longops where qcsid=mysid and sql_id=v_sql_id; */
    select count(*) into longops_count from v$session_longops where qcsid=mysid and sql_id=v_sql_id; 
    
    BEGIN
     if longops_count=0
     then
       dbms_output.put_line('------------NO ENTRY IN LONGOPS YET FOR ANY SLAVE PROCESS-------------');
     else
      select l.sid,l.serial#,l.opname,l.totalwork,l.sofar,nvl(l.time_remaining,-100),nvl(l.elapsed_seconds,-100),s.event bulk collect into slave_sid,slave_serial,slave_opname,slave_totalwork,slave_sofar,slave_time_remaining,slave_elapsed_seconds,slave_event from v$session_longops l,v$session s where l.qcsid=mysid and l.qcsid<>l.sid and l.sql_id=v_sql_id and l.sofar<>l.totalwork and l.sid=s.sid and l.serial#=s.serial# and l.sid in (select sid from v$px_session);
      dbms_output.put_line(CHR(9));
      dbms_output.put_line(CHR(9));
      dbms_output.put_line('------------LONGOPS SLAVES ARE-------------');
      dbms_output.put_line('SID'||CHR(9)||'SERIAL'||CHR(9)||'TOTALWORK'||CHR(9)||CHR(9)||'SOFAR'||CHR(9)||CHR(9)||'TIME REMAINING'||CHR(9)||CHR(9)||CHR(9)||'ELAPSED SECONDS'||CHR(9)||CHR(9)||'OPNAME'||CHR(9)||CHR(9)||CHR(9)||'EVENT');
    
      for i in slave_sid.first ..slave_sid.last 
      loop
       dbms_output.put_line(slave_sid(i)||CHR(9)||slave_serial(i)||CHR(9)||slave_totalwork(i)||CHR(9)||CHR(9)||CHR(9)||slave_sofar(i)||CHR(9)||CHR(9)||slave_time_remaining(i)||CHR(9)||CHR(9)||CHR(9)||CHR(9)||slave_elapsed_seconds(i)||CHR(9)||CHR(9)||CHR(9)||slave_opname(i)||CHR(9)||CHR(9)||slave_event(i));
      end loop;    
     end if;
     EXCEPTION
     WHEN OTHERS THEN
      dbms_output.put_line('------------NO ENTRY IN LONGOPS YET FOR ANY SLAVE PROCESS-------------');
     end;
   end if;
  /* parallel */ 
  end if;
/*PARALLEL PROCESSING SECTION ENDS HERE */  
 else
/* SECTION FOR INDEX CLEANUP */ 
  /* In case its a cleaning process*/
  dbms_output.put_line('Index Rebuild has been cancelled. Cleaning going on');
  select o.object_name,i.index_name,i.owner,o.owner into v_table_name,v_index_name,v_index_owner,v_table_owner from dba_indexes i,dba_objects o,v$locked_object l,dba_tables t where o.object_name = t.table_name and o.object_id = l.object_id and i.table_name=t.table_name and session_id=mysid;
  select serial#,sql_id,event into myserial,v_sql_id,v_event from v$session where sid=mysid;
  select sql_text into v_sql_text from v$sql where sql_id=v_sql_id and rownum<2;
  select 'SYS_JOURNAL_'||object_id into v_journal_name from dba_objects where object_name=v_index_name;
  execute immediate 'select count(*) from '||v_index_owner||'.'||v_journal_name into count_journal;
   dbms_output.put_line('SID              ---->'||mysid);
   dbms_output.put_line('SERIAL           ---->'||myserial);
   dbms_output.put_line('MODULE           ---->'||mymodule);
   dbms_output.put_line('MACHINE          ---->'||mymachine);
   dbms_output.put_line('USERNAME         ---->'||myusername);
   dbms_output.put_line('OSUSER           ---->'||myosuser);
   dbms_output.put_line('SQL TEXT         ---->'||v_sql_text);
   dbms_output.put_line('SQL ID           ---->'||v_sql_id);
   dbms_output.put_line('JOURNAL NAME     ---->'||v_journal_name);
   dbms_output.put_line('TABLE NAME       ---->'||v_table_name);
   dbms_output.put_line('EVENT_NAME       ---->'||v_event);
   dbms_output.put_line('IDLE SINCE       ---->'||mylastcall);
   dbms_output.put_line('CPU TIME         ---->'||v_cpu_time);
   dbms_output.put_line('ELAPSED TIME     ---->'||v_elapsed_time);
   dbms_output.put_line('JOURNAL COUNT    ---->'||count_journal);
    
  select 
   s.sid,s.serial#,s.status,s.last_call_et,s.command,s.module,s.machine,nvl(s.sql_id,'NONE'),NVL(s.prev_sql_id,'NONE') 
  bulk collect into 
   blocking_sid,blocking_serial,blocking_status,blocking_last_call_et,blocking_command,blocking_module,blocking_machine,blocking_sql_id,blocking_prev_sql_id
  from
   v$transaction t,v$session s
  where 
   t.addr=s.taddr
  and
   s.sid in 
     (
      select
       l.session_id
      from
       dba_indexes i,dba_objects o,v$locked_object l,dba_tables t
      where 
       o.object_name = t.table_name
      and 
       i.index_name =v_index_name
      and 
       i.owner = v_index_owner
      and 
       o.owner= v_table_owner
      and 
       o.object_id = l.object_id
      and 
       i.table_name=t.table_name
     );
  /*INNER BLOCK for exception handling */
  BEGIN
   dbms_output.put_line(CHR(9));
   dbms_output.put_line(CHR(9));
   dbms_output.put_line('------------SESSIONS BLOCKING CLEANING PROCESS-------------');
   dbms_output.put_line('SID'||CHR(9)||'SERIAL'||CHR(9)||'STATUS'||CHR(9)||CHR(9)||'LAST_CALL_ET'||CHR(9)||CHR(9)||'COMMAND'||CHR(9)||CHR(9)||'SQL_ID'||CHR(9)||CHR(9)||'PREV_SQL_ID'||CHR(9)||CHR(9)||'MODULE'||CHR(9)||CHR(9)||'MACHINE');   
       
   for i in blocking_sid.first ..blocking_sid.last 
    loop
     dbms_output.put_line(blocking_sid(i)||CHR(9)||blocking_serial(i)||CHR(9)||blocking_status(i)||CHR(9)||CHR(9)||blocking_last_call_et(i)||CHR(9)||CHR(9)||blocking_command(i)||CHR(9)||CHR(9)||blocking_sql_id(i)||CHR(9)||CHR(9)||blocking_prev_sql_id(i)||CHR(9)||CHR(9)||blocking_module(i)||CHR(9)||blocking_machine(i));
     dbms_output.put_line(CHR(9));
    end loop;
   select event,total_waits,total_timeouts,time_waited,average_wait,max_wait into v_event,v_total_waits,v_total_timeouts,v_time_waited,v_average_wait,v_max_wait from v$session_event where sid=mysid and event='index (re)build online cleanup';
   dbms_output.put_line(chr(9));
   dbms_output.put_line('------------CLEANUP PROCESS TIMOUT-------------');
   dbms_output.put_line('EVENT'||CHR(9)||CHR(9)||CHR(9)||CHR(9)||chr(9)||'TOTAL WAITS'||CHR(9)||CHR(9)||'TOTAL TIMEOUTS'||CHR(9)||CHR(9)||'TIME WAITED'||CHR(9)||CHR(9)||'AVG WAIT'||CHR(9)||CHR(9)||'MAX WAIT');
      dbms_output.put_line(v_event||CHR(9)||CHR(9)||v_total_waits||CHR(9)||CHR(9)||CHR(9)||v_total_timeouts||CHR(9)||CHR(9)||CHR(9)||v_time_waited||CHR(9)||CHR(9)||CHR(9)||v_average_wait||CHR(9)||CHR(9)||CHR(9)||v_max_wait);
  EXCEPTION
   WHEN OTHERS THEN
    dbms_output.put_line(CHR(9));
    dbms_output.put_line('NO POTENTIAL BLOCKERS FOUND. Cleaning GOING ON SMOOTHLY');
    dbms_output.put_line(CHR(9));
    dbms_output.put_line(CHR(9));
  END;  
 end if;
 dbms_output.put_line(CHR(9)); 
EXCEPTION
 WHEN OTHERS THEN
 dbms_output.put_line(CHR(9)); 
 dbms_output.put_line('UNKNOWN CONDITION. CHECK IF JOB COMPLETES.');
 dbms_output.put_line(CHR(9));  
end;
/