| Gather Table Stats - Partition [message #241260] |
Tue, 29 May 2007 00:41  |
Brayan
Messages: 315
Registered: June 2002
|
Senior Member |
|
|
Hi,
Table DEPOSITS has 40 million records, but..
SQL> select count(*) from deposits partition(max_partition) ;
COUNT(*)
---------
0
SQL>
and
begin
dbms_stats.GATHER_TABLE_STATS(ownname=>'TEST',tabname=>'DEPOSITS', partname=>'MAX_PARTITION', degree=>16);
end;
Takes a long time to gather stats(around 3 Hrs). What could be the reason...
Isn't it collects stats from specific partition?
Brayan.
|
|
|
|
|
|
| Re: Gather Table Stats - Partition [message #241264 is a reply to message #241260] |
Tue, 29 May 2007 01:02  |
Brayan
Messages: 315
Registered: June 2002
|
Senior Member |
|
|
Hi Frank,
Following is the space allocated from user_segments.
SQL> select segment_name, partition_name, bytes, blocks from user_segments
2 where segment_name = 'FEM_TERM_DEPOSITS' and partition_name = 'MAX_PARTITION';
SEGMENT_NAME PARTITION_NAME BYTES BLOCKS
-------------------- ------------------------------ --------- ---------
FEM_TERM_DEPOSITS MAX_PARTITION 40960 5
SQL>
For just 40K, it should not take 3 hours.
Brayan
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Gather Table Stats - Partition [message #241471 is a reply to message #241469] |
Tue, 29 May 2007 14:09 |
 |
Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
I don't think he stored 40 millions in 5 blocks, so I think he already shrunk the table.
That's what we can infer of what he posted.
In any case we can infer that this is not a fragmentation issue.
This is why I asked for a trace.
In a trace you can see all wait events and of course all scanned blocks.
Regards
Michel
[Updated on: Tue, 29 May 2007 14:16] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Gather Table Stats - Partition [message #241490 is a reply to message #241260] |
Tue, 29 May 2007 15:28 |
dbaxchangedba
Messages: 26
Registered: November 2005
|
Junior Member |
|
|
| Quote: | Isn't it collects stats from specific partition?
|
Yes it will along with the fact that it will collect global stats by default. If granularity is not specified then it takes the value of DEFAULT and that will cause partition stats and the table's global stats to be collected. If you don't want that to happen then you'll have to specify the granularity option as follows:
begin
dbms_stats.GATHER_TABLE_STATS(ownname=>'TEST',tabname=>'DEPOSITS', partname=>'MAX_PARTITION',[B]granularity=>'PARTITION'[/B],degree=>16);
end;
Good Luck....
http://www.dbaxchange.com
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
