SEO

Hopefully I'm giving enough detail here. I have a table, letter_bin,
which contains two CLOBS and a VARCHAR2(4000). On insert, the two
clobs are NULL, the VARCHAR2 may or may not be populated.

SQL> desc letter_bin
Name Null? Type
----------------------------------------- --------
----------------------------
LTR_BIN_BARCODE_ID NOT NULL NUMBER(18)
LETTER_DETAIL VARCHAR2(4000)
CONTENT CLOB
LTR_XML CLOB

AVG(LENGTH(LETTER_DETAIL)) COUNT(*) MAX(LENGTH(LETTER_DETAIL))
-------------------------- ---------- --------------------------
325.608595 1606180 1374


AVG(LENGTH(CONTENT)) COUNT(*) MAX(LENGTH(CONTENT))
-------------------- ---------- --------------------
364.742673 82638 37248


AVG(LENGTH(LTR_XML)) COUNT(*) MAX(LENGTH(LTR_XML))
-------------------- ---------- --------------------
2131.86929 1308347 5265

(handful of number/date columns omitted for simplicity)

TABLE_NAME AVG_ROW_LEN PCT_FREE PCT_USED NUM_ROWS FULL_SIZE
ACTUAL_SIZE DIFF WASTED
----------- ----------- ---------- ---------- ---------- ----------
----------- ---------- ---------
LETTER_BIN 1639 40 2168152 5577326592
3553601128 2023725464 809490186

It appears that this table is a good candidate for reorg. Since it
has the LOBs, its not a valid candidate for shrink space.
Fortunately, I'm going to me moving this schema to another instance; I
will get to expdp/impdp over the weekend. I also have about 250k
chained rows.

I'm sure that setting the pct_free much higher will take care of the
chained rows but what about the wasted space part? I have a few days
to tune this table's storage parameters before the move; I'd like to
right any wrongs so this doesn't become an issue again.

Not all of the CLOBS are populated so its hard to pin down a one-size-
fits-all solution for this table. Here are some details on the CLOB
fields, and the varchar2. I thought 40% pct_free would be OK since
not all of the LOBs are populated but I guess I'm off, considering my
250k chained rows.

AVG(LENGTH(LETTER_DETAIL)) COUNT(*) MAX(LENGTH(LETTER_DETAIL))
-------------------------- ---------- --------------------------
325.608595 1606180 1374

AVG(LENGTH(CONTENT)) COUNT(*) MAX(LENGTH(CONTENT))
-------------------- ---------- --------------------
364.742673 82638 37248

AVG(LENGTH(LTR_XML)) COUNT(*) MAX(LENGTH(LTR_XML))
-------------------- ---------- --------------------
2131.86929 1308347 5265

Finally, here is the result of dbms_space.space_usage, which is even
more confusing-It says I have only a (relatively)few blocks that are
candidates for reorg.

FS1 Blocks = 0 Bytes = 0
FS2 Blocks = 1 Bytes = 8192
FS3 Blocks = 3 Bytes = 24576
FS4 Blocks = 45 Bytes = 368640
Full Blocks = 680777 Bytes = 5576925184

So now, I'm thoroughly confused-Am I misunderstanding the issue, or
does the game change once LOBs are worked into the equation?

