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
寫作日期：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的真正的計劃。
--------------------------------------------------------------------------------
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 譯者僅保留翻譯版權
