求知 文章 文库 Lib 视频 Code iProcess 课程 角色 咨询 工具 火云堂 讲座吧   建模者  
会员   
 
  
每天15篇文章
不仅获得谋生技能
更可以追随信仰
 
 
 
不均衡分区和绑定变量窥视导致的查询计划错误
 

2010-09-14 作者:fuyuncat 来源:HelloDBA.com

 

周一收到生成支持人员的报告,系统上一个作业启动后很长时间没有完成,其执行时间远远大于上周的正常执行时间。接到报告后,首先检查了系统,不存在锁队列的问题。然后查询V$SESSION_LONGOPS,立即发现下面的语句正在进行长操作:

从V$SESSION_LONGOPS看,它正在对表CR_BKG_INTMD_SHMT_PARTITION做FULL TABLE SCAN。而表CR_BKG_INTMD_SHMT_PARTITION是一张非常大的分区表,是我们之前做的优化建立的分区表(该案例我有在《11g新特性 ——更加灵活的分区策略》中提到,Partition Key是COMP_ID,分区策略是每个VIP用户一个分区,所有非VIP用户在DEFAULT分区)。

这条语句的查询条件很简单,且在(BKG_CFM_ID,COMP_ID)上有建一个Global Index。通过直接对其解析查询计划,发现它能正确命中索引:

但是,通过SQL_ID查询,实际的查询计划却是全表扫描:

这一现象通常是由于绑定变量窥视(Bind Variable Peeking)造成的:Peeking的变量值比较特殊,造成计算出的全表扫描代价低于索引扫描代价。为了确认问题,我们找到解析查询计划所“窥视”到的数据:

可以看到,两个变量的值分别为V_BKG_CFM_ID : 592533、V_COMP_ID : 'BANDHK270600815'。使用这2个值,再重新解析查询计划,果然是全表扫描:

注意到在查询条件中存在Partition Key:COMP_ID,因此查询计划中存Partition List Single,仅对所在分区(14)查询。,这里的Full Table Scan实际上是对一个分区的Full Scan,而'BANDHK270600815'正是一个VIP用户。我们再看该分区上的statistics数据:

分区上的数据非常少,因此这个Full Scan的Cost不高,解析出的查询计划为Full Table Scan,当数据落入DEFAULT分区(最大分区),其查询计划仍为Full Scan,从而导致了性能问题!继续查询,发现还存在少数几个分区的数据也非常少。这一结果和当初我们做POC时的有出入:在POC中,所有VIP用户的数据都在10K以上,但生产环境上却出现数据量极少的VIP用户。这一问题恐怕需要从开发那边BA/SA找答案了。作为DBA,我们的当前的任务就是如何避免再次发生由此引发的性能问题。

由于我们的系统是工作日(周一到周五)运行,每周系统都会重启。因此,在周一很多语句都会被硬解析。这样的话,很难避免在硬解析时,窥视的数据再次落入这些小分区内。要避免再次造成性能问题,可以考虑以下方法:

  1. 相关语句上加HINT,强制使用索引。但是这样的修改涉及面太大,且如果将来Schema发生变化,代码维护更新困难;
  2. 用Stored Outline为语句固定查询计划。其缺点和第一点差不多;
  3. 禁用Bind Variable Peeking。因为我们的系统会每周重启,如果在db level禁用,风险较大,所以我们考虑在session level禁用。因为该模块的代码都是通过Package调用的,所以修改的代码量非常少:在入口函数上加上以下语句。

后记

此案例涉及两个问题值得注意:

  1. 分区的平衡问题。如果分区之间的数据量存在很大差异,在绑定变量窥视被启用(默认)的情况下,硬解析出来的查询计划在不同分区上的性能差异可能非常大;
  2. 绑定变量窥视其目的主要是帮助CBO下更加精确的计算出查询计划代价。但是,因为这依赖于被“窥视”的变量值,因而也为查询计划带来了不稳定性。数据的不平衡、分区的不平衡都可能会因为这种不稳定性而导致性能风险。


MySQL索引背后的数据结构
MySQL性能调优与架构设计
SQL Server数据库备份与恢复
让数据库飞起来 10大DB2优化
oracle的临时表空间写满磁盘
数据库的跨平台设计
更多...   


并发、大容量、高性能数据库
高级数据库架构设计师
Hadoop原理与实践
Oracle 数据仓库
数据仓库和数据挖掘
Oracle数据库开发与管理


GE 区块链技术与实现培训
航天科工某子公司 Nodejs高级应用开发
中盛益华 卓越管理者必须具备的五项能力
某信息技术公司 Python培训
某博彩IT系统厂商 易用性测试与评估
中国邮储银行 测试成熟度模型集成(TMMI)
中物院 产品经理与产品管理
更多...   
 
 
 
 
 
每天2个文档/视频
扫描微信二维码订阅
订阅技术月刊
获得每月300个技术资源
 
 

关于我们 | 联系我们 | 京ICP备10020922号 京公海网安备110108001071号