On May 13, 2:11*pm, neil kodner <nkod...@gmail.com> wrote:
> Hopefully I'm giving enough detail here. *I have a table, letter_bin,
> which contains two CLOBS and a VARCHAR2(4000). *On insert, the two
> clobs are NULL, the VARCHAR2 may or may not be populated.
>
> SQL> desc letter_bin
> *Name * * * * * * * * * * * * * * * * * * *Null? * *Type
> *----------------------------------------- --------
> ----------------------------
> *LTR_BIN_BARCODE_ID * * * * * * * * * * * *NOT NULL NUMBER(18)
> *LETTER_DETAIL * * * * * * * * * * * * * * * * * * *VARCHAR2(4000)
> *CONTENT * * * * * * * * * * * * * * * ** * * * * *CLOB
> *LTR_XML * * * * * * * * * * * * * * * ** * * * * *CLOB
>
> AVG(LENGTH(LETTER_DETAIL)) * COUNT(*) MAX(LENGTH(LETTER_DETAIL))
> -------------------------- ---------- --------------------------
> * * * * * * * * 325.608595 * *1606180 * * * * * * * * * * * 1374
>
> AVG(LENGTH(CONTENT)) * COUNT(*) MAX(LENGTH(CONTENT))
> -------------------- ---------- --------------------
> * * * * * 364.742673 * * *82638 * * * * * * * *37248
>
> AVG(LENGTH(LTR_XML)) * COUNT(*) MAX(LENGTH(LTR_XML))
> -------------------- ---------- --------------------
> * * * * * 2131.86929 * *1308347 * * * * * * * * 5265
>
> (handful of number/date columns omitted for simplicity)
>
> TABLE_NAME *AVG_ROW_LEN * PCT_FREE * PCT_USED * NUM_ROWS *FULL_SIZE
> ACTUAL_SIZE * * * DIFF * * WASTED
> ----------- ----------- ---------- ---------- ---------- ----------
> ----------- ---------- ---------
> LETTER_BIN * * * * 1639 * * * * 40 * * * * * ** 2168152 5577326592
> 3553601128 2023725464 *809490186
>
> It appears that this table is a good candidate for reorg. *Since it
> has the LOBs, its not a valid candidate for shrink space.
> Fortunately, I'm going to me moving this schema to another instance; I
> will get to expdp/impdp over the weekend. *I also have about 250k
> chained rows.
>
> I'm sure that setting the pct_free much higher will take care of the
> chained rows but what about the wasted space part? *I have a few days
> to tune this table's storage parameters before the move; I'd like to
> right any wrongs so this doesn't become an issue again.
>
> Not all of the CLOBS are populated so its hard to pin down a one-size-
> fits-all solution for this table. *Here are some details on the CLOB
> fields, and the varchar2. *I thought 40% pct_free would be OK since
> not all of the LOBs are populated but I guess I'm off, considering my
> 250k chained rows.
>
> AVG(LENGTH(LETTER_DETAIL)) * COUNT(*) MAX(LENGTH(LETTER_DETAIL))
> -------------------------- ---------- --------------------------
> * * * * * * * * 325.608595 * *1606180 * * * * * * * * * * * 1374
>
> AVG(LENGTH(CONTENT)) * COUNT(*) MAX(LENGTH(CONTENT))
> -------------------- ---------- --------------------
> * * * * * 364.742673 * * *82638 * * * * * * * *37248
>
> AVG(LENGTH(LTR_XML)) * COUNT(*) MAX(LENGTH(LTR_XML))
> -------------------- ---------- --------------------
> * * * * * 2131.86929 * *1308347 * * * * * * * * 5265
>
> Finally, here is the result of dbms_space.space_usage, which is even
> more confusing-It says I have only a (relatively)few blocks that are
> candidates for reorg.
>
> FS1 Blocks = 0 Bytes = 0
> FS2 Blocks = 1 Bytes = 8192
> FS3 Blocks = 3 Bytes = 24576
> FS4 Blocks = 45 Bytes = 368640
> Full Blocks = 680777 Bytes = 5576925184
>
> So now, I'm thoroughly confused-Am I misunderstanding the issue, or
> does the game change once LOBs are worked into the equation?

Remember that Oracle creates a LOB table for every LOB column and all
data over 2000 bytes is directed to the LOB table.

To avoid table fragmentation and row migration problems on the base
table I would declare both LOBs as no inline storage then all the LOB
data will be in the associated LOB table for each column, if
fragmentation and row migration were determined to be a problem. What
does an analyze show for chained rows? Then you would only have to
worry about setting the pctfree to handle the effect from the
letter_detail column.

HTH -- Mark D Powell --

