Home » RDBMS Server » Performance Tuning » Advice on Gather_stats (Oracle 8i, 9i and 10G)
Advice on Gather_stats [message #522071] Tue, 06 September 2011 06:12 Go to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
Hi,

I am in the situation of analyzing all the objects resides in some schemas for 63 databases. Since the objects(>80%) were not getting analyzed for more than 2 years.

Which is the best method to analyze the particular schema objects? Since some of my colleagues are suggested, not to go with schema analyze. Since it will make some problem in the performance side.And they said, it always best to go with individual table analyze.

Can anybody help me to decide which is the best approach for analyzing the schema objetcs by without affecting the query performace negatively?

Report message to a moderator

Re: Advice on Gather_stats [message #522098 is a reply to message #522071] Tue, 06 September 2011 08:06 Go to previous messageGo to next message
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well 10g has a job to automatically gather stats for you. As for the other versions, what makes your colleages believe this:

sathik wrote on Tue, 06 September 2011 12:12
Hi,

Since some of my colleagues are suggested, not to go with schema analyze. Since it will make some problem in the performance side.And they said, it always best to go with individual table analyze.

Report message to a moderator

Re: Advice on Gather_stats [message #522103 is a reply to message #522098] Tue, 06 September 2011 08:37 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I am in the situation of analyzing all the objects resides in some schemas for 63 databases.
>Since the objects(>80%) were not getting analyzed for more than 2 years.

You can NOT utilize LAST_ANALYZED to decide when ANALYZE/DBMS_STATS was run against object.
if/when table data is static, then no new statistics are gathered so LAST_ANALYZED column remains unchanged

Report message to a moderator

Re: Advice on Gather_stats [message #522382 is a reply to message #522103] Fri, 09 September 2011 02:54 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
hi Sathik,

When you analyze complete schema then you are updating statistics into dynamic performance views.If you are going
to analyze schema then your rollback segment usage will increase.
But its always better to analyze specific tables according to your need.

If you are trying to tune your database then you should analyze complete schema before running statspack report.
Actually it depends on situation what you need.If you are working in normal scenario then only analyze to specific
tables according to your requirement.

just use this command:

Analyze table IN Oracle 8i,9i,10g: 


begin
 dbms_stats.gather_table_stats( 
      ownname          => 'NCL_PIMS', 
      tabname          => 'PHYSICAL_RESOURCE_SPEC',
      estimate_percent => dbms_stats.auto_sample_size, 
      method_opt       => 'for all columns size skewonly', 
      cascade          => true,
      degree           => 7
     );
end;


Analyze schema in Oracle8i,9i,10g:


begin
DBMS_STATS.GATHER_SCHEMA_STATS( 'PIRM_OWNER'); 
end;


DBMS_STATS package available for Oracle8i and above.

Report message to a moderator

Re: Advice on Gather_stats [message #522384 is a reply to message #522382] Fri, 09 September 2011 03:16 Go to previous messageGo to next message
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
Senior Member
msol25 wrote on Fri, 09 September 2011 08:54
But its always better to analyze specific tables according to your need.

Why?

Report message to a moderator

Re: Advice on Gather_stats [message #522391 is a reply to message #522384] Fri, 09 September 2011 03:57 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member

Thanks Cookiemonster for your query,

Actually when we are analyzing complete database then it will always better approach,but sometime
our shared pool allocation increase due t sort segment in case of Shared server envtt. and database
performance degrade.

Because schema can have Merge-sort full table scan then in that case our redo entry will increase
and due to that shared pool memory fragmentation will also increase.

Report message to a moderator

Re: Advice on Gather_stats [message #522393 is a reply to message #522391] Fri, 09 September 2011 04:04 Go to previous messageGo to next message
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
Senior Member
That sounds like an issue with a rather specialized setup.

Report message to a moderator

Re: Advice on Gather_stats [message #522397 is a reply to message #522393] Fri, 09 September 2011 04:25 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
Hi Cookie,

But we can't commit it will not happen..

Report message to a moderator

Re: Advice on Gather_stats [message #522401 is a reply to message #522397] Fri, 09 September 2011 04:32 Go to previous messageGo to next message
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
Senior Member
I have no idea what you mean

Report message to a moderator

Re: Advice on Gather_stats [message #522402 is a reply to message #522382] Fri, 09 September 2011 04:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
DBMS_STATS package available for Oracle8i and above

But not "dbms_stats.auto_sample_size".
Once again take care of what you post.

Regards
Michel

Report message to a moderator

Re: Advice on Gather_stats [message #522403 is a reply to message #522401] Fri, 09 September 2011 04:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
cookiemonster wrote on Fri, 09 September 2011 11:32
I have no idea what you mean


Neither I am.

Regards
Michel

Report message to a moderator

Re: Advice on Gather_stats [message #522409 is a reply to message #522403] Fri, 09 September 2011 05:00 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
I assume large_pool is not configured and we are using shared_pool for sort segment allocation.

Report message to a moderator

Re: Advice on Gather_stats [message #522412 is a reply to message #522409] Fri, 09 September 2011 05:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Ah! If you assume that this is perfectly clear now.

Regards
Michel

Report message to a moderator

Re: Advice on Gather_stats [message #522420 is a reply to message #522412] Fri, 09 September 2011 05:17 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
Many Thanks Michel..

Report message to a moderator

Re: Advice on Gather_stats [message #522493 is a reply to message #522420] Sun, 11 September 2011 00:18 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
hi msol25,

I am not clear in your answer. Before clearing that, i like to post my question once again for all our better understanding. for ex, I have 100 tables in a schema. Those objects are not analyzed since last 2 years. I like to analyze all the objects in that schema to improve the query performance. In this case,can u pls tell me which is the best approach interms of performance either by analyzing the schema using gather_schema_stats or analyzing 100 tables individualy using gather_table_stats?

Report message to a moderator

Re: Advice on Gather_stats [message #522494 is a reply to message #522493] Sun, 11 September 2011 00:25 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
gather schema stats

Report message to a moderator

Re: Advice on Gather_stats [message #522495 is a reply to message #522493] Sun, 11 September 2011 00:26 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Those objects are not analyzed since last 2 years.

post SQL & results that support statement above.
Has table content changed in the last 2 years?
if table content remained constant in last 2 years, why should statistics be (re)calculated to remain the same?

Report message to a moderator

Re: Advice on Gather_stats [message #522496 is a reply to message #522495] Sun, 11 September 2011 00:44 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
yes blackswan. The content has been changed.

Report message to a moderator

Re: Advice on Gather_stats [message #522497 is a reply to message #522496] Sun, 11 September 2011 00:46 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
sathik wrote on Sat, 10 September 2011 22:44
yes blackswan. The content has been changed.


post SQL & results that show above is true.

do you believe me that I taught my goat to fly?

Report message to a moderator

Re: Advice on Gather_stats [message #522509 is a reply to message #522497] Sun, 11 September 2011 01:25 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
I asked the same question whatevr u asked to the application team, they said the same answer whatever I said. So I can't able to provide the proof for the data difference.

Report message to a moderator

Re: Advice on Gather_stats [message #522510 is a reply to message #522509] Sun, 11 September 2011 01:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
To answer your question:
1/ delete all previous statistics gathered by analyze using analyze command (delete statistics option)
2/ gather new statistics using dbms_stats package.

Analyze command is obsolete to gather statistics, no more use it (and you use imp/exp always add statistics=none option).
In the current versions, always use dbms_stats; and in 10g and up, let the standard job gather them for you (unless and until you find some statistics are not appropriate for you).

Regards
Michel

Report message to a moderator

Re: Advice on Gather_stats [message #522513 is a reply to message #522510] Sun, 11 September 2011 03:11 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
hi Sathik,

Please follow steps given my michel and after that your v$ views(dynamic performance views) will populate with correct statistics.

Then check your hit ratios.

Report message to a moderator

Re: Advice on Gather_stats [message #522514 is a reply to message #522513] Sun, 11 September 2011 03:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Dynamic views does not contain statistics on tables gotten by dbms_stats...
Static DBA/ALL/USER_%STATISTICS dictionary views do.

Regards
Michel

Report message to a moderator

Re: Advice on Gather_stats [message #522515 is a reply to message #522513] Sun, 11 September 2011 03:38 Go to previous message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
Then check your hit ratios.

Which hit ratios?
It seems you've been out of Oracle world since the previous century.
I think you shuld reactualize your knowledge reading documentation and articles from versions that are from this century.

Regards
Michel

Report message to a moderator

Previous Topic: Don't Know how to start performance tuning.
Next Topic: pl/sql
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Nov 22 01:04:57 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.