Home » RDBMS Server » Performance Tuning » dbms_stats.gather_table_stats (oracle 10g)
dbms_stats.gather_table_stats [message #361501] Wed, 26 November 2008 12:17 Go to next message
pdhayan@qwest.com
Messages: 6
Registered: August 2008
Junior Member
Hi,
I've a partitioned table with a global PK. I use dbms_stats.gather_table_stats pack to gather stats on the the table. I gather stats at partition level by passing partition name to partname parameter. Right now the granularity is set to ALL, I'm planning to change it to 'PARTITION'.
Will there be any impact due to this? I access the table most of the time by the PK. since I'm changing the valueto 'PARTITION' will there be any perf difference?

Thanks in advance for your advice....
Re: dbms_stats.gather_table_stats [message #361506 is a reply to message #361501] Wed, 26 November 2008 12:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Let the default options unless you perfectly know what you do, and only gather the statistics on the table with these options.

Regards
Michel
Re: dbms_stats.gather_table_stats [message #361511 is a reply to message #361501] Wed, 26 November 2008 12:50 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> I access the table most of the time by the PK
That might help with what you are trying to do. If the query knows the partition from which the data is to be fetched, global stats is of not much use. But as you said, that is "most of the time".
For the "rest of the time", if there is no global stats, Oracle CBO will assume one by deriving values from partition level stats and may get very confused.
Previous Topic: procedure running with no end
Next Topic: Index on Partition Table (merged)
Goto Forum:
  


Current Time: Tue Dec 06 02:46:47 CST 2016

Total time taken to generate the page: 0.26893 seconds