On May 14, 11:45*am, Mark D Powell <Mark.Pow...@eds.com> wrote:
> On May 13, 2:11*pm, neil kodner <nkod...@gmail.com> wrote:
>
>
>
> > Hopefully I'm giving enough detail here. *I have a table, letter_bin,
> > which contains two CLOBS and a VARCHAR2(4000). *On insert, the two
> > clobs are NULL, the VARCHAR2 may or may not be populated.
>
> > SQL> desc letter_bin
> > *Name * * * * * * * * * * * * * * * * * * *Null? * *Type
> > *----------------------------------------- --------
> > ----------------------------
> > *LTR_BIN_BARCODE_ID * * * * * * * * * * * *NOTNULL NUMBER(18)
> > *LETTER_DETAIL * * * * * * * * * * * * * ** * * * *VARCHAR2(4000)
> > *CONTENT * * * * * * * * * * * * * * * * * * * * * *CLOB
> > *LTR_XML * * * * * * * * * * * * * * * * * * * * * *CLOB
>
> > AVG(LENGTH(LETTER_DETAIL)) * COUNT(*) MAX(LENGTH(LETTER_DETAIL))
> > -------------------------- ---------- --------------------------
> > * * * * * * * * 325.608595 * *1606180 * * * * * * * * * * * 1374
>
> > AVG(LENGTH(CONTENT)) * COUNT(*) MAX(LENGTH(CONTENT))
> > -------------------- ---------- --------------------
> > * * * * * 364.742673 * * *82638 * * * * * * * *37248
>
> > AVG(LENGTH(LTR_XML)) * COUNT(*) MAX(LENGTH(LTR_XML))
> > -------------------- ---------- --------------------
> > * * * * * 2131.86929 * *1308347 * * * * * * * * 5265
>
> > (handful of number/date columns omitted for simplicity)
>
> > TABLE_NAME *AVG_ROW_LEN * PCT_FREE * PCT_USED * NUM_ROWS *FULL_SIZE
> > ACTUAL_SIZE * * * DIFF * * WASTED
> > ----------- ----------- ---------- ---------- ---------- ----------
> > ----------- ---------- ---------
> > LETTER_BIN * * * * 1639 * * * * 40 * * * * * * * 2168152 5577326592
> > 3553601128 2023725464 *809490186
>
> > It appears that this table is a good candidate for reorg. *Since it
> > has the LOBs, its not a valid candidate for shrink space.
> > Fortunately, I'm going to me moving this schema to another instance; I
> > will get to expdp/impdp over the weekend. *I also have about 250k
> > chained rows.
>
> > I'm sure that setting the pct_free much higher will take care of the
> > chained rows but what about the wasted space part? *I have a few days
> > to tune this table's storage parameters before the move; I'd like to
> > right any wrongs so this doesn't become an issue again.
>
> > Not all of the CLOBS are populated so its hard to pin down a one-size-
> > fits-all solution for this table. *Here are some details on the CLOB
> > fields, and the varchar2. *I thought 40% pct_free would be OK since
> > not all of the LOBs are populated but I guess I'm off, considering my
> > 250k chained rows.
>
> > AVG(LENGTH(LETTER_DETAIL)) * COUNT(*) MAX(LENGTH(LETTER_DETAIL))
> > -------------------------- ---------- --------------------------
> > * * * * * * * * 325.608595 * *1606180 * * * * * * * * * * * 1374
>
> > AVG(LENGTH(CONTENT)) * COUNT(*) MAX(LENGTH(CONTENT))
> > -------------------- ---------- --------------------
> > * * * * * 364.742673 * * *82638 * * * * * * * *37248
>
> > AVG(LENGTH(LTR_XML)) * COUNT(*) MAX(LENGTH(LTR_XML))
> > -------------------- ---------- --------------------
> > * * * * * 2131.86929 * *1308347 * * * * * * * * 5265
>
> > Finally, here is the result of dbms_space.space_usage, which is even
> > more confusing-It says I have only a (relatively)few blocks that are
> > candidates for reorg.
>
> > FS1 Blocks = 0 Bytes = 0
> > FS2 Blocks = 1 Bytes = 8192
> > FS3 Blocks = 3 Bytes = 24576
> > FS4 Blocks = 45 Bytes = 368640
> > Full Blocks = 680777 Bytes = 5576925184
>
> > So now, I'm thoroughly confused-Am I misunderstanding the issue, or
> > does the game change once LOBs are worked into the equation?
>
> Remember that Oracle creates a LOB table for every LOB column and all
> data over 2000 bytes is directed to the LOB table.
>
> To avoid table fragmentation and row migration problems on the base
> table I would declare both LOBs as no inline storage then all the LOB
> data will be in the associated LOB table for each column, if
> fragmentation and row migration were determined to be a problem. *What
> does an analyze show for chained rows? *Then you would only have to
> worry about setting the pctfree to handle the effect from the
> letter_detail column.
>
> HTH -- Mark D Powell --

