Clustering Factor of Indexes

The clustering factor is a number which represent the degree to which data is randomly distributed in a table.It is the number of “block switches” while reading a table using an index.
It is used by Oracle's optimizer to help determine the cost associated with index range scans in comparison to full table scans.

To calculate the clustering factor of an index during the gathering of index statistics, Oracle does the following.
For each entry in the index Oracle compares the entry's table rowid block with the block of the previous index entry.
If the block is different, Oracle increments the clustering factor by 1.
The minimum possible clustering factor is equal to the number of blocks identified through the index's list of rowid's -- for an index on a column or columns conatining no nulls, this will be equal to the number of blocks in the table that contain data. The maximum clustering factor is the number of entries in the index.

create table abc as select object_id,object_name from all_objects;

create index aidx on abc(object_id) compute statistics;

select clustering_factor From user_indexes where index_name ='AIDX';

CLUSTERING_FACTOR
-----------------
279

select count(*) from abc;

COUNT(*)
----------
54540


select blocks from user_segments where segment_name='ABC';

BLOCKS
----------
384

In the above example index has to jump 279 times to give all the data if a full table scan is performed using the index.

A good clustering factor is equal (or near) to the values of number of blocks of table.

A bad clustering factor is equal (or near) to the number of rows of table.

Rebuilding index does not improve the clustering factor. To improve the clustering factor table must be rebuild and reordered.

Gather Schema Stats

Gathering schema stats.

exec dbms_stats.gather_schema_stats (
ownname => 'SCHEMA NAME',
degree => dbms_stats.default_degree,
cascade => dbms_stats.auto_cascade,
options => 'GATHER AUTO'
);

OR

exec dbms_stats.gather_schema_stats (
ownname => 'SCHEMA NAME',
options => 'GATHER AUTO'
);

• ownname : - Schema to analyze (NULL means current schema)
• degree : - Degree of parallelism. The default for degree is NULL. Use the constant DBMS_STATS.DEFAULT_DEGREE to specify the default value based on the initialization parameters. DEFAULT_DEGREE (the system default value based on number of CPUs and initialization parameters) according to size of the object.
• cascade :- Gather statistics on the indexes as well.Index statistics gathering is not parallelized. Use the constant DBMS_STATS.AUTO_CASCADE to have Oracle determine whether index statistics to be collected or not.
• options :- Further specification of which objects to gather statistics for: GATHER AUTO: Gathers all necessary statistics automatically. Oracle implicitly determines which objects need new statistics, and determines how to gather those statistics. When GATHER
  AUTO is specified, the only additional valid parameters are ownname, stattab, statid, objlist and statown; all other parameter settings are ignored.
  

Finding OS Disk path from ASMLib Disk group

Run querydisk command on oracleasm look up major and minor versions from ls -la /dev

or use

export ORACLE_SID=+ASM
export ORAENV_ASK=NO
. oraenv

$ORACLE_HOME/bin/sqlplus -S "/ as sysdba" << EOS 2>&1 |grep [A-Z] > asmdisks.txt
set head off feed off
select a.group_number||' '||b.disk_number||' '||a.name||' '||b.label
from v\$asm_diskgroup a, v\$asm_disk b
where a.group_number = b.group_number;
exit
EOS

printf "%-9s %-30s %-3s %-10s %-3s\n" "ASM Disk" "Device Path [MAJ,MIN]" "GRP" "Disk Group" "DSK"
/etc/init.d/oracleasm querydisk `/etc/init.d/oracleasm listdisks` | cut -f2,10,11 -d" " | perl -pe 's/"(.*)".*\[(.*), *(.*)\]/$1 $2 $3/g;' | while read v_asmdisk v_minor v_major
do
v_device=`ls -la /dev | grep " $v_minor, *$v_major " | awk '{print $10}'`
grp=`grep $v_asmdisk asmdisks.txt|cut -f1 -d" "`
dsk=`grep $v_asmdisk asmdisks.txt|cut -f2 -d" "`
diskgroup=`grep $v_asmdisk asmdisks.txt|cut -f3 -d" "`

printf "%-9s /dev/%-25s %-3s %-10s %-3s\n" $v_asmdisk "$v_device [$v_minor, $v_major]" $grp $diskgroup $dsk
done