JL Computer Consultancy

Cost Based Oracle: Fundamentals

Chapter 1: What do you mean by “cost”?


Addenda

First_rows (27th Feb 2006)

view_merge_01.sql (10th Jan 2006)

Cost is time (30th Dec 2005)

Errata

 

Addenda

First_rows (27th Feb 2006)

Page 2, first bullet point. I comment that first_rows as an optimization mode is deprecated in 9i but maintained for backwards compatibility. This is based (amongst other details) on the comments in Performance Tuning Guide and Reference 9.2 (A96533-01) page 1-12:

The optimizer uses a mix of cost and heuristics to find a best plan for fast delivery of the first few rows. Note: Using heuristics sometimes leads the CBO to generate a plan with a cost that is significantly larger than the cost of a plan without applying the heuristic. FIRST_ROWS is available for backward compatibility [my emphasis] and plan stability.

This is not an official “statement of deprecation”, and does not carry the force of (for example) the following quote from the Performance Tuning Guide and Reference 10g Release 1 (B10752-01) page xxxi:

The AND_EQUAL, HASH_AJ, MERGE_AJ, NL_AJ, HASH_SJ, MERGE_SJ, NL_SJ, EXPAND_GSET_TO_UNION, ORDERED_PREDICATES, ROWID, and STAR hints have been deprecated [my emphasis] and should not be used.

Given that this isn’t an explicit statement of deprecation, I would be very surprised if anyone in Oracle told you they weren't going to fix a real bug with first_rows optimization because the feature is only there for backward compatibility. But I wouldn't be surprised if it took longer to fix the bug than it would to fix an equivalent bug in first_rows_N – after all, any code path that is pure first_rows has probably been frozen and fewer and fewer people will be familiar with it as time passes.  Nor would I be surprised if you found an optimization issue in first_rows and got told that it was expected behaviour that wasn't going to be changed, with the suggestion that the best workaround would be to migrate to first_rows_1, and the explanation that first_rows is only there for backwards compatibility.

Bottom line(s):

        If you are still running rule-based, you should definitely not pick first_rows as your choice for cost-based optimization.

        If you are running 8i in first_rows mode still planning to upgrade to 9i or 10g, you should switch to first_rows_1 or first_rows_10 with CPU costing enabled at the same time. The upgrade is likely to give you so many optimization headaches anyway that you might as well get into the newer technology at the same time.

        If you are running 9i in first_rows without CPU costing, with the intention of enabling CPU costing or upgrading to 10g, then, again, I would include first_rows_1 or first_rows_10 as part of the strategic target.

        If you are running an existing application on 9i or 10g with CPU costing and using first_rows, I wouldn’t panic about it; but I would consider attempting the switch to first_rows_1 or first_rows_10 at your next major release and get it checked out at the same time as all the other regression testing.

view_merge_01.sql (10th Jan 2006)

Page 5, Transformation and Costing. I introduce a script called view_merge_01.sql to demonstrate the concept and consequences of complex view merging as you move through the versions of Oracle. I’ve just had an email from Joseph Amalraj who finally tested this script against Oracle10.2, and came up with the surprise execution plan of:

----------------------------------------------------+-----------------------------------+

| Id | Operation | Name | Rows | Bytes | Cost | Time |

----------------------------------------------------+-----------------------------------+

| 0 | SELECT STATEMENT | | | | 8 | |

| 1 | NESTED LOOPS | | 1 | 95 | 8 | 00:00:01 |

| 2 | VIEW | AVG_VAL_VIEW| 32 | 832 | 7 | 00:00:01 |

| 3 | HASH GROUP BY | | 32 | 224 | 7 | 00:00:01 |

| 4 | TABLE ACCESS FULL | T2 | 1024 | 7168 | 6 | 00:00:01 |

| 5 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 69 | 1 | 00:00:01 |

| 6 | INDEX UNIQUE SCAN | T1_PK | 1 | | 0 | |

----------------------------------------------------+-----------------------------------+

You will notice that the cost of the nested loop join (line 1) is 8. Introduce hints to the query to make 9i or 10.1 take the same path, and the cost of the nested loop join will be 39, from the standard formula (see chapter 11)

Cost of acquiring data from first table (7 in line two) +

Cardinality of result from first table (32 in line 2) * Cost of single visit to second table (1 in line 5)

 

39 = 7 + (32 * 1)

So why is Oracle 10.2 reporting a cost of 8 ? Since this is really all about nested loops, I have put the answer in the addenda to Chapter 11.

Cost is time (30th Dec 2005)

Page 3, last complete paragraph. Following my claim that ‘cost’ really is ‘estimated time to run’, I explain that Oracle 9i chooses the sreadtim as the unit of time measurement for backwards compatibility so that the cost reported by 9i will generally be close to the cost reported by 8i for small, precise OLTP activity.

Another way of thinking about this is as follows:

By the definition in the 9.2 Performance Tuning Guide:

Cost = (

#SRds * sreadtim +

#MRds * mreadtim +

#CPUCycles / cpuspeed

) / sreadtim

This says the cost is: “the time to estimated run in milliseconds, divided the sreadtim”. But in 8i, Oracle assumes that all read requests take the same time, and that CPU doesn’t count; in other words sreadtim = mreadtim, and CPUCycles = 0. So what happens if you put those assumptions into the 9i formula:

Cost = (

#SRds * sreadtim +

#MRds * sreadtim +

0

) / sreadtim = #SRds + #MRds

So the 9i formula gives the 8i result when you apply the 8i assumptions. In other words, Oracle 9i estimates the time to run, then divides by the sreadtim so that a query that doesn’t involved multi-block reads will show (nearly) the same cost in 8i and 9i.

As a final thought, the execution plan that you get from dbms_xplan.display() in 10g includes a time column. Here’s the execution plan for a simple query to count the rows in a table. Note that the execution plan is a full tablescan, doing multi-block reads only, but that I’ve modified the single-block read time before generating the execution plan:

/* execute dbms_stats.set_system_stats('SREADTIM',10) */

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

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

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

| 0 | SELECT STATEMENT | | 1 | | 329 (1)| 00:00:04 |

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

| 2 | TABLE ACCESS FULL| T2 | 48000 | | 329 (1)| 00:00:04 |

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

And here is it again, just moments later:

/* execute dbms_stats.set_system_stats('SREADTIM',2) */

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

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

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

| 0 | SELECT STATEMENT | | 1 | | 1641 (1)| 00:00:04 |

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

| 2 | TABLE ACCESS FULL| T2 | 48000 | | 1641 (1)| 00:00:04 |

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

What’s the difference between these two runs that could cause such a change in the cost ? The fact that I changed the optimizer’s assumption about the time it would take for a single block read to complete. (By scaling the single block read down by a factor of five, I effectively scaled the multiblock read time up by a factor of five – so this query, which did nothing but multiblock reads increased in cost by a factor of five).

Notice that even though the cost of the query has changed dramatically the time for the query to complete has not changed. So how has the optimizer calculated the time? It’s simply taken the 9i formula and multiplied the sreadtim back in to get back to the original time. Go back to my settings for the sreadtim statistic.

In the first case (10 milliseconds): ceiling (329 * 10/1000) = 4, hence the four second time.

In the second case (2 milliseconds): ceiling(1641 * 2/1000) = 4, hence the same four second time.

Cost is Time – but the units are a bit funny.

Back to Book Index

Top of page.


Errata

 


Back to Book Index

Top of page.