JL Computer Consultancy

Cost Based Oracle: Fundamentals

Chapter 2:  Tablescans


Addenda

Partitioning Precision (11th Feb 2006)

Bind (not) peeking (22nd Nov 2005)

Errata

Sub-partition stats (updated 12th June 2006)

Typos (updated 10th Feb 2006)

Clarification (10th Jan 2006)

cpu_costing / CPU costing (10th Jan 2006)

Tablescans (30th Dec 2005)

io_cost, cpu_cost  (22nd Nov 2005)

 

Addenda

Partitioning Precision

Page 37, para 1: I’ve pointed out that our query has crossed a partition boundary, and that instead of calculating cardinalities for each partition in turn and summing them, the optimizer has simply switched to using the table-level statistics to get its answers.

Boris Dali has pointed out that (in very special cases) you might consider rewriting your queries to help the optimizer find the partition boundaries, and use the partition-level statistics. For example:

    select  count(*)

    from    t1

    where  

        (part_col >= 150 and part_col <  200)

    or  (part_col >= 200 and part_col <= 250)

    ;

Another possibility (avoiding the explicit naming of partitions) would be:

    select  count(*)

    from    (

        select *

        from t1

        where  

            (part_col >= 150 and part_col <  200)

        union all

        select *

        from t1

        where  

            (part_col >= 200 and part_col <= 250)

    )

    ;

Note that very explicitly we have to have “strictly less than” (<) for the first predicate, as this is how partition boundary definitions work.

There is some scope for doing things like this if you have some type of application generator and can control the code automatically. But I’m not sure whether the first strategy is a good idea at present – the numbers are still wrong, and I think the optimizer has used table-level statistics to get them (I haven’t checked properly yet) and the clue that this is the case comes from the fact that whilst the execution plan a concatenation of two explicit partitions, it predicts the same number of rows (9,999) from each. This is the result from dbms_xplan.display() on 9.2.0.6 (note the redundant call to the  lnnvl() function – this is not needed as part_col is declared to be not null).

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

    | Id  | Operation            |  Name       | Rows  | Bytes | Cost  | Pstart| Pstop |

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

    |   0 | SELECT STATEMENT     |             |     1 |     4 |   258 |       |       |

    |   1 |  SORT AGGREGATE      |             |     1 |     4 |       |       |       |

    |   2 |   CONCATENATION      |             |       |       |       |       |       |

    |*  3 |    TABLE ACCESS FULL | T1          |  9999 | 39996 |    65 |     2 |     2 |

    |*  4 |    TABLE ACCESS FULL | T1          |  9999 | 39996 |    65 |     1 |     1 |

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

 

    Predicate Information (identified by operation id):

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

       3 - filter("T1"."PART_COL"<=250)

       4 - filter("T1"."PART_COL">=150 AND (LNNVL("T1"."PART_COL">=200) OR

                  LNNVL("T1"."PART_COL"<=250)))

I think it is worth keeping an eye on this plan, though, as it is possible that future enhancements (or corrections in this case) to the optimizer’s mechanisms for concatenation may result in this plan producing exactly the right answer.

The plan for the second strategy is as follows in 9.2.0.6:

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

    | Id  | Operation            |  Name       | Rows  | Bytes | Cost  | Pstart| Pstop |

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

    |   0 | SELECT STATEMENT     |             |     1 |       |   258 |       |       |

    |   1 |  SORT AGGREGATE      |             |     1 |       |       |       |       |

    |   2 |   VIEW               |             | 40800 |       |   258 |       |       |

    |   3 |    UNION-ALL         |             |       |       |       |       |       |

    |*  4 |     TABLE ACCESS FULL| T1          | 10049 | 40196 |    65 |     1 |     1 |

    |*  5 |     TABLE ACCESS FULL| T1          | 30751 |   120K|   193 |     2 |     2 |

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

 

    Predicate Information (identified by operation id):

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

       4 - filter("T1"."PART_COL">=150)

       5 - filter("T1"."PART_COL"<=250)

