OCP认证考试指南（14）：管理数据库性能（1）

1、无效的对象

Oracle总是会尝试自动重编译无效的PL/SQL对象和视图，但是可能不会成功。虽然Oracle可能建议手动地进行编译，不过我们并非必须进行这个操作。

1.1、标识无效的对象

为了标识数据库中的所有无效的对象，我们可以作为SYSTEM用户或其他有权限的用户做如下查询。

^?[Copy to clipboard] View Code SQL

SQL> select owner, object_name, object_type 2 from dba_objects 3 where status = 'INVALID';

1.2、修正无效的对象

编译对象

^?[Copy to clipboard] View Code SQL

SQL> alter procedure ADD_NUMS compile;   Procedure altered.

如果编译成功，就不存在问题，如果编译失败，就需要找出失败的原因。

^?[Copy to clipboard] View Code SQL

SQL> show errors

如果希望确定编译错误的原因，通常需要首先使用DBA_DEPENDENCIES视图。

^?[Copy to clipboard] View Code SQL

SQL> desc dba_dependencies; Name Null? Type ----------------------------------------- -------- --------------- OWNER NOT NULL VARCHAR2(30) NAME NOT NULL VARCHAR2(30) TYPE VARCHAR2(17) REFERENCED_OWNER VARCHAR2(30) REFERENCED_NAME VARCHAR2(64) REFERENCED_TYPE VARCHAR2(17) REFERENCED_LINK_NAME VARCHAR2(128) DEPENDENCY_TYPE VARCHAR2(4)

如果要重新编译成百上千的的无效对象，可以运行下面命令。

^?[Copy to clipboard] View Code SQL

SQL> @?/rdbms/admin/utlrp

练习一个修正无效对象的例子。

^?[Copy to clipboard] View Code SQL

############################################# # 建个用户，也是新建个模式 #############################################   SQL> grant dba to testuser identified by testuser;   Grant succeeded.   SQL> conn testuser/testuser Connected.   ############################################# # 建表、建视图、建过程 #############################################   SQL> create table testtab(n1 number, d1 date);   Table created.   SQL> insert into testtab values (1, sysdate);   1 row created.   SQL> create or replace view v1 2 as 3 select d1 from testtab;   View created.   SQL> create or replace procedure p1 2 as 3 cnt number; 4 begin 5 select count(*) into cnt from testtab; 6 end; 7 /   Procedure created.   ############################################# # 查询对象的状态，都是VALID # 把表删除一个列后，视图和过程的状态都为INVALID #############################################   SQL> select object_name, object_type, status from user_objects;   OBJECT_NAME OBJECT_TYPE STATUS -------------------- ------------------- ------- V1 VIEW VALID TESTTAB TABLE VALID P1 PROCEDURE VALID   SQL> alter table testtab drop column d1;   Table altered.   SQL> select object_name, object_type, status from user_objects;   OBJECT_NAME OBJECT_TYPE STATUS -------------------- ------------------- ------- V1 VIEW INVALID TESTTAB TABLE VALID P1 PROCEDURE INVALID   ############################################# # 重新编译之后，过程可以，视图报错。因为过程没有根据名称引用列，视图是基于某个列的 #############################################   SQL> alter procedure p1 compile;   Procedure altered.   SQL> alter view v1 compile;   Warning: View altered with compilation errors.   ############################################# # 通过诊断，我们能发现原因 #############################################   SQL> 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   SQL> select text from user_views where view_name = 'V1';   TEXT ------------------------------------------------------------------ select d1 from testtab   ############################################# # 将指定列添加回TESTTAB表 #############################################   SQL> alter table testtab add (d1 date);   Table altered.   SQL> alter view v1 compile;   View altered.

2、无用的索引

2.1、标识无用的索引

旧版本中执行SQL语句时，如果指定会话试图使用无用的索引，就会立即返回一条错误的消息，同时语句执行失败。如果希望10G数据库实现旧版本中无用索引导致返回错误消息的功能，可以执行以下命令。（默认为TRUE）

^?[Copy to clipboard] View Code SQL

SQL> alter system set skip_unusable_indexes = false;

检测就得无用的索引

^?[Copy to clipboard] View Code SQL

SQL> select owner, index_name from dba_indexes where status = 'UNUSABLE';

2.2、修正无用的索引

如果rowid不正确，索引就会标记为无用。为了修复无用索引，必须使用ALTER INDEX…REBUILD命令。索引重建过程需要额外的存储空间。

REBUILD命令语法具有多种选项，其中最重要的选项是TABLESPACE、ONLINE以及NOLOGGING。在默认情况下，索引在其当前表空间内重建，不过如果使用TABLESPACE关键字指定某个表空间，重建就会动至这个表空间内进行。同样在默认情况下，重建过程会为DML命令锁定指定的表，不过使用ONLINE关键字可以避免这种状况。NOLOGGING关键字则指示不为索引重建操作生成重做，这样能更快速完成重建。

启用NOLOGGING选项只是为索引重建禁用重做生成，在索引重建之后，针对该索引的所有DML命令都会像平时一样生成重做。

^?[Copy to clipboard] View Code SQL

############################################# # 用上面测试过的用户和表，创建两个索引 #############################################   SQL> create index n1_idx on testtab(n1);   Index created.   SQL> create index d1_idx on testtab(d1);   Index created.   ############################################# # 查看索引状态有效，之后我们移动指定的表，再查状态变成无用索引 #############################################   SQL> select index_name, status from user_indexes;   INDEX_NAME STATUS ------------------------------ -------- N1_IDX VALID D1_IDX VALID   SQL> alter table testtab move;   Table altered.   SQL> select index_name, status from user_indexes;   INDEX_NAME STATUS ------------------------------ -------- N1_IDX UNUSABLE D1_IDX UNUSABLE   ############################################# # 重建索引 #############################################   SQL> alter index n1_idx rebuild online nologging;   Index altered.   SQL> select index_name, status from user_indexes;   INDEX_NAME STATUS ------------------------------ -------- N1_IDX VALID D1_IDX UNUSABLE