Currently the LOBs are stored inline, as the lob sizes are
(relatively) small. I made this decision based on a LOB tuning white
paper from Oracle.com. Is the lob storage something I can change with
an alter table move command, or do I need to rebuild/repopulate the
table?

"neil kodner" <nkodner@gmail.com> wrote in message
news:f68f4756-95ad-4842-ba17-5f1f33069c03@b1g2000hsg.googlegroups.com...
> Hopefully I'm giving enough detail here. I have a table, letter_bin,
> which contains two CLOBS and a VARCHAR2(4000). On insert, the two
> clobs are NULL, the VARCHAR2 may or may not be populated.
>
> SQL> desc letter_bin
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> LTR_BIN_BARCODE_ID NOT NULL NUMBER(18)
> LETTER_DETAIL VARCHAR2(4000)
> CONTENT CLOB
> LTR_XML CLOB
>
> AVG(LENGTH(LETTER_DETAIL)) COUNT(*) MAX(LENGTH(LETTER_DETAIL))
> -------------------------- ---------- --------------------------
> 325.608595 1606180 1374
>
>
> AVG(LENGTH(CONTENT)) COUNT(*) MAX(LENGTH(CONTENT))
> -------------------- ---------- --------------------
> 364.742673 82638 37248
>
>
> AVG(LENGTH(LTR_XML)) COUNT(*) MAX(LENGTH(LTR_XML))
> -------------------- ---------- --------------------
> 2131.86929 1308347 5265
>
> (handful of number/date columns omitted for simplicity)
>
> TABLE_NAME AVG_ROW_LEN PCT_FREE PCT_USED NUM_ROWS FULL_SIZE
> ACTUAL_SIZE DIFF WASTED
> ----------- ----------- ---------- ---------- ---------- ----------
> ----------- ---------- ---------
> LETTER_BIN 1639 40 2168152 5577326592
> 3553601128 2023725464 809490186
>
> It appears that this table is a good candidate for reorg. Since it
> has the LOBs, its not a valid candidate for shrink space.
> Fortunately, I'm going to me moving this schema to another instance; I
> will get to expdp/impdp over the weekend. I also have about 250k
> chained rows.
>
> I'm sure that setting the pct_free much higher will take care of the
> chained rows but what about the wasted space part? I have a few days
> to tune this table's storage parameters before the move; I'd like to
> right any wrongs so this doesn't become an issue again.
>
> Not all of the CLOBS are populated so its hard to pin down a one-size-
> fits-all solution for this table. Here are some details on the CLOB
> fields, and the varchar2. I thought 40% pct_free would be OK since
> not all of the LOBs are populated but I guess I'm off, considering my
> 250k chained rows.
>
> AVG(LENGTH(LETTER_DETAIL)) COUNT(*) MAX(LENGTH(LETTER_DETAIL))
> -------------------------- ---------- --------------------------
> 325.608595 1606180 1374
>
> AVG(LENGTH(CONTENT)) COUNT(*) MAX(LENGTH(CONTENT))
> -------------------- ---------- --------------------
> 364.742673 82638 37248
>
> AVG(LENGTH(LTR_XML)) COUNT(*) MAX(LENGTH(LTR_XML))
> -------------------- ---------- --------------------
> 2131.86929 1308347 5265
>
> Finally, here is the result of dbms_space.space_usage, which is even
> more confusing-It says I have only a (relatively)few blocks that are
> candidates for reorg.
>
> FS1 Blocks = 0 Bytes = 0
> FS2 Blocks = 1 Bytes = 8192
> FS3 Blocks = 3 Bytes = 24576
> FS4 Blocks = 45 Bytes = 368640
> Full Blocks = 680777 Bytes = 5576925184
>
> So now, I'm thoroughly confused-Am I misunderstanding the issue, or
> does the game change once LOBs are worked into the equation?


