well, according to BMC's own white paper, having the LOB in-row is better than out-of-row for the ITSM application suite. So in the specific context/scenario of the BMC ITSM application suite, the in-row option seems like the better option...
maybe in some other situation (Oracle financial apps, etc) the out-of-row is a better option, or when the ARS server is out of picture, like you mention... which then begs the question as to why ARS is slowing things down if it is in fact the bottleneck (or maybe the OCI libraries are at fault who knows) -Guillaume -----Original Message----- From: Action Request System discussion list(ARSList) on behalf of Nicky Madjarov Sent: Mon 04/27/09 11:00 AM To: arslist@ARSLIST.ORG Subject: Re: Oracle LOB getting very big 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: arslist@ARSLIST.ORG 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: arslist@ARSLIST.ORG 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) [arsl...@arslist.org] > On Behalf Of Shellman, David [dave.shell...@tycoelectronics.com] > Sent: Friday, April 24, 2009 6:39 AM > To: arslist@ARSLIST.ORG > 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:arsl...@arslist.org] On Behalf Of Misi Mladoniczky > Sent: Friday, April 24, 2009 9:02 AM > To: arslist@ARSLIST.ORG > 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:arsl...@arslist.org] On Behalf Of Misi Mladoniczky >> Sent: Friday, April 24, 2009 8:04 AM >> To: arslist@ARSLIST.ORG >> 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 >>> ------------------------- >>> dave.shell...@tycoelectronics.com >>> (Wireless) >>> >>> ----- Original Message ----- >>> From: Action Request System discussion list(ARSList) >>> <arslist@ARSLIST.ORG> >>> To: arslist@ARSLIST.ORG <arslist@ARSLIST.ORG> >>> 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:rmisoluti...@verizon.net 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:rmisoluti...@verizon.net ARSlist: "Where the Answers >> Are" >> >> _______________________________________________________________________________ >> UNSUBSCRIBE or access ARSlist Archives at www.arslist.org >> Platinum Sponsor:rmisoluti...@verizon.net 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:rmisoluti...@verizon.net ARSlist: "Where the Answers Are" > > _______________________________________________________________________________ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are" > _______________________________________________________________________________ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > Platinum Sponsor:rmisoluti...@verizon.net 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:rmisoluti...@verizon.net ARSlist: "Where the Answers Are" _Platinum Sponsor: rmisoluti...@verizon.net ARSlist: "Where the Answers Are"_ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are"
