|
|
| Re: Does compute statistics and Coalesce Tablespace Imporve Performance [message #65506 is a reply to message #65505] |
Fri, 08 October 2004 22:01  |
 |
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.
or
trash your DMT ( dictionary managed tablespaces )
and
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
