十 管理数据库性能

    性能监视分为反应式(reactive)和前摄式(proactive)两种形式。其中，反应式意味着在出现某个问题的时候后之后执行某些动作；前摄式意味着成为问题之前标志未决的问题。显而易见，使问题对终端用户的影响最小化的理想方法是前摄式监视。

1、  无效的对象(PL/SQL对象和视图)

大多数PL/SQL对象都会引用表，如果某个PL/SQL对象引用的表在该PL/SQL对象的编译之后发生变化，那么这个过程会被标记为invalid。

视图在创建时是有效的，但是在其所基于的表的定义发生变化时，视图就会失效。

Oracle总是会尝试自动编译无效的PL/SQL对象和视图，但是可能不会成功。如果不成功的话，就需要手动地进行编译（不过这个并不是必须的）。

1.1   标志无效的对象(PL/SQL对象和视图)

标志无效的对象(PL/SQL对象和视图)是自动的，使用DBA_OBJECTS视图来查询无效的对象。

sys@ORCL> select owner, object_name, object_type from dba_objects

  2  where status='INVALID';

 

OWNER                OBJECT_NAME                      OBJECT_TYPE

-------------------- -----------------------------------                      ------------

PUBLIC               DBA_HIST_FILESTATXS                 SYNONYM

PUBLIC               DBA_HIST_SQLSTAT                    SYNONYM

PUBLIC               DBA_HIST_SQLBIND                    SYNONYM

PUBLIC               DBA_HIST_SYSTEM_EVENT               SYNONYM

PUBLIC               DBA_HIST_WAITSTAT                   SYNONYM

PUBLIC               DBA_HIST_LATCH                      SYNONYM

PUBLIC               DBA_HIST_LATCH_MISSES_SUMMARY       SYNONYM

PUBLIC               DBA_HIST_DB_CACHE_ADVICE            SYNONYM

PUBLIC               DBA_HIST_ROWCACHE_SUMMARY           SYNONYM

PUBLIC               DBA_HIST_SGASTAT                    SYNONYM

PUBLIC               DBA_HIST_SYSSTAT                    SYNONYM

PUBLIC               DBA_HIST_SYS_TIME_MODEL             SYNONYM

PUBLIC               DBA_HIST_OSSTAT                     SYNONYM

PUBLIC               DBA_HIST_PARAMETER                  SYNONYM

PUBLIC               DBA_HIST_SEG_STAT                   SYNONYM

PUBLIC               DBA_HIST_ACTIVE_SESS_HISTORY        SYNONYM

PUBLIC               DBA_HIST_TABLESPACE_STAT            SYNONYM

PUBLIC               DBA_HIST_SERVICE_STAT               SYNONYM

PUBLIC               DBA_HIST_SERVICE_WAIT_CLASS         SYNONYM

已选择19行。

如果这个对象从未起过如何作用，也可能不需要，这样的话最好删除该对象。

1.2   修正无效的对象(PL/SQL对象和视图)

使用ALTER…COMPILE命令编译对象，如果失败，则用show errors来查看原因（视图不能使用这个命令），使用dba_dependencies视图来确定编译错误的原因。

编译对象：

Alter procedure hr.add_reg compile;

Alter view rname compile;

使用utlrp脚本可以编译所有无效的对象。

示例：

1、  搭建环境

sys@ORCL> conn system/123

已连接。

system@ORCL> create user testuser identified by testuser;

用户已创建。

system@ORCL> grant dba to testuser;

授权成功。

system@ORCL> conn testuser/testuser

已连接。

testuser@ORCL> create table testtab(n1 number, d1 date);

表已创建。

testuser@ORCL> insert into testtab values(1,sysdate);

已创建 1 行。

testuser@ORCL> create or replace view v1 as select d1 from testtab;

 

视图已创建。

 

testuser@ORCL> create or replace procedure p1 as

  2  cnt number;

  3  begin

  4  select count(*) into cnt from testtab;

  5  end;

  6  /

 

过程已创建。

2、  确认对象的状态

testuser@ORCL> select object_name, object_type, status from user_objects;

 

OBJECT_NAME                 OBJECT_TYPE                         STATUS

----------------------------------- ----------------------------------- -------

TESTTAB                      TABLE                               VALID

V1                           VIEW                                VALID

P1                           PROCEDURE                           VALID

3、  执行DDL命令

testuser@ORCL> alter table testtab drop column d1;

表已更改。

4、  重新确认对象的状态

testuser@ORCL> select object_name, object_type, status from user_objects;

OBJECT_NAME              OBJECT_TYPE            STATUS

----------------------------------- ----------------------------------- -------

TESTTAB                      TABLE                     VALID

V1                           VIEW                     INVALID

P1                         PROCEDURE                  INVALID

5、  重新编译指定的过程

testuser@ORCL> alter procedure p1 compile;

过程已更改。

6、  重新编译指定视图

testuser@ORCL> alter view v1 compile;

警告: 更改的视图带有编译错误。

7、  通过dba_dependencies和dba_views视图诊断,解决问题

testuser@ORCL> select referenced_name,referenced_owner,referenced_type

  2  from user_dependencies where name='V1';

 

REFERENCED_NAME      REFERENCED_OWNER          REFERENCED_TYPE

-------------------- ------------------------------ -----------------

TESTTAB              TESTUSER                       TABLE

D1                   TESTUSER                       NON-EXISTENT

D1                   PUBLIC                         NON-EXISTENT

testuser@ORCL> select text from user_views where view_name='V1';

 

TEXT

-----------------------------------------------------------------

 

select d1 from testtab

从上可以看出testtab少了d1列，对testtab表增加d1列，重新编译视图v1，就可以完成修复对象。

