Hi Misi, I'll try to regurgitate what was said to me from my Oracle DBA regarding this exact situation. (Disclaimer: I'm not a real Oracle DBA...I just play one on TV.) Also, it was shared with me about two years ago, so there's a layer of dust on this memory.
1. In Row/Out of Row: By selecting "In Row", the data is stored with the record in the T table, up to 4k (I think this is configurable in Oracle.) If the data is 4k + 1 byte, it gets shoved over to the LOB tablespace (No data is stored in the T table with the row, hence "in row.") Also, the data stored "In Row" is stored as a VARCHAR2, so it only uses as much space as it needs instead of allocating a huge block of space. 2. Sizes: (Configurable in Oracle) New LOB entries in the LOB tablespace will allocate space in blocks. Our instances are configured to store data in 8k blocks. So, if data is stored is 4k + 1 byte, it will have an 8k block of space in the LOB. 8k + 1 byte allocates 16k of space in the LOB tablespace, and so on. It sounds like your Oracle instance is configured to use larger (12k) blocks, perhaps? I'm not sure about the empty row allocation. I don't have that kind of visibility into the LOB tablespace. (My Oracle DBA's get jealous when we play in their sandbox.) J.T. Davies New Edge Networks, Inc. An EarthLink Company -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Misi Mladoniczky Sent: Friday, April 24, 2009 6:02 AM To: [email protected] Subject: Re: Oracle LOB getting very big Hi David, Can anyone explain how it can get som MUCH bigger. I estimate that the new system took up 3-4 times as much data in the database, 45+Gb instead of 15Gb. The size of the CLOB for the empty 32000-char-field was 12000 bytes per record, 500Mb for 44000 records... Note that the data was empty on all rows. Does the system allocate empty space for each record in advance??? Best Regards - Misi, RRR AB, http://rrr.se > By default, 7.x is configured to always store LOB's out of row. Once > the value is set all forms created after will store in row and out of > row depending on the data size for that field within the record. > > Not all the data is stored in row. There is a limit where the switch > is made to store out of row for that record. The issue was that data > was always stored out of row even if there was space to store the data in row. > > We were on 7.0.1 for a year or so before we found out about this behavior. > Under 7.0.1 the size of the instance was growing faster than under 6.3. > We finally identified it was this issue with out of row storage of the > data. BMC/Remedy sent me a procedure to switch our data. I haven't > run it yet. At this point I think we will wait until we migrate to a > new server. > > If I remember right Rick Cook did a lot of testing of in row and out > of row back under 7.0.1. > > Dave > > -----Original Message----- > From: Action Request System discussion list(ARSList) > [mailto:[email protected]] On Behalf Of Misi Mladoniczky > Sent: Friday, April 24, 2009 8:04 AM > To: [email protected] > Subject: Re: Oracle LOB getting very big > > Thanks David, > > That did the trick! > > You had to specify this before the forms were created, but we are > still in the testing environment, so this is ok. > > In this case I guess that the CLOB-data will be stored within the > actual row instead of the external table? Is there a size limit where > the CLOB-data is stored outside of the row? > > Best Regards - Misi, RRR AB, http://www.rrr.se > > Products from RRR Scandinavia: > * RRR|License - Not enough Remedy licenses? Save money by optimizing. > * RRR|Log - Performance issues or elusive bugs? Analyze your Remedy logs. > * RRR|Translator - Manage and automate your language translations. > Find these products, and many free tools and utilities, at http://rrr.se. > >> Misi, >> >> I think you're seeing the in row/out of row issue with storage of LOB's. >> By default version 7.x stores out of row. There is a switch so that >> new forms will store in row. There is also a conversion routine from >> BMC/Remedy but I've not been comfortable running it. >> >> If your 7.1 server is not in production, you could try setting the in >> row parameter. Deleting the form and importing the form definition >> again. >> Dave >> ------------------------- >> [email protected] >> (Wireless) >> >> ----- Original Message ----- >> From: Action Request System discussion list(ARSList) >> <[email protected]> >> To: [email protected] <[email protected]> >> Sent: Fri Apr 24 07:05:27 2009 >> Subject: Oracle LOB getting very big >> >> Hi, >> >> I am moving data from a ARS 6.3.0 server to a 7.1.0 patch 6 server. >> Both are accessing an Oracle 10 database. >> >> In the target server, 3 LOBs are becomming very big. They are >> connected to large text fields (32000 chars) and a diary field. >> >> For example one of these text fields has no data in the column for >> any of the 44361 records. The resulting LOB is about 512 Mb in >> size... >> >> In the source 6.3 machine, the size is very small. >> >> Has anyone seen anything similar? >> >> The real problem is another form with 4 million records, where one >> LOB is >> 13 Gb in size... >> >> I am moving the data with RRR|Chive, which is exactly the same as >> using AR Import of an ARX-file. In other words by doing the API-calls >> ARGetEntry() >> and ARMergeEntry(). >> >> Best Regards - Misi, RRR AB, http://www.rrr.se >> >> Products from RRR Scandinavia: >> * RRR|License - Not enough Remedy licenses? Save money by optimizing. >> * RRR|Log - Performance issues or elusive bugs? Analyze your Remedy >> logs. >> * RRR|Translator - Manage and automate your language translations. >> Find these products, and many free tools and utilities, at >> http://rrr.se. >> >> _____________________________________________________________________ >> __________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org >> Platinum Sponsor:[email protected] ARSlist: "Where the Answers >> Are" >> >> -- >> This message was scanned by ESVA and is believed to be clean. >> >> > > ______________________________________________________________________ > _________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > Platinum Sponsor:[email protected] ARSlist: "Where the Answers Are" > > ______________________________________________________________________ > _________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > Platinum Sponsor:[email protected] ARSlist: "Where the Answers Are" > > -- > This message was scanned by ESVA and is believed to be clean. > > ________________________________________________________________________ _______ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:[email protected] ARSlist: "Where the Answers Are" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:[email protected] ARSlist: "Where the Answers Are"
