Re: Read only blocking
- From: Sandra Becker <sbecker6925@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 23 Nov 2010 13:40:38 -0700
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:
