Re: Calculating Average Row Length in Oracle 10g

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 9 Jun 2009 20:14:40 +0100
Message-ID: <av-dnWErGss7KbPXnZ2dnUVZ8iednZ2d_at_bt.com>

"Nag" <naga.challa_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 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

Received on Tue Jun 09 2009 - 14:14:40 CDT