Getting blocking / waiting sessions info 
Author Message
 Getting blocking / waiting sessions info

I want to get session statistics and I have script for that.
However, the SQL used for getting blocking sessions and waiting
sessions per sid takes lot of time to execute, especially the second
one.
SQL stmts I use for this are :
1. select count(*) from dba_waiters group by holding_sessions;
2. select count(*) from dba_waiters group by waiting_sessions;

Could anybody please tell me why it's taking long time.
If this table is not the correct one to get this information, which
tables should I use to get this information.

Thanks in advance,
Sunil.

Sent via Deja.com http://www.***.com/
Before you buy.



Wed, 18 Jun 1902 08:00:00 GMT
 Getting blocking / waiting sessions info



Quote:
> I want to get session statistics and I have script for that.
> However, the SQL used for getting blocking sessions and waiting
> sessions per sid takes lot of time to execute, especially the second
> one.
> SQL stmts I use for this are :
> 1. select count(*) from dba_waiters group by holding_sessions;
> 2. select count(*) from dba_waiters group by waiting_sessions;

> Could anybody please tell me why it's taking long time.
> If this table is not the correct one to get this information, which
> tables should I use to get this information.

I dunno which Oracle version you're using but on 8.0.4 you could run
the following:
1.select count(*) from pending_sessions$
2.select count(*) from pending_sub_sessions$

Cheers
JC

Quote:
> Thanks in advance,
> Sunil.

> Sent via Deja.com http://www.deja.com/
> Before you buy.

Sent via Deja.com http://www.deja.com/
Before you buy.


Wed, 18 Jun 1902 08:00:00 GMT
 Getting blocking / waiting sessions info
My application is generic, which runs on any Oracle version from 7.3.2
onwards. So far my query was working fine. But recently when checked
particularly when Oracle was having lo of load, took lot of time. Even
that time 3 sids were holding 28 locks.
I am not sure, if that was really too much load or not, but other
queries were working fine.
Anyway I will try with the queries you provided.


Quote:



> > I want to get session statistics and I have script for that.
> > However, the SQL used for getting blocking sessions and waiting
> > sessions per sid takes lot of time to execute, especially the second
> > one.
> > SQL stmts I use for this are :
> > 1. select count(*) from dba_waiters group by holding_sessions;
> > 2. select count(*) from dba_waiters group by waiting_sessions;

> > Could anybody please tell me why it's taking long time.
> > If this table is not the correct one to get this information, which
> > tables should I use to get this information.

> I dunno which Oracle version you're using but on 8.0.4 you could run
> the following:
> 1.select count(*) from pending_sessions$
> 2.select count(*) from pending_sub_sessions$

> Cheers
> JC
> > Thanks in advance,
> > Sunil.

> > Sent via Deja.com http://www.deja.com/
> > Before you buy.

> Sent via Deja.com http://www.deja.com/
> Before you buy.

Sent via Deja.com http://www.deja.com/
Before you buy.


Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Getting blocking / waiting sessions info.

2. q: buffer busy wait(data block wait) when P3=130

3. Blocking processes - getting some info

4. Wait Message during block execute

5. hot block..buffer busy waits.. questions

6. buffer busy waits for data block

7. Wait for credit - send blocked

8. high data block waits in v$waitstat 8.1.7

9. Trace SQL in a session with bind and wait events 8i

10. Total wait time per session incurred by SQL locks

11. i/o waits by session?

12. Session is waiting for long....


 
Powered by phpBB® Forum Software