ITPub博客

首页 > 数据库 > Oracle > 统计信息管理

统计信息管理

原创 Oracle 作者:stilllovekk 时间:2016-03-11 15:46:17 0 删除 编辑

 

第一章   统计信息是什么

统计信息为了让oracle SQL优化器了解数据信息 包括数据量的多少和分布,以便SQL优化器 CBO能够获得较佳的执行计划,较好的执行计划 意味着oracle对于不同的SQL使用合理的访问路径和Join方式,简而言之是得到更好的性能。

一般情况下,查询统计信息会在如下视图中:

user_tables(dba_tables),

user_table_statistics(dba_tab_statistics)

第二章   查看是否开启自动收集

首先,oracle 11g在安装数据库软件的时候会提示是否开启自动收集统计信息,默认是开启的。

dba_autotas_client视图可以查询到是否开启了自动收集。

如上图:我的自动收集统计信息是开启的。

第三章   删除统计信息

为了重新进行测试,首先,把我的统计信息清空,然后关闭。

清除统计信息的方法可以自己从dbms_stats中去找,如下(如下显示的为其中的一部分):

SQL> desc dbms_stats

Element                        Type     

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

GET_COLUMN_STATS               PROCEDURE

GET_INDEX_STATS                PROCEDURE

GET_TABLE_STATS                PROCEDURE

DELETE_COLUMN_STATS            PROCEDURE

DELETE_INDEX_STATS             PROCEDURE

DELETE_TABLE_STATS             PROCEDURE

DELETE_SCHEMA_STATS            PROCEDURE

DELETE_DATABASE_STATS          PROCEDURE

EXPORT_COLUMN_STATS            PROCEDURE

EXPORT_INDEX_STATS             PROCEDURE

EXPORT_TABLE_STATS             PROCEDURE

EXPORT_SCHEMA_STATS            PROCEDURE

EXPORT_DATABASE_STATS          PROCEDURE

IMPORT_COLUMN_STATS            PROCEDURE

IMPORT_INDEX_STATS             PROCEDURE

IMPORT_TABLE_STATS             PROCEDURE

IMPORT_SCHEMA_STATS            PROCEDURE

IMPORT_DATABASE_STATS          PROCEDURE

GATHER_INDEX_STATS             PROCEDURE

GATHER_TABLE_STATS             PROCEDURE

GATHER_SCHEMA_STATS            PROCEDURE

GATHER_DATABASE_STATS          PROCEDURE

GATHER_SYSTEM_STATS            PROCEDURE

GET_SYSTEM_STATS               PROCEDURE

SET_SYSTEM_STATS               PROCEDURE

DELETE_SYSTEM_STATS            PROCEDURE

IMPORT_SYSTEM_STATS            PROCEDURE

EXPORT_SYSTEM_STATS            PROCEDURE

GATHER_DICTIONARY_STATS        PROCEDURE

DELETE_DICTIONARY_STATS        PROCEDURE

EXPORT_DICTIONARY_STATS        PROCEDURE

IMPORT_DICTIONARY_STATS        PROCEDURE

LOCK_TABLE_STATS               PROCEDURE

LOCK_PARTITION_STATS           PROCEDURE

LOCK_SCHEMA_STATS              PROCEDURE

UNLOCK_TABLE_STATS             PROCEDURE

UNLOCK_PARTITION_STATS         PROCEDURE

UNLOCK_SCHEMA_STATS            PROCEDURE

在官方文档中查询到删除schema统计信息的语法如下:

 

执行删除统计信息:

SQL> exec dbms_stats.delete_schema_stats('TEST');

 PL/SQL proceduresuccessfullycompleted

执行之后可以发现,统计信息已经清空。

SQL> select table_name,num_rows from user_tables;

 

TABLE_NAME                       NUM_ROWS

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

XINZI                         

T2                            

T1                            

TICTACTOE                     

TEST_TABLE                     

TEST                          

B                             

A                             

T                             

ZONGZHANG                     

10 rows selected

第四章   开启与关闭自动收集

4.1      关闭统计信息自动收集:

SQL> exec DBMS_AUTO_TASK_ADMIN.DISABLE(

    client_name => 'auto optimizer stats collection',

    operation => NULL,

    window_name => NULL);

PL/SQL procedure successfully completed

再查看一下是否开启:

SQL> select client_name,status from dba_autotask_client;

 

CLIENT_NAME                                                      STATUS

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

auto optimizer stats collection                               DISABLED

auto space advisor                                               ENABLED

sql tuning advisor                                               DISABLED

可以发现,统计信息自动收集已经关闭。

4.2      开启自动收集:

SQL> exec DBMS_AUTO_TASK_ADMIN.ENABLE(

    client_name => 'auto optimizer stats collection',

    operation => NULL,

    window_name => NULL);

PL/SQL procedure successfully completed

执行完结果如下:

SQL> select client_name,status from dba_autotask_client;

 

CLIENT_NAME                                                      STATUS

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

auto optimizer stats collection                                  ENABLED

auto space advisor                                               ENABLED

sql tuning advisor                                               DISABLED

第五章   修改自动收集执行时间

查看数据库执行自动收集统计信息的时间:

关于如何入查询执行的时间,按照如下步骤:

1,         DBA_AUTOTASK_CLIENT中找到该任务对应的window_group

2,         DBA_SCHEDULER_WINGROUP_MEMBERS中找到该window_groupwindow_name

