Just for everyone's records, there's also a white paper on the subject: 17-Mar-2008 Using Oracle CLOBs with BMC Remedy Action Request System http://documents.bmc.com/supportu/documents/96/63/89663/89663.pdf
-David J. Easter Sr. Product Manager, Solution Strategy and Development BMC Software, Inc. The opinions, statements, and/or suggested courses of action expressed in this E-mail do not necessarily reflect those of BMC Software, Inc. My voluntary participation in this forum is not intended to convey a role as a spokesperson, liaison or public relations representative for BMC Software, Inc. ________________________________________ From: Action Request System discussion list(ARSList) [[email protected]] On Behalf Of Shellman, David [[email protected]] Sent: Friday, April 24, 2009 6:39 AM To: [email protected] Subject: Re: Oracle LOB getting very big Misi, I see Axton replied. He had some good info on this. Here is a sql command that BMC/Remedy support gave me to show lob space allocation select substr(s.segment_name,1,30) Lobsegment, l.table_name, substr(l.column_name,1,12) Column_name, sum(s.bytes/1024/1024) Mbytes from user_segments s, user_lobs l where l.segment_name = s.segment_name having sum(s.bytes/1024/1024) > 100 group by s.segment_name, l.table_name, l.column_name order by sum(s.bytes/1024/1024); Dave -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Misi Mladoniczky Sent: Friday, April 24, 2009 9: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" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:[email protected] ARSlist: "Where the Answers Are"
