> 节选自《基于Oracle的SQL优化》第五章 ``` --收集统计信息 --估算模式，采样比例15% analyze table t2 estimate statistics sample 15 percent for table; --计算模式,只收集表的 analyze table t2 compute statistics for table; --收集所有 analyze table t2 compute statistics; --删除 analyze index idx_t2 delete statistics; --针对某列 analyze table t2 compute statistics for columns object_name,object_id; --一次性收集所有 exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T2',estimate_percent=>100,cascade=>true); --分析行迁移，分析索引 analyze table xxx list chained rows into yyy; analyze index xxx validate structure; ``` ###### ANALYZE和DBMS_STATS区别 1. analyze命令不能收集分区表统计信息 2. analyze不能并行收集统计信息 3. dbms_stats只能收集与cbo有关的统计信息，其他如行迁移、行链接可以通过analyze分析。 ##### 查看统计信息 > mos:3112.1 or http://www.dbsnake.net/books --sosi.txt #### 表的统计信息 ``` --查看历史收集的统计信息 select savtime,rowcnt,blkcnt,avgrln,analyzetime from sys.wri$_optstat_tab_history where obj#=224099; --truncate 操作后，oracle会自动收集统计信息 ``` #### 索引的统计信息 ``` --降低索引层次 alter index idx_t1 rebuild; analyze index idx_t1 validate structure; select name,height,lf_rows,lf_blks,del_lf_rows from index_stats; ``` ##### 聚簇引子的含义及重要性 ``` --查看表位置，文件号，block号 select id,dbms_rowid.rowid_relative_fno(rowid||'_'||dbms_rowid.rowid_block_number(rowid) location from t1 order by location,id; --手动调整索引的集簇引子 exec dbms_stats.set_index_stats(ownname='IPRA',indname=>'IDX_T1',clstfct => 100000,no_invalidate=>false); ``` #### 列的统计信息 > 列统计信息实际存放在 hist_head$中，包含了列的distinct值的数量，null数量、列最大值、最小值等，dba_tab_col_statistics可查 ``` --查看绑定变量的值 select snap_id, dbms_sqltune.extract_bind(bind_data,1),value_string bind1, dbms_sqltune.extract_bind(bind_data,2),value_string bind2, dbms_sqltune.extract_bind(bind_data,3),value_string bind3, dbms_sqltune.extract_bind(bind_data,4),value_string bind4 from dba_hist_sqlstat where sql_id='' order by snap_id; --检查列统计信息 select low_value,high_value from dba_tab_col_statistics where table_name='' and column_name=''; --换算成时间 var temp date; exec dbms_stats.convert_raw_value('LOW_VALUE',:temp); --谓词越界，超出了列统计信息中的范围。最大值 最小值 ``` #### 直方图 > 在Oracle中，CBO默认认为目标列的数据在其最小值LOW_VALUE和最大值HIGH_VALUE之间是均匀分布的。 并按照此原则计算成本，基数等。 > 直方图存在HISTGRM$表中，dba_tab_histograms ##### 直方图的类型 ###### Frequency类型直方图 > 目标列直方图的Bucket的数量等于目标列distinct值的数量。 ENDPOINT_VALUE记录了这些distinct值，ENDPOINT_NUMBER则记录了到此distinct为止总共有多少记录，该值为累加记录。 > Frequency类型直方图所对应的Bucket数量不能超过254.Oracle12c不再限制，只适用于那些目标列的distinct值数量小于或等于254的情形。 ``` --收集直方图信息，只有用过的列才会被收集 --eg auto自动决定使用哪种类型直方图 select * from t where x='x'; exec dbms_stats.gather_table_stats(ownname='MYTEST',tabname=>'T',method_opt=>'for columns size auto X',cascade=>true); --可以通过以下视图检查 select object_id from dba_objects where object_name='X'; select name,intcol# from sys.col$ where obj#=; select obj#,intcol#,equality_preds from sys.col_usage$ where obj#=; select table_name,column_name,num_distinct,density,num_buckets,histogram from dba_tab_col_statistics where table_name=''; select table_name,column_name,endpoint_number,endpoint_value from dba_tab_histograms where table_name=''; ``` > 如果针对文本类型收集直方图，Oracle只会将该文本类型字段的文本值的头32个字节给取出来（实际上15个字节），将其转化为一个浮点数。 ###### Height Balanced 类型直方图 > 目标列直方图的Bucket的数量会小于目标列的distinct值的数量。 ``` --收集直方图，指定5个Bucket exec dbms_stats.gather_table_stats(ownname=>'MYTEST',tabname=>'T',method_opt=>'for columns size 5 x',cascade=>true,estimate_percent=>100); ``` ##### 直方图的收集方法 ``` --所有有索引列收集直方图 exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP',method_opt=>'for all indexed columns size auto'); --SIZE {integer |REPEAT |AUTO |SKEWONLY} --只删除empno的直方图信息 integer:1-254,1表示删除 exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP',method_opt=>'for columns empno size 1'); ``` ##### 直方图对CBO的影响 > 当某个列有了直方图统计信息后，cbo会人为对该目标列施加的等值查询条件是一个不安全的为此条件。尤其cursor_sharing设置为similar后。 产生更多硬解析。 shared Cursor影响比较大。 > 再个就是对可选择率的影响。dba_tab_col_statistics DENSITY的值可以卡看做是对目标列施加等值查询条件后的可选择率，在没有直方图统计信息时，值等于1/num_distinct. ##### 使用直方图注意事项 1. 直方图是为了准确评估分布不均匀的列可选择率、结果的Cardinality被引入的，如果目标列分布均匀，不需要收集直方图统计信息。 2. 对于那些从来没有在sql语句where条件中出现的列，无需收集 3. 直方图统计信息可能会影响Shared Cursor的共享，特别是有绑定变量联用时。 4. 在配置Oracle 10g引入的自动统计信息收集作业时，需要特别注意对直方图统计信息的收集策略。 #### 全局统计信息 > 是只从直接对象本身这一级收集的统计信息，只能通过dbms_stats包来收集 - 应使用DBMS_STATS包来对分区表收集全局统计信息 - 应用 DBMS_STATS包中的相关存储过程来删除分区表统计信息 - 收集分区表统计信息时应一致使用GRANULARITY参数。 - 尽量不要使用analyze命令来对分区表收集统计信息，尤其不要与DBMS_STATS联合使用。 #### 动态采样 > 为了更准确地估算where条件出现有关联关系的列时整个where条件的组合可选择率，进而更准确的估算返回结果集Cardinality，Oracle引入了动态采样。 两方面作用 - 估算准确的选择率 - 可一定程度上解决这种因临时表没有统计信息而导致的CBO选错执行计划的问题。 > 动态采样仅适用于那些针对单表或多表关联的第一个驱动表的select、update和delete语句。 动态采样的值(功能默认开启,optimizer_dynamic_sampling) - 0:不启用 - 1：对没有统计信息且没有索引、数据块总数大于默认采样数据块数量 - 2：对没有统计信息的表启用，使用默认采样数据块数量 - 3：对没有统计信息和有但cbo无法准确估算启用，默认块数 - 4：除了满足3，再加那些针对单表且查询条件中至少出现了两列的表启用。默认块数 - 5：满足4，且采样块数是2倍默认数 - 6：满足5, 且采样块数是4倍默认数 - 7：满足6，且采样块数是8倍默认数 - 8：满足7，且采样块数是32倍默认数 - 9: 满足8，且采样块数是128倍默认数 - 10：满足9，采样时使用所有目标表数据块。 > 默认采样块数为32块，隐含参数"_OPTIMIZER_DYN_SMP_BLKS"。 hint方式：/*+ dynamic_sampling(t2,4) */ #### 多列统计信息 > 可以人为指定存在关联关系的一组目标表上的一个组合列，然后使用DBMS_STATS针对组合列收集统计信息。 ``` select * from t2 where n1=3 and n2=3 and c1='a'; --创建组合列 set serveroutput on declare cg_name varchar2(30); begin cg_name := sys.dbms_stats.create_extended_stats('SOCTT','T2','(n1,n2)'); dbms_output.put_line(cg_name); end; / --收集统计信息 exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T2',method_opt=>'for columns(n1,n2) size auto',estimate_percent => 100); ``` #### 系统统计信息 > 用来描述Oracle数据库所在服务器的系统处理能力的辅助统计信息，主要包括cpu主频、单块读的平均耗费时间、多块读的平均耗费时间等。 收集方法 1. 执行DBMS_STATS.GATHER_SYSTEM_STATS('start')开始收集 2. 接着开始实际地使用系统，最好是能以相同典型的负载实际使用一段时间 3. 最后执行DBMS_STATS.GATHER_SYSTEM_STATS('stop'). > 系统统计信息会存在于视图 aux_stats$ #### 数据字典统计信息 ``` exec dbms_stats.gather_dictionary_stats; ``` #### 内部对象统计信息 > x$ 相关表,可通过执行计划查看访问了哪些内部表. 内存结构，如果收集统计信息，rac每个节点都需要执行。 ``` exec dbms_stats.gather_fixed_objects_stats; ``` #### Oracle里的自动统计信息收集 ``` --oracle10g gather_stats_job select program_action from dba_scheduler_programs where program_name='GATHER_STATS_PROG'; select * from dba_scheduler_wingroup_members where window_group_name='MAINTENANCE_WINDOW_GROUP'; --查询运行时间 select window_name,repeat_interval,duration from dba_scheduler_windows where window_name in () and enabled='TRUE'; --查看是否自愿控制，如果没有，会不限制的消耗资源 select window_name,resource_plan from dba_scheduler_windows; --查看实际执行情况 select actual_start_date,job_name,status from (select *from dba_scheduler_job_run_details where job_name='GATHER_STATS_JOB' order by log_date desc) where rownum<=4; ``` 缺点： 1. 维护窗口太少，不够灵活 2. 没有施加资源控制，过度消耗资源影响数据库运行 ``` --Oracle11g add:GATHER_STATS_PROG select client_name,task_name,operation_name,status from dba_autotask_task; --维护窗口 select window_name,autotask_status from dba_autotask_window_clients; --运行时间 select window_name,repeat_interval,duration from dba_scheduler_windows where enabled='TRUE'; --查看是否资源限制 select window_name,resource_plan from dba_scheduler_windows; --查看历史执行信息 select * from dba_autotask_job_history where client_name='auto optimizer stats collection' order by window_start_time desc; --可以看 创建脚本 $?/rdbms/admin/catmwin.sql --自上次自动统计信息收集后，mon_mods_all$记录了dml操作的记录数是否超过目标表在tab$总记录数的10% select obj#,inserts,updates,deletes,flags from sys.mon_mods_all$ where obj#=; --手动监控 exec dbms_stats.flush_database_monitoring_info(); ``` #### 禁用 ``` --10g all exec dbms_scheduler.disable('GATHER_STATS_JOB'); --11G ALL exec dbms_auto_task_admin.disable( client_name=>'auto optimizer stats collection', operation=> NULL, window_name=>NULL); --10g one of all begin dbms_scheduler.disable( name=>'WEEKNIGHT_WINDOW', force=>TRUE); end; / --11g one of all begin dbms_scheduler.disable( name=>'MONDAY_WINDOW', force=TRUE); end; / --oracle10g 默认收集直方图，修改为止收集之前有直方图信息的表 select dbms_stats.get_param('METHOD_OPT') from dual; exec dbms_stats.set_param('METHOD_OPT','FOR ALL COLUMNS SIZE REPEAT'); --oracle11g exec dbms_stats.set_global_prefs('METHOD_OPT','FOR ALL COLUMNS SIZE REPEAT'); ``` #### Oracle里应如何收集统计信息 - 建议额外收集下系统统计信息 - 系统有大量数据插入、删除，及时收集 - 新上线系统及时收集 - 日期字段及时收集统计信息，避免为此越界