Re: FW: Oracle ?

Dirk Moolman wrote: > I just wanted to find a couple of people who come from an Informix > background like me, but also know Oracle. I just completed the Oracle > Fundamentals I course, but still have some questions that the instructor > couldn't answer. > > I think the specialise in their specific course, and the Fundamentals > guy couldn't answer my performance tuning questions. > > One of my biggest questions - why does every Oracle dba I speak to, say > that fragmentation of tables / too many extents (non-contiguous space) > does not matter. Surely that should still matter, no matter which > database you use ? > > Another big question - why does Oracle say that cooked space should be > used rather than raw space - and this answer I got from their > performance tuning guy as well. > > Dirk The reason the fragmentation to which you refer doesn't matter is that there is no such thing as non-fragmented data in the real world. I'll use Oracle terminology to explain this. For those that don't know Oracle substitute dbspace for tablespace and chunk for datafile (hope I got that right). Extents are extents in both. Assume you have a single tablespace consisting of a single data file containing a single table placed originally laid down on a clean disk. With some operating systems ... that single file will be fragmented as the o/s intentionally does so (Windows for one). But lets assume we are working with a different o/s that creates one contiguous file. Now take that theoretically perfect situation, above, and modify it to be a bit more realistic: Add a dozen new tables with constraints and indexes and a data dictionary and dozens or hundreds of simultaneous users. The heads will be flying all over the place. The fact that anything is contiguous will give you no measurable difference in performance or scalability. With Oracle it is not said that all cooked space is better than raw: Or at least it shouldn't be. But the reality is that almost all big installations are now done against SAN or NAS where no one writes to a file system anyway: Rather all writing is done to a RAM cache. Also, Oracle provides its own code for writing directly to the file system so the penalty of operating system writes may not exist. -- Daniel A. Morgan University of Washington damorgan@x.washington.edu (replace 'x' with 'u' to respond)