THE ORACLE (TM) Users' Co-Operative Faqwhy Does Autotrace Not Show Partition In The Explain Plan? Why does AutoTrace displays partition trunning in the execution plan? -------------------------------------------------- ------------------------------
Author's name: Norman Dunbar Author: Norman DunbarAuthor's Email: Oracle (at) BountifulSolutions.co.uk Date written: 25 March 2004 Writing Date: March 25, 2004 Oracle version (s): 9.2.0.3.0Oracle Version: 9.2. 0.3.0Why IS IT That WHEN I Use Autotrace In SQL * Plus, The Explain Plan Never Shows Partition Pruning Taking Place? Why is the execution plan never showed a partition truncation when I use autotrace in SQL * Plus?
-------------------------------------------------- ------------------------------
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 does not display partition truncation / error (bug) 1426992, but after the investigation Oracle found this is not an optimizer's error, but SQL * Plus The problem. After the data is understood and after a test, you can infer the interception of the partition according to the output of AutoTrace, but there is a simpler method! 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 following demonstration shows the other method of determining whether a partition can not be displayed AUTOTRACE truncation occurs.
-------------------------------------------------- ------------------------------
AutotraceAUTOTRACEFirst of all, create a simple table range partitioned over 6 different partitions, and fill it with some test data extracted from ALL_OBJECTS. According to first create a simple interval is divided into six partitions (range partitioned) table, and some of the filling from ALL_OBJECTS Test Data. SQL> CREATE TABLE TAB_PART (Part_key Number (1), Some_Text Varchar2 (500)) 2 Partition By Range (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 once. The data is filled in data, analyzes and views how data is distributed in different partitions. The first and last partitions have more data more than the other four partitions.
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 4937PART_2 2469PART_3 2469PART_4 2468PART_5 2468PART_6 98726 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: Now we have a test table to see how AutoTrace handles the partition truncation. But first, pay attention to the logical reading you need for full mete: SQL> Set Autotrace OnSql> 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 Clom Client 2 SQL * Net RoundTrips To / from client 0 sorts (memory) 0 sorts (disk) 1 rows processedTo 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 INLY LOOK IN A SINGLE Partition: In order to read 24,683 row data, Oracle has 135 logically read. Remember these, and note that AutoTrace output shows a full metric scan - just as a non-indeed list, we expect. The following count should only search a separate partition: 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 processedThis 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 'keto h m m YOULD BE HARD PRESSED TO DETERMINE THAT PARTITION ELIMINATION HAD TAKEN Place In this scan, it seems to have a full table scan, but as we know, a real FTS needs 135 logically read, in fact, the 49 needed here The explanation has changed. AutoTrace's output does not have a district truncation. If you don't know the full table scanning needs to be read (number of times), it is difficult to judge that the search has occurred in the partition. Event 10053 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 the it: o) We have all other ways to get panoramic views of the program used by the optimizer - for example, one 10053 tracking will display more information. I have never had to track 10053, so I am very unfortunate. I am not suitable for explaining its usage. I will leave it as "an exercise of readers": o) SQL_TRACE AND TKPROFI HAVE USED SQL_TRACE AND TKPROFIH, SO Here's What Shows Up when sql_trace is set true.sql_trace with tkprof I use SQL_TRACE with TKPROF, below is the output when SQL_TRACE is set to TRUE.
SQL> SET Autotrace Offsql> 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 = falseSession 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: At this point, exiting SQL * Plus and locate the name containing "partition" tracking files based on user_dump_dest. Run TKPROF to explain this file, the output is as follows:
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 0Ecute 1 0.01 0.00 0 0 0 0Fetch 2 0.01 0.01 0 49 0 1------- ------ -------- ---------- ---------- -------- - ---------- ---------- Total 4 0.02 0.01 0 49 0 1Misses in Library Cache During Parse: 0Optimizer Goal: ChooseParsing 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) Time = 6146 US) The Explain Plan Clearly Shows That Partition 6 Was The Start and Stop Partition in The Scan In AdduL, There Were 49 Logical Reads P ERFORMED TO GET AT THE SAW ABOVE IDTRACE, Except We Get To See That Partition Pruning Did Actual Take Place. The execution plan is clearly displayed. Partition 6 is the start and end partition of the scan. And, 49 logically reads were performed. In addition to real see the interception of the partition, this is consistent with us on the results of AutoTrace. Explain Plan Executive Plan Back In SQL * Plus, There IS Another Method That Can Be Used. The Old Faithful Explain Plan Will Show How Part Proning Did Take Place. Back to SQL * PLUS, you can also use another method. The old and faithful Explain Plan will show how the partition truncation occurs.
SQL> explain plan 2 set statement_id = 'Norman' 3 for 4 select count (*) from tab_part where part_key = 7; Explained.SQL> set lines 132SQL> set pages 10000SQL> col operation format a20SQL> col options format a15SQL> col object_name format a15SQL> col p_start format a15SQL> col p_stop format a15SQL> col level noprintSQL> 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 levelOPERATION OPTIONS OBJECT_NAME P_START P_STOP CARDINALITY ------------- ------------------------------------- - --------------- ----------- Select Statement 1 Sort Aggregate 1 TABLE ACCESS FULL TAB_PART 6 6 2468Once again, the plan clearly shows that partition pruning takes place. The problem is that autotrace does not 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. The program has been cut off again. The problem is AutoTrace not displayed. Unless you really know how much data blocks are in the table and all partitions, you will find that the partition table and autotrace do difficult to determine the "real" plan.
NOTE: DO you ever suffer from the plan_table growth Too BIG AS DEVELOPERS FAIL TO DELETE OLD ROWS from THE? AlternaTively, do you forget to delete rows from the table? Note: Is there any growth for Plan_Table too fast and developers? Delete the old line and pain? Or, have you forgotten to delete a record from the table? Take a copy of $ oracle_home / rdbms / admin / utlxplan.sql and edit it. Copy a $ ORACLE_HOME / RDBMS / Admin / UTLXPLAN.SQL and Edit: Change this: Modify:
Create Table Plan_Table (Statement_ID VARCHAR2 (30), ... filter_predicates varcha2 (4000)); To this:
Create Global Temporary Table Plan_Table (Statement_ID VARCHAR2 (30), ... filter_predicates varcha2 (4000)) on commit preserve rows; now Login to SQL * Plus as sys and: SQL * Plus is logged into SQL * Plus now, 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 you want to keep rows in plan_table between sessions. Now that their records will be deleted when developers or DBAs use Plan_Table and log out. A self-cleaning plan_table. Of course, if you need to keep the record in Plan_Table in the session, you can't.
DBMS_XPLANUnder 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:. In Oracle 9i (I think Release 2), the There is a new PL / SQL package to display the execution plan. The above statement can be displayed with the following instructions:
SQL> Select * from table (statement_id => 'Norman')); or, if this was the only statement in my plan_table: or, if this is the only statement in 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 do not have to worry about all that formatting either wherein the ratio of "regular". The execution plan shows more information, and you don't have to worry about formatting. Summary summary
In summary, autotrace does not 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 The partition truncation is not displayed until Oracle 9iR2. So you should pay attention to this fact and use SQL_Trace or Explain_PLAN to get the true plan of the SQL you need to adjust.
-------------------------------------------------- ------------------------------
FURTHER Reading: Further reading:
Note: 166118.1 on metaLink. You will Need A Support Contract to Access Metalink. Note: 166118.1 Partition Pruning / Elimination On Metalink. You need a support contract to access Metalink.
Bug: 1426992 SQLPlus Autotrace Does Not Show Correct Explain Plan for Partition Elimination. Again On Metalink. Bug: 1426992 SQLPLUS AutoS. Not Show Correct Explain Plan for Partition Elimination. Or in Metalink.
-------------------------------------------------- ----------------------------- This article is translated from http://www.jlcomp.deMon.co.uk/faq/autotrace_pruning .html translator only retains the translation copyright
