How can that session be blocking me when all it is running is a select?

I've seen this question asked a couple of times in situations where a session is seemingly being blocked by another 'idle' session that 'just ran a select'.
The answer is of course that the sql_id shown in v$session is for the most statement executed in that session which could of course be a select statement, there is nothing to say you have to commit after doing dml, you could easily run several thousand select statements after an insert/update/delete without commiting - so it's easily possible to block another session from something you did earlier on.

Take the example where user 'A' inserts a row into a table called TEST_LOCK.

SQL> create table test_lock (col1 number);

SQL>alter table test_lock add primary key (col1);
SQL>insert into test_lock values(1);

1 row created.



User 'B' now tries to insert the same value into the table

SQL>insert into test_lock values(1);

This just hangs as user 'A' has 'locked' this PK value.

If we look at v$session at this point then we see the sql statement  'insert into test_lock values(1);' listed for both sessions.

select sql_text from v$sql a,v$session b
 where ( a.sql_id =b.sql_id  or a.sql_id=b.PREV_SQL_ID )
and username='PUMPY' and taddr is not null

Note we have to use prev_sql_id here for user 'A' as as soon as we get the row created message back the current sql_id is nulled out and moved to prev_sql_id.

This illustrates the issue quite nicely - as soon as we do something else in session A, we lose track of what SQL is actually causing the block. We know the session and we know that it's obviously done 'something' to the table blocking session 'B' but what was the statement.

We could look (if licenced) into the ASH data and may get lucky and we could even try just searching through v$sql - but we are not guaranteed to find it.

Is there another way?

Well sort of is the answer to that......

As part of the whole suite of flashback related features oracle rolled out (the list seems to be ever growing) a view was created called FLASHBACK_TRANSACTION_QUERY which is based on the fixed table/view called sys.x$ktuqqry - this is populated by the database engine as it goes - however database level supplemental logging has to be enabled to see a lot of the useful information - table level does not seem to be enough.

So lets switch it on:

SQL> alter database add supplemental log data;

We can now query this view -

select * from  flashback_transaction_query where logon_user='PUMPY' and table_name='TEST_LOCK'

	XID	START_SCN	START_TIMESTAMP	COMMIT_SCN	COMMIT_TIMESTAMP	LOGON_USER	UNDO_CHANGE#	OPERATION	TABLE_NAME	TABLE_OWNER	ROW_ID	UNDO_SQL
1	02001300F51E0100	8955577853255	03/01/2014 20:51:38		03/01/2014 13:00:25	PUMPY	1	INSERT	TEST_LOCK	PUMPY	AAAuulAABAAAQYpAAA	delete from "PUMPY"."TEST_LOCK" where ROWID = 'AAAuulAABAAAQYpAAA';
2	0A000800441B0200	8955577853257	03/01/2014 20:51:44		03/01/2014 13:12:44	PUMPY	1	INSERT	TEST_LOCK	PUMPY	AAAuulAABAAAQYpAAC	delete from "PUMPY"."TEST_LOCK" where ROWID = 'AAAuulAABAAAQYpAAC';

And we can at least see 'something'..... User A has clearly inserted a row as the undo_sql shows as a delete statement - however we still can't see the actual SQL - we know it's rowid but that is not that much use as we can't see 'dirty' data from other transactions as Oracle does not support that isolation level.

So how can we see this?

Logminer can help here - but again is reliant on supplemental logging being on to capture everything we need. The interface has simplified slightly and we can now access the data we need in the following way:

First we initialise logminer for the time period we are interested in (easier than specifying all the logfiles - in this case we just use the online dictionary).

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
BEGIN
   DBMS_LOGMNR.START_LOGMNR(
   STARTTIME => '03-Jan-2014 20:00:00',
   ENDTIME => '03-Jan-2014 22:00:00',
   OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG +    DBMS_LOGMNR.CONTINUOUS_MINE);
   END;

Now the contents of the redo logs for that 2 hour window are viewable through the view v$logmnr_contents.

If we now query that for the xid we are interested in:

select * from     v$logmnr_contents where xid='02001300F51E0100';

	SCN	START_SCN	COMMIT_SCN	TIMESTAMP	START_TIMESTAMP	COMMIT_TIMESTAMP	XIDUSN	XIDSLT	XIDSQN	XID	PXIDUSN	PXIDSLT	PXIDSQN	PXID	TX_NAME	OPERATION	OPERATION_CODE	ROLLBACK	SEG_OWNER	SEG_NAME	TABLE_NAME	SEG_TYPE	SEG_TYPE_NAME	TABLE_SPACE	ROW_ID	USERNAME	OS_USERNAME	MACHINE_NAME	AUDIT_SESSIONID	SESSION#	SERIAL#	SESSION_INFO	THREAD#	SEQUENCE#	RBASQN	RBABLK	RBABYTE	UBAFIL	UBABLK	UBAREC	UBASQN	ABS_FILE#	REL_FILE#	DATA_BLK#	DATA_OBJ#	DATA_OBJV#	DATA_OBJD#	SQL_REDO	SQL_UNDO	RS_ID	SSN	CSF	INFO	STATUS	REDO_VALUE	UNDO_VALUE	SAFE_RESUME_SCN	CSCN	OBJECT_ID	EDITION_NAME	CLIENT_ID
1	8955577853255			03/01/2014 20:51:41			2	19	73461	02001300F51E0100	2	19	73461	02001300F51E0100		START	6	0				0			AAAuulAAAAAAAAAAAA	PUMPY	oracle		5125027	298	6531	login_username=PUMPY client_info= OS_username=oracle Machine_name= OS_terminal=pts/0 OS_process_id=10783 OS_program_name=sqlplus@ (TNS V1-V3)	1	1	3591	460357	16	3	0	0	0	0	0	0	0	0	0	set transaction read write;		0x000e07.00070645.0010	0	0		0	214	215
2	8955577853255			03/01/2014 20:51:41			2	19	73461	02001300F51E0100	2	19	73461	02001300F51E0100		INSERT	1	0	PUMPY	TEST_LOCK	TEST_LOCK	2	TABLE	SYSTEM	AAAuulAABAAAQYpAAA	PUMPY	oracle		5125027	298	6531	login_username=PUMPY client_info= OS_username=oracle Machine_name= OS_terminal=pts/0 OS_process_id=10783 OS_program_name=sqlplus@ (TNS V1-V3)	1	1	3591	460357	16	3	12584815	12	19418	1	1	67113	191397	1	191397	insert into "PUMPY"."TEST_LOCK"("COL1") values ('2');	delete from "PUMPY"."TEST_LOCK" where "COL1" = '2' and ROWID = 'AAAuulAABAAAQYpAAA';	0x000e07.00070645.0010	0	0		0	216	217

We can now see the data in the SQL_REDO column - magic!

Lets now switch off log miner and remove that extra logging

BEGIN
DBMS_LOGMNR.END_LOGMNR;
END;

alter database drop supplemental log data;

A useful exercise, but not something that you would need every day, though there are a number of very useful topics in this area mentioned in this note.