性能监控

性能监控

性能监视：

dba应该再开始任何调节努力之前准确测出系统当前正在怎样运行。（如各种基准度量标准等）

Sources of Tuning Information

The Alert log

Background and user trace files

Dynamic performance views

Data dictionary views

Dynamic performance views: V$SGASTAT V$EVENT_NAME V$SYSTEM_EVENT V$SESSION_EVENT V$SESSION_WAIT V$STATNAME V$SYSSTAT V$SESSTAT V$SESSION V$WAITSTAT

Data dictionary views：DBA_TABLES Table DBA_INDEXES INDEX_STATS DBA_DATA_FILES DBA_SEGMENTS DBA_HISTOGRAMS DBA_OBJECTS

Identifying Unusable Objects Using Data Dictionary

SQL> SELECT owner, object_name, object_type FROM dba_objects WHERE status = 'INVALID';

ALTER PACKAGE my_package COMPILE;

ALTER PACKAGE my_package COMPILE BODY;

ALTER PROCEDURE my_procedure COMPILE;

ALTER FUNCTION my_function COMPILE;

ALTER TRIGGER my_trigger COMPILE;

ALTER VIEW my_view COMPILE;

SQL> SELECT owner, index_name, index_type FROM dba_indexes WHERE status = 'UNUSABLE';

SQL> ALTER INDEX hr.job_id_pk REBUILD;

用OEM实现查找编译索引重建，很方便。索引重建选择脱机或联机还可以制定“便笺式”表空间（存储中间结果）

The cost-based optimizer (CBO) uses these statistics to formulate efficient execution plans

for each SQL statement that is issued by application users.

Because of this, the statistics gathered and stored in the data dictionary views are sometimes

called optimizer statistics.

Automatic Collection of Statistics:DBCA创建的数据库，默认，定时执行

Manual Collection of Statistics（不推荐，有定时的job）

OEM：略

Manually Gathering Statistics Using DBMS_STATS：备份旧的统计数据，允许以后恢复，执行并行分析更快速收集统计数据等……

GATHER_INDEX_STATS GATHER_INDEX_STATS GATHER_SCHEMA_STATS GATHER_DATABASE_STATS

如：The following example shows how the DBMS_STATS packages can be used to gather statistics on the PRODUCT_HISTORY table in SH’s schema:

SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS (‘SH’,’PRODUCT_HISTORY’);

The presence of accurate optimizer statistics has a big impact on two important measures of overall system performance: throughput and response time.

Important Performance Metrics

Throughput is another example of a statistical performance metric. Throughput is the amount of processing that a computer or system can perform in a given amount of time,

Throughput is an important measure when considering the scalability of the system. Scalability refers to the degree to which additional users can be added to the system without

system performance declining significantly.

Another important metric related to performance is response time. Response time is the amount of time that it takes for a single user’s request to return the desired result when using

an application

把服务器的I/O能力告诉ADDM

做I/O测试，我们的磁盘子系统默认的是10毫秒值