Re: Effects of framentation

> How can I do that? How can I prove that queries answer too slow
> because of the fragmentation.

You won't be able to prove this because the premise is false. Fragmentation does not cause query slowness. In fact, if you are using Locally Managed Tablespaces, then fragmentation is not only possible, it is almost 100% likely for some of the segments in the tablespace.

In many systems, it is common for a query that ran fine in the beginning to become slow over time. In the beginning, many people don't notice it becoming slower and slower. But one day, everyone starts complaining about how the query is now slow. This is a common performance scenario. In reality, the query was not executed properly enough to scale as the data grows.

If your queries are slow, then there are two things you need to concentrate on:

1. Make sure that your query is optimized. This involves running Explain Plan and making sure that the execution path of the query is the most optimal. This should also involve the CBO. This is quite an involved topic, too large to go into detail here.
2. Run a 10046 trace with level 8 or 12 and examine the trace file from running the poor performing query. The trace file will tell you where the query is bottlenecked.

Concentrating on fragmentation of the database segments will not solve your problem. If you do manage to defragment the database segment and your query does perform better, then I would argue that there are other contributing factors at work that have nothing to do with fragmentation but were fixed when you defragmented. For instance, you could have tons of migrated rows. These migrated rows can cause performance problems. In the process of defragmenting, your rows were no longer migrated thereby fixing your problem. Or, you could have had old, stale CBO statistics and after you defragmented, you calculated new statistics. Or, you could have indexes that need rebuilding (rare, but it happens) and after you defragmented, you rebuilt the index and that solved your performance problem.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"