Oracle 11g新特性

最新推荐文章于 2024-07-25 10:28:16 发布

tannafe

最新推荐文章于 2024-07-25 10:28:16 发布

阅读量6.5k

点赞数

分类专栏： Linux oracle 文章标签： oracle oracle11g sql table 数据库 statistics

Linux oracle 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

Oracle 11g新特性

　　本专题从分区、数据压缩技术、自动内存管理、统计信息收集、 SecureFiles、自适应游标共享六个方面详细介绍了Oracle 11g的新特性：分区就不用说了，正是分区的存在让Oracle高效的处理海量数据成为可能；Oracle从9i开始羞羞答答的引入表级压缩，但实际的贡献就不怎么样，Oracle11g专门推出了一个叫做Advance Compression的组件，全面支持普通表压缩、非结构化数据压缩等各类压缩技术；Auto Memory Management是Oracle10g提出来的一个新特性，在最新的Oracle11g数据库中又得到了进一步的发展；有关收集统计信息方面功能也得到了增强。当然Oracle11g的新特性不止这些，不完整之处希望大家积极发言或来信补充，谢谢！

一、统计信息收集

摘要：本文描述了11g的新特性中，有关收集统计信息方面功能的增强。本文围绕统计信息收集，分别对收集统计信息时可以设置的选项、对合并列收集统计信息，对表达式和函数收集统计信息以及延迟发布统计信息这四个方面做了阐述。

　　1. 设置收集统计信息时的选项

　　我们知道，数据库里的对象的统计信息（statistics）对于优化器得到正确的执行计划来说起着至关重要的作用。因此从10g R1开始，只要使用DBCA安装的数据库，都会自动创建一个job，该job缺省周一到周五每天晚上10点到第二天早上6点（周末则为全天）负责收集数据库所有对象的统计信息。不过，可能存在某些情况，你需要用自己的脚本来收集某些特殊对象的统计信息。但是由于你采用了自动收集统计信息，oracle就会对所有对象使用相同的选项来收集统计信息，这样你就失去了对某个对象的控制权。当你发现缺省的统计信息收集方式对某个对象不是很合适时，你必须锁定该对象的统计信息，并使用一个特殊的选项值对该对象来收集统计信息。

　　比如，某个表的列的数据倾斜（列为某种值的记录行数非常多，而某种值的记录行数又非常少）的非常严重，这时如果采用标准的采样率：ESTIMATE_PERCCENT=AUTO_SAMPLE_SIZE可能就不适合了。这时你就需要单独指定该对象的采样率。我们知道，在11g之前的收集统计信息方面，oracle提供的类似的其他选项还包括：CASCADE、DEGREE、METHOD_OPT、NO_INVALIDATE、GRANULARITY.

　　到了11g里，则提供了更大的灵活性，从而使得你可以很简单的处理上面所说的这种情况。在11g里，上面说的这些选项可以在不同的级别上分别设置，级别由高到低分别为：global级别、数据库级别、schema级别、表级别。其中，低级别的选项覆盖高级别的选项。

　　比如，对于上面所举的例子来说，如果要对你的一个特殊的、列上的值倾斜的很严重的表收集统计信息时，你只需要简单的调用如下的存储过程来设置该表级别上的的ESTIMATE_PERCCENT=100即可，如下所示：

　　SQL> exec dbms_stats.set_table_prefs（'Schema_name'，'Table_name'，'ESTIMATE_PERCCENT'，'100'）；

　　这样设置以后，当数据库在自动收集统计信息时，对于其他没有单独设置采样率的表来说，采样率会采用AUTO_SAMPLE_SIZE，而对于你单独设置的Table_name表，则会使用100的采样率来收集统计信息。

　　类似的，如果需要设置global级别上的选项，则调用dbms_stats.set_global_prefs；如果要设置数据库级别上的选项，则调用dbms_stats.set_database_prefs；如果要设置schema级别上的选项，则调用dbms_stats.set_schema_prefs即可。

　　同时到了11g里，除了上面提到的这些选项以外，还添加了另外三种新的选项：PUBLISH、INCREMENTAL、STALE_PERCENT.其中：

　　1） PUBLISH：收集完统计信息以后是否立即将统计信息发布到数据字典里，还是将它们存放在私有区域里。TRUE表示立即发布，FALSE表示存放到私有区域里。

　　2） STALE_PERCENT：确定某个对象的统计信息过时的上限，如果过时就需要重新收集统计信息，缺省为10.计算某个表的统计信息是否过时，oracle会计算自从上一次收集该表的统计信息以来，该表中被修改的数据行数占该表的总行数的百分比。然后用得出的百分比值与该选项配置的值（如果缺省，就是10）进行比较，大于10，则说明该表的统计信息过时了，需要重新收集统计信息；否则就认为该表的统计信息不过时，不用再次收集。

　　3） INCREMENTAL：在分区表上收集global的统计信息时（将GRANULARITY设置为GLOBAL），采用增量方式完成。使用该选项是因为对于某些分区表来说，比如按照月份进行范围分区的分区表来说，除了代表当前月的分区里的数据会经常变化以外，其他分区里的数据不会变动。因此在收集该分区表上的global的统计信息时，就没有必要再次扫描那些非当前月的分区了。如果你将INCREMENTAL设置为TRUE时，则在收集统计信息时，就不会扫描那些非当前月的分区里的数据，而只会扫描当前月的分区里的数据。最后将非当前月的分区上已经存在的统计信息加上当前月新算出来的统计信息合并就得出了分区表的global的统计信息。

　　可以从视图：DBA_TAB_STAT_PREFS里看到所有的收集统计信息时的各个选项的值。

　　2. 对合并列收集统计信息

　　对于where条件里具有两个列以上的情况，比如where c1=‘A’ and c2=‘B’来说，11g以前优化器评估其selectivity时，总是将每个列的selectivity相乘，从而得到整个where条件的selectiviey.但是如果两个列具有很强的依赖关系，比如汽车制造商与汽车型号这两个列来说，我们知道每个汽车制造商所生产的汽车型号几乎都不会重复，也就是说当你发出where 汽车制造商列=‘XXX’ and 汽车型号列=‘XXX’时，与发出where汽车型号列=‘XXX’时返回的记录行数可能几乎一样。这时如果在计算where条件的selectivity时仍然采用将汽车制造商列的selectivity乘以汽车型号列的selectivity时，就会导致总的selectivity过低，从而导致优化器估计返回的记录行数过少，从而可能导致不正确的执行计划。

为了弥补这样的问题，11g以后可以让你将多个依赖程度很高列合并成一个组，然后对该组收集统计信息。具体如何实现，则可以看下面的例子。

　　select dbms_stats.create_extended_stats（'Schema_name'，'Table_name'，'（C1，C2）'） from dual；

　　通过调用函数dbms_stats.create_extended_stats将两个或多个列合并，并返回一个虚拟的隐藏列的列名，其名字类似于：SYS_STUW_5RHLX443AN1ZCLPE_GLE4.

　　然后，我们可以开始对表收集统计信息，收集完以后，你可以使用ALL|DBA|USER_STAT_EXTIONSIONS视图来查看列组合的统计信息。

　　exec dbms_stats.gather_table_stats（'Schema_name'，'Table_name'）；

　　如果你要对组合列收集直方图，则可以如下所示：

　　exec dbms_stats.gather_table_stats（'Schema_name'，'Table_name'，

　　method_opt=>'for columns （C1，C2） size AUTO'）；

　　3. 对函数以及表达式收集统计信息

　　如果where条件类似于function_name（table_name.column_name）=‘XXX’时，则优化器在估计这样的where条件的selectivity时，总是会假设其selectivity为1％，也就是该where条件将返回table_name里总记录行数的1％的记录行数。很明显的，这种假设肯定是错误的，从而可能导致优化器产生了不够优化的执行计划。

　　从11g开始，我们可以对函数或者表达式收集统计信息了。该特性依赖于虚拟列，也就是说你需要先用dbms_stats.create_extended_stats函数为function_name（table_name.column_name）创建一个虚拟列，然后对该虚拟列收集统计信息。比如下面的例子。

　　select dbms_stats.create_extended_stats（'Schema_name'，'Table_name'，'length（C1）'） from dual；

　　下面则显示的是对表达式来收集统计信息。

　　select dbms_stats.create_extended_stats（'Schema_name'，'Table_name'，'C1*C2'） from dual；

　　然后你可以对表收集统计信息时，就会为函数length（C1）对应的虚拟列收集统计信息了。如果你要对该虚拟列收集直方图，则可以如下所示：

　　exec dbms_stats.gather_table_stats（'Schema_name'，'Table_name'，

　　method_opt=>'for columns （length（C1）） size AUTO'）；

　　_PRIVATE_STATS里看到这些私有的统计信息。

　　为了测试这些私有统计信息，你可以有两种方法：

　　1）第一种方式使用DBMS_STAT.EXPORT_PRIVATE_STATS存储过程将私有统计信息转移到你自己的统计信息表（可以使用存储过程DBMS_STATS.CREATE_STAT_TABLE来创建你自己的统计信息表）里。然后可以使用expdp导出你的统计信息表，然后再使用impdp将导出文件导入到测试环境中，再使用DBMS_STAT.IMPORT_TABLE_STATS将其导入到测试环境中进行测试。

　　2）第二种方式不导出私有的统计信息，而是直接在产品库的session级别，将11g引入的新的初始化参数： OPTIMIZER_PRIVATE_STATISTICS设置为TRUE（缺省情况下该参数为FALSE）。这时你执行SQL时，优化器就会参考私有统计信息来解析SQL语句并生成执行计划了。

　　最后，测试完毕，发现最新的统计信息没有问题的话，你就可以使用DBMS_STAT.PUBLISH_PRIVATE_STATS在产品库上将私用统计信息发布出去，从而让优化器能够看到它们。

　　下面列举一个例子来简单说明这个过程。首先设置表级别的publish选项为false：

　　exec dbms_stats.set_table_prefs（'Schema_name'，'Table_name'，'PUBLISH'，'false'）；

　　然后，收集表的统计信息：

　　exec dbms_stats.gather_table_stats（'Schema_name'，'Table_name'）；

　　第三，设置相关初始化参数：

　　alter session set optimizer_use_private_statistics = true；

　　第四，进行测试，运行相关的SQL语句，并检查产生的执行计划。

　　最后，把该表的统计信息发布出去：

　　exec dbms_stats.publish_private_stats（'Schema_name'，'Table_name'）；

二、数据压缩技术

随着数据量的不断海量，CPU的不断强劲，双核四核的叫个不停，一种叫做时间换空间的优化技术应该会越来越流行。所以，数据压缩对于今后的数据库来说，应该会从核武器变成常规武器。Oracle从9i开始羞羞答答的引入表级压缩，一直以来都是像中国的核电站一样，宣传的用处大，论实际的贡献就不怎么样了。

