Re: Read only blocking

I see the same thing very frequently.  Did anyone send you the reasons
behind this?  Exec management wants answers.

Sandy
Transzap, Inc.

On Fri, Nov 5, 2010 at 1:38 PM, <Joel.Patterson@xxxxxxxxxxx> wrote:

>
> 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
>
>
>



-- 
Sandy
Transzap, Inc.

Other related posts: