Re: PCTFREE/PCTUSED for table with updates to LOBs?

"neil kodner" <nkodner_at_gmail.com> wrote in message news:f68f4756-95ad-4842-ba17-5f1f33069c03_at_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/thread.jspa?threadID=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