Are you using 9i or 10g.

If it's 9i then your confusion may relate to 9i not
getting the avg_row_len correct when there are
lobs in the table - it loses them !

Create a simple table with a couple of small columns, and
a clob somewhere - insert a row, then collect stats and
see what you get.

There's a recent thread on exactly this topic on OTN:

http://forums.oracle.com/forums/thre...hreadID=654447


--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

On May 14, 12:36*pm, "Jonathan Lewis" <jonat...@jlcomp.demon.co.uk>
wrote:
> "neil kodner" <nkod...@gmail.com> wrote in message
>
> news:f68f4756-95ad-4842-ba17-5f1f33069c03@b1g2000hsg.googlegroups.com...
>
>
>
> > Hopefully I'm giving enough detail here. *I have a table, letter_bin,
> > which contains two CLOBS and a VARCHAR2(4000). *On insert, the two
> > clobs are NULL, the VARCHAR2 may or may not be populated.
>
> > SQL> desc letter_bin
> > Name * * * * * * * * * * * * * * * * ** *Null? * *Type
> > ----------------------------------------- --------
> > ----------------------------
> > LTR_BIN_BARCODE_ID * * * * * * * * * * * *NOT NULL NUMBER(18)
> > LETTER_DETAIL * * * * * * * * * * * * * * * * * * *VARCHAR2(4000)
> > CONTENT * * * * * * * * * * * * * * * * * * * * * *CLOB
> > LTR_XML * * * * * * * * * * * * * * * * * * * * * *CLOB
>
> > AVG(LENGTH(LETTER_DETAIL)) * COUNT(*) MAX(LENGTH(LETTER_DETAIL))
> > -------------------------- ---------- --------------------------
> > * * * * * * * *325.608595 * *1606180 * * * ** * * * * * * 1374
>
> > AVG(LENGTH(CONTENT)) * COUNT(*) MAX(LENGTH(CONTENT))
> > -------------------- ---------- --------------------
> > * * * * *364.742673 * * *82638 * * * * * * * *37248
>
> > AVG(LENGTH(LTR_XML)) * COUNT(*) MAX(LENGTH(LTR_XML))
> > -------------------- ---------- --------------------
> > * * * * *2131.86929 * *1308347 * * * * * * ** 5265
>
> > (handful of number/date columns omitted for simplicity)
>
> > TABLE_NAME *AVG_ROW_LEN * PCT_FREE * PCT_USED * NUM_ROWS *FULL_SIZE
> > ACTUAL_SIZE * * * DIFF * * WASTED
> > ----------- ----------- ---------- ---------- ---------- ----------
> > ----------- ---------- ---------
> > LETTER_BIN * * * * 1639 * * * * 40 * * * * * * * 2168152 5577326592
> > 3553601128 2023725464 *809490186
>
> > It appears that this table is a good candidate for reorg. *Since it
> > has the LOBs, its not a valid candidate for shrink space.
> > Fortunately, I'm going to me moving this schema to another instance; I
> > will get to expdp/impdp over the weekend. *I also have about 250k
> > chained rows.
>
> > I'm sure that setting the pct_free much higher will take care of the
> > chained rows but what about the wasted space part? *I have a few days
> > to tune this table's storage parameters before the move; I'd like to
> > right any wrongs so this doesn't become an issue again.
>
> > Not all of the CLOBS are populated so its hard to pin down a one-size-
> > fits-all solution for this table. *Here are some details on the CLOB
> > fields, and the varchar2. *I thought 40% pct_free would be OK since
> > not all of the LOBs are populated but I guess I'm off, considering my
> > 250k chained rows.
>
> > AVG(LENGTH(LETTER_DETAIL)) * COUNT(*) MAX(LENGTH(LETTER_DETAIL))
> > -------------------------- ---------- --------------------------
> > * * * * * * * *325.608595 * *1606180 * * * ** * * * * * * 1374
>
> > AVG(LENGTH(CONTENT)) * COUNT(*) MAX(LENGTH(CONTENT))
> > -------------------- ---------- --------------------
> > * * * * *364.742673 * * *82638 * * * * * * * *37248
>
> > AVG(LENGTH(LTR_XML)) * COUNT(*) MAX(LENGTH(LTR_XML))
> > -------------------- ---------- --------------------
> > * * * * *2131.86929 * *1308347 * * * * * * ** 5265
>
> > Finally, here is the result of dbms_space.space_usage, which is even
> > more confusing-It says I have only a (relatively)few blocks that are
> > candidates for reorg.
>
> > FS1 Blocks = 0 Bytes = 0
> > FS2 Blocks = 1 Bytes = 8192
> > FS3 Blocks = 3 Bytes = 24576
> > FS4 Blocks = 45 Bytes = 368640
> > Full Blocks = 680777 Bytes = 5576925184
>
> > So now, I'm thoroughly confused-Am I misunderstanding the issue, or
> > does the game change once LOBs are worked into the equation?
>
> Are you using 9i or 10g.
>
> If it's 9i then your confusion may relate to 9i not
> getting the avg_row_len correct when there are
> lobs in the table - it loses them !
>
> Create a simple table with a couple of small columns, and
> a clob somewhere - insert a row, then collect stats and
> see what you get.
>
> There's a recent thread on exactly this topic on OTN:
>
> http://forums.oracle.com/forums/thre...hreadID=654447
>
> --
> Regards
>
> Jonathan Lewishttp://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html

