Properly Sizing Oracle Cluster

Sizing a cluster in Oracle 23ai is an important task to ensure that your database performs efficiently and that storage is used effectively. A cluster in Oracle is a schema object that contains one or more tables that share the same data blocks because they are often queried together. Properly sizing a cluster involves estimating the space required for the data and considering the size of the cluster key.
Steps to Properly Size a Cluster in Oracle 23ai:

1. Understand the Concept of a Cluster Key: The cluster key is a column or a set of columns used to group rows from one or more tables in a cluster. Rows with the same cluster key value are stored together in the same data block.
2. Estimate the Average Row Size: Calculate the average size of a row in each table that will be part of the cluster. Include all columns and take into account data types, null values, and any potential overhead.
   Example estimation:
   

   SELECT AVG(VSIZE(column1) + VSIZE(column2) + ... + VSIZE(columnN)) AS avg_row_size
   FROM table_name;

3. Determine the Number of Rows per Cluster Key: Estimate how many rows are associated with each cluster key value. This can be done by querying the tables and grouping by the cluster key.
   Example:

   SELECT cluster_key_column, COUNT(*)
   FROM table_name
   GROUP BY cluster_key_column;

4. Calculate the Space Required for Each Cluster Key: Multiply the average row size by the average number of rows per cluster key.
   Formula:

   space_per_key = avg_row_size * avg_rows_per_key

5. Include Overhead and Padding: Account for additional space required for overheads such as row headers, block headers, and any padding. It's typically recommended to add 10-20% overhead to your calculations.
6. Set the Cluster Size: The cluster size is the size of a data block multiplied by the number of data blocks that should fit the estimated space for each cluster key. You should ensure that the calculated cluster size can hold all rows associated with each cluster key value.
   Example for setting a cluster size:
   

   CREATE CLUSTER cluster_name
   (cluster_key_column DATA_TYPE)
   SIZE cluster_size
   TABLESPACE tablespace_name;

   
   Here, cluster_size should be set based on your calculations to ensure that all rows associated with a cluster key fit within the cluster's blocks.
7. Monitor and Adjust: After implementing the cluster, monitor its performance and space usage. Use the DBA_CLUSTERS and DBA_TABLES views to monitor the space and adjust if necessary.
8. Rebuild the Cluster if Necessary: If performance degrades or you notice excessive space usage, you may need to rebuild the cluster with a different size, especially if the distribution of rows per cluster key changes significantly over time.

A Worked Example

Walking through the formula with real numbers makes the process concrete. Suppose you are clustering an ORDER_DETAIL table by ORDER_ID:

• Average row size, from the VSIZE query above: 85 bytes
• Average number of detail rows per order, from the GROUP BY query: 12 rows

space_per_key = 85 * 12 = 1,020 bytes

Adding 15% overhead for row and block headers brings the estimate to approximately 1,173 bytes. Since Oracle allocates space in whole data blocks rather than exact byte counts, you would round this up to the next block boundary — for an 8K block database, a single block comfortably covers this cluster key's data with room to spare, so SIZE 1024 (1K) or a similarly conservative round figure is a reasonable starting point, to be refined once real data volumes are observed.

This same SIZE parameter also applies to hash clusters, though hash clusters add a second sizing input alongside it: the HASHKEYS clause, which tells Oracle how many distinct cluster key values to plan for when computing hash slot locations. SIZE still governs how much space each key's rows occupy; HASHKEYS governs how many keys the cluster is built to hold. Both should be estimated from the same underlying row-size and row-count analysis described above.

Sizing a Cluster with Multiple Tables

The worked example above sized a cluster for a single table's rows. When a cluster holds more than one table sharing the same key — as with the EMPLOYEES/DEPARTMENTS example below — the space_per_key calculation has to account for every table's contribution, not just one. For a given DEPARTMENT_ID, the cluster block must hold the DEPARTMENTS row for that department plus every EMPLOYEES row that references it. If the average DEPARTMENTS row is 60 bytes and the average EMPLOYEES row is 70 bytes, and a department has an average of 8 employees, the calculation becomes:

space_per_key = dept_row_size + (avg_employees_per_dept * employee_row_size)
             = 60 + (8 * 70)
             = 620 bytes

Adding the same 15% overhead allowance brings this to roughly 713 bytes — still comfortably within a single 8K block, but noticeably larger than either table's contribution taken alone. Omitting one table's contribution from the estimate is a common sizing mistake with join clusters specifically: because each table's average row size looks small in isolation, it is easy to under-budget the combined space a busy cluster key actually needs.

