Unfortunately with the BMC ITSM apps, we don't have a choice of not using LOBs, right? Or do we?
-Guillaume -----Original Message----- From: Action Request System discussion list(ARSList) on behalf of Axton Sent: Mon 04/27/09 11:42 AM To: [email protected] Subject: Re: Oracle LOB getting very big Unless explicitly defined when creating a lob, storing the lob out of row is the default behavior. I'm guessing the authors wanted to adhere to the defaults. I do know that when performing the ARS installation, the LOB storage parameter for the meta-data tables (e.g, arschema) is set to store the lob in-row. Imho, it's better to just not use lobs. Axton 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. On Mon, Apr 27, 2009 at 9:23 AM, Guillaume Rheault <[email protected]>wrote: > ** > > 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] <[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] <[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]<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" > > _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"
