Home » RDBMS Server » Performance Tuning » analyze table
analyze table [message #191253] Tue, 05 September 2006 07:29 Go to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Hi All,
can the command of 'ANALYZE TABLE 'xxx'COMPUTE STATISTICS;
have anything to do with the query execution time on the same table 'xxx'.

Thanks,

best Regards
Re: analyze table [message #191256 is a reply to message #191253] Tue, 05 September 2006 07:33 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> have anything to do with the query execution time on the same table 'xxx'.
It has everything to do with the query.
By 'analyzing' the table you are collecting the vital statisitcs/real time information using which the CBO will estimate a shortest/best path to fetch the data.
Better use DBMS_STATS instead of analyze table.
You need to collect statistics on both tables and indexes.
Re: analyze table [message #191264 is a reply to message #191256] Tue, 05 September 2006 07:39 Go to previous messageGo to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Hi,
wel i have a query on a table and the execution time of the query was 20 sec ,before i intiate the 'ANALYZE TABLE 'xxx' COMPUTE STATISTICS;' it becomes no more than 2 sec after that.
if this is the solution for the performance problem i face ,i will consider putting it in the script that run the query..

Thanks,
Best Regards
Re: analyze table [message #191267 is a reply to message #191264] Tue, 05 September 2006 07:44 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>if this is the solution for the performance problem i face ,i will consider putting it in the script that run the query..
Did you determine what is the 'problem' here?
Statistics collection is something you need to do as part of regular database maintenance.

>>,i will consider putting it in the script that run the query
doin that everytime you run the query will help nothing ( and may produce negative effect).
Collect statistics only after there is a major change in data.
Else, once a while stats on tables/indexes would do.
Search this forum.
Read the discussions.
Previous Topic: Check if the Index is performing good or not
Next Topic: advantage of append mode ??
Goto Forum:
  


Current Time: Sun Dec 04 20:33:54 CST 2016

Total time taken to generate the page: 0.11045 seconds