RE: Oracle LOB getting very bigOut of line is probably the better way to implement LOB's, you will probably have better transaction processing performance, not to mention the 2 GB limit of the in row LOBs (at least according to Oracle). Why it is slowing down the ARS is entirely different story. I agree with you that there are quite a few settings for each LOB implementation and they should be part of install/config utils.
Regards, Nicky Madjarov phone: 973-202-4278 Find out how to bust your AR System performance @ http://www.SpeedUpARS.com ----- Original Message ----- From: Guillaume Rheault Newsgroups: public.remedy.arsystem.general To: [email protected] Sent: Monday, April 27, 2009 10:23 AM Subject: Re: Oracle LOB getting very big ** That is the $60000 question. Seems to me the ARS installer should ask what the value of that setting should be set to -Guillaume -----Original Message----- From: Action Request System discussion list(ARSList) on behalf of Misi Mladoniczky Sent: Mon 04/27/09 6:40 AM To: [email protected] Subject: Re: Oracle LOB getting very big Hi, Thank you all for the responses. One thing confuses me though... In all these comparisons, the In-Row-LOB requires less space and is faster. Why has BMC set the default to Out-Row-LOB? Especially since older AR System Versions seems to use the In-Row-LOB setting??? 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. > 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" > > -- > 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" _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"
