Miladin Modrakovic's Blog: Oraclue

How to find SQL statements that use SERIALIZABLE isolation level?

I have used  v$sql_optimizer_env ( not x$ table to make it less complicated ) to get this information.
If you like x$ tables than this v$ is based on  x$kqlfsqce table.There are two additional tables x$qkscesys
and  x$qksceses which keep hidden Optimizer Environment.See CBO book from JL and Julian Dyke site for more info..

Doug also has excellent post on these views:

http://oracledoug.com/serendipity/index.php?/archives/907-10g-Optimiser-Environment-Views.html

 So to get sql_text I have joined v$sql_optimizer_env and v$sql table.In my case GV$ because of the RAC.
.

select distinct s.sql_id ,s.sql_text,s.parsing_schema_name,o.name,o.value,o.isdefault
from gv$sql  s,gv$sql_optimizer_env o
where s.parsing_schema_name=’ADMIN’
and s.sql_id=o.sql_id
and o.name=’transaction_isolation_level’
and isdefault <> ‘YES’

 Output looks like:

                                                                        
8a7fgfsr5gffgqgtfqnn  SELECT ‘INSERT’ FROM DUAL WHERE NOT EXISTS (SELECT ‘IN THE QUEUE’)..  ADMIN transaction_isolation_level serializable   NO
 
 
Now I know which queries are involved and focus on the code.

You can always turn on tracing using 10046 and 10053 events  or have dump errostack on error:

alter system set events ’8177 trace name errorstack level 3′
From Oracle documentation default for isolation level is read commited but it can be changed using

set transaction or   alter session command..
Some notes on SERIALIZABLE isolation level

Oracle Database stores control information in each data block to manage access by concurrent transactions.
To use the SERIALIZABLE isolation level, you must use the INITRANS clause
of the CREATE TABLE or ALTER TABLE command to set aside storage for this control information.
To use serializable mode, INITRANS must be set to at least 3.

This must be done at the CREATE TABLE/CREATE INDEX time to ensure that it is set for all blocks of the object.
Also  rebuilding table/index should do the trick..
  Oracle keeps note of which rows are locked by which transaction in an area
  at the top of each data block known as the ‘interested transaction list’.
  The number of ITL slots in any block in an object is controlled by
  the INITRANS and MAXTRANS attributes. INITRANS is the number of slots
  initially created in a block when it is first used, while MAXTRANS places
  an upper bound on the number of entries allowed. Each transaction which
  wants to modify a block requires a slot in this ‘ITL’ list in the block.

  INITRANS provides a minimum guaranteed ‘per-block’ concurrency.

Oracle recored  ITL Waits statistics in v$segment_statistics :
SELECT t.OWNER, t.OBJECT_NAME, t.OBJECT_TYPE, t.STATISTIC_NAME, t.VALUE
     FROM v$segment_statistics t
     WHERE t.STATISTIC_NAME = ‘ITL waits’
     AND t.owner <> ‘SYS’
     AND t.VALUE > 0
     order by t.owner,t.object_name;

 
 Increasing  INITRANS for objects that will experience many transactions updating the same block will enable Oracle to allocate sufficient storage in each block to
 record the history of recent transactions that accessed the block.
 
 Or  change  SERIALIZABLE back to read commited..
 
 If you are using ODP.NET  TransactionIsolationLevel  Specifies the Oracle transaction isolation level. The default is ReadCommitted.