Read only blocking
- From: <Joel.Patterson@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 5 Nov 2010 15:38:26 -0400
I have cut out part of a scenario. My question is does anyone know why I would
see session 61, being a read only select query, be blocked, and then
subsequently becoming a blocker?
How can a select be blocked, or block in oracle? Is it a Hibernate thing? I
fairly sure it is not a flushing of blocks. This is a java app and most
queries created by Hibernate. Oracle 10.2.0.4, Solaris 10.
14:30:36 SYS AS SYSDBA @ cosprd> @s:\see\locks\lockblock
SID BLO WAI
------ --- ---
205 YES NO
61 NO YES
91 NO YES
78 NO YES
67 NO YES
85 NO YES
211 NO YES
189 NO YES
191 NO YES
68 NO YES
50 NO YES
11 rows selected.
14:34:33 SYS AS SYSDBA @ cosprd> @s:\see\sessions\startime_sid
Enter value for v_sid: 61
Session Process p.pid p.spid
Session
LOGON_TIME SQL_ID SID Serial# Serial# Ora ProcID OS ProcID
Process Session Username
------------------ ------------- ------ ------- ------- ---------- ------------
------------ --------------------
05-NOV-10 11:44:55 77naw8d41n03d 61 1261 11 32 23128
1234 COS
14:42:32 SYS AS SYSDBA @ cosprd> select executions, SQL_TEXT from v$sql where
sql_id = '77naw8d41n03d';
EXECUTIONS SQL_TEXT
----------
----------------------------------------------------------------------------------------------------
1577672 select contractli0_.CONTRACTLINEITEMID as CONTRACT2_1_,
contractli0_.CONTRACTLINEITEMRATEID as CONTR
ACT1_1_, contractli0_.CONTRACTLINEITEMRATEID as CONTRACT1_187_0_,
contractli0_.CONTRACTLINEITEMID as
CONTRACT2_187_0_, contractli0_.SERVICECODE as SERVICEC3_187_0_,
contractli0_.RATEBASIS as RATEBASIS
187_0_, contractli0_.RATE as RATE187_0_, contractli0_.SUMMBLIND as
SUMMBLIND187_0_, contractli0_.SER
VICEFEE as SERVICEFEE187_0_, contractli0_.CONTRACTSERVICE as
CONTRACT8_187_0_ from COS.CONTRACTLINEI
TEMRATE contractli0_ where contractli0_.CONTRACTLINEITEMID=:1 order
by contractli0_.contractlineitem
rateid asc
14:42:41 SYS AS SYSDBA @ cosprd> alter system kill session '205,3450';
System altered.
14:43:17 SYS AS SYSDBA @ cosprd> @s:\see\locks\lockblock
SID BLO WAI
------ --- ---
61 YES NO
91 YES NO
78 YES NO
78 NO YES
68 NO YES
50 NO YES
189 NO YES
191 NO YES
211 NO YES
200 NO YES
85 NO YES
69 NO YES
91 NO YES
67 NO YES
14 rows selected.
14:43:55 SYS AS SYSDBA @ cosprd> @s:\see\sessions\startime_sid
Enter value for v_sid: 91
Session Process p.pid p.spid
Session
LOGON_TIME SQL_ID SID Serial# Serial# Ora ProcID OS ProcID
Process Session Username
------------------ ------------- ------ ------- ------- ---------- ------------
------------ --------------------
05-NOV-10 14:22:17 bg84ptqnjywfx 91 553 78 34 22758
1234 COS
14:44:22 SYS AS SYSDBA @ cosprd> select executions, SQL_TEXT from v$sql where
sql_id = 'bg84ptqnjywfx';
EXECUTIONS SQL_TEXT
----------
----------------------------------------------------------------------------------------------------
69742 delete from COS.CONTRACTLINEITEMRATE where CONTRACTLINEITEMID is null
14:46:39 SYS AS SYSDBA @ cosprd> alter system kill session '&1,&2';
Enter value for 1: 61
Enter value for 2: 1261
old 1: alter system kill session '&1,&2'
new 1: alter system kill session '61,1261'
SID BLO WAI
------ --- ---
78 YES NO
211 NO YES
50 NO YES
191 NO YES
14:47:37 SYS AS SYSDBA @ cosprd> alter system kill session '&1,&2';
Enter value for 1: 78
Enter value for 2: 42257
old 1: alter system kill session '&1,&2'
new 1: alter system kill session '78,42257'
System altered.
14:47:52 SYS AS SYSDBA @ cosprd> @s:\see\locks\lockblock
SID BLO WAI
------ --- ---
191 YES NO
50 NO YES
211 NO YES
Joel Patterson
Database Administrator
904 727-2546
Joel Patterson
Database Administrator
904 727-2546
Other related posts:
