Home » RDBMS Server » Server Administration » database locks with inactive sessions (solaris 10 oracle 10.2.0.3)
database locks with inactive sessions [message #341937] Wed, 20 August 2008 15:44 Go to next message
kumar.joy
Messages: 69
Registered: August 2007
Location: morrisville
Member
Experts,

i getting database locks with inactive sessions. this is happening frequently. when i check the status of lock holding session it is showing "INACTIVE". after some time the status remains back to "ACTIVE". i am not able to find the problem.

when i check the session I/O of the inactive session it is doing some I/O

please find the details
                                                                                   Database Locks
ADDR             KADDR               SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK BLOCKING                 WAITING
---------------- ---------------- ------ -- ---------- ---------- ---------- ---------- ---------- ---------- ------------------------ ---------------------
0000000456CEC770 0000000456CEC7A8   1614 TX    2228228    3013922          6          0        125          1 Blocking
00000004943C9CB8 00000004943C9CD8    520 TX    2228228    3013922          0          6        125          0          


SYSTEM> select status from v$session where sid=1614;

                                                                                   Database Locks
STATUS
------------------------
INACTIVE
------------------------------------------

SYSTEM> /

       SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES CONSISTENT_CHANGES
---------- ---------- --------------- -------------- ------------- ------------------
      1614     211463          489767           6585        104681              25891
SYSTEM> /

       SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES CONSISTENT_CHANGES
---------- ---------- --------------- -------------- ------------- ------------------
      1614     211480          489772           6585        104693              25891
SYSTEM> /

       SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES CONSISTENT_CHANGES
---------- ---------- --------------- -------------- ------------- ------------------
      1614     211480          489772           6585        104693              25891
SYSTEM> /

       SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES CONSISTENT_CHANGES
---------- ---------- --------------- -------------- ------------- ------------------
      1614     211650          489822           6585        104813              25891




i am not able to understand what could be the problem. can any one help me find out the problem

Re: database locks with inactive sessions [message #341939 is a reply to message #341937] Wed, 20 August 2008 15:54 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
HUH? STATUS within V$SESSION has NOTHING to do with locks or locking!

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2088.htm#sthref2731


STATUS 	VARCHAR2(8) 	Status of the session:

    *

      ACTIVE - Session currently executing SQL
    *

      INACTIVE
    *

      KILLED - Session marked to be killed
    *

      CACHED - Session temporarily cached for use by Oracle*XA
    *

      SNIPED - Session inactive, waiting on the client


Are you bored & looking for problems to be solved?
Re: database locks with inactive sessions [message #341943 is a reply to message #341939] Wed, 20 August 2008 16:12 Go to previous messageGo to next message
kumar.joy
Messages: 69
Registered: August 2007
Location: morrisville
Member
Thanks for reply.

i had some question. are you saying that we don't consider session status in the locks?


where i am doing wrong. how to pinpoint this problem.
can you guide me ..

one more question i have.

is session status is 'INACTIVE' Means that it it not making sql call to oracle. it it right?

if yes, then why i am able to see session i/o.

at what condition session are becomes inactive.


Re: database locks with inactive sessions [message #341945 is a reply to message #341937] Wed, 20 August 2008 16:27 Go to previous message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

>are you saying that we don't consider session status in the locks?
The STATUS field of V$SESSION is contains no relevant information regarding "locks'.


?how to pinpoint this problem.
Problem? What problem? Why do you think a problem exists?

>is session status is 'INACTIVE' Means that it it not making sql >call to oracle. it it right?
>if yes, then why i am able to see session i/o.

I have no idea how quickly the STATUS field is updated.
When you query STATUS you get the present value of it.
A microsecond before or a microsecond later it may contain a different value.
Why are you fixated on the contents of V$SESSION.STATUS?
You can not control the contents & I can't think of any meaningful action you could or should initiate based upon its value.


>at what condition session are becomes inactive.
No SQL being processed for this session.

What problem do you think needs to be solved?
Based upon what criteria would an independent observer conclude
your problem has actually been solved?
Previous Topic: Server Hang Problem
Next Topic: database links
Goto Forum:
  


Current Time: Thu Dec 08 18:41:51 CST 2016

Total time taken to generate the page: 0.07380 seconds