This forum is currently locked. You can't register or post questions at this time. (read more)

Need help on the query for V$active_session_history

All posts relating to Oracle database administration.

Moderator: Tim...

caesardutta
Senior Member
Posts: 58
Joined: Thu Jul 15, 2010 11:11 am

Need help on the query for V$active_session_history

Postby caesardutta » Thu Jun 06, 2013 10:22 am

Dear Sirs:

I am running the following query to get wait events for last 15 minutes. I have confusion with time as well as why one
row is there with "Event name" as blank. Please help.

Thanks and regards,

caesar

Query:

Code: Select all

SELECT a.event,
SUM(a.wait_time +
a.time_waited) total_wait_time
FROM v$active_session_history a
WHERE a.sample_time between
sysdate - 30/2880 and sysdate
GROUP BY a.event
ORDER BY total_wait_time DESC



Result

Code: Select all

EVENT                                                            TOTAL_WAIT_TIME
---------------------------------------------------------------- ---------------
cr request retry                                                       105461147
                                                                        66395440
TCP Socket (KGAS)                                                       28919529
db file sequential read                                                  3741430
db file scattered read                                                   1499461
reliable message                                                         1133853
db file parallel read                                                     717652
gc buffer busy                                                            396941
db file parallel write                                                    222609
log file sync                                                             194655
log file parallel write                                                   194588

EVENT                                                            TOTAL_WAIT_TIME
---------------------------------------------------------------- ---------------
control file sequential read                                              130846
read by other session                                                     121621
gc current grant busy                                                      30013
gc cr block 2-way                                                          29833
control file parallel write                                                27531
cursor: pin S wait on X                                                    10304
gc cr grant 2-way                                                           9384
gc current block 2-way                                                      8629
row cache lock                                                              2309
gc cr block busy                                                            1225
gc cr multi block request                                                    962

EVENT                                                            TOTAL_WAIT_TIME
---------------------------------------------------------------- ---------------
SQL*Net more data to client                                                   53
null event                                                                     0
gc cr request                                                                  0

25 rows selected.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Need help on the query for V$active_session_history

Postby Tim... » Thu Jun 06, 2013 10:44 am

Hi.

You've fallen for a classic trap where ASH is concerned. The data is a sample, so you can't use the wait time in this way. Let's say you have a single event that lasts 5 seconds. The samples over that period will be:

Code: Select all

Event              Sample Wait Time
================== ====== =========
cr request retry        1 1 seconds
cr request retry        2 2 seconds
cr request retry        3 3 seconds
cr request retry        4 4 seconds
cr request retry        5 5 seconds


So the event has lasted 5 seconds, but if you add the wait times together it looks like you've waited 15 seconds. Why, because the wait time has been incrementing between each sample.

You never analyze wait times in ASH like this. Instead, you "guestimate" the times of the events using a count, which assumes any events happening at sample time will last the whole second. This lack of complete accuracy is one of the things you must understand before using ASH. It sounds like it may be a problem, but in reality it rarely is. :)

So your query would be better written as:

Code: Select all

SELECT NVL(a.event, 'ON CPU') AS event, COUNT(*) as total_wait_time
FROM v$active_session_history a
WHERE a.sample_time between
sysdate - 30/2880 and sysdate
GROUP BY a.event
ORDER BY total_wait_time DESC;


The blank event is when the wait is ON CPU. That is explained in the doc. The time columns in the ASH views are actually pretty complex, because their values mean different things depending on the state of the event.

If in doubt, check the docs. :)

http://docs.oracle.com/cd/E11882_01/ser ... s_1007.htm

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Need help on the query for V$active_session_history

Postby Tim... » Thu Jun 06, 2013 10:49 am

I've deleted your second question because as far as I could see it was the same as this. Please just progress one thread on a specific subject. Only start a new thread for a new question... :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

caesardutta
Senior Member
Posts: 58
Joined: Thu Jul 15, 2010 11:11 am

Re: Need help on the query for V$active_session_history

Postby caesardutta » Thu Jun 06, 2013 11:13 am

Awe some reply Tim. Thanks so much for clearing the doubt. Yes - I should not have created the other thread.

Caesar

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Need help on the query for V$active_session_history

Postby Tim... » Thu Jun 06, 2013 11:51 am

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 4 guests

cron