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.

Test Case:-

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.

SQL> create sequence id_seq;
Sequence created.
SQL> CREATE TABLESPACE data_non_assm
DATAFILE ‘+DATA’
SIZE 10M EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT MANUAL;  2    3    4
Tablespace created.
SQL> create table t (id number, sometext varchar2(50),my_date date) tablespace data_non_assm;
Table created.
SQL> create or replace procedure manyinserts as
begin
for i in 1..1000000 loop
 insert into t values (id_seq.nextval, ‘DOES THIS CAUSE BUFFER BUSY WAITS?’,sysdate);
end loop;
commit;
end;
/  2    3    4    5    6    7    8
Procedure created.
SQL> create or replace procedure manysessions as
v_jobno number:=0;
begin
FOR i in 1..30 LOOP
dbms_job.submit(v_jobno,’manyinserts;’, sysdate);
END LOOP;
commit;
end;
/  2    3    4    5    6    7    8    9
Procedure created.
SQL> create index i_t on t(id);
Index created.
SQL> EXEC dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
SQL>exec manysessions;
SQL> exec dbms_stats.gather_table_stats(‘C##TEST’,’T’);

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.

SQL> CREATE TABLESPACE data_assm
DATAFILE ‘+DATA’
SIZE 5G EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;  2    3    4
Tablespace created.
SQL> create table t (id number, sometext varchar2(50),my_date date) tablespace data_assm;
Table created.
SQL> create or replace procedure manyinserts as
begin
for i in 1..1000000 loop
 insert into t values (id_seq.nextval, ‘DOES THIS CAUSE BUFFER BUSY WAITS?’,sysdate);
end loop;
commit;
end;
/  2    3    4    5    6    7    8
Procedure created.
SQL> create or replace procedure manysessions as
v_jobno number:=0;
begin
FOR i in 1..30 LOOP
dbms_job.submit(v_jobno,’manyinserts;’, sysdate);
END LOOP;
commit;
end;
/  2    3    4    5    6    7    8    9
Procedure created.
SQL> create index i_t on t(id) tablespace data_assm;
Index created.
SQL>exec manysessions;
SQL> exec dbms_stats.gather_table_stats(‘C##TEST’,’T’);
PL/SQL procedure successfully completed.
SQL> EXEC dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.

Conclusion:-

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
Non-ASSM 3023653 29625561 7040
ASSM 25514790 7351694 2166

Leave a Reply

Your email address will not be published. Required fields are marked *