The storage clause options available when creating the cluster, such as PCTFREE, interact with this calculation too. A cluster key whose rows are expected to grow over time — for example, if EMPLOYEES rows are frequently updated with longer values — benefits from a higher PCTFREE setting, reserving some of each block's space for that growth rather than relying on the SIZE estimate alone to absorb it.

Example of Creating a Cluster:
Suppose you have two tables EMPLOYEES and DEPARTMENTS that share a DEPARTMENT_ID column and you want to cluster them together:

-- Create the cluster
CREATE CLUSTER emp_dept_cluster
(DEPARTMENT_ID NUMBER)
SIZE 1024  -- Estimated size for each department's data
TABLESPACE users;

-- Create the tables in the cluster
CREATE TABLE DEPARTMENTS (
  DEPARTMENT_ID NUMBER PRIMARY KEY,
  DEPARTMENT_NAME VARCHAR2(50)
)
CLUSTER emp_dept_cluster (DEPARTMENT_ID);

CREATE TABLE EMPLOYEES (
  EMPLOYEE_ID NUMBER PRIMARY KEY,
  EMPLOYEE_NAME VARCHAR2(50),
  DEPARTMENT_ID NUMBER
)
CLUSTER emp_dept_cluster (DEPARTMENT_ID);

In this example, SIZE 1024 represents the estimated size needed for all rows associated with a single DEPARTMENT_ID. Note that an index cluster like this one still requires a cluster index before it can accept rows — creating that index is covered in the next lesson, so treat this example as illustrating the sizing decision, not yet a complete, runnable setup.

Summary:

• Estimate average row size and number of rows per cluster key.
• Calculate the space required per cluster key.
• Include overhead in your calculations.
• Set the cluster size based on these estimates when creating the cluster.
• Monitor performance and adjust as necessary.

Properly sizing a cluster helps to optimize query performance and ensures efficient storage utilization, leading to overall better database management.

In a non-clustered table, the size of the data to be contained in the table is not a great concern.
A DBA always must be sure to have enough disk space to store data, but management of the underlying storage is handled by your Oracle database. When you use a cluster, however, you are explicitly linking data, by value, with the way it is stored on disk. Because of this, you must specify a size for the data associated with a particular value of the cluster key.

The CREATE CLUSTER command has a required SIZE parameter.
You can specify this size either in K(ilobytes) or M(egabytes). When you create a cluster, each time you add a value for the cluster key, Oracle allocates the number of data blocks required by the value in the SIZE clause. The size specified in the CREATE CLUSTER should be large enough to store all of the data for all of the rows associated with the cluster value and the cluster value itself.

• Improperly size your cluster?
  If you size your cluster too small, the data for the cluster value will extend beyond the pre-allocated space, and the additional chaining will reduce the overall performance advantages of the cluster. If you specify too large a size for your cluster, you will end up wasting disk space, which can also contribute to reduced disk and database performance. The next lesson shows how to create a cluster index.

Watching for Sizing Problems After Deployment

An initial SIZE estimate is a starting point, not a permanent commitment. Real data distributions rarely match estimates exactly, and it is normal to revisit the sizing decision after a cluster has been in production use for a while. Two symptoms are worth watching for specifically:

• Chained rows. When a cluster key's data outgrows its allocated space, Oracle chains additional blocks onto the key's storage to hold the overflow. A small amount of chaining is not alarming, but a cluster where most keys are chained is effectively behaving like a much less efficient, ordinary table — the physical-proximity benefit that justified clustering in the first place is largely lost.
• Unused allocated space. The opposite problem is quieter but still costly: a SIZE set well above what most keys actually use wastes disk space across every key in the cluster, and can also reduce the number of keys that fit in a single data block, which works against the same physical-proximity benefit clustering is meant to provide.

Gathering statistics regularly with DBMS_STATS and reviewing space usage through DBA_CLUSTERS and DBA_TABLES is the most reliable way to catch either problem before it affects application performance. If the gap between your original estimate and observed usage is large, rebuilding the cluster with a revised SIZE is usually preferable to leaving a badly-sized cluster in place.

Click the Exercise link below to practice creating a cluster in an Oracle database.
Sizing Cluster - Exercise

Click the Exercise link below to practice creating a cluster in an Oracle database.
Sizing Cluster - Exercise