对表进行统计分析会修改那些表

基于CBO的优化根据statistics来选择explain plan.但analyze table 对data dictionary作了那些修改呢?

新创建一个用户

create user oak identified by oak

default tablespace users

temporary tablespace temp

alter user oak quota unlimited on users;

创建测试用表及索引

sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Aug 2 12:50:05 2008

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

SQL> conn oak/oak

Connected.

SQL> create table test

  2  (

  3  nu number,

  4  name char(10)

  5  );

Table created.

SQL> create index ndx_test on test(nu);

插入数据

SQL> begin

  2  for i in 1..10000 loop

  3  insert into test values(i ,'oak');

  4  end loop;

  5  commit;

  6  end;

  7  /

分析前查询

1. SQL> select table_name ,num_rows from user_tables;

TABLE_NAME                       NUM_ROWS

------------------------------ ----------

TEST

2. SQL> select index_name ,num_rows from user_indexes;

INDEX_NAME                       NUM_ROWS

------------------------------ ----------

NDX_TEST

3. SQL> select column_name ,endpoint_number ,endpoint_value

  2  from user_tab_histograms

  3  where table_name='TEST';

no rows selected

使用DBMS_STATS.GATHER_TABLE_STATS分析表

execute dbms_stats.gather_table_stats (ownname => 'oak', tabname => 'test' ,estimate_percent => null, method_opt =>'for all columns' ,cascade => true)

分析后查询

1. SQL> select table_name ,num_rows from user_tables;

TABLE_NAME                       NUM_ROWS

------------------------------ ----------

TEST                                10000

2. select index_name ,num_rows from user_indexes;

INDEX_NAME                       NUM_ROWS

------------------------------ ----------

NDX_TEST                            10000

3. SQL> select column_name ,endpoint_number ,endpoint_value

  2  from user_tab_histograms

  3  where table_name='TEST';

COLUMN_NAME     ENDPOINT_NUMBER ENDPOINT_VALUE

--------------- --------------- --------------

NU                            0              1

NU                            1            134

NU                            2            268

NU                            3            402

………….

77 rows selected.

4.新的查询

SQL> select inserts ,updates ,deletes from user_tab_modifications where table_name='TEST';

no rows selected

insert数据

SQL> begin

  2  for i in 10001 .. 12000 loop

  3  insert into test values(i ,'oak');

  4  end loop;

  5  commit;

  6  end;

  7  /

delete数据

SQL> delete from test where nu>11000;

1000 rows deleted.

再次查询

SQL>  select inserts ,updates ,deletes from user_tab_modifications where table_name='TEST';

no rows selected

处于对性能的考虑oracle对这个视图写有delay所以还是没有数据.你可以运行DBMS_STATS包的FLUSH_DATABASE_MONITORING_INFO procedure来把信息写回表.

execute dbms_stats.FLUSH_DATABASE_MONITORING_INFO

SQL>  select inserts ,updates ,deletes from user_tab_modifications where table_name='TEST';

   INSERTS    UPDATES    DELETES

---------- ---------- ----------

      2000          0       1000

注:在oracle10g中有一个收集统计信息的job:gather_stats_job每天都会在maintenance window time 运行.其它版本就不知道了.