|
|
Index: Date Index | Thread Index
[Date Prev] | [Date Next] | [Thread Prev] | [Thread Next] [OAUGNetDBA]-Re: Oracle Apps on EMC CX500
Rather than a discussion of hypothetical IO issues, we just looked at our own situation and made reasonable conclusions. My disk contention comments are based upon our own experience that while many large queries involved full table scans, other simultaneous queries were primarily using indexed row lookups and index scans. Our decision to move to this layout was based upon analysis of hot data files in the existing system. I think this is the most important factor. Other performance factors included partitions and parallel query. (Parallel queries involving multiple partitions were improved when the partitions were separated onto different LUNs.) - brad Jurijs Velikanovs wrote: Brad, Declaimer: I am not arguing (because I don't know the right answer, and there is probably no one right answer), but would like to make a healthy discussion in an IO subsystem context. I am not advocating SAME. I am not an expert in IO subsystems.That helped with physical level disk contention and when doing large loads / extracts in parallel, we were glad we'd separated the I/O.Can you please explain to me (us) how it (separating ind/tbl across different LUNs) helps with disks contention? My thoughts in that context are as follows: I. Indexed Reading a. To my limited understanding at the time then a process doing reading of indexed data through an index it accessing index (I) and data (D) blocks serially. First of all it is reading index's blocks then data blocks. Separating index data from table data on different IO spins doesn't help or improve performance. b. Even if during an indexed read one process makes multiple IO requests at the same time (using asynchronous IO for example) indexes tends to be more compact and ~80-90% of the data Oracle access is table data. In that case more IO spins we have better performance we got (stripe D/I on the same LUNs is better). II. Full tables scan (TFSC) a. In single process access both solutions are equal. There is no significant performance deference if we separating I from D or not. b. In concurrent TFSC-s it is better to have more IO spins. In that case putting D&I on the same LUN located on more physical disks looks preferable. III. Writing As we know writing in Oracle is done by two processes (in some cases excluding direct writes): DBWR & LGWR. a. DBWR has to write blocks (independently I or D) from CACHE BUFFERS to the original locations in data files. Probably most of the blocks are written in random locations. More IO spins we have got in our system better. b. LGWR – looks like it is good idea to separate those files from D&I. But 1. in that context we are discussing Data & Indexes 2. In most cases REDO LOG files size is less then 1-2% of all data and in most configurations those are located together on the same LUN as Data & Indexes. 3. In ideal world I would dedicate one IO spin for each REDOLOG file, one group (LUN) of discs to ARCH directory. IV. Maintenance. Creating/Recreating indexes. Moving tables. etc. If table data if big enough then Oracle process (or processes, if we are doing it in parallel) is reading data, sorting it and writing to a temp tablespace. As second part process reading data from the temp tablespace and writes it to index tablespace. In that case in context of performance it is better to separate TEMP tablespace from D&I data. VI. Convenience in LUNs management. One consideration in taking decision to stripe multi-terabyte database across single LUN or multiple LUNs is inconvenient/convenient of administration of that one big FS. To my mind there might be difficult to perform some maintenance task having huge single FS.It looks like it is better to separate REDO from TEMP from D&I rather I from D.Just my 0.02£ Yury On 6/29/06, Brad Simmons <bsimmons@lanl.gov> wrote: -- #=================================================================== . Brad Simmons . Information Systems & Technology; Business Systems Infrastructure . Los Alamos National Laboratory, MS P223, Los Alamos, NM 87544 . bsimmons@lanl.gov . Schedule B . v: (505) 667-9156 . f: (505) 665-0218 . . "Better a witty fool than a foolish wit." . - Shakespeare ############################################################# This message is sent to you because you are subscribed to the mailing list <OAUGNetDBA@oaug.com>. To unsubscribe, E-mail to: <OAUGNetDBA-off@oaug.com> To switch to the FEED mode, send any message to <OAUGNetDBA-feed@oaug.com> To switch to the DIGEST mode, E-mail to <OAUGNetDBA-digest@oaug.com> To switch to the INDEX mode, E-mail to <OAUGNetDBA-index@oaug.com> Send administrative queries to <OAUGNetDBA-request@oaug.com> Index: Date Index | Thread Index Thank you for using the OAUG Listserver Archive.
|
|
