Home » RDBMS Server » Performance Tuning » Query Regarding, Average Active Session
| Query Regarding, Average Active Session [message #605641] |
Mon, 13 January 2014 00:25  |
 |
VIP2013
Messages: 91
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  |
John Watson
Messages: 8964
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 #605693 is a reply to message #605656] |
Mon, 13 January 2014 12:29 |
|
VIP2013
Messages: 91
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 1843 times)
[Updated on: Mon, 13 January 2014 13:13] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Sat Feb 01 15:49:49 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
