Oracle - The DBA HIST tables

The DBA_HIST tables were introduced along with the 10g AWR mechanism and their biggest advantage is that they answer the "What has changed since...?" question that we often ask our selves during various database tuning tasks. The tables hold history statistics and information of different system and object metrics, memory utilization and sql execution metrics.
The information in most of the DBA_HIST tables corresponds to the AWR snapshots (the SNAP_ID column tells us to which snapshot the information is relevant to) so they are populated with new information when a snapshot is taken and old information is purged along with the AWR snapshot according to the AWR retention settings.
Another setting that affects the ammount of data collected by each snapshot is the STATISTICS_LEVEL parameter, the default is TYPICAL but I recommend setting it to ALL, the performance implications are unnoticeable but the extra information is very useful, I also recommend on taking more snapshots and keeping them available for more time.

My favorites

• DBA_HIST_SNAPSHOT - The table holds AWR snapshots information for all snapshots.
• DBA_HIST_SQLSTAT - The informaiton and statistics in this table are taken from V$SQL with each AWR snapshot, it chooses the top SQL statements from the dynamic view. The number of statements depends on the value of STATISTICS_LEVEL parameter.
• DBA_HIST_SQL_PLAN - This table is pretty much the same as V$SQL_PLAN. the greatest thing about it is that it keeps execution plans of all statements and the informaiton is not purged when snapshots are deleted.
• DBA_HIST_SQLTEXT - Same as DBA_HIST_SQL_PLAN but with V$SQL_TEXT.
• DBA_HIST_SEG_STAT - Same as DBA_HIST_SQLSTAT but with V$SEGMENT_STATISTICS, the columns are slightly different from the V$, the HIST table has a column for each statistic instead of the STATISTIC_NAME column that the V$ has. This table also holds information of the top segments of each snapshot and the number is also controlled by the STATISTIC_LEVEL parameter.
• DBA_HIST_SYSTEM_EVENT - The HIST of V$SYSTEM_EVENT, holds the wait events information for every snapshot.
• DBA_HIST_ACTIVE_SESS_HISTORY - The HIST table of V$ACTIVE_SESSION_HISTORY.

Examples

• You add a new index to a table, and after a couple of days you want to see what queries (if any) are using your new index, and was there any performance gain.
  The following query will give you a pretty good idea:
  

  select txt.sql_text,new.sql_id,new_hash,old_hash,new_execs,old_execs,new_gets,old_gets,new_reads,old_reads,new_cpu,old_cpu,new_min_snap,old_min_snap,new_max_snap,old_max_snapfrom(select  st_n.sql_id,st_n.plan_hash_value new_hash,sum(st_n.executions_delta) new_execs,sum(st_n.buffer_Gets_delta) new_gets, sum(st_n.disk_reads_delta) new_reads,sum(st_n.cpu_time_delta) new_cpu,min(snp.begin_interval_time) new_min_snap, max(snp.begin_interval_time) new_max_snap from dba_hist_sqlstat st_n join dba_hist_snapshot snp on st_n.snap_id=snp.snap_id where (st_n.sql_id, st_n.plan_hash_value) in          (select pl.sql_id,pl.plan_hash_value from dba_hist_sql_plan pl where object_name='INDEX NAME'       and object_owner='INDEX OWNER') group by st_n.sql_id,st_n.plan_hash_value) newjoin(select  st_o.sql_id,st_o.plan_hash_value old_hash,sum(st_o.executions_delta) old_execs,sum(st_o.buffer_Gets_delta) old_gets, sum(st_o.disk_reads_delta) old_reads,sum(st_o.cpu_time_delta) old_cpu,min(snp.begin_interval_time) old_min_snap, max(snp.begin_interval_time) old_max_snap from dba_hist_sqlstat st_o join dba_hist_snapshot snp on st_o.snap_id=snp.snap_id where (st_o.sql_id, st_o.plan_hash_value) not in          (select pl.sql_id,pl.plan_hash_value from dba_hist_sql_plan pl where object_name='INDEX NAME'        and object_owner='INDEX OWNER') group by st_o.sql_id,st_o.plan_hash_value) oldon new.sql_id = old.sql_idjoin dba_hist_sqltext txt on txt.sql_id = new.sql_id

  
  
  
• Use the following query to get the modules that did the most non idle waiting in a specific time frame:
  

  SELECT SUM(TIME_WAITED) total_time,moduleFROM dba_hist_active_sess_history h JOIN dba_hist_snapshot sn ON h.snap_id = sn.snap_idWHERE sn.begin_interval_time   BETWEEN TO_DATE('03/12/09 00:00','MM/DD/YY HH24:MI') AND TO_DATE('03/12/09 12:00','MM/DD/YY HH24:MI')group by moduleorder by total_time desc

  
  
  
• Did you ever wonder if someone had changed a system parameter and did not inform you? No more, the DBA_HIST_PARAMETER keeps system parameter information for all snapshots and helps the DBA track any parameter.
  The following query compares current system parameters values with past values and provides the differences and times of the changes:
  

  select c.name,c.value,hp.value,min((select begin_interval_time from dba_hist_snapshot where snap_id=hp.snap_id))from v$parameter c join dba_hist_parameter hp on c.name = hp.parameter_name and c.value != hp.value group by c.name,c.value,hp.valueorder by c.name

  
  NOTE: For some reason the subquery (with DBA_HIST_SNAPSHOT inside the min) works a lot faster then the join version of the query.

CONCLUSION

The DBA_HIST tables add a new dimension to database tuning optimization and performance investigations, and I urge you to review the many DBA_HISTS that aren't mentioned in this article and find infomation that can assist you in current and future DB issues you might have.