William,
here's an excerpt out of the oracle docs
Choosing the Value of CHUNK
Once the value of CHUNK is chosen (when the LOB column is created), it cannot
be changed. Hence, it is important that you choose a value which optimizes your
storage and performance requirements.
Space Considerations
The value of CHUNK does not matter for LOBs that are stored inline. This
happens when ENABLE STORAGE IN ROW is set, and the size of the LOB locator and
the LOB data is less than 4000 bytes. However, when the LOB data is stored
out-of-line, it will always take up space in multiples of the CHUNK parameter.
This can lead to a large waste of space if your data is small, but the CHUNK is
set to a large number. The following table illustrates this point:
Table 4-4 Data Size and CHUNK Size
Data Size CHUNK Size Disk Space Used
to Store the LOB Space Utilization (Percent)
3500 enable storage in row irrelevant 3500 in row
100
3500 disable storage in row 32 KB 32 KB
10
3500 disable storage in row 4 KB 4 KB
90
33 KB 32 KB 64 KB
51
2 GB +10 32 KB 2 GB + 32 KB
99+
HTH
Kind Regards Conny
-----Ursprüngliche Nachricht-----
Von: Action Request System discussion list(ARSList)
[mailto:[email protected]] Im Auftrag von William Rentfrow
Gesendet: Mittwoch, 18. November 2009 18:50
An: [email protected]
Betreff: Re: Oracle in-row LOB storage issue - IM 7.03
Thanks for all the responses.
I'm trying to reconcile our db size usage (53% of 47 MB after some
re-structuring) with BMC's in-row examples.
The in-row examples (from Oracle) in their docs mention a table with 43000+
with 32000+ of those having LOB entries and a size of ~261 MB.
Even if I give Remedy a 50% fudge factor and bump that number to 390 (heck,
let's go 400) we are using nearly 100X the space. And we only have 10X the
records.
All LOBS are now confirmed (again) as being in-row - waiting for BMC..and any
bright ideas from the list. DBA is working on your suggestions to determine
where exactly all the space has gone.
William Rentfrow
Principal Consultant, StrataCom Inc.
[email protected]
O 715-592-5185
C 715-410-8056
-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:[email protected]] On Behalf Of Grooms, Frederick W
Sent: Wednesday, November 18, 2009 11:25 AM
To: [email protected]
Subject: Re: Oracle in-row LOB storage issue - IM 7.03
I should have posted my output:
select SEGMENT_NAME, bytes
from USER_SEGMENTS
Where SEGMENT_NAME in (select SEGMENT_NAME from USER_LOBS where TABLE_NAME
like '_10' or TABLE_NAME like '_10C%')
or SEGMENT_NAME like '_10'
or SEGMENT_NAME like '_10C%'
SEGMENT_NAME BYTES
-------------------------- ----------
B10 1048576
H10 2097152
T10 2097152
SYS_LOB0000039519C00010$$ 1048576
SYS_LOB0000039519C00012$$ 1048576
You can verify the In Row of your CLOB data with:
select TABLE_NAME, COLUMN_NAME, SEGMENT_NAME, IN_ROW
from USER_LOBS
where TABLE_NAME like '_10' or TABLE_NAME like '_10C%'
TABLE_NAME COLUMN_NAME SEGMENT_NAME IN_ROW
----------- ------------ -------------------------- ---
T10 C536870915 SYS_LOB0000039519C00012$$ YES
T10 C536870913 SYS_LOB0000039519C00010$$ YES
Fred
-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:[email protected]] On Behalf Of Grooms, Frederick W
Sent: Wednesday, November 18, 2009 11:02 AM
To: [email protected]
Subject: Re: Oracle in-row LOB storage issue - IM 7.03
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:[email protected]] On Behalf Of Axton
Sent: Wednesday, November 18, 2009 10:24 AM
To: [email protected]
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 <[email protected]>
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
_______________________________________________________________________________
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"
