[bbk2340] 第45集 - Chapter 09-Optimizing Sore Perations(00)

Ojbectives

After completing this lesson,you should be able to do the following

Understanding Statistics

管理statistic的package：dbms_stats.

Managing Statistics

 

Use the dbms_stats packages:

  • gather_table_stats
  • gather_index_stats
  • gather_schema_stats
  • gahter_database_stats
  • gather_stable_stats
创建表、插入数据 
SQL> create table t(id int,name char(10));

Table created.

SQL> insert into t values(0,'arcerzhang');

1 row created.

SQL> insert into t values(1,'arcerzhang');

1 row created.

SQL> insert into t values(2,'arcerzhang');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;

        ID NAME
---------- ----------
         0 arcerzhang
         1 arcerzhang
         2 arcerzhang
分析之前查询数据字典DBA_TABLES 
SQL> select num_rows,blocks from dba_tables where owner='HR' and table_name='T';

NUM_ROWS BLOCKS
---------- ----------
分析表 
SQL> exec dbms_stats.gather_table_stats('HR','T');

PL/SQL procedure successfully completed.
分析之后查询数据字典DBA_TABLES 
SQL> select num_rows,blocks from dba_tables where owner='HR' and table_name='T';

  NUM_ROWS     BLOCKS
---------- ----------
         3          5

 

分析完成后，将分析结果存放在数据字典里面的；上述图表列出了Tables、Columns、Indexes对应的信息（分析完成数据之后，各自存放在数据字典表中的位置）. 

注意:上述信息，是10g以后才有的，之前的版本是没有这些数据的.

When to Gather Statistics 

 

 

 

 

 

 

 

posted @ 2013-05-07 13:15  ArcerZhang  阅读(200)  评论(1)  编辑  收藏  举报