Sunday, July 6, 2025
-- Prompt for schema
ACCEPT schema_name CHAR PROMPT 'Enter schema name: '
-- Spool to log file
SPOOL rebuild_indexes_&&schema_name..log
SET SERVEROUTPUT ON SIZE UNLIMITED
DECLARE
v_sql VARCHAR2(4000);
v_owner VARCHAR2(128) := UPPER('&&schema_name');
v_unusable_before NUMBER := 0;
v_unusable_after NUMBER := 0;
v_parallel_before NUMBER := 0;
v_parallel_after NUMBER := 0;
BEGIN
-- Count unusable indexes before
SELECT COUNT(*) INTO v_unusable_before FROM (
SELECT 1 FROM dba_indexes WHERE owner = v_owner AND status = 'UNUSABLE'
UNION ALL
SELECT 1 FROM dba_ind_partitions WHERE index_owner = v_owner AND status = 'UNUSABLE'
UNION ALL
SELECT 1 FROM dba_ind_subpartitions WHERE index_owner = v_owner AND status = 'UNUSABLE'
);
-- Count indexes with PARALLEL > 1 before
SELECT COUNT(*) INTO v_parallel_before
FROM dba_indexes
WHERE owner = v_owner AND NVL(DEGREE, '1') NOT IN ('1', 'DEFAULT');
DBMS_OUTPUT.PUT_LINE('Unusable indexes before rebuild: ' || v_unusable_before);
DBMS_OUTPUT.PUT_LINE('Indexes with parallelism > 1 before rebuild: ' || v_parallel_before);
-- Rebuild global unusable indexes
FOR rec IN (
SELECT index_name, owner
FROM dba_indexes
WHERE status = 'UNUSABLE' AND owner = v_owner
)
LOOP
v_sql := 'ALTER INDEX "' || rec.owner || '"."' || rec.index_name || '" REBUILD ONLINE PARALLEL 4';
DBMS_OUTPUT.PUT_LINE('Executing: ' || v_sql);
BEGIN
EXECUTE IMMEDIATE v_sql;
EXECUTE IMMEDIATE 'ALTER INDEX "' || rec.owner || '"."' || rec.index_name || '" NOPARALLEL';
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Failed: ' || v_sql || ' - ' || SQLERRM);
END;
END LOOP;
-- Rebuild unusable index partitions
FOR rec IN (
SELECT index_name, index_owner, partition_name
FROM dba_ind_partitions
WHERE status = 'UNUSABLE' AND index_owner = v_owner
)
LOOP
v_sql := 'ALTER INDEX "' || rec.index_owner || '"."' || rec.index_name ||
'" REBUILD PARTITION "' || rec.partition_name || '" ONLINE PARALLEL 4';
DBMS_OUTPUT.PUT_LINE('Executing: ' || v_sql);
BEGIN
EXECUTE IMMEDIATE v_sql;
EXECUTE IMMEDIATE 'ALTER INDEX "' || rec.index_owner || '"."' || rec.index_name || '" NOPARALLEL';
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Failed: ' || v_sql || ' - ' || SQLERRM);
END;
END LOOP;
-- Rebuild unusable index subpartitions
FOR rec IN (
SELECT index_name, index_owner, subpartition_name
FROM dba_ind_subpartitions
WHERE status = 'UNUSABLE' AND index_owner = v_owner
)
LOOP
v_sql := 'ALTER INDEX "' || rec.index_owner || '"."' || rec.index_name ||
'" REBUILD SUBPARTITION "' || rec.subpartition_name || '" ONLINE PARALLEL 4';
DBMS_OUTPUT.PUT_LINE('Executing: ' || v_sql);
BEGIN
EXECUTE IMMEDIATE v_sql;
EXECUTE IMMEDIATE 'ALTER INDEX "' || rec.index_owner || '"."' || rec.index_name || '" NOPARALLEL';
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Failed: ' || v_sql || ' - ' || SQLERRM);
END;
END LOOP;
-- Count unusable indexes after
SELECT COUNT(*) INTO v_unusable_after FROM (
SELECT 1 FROM dba_indexes WHERE owner = v_owner AND status = 'UNUSABLE'
UNION ALL
SELECT 1 FROM dba_ind_partitions WHERE index_owner = v_owner AND status = 'UNUSABLE'
UNION ALL
SELECT 1 FROM dba_ind_subpartitions WHERE index_owner = v_owner AND status = 'UNUSABLE'
);
-- Count indexes with PARALLEL > 1 after
SELECT COUNT(*) INTO v_parallel_after
FROM dba_indexes
WHERE owner = v_owner AND NVL(DEGREE, '1') NOT IN ('1', 'DEFAULT');
DBMS_OUTPUT.PUT_LINE('Unusable indexes after rebuild: ' || v_unusable_after);
DBMS_OUTPUT.PUT_LINE('Indexes with parallelism > 1 after reset: ' || v_parallel_after);
END;
/
SPOOL OFF
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
/
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';
SET MARKUP HTML ON
SET ECHO OFF FEEDBACK OFF
SET LINESIZE 120 PAGESIZE 100
COLUMN begin_hour FORMAT A20 HEADING "Hour"
COLUMN instance_number FORMAT 99 HEADING "Inst#"
COLUMN average_active_sessions FORMAT 9999.99 HEADING "AAS"
COLUMN status FORMAT A10 HEADING "Status"
SPOOL hourly_instance_aas.html
PROMPT ',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;
PROMPT +------------------------------------------------------------------------+
PROMPT | Report : Latch statistics 1 |
PROMPT | This is RAC aware script |
PROMPT | Instance : ¤t_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 : ¤t_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;
PROMPT +------------------------------------------------------------------------+
PROMPT | Report : No Willing to wait mode latch stats |
PROMPT | This is RAC aware script |
PROMPT | Desc: This section is for those latches who requests in immediate_gets |
PROMPT | mode. Look for SLEEPSMISS column which is last one in results |
PROMPT | Instance : ¤t_instance |
PROMPT +------------------------------------------------------------------------+
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);
PROMPT +------------------------------------------------------------------------+
PROMPT | Report : SQL with 100 or more unshared child cursors |
PROMPT | This is RAC aware script |
PROMPT | Desc: Results coming here with more than 500 childs can lead to high |
PROMPT | hard parsing situations which could lead to Library cache latching issu|
PROMPT | Instance : ¤t_instance |
PROMPT +------------------------------------------------------------------------+
WITH
not_shared AS (
SELECT /*+ MATERIALIZE NO_MERGE */ /* 2a.135 */
sql_id, COUNT(*) child_cursors,
RANK() OVER (ORDER BY COUNT(*) DESC NULLS LAST) AS sql_rank
FROM gv$sql_shared_cursor
GROUP BY
sql_id
HAVING COUNT(*) > 99
)
SELECT /*+ NO_MERGE */ /* 2a.135 */
ns.sql_rank,
ns.child_cursors,
ns.sql_id,
(SELECT s.sql_text FROM gv$sql s WHERE s.sql_id = ns.sql_id AND ROWNUM = 1) sql_text
FROM not_shared ns
ORDER BY
ns.sql_rank,
ns.child_cursors DESC,
ns.sql_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 : Archive generation per hour basis |
PROMPT | This is RAC aware script |
PROMPT | Desc: This will give an idea about any spike in redo activity or DMLs |
PROMPT | Instance : ¤t_instance |
PROMPT +------------------------------------------------------------------------+
set linesize 140
set feedback off
set timing off
set pagesize 1000
col ARCHIVED format a8
col ins format 99 heading "DB"
col member format a80
col status format a12
col archive_date format a20
col member format a60
col type format a10
col group# format 99999999
col min_archive_interval format a20
col max_archive_interval format a20
col h00 heading "H00" format a3
col h01 heading "H01" format a3
col h02 heading "H02" format a3
col h03 heading "H03" format a3
col h04 heading "H04" format a3
col h05 heading "H05" format a3
col h06 heading "H06" format a3
col h07 heading "H07" format a3
col h08 heading "H08" format a3
col h09 heading "H09" format a3
col h10 heading "H10" format a3
col h11 heading "H11" format a3
col h12 heading "H12" format a3
col h13 heading "H13" format a3
col h14 heading "H14" format a3
col h15 heading "H15" format a3
col h16 heading "H16" format a3
col h17 heading "H17" format a3
col h18 heading "H18" format a3
col h19 heading "H19" format a3
col h20 heading "H20" format a3
col h21 heading "H21" format a3
col h22 heading "H22" format a3
col h23 heading "H23" format a3
col total format a6
col date format a10
select * from v$logfile order by group#;
select * from v$log order by SEQUENCE#;
select max( sequence#) last_sequence, max(completion_time) completion_time, max(block_size) block_size from v$archived_log ;
SELECT instance ins,
log_date "DATE" ,
lpad(to_char(NVL( COUNT( * ) , 0 )),6,' ') Total,
lpad(to_char(NVL( SUM( decode( log_hour , '00' , 1 ) ) , 0 )),3,' ') h00 ,
lpad(to_char(NVL( SUM( decode( log_hour , '01' , 1 ) ) , 0 )),3,' ') h01 ,
lpad(to_char(NVL( SUM( decode( log_hour , '02' , 1 ) ) , 0 )),3,' ') h02 ,
lpad(to_char(NVL( SUM( decode( log_hour , '03' , 1 ) ) , 0 )),3,' ') h03 ,
lpad(to_char(NVL( SUM( decode( log_hour , '04' , 1 ) ) , 0 )),3,' ') h04 ,
lpad(to_char(NVL( SUM( decode( log_hour , '05' , 1 ) ) , 0 )),3,' ') h05 ,
lpad(to_char(NVL( SUM( decode( log_hour , '06' , 1 ) ) , 0 )),3,' ') h06 ,
lpad(to_char(NVL( SUM( decode( log_hour , '07' , 1 ) ) , 0 )),3,' ') h07 ,
lpad(to_char(NVL( SUM( decode( log_hour , '08' , 1 ) ) , 0 )),3,' ') h08 ,
lpad(to_char(NVL( SUM( decode( log_hour , '09' , 1 ) ) , 0 )),3,' ') h09 ,
lpad(to_char(NVL( SUM( decode( log_hour , '10' , 1 ) ) , 0 )),3,' ') h10 ,
lpad(to_char(NVL( SUM( decode( log_hour , '11' , 1 ) ) , 0 )),3,' ') h11 ,
lpad(to_char(NVL( SUM( decode( log_hour , '12' , 1 ) ) , 0 )),3,' ') h12 ,
lpad(to_char(NVL( SUM( decode( log_hour , '13' , 1 ) ) , 0 )),3,' ') h13 ,
lpad(to_char(NVL( SUM( decode( log_hour , '14' , 1 ) ) , 0 )),3,' ') h14 ,
lpad(to_char(NVL( SUM( decode( log_hour , '15' , 1 ) ) , 0 )),3,' ') h15 ,
lpad(to_char(NVL( SUM( decode( log_hour , '16' , 1 ) ) , 0 )),3,' ') h16 ,
lpad(to_char(NVL( SUM( decode( log_hour , '17' , 1 ) ) , 0 )),3,' ') h17 ,
lpad(to_char(NVL( SUM( decode( log_hour , '18' , 1 ) ) , 0 )),3,' ') h18 ,
lpad(to_char(NVL( SUM( decode( log_hour , '19' , 1 ) ) , 0 )),3,' ') h19 ,
lpad(to_char(NVL( SUM( decode( log_hour , '20' , 1 ) ) , 0 )),3,' ') h20 ,
lpad(to_char(NVL( SUM( decode( log_hour , '21' , 1 ) ) , 0 )),3,' ') h21 ,
lpad(to_char(NVL( SUM( decode( log_hour , '22' , 1 ) ) , 0 )),3,' ') h22 ,
lpad(to_char(NVL( SUM( decode( log_hour , '23' , 1 ) ) , 0 )),3,' ') h23
FROM (
SELECT thread# INSTANCE ,
TO_CHAR( first_time , 'YYYY-MM-DD' ) log_date ,
TO_CHAR( first_time , 'hh24' ) log_hour
FROM v$log_history
)
GROUP BY
instance,log_date
ORDER BY
log_date ;
select trunc(min(completion_time - first_time))||' Day '||
to_char(trunc(sysdate,'dd') + min(completion_time - first_time),'hh24:mm:ss')||chr(10) min_archive_interval,
trunc(max(completion_time - first_time))||' Day '||
to_char(trunc(sysdate,'dd') + max(completion_time - first_time),'hh24:mm:ss')||chr(10) max_archive_interval
from gv$archived_log
where sequence# <> ( select max(sequence#) from gv$archived_log ) ;
set feedback on
set timing on
PROMPT +------------------------------------------------------------------------+
PROMPT | Report : SESSION DETAILS |
PROMPT | This is RAC aware script |
PROMPT | Desc: Shows details about all sessions and their states active, inactiv|
PROMPT | Instance : ¤t_instance |
PROMPT +------------------------------------------------------------------------+
set linesize 400 pagesize 400
select resource_name, current_utilization, max_utilization, limit_value, inst_id
from gv$resource_limit
where resource_name in ('sessions', 'processes');
select count(s.status) INACTIVE_SESSIONS
from gv$session s, gv$process p
where
p.addr=s.paddr and
s.status='INACTIVE';
select count(s.status) "INACTIVE SESSIONS > 3HOURS "
from gv$session s, gv$process p
where
p.addr=s.paddr and
s.last_call_et > 10800 and
s.status='INACTIVE';
select count(s.status) ACTIVE_SESSIONS
from gv$session s, gv$process p
where
p.addr=s.paddr and
s.status='ACTIVE';
select s.program,count(s.program) Inactive_Sessions_from_1Hour
from gv$session s,gv$process p
where p.addr=s.paddr AND
s.status='INACTIVE'
and s.last_call_et > (10800)
group by s.program
order by 2 desc;
set linesize 400 pagesize 400
col INST_ID for 99
col spid for a10
set linesize 150
col PROGRAM for a10
col action format a10
col logon_time format a16
col module format a13
col cli_process format a7
col cli_mach for a15
col status format a10
col username format a10
col last_call_et_Hrs for 9999.99
col sql_hash_value for 9999999999999
col username for a10
set linesize 152
set pagesize 80
col "Last SQL" for a60
col elapsed_time for 999999999999
select p.spid, s.sid,s.serial#,s.last_call_et/3600 last_call_et_3Hrs ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
and s.last_call_et > (10800)
order by last_call_et;
PROMPT +------------------------------------------------------------------------+
PROMPT | Report : Lists all locked objects for whole RAC. |
PROMPT | This is RAC aware script |
PROMPT | Desc: Remember to always look for X type locks, SS, SX, S, SSX are fine|
PROMPT | Instance : ¤t_instance |
PROMPT +------------------------------------------------------------------------|
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
COLUMN owner FORMAT A20
COLUMN username FORMAT A20
COLUMN object_owner FORMAT A20
COLUMN object_name FORMAT A30
COLUMN locked_mode FORMAT A15
SELECT b.inst_id,
b.session_id AS sid,
NVL(b.oracle_username, '(oracle)') AS username,
a.owner AS object_owner,
a.object_name,
Decode(b.locked_mode, 0, 'None',
1, 'Null (NULL)',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share (S)',
5, 'S/Row-X (SSX)',
6, 'Exclusive (X)',
b.locked_mode) locked_mode,
b.os_user_name
FROM dba_objects a,
gv$locked_object b
WHERE a.object_id = b.object_id
ORDER BY 1, 2, 3, 4;
PROMPT +------------------------------------------------------------------------+
PROMPT | Report : Undo usage report |
PROMPT | This is RAC aware script |
PROMPT | Desc: This shows details about all undo rollback segments, best for 01555|
PROMPT | Instance : ¤t_instance |
PROMPT +------------------------------------------------------------------------|
SET LINESIZE 200
COLUMN username FORMAT A15
SELECT s.inst_id,
s.username,
s.sid,
s.serial#,
t.used_ublk,
t.used_urec,
rs.segment_name,
r.rssize,
r.status
FROM gv$transaction t,
gv$session s,
gv$rollstat r,
dba_rollback_segs rs
WHERE s.saddr = t.ses_addr
AND s.inst_id = t.inst_id
AND t.xidusn = r.usn
AND t.inst_id = r.inst_id
AND rs.segment_id = t.xidusn
ORDER BY t.used_ublk DESC;
PROMPT +------------------------------------------------------------------------+
PROMPT | Report : Local Enqueues |
PROMPT | This is RAC aware script |
PROMPT | Desc: This section will show us if there are any local enqueues. |
PROMPT | The addr column will show the lock address. The type will show the type|
PROMPT | Instance : ¤t_instance |
PROMPT +------------------------------------------------------------------------|
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;
PROMPT +------------------------------------------------------------------------+
PROMPT | Report : ORA errors reported in alert log of databases, SYSDATE-1 |
PROMPT | This is RAC aware script |
PROMPT | Desc: Shows all alert log ora errors and log files with locations |
PROMPT | Instance : ¤t_instance |
PROMPT +------------------------------------------------------------------------|
select TO_CHAR(A.ORIGINATING_TIMESTAMP, 'dd.mm.yyyy hh24:mi:ss') MESSAGE_TIME
,inst_id, message_text
,host_id
,inst_id
,adr_home
from v$DIAG_ALERT_EXT A
where A.ORIGINATING_TIMESTAMP > sysdate-1
and component_id='rdbms'
and message_text like '%ORA-%'
order by 1 desc;
spool off
exit
PROMPT +------------------------------------------------------------------------+
PROMPT | Report : Top object waits in the database. |
PROMPT | This is RAC aware script |
PROMPT | Desc: Shows Object name along with count, sqlid, and total time waited |
PROMPT | Instance : ¤t_instance |
PROMPT +------------------------------------------------------------------------|
set lines 170 echo off
col event format a26 head 'Wait Event' trunc
col mod format a26 head 'Module' trunc
col sqlid format a13 head 'SQL Id'
col oname format a38 head 'Object Name'
col sname format a30 head 'SubObject Name'
col otyp format a10 head 'Object Typ' trunc
col cnt format 999999 head 'Wait Cnt'
col twait format 9999999999 head 'Tot Time|Waited'
select o.owner||'.'||o.object_name oname
,o.object_type otyp
,o.subobject_name sname
,h.event event
,h.wcount cnt
,h.twait twait
,h.sql_id sqlid
,h.module mod
from (select current_obj#,sql_id,module,event,count(*) wcount,sum(time_waited+wait_time) twait
from gv$active_session_history
where event not in (
'queue messages'
,'rdbms ipc message'
,'rdbms ipc reply'
,'pmon timer'
,'smon timer'
,'jobq slave wait'
,'wait for unread message on broadcast channel'
,'wakeup time manager')
and event not like 'SQL*Net%'
and event not like 'Backup%'
group by current_obj#,sql_id,module,event
order by twait desc) h
,dba_objects o
where h.current_obj# = o.object_id
and rownum < 31
;
PROMPT +------------------------------------------------------------------------+
PROMPT | Report : list of all custom SQL Profiles in DB |
PROMPT | This is RAC aware script |
PROMPT | Desc: Shows details of all SQL profiles already there in the database |
PROMPT | Instance : ¤t_instance |
PROMPT +------------------------------------------------------------------------|
set linesize 400 pagesize 400
col name for a30
col created for a30
select name, created, status,sql_text as SQLTXT from dba_sql_profiles order by created desc;
PROMPT +------------------------------------------------------------------------+
PROMPT | Report : Show active sessions from latest ASH sample |
PROMPT | This is RAC aware script |
PROMPT | Desc: Shows Show active sessions from latest ASH sample |
PROMPT | Instance : ¤t_instance |
PROMPT +------------------------------------------------------------------------|
set linesi 290
col hostcpu format 999.9 head 'Host|Cpu%'
col module format a15 head 'Module' trunc
col sidser format a15 head 'Sid,Serial',module) module
from gv$active_session_history
where PGA_ALLOCATED > 2*1024*1024
and sample_time > sysdate-3/60/1440
order by sample_time, qc_session_id, SESSION_ID
/
PROMPT +------------------------------------------------------------------------+
PROMPT | Report : Top 10 objects in database.
PROMPT | This is RAC aware script |
PROMPT | Desc: Database Usage |
PROMPT | Instance : ¤t_instance |
PROMPT +------------------------------------------------------------------------|
col segment_name format a30
col owner format a20
col tablespace_name format a30
select * from (select owner,segment_name,SEGMENT_TYPE,TABLESPACE_NAME,round(sum(BYTES)/(1024*1024*1024)) size_in_GB
from dba_segments group by owner,segment_name,SEGMENT_TYPE,TABLESPACE_NAME order by 5 desc ) where rownum<=10;
Hourly Average Active Sessions per Instance (Last 24 Hours)
WITH interval_data AS ( SELECT TRUNC(begin_time,'HH24') AS begin_hour, instance_number, MAX(CASE WHEN metric_name = 'Average Active Sessions' THEN average END) AS aas FROM dba_hist_con_sysmetric_summ WHERE begin_time >= SYSDATE - 1 AND metric_name = 'Average Active Sessions' GROUP BY TRUNC(begin_time,'HH24'), instance_number ) SELECT TO_CHAR(begin_hour, 'YYYY-MM-DD HH24:MI') AS begin_hour, instance_number, ROUND(aas, 2) AS average_active_sessions, CASE WHEN aas >= 10 THEN 'CRITICAL' WHEN aas >= 5 THEN 'WARNING' ELSE 'OK' END AS status FROM interval_data ORDER BY begin_hour, instance_number; SPOOL OFF; COLUMN INST_ID FORMAT 99 COLUMN HOST_ID FORMAT A30 COLUMN CONTAINER_NAME FORMAT A30 COLUMN PROBLEM_KEY FORMAT A30 COLUMN ORIGINATING_TIMESTAMP FORMAT A45 COLUMN RPT FORMAT 9999 COLUMN STATUS FORMAT A10 SPOOL ora_errors_rac_last24h.html PROMPTORA‑ Errors (Last 24h, All RAC Instances)
SELECT INST_ID, TO_CHAR(TRUNC(ORIGINATING_TIMESTAMP,'MI'),'DD.MM.YYYY HH24:MI') AS ORIGINATING_TIMESTAMP, HOST_ID, CONTAINER_NAME, NVL(PROBLEM_KEY, 'N/A') AS PROBLEM_KEY, COUNT(*) AS RPT, CASE WHEN COUNT(*) > 20 THEN 'CRITICAL' WHEN COUNT(*) > 5 THEN 'WARNING' ELSE 'OK' END AS STATUS, SUBSTR(MESSAGE_TEXT,1,200) AS MESSAGE_TEXT FROM TABLE( gv$( CURSOR( SELECT INST_ID, ORIGINATING_TIMESTAMP, HOST_ID, CONTAINER_NAME, PROBLEM_KEY, MESSAGE_TEXT FROM v$diag_alert_ext WHERE ORIGINATING_TIMESTAMP >= SYSDATE - 1 AND COMPONENT_ID = 'rdbms' AND MESSAGE_TEXT LIKE '%ORA-%' ) ) ) GROUP BY INST_ID, HOST_ID, CONTAINER_NAME, NVL(PROBLEM_KEY, 'N/A'), TRUNC(ORIGINATING_TIMESTAMP, 'MI'), SUBSTR(MESSAGE_TEXT,1,200) ORDER BY INST_ID, RPT DESC, ORIGINATING_TIMESTAMP DESC; SPOOL OFF; alter session set cursor_sharing=exact; col username for a40 col sql_text for a70 col sid for 9999999 col status for a10 col slv_req for 9999999 col slv_alloc for 9999999 col secs_in_q for 99999999 col IS_ADAPTIVE_PLAN for a30 SELECT inst_id, sid, session_serial# sess#, username, sql_id, status, px_servers_requested slv_req, px_servers_allocated slv_alloc, substr(sql_text,1,30)||'...' sql_text, LAST_REFRESH_TIME, IS_ADAPTIVE_PLAN, PX_MAXDOP, queuing_time/1000000 secs_in_q, RM_CONSUMER_GROUP FROM gv\$sql_monitor WHERE status in ('QUEUED', 'EXECUTING') AND sql_text is not null AND px_servers_requested is not null AND px_servers_allocated is not null AND px_servers_requested != px_servers_allocated ORDER BY status desc, secs_in_q desc, sql_id; col HOST_ID for a30 col CONTAINER_NAME for a30 col PROBLEM_KEY for a30 col ORIGINATING_TIMESTAMP for a45 col rpt for a10 select originating_timestamp, HOST_ID, NVL(PROBLEM_KEY, 0), count(*) as REPEATED, CONTAINER_NAME, substr(MESSAGE_TEXT, 1, 70) MESSAGE, CASE WHEN MESSAGE_TYPE = 1 THEN 'UNK' WHEN MESSAGE_TYPE = 2 THEN 'INCIDENT_ERROR' WHEN MESSAGE_TYPE = 3 THEN 'ERROR' WHEN MESSAGE_TYPE = 4 THEN 'WARNING' WHEN MESSAGE_TYPE = 5 THEN 'NOTIFICATION' WHEN MESSAGE_TYPE = 6 THEN 'TRACE' END AS MESSAGE_TYPE_DESC, CASE WHEN MESSAGE_LEVEL = 1 THEN 'CRITICAL' WHEN MESSAGE_LEVEL = 2 THEN 'SEVERE' WHEN MESSAGE_LEVEL = 8 THEN 'IMPORTANT' WHEN MESSAGE_LEVEL = 16 THEN 'NORMAL' END AS MESSAGE_LEVEL_DESC, x.* FROM TABLE (gv$ (CURSOR (SELECT * FROM V$DIAG_ALERT_EXT))) x WHERE originating_timestamp > (SYSDATE - 12/24) AND substr(MESSAGE_TEXT, 1, 20) like '%ORA-%' AND substr(MESSAGE_TEXT, 1, 20) not like '%Result%' GROUP BY originating_timestamp, HOST_ID, PROBLEM_KEY, CONTAINER_NAME, MESSAGE_TEXT ORDER BY 3 DESC; ## alter session set cursor_sharing=exact; COL px_qcsid HEAD QC_SID FOR A13 COL px_instances FOR A100 COL px_username HEAD USERNAME FOR A25 WRAP SELECT pxs.qcsid || ',' || pxs.qcserial# px_qcsid, pxs.qcinst_id, ses.username px_username, ses.sql_id, pxs.degree, pxs.req_degree, COUNT(*) slaves, COUNT(DISTINCT pxs.inst_id) inst_cnt, MIN(pxs.inst_id) min_inst, MAX(pxs.inst_id) max_inst, -- LISTAGG(TO_CHAR(pxs.inst_id), ',') WITHIN GROUP (ORDER BY pxs.inst_id) px_instances FROM gv\$px_session pxs, gv\$session ses, gv\$px_process p WHERE ses.sid = pxs.sid AND ses.serial# = pxs.serial# AND p.sid = pxs.sid AND pxs.inst_id = ses.inst_id AND ses.inst_id = p.inst_id AND pxs.req_degree IS NOT NULL -- qc GROUP BY pxs.qcsid || ',' || pxs.qcserial#, pxs.qcinst_id, ses.username, ses.sql_id, pxs.degree, pxs.req_degree ORDER BY pxs.qcinst_id, slaves DESC; ### ol ERROR_NUMBER for a30 col USERNAME for a30 col SERVICE_NAME for a30 col program for a50 select ERROR_NUMBER, count(*) as repeated, inst_id, sid, SESSION_SERIAL#, username, sql_id, program, service_name, FIRST_REFRESH_TIME from GV\$SQL_MONITOR where ERROR_NUMBER is not null and FIRST_REFRESH_TIME > sysdate - 1/24 group by ERROR_NUMBER, inst_id, sid, SESSION_SERIAL#, username, sql_id, program, service_name, FIRST_REFRESH_TIME order by FIRST_REFRESH_TIME; select * from ( select b.inst_id, b.sid, b.serial#, a.spid, substr(b.machine,1,30) machine, to_char(b.logon_time,'dd-mon-yyyy hh24:mi:ss') logon_time, substr(b.username,1,30) username, substr(b.osuser,1,20) os_user, substr(b.program,1,30) program, substr(b.event,1,20) event, b.wait_class, b.last_call_et AS last_call_et_secs, b.sql_id from gv\$session b, gv\$process a where b.paddr = a.addr and a.inst_id = b.inst_id and b.type='USER' and b.status='ACTIVE' and b.last_call_et > 3600 order by b.logon_time, b.last_call_et ) where rownum < 10; col event for a30 col username for a30 col WAIT_CLASS for a30 select inst_id, blocking_session, sid, serial#, username, wait_class, EVENT, seconds_in_wait, sql_id from gv\$session where blocking_session is not NULL and seconds_in_wait > 300 order by seconds_in_wait; select i.host_name, i.instance_name, i.version_full version, i.instance_number, p.name pdb, p.open_mode, p.restricted, i.STARTUP_TIME, round((i.startup_time - (sysdate - 1/24)), 2) as "Bit" from gv\$instance i, gv\$pdbs p where i.inst_id = p.inst_id order by 2; Col NAME for a40; Col N1 for a1 Col N2 for a1 Col N3 for a1 Col N4 for a1 Col N5 for a1 Col N6 for a1 Col N7 for a1 Col N8 for a1 SELECT a.NAME, b.node1 as N1, b.node2 as N2, b.node3 as N3, b.node4 as N4, b.node5 as N5, b.node6 as N6, b.node7 as N7, b.node8 as N8 FROM (SELECT DISTINCT UPPER(NAME) NAME FROM gv\$services WHERE NAME NOT LIKE 'SYS$%') a, (SELECT UPPER(NAME) NAME, MAX(DECODE(inst_id, 1, 'Y', '')) node1, MAX(DECODE(inst_id, 2, 'Y', '')) node2, MAX(DECODE(inst_id, 3, 'Y', '')) node3, MAX(DECODE(inst_id, 4, 'Y', '')) node4, MAX(DECODE(inst_id, 5, 'Y', '')) node5, MAX(DECODE(inst_id, 6, 'Y', '')) node6, MAX(DECODE(inst_id, 7, 'Y', '')) node7, MAX(DECODE(inst_id, 8, 'Y', '')) node8 FROM gv\$active_services WHERE NAME NOT LIKE 'SYS$%' GROUP BY UPPER(NAME) ) b WHERE a.NAME = b.NAME(+) ORDER BY 1; col event for a30 col username for a30 col WAIT_CLASS for a30 col blocking_status for a100 col Machine for a40 col LOCKED_OBJECT for a40 SELECT decode(request, 0, 'Holder: ', 'Waiter: ') || l.sid sess, machine, do.object_name AS locked_object, id1, id2, lmode, request, l.type FROM gv\$lock l JOIN gv\$session s ON l.sid = s.sid AND l.inst_id = s.inst_id JOIN gv\$locked_object lo ON l.sid = lo.session_id AND l.inst_id = lo.inst_id JOIN dba_objects do ON lo.object_id = do.object_id WHERE (id1, id2, l.type) IN ( SELECT id1, id2, type FROM gv\$lock WHERE request > 0 ) ORDER BY id1, request; SET MARKUP HTML ON SET ECHO OFF FEEDBACK OFF SET LINESIZE 200 PAGESIZE 100 COLUMN inst_id FORMAT 99 COLUMN HOST_ID FORMAT A30 COLUMN CONTAINER_NAME FORMAT A30 COLUMN PROBLEM_KEY FORMAT A30 COLUMN ORIGINATING_TIMESTAMP FORMAT A45 COLUMN RPT FORMAT 9999 COLUMN STATUS FORMAT A10 SPOOL ora_errors_rac_last24h.html PROMPTORA‑ Errors (Last 24h, All RAC Instances)
SELECT inst_id, TO_CHAR(originating_timestamp, 'DD.MM.YYYY HH24:MI') AS ORIGINATING_TIMESTAMP, host_id, container_name, NVL(problem_key, 'N/A') AS problem_key, COUNT(*) AS RPT, CASE WHEN COUNT(*) > 10 THEN 'CRITICAL' WHEN COUNT(*) > 3 THEN 'WARNING' ELSE 'OK' END AS STATUS, SUBSTR(message_text,1,200) AS MESSAGE_TEXT FROM TABLE( gv$( CURSOR( SELECT inst_id, originating_timestamp, host_id, container_name, problem_key, message_text FROM v$diag_alert_ext WHERE originating_timestamp >= SYSDATE - 1 AND component_id = 'rdbms' AND message_text LIKE '%ORA-%' ) ) ) GROUP BY inst_id, host_id, container_name, COALESCE(problem_key,'N/A'), TRUNC(originating_timestamp,'MI'), SUBSTR(message_text,1,200) ORDER BY inst_id, RPT DESC, ORIGINATING_TIMESTAMP DESC; SPOOL OFF; SELECT inst_id, ROUND(COUNT(*) / (EXTRACT(DAY FROM (MAX(sample_time) - MIN(sample_time))) * 24 * 60 * 60 + EXTRACT(HOUR FROM (MAX(sample_time) - MIN(sample_time))) * 60 * 60 + EXTRACT(MINUTE FROM (MAX(sample_time) - MIN(sample_time))) * 60 + EXTRACT(SECOND FROM (MAX(sample_time) - MIN(sample_time))) ), 2) AS average_active_sessions, CASE WHEN ROUND(COUNT(*) / (EXTRACT(DAY FROM (MAX(sample_time) - MIN(sample_time))) * 24 * 60 * 60 + EXTRACT(HOUR FROM (MAX(sample_time) - MIN(sample_time))) * 60 * 60 + EXTRACT(MINUTE FROM (MAX(sample_time) - MIN(sample_time))) * 60 + EXTRACT(SECOND FROM (MAX(sample_time) - MIN(sample_time))) ), 2) >= 10 THEN 'CRITICAL' WHEN ROUND(COUNT(*) / (EXTRACT(DAY FROM (MAX(sample_time) - MIN(sample_time))) * 24 * 60 * 60 + EXTRACT(HOUR FROM (MAX(sample_time) - MIN(sample_time))) * 60 * 60 + EXTRACT(MINUTE FROM (MAX(sample_time) - MIN(sample_time))) * 60 + EXTRACT(SECOND FROM (MAX(sample_time) - MIN(sample_time))) ), 2) >= 5 THEN 'WARNING' ELSE 'OK' END AS status FROM gv$active_session_history WHERE sample_time BETWEEN TO_DATE('2025-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('2025-06-01 23:59:59', 'YYYY-MM-DD HH24:MI:SS') GROUP BY inst_id ORDER BY inst_id; SET LINESIZE 200 SET PAGESIZE 100 SET MARKUP HTML ON SPOOL rac_alert_errors.html PROMPTORA- Errors from All RAC Instances (Last 24 Hours)
SELECT inst_id, TO_CHAR(originating_timestamp, 'DD.MM.YYYY HH24:MI:SS') AS message_time, host_id, adr_home, message_text FROM TABLE(gv$(CURSOR( SELECT originating_timestamp, host_id, adr_home, message_text FROM v$diag_alert_ext WHERE originating_timestamp > SYSDATE - 1 AND component_id = 'rdbms' AND message_text LIKE '%ORA-%' ))) ORDER BY originating_timestamp DESC; SPOOL OFF; SET LINESIZE 200 SET PAGESIZE 100 SET MARKUP HTML ON SPOOL open_cursor_stats.html PROMPTOpen Cursor Statistics per RAC Instance
SELECT inst_id, TO_CHAR(execute_count, '999G999G999G999') AS "SQL Execution", TO_CHAR(parse_count, '999G999G999G999') AS "Parse Count", TO_CHAR(cursor_hits, '999G999G999G999') AS "Cursor Hits", ROUND((parse_count / NULLIF(execute_count, 0)) * 100, 2) AS "Parse % Total", ROUND((cursor_hits / NULLIF(parse_count, 0)) * 100, 2) AS "Cursor Cache % Total", CASE WHEN ROUND((cursor_hits / NULLIF(parse_count, 0)) * 100, 2) >= 90 THEN 'OK' WHEN ROUND((cursor_hits / NULLIF(parse_count, 0)) * 100, 2) >= 80 THEN 'WARNING' ELSE 'CRITICAL' END AS status FROM ( SELECT inst_id, MAX(CASE WHEN name = 'execute count' THEN value END) AS execute_count, MAX(CASE WHEN name = 'parse count (total)' THEN value END) AS parse_count, MAX(CASE WHEN name = 'session cursor cache hits' THEN value END) AS cursor_hits FROM gv$sysstat WHERE name IN ('execute count', 'parse count (total)', 'session cursor cache hits') GROUP BY inst_id ) ORDER BY inst_id; SPOOL OFF; SELECT inst_id, ROUND(COUNT(*) / (24 * 60 * 60), 2) AS average_active_sessions, CASE WHEN ROUND(COUNT(*) / (24 * 60 * 60), 2) >= 10 THEN 'CRITICAL' WHEN ROUND(COUNT(*) / (24 * 60 * 60), 2) >= 5 THEN 'WARNING' ELSE 'OK' END AS status FROM gv$active_session_history WHERE sample_time >= SYSDATE - 1 GROUP BY inst_id ORDER BY inst_id; SET LINESIZE 200 SET PAGESIZE 100 SET MARKUP HTML ON SPOOL db_time_peak.html PROMPTTop 30 DB Time Intervals per Instance in the Last 24 Hours
WITH db_time_deltas AS ( SELECT s.instance_number AS inst_id, s.snap_id, s.begin_interval_time, s.end_interval_time, t.value, LAG(t.value) OVER (PARTITION BY s.instance_number ORDER BY s.snap_id) AS prev_value FROM dba_hist_snapshot s JOIN dba_hist_sys_time_model t ON s.snap_id = t.snap_id AND s.dbid = t.dbid AND s.instance_number = t.instance_number WHERE s.begin_interval_time >= SYSDATE - 1 AND t.stat_name = 'DB time' ), db_time_calc AS ( SELECT inst_id, snap_id, begin_interval_time, end_interval_time, ROUND(NVL(value - prev_value, 0) / 1e6 / 60, 2) AS db_time_min FROM db_time_deltas ) SELECT * FROM ( SELECT inst_id, snap_id, begin_interval_time, end_interval_time, db_time_min, CASE WHEN db_time_min > 60 THEN 'CRITICAL' WHEN db_time_min > 30 THEN 'WARNING' ELSE 'OK' END AS status FROM db_time_calc ORDER BY db_time_min DESC ) WHERE ROWNUM <= 30; SPOOL OFF; SELECT inst_id, metric_name, ROUND(value, 2) AS metric_value, metric_unit, CASE metric_name WHEN 'Average Active Sessions' THEN CASE WHEN value > 10 THEN 'CRITICAL' WHEN value > 5 THEN 'WARNING' ELSE 'OK' END WHEN 'Average Synchronous Single-Block Read Latency' THEN CASE WHEN value > 20 THEN 'CRITICAL' WHEN value > 10 THEN 'WARNING' ELSE 'OK' END WHEN 'CPU Usage Per Sec' THEN CASE WHEN value > 90 THEN 'CRITICAL' WHEN value > 70 THEN 'WARNING' ELSE 'OK' END WHEN 'Background CPU Usage Per Sec' THEN CASE WHEN value > 50 THEN 'CRITICAL' WHEN value > 30 THEN 'WARNING' ELSE 'OK' END WHEN 'DB Block Changes Per Txn' THEN CASE WHEN value > 100 THEN 'CRITICAL' WHEN value > 50 THEN 'WARNING' ELSE 'OK' END WHEN 'Executions Per Sec' THEN CASE WHEN value < 10 THEN 'CRITICAL' WHEN value < 50 THEN 'WARNING' ELSE 'OK' END WHEN 'Host CPU Usage Per Sec' THEN CASE WHEN value > 90 THEN 'CRITICAL' WHEN value > 70 THEN 'WARNING' ELSE 'OK' END WHEN 'I/O Megabytes per Second' THEN CASE WHEN value > 1000 THEN 'CRITICAL' WHEN value > 500 THEN 'WARNING' ELSE 'OK' END WHEN 'I/O Requests per Second' THEN CASE WHEN value > 10000 THEN 'CRITICAL' WHEN value > 5000 THEN 'WARNING' ELSE 'OK' END WHEN 'Logical Reads Per Txn' THEN CASE WHEN value > 1000 THEN 'CRITICAL' WHEN value > 500 THEN 'WARNING' ELSE 'OK' END WHEN 'Logons Per Sec' THEN CASE WHEN value > 100 THEN 'CRITICAL' WHEN value > 50 THEN 'WARNING' ELSE 'OK' END WHEN 'Network Traffic Volume Per Sec' THEN CASE WHEN value > 1000 THEN 'CRITICAL' WHEN value > 500 THEN 'WARNING' ELSE 'OK' END WHEN 'Physical Reads Per Sec' THEN CASE WHEN value > 1000 THEN 'CRITICAL' WHEN value > 500 THEN 'WARNING' ELSE 'OK' END WHEN 'Physical Reads Per Txn' THEN CASE WHEN value > 100 THEN 'CRITICAL' WHEN value > 50 THEN 'WARNING' ELSE 'OK' END WHEN 'Physical Writes Per Sec' THEN CASE WHEN value > 1000 THEN 'CRITICAL' WHEN value > 500 THEN 'WARNING' ELSE 'OK' END WHEN 'Redo Generated Per Sec' THEN CASE WHEN value > 500 THEN 'CRITICAL' WHEN value > 250 THEN 'WARNING' ELSE 'OK' END WHEN 'Redo Generated Per Txn' THEN CASE WHEN value > 100 THEN 'CRITICAL' WHEN value > 50 THEN 'WARNING' ELSE 'OK' END WHEN 'Response Time Per Txn' THEN CASE WHEN value > 1 THEN 'CRITICAL' WHEN value > 0.5 THEN 'WARNING' ELSE 'OK' END WHEN 'SQL Service Response Time' THEN CASE WHEN value > 1 THEN 'CRITICAL' WHEN value > 0.5 THEN 'WARNING' ELSE 'OK' END WHEN 'Total Parse Count Per Txn' THEN CASE WHEN value > 100 THEN 'CRITICAL' WHEN value > 50 THEN 'WARNING' ELSE 'OK' END WHEN 'User Calls Per Sec' THEN CASE WHEN value > 1000 THEN 'CRITICAL' WHEN value > 500 THEN 'WARNING' ELSE 'OK' END WHEN 'User Transaction Per Sec' THEN CASE WHEN value < 10 THEN 'CRITICAL' WHEN value < 50 THEN 'WARNING' ELSE 'OK' END ELSE 'N/A' END AS status 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 -- Last 60-second interval ORDER BY inst_id, metric_name; set termout off set linesize 90 set pagesize 20 ttitle center 'PDHC - A Quick Health Check' skip 2 btitle center 'Confidential' 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 | 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 : ¤t_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 : ¤t_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 : ¤t_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 : ¤t_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 : ¤t_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 : ¤t_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 : ¤t_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 : ¤t_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 : ¤t_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 : ¤t_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 : ¤t_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 : ¤t_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 : ¤t_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 : ¤t_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 : ¤t_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 : ¤t_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 : ¤t_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 : ¤t_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 : ¤t_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 : ¤t_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 : ¤t_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 : ¤t_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 : ¤t_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 : ¤t_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 : ¤t_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 : ¤t_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 : ¤t_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,'
Subscribe to:
Posts (Atom)