Home » RDBMS Server » Performance Tuning » Does compute statistics and Coalesce Tablespace Imporve Performance
Does compute statistics and Coalesce Tablespace Imporve Performance [message #65505] Fri, 08 October 2004 21:27 Go to next message
Messages: 16
Registered: September 2004
Junior Member
I just want to know that does Compute Statistics from Oracle entreprise manager and Coalescing tablespace improves any performance of Oracle server or it degrades also some time
Re: Does compute statistics and Coalesce Tablespace Imporve Performance [message #65506 is a reply to message #65505] Fri, 08 October 2004 22:01 Go to previous message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
quoting docs

If you find that fragmentation of space is high
(contiguous space on your disk appears as non-contiguous),
you can coalesce your free space in a single space transaction.

< /endquote >

So, the contigous free extents are added together to form a bigger free extent.
Coalescing can ONLY have an effect after a drop or truncate.

Usually SMON will take care of this.
By always using pctincrease=0 and initial=next=constant value, YOU can avoid coalescing.
trash your DMT ( dictionary managed tablespaces )
always USE an LMT ( locally managed tablespace). with LMT no frgmentations, no need for coalescing.

Compute Statistics / Estimate statistics are outdated. (still supported, But Oracle strongly recomends to use
dbms_stats. ANALYZE only for listchainrows / validate structure).

by gathering statistics, much information about tables and indexes (statistics) are updated in dictionary.
Oracle CBO uses these statistics to lay an 'shortest / easiest ' route to fetch the data.
for example
Based on statistics, Oracle decides whether to use and index or not.
So for a healthy database, the statistics should be always updated.

>>or it degrades also some time
It depends on how the options are used .
Without proper investigation, No conclusions can be given.
Previous Topic: Question on indexes
Next Topic: How to calculate Selectivity and Skew
Goto Forum:

Current Time: Wed Feb 28 04:48:28 CST 2024