Wednesday, December 10, 2008

SQL workload history with DBA_HIST_SQLSTAT

Using the DBA_HIST_XXX views from the AWR (automatic workload repository), it has become easier for a DBA to track changes in workload metrics over time. Through a SR I logged with Oracle Support they have supplied me with the following sql statement to track such changes for a single SQL statement.

The view DBA_HIST_SQLSTAT displays historical information about SQL statistics. Each statistic is stored in two separate columns:metric_TOTAL for the total value of the statistic since instance startup.metic_DELTA for the change in a statistic’s value between BEGIN_INTERVAL_TIME to the END_INTERVAL_TIME that is stored in the DBA_HIST_SNAPSHOT view.

You can also query DBA_HIST_SQL_PLAN to compare the execution plans, if PLAN_HASH_VALUE has changed .