Oracle segment啥意思,Oracle segment 方面的stat信息

视图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_SQLBINDSQL 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

阅读(1117) | 评论(0) | 转发(0) |