Re-SPECT.
Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Washington, Denver, Sydney - Hotsos Symposium 2004, March 7-10 Dallas - Visit www.hotsos.com for schedule details... -----Original Message----- [EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 3:29 PM To: Multiple recipients of list ORACLE-L Yes, complicated with as simple a combination as tables and their indexes. Consider even more variables introduced by using LOBs and interMedia Text. In a past job, I used to make myself crazy analyzing and placing all the segments involved with interMedia-indexed CLOBs. Here's what I came up with: - the main table on its own spindle - the out-of-line CLOB segments on a 2nd spindle - the "regular" indexes for the main table on a 3rd spindle - the "I" table part of the interMedia index on a 4th spindle - the "K", "R" and "N" tables and "I" index segments on a 5th spindle This was all on 22 JBODs (36GB each) under 8.1.7 / Win2k. The 13 million CLOB documents that were online and indexed made the database about 200GB. It was beautiful (to this nerd) to watch the disk I/O on the Win2k Performance Monitor as different queries would hit different parts of those segments at different times. Though it probably wasn't, the PerfMon graphs looked like there was a lot of parallel work going on across all those spindles. That database was used by only one or a very vew users at a time, doing similar queries, so I think performance benefitted from that segment separation. I would definitely agree, though, that with dozens or hundreds of concurrent users, I would have had to closely monitor tablespace / datafile I/O and shuffle datafiles around to better distribute load across available spindles. That would especially hold true if user activity coincided with the nightly loading and reindexing of up to 250,000 new CLOBs. No particular point here, but I/O distribution was a consideration for segment-to-tablespace mapping, even though an equal (or greater) consideration was differing extent sizes - 1MB for tables and "regular" indexes 100MB for the CLOB segments. ...all on LMTs, of course. Here at AISD, almost all our 8.1.7 databases live on the 14 RAID-5 LUNs presented by our HP XP-512 array (that's just something we've gotta live with, though I'm definitely a BAARFist). Most of the DBs are on 3 HP-UX boxes, but one is on Win2k. I've not yet had a chance to map all the pieces of all the DBs to see which pieces share which physical drives, but I/O hasn't seemed to hurt DB performance. Bad performance continues to be more than adequately addressed by the horribly-inefficient SQL produced by our two 3rd Party apps (Student Info. System and Financials System). I guess that goes to show that segment distribution - even on RAID-5 - is an insignificant factor when compared to bad SQL (producing tens of millions of unnecessary logical I/Os, in this case). Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] "Cary Millsap" <[EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> sos.com> cc: Sent by: Subject: RE: should you seperate indexes from tables in seperate datafiles? [EMAIL PROTECTED] .com 07/15/2003 12:19 PM Please respond to ORACLE-L The thing that occurred to me a few years ago (as a result of a test designed by Craig Shallahamer) is that "what disks do" gets very, very complicated when you add users. On any system busy enough to have a performance problem, the odds are usually slim that a disk is just "sitting there" waiting for your next I/O call. On a busy system, someone else's I/O call is almost always going to intercede between two of *your* I/O calls. As has been said many times, many ways... - DO separate tables and indexes into different tablespaces. There are lots of reasons you should do this. - DON'T necessarily feel that you have to put the index and data tablespaces on different devices. One decision criterion is performance: don't ever put two files on the same device if the sum of their I/O-per-second rates exceeds the I/O-per-second capacity of the device. Another decision criterion is availability: don't ever put more data on a device than you can recover in your acceptable downtime window. The list goes on... Cary Millsap ... -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Cary Millsap INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).