| DBMS_STATS.GATHER_SCHEMA_STATS [message #394189] |
Thu, 26 March 2009 00:39  |
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 #394201 is a reply to message #394189] |
Thu, 26 March 2009 01:06  |
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 #394284 is a reply to message #394207] |
Thu, 26 March 2009 06:44 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
