This page has been permanently moved. Please
CLICK HERE to be redirected.
Thanks, Craig.
Eventually you'll want to really know...When performance data is periodically collected (i.e., snapshotted) and stored, the question undoubtedly will cross our minds, "Is the stored value correct at the beginning or the ending snapshot time?" In relation to Oracle's AWR system and specifically the dba_hist_sysstat table, this is what this posting is all about.
If you have ever written a data collection program or pulled data from either Statspack or AWR, you've had to figure this out.... or guessed and know you were kinda close.
Let me demonstrate this problem. Let's say this is the data:
Snap # Start Time End Time Value
------ ---------- -------- ----- There are two ways we could interpret this data. The question we want answered is, "What happened between 1000 and 1100? Based solely on the data above, there are two answers! If the statistic value was captured at the start time, then the delta (i.e., the activity between 1000 and 1100) for snap 3 would be 3; 118-115. However, if the statistic value was captured at the end time, then the delta for snap 3 would be 5; 115-1100 0700 0800 981 0800 0900 100 2 0900 1000 110 3 1000 1100 115 4 1100 1200 118
If the above two sentences are not crystal clear, then please STOP and re-read them a few times.
My Motivation
In just a few days, I'm going to teach a one day seminar at Collaborate 2012 entitled, Daily Forecasting and Predictive Analysis. In only five hours, I'm supposed to provide my students with enough information so they can do some responsible forecasting. And it's lecture only except for the demos I'll be giving. This meant I need to quickly empower my students with collecting good performance data.
When developing predictive models, data collection and characterizing the data is a really big deal: After all, how can you say, "What happens if we double the workload?" if we don't know what the workload is?
To help my students quickly get going, I wanted to use an existing data collection facility. So I decided to use AWR. This meant I had to quickly create some very flexible reports. (Actually I created a few functions that I use in the reporting SQL statements, which gives me lots of flexibility and allows for really easy yet advanced reporting.) All this to say, I really, really, really needed to know when raw performance data is stored in a particular AWR table: Is the value collected at the beginning or the ending snapshot time.
To figure this out, I created an experiment...
If you just want to know and don't want to read the rest of this posting, scroll down to the Conclusion section.
For this experiment I wanted to completely control AWR data collection and get some data as quickly as possible. I create a variable light load on the database server and using the snippet below, collected data every 10 seconds; yes every 10 seconds, not minutes... I was in a hurry.
-- AWR collection and firing time and delay
--
select to_char(sysdate,'HH24:MI:SS') now from dual;
EXEC dbms_workload_repository.create_snapshot;
exec dbms_lock.sleep(10);
select to_char(sysdate,'HH24:MI:SS') now from dual;
EXEC dbms_workload_repository.create_snapshot;
exec dbms_lock.sleep(10);
select to_char(sysdate,'HH24:MI:SS') now from dual;
EXEC dbms_workload_repository.create_snapshot;
...
AWR
NOW SYSSTAT_LIO AWR_LIO DIFF_LIO SNAP_ID AWR_BEGIN AWR_END
-------- ------------ ------------ --------- ------- --------------- ---------------
10:51:26 11284510295 11263576910 20933385 9474 10.47.10.806 AM 10.47.21.336 AM
10:51:27 11284590227 11263576910 21013317 9474 10.47.10.806 AM 10.47.21.336 AM
10:51:28 11284678032 11263576910 21101122 9474 10.47.10.806 AM 10.47.21.336 AM
10:51:29 11284759623 11263576910 21182713 9474 10.47.10.806 AM 10.47.21.336 AM
10:51:30 11284845769 11263576910 21268859 9474 10.47.10.806 AM 10.47.21.336 AM
10:51:31 11284935284 11263576910 21358374 9474 10.47.10.806 AM 10.47.21.336 AM
10:51:32 11285013563 11263576910 21436653 9474 10.47.10.806 AM 10.47.21.336 AM
10:51:33 11285099853 11285060579 39274 9475 10.47.21.336 AM 10.51.30.390 AM
10:51:34 11285178132 11285060579 117553 9475 10.47.21.336 AM 10.51.30.390 AM
10:51:35 11285259385 11285060579 198806 9475 10.47.21.336 AM 10.51.30.390 AM
10:51:36 11285336246 11285060579 275667 9475 10.47.21.336 AM 10.51.30.390 AM
10:51:37 11285444373 11285060579 383794 9475 10.47.21.336 AM 10.51.30.390 AM
10:51:40 11285652730 11285060579 592151 9475 10.47.21.336 AM 10.51.30.390 AM
10:51:41 11285749950 11285060579 689371 9475 10.47.21.336 AM 10.51.30.390 AM
10:51:42 11285837157 11285060579 776578 9475 10.47.21.336 AM 10.51.30.390 AM
10:51:43 11285920825 11285060579 860246 9475 10.47.21.336 AM 10.51.30.390 AM
10:51:44 11286003933 11285942610 61323 9476 10.51.30.390 AM 10.51.43.236 AM
10:51:45 11286097986 11285942610 155376 9476 10.51.30.390 AM 10.51.43.236 AM
10:51:46 11286182852 11285942610 240242 9476 10.51.30.390 AM 10.51.43.236 AM
10:51:47 11286279336 11285942610 336726 9476 10.51.30.390 AM 10.51.43.236 AM
10:51:48 11286394216 11285942610 451606 9476 10.51.30.390 AM 10.51.43.236 AM
10:51:51 11286619230 11285942610 676620 9476 10.51.30.390 AM 10.51.43.236 AM
10:51:52 11286708688 11285942610 766078 9476 10.51.30.390 AM 10.51.43.236 AM
10:51:53 11286798146 11285942610 855536 9476 10.51.30.390 AM 10.51.43.236 AM
10:51:54 11286890285 11285942610 947675 9476 10.51.30.390 AM 10.51.43.236 AM
10:51:55 11286984898 11286876687 108211 9477 10.51.43.236 AM 10.51.53.794 AM
10:51:56 11287096753 11286876687 220066 9477 10.51.43.236 AM 10.51.53.794 AM
10:51:57 11287175032 11286876687 298345 9477 10.51.43.236 AM 10.51.53.794 AM
10:51:58 11287252596 11286876687 375909 9477 10.51.43.236 AM 10.51.53.794 AM
10:51:59 11287337325 11286876687 460638 9477 10.51.43.236 AM 10.51.53.794 AM
10:52:00 11287421048 11286876687 544361 9477 10.51.43.236 AM 10.51.53.794 AM
10:52:01 11287499333 11286876687 622646 9477 10.51.43.236 AM 10.51.53.794 AM
First, let me explain each column.
- NOW is the clock time. The first sample was reported at 10:51:26, that is, about 10:51am.
- SYSSTAT_LIO is the actual v$sysstat statistic session logical reads value at the "NOW" time. This value is "the truth" at the NOW time.
- AWR_LIO is the stored session logical reads most recent value from the dba_hist_sysstat table.
- DIFF_LIO is the difference between SYSSTAT_LIO and AWR_LIO. The smaller the number the closer to the truth the AWR data is.
- AWR SNAP_ID is the maximum, that is, most recent AWR snapshot ID.
- AWR_BEGIN is the beginning snapshot time for its associated AWR_SNAP ID.
- AWR_END is the ending snapshot time for its associated AWR_SNAP ID. Notice there is about a 10 second difference between the AWR_BEGIN and AWR_END time; hence the 10 second AWR collection period.
If you haven't had your morning cup of coffee, now would be a good time! The below four points reference the looper.sql script output shown above.
First, notice AWR SNAP_ID does not appear until after the ending collection time has occurred. That is, once NOW surpasses the AWR_END time we see the snapshot data. If AWR is capturing data at the beginning, it is not allowing us to see it yet.
Second, notice the minimum DIFF_LIO values occur when a new AWR SNAP_ID appears. This minimum time is when the AWR data is the closest to the truth.
Third, since the AWR data is closest to the truth at the snapshot end time (AWR_END), we know the statistic value stored was gathered at the end of the snapshot period, not the beginning.
Fourth, therefore for a specific AWR SNAP_ID, the ending statistic value is the value stored for the AWR SNAP_ID and the starting value is the value stored with the previous AWR SNAP_ID.
Now let's apply the AWR strategy of collecting and storing raw performance statistic values to the example I provided at the top of this posting. You'll immediately notice I added the "Delta" column, which is what we see on a typical AWR report or one of my AWR data collection reports.
Snap # Start Time End Time Value Delta
------ ---------- -------- ----- ----- 0 0700 0800 98 -
1 0800 0900 100 2
2 0900 1000 110 10
3 1000 1100 115 5
4 1100 1200 118 3
So if I had to guess when users were upset during the morning, it would be between 0900 and 1000.
Conclusion
The dba_hist_sysstat statistic values (column value) are pulled from the raw v$sysstat statistic values (column value) at the end of the collection period.
My guess is this is what you thought. At least that was my guess... and it was a guess. But now I really know.
Thanks for reading!
Craig.
If you enjoyed this blog entry, I suspect you'll get a lot out of my courses; Oracle Performance Firefighting and Advanced Oracle Performance Analysis. I teach these classes around the world multiple times each year. I also occasionally teach my Oracle Forecasting and Predictive Analysis class and also a one day Performance Research Seminar. For the latest schedule, click here. I also offer on-site training and consulting services.
P.S. If you want me to respond with a comment or you have a question, please feel free to email me directly at orapub.craig@gmail .com.
Craig,
ReplyDeleteThanks for this study.
Regarding calculating AWR deltas, I strongly suggest using "analytic" functions LEAD() or LAG(), rather than doing cumbersome self-joins.
Use of dba_hist_sysstat is often easier after denormalizing using max-decode trick.
And all this SQL is easier to review and maintain using "subquery factoring, ie. using WITH, aka common table expressions.
All of these techniques are illustrated by http://www.rahul.net/abe/blog/downloads/cputime-dbtime-usercalls-gets-awr-daytime.sql.txt as linked from http://aberdave.blogspot.com/2011/08/universal-scalability-law-usl-in-oracle.html
Enjoy!
This is really a nice post. Though I knew how to interpret the dba_hist_sysstat output but now I am sure what does it mean. At least I have evidence!
ReplyDeleteCheersm
Neeraj
Sir, I saw so many post in the net that to find out the "session uga memory" and "session pga memory" we should not use the tables like dba_hist_sysstat if I have understood correctly), "For more clarity and close to accurate figure this value should be retrieved from v$sesstat and
ReplyDeletev$session view periodically. After plotting graph of these observations for fair amount of days, I could analyze the process memory usage more correctly."
But My concern is that - what if we wanted to plot the
graphs for last 72 hours trend ? and my DB is restarted becoz of some issues/planned shutdown...then V$session and V$sesstat is get reset... so at that time what to do ? I can plot the data by using below query but it will not give us the correct values , right ?
#################
select to_char(sn.end_interval_time,'yyyy-mm-dd hh24:mi') end_time,
(s1.value/1024/1024) session_pga_memory,
(s2.value/1024/1024) session_uga_memory
from
dba_hist_sysstat s1,
dba_hist_sysstat s2,
dba_hist_snapshot sn
where
s1.snap_id = sn.snap_id
and s2.snap_id = sn.snap_id
and s1.stat_name = 'session pga memory'
and s2.stat_name = 'session uga memory'
order by to_char(sn.end_interval_time,'yyyy-mm-dd hh24:mi')
##################
Please suggest ... and thanks again.
from INDRANIL
Thanks for sharing these information. It’s a very nice topic. We are providing online training classes
ReplyDeleteBest bca college in noida
Top bca colleges in noida