楼主: warehouse

列上"数据特殊"、优化器统计信息的问题导致死活不走index诊断、调整过程

[复制链接]
论坛徽章:
1
2013年新春福章
日期:2013-02-25 14:51:24
发表于 2013-4-16 21:47 | 显示全部楼层
不错,学习学习

使用道具 举报

回复
论坛徽章:
5
雪佛兰
日期:2013-08-13 16:04:59林肯
日期:2013-08-13 18:33:00凯迪拉克
日期:2013-08-26 10:08:512014年新春福章
日期:2014-02-18 16:48:49马上加薪
日期:2014-02-18 16:48:49
发表于 2013-4-16 22:23 | 显示全部楼层
又跟老谢涨知识了。

使用道具 举报

回复
认证徽章
论坛徽章:
11
2012新春纪念徽章
日期:2012-01-04 11:56:442015年新春福章
日期:2015-03-06 11:58:39暖羊羊
日期:2015-03-04 14:53:002014年新春福章
日期:2014-02-18 16:44:08问答徽章
日期:2014-01-02 15:42:02优秀写手
日期:2013-12-18 09:29:11ITPUB社区12周年站庆徽章
日期:2013-08-20 11:30:112013年新春福章
日期:2013-02-25 14:51:24复活蛋
日期:2013-01-25 13:54:43奥运纪念徽章
日期:2012-12-06 09:21:40
发表于 2013-4-16 22:28 | 显示全部楼层
学习

使用道具 举报

回复
论坛徽章:
59
狮子座
日期:2016-03-26 13:35:402013年新春福章
日期:2013-02-25 14:51:24双黄蛋
日期:2013-02-25 11:06:15ITPUB 11周年纪念徽章
日期:2012-10-09 18:06:20灰彻蛋
日期:2012-04-25 13:19:33紫蛋头
日期:2012-03-14 11:16:09最佳人气徽章
日期:2012-03-13 17:39:18玉石琵琶
日期:2012-02-21 15:04:38鲜花蛋
日期:2011-11-30 14:13:01ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15
发表于 2013-4-16 23:36 | 显示全部楼层
呵呵这种东西,只要碰到过一次,以后一眼就知道了
metalink有篇article专门讲optimizer不足的地方

使用道具 举报

回复
论坛徽章:
59
狮子座
日期:2016-03-26 13:35:402013年新春福章
日期:2013-02-25 14:51:24双黄蛋
日期:2013-02-25 11:06:15ITPUB 11周年纪念徽章
日期:2012-10-09 18:06:20灰彻蛋
日期:2012-04-25 13:19:33紫蛋头
日期:2012-03-14 11:16:09最佳人气徽章
日期:2012-03-13 17:39:18玉石琵琶
日期:2012-02-21 15:04:38鲜花蛋
日期:2011-11-30 14:13:01ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15
发表于 2013-4-16 23:42 | 显示全部楼层
[size=130%]有兴趣的可以看下,贴出来了
Limitations of the Oracle Cost Based Optimizer [ID 212809.1]

Cost Based Optimizer LimitationsThe Cost Based Optimizer (CBO) uses a complex and comprehensive model to choose the plan with the lowest cost overall. In most cases the model picks the best access methods for accessing the data in the most efficient manner. However, even with computed statistics and column histograms it is possible for the Cost Based Optimizer to choose a sub-optimal plan. There are limitations to the Cost model that can affect queries in some circumstances. Some of these are listed and explained below:
Details
  • Potential for incorrect estimation of intermediate result set cardinality Cardinality is the CBO estimate of the number of rows produced by a row source or combination of row sources. In some cases, the cardinality of result sets can be miscalculated. This is most common with complex predicates where the statistics do not accurately reflect the data or where predicate values are correlated. The following is an illustration of a statement featuring correlated predicates:
    select ename from emp where sal >= 25000 and job = 'PRESIDENT';
    In this example there is a hidden correlation between the data values in the sal and job columns. Only the president earns more than $25,000. There are no employees with a "sal >= 25000" who are not 'PRESIDENT'. The optimizer has no way of determining that such a correlation exists and would treat the 2 columns as though their values were independent of each other. This would result in an underestimation of the selectivity of the predicates and thus an inaccurate cardinality estimate.

    Note: 11g and above addresses this issue by providing the facility to create extended statistics in the form of column groups . See:

    Note:452883.1 MultiColumn/Column Group Statistics Examples (Doc ID 452883.1)

    This only addresses cases where there is a correlation between columns in the same table. Correlation in different tables is not resolved by this.
  • Assumption that all statements run standalone Since the CBO assumes that all statements run standalone, it may underestimate the volume of data that has been cached as a result of other statements running previously or concurrently. This can result in significant over estimation of the cost of index accesses which can read from cached data rather than having to retrieve the data from disk. The parameters <Parameter:OPTIMIZER_INDEX_CACHING> and <Parameter:OPTIMIZER_INDEX_COST_ADJ> can be used to modify these costs to reflect the characteristics of the system in question.

    Another example of the situation not taken into account by the optimizer is block contention, which occurs at times of heavy concurrent access to the same blocks from SQLs running in other sessions. Such contention may be seen for example as 'buffer busy waits' and/or 'latch free' waits for 'cache buffers chains' latches. At this stage of the CBO evolution it is not clear how the 'run time' database statistics can be accounted by the CBO and whether at all it may be useful.
  • Histogram bucket limitations Histograms are limited to 254 buckets so if there are more than 254 distinct values and there is no single value that dominates the column's dataset then histograms may not provide helpful statistics. With large numbers of distinct values, histogram usage can be further impacted because differences in non popular values cannot be recorded. The choice of 254 buckets per column histogram was a balance between accuracy of the statistics and the speed of histogram collection/amount of space required to store the information.
  • Limitations of Histogram on Character Columns Histograms only store the first 32 characters of a character string (5 characters pre 8.1.6 See Bug:598799 ). If histograms are gathered on character columns that have data that is longer than 31 characters and the first 31 characters are identical then column histograms may not reflect the cardinality of these columns correctly as these values will all be treated as if they are identical.
  • Bind Variables Bind variables are recommended for situations where cursors are frequently executed with different column values and would otherwise cause shared pool fragmentation and contention.

    Historically, the optimizer generated a single access path for an identical shared query featuring bind variables.

    Prior to 9i, the optimizer did not know the value of the bind variable when the plan was determined which could result in a sub-optimal plan, since the 'average' statistic may not reflect the actuality of the data in all cases.

    Since Bind variable peeking was introduced in Oracle 9i, the plan is based upon the first value that is bound to the variable. However, where column data is non-uniform a non-representative value can deliver inconsistent performance for queries using different bind variable values.

    From the optimizer point of view, it is recommended to use literal values where there is the possibility of significant performance differences associated with using different bind variable values and applying the same plan for all executions. In these cases the choice of a good plan usually significantly outweighs the potential usage of shared pool space (assuming that the shared pool usage is not excessive). See:
    Note:70075.1 Use of bind variables in queries
    In later versions, features such as Adaptive Cursor Sharing and SQL Plan Management can be used to manage queries utilizing bind variables in a more flexible manner and make better decisions on what execution plans should and should not be shared. See:
    Note:1359841.1 Plan Stability Features (Including SPM) Start Point

    Note:1115994.1 Introduction to Adaptive Cursor Sharing concepts in 11G and mutimedia demo [Video]
    Note:836256.1 Adaptive Cursor Sharing in 11G
    Note:740052.1 Adaptive Cursor Sharing Overview


  • Subquery Unnesting and View Merging Oracle exhaustively attempts to transform statements containing subqueries and views in to simpler statements. The goal of this activity is to make more access paths accessible and find the optimal plan. However, Subquery Unnesting and View Merging is a heuristic process. Subqueries are unnested and views are merged based upon a set of rules. No costs are generated and compared back to the unchanged statement. This could mean that the transformed statement does not perform as well as the untransformed one.
    N.B. Oracle 10G introduces Costed Subquery Unnesting and View Merging which should go some way to alleviating the effects of this limitation

  • Join Permutations The CBO evaluates each permutation of tables up to a predefined limit (<Parameterptimizer_max_permutations>. As the number of tables involved in a query increases, so the total number of permutations increases. Soon the number that can be evaluated in a realistic timeframe is a minute proportion of the total. There are a large number of adjustments that occur within the optimization process to attempt to minimise the possibility of a good candidate being overlooked, but it is possible that the optimizer may not even consider the most optimal join order and method in large queries. The following article has more detail on optimizer permutations:


    Note:73489.1 Affect of Number of Tables on Join Order Permutations


Potential workarounds for these issues
Workarounds for these issues typically include hinting the correct plan or using stored outlines. Enhancements to alleviate the affects of some of these issues are expected in future versions.


使用道具 举报

回复
求职 : 数据库管理员
认证徽章
论坛徽章:
15
复活蛋
日期:2013-01-11 22:03:44秀才
日期:2015-11-30 09:59:23优秀写手
日期:2013-12-24 06:00:13ITPUB社区千里马徽章
日期:2013-08-22 09:58:03ITPUB社区12周年站庆徽章
日期:2013-08-12 17:41:08迷宫蛋
日期:2013-06-26 10:29:27迷宫蛋
日期:2013-06-24 09:16:43咸鸭蛋
日期:2013-05-17 13:33:14茶鸡蛋
日期:2013-05-09 11:07:43灰彻蛋
日期:2013-04-16 17:22:39
发表于 2013-4-17 08:45 | 显示全部楼层

使用道具 举报

回复
论坛徽章:
29
茶鸡蛋
日期:2013-01-16 10:42:10红孩儿
日期:2014-03-04 16:40:38马上有车
日期:2014-03-27 09:27:03马上加薪
日期:2014-03-27 09:33:52马上有车
日期:2014-04-08 12:28:472014年世界杯参赛球队: 韩国
日期:2014-06-05 09:57:31itpub13周年纪念徽章
日期:2014-09-28 10:55:55itpub13周年纪念徽章
日期:2014-10-08 15:16:50itpub13周年纪念徽章
日期:2014-10-08 15:16:50itpub13周年纪念徽章
日期:2014-10-08 15:16:50
发表于 2013-4-17 09:33 | 显示全部楼层
学习~~

使用道具 举报

回复
论坛徽章:
4
2011新春纪念徽章
日期:2011-02-18 11:43:33ITPUB十周年纪念徽章
日期:2011-11-01 16:24:042012新春纪念徽章
日期:2012-01-04 11:53:542013年新春福章
日期:2013-02-25 14:51:24
发表于 2013-4-17 09:55 | 显示全部楼层
不错 学习了

使用道具 举报

回复
论坛徽章:
20
ITPUB 11周年纪念徽章
日期:2012-10-09 18:16:00法拉利
日期:2013-12-13 16:56:582014年新春福章
日期:2014-02-18 16:44:08马上有对象
日期:2014-02-18 16:44:08马上有房
日期:2014-07-22 17:26:49沸羊羊
日期:2015-02-25 10:40:302015年新春福章
日期:2015-03-04 14:53:162015年新春福章
日期:2015-03-06 11:58:39喜羊羊
日期:2015-03-09 12:20:50法拉利
日期:2013-12-09 09:34:16
发表于 2013-4-17 10:31 | 显示全部楼层
学习了

使用道具 举报

回复
论坛徽章:
41
紫蛋头
日期:2012-11-12 14:45:312014年新春福章
日期:2014-02-18 16:43:09马上有钱
日期:2014-02-18 16:43:092014年新春福章
日期:2014-03-03 14:29:41马上有车
日期:2014-03-20 17:48:31马上加薪
日期:2014-03-28 15:18:022014年世界杯参赛球队: 厄瓜多尔
日期:2014-06-25 18:57:33马上有车
日期:2014-07-29 18:27:14马上有车
日期:2014-08-19 14:32:59itpub13周年纪念徽章
日期:2014-10-08 15:16:50
发表于 2013-4-17 10:40 | 显示全部楼层
nice 学习了

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

SACC2019中国系统架构师大会

【数字转型 架构演进】SACC2019中国系统架构师大会,7折限时优惠重磅来袭!
2019年10月31日~11月2日第11届中国系统架构师大会(SACC2019)将在北京隆重召开。四大主线并行的演讲模式,1个主会场、20个技术专场、超千人参与的会议规模,100+来自互联网、金融、制造业、电商等领域的嘉宾阵容,将为广大参会者提供一场最具价值的技术交流盛会。

限时七折期:2019年8月31日前


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

大会官网>>
TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表