/********* Wait.sql ************/
set pages 2000
set lines 132
column p1text format a18
column event format a35
column WT format 9999
column SW format 9999
select sid, event,p1text,p1raw,p1,p2,p3,seconds_in_Wait “SW”,Wait_time “WT”
from v$session_wait
where event not in(
‘KXFQ: Dequeue Range Keys – Slave’,
‘KXFQ: Dequeuing samples’,
‘KXFQ: kxfqcls – consumer closing TQ’,
‘KXFQ: kxfqdeq – dequeue from specific qref’,
‘KXFQ: kxfqdeq – normal deqeue’,
‘KXFX: Execution Message Dequeue – Slave’,
‘KXFX: Message Fragment Dequeue – Slave’,
‘KXFX: Parse Reply Dequeue – Query Coord’,
‘Null event’,
‘PL/SQL lock timer’,
‘Parallel Query Idle Wait – Slaves’,
‘Replication Dequeue’,
‘SQL*Net message from client’,
‘buffer deadlock’,
‘dispatcher timer’,
‘io done’,
‘pipe get’,
‘pmon timer’,
‘rdbms ipc message’,
‘rdbms ipc message block’,
‘rdbms ipc reply’,
‘slave wait’,
‘smon timer’,
‘virtual circuit status’);
/******** locktree.sql ***********/
/* Print out the lock wait-for graph in a tree structured fashion.
*
* This script prints the sessions in the system that are waiting for
* locks, and the locks that they are waiting for. The printout is tree
* structured. If a sessionid is printed immediately below and to the right
* of another session, then it is waiting for that session. The session ids
* printed at the left hand side of the page are the ones that everyone is
* waiting for.
*
* For example, in the following printout session 9 is waiting for
* session 8, 7 is waiting for 9, and 10 is waiting for 9.
*
* WAITING_SESSION TYPE MODE REQUESTED MODE HELD LOCK ID1 LOCK ID2
* —————– —- —————– —————– ——– ——–
* 8 NONE None None 0 0
* 9 TX Share (S) Exclusive (X) 65547 16
* 7 RW Exclusive (X) S/Row-X (SSX) 33554440 2
* 10 RW Exclusive (X) S/Row-X (SSX) 33554440 2
*
* The lock information to the right of the session id describes the lock
* that the session is waiting for (not the lock it is holding).
*
* Note that this is a script and not a set of view definitions because
* connect-by is used in the implementation and therefore a temporary table
* is created and dropped since you cannot do a join in a connect-by.
*
* This script has two small disadvantages. One, a table is created when
* this script is run. To create a table a number of locks must be
* acquired. This might cause the session running the script to get caught
* in the lock problem it is trying to diagnose. Two, if a session waits on
* a lock held by more than one session (share lock) then the wait-for graph
* is no longer a tree and the conenct-by will show the session (and any
* sessions waiting on it) several times.
*/
/* Select all sids waiting for a lock, the lock they are waiting on, and the
* sid of the session that holds the lock.
* UNION
* The sids of all session holding locks that someone is waiting on that
* are not themselves waiting for locks. These are included so that the roots
* of the wait for graph (the sessions holding things up) will be displayed.
*/
drop table lock_holders;
create table LOCK_HOLDERS /* temporary table */
(
waiting_session number,
holding_session number,
lock_type varchar2(26),
mode_held varchar2(14),
mode_requested varchar2(14),
lock_id1 varchar2(22),
lock_id2 varchar2(22)
);
drop table dba_locks_temp;
create table dba_locks_temp as select * from dba_locks;
/* This is essentially a copy of the dba_waiters view but runs faster since
* it caches the result of selecting from dba_locks.
*/
insert into lock_holders
select w.session_id,
h.session_id,
w.lock_type,
h.mode_held,
w.mode_requested,
w.lock_id1,
w.lock_id2
from dba_locks_temp w, dba_locks_temp h
where h.blocking_others = ‘Blocking’
and h.mode_held != ‘None’
and h.mode_held != ‘Null’
and w.mode_requested != ‘None’
and w.lock_type = h.lock_type
and w.lock_id1 = h.lock_id1
and w.lock_id2 = h.lock_id2;
commit;
drop table dba_locks_temp;
insert into lock_holders
select holding_session, null, ‘None’, null, null, null, null
from lock_holders
minus
select waiting_session, null, ‘None’, null, null, null, null
from lock_holders;
commit;
column waiting_session format a17;
column lock_type format a17;
column lock_id1 format a17;
column lock_id2 format a17;
/* Print out the result in a tree structured fashion */
select lpad(‘ ‘,3*(level-1)) || waiting_session waiting_session,
lock_type,
mode_requested,
mode_held,
lock_id1,
lock_id2
from lock_holders
connect by prior waiting_session = holding_session
start with holding_session is null;
drop table lock_holders;
/*********************** L.SQL ******************/
—
—
— view renamed to v$longlocks
—
— See XVIEW.SQL
—
set linesize 120
set pages 20000
col SID format 99999
col type format a4
col “HELD SEC” format 999999
col OSUSER format a9
col PROCESS format a15
col TERMINAL format a25
col BLOCKER format a15
col first_name format a7 tru
col last_name format a9 tru
col req format 999
col hld format 999
set feedback off
spo junklock.txt
prompt Subj: People are running Crazy ………….
prompt
prompt Locking Report:
prompt
select * from v$longlocks
/
prompt
prompt
spo off
set feed on
—-exit;
/********************* enq_sql.sql *****************/
set lines 132
set pages 2000
col SQL_WHICH_DOING_SCATTERED_READ format a64 word_wrapped
col ksuudlna format a10 heading “ORA User”
col KSUSEUNM format a10 heading “OS User”
col KSUSEMNM format a10 heading “Machine”
col INDX format 9999 heading “SID”
break on KSUUDLNA on KSUSEUNM on KSUSEMNM on INDX
select /*+ USE_NL(s) use_nl(e) use_nl(b) use_nl(c) */
b.name SQL_waiting_on_ENQUEUE,c.ksuudlna,c.KSUSEUNM,c.KSUSEMNM,c.INDX
from
x$ksusecst s,
x$ksled e,
x$kglna b,
x$ksuse c
where bitand(s.ksspaflg,1)!=0 and bitand(s.ksuseflg,1)!=0
and s.ksussseq!=0
and s.ksussopc=e.indx
and e.kslednam =’enq: TX – row lock contention’
and s.indx=c.indx
and b.KGLHDADR=c.ksusesql
and s.indx=c.indx
order by s.indx,b.piece
/
/******************** USER.SQL for finding SQL Hash Value ***********/
col osuser format a14
select sid,serial#,SQL_HASH_VALUE,sql_address,PREV_HASH_VALUE,PREV_SQL_ADDR,
program,osuser,machine,username,status,to_char(logon_time,’DD-MON-YY HH24:MI:SS’) from v$session where SID = &1
/
/********************* stext.sql for finding sql statement from SQl Hash Value ***********/
accept val1 prompt “Enter Hash Value:”
select sql_text from v$SQLTEXT_WITH_NEWLINES where hash_value=&val1
order by piece;