Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • Another Tuning Article for subheap of shared pool Oracle
  • Building Our Own Namespaces with “Create Context” Oracle
  • online_bkup.sql Oracle
  • How to sort list of files on basis of their sizes. Linux/Unix
  • Find All internal Parameters Oracle
  • New Latest Param.sql for finding all hidden parameters also Oracle
  • Paste command syntax Linux/Unix
  • Find Command Linux/Unix
  • Very Good Oralce Internal Tuning Book Oracle
  • Alter procedure auditing Oracle
  • Oracle 10g Installation/Applying Patches Tips Oracle
  • login.sql Oracle
  • scripts to take listener.log backup Linux/Unix
  • pvm_rbs1.sql (to collect rbs info from db) Oracle
  • Identical Dblink Issue… Oracle

Vivek Tuning for Row Locks.

Posted on 03-Nov-2005 By Admin No Comments on Vivek Tuning for Row Locks.

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

rem

rem $Header: utllockt.sql 21-jan-2003.16:21:56 bnnguyen Exp $ locktree.sql

rem

Rem Copyright (c) 1989, 2003, Oracle Corporation. All rights reserved.

Rem NAME

REM UTLLOCKT.SQL

Rem FUNCTION – Print out the lock wait-for graph in tree structured fashion.

Rem This is useful for diagnosing systems that are hung on locks.

Rem NOTES

Rem MODIFIED

Rem bnnguyen 01/21/03 – bug2166717

Rem pgreenwa 04/27/95 – fix column definitions for LOCK_HOLDERS

Rem pgreenwa 04/26/95 – modify lock_holders query to use new dba_locks f

Rem glumpkin 10/20/92 – Renamed from LOCKTREE.SQL

Rem jloaiza 05/24/91 – update for v7

Rem rlim 04/29/91 – change char to varchar2

Rem Loaiza 11/01/89 – Creation

Rem

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

/********************* enq_10_sql.sql *************************/

set lines 132

set pages 2000

col SQ_L format a64 word_wrapped heading “SQL executed by Session waiting on TX Enqueue”

col UN format a10 heading “ORA User”

col OS format a10 heading “OS User”

col MA format a20 heading “Machine”

col SI format 9999 heading “SID”

col SR format 99999 heading “Ser#”

break on SI on SR on UN on OS on MA skip 1

select /*+ORDERED USE_NL(A B)*/ b.sql_Text “SQ_L”,a.sid SI,a.serial# SR,a.username UN,a.osuser OS,

substr(a.machine,1,20) MA

from v$session a,v$SQLTEXT_WITH_NEWLINES b

where a.event = ‘enq: TX – row lock contention’

and a.SQL_ADDRESS=b.ADDRESS

and a.SQL_HASH_VALUE =b.HASH_VALUE

order by a.sid,a.serial#,b.address,b.hash_value,b.piece

/

/************************** lockid.sql **********************/

set lines 132

col object_name format a40

select sid, lockwait,BLOCKING_SESSION,serial#, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#,e.object_name,

ROW_WAIT_ROW#,DBMS_ROWID.ROWID_CREATE(1, e.DATA_OBJECT_ID, d.RELATIVE_FNO, c.ROW_WAIT_BLOCK#, c.ROW_WAIT_ROW# ) locked_rowid

from v$session c,

dba_data_files d,

dba_objects e

where

FILE_ID=c.ROW_WAIT_FILE#

and lockwait is not null

and e.object_id = c.ROW_WAIT_OBJ#

/

/************************ user.sql ***********************/

col osuser format a14

select sid,serial#,SQL_HASH_VALUE,sql_address,PREV_HASH_VALUE,PREV_SQL_ADDR,event,

program,osuser,machine,username,status,to_char(logon_time,’DD-MON-YY HH24:MI:SS’) from v$session where SID = &1

/

/******************** get_spid.sql **************************/

set lines 132 pages 2000

accept val1 number prompt “Enter Oracle SID:”

column spid heading “Unix PID”

column sql_address heading “Address of Current SQL”

column username heading “Oracle Username”

column machine heading “Machine name” format a30

column kill_script heading “Unix Kill script”

select spid ,a.sql_address,a.username,a.machine,’kill -9 ‘||b.SPID kill_script

from v$session a,

v$process b

where a.paddr=b.addr and a.sid = &val1

/

Oracle, SQL scripts

Post navigation

Previous Post: Mutating Table Error while using database trigger
Next Post: Resolving RMAN Hung Jobs

Related Posts

  • find_longsql.sql Oracle
  • AWR settings- MMON is not taking snapshot. Oracle
  • SQLPLUS COPY command Precautions. Oracle
  • Best approach for Oracle database patching sequence to latest/required patchset along with CPU/PSU/any-other-one-off patch ID 865255.1 Oracle
  • 10g RAC: Troubleshooting CRS Root.sh Problems Oracle
  • Create type and Grant on it. 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 (392)
  • PHP/MYSQL/Wordpress (10)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (341)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • load SPM baseline from cursor cache05-Jun-2025
  • Drop all SPM baselines for SQL handle05-Jun-2025
  • Load SPM baseline from AWR05-Jun-2025
  • Drop specific SQL plan baseline – spm05-Jun-2025
  • findinfo.sql (SQL for getting CPU and Active session info)27-May-2025
  • 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

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • Roles and Stored Object behaviour Oracle
  • TABLE SIZING WITH DB_BLOCK ARCHITECTURE Reference : Metalink note : 10640.1 Oracle
  • Create type and Grant on it. Oracle
  • Consolidated Reference List Of Notes For Migration / Upgrade Service Requests -ID 762540.1 Oracle
  • Changing default shell Linux/Unix
  • Getting started with notebook Python/PySpark
  • RAC with RHEL4 and 11g Oracle
  • v$event_name Oracle

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme