如何减少共享池的碎片.docVIP

如何减少共享池的碎片 在SGA中，由shared_pool_size参数控制共享池的大小，共享池主要是sql area、library cache等部分组成，每部分所耗用的内存可以通过v$sgastat字典表查看，如果想了解共享池碎片是如何产生的，可以参考eygle的贴子（/internal/shared_pool-1.htm）。 理解了共享池的碎片是如何产生的，那么就可以想办法消除它。通常共享池碎片产生的原因很多，而且有些版本的oracle的bug也可能引起共享池的碎片，如果是由bug引起的，解决办法请参照eygle的这个贴子（/internal/shared_pool-6.htm），其它方面的原因大概有以下方面： 1 不良的数据库设计，导致数据库对象众多 2 非bind var的SQL，导致共享池过度交换 3 由cursor_sharing参数及histogram引起的 关于第1个原因，处理起来难度较大，风险也高，因为涉及到修改数据库结构，需要修改的地方就比较多，这样做首先要取得公司的支持，然后对数据库进行良构设计，消除结构上不太合理的地方。 例如偶公司的业务系统是从mysql迁移到oracle中的，有一些很重要的产品表以前是这样设计的game_card_gmxxxx，由于最初产品不是很多，所以矛盾并不突出，但随着业务的扩展，产品表从几十个增加到几百个，问题就随着来啦，每个表有6个索引，光产品表相关的对象就上千个（含每个产品表的sequence），庞大的对象体系必然增加了共享池（sql area、library cache等）的消耗，如果数据库结构重新设计的话，一个表/六个索引/一个序列就可以满足啦，可以极大地减少shared_pool的争用。 非bind var的SQL导致共享池中的SQL无限地aged out，被aged out的SQL大致可分为两种：可重用的或不可重用的。如DDL、DCL、DML（INSERT/DELETE/UPDATE）这些99%是不可以重用的，aged out后几乎不会被再次reload；但SELECT语句是可以重用的，如果大量的SELECT被换进换出就会影响数据库的性能。 图一 Library Cache Activity 如图一所示，显示SQL AREA被Reloads共20w次左右，每个SQL在共享池中需要分配20k来计算，SGA以外的内存中将需要提供320m左右的空间给这些被aged out出来的SELECT语句；至于不可重用的SQL可以按业务数据的增长计算出它们使用SGA以外内存的数量。如果你的statspack报表中关于Library Cache Activity的统计项中SQL AREA中存在Reloads值较多，那就需要考虑使用bind var，因为这部分只是SELECT语句的部分，关于DML这部分SQL被aged out后使用SGA之外部分内存就没有量化的标准，如果你的业务数据量增加较大的话，那被aged out 的INSERT肯定很多，所以需要对数据量增加较多的表或数据查询很频繁的SQL使用bind var。在常用的SQL中使用bind var，其本质就是通过减少共享池的使用来减少共享池的碎片。 由cursor_sharing参数及histogram引起的执行计划恶化问题，大家需要先了解一下cursor_sharing参数的意义。它共有三个可选值:exact、force、similar，exact是表示cursor共享文本量必须相同，是默认值；force与similar是控制cursor共享的参数，由于force在控制cursor共享时对histogram（柱状图）不敏感，oracle才提供similar参数增加cursor共享时对histogram的敏感性；以下是在上的测试结果： 图一 cursor_sharing等于exact时 如图一所示，创建test4表用于测试，添加10000条记录，然后查看cursor_sharing参数的取值，在exact下共享cursor必须确保SQL文本是全部相同的，虽然查询id = 1与id = 2只有细微的差别，却在共享池中保存了两个语法分析树。如图二所示，当cursor_sharing等于force时，虽然文本量不同，该SQL却共享了id = 3的SQL的执行计划，当然，其它类似的SQL也将可以共享id = 3的执行计划。 图二 cursor_sharing等于force时 图三 cursor_sharing等于similar时 图四 cursor_sharing等于similar时（已分析histogram） 如图三所示，cursor_sharing等于similar时，也体现了cursor_sharing=forc