testuser@ORCL> alter table testtab add (d1 date);

 

表已更改。

 

testuser@ORCL> alter view v1 compile;

 

视图已变更。

2、  无用的索引

如果索引变得无用，就必须对索引进行显式的修复操作。

2.1 标志无用的索引

    如果某条语句试图使用一个无用的索引，取决于skip_unusable_indexes参数。若skip_unusable_indexes为true,则这条语句会重新使用不需要该索引的执行计划，其执行结果总会成功，但其效率已降低。若为否，则会返回错误消息。

检查变得无用的索引

Select owner, index_name from dba_indexes where status=’unusable’;

2.2 修正无用的索引

需要重建索引，重建索引需要额外的存储空间。

重建命令alter index…rebuild

对索引重建启用nologging，只是为索引重建禁用重做生成。在索引重建之后，针对该索引的索引dml命令都会生成重做。

示例：

1、  搭建环境

 testuser@ORCL> create index d1_inx on testtab(d1);

 

索引已创建。

 

testuser@ORCL> create index n1_inx on testtab(n1);

索引已创建。

2、  确认索引状态

testuser@ORCL> select index_name, status from user_indexes;

 

INDEX_NAME                     STATUS

------------------------------ --------

D1_INX                         VALID

N1_INX                         VALID

3、  对表进行改动，使索引无效

testuser@ORCL> alter table testtab move;

 

表已更改。

 

testuser@ORCL> select index_name, status from user_indexes;

 

INDEX_NAME                     STATUS

------------------------------ --------

D1_INX                         UNUSABLE

N1_INX                         UNUSABLE

4、  诊断和解决问题

testuser@ORCL> alter index n1_inx rebuild online nologging;

 

索引已更改。

 

testuser@ORCL> select index_name, status from user_indexes;

 

INDEX_NAME                     STATUS

------------------------------ --------

D1_INX                         UNUSABLE

N1_INX                         VALID----------索引已重建恢复有效

我们也可以使用database control工具修复，在这里不再讲述。

3、  优化器统计

优化器紧密依赖于一些统计量，从而能够评价众多可执行计划的效率以及选择某个执行计划。统计量的正确性至关重要。

统计与PL/SQL无关，只与SQL有关。

3.1 对象统计量

整体统计量dba_tables视图：

。表中的记录数

。为表分配的存储块数

。存储块内的空闲空间大小

。每条记录的平均长度

。”链接”记录数

列统计量dba_columns

。不同值的数目

。最大值和最小值

。NULL值的数目

。平均列长度

索引统计量dba_indexes

。索引树的深度

。不同键值的大小

。聚合因子

与索引相关的统计量index_stats

。引用现存记录的索引数

。引用被删除记录的索引数

维护索引的方式为：记录被删除时，保留索引键。

3.2 收集统计量

对象统计量不是实时的，而是静态的。因此必须有规律地收集统计量。统计量的收集操作可以是自动的，也可以是手动的。

使用database control 工具、analyze命令和dbms_stats程序包中的gather_table_stats过程能够手动收集统计量。

收集统计量能够改善性能，但是在进行分析期间会对性能造成显著地影响。

示例：

自动化统计量收集

创建调度作业

BEGIN

sys.dbms_scheduler.create_job(

job_name => '"TESTUSER"."ANALYZE TESTTAB"',

job_type => 'PLSQL_BLOCK',

job_action => 'BEGIN

   DBMS_STATS.GATHER_TABLE_STATS(ownname => ''TESTUSER'',

                                 tabname => ''TESTTAB'',

                                 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

                                 method_opt => ''for all columns size repeat'',

                                degree => DBMS_STATS.AUTO_DEGREE,

                                 cascade=>TRUE

                                 );

END;',

start_date => systimestamp at time zone '+8:00',

job_class => 'DEFAULT_JOB_CLASS',

auto_drop => FALSE,

enabled => TRUE);

END;

使用database control工具执行调度作业一次

确认统计量是否收集：

testuser@ORCL> alter session set nls_date_format='dd-mm-yy hh24:mi:ss';

 

会话已更改。

 

testuser@ORCL> select table_name, last_analyzed from dba_tables

  2  where wner='TESTUSER'

  3  union

  4  select index_name, last_analyzed from dba_indexes

  5  where wner='TESTUSER';

 

TABLE_NAME                     LAST_ANALYZED

------------------------------ -----------------

D1_INX                         25-04-12 14:37:40

N1_INX                         25-04-12 14:37:40

TESTTAB                        25-04-12 14:37:40

4、  性能指标

统计量与指标的区别：统计量是一个原始数字，其本身可能无用；指标是一个经过转换变得有意义的统计量。

4.1 使用动态视图查看统计量

动态性能视图填充了来自实例或控制文件的信息，实例被打开后就能被查询；前缀为DBA、ALL或USER的视图则填充了来自数据字典的信息，只有数据库被打开后才能查询。

动态视图在数据库启动阶段被创建，在指定实例的生存期内进行更新，在数据库关闭阶段被删除。动态视图通常提供的是统计量而非指标。

常用的动态视图：

V$sysstat:该视图有大约300个统计量，这些统计量是监视活动的基础。

v$system_wait_class:该视图概述了可能导致会话或整个数据库运行缓慢的各种原因。

4.2使用DATABASE CONTROL查看性能指标

    Database control接口将统计量转换成指标(metric).

4.3 与性能问题相关的指标

。运行队列的长度：指示服务器cpu资源的紧张程度。

。分页速度：在服务器内存不足的情况下会增加。

。正在等待的数据库会话数及其原因。

。每秒钟的登陆与事务数。

。物理读取数以及每秒生成的重做量。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9537053/viewspace-722227/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9537053/viewspace-722227/