oracle10g 统计信息查看、收集

oracle10g 统计信息查看、收集

 

1.    统计信息查看... 2

1.1      单个表的全局统计信息、统计效果查看... 2

2.    统计信息分析（收集）... 2

2.1      分析工具选择... 2

2.2      分析前做index重建... 2

2.3      分析某数据表，可以在PL/SQL的command window下执行的... 2

2.4      分析SCHEMA，在SQLPLUS中进行的... 3

2.5      dbms_stats.gather_schema_stats详解... 3

2.6      初始化参数statistics_level与oracle默认统计信息收集JOB的关系... 4

3.    改良、辅助oracle默认统计信息收集JOB. 4

3.1 创建新的统计信息收集PROC、JOB，每周末执行一次... 4

3.2 查看新建的统计信息收集JOB的状态... 5

3.3 批量生成收信统计信息(按table)的SQL. 5

4.    最有力度的统计信息收集命令(失败与成功纪实) 5

4.1 失败的统计信息收集命令... 5

4.2尝试的命令，各命令都改变不了核心SQL的执行计划... 6

4.3按表收集统计信息：改变了核心SQL的执行计划---OK. 6

4.4统计信息收集实战的重要结论（oracle公司技术人员推荐）... 6

 

 

1.   统计信息查看

1.1 单个表的全局统计信息、统计效果查看

SQL> select COLUMN_NAME,NUM_DISTINCT,DENSITY,SAMPLE_SIZE,AVG_COL_LEN from DBA_TAB_COL_STATISTICS where table_name='TB1';

 

SQL> select GLOBAL_STATS from dba_tables where table_name='TB1';

如果是NO，则马上要分析统计信息。

 

查看某表最后一次统计信息收集时间：

SQL> select to_char(LAST_ANALYZED,'fmYYYY-MM-DD HH24:MI:SS') from dba_tables where WNER='PLATFORM_DEV' and TABLE_NAME='T_USER';

查看某索引最后一次统计信息收集时间：查dba_indexes、user_indexes的LAST_ANALYZED.

2.   统计信息分析（收集）

2.1 分析工具选择

dbms_stats可以并行分析

dbms_stats有自动分析的功能(alter table monitor )

 

analyze 分析统计信息，不准确----some times

 

 

2.2 分析前做index重建

按表重建索引：

SQL> select 'alter index platform_dev.'||INDEX_NAME||' rebuild online;' from dba_indexes where wner='PLATFORM_DEV' and TABLE_NAME='T_SSO_SESSION';

按用户(schema)重建索引：

SQL> select 'alter index platform_dev.'||INDEX_NAME||' rebuild online;' from dba_indexes where wner='PLATFORM_DEV';

 

 

2.3 分析某数据表，可以在PL/SQL的command window下执行的

缺省方式----最常用的方式：

SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS(

OWNNAME => 'LK',

TABNAME => 'TB1' ,

ESTIMATE_PERCENT => NULL ,

METHOD_OPT => 'FOR ALL INDEXED COLUMNS' ,

CASCADE => TRUE);

------整表全分析(全粒度)，注意，必加cascade=true，否则相关索引可能不统计。

 

 

 

 

SQL> begin

    DBMS_STATS.GATHER_TABLE_STATS(ownname => 'PLATFORM_DEV',tabname => 'T_OPERATION_LOG', CASCADE => TRUE);

    end;

    /

 

2.4 分析SCHEMA，在SQLPLUS中进行的

PL/SQL:

begin

dbms_stats.gather_schema_stats(ownname =>'PLATFORM_DEV',estimate_percent => 5,cascade=>true,degree=>5);

end;

/

SQLPLUS:

----资源耗用大，只能在服务器本机sqlplus中执行。

SQL> exec dbms_stats.gather_schema_stats(ownname =>'LK',estimate_percent => 5,cascade=>true,degree=>5);

 

2.5 dbms_stats.gather_schema_stats详解

