Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • Renaming Global Name GLOBAL_NAME Oracle
  • When error comes for temporary tablespace with version <= 9i Oracle
  • chk_space_SID.ksh Linux/Unix
  • Locktree.sql Oracle
  • shutdown linux Linux/Unix
  • Adding addidional hard drive and attach it to a linux box. Linux/Unix
  • Optimizer SORT Operations Oracle
  • Running some SQL on multiple databases connecting using monitoring userid and password Linux/Unix
  • Handling LOB data in Oracle Oracle
  • Jai Shree Ram Oracle
  • adding new line after specific pattern using sed Linux/Unix
  • alter database backup controlfile to trace Oracle
  • Finding Oracle Patches with opatch Oracle
  • Temporary tablespace explaination Oracle
  • restarting network in linux Linux/Unix

Wait Based Tuning Step by step with SQL statement

Posted on 30-Aug-2005 By Admin No Comments on Wait Based Tuning Step by step with SQL statement

/********* 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;

Oracle, SQL scripts

Post navigation

Previous Post: Renaming Oracle Instance Name
Next Post: Import and export statements

Related Posts

  • DBMS_Shared_pool pinning triggers Oracle
  • block_ident.sql Oracle
  • Renaming Global Name GLOBAL_NAME Oracle
  • Query to Generate aggregate on every 30 mins. Oracle
  • crtgr.sql /* For creating trigger from data dictionary */ Oracle
  • TABLE SIZING WITH DB_BLOCK ARCHITECTURE Reference : Metalink note : 10640.1 Oracle

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Categories

  • Ansible (0)
  • AWS (2)
  • Azure (1)
  • Linux/Unix (149)
  • MYSQL (5)
  • Oracle (392)
  • PHP/MYSQL/Wordpress (10)
  • POSTGRESQL (0)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (341)
  • SQL Server (6)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • SQL Server Vs Oracle Architecture difference25-Jul-2025
  • SQL Server: How to see historical transactions25-Jul-2025
  • SQL Server: How to see current transactions or requests25-Jul-2025
  • T-SQL Vs PL/SQL Syntax25-Jul-2025
  • Check SQL Server edition25-Jul-2025
  • Checking SQL Server Version25-Jul-2025
  • Oracle vs MYSQL Architecture differences (For DBAs)24-Jul-2025
  • V$INSTANCE of Oracle in MYSQL24-Jul-2025
  • Day to day MYSQL DBA operations (Compared with Oracle DBA)24-Jul-2025
  • MYSQL and Oracle Comparison for Oracle DBA24-Jul-2025

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • useful dg links Oracle
  • DBMS_UTILITY PACKAGE Oracle
  • Oracle Metalink useful notes Oracle
  • MYSQL for Oracle DBA MYSQL
  • Good Link from metalink 1 Oracle
  • Changing unix system clock when Oracle database is running. Oracle
  • Jai Shree Ram Oracle
  • Explain Plan Doesn’T Change For Sql After New Statistics Generated Oracle

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme