THE CLUSTERING FACTOR in Java

Drawing ANSI/AIM Code 39 in Java THE CLUSTERING FACTOR

create index t1_i1 on t1(date_ord, seq_ord); begin dbms_stats.gather_table_stats( user, 't1', cascade => true, estimate_percent => null, method_opt => 'for all columns size 1' ); end; / select blocks, num_rows from user_tables where table_name = 'T1' ; BLOCKS NUM_ROWS ---------- ---------749 26000 select index_name, blevel, leaf_blocks, clustering_factor from user_indexes where table_name = 'T1' ; INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR -------------------- ---------- ----------- ----------------T1_I1 1 86 1008 Notice how the clustering_factor in this case is similar to the number of blocks in the table, and very much smaller than the number of rows in the table. You may find the clustering_factor varies by 1% or 2% if you repeat the test, but it will probably stay somewhere around either the 750 mark or the 1,000 mark depending on whether you are running a single or multiple CPU machine. This looks as if it may be a good index, so let s test it with a slightly unfriendly (but perfectly ordinary) query that asks for all the data for a given date.

set autotrace traceonly explain select from where ; count(small_vc) t1 date_ord = trunc(sysdate) + 7

set autotrace off Execution Plan (9.2.0.6 autotrace) ---------------------------------------------------------0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=44 Card=1 Bytes=13) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=44 Card=1000 Bytes=13000) 3 2 INDEX (RANGE SCAN) OF 'T1_I1' (NON-UNIQUE) (Cost=5 Card=1000) Note that our query uses only one column of the two-column index. Applying the Wolfgang Breitling formula (which I introduced in 4), we can see the following figures drop out: cost = blevel + ceil(effective index selectivity * leaf_blocks) + ceil(effective table selectivity * clustering_factor) In this case, we are after 1 day out of 26 a selectivity of 3.846% or 0.03846 and the two selectivities are identical. Putting these figures into the formula: cost = 1 + ceil(0.03846 * 86) + ceil(0.03846 * 1,008) = 1 + 4 + 39 = 44 We know, and Oracle can observe through the clustering_factor, that all the rows for a given date have arrived at about the same time, and will be crammed into a small number of adjacent blocks. The index is used, even though Oracle has to fetch 1,000 rows, or nearly 4% of the data. This is good, as our simple model is probably a reasonable representation of many systems that are interested in daily activity.

Reducing Table Contention (Multiple Freelists)

But there may be a problem in our setup. In a high-concurrency system, we might have been suffering from a lot of contention. Take a look at the first few rows in the sample data that we have just produced. You will probably see something like this:

select /*+ full(t1) */ rowid, date_ord, seq_ord, small_vc from t1 where rownum <= 10 ; ROWID -----------------AAAMJHAAJAAAAAKAAA AAAMJHAAJAAAAAKAAB AAAMJHAAJAAAAAKAAC AAAMJHAAJAAAAAKAAD AAAMJHAAJAAAAAKAAE AAAMJHAAJAAAAAKAAF AAAMJHAAJAAAAAKAAG AAAMJHAAJAAAAAKAAH AAAMJHAAJAAAAAKAAI AAAMJHAAJAAAAAKAAJ DATE_ORD SEQ_ORD SMALL_VC --------- ---------- ---------18-FEB-04 1 A1 18-FEB-04 2 B1 18-FEB-04 3 C1 18-FEB-04 4 A2 18-FEB-04 5 D1 18-FEB-04 6 E1 18-FEB-04 7 B2 18-FEB-04 8 D2 18-FEB-04 9 B3 18-FEB-04 10 E2

Remember that the extended rowid is made up of the following: object_id Relative file_id Block within file Row within block First six letters (AAAMJH) Next three letters (AAJ) Next six letters (AAAAAK) Last three letters (AAA, AAB, AAC ...)

All these rows are in the same block (AAAAAK). In my test run, I populated the column small_vc with a tag that could be used to identify the process that inserted the row. All five of our processes were busy hitting the same table block at the same time. In a very busy system (in particular, one with a high degree of concurrency), we might have seen lots of buffer busy waits for blocks of class data block for whichever one block was the current focus of all the inserts. How do we address this issue Simple: we read the advice in the Oracle Performance Tuning Guide and Reference and (for older versions of Oracle particularly) realize that we should have created the table with multiple freelists. In this case, because we expect the typical degree of concurrency to be 5, we might go to exactly that limit, and create the table with the extra clause: storage (freelists 5) With this clause in place, Oracle maintains five linked lists of free blocks hanging from the table s segment header block, and when a process needs to insert a row, it uses its process ID to determine which list it should visit to find a free block. This means that (with just a little bit of luck) our five concurrent processes will never collide with each other; they will always be using five completely different table blocks to insert their rows.