Clustering Factor and it’s effect on non-assm and assm tablespace.
The clustering factor is a measure of the ordernesss of an index in comparison to the table that it is based on. It is used to check the cost of a table lookup following an index access (multiplying the clustering factor by the index’s selectivity gives you the cost of the operation). The clustering factor records the number of blocks that will be read when scanning the index. If the index being used has a large clustering factor, then more table data blocks have to be visited to get the rows in each index block (because adjacent rows are in different blocks). If the clustering factor is close to the number of blocks in the table, then the index is well ordered, but if the clustering factor is close to the number of rows in the table, then the index is not well ordered. The clustering factor is computed by the following (explained briefly):
1. The index is scanned in order.
2. The block portion of the ROWID pointed at by the current indexed value is compared to the
previous indexed value (comparing adjacent rows in the index).
3. If the ROWIDs point to different TABLE blocks, the clustering factor is incremented (this is
done for the entire index).
The CLUSTERING_FACTOR column in the USER_INDEXES view gives an indication as to how organized the data is compared to the indexed columns. If the value of the CLUSTERING_FACTOR column value is close to the number of leaf blocks in the index, the data is well ordered in the table. If the value is not close to the number of leaf blocks in the index, then the data in the table is not well ordered. The leaf blocks of an index store the indexed values as well as the ROWIDs to which they point.
A.In the test case I will create a non-assm tablespace .After that I will create a sequence and load data using the sequence from 30 parallel session.
PL/SQL procedure successfully completed.
B.In the test case I will create a assm tablespace .After that I will create a sequence and load data using the sequence from 30 parallel session.
For NON-ASSM,cluster factor is 8 times lower but buffer busy wait is much greater .Also DB CPU is greater in case of NON-ASSM.
|Cluster Factor||Buffer busy wait||DB CPU|