视图v$segstat 如果有跟段相关等等待，可以进一步查询具体是什么原因导致，比如contentionTS 热块 ITL事务槽contention 段扫描等
Name Null? Type
---------------------------- -------- --------------------------------
TS# NUMBER
OBJ# NUMBER
DATAOBJ# NUMBER
STATISTIC_NAME VARCHAR2(64)
STATISTIC# NUMBER
VALUE
STATISTIC_NAME
----------------------------------------------------------------
ITL waits
buffer busy waits
db block changes
gc buffer busy
gc cr blocks received
gc current blocks received
logical reads
optimized physical reads
physical read requests
physical reads
physical reads direct
physical write requests
physical writes
physical writes direct
row lock waits
segment scans
space allocated
space used
附带全部DBA_HIST 数据字典,标黄的我常用，注意是故障的事后处理，大部分都是sql问题导致
select table_name, COMMENTS
from dictionary
where table_name like 'DBA_HIST%';
TABLE_NAME COMMENTS
------------------------------ ---------------------------------------------------------------------------
DBA_HIST_ACTIVE_SESS_HISTORY Active Session Historical Statistics Information
DBA_HIST_ASH_SNAPSHOT
DBA_HIST_BASELINE Baseline Metadata Information
DBA_HIST_BASELINE_DETAILS Baseline Stats on per Instance Level
DBA_HIST_BASELINE_METADATA Baseline Metadata Information
DBA_HIST_BASELINE_TEMPLATE Baseline Template Information
DBA_HIST_BG_EVENT_SUMMARY Summary of Background Event 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_BUFFER_POOL_STAT Buffer Pool Historical Statistics Information
DBA_HIST_CLUSTER_INTERCON Cluster Interconnect Historical Stats
DBA_HIST_COLORED_SQL Marked SQLs for snapshots
DBA_HIST_COMP_IOSTAT I/O stats aggregated on component level
DBA_HIST_CR_BLOCK_SERVER Consistent Read Block Server Historical Statistics
DBA_HIST_CURRENT_BLOCK_SERVER Current Block Server Historical Statistics
DBA_HIST_DATABASE_INSTANCE Database Instance Information
DBA_HIST_DATAFILE Names of Datafiles
DBA_HIST_DB_CACHE_ADVICE DB Cache Advice History Information
DBA_HIST_DISPATCHER Dispatcher statistics
DBA_HIST_DLM_MISC Distributed Lock Manager Miscellaneous Historical Statistics Information
DBA_HIST_DYN_REMASTER_STATS Dynamic remastering statistics
DBA_HIST_ENQUEUE_STAT Enqueue Historical Statistics Information
DBA_HIST_EVENT_HISTOGRAM Event Histogram Historical Statistics Information
DBA_HIST_EVENT_NAME Event Names
DBA_HIST_FILEMETRIC_HISTORY File Metrics History
DBA_HIST_FILESTATXS Datafile Historical Statistics Information
DBA_HIST_IC_CLIENT_STATS Historical interconnect client statistics
DBA_HIST_IC_DEVICE_STATS Historical interconnect device statistics
DBA_HIST_INSTANCE_RECOVERY Instance Recovery Historical Statistics Information
DBA_HIST_INST_CACHE_TRANSFER Instance Cache Transfer Historical Statistics
DBA_HIST_INTERCONNECT_PINGS Instance to instance ping stats
DBA_HIST_IOSTAT_DETAIL Historical I/O statistics by function and filetype
DBA_HIST_IOSTAT_FILETYPE Historical I/O statistics by file type
DBA_HIST_IOSTAT_FILETYPE_NAME File type names for historical I/O statistics
DBA_HIST_IOSTAT_FUNCTION Historical I/O statistics by function
DBA_HIST_IOSTAT_FUNCTION_NAME Function names for historical I/O statistics
DBA_HIST_JAVA_POOL_ADVICE Java Pool Advice History
DBA_HIST_LATCH Latch Historical Statistics Information
DBA_HIST_LATCH_CHILDREN Latch Children Historical Statistics Information
DBA_HIST_LATCH_MISSES_SUMMARY Latch Misses Summary Historical Statistics Information
DBA_HIST_LATCH_NAME Latch Names
DBA_HIST_LATCH_PARENT Latch Parent Historical Historical Statistics Information
DBA_HIST_LIBRARYCACHE Library Cache Historical Statistics Information
DBA_HIST_LOG Log Historical Statistics Information
DBA_HIST_MEMORY_RESIZE_OPS Memory Resize Operations History
DBA_HIST_MEMORY_TARGET_ADVICE Memory Target Advice History
DBA_HIST_MEM_DYNAMIC_COMP Historical memory component sizes
DBA_HIST_METRIC_NAME Segment Names
DBA_HIST_MTTR_TARGET_ADVICE Mean-Time-To-Recover Target Advice History
DBA_HIST_MUTEX_SLEEP Mutex Sleep Summary Historical Statistics Information
DBA_HIST_MVPARAMETER Multi-valued Parameter Historical Statistics Information
DBA_HIST_OPTIMIZER_ENV Optimizer Environment Information
DBA_HIST_OSSTAT Operating System Historical Statistics Information
DBA_HIST_OSSTAT_NAME Operating System Statistic Names
DBA_HIST_PARAMETER Parameter Historical Statistics Information
DBA_HIST_PARAMETER_NAME Parameter Names
DBA_HIST_PERSISTENT_QMN_CACHE STREAMS AQ Persistent QMN Cache Historical Statistics Information
DBA_HIST_PERSISTENT_QUEUES STREAMS AQ Persistent Queues Historical Statistics Information
DBA_HIST_PERSISTENT_SUBS STREAMS AQ Persistent Queue Subscribers Historical Statistics Information
DBA_HIST_PGASTAT PGA Historical Statistics Information
DBA_HIST_PGA_TARGET_ADVICE PGA Target Advice History
DBA_HIST_PLAN_OPERATION_NAME Optimizer Explain Plan Operation Names
DBA_HIST_PLAN_OPTION_NAME Optimizer Explain Plan Option Names
DBA_HIST_PROCESS_MEM_SUMMARY Process Memory Historical Summary Information
DBA_HIST_RESOURCE_LIMIT Resource Limit Historical Statistics Information
DBA_HIST_ROWCACHE_SUMMARY Row Cache Historical Statistics Information Summary
DBA_HIST_RSRC_CONSUMER_GROUP Historical resource consumer group statistics
DBA_HIST_RSRC_PLAN Historical resource plan statistics
DBA_HIST_RULE_SET Rule sets historical statistics information
DBA_HIST_SEG_STAT Historical Statistics Information
DBA_HIST_SEG_STAT_OBJ Segment Names
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_SESSMETRIC_HISTORY System Metrics History
DBA_HIST_SESS_TIME_STATS CPU and I/O time for interesting (STREAMS) sessions
DBA_HIST_SGA SGA Historical Statistics Information
DBA_HIST_SGASTAT SGA Pool Historical Statistics Information
DBA_HIST_SGA_TARGET_ADVICE SGA Target Advice History
DBA_HIST_SHARED_POOL_ADVICE Shared Pool Advice History
DBA_HIST_SHARED_SERVER_SUMMARY Shared Server summary statistics
DBA_HIST_SNAPSHOT Snapshot Information
DBA_HIST_SNAP_ERROR Snapshot Error Information
DBA_HIST_SQLBIND SQL Bind Information 查询sql 绑定变量
DBA_HIST_SQLCOMMAND_NAME Sql command types
DBA_HIST_SQLSTAT SQL Historical Statistics Information
DBA_HIST_SQLTEXT SQL Text
DBA_HIST_SQL_BIND_METADATA SQL Bind Metadata Information
DBA_HIST_SQL_PLAN SQL Plan Information
DBA_HIST_SQL_SUMMARY Summary of SQL Statistics
DBA_HIST_SQL_WORKAREA_HSTGRM SQL Workarea Histogram History
DBA_HIST_STAT_NAME Statistic Names
DBA_HIST_STREAMS_APPLY_SUM STREAMS Apply Historical Statistics Information
DBA_HIST_STREAMS_CAPTURE STREAMS Capture Historical Statistics Information
DBA_HIST_STREAMS_POOL_ADVICE Streams Pool Advice History
DBA_HIST_SYSMETRIC_HISTORY System Metrics History
DBA_HIST_SYSMETRIC_SUMMARY System Metrics History
DBA_HIST_SYSSTAT System Historical Statistics Information
DBA_HIST_SYSTEM_EVENT System Event Historical Statistics Information
DBA_HIST_SYS_TIME_MODEL System Time Model Historical Statistics Information
DBA_HIST_TABLESPACE Tablespace Static Information
DBA_HIST_TABLESPACE_STAT Tablespace Historical Statistics Information
DBA_HIST_TBSPC_SPACE_USAGE Tablespace Usage Historical Statistics Information
DBA_HIST_TEMPFILE Names of Temporary Datafiles
DBA_HIST_TEMPSTATXS Temporary Datafile Historical Statistics Information
DBA_HIST_THREAD Thread Historical Statistics Information
DBA_HIST_TOPLEVELCALL_NAME Oracle top level call type
DBA_HIST_UNDOSTAT Undo Historical Statistics Information
DBA_HIST_WAITCLASSMET_HISTORY Wait Class Metric History
DBA_HIST_WAITSTAT Wait Historical Statistics Information
DBA_HIST_WR_CONTROL Workload Repository Control Information
DBA_HISTOGRAMS Synonym for DBA_TAB_HISTOGRAMS
阅读(2530) | 评论(0) | 转发(0) |
