Oracle internals, debugging and undocumented features
Debugging serializable transactions.. ORA-08177: can’t serialize access for this transaction
March 2, 2009Posted by on
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:
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
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.