为什么AUTOTRACE不会在执行计划中显示分区截断（partition pruning）？

The Oracle (tm) Users' Co-Operative FAQ

Why does AUTOTRACE not show partition pruning in the explain plan ?

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

Author's name: Norman Dunbar

作者：Norman Dunbar

Author's Email: Oracle (at) BountifulSolutions.co.uk

Date written: 25 March 2004

写作日期：2004年3月25日

Oracle version(s): 9.2.0.3.0

Oracle版本： 9.2.0.3.0

Why is it that when I use AUTOTRACE in SQL*Plus, the explain plan never shows partition pruning taking place?

为什么当我在SQL*Plus中使用AUTOTRACE时，执行计划从不显示发生了分区截断呢？

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

Autotrace not showing partition pruning/elimination is bug 1426992, but, after investigation Oracle has decided that this is not an optimiser bug, but a bug in SQL*Plus. You can, with a bit of knowledge of your data and a little experimentation, deduce that partition pruning is taking place from the output of autotrace, but there are much easier ways !

AUTOTRACE不显示分区截断/是错误（BUG）1426992，但调查后Oracle发现这不是优化器的错误，而是SQL*Plus的问题。对数据有所了解并经过一点试验后，你可以根据AUTOTRACE的输出推断出发生了分区截断，但有更简单的方法！

The following demonstration shows the failings in autotraceand demonstrates a couple of other methods of determining whether or not your partitions are being pruned - or not.

下面的演示显示了AUTOTRACE不能显示时用其他方法判断是否发生了分区截断。

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

Autotrace

AUTOTRACE

First of all, create a simple table range partitioned over 6 different partitions, and fill it with some test data extracted from ALL_OBJECTS.

首先创建一个简单的根据区间分为6个分区（range partitioned）的表，并从ALL_OBJECTS填充一些测试数据。

SQL> create table tab_part (part_key number(1), some_text varchar2(500))

2 partition by range (part_key) (

3 partition part_1 values less than (2),

4 partition part_2 values less than (3),

5 partition part_3 values less than (4),

6 partition part_4 values less than (5),

7 partition part_5 values less than (6),

8 partition part_6 values less than (MAXVALUE) );

Table created.

SQL> insert /*+ append */ into tab_part

2 select mod(rownum, 10), object_name

3 from all_objects;

24683 rows created.

SQL> commit;

Commit complete.

Once the table has been filled, analyse it and see how the data has been spread over the various partitions. The first and last partitions have more data in them that the remaining four, hence the differing totals.

一旦表中填入数据，分析并查看数据如何在不同的分区分布。第一和最后的分区比其他四个分区有更多的数据。

SQL> analyze table tab_part compute statistics;

Table analyzed.

SQL> select partition_name, num_rows

2 from user_tab_partitions

3 where table_name = 'TAB_PART'

4 order by partition_name;

PARTITION_NAME NUM_ROWS

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

PART_1 4937

PART_2 2469

PART_3 2469

PART_4 2468

PART_5 2468

PART_6 9872

6 rows selected.

Now that we have a table to work with, we shall see what autotrace has to say about partition elimination. First, however, note how many logical reads a full scan of the entire table needs :

现在我们有了一个试验表，来看看AUTOTRACE是如何处理分区截断的。不过首先，注意全表扫描所需的逻辑读：

SQL> set autotrace on

SQL> select count(*) from tab_part;

COUNT(*)

----------

24683

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=42 Card=1)

1 0 SORT (AGGREGATE)

2 1 PARTITION RANGE (ALL)

3 2 TABLE ACCESS (FULL) OF 'TAB_PART' (Cost=42 Card=24683)

Statistics

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

0 recursive calls

0 db block gets

135 consistent gets

0 physical reads

0 redo size

381 bytes sent via SQL*Net to client

499 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

To read 24,683 rows of data Oracle had to perform 135 logical reads. Keep this in mind and note that the autotrace output shows a full table scan - as we would expect on an unindexed table. The next count should only look in a single partition :

为了读取24,683行数据，Oracle进行了135次逻辑读。记住这些，并注意AUTOTRACE输出显示了一个全表扫描——正如对于一个无索引表，我们所预期的。下面的COUNT只应当搜索一个单独的分区：

SQL> select count(*) from tab_part where part_key = 7;

COUNT(*)

----------

2468

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=17 Card=1 Bytes=2)

1 0 SORT (AGGREGATE)

2 1 TABLE ACCESS (FULL) OF 'TAB_PART' (Cost=17 Card=2468 Bytes=4936)

Statistics

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

0 recursive calls

0 db block gets

49 consistent gets

0 physical reads

0 redo size

380 bytes sent via SQL*Net to client

499 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

This seems to have again carried out a full table scan, but as we already know that a real FTS takes 135 logical reads, the fact that only 49 were required here should indicate that something is different. Autotrace's output is not showing partition elimination. If you didn't know how many reads were required to full scan the table, you would be hard pressed to determine that partition elimination had taken place in this scan.

看起来又一次进行了全表扫描，但正如我们所知，一次真正的FTS需要135次逻辑读，事实上这里需要的49次说明有所改变了。AUTOTRACE的输出没有显示分区截断。如果你不知道全表扫描需要的读（次数），你很难判断这里的搜索发生了分区截断。

Event 10053

事件10053

There are other methods by which we can obtain a true picture of the plan used by the optimiser - a 10053 trace for example would show the details. I've never had to use a 10053 trace so I'm unfortunately not in a position to explain its use, I leave this as 'an exercise for the reader' as they say :o)

我们还有其他方法来获得优化器所用计划的全景——例如，一次10053跟踪将显示出详细信息。我从来都不是必须10053跟踪，所以很不幸我也不适合来解释它的用法，我把它留下来作为“读者的一个练习” :o)

SQL_TRACE and TKPROF

I have used SQL_TRACE and TKPROF though, so here's what shows up when SQL_TRACE is set true.

SQL_TRACE与TKPROF

我用SQL_TRACE与TKPROF，下面是当SQL_TRACE设为TRUE时的输出。

SQL> set autotrace off

SQL> alter session set sql_trace = true;

Session altered.

SQL> alter session set tracefile_identifier = 'PARTITION';

Session altered.

SQL> select count(*) from tab_part where part_key = 7;

COUNT(*)

----------

2468

SQL> alter session set sql_trace = false

Session altered.

At this point, exit from SQL*Plus and locate the trace file in USER_DUMP_DEST which has 'PARTITION' in it's name. This is the one you want to run through TKPROF. The output from this is shown below :

此时，退出SQL*Plus并根据USER_DUMP_DEST来定位名字含有“PARTITION”的跟踪文件。运行TKPROF来解释这个文件，输出如下：

select count(*) from tab_part where part_key = 7

call count cpu elapsed disk query current rows

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

Parse 1 0.00 0.00 0 0 0 0

Execute 1 0.01 0.00 0 0 0 0

Fetch 2 0.01 0.01 0 49 0 1

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

total 4 0.02 0.01 0 49 0 1

Misses in library cache during parse: 0

Optimizer goal: CHOOSE

Parsing user id: 62

Rows Row Source Operation

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

1 SORT AGGREGATE (cr=49 r=0 w=0 time=10353 us)

2468 TABLE ACCESS FULL TAB_PART PARTITION: 6 6 (cr=49 r=0 w=0 time=6146 us)

The explain plan clearly shows that partition 6 was the start and stop partition in the scan. In addition, there were 49 logical reads performed to get at the count. This is identical to what we saw above with autotrace, except we get to see that partition pruning did actually take place.

执行计划清楚的显示了分区6是扫描的起始和结束分区。并且，进行了49次逻辑读。除了真实的看到分区截断的确发生了，这与我们在上面用AUTOTRACE的结果一致。

Explain Plan

执行计划

Back in SQL*Plus, there is another method that can be used. The old faithful EXPLAIN PLAN will show how partition pruning did take place.

回到SQL*Plus，还可以用另一种方法。古老而忠实的EXPLAIN PLAN将显示分区截断是如何发生的。

SQL> explain plan

