After a day's worth of work we've figured out that the vast majority of space being used was coming from:
HPD:HelpDesk_AuditLogSystem This is the base product Audit logging built into IM 7.03 and as much as I love Remedy I am willing to call this feature very poor design for an application that is supposed to scale. Actually, I think Log-style auditing is ridiculous for many reasons. In this case the Audit Logging was using 25% of our total disk space. Needless to say we are in the process of disabling it and truncating tables. William Rentfrow Principal Consultant, StrataCom Inc. wrentf...@stratacominc.com O 715-592-5185 C 715-410-8056 -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Axton Sent: Wednesday, November 18, 2009 12:19 PM To: arslist@ARSLIST.ORG Subject: Re: Oracle in-row LOB storage issue - IM 7.03 I need sleep ;) The B#C# are used for attachments, I believe, at least they were in past versions. This is because Remedy used to use a LONG RAW data type for the binary storage and there could be only 1 long raw per table. The SYS_LOB0000039519C00010$$ cited by Frederick is the LOB segment. You can add the segment_type column to the results to see what type it actually is. The segment_type will tell you one of these: CACHE CLUSTER INDEX LOBINDEX LOBSEGMENT ROLLBACK TABLE TABLE PARTITION TYPE2 UNDO Axton Grams 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 Wed, Nov 18, 2009 at 11:01 AM, Grooms, Frederick W <frederick.w.gro...@xo.com> wrote: > Wow... I actually have something to disagree with Axton on ... > > If you create a form (since v6x) Remedy no longer uses the BtttCccccccc > structure to hold the long character fields. I am on 7.1.0 patch 7 with > Oracle 10g and all of my CLOB columns are just columns in the normal table. > > SQL> describe T10; > Name Null? Type > ------------ -------- --------------- > C1 NOT NULL VARCHAR2(15) > C2 VARCHAR2(254) > C3 NOT NULL NUMBER(15) > C4 VARCHAR2(254) > C5 NOT NULL VARCHAR2(254) > C6 NOT NULL NUMBER(15) > C7 NOT NULL NUMBER(15) > C8 NOT NULL VARCHAR2(128) > C536870912 VARCHAR2(255) > C536870913 CLOB > C536870914 NUMBER(15) > C536870915 CLOB > C536870916 NUMBER(15) > C536870921 VARCHAR2(4000) > C536870924 VARCHAR2(4000) > C536870925 NUMBER(15) > C540000710 VARCHAR2(255) > > And if I look at the create script thru TOAD I see: > > CREATE TABLE T10 > ( > C1 VARCHAR2(15 BYTE) NOT NULL, > C2 VARCHAR2(254 BYTE), > C3 NUMBER(15) NOT NULL, > C4 VARCHAR2(254 BYTE), > C5 VARCHAR2(254 BYTE) NOT NULL, > C6 NUMBER(15) NOT NULL, > C7 NUMBER(15) NOT NULL, > C8 VARCHAR2(128 BYTE) NOT NULL, > C536870912 VARCHAR2(255 BYTE), > C536870913 CLOB, > C536870914 NUMBER(15), > C536870915 CLOB, > C536870916 NUMBER(15), > C536870921 VARCHAR2(4000 BYTE), > C536870924 VARCHAR2(4000 BYTE), > C536870925 NUMBER(15), > C540000710 VARCHAR2(255 BYTE) > ) > TABLESPACE ARSYSTEM > LOGGING > NOCOMPRESS > LOB (C536870913) STORE AS > ( TABLESPACE ARSYSTEM > ENABLE STORAGE IN ROW > CHUNK 8192 > PCTVERSION 10 > NOCACHE > ) > LOB (C536870915) STORE AS > ( TABLESPACE ARSYSTEM > ENABLE STORAGE IN ROW > CHUNK 8192 > PCTVERSION 10 > NOCACHE > ) > NOCACHE > NOPARALLEL > MONITORING; > > > The Oracle query for schemaid 10 for me is: > select SEGMENT_NAME, bytes > from USER_SEGMENTS > Where SEGMENT_NAME in (select SEGMENT_NAME from USER_LOBS where > TABLE_NAME like '_10') > or SEGMENT_NAME like '_10' > or SEGMENT_NAME like '_10C%' > > > NOTE: I used USER_SEGMENTS and USER_LOBS in case your DBA has locked > down your database (and the DBA_ versions of these objects are not > available) > > Fred > > > -----Original Message----- > From: Action Request System discussion list(ARSList) > [mailto:arsl...@arslist.org] On Behalf Of Axton > Sent: Wednesday, November 18, 2009 10:24 AM > To: arslist@ARSLIST.ORG > Subject: Re: Oracle in-row LOB storage issue - IM 7.03 > > You can look at the oracle data dictionary view dba_segments to see > the size of the base tables, lob segments, indexes, etc: > > Here is an example for schemaid 746: > > SQL> ed > Wrote file afiedt.buf > > 1 select bytes, segment_name, owner > 2 from dba_segments > 3 where (segment_name like '_746' or segment_name like '_746C%') > 4* and segment_type = 'TABLE' > SQL> / > > BYTES SEGMENT_NAME OWNER > ---------- -------------- ------- > 3407872 B746 ARADMIN > 2390491136 T746 ARADMIN > 27525120 H746 ARADMIN > 932446208 B746C701000357 ARADMIN > > B746C701000357 is the lob segment for field id 701000357. The others > are the base tables. You can run the query without the where clause > for segment_type to get the indexes, lob segment indexes, etc: > > select bytes, segment_name, owner > from dba_segments > where (segment_name like '_746%' or segment_name like '_746C%') > > Axton Grams > > 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 Wed, Nov 18, 2009 at 12:57 AM, William Rentfrow > <wrentf...@stratacominc.com> wrote: >> ** >> >> I've read a LOT of the documentation, etc, regarding how LOB storage >> works (including Axton's very fine wiki on this topic) and I'm pretty >> familiar with it in theory. >> >> We are doing a data migration at the moment which is using an >> enormous amount of space. We are in the process of migratiing >> 330,000 records as a test. >> >> This has used (so far) 33 GB of table space. We only have 47 GB in >> this test system. Considering the actual ARX exports were < 1 GB >> something is clearly not right here. >> >> Initial consultation with a DBA has indicated these rows in the T1114 >> (aka, >> HPD:HelpDesk) are LOB's: >> >> C1000005940 z1D Template Related CI >> C1000000151 Detailed Decription >> C1000000156 Resolution >> C1000003742 z1D Mobile Worklog Upd >> C300270900 Reason Description >> >> All of these are character fields set to 0 (unlimited) length. >> >> The questions I have are: >> >> 1.) If a LOB is > 4K it is stored out of row regardless. Can anyone >> tell me how to determine the space used by any of those particular >> fields in Oracle? And how I can tell if these are in fact being >> stored out of row due to the "unlimited" option on the field? >> >> 2.) Aren't z1D fields supposed to be Display only according to >> Remedy's design and naming conventions? I'm pretty sure that is the >> case but both of the fields above with that prefix are stored as optional. >> >> Thanks in advance - >> >> William Rentfrow >> Principal Consultant, StrataCom Inc. >> wrentf...@stratacominc.com >> O 715-592-5185 >> C 715-410-8056 >> > > ______________________________________________________________________ > _________ 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"
