When Are Oracle Database DBA_HIST_SYSSTAT Values Correct?

Posted on 19-Apr-2012 by Craig Shallahamer, craig@orapub.com

Eventually you'll want to really know...

When Oracle Database 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 the Automated Workload Repository (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 
------  ----------  --------  ----- 
  0           0700      0800     98
  1           0800      0900    100      
  2           0900      1000    110      
  3           1000      1100    115      
  4           1100      1200    118       

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-110

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.

The Experiment

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;

I then created and executed the looper.sql script once a second to show the details about the true v$sysstat statistic value and what was stored in the associated AWR table, dba_hist_sysstat. Below is the report output.

-------- ------------ ------------ --------- ------- --------------- ---------------
10:51:26  11284510295  11263576910  20933385    9474 AM AM
10:51:27  11284590227  11263576910  21013317    9474 AM AM
10:51:28  11284678032  11263576910  21101122    9474 AM AM
10:51:29  11284759623  11263576910  21182713    9474 AM AM
10:51:30  11284845769  11263576910  21268859    9474 AM AM 
10:51:31  11284935284  11263576910  21358374    9474 AM AM
10:51:32  11285013563  11263576910  21436653    9474 AM AM 
10:51:33  11285099853  11285060579     39274    9475 AM AM 
10:51:34  11285178132  11285060579    117553    9475 AM AM
10:51:35  11285259385  11285060579    198806    9475 AM AM
10:51:36  11285336246  11285060579    275667    9475 AM AM
10:51:37  11285444373  11285060579    383794    9475 AM AM
10:51:40  11285652730  11285060579    592151    9475 AM AM
10:51:41  11285749950  11285060579    689371    9475 AM AM
10:51:42  11285837157  11285060579    776578    9475 AM AM
10:51:43  11285920825  11285060579    860246    9475 AM AM 
10:51:44  11286003933  11285942610     61323    9476 AM AM 
10:51:45  11286097986  11285942610    155376    9476 AM AM
10:51:46  11286182852  11285942610    240242    9476 AM AM
10:51:47  11286279336  11285942610    336726    9476 AM AM
10:51:48  11286394216  11285942610    451606    9476 AM AM
10:51:51  11286619230  11285942610    676620    9476 AM AM
10:51:52  11286708688  11285942610    766078    9476 AM AM
10:51:53  11286798146  11285942610    855536    9476 AM AM 
10:51:54  11286890285  11285942610    947675    9476 AM AM 
10:51:55  11286984898  11286876687    108211    9477 AM AM
10:51:56  11287096753  11286876687    220066    9477 AM AM
10:51:57  11287175032  11286876687    298345    9477 AM AM
10:51:58  11287252596  11286876687    375909    9477 AM AM
10:51:59  11287337325  11286876687    460638    9477 AM AM
10:52:00  11287421048  11286876687    544361    9477 AM AM
10:52:01  11287499333  11286876687    622646    9477 AM 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.

Results analyzed

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.


The Oracle Database table 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!


Start my FREE 18 lesson Machine Learning For Oracle Professionals E-Course here.

Craig Shallahamer is a long time Oracle DBA who specializes in predictive analytics, machine learning and Oracle performance tuning. Craig is a performance researcher and blogger, consultant, author of two books, an enthusiastic conference speaker a passionate teacher and an Oracle ACE Director. More about Craig Shallahamer...

If you have any questions or comments, feel free to email me directly at craig at orapub.com.

Understanding How An Intense And Diverse SQL Workload Causes Parsing Problems OraPub Oracle Database Training In South Africa A Duck In And Out Of The Oracle Water