Skip to content

Deadlock analyzes

trace file:

DEADLOCK DETECTED ( ORA-00060 )
[Transaction Deadlock]
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
———Blocker(s)——– ———Waiter(s)———
Resource Name process session holds waits process session holds waits
TM-00021851-00000000 142 644 SX 128 483 SX SSX
TX-000a0005-0001da7d 128 483 X 142 644 X
session 644: DID 0001-008E-00000053 session 483: DID 0001-0080-0000007E
session 483: DID 0001-0080-0000007E session 644: DID 0001-008E-00000053
Rows waited on:
Session 483: no row
Session 644: obj – rowid = 00021F2B – AAAh8rABKAAAHKMAAD
(dictionary objn – 139051, file – 74, block – 29324, slot – 3)
Information on the OTHER waiting sessions:
Session 483:
pid=128 serial=202 audsid=281953658 user: 59/TASGEN
O/S info: user: aswlsadm, term: unknown, ospid: 1234, machine: aipsnc11
program: JDBC Thin Client
application name: JDBC Thin Client, hash value=2546894660
Current SQL Statement:
delete FISR_CLS where FDSVCSCH_ARL_CD = :1 and FLT_NR = :2 and OPS_SUFFIX = :3 and FLT_DT = to_date(:4, ‘YYYY-MM-DD’) and DPT_STA_CD = :5 and ARR_STA_CD = :6 and DBV_CD = :7 and DEPT_CD = :8 and CLS_COS_CD = :9
End of information on OTHER waiting sessions.
Current SQL statement for this session:
delete from fisr_clspktvar where FDSVCSCH_ARL_CD = :1 and DBV_CD = :2 and DEPT_CD = :3 and DPT_STA_CD = :4 and ARR_STA_CD = :5 and CLS_COS_CD = :6 and FLT_NR = :7 and OPS_SUFFIX = :8 and FLT_DT = TO_DATE (’2012-04-19′, ‘YYYY-MM-DD’) and PKT_POL_NR = 2202 and PKT_POL_VAR_ND = 2
===================================================
PROCESS STATE

Get object name

from TM-00021851-00000000  - get 00021851 and convert to decimal – that is object_id

select *
from dba_objects
where object_id = to_number(’00021851′,’XXXXXXXX’)

Different Lock Types and Modes

The most common lock types seen in deadlock graphs are TX and TM locks. These may appear held / requested in a number of modes. It is the lock type and modes which help determine what situation has caused the deadlock.

Lock Mode Mode    Requested Probable Cause
TX X (mode 6) Application row level conflict.
Avoid by recoding the application to ensure  rows are always locked in
a particular order.
TX S (mode 4) There are a number of reasons that a TX lock may be requested in
S mode. See Document 62354.1 for a list of when TX locks are requested in mode 4.
TM SSX (mode 5)
or
S (mode 4)
This is usually related to the existence of foreign key constraints where the columns are not indexed on the child table. See Document 33453.1
for how to locate such constraints. See below for locating the OBJECT being waited on

Although other deadlock scenarios can happen the above are the most common. Read more…

SQL Optimizer

Overview of the Optimize

http://docs.oracle.com/cd/E11882_01/server.112/e25789/sqllangu.htm#i8709

Read more…

Find bind variables

Find bind variables http://shaharear.blogspot.com/2009/02/find-bind-variable-value.html

 

or
SELECT *
FROM TABLE (DBMS_XPLAN.display_cursor (’780868g07fy2f’, NULL, ‘ADVANCED’));

SELECT *
FROM TABLE (DBMS_XPLAN.display_AWR (’780868g07fy2f’,
NULL,
NULL,
‘ADVANCED’));

explain plan

  1.  Run EXPLAIN PLAN with 
    explain plan set statement_id='exp1'
    for
    SELECT
    (...)
  2.  Get your plan
    
    set lines 200;
    set pages 0;
    col plan format a80
    
    SELECT cardinality,COST,BYTES,CPU_COST,IO_COST,
           lpad(' ',level-1)||operation||' '||
           options||' '||object_name Plan
      FROM PLAN_TABLE
    CONNECT BY prior id = parent_id
            AND prior statement_id = statement_id
      START WITH id = 0
            AND statement_id = 'exp1'
      ORDER BY id
    ;

					

trace files

TASK:  Trace all Oracle user activities with bind parameters. 

  • login as sysdba

sqlplus / as sysdba

  • get trace files location

show parameters user_dump dest

  • Get searched user_id
SQL> select user_id from dba_users where username = ‘SCHEMA_NAME’;
   USER_ID
----------
       104
  • turn on tracing (without wait and with bind)

execute dbms_monitor.database_trace_enable(false,true);

  • get all trace files names  with activities of user 104:

find -name ‘*ora*.trc’ -exec grep -l ‘uid=104‘ {} \; > trace_list.lst

  • get trace names from file in loop;

while read line ; do echo -e “$line \n” ; done <trace_list.lst

  • turn off tracing. login as sysdba and execute:

execute dbms_monitor.database_trace_disable;

Read more…

Hello world!

Welcome to WordPress.com. After you read this, you should delete and write your own post, with a new title above. Or hit Add New on the left (of the admin dashboard) to start a fresh post.

Here are some suggestions for your first post.

  1. You can find new ideas for what to blog about by reading the Daily Post.
  2. Add PressThis to your browser. It creates a new blog post for you about any interesting  page you read on the web.
  3. Make some changes to this page, and then hit preview on the right. You can always preview any post or edit it before you share it to the world.
Follow

Get every new post delivered to your Inbox.