This is an analysis of 10k records where there is 1 byte in the lob column, one stored in row and one stored out of row:
TABLE_NAME SEGMENT_TYPE SEGMENT_NAME BYTES ------------------ ------------- -------------------------- ---------- TEST_LOB_INROW LOBINDEX SYS_IL0000076358C00002$$ 65536 TEST_LOB_INROW LOBSEGMENT SYS_LOB0000076358C00002$$ 65536 TEST_LOB_INROW TABLE TEST_LOB_INROW 589824 TEST_LOB_NOTINROW LOBINDEX SYS_IL0000076364C00002$$ 655360 TEST_LOB_NOTINROW LOBSEGMENT SYS_LOB0000076364C00002$$ 83886080 TEST_LOB_NOTINROW TABLE TEST_LOB_NOTINROW 393216 Check out the size difference between the lob in row and lob out of row-83mb vs 589kb. The rest of the article covers the specifics: http://arswiki.org/wiki/Oracle_Performance_Tuning#LOB_Storage Axton On Fri, Apr 24, 2009 at 8:01 AM, Misi Mladoniczky <[email protected]> wrote: > 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]<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]<sponsor%[email protected]>ARSlist: > > "Where the Answers Are" > > > > > _______________________________________________________________________________ > > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > > Platinum > > Sponsor:[email protected]<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]<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"
