-
Hi,
I came across this on the web
http://www.evdbt.com/MythsExtPerf.doc
This totally debunks the theory of fragmentation and the effects it has on the database.
My whole life till today I was under the impression that fragmentation is Bad and used to constantly monitor it.
Can others please give their opinions on fragmentation and its effects.
Thanks
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
-
I read another article about a year ago saying basically the same thing. It had a graph showing that performance was only adversely effected when there were hundreds or thousands of extents.
The key, however, was uniform sized extents. We've since completely revamped our object sizing in tablespaces with uniform extents (tablespace1_64k, tablespace2_512k, tablespace3_1m, etc.)
-
I agree completely with Ken! We use Uniform Extents and don't reorg unless we get ~1000 extents. We use size 128K, 4M and 128M extent_size tablespaces. We have seen some improvement in performance, but mostly we save because we don't have "unusable" space in our tablespaces. Everything is reused.
For a good article on this and how to implement, please see:
http://otn.oracle.com/deploy/performance/pdf/defrag.pdf or article "How to Stop Defragmenting and Start Living" at http://www.hotsos.com/catalog/
Good luck!
-
I'd be more concerned about SPACE Utilisation and Administration TIME rather than PERFORMANCE. The more fragmented your tablesapce, the more space you'll need to store the segments it contains, and the higher the change of processes falling over therfore increase time to fix the issue.
I have a case in which I have > 2000 tables truncated and reloaded each week and their associated indexes (of course are rebuilt). Not all together mind, about 400 each week day. Therefore, truncating and recreating indexes that are different sizes can can cause the process to fail because the associated index with different sized extents live in the same tablespace. Even coalescing doesn't soemtime return enough free space to recreate the object. Therefore leaving the only optioon to expand the tablespace again.
Obviously, maintaining uniform extents is the answer, but I'm just pointing out that the issue with fragmentation is more than just a PERFOMANCE issue. It is also a SPACE and TIME factor in which additional administration is required for fragmentated tablespaces... and thereby elevating my hate of teh COMPRESS=Y optiin in Exports.
Cheers,
OCP 8i, 9i DBA
Brisbane Australia
-
I have a few doubts....
1. If uniform extents is/was the solution why was this never told in oracle documentation or anywhere till recently. I am sure they all knew it all along.
2. There is no smoke without fire .. why did this myth ever start in the first place if it has no effects. It only make learning/managing oracle a nightmare.
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
-
I believe that changes to more recent versions have made the # of extents relatively a non-issue.
I'm with you .. I remember having a rule that we configure our tables and indexes to have no more than 5 extents over 2 years of use. I was deathly afraid of having a bunch of extents. It only makes sense to lessen the number of hops from extent to extent, right? That's got to have some additional overhead, right?
I've got a better technical explanation in a white paper somewhere around here. I'll dig through my stuff and post the details when I find them.
-
The performance impact of multiple extents is historic, not a myth. The amount of time to move from extent a to extent b is nothing today. Fifteen, twenty, twenty-five years ago that was not the case; the amount of time required to move the head and await the rotational delay could be the difference between acceptable and unacceptable.
I can tell you from experience, if you've gotten your burnt by something it's real difficult to 'put it in the past'.
FYI. Try uniform extents, it's a lot of work up front, but saves a huge amount of work later.
-
THere is another aspect to fragmentation.
15 years ago (when I started with Oracle) the emphasis was not on transactions but on reports. Yes, we could do transactions, but the 'paradigm' was report generation.
Large numbers of small extents (the modern tendency) gives better transaction performance, but 'worse' report performance. Small numbers of large extents give better report performance but waste a lot of time and resources for small transactions.
Joseph R.P. Maloney, CSP,CDP,CCP
'The answer is 42'
-
heres another link supporting this
http://asktom.oracle.com/pls/ask/f?p...0_P8_DISPLAYID,F4950_P8_B:730289259844,Y
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
-
Does all this mean that one day pctincrease and dictioanry managed tablespaces will be obsolete.
Thanks
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|