Sunday, 30 December 2012

Some ASH (Active Session History) views


select * from  DBA_HIST_ACTIVE_SESS_HISTORY;
select * from  DBA_HIST_BASELINE;
select * from  DBA_HIST_BG_EVENT_SUMMARY;
select * from  DBA_HIST_BUFFERED_QUEUES;
select * from  DBA_HIST_BUFFERED_SUBSCRIBERS;
select * from  DBA_HIST_BUFFER_POOL_STAT;
select * from  DBA_HIST_COMP_IOSTAT;
select * from  DBA_HIST_CR_BLOCK_SERVER;
select * from  DBA_HIST_CURRENT_BLOCK_SERVER;
select * from  DBA_HIST_DATABASE_INSTANCE;
select * from  DBA_HIST_DATAFILE;
select * from  DBA_HIST_DB_CACHE_ADVICE;
select * from  DBA_HIST_DLM_MISC;
select * from  DBA_HIST_ENQUEUE_STAT;
select * from  DBA_HIST_EVENT_NAME;
select * from  DBA_HIST_FILEMETRIC_HISTORY;
select * from  DBA_HIST_FILESTATXS;
select * from  DBA_HIST_INSTANCE_RECOVERY;
select * from  DBA_HIST_INST_CACHE_TRANSFER;
select * from  DBA_HIST_JAVA_POOL_ADVICE;
select * from  DBA_HIST_LATCH;
select * from  DBA_HIST_LATCH_CHILDREN;
select * from  DBA_HIST_LATCH_MISSES_SUMMARY;
select * from  DBA_HIST_LATCH_NAME;
select * from  DBA_HIST_LATCH_PARENT;
select * from  DBA_HIST_LIBRARYCACHE;
select * from  DBA_HIST_LOG;
select * from  DBA_HIST_METRIC_NAME;
select * from  DBA_HIST_MTTR_TARGET_ADVICE;
select * from  DBA_HIST_OPTIMIZER_ENV;
select * from  DBA_HIST_OSSTAT;
select * from  DBA_HIST_OSSTAT_NAME;
select * from  DBA_HIST_PARAMETER;
select * from  DBA_HIST_PARAMETER_NAME;
select * from  DBA_HIST_PGASTAT;
select * from  DBA_HIST_PGA_TARGET_ADVICE;
select * from  DBA_HIST_PROCESS_MEM_SUMMARY;
select * from  DBA_HIST_RESOURCE_LIMIT;
select * from  DBA_HIST_ROWCACHE_SUMMARY;
select * from  DBA_HIST_RULE_SET;
select * from  DBA_HIST_SEG_STAT;
select * from  DBA_HIST_SEG_STAT_OBJ;
select * from  DBA_HIST_SERVICE_NAME;
select * from  DBA_HIST_SERVICE_STAT;
select * from  DBA_HIST_SERVICE_WAIT_CLASS;
select * from  DBA_HIST_SESSMETRIC_HISTORY;
select * from  DBA_HIST_SESS_TIME_STATS;
select * from  DBA_HIST_SGA;
select * from  DBA_HIST_SGASTAT;
select * from  DBA_HIST_SGA_TARGET_ADVICE;
select * from  DBA_HIST_SHARED_POOL_ADVICE;
select * from  DBA_HIST_SNAPSHOT;
select * from  DBA_HIST_SNAP_ERROR;
select * from  DBA_HIST_SQLBIND;
select * from  DBA_HIST_SQLSTAT;
select * from  DBA_HIST_SQLTEXT;
select * from  DBA_HIST_SQL_BIND_METADATA;
select * from  DBA_HIST_SQL_PLAN;
select * from  DBA_HIST_SQL_SUMMARY;
select * from  DBA_HIST_SQL_WORKAREA_HSTGRM;
select * from  DBA_HIST_STAT_NAME;
select * from  DBA_HIST_STREAMS_APPLY_SUM;
select * from  DBA_HIST_STREAMS_CAPTURE;
select * from  DBA_HIST_STREAMS_POOL_ADVICE;
select * from  DBA_HIST_SYSMETRIC_HISTORY;
select * from  DBA_HIST_SYSMETRIC_SUMMARY;
select * from  DBA_HIST_SYSSTAT;
select * from  DBA_HIST_SYSTEM_EVENT;
select * from  DBA_HIST_SYS_TIME_MODEL;
select * from  DBA_HIST_TABLESPACE_STAT;
select * from  DBA_HIST_TBSPC_SPACE_USAGE;
select * from  DBA_HIST_TEMPFILE;
select * from  DBA_HIST_TEMPSTATXS;
select * from  DBA_HIST_THREAD;
select * from  DBA_HIST_UNDOSTAT;
select * from  DBA_HIST_WAITCLASSMET_HISTORY;
select * from  DBA_HIST_WAITSTAT;
select * from  DBA_HIST_WR_CONTROL;


Both ASH size and the total number of emergency flushes since instance startup can be
monitored by running the following query:

 select total_size,awr_flush_emergency_count from v$ash_info;

Active Session History (ASH) performed an emergency flush. This may mean that ASH is undersized. 
If emergency flushes are a recurring issue, you may consider increasing ASH size by setting 
the value of _ASH_SIZE to a sufficiently large value.

No comments:

Post a Comment