Re: Calculating Average Row Length in Oracle 10g

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Wed, 10 Jun 2009 07:07:09 -0700 (PDT)
Message-ID: <1255cb26-91ee-4958-9e42-e41dd7c379cc_at_v4g2000vba.googlegroups.com>

On Jun 9, 3:14 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "Nag" <naga.cha..._at_gmail.com> wrote in message
>
> news:36c2aca6-179f-4068-adfe-fc6c18ac7baf_at_r3g2000vbp.googlegroups.com...
>
> > All,
> > We can get this by analyzing the tables in a schema and looking at the
> > column avg_row_len.
> > But, is there a sql so that I can calculate manually. In out
> > environment, we gather statistics once and use them all the time.
>
> > Thanks,
> > Naga
>
> Code like the following would give you roughly
> the correct answer for simple tables.  Not sure
> how this should be altered to suit tables with
> LOB or LONG columns, or tables with more than
> 255 columns.
>
> select
>  3 +
>  (
>   nvl(c1,0) +
>   nvl(c2,0) +
>   nvl(c3,0) +
>   nvl(c4,0)
>  ) / ct
> from
>  (
>  select
>   count(*)   ct,
>   sum(sys_op_opnsize(ID))  c1,
>   sum(sys_op_opnsize(GRP)) c2,
>   sum(sys_op_opnsize(SMALL_VC)) c3,
>   sum(sys_op_opnsize(PADDING)) c4
>  from
>   t1
>  )
> ;
>
> The value 3 is "column count - 1";
>
> --
> 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

Naga, your said that "we gather statistics once and use them all the time." However with 10g+ Oracle provides a scheduled task that automatically collects CBO statistics on your tables. Have you disabled it? Otherwise, Oracle will update the statistics. If you only collected statistics one time and used them forever on prior versions of Oracle and have upgraded you will be affected by this job.

HTH -- Mark D Powell -- Received on Wed Jun 10 2009 - 09:07:09 CDT