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

On May 14, 2: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- Hide quoted text -
>
> - Show quoted text -

I just ran Jonathan's posted example SQL on 9.2.0.6 and got the same incorrect row_len of 15 bytes; however, an analyze of the table produced a row lenth of 3057.

If a table with inline LOBs is at the center of some performance issues then on 9.2 it may be worth resorting to analyzing the table to see if this results in better performance until such time as the system can be upgraded to 10g.

That or you use the analyze derived value to manually update the dmbs_stats produced statistics.

HTH -- Mark D Powell -- Received on Thu May 15 2008 - 12:09:12 CDT