第八课分析和动态采样

1、性能优化 第八课-数据分析2012.11.15法律【】和幻灯片为炼数成金网络课程的教学资料，所有资料只能在课程内使用，不得在课程以外范围散播，违者将可能被责任。法律和经济课程详情炼数成金培训http:2012.11.122分析CBO的数据来源CBO是一个数学模型需要准确的传入数据通过精确的数据计算出精确的执行计划2012.11.152013-1-12分析的最终目的让CBO理解数据！2012.11.152013-1-12-当没有分析数据时2012.11.152013-1-12-当没有分析数据时Cardinality = num_of_blocks * (block_size - cache_la

2、yer) / avg_row_len2012.11.152013-1-12-当分析信息不充足时2012.11.152013-1-12-当有足够的分析数据时2012.11.152013-1-12CBO的数据来源初始化参数优化参数CPU数据块大小多块读的大小.数据字典user_tables,user_tab_partitionsuser_indexes,user_ind_partitionsuser_tab_col_s.istics2012.11.152013-1-12DBMS_SS包和yze命令yze命令已经过时无法提供灵活的分析选项无法提供并行的分析无法对分析数据进行管理DBMS_SS专门为C

3、BO提供信息来源可以进行数据分析的多种组合可以对分区进行分析可以进行分析数据管理备份，恢复，删除，设置.2012.11.152013-1-12Oracle的自动信息收集Oracle11g的一个默认设置user_tab_modification表的修改当分析对象的数据修改超过10%时，Oracle会重新分析。定时任务GATHER_SS_JOB负责重新定时收集过旧数据的信息。2012.11.152013-1-12Oracle的自动信息收集2012.11.152013-1-12Oracle的自动信息收集2012.11.152013-1-12是否要完全依赖自动分析？当数据执行计划保持不错的时候，可以依

4、赖自动分析。比如，OLTP系统。否则，需要手工介入。比如，OLAP系统没有一个适合所有系统的数据分析方法2012.11.152013-1-12DBMS_SS包http/cd/E11882_01/appdev.112/e25788/d_ss.htm#i10455182012.11.152013-1-12表数据的收集DBMS_Sownname tabname partname estim block_s method_opt1,degreeHER_TABLE_SS (VARCHAR2,VARCHAR2,VARCHAR2NUMBERDEFAULT DEFAULT DEFAULTDEFAULTNULL

5、, NULL, FALSE,FOR ALL COLUMNSrcentleVARCHAR2SIZENUMBERVARCHAR2DEFAULT DEFAULT DEFAULT DEFAULT DEFAULTDEFAULTNULL, DEFAULT, FALSE, NULL, NULL, NULL,FALSE);granularitycascadess stabid ownVARCHAR2VARCHAR2 VARCHAR2no_invalidateDEFAULT2012.11.152013-1-12索引数据的收集DBMS_Sownname indnamepartnameS.GATHER_INDEX_

6、SVARCHAR2,VARCHAR2,S (VARCHAR2 DEFAULT NULL,estimate_percent NUMBERDEFAULT to_estimate_percent_type(GET_PARAM(ESTIMATE_PERCENT),ssstabidownVARCHAR2 DEFAULT NULL,VARCHAR2 DEFAULT NULL, VARCHAR2 DEFAULT NULL,NUMBERDEFAULT to_degree_type(get_param(DEGREE),VARCHAR2 DEFAULT GET_PARAM(GRANULARITY),degreeg

7、ranularityno_invalidateDEFAULT to_no_invalidate_type(GET_PARAM(NO_INVALIDATE),DEFAULT FALSE);force2012.11.152013-1-12数据分析示例2012.11.152013-1-12表（索引）分析中几个重要的参数ese_percentDBMS_SS.AUTO_SLE_SIZE手工设置（范围0.000001,100）超大表大表小表2012.11.152013-1-12gather_table_ss几个重要的参数granularity 数据分析的力度globalpartitionsubpartit

8、ion2012.11.152013-1-12全局分析和分区分析2013-1-12全局分析和分区分析使用分区统计信息使用全局统计信息使用全局统计信息2012.11.152013-1-12全局分析和分区分析当表上已经有全局统计信息时，单独对分区分析，不会更新全局信息。2012.11.152013-1-12全局分析和分区分析当表上没有全局统计信息时，单独对分区分析，会更新全局信息（合并分区信息-11g2012.11.152013-1-12全区和全局信息增量统计（Oracle11g)Oracle会增量的收集分区信息来更新全局信息2012.11.152013-1-12分区和全局信息结论：如何设置这个参数

9、在一个很大的分区表（OLAP)，全局分析代价是非常昂贵的。OLAP系统下，除了新加入的数据外，旧的数据基本上是没有变化的，全局分析很浪费资源。对于很大的分区表，将granulariy设置为partition（Oracle10g）或者incremental（ Oracle11g）是很有意义的。对于不大的分区表，可以使用默认设置。2012.11.152013-1-12gather_table_ss几个重要的参数method_opt 分析直方图选项2012.11.152013-1-12直方图概念-Oracle对列上的数据分布进行统计分析，对数据倾斜分布时很有用。CBO的数据来源2012.11.152

10、013-1-12表未分析数据块，默认值，动态采样表已分析（未生成直方图）num_rows,NDV，BLOCKS.表已分析（生成直方图）列上的数据分布？列的相关性直方图Frequency- 频率直方图Height-Balanced- 高度平衡直方图2012.11.152013-1-12直方图示例- HEIGHT BALANCED2012.11.152013-1-12直方图示例-FREQUENCY2012.11.152013-1-12直方图示例重回最初的示例。2012.11.152013-1-12GATHER_TABLE_SS.METHOD_OPTfor all columns:统计所有列的his

11、tograms.for all indexed columns:统计所有indexed列的histograms.for all hidden columns:统计你看不到列的histogramsfor columns SIZE | REPEAT | AUTO | SKEWONLY:N的取值范围1,254;REPEAT上次统计过的histograms;AUTO由oracle决定N的大小;SKEWONLY -size skewonly 只收集非均匀分布的直方图，系统自动决定桶数(bucket )2012.11.152013-1-12DBMS_SS-Extended Sistics2012.11.1

12、52013-1-12DBMS_SS-Extended Sistics2012.11.152013-1-12DBMS_SS-Extended Sistics2012.11.152013-1-12动态采样当表上没有分析信息时，Oracle会使用动态采样技术。2012.11.152013-1-12动态采样的级别不同的级别，采样的数据块数量不同level1-10，采样数据量逐级递增。level10 对所有数据进行采样分析2012.11.152013-1-12动态采样V.S.DBMS_SS观点动态采样只能作为一种辅助。对于海量数据，动态采样的数据块太少，无法准确的反映数据的真实情况；如果采样率高，会直接影响SQL的执行效率。DBMS_S分析比例S可以非常灵活的进行数据分析配置分析时间直方图分析数据的管理. .2012.11.152013-1-12炼数成金逆向式网络课程Dataguru（炼数成金）是专业数据分析，提供教育，内容，社区，数据分析业务等服务。的课程采用新兴的互联网教育形式，独