Managing Database Performance
■
①
select owner,object_name,object_type from dba_objects wherev status='INVALID';
alter procedure XXX compile;
alter view XX compile;
②Recompile hundreds or thousands of invalid objects.
$ORACLE_HOME/rdbms/admin/utlrp.sql
■
If a procedure does not compile,use the SQL*Plus command SHOW ERRORS to see why not.
(Unfortunately, SHOW ERRORS is not supported for views.)
■
The DBMS_STATS procedures can take many arguments to influence the
depth of the analysis, far more than the older ANALYZE command.
表分析:
analyze table test compute statistics;
analyze index TEST_INDEX validate structure
begin
dbms_stats.gather_table_stats('GAU001', 'TEST');
end;
begin
dbms_stats.gather_table_stats(ownname => 'TESTUSER',
tabname => 'TESTTAB',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
cascade => true,
method_opt => 'for all indexed columns size auto');
end;
■eg:
SQL> edit;
已写入 file afiedt.buf
1 create or replace procedure test1
2 as
3 begin
4 insert into table x values(0);
5 commit;
6* end;
SQL> /
警告: 创建的过程带有编译错误。
SQL> show errors;
PROCEDURE TEST1 出现错误:
LINE/COL ERROR
-------- ------------------------------------------
4/1 PL/SQL: SQL Statement ignored
4/13 PL/SQL: ORA-00903: invalid table name
SQL> analyze table test compute statistics;
表已分析。
