集簇因子对执行计划影响和统计信息拷贝

[实施步骤]

1.前提准备

1.1创建新表和索引：

SQL>  create table emp2 as select * from emp;

SQL>createindex EMP_EMPNO_IND on emp2(empno);

1.2分析emp2：

SQL> analyze table test computestatistics;

1.3查看列值的集簇因子：

selectt.table_name,t.NUM_ROWS,t.BLOCKS,t.AVG_ROW_LEN,i.index_name,i.CLUSTERING_FACTOR

from dba_tables t,dba_indexes i

where t.table_name=i.table_name

and t.owner='SCOTT'

and t.table_name='EMP2';

1.4使用索引列进行查询：

SQL> select * from emp2 whereempno=7788;

发现查询走的是索引

1.5修改pctfree的值，改为93,插入多次数据

SQL>alter table emp2 pctfree 93;

SQL>insert into emp2 select * from emp;

SQL>/

…

1.6分析表，查看执行计划

SQL> analyze table emp2 estimatestatistics;

SQL> select * from emp2 whereempno=7788;

发现走的是全表扫描

1.7查看集簇因子情况

SQL>select t.table_name,t.NUM_ROWS,t.BLOCKS,t.AVG_ROW_LEN,i.index_name,i.CLUSTERING_FACTOR

  2  fromdba_tables t,dba_indexes i

  3 where t.table_name=i.table_name

  4  andt.owner='SCOTT'

  5  andt.table_name='EMP2';

1.8改变集簇因子

SQL> create table emp2_tmp as select *from emp2;

 

SQL> truncate table emp2;

 

SQL> insert into emp2 select * fromemp2_tmp order by empno;

 

SQL> commit;

 

SQL> select * from emp2 where rownum< 10;

1.9重新分析表，查看新的执行计划

SQL> analyze table emp2 estimatestatistics;

 

SQL> /select * from emp2 whereempno=7788

1.10查看集簇因子的改变

SQL>selectt.table_name,t.NUM_ROWS,t.BLOCKS,t.AVG_ROW_LEN,i.index_name,i.CLUSTERING_FACTOR

  2  fromdba_tables t,dba_indexes i

  3 where t.table_name=i.table_name

  4  andt.owner='SCOTT'

  5  andt.table_name='EMP2';

集簇因子和blocks一致，所以走的是索引

总结

验证了集簇因子对执行计划的影响。

 

 

统计信息拷贝

[实施步骤]

1.环境部署

1.1创建表和索引

SQL> create table jicu asselect * from emp;

SQL> create indexjicu_ind_empno on jicu(empno);

1.2插入数据

SQL>  begin                                    

  2  fori in 1..10000 loop

  3  insertinto jicu values(7788,'WARD','SALESMAN',7839,sysdate,3000,2000,20);

  4  endloop;

  5  end;

  6  /

SQL>commit;

 

1.3 分析表，查看集簇因子

SQL> analyze table jicuestimate statistics;

SQL>  selectt.table_name,t.NUM_ROWS,t.BLOCKS,t.AVG_ROW_LEN,i.index_name,

  2 i.CLUSTERING_FACTOR from dba_tables t,dba_indexes i

  3  where t.table_name=i.table_name

  4  andt.owner='SCOTT'

  5  andt.table_name='JICU';

1.4 建立柱状图

SQL> execDBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'JICU', method_opt => 'FOR COLUMNSempno');

1.5查看执行计划

 

SQL> select * from jicuwhere empno=7788;

select * from jicu whereempno=7499;

 

2.环境部署EMREP库

2.1创建表和索引

SQL> create table jicu asselect * from emp;

SQL> create indexjicu_ind_empno on jicu(empno);

2.2分析表，查看集簇因子

SQL> analyze table jicu estimate statistics;

SQL> selectt.table_name,t.NUM_ROWS,t.BLOCKS,t.AVG_ROW_LEN,i.index_name,

  2 i.CLUSTERING_FACTOR from dba_tables t,dba_indexes i

  3 where t.table_name=i.table_name

  4  andt.owner='SCOTT'

  5  andt.table_name='JICU';

2.3查看执行计划

select * from jicu whereempno=7788;

3拷贝统计信息

3.1将统计信息导出到jicu_stats

SQL>  execdbms_stats.create_stat_table('scott','jicu_stats','system');

SQL>  execdbms_stats.export_table_stats('scott','jicu',NULL,'jicu_stats','liuj',true);

3.2导出jicu_stats表

[oracle@localhost ~]$ exptables=jicu_stats file=/home/oracle/jicu_stats.dbf

 

Export: Release 10.2.0.1.0 -Production on Wed Oct 15 08:37:03 2014

 

Copyright (c) 1982, 2005,Oracle.  All rights reserved.

 

 

Username: scott

Password:

 

Connected to: Oracle Database10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAPand Data Mining options

Export done in US7ASCIIcharacter set and AL16UTF16 NCHAR character set

server uses ZHS16GBKcharacter set (possible charset conversion)

 

About to export specifiedtables via Conventional Path ...

. . exporting table                     JICU_STATS         23 rows exported

Export terminatedsuccessfully without warnings.

3.3复制jicu_stats表到EMREP库，并导入

scp jicu_stats.dbf192.168.56.102:/home/oracle/

[oracle@gc2 ~]$ impfile=jicu_stats.dbf

 

3.4统计信息导入数据字典

SQL> execdbms_stats.import_table_stats('scott','jicu',NULL,'jicu_stats','liuj',true);

 

3.5查看集簇因子和执行计划

SQL> selectt.table_name,t.NUM_ROWS,t.BLOCKS,t.AVG_ROW_LEN,i.index_name,

  2   i.CLUSTERING_FACTOR from dba_tables t,dba_indexes i

  3   where t.table_name=i.table_name

  4   and t.owner='SCOTT'

  5   and t.table_name='JICU';

 

SQL> select * from jicuwhere empno=7788;

SQL>  select * from jicu where empno=7499;

总结

表的统计信息都是存在放数据字典中的，数据字典不能进行导入导出操作的。通过将数据字典中的数据导入到一张普通表中，再把普通表中的数据导出，然后导入到测试数据库中，最后将统计信息导入到测试库的数据字典中。
这样就在测试库上模拟了生产库的统计信息环境。可以利用这个统计信息在测试库上对应用进行调试，调试完毕后再应用到生产库。