DBA_HIST 를 활용하여 설정분석

DBA_HIST 를 활용하여 설정분석

DBA_HIST 테이블을 활용하면 DB에 남은 로그를 분석할 수 있다.

내가 주로 사용하는 것은 DBA_HIST_ACTIVE_SESS_HISTORY 테이블인데 

말그대로 ACTIVE SESSION HISTORY 를 보여준다.

사용용도는 이 테이블에서 SQL_ID 로 SQLTEXT 를 찾을 수 있다.

DBA_HIST_TEXT 테이블을 찾아보면 DB 에서 실행된 SQL 문을 확인 할 수 있다.

그 외 DBA_HIST 관련 테이블은 아래 리스트에서 확인 가능하다.

TABLE_NAME	COMMENTS
DBA_HIST_DATABASE_INSTANCE	Database Instance Information
DBA_HIST_SNAPSHOT	Snapshot Information
DBA_HIST_SNAP_ERROR	Snapshot Error Information
DBA_HIST_BASELINE	Baseline Metadata Information
DBA_HIST_WR_CONTROL	Workload Repository Control Information
DBA_HIST_DATAFILE	Names of Datafiles
DBA_HIST_FILESTATXS	Datafile Historical Statistics Information
DBA_HIST_TEMPFILE	Names of Temporary Datafiles
DBA_HIST_TEMPSTATXS	Temporary Datafile Historical Statistics Information
DBA_HIST_COMP_IOSTAT	I/O stats aggregated on component level
DBA_HIST_SQLSTAT	SQL Historical Statistics Information
DBA_HIST_SQLTEXT	SQL Text
DBA_HIST_SQL_SUMMARY	Summary of SQL Statistics
DBA_HIST_SQL_PLAN	SQL Plan Information
DBA_HIST_SQL_BIND_METADATA	SQL Bind Metadata Information
DBA_HIST_SQLBIND	SQL Bind Information
DBA_HIST_OPTIMIZER_ENV	Optimizer Environment Information
DBA_HIST_EVENT_NAME	Event Names
DBA_HIST_SYSTEM_EVENT	System Event Historical Statistics Information
DBA_HIST_BG_EVENT_SUMMARY	Summary of Background Event Historical Statistics Information
DBA_HIST_WAITSTAT	Wait Historical Statistics Information
DBA_HIST_ENQUEUE_STAT	Enqueue Historical Statistics Information
DBA_HIST_LATCH_NAME	Latch Names
DBA_HIST_LATCH	Latch Historical Statistics Information
DBA_HIST_LATCH_CHILDREN	Latch Children Historical Statistics Information
DBA_HIST_LATCH_PARENT	Latch Parent Historical Historical Statistics Information
DBA_HIST_LATCH_MISSES_SUMMARY	Latch Misses Summary Historical Statistics Information
DBA_HIST_LIBRARYCACHE	Library Cache Historical Statistics Information
DBA_HIST_DB_CACHE_ADVICE	DB Cache Advice History Information
DBA_HIST_BUFFER_POOL_STAT	Buffer Pool Historical Statistics Information
DBA_HIST_ROWCACHE_SUMMARY	Row Cache Historical Statistics Information Summary
DBA_HIST_SGA	SGA Historical Statistics Information
DBA_HIST_SGASTAT	SGA Pool Historical Statistics Information
DBA_HIST_PGASTAT	PGA Historical Statistics Information
DBA_HIST_PROCESS_MEM_SUMMARY	Process Memory Historical Summary Information
DBA_HIST_RESOURCE_LIMIT	Resource Limit Historical Statistics Information
DBA_HIST_SHARED_POOL_ADVICE	Shared Pool Advice History
DBA_HIST_STREAMS_POOL_ADVICE	Streams Pool Advice History
DBA_HIST_SQL_WORKAREA_HSTGRM	SQL Workarea Histogram History
DBA_HIST_PGA_TARGET_ADVICE	PGA Target Advice History
DBA_HIST_SGA_TARGET_ADVICE	SGA Target Advice History
DBA_HIST_INSTANCE_RECOVERY	Instance Recovery Historical Statistics Information
DBA_HIST_JAVA_POOL_ADVICE	Java Pool Advice History
DBA_HIST_THREAD	Thread Historical Statistics Information
DBA_HIST_STAT_NAME	Statistic Names
DBA_HIST_SYSSTAT	System Historical Statistics Information
DBA_HIST_SYS_TIME_MODEL	System Time Model Historical Statistics Information
DBA_HIST_OSSTAT_NAME	Operating System Statistic Names
DBA_HIST_OSSTAT	Operating System Historical Statistics Information
DBA_HIST_PARAMETER_NAME	Parameter Names
DBA_HIST_PARAMETER	Parameter Historical Statistics Information
DBA_HIST_UNDOSTAT	Undo Historical Statistics Information
DBA_HIST_SEG_STAT	Historical Statistics Information
DBA_HIST_SEG_STAT_OBJ	Segment Names
DBA_HIST_METRIC_NAME	Segment Names
DBA_HIST_SYSMETRIC_HISTORY	System Metrics History
DBA_HIST_SYSMETRIC_SUMMARY	System Metrics History
DBA_HIST_SESSMETRIC_HISTORY	System Metrics History
DBA_HIST_FILEMETRIC_HISTORY	File Metrics History
DBA_HIST_WAITCLASSMET_HISTORY	Wait Class Metric History
DBA_HIST_DLM_MISC	Distributed Lock Manager Miscellaneous Historical Statistics Information
DBA_HIST_CR_BLOCK_SERVER	Consistent Read Block Server Historical Statistics
DBA_HIST_CURRENT_BLOCK_SERVER	Current Block Server Historical Statistics
DBA_HIST_INST_CACHE_TRANSFER	Instance Cache Transfer Historical Statistics
DBA_HIST_ACTIVE_SESS_HISTORY	Active Session Historical Statistics Information
DBA_HIST_TABLESPACE_STAT	Tablespace Historical Statistics Information
DBA_HIST_LOG	Log Historical Statistics Information
DBA_HIST_MTTR_TARGET_ADVICE	Mean-Time-To-Recover Target Advice History
DBA_HIST_TBSPC_SPACE_USAGE	Tablespace Usage Historical Statistics Information
DBA_HIST_SERVICE_NAME	Service Names
DBA_HIST_SERVICE_STAT	Historical Service Statistics
DBA_HIST_SERVICE_WAIT_CLASS	Historical Service Wait Class Statistics
DBA_HIST_SESS_TIME_STATS	CPU and I/O time for interesting (STREAMS) sessions
DBA_HIST_STREAMS_CAPTURE	STREAMS Capture Historical Statistics Information
DBA_HIST_STREAMS_APPLY_SUM	STREAMS Apply Historical Statistics Information
DBA_HIST_BUFFERED_QUEUES	STREAMS Buffered Queues Historical Statistics Information
DBA_HIST_BUFFERED_SUBSCRIBERS	STREAMS Buffered Queue Subscribers Historical Statistics Information
DBA_HIST_RULE_SET	Rule sets historical statistics information
DBA_HISTOGRAMS	Synonym for DBA_TAB_HISTOGRAMS
[출처] DBA_HIST 시리즈|작성자 시와처