Need help in tuning the SQL

Clustering Factor

To calculate the clustering factor of an index, 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.

So this means that Oracle now has a statistic to allow it to estimate how many table blocks would be associated with an index range scan.

If the clustering factor is close to the number of entries in the index, then an index range scan of 1000 index entries may require nearly 1000 blocks to be read from the table.

If the clustering factor is close to the number of blocks in the table, then an index range scan of 1000 index entries may require only 50 blocks to be read from the table.

This can be compared with the cost of reading the entire table up to the high-water mark (using the more efficient multiblock i/o mechanism) to determine whether a full table scan or an index range scan offers the most efficient access mechanism.

Note that where extensive deletes have occurred from the table there may be blocks with no rows in. These will be accounted for in the clustering factor because those blocks will not appear in the index's rowid list. The full table scan will still read all table blocks up to the high water mark, regardless of whether they contain rows or not.

Note also that calculating the clustering factor is done without reference to the table at all -- it is based solely on information contained in the index.

Quote:

---

Originally posted by slimdave
Maybe so, but it's speculation. In this case Oracle doesn't appear to think they are closely packed.

---

It would be gr8, if you perform a test & u will know..

Quote:

---

Originally posted by slimdave
But before advising a reorg, why not advise checking on whether there are voids in the tables and indexes? There may indeed be reasons why the spaces might not be reused, but ...

---

Ok, thas correct, but i assume he would have done that in very first place..

Quote:

---

Originally posted by slimdave
iii) Voids are easily detactable with a query.

---

I have been using Package DBMS_SPACE for this ( to find %free space in blocks ), but quite not sure to do with a query, yes ofcourse you can get the breakup of number of rows in each block, but how would you calculate free space accordingly?

If you could let me know, it would be great.

Abhay.

Quote:

---

I have been using Package DBMS_SPACE for this ( to find %free space in blocks ), but quite not sure to do with a query, yes ofcourse you can get the breakup of number of rows in each block, but how would you calculate free space accordingly?

If you could let me know, it would be great.

---

As you say, the statistics tell you the number of rows, the number of blocks, and also you can read the average row length.

There are many statistics in user_tables that help detect voids -- empty_blocks, avg_space, num_freelist_blocks, avg_space_freelist_blocks.

You can even do something simplistic like taknig the avg row length and multipling by the number of rows, adding a little more to account for block headers, and comparing the result to the spacebelow the high water mark.

Quote:

---

Originally posted by slimdave
As you say, the statistics tell you the number of rows, the number of blocks, and also you can read the average row length.

There are many statistics in user_tables that help detect voids -- empty_blocks, avg_space, num_freelist_blocks, avg_space_freelist_blocks.

You can even do something simplistic like taknig the avg row length and multipling by the number of rows, adding a little more to account for block headers, and comparing the result to the spacebelow the high water mark.

---

Dave :

Ya, i know by all these figures you can estimate/evaluate the avg
number of blocks candidate for reuse and so, but still it will not
give you exact figures, when you said "Voids are easily detactable
with a query." I was wondering if you meant the exact figures..

PS what i was thinking...

Code:

---

TEST:ABHAY> @c:\dba\Free_Space_Blocks
Enter value for owner (RETURN for ALL) > ABHAY
Enter value for SEGMENT name (RETURN for ALL) > check_scatter
**************************************************************************
ABHAY TABLE CHECK_SCATTER
-------------------------------------------------------------------------
Tablespace : SEG_MANAGE_AUTO
Total Unformatted Bytes : 0 (0 blocks)
Total  0-25% Free : 0 (0 blocks)
Total  25-50% Free : 8192 (1 blocks)
Total  50-75% Free : 0 (0 blocks)
Total  75-100% Free : 1957888 (239 blocks)
Total Full : 7839744 (957 blocks)

I used DBMS_SPACE package for above results..i thought if you had
some query to find as such...

Below is what at best i can get, but i said i was unable to calculate
free sapce per block..i could only get breakup/blocks with number of
rows and so...ofcourse by AVG row lenght i can evaluate to almost to
near figures as above, but not exact...


So i wondered if you have designed some query to find so.!!

TEST:ABHAY> ed
Wrote file afiedt.buf

  1  Select
  2    Min(CNT) "Min_Rows_In_Block/s",
  3    Max(CNT) "Max_Rows_In_Block/s",
  4    Round(Sum(CNT)/Count(CNT)) "Avg_Rows_Per_Block"
  5  From
  6    (
  7    Select
  8      Substr(Rowid,1,15) Block,
  9      Count(*) CNT
 10    from
 11      Check_Scatter
 12    Group by
 13      Substr(Rowid,1,15)
 14*  )
 15  /

 Min_Rows_In_Block/s  Max_Rows_In_Block/s  Avg_Rows_Per_Block
-------------------- -------------------- --------------------
                112                  169                  167

TEST:ABHAY> ed
Wrote file afiedt.buf

  1  Select
  2    CNT Number_Of_Rows ,
  3    Count(*) Number_Of_Blocks
  4  From
  5    (
  6    Select
  7      Substr(Rowid,1,15) Block,
  8      Count(*) CNT
  9    from
 10      Check_Scatter
 11    Group by
 12      Substr(Rowid,1,15)
 13    )
 14  Group By
 15*  CNT
TEST:ABHAY> /

      NUMBER_OF_ROWS    NUMBER_OF_BLOCKS
-------------------- --------------------
                112                    1
                163                    1
                165                  304
                166                  168
                167                  39
                168                  438
                169                    7

---