OraFAQ Forum: Server Administration » database locks with inactive sessions

Home » RDBMS Server » Server Administration » database locks with inactive sessions (solaris 10 oracle 10.2.0.3)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

database locks with inactive sessions [message #341937]

Wed, 20 August 2008 15:44

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

Report message to a moderator

Re: database locks with inactive sessions [message #341939 is a reply to message #341937]

Wed, 20 August 2008 15:54

BlackSwan
Messages: 26766
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?

Report message to a moderator

Re: database locks with inactive sessions [message #341943 is a reply to message #341939]

Wed, 20 August 2008 16:12

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.

Report message to a moderator

Re: database locks with inactive sessions [message #341945 is a reply to message #341937]

Wed, 20 August 2008 16:27

BlackSwan
Messages: 26766
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?

Report message to a moderator

Previous Topic:	Server Hang Problem
Next Topic:	database links

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 29 19:30:45 CST 2024