jump to navigation

New Additions To My Blogroll January 24, 2010

Posted by Richard Foote in Richard's Musings.
2 comments

Thought it was time to update my blogroll with a couple of new additions.

Both Charles Hooper and Randolf Geist have provided excellent advice and information on the OTN forums for quite some time and both have excellent blogs that are well worth checking out.

They’re also both co-authors in a new Oracle book I’m keen to get my hands on-  Expert Oracle Practices: Oracle database Administration From The Oak Table.

My next series of posts will look at how Oracle implements row-level locking and achieves the magic that is read consistency.

For the point of view of indexes of course !!

Coming soon, after I’ve finished writing and updating a few new presentations and white papers while watching the Australian Open tennis.

The CPU Costing Model: A Few Thoughts Part V (Reality) January 13, 2010

Posted by Richard Foote in CBO, Multiblock Reads, Oracle Cost Based Optimizer, Richard's Musings, System Statistics.
5 comments

There’s plenty more I could talk about regarding the CBO CPU costing model and system statistics but I’ll make this my final little comment on this subject for now.
 
As previously discussed, the CPU costing model basically takes the time it takes to perform the all necessary I/O related activities and all the time it takes to perform all necessary CPU related activities and adds them together to get the overall time to complete a task. The CBO then takes this total and divides it by the average time to perform a single block I/O so that it expresses the overall costs in units of single block I/Os.
 
There are two advantages with expressing CBO costs in this manner.
 
Firstly, it makes the move from the old I/O costing model a little easier in that the “units” of cost under both CBO costing models is very similar.
 
With the I/O costing model, the unit of cost was also basically the number of I/Os. It’s just that the CBO made no (automatic) distinction between the I/O costs associated with single and multiblock reads. The cost was simply the expected total number of I/Os for a given execution plan, with single block and multiblock I/Os being consider the same (unless the optimiser_index_cost_adj parameter kicked in).
 
With the CPU costing modelling, the costs are expressed specifically in units of single block I/Os. However, the CBO automatically takes into consideration and differentiates the relative costs associated with multiblock I/Os (and CPU operations) and incorporates them automatically into the final cost.

The other nice advantage is that one can use the actual cost values as an indication of how long an operation or execution plan is likely to take. The overall execution times of the plan are divided by the average time of a single block I/O when using the CPU costing formula. Therefore by multiplying these cost values out again by the average time of a single block I/O (SREADTIM system statistic), one can have an indicative idea of the overall expected execution time.
 
The overall execution times as estimated by the CBO using the CPU costing model is therefore basically = cost of execution plan multiplied by SREADTIM system statistic.
 
Using my previous example with the FTS where the overall cost of the execution plan was 70, and the SREADTIM system statistic was 5:
 
the overall execution time as estimated by the CBO is approximately 70 x 5 = 350 ms.
 
Now this of course is only an indicative value. As all system related statistics are simply averages, there could obviously be discrepancies with how long specific I/Os take to actually perform, the size and number of specific multiblock read operations, etc. There may also be caching characteristics of objects that may influence the actual number of physical reads and associated wait times, it doesn’t take into consideration time taken to actually return data to the “client”, etc. etc. etc.
 
However, it provides one with a rough “ballpark figure”. If the actual executions times in the above example were (say) 20 seconds, then it’s a strong indication that the CBO may have got it wrong, that it may have calculated the wrong cost and maybe as a result the wrong execution plan. Somewhere, something such as the segment statistics, the system statistics, optimizer parameters, etc. may be inaccurate and is causing the CBO to get its costings incorrect.
 
The CBO cost value doesn’t compare well to reality and so is perhaps worthy of further investigation.
 
The cost values associated with CPU costing model is not some random, ambiguous, mysterious number but a value that can often be derived and which can be most useful in determining and resolving problematic SQL statements and execution plans.

The CPU Costing Model: A Few Thoughts Part IV (Map of the Problematique) January 7, 2010

Posted by Richard Foote in CBO, Oracle Cost Based Optimizer, System Statistics.
10 comments

It’s called the CPU Costing model because among other things, it includes the time associated with performing CPU operations.
 
The CPU Costing model formula once again:
 
(sum of all the single block I/Os x average wait time for a single block I/O +
 sum of all the multiblock I/Os x average wait time for a multiblock I/O +
 sum of all the required CPU cycles / CPU cycles per second)
