Re: Average row size ques...

From: Jared Still <jkstill_at_bcbso.com>
Date: Wed, 24 May 2000 12:15:34 -0700 (PDT)
Message-Id: <10507.106569@fatcity.com>

Try using this script, no analyze required.

Jared

set pause off echo off verify off head off pages 0 feed off set line 100
set trimspool on

col cuser noprint new_value chkuser
col ctab noprint new_value chktab

prompt Schema:
set term off feed off
select upper('&1') cuser from dual;

set term on feed on
prompt Get rowsize for which table:
set term off feed off
select upper('&2') ctab from dual;

set term on feed on

prompt This average will be based on the first 10000 rows of &&chktab

set serveroutput on size 1000000 term off

@tmpfile

set echo off term off feed off

spool &&_tmp_file_name_

select 'col rowsize format 9999.999' from dual; select 'select avg(' from dual;

declare

	cursor chktab_cur
	is
	select column_name, data_type
	from all_tab_columns
	where table_name = upper('&&chktab')
	and owner = upper('&&chkuser');
begin
	for tabrec in chktab_cur
	loop
		dbms_output.put_line( 
			'	nvl(vsize(decode(' || 
			tabrec.column_name || 
			', null, null, ' ||
			tabrec.column_name || ')),0)+'
		);
	end loop;

end;
/

select '0) rowsize' from dual;
select 'from &&chkuser..&&chktab' from dual;
select 'where rownum < 10001;' from dual;

spool off

set feed on head on pages 24 term on verify on

@&&_tmp_file_name_

set feed off term off
@rmfile &&_tmp_file_name_
set feed on term on

set verify off

undef 1 2

tmpfile.sql
create a temporary file name
it will be of the form /tmp/<INSTANCE>.<USER>.<HSECS>
the temp filename will be in the variable _tmp_file_name_
you must be able to select from v$timer for this to work
use rmfile to remove tmp files

set verify off feed off
set echo off pause off feed off term off

var tmpstamp_ varchar2(30);
var dbname_ varchar2(8);

declare

	tmpsecs  number;
	secs varchar2(9);
	sec_len integer := 0;
begin
	select hsecs into tmpsecs from v$timer;

	--dbms_output.enable(1000000);
	--dbms_output.put_line('secs: ' || secs);

	select to_char(tmpsecs)
	into :tmpstamp_ 
	from dual;

	select lower(substr(global_name,1,instr(global_name,'.')-1))
	into :dbname_
	from global_name;

end;
/

col tmpstamp_ noprint new_value timestamp col filetmp_ noprint new_value _tmp_file_name_ col dbname_ noprint new_value instance

select :tmpstamp_ tmpstamp_, :dbname_ dbname_ from dual;

select

	'/tmp/' || 
	'&&instance' || 
	'.' || 
	lower(user)  || '.' ||
	'&timestamp' filetmp_

from dual
/

set term on

On Tue, 23 May 2000, cyril wrote:

> 
> 
> 
> Hello!
> 
>  But I am 'scared' to analyze any objects in our database,
> 
>  'cos though we are running it in RULE_BASED mode, we have sometimes found
> that
>   some queries run in COST BASED..
> 
> Regards
> ---------------------- Forwarded by cyril/DBAMUM01/SHCIL/IN on 05/26/2000
> 09:06 AM ---------------------------
> 
> 
> Lisa_Koivu_at_gelco.com on 05/23/2000 04:56:09 AM
> 
> Please respond to ORACLE-L_at_fatcity.com
> 
> To:   Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> cc:    (bcc: cyril/DBAMUM01/SHCIL/IN)
> 
> 
> 
> 
> 
> Hi Steve,
> 
> analyze the table and look in DBA_TABLES for AVG_ROW_LEN.  That's the
> quickest
> way.
> 
> Lisa
> 
> 
> 
> 
> 
> "Stephen J Palmer" <spalmer_at_brk.photronics.com> on 05/22/2000 04:16:40 PM
> 
> Please respond to ORACLE-L_at_fatcity.com
> 
> To:   Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> cc:    (bcc: Lisa Koivu/GELCO)
> 
> 
> 
> 
> Hello all,
> I need to figure out what the average row size of a table is. I'm sure I
> have come
> across this a while back, but have been unable to locate it from my
> notes.
> 
> Any help with this would be greatly appreciated.
> 
> Thanks in advance.
> 
> Steve
> 
> Stephen Palmer
> Junior Database Administrator
> Photronics Inc.
> 15 Secor Road
> Brookfield, Conn.
> 203.740.5331
> Pager 203-830-0306  or  1-800-706-7109
> mailto:spalmer_at_brk.photronics.com
> Home Number 203.270.8159
> Alpha Pager mailto:spalmer.pager_at_brk.photronics.com
> 
> --
> Author: Stephen J Palmer
>   INET: spalmer_at_brk.photronics.com
> 
> Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California        -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> 
> 
> 
> 
> 
> 
> --
> Author:
>   INET: Lisa_Koivu_at_gelco.com
> 
> Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California        -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> 
> 
> 
> 
> -- 
> Author: cyril
>   INET: cyril_at_stockholding.com
> 
> Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California        -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).

Received on Wed May 24 2000 - 14:15:34 CDT