As you can see, the operation, and calculations are very good. (In the book I predicted 48,100 rows – so I’ll have to check to see whether I made a mistake).

 There are limitations to this approach, of course, and some cases where predicate pushing may not work, and you probably don’t want to have to tell end-users to do this (after all, it rather defeats the point (and price) of using partitioned tables). But it can work, and in fact for one production system using a 3rd party query generator, I actually pulled exactly this stunt a few years ago. The code generator did silly things with intermediate tables that destroyed the optimizer’s ability to do partition elimination, so I created a partition view which read:

    create or replace view partition_view as

    select * from table partition(pt1) where pt_col < {low2}

    union all

    select * from table partition(pt2) where pt_col >= {low2} and pt_col < {low3}

   

    union all

    select * from table partition(ptM) where pt_col >= {lowM} and pt_col < {lowN}

    union all

    select * from table partition(ptN) where pt_col >= {lowN}

    ;

It’s exactly the trick above – but slightly camouflaged. Specific partitions were eliminated by the effects of predicate pruning in the partition view, then the predicates supplied were further compared with the partition statistics. It wasn’t perfect, but it got the job done.

Of course, you always have to watch out for the upgrade.

Here are the same tests run on 10.2.0.1. Note how the lnnvl() has disappeared, and how 10g replaces the concatenation with a much more appropriate (and new) partition range or. The row counts are still identical (100K, rather than 9,999 – so something odd has happened though).

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

| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

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

|   0 | SELECT STATEMENT    |      |     1 |     4 |   380   (3)| 00:00:05 |       |       |

|   1 |  SORT AGGREGATE     |      |     1 |     4 |            |          |       |       |

|   2 |   PARTITION RANGE OR|      |   100K|   392K|   380   (3)| 00:00:05 |KEY(OR)|KEY(OR)|

|*  3 |    TABLE ACCESS FULL| T1   |   100K|   392K|   380   (3)| 00:00:05 |KEY(OR)|KEY(OR)|

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

 

Predicate Information (identified by operation id):

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

   3 - filter("PART_COL"<200 AND "PART_COL">=150 OR "PART_COL"<=250 AND

              "PART_COL">=200)

And the second plan looks like this under 10.2.0.1. Again the partition information that appears in the operation column is more informative, and tells us very clearly what is going on. Moreover, we see that the two partition range single steps have produced the correct row counts again – but look at the row count on the view line, something has clearly gone wrong again. This looks like one of those cases where it’s nice to have the test scripts ready to hand so that you can discover that there’s a problem waiting to appear when you upgrade.

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

| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

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

|   0 | SELECT STATEMENT          |      |     1 |       |  4685   (2)| 00:00:57 |       |       |

|   1 |  SORT AGGREGATE           |      |     1 |       |            |          |       |       |

|   2 |   VIEW                    |      |  2000K|       |  4685   (2)| 00:00:57 |       |       |

|   3 |    UNION-ALL PARTITION    |      |       |       |            |          |       |       |

|   4 |     PARTITION RANGE SINGLE|      | 10049 | 40196 |    95   (3)| 00:00:02 |     1 |     1 |

|*  5 |      TABLE ACCESS FULL    | T1   | 10049 | 40196 |    95   (3)| 00:00:02 |     1 |     1 |

|   6 |     PARTITION RANGE SINGLE|      | 30751 |   120K|   284   (3)| 00:00:04 |     2 |     2 |

|*  7 |      TABLE ACCESS FULL    | T1   | 30751 |   120K|   284   (3)| 00:00:04 |     2 |     2 |

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

 

Predicate Information (identified by operation id):

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

   5 - filter("PART_COL">=150)

   7 - filter("PART_COL"<=250)

Bind (not) Peeking: 

Page 38, middle of the page: There is a caution about bind peeking not taking place when using the JDBC thin driver with 10g. In fact my reviewers (Christian Antognini and Wolfgang Breitling) spent some time investigating this issue, and came to the conclusion that it was the version of the JDBC driver that was significant, not the version of Oracle.