/
average wait time for a single block I/O
 

So the portion detailing the sum of all required CPU cycles divided by the CPU cycles per second can potentially contribute a significant proportion of the overall costs.
 
When I previously discussed the costs associated with the CPU model between using an index and a FTS, the FTS example I used had an overall cost of 70 but I calculated that the I/O component costs were only 67. Therefore the costs directly related to CPU operations with the FTS example was 3.
 
However, these CPU specific costs in this example may vary from database to database, although the FTS might be identical as might the required CPU cycles. However, a variable in all this is the CPU cycles per second system statistic (CPUSPEED) associated with a particular database environment.
 
Obviously, the faster the CPUs, the quicker it can perform the necessary CPU operations associated with the FTS (or any operation for that matter). Conversely, the slower the CPUs, the longer it will take to complete the necessary CPU related operations. The CPU costing model formula automatically takes all this into consideration.
 
In the previous example, the CPUSPEED system statistic was 1745.
 
Let’s now run the identical FTS but this time with a faster and a slower CPU and see how this might adjust the overall related costs when using the CPU costing model.
 
One can simulate a “faster” CPU by simply adjusting the CPUSPEED system statistic. Let’s make the CPUs appear 10 times faster:
 

SQL> exec dbms_stats.set_system_stats(pname=>’cpuspeed’, pvalue=>17450);
 
PL/SQL procedure successfully completed.
 

OK, let’s now see how this impacts the cost of the FTS:
 
SQL> SELECT * FROM bowie_stuff WHERE id = 420;
 
1000 rows selected.
 

Execution Plan
———————————————————-
Plan hash value: 910563088
 
——————————————————————-
|Id|Operation         |Name       |Rows|Bytes|Cost (%CPU)|Time    |
——————————————————————-
| 0|SELECT STATEMENT  |           |1000|18000|   67   (0)|00:00:01|
|*1| TABLE ACCESS FULL|BOWIE_STUFF|1000|18000|   67   (0)|00:00:01|
——————————————————————-
 

We notice that the overall cost has reduced from 70 down to 67. The cost of 3 that was previously attributed to just the CPU related costs have all disappeared and the costs are now just the 67 in relation to the I/O component.
 
The CPU is now so fast that it can effectively perform all the necessary operations in a negligible amount of time. An even faster CPU will not further improve the costs associated with this FTS as the costs now only include the I/O related components.

The (%CPU) value of (0) gives us this information if you didn’t follow how I derived the I/O cost of 67 in my previous post.

If we go the other way and now make the CPU about 1/10 the speed of the original example:
 
 
SQL> exec dbms_stats.set_system_stats(pname=>’cpuspeed’, pvalue=>175);
 
PL/SQL procedure successfully completed.
 
SQL> SELECT * FROM bowie_stuff WHERE id = 420;
 
1000 rows selected.
 

Execution Plan
———————————————————-
Plan hash value: 910563088
 
——————————————————————-
|Id|Operation         |Name       |Rows|Bytes|Cost (%CPU)|Time    |
——————————————————————-
| 0|SELECT STATEMENT  |           |1000|18000|   93  (28)|00:00:01|
|*1| TABLE ACCESS FULL|BOWIE_STUFF|1000|18000|   93  (28)|00:00:01|
——————————————————————-
 

We now notice the overall costs have jumped up considerably up from 70 up 93.
 
The costs associated directly with CPU activities have now increased up from 3 to 26. The CPU component is in the ballpark of 10 times as expensive/significant when you take into account rounding errors (the original 3 value was rounded accordingly). Remember also that these figures are times expressed in units of time it takes to perform a single block I/O.
 
The CPUs are now so slow that it takes a considerable amount of time to complete all the required CPU operations.
 
Note that the (%CPU) value is now a significant (28%) of the overall costs as derived from the following formula:

round(cpu related cost/total cost) x 100 = round(26/93 x 100) = 28.
 
So having a faster (or possibly slower) CPU when performing a hardware upgrade/change can result in potentially different execution plan costings (and as such different execution plans) when using the CPU CBO costing model.
 
It’s called the CPU costing model for a reason and as one would indeed hope, the speed of said CPU(s) can directly impact the associated costs and decisions made by the CBO.