OCP读书笔记(14)

fateame

　　搜集统计信息
　　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> begin
dbms_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> begin
dbms_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> begin
dbms_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> begin
dbms_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> BEGIN
DBMS_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/utlxpls



SQL> @/u01/app/oracle/temptest/show_plan
select * from tab where b=1
SQL> @/u01/app/oracle/temptest/show_plan
select * from tab where b=5
　　收集直方图信息。看看是什么效果。由于列B唯一值的个数没有超过254因此产生的是等频直方图。



SQL> BEGIN
DBMS_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_plan
select * from tab where b=1
SQL> @/u01/app/oracle/temptest/show_plan
select * from tab where b=5
　　正在审核的统计信息
　　下面的例子说明了如何使SCOTT模式中EMP表的统计信息处于未发布状态：



begin
dbms_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 日，因此统计信息是刚刚收集的，我们再次收集统计信息：



begin
dbms_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 sysdba
SQL> shutdown immediate
SQL> startup mount
SQL> 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/dir
SQL> grant all on directory dump_dir to test;
　　测试环境已经建立好
　　一、启动捕获进程



SQL> conn system/a
SQL> 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/a
SQL> CREATE TABLE T(ID INT,NAME VARCHAR2(30));
SQL> !vi /u01/app/oracle/temptest/insert.sql
INSERT 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.sql
SQL> 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/a
SQL> 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 IMMEDIATE
SQL> STARTUP MOUNT
SQL> FLASHBACK DATABASE TO RESTORE POINT SP1;
SQL> ALTER DATABASE OPEN RESETLOGS;
SQL> CONN TEST/a;
SQL> select tname from tab;
SQL> conn /as sysdba
SQL> 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;