Oracle Performance and Engineering

Yesterday we did a little experiment on how tweaking DB_FILE_MULTIBLOCK_READ_COUNT value changes the execution plan of a SQL. Today we will see how it can be countered by re-adjusting OPTIMIZER_INDEX_COST_ADJ.

We saw how lowering the value of DB_FILE_MULTIBLOCK_READ_COUNT increases the I/O cost of full-table or index fast-full scans and vice versa. Now, we will tweak the parameter OPTIMIZER_INDEX_COST_ADJ to counter that effect.

First we set it to a very high value. i.e., we tell Oracle that reading an index block is 3 times as expensive than reading a table block. So, Oracle should back full-table scan (or, index fast-full scan) more than an index scan.

alter session set optimizer_index_cost_adj = 300;

select count(*) total from users b


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.05 0.04 0 595 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.06 0.05 0 595 0 1

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=595 pr=0 pw=0 time=47842 us)
300634 INDEX FAST FULL SCAN USERS_N9 (cr=595 pr=0 pw=0 time=601336 us)(object id 323598)

-- Then we change it to a very low value thus almost forcing index usage (full scan
-- is not a multi-block read operation

alter session set optimizer_index_cost_adj = 3;

select count(*) total from users


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.07 0.07 0 590 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.08 0.07 0 590 0 1

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=590 pr=0 pw=0 time=74051 us)
300634 INDEX FULL SCAN OBJ#(323598) (cr=590 pr=0 pw=0 time=601309 us)(object id 323598)

There is a rough way to calculate the value of optimizer_index_cost_adj to fix such discrepancy in this document (requires Metalink login).

Some Random (but useful) Notes :

(1) Thus it may appear that the cost of a full-table scan will be =

(# of blocks reads / db_file_multiblock_read_count)

But in reality the denominator is really a function of db_file_multiblock_read_count. Wolfgang Breitling derived the function to be like

k= 1.6765 ** 0.6581

Actual value used by Oracle for a set of popular values of this parameter -

Actual Oracle Used Value
DB_* to "cost" full scan
--------- -----------------
4 4.2
8 6.6
16 10.4
32 16.4
64 25.9
128 40.9

Thus the "cost" for a full-table scan will be -

(Blocks under High-water mark / Scaled DB_FILE_MULTIBLOCK_READ_COUNT)

Running multiple tests with different sized tables on a 9i I/O costing model supports the cost_of_full_table_scan - DB_FILE_MULTIBLOCK_READ_COUNT correlation.

However, in Oracle 9i onwards, if the system statistics is collected or set manually, then the MBRC (maximum block read count) value from the system statistics table (SYS.AUX_STATS$) is used to compute the cost of a full-table scan. This value is not adjusted to compute cost.

(2) Where the optimizer is running in RULE mode, it's beneficial to set this parameter to the maximum useful value. In RULE mode, the FULL scans are performed only when index scans are not feasible. Thus if full-table scan is performed higher value of this parameter will ensure less work by doing less number of I/Os.

Probably the same will hold for FIRST_ROWS (though not always), since Full table scans are generally rare in that mode too.

(3) Setting this value for a buffered I/O system is not very useful.

According to Steve Adams,

"Unfortunately, if you use buffered I/O the operating system will service multiblock reads as a series of small I/O operations anyway and the only benefit from multiblock reads will be a small saving in CPU usage. In this case the multiblock read count should be approximately equal to the typical track length to take advantage of track buffering and no larger lest the optimizer be deceived.

You can tell whether your filesystem is servicing multiblock reads atomically using the sar -d statistics. Divide the blocks per second metric by the reads+writes per second metric to get the average I/O size expressed in 512 byte blocks. If that is consistently close to 16 blocks (8K) while doing "large" multiblock reads then the buffering is indeed breaking your reads up into 8K chunks and you'll need to switch to raw or direct I/O to get the real benefit of multiblock reads. Note that the 8K is not invariant. Many Unix operating systems can also work with 4K file system blocks, and some use 16K."

So, in a nutshell, this parameter effects the choice of full-table scan (multi-block I/O or index fast-full scan where it's enabled) Vs. an index scan (single block I/O). But for Oracle 9i and onwards where system statistics is collected, this parameter is neglected to figure out the cost. In that case the cost is determined by the actual number of database blocks that can be read in a single read and a single multi-block I/O read time.