Oracle性能优化08 - 数据分析


DATAGURU专业数据分析网站 2012.11.15 性能优化 第八课--数据分析 2DATAGURU专业数据分析网站 2012.11.12 法律声明 【声明】本视频和幻灯片为炼数成金网络课程的教 学资料,所有资料只能在课程内使用,不得在课 程以外范围散播,违者将可能被追究法律和经济 责任。 课程详情访问炼数成金培训网站 http://edu.dataguru.cn 2013- 1-12 DATAGURU专业数据分析网站 2012.11.15 分析----CBO的数据来源 � CBO是一个数学模型 � 需要准确的传入数据 � 通过精确的数据计算出精确的执行计划 2013- 1-12 DATAGURU专业数据分析网站 2012.11.15 分析的最终目的 让CBO理解数据! 2013- 1-12 DATAGURU专业数据分析网站 2012.11.15 引子--当没有分析数据时 2013- 1-12 DATAGURU专业数据分析网站 2012.11.15 引子--当没有分析数据时 Cardinality = num_of_blocks * (block_size - cache_layer) / avg_row_len 2013- 1-12 DATAGURU专业数据分析网站 2012.11.15 引子--当分析信息不充足时 2013- 1-12 DATAGURU专业数据分析网站 2012.11.15 引子--当有足够的分析数据时 2013- 1-12 DATAGURU专业数据分析网站 2012.11.15 CBO的数据来源 � 初始化参数 – 优化参数 –CPU – 数据块大小 – 多块读的大小 ...... � 数据字典 – user_tables,user_tab_partitions – user_indexes,user_ind_partitions – user_tab_col_statistics ...... 2013- 1-12 DATAGURU专业数据分析网站 2012.11.15 DBMS_STATS包和analyze命令 � analyze命令已经过时 – 无法提供灵活的分析选项 – 无法提供并行的分析 – 无法对分析数据进行管理 � DBMS_STATS – 专门为CBO提供信息来源 – 可以进行数据分析的多种组合 – 可以对分区进行分析 – 可以进行分析数据管理 • 备份,恢复,删除,设置.... 2013- 1-12 DATAGURU专业数据分析网站 2012.11.15 Oracle的自动信息收集 � Oracle11g的一个默认设置 � user_tab_modification跟踪表的修改 � 当分析对象的数据修改超过10%时,Oracle会重新分析。 � 定时任务GATHER_STATS_JOB负责重新定时收集过旧数据的信息。 2013- 1-12 DATAGURU专业数据分析网站 2012.11.15 Oracle的自动信息收集 2013- 1-12 DATAGURU专业数据分析网站 2012.11.15 Oracle的自动信息收集 2013- 1-12 DATAGURU专业数据分析网站 2012.11.15 是否要完全依赖自动分析? �当数据执行计划保持不错的时候,可以依赖自动分析。 – 比如,OLTP系统。 �否则,需要手工介入。 – 比如,OLAP系统 �没有一个适合所有系统的数据分析方法 2013- 1-12 DATAGURU专业数据分析网站 2012.11.15 DBMS_STATS包 http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_stats.htm#i1045518 2013- 1-12 DATAGURU专业数据分析网站 2012.11.15 表数据的收集 DBMS_STATS.GATHER_TABLE_STATS ( DBMS_STATS.GATHER_TABLE_STATS ( DBMS_STATS.GATHER_TABLE_STATS ( DBMS_STATS.GATHER_TABLE_STATS ( ownname VARCHAR2, ownname VARCHAR2, ownname VARCHAR2, ownname VARCHAR2, tabname VARCHAR2, tabname VARCHAR2, tabname VARCHAR2, tabname VARCHAR2, partname VARCHAR2 DEFAULT NULL, partname VARCHAR2 DEFAULT NULL, partname VARCHAR2 DEFAULT NULL, partname VARCHAR2 DEFAULT NULL, estimate_percent NUMBER DEFAULT NULL, estimate_percent NUMBER DEFAULT NULL, estimate_percent NUMBER DEFAULT NULL, estimate_percent NUMBER DEFAULT NULL, block_sample BOOLEAN DEFAULT FALSE, block_sample BOOLEAN DEFAULT FALSE, block_sample BOOLEAN DEFAULT FALSE, block_sample BOOLEAN DEFAULT FALSE, method_opt VARCHAR2 DEFAULT 'FOR ALL COLUMNS SIZE method_opt VARCHAR2 DEFAULT 'FOR ALL COLUMNS SIZE method_opt VARCHAR2 DEFAULT 'FOR ALL COLUMNS SIZE method_opt VARCHAR2 DEFAULT 'FOR ALL COLUMNS SIZE 1', 1', 1', 1', degree NUMBER DEFAULT NULL, degree NUMBER DEFAULT NULL, degree NUMBER DEFAULT NULL, degree NUMBER DEFAULT NULL, granularity VARCHAR2 DEFAULT 'DEFAULT', granularity VARCHAR2 DEFAULT 'DEFAULT', granularity VARCHAR2 DEFAULT 'DEFAULT', granularity VARCHAR2 DEFAULT 'DEFAULT', cascade BOOLEAN DEFAULT FALSE, cascade BOOLEAN DEFAULT FALSE, cascade BOOLEAN DEFAULT FALSE, cascade BOOLEAN DEFAULT FALSE, stattab VARCHAR2 DEFAULT NULL, stattab VARCHAR2 DEFAULT NULL, stattab VARCHAR2 DEFAULT NULL, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT FALSE); no_invalidate BOOLEAN DEFAULT FALSE); no_invalidate BOOLEAN DEFAULT FALSE); no_invalidate BOOLEAN DEFAULT FALSE); 2013- 1-12 DATAGURU专业数据分析网站 2012.11.15 索引数据的收集 DBMS_STATS.GATHER_INDEX_STATS ( ownname VARCHAR2, indname VARCHAR2, partname VARCHAR2 DEFAULT NULL, estimate_percent NUMBER DEFAULT to_estimate_percent_type (GET_PARAM('ESTIMATE_PERCENT')), stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')), granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'), no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (GET_PARAM('NO_INVALIDATE')), force BOOLEAN DEFAULT FALSE); 2013- 1-12 DATAGURU专业数据分析网站 2012.11.15 数据分析示例 2013- 1-12 DATAGURU专业数据分析网站 2012.11.15 表(索引)分析中几个重要的参数 � estimate_percent estimate_percent estimate_percent estimate_percent –DBMS_STATS.AUTO_SAMPLE_SIZEDBMS_STATS.AUTO_SAMPLE_SIZEDBMS_STATS.AUTO_SAMPLE_SIZEDBMS_STATS.AUTO_SAMPLE_SIZE – 手工设置(范围 0.000001,100 0.000001,100 0.000001,100 0.000001,100) • 超大表 • 大表 • 小表 2013- 1-12 DATAGURU专业数据分析网站 2012.11.15 gather_table_stats几个重要的参数 � granularity 数据分析的力度 – global – partition – subpartition 2013- 1-12 DATAGURU专业数据分析网站 2012.11.15 全局分析和分区分析 2013- 1-12 DATAGURU专业数据分析网站 2012.11.15 全局分析和分区分析 使用分区统计信息 使用全局统计信息 使用全局统计信息 2013- 1-12 DATAGURU专业数据分析网站 2012.11.15 全局分析和分区分析 � 当表上已经有全局统计信息时,单独对分区分析,不会更新全局信息。 2013- 1-12 DATAGURU专业数据分析网站 2012.11.15 全局分析和分区分析 当表上没有全局统计信息时,单独对分区分析,会更新全局信息(合并分区信息--11g)。 2013- 1-12 DATAGURU专业数据分析网站 2012.11.15 全区和全局信息 � 增量统计(Oracle11g) – Oracle会增量的收集分区信息来更新全局信息 2013- 1-12 DATAGURU专业数据分析网站 2012.11.15 分区和全局信息 � 结论:如何设置这个参数 – 在一个很大的分区表(OLAP),全局分析代价是非常昂贵的。 –OLAP系统下,除了新加入的数据外,旧的数据基本上是没有变化的,全局分析很浪费资源。 – 对于很大的分区表,将granulariy设置为partition(Oracle10g)或者incremental( Oracle11g)是很有意义的。 – 对于不大的分区表,可以使用默认设置。 2013- 1-12 DATAGURU专业数据分析网站 2012.11.15 gather_table_stats几个重要的参数 � method_opt 分析直方图选项 2013- 1-12 DATAGURU专业数据分析网站 2012.11.15 直方图 � 概念--Oracle对列上的数据分布进行统计分析,对数据倾斜分布时 很有用。 � CBO的数据来源 表未分析 数据块,默认值,动态采样 表已分析(未生成直方图) num_rows,NDV,BLOCKS.... ????? 列的相关性 表已分析(生成直方图) 列上的数据分布 2013- 1-12 DATAGURU专业数据分析网站 2012.11.15 直方图 Frequency-- 频率直方图 Height-Balanced-- 高度 平衡直方图 2013- 1-12 DATAGURU专业数据分析网站 2012.11.15 直方图示例--- HEIGHT BALANCED 2013- 1-12 DATAGURU专业数据分析网站 2012.11.15 直方图示例--- FREQUENCY 2013- 1-12 DATAGURU专业数据分析网站 2012.11.15 直方图示例 � 重回最初的示例。 2013- 1-12 DATAGURU专业数据分析网站 2012.11.15 GATHER_TABLE_STATS.METHOD_OPT �� for all columns:for all columns:统计所有列的统计所有列的histograms.histograms. �� for all indexed columns:for all indexed columns:统计所有统计所有indexedindexed列的列的histograms.histograms. �� for all hidden columns:for all hidden columns:统计你看不到列的统计你看不到列的histogramshistograms �� for columns SIZE | REPEAT | AUTO | SKEWONLY:for columns SIZE | REPEAT | AUTO | SKEWONLY: –N的取值范围[1,254]; –REPEAT上次统计过的histograms; –AUTO由oracle决定N的大小; – SKEWONLY -size skewonly 只收集非均匀分布的直方图,系统自动决定桶数( bucket ) 2013- 1-12 DATAGURU专业数据分析网站 2012.11.15 DBMS_STATS--Extended Statistics 2013- 1-12 DATAGURU专业数据分析网站 2012.11.15 DBMS_STATS--Extended Statistics 2013- 1-12 DATAGURU专业数据分析网站 2012.11.15 DBMS_STATS--Extended Statistics 2013- 1-12 DATAGURU专业数据分析网站 2012.11.15 动态采样 � 当表上没有分析信息时,Oracle会使用动态采样技术。 2013- 1-12 DATAGURU专业数据分析网站 2012.11.15 动态采样的级别 � 不同的级别,采样的数据块数量不同 – level1-10,采样数据量逐级递增。 – level10 对所有数据进行采样分析 2013- 1-12 DATAGURU专业数据分析网站 2012.11.15 动态采样 V.S. DBMS_STATS � 我的观点 – 动态采样只能作为一种辅助手段。 – 对于海量数据,动态采样的数据块太少,无法准确的反映数据的真实情况; – 如果采样率高,会直接影响SQL的执行效率。 –DBMS_STATS可以非常灵活的进行数据分析配置 • 分析比例 • 分析时间 • 直方图 • 分析数据的管理 ... ... 40DATAGURU专业数据分析网站 2012.11.12 炼数成金逆向收费式网络课程 � Dataguru(炼数成金)是专业数据分析网站,提供教育,媒体,内容,社区,出版, 数据分析业务等服务。我们的课程采用新兴的互联网教育形式,独创地发展了逆向收 费式网络培训课程模式。既继承传统教育重学习氛围,重竞争压力的特点,同时又发 挥互联网的威力打破时空限制,把天南地北志同道合的朋友组织在一起交流学习,使 到原先孤立的学习个体组合成有组织的探索力量。并且把原先动辄成千上万的学习成 本,直线下降至百元范围,造福大众。我们的目标是:低成本传播高价值知识,构架 中国第一的网上知识流转阵地。 � 关于逆向收费式网络的详情,请看我们的培训网站 http://edu.dataguru.cn DATAGURU专业数据分析网站 Thanks FAQ时间
还剩40页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 3 金币 [ 分享pdf获得金币 ] 2 人已下载

下载pdf

pdf贡献者

adt126

贡献于2014-01-21

下载需要 3 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!
下载pdf