OS & Oracle

SQL tuning is the most important part of performance tuning. Cost based optimizer, which is used to evaluate the cost for the SQL statement and determine which execution plan is suitable for that specified statement, plays a key role in SQL tuning.

So, what is Cost? From the Oracle Document, cost is defined as below:

Cost = (
#SRds * sreadtim +
#MRds * mreadtim +
#CPUCycles / cpuspeed
) / sreadtim
= #SRds + #MRds * mreadtim/sreadtim + #CPUCycles/(cpuspeed*sreadtim)
where
#SRDs - number of single block reads
#MRDs - number of multi block reads
#CPUCycles - number of CPU Cycles
sreadtim - single block read time
mreadtim - multi block read time
cpuspeed - CPU cycles per second

So, if we don’t count the CPU costing, which is #CPUCycles/(cpuspeed*#SRds), we get a conclusion that the cost is the sum of the number of single block reads and the number of multiple block reads, factored by  mreadtime/sreadtime. This is the model what Oracle 8i does. In Oracle 9i and forward, the CPU cost is accounted and the CPU cost part is add to this cost formula.

For full table scan, the cost will be dependant on the total number of blocks in that table and the number of blocks each read can retrieve, or to say, the data_file_multiblock_read_count parameter. The cost for full table scan be can expressed as

full_table_scan_cost = total_number_of_blocks / adjusted_data_file_multiblock_count

Here, adjusted_data_file_multiblock_count is just an adjustment to data_file_multiblock_read_count, and the value can be obtained by repeated experiments with different total_number_of_blocks and the resulted full_table_scan_cost.

And for the index access path, the cost is

btree_index_access_cost =
blevel +
ceiling(leaf_blocks * effective index selectivity) +
ceiling(clustering_factor * effective table selectivity)

Here, the concepts of selectivity, clustering factor, will be explained in the later articles. one thing to highlight is that the cost is usually based the unit of “BLOCK”.

Also, there are cost for join operations, such as nested loops, hash joins, sorting and merge joins.

nested_loop_cost = Cost of acquiring data from first table +
Cardinality of result from first table * Cost of single visit to second table

hash_join_cost =
(probe passes + 1) * round(Size of large data set/adjusted_data_file_multiblock_count)
+ round(Size of large data set/adjusted_data_file_multiblock_count) 

The information of this article mainly comes from the great book Jonathan Lewis wrote, “Cost-Based Oracle Fundamental”, thanks to Jonathan.