搜集统计信息
1. dbms_stats
gather_schema_stats
1)option：有四个选项：
a.gather empty：只分析目前还没有搜集过统计信息的表
SQL> conn scott/tiger;SQL> select table_name, num_rows, blocks from user_tables;
只有big_table和t没有搜集
SQL> begindbms_stats.gather_schema_stats( ownname => 'SCOTT', options => 'GATHER EMPTY', estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for all columns size repeat', degree => 3);end;/SQL> select table_name, num_rows, blocks from user_tables;SQL> select count(*) from tab;
此次只收集了BIG_TABLE和T，因为这个表没有搜集过，TAB也没有搜集，因为之前他已经搜集过了
b.gather stale： 只重新分析修改量超过10%的表（这些修改包括插入、更新和删除）SQL> begin DBMS_STATS.SET_SCHEMA_PREFS('SCOTT','STALE_PERCENT', 10);end;/SQL> conn scott/tiger;SQL> create table tt as select * from emp;SQL> select table_name, num_rows, blocks from user_tables;SQL> begindbms_stats.gather_schema_stats( ownname => 'SCOTT', options => 'GATHER STALE', estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for all columns size repeat', degree => 3);end;/ tab中的数据修改量已经超出了10%，所以是stale的，此次收集只搜集stale，tt属于目前还没有统计的表所以不搜集
c. gather auto：重新分析当前没有统计的对象，以及统计数据过期的对象
SQL> begindbms_stats.gather_schema_stats( ownname => 'SCOTT', options => 'GATHER AUTO', estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for all columns size repeat', degree => 3);end;/SQL> select table_name, num_rows, blocks from user_tables;
此次收集stale和空的
d.gather：重新分析整个模式下的所有对象，包括表，索引等
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';SQL> select table_name, num_rows, blocks, LAST_ANALYZED from user_tables;SQL> begindbms_stats.gather_schema_stats( ownname => 'SCOTT', options => 'GATHER', estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for all columns size repeat', degree => 3);end;/SQL> select table_name, num_rows, blocks, LAST_ANALYZED from user_tables;
从时间上可以看出所有的表都被收集了
直方图
SQL> create table tab (a number, b number);SQL> begin for i in 1..10000 loop insert into tab values (i, i); end loop; commit;end;/SQL> update tab set b=5 where b between 6 and 9995;SQL> commit;SQL> create index ix_tab_b on tab(b);
然后分析表，强制使列B不产生直方图
SQL> BEGINDBMS_STATS.GATHER_TABLE_STATS( OWNNAME => 'SCOTT', TABNAME => 'TAB', CASCADE => TRUE, METHOD_OPT => 'FOR COLUMNS B SIZE 1 ');END;/
在没有直方图的情况下，在B列上进行等值查询的时候，都是索引范围扫描
show_plan.sql内容如下：
explain plan for &sql;@?/rdbms/admin/utlxplsSQL> @/u01/app/oracle/temptest/show_planselect * from tab where b=1SQL> @/u01/app/oracle/temptest/show_planselect * from tab where b=5
收集直方图信息。看看是什么效果。由于列B唯一值的个数没有超过254因此产生的是等频直方图。
SQL> BEGINDBMS_STATS.GATHER_TABLE_STATS( OWNNAME => 'SCOTT', TABNAME => 'TAB', CASCADE => TRUE, METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO ');END;/
在B=1时候采用索引扫描，而B=5时候，已经采用全表扫描了，说明直方图起了作用
SQL> @/u01/app/oracle/temptest/show_planselect * from tab where b=1SQL> @/u01/app/oracle/temptest/show_planselect * from tab where b=5
正在审核的统计信息
下面的例子说明了如何使SCOTT模式中EMP表的统计信息处于未发布状态：
begindbms_stats.set_table_prefs ( ownname => 'SCOTT', tabname => 'EMP', pname => 'PUBLISH', pvalue => 'FALSE');end;/
一旦您将发布首选项设置为false，收集的统计信息就将被标记为正在审核，下面是现在该表的统计信息：
SQL> select num_rows, to_char(last_analyzed,'mm/dd/yy hh24:mi:ss') from user_tables where table_name = 'EMP';
今天是 2007 年 9 月 21 日，因此统计信息是刚刚收集的，我们再次收集统计信息：
begindbms_stats.gather_table_stats ( ownname => 'SCOTT', tabname => 'EMP', estimate_percent=> 100);end;/
如果您再次查看统计信息：
您可以看到行数没变，同时last_analyzed 值也没变，那么，刚收集的统计信息发生了什么变化呢？统计信息被标记为正在审核
正在审核的统计信息可在视图 USER_TAB_PENDING_STATS 中看到：SQL> select num_rows, to_char(last_analyzed,'mm/dd/yy hh24:mi:ss') from user_tab_pending_stats where table_name = 'EMP';
现在表有9行，显示正在审核的统计信息，同时显示有收集时间，假定您想让优化程序看到这些正在审核的统计信息，您可以发布它们：
begin dbms_stats.publish_pending_stats('SCOTT', 'EMP');end;/ 检查视图user_tab_pending_stats，发现它现在是空的，如果现在检查USER_TABLES，您会看到最新的统计信息：
这种将统计信息的收集和发布相分开的方法也可用于分区的表，假定正在逐个分区地加载一个表，不希望只提供部分信息给优化程序，更希望所有分区的统计信息能够同时被优化程序看到，但是还想在加载分区后立刻利用这一时间，那么，可以在加载分区后立即收集它的统计信息，但不发布这些信息，分析完所有分区后，您可以一次性发布这些统计信息
数据库重放
首先创建测试用户：
SQL> conn / as sysdbaSQL> shutdown immediateSQL> startup mountSQL> alter database flashback on;SQL> alter database open;SQL> create tablespace test datafile '/u01/app/oracle/oradata/orcl/test01.dbf' size 50m;SQL> grant connect,resource to test identified by a;SQL> alter user test default tablespace test;SQL> create restore point sp1;SQL> create or replace directory dump_dir as '/u01/app/oracle/dir';SQL> !mkdir -p /u01/app/oracle/dirSQL> grant all on directory dump_dir to test;
测试环境已经建立好
一、启动捕获进程
SQL> conn system/aSQL> EXEC DBMS_WORKLOAD_CAPTURE.START_CAPTURE('CAPTURE1','DUMP_DIR');SQL> SELECT ID, NAME, DBNAME, DBVERSION, DIRECTORY, STATUS FROM DBA_WORKLOAD_CAPTURES; test对象对应的操作系统目录下将会生成几个文件：
ls -l /u01/app/oracle/dir
二、模拟数据库活动
SQL> conn test/aSQL> CREATE TABLE T(ID INT,NAME VARCHAR2(30));SQL> !vi /u01/app/oracle/temptest/insert.sqlINSERT INTO T VALUES(1,'ICOL$');INSERT INTO T VALUES(2,'I_USER1');INSERT INTO T VALUES(3,'CON$');INSERT INTO T VALUES(4,'UNDO$');INSERT INTO T VALUES(5,'C_COBJ#');INSERT INTO T VALUES(6,'I_OBJ#');INSERT INTO T VALUES(7,'PROXY_ROLE_DATA$');INSERT INTO T VALUES(8,'I_IND1');INSERT INTO T VALUES(9,'I_CDEF2');INSERT INTO T VALUES(10,'I_OBJ5');INSERT INTO T VALUES(11,'I_PROXY_ROLE_DATA$_1');INSERT INTO T VALUES(12,'FILE$');INSERT INTO T VALUES(13,'UET$');INSERT INTO T VALUES(14,'I_FILE#_BLOCK#');INSERT INTO T VALUES(15,'I_FILE1');INSERT INTO T VALUES(16,'I_CON1');INSERT INTO T VALUES(17,'I_OBJ3');INSERT INTO T VALUES(18,'I_TS#');INSERT INTO T VALUES(19,'I_CDEF4');INSERT INTO T VALUES(20,'IND$');INSERT INTO T VALUES(21,'SEG$');INSERT INTO T VALUES(22,'C_TS#');SQL> @/u01/app/oracle/temptest/insert.sqlSQL> COMMIT;SQL> SELECT COUNT(1) FROM T;SQL> create table trans(id number,c1 varchar2(100),c2 date,c3 number,c4 number);SQL> create sequence trans_id_seq start with 1 increment by 1;SQL> declare l_stmt varchar2(2000);begin for ctr in 1..1000 loop l_stmt := 'insert into trans values ('||trans_id_seq.nextval||','|| ''''||dbms_random.string('U',20)||''','||'sysdate - '|| round(dbms_random.value(1,365))||','||round(dbms_random.value(1,99999999),2)||','|| round(dbms_random.value(1,99))||')'; dbms_output.put_line(l_stmt); execute immediate l_stmt; commit; end loop;end;/SQL> select count(*) from trans; 三、结束捕获进程
SQL> conn system/aSQL> EXEC DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE;SQL> SELECT ID, NAME, DBNAME, DBVERSION, DIRECTORY, STATUS FROM DBA_WORKLOAD_CAPTURES;
此时数据库的活动全部记录在以下目录中的文件中
ls -l /u01/app/oracle/dir
四、闪回到SP1时刻
由于只有一个11G的数据库环境,因此重放也是在这个数据库中进行的。此时需要闪回到以前创建表的时候,以便观察
SQL> CONN / AS SYSDBA;SQL> SHUTDOWN IMMEDIATESQL> STARTUP MOUNTSQL> FLASHBACK DATABASE TO RESTORE POINT SP1;SQL> ALTER DATABASE OPEN RESETLOGS;SQL> CONN TEST/a;SQL> select tname from tab;SQL> conn /as sysdbaSQL> create or replace directory dump_dir as '/u01/app/oracle/dir';SQL> grant all on directory dump_dir to test;
五、准备重放进程
首先进行预处理
SQL> EXEC DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE('DUMP_DIR');SQL> EXEC DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY('REPLAY1','DUMP_DIR');SQL> EXEC DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY; 以ORACLE用户登录到操作系统中,启动重放客户端
wrc userid=system password=a replaydir=/u01/app/oracle/dir
此时可以看到等待重放
重新回到SQLPLUS下：
六、开始重放
SQL> EXEC DBMS_WORKLOAD_REPLAY.START_REPLAY;SQL> SELECT ID, NAME, DBNAME, DBVERSION, DIRECTORY, STATUS FROM DBA_WORKLOAD_REPLAYS;
此时可以看到重放客户端会出现 Replay started
wrc userid=system password=oracle replaydir=/u01/app/oracle/dir
重放完成后,DBA_WORKLOAD_REPLAYS的STATUS列会标记为COMPLETED.
SQL> SELECT ID, NAME, DBNAME, DBVERSION, DIRECTORY, STATUS FROM DBA_WORKLOAD_REPLAYS;
重放客户端会自动退出
wrc userid=system password=oracle replaydir=/u01/app/oracle/dir
SQL> select count(*) from t;SQL> select count(*) from trans;