ORA FAQ 性能调整系列之——为什么AUTOTRACE不会在执行计划中显示分区截断(partition pruning)?

原创 2004年09月28日 22:31:00

The Oracle (tm) Users' Co-Operative FAQ
Why does AUTOTRACE not show partition pruning in the explain plan ?
为什么AUTOTRACE不会在执行计划中显示分区截断(partition pruning)?

Author's name: Norman Dunbar
作者:Norman Dunbar
Author's Email: Oracle (at) BountifulSolutions.co.uk
 Date written:  25  March 2004
Oracle version(s):
Why is it that when I use AUTOTRACE in SQL*Plus, the explain plan never shows partition pruning taking place?



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 !
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.


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 :

SQL> set autotrace on
SQL> select count(*) from tab_part;

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)

          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 :

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

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)
          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.

Event 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)

I have used SQL_TRACE and TKPROF though, so here's what shows up when SQL_TRACE is set 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;

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 :

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.

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;
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.

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?

Take a copy of $ORACLE_HOME/rdbms/admin/utlxplan.sql and edit it.
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 :

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.

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 :

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.


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的真正的计划。


Further reading:

Note: 166118.1 Partition Pruning/Elimination on Metalink. You will need a support contract to access Metalink.
注意:166118.1 Partition Pruning/Elimination on Metalink. 你需要一个支持合同来访问Metalink。

Bug: 1426992 SQLPlus AUTOTRACE does not show correct explain plan for partition elimination. Again on Metalink.
Bug: 1426992 SQLPlus AUTOTRACE does not show correct explain plan for partition elimination. 还是在Metalink。

本文翻译自http://www.jlcomp.demon.co.uk/faq/autotrace_pruning.html  译者仅保留翻译版权


  • zgqtxwd
  • zgqtxwd
  • 2008年04月27日 02:54
  • 150


Partition Pruning在数据仓库中分区修剪是一种非常有效的性能特性。分析修剪分析SQL中的WHERE 和FROM字句,从而在查询中消除不不必要分区。分区修剪技术能大大的减少从磁盘上读取的数...
  • sopost
  • sopost
  • 2010年12月21日 16:11
  • 1536

分区修剪(Partition Pruning)

Partition Pruning Partition pruning is an essential performance feature for data warehouses. In...
  • u013169075
  • u013169075
  • 2016年11月12日 19:35
  • 304


分区表有很多好处,以大化小,一小化了,加上并行的使用,在loap中能往往能提高几十倍甚至几百倍的效果。当然表设计得不好也会适得其反,效果比普通表跟糟糕。 为了更好的使用分区表,这里看一下分区表的执行计...
  • gdmzlhj1
  • gdmzlhj1
  • 2014年12月18日 09:37
  • 1160

oracle 配置autotrace监控sql执行计划

获取执行计划和执行成本,写出最优的sql语句,是每个oracle开发人员和dba必须具备的能力之一。 oracle 11g以后已经默认为dba role自动打开autotrace? ...
  • Dream19881003
  • Dream19881003
  • 2011年08月12日 11:27
  • 1643


何在SQL*PLUS中使用Autotrace? 如何在SQL*PLUS中使用Autotrace?sqlplus system or sys as sysdbaSQL> @?/rdbms/admin/u...
  • annicybc
  • annicybc
  • 2006年06月27日 14:27
  • 1615

plsqldeveloper命令行Cannot SET AUTOTRACE 开启执行计划失败

一、问题描述: 今天想看一下SQL的执行计划,在PL/SQL的command窗口中输入set autotrace on时,报Cannot SET AUTOTRACE的错误。 二、解决办法:...
  • weixin_39415084
  • weixin_39415084
  • 2017年07月28日 16:25
  • 8726


今天在调试sparksql的时候,查询分区信息, SELECT  T.PROD_INST_ID      , T.PROD_ID           , T.STATUS_CD       , ...
  • sydt2011
  • sydt2011
  • 2017年03月04日 09:22
  • 208

表分区问题 ORA-02149: Specified partition does not exist

1. 第一次接触表分区,做了些练习,遇到一个超级恶心的问题。 --查看某个表的表分区 select TABLESPACE_NAME,partition_name, High_value from ...
  • u012881151
  • u012881151
  • 2016年10月26日 19:17
  • 1047


要优化,就得看懂执行计划,要看懂执行计划,首先要能显示多种格式的执行计划,而不仅仅是看图形的执行计划,因为文本的执行计划更细,能看出更多的问题。 1、设置显示简单格式的执行计划,不执行sql...
  • yupeigu
  • yupeigu
  • 2016年12月10日 14:36
  • 626
您举报文章:ORA FAQ 性能调整系列之——为什么AUTOTRACE不会在执行计划中显示分区截断(partition pruning)?