　　Oracle11g似乎是正儿八经的要推广数据压缩技术了，专门推出了一个叫做Advance Compression的组件，全面支持普通表压缩，非结构化数据压缩（SecureFile数据压缩），Data Pump数据压缩，以及RMAN备份压缩，数据压缩技术从此名正言顺的登上历史舞台。既然是专门做为一个Option推出，Oracle一定是对该特性相当有信心，所以需要单独为该特性购买License.

　　在Oracle9i中虽然引入了表压缩，但是有很大的限制。只能对批量装载操作（比如直接路径装载，CTAS等）涉及的数据进行压缩，普通的DML操作的数据是无法压缩的。这应该是对于写操作的压缩难题没有解决，一直遗留到Oracle11g，总算是解决了关系数据压缩的写性能问题。Oracle的表压缩是针对Block级别的数据压缩，主要技术和Oracle9i差不多，还是在Block中引入symbol表，将block中的重复数据在symbol中用一个项表示。Oracle会对block进行批量压缩，而不是每次在block中写入数据时都进行压缩，通过这种方式，可以尽量降低数据压缩对于DML操作的性能影响。这样，在block级别应该会引入一个新的参数，用于控制block中未压缩的数据量达到某个标准以后进行压缩操作。

　　SecureFile也是Oracle11g新推出的一项特性，用于存储非结构化数据。SecureFile也将支持数据压缩操作。这样对于传统的LOB字段也可以进行压缩，将极大的减少大型数据库的存储空间需求。当然，有得比有失，压缩和解压时，对于CPU的要求也将更高。但是，目前CPU的发展速度明显比IO和存储空间快速的情况下，压缩是大有可为的技术。通过在压缩率和压缩效率方面的不断提升，以后应该为成为各个数据库的标准配置。

　　除了对数据库中的数据进行压缩，Advance Compression Option还将支持备份数据的压缩。做为逻辑备份的Data Pump和物理备份的RMAN工具，都将支持该技术。在Oracle10gR2中，Data Pump已经开始支持压缩源数据，Oracle11g中则可以直接压缩导出文件，这样导出的时候就可以极大的减少存储空间的需求。在以前版本中，利用WinRAR等，经常可以将几个G的导出文件压缩到几十M，Oracle11g的白皮书上说压缩率可以达到74.67%，等软件出来，大家可以好好的测试一把。同样的，Oracle也在10g中开始引入RMAN的压缩技术。但是Oracle11g号称采用了更先进的ZLIB要所算法，可以比Oracle10g的压缩算法快上40%，空间需求也将减少20%.

　　除了上述的数据压缩技术，Oracle 11g Advanced Compression Option还将引入另外一种压缩技术。我们知道在Data Guard中，需要将日志从主库传递到备库。如果主库的事务很多，则单位时间内需要传递的日志量将相当可观。如果能将这些日志压缩后在传递，然后在备库解压后应用，将极大的减少对于网络带宽的需求，从而已减少主备库的时间差。

　　另外，Oracle的bitmap一直就是压缩存储的，10g中的bitmap对于9i就有比较大的改动，通过一些细节的完善，提供更好的性能和更高的稳定性，也是oracle一贯的风格。对于bitmap在Oracle11g中将如何实现，也将是非常值得关注的一个特点。

　　从Oracle11g开始，将没有什么是不可压缩的。使用更强大的CPU，就可以降低或者延缓对存储空间无休止的渴求，或许很多大型OLTP和大多数的数据仓库，都将从数据压缩技术中收益。

三、分区

Partition（分区）一直是Oracle数据库引以为傲的一项技术，正是分区的存在让Oracle高效的处理海量数据成为可能，在即将发布的Oracle 11g中，分区技术在易用性和可扩展性上再次得到了增强。

　　1. Interval Partitioning

　　在我曾经的一个项目中，由于数据量的巨大，所以表设计为每一个小时一个分区，数据库管理员日常要做的一件重复而无聊的工作就是每隔一天要生成新的24个分区，用以存储第二天的数据。

　　而在11g中这项工作可以交由Oracle自动完成了，基于Range和List的Interval Partitioning分区类型登场。

CREATE TABLE TB_INTERVAL 
PARTITION BY RANGE (time_col) 
INTERVAL(NUMTOYMINTERVAL(1, 'month')) 
(PARTITION P0 VALUES LESS THAN (TO_DATE('1-1-2007', 'dd-mm-yyyy')));

　　指定需要Oracle自动创建分区的间隔时间，上面这个例子是1个月，然后至少创建一个基本分区，上面这个例子是在2007-1-1之前的所有数据都在P0分区中，以后每个月的数据都会存放在Oracle自动创建的一个新分区中。

　　目前还没有更多的资料显示Oracle如何控制每个新分区的属性，比如存放在哪个表空间中，自动创建的数据文件有多大，如果是数据文件是裸设备如何处理，当第一条跨分区的记录插入时实时创建分区效率如何，虽然这些仍然是未知数，但是我们不得不承认这是一个人性化的进步。

　　2. System Partitioning

　　又一个人性化的分区类型，系统分区，在这个新的类型中，我们不需要指定任何分区键，数据会进入哪个分区完全由应用程序决定，实际上也就是由SQL来决定，终于，我们在Insert语句中可以指定插入哪个分区了。

　　假设我们创建了下面这张分区表，注意，没有指定任何分区键：