3,         DBA_SCHEDULER_WINDOWS找到每个window_name对应的时间(repeat_interval

SQL> select t1.window_name, t1.repeat_interval, t1.duration

  2    from dba_scheduler_windows t1, dba_scheduler_wingroup_members t2

  3   where t1.window_name = t2.window_name

  4     and t2.window_group_name = 'ORA$AT_WGRP_OS'

 

下面开始修改执行时间,把周一的windos改为中午12点执行,持续3个小时,如下:

5.1      停止该窗口

SQL> BEGIN

  2    DBMS_SCHEDULER.DISABLE(name => '"SYS"."MONDAY_WINDOW"', force => TRUE);

  3  END;

  4  /

PL/SQL procedure successfully completed

5.2      修改执行时间

SQL> BEGIN

  2    DBMS_SCHEDULER.SET_ATTRIBUTE(name      => '"SYS"."MONDAY_WINDOW"',

  3   attribute => 'REPEAT_INTERVAL',

  4  value     => 'FREQ=WEEKLY;BYDAY=MON;BYHOUR=12;BYMINUTE=0;BYSECOND=0');

  5  END;

  6  /

 

PL/SQL procedure successfully completed

5.3      修改持续时间

SQL> BEGIN

  2    DBMS_SCHEDULER.SET_ATTRIBUTE(name      => '"SYS"."MONDAY_WINDOW"',

  3                                 attribute => 'DURATION',

  4                                 value     => numtodsinterval(180, 'minute'));

  5  END;

  6  /

PL/SQL procedure successfully completed

5.4      重启该window

SQL> BEGIN

  2    DBMS_SCHEDULER.ENABLE(name => '"SYS"."FRIDAY_WINDOW"');

  3  END;

  4  /

PL/SQL procedure successfully completed

5.5      再次查看执行时间

 

可以发现,执行时间和持续时间都已经改变。

第六章   手工执行统计信息收集

在前面删除了test这个schema下的统计信息。下面进行收工收集,方法跟第二章一样,desc找到function或者proceudre,然后去官方文档找语法。此步骤省略。

6.1      收集schema=test的统计信息

SQL> exec dbms_stats.gather_schema_stats('TEST');

PL/SQL procedure successfully completed

6.2      查看统计信息

SQL> select table_name,num_rows from user_tables;

 

TABLE_NAME                       NUM_ROWS

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

XINZI                                 692

T2                                2327293

T1                                2256768

TICTACTOE                               0

TEST_TABLE                          30000

TEST                                    0

B                                       7

A                                       2

T                                    8031

ZONGZHANG                             400

10 rows selected

第七章   收集统计信息跳过某张表

有的时候呢,个别表的数据量非常的大,收集统计信息的时候非常非常的慢;而且这种大表在短时间内变化很小,这时候,如果跟其他小表一样频繁收集统计信息,无疑会给数据库带来不必要的压力。所以,在收集整体统计信息的时候,可以跳过个别大表。过程如下:

7.1      首先,还得删除统计信息

SQL> exec dbms_stats.delete_schema_stats('TEST');

 PL/SQL proceduresuccessfullycompleted

在第五章可以发现,表T1T2比较大,现在不想收集这两张表统计信息,操作如下:

7.2      查看当前统计信息情况

SQL> select table_name,num_rows from user_tables;

 

TABLE_NAME                       NUM_ROWS

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

XINZI                         

T2                            

T1                            

TICTACTOE                     

TEST_TABLE                    

TEST                          

B                             

A                             

T                             

ZONGZHANG                     

10 rows selected

7.3      在收集前锁住T1T2表,使得不收集该表统计信息

SQL> exec dbms_stats.lock_table_stats('TEST','T1');

PL/SQL procedure successfully completed

 

SQL>  exec dbms_stats.lock_table_stats('TEST','T2');

PL/SQL procedure successfully completed

7.4      手动收集test这个schema的统计信息

SQL> exec dbms_stats.gather_schema_stats('TEST');

PL/SQL procedure successfully completed

7.5      再次查看统计信息

SQL> select table_name,num_rows from user_tables;

 

TABLE_NAME                       NUM_ROWS

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

XINZI                                 692

T2                            

T1                            

TICTACTOE                               0

TEST_TABLE                          30000

TEST                                    0

B                                       7

A                                       2

T                                    8031

ZONGZHANG                             400

10 rows selected

可以发现,在收集统计信息的时候,自动跳过了那两张表。如果要解锁的话在dbms_stats包中还有unlock_table_stats等函数,在此不一一举例。

第八章   手动设置统计信息

有的时候,真实的统计信息无法让cbo生成我们想要的执行计划,这时候可以手动设置统计信息。如下案例,将表T1的统计信息设置为行数100W行。

8.1      set_table_stats

官方文档语法如下:

 

SQL> begin

  2  dbms_stats.set_table_stats(

  3  ownname =>'TEST',

  4  tabname =>'T1',

  5  numrows =>'1000000');

  6  end;

  7  /

PL/SQL procedure successfully completed

 设置之后表T1的统计信息如下:

SQL> select table_name,num_rows from user_tables;

 

TABLE_NAME                       NUM_ROWS

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

XINZI                                 692

T2                            

T1                                1000000

TICTACTOE                               0

TEST_TABLE                          30000

TEST                                    0

B                                       7

A                                       2

T                                    8031

ZONGZHANG                             400

10 rows selected

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30123160/viewspace-2055662/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2015-01-30

  • 博文量
    20
  • 访问量
    29117