Saturday, May 31, 2025
waiteventcontributors.sql
WITH
events AS (
SELECT
SUBSTR(TRIM(h.sql_id||' '||h.program||' '||
CASE h.module WHEN h.program THEN NULL ELSE h.module END), 1, 128) source,
h.dbid,
COUNT(*) samples
FROM dba_hist_active_sess_history h,
dba_hist_snapshot s
WHERE h.wait_class = TRIM('&Event_Class') AND h.event = TRIM('&Event_Name')
AND h.snap_id BETWEEN &Begin_Snap AND &End_Snap
AND h.dbid = &dbid
AND s.snap_id = h.snap_id
AND s.dbid = h.dbid
AND s.instance_number = h.instance_number
GROUP BY
h.sql_id,
h.program,
h.module,
h.dbid
ORDER BY
3 DESC
),
total AS (
SELECT SUM(samples) samples,
SUM(CASE WHEN ROWNUM > 15 THEN samples ELSE 0 END) others
FROM events
)
SELECT e.source,
e.samples,
ROUND(100 * e.samples / t.samples, 1) percent,
(SELECT DBMS_LOB.SUBSTR(s.sql_text, 1000, 1) FROM dba_hist_sqltext s WHERE s.sql_id = SUBSTR(e.source, 1, 13) AND s.dbid = e.dbid AND ROWNUM = 1) sql_text
FROM events e,
total t
WHERE ROWNUM 0.1
UNION ALL
SELECT 'Others',
others samples,
ROUND(100 * others / samples, 1) percent,
NULL sql_text
FROM total
WHERE others > 0
AND ROUND(100 * others / samples, 1) > 0.1;
RACDIAG.SQL
-- NAME: RACDIAG.SQL
-- SYS OR INTERNAL USER, CATPARR.SQL ALREADY RUN, PARALLEL QUERY OPTION ON
-- ------------------------------------------------------------------------
-- AUTHOR:
-- Michael Polaski - Oracle Support Services
-- Copyright 2002, Oracle Corporation
-- ------------------------------------------------------------------------
-- PURPOSE:
-- This script is intended to provide a user friendly guide to troubleshoot
-- RAC hung sessions or slow performance scenerios. The script includes
-- information to gather a variety of important debug information to determine
-- the cause of a RAC session level hang. The script will create a file
-- called racdiag_.out in your local directory while dumping hang analyze
-- dumps in the user_dump_dest(s) and background_dump_dest(s) on all nodes.
--
-- ------------------------------------------------------------------------
-- DISCLAIMER:
-- This script is provided for educational purposes only. It is NOT
-- supported by Oracle World Wide Technical Support.
-- The script has been tested and appears to work as intended.
-- You should always run new scripts on a test instance initially.
-- ------------------------------------------------------------------------
-- Script output is as follows:
set echo off
set feedback off
column timecol new_value timestamp
column spool_extension new_value suffix
select to_char(sysdate,'Mondd_hhmi') timecol,
'.out' spool_extension from sys.dual;
column output new_value dbname
select value || '_' output
from v$parameter where name = 'db_name';
spool racdiag_&&dbname&×tamp&&suffix
set lines 200
set pagesize 35
set trim on
set trims on
alter session set nls_date_format = 'MON-DD-YYYY HH24:MI:SS';
alter session set timed_statistics = true;
set feedback on
select to_char(sysdate) time from dual;
set numwidth 5
column host_name format a20 tru
select inst_id, instance_name, host_name, version, status, startup_time
from gv$instance
order by inst_id;
set echo on
-- WAIT CHAINS
-- 11.x+ Only (This will not work in < v11
-- See Note 1428210.1 for instructions on interpreting.
set pages 1000
set lines 120
set heading off
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,'',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,
'',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;
-- Taking Hang Analyze dumps
-- This may take a little while...
oradebug setmypid
oradebug unlimit
oradebug -g all hanganalyze 3
-- This part may take the longest, you can monitor bdump or udump to see if
-- the file is being generated.
oradebug -g all dump systemstate 258
-- WAITING SESSIONS:
-- The entries that are shown at the top are the sessions that have
-- waited the longest amount of time that are waiting for non-idle wait
-- events (event column). You can research and find out what the wait
-- event indicates (along with its parameters) by checking the Oracle
-- Server Reference Manual or look for any known issues or documentation
-- by searching Metalink for the event name in the search bar. Example
-- (include single quotes): [ 'buffer busy due to global cache' ].
-- Metalink and/or the Server Reference Manual should return some useful
-- information on each type of wait event. The inst_id column shows the
-- instance where the session resides and the SID is the unique identifier
-- for the session (gv$session). The p1, p2, and p3 columns will show
-- event specific information that may be important to debug the problem.
-- To find out what the p1, p2, and p3 indicates see the next section.
-- Items with wait_time of anything other than 0 indicate we do not know
-- how long these sessions have been waiting.
--
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, 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;
-- EVENT PARAMETER LOOKUP:
-- This section will give a description of the parameter names of the
-- events seen in the last section. p1test is the parameter value for
-- p1 in the WAITING SESSIONS section while p2text is the parameter
-- value for p3 and p3 text is the parameter value for p3. The
-- parameter values in the first section can be helpful for debugging
-- the wait event.
--
column event format a30 tru
column p1text format a25 tru
column p2text format a25 tru
column p3text format a25 tru
select distinct event, p1text, p2text, p3text
from gv$session_wait sw
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 seconds_in_wait > 0
order by event;
-- GES LOCK BLOCKERS:
-- This section will show us any sessions that are holding locks that
-- are blocking other users. The inst_id will show us the instance that
-- the session resides on while the sid will be a unique identifier for
-- the session. The grant_level will show us how the GES lock is granted to
-- the user. The request_level will show us what status we are trying to
-- obtain. The lockstate column will show us what status the lock is in.
-- The last column shows how long this session has been waiting.
--
set numwidth 5
column state format a16 tru;
column event format a30 tru;
select dl.inst_id, s.sid, p.spid, dl.resource_name1,
decode(substr(dl.grant_level,1,8),'KJUSERNL','Null','KJUSERCR','Row-S (SS)',
'KJUSERCW','Row-X (SX)','KJUSERPR','Share','KJUSERPW','S/Row-X (SSX)',
'KJUSEREX','Exclusive',request_level) as grant_level,
decode(substr(dl.request_level,1,8),'KJUSERNL','Null','KJUSERCR','Row-S (SS)',
'KJUSERCW','Row-X (SX)','KJUSERPR','Share','KJUSERPW','S/Row-X (SSX)',
'KJUSEREX','Exclusive',request_level) as request_level,
decode(substr(dl.state,1,8),'KJUSERGR','Granted','KJUSEROP','Opening',
'KJUSERCA','Canceling','KJUSERCV','Converting') as state,
s.sid, sw.event, sw.seconds_in_wait sec
from gv$ges_enqueue dl, gv$process p, gv$session s, gv$session_wait sw
where blocker = 1
and (dl.inst_id = p.inst_id and dl.pid = p.spid)
and (p.inst_id = s.inst_id and p.addr = s.paddr)
and (s.inst_id = sw.inst_id and s.sid = sw.sid)
order by sw.seconds_in_wait desc;
-- GES LOCK WAITERS:
-- This section will show us any sessions that are waiting for locks that
-- are blocked by other users. The inst_id will show us the instance that
-- the session resides on while the sid will be a unique identifier for
-- the session. The grant_level will show us how the GES lock is granted to
-- the user. The request_level will show us what status we are trying to
-- obtain. The lockstate column will show us what status the lock is in.
-- The last column shows how long this session has been waiting.
--
set numwidth 5
column state format a16 tru;
column event format a30 tru;
select dl.inst_id, s.sid, p.spid, dl.resource_name1,
decode(substr(dl.grant_level,1,8),'KJUSERNL','Null','KJUSERCR','Row-S (SS)',
'KJUSERCW','Row-X (SX)','KJUSERPR','Share','KJUSERPW','S/Row-X (SSX)',
'KJUSEREX','Exclusive',request_level) as grant_level,
decode(substr(dl.request_level,1,8),'KJUSERNL','Null','KJUSERCR','Row-S (SS)',
'KJUSERCW','Row-X (SX)','KJUSERPR','Share','KJUSERPW','S/Row-X (SSX)',
'KJUSEREX','Exclusive',request_level) as request_level,
decode(substr(dl.state,1,8),'KJUSERGR','Granted','KJUSEROP','Opening',
'KJUSERCA','Cancelling','KJUSERCV','Converting') as state,
s.sid, sw.event, sw.seconds_in_wait sec
from gv$ges_enqueue dl, gv$process p, gv$session s, gv$session_wait sw
where blocked = 1
and (dl.inst_id = p.inst_id and dl.pid = p.spid)
and (p.inst_id = s.inst_id and p.addr = s.paddr)
and (s.inst_id = sw.inst_id and s.sid = sw.sid)
order by sw.seconds_in_wait desc;
-- LOCAL ENQUEUES:
-- This section will show us if there are any local enqueues. The inst_id will
-- show us the instance that the session resides on while the sid will be a
-- unique identifier for. The addr column will show the lock address. The type
-- will show the lock type. The id1 and id2 columns will show specific
-- parameters for the lock type.
--
set numwidth 12
column event format a12 tru
select l.inst_id, l.sid, l.addr, l.type, l.id1, l.id2,
decode(l.block,0,'blocked',1,'blocking',2,'global') block,
sw.event, sw.seconds_in_wait sec
from gv$lock l, gv$session_wait sw
where (l.sid = sw.sid and l.inst_id = sw.inst_id)
and l.block in (0,1)
order by l.type, l.inst_id, l.sid;
-- LATCH HOLDERS:
-- If there is latch contention or 'latch free' wait events in the WAITING
-- SESSIONS section we will need to find out which proceseses are holding
-- latches. The inst_id will show us the instance that the session resides
-- on while the sid will be a unique identifier for. The username column
-- will show the session's username. The os_user column will show the os
-- user that the user logged in as. The name column will show us the type
-- of latch being waited on. You can search Metalink for the latch name in
-- the search bar. Example (include single quotes):
-- [ 'library cache' latch ]. Metalink should return some useful information
-- on the type of latch.
--
set numwidth 5
select distinct lh.inst_id, s.sid, s.username, p.username os_user, lh.name
from gv$latchholder lh, gv$session s, gv$process p
where (lh.sid = s.sid and lh.inst_id = s.inst_id)
and (s.inst_id = p.inst_id and s.paddr = p.addr)
order by lh.inst_id, s.sid;
-- LATCH STATS:
-- This view will show us latches with less than optimal hit ratios
-- The inst_id will show us the instance for the particular latch. The
-- latch_name column will show us the type of latch. You can search Metalink
-- for the latch name in the search bar. Example (include single quotes):
-- [ 'library cache' latch ]. Metalink should return some useful information
-- on the type of latch. The hit_ratio shows the percentage of time we
-- successfully acquired the latch.
--
column latch_name format a30 tru
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);
-- No Wait Latches:
--
select inst_id, name latch_name,
round((immediate_gets/(immediate_gets+immediate_misses)), 3) hit_ratio,
round(sleeps/decode(immediate_misses,0,1,immediate_misses),3) "SLEEPS/MISS"
from gv$latch
where round((immediate_gets/(immediate_gets+immediate_misses)), 3) < .99
and immediate_gets + immediate_misses > 0
order by round((immediate_gets/(immediate_gets+immediate_misses)), 3);
-- GLOBAL CACHE CR PERFORMANCE
-- This shows the average latency of a consistent block request.
-- AVG CR BLOCK RECEIVE TIME should typically be about 15 milliseconds
-- depending on your system configuration and volume, is the average
-- latency of a consistent-read request round-trip from the requesting
-- instance to the holding instance and back to the requesting instance. If
-- your CPU has limited idle time and your system typically processes
-- long-running queries, then the latency may be higher. However, it is
-- possible to have an average latency of less than one millisecond with
-- User-mode IPC. Latency can be influenced by a high value for the
-- DB_MULTI_BLOCK_READ_COUNT parameter. This is because a requesting process
-- can issue more than one request for a block depending on the setting of
-- this parameter. Correspondingly, the requesting process may wait longer.
-- Also check interconnect badwidth, OS tcp settings, and OS udp settings if
-- AVG CR BLOCK RECEIVE TIME is high.
--
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 ;
-- GLOBAL CACHE LOCK PERFORMANCE
-- This shows the average global enqueue get time.
-- Typically AVG GLOBAL LOCK GET TIME should be 20-30 milliseconds. the
-- elapsed time for a get includes the allocation and initialization of a
-- new global enqueue. If the average global enqueue get (global cache
-- get time) or average global enqueue conversion times are excessive,
-- then your system may be experiencing timeouts. See the 'WAITING SESSIONS',
-- 'GES LOCK BLOCKERS', GES LOCK WAITERS', and 'TOP 10 WAIT EVENTS ON SYSTEM'
-- sections if the AVG GLOBAL LOCK GET TIME is high.
--
set numwidth 20
column "AVG GLOBAL LOCK GET TIME (ms)" format 9999999.9
select b1.inst_id, (b1.value + b2.value) "GLOBAL LOCK GETS",
b3.value "GLOBAL LOCK GET TIME",
(b3.value / (b1.value + b2.value) * 10) "AVG GLOBAL LOCK GET TIME (ms)"
from gv$sysstat b1, gv$sysstat b2, gv$sysstat b3
where b1.name = 'global lock sync gets' and
b2.name = 'global lock async gets' and b3.name = 'global lock get time'
and b1.inst_id = b2.inst_id and b2.inst_id = b3.inst_id
or b1.name = 'global enqueue gets sync' and
b2.name = 'global enqueue gets async' and b3.name = 'global enqueue get time'
and b1.inst_id = b2.inst_id and b2.inst_id = b3.inst_id;
-- RESOURCE USAGE
-- This section will show how much of our resources we have used.
--
set numwidth 8
select inst_id, resource_name, current_utilization, max_utilization,
initial_allocation
from gv$resource_limit
where max_utilization > 0
order by inst_id, resource_name;
-- DLM TRAFFIC INFORMATION
-- This section shows how many tickets are available in the DLM. If the
-- TCKT_WAIT columns says "YES" then we have run out of DLM tickets which
-- could cause a DLM hang. Make sure that you also have enough TCKT_AVAIL.
--
set numwidth 10
select * from gv$dlm_traffic_controller
order by TCKT_AVAIL;
-- DLM MISC
--
set numwidth 10
select * from gv$dlm_misc;
-- LOCK CONVERSION DETAIL:
-- This view shows the types of lock conversion being done on each instance.
--
select * from gv$lock_activity;
-- INITIALIZATION PARAMETERS:
-- Non-default init parameters for each node.
--
set numwidth 5
column name format a30 tru
column value format a50 wra
column description format a60 tru
select inst_id, name, value, description
from gv$parameter
where isdefault = 'FALSE'
order by inst_id, name;
-- TOP 10 WAIT EVENTS ON SYSTEM
-- This view will provide a summary of the top wait events in the db.
--
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;
-- SESSION/PROCESS REFERENCE:
-- This section is very important for most of the above sections to find out
-- which user/os_user/process is identified to which session/process.
--
set numwidth 7
column event format a30 tru
column program format a25 tru
column username format a15 tru
select p.inst_id, s.sid, s.serial#, p.pid, p.spid, p.program, s.username,
p.username os_user, sw.event, sw.seconds_in_wait sec
from gv$process p, gv$session s, gv$session_wait sw
where (p.inst_id = s.inst_id and p.addr = s.paddr)
and (s.inst_id = sw.inst_id and s.sid = sw.sid)
order by p.inst_id, s.sid;
-- SYSTEM STATISTICS:
-- All System Stats with values of > 0. These can be referenced in the
-- Server Reference Manual
--
set numwidth 5
column name format a60 tru
column value format 9999999999999999999999999
select inst_id, name, value
from gv$sysstat
where value > 0
order by inst_id, name;
-- CURRENT SQL FOR WAITING SESSIONS:
-- Current SQL for any session in the WAITING SESSIONS list
--
set numwidth 5
column sql format a80 wra
select sw.inst_id, sw.sid, sw.seconds_in_wait sec, sa.sql_text sql
from gv$session_wait sw, gv$session s, gv$sqlarea sa
where sw.sid = s.sid (+)
and sw.inst_id = s.inst_id (+)
and s.sql_address = sa.address
and 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
order by sw.seconds_in_wait desc;
-- WAIT CHAINS
-- 11.x+ Only (This will not work in < v11
-- See Note 1428210.1 for instructions on interpreting.
set pages 1000
set lines 120
set heading off
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,'',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,
'',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;
-- Taking Hang Analyze dumps
-- This may take a little while...
oradebug setmypid
oradebug unlimit
oradebug -g all hanganalyze 3
-- This part may take the longest, you can monitor bdump or udump to see
-- if the file is being generated.
oradebug -g all dump systemstate 258
set echo off
select to_char(sysdate) time from dual;
spool off
-- ---------------------------------------------------------------------------
Prompt;
Prompt racdiag output files have been written to:;
Prompt;
host pwd
Prompt alert log and trace files are located in:;
column host_name format a12 tru
column name format a20 tru
column value format a60 tru
select distinct i.host_name, p.name, p.value
from gv$instance i, gv$parameter p
where p.inst_id = i.inst_id (+)
and p.name like '%_dump_dest'
and p.name != 'core_dump_dest';
ash_event_trend.sql
col event format a32 trunc
col sql_id format a13
col wait format 99999
col io format 99999 head 'IO'
col tot_t0 format 99999 head '-15m|*Tot*'
col tot_t3 format 99999 head '-60m|Tot'
col tot_t2 format 99999 head '-45m|Tot'
col tot_t1 format 99999 head '-30m|Tot'
col rnk format 99 head 'Rnk'
col delim format a01 head '|'
col pctio format 999 head 'IO%'
col pctwait format 999 head 'WAI%'
col pcttot format 999 head 'TOT%'
with t3 as
(
select event
,sql_id
,wait
,100*ratio_to_report (wait) over () pctwait
,io
,100*ratio_to_report (io) over () pctio
,tot
,100*ratio_to_report (tot) over () pcttot
,rownum rnk
from
(
select ash.event event
,ash.sql_id sql_id
,sum(decode(ash.session_state,'WAITING',1,0)) - sum(decode(ash.session_state,'WAITING',decode(en.wait_class, 'User I/O',1,0),0)) wait
,sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0)) io
,sum(decode(ash.session_state,'ON CPU',1,1)) tot
from v$active_session_history ash
,v$event_name en
where event is not NULL
and sql_id is not NULL
and ash.session_state = 'WAITING'
and ash.event#=en.event# (+)
and sample_time between sysdate-60/1440 and sysdate-46/1440
group by event, sql_id
order by sum(decode(ash.session_state,'WAITING',1,1)) desc
)
where rownum <=100
)
,t2 as
(
select event
,sql_id
,wait
,100*ratio_to_report (wait) over () pctwait
,io
,100*ratio_to_report (io) over () pctio
,tot
,100*ratio_to_report (tot) over () pcttot
,rownum rnk
from
(
select ash.event event
,ash.sql_id sql_id
,sum(decode(ash.session_state,'WAITING',1,0)) - sum(decode(ash.session_state,'WAITING',decode(en.wait_class, 'User I/O',1,0),0)) wait
,sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0)) io
,sum(decode(ash.session_state,'ON CPU',1,1)) tot
from v$active_session_history ash
,v$event_name en
where event is not NULL
and sql_id is not NULL
and ash.session_state = 'WAITING'
and ash.event#=en.event# (+)
and sample_time between sysdate-45/1440 and sysdate-31/1440
group by event, sql_id
order by sum(decode(ash.session_state,'WAITING',1,1)) desc
)
where rownum <=100
)
,t1 as
(
select event
,sql_id
,wait
,100*ratio_to_report (wait) over () pctwait
,io
,100*ratio_to_report (io) over () pctio
,tot
,100*ratio_to_report (tot) over () pcttot
,rownum rnk
from
(
select ash.event event
,ash.sql_id sql_id
,sum(decode(ash.session_state,'WAITING',1,0)) - sum(decode(ash.session_state,'WAITING',decode(en.wait_class, 'User I/O',1,0),0)) wait
,sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0)) io
,sum(decode(ash.session_state,'ON CPU',1,1)) tot
from v$active_session_history ash
,v$event_name en
where event is not NULL
and sql_id is not NULL
and ash.session_state = 'WAITING'
and ash.event#=en.event# (+)
and sample_time between sysdate-30/1440 and sysdate-16/1440
group by event, sql_id
order by sum(decode(ash.session_state,'WAITING',1,1)) desc
)
where rownum <=100
)
,t0 as
(
select event
,sql_id
,wait
,100*ratio_to_report (wait) over () pctwait
,io
,100*ratio_to_report (io) over () pctio
,tot
,100*ratio_to_report (tot) over () pcttot
,rownum rnk
from
(
select ash.event event
,ash.sql_id sql_id
,sum(decode(ash.session_state,'WAITING',1,0)) - sum(decode(ash.session_state,'WAITING',decode(en.wait_class, 'User I/O',1,0),0)) wait
,sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0)) io
,sum(decode(ash.session_state,'ON CPU',1,1)) tot
from v$active_session_history ash
,v$event_name en
where event is not NULL
and sql_id is not NULL
and ash.session_state = 'WAITING'
and ash.event#=en.event# (+)
and sample_time between sysdate-15/1440 and sysdate
group by event, sql_id
order by sum(decode(ash.session_state,'WAITING',1,1)) desc
)
where rownum <=20
)
select t3.wait wait
,t3.pctwait pctwait
,t3.io io
,t3.pctio pctio
,t3.tot tot_t3
,'|' delim
,t2.wait wait
,t2.pctwait pctwait
,t2.io io
,t2.pctio pctio
,t2.tot tot_t2
,'|' delim
,t1.wait wait
,t1.pctwait pctwait
,t1.io io
,t1.pctio pctio
,t1.tot tot_t1
,'|' delim
--,t0.rnk rnk
,t0.event event
,t0.sql_id sql_id
,'|' delim
,t0.wait wait
,t0.pctwait pctwait
,t0.io io
,t0.pctio pctio
,t0.tot tot_t0
from t0
,t3
,t2
,t1
where t0.event = t3.event (+)
and t0.sql_id = t3.sql_id (+)
and t0.event = t2.event (+)
and t0.sql_id = t2.sql_id (+)
and t0.event = t1.event (+)
and t0.sql_id = t1.sql_id (+)
order by t0.rnk
;
mypoormanscript.sql
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.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')) 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;
set pagesize 10000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set scan on;
set verify off;
--
column sql_text format a40 heading 'SQL-Statement'
column executions format 999,999 heading 'Total|Runs'
column reads_per_run format 999,999,999.9 heading 'Read-Per-Run|[Number of]'
column disk_reads format 999,999,999 heading 'Disk-Reads|[Number of]'
column buffer_gets format 999,999,999 heading 'Buffer-Gets|[Number of]'
column hit_ratio format 99 heading 'Hit|Ratio [%]'
ttitle left 'I/O-intensive SQL-Statements in the memory (V$SQLAREA)' -
skip 2
SELECT sql_text, executions,
round(disk_reads / executions, 2) reads_per_run,
disk_reads, buffer_gets,
round((buffer_gets - disk_reads) / buffer_gets, 2)*100 hit_ratio
FROM v$sqlarea
WHERE executions > 0
AND buffer_gets > 0
AND (buffer_gets - disk_reads) / buffer_gets < 0.80
ORDER BY 3 desc;
set lin 200
col BYTES_PROCESSED for 999999999999999999
col ESTD_EXTRA_BYTES_RW for 9999999999999999
col ESTD_TIME for 99999999999
col PGA_TARGET_FOR_ESTIMATE for 99999999999999999
select * from V$PGA_TARGET_ADVICE;
select name,value
from v$pgastat
where name in ('aggregate PGA target parameter',
'aggregate PGA auto target',
'maximum PGA allocated',
'total PGA inuse',
'total PGA allocated',
'over allocation count',
'extra bytes read/written',
'cache hit percentage',
'process count');
select sum(OPTIMAL_EXECUTIONS) OPTIMAL,
sum(ONEPASS_EXECUTIONS) ONEPASS ,
sum(MULTIPASSES_EXECUTIONS) MULTIPASSES
from v$sql_workarea
where POLICY='AUTO';
high_version_count.sql
set lines 180
select sql_id,sc.address,version_count,parsing_schema_name,reason,lpad(' ',180,'-')||replace(sql_text,(13)) sql_text from (
select
sql_id, address, '** reason => '
||decode(max(UNBOUND_CURSOR),'Y','UNBOUND_CURSOR '||': '||count(*)||' | ')
||decode(max(SQL_TYPE_MISMATCH),'Y','SQL_TYPE_MISMATCH '||': '||count(*)||' | ')
||decode(max(OPTIMIZER_MISMATCH),'Y','OPTIMIZER_MISMATCH '||': '||count(*)||' | ')
||decode(max(OUTLINE_MISMATCH),'Y','OUTLINE_MISMATCH '||': '||count(*)||' | ')
||decode(max(STATS_ROW_MISMATCH),'Y','STATS_ROW_MISMATCH '||': '||count(*)||' | ')
||decode(max(LITERAL_MISMATCH),'Y','LITERAL_MISMATCH '||': '||count(*)||' | ')
||decode(max(FORCE_HARD_PARSE),'Y','FORCE_HARD_PARSE '||': '||count(*)||' | ')
||decode(max(EXPLAIN_PLAN_CURSOR),'Y','EXPLAIN_PLAN_CURSOR '||': '||count(*)||' | ')
||decode(max(BUFFERED_DML_MISMATCH),'Y','BUFFERED_DML_MISMATCH '||': '||count(*)||' | ')
||decode(max(PDML_ENV_MISMATCH),'Y','PDML_ENV_MISMATCH '||': '||count(*)||' | ')
||decode(max(INST_DRTLD_MISMATCH),'Y','INST_DRTLD_MISMATCH '||': '||count(*)||' | ')
||decode(max(SLAVE_QC_MISMATCH),'Y','SLAVE_QC_MISMATCH '||': '||count(*)||' | ')
||decode(max(TYPECHECK_MISMATCH),'Y','TYPECHECK_MISMATCH '||': '||count(*)||' | ')
||decode(max(AUTH_CHECK_MISMATCH),'Y','AUTH_CHECK_MISMATCH '||': '||count(*)||' | ')
||decode(max(BIND_MISMATCH),'Y','BIND_MISMATCH '||': '||count(*)||' | ')
||decode(max(DESCRIBE_MISMATCH),'Y','DESCRIBE_MISMATCH '||': '||count(*)||' | ')
||decode(max(LANGUAGE_MISMATCH),'Y','LANGUAGE_MISMATCH '||': '||count(*)||' | ')
||decode(max(TRANSLATION_MISMATCH),'Y','TRANSLATION_MISMATCH '||': '||count(*)||' | ')
||decode(max(BIND_EQUIV_FAILURE),'Y','BIND_EQUIV_FAILURE '||': '||count(*)||' | ')
||decode(max(INSUFF_PRIVS),'Y','INSUFF_PRIVS '||': '||count(*)||' | ')
||decode(max(INSUFF_PRIVS_REM),'Y','INSUFF_PRIVS_REM '||': '||count(*)||' | ')
||decode(max(REMOTE_TRANS_MISMATCH),'Y','REMOTE_TRANS_MISMATCH '||': '||count(*)||' | ')
||decode(max(LOGMINER_SESSION_MISMATCH),'Y','LOGMINER_SESSION_MISMATCH '||': '||count(*)||' | ')
||decode(max(INCOMP_LTRL_MISMATCH),'Y','INCOMP_LTRL_MISMATCH '||': '||count(*)||' | ')
||decode(max(OVERLAP_TIME_MISMATCH),'Y','OVERLAP_TIME_MISMATCH '||': '||count(*)||' | ')
||decode(max(EDITION_MISMATCH),'Y','EDITION_MISMATCH '||': '||count(*)||' | ')
||decode(max(MV_QUERY_GEN_MISMATCH),'Y','MV_QUERY_GEN_MISMATCH '||': '||count(*)||' | ')
||decode(max(USER_BIND_PEEK_MISMATCH),'Y','USER_BIND_PEEK_MISMATCH '||': '||count(*)||' | ')
||decode(max(TYPCHK_DEP_MISMATCH),'Y','TYPCHK_DEP_MISMATCH '||': '||count(*)||' | ')
||decode(max(NO_TRIGGER_MISMATCH),'Y','NO_TRIGGER_MISMATCH '||': '||count(*)||' | ')
||decode(max(FLASHBACK_CURSOR),'Y','FLASHBACK_CURSOR '||': '||count(*)||' | ')
||decode(max(ANYDATA_TRANSFORMATION),'Y','ANYDATA_TRANSFORMATION '||': '||count(*)||' | ')
||decode(max(PDDL_ENV_MISMATCH),'Y','PDDL_ENV_MISMATCH '||': '||count(*)||' | ')
||decode(max(TOP_LEVEL_RPI_CURSOR),'Y','TOP_LEVEL_RPI_CURSOR '||': '||count(*)||' | ')
||decode(max(DIFFERENT_LONG_LENGTH),'Y','DIFFERENT_LONG_LENGTH '||': '||count(*)||' | ')
||decode(max(LOGICAL_STANDBY_APPLY),'Y','LOGICAL_STANDBY_APPLY '||': '||count(*)||' | ')
||decode(max(DIFF_CALL_DURN),'Y','DIFF_CALL_DURN '||': '||count(*)||' | ')
||decode(max(BIND_UACS_DIFF),'Y','BIND_UACS_DIFF '||': '||count(*)||' | ')
||decode(max(PLSQL_CMP_SWITCHS_DIFF),'Y','PLSQL_CMP_SWITCHS_DIFF '||': '||count(*)||' | ')
||decode(max(CURSOR_PARTS_MISMATCH),'Y','CURSOR_PARTS_MISMATCH '||': '||count(*)||' | ')
||decode(max(STB_OBJECT_MISMATCH),'Y','STB_OBJECT_MISMATCH '||': '||count(*)||' | ')
||decode(max(CROSSEDITION_TRIGGER_MISMATCH),'Y','CROSSEDITION_TRIGGER_MISMATCH '||': '||count(*)||' | ')
||decode(max(PQ_SLAVE_MISMATCH),'Y','PQ_SLAVE_MISMATCH '||': '||count(*)||' | ')
||decode(max(TOP_LEVEL_DDL_MISMATCH),'Y','TOP_LEVEL_DDL_MISMATCH '||': '||count(*)||' | ')
||decode(max(MULTI_PX_MISMATCH),'Y','MULTI_PX_MISMATCH '||': '||count(*)||' | ')
||decode(max(BIND_PEEKED_PQ_MISMATCH),'Y','BIND_PEEKED_PQ_MISMATCH '||': '||count(*)||' | ')
||decode(max(MV_REWRITE_MISMATCH),'Y','MV_REWRITE_MISMATCH '||': '||count(*)||' | ')
||decode(max(ROLL_INVALID_MISMATCH),'Y','ROLL_INVALID_MISMATCH '||': '||count(*)||' | ')
||decode(max(OPTIMIZER_MODE_MISMATCH),'Y','OPTIMIZER_MODE_MISMATCH '||': '||count(*)||' | ')
||decode(max(PX_MISMATCH),'Y','PX_MISMATCH '||': '||count(*)||' | ')
||decode(max(MV_STALEOBJ_MISMATCH),'Y','MV_STALEOBJ_MISMATCH '||': '||count(*)||' | ')
||decode(max(FLASHBACK_TABLE_MISMATCH),'Y','FLASHBACK_TABLE_MISMATCH '||': '||count(*)||' | ')
||decode(max(LITREP_COMP_MISMATCH),'Y','LITREP_COMP_MISMATCH '||': '||count(*)||' | ')
||decode(max(PLSQL_DEBUG),'Y','PLSQL_DEBUG '||': '||count(*)||' | ')
||decode(max(LOAD_OPTIMIZER_STATS),'Y','LOAD_OPTIMIZER_STATS '||': '||count(*)||' | ')
||decode(max(ACL_MISMATCH),'Y','ACL_MISMATCH '||': '||count(*)||' | ')
||decode(max(FLASHBACK_ARCHIVE_MISMATCH),'Y','FLASHBACK_ARCHIVE_MISMATCH '||': '||count(*)||' | ')
||decode(max(LOCK_USER_SCHEMA_FAILED),'Y','LOCK_USER_SCHEMA_FAILED '||': '||count(*)||' | ')
||decode(max(REMOTE_MAPPING_MISMATCH),'Y','REMOTE_MAPPING_MISMATCH '||': '||count(*)||' | ')
||decode(max(LOAD_RUNTIME_HEAP_FAILED),'Y','LOAD_RUNTIME_HEAP_FAILED '||': '||count(*)||' | ')
||decode(max(HASH_MATCH_FAILED),'Y','HASH_MATCH_FAILED '||': '||count(*)||' | ')
||decode(max(PURGED_CURSOR),'Y','PURGED_CURSOR '||': '||count(*)||' | ')
||decode(max(BIND_LENGTH_UPGRADEABLE),'Y','BIND_LENGTH_UPGRADEABLE '||': '||count(*)||' | ')
||decode(max(USE_FEEDBACK_STATS),'Y','USE_FEEDBACK_STATS '||': '||count(*)||' | ')
reason
from
v$sql_shared_cursor
group by
sql_id, address
) sc join v$sqlarea sq using(sql_id)
where version_count > 10
and parsing_schema_name not in ('SYS')
order by sql_id, version_count
;
Nonindexedfkconstraints.sql
WITH
ref_int_constraints AS (
SELECT
col.owner,
col.table_name,
col.constraint_name,
con.status,
con.r_owner,
con.r_constraint_name,
COUNT(*) col_cnt,
MAX(CASE col.position WHEN 01 THEN col.column_name END) col_01,
MAX(CASE col.position WHEN 02 THEN col.column_name END) col_02,
MAX(CASE col.position WHEN 03 THEN col.column_name END) col_03,
MAX(CASE col.position WHEN 04 THEN col.column_name END) col_04,
MAX(CASE col.position WHEN 05 THEN col.column_name END) col_05,
MAX(CASE col.position WHEN 06 THEN col.column_name END) col_06,
MAX(CASE col.position WHEN 07 THEN col.column_name END) col_07,
MAX(CASE col.position WHEN 08 THEN col.column_name END) col_08,
MAX(CASE col.position WHEN 09 THEN col.column_name END) col_09,
MAX(CASE col.position WHEN 10 THEN col.column_name END) col_10,
MAX(CASE col.position WHEN 11 THEN col.column_name END) col_11,
MAX(CASE col.position WHEN 12 THEN col.column_name END) col_12,
MAX(CASE col.position WHEN 13 THEN col.column_name END) col_13,
MAX(CASE col.position WHEN 14 THEN col.column_name END) col_14,
MAX(CASE col.position WHEN 15 THEN col.column_name END) col_15,
MAX(CASE col.position WHEN 16 THEN col.column_name END) col_16,
par.owner parent_owner,
par.table_name parent_table_name,
par.constraint_name parent_constraint_name
FROM dba_constraints con,
dba_cons_columns col,
dba_constraints par
WHERE con.constraint_type = 'R'
AND con.owner NOT IN ('ANONYMOUS','APEX_030200','APEX_040000','APEX_SSO','APPQOSSYS','CTXSYS','DBSNMP','DIP','EXFSYS','FLOWS_FILES','MDSYS','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OUTLN','OWBSYS')
AND con.owner NOT IN ('SI_INFORMTN_SCHEMA','SQLTXADMIN','SQLTXPLAIN','SYS','SYSMAN','SYSTEM','TRCANLZR','WMSYS','XDB','XS$NULL','PERFSTAT','STDBYPERF','MGDSYS','OJVMSYS')
AND col.owner = con.owner
AND col.constraint_name = con.constraint_name
AND col.table_name = con.table_name
AND par.owner(+) = con.r_owner
AND par.constraint_name(+) = con.r_constraint_name
GROUP BY
col.owner,
col.constraint_name,
col.table_name,
con.status,
con.r_owner,
con.r_constraint_name,
par.owner,
par.constraint_name,
par.table_name
),
ref_int_indexes AS (
SELECT /*+ MATERIALIZE NO_MERGE */ /* 2a.87 */
r.owner,
r.constraint_name,
c.table_owner,
c.table_name,
c.index_owner,
c.index_name,
r.col_cnt
FROM ref_int_constraints r,
dba_ind_columns c,
dba_indexes i
WHERE c.table_owner = r.owner
AND c.table_name = r.table_name
AND c.column_position <= r.col_cnt
AND c.column_name IN (r.col_01, r.col_02, r.col_03, r.col_04, r.col_05, r.col_06, r.col_07, r.col_08,
r.col_09, r.col_10, r.col_11, r.col_12, r.col_13, r.col_14, r.col_15, r.col_16)
AND i.owner = c.index_owner
AND i.index_name = c.index_name
AND i.table_owner = c.table_owner
AND i.table_name = c.table_name
AND i.index_type != 'BITMAP'
GROUP BY
r.owner,
r.constraint_name,
c.table_owner,
c.table_name,
c.index_owner,
c.index_name,
r.col_cnt
HAVING COUNT(*) = r.col_cnt
)
SELECT /*+ NO_MERGE */ /* 2a.87 */
*
FROM ref_int_constraints c
WHERE NOT EXISTS (
SELECT NULL
FROM ref_int_indexes i
WHERE i.owner = c.owner
AND i.constraint_name = c.constraint_name
)
ORDER BY
1, 2, 3;
#
set line 200
-- This is not written by me and is a Oracle provided script but its great to use
-- NAME: CELLPERFDIAG.SQL
----
-- ------------------------------------------------------------------------
-- ------------------------------------------------------------------------
-- ------------------------------------------------------------------------
-- ------------------------------------------------------------------------
-----------
-----
--
-- PURPOSE:
-- This script is intended to provide a user friendly guide to troubleshoot
-- cell performance specifically to identify which cell(s) may be problematic.
-- The script will create a file called cellperfdiag_.out in your
-- local directory.
set echo off
set feedback off
column timecol new_value timestamp
column spool_extension new_value suffix
select to_char(sysdate,'Mondd_hh24mi') timecol,
'.out' spool_extension from sys.dual;
column output new_value dbname
select value || '_' output
from v$parameter where name = 'db_name';
spool cellperfdiag_&&dbname&×tamp&&suffix
set trim on
set trims on
set lines 160
set long 10000
set pages 60
set verify off
alter session set optimizer_features_enable = '10.2.0.4';
-- Additional formatting
column avg_wait_time format 99999999999.9
column cell_name format a30 wra
column cell_path format a30 wra
column disk_name format a30 wra
column event format a40 wra
column inst_id format 999
column minute format a12 tru
column sample_time format a25 tru
column total_wait_time format 99999999999.9
PROMPT CELLPERFDIAG DATA FOR &&dbname&×tamp
PROMPT
PROMPT IMPORTANT PARAMETERS RELATING TO CELL PERFORMANCE:
PROMPT
column name format a40 wra
column value format a40 wra
select inst_id, name, value from gv$parameter
where (name like 'cell%' or name like '_kcfis%' or name like '%fplib%')
and value is not null
order by 1, 2, 3;
PROMPT
PROMPT TOP 20 CURRENT CELL WAITS
PROMPT
PROMPT This is to look at current cell waits, may not return any data.
select * from (
select c.cell_path, sw.inst_id, sw.event, sw.p1 cellhash#, sw.p2 diskhash#, sw.p3 bytes, sw.state, sw.seconds_in_wait
from v$cell c, gv$session_wait sw
where sw.p1text = 'cellhash#' and c.cell_hashval = sw.p1
order by 8 desc)
where rownum < 21;
PROMPT
PROMPT ASH CELL PERFORMANCE SUMMARY
PROMPT
PROMPT This query will look at the average cell wait times for each cell in ASH
select c.cell_path, sum(a.time_waited) TOTAL_WAIT_TIME, avg(a.time_waited) AVG_WAIT_TIME
from v$cell c, gv$active_session_history a
where a.p1text = 'cellhash#' and c.cell_hashval = a.p1
group by c.cell_path
order by 3 desc, 2 desc;
PROMPT
PROMPT 20 WORST CELL PERFORMANCE MINUTES IN ASH:
PROMPT
PROMPT APPROACH: These are the minutes where the avg cell perf time
PROMPT was the highest. See which cell had the longest waits and
PROMPT during what minute.
select * from (
select to_char(a.sample_time,'Mondd_hh24mi') minute, c.cell_path,
sum(a.time_waited) TOTAL_WAIT_TIME, avg(a.time_waited) AVG_WAIT_TIME
from v$cell c, gv$active_session_history a
where a.p1text = 'cellhash#' and c.cell_hashval = a.p1
group by to_char(sample_time,'Mondd_hh24mi'), c.cell_path
order by 4 desc, 3 desc)
where rownum < 21;
PROMPT
PROMPT 50 LONGEST CELL WAITS IN ASH ORDERED BY WAIT TIME
PROMPT
PROMPT APPROACH: These are the top 50 individual cell waits in ASH
PROMPT in wait time order.
select * from (
select a.sample_time, c.cell_path, a.inst_id, a.event, a.p1 cellhash#, a.p2 diskhash#, a.p3 bytes, a.time_waited
from v$cell c, gv$active_session_history a
where a.p1text = 'cellhash#' and c.cell_hashval = a.p1
order by time_waited desc)
where rownum < 51;
PROMPT
PROMPT 100 LONGEST CELL WAITS IN ASH ORDERED BY SAMPLE TIME
PROMPT
PROMPT APPROACH: These are the top 50 individual cell waits in ASH
PROMPT in sample time order.
select * from (
select * from (
select a.sample_time, c.cell_path, a.inst_id, a.event, a.p1 cellhash#, a.p2 diskhash#, a.p3 bytes, a.time_waited
from v$cell c, gv$active_session_history a
where a.p1text = 'cellhash#' and c.cell_hashval = a.p1
order by time_waited desc)
where rownum < 101)
order by 1;
PROMPT
PROMPT ASH HISTORY CELL PERFORMANCE SUMMARY
PROMPT
PROMPT This query will look at the average cell wait times for each cell in ASH
select c.cell_path, sum(a.time_waited) TOTAL_WAIT_TIME, avg(a.time_waited) AVG_WAIT_TIME
from v$cell c, DBA_HIST_ACTIVE_SESS_HISTORY a
where a.p1text = 'cellhash#' and c.cell_hashval = a.p1
group by c.cell_path
order by 3 desc, 2 desc;
PROMPT
PROMPT 20 WORST CELL PERFORMANCE MINUTES IN ASH HISTORY:
PROMPT
PROMPT APPROACH: These are the minutes where the avg cell perf time
PROMPT was the highest. See which cell had the longest waits and
PROMPT during what time minute.
select * from (
select to_char(a.sample_time,'Mondd_hh24mi') minute, c.cell_path,
sum(a.time_waited) TOTAL_WAIT_TIME, avg(a.time_waited) AVG_WAIT_TIME
from v$cell c, DBA_HIST_ACTIVE_SESS_HISTORY a
where a.p1text = 'cellhash#' and c.cell_hashval = a.p1
group by to_char(sample_time,'Mondd_hh24mi'), c.cell_path
order by 4 desc, 3 desc)
where rownum < 21;
PROMPT
PROMPT 50 LONGEST CELL WAITS IN ASH HISTORY ORDERED BY WAIT TIME
PROMPT
PROMPT APPROACH: These are the top 50 individual cell waits in ASH
PROMPT history in wait time order.
select * from (
select a.sample_time, c.cell_path, a.instance_number inst_id, a.event, a.p1 cellhash#, a.p2 diskhash#, a.p3 bytes, a.time_waited
from v$cell c, DBA_HIST_ACTIVE_SESS_HISTORY a
where a.p1text = 'cellhash#' and c.cell_hashval = a.p1
order by time_waited desc)
where rownum < 51;
PROMPT
PROMPT 100 LONGEST CELL WAITS IN ASH HISTORY ORDERED BY SAMPLE TIME
PROMPT
PROMPT APPROACH: These are the top 100 individual cell waits in ASH
PROMPT history in sample time order.
select * from (
select * from (
select a.sample_time, c.cell_path, a.instance_number inst_id, a.event, a.p1 cellhash#, a.p2 diskhash#, a.p3 bytes, a.time_waited
from v$cell c, DBA_HIST_ACTIVE_SESS_HISTORY a
where a.p1text = 'cellhash#' and c.cell_hashval = a.p1
order by time_waited desc)
where rownum < 101)
order by 1;
PROMPT
PROMPT AWR CELL DISK UTILIZATION
PROMPT
PROMPT APPROACH: This query only works in 12.1 and above. This is looking
PROMPT in the AWR history tables to look at cell disk utilization for some
PROMPT of the worst periods. Top 100 disk utils.
PROMPT DISK_UTILIZATION_SUM: Sum of the per-minute disk utilization metrics.
PROMPT IO_REQUESTS_SUM: Sum of the per-minute IOPs.
PROMPT IO_MB_SUM: Sum of the per-minute I/O metrics, in megabytes per second.
select * from (select * from (
select distinct dhs.snap_id, to_char(dhs.begin_interval_time,'Mondd_hh24mi') BEGIN,
to_char(dhs.end_interval_time,'Mondd_hh24mi') END,
cd.cell_name, cd.disk_name, DISK_UTILIZATION_SUM, IO_REQUESTS_SUM, IO_MB_SUM
from dba_hist_snapshot dhs, DBA_HIST_CELL_DISK_SUMMARY cds, v$cell_disk cd
where (cds.cell_hash = cd.cell_hash and cds.disk_id = cd.disk_id)
and dhs.snap_id = cds.snap_id and to_char(dhs.begin_interval_time,'Mondd_hh24') in
(select hour from (
select to_char(a.sample_time,'Mondd_hh24') hour, avg(a.time_waited) AVG_WAIT_TIME
from DBA_HIST_ACTIVE_SESS_HISTORY a
where event like 'cell%' or event like 'db file%' or event like 'log file%' or event like 'control file%'
group by to_char(a.sample_time,'Mondd_hh24')
order by 2 desc)
where rownum < 6)
order by DISK_UTILIZATION_SUM desc, IO_REQUESTS_SUM desc)
where rownum < 101)
order by 1,2,3,4,5;
SELECT ksqdngunid DB_ID_FOR_CURRENT_DB FROM X$KSQDN;
PROMPT
PROMPT CELL THREAD HISTORY - LAST FEW MINUTES
PROMPT
PROMPT This query only works in 12.1 and above.
select * from (
select count(*), sql_id, cell_name, job_type, database_id, instance_id
from v$cell_thread_history
where wait_state not in ('waiting_for_SKGXP_receive','waiting_for_connect','looking_for_job')
group by sql_id, cell_name, job_type, database_id, instance_id
order by 1 desc, 2, 3)
where rownum < 51;
PROMPT
PROMPT CELL CONFIG
PROMPT
select cellname, XMLTYPE.createXML(confval) confval
from v$cell_config
where conftype='CELL';
PROMPT
PROMPT IORM CONFIG
PROMPT
select cellname, XMLTYPE.createXML(confval) confval
from v$cell_config
where conftype='IORM';
select to_char(sysdate,'Mondd hh24:mi:ss') TIME from dual;
spool off
PROMPT
PROMPT OUTPUT FILE IS: cellperfdiag_&&dbname&×tamp&&suffix
PROMPT
##
col start_date format a20
col owner format a10
col job_name format a20
col program_name format a20
col enabled format a3 head 'ENA|BLE'
col start_date format a21 head 'START DATE'
col next_run_date format a21 head 'NEXT RUN DATE'
col last_start_date format a21 head 'LAST START DATE'
col repeat_interval format a15 head 'REPEAT INTERVAL' word_wrapped
col last_run_duration format a14 head 'LAST RUN|DURATION|DD:HH:MM:SS'
col run_count format 99,999 head 'RUN|COUNT'
col retry_count format 9999 head 'RETRY|COUNT'
col max_runs format 999,999 head 'MAX|RUNS'
col job_action format a15 head 'CODE' word_wrapped
select
owner
, job_name
--, program_name
, to_char(cast(start_date as date),'mm/dd/yy-hh24:mi:ss')
|| ' ' || extract (timezone_abbr from start_date )
start_date
--, state
, case enabled
when 'TRUE' then 'YES'
when 'FALSE' then 'NO'
end enabled
-- last_run_duration is an interval
, lpad(nvl(extract (day from last_run_duration ),'00'),2,'0')
|| ':' || lpad(nvl(extract (hour from last_run_duration ),'00'),2,'0')
|| ':' || lpad(nvl(extract (minute from last_run_duration ),'00'),2,'0')
|| ':' || ltrim(to_char(nvl(extract (second from last_run_duration ),0),'09.90'))
last_run_duration
, to_char(cast(next_run_date as date),'mm/dd/yy-hh24:mi:ss')
|| ' ' || extract (timezone_abbr from next_run_date )
next_run_date
, to_char(cast(last_start_date as date),'mm/dd/yy-hh24:mi:ss')
|| ' ' || extract (timezone_abbr from last_start_date )
last_start_date
, run_count
--, max_runs
, retry_count
, job_action
, repeat_interval
, state
from DBA_SCHEDULER_JOBS
order by owner
/
---------------------------------------------------------------------------------------
set lines 155
col dbtime for 999,999.99
col begin_timestamp for a40
select * from (
select begin_snap, end_snap, timestamp begin_timestamp, inst, a/1000000/60 DBtime from
(
select
e.snap_id end_snap,
lag(e.snap_id) over (order by e.snap_id) begin_snap,
lag(s.end_interval_time) over (order by e.snap_id) timestamp,
s.instance_number inst,
e.value,
nvl(value-lag(value) over (order by e.snap_id),0) a
from dba_hist_sys_time_model e, DBA_HIST_SNAPSHOT s
where s.snap_id = e.snap_id
and e.instance_number = s.instance_number
and to_char(e.instance_number) like nvl('&instance_number',to_char(e.instance_number))
and stat_name = 'DB time'
)
where begin_snap between nvl('&begin_snap_id',0) and nvl('&end_snap_id',99999999)
and begin_snap=end_snap-1
order by dbtime desc
)
where rownum < 31
/
--------------------------------------------------------------------------------------------------------------------
-- File name: prashantpoormanscript1.sql
-- Version: V1.1 (12-08-2021) Fancy Version
-- Purpose: This script can be used on any Oracle DB to know what all running and for how long and waiting
-- Also provides details on SQL and SESSION level.
-- Author: Prashant Dixit The Fatdba www.fatdba.com
--------------------------------------------------------------------------------------------------------------------
set linesize 400
set pagesize 400
col ACTION for a22
col USERNAME for a9
col SQL_ID for a16
col EVENT for a20
col OSUSER for a10
col PROCESS for a8
col MACHINE for a15
col OSUSER for a8
col PROGRAM for a15
col module for a20
col BLOCKING_INSTANCE for a20
select
'InstID .............................................: '||x.inst_id,
'SID ................................................: '||x.sid,
'Serial .............................................: '||x.serial#,
'Username ...........................................: '||x.username,
'SQLID ..............................................: '||x.sql_id,
'PHV ................................................: '||plan_hash_value,
'DISK_READS .........................................: '||sqlarea.DISK_READS,
'BUFFER_GETS ........................................: '||sqlarea.BUFFER_GETS,
'ROWS_PROCESSED ..... ...............................: '||sqlarea.ROWS_PROCESSED,
'Event .............................................: '||x.event,
'OSUser .............................................: '||x.osuser,
'Status .............................................: '||x.status,
'BLOCKING_SESSION_STATUS ............................: '||x.BLOCKING_SESSION_STATUS,
'BLOCKING_INSTANCE ..................................: '||x.BLOCKING_INSTANCE,
'BLOCKING_SESSION ...................................: '||x.BLOCKING_SESSION,
'PROCESS ............................................: '||x.process,
'MACHINE ............................................: '||x.machine,
'PROGRAM ............................................: '||x.program,
'MODULE .............................................: '||x.module,
'ACTION .............................................: '||x.action,
'LOGONTIME ..........................................: '||TO_CHAR(x.LOGON_TIME, 'MM-DD-YYYY HH24:MI:SS') logontime,
'LAST_CALL_ET .......................................: '||x.LAST_CALL_ET,
'SECONDS_IN_WAIT ....................................: '||x.SECONDS_IN_WAIT,
'STATE ..............................................: '||x.state,
'RUNNING_SINCE ......................................: '||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,
'SQLTEXT ............................................: '||sql_text
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%'
and sql_text not like '%select :"SYS_B_00"||x.inst_id, :"SYS_B_01"||x.sid, :"SYS_B_02"||x.serial#,%'
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;
set lines 155
col dbtime for 999,999.99
col begin_timestamp for a40
select * from (
select begin_snap, end_snap, timestamp begin_timestamp, inst, a/1000000/60 DBtime from
(
select
e.snap_id end_snap,
lag(e.snap_id) over (order by e.snap_id) begin_snap,
lag(s.end_interval_time) over (order by e.snap_id) timestamp,
s.instance_number inst,
e.value,
nvl(value-lag(value) over (order by e.snap_id),0) a
from dba_hist_sys_time_model e, DBA_HIST_SNAPSHOT s
where s.snap_id = e.snap_id
and e.instance_number = s.instance_number
and to_char(e.instance_number) like nvl('&instance_number',to_char(e.instance_number))
and stat_name = 'DB time'
)
where begin_snap between nvl('&begin_snap_id',0) and nvl('&end_snap_id',99999999)
and begin_snap=end_snap-1
order by dbtime desc
)
where rownum < 31
/
