Home » RDBMS Server » Server Administration » Blocking Session in v$session (Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 Linux as 4)
Blocking Session in v$session [message #561888] Thu, 26 July 2012 01:57 Go to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Hi All,
I had a strange situation today.

I `ve 2 sessions with SID 375,659.

On Session 659 ,I am describing One Procedure or compiling one procedure.Its taking lot of time to complete or to return So From Sid# 375 I am monitoring it for the Blocking session.

Dev>  select
  2      blocking_session,
  3   blocking_instance,
  4      sid,
  5      serial#,
  6      wait_class,
  7      seconds_in_wait,wait_time,state
  8   from
  9      v$session
 10   where
 11      blocking_session is not NULL
 12   order by
 13      blocking_session
 14   /

BLOCKING_SESSION BLOCKING_INSTANCE        SID    SERIAL# WAIT_CLASS    SECONDS_IN_WAIT  WAIT_TIME STATE
---------------- ----------------- ---------- ---------- ------------- --------------- ---------- --
             593                 1        659      48646 Concurrency               459          0 WAITING


But when I verified for Blocking session There are no sessions with that sid(593) in v$session view.

Dev> select count(*) from v$session where sid=593;

  COUNT(*)
----------
         0

1 row selected.



It was In wait state Upto seconds_in_wait 900 approx. Then Again Its restarted from 0.
Please see below.


Dev> /

BLOCKING_SESSION BLOCKING_SE BLOCKING_INSTANCE        SID    SERIAL#
---------------- ----------- ----------------- ---------- ----------
WAIT_CLASS    SECONDS_IN_WAIT  WAIT_TIME STATE
------------- --------------- ---------- -------------------
             593 VALID                       1        659      48646
Concurrency               898          0 WAITING


1 row selected.

Dev> /

no rows selected

Dev> /

BLOCKING_SESSION BLOCKING_SE BLOCKING_INSTANCE        SID    SERIAL#
---------------- ----------- ----------------- ---------- ----------
WAIT_CLASS    SECONDS_IN_WAIT  WAIT_TIME STATE
------------- --------------- ---------- -------------------
             593 VALID                       1        659      48646
Concurrency                 0          0 WAITING


1 row selected.

Dev> /

BLOCKING_SESSION BLOCKING_SE BLOCKING_INSTANCE        SID    SERIAL#
---------------- ----------- ----------------- ---------- ----------
WAIT_CLASS    SECONDS_IN_WAIT  WAIT_TIME STATE
------------- --------------- ---------- -------------------
             593 VALID                       1        659      48646
Concurrency                 3          0 WAITING


1 row selected.


But When I killed the session with sid 659 blocking session 593 also got cleared.

Dev>  select sid,serial#,program,terminal,type ,username,status,sql_id,prev_sql_id
  2   from v$session
  3   where username='Sriram' and terminal='Machine1'
  4   /

       SID    SERIAL# PROGRAM      TERMINAL TYPE       USERNAME   STATUS
---------- ---------- ------------ -------- ---------- ---------- --------
SQL_ID        PREV_SQL_ID
------------- -------------
       375      58962 sqlplusw.exe Machine1  USER       Sriram    ACTIVE
89dr4vkmr9jfs 633nsfc8bvnm3

       659      48646 sqlplusw.exe Machine1  USER       Sriram   ACTIVE
              cvv8a6j3z0g3s


2 rows selected.

Dev> alter system disconnect session '659,48646' immediate;



Dev>   select
  2       blocking_session,
  3   blocking_session_status,
  4    blocking_instance,
  5       sid,
  6       serial#,
  7       wait_class,
  8       seconds_in_wait,wait_time,state
  9    from
 10       v$session
 11    where
 12       blocking_session is not NULL
 13    order by
 14       blocking_session
 15   /

no rows selected



Whats Causing the Problem here. Am I missing anything here ?
Please suggest !

Thank you
Sriram Sanka

Report message to a moderator

Re: Blocking Session in v$session [message #561903 is a reply to message #561888] Thu, 26 July 2012 04:23 Go to previous messageGo to next message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
May I ask one question: What does the procedure do?

Report message to a moderator

Re: Blocking Session in v$session [message #561970 is a reply to message #561903] Thu, 26 July 2012 08:57 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>On Session 659 ,I am describing One Procedure or compiling one procedure.Its taking lot of time to complete or to return So From Sid# 375 I am monitoring it for the Blocking session.


when a procedure does DML, then Oracle prevents it from being recompiled until after COMMIT or ROLLBACK is issued.

Report message to a moderator

Re: Blocking Session in v$session [message #562165 is a reply to message #561970] Mon, 30 July 2012 02:06 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
@ Blackswan
There are no DML in the Procedure/Function. Its Just Purely a select statement based on the argument value..

Like for Example ...Its a Function which works as below


If idate >sysdate then <select data into result from <table_name>>
elsif idate <sysdate then <select data into result from <table_name>>
End if;

and return(result)

Report message to a moderator

Re: Blocking Session in v$session [message #562228 is a reply to message #562165] Mon, 30 July 2012 08:39 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
within Oracle, Readers do not block Writers & Writers do not block Readers.

perhaps the blockage only exists between you ears.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/


post SQL & results that shows what you claim is true.

Report message to a moderator

Re: Blocking Session in v$session [message #567234 is a reply to message #562228] Tue, 25 September 2012 08:20 Go to previous messageGo to next message
AnilKampiri
Messages: 69
Registered: September 2012
Location: Chennai
Member
Enable Trace of your session and analyze on it.

Regards,
Anil

Report message to a moderator

Re: Blocking Session in v$session [message #567240 is a reply to message #567234] Tue, 25 September 2012 09:33 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Do you really think It is still blocking ? Wink

Report message to a moderator

icon14.gif  Re: Blocking Session in v$session [message #567279 is a reply to message #567240] Wed, 26 September 2012 01:33 Go to previous message
AnilKampiri
Messages: 69
Registered: September 2012
Location: Chennai
Member
hi,

I mean to say that just enable trace of your session when you faced such scenario again.

Regards,
Anil

Report message to a moderator

Previous Topic: howto Change default XML Storage
Next Topic: user privileges changed misteriously
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Dec 01 14:31:44 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.