首先来介绍oracle数据库使用基于规则优化器(RBO)来决定如何执行一个sql语句.基于规则优化器顾名思义,它是遵循一组规则来判断一个sql语句的执行计划.这组规则是有排名的如果有两种可能的规则应该应用于一个sql语句,那么排名较低的规则会被使用
在oracle 7中,引入了基于成本的优化器使用优化器的功能得以增强包括并行执行,分区,还会考虑用户真实数据的内容和分布情况.基于成本的优化器会检查一个sql语句的所有执行计划并选择一个成本值最低的,这里的成本代表一个指定执行计划所要消耗的资源使用情况.一个执行计划成本值越低该执行计划越有效.为了让基于成本的优化器精确的判断一个执行计划的成本它必需有描述该语句所访问的所有对象(表和索引)的信息和描述运行这个语句的系统信息.这些需要的信息通常被称为优化器统计数据.理解和管理优化器统计数据是优化sql执行的关键.知道何时以及及时的怎样收集统计数据对于维护一个稳定的性能来说很关键.优化器统计数据包括以下方面的信息:
什么是优化器统计数据
收集统计数据
管理统计数据
其它类型的统计数据
什么是优化器统计数据
优化器统计数据是一组描述数据库和数据库中对象信息的集合.这些统计数据在优化器给每一个sql语句选择一个最优的执行计划时会被使用.统计数据存储在数据字典中且它们能通过数据字典视图比如象user_tab_statistics来进行访问.优化器统计数据与v$视图来查看的性能统计数据不同.v$视图中的信息是系统状态和执行sql工作负载的信息.
表和列统计数据
表统计数据包括了表中的行记录数,表用来存储这些数据的数据块数,表中的平均行长度等信息.优化器使用这些信息并结合其它的统计数据来计算执行计划中各个操作的成本并评估每一个操作将返回的行数.例如,一个表扫描的成本是使用表所使用的数据块数和参数db_file_multiblock_read_count来计算出来的.可以查询user_tab_statistics视图来查看表的统计数据.
列统计数据包括一个列中的不同值的个数(NDV)还有在这个列中的最小值/最大值.可能查询user_tab_col_statistics视图来查看这列的统计数据.优化器使用列统计数据并结合表的统计数据(行数)来评估一个sql操作将要返回的行数.例如一个表有100行记录且对一个有10个不同值的列使用等号谓词评估,那么优化器会假设统一的数据分布,那么评估的基数等于表的记录数除以这个列不同值的个数:100/10=10.
额外的列统计数据
基本表和列统计数据没有提供一种机制来告诉优化器关于表中或列中的数据的特性.例如,这些统计数据不能告诉优化器表中的列中的数据是否有倾斜或者列之间是否存在关联.数据特性的信息可以通过扩展基本的统计数据象,直方图,列组和表达式统计数据提供给优化器.
直方图
直方图告诉优化器关于列中的数据分布情况.缺省情况(没有直方图),优化器会假设一个列中的不同值会均匀分布的.同上所述,优化器评估一个等号谓词的基数是通过这个表中的行数除以这个等号谓词列中不同值的个数得到的.如果数据分布不是均匀的(比如数据倾斜),那么这样的基数评估就会出错.为了精确的反映非均匀的数据分布就需要对列创建直方图.直方图的存在改变了优化器评估基数的公式且会让优化器生成一个更精确的执行计划.
Oracle基于列的使用信息(SYS.COL_USAGE$)和数据的倾斜情况能自动判断这个列是否需要直方图.例如只在一个等号谓词中看到的唯一性列oracle是不会对这个唯一性列自动创建直方图.
有两种类型的直方图,频率直方图和高度平衡直方图.oracle会基于列中的不同值的个数来决定创建直方图的类型.
频率直方图
当列中的不同值的个数据小于254时会创建频率直方图.oracle使用下面的步骤来创建频率直方图:
1. 让我们假设oracle正对promotions表的promo_category_id创建一个频率直方图.第一步从promotions表中通过对promo_category_id排序来查询promo_category_id
2. 每一个promo_category_id被指派到它所属的直方图bucket中
在这个步骤中可能直方图的bucket数超过254,因此拥有相同值的bucket会被压缩到使用这个值的最高bucket中.在这种情况下,bucket 2到 115会被压缩到bucket 115,且bucket 484到503会被压缩到bucket 503直到总的bucket数仍然保持与列中不同值的个数相等.注意上面的步骤是出于演示.dbms_stats包对直接构造压缩直方图进行了优化.
优化器现在使用频率直方图可以精确的判断谓词promo_category_id的基数.例如,对于谓词promo_category_id=10,优化器首先需要判断在直方图中10作为end point的bucket的数量.通过找到endpoint为10的bucket 503,然后减去前面的bucket数,bucket 483,503-483=20.然后基数评估将使用下面的公式进行计算(number of bucket endpoints/ total number of bucket) * num_rows,20/503*503,所以在promotoins表中promo_category_id=10的记录有20行.
当列中的不同值的数理超过254时就会创建高度平衡直方图.在高度平衡直方图中,列值被划分到bucket中所以每一个bucket可能包含相同数量的行.oracle使用下面的步骤来创建一个高度平衡直方图.
1. 我们假设oracle将要对customers表中的cust_city_id列创建一个高度平衡直方图因cust_city_id列的不同值的数量超过了254.与频率直方图类似,第一步是执行ordered by cust_city_id子句来查询customers表中的cust_city_id.
2. 在customers表中有55500行直方图中最多有254个bucket.为了让每一个bucket中有一个相等的行数,oracle必须在每一个bucket中存入219行.第219行的cust_city_id将成为第一个bucket的endpoint在这种情况下是51043.第438行的cust_city_id将成为第二个bucket的endpoint并且一直到所有254buckets被填满为止.
3. 一旦bucket创建完成后oracle会检查是否第一个bucket的endpoint值是否是cust_city_id列中的最小值,如果不是,一个”zero”bucket会被添加到直方图中而且cust_city_id列中的最小值作为它的endpoint.
4. 与频率直方图一样最后的步骤是压缩高度平衡直方图并且删除有重复endpoint的bucket.在cust_city_id列的高度平衡直方图中51166是bucket24和bucket25的endpoint.因此bucket24将会被压缩到bucket 25中
5. 使用高度平衡直方图现在优化器能对谓词cust_city_id列进行更好的基数评估.例如,对于谓词cust_city_id=51806,优化器首先会检查在直方图中使用51806作为endpoint的有多少个bucket.在这种情况下136,137,138和139的endpoint是51806(查看user_histograms).优化器将会使用下面的计算公式:
(Number of bucket endpoints / total number of buckets) * number of rows in the table
在这种情况下: 4/254 * 55500=874
然而如果谓词cust_city_id=52500它不是任何bucket的endpoint值那么优化器会使用一个不同的计算公式.对于只是一个bucket或者不是任何bucket的endpoint的值优化器将使用下面的计算公式:
DENSITY * number of rows in the table
这里的density是对直方图使用内部算法计算出来的.density的值可以通过查看user_tab_col_statistics得到这个值是从oracle database 10.2.0.4之前使用.这个值是为了向后兼容,这个值在oracle database 9i和前期的oracle database 10g中使用.因此如果optimizer_features_enable被设置的版本比10.2.0.4前那么视图中的density值将会被使用.
扩展统计数据
在oracle database 11g中引入了扩展列统计数据.扩展统计数据包括另外两种额外类型的统计数:列组和表达式统计数据.
列组
在真实的数据中,在相同表中存储在不同列中的数据之间通常是有关联的.例如,在customers表中,cust_state_province列会受country_id列的影响,比如当state为California那么country只能是United States.只使用基本的列统计数据优化器是没有办法知道真实数据之间的关系如果一个语句中的where子句中有多个列来自同一个表那么有可能会计算出错误的基数.通过将这些列作为一个团体组来扩展它的统计数据使优化器知道这些真实世界的关系.
通过对一组列创建统计数据,当来自同一个表的几个列同时一起出现在语句中的where子句中时优化器能更好的评估基数.可以使用dbms_stats.create_extended_stats函数来对要收集列统计数据的这些列定义一个列组.当一个列组被创建后,当对这个表收集统计数据时和其它原始列一样oracle将自动对列组维护统计数据.
在创建列组和重新收集统计数据之后,在视图user_tab_col_statistics中可以看到一个系统生成名字的列.这个新列代表列组:
为了将系统生成列名映射到列组上可查看用户下其它扩展的统计数据,可以查询user_stat_extensions
现在当这些列一起出现在where子句中优化器就能使用列组统计数据而不使用单列统计数据.要注意的是并不是这个列组中的所有列都必须出现在sql语句中只要列组中的这些列的子集出现在sql语句中优化器就会使用扩展的统计数据.
表达式统计数据
也可以对一个表达式(包括函数)创建扩展的统计数据,来帮助优化器来对where子句中嵌有表达式的列进行基数评估.例如,常见的是一个where子句对一个customers表的last name使用upper函数,upper(cust_last_name)=:B1,那么对于这个表达式upper(cust_last_name)创建一个扩展的统计数据是有益的.
与列组一样,在表达式统计数据被定义后需要重新对这个表收集统计数据.在统计数据被收集后可能通过user_tab_col_statistics视图来查看通过系统生成的列名,它代表表达式统计数据.就象列组一样关于表达式统计数据可以在user_stat_extensions视图中可以找到.
扩展的统计数据的限制
扩展的统计数据只有当where子句中是等号谓词或者in列表时才会被使用.如果在底层的列存在直方图且列组上不存在直方图那么扩展的统计数据将不会被使用.
索引统计数据
索引统计数据提供了索引中不同值的数量(distinct keys),索引的深度(blevel),索引的叶子块数量(leaf_blocks)和集族因子.优化器使用这些信息与其它的统计数据一起来判断一个索引访问的成本.例如优化器使用blevel,leaf_blocks和表统计数据的num_rows来判断一个索引范围扫描的成本
收集统计数据
对于不断改变的数据对象统计数据也必须定期的收集才能精确的描述数据库对象.dbms_stats包是oracle推荐收集统计数据的方法用它来替代过时的analyze命令.dbms_stats包包含了超过50个不同的收集和管理统计数据的过程.但最重要的是这些过程gather_*_stats过程.这些过程被用来收集表,列和索引统计数据.运行这些过程需要需要是对象的所有者或者有analyze any的系统权限或者是dba角色.这些过程使用的参数几乎是相同的.所以这里重点介绍gather_table_stats过程.
Gather_table_stats
Dbms_stats.gather_table_stats过程可以用来收集表,分区,索引和列统计数据.虽然这个过程有15个不同的参数,但是运行这个过程时只需要指定前两个或前三个就能满足大多数用户的需求.
包含这个表的方案名
表名
如果是分区表且想要对这个特定的分区收集统计数据就是一个特定的分区名
其它的参数在大多数情况下可以保留其默认值.
Estimate_percent参数
这个estimate_percent参数判断在计算统计数据是使用的行记录的百分比.当表中所有的行(100%sample)被处理时收集的统计数据最精确,通常也称作计算统计数据.在oracle database 11g中引入了一种新的抽样算法是基于哈希的且提供了确定的统计数据.这种新的算法精确度与100%抽样很接近但是成本只有100%抽样的10%.当任何dbms_stats.gather_*_stats过程中的estimate_percent设置为auto_sample_size(缺省值)时就会使用这种新的算法.以前用户将estimate_percent参数设置为一个较低的值来确保统计数据能被快速的收集完成.然而没有经过详细的测试是很难知道使用什么样的抽样大小可以得到精确的统计数据.强烈建议从oracle database 11g开始让estimate_percent参数使用其缺省值(而不是显式的设置).
Method_opt参数
这个method_opt参数控制着在收集统计数据时是否创建直方图.直方图是当列中数据不是均匀分布时创建的一种特定类型的列统计数据.使用缺省值for all column size auto,oracle会基于列的使用信息(dbms_stats.report_col_usage)和列中不同值的数量来自动判断哪个列和将要使用的bucket的数量.列的使用信息反映了数据库对一个指定对象所处理的所有sql操作的一种分析.列使用跟踪缺省情况下是启用的.
如果列在where子句中以等号谓词,范围,like等形式出现那么这个列是创建直方图的一个备选者.如果在创建直方图之前列中的数据存在倾斜oracle也会验证.例如列只以等号谓词出现
且是唯一性列将不会对这个列创建直方图.
Degree参数
这个degree参数控制着用于收集统计数据的并行服务器进程的个数.通常oracle使用并行服务器进程的个数与数据字典中表的degree属性指定的值相同(并行度).缺省情况下在oracle database中所有表的这个属性被设置为1,如果对一个大表收集统计数据为了加快收集的速度这个参数是用的.当参数degree设置为auto_degree,oracle将基于对象的大小来自动判断收集统计数据时的并行服务器进程的个数.这个值的范围介于小对象的1(串行执行)到大对象的
Default_degree(parallel_threads_per_cpu * cpu_count).
Granularity参数
这个granularity参数指示了对分区表收集统计数据的级别.可能的级别是表(全局),分区或子分区.缺省情况下基于表的分区策略oracle将会判断需要使用的级别.统计数据总是在第一级分区进行收集不管分区的类型.当子分区类型是list或range时会收集子分区统计数据.如果表不是分区表这个参数会被忽略.
Cascade参数
这个cascade参数控制着是否对表中的索引收集统计数据.缺省值auto_cascade,oracle将只会对哪些统计数据过期的表重新收集索引统计数据.当一个大量数据直接加载时并且索引是禁用时节cascade通常是设置为false.在数据加载完成后,索引要被重建统计数据也会自动创建,当收集表统计数据时不需要收集索引统计数据
No_invalidate参数
这个no_invalidate参数决定在统计数据收集后游标(当收集统计数据时访问表的游标)是否立即失效.缺省值是dbms_stats.auto_invalidate,游标(已经被解析的语句)不会立即失效.它们将使用之前统计数据所创建的执行计划直到oracle基于内部启示决定依赖的游标失效为止.为了确保在共享池中没有性能问题或者如果有大量的依赖游标而且它们都是硬解析随着时间的推移失效将会发生.
改变dbms_stats.gather_*_stats中参数的缺省值
可以对一个单独的dbms_stats.gather_*_stats命令指定一个非缺省值或者对于数据库覆盖其缺省值.可以使用dbms_stats.set_*_prefs
过程来覆盖dbms_stats.gather_*_stats过程中的缺省参数值.下面是可以修改的参数列表:
AUTOSTATS_TARGET (SET_GLOBAL_PREFS only as it relates to the auto stats job) CONCURRENT (SET_GLOBAL_PREFS only)
CASCADE DEGREE ESTIMATE_PERCENT METHOD_OPT NO_INVALIDATE GRANULARITY PUBLISH INCREMENTAL STALE_PERCENT
可以使用下面的dbms_stats.set_*_prefs过程来在表,方案,数据库或全局级别来覆盖每一个参数的缺省值.
SET_TABLE_PREFS
SET_SCHEMA_PREFS
SET_DATABASE_PREFS
SET_GLOBAL_PREFS
Set_table_prefs过程允许修改用于一个特定表统计收集dbms_stats.gather_*_stats过程的缺省参数值.
Set_schema_prefs过程允许修改用于一个特定方案中所有表统计收集dbms_stats.gather_*_stats过程的缺省参数值.这个过程实际上是对一个特定方案中的每一个表调用一次set_table_prefs过程,因为它是调用set_table_prefs所以调用这个过程在其运行后不会对新创建的对象有影响.新对象将对所有参数使用golable引用值.
Set_database_prefs过程允许修改用于数据库所有用户方案统计收集dbms_stata.gather_*_stats过程的缺省参数值.这个过程实际上是对每一个用户方案中的每一个表调用set_table_prefs过程. 因为它是调用set_table_prefs所以调用这个过程在其运行后不会对新创建的对象有影响.新对象将对所有参数使用golable引用值.如果将参数add_sys设置为true那么它也有可能包含oracle所拥有的方案(sys,system等).
Set_global_prefs过程允许修改用于数据库中任何对象统计收集dbms_stats.gather_*_stats过程的缺省参数值.所有参数缺省为全局设置除非有表进行了优先设置或者这个参数通过gahter_*_stats命令被显式的设置.通过这个过程修改的参数将会影响修改之后所有新创建的对象.新对象将会使用所有参数的global_prefs.
使用set_global_prefs也可以对两个额外的参数autostat_target和concurrent.autostat_target设置缺省值来控制什么对象将会被自动统计收集job来进行统计收集.这个参数可能的值有all,oracle和auto.缺省值是auto.
Concurrent参数控制着在一个用户(或数据库)是否并发对多个表和对一个表的多个分区收集统计数据.这是一个布尔型的参数,缺省值为false.concurrent参数值不会影响自动统计收集job.
Dbms_stats.gather_*_stats过程和自动统计收集job遵循以下层次的参数值,通过命令显式设置的参数值将会对其它设置进行覆盖.如果参数没有通过命令进行设置,就会检查表级的参数引用.如果没有表级引用设置,就使用global引用.
如果你不能确保什么级别的参数引用被设置可以使用dbms_stats.get_prefs函数来检查.这个函数有三个参数,参数名,方案名和表名.在下面的例子中我们首先检查sh.sales表上的stale_percent的值.然后设置表级参数并且检查使用dbms_stats.get_prefs的影响.
自动统计收集job
通过在一个预定义的维护窗口中运行一个oracle自动任务来收丢失统计或统计过期的所有数据库对象自动收集统计数据(工作日的上午10点到零晨2点和周末的6点到零晨2点).
自动收集统计数据是通过调用内部过程
Dbms_stats.gather_database_stats_job_proc来实现的.这个过程操作非常类似如使用gahter auto选项的dbms_stats.gather_database_stats过程.主要区别是oracle内部非常重视需要统计数据的数据库对象，所以这些对象那个最需要更新统计数据谁就会被优先处理.可以通过查询dba_autotask_client_job视图或EM来验证.也可以通过EM来改变这个job的维护窗口.
一个表中被修改的行数超过stale_percent(缺省值10%)时就会认为这个表的统计数据过时了.oracle会监控所有表的DML活动并在SGA中进行记录.监控的信息会定时的刷新到磁盘且可以通过*_tab_modifications视图来查看.
也可以调用dbms_stats.flush_database_monitoring_info过程来手动刷新这些数据.如果想在查询时得到最新信息(在所有统计数据收集之前内部监控数据会被刷新).可以通过查询user_tab_statistics视图中的stale_stats列来查看哪个表的统计数据过时了.
表的stale_stats被设置为NO,统计数据是最新的.表的stale_stats被设置为YES,统计数据是过时的.表的stale_stats没有被设置说明丢失统计数据.
如果你有一套完善的统计数据收集过程或者因为某些原因想对主应用程序方案禁用自动统计收集,保留对数据字典表的收集.可以将dbms_stats.set_global_prefs过程的autostats_target从auto设置为oracle.
BEGIN
DBMS_STATS.SET_GLOBAL_PREFS(‘AUTOSTATS_TARGET’,’ORACLE’);
END;
/
同时一起禁用自动收集任务:
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => ‘auto optimizer stats collection’,
operation => NULL,
window_name => NULL);
END;
/
提高收集统计数据的效率
当你确定了对哪些统计数据感兴趣你可能想要及时的收集这些统计数据.传统上人们希望通过使用并行来提高统计数据收集的速度.然而,如果方案中所有的对象很小并不能使用并行执行那要怎样提高统计数据收集的速度呢
并行统计数据收集
在oracle database 11g release 2(11.2.0.2)中,并行统计数据收集模式被引入来同时并发的收集一个方案中的多个表和一个表中的多个分(子分)区.对多个表和多个分(子分)区同时并行收集统计数据能充分利用多CPU的资源来减少收集的时间.
并发统计数据收集是通过全局参数来控制的,concurrent可以设置为true或false.缺省值为false.当concurrent设置为true时,oracle将会使用oracle job调度和高级队列组件来创建和管理多个并发统计数据收集job.
当concurrent设置为true时,对一个分区表调用dbms_stats.gather_table_stats使用oracle对这个分区表的每一个分区创建一个竭的统计数据收集job.这些job中有多少将会同时并发执行,这些可能job队列过程中有多少将会排队(初始化参数job_queue_processes.在rac中每一个节点的job_queue_processes)和有多不可用的系统资源.当当前运行的job完成后,更多的job会被排队和执行直到所有的分区都完成统计数据收集为止.
如果使用dbms_stats.gather_database_stats,dbms_stats.gather_schema_stats或者dbms_stats.gather_dictionary_stats收集统计数据,那么oracle将会对每一个非分区表和对分区表的每一个分区创建一个单独的统计数据收集job.每一个分区表都有一个调度job来管理它的分区job.数据库会尽可能的同时多运行几个job和余下的job将会排队直到执行的job完成.然而为了防止死锁的发生多个分区表不能同时被处理.因此如果对一个分区表运行了几个job那么其它的分区表将会进行排队等待直到当前的收集job完成为止.对于非分区表没有限制.
下面的数据说明了在sh方案中使用dbms_stats.gather_schema_stats命令在不同级别创建job.
Oracle将会对每一个非分区表创建一个统计数据收集job.
CHANNELS,
COUNTRIES,
CUSTOMERS,
PRODUCTS,
PROMOTIONS,
TIMES
每一个分区表一个调度job.比如,sales和costs.且它将对sales和costs中的每一个分区创建一个统计数据收集job.
假设job_queue_processes参数设置为32,oracle job调度器允许运行32个统计数据收集job.假设costs表的第29个job开始执行,那么三个非分区表统计数据收集job也会开始运行,sales的统计数据收集job将会自动排队,因为一次只能处理一个分区表.当每一个job完成后,另外的job将会进行排队和开始执行直到所有64个job全部完成为止.每一个单独的统计数据收集job都能使用并行执行.
配置和设置
在oracle database 11.2.0.2中,统计数据收集的并行设置缺省情况下是关闭的.可以使用下面的命令将其打开.
BEGIN
DBMS_STATS.SET_GLOBAL_PREFS(‘CONCURRENT’,’TRUE’);
END;
/
为了收集统计数据可能也会需要一些额外的权限.用户必须要有job scheduler和AQ权限:
CREATE JOB
MANAGE SCHEDULER
MANAGE ANY QUEUE
当job scheduler在sysaux表空间中存储它的内部表和视图时sysaux表空间应该是联机状态.最后对于统计数据收集过程来说job_queue_processes参数的设置应该要能完全利用可用的系统资源.如果不计划使用并行执行应该将job_queue_processes设置为cpu核数的2倍(在rac中需要对每一个节点进行设置).要确保在系统级别设置这个参数(alter system..或者通过init.ora文件)而不是在会话级别进行设置(alter session).
如果将使用并行执行作为并发统计数据收集的一部分应该禁用parallel_adaptive_multi_user初始化参数.
ALTER SYSTEM SET parallel_adaptive_multi_user=false;
也建议启用并行语句队列.这要求资源管理器是激活的且创建一个临时资源计划组资源消耗组”OTHER_GROUPS”将启用排队.通常资源管理器只在维护窗口期间是激活的.下面的脚本将创建一个临时资源计划并对这个计划使用资源管理器.
你会注意到自动统计数据收集job现在不会使用并发.将concurrent设置为true对自动统计数据收集job不会有影响.
分区表的统计数据收集
分区表的统计数据收集它是由表级别和分区级别的统计数据收集组成.在oracle database 11g之前,新增加一个分区或修改一个分区都要求扫描整个表来重新刷新表级别的统计数据.如果跳过全局层面的统计数据收集那么优化器会基于已经存在的分区级别的统计数据来推断全局层面的统计数据.这种方法对于简单表的统计数据来说是精确的比如象行数可以通过聚合每一个分区的行数来统计—但是其它的统计数据是不能精确的判断的.比如,不能基于所有分区的单个分区来精确的判断一个列中的不同值的数量(优化器使用的最关键的统计数据).
在oracle database 11g中通过引入增量全局统计数据增加了对分区表的统计数据收集.如果一个分区的incremental参数设置为true,dbms_stats.gather_*_stats参数granularity为global,estimate_percent设置为auto_sample_size. Oracle将会对新的分区收集统计数据并通过扫描新增加的或修改的分区来更新所有全局层面的统计数据但不是扫描整个表.
增量全局统计数据是通过为表中每一个分区存储的概要来计算出来的.一个概要是分区和分区中列的统计数据的元数据.每一个概要存储在sysaux表空间中.全局统计数据是通过聚合分区级别的统计数据和每一个分区的概要生成的,因此消除了为了收集表级别统计数据而要扫描整个表.当一个新的分区被增加到表中,仅需要对新增加的分区收集统计数据.全局统计数据会自动使用新的分区概要和已经存在的分区概要来精确的更新.
下面是使用增量全局统计数据的步骤
在表级或全局层面启用增量统计数据
BEGIN
DBMS_STATS.SET_TABLE_PREFS(‘SH’,’SALES’,’INCREMENTAL’,’TRUE’);
END;
/
为了检查一个指定表的incremental的当前设置可以使用dbms_stats.get_prefs;
SELECT DBMS_STATS.GET_PREFS(‘INCREMENTAL’,’SH’,’SALES’) FROM dual;
注意incremental不会应用到子分区上.子分区和分区的统计数据会正常的被收集.只是分区统计数据将会用来确定全局或表级别的统计数据.
管理统计数据
为了收到适当的统计数据,提供一种全面框架来管理它们也很重要.oracle提供了一系列的方法来做这件事包括将统计数据还原到之前的版本,将统计数据从一个系统传输到另一个系统或者甚至手动设置统计数据.这些选项在特定情况下是非常有用的,但是不建议替换标准的收集统计数据的方法dbms_stats.
还原统计数据
从oracle database 10g开始,当你使用dbms_stats收集统计数据时原始的统计数据会自动在数据字典表中进行备份, 如果新收集的统计数据导致任何的问题通过运行dbms_stats.restore_table_stats就能很容易的还原.视图dba_tab_stats_history包含了每一个表统计数据保存的时间戳.
下面的例子将sales表的统计数据还原成昨天收集的且在shared_pool中引用sales表的所有游标会自动变为无效.想让所有的游标变为无效因为将统计数据还原成昨天的想让它们立即影响所有游标.no_invalidate参数值会判断引用该表的游标是否会变为无效.
BEGIN
DBMS_STATS.RESTORE_TABLE_STATS(ownname => ‘SH’,
tabname => ‘SALES’,
as_of_timestamp => SYSTIMESTAMP-1
force => FALSE,
no_invalidate => FALSE);
END;
/
Pending统计数据
通常情况下当收集统计数据时,收集到的统计数据会被立即发布(写)到适当的数据字典表并提供给优化器使用.在oracle database 11g中可以将收集到的统计数据不立即发布,而是将它们存储为一个没有发布的pending状态.它们不是存储在常用的数据字典表中而是存储在pending表所以在发布它们之前可以对它们进行测试.这些pending统计数据可以以一种受控的方式对单个会话启用,这样就可以在发布之前对这些统计数据进行验证.为了启用pending统计数据收集需要使用dbms_stats.set_*_prefs这样的一个过程对你想创建pending统计数据的对象将参数publish从true改变false.
BEGIN
DBMS_STATS.SET_TABLE_PREFS(‘SH’,’SALES’,‘PUBLISH’,’FALSE’);
END;
/
收集统计数据
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(‘SH’,’SALES’);
END;
/
对这些对象收集的统计数据可以通过user_*_pending_stats来显示.可以通过一个alter session命令来设置初始化参数optimizer_use_pending_stats为true来告诉优化器使用pending统计数据并运一个sql,对于访问的表没有pending统计数据优化器将会使用标准数据字典表中的当前统计数据.当对pending统计数据进行验证后可以使用下面的过程将其发布.
DBMS_STATS.PUBLISH_PENDING_STATS.
BEGIN
DBMS_STATS.PUBLISH_PENDING_STATS(‘SH’,’SALES’);
END;
/
导出/导入统计数据
当开发一个新应用程序或一个新模块想要测试时,理想情况下想要测试系统与生产系统的硬件平台和数据大小是相同的.这种情况并不总是可以满足的.最常见的问题是生产系统的大小.通过将一个产生数据库中的优化统计数据复制到另一个相同数据库版本的系统中,这样就可以模仿生产环境中的行为.生产数据库中的统计数据可以使用dbms_stats.export_*_stats和dbms_stats.import_*_stats过程复制到测试系统中.
在导出统计数据之前需要使用dbms_stats.create_stat_table过程来创建一个表来存储这些统计数据.在这个表创建之后可以使用dbms_stats.export_*_stats过程从数据字典中导出统计数据.当统计数据填充到统计数据表中,可以使用datadump从生产数据库中将统计数据表中的数据导出并导入到测试数据库中.当统计数据表完全导入到测试数据库中,可以使用dbms_stats.import_*_stats过程导入到数据字典表.下面的例子将创建一个统计数据表MYSTATS并从sh方案中将统计数据导出到MYSTATS统计数据表中.
复制分区统计数据
当处理分区表时优化器依赖整个表(全局统计数据)统计数据和单个分区统计数据来给一个sql语句选择一个好的执行计划.如果查询需要访问的只有单个分区优化器只会使用要访问的这个分区的统计数据.如果查询要访问多个分区优化器将会使用全局统计数据和分区统计数据.
常见的一个范围分区表有一个新的分区增加到一个存在的表中并向这个增加的分区插入数据.如果终端用户在统计数据收集之前要查询新插入的数据,由于过时的统计数据可能会选择一个次优的执行计划.最常见的一种情况是where子句中谓词使用值超出了列统计数据中[最小值,最大值]的范围.这就是一个’out-of-range’的错误.
超出范围的条件可以使用dbms_stats.copy_table_stats过程(从oracle database 10.2.0.4开始可以使用)来预防.这个过程复制一个代表源(子)分区的统计数据到一个新创建的空的(子)分区中.它也会复制依赖对象的统计数据:列,本地(分区)索引等等.按下面的方式来调整分区列的最小值和最大值.
如果分区类型是hash分区那么目标分区的最小值和最大值与源分区相同.
如果分区类型是list分区且目标分区是一个not default分区,那么目标分区的最小值将被设置为描述目标分区列表值中的最小值.最大值将被设置为描述目标分区列表值中的最大值.
如果分区炻是list分区且目标分区是一个default分区,那么目标分区的最小值被设置为源分区中的最小值,目标分区的最大值被设置为源分区中的最大值.
如果分区类型是range那么目标分区的最小值设置为之前分区的上限值,目标分区的最大值设置为目标分区的上限值除非目标分区的上限值是maxvalue,在这种情况下目标分区的最大值设置为之前分区的上限值.
它也能基于指定的比例来复制统计数据(比如块数或行数).下面的命令将sales_q3_2011范围分区的统计数据复制到sales表的sales_q4_2011分区中.比例因子为2.
BEGIN
DBMS_STATS.COPY_TABLE_STATS(‘SH’,’SALES’,’SALES_Q3_2002′,’SALES_Q4_2002′, 2);
END;
/
如果索引分区名与表分区一样只会复制索引统计数据.全局或表级统计数据缺省情况下不会被更新.全局层面的统计数据只有调用dbms_stats.copy_table_stats时没有全局统计数据存在时通过聚合生成全局统计数据时才会被影响.
比较统计数据
一个系统中sql语句的执行计划与另一个系统中的执行计划不同一个关键的原因变是每个系统中的统计数据不同,例如测试系统中的数据不是100%的与生成系统的中相同.为了识别不同的统计数据
Dbms_stats.diff_table_stats_*函数可以用来比较两个不同数据来源的表统计数据.统计数据来源可以是:
一个用户统计数据表和数据字典中的当前统计数
单个用户统计数据表包含两组统计数据可以通过statids来识别
两个不同用户统计数据表
历史中的两个时间点的统计数据
当前统计数与厍上的一个时间点的统计数据
Pending统计数据与数据字典中当前的统计数据
Pending统计数据与用户统计数据表
这个函数也可以比较两个单独对象的统计数据(索引,列,分区)而且如果统计数据之间的差别超过了指定的阈值也俑显示两个来源对象的所有统计数据.这个阈值可以通过一个函数的参数来指定缺省值是10%.第一个来源的统计数据将会用作计算不同百分比的基数.
下面的例子中将用当前数据字典中emp表的统计数据与统计数据表TAB1中emp表的统计数据进行比较,下面的sql语句将会生成了一个报告:
SELECT report, maxdiffpct
FROM table(DBMS_STATS.DIFF_TABLE_STATS_IN_STATTAB(‘SCOTT’,’EMP’,’TAB1’ ));
锁定统计数据
在有些情况下,可以通过锁定统计数据来阻止对表或方案收集新的统计数据.当统计数据被锁定后这些统计数据直到这些统计数据被解锁或者使用gather_*_stats过程时将参数force设置为true否则是不会被修改的.
在oracle database 11g中dbms_stats包被扩展到允许统计数据在分区级别被锁定和解锁.这些额外的过程允许更细粒度的来控制.
BEGIN
DBMS_STATS.LOCK_PARTITION_STATS(‘SH’,’SALES’, ‘SALES_Q3_2000’);
END;
注意这里有一种层次结构的锁定统计数据.例如,如果对一个分区表锁定统计数据,然后为了对一个分区重新收集统计数据需要对这一个分区解锁统计数据这时就会收到ora-20005错误.出现这个错误是因为分区虽然已经解锁但表级仍然被锁定.这时要对分区重新收集统计数据只在将force参数设置为true才能成功.
手动设置统计数据
在某些特定的情况下手动设置存储在数据字典中的优化器统计数据是非常有效的.比如一个高度不稳定的全局临时表(注意虽然这里里讨论手动设置统计数据,但是不建议这么做.因为不精确或不一致的统计数据可能导致选择性能极差的执行计划).统计数据可能调用
Dbms_stats.set_*_stats过程来手动设置.
其它类型的统计数据
除了基本表,列和索引统计数据之外优化器还使用额外的信息来判断一个语句的执行计划.额外的信息包含动态抽样和系统统计数据.
动态抽样
动态抽样是在oracle database 9i release 2中引入用来在优化一个sql语句时收集额外的语句特定对象的统计数据.最常见的误解是动态抽样可以用来替优化器统计数据.动态抽样的目的是当常规的统计数据不能满足良好的基数评估时来扩大现有的统计数据.
所以怎样和何时使用动态抽样呢.在编译一个sql语句时优化器通过考虑可用的统计数据是否能生成一个良好的执行计划来决定是否使用动态抽样.如果可用的统计数据不满足就会使用动态抽样.它通常是用来弥补由于丢失或无效的统计数据导致一个糟糕的执行计划的情况.对于一个查询中的一个或多个表没有统计数据,优化器在优化语句之前将会使用动态抽样来对这些表生成基本的统计数据.动态抽样收集的统计数据质量不高或者没有使用dbms_stats收集的完整.
第二种使用动态抽样的情况是当语句包含一个复杂的谓词表达式且扩展统计数据不可以使用或者不能使用的时候.例如,一个查询在两个相关列之间使用的不是等号谓词.在这种情况下标准的统计数据不能满足判断,优化器假设每一个谓词将会减少查询所要返回的行数.根据标准的统计数据判断的基数是20197但实际上返回的行数是210420.
SELECT count(*)
FROM sh.Sales
WHERE cust_id < 2222
AND prod_id > 5;
使用标准的统计数据优化器不能识别sales表中cust_id与prod_id之间的关联.通过设置
Optimizer_dynamic_sampling为级别6,优化器将使用动态抽样对复杂谓词表达式收集额外的信息.通过动态抽样提供的额外信息允许优化器生成一个更精确的基数评估因此会选择一个更好的执行计划.
正如你所看到的一样动态抽样是由参数optimizer_dynamic_sampling来控制的,它可能设置为不同的级别(0-10).这些级别控制着两件不同的事;当动态抽样开始时使用多大的抽样大小来生成统计数据.太大的抽样大小,动态抽样对编译查询的时间的影响就会越大.
从oracle database 11g release 2开始,优化器将会自动决定动态抽样是否有用和对于并行执行的sql语句使用什么样的动态抽样级别.这个决定是基于语句中表的大小和谓词的复杂程度.然而如果optimizer_dynamic_sampling参数被显式的设置为一个非缺省值,那么用户指定的值将会被使用.可以查看执行计划的note部分来了解是否使用了动态抽样.例如,如果对sales表启用了并行执行,执行下面的查询,优化器将会自动使用级别为4的动态抽样
对于串行执行的sql语句,动态抽样的级别将根据optimizer_dynamic_sampling参数来决定.将不是由优化器自动触发.原因是串行语句通常运行时间短且在编译时的任何开销都可能影响它的性能.而并行语句会使用更多的资源,所以在编译时产生的额外开销对获得更好的执行计划来说是值得的.
系统统计数据
在oracle database 9i中,系统统计数据引入是为了让优化器通过使用关于执行语句的真实系统硬件信息比如cpu速度和IO性能来更精确的计算出执行计划中每一步的成本.
系统统计数据缺省情况下是启用且缺省值是自动初始化的,这些值代表了大多数系统.当收集系统统计数据时它们将会覆盖这些初始值.为了收集系统统计数据可以在一个有代表性的工作负载时间窗口使用dbms_stats.gather_system_stats,理想的情况是在负载高峰期间收集.
系统统计数据只需收集一次.系统统计数据不是作为自动统计数据收集job的一部分也不会自动被收集.必须使用gahter_system_statistics来更新系统统计数据.
字典表统计数据
因为现在优化器只支持基于成本的优化器,数据库中所有的表都需要有统计数据包括所有字典表(sys,system用户所拥有的表它们存储在system和sysaux表空间中).字典表的统计数据是由在晚上维护窗口运行的自动统计数据收集job来维护的.如果想对应用程序用户关闭自动统计数据收集job任务但是对字典表保留.可以调用dbms_stats.set_global_prefs将autostats_target参数从auto改为oracle.
BEGIN
DBMS_STATS.SET_GLOBAL_PREFS(‘AUTOSTATS_TARGET’,’ORACLE’);
END;
/
字典表的统计数据通过使用dbms_stats.gather_dictionary_stats过程来手动设置.用户必须要有analyze any dictionary和analyze any系统权限或者dba角色来更新字典表统计数据.建议对字典表的统计数据维护与常规用户方案统计数据维护方式保持一致.
固定表的统计数据
需要对动态性能表和它们的索引(固定对象)收集统计数据.它们有x$表和v$视图.因为v$视图可能会象其它的用户表或视图一样出现在sql语句中.对这些表收集统计数据来让优化器生成一个更好的执行计划来说是很重要的.然而不象其它的数据库表,当优化统计数据丢失时不会对语句调用的x$表使用动态抽样.优化器会使用预先定义的缺省统计数据.这些缺省值可能没有代表性且可能会导致选择一个次优的执行计划.在系统中可能会造成一些性能问题.因为这个原因强烈建议收集固定对象的统计数据.
自动统计数据收集job不能对固定对象的统计数据进行收集或维护.可以使用dbms_stats.gather_fixed_objects_stats过程来收集固定对象的统计数据
BEGIN
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
END;
/
这个dbms_stats.gather_fixed_objects_stats过程除了数据块的数量之外与dbms_stats.gather_table_stats过程收集的统计数据是一样的.因为x$表只是在内存结构中不存储到磁盘所在块总是为0.因为x$表的瞬态性很重要当系统中有一个有代表性的工作负载就是对固定对象收集统计数据的时候.用户必须要有analyze any dictionary系统权限或者dba角色来更新固定对象统计数据.当你对数据库或程序进行升级后强烈建议重新收集固定对象的统计数据.
小结
为了让基于成本的优化器精确的判断一个执行计划的成本,它必须有sql语句所访问的所有对象的信息和关于运行这个语句的系统方面的信息.这些必要的信息通常被称作优化器统计数据.理解和管理优化器统计数据是优化sql执行的关键.知道何时以及怎样及时的收集统计数据是维护良好性能的关键.
通过使用自动统计数据收集job和dbms_stats包.dba可以对系统维护一组精确的统计数据来确保优化器将有最好的资源信息来确定语句的执行计划.