For further reference, Alberto Dell’Era has also sent me the following reference to a discussion he had had on AskTom about the same issue: http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:19398056075583#48441415218226

 

Back to Book Index

Top of page.


Errata

Sub-Partition stats (12th June)

Page 39, Second paragraph:

The problem of partitions and table-level statistics echoes on down the chain to subpartitions. If you want to query exactly one subpartition of one partition, then the optimizer uses the statistics for that one subpartition.

This is wrong. It looks as if Oracle hardly uses sub-partition statistics at all. I have a test case on 10.2.0.1 which shows the optimizer using the partition level statistics to calculate the cardinality of a very simple query that has been identified as targeting exactly one sub-partition of one partition.  However, the access path is a full table scan of that sub-partition, and the cost of the scan is clearly derived from the number of blocks in the sub-partition.

Thanks to Adrian Billington for bringing this to my attention.

Typos (updated 10th Jan 2006 – so not in page order)

Page 20, Second and third bullet points: I refer to iotrfrspeed in the formula for sreadtim, and then iotftspeed in the formula for mreadtim. Both of these should be iotfrspeed (Thanks to Ramakrishna Menon for picking these up).

Page 10, last line of note, I refer to the script dbms_utl.sql – this should be dbmsutil.sql

Page 21, middle of page, code format: “cost = (1000/4) * 18/12 = ” this should be “cost = (10000/4) * 18/12 = ”  (Thanks to Yong Huang for pointing out this error – and several other also dated 10th Jan).

Page 26, subtitle:  “9I” should be “9i”. It‘s not really worth publishing, but I’m using these pages as a log of details to send to Apress for the reprints.

Page 37, paragraph three: “There are 1,000,000 million rows”.  Not really, there are either 1,000,000 or one million – but not a million million.

Clarification (10th Jan 2006)

Page 21, last paragraph: “Where did the extra 1 come from”.  When I wrote this, it was obvious (to me) what I was talking about. But reading it again several months later, it is far less obvious. The “extra 1” is a reference back to the middle of page 18, where the cost of a tablescan changed from 5031 to 5032 as I changed the size of the db_file_multiblock_read_count to 32.

cpu_costing / CPU costing (10th Jan 2006)

Page 10, last bullet point: “System statistics (cpu_costing) initially disabled.” That should be “System statistics (CPU costing) initially disabled.” And I have made the same mistake elsewhere – for example the middle of page 20, end of page 20, and twice at the top of page 21. “cpu_costing” is very specifically the hint you can use in an SQL statement to make the optimizer use the new mechanism – “CPU costing” is just an alternative way to talk about the use of “System Statistics”.

To add to the confusion, we also have the cpu_cost column in the plan_table. But when Oracle quotes the formula for calculating costs, the value reported in the plan_table changes its name to #CPUcycles in the formula. Finally, I have then compounded the confusion by using the term “CPU cost” to refer to the number you get by using that part of the formula to convert the #CPUcycles into the value that gets added into the total cost of the query.

Tablescans (30th Dec 2005)

Page 14, Effects of Block Sizes, paragraph 1, and Page 19, The I/O Bit, paragraph:  I refer to the hidden parameter _tablescan_cost_plus_one; I have missed an underscore from the name, it should be _table_scan_cost_plus_one. The same error appears in Chapter 9, page 209, paragraph 1.

Thanks to Ramakrishna Menon for spotting this error.

io_cost, cpu_cost: (22nd Nov 2005)

Page 19, top of the page: I have an execution plan where the first line includes the text New(5001,72914400,0). A few lines later I make the comment: The three figures reported as New ( , , ) are the cpu_cost, io_cost, and temp_space of the new cpu_costing algorithm.  The comment is nearly correct, but the two column names cpu_cost and io_cost are in the wrong order.

Thanks to Alberto Dell’Era and Mike Thomas for spotting this one.


Back to Book Index

Top of page.

.