Calculating Average Row Length in Oracle 10g

[Nag]

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

[Jonathan Lewis]

"Nag" <naga....@gmail.com> wrote in message
news:36c2aca6-179f-4068...@r3g2000vbp.googlegroups.com...

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 Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

[Mark D Powell]

On Jun 9, 3:14 pm, "Jonathan Lewis" <jonat...@jlcomp.demon.co.uk>
wrote:
> "Nag" <naga.cha...@gmail.com> wrote in message

> 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 --