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

On May 14, 12:36 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "neil kodner" <nkod..._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 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? Received on Wed May 14 2008 - 14:45:08 CDT