estimate_percent选项：允许Oracle的dbms_stats在收集统计数据时，自动估计要采样的一个segment的最佳百分比：
estimate_percent => dbms_stats.auto_sample_size   ---这是它的缺省值

estimate_percent=>100         ---完全统计

 

cascade选项：递归统计，统计相关表对应的索引

cascade=> TRUE

 

degree选项:决定并行度.默认值为null

 

options参数

options=>"gather"——重新分析整个架构（Schema）。

options=>"gather empty"  -----收集没有分析过的表的统计信息

options=>"gather stale"   ----重新分析修改量超过10%的表（即增、删、改）。

options=>"gather auto"    ----重新分析当前没有统计的对象，以及统计数据过期（变脏）的对象。注意，使用gather auto类似于组合使用gather stale和gather empty。

 

method_opt参数

适合在表和索引数据发生变化时刷新统计数据，也适合用于判断哪些列需要直方图（histograms）。

method_opt=>'for all columns size skewonly'

method_opt=>'for all columns size repeat'     ----定期重新分析统计数据时，使用它，重新分析任务所消耗的资源就会少一些。它只会为现有的直方图重新分析索引，不再搜索其他直方图机会。-----触发了BUG！别用此项！

method_opt=>'for all columns size auto'     -----ORACLE公司推荐设置值

 

2.6 初始化参数statistics_level与oracle默认统计信息收集JOB的关系

Ø         Sys用户，找到默认统计信息收集JOB：

select  OWNER,PROGRAM_NAME,job_action,job_name, state from Dba_Scheduler_Jobs where JOB_NAME ='GATHER_STATS_JOB'

Ø         初始化参数statistics_level三种状态（BASIC、TYPICAL或ALL）

statistics_level=basic时,oracle关闭默认统计信息收集JOB中所有性能数据的收集,也即若要关闭AWR或statspack收集,只要设置alter system set statistics_level=basic;就行了。

statistics_level=typical的时候,除了plan_executetion_statistics和OS Statistics不能收集外,其他的都可以收集,如要要收集这个两项,必须设置statistics_level=all;

 

3.   改良、辅助oracle默认统计信息收集JOB

3.1 创建新的统计信息收集PROC、JOB，每周末执行一次

/可选的方法---禁用默认的统计信息收集JOB，但不提倡

Sys用户，将默认统计信息收集JOB禁用：
SQL> BEGIN
   DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
   END;        ----再次启用就用DBMS_SCHEDULER.ENABLE命令
复查是否真已被禁用：

SQL> SELECT OWNER,JOB_NAME,ENABLED FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';      ---- 此时ENABLED应为FALSE

/可选的方法---禁用默认的统计信息收集JOB，但不提倡，结束

 

Ø         Sys用户创建周末统计信息收集存储过程

create or replace procedure PROC_STATISTICS_GATHER_WEEKEND  is
begin
---gather statistics of schema:LK
    dbms_stats.gather_schema_stats(ownname =>'LK',

estimate_percent => 30,

cascade=>true,degree=>5);   

----repeat above sentence for other schema.
    end;

----参数method_opt=>'for all columns size repeat'是否有问题 ，不显式指定而用默认值最好。

Ø         Sys用户创建周末统计信息收集JOB

begin
    dbms_scheduler.create_job (
    job_name => 'JOB_STATISTICS_GATHER_WEEKEND',
    job_type => 'STORED_PROCEDURE',
    job_action => 'SYS.PROC_STATISTICS_GATHER_WEEKEND',
    start_date => to_date('2010-08-22 04:00:00','fmYYYY-MM-DD HH24:MI:SS'),
    repeat_interval => 'FREQ=DAILY;INTERVAL=7',
    comments => 'author: liukan');
end;

----注：每7天一次，第一次是2010-08-22 04:00:00，

Ø         Sys开启ENABLE上面的JOB

begin
    dbms_scheduler.enable('JOB_STATISTICS_GATHER_WEEKEND');
end;

Ø         Sys手动试运行上面的JOB

begin
    dbms_scheduler.run_job(job_name => 'JOB_STATISTICS_GATHER_WEEKEND',
    use_current_session => TRUE);
 end;

3.2 查看新建的统计信息收集JOB的状态

Sys用户：

select job_name,job_action,state,to_char(START_DATE,'fmYYYY-MM-DD HH24:MI:SS') START_DATE, to_char(NEXT_RUN_DATE,'fmYYYY-MM-DD HH24:MI:SS') NEXT_RUN_DATE from user_scheduler_jobs;

3.3 批量生成收信统计信息(按table)的SQL

select 'exec DBMS_STATS.GATHER_TABLE_STATS(ownname => '||chr(39)||'PLATFORM_DEV'||chr(39)||',tabname => '||chr(39)||table_name||chr(39)||',CASCADE => TRUE,degree => 5);'  from user_tables;

 

4.   最有力度的统计信息收集命令(失败与成功纪实)

4.1 失败的统计信息收集命令

create or replace procedure PROC_STATISTICS_GATHER_WEEKEND  is
begin
    dbms_stats.gather_schema_stats(ownname =>'PLATFORM_DEV',

estimate_percent => 50,

method_opt=>'for all columns size repeat',

cascade=>true,degree=>5);

 end;

----它的执行导致友商网主站库最最核心的（用户登录）SQL无法走最佳执行计划。

4.2尝试的命令，各命令都改变不了核心SQL的执行计划

命令1：

exec dbms_stats.gather_schema_stats(ownname =>'PLATFORM_DEV',estimate_percent => 30,cascade=>true,degree=>5);

命令2：

exec dbms_stats.GATHER_SCHEMA_STATS(ownname=>'PLATFORM_DEV',estimate_percent => 30,method_opt=>'for all indexed columns',options=>'GATHER',cascade=>TRUE);

命令3：

exec dbms_stats.gather_schema_stats(ownname => 'PLATFORM_DEV', estimate_percent => 30, method_opt => 'for all columns size skewonly', cascade=>true,degree => 5);

命令4：

exec dbms_stats.gather_schema_stats(ownname => 'PLATFORM_DEV', estimate_percent => 30, method_opt => 'for all columns size skewonly', cascade=>true,degree => 5);

这些命令都没用，在重建完所有索引后，再执行这些语句也没用，核心的（用户登录）SQL还是无法走最佳执行计划。

4.3按表收集统计信息：改变了核心SQL的执行计划---OK

exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'PLATFORM_DEV',tabname => 'T_USER', CASCADE => TRUE,degree => 5);

exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'PLATFORM_DEV',tabname => 'T_CORPORATION_USER', CASCADE => TRUE,degree => 5);

……

核心的（用户登录）SQL走了最佳执行计划!

4.4统计信息收集实战的重要结论（oracle公司技术人员推荐）

dbms_stats.gather_schema_stats可能不好用，还不如：

1.      每三个月或每半年（根据数据变更的频繁度决定）利用例行更新停机时间，整理主要表的存储结构(move tablespace可同一tbs)、重建所有索引(rebuild无online选项)，在此基础上手工按schema收集统计信息，然后，密切跟踪数据库性能。+

2.       平时的统计信息收集由oracle自带的自动统计信息收集机制完成+

3.       发现慢SQL，可重点考虑重建与慢SQL相关的表的索引、重新收集相关表的统计信息（DBMS_STATS.GATHER_TABLE_STATS）+

/

select 'exec DBMS_STATS.GATHER_TABLE_STATS(ownname => ' || chr(39) ||
       'PLATFORM_DEV' || chr(39) || ',tabname => ' || chr(39) || table_name ||
       chr(39) || ',estimate_percent =>30,CASCADE => TRUE,degree => 5);'
  from user_tables t where t.table_name not like '%$%'; 

4.      最核心、最频繁执行、且并非程序复杂拼装的SQL，应加HINT，以强制其执行计划。

5.       重要、咽喉的、核心的SQL以存储过程或视图形式存于oracle数据库中，以方便快速SQL调优、故障解决。

 

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

转载于:http://blog.itpub.net/15119715/viewspace-677120/