Nrthugu

I would like to ask a question :

This is my environment :

• Solaris Version 10; Sun OS Version 5.10


• Oracle Version: 11g Enterprise x64 Edition.

When I am running this query :

select c.owner, c.object_name, c.object_type,b.sid, b.serial#, b.status, b.osuser, b.machine 

  from v$locked_object a , v$session b, dba_objects c 

 where b.sid = a.session_id 

  and a.object_id = c.object_id;

Sometimes I get many status to be 'INACTIVE'.

What does this inactive mean?
Does this will make my db and application slow?
What are the affects of active and inactive status?

What does this inactive mean?

Just before the oracle executable executes a read to get the next "command" that it should execute for its session, it will set its session's state to INACTIVE. After the read completes, it will set it to ACTIVE. It will remain in that state until it is done executing the requested work. Then it will do the whole thing all over again.

Does this will make my db and application slow?

Not necessarily. See the answer to your final question.

What are the affects of active and inactive status?

The consequences of a large number of sessions (ACTIVE or INACTIVE) are significant in two ways.

The first is if the number is monotonically increasing, which would lead one to investigate the possibility that the application is leaking connections. I'm confident that such a catastrophe is not the case otherwise you would have mentioned it specifically.

The second, where the number fluctuates within the declared upper bound, is more likely. According to Andrew Holdsworth and other prominent members of the RWP, some architects allow too many connections in the application's connection pool and they demonstrate what happens (response time and availability consequences) when it is too high. They also have a prescription for how to better define the connection pool's attributes and behavior.

The essence of their argument is that by allowing a large number of connections in the pool, you allow them to all be busy at the same time. Rather than having the application tier queue transactions, the database server may have to play a primary role in queuing for low level resources like disk, CPU, network, and even other things like enqueues.

Even if all the sessions are busy for only a short time and they're contending for various resources, the contention is wasteful and can repeat over and over and over again. It makes more sense to spend extra time devising a good user experience queueing model so that you don't waste resources on what is undoubtedly the most expensive (hardware and software licenses) tier in your architecture.

ACTIVE means the session is currently executing some SQL operations whereas INACTIVE means the opposite. Check out the ORACLE v$session documentation

By nature, a high number of ACTIVE sessions will slow down the whole DBMS including your application. To what extent is hard to say - here you have to look at IO, CPU, etc. loads.

Inactive sessions will have a low impact unless you exceed the maximum session number.

In simple words, an INACTIVE status in v$session means no SQL statement is being executed at the time you check in v$session.

On other hand, if you see a lot of inactive sessions, then first check the last activity time for each session.

What I suspect is that, they might be part of a connection pool, hence they might be getting used frequently. You shouldn't worry about it, since from an application connection perspective, the connection pool will take care of it. You might see such INACTIVE sessions for quite a long time.

It does not at all suggest D/B is slow.

Status INACTIVE means session is not executing any query now.
ACTIVE means it's executing query.