Dion Cho – Oracle Performance Storyteller

We are natural born scientists

Archive for the ‘Transaction’ Category

Detecting serializable transaction

leave a comment »

One of our customers had this problem.

We hit ORA-08177 : can’t serialize access for this transaction error without any reason. As far as I know, we do not use serializable transaction. What should I do now?

My recommendation was to check it themselves whether they actually have serializable transactions.

Metalink note# 252545.1 provides a useful technique to identify serializable transaction. Here goes a very simple test case to demonstrate it.

1. Create serializable transaction in session #1

UKJA@ukja102> set transaction isolation level serializable;

Transaction set.

UKJA@ukja102> update t1 set c1 = c1 + 1 where rownum = 1;

1 row updated.




2. Check which session is transacting serializably in session #2

UKJA@ukja102> select s.sid, s.serial#,
  2    substr(q.sql_text, 1, 100) as sql_text
  3  from
  4    v$session s, v$sql q
  5  where
  6    s.taddr = (select addr from v$transaction
  7        where bitand(flag,268435456) = 268435456)
  8    and ((s.prev_hash_value = q.hash_value and s.prev_sql_addr = q.address)
  9         or (s.sql_hash_value = q.hash_value and s.sql_address = q.address))
 10  ;

       SID    SERIAL#
---------- ----------
SQL_TEXT
--------------------------------------------------------------------------------
       149        829
update t1 set c1 = c1 + 1 where rownum = 1




Cool, isn’t it?

=========================================================

One tiny complaint(but not significant here) is the potential performance problem of the above query. See the execution plan.

UKJA@ukja102> @plan
--------------------------------------------------------------
| Id  | Operation                | Name              | Rows  |
--------------------------------------------------------------
|   0 | SELECT STATEMENT         |                   |     1 |
|   1 |  NESTED LOOPS            |                   |     1 |
|   2 |   NESTED LOOPS           |                   |     1 |
|*  3 |    FIXED TABLE FULL      | X$KGLCURSOR_CHILD |     1 |
|*  4 |    FIXED TABLE FULL      | X$KSUSE           |     1 |
|*  5 |     FIXED TABLE FULL     | X$KTCXB           |     1 |
|*  6 |   FIXED TABLE FIXED INDEX| X$KSLED (ind:2)   |     1 |
--------------------------------------------------------------





It full scans the sql child cursors which means that the performance might not be good when you have tons of child cursors. The X$KGLCHILD_CURSOR(which is actually X$KGLOB) has following indexes.

UKJA@ukja102> select * from v$indexed_fixed_column
  2  where table_name = 'X$KGLOB';

TABLE_NAME           INDEX_NUMBER COLUMN_NAME          COLUMN_POSITION
-------------------- ------------ -------------------- ---------------
X$KGLOB                         1 KGLNAHSH                           0
X$KGLOB                         2 KGLOBT03                           0





So I would rewrite the query using union all.

UKJA@ukja102> select s.sid, s.serial#,
  2    substr(q.sql_text, 1, 100) as sql_text
  3  from
  4    v$session s, v$sql q
  5  where
  6    s.taddr = (select addr from v$transaction
  7        where bitand(flag,268435456) = 268435456)
  8    and (s.prev_hash_value = q.hash_value and s.prev_sql_addr = q.address)
  9  union all
 10   select s.sid, s.serial#,
 11    substr(q.sql_text, 1, 100) as sql_text
 12  from
 13    v$session s, v$sql q
 14  where
 15    s.taddr = (select addr from v$transaction
 16        where bitand(flag,268435456) = 268435456)
 17    and (s.sql_hash_value = q.hash_value and s.sql_address = q.address)
 18  ;

------------------------------------------------------------------------
| Id  | Operation                  | Name                      | Rows  |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                           |     2 |
|   1 |  UNION-ALL                 |                           |       |
|   2 |   NESTED LOOPS             |                           |     1 |
|   3 |    NESTED LOOPS            |                           |     1 |
|*  4 |     FIXED TABLE FULL       | X$KSUSE                   |     1 |
|*  5 |      FIXED TABLE FULL      | X$KTCXB                   |     1 |
|*  6 |     FIXED TABLE FIXED INDEX| X$KSLED (ind:2)           |     1 |
|*  7 |    FIXED TABLE FIXED INDEX | X$KGLCURSOR_CHILD (ind:1) |     1 |
|   8 |   NESTED LOOPS             |                           |     1 |
|   9 |    NESTED LOOPS            |                           |     1 |
|* 10 |     FIXED TABLE FULL       | X$KSUSE                   |     1 |
|* 11 |      FIXED TABLE FULL      | X$KTCXB                   |     1 |
|* 12 |     FIXED TABLE FIXED INDEX| X$KSLED (ind:2)           |     1 |
|* 13 |    FIXED TABLE FIXED INDEX | X$KGLCURSOR_CHILD (ind:1) |     1 |
------------------------------------------------------------------------





As I mentioned here, a general query tuning technique should always be applied to queries on v$ or x$ views.

Written by Dion Cho

April 10, 2009 at 8:18 am

Follow

Get every new post delivered to your Inbox.

Join 58 other followers