Re: estimated of CLUSTERING_FACTOR

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 05 Sep 2007 08:05:37 -0700
Message-ID: <1189004731.466673@bubbleator.drizzle.com>

Andrea wrote:

> On 5 Set, 13:58, sybrandb <sybra..._at_gmail.com> wrote:

>> On Sep 5, 1:09 pm, Andrea <netsecur..._at_tiscali.it> wrote:
>>
>>
>>
>>> Hi,
>>> i was reading some paper related on clustering factor, this column
>>> aimed to identified if the rows of a table are synchronized (ordered)
>>> with the index.
>>> If i don't mistake, the value of clustering_factor have to approached
>>> to number of blocks of the table and more far away from number of
>>> rows.
>>> So, if i have understood well, main indications on this column are:
>>> 1) if c factor is lower than blocks, maybe the table could have
>>> problems of fragmentation because of many DML statements (insert and
>>> delete)
>>> 2) if c factor is higher than blocks and it approach to num_rows, then
>>> the rows in the index are not ordered (not sync with the index).
>>> In first case: is SHRINK the table a method for resolve the problem?
>>> In second case: rebuild index resolve order of the rows ?
>>> Or, for both case the only best method is truncate and reinsert all
>>> rows?
>>> thanks for your advice and opinions.
>>> bye
>>> --
>>> Andrea
>>> ( a guy that tries to become to DBA)
>> The best method is to bother only when it will *resolve* anything.
>> Otherwise you are embarking on an exercise in futility and getting
>> paid for wasting your and your employers time.
>>
>> --

> 
> sorry but i discord with you, some crumbs of my FREE time i dedicated
> for learn and increase my competence.
> I think that one (but important) of DBA role is "also" do a proactive
> tuning of database.
> what is badly in this?
> if i search and engage to find possible bottleneck (leaf blocks,
> chained rows, objects fragmentation, waits, clustering factor, etc..)
> i could  to avoid that this become to a problem (reactive).

To be proactive is a good thing. But there is a line that separates being proactive and having, as Gaja Krishna Vaidyanatha has called it, "Compulsive Tuning Disorder." More people have the later.

I too am in agreement with Sybrand. It appears that faced with a need to do "something" you have chosen an arbitrary "something" without metrics supporting that that "something" has any value.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Wed Sep 05 2007 - 10:05:37 CDT