RE: ** SPAM scored: Low **Re: Clustering factor smaller than table blocks.
- From: "Polarski, Bernard" <Bernard.Polarski@xxxxxxxxxxxxxx>
- To: "Stefan Knecht" <knecht.stefan@xxxxxxxxx>,<jonathan@xxxxxxxxxxxxxxxxxx>
- Date: Wed, 27 Sep 2006 13:37:31 +0200
Thanks stephan, you had it right, I got the reason of the descrepancy:
it comes from the stats in the PK partitions following a first
dbms_stat with percent less than 100. subsequence dbms_stat with percent
= 100 where done only at partitions level.
I had :
1 select 'partitions' , sum(DISTINCT_KEYS)DISTINCT_KEYS,
2 sum(NUM_ROWS)NUM_ROWS
3 from dba_ind_partitions where index_name = 'CUSTOMERS_PK'
and index_owner = 'SOE'
4 union
5* select 'global' , DISTINCT_KEYS , num_rows from dba_indexes
where index_name = 'CUSTOMERS_PK' and owner = 'SOE'
SQL> /
'PARTITION DISTINCT_KEYS NUM_ROWS
---------- ------------- ----------
global 36116 36112
partitions 36112 36112
So I made one at global level:
exec dbms_stats.gather_table_stats( ownname=>'SOE', tabname=>
'CUSTOMERS', Degree=> 4, estimate_percent=> 100, granularity=>'GLOBAL',
cascade=>TRUE) ;
And now:
'PARTITION DISTINCT_KEYS NUM_ROWS
---------- ------------- ----------
global 36112 36112
partitions 36112 36112
However the block count on the table is still 368 and CF of pk set to
345. I am investigating empty blocks not referenced in the free list.
B. Polarski
-----Original Message-----
From: Jonathan Lewis [mailto:jonathan@xxxxxxxxxxxxxxxxxx]
Sent: Wednesday, 27 September, 2006 11:03 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: ** SPAM scored: Low **Re: Clustering factor smaller than table
blocks.
One possibility relates to ASSM - you can have blocks below the
highwater mark that are not yet formatted. I'd have to check, but I
don't think they get recorded as EMPTY_BLOCKS.
The rather more surprising thing about your example is that you have
done a compute, so the results should be accurate, but you have 36,364
rows in the table and only 36,112 entries in what we guess is the
primary key index. Do you have a corrupt index perhaps ?
Regards
Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
----- Original Message -----
From: "Polarski, Bernard" <Bernard.Polarski@xxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, September 27, 2006 9:47 AM
Subject: Clustering factor smaller than table blocks.
I have a table with 368 blocks, no free blocks, and the clustering
factor of the primary key is 345. how is it possible?
I thought that CF will give you the amount of table blocks that an index
will need to access for a given rowset. Something like
smallest( table blocks, ceil( (CF/tot table rows)*rowset/avg rows per
block)) (real formula must be much complicated.
However if I have an avg ro
Here are the brute facts:
select EMPTY_BLOCKS,AVG_SPACE_FREELIST_BLOCKS ,NUM_FREELIST_BLOCKS
from dba_tables where table_name = 'CUSTOMERS' and owner =
'SOE' ;
EMPTY_BLOCKS AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS
------------ ------------------------- -------------------
0 0 0
The table was analysed with dbms_stat, percent = 100:
exec dbms_stats.gather_table_stats( ownname=>'SOE', tabname=>
'CUSTOMERS', Degree=> 4, estimate_percent=> 100, granularity=>'ALL',
cascade=>TRUE, stattab=>'sm_stattab', statid=>'CUSTOMERS09270906',
statown=>'SOE')
Table name Owner NUM_ROWS BLOCKS
Size (m) LAST_ANALYSED
------------------------------ ---------------- ---------- ----------
-------- -------------------
CUSTOMERS SOE 36364 368
3 09/27/2006 08:39:50
But still the clust factor reported by dba_indexes for CUSTOMERS_PK is
inferiors to CUSTOMERS block count.
Table Index Column
Clust Distinct
Name Name U Name
Factor Keys NUM_ROWS
-------------------- ------------------------------ -
-------------------- ---------- ---------- ----------
CUSTOMERS CUSTOMERS_PK U CUSTOMER_ID
345 36112 36112
This is oracle 10.1.0.4
B. Polarski
------------------------------------------------------------------------
--------
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.405 / Virus Database: 268.12.9/457 - Release Date:
26/09/2006
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » RE: ** SPAM scored: Low **Re: Clustering factor smaller than table blocks.
