Total wait time per session incurred by SQL locks 
Author Message
 Total wait time per session incurred by SQL locks
Is it possible to view, during a session, or at the end of the session, the
total amount of time the session was/is waiting for an Oracle lock(s) to be
released.

For example, if the session has completed and there are three SQL statements
that waited for 10,5 and 50 seconds respectively the total wait time would
be 65 seconds.



Wed, 18 Jun 1902 08:00:00 GMT
 Total wait time per session incurred by SQL locks

The name of the wait may vary slightly with version,
but look at v$session_event for 'enqueue' waits.  You
will need to have timed_statistics = true to get times.
You may also (depending on application) want to
look at something with a name like 'PL/SQL sleep waits'.

Best bet:
    connect as SYS

    create or replace view v$my_session_event
    as
    select * from v$session_event
    where sid in (
            select sid from v$session
           where audsid = userenv('sessionid')
    );

    grant select on v$my_session_event to public;
    create public synonym v$my_session_event;

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Quote:

>Is it possible to view, during a session, or at the end of the session, the
>total amount of time the session was/is waiting for an Oracle lock(s) to be
>released.

>For example, if the session has completed and there are three SQL
statements
>that waited for 10,5 and 50 seconds respectively the total wait time would
>be 65 seconds.



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

 Relevant Pages 

1. IDS license count per user or per session?

2. Performance: Lock Object, Avg Wait Time

3. Wait time for locks

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

5. Computing Login / Logout times | Total complete time

6. Select error: A time out occcured while waiting for memory resources (SQL Server 7.0, SP2)

7. Total bytes per row?

8. How do I return N results per page + total number of results

9. Totals per page

10. Sessions in the lock list with no locks (0,0)

11. Sessions in the lock list with no locks (0,0)

12. Getting blocking / waiting sessions info.


 
Powered by phpBB® Forum Software