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

On May 14, 11:45 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> 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 --

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? Received on Wed May 14 2008 - 12:53:53 CDT