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

On May 13, 2:11 pm, neil kodner <nkod..._at_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 -- Received on Wed May 14 2008 - 12:45:22 CDT