2 set statement_id = 'Norman'

3 for

4 select count(*) from tab_part where part_key = 7;

Explained.

SQL> set lines 132

SQL> set pages 10000

SQL> col operation format a20

SQL> col options format a15

SQL> col object_name format a15

SQL> col p_start format a15

SQL> col p_stop format a15

SQL> col level noprint

SQL> select level,lpad(' ', 2*level-1)||operation as operation,

2 options,

3 object_name,

4 partition_start as p_start,

5 partition_stop as p_stop,

6 cardinality

7 from plan_table

8 where statement_id = 'Norman'

9 start with id=0

10 connect by prior id=parent_id

11 order by level

OPERATION OPTIONS OBJECT_NAME P_START P_STOP CARDINALITY

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

SELECT STATEMENT 1

SORT AGGREGATE 1

TABLE ACCESS FULL TAB_PART 6 6 2468

Once again, the plan clearly shows that partition pruning takes place. The problem is that autotrace doesn't show it at all. Unless you really know how many blocks of data you have in a table and all of its partitions, you may find it difficult to determine whether or not you are seeing a 'true' plan when using partitioned tables and autotrace.

计划再次清楚的显示发生了分区截断。问题是AUTOTRACE不显示。除非真的知道表中有多少数据块与所有的分区，你会发现使用分区表和AUTOTRACE确实很难确定“真正”的计划。

Note: Do you ever suffer from the PLAN_TABLE growing too big as developers fail to delete old rows from the table? Alternatively, do you forget to delete rows from the table?

注意：有没有为PLAN_TABLE增长太快而开发人员不从表中删除旧行而痛苦？或者，你是否忘记从表中删除记录？

Take a copy of $ORACLE_HOME/rdbms/admin/utlxplan.sql and edit it.

复制一份$ORACLE_HOME/rdbms/admin/utlxplan.sql并编辑：

Change this :

修改：

create table PLAN_TABLE (

statement_id varchar2(30), ...

filter_predicates varchar2(4000));

To this :

为：

create global temporary table PLAN_TABLE (

statement_id varchar2(30), ...

filter_predicates varchar2(4000))

on commit preserve rows;

Now login to SQL*Plus as SYS and :

现在以SYS登陆SQL*Plus，并：

sql> @?/rdbms/admin/utlxplan_edited /* Or whatever your copy is called */

sql> grant all on plan_table to public;

sql> create public synonym PLAN_TABLE for SYS.PLAN_TABLE;

Now when developers or DBAs use PLAN_TABLE and logout their rows will be deleted. A self-tidying PLAN_TABLE. Of course, this is no good if you want to keep rows in PLAN_TABLE between sessions.

现在当开发人员或DBA们使用PLAN_TABLE并登出时，他们的记录将被删除。一个自我清洁的PLAN_TABLE。当然，如果你需要在会话间保留PLAN_TABLE中的记录就不行了。

DBMS_XPLAN

Under Oracle 9i (release 2 I think) there is a new PL/SQL package which you can use to show explain plans. The above statement could have its plan shown using this command :

在Oracle 9i（我想是Release 2）中，有一个新的PL/SQL包可以用于显示执行计划。上面的语句可以用如下指令显示计划：

SQL> Select * from table(dbms_xplan.display(statement_id=>'Norman'));

or, if this was the only statement in my PLAN_TABLE :

或者，如果这时我PLAN_TABLE中唯一的语句：

SQL> Select * from table(dbms_xplan.display);

There is much more information shown with this new feature than with a 'normal' explain plan and you don't have to worry about all that formatting either.

其中比“正规”的执行计划显示了更多信息，并且你也不必为格式化操心。

Summary

总结

In summary, autotrace doesn't show partition elimination in Oracle up to versions 9i release 2. You should therefore be aware of this fact and use SQL_TRACE or EXPLAIN_PLAN to get at the true plan for the SQL you are trying to tune/debug.

AUTOTRACE到Oracle 9iR2为止不显示分区截断。所以你应当注意这个事实并使用SQL_TRACE或EXPLAIN_PLAN来获得你需要调整的SQL的真正的计划。

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