Create your own reports with Oracle's Snapshot Information
May 1, 2008
If you are tired of running the same old Oracle AWR reports, not getting to the data quickly, or just need to write your own, then this article will get you started.
Oracles Automatic Workload Repository (AWR), through snapshots, collects and maintains a set of statistics that can be used for performance tuning through the use of canned reports. Wow! That was a lot of information in one sentence and it is assumed that we all are knowledgeable with AWR, snapshots, and the underlying structures. We are only going to touch AWR very lightly in this article but probably in a way you havent done before. First, AWR is nothing more than, as the name states, a repository that holds statistical information. This information is collected through a snapshot (point-in-time collection) process that inserts rows into structures that can be queried at a later date for reporting.
As a default to the Oracle environment, you typically look at AWR data/reports through Oracles Enterprise Manager. If you are a command line junkie, you might use the provided AWR report scripts such as one of the followingeach of which will ask you information like the beginning and ending snapshop IDs and the instance against which you want to report.
$ORACLE_HOME/rdbms/admin/awrrpt.sql - Standard AWR report $ORACLE_HOME/rdbms/admin/awrrpti.sql - AWR report for a specific instance $ORACLE_HOME/rdbms/admin/awrsqrpt.sql - AWR report for a SQL statement
You should have noticed the similarity in these script files, there are more, as they all are in the $ORACLE_HOME/rdbms/admin directory and they all start with awr. You can run these all at anytime and I encourage you to do this. Mostly this is where we now venture off in our attempts to imitate some of their behavior. Please do note that some of these are to only be run under the supervision of Oracle Support. Just be cautious and read the script before running it.
Since I plan to take you through a complete script, have patience. The first thing we need to do is set up some general .sql script environmental behaviors. So, the first part of our script will be:
set linesize 132 set echo off set feedback off set heading off set linesize 132 set pagesize 55 set verify off
Now that weve got that out of the way, lets begin by asking the number of days to look for snapshots. We can do this with the following SQL, which joins the DBA_HIST_DATABASE_INSTANCE and DBA_HIST_SNAPSHOT viewsshowing historical information on the snapshots in the Workload Repository. We obviously need to join these tables on the dbid, instance_number, and, the important part, startup_time. We also need to make sure that we only bring back snapshots that are newer than the number of days back specified by the user, by comparing the time of the actual snapshot (end_interval_time). Please note that this script will output a status of **db restart** for those times that the database was down and unavailable. This is very important as it shows us those times that Oracle was not collecting statistics (the database was down) and more importantly, the statistic counters were zeroed. We can report on a bounce condition if the startup_time and begin_interval_time are the same.
prompt prompt prompt Enter the number of days to look for snapshot IDs prompt ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ select dhdi.instance_name, dhdi.db_name, dhs.snap_id, to_char(dhs.begin_interval_time,'MM/DD/YYYY:HH24:MI') begin_snap_time, to_char(dhs.end_interval_time,'MM/DD/YYYY:HH24:MI') end_snap_time, decode(dhs.startup_time,dhs.begin_interval_time,'**db restart**',null) db_bounce from dba_hist_snapshot dhs, dba_hist_database_instance dhdi where dhdi.dbid = dhs.dbid and dhdi.instance_number = dhs.instance_number and dhdi.startup_time = dhs.startup_time and dhs.end_interval_time >= to_date(sysdate - &&num_days_back) order by db_name, instance_name, snap_id;
The next part of the script does nothing more than ask you to look back at the output from the previous SQL and select a beginning snapshot ID and an ending snapshot ID. These values will be kept in the variable beg_snap_id and end_snap_id respectively.
\prompt prompt prompt Enter Begining snapshot ID prompt ^^^^^^^^^^^^^^^^^^^^^^^^^^ prompt &&beg_snap_id prompt prompt Enter Ending snapshot ID prompt ^^^^^^^^^^^^^^^^^^^^^^^^^^ prompt &&end_snap_id
I always like to give my reports a unique name so the next part of the script will provide that granularity. Notice that I preface with the instance_name and have the beg_snap_id and end_snap_id in the name. Also, I put my results in a lower directory named LST. Please name your report something meaningful.
set termout off column rpt new_value rpt select instance_name||'_wrh_sample_'||&&beg_snap_id||'_'||&&end_snap_id||'.LST' rpt from v$instance; set termout on prompt prompt prompt ^^^^^^^^^^^^^ prompt Report Name : ../LST/&&rpt prompt ^^^^^^^^^^^^^ spool ../LST/&&rpt
Now pull some statistics out of the Workload Repository History tablesfor the beginning snap ID. We are all familiar with the V$SYSSTAT view, and the Workload Repository History has one very similar named WRH$_SYSSTAT. Join this table with WRH$_STAT_NAME for the statistic names and we can easily extract any collected statistic for our snapshot ID. In this example, I am pulling out the values for physical reads and writes for the beginning snapshot ID. These are stored in the variable tbr1 and tbw1 respectively. Probably one of the funniest and confusing parts of this script is the SUM(DECODE part. All this does is, as rows are processed/scanned, add the value to the variable if the statistic name is equal to the name in the DECODE. There is only one row for each statistic and I could have written out a simple SELECT..FROM..WHERE for each statistic name, but I like having just one SQL statement and scan the table once as opposed to once for every statistic.
column db_bounce new_value db_bounce column tbr1 new_value tbr1 column tbw1 new_value tbw1 set termout off SELECT sum(decode(stat_name,'physical read total bytes',value,0)) tbr1, sum(decode(stat_name,'physical write total bytes',value,0)) tbw1 FROM wrh$_sysstat, wrh$_stat_name WHERE wrh$_sysstat.stat_id = wrh$_stat_name.stat_id AND wrh$_sysstat.snap_id = &&beg_snap_id ;
Check the bounce flag and see if we should zero out the beginning variables. Later in this script, since Oracle accumulates statistics over time and zeroes them out if a bounce occurs, we need to zero out the beginning variables so as not to subtract the beginning variables from the ending variables and come up with a negative or irrelevant number.
select decode(startup_time,begin_interval_time,1,0) db_bounce from dba_hist_snapshot where snap_id = &&end_snap_id; SELECT decode(&&db_bounce,1,0,&&tbr1) tbr1, decode(&&db_bounce,1,0,&&tbw1) tbw1 FROM dual; set termout on
This is just to print out the beginning variables to the screennice and pretty like.
prompt prompt ^^^^^^^^^^^^ prompt First Sample prompt ^^^^^^^^^^^^ prompt Total Bytes Read : &&tbr1 prompt Total Bytes Written : &&tbw1 prompt
Now it is time to get the values for our same statistics at the time of the second snapshot. This time the script asks for the end_snap_id and relies upon the user to enter in an ID from the very first output of all snapshot IDs earlier in the script. This SQL is exactly the same as the one above for the beg_snap_id.
column tbr2 new_value tbr2 column tbw2 new_value tbw2 set termout off SELECT sum(decode(stat_name,'physical read total bytes',value,0)) tbr2, sum(decode(stat_name,'physical write total bytes',value,0)) tbw2 FROM wrh$_sysstat, wrh$_stat_name WHERE wrh$_sysstat.stat_id = wrh$_stat_name.stat_id AND wrh$_sysstat.snap_id = &&end_snap_id; set termout on
The ending variables extracted need no check against the bounce of the database so we can just print them out on the screen.
prompt prompt ^^^^^^^^^^^^^ prompt Second Sample prompt ^^^^^^^^^^^^^ prompt Total Bytes Read : &&tbr2 prompt Total Bytes Written : &&tbw2
Now we just subtract the beginning statistical values from the ending values to get the amount between the two snapshots. Then print the results to the screen.
prompt ^^^^^^^^^ prompt Results : prompt ^^^^^^^^^ column tr new_value tr column tw new_value tw set termout off SELECT ROUND(&&tbr2-&&tbr1) tr, ROUND(&&tbw2-&&tbw1) tw FROM dual; set termout on prompt Total Bytes Read = &&tr prompt Total Bytes Written = &&tw
A little house cleaning to clear out the variables we used.
spool off undefine num_days_back undefine beg_snap_id undefine end_snap_id undefine db_bounce
This may seem like a lot of work to subtract a few statistical values, but the beauty of this is that you can extend this simple, ok semi-simple script, to do a lot of work around the selected snapshot IDs. Most of which you could not do otherwise by any cleaver single SQL statement. Moreover, the most important part is that the existing AWR reports do not report on every type of statistical information you need to tune your database. I have often times found myself punching a calculator to find some metric. No longer do I, or you, have to do this. Create a script such as this, put in your own statistic names, and do the calculations at the end. Happy Reporting.