Home » RDBMS Server » Server Administration » DBMS_STATS.GATHER_SCHEMA_STATS (oracle 10g)
DBMS_STATS.GATHER_SCHEMA_STATS [message #394189] Thu, 26 March 2009 00:39 Go to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Oracle 10.2.0.1
User database size is 200GB

How much percent should I use for estimate_percent

Is stimate_percent=> 15 is ok?
or do i need to specify 100%
Please explain about it.

*exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname => TEST', estimate_percent => 15, CASCADE => TRUE, method_opt => 'for indexed columns size* *skewonly');*

Thanks
Re: DBMS_STATS.GATHER_SCHEMA_STATS [message #394194 is a reply to message #394189] Thu, 26 March 2009 00:48 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
After import i want to gather statistics 1st time.
How much i need to specify?

Thanks,
Re: DBMS_STATS.GATHER_SCHEMA_STATS [message #394201 is a reply to message #394189] Thu, 26 March 2009 01:06 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
I read on the internet that i can use dbms_stats.auto_sample_size.

Is it correct ?
exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname => TEST', estimate_percent=>dbms_stats.auto_sample_size, CASCADE => TRUE, method_opt => 'for indexed columns size skewonly');


Then how much time will it take?
User database size is 200GB
Re: DBMS_STATS.GATHER_SCHEMA_STATS [message #394203 is a reply to message #394189] Thu, 26 March 2009 01:21 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
I suggest you take some time (multiple hours) to relax & understand reality.
Re: DBMS_STATS.GATHER_SCHEMA_STATS [message #394204 is a reply to message #394189] Thu, 26 March 2009 01:28 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Why i cant determine the time required?
There must be something to calculate exact time for gathering statistcs?

Thanks,
Re: DBMS_STATS.GATHER_SCHEMA_STATS [message #394207 is a reply to message #394189] Thu, 26 March 2009 01:44 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Does objects i.e. TABLE,INDEXES locks during the gathering schema stats?
Or what will be effect on the database objects and performance?

Thanks,
Re: DBMS_STATS.GATHER_SCHEMA_STATS [message #394268 is a reply to message #394207] Thu, 26 March 2009 06:14 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
All your questions are answered in docs.
>>There must be something to calculate exact time for gathering statistcs?
No standard rule. It depends on your data, the options you choose and load.
>>Is it correct ?
May be. May not be. It is your database. Your data.
Does it matter if we certify it?
Re: DBMS_STATS.GATHER_SCHEMA_STATS [message #394284 is a reply to message #394207] Thu, 26 March 2009 06:44 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Oracle does not lock objects during stats gathering (or at least not any objects that you should ever think about updating yourself).
There will be a performance hit, as the stats gathering process will need to read a substantial amount of data from your tables.

The option that you use for Method_Opt depends a lot on the queries that you will be running.

If you have a lot of where clause lines of the form "WHERE a = '<some value>'" then you might want to consider generating stats for those tables using 'FOR ALL COLUMNS'.

I'd probably use 'FOR ALL INDEXED COLUMNS' rather than including the Skewonly option - it provides the CBO with more information.
Previous Topic: Changing a subpartition initial extent
Next Topic: INSERT without archive
Goto Forum:
  


Current Time: Mon Dec 05 09:11:10 CST 2016

Total time taken to generate the page: 0.09317 seconds