statspack , LAG - does this look OK
- From: BN <bnsarma@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 26 Oct 2007 10:26:14 -0400
Greetings
Please see the sql and the out put, have a few questions (Orale 8.1.7.4 and
9i Rel2, will be doing the same for 10g also):
Want to send this as a report for DEV team on how we are doing with DB:
I am calculating per minute values.
STATSPACK Report shows these values, but I have to pull the report every
hour and compile them to show this info.
1. Am I doing it right
2. Is it efficent or is there a better way to do this
3. See some negative values, I am guessing the previous values were higher
than the present, what do I do in such cases
4, Does the numbers make sense or am I wasting my time on this
5. If this is OK, planning to add CPU used by this session, db block gets,
consistent gets, physical reads
6. How can I add consistent gets+ db block get to show LIO
7. Does it make sense to put these values in columns, if Yes, How
Apprecite your Help
**
*select snap_time, name, round( (value -lag(value) over (order by s.snap_id)
)/
round( (SNAP_TIME - lag(SNAP_TIME) over (ORDER BY s.snap_id))* 1440 *
60/60,0), 0) "Per Min"
from stats$sysstat s, stats$snapshot t
where
-- trunc(t.SNAP_TIME)=trunc(sysdate) and
s.SNAP_ID=t.SNAP_ID and
name = 'user commits'
UNION ALL
select snap_time, name, round( (value -lag(value) over (order by s.snap_id)
)/
round( (SNAP_TIME - lag(SNAP_TIME) over (ORDER BY s.snap_id))* 1440 *
60/60,0), 0) "Per Min"
from stats$sysstat s, stats$snapshot t
where
-- trunc(t.SNAP_TIME)=trunc(sysdate) and
s.SNAP_ID=t.SNAP_ID and
name = 'user rollbacks'
UNION ALL
select snap_time, name, round( (value -lag(value) over (order by s.snap_id)
)/
round( (SNAP_TIME - lag(SNAP_TIME) over (ORDER BY s.snap_id))* 1440 *
60/60,0), 0) "Per Min"
from stats$sysstat s, stats$snapshot t
where
-- trunc(t.SNAP_TIME)=trunc(sysdate) and
s.SNAP_ID=t.SNAP_ID and
name = 'physical writes'
ORDER BY SNAP_TIME, NAME
/*
OutPut :
SNAP_TIME NAME Per Min
------------------- -------------------- ----------
09/07/2007 09:24:19 physical writes
user commits
user rollbacks
09/07/2007 09:49:58 physical writes 6985
user commits 1128
user rollbacks 358
...
10/24/2007 10:39:50 physical writes -5782
user commits -1336
user rollbacks -876
10/25/2007 08:18:29 physical writes 2370
user commits 1665
user rollbacks 2750
10/25/2007 08:24:13 physical writes 1127
user commits 2159
user rollbacks 3927
10/25/2007 08:32:39 physical writes 752 #
After the Vender Patch
user commits 1374
user rollbacks 2777
10/26/2007 08:00:04 physical writes 73
user commits 427
user rollbacks 1416
10/26/2007 09:00:05 physical writes 182
user commits 696
user rollbacks 2104
10/26/2007 10:00:05 physical writes 203
user commits 454
user rollbacks 1512
-
Regards & Thanks
BN
Other related posts:
