Kaya 发表于 os2ora.com
到了10g的时代，cardinality feedback这个词正在变得越来越流行。我想，开始导致这个词流行的或许不是来自Oracle官方的推广，而是来自Wolfgang Breitling在Hotsos Symposium 2006上的一个演讲: Tuning by Cardinality Feedback: Method and Examples. 从作者的网站有下面的信息:
Tuning by Cardinality Feedback
Tuning by cardinality feedback looks at discrepancies between estimated and real row source cardinalities of an execution plan and attempts to find ways to correct the CBO’s error in estimation and trusting it to find a better plan based on the corrected, more accurate estimates.
Faced with an underperforming SQL, the question the TCF method is trying to answer is not
What would be a better access plan?
But instead
Why is this plan, which the CBO chose as optimal, performing so poorly?Once the answer to that question is found, the next goal is to find a way to remedy the cause for the miscalculation, but ultimately get out of the way and let the CBO do its job again.
The presentation was given at the 2006 Hotsos Symposium on Oracle® System Performance March 5–9, 2006 in Dallas, Texas and at CBO Days June 21-22 in Zurich, Switzerland.
这种方法，也是我们平常调优SQL的最主要方法。虽然以前没有专门介绍过，不过有一些文章还是偶尔提及的，例如这篇: 11gR2出色的SQL Monitor Report, 提到了用SQL Monitor Report查看actual rows与estimate rows的方法。
网上的另一个介绍cardinality feedback的演讲可以参考这一篇，http://jonathanlewis.wordpress.com/2009/05/11/cardinality-feedback/,作者是Michelle Deng，Sanofi Aventis.注意这里已经是2009年的事情了。
看看Oracle内部开发人员的演讲吧，这是VLDB 2008上的一个演讲.
Allison W. Lee, Mohamed Zaït: Closing the query processing loop in Oracle 11g: Paper, Presentation
这俨然成为了11g的一个new feature，如何你手头有Oracle 11g，你甚至现在就可以演练一下。
下面是一个用到了cardinality feedback的执行计划，注意最后一行: cardinality feedback used for this statement
--------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 98089 (100)| | | 1 | SORT ORDER BY | | 1 | 97 | 98089 (1)| 00:19:38 | |* 2 | HASH JOIN | | 1 | 97 | 98088 (1)| 00:19:38 | | 3 | VIEW | VW_NSO_1 | 4 | 36 | 47108 (1)| 00:09:26 | | 4 | SORT UNIQUE | | 4 | 148 | 47108 (51)| 00:09:26 | | 5 | UNION-ALL | | | | | | |* 6 | TABLE ACCESS STORAGE FULL| TA | 2 | 62 | 23549 (1)| 00:04:43 | |* 7 | TABLE ACCESS STORAGE FULL| TA | 2 | 86 | 23558 (1)| 00:04:43 | |* 8 | TABLE ACCESS STORAGE FULL | TB | 469K| 39M| 50978 (1)| 00:10:12 | --------------------------------------------------------------------------------------------------------------- .... Note ----- - cardinality feedback used FOR this statement
可以google下”cardinality feedback”。会有人其它人的演练报告。
想想吧，以后的DBA，回顾这段历史，想想以前的人们如何用手工的方法用cardinality feedback 进行调优，是不是会和我们现在想着过去人们如何用手工方法进行space management, memory management的场景类似？ evolving, evolving, evolving…
最近评论