test25

如何收集表的部分统计信息

如果表很大，收集表的统计信息需要花很长的时间，这时可以仅收集表的部分统计信息提供优化器统计信息
收集表的5%的大小作为统计信息
  Analyze table m estimate statistics sample 5 percent

如何分析在线日志或归档日志中的内容

Sqlplus “/ as sysdba”
SQL> select member from v$logfile;
MEMBER
--------------------------------------------
/home/oracle/oradata/fanu10/redo01.log
/home/oracle/oradata/fanu10/redo02.log
/home/oracle/oradata/fanu10/redo03.log

Sqlplus “/ as sysdba”
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/home/oracle/oradata/fanu10/redo03.log',
DBMS_LOGMNR.NEW);   -----这里也可以是归档日志的绝对路径。

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/home/oracle10/oradata/orcl/redo02.log',DB
MS_LOGMNR.ADDFILE);  ------这里可以增加多个分析内容的日志路径。

PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(options=>16);
PL/SQL procedure successfully completed.

SQL> select sql_redo from v$logmnr_contents;
SQL_REDO
-------------------------------------------------------------------
delete from "SYS"."COL_USAGE$" where "OBJ#" = '324044' and "INTCOL#" = '1' and "
EQUALITY_PREDS" = '0' and "EQUIJOIN_PREDS" = '1' and "NONEQUIJOIN_PREDS" = '0' a
nd "RANGE_PREDS" = '0' and "LIKE_PREDS" = '0' and "NULL_PREDS" = '0' and "TIMEST
AMP" = TO_DATE('05-6月 -07', 'DD-MON-RR') and ROWID = 'AAAAHZAABAAAA1bABG';

delete from "SYS"."COL_USAGE$" where "OBJ#" = '324046' and "INTCOL#" = '1' and "
EQUALITY_PREDS" = '0' and "EQUIJOIN_PREDS" = '1' and "NONEQUIJOIN_PREDS" = '0' a
nd "RANGE_PREDS" = '0' and "LIKE_PREDS" = '0' and "NULL_PREDS" = '0' and "TIMEST
AMP" = TO_DATE('05-6月 -07', 'DD-MON-RR') and ROWID = 'AAAAHZAABAAAA1bABH';