Home » RDBMS Server » Performance Tuning » Query Regarding, Average Active Session
Query Regarding, Average Active Session [message #605641] Mon, 13 January 2014 00:25 Go to next message
VIP2013
Messages: 84
Registered: June 2013
Member
Hi , I am using Release 11.2.0.3.0 of oracle.

I am new to this prod environment, i was extracting the AAS(average active sessions data) for this(by combining dba_hist_snapshot,dba_hist_system_event) , to have idea regarding the daily activity. Also its matching with OEM data too.


My question is ,during revisting the past data, during the business hour, mostly i am getting AAS < 40, but at some point in time i am gettinng the value too large i.e. to the order ~4882550, and at this point, i found , almost 100% of the DB time has been spent in 'DB CPU' only, which is good.


But what i have read that , if the the no of AAS will be >= the no of CPU count, then it might be the sign of coming performace issue, and need to be taken into analysis. So just want expert advice on this existing stats and its actual interpretation, if its really alarming or i am interpreting in wrong way?


below are few information about database:
Its a two node RAC DB having cpu_count value as 42 each. Also i am seeing 1hr difference between each of the snap stored in dba_hist_snapshot.


below is the part of the query which i am using, for deriving AAS data. and considering, only those specific to stats 'DB TIME'

SELECT snap_id,
         bet,
         MAX (v) OVER (PARTITION BY snap_id) dbt,
         stat_name,
         v,
         ROUND (100 * (v / (MAX (v) OVER (PARTITION BY snap_id))), 2) pctdbt,
         v / secs aas
    FROM (  SELECT snap_id,
                   TO_CHAR (begin_interval_time, 'DD-MON-RR HH24:MI') bet,
                   stat_name,
                   SUM (v) v,
                   secs
              FROM (SELECT t1.snap_id,
                           s1.begin_interval_time,
                           86400
                           * (TO_DATE (
                                 TO_CHAR (s2.begin_interval_time,
                                          'DD-MON-RR HH24:MI:SS'),
                                 'DD-MON-RR HH24:MI:SS')
                              - TO_DATE (
                                   TO_CHAR (s1.begin_interval_time,
                                            'DD-MON-RR HH24:MI:SS'),
                                   'DD-MON-RR HH24:MI:SS'))
                              secs,
                           t1.stat_name,
                           (t2.VALUE - t1.VALUE) / 1000000 v
                      FROM dba_hist_sys_time_model t1,
                           dba_hist_sys_time_model t2,
                           dba_hist_snapshot s1,
                           dba_hist_snapshot s2
                     WHERE     t1.stat_name = 'DB time'
                           AND s1.dbid = (SELECT dbid FROM v$database)
                           AND s1.dbid = s2.dbid
                           AND s1.instance_number = s2.instance_number
                           AND t1.stat_name = t2.stat_name
                           AND s2.snap_id = s1.snap_id + 1
                           AND s2.snap_id = t2.snap_id
                           AND s1.snap_id = t1.snap_id
                           AND s2.instance_number = t2.instance_number
                           AND s2.dbid = t2.dbid
                           AND s1.instance_number = t1.instance_number
                           AND s1.dbid = t1.dbid
                           AND s1.snap_id BETWEEN :bid AND :eid
                    UNION
                    SELECT t1.snap_id,
                           s1.begin_interval_time,
                           86400
                           * (TO_DATE (
                                 TO_CHAR (s2.begin_interval_time,
                                          'DD-MON-RR HH24:MI:SS'),
                                 'DD-MON-RR HH24:MI:SS')
                              - TO_DATE (
                                   TO_CHAR (s1.begin_interval_time,
                                            'DD-MON-RR HH24:MI:SS'),
                                   'DD-MON-RR HH24:MI:SS'))
                              secs,
                           t1.stat_name,
                           (t2.VALUE - t1.VALUE) / 1000000 v
                      FROM dba_hist_sys_time_model t1,
                           dba_hist_sys_time_model t2,
                           dba_hist_snapshot s1,
                           dba_hist_snapshot s2
                     WHERE     t1.stat_name = 'DB CPU'
                           AND s1.dbid = (SELECT dbid FROM v$database)
                           AND s1.dbid = s2.dbid
                           AND s1.instance_number = s2.instance_number
                           AND t1.stat_name = t2.stat_name
                           AND s2.snap_id = s1.snap_id + 1
                           AND s2.snap_id = t2.snap_id
                           AND s1.snap_id = t1.snap_id
                           AND s2.instance_number = t2.instance_number
                           AND s2.dbid = t2.dbid
                           AND s1.instance_number = t1.instance_number
                           AND s1.dbid = t1.dbid
                           AND s1.snap_id BETWEEN :bid AND :eid
                    UNION
                    SELECT e1.snap_id,
                           s1.begin_interval_time,
                           86400
                           * (TO_DATE (
                                 TO_CHAR (s2.begin_interval_time,
                                          'DD-MON-RR HH24:MI:SS'),
                                 'DD-MON-RR HH24:MI:SS')
                              - TO_DATE (
                                   TO_CHAR (s1.begin_interval_time,
                                            'DD-MON-RR HH24:MI:SS'),
                                   'DD-MON-RR HH24:MI:SS'))
                              secs,
                           e1.event_name,
                           (e2.time_waited_micro_fg - e1.time_waited_micro_fg)
                           / 1000000
                              v
                      FROM dba_hist_system_event e1,
                           dba_hist_system_event e2,
                           dba_hist_snapshot s1,
                           dba_hist_snapshot s2
                     WHERE     s1.dbid = (SELECT dbid FROM v$database)
                           AND s1.dbid = s2.dbid
                           AND s1.instance_number = s2.instance_number
                           AND e1.wait_class <> 'Idle'
                           AND e1.event_name = e2.event_name
                           AND e1.event_id = e2.event_id
                           AND e2.total_waits > NVL (e1.total_waits, 0)
                           AND s2.snap_id = s1.snap_id + 1
                           AND s2.snap_id = e2.snap_id
                           AND s1.snap_id = e1.snap_id
                           AND s2.instance_number = e2.instance_number
                           AND s2.dbid = e2.dbid
                           AND s1.instance_number = e1.instance_number
                           AND s1.dbid = e1.dbid
                           AND s1.snap_id BETWEEN :bid AND :eid)
             WHERE v > 1
          GROUP BY snap_id,
                   TO_CHAR (begin_interval_time, 'DD-MON-RR HH24:MI'),
                   stat_name,
                   secs)
ORDER BY snap_id, 5 DESC
Re: Query Regarding, Average Active Session [message #605654 is a reply to message #605641] Mon, 13 January 2014 02:53 Go to previous messageGo to next message
John Watson
Messages: 4477
Registered: January 2010
Location: Global Village
Senior Member
Can you explain what it is that you need that you cannot find in the pre-programmed ASH and AWR reports? It would take forever to work out what your code is trying to do, so it will be easier if you explain. And also, from where did you get that query? The author may have documented it.
Re: Query Regarding, Average Active Session [message #605656 is a reply to message #605654] Mon, 13 January 2014 02:57 Go to previous messageGo to next message
cookiemonster
Messages: 10899
Registered: September 2008
Location: Rainy Manchester
Senior Member
Also an AAS of ~4882550 would indicate the calculation has gone wrong somewhere.
Re: Query Regarding, Average Active Session [message #605693 is a reply to message #605656] Mon, 13 January 2014 12:29 Go to previous message
VIP2013
Messages: 84
Registered: June 2013
Member
Thank you all. The first 'Select' query(for UNION clause) will give the total 'DB TIME' value for the snapid from 'dba_hist_sys_time_model' and the second 'Select' query(for UNION clause) will give the 'DB CPU' value for the same snapid and the third 'Select' will give the total time for the same snapid for all the 'wait classes'. So basically DB time='db cpu'+'all the time spent in different wait classes'.

aas(average active session) = db time/clocktime.

Hope its clear.

Attached is the screenshot of the past(7 days) active session history from OEM, and it matches with the query results.
  • Attachment: Untitled.png
    (Size: 34.57KB, Downloaded 69 times)

[Updated on: Mon, 13 January 2014 13:13]

Report message to a moderator

Previous Topic: Performance issue
Next Topic: general view is also takin long time, all of sudden...
Goto Forum:
  


Current Time: Wed Aug 20 06:10:52 CDT 2014

Total time taken to generate the page: 0.05395 seconds