Home » RDBMS Server » Performance Tuning » gather stats how?
gather stats how? [message #355345] Thu, 23 October 2008 15:35 Go to next message
ArvindBhope
Messages: 55
Registered: June 2008
Member
Hi there!!

I would like to gather stats on a table. Iam aware that I have to consider the following
Number of rows
Number of blocks
Average row length

BUt how do you come to conclusion what is the percentage and are there any specific rule or formulas for coming to conclusion on this item ?

thanks!
Arvind

Report message to a moderator

Re: gather stats how? [message #355348 is a reply to message #355345] Thu, 23 October 2008 15:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use dbms_stats.

Regards
Michel

Report message to a moderator

Re: gather stats how? [message #355349 is a reply to message #355345] Thu, 23 October 2008 15:40 Go to previous messageGo to next message
ArvindBhope
Messages: 55
Registered: June 2008
Member
thanks! iam aware of that dbms_stats. I was looking at the values ...how are they passed and on what basis ?

thanks again,
Arvind

Report message to a moderator

Re: gather stats how? [message #355354 is a reply to message #355349] Thu, 23 October 2008 15:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which values?

Regards
Michel

Report message to a moderator

Re: gather stats how? [message #355504 is a reply to message #355349] Fri, 24 October 2008 09:19 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
DBMS_STATS calculates/estimates the number of rows, number of blocks and avg row length - you don't pass them in to it.

Report message to a moderator

Re: gather stats how? [message #355831 is a reply to message #355345] Mon, 27 October 2008 16:28 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Consider the following:

DBMS_STATS.GATHER_TABLE_STATS (
   ownname          VARCHAR2, 
   tabname          VARCHAR2, 
   partname         VARCHAR2 DEFAULT NULL,
   estimate_percent NUMBER   DEFAULT NULL, 
   block_sample     BOOLEAN  DEFAULT FALSE,
   method_opt       VARCHAR2 DEFAULT 'FOR ALL COLUMNS SIZE 1',
   degree           NUMBER   DEFAULT NULL,
   granularity      VARCHAR2 DEFAULT 'DEFAULT', 
   cascade          BOOLEAN  DEFAULT FALSE,
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL,
   statown          VARCHAR2 DEFAULT NULL,
   no_invalidate    BOOLEAN  DEFAULT FALSE);


Quote:
estimate_percent

This parameter is similar to the old “estimate statistics sample x percent” parameter of the ANALYZE command. The value for estimate_percent is the percentage of rows to estimate, with NULL meaning compute. You can use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the best sample size for good statistics
. 

exec dbms_stats.gather_table_stats(user,'a',cascade=>true)

exec dbms_stats.gather_table_stats(user,'a',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,cascade=>true)


Good luck, Kevin

Report message to a moderator

Re: gather stats how? [message #356080 is a reply to message #355831] Wed, 29 October 2008 07:13 Go to previous messageGo to next message
ArvindBhope
Messages: 55
Registered: June 2008
Member
Kevin,

I wanted to know how do we come to a result that we need to estimate_percent=>xx ...

Are there any abse lines over here to decide ?

thanks again,
arvind

Report message to a moderator

Re: gather stats how? [message #356101 is a reply to message #356080] Wed, 29 October 2008 09:02 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Well, I don't have an exact answer for you but I can say this:

There is of course a large mathematical body of theory behind statistics collection. I believe IBM had something called communication theory that provided many of the basic formulae used today.

That said, most of us can't know this stuff. I for one do not have the math background, nor do I really care. It is enough for me to know that the Oracle database will do it all for me.

If I can't do compute statistics then I use the auto flag and let the software do the work:

estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE

Kevin

Report message to a moderator

Re: gather stats how? [message #356102 is a reply to message #356080] Wed, 29 October 2008 09:02 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Well, I don't have an exact answer for you but I can say this:

There is of course a large mathematical body of theory behind statistics collection. I believe IBM had something called communication theory that provided many of the basic formulae used today.

That said, most of us can't know this stuff. I for one do not have the math background, nor do I really care. It is enough for me to know that the Oracle database will do it all for me.

If I can't do compute statistics then I use the auto flag and let the software do the work:

estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE

Kevin

Report message to a moderator

Re: gather stats how? [message #356103 is a reply to message #355345] Wed, 29 October 2008 09:12 Go to previous messageGo to next message
ArvindBhope
Messages: 55
Registered: June 2008
Member
thanks Kevin for the inputs,

Guys, do we have any inputs,methods over here on the calculation of the estimate percentage?

thanks,
Arvind

Report message to a moderator

Re: gather stats how? [message #356108 is a reply to message #355345] Wed, 29 October 2008 10:14 Go to previous messageGo to next message
efachim
Messages: 42
Registered: July 2008
Member
Hi,

I vaguely remember Oracle recommending certain percentages to use for the estimate, depending on how large the table was. Is that what you are looking for, or what those figures were arrived at?

rgds, E

Report message to a moderator

Re: gather stats how? [message #356109 is a reply to message #355345] Wed, 29 October 2008 10:20 Go to previous messageGo to next message
ArvindBhope
Messages: 55
Registered: June 2008
Member
hi efachim,

yeah ,iam looking at that info only.And along with that how those figures were arrived at ? ...like for a X table of xyz parameters abc should be the estimate percent.

let me know if you are looking at any other info.

thanks,
Arvind

Report message to a moderator

Re: gather stats how? [message #357237 is a reply to message #356102] Tue, 04 November 2008 06:51 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Let me rephrase what Kevin said.

What evidence do you have that the Auto setting for estimate_percentage is gathering an inaccurate set of statistics.

Additionally, what level of performance gain do you expect to see by setting the value manually?

You can empirically check the percentage of rows analyzed by looking at the Sample_Size column in DBA_TABLES and DBA_INDEXES, but to the best of my knowledge, Oracel have not made the algorithm they use public.

Report message to a moderator

Re: gather stats how? [message #357239 is a reply to message #357237] Tue, 04 November 2008 07:04 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Here is an article about the algorithm in 11g, which can be sumarised as 'AUTO generates very good stats'

Report message to a moderator

Previous Topic: select does a full table scan. 130.000.000 rows
Next Topic: Gather statistics for scale up / down decision process
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Jun 29 04:32:13 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.