RE: SAME and separating disk and index tablespaces
Dave, during a 'db file sequential read', an index is _not_ accessed sequentially. An index is not a sequential structure, so reading from an index in order will cause multiple seeks on the index itself. And we're talking single user here regards, Hans Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 08 Oct 2003 16:29:25 -0800 Great responses ! Thanks very much .. -Original Message- Dave Hau Sent: Wednesday, October 08, 2003 3:19 PM To: Multiple recipients of list ORACLE-L Hi Gaja, I agree that throughput can always be improved by adding more drives to the striped array. However, this does not improve access time. If you have your tables and indexes on the same striped array, necessarily the two I/O's have to be done sequentially, incurring two times access time at a minimum. However, if you separate the two into different arrays, then you can access them in parallel, starting to get data from each disk array in 1* access time. This makes sense esp. in scenarios where response time is more important than throughput, and also in use cases where your access pattern is random rather than sequential. So I feel that there's a tradeoff between access time and throughput. If you have ten drives, and you stripe all of them into a single array and put both your data and indexes onto this array, you get maximum throughput but you're sacrificing access time for throughput. However, if you build two arrays each consisting of five drives, and put your data and indexes onto each array, you get half of the previous throughput, but you get better access time because now your data and index access can be truly in parallel. Regards, Dave [EMAIL PROTECTED] wrote: Hi Hans/Vikas, I tend to agree that the old draconian rule that thou shalt always separate indexes from tables may not apply any more. We used to apply that principle in the past when the number of available spindles was not adequate. Seems like with 256G drives in the market, we are being pushed back in time, in some way!!! The way I look at the problem is purely from an IOPS perspective. For example, if each physical disk is capable of 256 IOPS (ignore the cache configured here) and you have 10 disks in your volume, then the total I/O capacity on this volume is 2560 IOPS. Separation of objects across multiple volumes may becomes an issue, only when the demand for I/O outstrips the supply (in this case 2560 IOPS). Even then, you can always add more drives to the existing volume and restripe, i.e., adding 5 more drives to 10 drives increases the I/O capacity by 50%. At the end of the day, the I/O sub-system does not care, whether it is servicing a data segment, index segment or undo segment. But, in certain environments, that I have dealt with, there has been a need to separate heavily and concurrently accessed objects (does not matter whether these objects are all indexes or tables or both). This may be true only for certain objects and certain queries. So, please don't apply this in a blanket fashion. Empirical data is always the best justification mechnism for a configuration exercise such as this. Plus, you may have partitioning and other requirements such as parallelism that impact the placement and availability of your data. This in turn will control the number of logical volumes that need to be created. I think the idea and philosophy behind SAME is noble - Use all available drives, so that you do not have localized hot-spots. But the implementation of SAME and how many volumes you need in your enviroment, is a function of your custom needs based on your system and application demands. When you over-simplify something, you lose the flexibility. The art factor here (which requires some planning) is in achieving a balance between simplicity, flexibility, performance, manageability and availability. Hope that helps, Gaja --- Hans de Git [EMAIL PROTECTED] wrote: Vikas, Spend an hour on reading this usenet thread: http://groups.google.nl/groups?hl=nllr=ie=UTF-8oe=UTF-8threadm=brjz8 .15%24707.245%40news.oracle.comrnum=1prev=/groups%3Fhl%3Dnl%26lr%3D%26 ie%3DUTF-8%26oe%3DUTF-8%26q%3Drogers%2Bseparate%2Bdata%2Bindex It will open your eyes about separating data/index. Still not sure about the redolog stream...Because of the sequential nature of redologfiles. I've read tests that 'prove' it doesn't matter much whether you separate your redolog from 'ordinary' datafiles or not. It does simplify things when you pure SAME. Regards, Hans Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 08 Oct 2003 09:54:30 -0800 Thanks Gaja ! Does it also make sense from a performance perspective (I/O issues due to concurrent access of index and data ) to separate them or is that point moot once you apply the SAME methodology ? -Original Message- Gaja Krishna Vaidyanatha Sent: Wednesday
RE: SAME and separating disk and index tablespaces
Vikas, Spend an hour on reading this usenet thread: http://groups.google.nl/groups?hl=nllr=ie=UTF-8oe=UTF-8threadm=brjz8.15%24707.245%40news.oracle.comrnum=1prev=/groups%3Fhl%3Dnl%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26q%3Drogers%2Bseparate%2Bdata%2Bindex It will open your eyes about separating data/index. Still not sure about the redolog stream...Because of the sequential nature of redologfiles. I've read tests that 'prove' it doesn't matter much whether you separate your redolog from 'ordinary' datafiles or not. It does simplify things when you pure SAME. Regards, Hans Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 08 Oct 2003 09:54:30 -0800 Thanks Gaja ! Does it also make sense from a performance perspective (I/O issues due to concurrent access of index and data ) to separate them or is that point moot once you apply the SAME methodology ? -Original Message- Gaja Krishna Vaidyanatha Sent: Wednesday, October 08, 2003 9:24 AM To: Multiple recipients of list ORACLE-L Vikas, The answer is an enthusiastic yes. This is purely from an administrative and manageability standpoint. For example, if you have INDEX and DATA segments separated in 2 different tablespaces, the backup of these tablespaces can be done INDEPENDENTLY. This is relevant, as if you were to rebuild your indexes using the NOLOGGING option between 2 backup jobs. If that were the case, then all you will need to do after the rebuild is complete, is to backup only the INDX tablespace. This is a best practice (if not a requirement) in most production shops, unless you think you can re-re-build your indexes in the event of media failure and you lose your INDX tablespace. Hope that helps, Gaja --- vikas kawatra [EMAIL PROTECTED] wrote: Guys, Does it make sense to separate data and index segments into separate tablespaces if you create a single logical volume and all files are striped using the SAME methodology ? Thanks vikas -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: vikas kawatra INET: [EMAIL PROTECTED] = Gaja Krishna Vaidyanatha Principal Technical Product Manager, Application Performance Management, Veritas Corporation E-mail : [EMAIL PROTECTED] Phone: (650)-527-3180 Website: http://www.veritas.com __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gaja Krishna Vaidyanatha 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: vikas kawatra 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). _ Chatten met je online vrienden via MSN Messenger. http://messenger.msn.nl/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hans de Git 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).
Re: More info about _db_writer_max_writes
Thaks, log buffer is 2M. I don't see a connection between the the log buffer and this hidden parameter. BTW: this database issues 1500 ios per second. Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Mon, 06 Oct 2003 08:19:27 -0800 Hans: This parameter limits the max # of buffers can be pending for an AIO operation. Usually you never need to tune (!) this parameter as the default holds good for most platforms. BTW what is the size of the log buffer? You will never have more than 4k pending buffers unless you have a really huge (!) log buffer. KG - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, October 06, 2003 9:19 PM Hi All, The current value for my AIX 4.3.3 / 8.1.7.4 instance parameter _db_writer_max_writes is 4096. I've just raised my aio maxreqs to 16384. Would it be beneficial to increase _db_writer_max_writes? HTH, Hans de Git _ MSN Zoeken, voor duidelijke zoekresultaten! http://search.msn.nl -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hans de Git 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: K Gopalakrishnan 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). _ MSN Zoeken, voor duidelijke zoekresultaten! http://search.msn.nl -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hans de Git 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 help me tune this io done wait event
Hi All, We suffer buffer busy waits and io done waits during batch processing. The batch does direct-path inserts (via Sqlloader and insert-append) in a 16k blocksize database (JFS, AIX). Async io servers = 300, maxreqs = 16384 What is the general approach to tune the io done' wait event? The explanation in the Oracle manual is -of course- not clear to me: The session waits for an I/O to complete or it waits for a slave process to become available to submit the I/O request. This event occurs on platforms that do not support asynchronous I/O. AIX does support async IOWhat is the slave process? Which io has to complete? Which write has to complete? Thanks. Regards, Hans de Git _ Hotmail en Messenger on the move http://www.msn.nl/communicatie/smsdiensten/hotmailsmsv2/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hans de Git 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).
More info about _db_writer_max_writes
Hi All, The current value for my AIX 4.3.3 / 8.1.7.4 instance parameter _db_writer_max_writes is 4096. I've just raised my aio maxreqs to 16384. Would it be beneficial to increase _db_writer_max_writes? HTH, Hans de Git _ MSN Zoeken, voor duidelijke zoekresultaten! http://search.msn.nl -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hans de Git 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).
RE: EMC striping question
Clear. Sure, the cache is nice. EMC are the gods of cacheEventually, the writes have to be made permanent. What happens when the cache is full? Right, EMC blocks io until most of the io has gone to disk. This leaves the poor user waiting, and waiting, and grow a beard. Symmetrix is limited to a 64G cache anyway.I think we should move to OS striping. Thanks. Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Fri, 03 Oct 2003 09:09:42 -0800 Why worry about how it gets written. The cache will make any implementation sing like a bird. And if you think you are having problems just add more cache and that will take care of it. - As told to us by a past EMC Sales Weenie -Original Message- Sent: Friday, October 03, 2003 6:50 AM To: Multiple recipients of list ORACLE-L Hi All, Today I saw an archived thread on orafaq about striped volumes in an EMC Symmetrix. Gaja mentioned that writing to a striped volume is performed in a sequential fashion i.e. spindle B will not start writing block 2 before spindle A has completed writing block 1. Is this still true for a Symmetrix with 5568 firmware? Perhaps it's a better idea to let the OS handle the striping? Regards, Hans de Git _ Chatten met je online vrienden via MSN Messenger. http://messenger.msn.nl/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hans de Git 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: Tony Johnson 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). _ Chatten met je online vrienden via MSN Messenger. http://messenger.msn.nl/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hans de Git 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).
EMC striping question
Hi All, Today I saw an archived thread on orafaq about striped volumes in an EMC Symmetrix. Gaja mentioned that writing to a striped volume is performed in a sequential fashion i.e. spindle B will not start writing block 2 before spindle A has completed writing block 1. Is this still true for a Symmetrix with 5568 firmware? Perhaps it's a better idea to let the OS handle the striping? Regards, Hans de Git _ Chatten met je online vrienden via MSN Messenger. http://messenger.msn.nl/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hans de Git 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).
Re: EMC striping question
Sorry Matt, Just checked with EMC netherlands, unfortunately, I'm right. About the cache: a shared cache is easily flooded with large io's. Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Fri, 03 Oct 2003 06:01:00 -0800 Hi Hans, Absolutely not true, and has not been true for a long time. Writes to an EMC never go directly to disk anyway, and when they do go to disk is purely determined by the microcode algorithms, and will often have nothing to do with stripe layout at all. Thanks, Matt - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 03, 2003 9:50 AM Hi All, Today I saw an archived thread on orafaq about striped volumes in an EMC Symmetrix. Gaja mentioned that writing to a striped volume is performed in a sequential fashion i.e. spindle B will not start writing block 2 before spindle A has completed writing block 1. Is this still true for a Symmetrix with 5568 firmware? Perhaps it's a better idea to let the OS handle the striping? Regards, Hans de Git _ Chatten met je online vrienden via MSN Messenger. http://messenger.msn.nl/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hans de Git 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: Matthew Zito 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). _ Hotmail en Messenger on the move http://www.msn.nl/communicatie/smsdiensten/hotmailsmsv2/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hans de Git 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).
blocksize on AIX
Hi all, I've read on ixora.com.au and other sites that the optimal block size for AIX is 4K, because JFS pages are 4K also. Has anyone of you ever experienced performance problems on AIX due to a larger blocksize? What exactly are the 'read ahead' issues and cpu problems regarding double buffering etc. Are there any alternatives, other than moving to raw volumes? HTH, Hans _ MSN Zoeken, voor duidelijke zoekresultaten! http://search.msn.nl -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hans de Git 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).
Anyone ever implemented aix 5.2 concurrent io ?
Hello everyone, I've come across an IBM whitepaper: improving database performance with aix concurrent io. IBM have tested this with Oracle 9i R2 and claim performance comparable to using raw-volumes. Has anyone in the fatcity community ever tried this? Does Oracle do an open of the database files using the o_cio flag? Or should one mount the jfs2 filesystem using the -o cio option? Looking forward to your reactions. Regards, Hans de Git _ Chatten met je online vrienden via MSN Messenger. http://messenger.msn.nl/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hans de Git 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).
RE: direct path write waits, please help
FYI The application that is causing the wait events is a third party product that really sucks (autocommit, no bind variables, bad data model, etc., etc.) We're on EMC Symmetrix. There are hardly any wait-io's measurable on AIX; the log file sync problem is not so much of a problem; moving to raw volumes for the redologs should put the log file sync waits down in the top-n. Indeed, the direct path writes have a neglible effect on overall response time. I just want to get a good understanding of the 'direct path writes'. sorts (disk) =4 physical writes direct = 2,444,555 physical writes = 2,470,809 Those are statistics gathered in a two hour interval. Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Tue, 29 Jul 2003 10:14:29 -0800 But, I would like to know how this seemingly high wait for 'direct path write' is affecting the overall response time. (ResponseTime = WaitTime + ServiceTime) If the 'CPU used by this session' is not considered in light of these wait times, aren't you getting ready to bark at the wrong tree? - Kirti --- John Kanagaraj [EMAIL PROTECTED] wrote: Hans, Now let me guess Your disks are all RAID 5, right? And you possibly are bottlenecking on CPU as well? It is clear from the Top 5 that writes are an issue across the board, to TEMP (direct path write), Redo (log file sync) and DB files (db file parallel writes). Creating a RAID 1 set of disks and moving at least the TEMP, RBS, Redo (and Arch if present) to this will definitely help. John Kanagaraj Phone: 408-970-7002 (W) Fax: 408 327 3086 (Call/Email prior to fax) -Original Message- Sent: Tuesday, July 29, 2003 8:54 AM To: Multiple recipients of list ORACLE-L Hi All, Please help me tune this i/o related wait event. This is my 8.1.6 statspack top-5 wait list: Top 5 Wait Events ~ Wait % Total Event Waits Time (cs) Wt Time --- direct path write 304,867 35,925 49.83 log file sync 145,015 23,441 32.52 db file sequential read11,3703,684 5.11 file open 9813,326 4.61 db file parallel write 1,8933,115 4.32 You'll notice that 'direct path write' is the most expensive one in the list. I cannot find enough info on the net about this wait event, therefore I'm asking the real experts. What events in Oracle trigger this wait event? In what way is this event different from db file parallel write? I mostly read comments that suggest lots of sorting and parallallel queries. However, most sorts are done in memory and degree = 0 for all tables. Any suggestions are very welcome. Thanks, Hans de Git __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande 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). _ Chatten met je online vrienden via MSN Messenger. http://messenger.msn.nl/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hans de Git 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).
Re: direct path write waits, please help
Could it be that hash joins account for the writes to TEMP without
increasing the sort stats? Or 'group by' statements, perhaps?
In a 10 minute interval, I can see no increase in the number of sorts to
disk, but the writes and reads from v$tempstat increase by thousands.
If that's the case, then I think I should increase sort_area_size and/or
hash_area_size (memory is not an issue...). Please correct me if i'm wrong.
Would it be beneficial to change optimizer_index_caching or
optimizer_index_cost_adj to force Oracle into using more nested loops?
Don't get me wrong: I'm all against throwing hardware at an application that
is so poorly written. But we've past that point, the supplier will not
change its behaviour, and from a functional point of view, the end-users are
very satisfied. Bummer..
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: Wed, 30 Jul 2003 01:24:24 -0800
Hi!
Either your 4 disk sorts are huge generating lot's of IO or there direct
writes aren't because of sorting.
They could be because NOCACHE LOB access for example (also CTAS and direct
path insert). You should view 10046 level 8 output and check in which file
are the IOs occurring.
Tanel.
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Wednesday, July 30, 2003 11:34 AM
FYI
The application that is causing the wait events is a third party product
that really sucks (autocommit, no bind variables, bad data model, etc.,
etc.) We're on EMC Symmetrix. There are hardly any wait-io's measurable
on
AIX; the log file sync problem is not so much of a problem; moving to raw
volumes for the redologs should put the log file sync waits down in the
top-n.
Indeed, the direct path writes have a neglible effect on overall response
time. I just want to get a good understanding of the 'direct path
writes'.
sorts (disk) =4
physical writes direct = 2,444,555
physical writes = 2,470,809
Those are statistics gathered in a two hour interval.
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: Tue, 29 Jul 2003 10:14:29 -0800
But, I would like to know how this seemingly high wait for 'direct path
write' is affecting the
overall response time. (ResponseTime = WaitTime + ServiceTime)
If the 'CPU used by this session' is not considered in light of these
wait
times, aren't you
getting ready to bark at the wrong tree?
- Kirti
--- John Kanagaraj [EMAIL PROTECTED] wrote:
Hans,
Now let me guess Your disks are all RAID 5, right? And you
possibly
are
bottlenecking on CPU as well? It is clear from the Top 5 that writes
are
an
issue across the board, to TEMP (direct path write), Redo (log file
sync)
and DB files (db file parallel writes). Creating a RAID 1 set of disks
and
moving at least the TEMP, RBS, Redo (and Arch if present) to this will
definitely help.
John Kanagaraj
Phone: 408-970-7002 (W)
Fax: 408 327 3086 (Call/Email prior to fax)
-Original Message-
Sent: Tuesday, July 29, 2003 8:54 AM
To: Multiple recipients of list ORACLE-L
Hi All,
Please help me tune this i/o related wait event. This is my 8.1.6
statspack
top-5 wait list:
Top 5 Wait Events
~ Wait
%
Total
Event Waits Time (cs)
Wt
Time
---
direct path write 304,867 35,925
49.83
log file sync 145,015 23,441
32.52
db file sequential read11,3703,684
5.11
file open 9813,326
4.61
db file parallel write 1,8933,115
4.32
You'll notice that 'direct path write' is the most expensive one in
the
list. I cannot find enough info on the net about this wait event,
therefore
I'm asking the real experts.
What events in Oracle trigger this wait event? In what way is this
event
different from db file parallel write?
I mostly read comments that suggest lots of sorting and parallallel
queries.
However, most sorts are done in memory and degree = 0 for all tables.
Any suggestions are very welcome.
Thanks,
Hans de Git
__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Kirtikumar Deshpande
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
Re: direct path write waits, please help
Quote: Group by is still doing sorting, and is accounted in sorts stats (unless an index scan wasn't used to get rows in desired order). But yes, hash joins don't increase sort stats by themselves. end of quote I think you meant was usedin sted of wasn't used. Just like you said, is's all hash joins. It's a production system; I can only peek via the perfstat schema, but I know the application and instance well enough. Case closed, as far as I'm concerned. Thank you all for the input. Regards, Hans de Git Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 30 Jul 2003 03:59:23 -0800 Hi! Group by is still doing sorting, and is accounted in sorts stats (unless an index scan wasn't used to get rows in desired order). But yes, hash joins don't increase sort stats by themselves. You should check 10046 level 8 output, find which SQL statement is doing direct path writes, then get execution plan for these statements to see whether they are using hash joins (since you are on 8.1.6, it can be bit problematic, because execution path information is stored in raw trace file starting from 8.1.7 AFAIK. Problematic in sense that, when doing explain plan under regular session, some session parameters might be different than using the application). But if you find out, that statements with hash join execution plans are the ones waiting on direct path access on temp datafiles, you should also enable event 10104 at level 1 to get hash join trace information. Maybe your statistics are not up to date, that CBO thinks based on ancient statistics it's good idea to hash join because one row set is fairly small, but when it starts building hash build partitions, they actually don't fit into hash area, and some of the partitions have to be written to temp. Check under PHASE 1 in 10104 trace, how many total build partitions you got and how may of them fit into memory. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, July 30, 2003 2:14 PM Could it be that hash joins account for the writes to TEMP without increasing the sort stats? Or 'group by' statements, perhaps? In a 10 minute interval, I can see no increase in the number of sorts to disk, but the writes and reads from v$tempstat increase by thousands. If that's the case, then I think I should increase sort_area_size and/or hash_area_size (memory is not an issue...). Please correct me if i'm wrong. Would it be beneficial to change optimizer_index_caching or optimizer_index_cost_adj to force Oracle into using more nested loops? Don't get me wrong: I'm all against throwing hardware at an application that is so poorly written. But we've past that point, the supplier will not change its behaviour, and from a functional point of view, the end-users are very satisfied. Bummer.. Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 30 Jul 2003 01:24:24 -0800 Hi! Either your 4 disk sorts are huge generating lot's of IO or there direct writes aren't because of sorting. They could be because NOCACHE LOB access for example (also CTAS and direct path insert). You should view 10046 level 8 output and check in which file are the IOs occurring. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, July 30, 2003 11:34 AM FYI The application that is causing the wait events is a third party product that really sucks (autocommit, no bind variables, bad data model, etc., etc.) We're on EMC Symmetrix. There are hardly any wait-io's measurable on AIX; the log file sync problem is not so much of a problem; moving to raw volumes for the redologs should put the log file sync waits down in the top-n. Indeed, the direct path writes have a neglible effect on overall response time. I just want to get a good understanding of the 'direct path writes'. sorts (disk) =4 physical writes direct = 2,444,555 physical writes = 2,470,809 Those are statistics gathered in a two hour interval. Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Tue, 29 Jul 2003 10:14:29 -0800 But, I would like to know how this seemingly high wait for 'direct path write' is affecting the overall response time. (ResponseTime = WaitTime + ServiceTime) If the 'CPU used by this session' is not considered in light of these wait times, aren't you getting ready to bark at the wrong tree? - Kirti --- John Kanagaraj [EMAIL PROTECTED] wrote: Hans, Now let me guess Your disks are all RAID 5, right? And you possibly are bottlenecking on CPU as well? It is clear from the Top 5 that writes are an issue across the board, to TEMP (direct path write), Redo (log file sync) and DB files (db file parallel
direct path write waits, please help
Hi All, Please help me tune this i/o related wait event. This is my 8.1.6 statspack top-5 wait list: Top 5 Wait Events ~ Wait % Total Event Waits Time (cs) Wt Time --- direct path write 304,867 35,925 49.83 log file sync 145,015 23,441 32.52 db file sequential read11,3703,684 5.11 file open 9813,326 4.61 db file parallel write 1,8933,115 4.32 You'll notice that 'direct path write' is the most expensive one in the list. I cannot find enough info on the net about this wait event, therefore I'm asking the real experts. What events in Oracle trigger this wait event? In what way is this event different from db file parallel write? I mostly read comments that suggest lots of sorting and parallallel queries. However, most sorts are done in memory and degree = 0 for all tables. Any suggestions are very welcome. Thanks, Hans de Git _ MSN Zoeken, voor duidelijke zoekresultaten! http://search.msn.nl -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hans de Git 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).
Billing cpu usage on a monthly basis
Hi, We're planning to consolidate a large amount of our production databases into one database. Our customers will be charged based on their use of the central machine's resources (other suggestions are very welcome). I saw a method of keeping track of resource usage, by means of the auditing mechanisms in Oracle: - build a system trigger which recognises logon and logoff. I assume that'll work just fine. However, I'm not sure how to charge our customer for aborted sessions. Any suggestions are very welcome. HTH, Hans de Git _ MSN Zoeken, voor duidelijke zoekresultaten! http://search.msn.nl -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hans de Git 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).
