Read only blocking

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: