管理Oracle统计信息

secce

在CBO(基于代价的优化器模式)条件下，SQL语句的执行计划由统计信息来决定，若没有统计信息则会采取动态采样的方式决定执行计划！可以说统计信息关乎sql的执行计划是否正确，属于sql执行的指导思想，oracle的初始化参数statistics_level控制收集统计信息的级别，有三个参数值:BASIC :收集基本的统计信息
TYPICAL：收集大部分统计信息(数据库的默认设置)
ALL：收集全部统计信息

统计信息包含：
行统计信息(user_tables)：行数(NUM_ROWS)，块数(BLOCKS)，行平均长度(AVG_ROW_LEN)；
列统计信息(user_tab_columns)：列中唯一值的数量（NUM_DISTINCT），NULL值的数量(NUM_NULLS)，数据分布(HISTOGRAM)；         
索引统计(user_index)：--叶块数量(LEAF_BLOCKS)，等级(BLEVEL)，聚簇因子(CLUSTERING_FACTOR)；

统计信息的收集时间由时间窗口来调度！


一：10g关闭自动收集统计信息任务

• begin
• DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
• end;
  

二：11g关闭自动收集统计信息任务

• BEGIN
• DBMS_AUTO_TASK_ADMIN.DISABLE(
• client_name => 'auto optimizer stats collection',
• operation => NULL,
• window_name => NULL);
• END;
  

查询

• SQL> select client_name ,status from dba_autotask_client;
• CLIENT_NAME                                                      STATUS
• ---------------------------------------------------------------- --------
• auto optimizer stats collection                                  DISABLED
• auto space advisor                                               ENABLED
• sql tuning advisor                                               ENABLED
  

三：手动收集统计信息，采样10%，并行度为8，METHOD_OPT选项代表收集index列分布情况，并生成直方图

• BEGIN
• dbms_stats.gather_schema_stats(
• ownname=>'HR',
• METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE SKEWONLY',
• CASCADE=>TRUE,
• ESTIMATE_PERCENT=>10,
• DEGREE=>8);
• END;
  

四：查看表或索引的统计信息

• select table_name,
•        last_analyzed,
•        num_rows,
•        avg_row_len,
•        row_movement，
•        monitoring
• from dba_tables
• where owner = 'HR';
• select index_name,
•        table_name,
•        blevel,
•        status,
•        clustering_factor,
•        last_analyzed,
•        num_rows,
•        leaf_blocks,
•        distinct_keys,
•        avg_leaf_blocks_per_key
• from dba_indexes
• where owner = 'HR';
  

五：导出统计信息

• begin
•   dbms_stats.create_stat_table(ownname => 'HR',
•   stattab => 'HR_STAT_BAK');
• end;
• begin
•   dbms_stats.export_schema_stats(ownname => 'HR',
•   stattab => 'HR_STAT_BAK',
•   statid => 'N1',
•   statown => 'HR');
• end;
• select * from HR_STAT_BAK；
  

六：删除统计信息

• begin
•   dbms_stats.delete_schema_stats(ownname => 'HR');
• end;
  

七：导入统计信息

• begin
•   dbms_stats.import_schema_stats(ownname => 'HR',
•   stattab => 'HR_STAT_BAK',
•   statid => 'N1',
•   statown => 'HR');
• end;
  

八：异机导入，首先需要将HR_STAT_BAK表通过expdp/impdp方式导入到其他oracle服务器上

• SQL> exec dbms_stats.upgrade_stat_table('HR','HR_STAT_BAK');
• PL/SQL 过程已成功完成。
• SQL> EXEC dbms_stats.import_schema_stats(ownname => 'HR',stattab => 'HR_STAT_BAK',statid => 'N1',statown=>'HR');
• PL/SQL 过程已成功完成。
  

九：锁定与解锁统计信息

• 1：锁定统计信息
• begin
• dbms_stats.lock_schema_stats(ownname=>'HR');
• end;
• 2：查询哪些表或索引的统计信息被锁定
• select * from user_tab_statistics where stattype_locked='ALL';
• select * from user_ind_statistics where stattype_locked='ALL';
• 3：如果在锁定条件下收集统计信息，则会出现如下报错
• ERROR at line 1:
• ORA-20005: object statistics are locked (stattype = ALL)
• ORA-06512: at "SYS.DBMS_STATS", line 20337
• ORA-06512: at "SYS.DBMS_STATS", line 20360
• ORA-06512: at line 1
• 4：解锁统计信息
• begin
• dbms_stats.lock_schema_stats(ownname=>'HR');
• end;
  

0

吃饭与ml是第一生产力。

shilang

如果没有毛片，中国的性教育简直就是一片空白！

xuxiaohui9216

我不在江湖,但江湖中有我的传说。

bgey

我喜欢孩子，更喜欢造孩子的过程!

偷瓜的贼

内练一口气，外练一口屁。

515439429

有竞争才有进步嘛