Re: LOB objects

Some weeks ago I have discussed lob's connection with flashback database feature. Read metalink "Flashback Database Best Practices & Performance [ID 565535.1]"

Randolf Geist advised me another MOS document "LOBS - Storage, Redo and Performance Issues [ID 66431.1]]

My personal point of view is this:

LOB's intensively used as "just bigger varchar2's" with default settings can(and did) pose performance problems. Looks like LOB's were designed to hold large amounts of data:

• they are not cached by default. That has consequences.
• they are not generally stored inline. There exists index structure to access them. That has consequences too.
• they use direct read/write (if not cached.) Again, there are consequences. My believe is that this is exactly what causes pressure on flashback writer (if flashback is enabled in database.) Haven't had a time to test some assumptions how it functions but "Flashback Database Best Practices & Performance [ID 565535.1]" points out specifically that LOB caching is good for flashback writed and yes, it did help us.

Basically, for me LOB's are evil if used in tables subject to high transactions volume.

Brgds, Laimis N

Please consider the environment before printing this e-mail

                                                                                                                                                  
  From:       <Joel.Patterson_at_crowley.com>                                                                                                        
                                                                                                                                                  
  To:         <oracle-l_at_freelists.org>                                                                                                            
                                                                                                                                                  
  Date:       2011.03.17 20:09                                                                                                                    
                                                                                                                                                  
  Subject:    LOB objects                                                                                                                         
                                                                                                                                                  

Can anyone point me to some practical pages or experience working with CLOBs. We have some databases that are using CLOBs, and will be using them more.

So I am interested it things like ‘do they grow forever’ i.e. deletions really don’t happen physically, (if that is so, what to do about it). Tuning: If that is necessary, all aspects, gothas. Things I could or should do?

Joel Patterson
Database Administrator
904 727-2546

--
http://www.freelists.org/webpage/oracle-l