Home » RDBMS Server » Performance Tuning » Estimate_Percent for Analyze Schema
Estimate_Percent for Analyze Schema [message #192348] Tue, 12 September 2006 00:32 Go to next message
rkulasek
Messages: 15
Registered: April 2006
Junior Member
Hi

We are running a database where the largest tables could run into a few billion rows. The rest of the tables load only thousands of rows.
We are trying to do analyze schema on this db.
Is there any recommended estimate_percent we can use for analyzing schema?

Does it make sense to use individual estimate_percent numbers for each table? If so, how do we determine what estimate_percent to use for which table?

Your inputs will be very useful

Thanks much
Raja
Re: Estimate_Percent for Analyze Schema [message #192407 is a reply to message #192348] Tue, 12 September 2006 03:56 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> largest tables could run into a few billion rows.
Are these tables partitioned? I beleive so.
then collect statistics partition wise.
For the rest, you can collect only the Stale.
More information in documentation.
Regards.
Re: Estimate_Percent for Analyze Schema [message #192417 is a reply to message #192348] Tue, 12 September 2006 04:56 Go to previous messageGo to next message
rkulasek
Messages: 15
Registered: April 2006
Junior Member
Hi Mahesh

The tables are non partitioned.

By the way, could you explain how to use "analyze table with estimate percent" for these tables?
How do I determine what estimate_percent to use for which table?
If you can provide links to the documentation also, it would help

Thanks much
Raja
Re: Estimate_Percent for Analyze Schema [message #192455 is a reply to message #192417] Tue, 12 September 2006 08:18 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
I would consider to partition those large tables.
>>By the way, could you explain how to use "analyze table with estimate percent" for these tables?
I would rather use DBMS_STATS insted of analyze.
http://download-east.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_stats.htm#999107
Re: Estimate_Percent for Analyze Schema [message #192867 is a reply to message #192348] Thu, 14 September 2006 00:25 Go to previous message
rkulasek
Messages: 15
Registered: April 2006
Junior Member
Thanks very much for your information Mahesh.

My next question is --- is it possible to determine what estimate_percent to use with what tables when gathering table stats?

For example,
For the biggest table, use x%
For the least populated table, use y%
etc.

Your inputs are much appreciated

Thanks
Raja

Previous Topic: Analyze increases cost
Next Topic: Number of Datafiles in a Tablespace
Goto Forum:
  


Current Time: Mon Dec 05 03:14:46 CST 2016

Total time taken to generate the page: 0.21215 seconds