Re: Re: [Q] wait time /lob def
Hi! > i apologize if someone posted this answer(i missed it if you did), but why do cached lobs require writes on the control file? NOCACHE NOLOGGING LOBs require writes to controlfile, because last nologging operation to a datafile has to be reflected somewhere, for being able to determine which files need to be backed up (since recovery information is not logged for nologging operations). Another option would be to use NOCACHE LOGGING operations, that way no controlfile updates are needed, but I do not recommend it, because NOCACHE LOGGING LOBs require the writing of the whole LOB segment block to redo, even if you modify just one byte in it. CACHE LOGGING is more efficient way in sense of redo generation, that way only changed bytes will be recorded. > and jonathan said that isnt 'necessarily' bad. is that because there isnt alot of I/O? If you don't spend too much time waiting on these events, then yes it's not bad at all. > > so caching lobs are primarily useful for read only or read 'mostly' LOBs? They are useful in many cases, for write LOBs as well in some cases, but the problem is that they always generate redo, which might not be acceptable in high data load environments. IMO, if you can use a combination of disk array with write cache, asynch IO, LOB buffering in client side and write batching, then NOCACHE NOLOGGING LOBs can meet acceptable performance constraints and if you use CACHE READ NOLOGGING LOBs (which seem to be available even from 8.1.6), then your performance will be just fine. Of course, there is always cases with special requirements/contstraints, so YMMAFV (your mileage may, and will vary). Tanel. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, January 30, 2004 2:49 PM > > From: "Jonathan Lewis" <[EMAIL PROTECTED]> > > Date: 2004/01/30 Fri AM 04:09:25 EST > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > > Subject: Re: [Q] wait time /lob def > > > > > > Tanel, > > > > Oracle 9-2 SQL Ref manual. Lob storage clause. > > The options for lobs on creation are: > > CACHE > > NO CACHE > > CACHE READS > > > > I don't think the last one appeared until 9.2 > > I was wrong about caching only writes, though - > > one of the joys of trying to quote everything from > > memory. > > > > The CACHE READS option means that the > > LOB goes into the buffer cache for reads, but > > not for writes. Pity, really, because I'd quite > > like to see it the other way around. > > > > > > > > Regards > > > > Jonathan Lewis > > http://www.jlcomp.demon.co.uk > > > > The educated person is not the person > > who can answer the questions, but the > > person who can question the answers -- T. Schick Jr > > > > > > Next public appearances: > > Jan 29th 2004 UKOUG Unix SIG - v$ and x$ > > March 2004 Hotsos Symposium - The Burden of Proof > > March 2004 Charlotte NC OUG - CBO Tutorial > > April 2004 Iceland > > > > > > One-day tutorials: > > http://www.jlcomp.demon.co.uk/tutorial.html > > > > > > Three-day seminar: > > see http://www.jlcomp.demon.co.uk/seminar.html > > UK___February > > UK___June > > > > > > The Co-operative Oracle Users' FAQ > > http://www.jlcomp.demon.co.uk/faq/ind_faq.html > > > > > > - Original Message - > > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > > Sent: Friday, January 30, 2004 12:19 AM > > > > > > > Jonathan, > > > > > > about which version are you talking here? > > > > > > > (You do also have the option in more recent versions > > > > of refining the caching properties so the LOB can be > > > > readcache only, writecache only or read/write cache > > > > or nocache, I believe). > > > > > > I haven't found a way to explicitly set read or write caching for LOBs in > > > system level, although I've done some research on them lately (on 10g as > > > well). Or are you talking about OCI LOB caching here? > > > > > > Tanel. > > > > > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Jonathan Lewis > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > San Diego, California-- Mailing list and web hosting services &g
Re: Re: [Q] wait time /lob def
i apologize if someone posted this answer(i missed it if you did), but why do cached lobs require writes on the control file? and jonathan said that isnt 'necessarily' bad. is that because there isnt alot of I/O? so caching lobs are primarily useful for read only or read 'mostly' LOBs? > > From: "Jonathan Lewis" <[EMAIL PROTECTED]> > Date: 2004/01/30 Fri AM 04:09:25 EST > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: Re: [Q] wait time /lob def > > > Tanel, > > Oracle 9-2 SQL Ref manual. Lob storage clause. > The options for lobs on creation are: > CACHE > NO CACHE > CACHE READS > > I don't think the last one appeared until 9.2 > I was wrong about caching only writes, though - > one of the joys of trying to quote everything from > memory. > > The CACHE READS option means that the > LOB goes into the buffer cache for reads, but > not for writes. Pity, really, because I'd quite > like to see it the other way around. > > > > Regards > > Jonathan Lewis > http://www.jlcomp.demon.co.uk > > The educated person is not the person > who can answer the questions, but the > person who can question the answers -- T. Schick Jr > > > Next public appearances: > Jan 29th 2004 UKOUG Unix SIG - v$ and x$ > March 2004 Hotsos Symposium - The Burden of Proof > March 2004 Charlotte NC OUG - CBO Tutorial > April 2004 Iceland > > > One-day tutorials: > http://www.jlcomp.demon.co.uk/tutorial.html > > > Three-day seminar: > see http://www.jlcomp.demon.co.uk/seminar.html > UK___February > UK___June > > > The Co-operative Oracle Users' FAQ > http://www.jlcomp.demon.co.uk/faq/ind_faq.html > > > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Friday, January 30, 2004 12:19 AM > > > > Jonathan, > > > > about which version are you talking here? > > > > > (You do also have the option in more recent versions > > > of refining the caching properties so the LOB can be > > > readcache only, writecache only or read/write cache > > > or nocache, I believe). > > > > I haven't found a way to explicitly set read or write caching for LOBs in > > system level, although I've done some research on them lately (on 10g as > > well). Or are you talking about OCI LOB caching here? > > > > Tanel. > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Jonathan Lewis > 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: <[EMAIL PROTECTED] 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: [Q] wait time /lob def
Hi Jonathan, > I don't think the last one appeared until 9.2 > I was wrong about caching only writes, though - > one of the joys of trying to quote everything from > memory. Thank you for this note, I had somehow missed this important improvement. > > The CACHE READS option means that the > LOB goes into the buffer cache for reads, but > not for writes. Pity, really, because I'd quite > like to see it the other way around. It guess it has to do with the kernel cache layer, that it's probably not feasible to start modifying cache layer code the way it wouldn't log any changes? Anyway, some testing needs to be done now :) Actually CACHE READS is exactly what I need for a current project, the incoming LOB feed is too high to have them logging, but the app can somewhat benefit from LOB caching in buffer cache. I relieved write performance problem by using OCI LOB caching and batch writes for incoming content feed (and the app is gonna run on a dedicated write-cache enabled Hitachi 9980V as well). Thanks, Tanel. > > > > Regards > > Jonathan Lewis > http://www.jlcomp.demon.co.uk > > The educated person is not the person > who can answer the questions, but the > person who can question the answers -- T. Schick Jr > > > Next public appearances: > Jan 29th 2004 UKOUG Unix SIG - v$ and x$ > March 2004 Hotsos Symposium - The Burden of Proof > March 2004 Charlotte NC OUG - CBO Tutorial > April 2004 Iceland > > > One-day tutorials: > http://www.jlcomp.demon.co.uk/tutorial.html > > > Three-day seminar: > see http://www.jlcomp.demon.co.uk/seminar.html > UK___February > UK___June > > > The Co-operative Oracle Users' FAQ > http://www.jlcomp.demon.co.uk/faq/ind_faq.html > > > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Friday, January 30, 2004 12:19 AM > > > > Jonathan, > > > > about which version are you talking here? > > > > > (You do also have the option in more recent versions > > > of refining the caching properties so the LOB can be > > > readcache only, writecache only or read/write cache > > > or nocache, I believe). > > > > I haven't found a way to explicitly set read or write caching for LOBs in > > system level, although I've done some research on them lately (on 10g as > > well). Or are you talking about OCI LOB caching here? > > > > Tanel. > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Jonathan Lewis > 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: Tanel Poder 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: [Q] wait time /lob def
It's just one example of my general suggestion that messing about with block sizes rarely has any direct performance benefit. But if you can put something out of the way where it can't do so much damage then the performance of everything else might benefit. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearances: Jan 29th 2004 UKOUG Unix SIG - v$ and x$ March 2004 Hotsos Symposium - The Burden of Proof March 2004 Charlotte NC OUG - CBO Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February UK___June The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, January 29, 2004 11:04 PM > That is an interesting use of an alternate block size Jonathan. > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: [Q] wait time /lob def
Tanel, Oracle 9-2 SQL Ref manual. Lob storage clause. The options for lobs on creation are: CACHE NO CACHE CACHE READS I don't think the last one appeared until 9.2 I was wrong about caching only writes, though - one of the joys of trying to quote everything from memory. The CACHE READS option means that the LOB goes into the buffer cache for reads, but not for writes. Pity, really, because I'd quite like to see it the other way around. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearances: Jan 29th 2004 UKOUG Unix SIG - v$ and x$ March 2004 Hotsos Symposium - The Burden of Proof March 2004 Charlotte NC OUG - CBO Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February UK___June The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, January 30, 2004 12:19 AM > Jonathan, > > about which version are you talking here? > > > (You do also have the option in more recent versions > > of refining the caching properties so the LOB can be > > readcache only, writecache only or read/write cache > > or nocache, I believe). > > I haven't found a way to explicitly set read or write caching for LOBs in > system level, although I've done some research on them lately (on 10g as > well). Or are you talking about OCI LOB caching here? > > Tanel. > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: [Q] wait time /lob def
Thanks, Tanel. Yes, I'll be at RMOUG. I'll be in the front row for your presentation. Again, thanks for the info. Barb --- Tanel Poder <[EMAIL PROTECTED]> wrote: > Comments below. > > - Original Message - > To: "Multiple recipients of list ORACLE-L" > <[EMAIL PROTECTED]> > Sent: Thursday, January 29, 2004 7:04 PM > > > > Jonathan / Tanel: > > I, however, AM having this problem. Didn't know > where > > to look till I saw this message. (I love this > list!) > > So do I ;) > > > > > I've yet to capture the sid (and therefore track > back > > to the table) where the 'direct path write' > occurs. > > Definitions for the tables were supplied by the > > vendor. When I look at at the lobs, the > definitions > > are mixed. Most are nocache, logging yes. some > are > > no/no > > > > I don't see much on metalink about this -- just a > > couple of generic articles on lob storage. > > If you are up to attending RMOUG Training days in > few weeks, then I'll be > speaking about LOB performance tuning there, you're > most welcome ;) > > > > > Should I change the lobs to cache/logging across > the > > board? > > Well, if you set all your LOBs to CACHE (cached LOBs > are always logged), > then you'll be generating more redo for current > nocache nologging LOBs as > well. Also, your buffer cache gets hit, especially > if your LOBs are big. > > One possibility is setting event 10359 at level 1 to > what Jonathan also > referred, then your direct writes won't cause > controlfile updates anymore. > But when you have set this event, you can't rely on > UNRECOVERABLE_CHANGE# in > your V$DATAFILE anymore, for determining whether a > datafile should be backed > up due nologging operations in it. > > If you should ever need to restore a file containing > NOLOGGING NOCACHE LOBs, > then any "inconsistent" blocks will be marked > corrupt (block sqn=0xFF) since > there was no redo information logged for them. In > that case you have update > the corrupt LOBs to nulls for example to reclaim the > space in LOB segment. > Note that having few corrupt LOB items in a LOB > segment won't affect other, > healthy LOB items, you just have to have some kind > of error handling > mechanism implemented when you hit a corrupt item. > > Note that updates to LOB indexes are always cached > and logged, despite the > NOLOGGING setting. > > Tanel. > > > > > > Thanks for any insight. > > Barb > > > > Table > > NameCach LOG > > --- > > CONTACTGROUP NO YES > > CONTENT NO YES > > IMAGEBINARY NO YES > > IMAGEHEADER NO YES > > IMAGEROW NO NO > > IMPORTIN NO NO > > TF_NEW_CONTENTNO YES > > XMLS_DICTIONARY NO YES > > XMLS_SALESSUPPORT NO NO > > XMLS_STYLEPARTYES YES > > > > --- Tanel Poder <[EMAIL PROTECTED]> wrote: > > > Hi! > > > > > > > Do you have any LOBs defined with > > > > the nocache nologging attribute ? > > > > > > This was what I initially thought would be the > > > problem, but then I thought, > > > in case of nocache lobs you should see direct > path > > > writes (lob) a lot... > > > > > > Tanel. > > > > > > > > > > > > > > > -- > > > Please see the official ORACLE-L FAQ: > > > http://www.orafaq.net > > > -- > > > Author: Tanel Poder > > > 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). > > > > > > __ > > Do you Yahoo!? > > Yahoo! SiteBuilder - Free web site building tool. > Try it! > > http://webhosting.yahoo.com/ps/sb/ > > -- > > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > > -- > > Author: Barbara Baker > > 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: Tanel Poder > INET: [EMAIL PROTE
Re: [Q] wait time /lob def
Comments below. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, January 29, 2004 7:04 PM > Jonathan / Tanel: > I, however, AM having this problem. Didn't know where > to look till I saw this message. (I love this list!) So do I ;) > > I've yet to capture the sid (and therefore track back > to the table) where the 'direct path write' occurs. > Definitions for the tables were supplied by the > vendor. When I look at at the lobs, the definitions > are mixed. Most are nocache, logging yes. some are > no/no > > I don't see much on metalink about this -- just a > couple of generic articles on lob storage. If you are up to attending RMOUG Training days in few weeks, then I'll be speaking about LOB performance tuning there, you're most welcome ;) > > Should I change the lobs to cache/logging across the > board? Well, if you set all your LOBs to CACHE (cached LOBs are always logged), then you'll be generating more redo for current nocache nologging LOBs as well. Also, your buffer cache gets hit, especially if your LOBs are big. One possibility is setting event 10359 at level 1 to what Jonathan also referred, then your direct writes won't cause controlfile updates anymore. But when you have set this event, you can't rely on UNRECOVERABLE_CHANGE# in your V$DATAFILE anymore, for determining whether a datafile should be backed up due nologging operations in it. If you should ever need to restore a file containing NOLOGGING NOCACHE LOBs, then any "inconsistent" blocks will be marked corrupt (block sqn=0xFF) since there was no redo information logged for them. In that case you have update the corrupt LOBs to nulls for example to reclaim the space in LOB segment. Note that having few corrupt LOB items in a LOB segment won't affect other, healthy LOB items, you just have to have some kind of error handling mechanism implemented when you hit a corrupt item. Note that updates to LOB indexes are always cached and logged, despite the NOLOGGING setting. Tanel. > > Thanks for any insight. > Barb > > Table > NameCach LOG > --- > CONTACTGROUP NO YES > CONTENT NO YES > IMAGEBINARY NO YES > IMAGEHEADER NO YES > IMAGEROW NO NO > IMPORTIN NO NO > TF_NEW_CONTENTNO YES > XMLS_DICTIONARY NO YES > XMLS_SALESSUPPORT NO NO > XMLS_STYLEPARTYES YES > > --- Tanel Poder <[EMAIL PROTECTED]> wrote: > > Hi! > > > > > Do you have any LOBs defined with > > > the nocache nologging attribute ? > > > > This was what I initially thought would be the > > problem, but then I thought, > > in case of nocache lobs you should see direct path > > writes (lob) a lot... > > > > Tanel. > > > > > > > > > > -- > > Please see the official ORACLE-L FAQ: > > http://www.orafaq.net > > -- > > Author: Tanel Poder > > 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). > > > __ > Do you Yahoo!? > Yahoo! SiteBuilder - Free web site building tool. Try it! > http://webhosting.yahoo.com/ps/sb/ > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Barbara Baker > 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: Tanel Poder 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 (li
Re: [Q] wait time /lob def
One more similar reason for using different block sizes for different LOBs is that a LOB item always allocates space in chunks for it and a chunk can be a multiple of blocksize, not less. Big blocks mean space wastage for lots of out of line small LOBs, however small blocks mean performance degradation due fragmentation and LOB index growth for large LOBs. This situation can be relieved by having tablespaces with different blocksizes for different LOBs. Tanel. - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Sent: Friday, January 30, 2004 1:04 AM Subject: Re: [Q] wait time /lob def That is an interesting use of an alternate block size Jonathan. "Jonathan Lewis" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 01/29/2004 09:59 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: Re: [Q] wait time /lob defThere is a note in one of the manuals about nologging lobs (or nocache lob, I forget which) that points outthat the "unrecoverable SCN" for file that holds theLOB has to be updated in the control file whenever theLOB is updated.If you actually have a performance problem because ofthis - i.e. if lots of people/processes are running slowly because they are waiting on control file writes - thenyou might want to make the LOB cache/loggong. Butcontrol file writes are not inherently a bad thing to beblocked. Of course, if the LOBs are quite large, thenthe time taken to write the LOB may be far greater than the time taken to update the controlfile - whichwould make any concerns about the controlfile updateirrelevant. So there is no 'obvious' correct answer toyour question, without examing your current activity.The note (which I think Steve Adams' also has on hiswebsite) mentions an event that can be set to stop thecontrolfile update when the LOB is updated. This maynot be a good idea, though, as it may affect some aspects of recoverability.If you do make the LOB 'cached', then remember thatall reads and writes go through the db_block_buffer,which could affect all the other I/O activity adversely,so you might want to consider putting the LOBs intoa tablespace with a non-standard block size so thatthe LOB activity doesn't affect the rest of the cache.(You do also have the option in more recent versionsof refining the caching properties so the LOB can bereadcache only, writecache only or read/write cacheor nocache, I believe).RegardsJonathan Lewishttp://www.jlcomp.demon.co.uk
Re: [Q] wait time /lob def
Jonathan, about which version are you talking here? > (You do also have the option in more recent versions > of refining the caching properties so the LOB can be > readcache only, writecache only or read/write cache > or nocache, I believe). I haven't found a way to explicitly set read or write caching for LOBs in system level, although I've done some research on them lately (on 10g as well). Or are you talking about OCI LOB caching here? Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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: [Q] wait time /lob def
That is an interesting use of an alternate block size Jonathan. "Jonathan Lewis" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 01/29/2004 09:59 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: Re: [Q] wait time /lob def There is a note in one of the manuals about nologging lobs (or nocache lob, I forget which) that points out that the "unrecoverable SCN" for file that holds the LOB has to be updated in the control file whenever the LOB is updated. If you actually have a performance problem because of this - i.e. if lots of people/processes are running slowly because they are waiting on control file writes - then you might want to make the LOB cache/loggong. But control file writes are not inherently a bad thing to be blocked. Of course, if the LOBs are quite large, then the time taken to write the LOB may be far greater than the time taken to update the controlfile - which would make any concerns about the controlfile update irrelevant. So there is no 'obvious' correct answer to your question, without examing your current activity. The note (which I think Steve Adams' also has on his website) mentions an event that can be set to stop the controlfile update when the LOB is updated. This may not be a good idea, though, as it may affect some aspects of recoverability. If you do make the LOB 'cached', then remember that all reads and writes go through the db_block_buffer, which could affect all the other I/O activity adversely, so you might want to consider putting the LOBs into a tablespace with a non-standard block size so that the LOB activity doesn't affect the rest of the cache. (You do also have the option in more recent versions of refining the caching properties so the LOB can be readcache only, writecache only or read/write cache or nocache, I believe). Regards Jonathan Lewis http://www.jlcomp.demon.co.uk
Re: [Q] wait time /lob def
There is a note in one of the manuals about nologging lobs (or nocache lob, I forget which) that points out that the "unrecoverable SCN" for file that holds the LOB has to be updated in the control file whenever the LOB is updated. If you actually have a performance problem because of this - i.e. if lots of people/processes are running slowly because they are waiting on control file writes - then you might want to make the LOB cache/loggong. But control file writes are not inherently a bad thing to be blocked. Of course, if the LOBs are quite large, then the time taken to write the LOB may be far greater than the time taken to update the controlfile - which would make any concerns about the controlfile update irrelevant. So there is no 'obvious' correct answer to your question, without examing your current activity. The note (which I think Steve Adams' also has on his website) mentions an event that can be set to stop the controlfile update when the LOB is updated. This may not be a good idea, though, as it may affect some aspects of recoverability. If you do make the LOB 'cached', then remember that all reads and writes go through the db_block_buffer, which could affect all the other I/O activity adversely, so you might want to consider putting the LOBs into a tablespace with a non-standard block size so that the LOB activity doesn't affect the rest of the cache. (You do also have the option in more recent versions of refining the caching properties so the LOB can be readcache only, writecache only or read/write cache or nocache, I believe). Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearances: Jan 29th 2004 UKOUG Unix SIG - v$ and x$ March 2004 Hotsos Symposium - The Burden of Proof March 2004 Charlotte NC OUG - CBO Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February UK___June The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, January 29, 2004 5:04 PM > Jonathan / Tanel: > I, however, AM having this problem. Didn't know where > to look till I saw this message. (I love this list!) > > I've yet to capture the sid (and therefore track back > to the table) where the 'direct path write' occurs. > Definitions for the tables were supplied by the > vendor. When I look at at the lobs, the definitions > are mixed. Most are nocache, logging yes. some are > no/no > > I don't see much on metalink about this -- just a > couple of generic articles on lob storage. > > Should I change the lobs to cache/logging across the > board? > > Thanks for any insight. > Barb > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: [Q] wait time /lob def
Jonathan / Tanel: I, however, AM having this problem. Didn't know where to look till I saw this message. (I love this list!) I've yet to capture the sid (and therefore track back to the table) where the 'direct path write' occurs. Definitions for the tables were supplied by the vendor. When I look at at the lobs, the definitions are mixed. Most are nocache, logging yes. some are no/no I don't see much on metalink about this -- just a couple of generic articles on lob storage. Should I change the lobs to cache/logging across the board? Thanks for any insight. Barb Table NameCach LOG --- CONTACTGROUP NO YES CONTENT NO YES IMAGEBINARY NO YES IMAGEHEADER NO YES IMAGEROW NO NO IMPORTIN NO NO TF_NEW_CONTENTNO YES XMLS_DICTIONARY NO YES XMLS_SALESSUPPORT NO NO XMLS_STYLEPARTYES YES --- Tanel Poder <[EMAIL PROTECTED]> wrote: > Hi! > > > Do you have any LOBs defined with > > the nocache nologging attribute ? > > This was what I initially thought would be the > problem, but then I thought, > in case of nocache lobs you should see direct path > writes (lob) a lot... > > Tanel. > > > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Tanel Poder > 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). __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barbara Baker 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).
