set termout off

set linesize 90

set pagesize 20

ttitle center 'PDHC - A Quick Health Check' skip 2

btitle center '<span style="background-color:#c90421;color:#ffffff;border:1px solid black;">Confidential</span>'

set markup html on spool on entmap off

spool DB_Detail_status.html

COLUMN current_instance NEW_VALUE current_instance NOPRINT;

SELECT rpad(instance_name, 17) current_instance FROM v$instance;

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

set linesize 400 pagesize 400

SET TERMOUT ON;

PROMPT

PROMPT

PROMPT~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PROMPT------ /) (\

PROMPT------ .-._((,~~.))_.-,

PROMPT------ `-. @@ ,-'

PROMPT------ / ,o--o. \

PROMPT------ ( ( .__. ) )

PROMPT------ ) `----' (

PROMPT------ / \

PROMPT------ / \

PROMPT------ / \

PROMPT------ "The Silly Cow"

PROMPT----- Script: Healthcheck.sql (pdhc.sql)

PROMPT----- Author: Prashant 'The FatDBA'

PROMPT----- Cat: Performance Management and Issue Identification

PROMPT----- Version: V1.2 (Date: 04-02-2020)

PROMPT-----

PROMPT-----

PROMPT-----

PROMPT~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PROMPT +------------------------------------------------------------------------+

PROMPT | Report : Average Number of active sessions |

PROMPT | This is not RAC aware script |

PROMPT | Description: This number gives you an idea about the database workload |

PROMPT | or business. Higer this number, more is the database busy doing work on|

PROMPT | specific node |

PROMPT | Instance : &current_instance |

PROMPT +------------------------------------------------------------------------+

select round((count(ash.sample_id) / ((CAST(end_time.sample_time AS DATE) - CAST(start_time.sample_time AS DATE))*24*60*60)),2) as AAS

from

(select min(sample_time) sample_time

from v$active_session_history ash

) start_time,

(select max(sample_time) sample_time

from v$active_session_history

) end_time,

v$active_session_history ash

where ash.sample_time between start_time.sample_time and end_time.sample_time

group by end_time.sample_time,start_time.sample_time;

PROMPT +------------------------------------------------------------------------+

PROMPT | Report : Load Profile of any database |

PROMPT | This is not RAC aware script |

PROMPT | Description: This section contains same stats what you will see anytime|

PROMPT | in AWR of database. Few of the imp sections are |

PROMPT | DB Block Changes Per Txn, Average Active Sessions, Executions Per Sec |

PROMPT | User Calls Per Sec, Physical Writes Per Sec, Physical Reads Per Txn etc|

PROMPT | Instance : &current_instance |

PROMPT +------------------------------------------------------------------------+

SELECT

metric_name, inst_id

, ROUND(value,2) w_metric_value

, metric_unit

FROM

gv$sysmetric

WHERE

metric_name IN (

'Average Active Sessions'

, 'Average Synchronous Single-Block Read Latency'

, 'CPU Usage Per Sec'

, 'Background CPU Usage Per Sec'

, 'DB Block Changes Per Txn'

, 'Executions Per Sec'

, 'Host CPU Usage Per Sec'

, 'I/O Megabytes per Second'

, 'I/O Requests per Second'

, 'Logical Reads Per Txn'

, 'Logons Per Sec'

, 'Network Traffic Volume Per Sec'

, 'Physical Reads Per Sec'

, 'Physical Reads Per Txn'

, 'Physical Writes Per Sec'

, 'Redo Generated Per Sec'

, 'Redo Generated Per Txn'

, 'Response Time Per Txn'

, 'SQL Service Response Time'

, 'Total Parse Count Per Txn'

, 'User Calls Per Sec'

, 'User Transaction Per Sec'

)

AND group_id = 2 -- get last 60 sec metrics

ORDER BY

metric_name, inst_id

/

PROMPT +------------------------------------------------------------------------+

PROMPT | Report : Sesions Waiting |

PROMPT | Desc: The entries that are shown at the top are the sessions that have |

PROMPT | waited the longest amount of time that are waiting for non-idle wait |

PROMPT | events (event column).

PROMPT | This is RAC aware script |

PROMPT +------------------------------------------------------------------------+

set numwidth 15

set heading on

column state format a7 tru

column event format a25 tru

column last_sql format a40 tru

select sw.inst_id, sa.sql_id,sw.sid, sw.state, sw.event, sw.seconds_in_wait seconds,

sw.p1, sw.p2, sw.p3, sa.sql_text last_sql

from gv$session_wait sw, gv$session s, gv$sqlarea sa

where sw.event not in

('rdbms ipc message','smon timer','pmon timer',

'SQL*Net message from client','lock manager wait for remote message',

'ges remote message', 'gcs remote message', 'gcs for action', 'client message',

'pipe get', 'null event', 'PX Idle Wait', 'single-task message',

'PX Deq: Execution Msg', 'KXFQ: kxfqdeq - normal deqeue',

'listen endpoint status','slave wait','wakeup time manager')

and sw.seconds_in_wait > 0

and (sw.inst_id = s.inst_id and sw.sid = s.sid)

and (s.inst_id = sa.inst_id and s.sql_address = sa.address)

order by seconds desc;

PROMPT +------------------------------------------------------------------------+

PROMPT | Report : Top 5 SQL statements in the past one hour |

PROMPT | This is RAC aware script |

PROMPT | Description: Overall top SQLs on the basis on time waited in DB |

PROMPT | This is sorted on the basis of the time each one of them spend in DB |

PROMPT | Instance : &current_instance |

PROMPT +------------------------------------------------------------------------+

select * from (

select active_session_history.sql_id,

dba_users.username,

sqlarea.sql_text,

sum(active_session_history.wait_time +

active_session_history.time_waited) ttl_wait_time

from gv$active_session_history active_session_history,

gv$sqlarea sqlarea,

dba_users

where

active_session_history.sample_time between sysdate - 1/24 and sysdate

and active_session_history.sql_id = sqlarea.sql_id

and active_session_history.user_id = dba_users.user_id

group by active_session_history.sql_id,sqlarea.sql_text, dba_users.username

order by 4 desc )

where rownum < 6;

PROMPT +------------------------------------------------------------------------+

PROMPT | Report : Top10 SQL statements present in cache (elapsed time) |

PROMPT | This is RAC aware script |

PROMPT | Description: Overall top SQLs on the basis on elapsed time spend in DB |

PROMPT | Look out for ways to reduce elapsed time, check if its waiting on some-|

PROMPT | thing or other issues behind the high run time of query.

PROMPT | Instance : &current_instance |

PROMPT +------------------------------------------------------------------------+

select rownum as rank, a.*

from (

select elapsed_Time/1000000 elapsed_time,

executions, inst_id,

elapsed_Time / (1000000 * decode(executions,0,1, executions) ) etime_per_exec,

buffer_gets,

disk_reads,

cpu_time

hash_value, sql_id,

sql_text

from gv$sqlarea

where elapsed_time/1000000 > 5

order by etime_per_exec desc) a

where rownum < 11

/

PROMPT +------------------------------------------------------------------------+

PROMPT | Report : Top 5 SQL statements present in cache (PIOs or Disk Reads) |

PROMPT | This output is sorted on the basis of TOTAL DISK READS |

PROMPT | This is RAC aware script |

PROMPT | Description: Overall top SQLs on the basis on Physical Reads or D-Reads|

PROMPT | Most probably queries coming under this section are suffering from Full|

PROMPT | Table Scans (FTS) or DB File Scattered Read (User IO) Waits. Look for |

PROMPT | options if Index can help. Run SQL Tuning Advisories or do manual check|

PROMPT | Instance : &current_instance |

PROMPT +------------------------------------------------------------------------+

select rownum as rank, a.*

from (

select disk_reads, inst_id,

executions,

disk_reads / decode(executions,0,1, executions) reads_per_exec,

hash_value,

sql_text

from gv$sqlarea

where disk_reads > 10000

order by disk_reads desc) a

where rownum < 11

/

PROMPT +------------------------------------------------------------------------+

PROMPT | Report : Top 5 SQL statements present in cache (PIOs or Disk Reads) |

PROMPT | This output is sorted on the basis of DISK READS PER EXEC |

PROMPT | This is RAC aware script |

PROMPT | Description: Overall top SQLs on the basis on Physical Reads or D-Reads|

PROMPT | Most probably queries coming under this section are suffering from Full|

PROMPT | Table Scans (FTS) or DB File Scattered Read (User IO) Waits. Look for |

PROMPT | options if Index can help. Run SQL Tuning Advisories or do manual check|

PROMPT | Instance : &current_instance |

PROMPT +------------------------------------------------------------------------+

select rownum as rank, a.*

from (

select disk_reads, inst_id,

executions,

disk_reads / decode(executions,0,1, executions) reads_per_exec,

hash_value,

sql_text

from gv$sqlarea

where disk_reads > 10000

order by reads_per_exec desc) a

where rownum < 11

/

PROMPT +------------------------------------------------------------------------+

PROMPT | Report : Top 10 SQL statements present in cache (LIOs or BufferReads)|

PROMPT | Sorted on the basis of TOTAL BUFFER GETS |

PROMPT | This is RAC aware script |

PROMPT | Description: Overall top SQLs on the basis on Memmory Reads or L-Reads |

PROMPT | Most probably queries coming under this section are the ones doing FTS |

PROMPT | and might be waiting for any latch/Mutex to gain access on block. Pleas|

PROMPT | check the value of column 'gets_per_exec' that means average memory |

PROMPT | reads per execution. |

PROMPT | Instance : &current_instance |

PROMPT +------------------------------------------------------------------------+

select rownum as rank, a.*

from (

select buffer_gets, inst_id,

executions,

buffer_gets/ decode(executions,0,1, executions) gets_per_exec,

hash_value,

sql_text

from gv$sqlarea

where buffer_gets > 50000

order by buffer_gets desc) a

where rownum < 11

/

PROMPT +------------------------------------------------------------------------+

PROMPT | Report : Top 10 SQL statements present in cache (LIOs or BufferReads)|

PROMPT | Sorted on the basis of BUFFER GETS PER EXECUTION |

PROMPT | This is RAC aware script |

PROMPT | Description: Overall top SQLs on the basis on Memmory Reads or L-Reads |

PROMPT | Most probably queries coming under this section are the ones doing FTS |

PROMPT | and might be waiting for any latch/Mutex to gain access on block. Pleas|

PROMPT | check the value of column 'gets_per_exec' that means average memory |

PROMPT | reads per execution. |

PROMPT | Instance : &current_instance |

PROMPT +------------------------------------------------------------------------+

select rownum as rank, a.*

from (

select buffer_gets, inst_id,

executions,

buffer_gets/ decode(executions,0,1, executions) gets_per_exec,

hash_value,

sql_text

from gv$sqlarea

where buffer_gets > 50000

order by gets_per_exec desc) a

where rownum < 11

/

PROMPT +----------------------------------------------------------------------------------------------+

PROMPT | Report : SQLs with the highest concurrency waits (possible latch / mutex-related) |

PROMPT | This is RAC aware script |

PROMPT | Description: Queries sorted basis on concurrency events i.e. Latching or Mutex waits |

PROMPT | look out for Conc Time (ms) and SQL Conc Time% columns.

PROMPT | Instance : &current_instance |

PROMPT +----------------------------------------------------------------------------------------------+

column sql_text format a40 heading "SQL Text"

column con_time_ms format 99,999,999 heading "Conc Time (ms)"

column con_time_pct format 999.99 heading "SQL Conc | Time%"

column pct_of_con_time format 999.99 heading "% Tot | ConcTime"

WITH sql_conc_waits AS

(SELECT sql_id, SUBSTR (sql_text, 1, 80) sql_text, inst_id,

concurrency_wait_time / 1000 con_time_ms,

elapsed_time,

ROUND (concurrency_wait_Time * 100 /

elapsed_time, 2) con_time_pct,

ROUND (concurrency_wait_Time * 100 /

SUM (concurrency_wait_Time) OVER (), 2) pct_of_con_time,

RANK () OVER (ORDER BY concurrency_wait_Time DESC)

FROM gv$sql

WHERE elapsed_time> 0)

SELECT sql_text, con_time_ms, con_time_pct, inst_id,

pct_of_con_time

FROM sql_conc_waits

WHERE rownum <= 10

;

PROMPT +---------------------------------------------------------------------------------------+

PROMPT | Report : Current CPU Intensive statements (current 15) |

PROMPT | This is RAC aware script |

PROMPT | Instance : &current_instance |

PROMPT | Description: This gives you expensive SQLs which are in run right now and consuming |

PROMPT | huge CPU seconds. Check column CPU_USAGE_SECONDS and investigate using SQLID |

PROMPT +---------------------------------------------------------------------------------------+

set pages 1000

set lines 1000

col OSPID for a06

col SID for 99999

col SERIAL# for 999999

col SQL_ID for a14

col USERNAME for a15

col PROGRAM for a20

col MODULE for a18

col OSUSER for a10

col MACHINE for a25

select * from (

select p.spid "ospid",

(se.SID),ss.serial#,ss.inst_id,ss.SQL_ID,ss.username,ss.program,ss.module,ss.osuser,ss.MACHINE,ss.status,

se.VALUE/100 cpu_usage_seconds

from

gv$session ss,

gv$sesstat se,

gv$statname sn,

gv$process p

where

se.STATISTIC# = sn.STATISTIC#

and

NAME like '%CPU used by this session%'

and

se.SID = ss.SID

and ss.username !='SYS' and

ss.status='ACTIVE'

and ss.username is not null

and ss.paddr=p.addr and value > 0

order by se.VALUE desc)

where rownum <16;

PROMPT +---------------------------------------------------------------------------------------+

PROMPT | Report : Top 10 CPU itensive queries based on total cpu seconds spend |

PROMPT | This is RAC aware script |

PROMPT | Instance : &current_instance |

PROMPT +---------------------------------------------------------------------------------------+

col SQL_TEXT for a99

select rownum as rank, a.*

from (

select cpu_time/1000000 cpu_time, inst_id,

executions,

buffer_gets,

disk_reads,

cpu_time

hash_value,

sql_text

from gv$sqlarea

where cpu_time/1000000 > 5

order by cpu_time desc) a

where rownum < 11

/

PROMPT +---------------------------------------------------------------------------------------+

PROMPT | Report : Top 10 CPU itensive queries based on total cpu seconds spend per execution |

PROMPT | This is RAC aware script |

PROMPT | Instance : &current_instance |

PROMPT +---------------------------------------------------------------------------------------+

select rownum as rank, a.*

from (

select cpu_time/1000000 cpu_time, inst_id,

executions,

cpu_time / (1000000 * decode(executions,0,1, executions)) ctime_per_exec,

buffer_gets,

disk_reads,

cpu_time

hash_value,

sql_text

from gv$sqlarea

where cpu_time/1000000 > 5

order by ctime_per_exec desc) a

where rownum < 11

/

PROMPT +------------------------------------------------------------------------+

PROMPT | Report : LONG OPS |

PROMPT | This is RAC aware script |

PROMPT | Description: This view displays the status of various operations that |

PROMPT | run for longer than 6 seconds (in absolute time). These operations |

PROMPT | currently include many backup and recovery functions, statistics gather|

prompt | , and query execution, and more operations are added for every OracleRE|

PROMPT | Instance : &current_instance |

PROMPT +------------------------------------------------------------------------+

set lines 120

cle bre

col sid form 99999

col start_time head "Start|Time" form a12 trunc

col opname head "Operation" form a12 trunc

col target head "Object" form a30 trunc

col totalwork head "Total|Work" form 9999999999 trunc

col Sofar head "Sofar" form 9999999999 trunc

col elamin head "Elapsed|Time|(Mins)" form 99999999 trunc

col tre head "Time|Remain|(Mins)" form 999999999 trunc

select sid,serial#,to_char(start_time,'dd-mon:hh24:mi') start_time,

opname,target,totalwork,sofar,(elapsed_Seconds/60) elamin,

time_remaining tre

from v$session_longops

where totalwork <> SOFAR

order by 9 desc;

/

PROMPT +----------------------------------------------------------------------------------------------+

PROMPT | Report : IO wait breakdown in the datbase during runtime of this script |

PROMPT | This is RAC aware script |

PROMPT | Desc: Look for last three cols, TOTAL_WAITS, TIME_WAITED_SECONDS and PCT. Rank matters here |

PROMPT | Instance : &current_instance |

PROMPT +----------------------------------------------------------------------------------------------+

column wait_type format a35

column lock_name format a12

column time_waited_seconds format 999,999.99

column pct format 99.99

set linesize 400 pagesize 400

WITH system_event AS

(SELECT CASE

WHEN event LIKE 'direct path% temp' THEN

'direct path read / write temp'

WHEN event LIKE 'direct path%' THEN

'direct path read / write non-temp'

WHEN wait_class = 'User I / O' THEN

event

ELSE wait_class

END AS wait_type, e. *

FROM gv$system_event e)

SELECT wait_type, SUM (total_waits) total_waits,

ROUND (SUM (time_waited_micro) / 1000000, 2) time_waited_seconds,

ROUND (SUM (time_waited_micro)

* 100

/ SUM (SUM (time_waited_micro)) OVER (), 2)

pct

FROM (SELECT wait_type, event, total_waits, time_waited_micro

FROM system_event e

UNION

SELECT 'CPU', stat_name, NULL, VALUE

FROM gv$sys_time_model

WHERE stat_name IN ('background cpu time', 'CPU DB')) l

WHERE wait_type <> 'Idle'

GROUP BY wait_type

ORDER BY 4 DESC

/

PROMPT +------------------------------------------------------------------------+

PROMPT | Report : Current waits and counts |

PROMPT | This is RAC aware script |

PROMPT | Desc: This script shows what all sessions waits currently their count|

PROMPT | Instance : &current_instance |

PROMPT +------------------------------------------------------------------------+

select event, state, inst_id, count(*) from gv$session_wait group by event, state, inst_id order by 4 desc;

set numwidth 10

column event format a25 tru

select inst_id, event, time_waited, total_waits, total_timeouts

from (select inst_id, event, time_waited, total_waits, total_timeouts

from gv$system_event where event not in ('rdbms ipc message','smon timer',

'pmon timer', 'SQL*Net message from client','lock manager wait for remote message',

'ges remote message', 'gcs remote message', 'gcs for action', 'client message',

'pipe get', 'null event', 'PX Idle Wait', 'single-task message',

'PX Deq: Execution Msg', 'KXFQ: kxfqdeq - normal deqeue',

'listen endpoint status','slave wait','wakeup time manager')

order by time_waited desc)

where rownum < 11

order by time_waited desc;

PROMPT +------------------------------------------------------------------------+

PROMPT | Report : IN-FLIGHT TRANSACTION |

PROMPT | This is RAC aware script |

PROMPT | Desc: This output gives a glimpse of what all running/waiting in DB |

PROMPT | Instance : &current_instance |

PROMPT +------------------------------------------------------------------------+

set linesize 400 pagesize 400

select x.inst_id,x.sid

,x.serial#

,x.username

,x.sql_id

,plan_hash_value

,sqlarea.DISK_READS

,sqlarea.BUFFER_GETS

,sqlarea.ROWS_PROCESSED

,x.event

,x.osuser

,x.status

,x.BLOCKING_SESSION_STATUS

,x.BLOCKING_INSTANCE

,x.BLOCKING_SESSION

,x.process

,x.machine

,x.OSUSER

,x.program

,x.module

,x.action

,TO_CHAR(x.LOGON_TIME, 'MM-DD-YYYY HH24:MI:SS') logontime

,x.LAST_CALL_ET

--,x.BLOCKING_SESSION_STATUS

,x.SECONDS_IN_WAIT

,x.state

,sql_text,

ltrim(to_char(floor(x.LAST_CALL_ET/3600), '09')) || ':'

|| ltrim(to_char(floor(mod(x.LAST_CALL_ET, 3600)/60), '09')) || ':'

|| ltrim(to_char(mod(x.LAST_CALL_ET, 60), '09')) RUNNING_SINCE

from gv$sqlarea sqlarea

,gv$session x

where x.sql_hash_value = sqlarea.hash_value

and x.sql_address = sqlarea.address

and sql_text not like '%select x.inst_id,x.sid ,x.serial# ,x.username ,x.sql_id ,plan_hash_value ,sqlarea.DISK_READS%'

and x.status='ACTIVE'

and x.USERNAME is not null

and x.SQL_ADDRESS = sqlarea.ADDRESS

and x.SQL_HASH_VALUE = sqlarea.HASH_VALUE

order by RUNNING_SINCE desc;

PROMPT +------------------------------------------------------------------------+

PROMPT | Report : Temp or Sort segment usage |

PROMPT | This is RAC aware script |

PROMPT | Desc: Queies consuming huge sort area from last 2 hrs and more than 5GB| |

PROMPT | Instance : &current_instance |

PROMPT +------------------------------------------------------------------------+

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

select sql_id,max(TEMP_SPACE_ALLOCATED)/(1024*1024*1024) gig

from DBA_HIST_ACTIVE_SESS_HISTORY

where

sample_time > sysdate - (120/1440) and

TEMP_SPACE_ALLOCATED > (5*1024*1024*1024)

group by sql_id order by gig desc;

PROMPT +------------------------------------------------------------------------+

PROMPT | Report : Who is doing what with TEMP segments or tablespace |

PROMPT | This is RAC aware script |

PROMPT | Desc: Look for cols usage_mb and sql_id and sql_text and username |

PROMPT | Instance : &current_instance |

PROMPT +------------------------------------------------------------------------+

SELECT sysdate "TIME_STAMP", vsu.username, vs.sid, vp.spid, vs.sql_id, vst.sql_text, vsu.tablespace,

sum_blocks*dt.block_size/1024/1024 usage_mb

FROM

(

SELECT username, sqladdr, sqlhash, sql_id, tablespace, session_addr,

-- sum(blocks)*8192/1024/1024 "USAGE_MB",

sum(blocks) sum_blocks

FROM gv$sort_usage

HAVING SUM(blocks)> 1000

GROUP BY username, sqladdr, sqlhash, sql_id, tablespace, session_addr

) "VSU",

gv$sqltext vst,

gv$session vs,

gv$process vp,

dba_tablespaces dt

WHERE vs.sql_id = vst.sql_id

-- AND vsu.sqladdr = vst.address

-- AND vsu.sqlhash = vst.hash_value

AND vsu.session_addr = vs.saddr

AND vs.paddr = vp.addr

AND vst.piece = 0

AND dt.tablespace_name = vsu.tablespace

order by usage_mb;

SET TERMOUT OFF;

COLUMN current_instance NEW_VALUE current_instance NOPRINT;

SELECT rpad(instance_name, 17) current_instance FROM v$instance;

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

set linesize 400 pagesize 400

SET TERMOUT ON;

PROMPT +------------------------------------------------------------------------+

PROMPT | Report : Table Level Locking session details |

PROMPT | This is RAC aware script and will show all instances of TM Level RLCon |

PROMPT | Desc: This output shows what all active sessions waiting on TM Content.|

PROMPT | Instance : &current_instance |

PROMPT +------------------------------------------------------------------------+

set linesize 400 pagesize 400

select x.inst_id,x.sid

,x.serial#

,x.username

,x.sql_id

,x.event

,x.osuser

,x.status

,x.process

,x.machine

,x.OSUSER

,x.program

,x.module

,x.action

,TO_CHAR(x.LOGON_TIME, 'MM-DD-YYYY HH24:MI:SS') logontime

,x.LAST_CALL_ET

,x.SECONDS_IN_WAIT

,x.state

,sql_text,

ltrim(to_char(floor(x.LAST_CALL_ET/3600), '09')) || ':'

|| ltrim(to_char(floor(mod(x.LAST_CALL_ET, 3600)/60), '09')) || ':'

|| ltrim(to_char(mod(x.LAST_CALL_ET, 60), '09')) RUNT

from gv$sqlarea sqlarea

,gv$session x

where x.sql_hash_value = sqlarea.hash_value

and x.sql_address = sqlarea.address

and x.status='ACTIVE'

and x.event like '%TM - contention%'

and x.USERNAME is not null

and x.SQL_ADDRESS = sqlarea.ADDRESS

and x.SQL_HASH_VALUE = sqlarea.HASH_VALUE

order by runt desc;

PROMPT +------------------------------------------------------------------------+

PROMPT | Report : Row Level Locking session details | |

PROMPT | This is RAC aware script and will show all instances of TX Level RLCon |

PROMPT | Desc: This output shows what all active sessions waiting on TX Content.|

PROMPT | Instance : &current_instance |

PROMPT +------------------------------------------------------------------------+

set linesize 400 pagesize 400

select x.inst_id,x.sid

,x.serial#

,x.username

,x.sql_id

,x.event

,x.osuser

,x.status

,x.process

,x.machine

,x.OSUSER

,x.program

,x.module

,x.action

,TO_CHAR(x.LOGON_TIME, 'MM-DD-YYYY HH24:MI:SS') logontime

,x.LAST_CALL_ET

,x.SECONDS_IN_WAIT

,x.state

,sql_text,

ltrim(to_char(floor(x.LAST_CALL_ET/3600), '09')) || ':'

|| ltrim(to_char(floor(mod(x.LAST_CALL_ET, 3600)/60), '09')) || ':'

|| ltrim(to_char(mod(x.LAST_CALL_ET, 60), '09')) RUNT

from gv$sqlarea sqlarea

,gv$session x

where x.sql_hash_value = sqlarea.hash_value

and x.sql_address = sqlarea.address

and x.status='ACTIVE'

and x.event like '%row lock contention%'

and x.USERNAME is not null

and x.SQL_ADDRESS = sqlarea.ADDRESS

and x.SQL_HASH_VALUE = sqlarea.HASH_VALUE

order by runt desc;

PROMPT +------------------------------------------------------------------------+

PROMPT | Report : Blocking Tree |

PROMPT | This output helps a DBA to identify all parent lockers in a pedigree |

PROMPT | Desc: Creates a ASCII tree or graph to show parent and child lockers |

PROMPT | Instance : &current_instance |

PROMPT +------------------------------------------------------------------------+

col LOCK_TREE for a10

with lk as (select blocking_instance||'.'||blocking_session blocker, inst_id||'.'||sid waiter

from gv$session where blocking_instance is not null and blocking_session is not null and username is not null)

select lpad(' ',2*(level-1))||waiter lock_tree from

(select * from lk

union all

select distinct 'root', blocker from lk

where blocker not in (select waiter from lk))

connect by prior waiter=blocker start with blocker='root';

PROMPT +------------------------------------------------------------------------+

PROMPT | Report : TX Row Lock Contention Details |

PROMPT | This report or result shows some extra and imp piece of data |

PROMPT | Instance : &current_instance |

PROMPT +------------------------------------------------------------------------+

col LOCK_MODE for a10

col OBJECT_NAME for a30

col SID_SERIAL for a19

col OSUSER for a9

col USER_STATUS for a14

SELECT DECODE (l.BLOCK, 0, 'Waiting', 'Blocking ->') user_status

,CHR (39) || s.SID || ',' || s.serial# || CHR (39) sid_serial

,(SELECT instance_name FROM gv$instance WHERE inst_id = l.inst_id)

conn_instance

,s.SID

,s.PROGRAM

,s.inst_id

,s.osuser

,s.machine

,DECODE (l.TYPE,'RT', 'Redo Log Buffer','TD', 'Dictionary'

,'TM', 'DML','TS', 'Temp Segments','TX', 'Transaction'

,'UL', 'User','RW', 'Row Wait',l.TYPE) lock_type

--,id1

--,id2

,DECODE (l.lmode,0, 'None',1, 'Null',2, 'Row Share',3, 'Row Excl.'

,4, 'Share',5, 'S/Row Excl.',6, 'Exclusive'

,LTRIM (TO_CHAR (lmode, '990'))) lock_mode

,ctime

--,DECODE(l.BLOCK, 0, 'Not Blocking', 1, 'Blocking', 2, 'Global') lock_status

,object_name

FROM

gv$lock l

JOIN

gv$session s

ON (l.inst_id = s.inst_id

AND l.SID = s.SID)

JOIN gv$locked_object o

ON (o.inst_id = s.inst_id

AND s.SID = o.session_id)

JOIN dba_objects d

ON (d.object_id = o.object_id)

WHERE (l.id1, l.id2, l.TYPE) IN (SELECT id1, id2, TYPE

FROM gv$lock

WHERE request > 0)

ORDER BY id1, id2, ctime DESC;

PROMPT +------------------------------------------------------------------------+

PROMPT | Report : What is blocking what ..... |

PROMPT | This is that old and popular simple output that everybody knows |

PROMPT | Instance : &current_instance |

PROMPT +------------------------------------------------------------------------+

select l1.sid, ' IS BLOCKING ', l2.sid

from gv$lock l1, gv$lock l2 where l1.block =1 and l2.request > 0

and l1.id1=l2.id1

and l1.id2=l2.id2;

PROMPT +------------------------------------------------------------------------+

PROMPT | Report : Some more on locking |

PROMPT | Little more formatted data that abive output |

PROMPT | Instance : &current_instance |

PROMPT +------------------------------------------------------------------------+

col BLOCKING_STATUS for a120

select s2.inst_id,s1.username || '@' || s1.machine

|| ' ( SID=' || s1.sid || ' ) is blocking '

|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status

from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2

where s1.sid=l1.sid and s2.sid=l2.sid and s1.inst_id=l1.inst_id and s2.inst_id=l2.inst_id

and l1.BLOCK=1 and l2.request > 0

and l1.id1 = l2.id1

and l2.id2 = l2.id2

order by s1.inst_id;

PROMPT +------------------------------------------------------------------------+

PROMPT | Report : More on locks to read and analyze |

PROMPT | Thidata you can use for your deep drill down |

PROMPT | Instance : &current_instance |

PROMPT +------------------------------------------------------------------------+

col BLOCKER for a8

col WAITER for a10

col LMODE for a14

col REQUEST for a15

SELECT sid,

TYPE,

DECODE( block, 0, 'NO', 'YES' ) BLOCKER,

DECODE( request, 0, 'NO', 'YES' ) WAITER,

decode(LMODE,1,' ',2,'RS',3,'RX',4,'S',5,'SRX',6,'X','NONE') lmode,

decode(REQUEST,1,' ',2,'RS',3,'RX',4,'S',5,'SRX',6,'X','NONE') request,

TRUNC(CTIME/60) MIN ,

ID1,

ID2,

block

FROM gv$lock

where request > 0 OR block =1;

PROMPT +---------------------------------------------------------------------------------------+

PROMPT | Report : Database Objects Experienced the Most Number of Waits in the Past One Hour |

PROMPT | This is RAC aware script |

PROMPT | Description: Look for EVENT its getting and last column TTL_WAIT_TIME, time waited |

PROMPT | Instance : &current_instance |

PROMPT +---------------------------------------------------------------------------------------+

col event format a40

col object_name format a40

select * from

(

select dba_objects.object_name,

dba_objects.object_type,

active_session_history.event,

sum(active_session_history.wait_time +

active_session_history.time_waited) ttl_wait_time

from gv$active_session_history active_session_history,

dba_objects

where

active_session_history.sample_time between sysdate - 1/24 and sysdate

and active_session_history.current_obj# = dba_objects.object_id

group by dba_objects.object_name, dba_objects.object_type, active_session_history.event

order by 4 desc)

where rownum < 6;

PROMPT +------------------------------------------------------------------------+

PROMPT | Report : GLOBAL CACHE CR PERFORMANCE |

PROMPT | Desc: This shows the average latency of a consistent block request. |

PROMPT | AVG CR BLOCK RECEIVE TIME should typically be about 15 milliseconds |

PROMPT | This is RAC aware script |

PROMPT +------------------------------------------------------------------------+

set numwidth 20

column "AVG CR BLOCK RECEIVE TIME (ms)" format 9999999.9

select b1.inst_id, b2.value "GCS CR BLOCKS RECEIVED",

b1.value "GCS CR BLOCK RECEIVE TIME",

((b1.value / b2.value) * 10) "AVG CR BLOCK RECEIVE TIME (ms)"

from gv$sysstat b1, gv$sysstat b2

where b1.name = 'global cache cr block receive time' and

b2.name = 'global cache cr blocks received' and b1.inst_id = b2.inst_id

or b1.name = 'gc cr block receive time' and

b2.name = 'gc cr blocks received' and b1.inst_id = b2.inst_id ;

PROMPT +----------------------------------------------------------------------------------------------+

PROMPT | Report : RAC Lost blocks report plus GC specific events |

PROMPT | This is RAC aware script |

PROMPT | Desc: This shows all RAC specific metrics like block lost, blocks served and recieved |

PROMPT | Instance : &current_instance |

PROMPT +----------------------------------------------------------------------------------------------+

col name format a30

SELECT name, SUM (VALUE) value

FROM gv$sysstat

WHERE name LIKE 'gc% lost'

OR name LIKE 'gc% received'

OR name LIKE 'gc% served'

GROUP BY name

ORDER BY name;

PROMPT +------------------------------------------------------------------------+

PROMPT | Report : Wait Chains in RAC systems |

PROMPT | Desc: This will show you the top 100 wait chain processes at any given |

PROMPT | point.You should look for number of waiters and blocking process |

PROMPT | This is RAC aware script and only works with 11g and up versions |

PROMPT +------------------------------------------------------------------------+

set pages 1000

set lines 120

column w_proc format a50 tru

column instance format a20 tru

column inst format a28 tru

column wait_event format a50 tru

column p1 format a16 tru

column p2 format a16 tru

column p3 format a15 tru

column Seconds format a50 tru

column sincelw format a50 tru

column blocker_proc format a50 tru

column waiters format a50 tru

column chain_signature format a100 wra

column blocker_chain format a100 wra

SELECT *

FROM (SELECT 'Current Process: '||osid W_PROC, 'SID '||i.instance_name INSTANCE,

'INST #: '||instance INST,'Blocking Process: '||decode(blocker_osid,null,'<none>',blocker_osid)||

' from Instance '||blocker_instance BLOCKER_PROC,'Number of waiters: '||num_waiters waiters,

'Wait Event: ' ||wait_event_text wait_event, 'P1: '||p1 p1, 'P2: '||p2 p2, 'P3: '||p3 p3,

'Seconds in Wait: '||in_wait_secs Seconds, 'Seconds Since Last Wait: '||time_since_last_wait_secs sincelw,

'Wait Chain: '||chain_id ||': '||chain_signature chain_signature,'Blocking Wait Chain: '||decode(blocker_chain_id,null,

'<none>',blocker_chain_id) blocker_chain

FROM v$wait_chains wc,

v$instance i

WHERE wc.instance = i.instance_number (+)

AND ( num_waiters > 0

OR ( blocker_osid IS NOT NULL

AND in_wait_secs > 10 ) )

ORDER BY chain_id,

num_waiters DESC)

WHERE ROWNUM < 101;

PROMPT +------------------------------------------------------------------------+

PROMPT | Report : Latch statistics 1 |

PROMPT | This is RAC aware script |

PROMPT | Instance : &current_instance |

PROMPT +------------------------------------------------------------------------+

select inst_id, name latch_name,

round((gets-misses)/decode(gets,0,1,gets),3) hit_ratio,

round(sleeps/decode(misses,0,1,misses),3) "SLEEPS/MISS"

from gv$latch

where round((gets-misses)/decode(gets,0,1,gets),3) < .99

and gets != 0

order by round((gets-misses)/decode(gets,0,1,gets),3);

PROMPT +------------------------------------------------------------------------+

PROMPT | Report : Latch status |

PROMPT | This is RAC aware script |

PROMPT | Desc: Please look for cols WAIT_TIME_SECONDS and WAIT_TIME |

PROMPT | Critical if both of the numbers are high |

PROMPT | Instance : &current_instance |

PROMPT +------------------------------------------------------------------------+

col NAME for a50

select v.*

from

(select

name, inst_id,

gets,

misses,

round(misses*100/(gets+1), 3) misses_gets_pct,

spin_gets,

sleep1,

wait_time,

round(wait_time/1000000) wait_time_seconds,

rank () over

(order by wait_time desc) as misses_rank

from

gv$latch

where gets + misses + sleep1 + wait_time > 0

order by

wait_time desc

) v

where

misses_rank <= 10;