Table Clusters: 6. Testing the Cluster & Conclusion (TL;DR)

This post is the last part of a series that discusses table clustering in Oracle.

1. Introduction and Ancient History
2. Cluster & Cluster Key Design Considerations
3. Populating the Cluster with DBMS_PARALLEL_EXECUTE
4. Checking the Cluster Key
5. Using the Cluster Key Index instead of the Primary/Unique Key Index
6. Testing the Cluster & Conclusion (TL;DR)

Testing

We did get improved performance with the clustered tables.  More significantly, we encountered less inter-process contention, and so were able to run more concurrent processes, and the overall elapsed time of all the processes was reduced.

Looking at just the performance of the bulk delete statements on the result tables, there is a significant reduction in DB time and physical I/O time on the clustered tables.  The reduction in physical I/O is not only because the table is smaller, but because there is no need to perform consistent read recovery on the blocks, there are fewer reads from the undo segment and less CPU was consumed creating consistent read copies in the buffer cache.

Statement		Heap Table	Clustered Table
		DELETE FROM PS_GP_RSLT_ACUM…
DB Time (s)		2182	1662
delete statement only	db file sequential	1451	891
	CPU	941	531

Statement		Heap Table	Clustered Table
		DELETE FROM PS_GP_RSLT_ABS…
DB Time (s)		781	330
delete statement only	db file sequential	340	210
	CPU	300	120

GP_RSLT_PIN is another, albeit smaller, result table.  It is a candidate for clustering, however, it was not clustered for this test and therefore did not show any significant improvement.  It was subsequently clustered.

Statement		Heap Table	Heap in Cluster Test
		DELETE FROM PS_GP_RSLT_PIN…
DB Time (s)		270	250
delete statement only	db file sequential	110	120
	CPU	110	90

The execution plans for some queries on clustered tables changed to use the cluster key index which resulted in poorer performance.  I had to introduce some SQL profiles to reinstate the original execution plans.  

However, the execution plans for these delete statements also switched to the cluster key index resulting in improved performance.  So it depends.

Conclusion (TL;DR)

Table partitioning can help you find data efficiently by allowing the database to eliminate partitions that cannot contain the data.  However, you must be running Enterprise Edition and license the partitioning option.

Table clustering is effective when you are regularly querying data from multiple tables with similar keys, and you can store them in the same data blocks, thus saving the overhead of retrieving multiple blocks.  It is available on any Oracle database and does not require any additional licence.

Both partitioning and clustering can help avoid the overhead of read consistency by storing dissimilar data in different blocks.

Sometimes, using the cluster key index can result in worse performance than using the original indexes.  A SQL profile or SQL baseline may be needed to stabilise some execution plans.