I'm using 10.2.0.3 on Solaris. Is out-of-line CLOB storage a good
option considering that the avg(length() of my clobs are 364 and 2131
charcters? Additionally, in a 2.17M row table(682872 8k blocks), clob
ltr_xml is null 854k rows, and content is null in 2.08M rows.
Understanding that this isn't the best table design(and outside of my
control), is this something I should be altering once I migrate the db?

"neil kodner" <nkodner@gmail.com> wrote in message
news:5b6e41fd-0d59-4e84-a43e-e4d234c3b762@d45g2000hsc.googlegroups.com...

I'm using 10.2.0.3 on Solaris. Is out-of-line CLOB storage a good
option considering that the avg(length() of my clobs are 364 and 2131
charcters? Additionally, in a 2.17M row table(682872 8k blocks), clob
ltr_xml is null 854k rows, and content is null in 2.08M rows.
Understanding that this isn't the best table design(and outside of my
control), is this something I should be altering once I migrate the db?


That's a little difficult to answer.

Looking at the table - one id and 3 versions of the "letter"
it looks like the design has already 'put the letter out of line'
to avoid bulking up a table of more regular data. If this is
the case, then you probably want to avoid extra random
I/Os by keeping the lobs in line, rather than deliberately
putting them out of line.

It's then a question of picking a good value for PCTFREE
for this table - based on the way you insert, update and delete
rows - so that you don't get excessive row chaining.

This might be a case where you choose to waste space in a
block to make sure that most calls to this table read only
one block. It certainly looks like a case where you might
consider putting the table into the RECYCLE pool - or
even picking a different size data block to get a totally
isolated buffer cache and an ideal block size.

There's an example of a script on my blog I used to analyze
lob sizing for a vaguely related problem - it might be useful
to find out how that average of 5K spreads out in (for
example) 2Kb steps:

http://jonathanlewis.wordpress.com/2...11/lob-sizing/

--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html