DBA AWR Blocking Session Summary

Description
Categories: Diagnostic Pack, Enginatics, Toolkit - DBA
Repository: Github Columns: Seconds, Instance Number, Sid Serial#, User Name, Responsibility, Module Type, Module Name, Code, Code Line#, Sql Id ...
Summary of blocked and blocking sessions based on the active session history from the AWR.
The link to blocking sessions is deliberately nonunique without jointing to sample_id to increase the chance to fetch the blocking session's additional information such as module, action and client_id.
In some cases, such as row lock scenarios, the blocking session is idle and does not show up in the A ... 
Summary of blocked and blocking sessions based on the active session history from the AWR.
The link to blocking sessions is deliberately nonunique without jointing to sample_id to increase the chance to fetch the blocking session's additional information such as module, action and client_id.
In some cases, such as row lock scenarios, the blocking session is idle and does not show up in the ASH.

We recommend doing further analysis with a pivot in Excel rather than by SQL, as Excel's drag- and drop is a lot faster than typing commands manually.
   more
with y as (
select /*+ materialize*/ distinct
count(*) over (partition by
x.instance_number,
x.sid_serial#,
x.code,
x.code_line#,
x.sql_id,
x.plan_hash_value,
x.session_type,
x.session_state,
x.event,
x.wait_class,
x.time_model,
x.sql_plan_operation,
x.sql_plan_options,
x.object,
x.blocking_inst_id,
x.blocking_sid_serial#,
x.blocking_client_id,
x.blocking_action,
x.blocking_module,
x.blocking_program,
x.blocking_session_type,
x.blocking_machine,
x.machine,
x.command_type,
x.schema,
x.client_id,
x.action,
x.module,
x.program
) seconds,
x.instance_number,
x.sid_serial#,
x.code,
x.code_line#,
x.sql_id,
x.plan_hash_value,
x.session_type,
x.session_state,
x.event,
x.wait_class,
x.time_model,
x.sql_plan_operation,
x.sql_plan_options,
x.object,
x.blocking_inst_id,
x.blocking_sid_serial#,
x.blocking_client_id,
x.blocking_action,
x.blocking_module,
x.blocking_program,
x.blocking_session_type,
x.blocking_machine,
x.machine,
x.command_type,
x.schema,
x.client_id,
x.action,
x.module,
x.program
from
(
select
dhash.sample_time_ sample_time,
dhash.instance_number,
dhash.session_id||' - '||dhash.session_serial# sid_serial#,
dhash.client_id,
(select so.name from sys.obj$ so where gsa.program_id=so.obj#) code,
case when gsa.program_line#>0 then gsa.program_line# end code_line#,
dhash.sql_id,
dhash.sql_plan_hash_value plan_hash_value,
dhash.session_type,
dhash.session_state,
nvl(dhash.event,dhash.session_state)||
case
when dhash.event like 'enq%' and dhash.session_state='WAITING' then ' (mode='||bitand(dhash.p1,power(2,14)-1)||')'
when dhash.event in ('buffer busy waits','gc buffer busy','gc buffer busy acquire','gc buffer busy release') then ' ('||
coalesce(
(select vw.class from (select vw.class, rownum row_num from v$waitstat vw) vw where dhash.p3=vw.row_num),
(select decode(mod(bitand(dhash.p3,to_number('FFFF','XXXX'))-17,2),0,'undo header',1,'undo data','error') from dual)
)||')'
end event,
dhash.wait_class,
rtrim(
case when bitand(dhash.time_model,power(2,01))=power(2,01) then 'DBTIME ' end||
case when bitand(dhash.time_model,power(2,02))=power(2,02) then 'BACKGROUND ' end||
case when bitand(dhash.time_model,power(2,03))=power(2,03) then 'CONNECTION_MGMT ' end||
case when bitand(dhash.time_model,power(2,04))=power(2,04) then 'PARSE ' end||
case when bitand(dhash.time_model,power(2,05))=power(2,05) then 'FAILED_PARSE ' end||
case when bitand(dhash.time_model,power(2,06))=power(2,06) then 'NOMEM_PARSE ' end||
case when bitand(dhash.time_model,power(2,07))=power(2,07) then 'HARD_PARSE ' end||
case when bitand(dhash.time_model,power(2,08))=power(2,08) then 'NO_SHARERS_PARSE ' end||
case when bitand(dhash.time_model,power(2,09))=power(2,09) then 'BIND_MISMATCH_PARSE ' end||
case when bitand(dhash.time_model,power(2,10))=power(2,10) then 'SQL_EXECUTION ' end||
case when bitand(dhash.time_model,power(2,11))=power(2,11) then 'PLSQL_EXECUTION ' end||
case when bitand(dhash.time_model,power(2,12))=power(2,12) then 'PLSQL_RPC ' end||
case when bitand(dhash.time_model,power(2,13))=power(2,13) then 'PLSQL_COMPILATION ' end||
case when bitand(dhash.time_model,power(2,14))=power(2,14) then 'JAVA_EXECUTION ' end||
case when bitand(dhash.time_model,power(2,15))=power(2,15) then 'BIND ' end||
case when bitand(dhash.time_model,power(2,16))=power(2,16) then 'CURSOR_CLOSE ' end||
case when bitand(dhash.time_model,power(2,17))=power(2,17) then 'SEQUENCE_LOAD ' end||
case when bitand(dhash.time_model,power(2,18))=power(2,18) then 'INMEMORY_QUERY ' end||
case when bitand(dhash.time_model,power(2,19))=power(2,19) then 'INMEMORY_POPULATE ' end||
case when bitand(dhash.time_model,power(2,20))=power(2,20) then 'INMEMORY_PREPOPULATE ' end||
case when bitand(dhash.time_model,power(2,21))=power(2,21) then 'INMEMORY_REPOPULATE ' end||
case when bitand(dhash.time_model,power(2,22))=power(2,22) then 'INMEMORY_TREPOPULATE ' end||
case when bitand(dhash.time_model,power(2,23))=power(2,23) then 'TABLESPACE_ENCRYPTION ' end
) time_model,
dhash.sql_plan_operation,
dhash.sql_plan_options,
(select do.owner||'.'||do.object_name||' ('||do.object_type||')' from dba_objects do where dhash.current_obj#=do.object_id) object,
dhash.blocking_inst_id blocking_inst_id,
nvl2(dhash.blocking_session,dhash.blocking_session||' - '||dhash.blocking_session_serial#,null) blocking_sid_serial#,
dhash0.client_id blocking_client_id,
dhash0.action blocking_action,
dhash0.module blocking_module,
dhash0.program blocking_program,
dhash0.session_type blocking_session_type,
dhash0.machine blocking_machine,
dhash.machine,
lower(dhash.sql_opname) command_type,
du.username schema,
dhash.action