Re: Calculating Average Row Length in Oracle 10g

From: Jonathan Lewis <>
Date: Tue, 9 Jun 2009 20:14:40 +0100
Message-ID: <>

"Nag" <> wrote in message
> 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.

 3 +

  nvl(c1,0) +
  nvl(c2,0) +
  nvl(c3,0) +

 ) / ct
  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


The value 3 is "column count - 1";


Jonathan Lewis

Author: Cost Based Oracle: Fundamentals

The Co-operative Oracle Users' FAQ
Received on Tue Jun 09 2009 - 14:14:40 CDT

Original text of this message