CREATE TABLE systab (c1 integer, c2 integer) 
PARTITION BY SYSTEM 
( 
PARTITION p1 TABLESPACE tbs_1, 
PARTITION p2 TABLESPACE tbs_2, 
PARTITION p3 TABLESPACE tbs_3, 
PARTITION p4 TABLESPACE tbs_4

　　现在由SQL语句来指定插入哪个分区：

　　—— 数据插入p1分区

　　INSERT INTO systab PARTITION （p1） VALUES （4，5）；

　　—— 数据插入第2个分区，也就是p2分区

　　INSERT INTO systab PARTITION （2） VALUES （7，8）；

　　—— 为了实现绑定变量，用pno变量来代替实际分区号，以避免过度解析

　　INSERT INTO systab PARTITION （：pno） VALUES （9，10）；

　　由于System Partitioning的特殊性，所以很明显，这种类型的分区将不支持Partition Split操作，也不支持create table as select操作。

　　3. More Composite Partitioning

　　在10g中，我们知道复合分区只支持Range-List和Range-Hash，而在在11g中复合分区的类型大大增加，现在Range，List，Interval都可以作为Top level分区，而Second level则可以是Range，List，Hash，也就是在11g中可以有3*3=9种复合分区，满足更多的业务需求。

　　4. Virtual Column-Based Partitioning

　　Virtual Column是11g中的一个新功能，这种列中的数据并不实际存储于磁盘上（我们可以看成是一个类似Function的列），只有当读取的时候才实时计算。暂时不讨论性能问题，这个功能还是比较有意思的。

　　可以通过这样的语句来创建虚拟列。

CREATE TABLE tb_v 
(col_1 number(6) not null, 
col_2 number not null, 
… 
col_v as (col_1 *( 1+col_2));

　　虚拟列虽然没有实际的存储空间，但是却可以跟其他普通列一样，创建索引，作为分区键，甚至可以收集统计信息，这是让人感觉有意思的地方，一个实时计算的Function如何创建索引呢？等到11g推出的时候就真相大白了。

四、内存管理

Auto Memory Management是 Oracle10g提出来的一个新特性，在最新的 Oracle11g数据库中又得到了进一步的发展。

　　通过使用自动内存管理，Oracle数据库中的PGA和SGA内存之间可以互相转换，根据当前的工作负载来自动设定Oracle内存区域中的PGA和SGA的大小。这种间接的内存转换依赖于操作系统的共享内存的释放机制来获得内部实例的调优。目前这种技术可以应用于Linux， Solaris， HPUX， AIX 和Windows等操作系统上。

　　首先我们来回顾下Oracle10g的自动内存管理特性。在Oracle10g的数据库中，只有SHARED_POOL_SIZE、DB_CACHE_SIZE、LARGE_POOL_SIZE、JAVA_POOL_SIZE、STREAMS_POOL_SIZE五个SGA组件可以被自动调整，其中PGA的最大值由初始化参数PGA_AGGREGATE_TARGET决定，SGA的最大值由初始化参数SGA_TARGET决定。

　　在Oracle11g数据库中，使用自动内存管理特性不再需要设定参数PGA_AGGREGATE_TARGET和SGA_TARGET，因为这两个参数都已经被修改成自动调优的，除非想指定PGA和SGA的最小值才需要设定这两个参数。

　　在Oracle11g数据库中，则需要设置一个叫做MEMORY_TARGET的初始化参数，这个参数是指整个Oracle实例所能使用的内存大小，包括PGA和SGA的整体大小，在MEMORY_TARGET的内存大小之内，PGA和SGA所用的内存可以根据当前负载情况自动相互转换。

　　如果当初始设定的MEMORY_TARGET的内存不够当前数据库使用的时候，Oracle11g还提供了另外一个初始化参数MEMORY_MAX_TARGET，当原始设定的内存不够使用的时候，可以手工来动态调节MEMORY_TARGET的大小，但是不允许超过MEMORY_MAX_TARGET的值。

　　此外，Oracle11g数据库还提供了几个用于监控自动内存管理的视图：

　　V$MEMORY_DYNAMIC_COMPONENTS：描述当前所有内存组件的状态

　　V$MEMORY_RESIZE_OPS：循环记录最后800次的SGA大小调整请求

　　X$KMGSTFR：循环记录最后800次的SGA的转换地址

　　_MEMORY_MANAGEMENT_TRACING=23：对于所有的内存转换调整行为均记录保存为跟踪文件

五、SecureFiles

　 Oracle11g数据库新推出了一个完全重新设计的大对象（LOB）数据类型用于大幅度的提高性能、可管理性和简化应用的开发。这个全新的数据类型同时还提供了高级的下一代功能例如智能压缩和透明加密等功能。SecureFiles提供了如下的组件：

　　压缩：使得可以明确地压缩SecureFiles来节省磁盘空间，I/O和记录重做日志的开销。

　　数据路径优化：支持针对于SecureFiles的性能优化，包括

　　动态设置是否使用缓存，并且避免污染对于高速缓存中的大的缓存的SecureFiles.

　　利用事务COMMIT NOWAIT BATCH来达到同步或者异步。

　　类似于文件服务器的脏缓存写，写缓存收集能够自动分期偿还空间分配、信息节点更新，记录重做日志的代价，并且允许磁盘大规模的I/O读写。

　　针对于SecureFiles块的分布式锁管理器，这使用一个单一的分布式锁管理器来覆盖所有的SecureFilesLOB块，因此对于其他的文件系统来说LOB的性能会更好一些。

　　不重复：自动检测重复的SecureFiles LOB数据并且只保留一份拷贝空间来实现节省磁盘存储，I/O和重做日志记录的消耗。不重复操作可以在表级或者是分区级别上指定，但是不能跨越分区的LOBs.

　　加密：加密存储LOB数据并且可以任意的读和写来提供更强大的数据安全性。

　　信息节点：针对于SecureFiles的新的存储架构被设计和实现用于支持高性能的访问目标数据。除了提高基础数据访问之外，新的存储架构也支持丰富的功能，保证最小的性能代价，这些功能例如：

　　固有的压缩和加密

　　数据共享

　　用户控制的版本

　　COMPATIBLE初始化参数比较要设置为11.1或者更高才可以使用SecureFiles.

　　通过设置初始化参数DB_SECUREFILE来确定是否启用SecureFiles，这个参数可以设置为以下几个值：

　　PERMITTED：允许创建SecureFiles（默认）

　　NEVER：不允许创建SecureFiles

　　ALWAYS：强制所有的LOBs都必须要创建SecureFiles

　　IGNORE：不允许创建SecureFiles并且忽略由强制BasicFiles使用SecureFiles特性而引起的任何错误。

　　如果指定值为NEVER，任何LOBs都将以BasicFiles来创建，所有的指定的SecureFiles的存储选项和特性都将引起一个意外。

　　如果指定值为ALWAYS，所有系统中的LOBs都以SecureFiles来创建，LOB必须在ASSM（Automatic Segment Space Management）表空间中创建，否则会产生错误。任何指定的BasicFile存储选项都将会被忽略。

　　下面是一个创建Securefiles的例子：

      
      
       
       CREATE TABLE func_spec(
id number, doc CLOB ENCRYPT USING 'AES128' )
LOB(doc) STORE AS SECUREFILE
( DEDUPLICATE LOB CACHE NOLOGGING );

可以使用ALTER TABLE table_name MODIFY来修改当前的Securefiles的各种属性，例如：
ALTER TABLE t1 MODIFY LOB(a) ( DEDUPLICATE LOB );

　　使用此命令能修改的Securefiles属性如下：

　　DEDUPLICATE/KEEP_DUPLICATES： DEDUPLICATE选项允许指定在一个LOB列中有两行或者更多行的时候，所有行都共享同样的数据块。相反的就是KEEP_DUPLICATES选项。Oracle11g数据库使用一个安全的哈希索引来检测重复性并且联合LOBs的内容到一个单一的拷贝里面，减少存储空间并且简化存储管理。

　　VALIDATE：对SecureFiles执行一个字节到字节的比较，确定SecureFiles都是同样的安全哈希值。

　　COMPRESS/NOCOMPRESS：决定是否启用LOB压缩。

　　ENCRYPT/DECRYPT：决定是否启用LOB加密。如果一旦设置好了加密，就只能用ALTER TABLE REKEY命令来更新加密算法或者是加密值。

　　RETENTION：只能影响使用ALTER TABLE语句之后所创建的空间。

　　对于Securefiles的访问，Oracle11g数据库是通过使用DBMS包来实现的。主要是通过DBMS_LOB包和DBMS_SPACE包来访问。

　　DBMS_LOB包：LOBs继承LOB列的设定的属性，当然也可以通过LOB locator API配置成每个LOB级别上。但是LONG API不能用于配置这些LOB设定，必须使用DBMS_LOB包来设定这些属性：

　　DBMS_LOB.GETOPTIONS：可以用来获取设定，每个选项类型的预定义的一个常量对应于一个整数将会返回。

　　DBMS_LOB.SETOPTIONS：用于设定特性并且允许在每一个LOB基础上设定特性，会覆盖所有默认的LOB设定。

　　DBMS_LOB.GET_DEDUPLICATE_REGIONS：这个存储过程用于输出在一个LOB中不重复区域的记录收集。LOB级别上只包含一个不重复区域。

　　DBMS_SPACE.SPACE_USAGE：这个存储过程用于返回LOB空间使用的负载信息。返回在LOB段中所有LOB使用的磁盘空间的blocks的数量。这个存储过程目前只能用于ASSM创建的表空间，不支持属于BasicFiles那部分LOB使用的空间。

　　对于从BasicFiles移植SecureFiles，有两种推荐的方法来移植。这两种方法分别是交换分区和在线重定义。

　　交换分区：

　　需要和表中最大的分区相等的额外空间

　　在交换的时候可以维护索引

　　能够在几个小的维护窗口延展工作负载

　　执行交换分区的时候需要将表或者分区离线

　　在线重定义：

　　不要求表或者分区离线

　　可以被并行的执行

　　要求额外的等于整个表或者所有LOB段大小的可用空间

　　要求必须重建所有的全局索引

　　如果你想升级你的BasicFiles到SecureFiles，你可以使用传统的方法来升级数据，例如CTAS/ITAS、export/import、column-to-column拷贝等等。大部分这些解决方案都要求使用两倍的磁盘空间来移植。然而，使用分区解决方案来移植可以按照每个分区来移植，从而有助于减少磁盘空间的需求。

六、自适应游标共享

任何SQL语句优化引擎的核心是判定获得SQL语句目标数据的最廉价和最有效的访问路径的能力，使用绑定变量可以限制需要存储在库缓存中返回相似结果集的SQL语句游标的数量。本文介绍 Oracle 11g中的新特性 – 自适应游标共享 – 并说明实际情况中该如何使用它，以及如何限制带有绑定变量的SQL语句的硬解析，同时提高这些语句的执行效率。

　　正如我过去做老师时告诉学生的那样，我有一个好方法可以避免数据库性能降低，说来也很简单，就是要求应用程序开发人员不要在数据库运行任何SQL语句，但这的确引来不少人的笑声和嘲笑，还是回到现实中来吧，讨论一下建立有效SQL语句的途径，我认为没有什么标准可供借鉴，但构造不佳的SQL语句迟早会导致联机事务处理(OLTP)系统、决策支持系统(DSS)或混合数据库表现不佳。

“偷窥”绑定变量：这不是作弊

　　幸运的是，Oracle数据库为我们提供了一些很好的工具，用以确定需要改善性能的SQL语句，并为提升它们的性能提供建议，这个方法的核心是当确实需要一个新的执行计划时，我们可以修改基于成本的SQL优化器解析SQL语句的方式。当然，当一条语句第一次执行时必须硬解析，这样优化器可以确定获取所需数据的最佳路径，因为解析是一个相对费时的操作，因此，DBA通常会限制存储在库缓存中的唯一性游标的数量，特别是在联机事务处理环境下，相同的语句可能会被执行成百上千次，它们为用户会话返回相似的结果集。

　　设置有效的游标共享最简单的方法就是按照应用程序工作量的需要为CURSOR_SHARING初始化参数设定合适的值，将这个参数的值设为SIMILAR告诉优化器当SQL语句完全相同，除了谓词部分外，可以使用游标共享，执行计划提供相等或更优的性能，同样，将其值设为CURSOR_SHARING时，不论是否存在更好的执行计划，告诉优化器强制共享游标，当SQL语句包含绑定变量时，有极好的机会提供这方面的性能优势，但当优化器在不知道是什么值来填充产生的游标时，怎样才能构建一个有效的执行计划呢?

　　早在Oracle 9i就进行了一些尝试，为了克服可能出现的非最佳的执行计划，引入了绑定变量偷窥，顾名思义，当一个包含绑定变量的SQL语句首次执行时，Oracle会快速查看这些绑定变量的一个真实值，以便构建一个最佳的执行计划，这个方法的优点很明显：不再是猜测最佳的执行计划了，因为那样产生的执行计划可能并不是最佳的，基于成本的优化器使用真实的值来构建执行计划。

　　但不幸的是，这个方法对于非OLTP系统的缺点也很明显，例如，如果决策支持系统下次运行的查询指定了一套绑定变量的值，那此时要想高效地执行查询，需要一个完全不同的执行计划吗?实际上，这种数据仓库环境并不罕见，对于决策支持系统，它可能非常希望有多个可用的执行计划作为候选，因为一个绑定变量集可能返回的结果集只包含几百行的数据，而另一套绑定变量可能返回几百万行数据，因此，Oracle建议保留CURSOR_SHARING作为该初始化参数的默认值，以强制产生一个新的更有效的执行计划。

自适应游标共享：更灵活的绑定

　　Oracle 11g提供自适应游标共享(ACS)以克服不该共享时的游标共享，ACS使用了两个新的度量机制：绑定敏感度和绑定感知。

　　绑定敏感度：无论何时，当包含绑定变量的SQL语句首次执行时，优化器在偷窥了绑定变量的值后，会为其标记一个绑定敏感度，以确定语句的谓词，但偷窥结束时也类似，因为它也为后面相同语句相同绑定变量不同值时进行对比，以确定是否要产生新的执行计划。

　　为了说明这些绑定敏感度是如何工作的，我在Oracle 11g的SH示例方案中的SH.SALES表上构造了一个简单的查询，因为它是方案中最大的表了，并且也按时间范围进行分区，如列表1所示：

-- 清空缓冲去缓存和共享池

　　ALTER SYSTEM FLUSH SHARED_POOL;

　　ALTER SYSTEM FLUSH BUFFER_CACHE;

　　--声明绑定变量

　　VARIABLE cust_start NUMBER;

　　VARIABLE cust_end NUMBER;

　　VARIABLE time_start DATE;

　　VARIABLE time_end DATE;

　　VARIABLE total_sold NUMBER;

　　VARIABLE total_qty NUMBER;

　　-- 测试#1

　　BEGIN

　　:cust_start := 2;

　　:cust_end := 38;

　　:time_start := '01 JAN 1998';

　　:time_end := '31 MAR 1998';

　　SELECT

　　SUM(amount_sold)

　　,SUM(quantity_sold)

　　INTO

　　:total_sold

　　,:total_qty

　　FROM sh.sales

　　WHERE cust_id BETWEEN :cust_start AND :cust_end

　　AND time_id BETWEEN :time_start AND :time_end;

　　END;

　　这个查询使用了四个绑定变量来确定SH.SALES.TIME_ID和SH.SALES.CUST_ID的开始和结束范围。

　　自适应游标共享元数据：Oracle 11g提供了三个新的视图，并在v$sql视图中添加了两个新列以便让Oracle DBA确定优化器是否已经决定SQL语句是否适合自适应游标共享，优化器使用业务规则将SQL语句的执行计划进行分类以便共享：

在列表2中我在这些视图上构造一些简单的查询及格式化输出。

　　列表2

　-- 目的：显示优化器选择哪个SQL语句进行自适应游标共享

　　TTITLE 'SQL Statements With Bind Sensitivity Enabled|(from V$SQL)'

　　COL sql_id FORMAT A16 HEADING 'SQL ID'

　　COL hash_value FORMAT 99999999999 HEADING 'Hash|Value'

　　COL plan_hash_value FORMAT 99999999999 HEADING 'Plan|Hash|Value'

　　COL iba_flag FORMAT A06 HEADING 'Bind|Aware?'

　　COL sql_text FORMAT A80 HEADING 'SQL Text'

　　SELECT

　　sql_id

　　,hash_value

　　,plan_hash_value

　　,is_bind_sensitive ibs_flag

　　,is_bind_aware iba_flag

　　,sql_text

　　FROM v$sql

　　WHERE ((is_bind_sensitive <> 'N') OR (is_bind_aware <> 'N'))

　　ORDER BY hash_value

　　;

　　TTITLE OFF

　　--目的:显示当前自适应游标共享元数据的分布情况

　　TTITLE 'Histograms for Adaptive Cursor Sharing|(from V$SQL_CS_HISTOGRAM)'

　　COL hash_value FORMAT 99999999999 HEADING 'Hash|Value'

　　COL sql_id FORMAT A16 HEADING 'SQL ID'

　　COL child_number FORMAT 9999 HEADING 'Chld|#'

　　COL bucket_id FORMAT 9999 HEADING 'Bckt|ID#'

　　COL count FORMAT 999999 HEADING 'Exec-|ution|Count'

　　SELECT

　　hash_value

　　,sql_id

　　,child_number

　　,bucket_id

　　,count

　　FROM v$sql_cs_histogram

　　;

　　TTITLE OFF

　　-- 目的:显示使用了自适应游标共享的游标执行统计情况

　　TTITLE 'Selectivity Metrics for Adaptive Cursor Sharing|(from V$SQL_CS_STATISTICS)'

　　COL hash_value FORMAT 99999999999 HEADING 'Hash|Value'

　　COL sql_id FORMAT A16 HEADING 'SQL ID'

　　COL child_number FORMAT 9999 HEADING 'Chld|#'

　　COL bind_set_hash_value FORMAT 99999999999 HEADING 'Hash|Value'

　　COL peeked FORMAT A05 HEADING 'Peek?'

　　COL executions FORMAT 999999 HEADING '# of|Exec-|utions'

　　COL rows_processed FORMAT 999999 HEADING '# of|Rows'

　　COL buffer_gets FORMAT 999999 HEADING 'Buffer|Gets'

　　COL cpu_time FORMAT 999999 HEADING 'CPU|Time'

　　SELECT

　　hash_value

　　,sql_id

　　,child_number

　　,bind_set_hash_value

　　,peeked

　　,executions

　　,rows_processed

　　,buffer_gets

　　,cpu_time

　　FROM v$sql_cs_statistics

　　;

　　TTITLE OFF

　　-- 目的:显示自适应游标共享决定两个不同绑定变量的游标是否要创建新的执行计划的选择性度量

　　TTITLE 'Selectivity Metrics for Adaptive Cursor Sharing|(from V$SQL_CS_SELECTIVITY)'

　　COL hash_value FORMAT 99999999999 HEADING 'Hash|Value'

　　COL sql_id FORMAT A16 HEADING 'SQL ID'

　　COL child_number FORMAT 9999 HEADING 'Chld|#'

　　COL range_id FORMAT 9999 HEADING 'Rng|ID#'

　　COL low FORMAT A12 HEADING 'Low Value'

　　COL high FORMAT A12 HEADING 'High Value'

　　COL predicate FORMAT A80 HEADING 'Predicates'

　　SELECT

　　hash_value

　　,sql_id

　　,child_number

　　,range_id

　　,low

　　,high

　　,predicate

　　FROM v$sql_cs_selectivity

　　;

　　TTITLE OFF

　　我将在本文剩下的部分中使用到它们以说明自适应游标共享是如何工作的，此外，在列表3中我显示了在这个元数据上第一次执行这个语句的影响。

　　列表3：

　　第一次这个语句被硬解析后，它的游标自动被标记为绑定敏感，但还没有绑定感知，查询的绑定变量值在第一次执行期间被放在自适应游标共享三个直方图的中间位置。

　　绑定感知：一旦SQL语句的游标被标记为绑定敏感，优化器可能还会决定将其视为绑定感知，优化器是通过检查提供给绑定变量的值是否与相同查询后面的执行计划匹配来实现的，如果优化器决定它可以使用现有的执行计划，那就只需要更新游标执行直方图以反应语句的执行情况，换句话说，如果绑定变量值发了重大变化，优化器可能会决定创建一个全新的子游标和执行计划，如果是这样的话，Oracle 11g也会存储自适应游标共享元数据中的子游标的相对选择性。

　　我觉得它有助于把这些选择性评级作为“电子云”或影响范围的中心点，Oracle文档了使用的术语是“选择性立方体”，在随后游标的执行过程中，优化器会使用游标最近执行的统计信息与现有的选择性统计信息进行比较，如果它观察到大多数执行都使用系统的选择性范围，游标将会被标记为绑定感知。

我用同一个查询的另外两次执行来说明了这个概念，但使用了完全不同的绑定变量，如列表4所示：

列表4：

　-- Execution #2

　　-----

　　BEGIN

　　:cust_start := 42999;

　　:cust_end := 50000;

　　:time_start := '01 JAN 1997';

　　:time_end := '31 MAR 1998';

　　SELECT

　　SUM(amount_sold)

　　,SUM(quantity_sold)

　　INTO

　　:total_sold

　　,:total_qty

　　FROM sh.sales

　　WHERE cust_id BETWEEN :cust_start AND :cust_end

　　AND time_id BETWEEN :time_start AND :time_end;

　　END;

　　/

　　-----

　　-- Execution #3

　　-----

　　BEGIN

　　:cust_start := 1000;

　　:cust_end := 1400;

　　:time_start := '01 JAN 1996';

　　:time_end := '31 MAR 1997';

　　SELECT

　　SUM(amount_sold)

　　,SUM(quantity_sold)

　　INTO

　　:total_sold

　　,:total_qty

　　FROM sh.sales

　　WHERE cust_id BETWEEN :cust_start AND :cust_end

　　AND time_id BETWEEN :time_start AND :time_end;

　　END;

　　为查询游标指定的自适应游标共享元数据产生的变化显示在列表5中。

　　列表5：

　SQL Statements With Bind Sensitivity Enabled

　　(from V$SQL)

　　Plan Bind

　　Hash Hash Sensi- Bind

　　SQL ID Value Value tive? Aware? SQL Text

　　---------------- ------------ ------------ ------ ------

　　87qtpurhk664g 3777173647 2855975716 Y Y SELECT SUM(AMOUNT_SOLD) ,SUM(QUANTITY_SOLD) FROM SH.SALES WHERE CUST_ID BETWEEN

　　:B4 AND :B3 AND TIME_ID BETWEEN :B2 AND :B1

　　87qtpurhk664g 3777173647 787661731 Y N SELECT SUM(AMOUNT_SOLD) ,SUM(QUANTITY_SOLD) FROM SH.SALES WHERE CUST_ID BETWEEN

　　:B4 AND :B3 AND TIME_ID BETWEEN :B2 AND :B1

　　Histograms for Adaptive Cursor Sharing

　　(from V$SQL_CS_HISTOGRAM)

　　Exec-

　　Hash Chld Bckt ution

　　Value SQL ID # ID# Count

　　------------ ---------------- ----- ----- -------

　　3777173647 87qtpurhk664g 1 0 1

　　3777173647 87qtpurhk664g 1 1 0

　　3777173647 87qtpurhk664g 1 2 0

　　3777173647 87qtpurhk664g 0 0 1

　　3777173647 87qtpurhk664g 0 1 1

　　3777173647 87qtpurhk664g 0 2 0

　　Selectivity Metrics for Adaptive Cursor Sharing

　　(from V$SQL_CS_STATISTICS)

　　# of

　　Hash Chld Hash Exec- # of Buffer CPU

　　Value SQL ID # Value Peek? utions Rows Gets Time

　　------------ ---------------- ----- ------------ ----- ------- -------

　　3777173647 87qtpurhk664g 1 1601990286 Y 1 1 2 0

　　3777173647 87qtpurhk664g 0 4302390 Y 1 1098 3178 0

　　Selectivity Metrics for Adaptive Cursor Sharing

　　(from V$SQL_CS_SELECTIVITY)

　　Hash Chld Rng

　　Value SQL ID # ID# Low Value High Value Predicates

　　------------ ---------------- ----- ----- ------------ ------------

　　3777173647 87qtpurhk664g 1 0 0.000616 0.000753 <=B1

　　3777173647 87qtpurhk664g 1 0 0.900000 1.100000 >=B2

　　3777173647 87qtpurhk664g 1 0 0.109520 0.133858 <=B3

　　3777173647 87qtpurhk664g 1 0 0.821710 1.004312 >=B4

注意，Oracle 11g已经为hash值为2855975716的SQL语句创建了新的子游标，不将它们都标记为绑定敏感和绑定感知，元数据中为这些游标指定的选择性度量值也更新了。

当绑定变量的值超出了现有绑定感知游标影响的范围时，执行包含这个绑定变量的查询会发生什么？在语句的硬解析期间，优化器可能只会选择扩大选择范围，以包括新的绑定值，这是通过创建新的子游标结合这两套绑定变量值，然后删除旧的、范围小的游标来实现的，显然，这样只会产生几个的确需要的几个子游标。

那么如何激活这一新功能呢？好消息是在Oracle 11g中默认就已经启动了，它完全与CURSOR_SHARING初始化参数无关，这大大增加了在OLTP/DSS系统中SQL语句使用绑定变量的机会。

对SQL计划管理（SPM）的影响：如果你读过我之前写的SQL计划管理方面的文章，你可能会疑惑自适应游标共享是否会影响SQL计划管理捕获和保存SQL执行计划到SQL管理基础库中的功能，下面列出它们之间交互的摘要信息：

如果初始化参数OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES被设置为TRUE以激活自动捕获执行计划，那么带有绑定变量的SQL语句也会被标记为启用和接收执行计划。

如果同一个语句构建了第二个执行计划 – 并不是自适应游标共享 – 那么该计划只会简单地添加到语句的计划历史中，但它不会立即被使用，因为SPM首先会要求校验这个新的执行计划。

不幸的是，这意味着一个很好的执行计划会被忽略，解决这个问题的一个好办法是将自动捕获计划设置为FALSE，然后在库缓存中将所有子游标捕获到SMB中，这样将会强制所有子游标的计划被标记为SQL计划基线。

结语

Oracle 11g的新特性自适应游标共享为包含有绑定变量的SQL语句有效共享执行计划提供了一个更简单的方法，但只有绑定变量有值时才有意义，自适应游标共享有时也会产生新的执行计划，但共享的游标会保持相对小的数量。

我用同一个查询的另外两次执行来说明了这个概念，但使用了完全不同的绑定变量，如列表4所示：列表4：

tannafe

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
1
评论
Oracle 11g新特性

Oracle 11g新特性　　本专题从分区、数据压缩技术、自动内存管理、统计信息收集、 SecureFiles、自适应游标共享六个方面详细介绍了Oracle 11g的新特性：分区就不用说了，正是分区的存在让Oracle高效的处理海量数据成为可能；Oracle从9i开始羞羞答答的引入表级压缩，但实际的贡献就不怎么样，Oracle11g专门推出了一个叫做Advance Compressio
复制链接

扫一扫

专栏目录