第一章 统计信息是什么
统计信息为了让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_group的window_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
在第五章可以发现，表T1，T2比较大，现在不想收集这两张表统计信息，操作如下：
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 在收集前锁住T1，T2表，使得不收集该表统计信息
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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/30123160/viewspace-2055662/
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
