使用TKProf比较实际行数和预测行数 (Doc ID 214106.1)

Using TKProf to Compare Actual and Predicted Row Counts (Doc ID 214106.1)

PURPOSE

Explain how TKProf can be used to highlight differences in predicated and actual row counts to identify potential optimizer problems.   说明如何使用TKProf突出显示行数与实际行数之间的差异，以识别潜在的优化程序问题。

QUESTIONS AND ANSWERS

SQL_TRACE combined with TKProf can provide some very useful information to assist with the tuning of queries when compared against Explain plan output.  与Explain计划输出相比，SQL_TRACE与TKProf结合可以提供一些非常有用的信息，以帮助优化查询。
Especially useful for tuning SQL are the actual row counts as these can be compared against the optimizer's predicted row counts. Expected row counts (cardinalities e.g. Card=12345) can be found in explain plans for queries optimized using the Cost Based Optimizer (CBO). Actual row counts can be found in tkprof output or in the STAT lines from raw <Parameter:sql_trace> (or event:10046 output ). Any anomalies can then be investigated.  
实际的行数对调整SQL特别有用，因为可以将其与优化程序的预测行数进行比较。预期的行数（基数，例如Card = 12345）可以在执行计划中找到，这些执行计划使用基于成本的优化器（CBO）进行了优化查询。实际的行数可以在tkprof输出中或从原始<Parameter:sql_trace> (or event:10046 output )的STAT行中找到。然后可以调查任何异常情况。
For example, if the actual number of rows returned by a particular explain plan step differs significantly from the CBO's cardinality estimates, then it is possible that this is a source of a bad plan choice. The cause of the incorrect statistics can be investigated and corrected.  例如，如果特定的执行计划步骤返回的实际行数与CBO的基数估计有明显差异，则这可能是错误的计划选择的来源。可以对错误统计信息的原因进行调查和纠正。

Consider the following statement:  考虑以下语句

SELECT ename FROM emp e, dept d WHERE e.deptno=d.deptno;

The execution plan from autotrace (containing cost and cardinality information) can be compared with the actual row counts displayed against the raw trace file or the tkprof explain plan:  可以将自动跟踪的执行计划（包含成本和基数信息）与针对原始跟踪文件或tkprof解释计划显示的实际行数进行比较

Autotrace output:

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=14 Bytes=126)
1 0 HASH JOIN (Cost=5 Card=14 Bytes=126)
2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=98)
3 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=4 Bytes=8)

The autotrace output shows the expected cardinality figures (Card=...) for each step in the query. These values are calculated using the statistics gathered on the objects with modifications according to the query predicates. The cardinalities can be compared with the actual figures as collected by SQL_TRACE:  自动跟踪输出显示查询中每个步骤的预期基数数字(Card=...)。这些值是使用收集在对象上的统计信息（根据查询谓词进行修改）来计算的。可以将基数与SQL_TRACE收集的实际数字进行比较

Raw trace file:

STAT #1 id=1 cnt=14 pid=0 pos=1 obj=0 op='HASH JOIN '
STAT #1 id=2 cnt=14 pid=1 pos=1 obj=44913 op='TABLE ACCESS FULL EMP '
STAT #1 id=3 cnt=4 pid=1 pos=2 obj=42480 op='TABLE ACCESS FULL DEPT '

The cnt value in the relevant 'STAT' line for the query shows the row count for each step of the recorded plan (the number immediately after the STAT # indicates the cursor that the statistics refer to). This plan is formatted by TKProf to give the following output:   用于查询的相关'STAT'行中的cnt值显示记录的计划的每个步骤的行数（STAT＃紧随其后的数字表示统计信息所指向的游标）。该计划由TKProf格式化，以提供以下输出：

Formatted tkprof explain plan:

Rows Row Source Operation
------- ---------------------------------------------------
14 HASH JOIN
14 TABLE ACCESS FULL EMP
4 TABLE ACCESS FULL DEPT

The cardinality and row figures for each step should be the same. If they are not then this may indicate thats there is a problem in the way the base statistics have been gathered (no data sampling, insufficient sample size, skewed data etc) or some deficiency in the cardinality computation method. If the differences are causing you performance problems with a particular query then you can use SQLTXPLAIN to investigate this further:  每个步骤的基数和行数应相同。如果不是，则可能表明存在收集基础统计信息的方式存在问题（无数据采样，样本量不足，数据偏斜等）或基数计算方法存在某些缺陷。如果差异导致特定查询的性能出现问题，则可以使用SQLTXPLAIN进行进一步调查：

Document 215187.1 SQLT (SQLTXPLAIN) - Tool that helps to diagnose SQL statements performing poorly

For recommendations regarding statistics gathering see:   有关收集统计信息的建议，请参阅

Document 1369591.1 Optimizer Statistics - Central Point 

The Cost Based optimizer has a few limitations to its model that affects queries in a small number of cases. These limitations are documented in the following article:  基于成本的优化器的模型有一些局限性，在少数情况下会影响查询。这些限制记录在以下文章中

Note:212809.1 Limitations of the Oracle Cost Based Optimizer

REFERENCES

NOTE:43214.1 - AUTOTRACE Option in SQL*Plus
NOTE:62160.1 - Tracing Sessions in Oracle Using the DBMS_SUPPORT Package
NOTE:199081.1 - SQL_TRACE (10046), TKProf and Explain Plan - Overview Reference
NOTE:1369591.1 - Master Note: Optimizer Statistics
NOTE:39817.1 - Interpreting Raw SQL_TRACE output
NOTE:760786.1 - TKProf Interpretation (9i and above)
NOTE:41634.1 - TKPROF Basic Overview