Home » RDBMS Server » Server Administration » Gather Table Stats - Partition
Gather Table Stats - Partition [message #241260] Tue, 29 May 2007 00:41 Go to next message
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 #241262 is a reply to message #241260] Tue, 29 May 2007 00:53 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
It may mean that there are no rows in that partition, but it has a lot of space allocated to it. If so, it would take quite some time to scan through it (not using an index).
Re: Gather Table Stats - Partition [message #241264 is a reply to message #241260] Tue, 29 May 2007 01:02 Go to previous messageGo to next message
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 #241277 is a reply to message #241260] Tue, 29 May 2007 01:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Activate a trace on the session and check on what it is waiting for.

Regards
Michel
Re: Gather Table Stats - Partition [message #241467 is a reply to message #241277] Tue, 29 May 2007 13:50 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Quote:
Table DEPOSITS has 40 million records, but..



did you delete that??

If yes then shrik table may be there is fragmentation.
Re: Gather Table Stats - Partition [message #241468 is a reply to message #241467] Tue, 29 May 2007 13:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DreamzZ,

Don't you see the segment size posted by Brayan?
5 blocks, I don't think there is fragmentation in 5 blocks.

Regards
Michel
Re: Gather Table Stats - Partition [message #241469 is a reply to message #241468] Tue, 29 May 2007 14:03 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
But michel he also wrote that partitions had 40 millions rows??
Then where are rows?obviously delete doesnt reclaim used space.

if rows are deleted then there could be a chance of fragmentation.

[Updated on: Tue, 29 May 2007 14:07]

Report message to a moderator

Re: Gather Table Stats - Partition [message #241471 is a reply to message #241469] Tue, 29 May 2007 14:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
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 #241472 is a reply to message #241471] Tue, 29 May 2007 14:10 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Thanks.
Re: Gather Table Stats - Partition [message #241476 is a reply to message #241472] Tue, 29 May 2007 14:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You're welcome.

Regards
Michel
Re: Gather Table Stats - Partition [message #241490 is a reply to message #241260] Tue, 29 May 2007 15:28 Go to previous message
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
Previous Topic: Cannot startup db
Next Topic: control file
Goto Forum:
  


Current Time: Sun Dec 04 23:00:31 CST 2016

Total time taken to generate the page: 0.08083 seconds