Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • scripts to take listener.log backup Linux/Unix
  • mutex in Oracle 10.2.0.2 or Oracle 10g Oracle
  • Oracle10g – Using SQLAccess Advisor (DBMS_ADVISOR) with the Automatic Workload Repository Oracle
  • Changing unix system clock when Oracle database is running. Oracle
  • to see when crontab is changed. Linux/Unix
  • CTAS with LONG Column for 9i and higher Oracle
  • Insert cause enqueue locks Oracle
  • On solaris 10, “S” link is not part of $ORACLE_HOME/bin/oracle as default. ( For 9.2.0.8) Oracle
  • sql_doing_fts.sql Oracle
  • Find_planinfo.sql Oracle
  • Temporary tablespace explaination Oracle
  • Example of How To Resize the Online Redo Logfiles Note:1035935.6 Oracle
  • pvm_metric.sql for gathering report from vmstat tables Oracle
  • pvm_rbs1.sql (to collect rbs info from db) Oracle
  • online_bkup.sql Oracle

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

  • Rename Oracle Instance Name Oracle
  • Remove duplicate rows from table Oracle
  • Jai Shree Ram Oracle
  • DBMS_SQL for alter session. Oracle
  • Good Oracle Architecture In Short and point to point Oracle
  • v$event_name Oracle

Leave a Reply Cancel reply

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

Categories

  • AWS (2)
  • Azure (1)
  • Linux/Unix (149)
  • Oracle (387)
  • PHP/MYSQL/Wordpress (10)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (336)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • SQL Tracker by SID sqltrackerbysid.sql22-Apr-2025
  • How to connect to Oracle Database with Wallet with Python.21-Mar-2025
  • JSON/XML Types in Oracle18-Mar-2025
  • CPU Core related projections12-Mar-2025
  • Exadata Basics10-Dec-2024
  • Reading config file from other folder inside class24-Sep-2024
  • Python class import from different folders22-Sep-2024
  • Transfer SQL Profiles from One database to other database.05-Sep-2024
  • Load testing on Oracle 19C RAC with HammerDB18-Jan-2024
  • Add new columns in dataframe30-Sep-2023

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • xargs use Linux/Unix
  • Find Multiple levels of object dependencies : depen.sql Oracle
  • get_vmstat_linux Oracle
  • create PLAN_TABLE command. Oracle
  • Oracle Identifiers Oracle
  • VIvek Encryption Package and Its Usage Oracle
  • CTAS with LONG Column for 9i and higher Oracle
  • Oracle 11g RAC on OEL 5 and Vmware 2 Oracle

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme