Re: awr history
- From: Karl Arao <karlarao@xxxxxxxxx>
- To: Brandon.Allen@xxxxxxxxxxx
- Date: Fri, 14 Jan 2011 04:07:20 +0800
Hi Allen, Dan,
Regarding the LAG function, I had a bit of headache+laziness so I haven't
worked it out yet.
But the SQL trick I'm doing does the job and the objective of the
tool/script is what "start and end SNAP_ID" you feed in when running
@?/rdbms/admin/awrrpt.sql
should be the same "start and end SNAP_ID" when you see it in a time series
manner. So that when you find a peak period, you are good to go on drilling
down on the larger reports (awrrpt.sql)
You can see an example AWR report here which has SNAP_ID 338-339... now we
usually have this report by using the awrrpt.sql
http://karlarao.tiddlyspot.com/#%5B%5BPerformance%20Formulas%5D%5D
then on a time series manner.. what values you see on the long report is the
same when you look at SNAP_ID 338... look at the DB Time.. 1324.58÷60
= 22.08... so that is the beauty of the script..
http://lh3.ggpht.com/_F2x5WXOJ6Q8/S2hR6V8NjCI/AAAAAAAAAo0/YM_c7VhFKiI/dba_hist3.png
I remember having notes on this issue as well.. but haven't really had the
time to put some more explanations..
http://karlarao.tiddlyspot.com/#%5B%5BPerformance%20Formulas%5D%5D
"Snap|ID"
s0.snap_id id,
- Must be the s0.snap_id
- This is the beginning value of dba_hist_snapshot, this is your marker when
you want to drill down to that particular period by creating an AWR report
using awrrpt.sql
- I'm having issues using the LAG function because it makes this column use
the s1.snap_id which is wrong
And here as well
http://karlarao.tiddlyspot.com/#MissingSQLinDBA_HIST_SQLSTAT
-- NOTE.. the values from snap 1333 is actually what you see in AWR
report 1332-1333.. so it's just getting the end value..
-- need a way to show the 1332 that's why i have to
do the SQL trick e.snap_id = s0.snap_id + 1
Regarding the shutdown periods... the script will just output negative
numbers for the delta and it will be easily noticed when you look at the
textual and graphed data.. So if I use this on client visits I would know
without looking at the alert log if there was a shutdown period or instance
reboot..
http://lh3.ggpht.com/_F2x5WXOJ6Q8/TEvmJcNuUXI/AAAAAAAAAxU/nz4vwuobkE4/AAS%20ALL.PNG
To remove that negative delta on shutdowns, which I did in the regression
analysis toolkit (http://karlarao.tiddlyspot.com/#r2project) to exclude the
periods from being processed by R2
I added the following
AND s1.startup_time = s0.startup_time
--
Karl Arao
karlarao.wordpress.com
karlarao.tiddlyspot.com
Other related posts:
