Web Hosting by Netfirms | Free Domain Names by Netfirms

Oracle SQL*/PLSQL Examples

rem $DBA/analyze.sql
rem
rem Performs an ANALYZE COMPUTE STATISTICS on all non-SYS/SYSTEM-owned tables
rem and indexes.
rem
rem Creates an analysis report (analyze.lis) of all analyzed tables and indexes.
rem
rem ***** Notes: *****
rem    1) This may take many hours when analyzing large tables!
rem    2) The analyze will AUTOMATICALLY turn on Cost-Based Optimization !!!
rem    3) Oracle requires locks on the tables to be analyzed.  If this prevents
rem       this script from successfully executing, use the analinds.sql script.
rem
rem Last Change 11/17/98 by Brian Lomasky - Ensure distinct_keys is never 0.
rem					    Handle null or zero values for
rem					    num_rows, chain_cnt, avg_row_len.
rem Last Change 05/22/97 by Brian Lomasky
rem
set echo off
set feedback off
set heading off
set pagesize 0
set serveroutput on size 1000000
set termout off
set verify off
drop table anal_temp;
set termout on
create table anal_temp (lineno number, text varchar2(80));
declare
	cursor time_cursor is select
		to_char(sysdate, 'HH:MI:SS')
		from dual;

	cursor tab_cursor is select
		owner,
		table_name,
		nvl(num_rows, 0),
		nvl(chain_cnt, 0),
		nvl(avg_row_len, 0)
		from sys.dba_tables
		where owner != 'SYS' and owner != 'SYSTEM'
		and table_name != 'ANAL_TEMP'
		order by owner, table_name;

	cursor ind_cursor (c_own VARCHAR2, c_tab VARCHAR2) is select
		to_char(sysdate, 'HH:MI:SS'),
		index_name,
		decode(uniqueness,
			'NONUNIQUE','Non-Unique',
			'UNIQUE','    Unique',
			'BITMAP','    Bitmap', uniqueness),
		decode(nvl(distinct_keys, 1), 0, 1, distinct_keys)
		from sys.dba_indexes
		where owner = c_own and table_name = c_tab
		order by index_name;

	cursor col_cursor (c_own varchar2, c_ind varchar2) is select
		decode(column_position, 1, column_name, ', ' || column_name)
		from sys.dba_ind_columns
		where index_owner = c_own and index_name = c_ind
		order by column_position;

	lv_owner		sys.dba_tables.owner%TYPE;
	lv_table_name		sys.dba_tables.table_name%TYPE;
	lv_num_rows		sys.dba_tables.num_rows%TYPE;
	lv_chain_cnt		sys.dba_tables.chain_cnt%TYPE;
	lv_avg_row_len		sys.dba_tables.avg_row_len%TYPE;
	lv_index_name		sys.dba_indexes.index_name%TYPE;
	lv_distinct_keys	sys.dba_indexes.distinct_keys%TYPE;
	lv_column_name		sys.dba_ind_columns.column_name%TYPE;
	lv_uniqueness		char(10);
	now			varchar2(8);
	lineno			number;
	recno			number;
	n			number;
	a_lin			varchar2(80);
	x			varchar2(80);

	function vwri(x_lin in varchar2, x_str in varchar2,
		x_force in number) return varchar2 is
	begin
		if length(x_lin) + length(x_str) > 80
		then
			lineno := lineno + 1;
			insert into anal_temp values (lineno, x_lin);
			if x_force = 0
			then
				return '                              ' ||
					x_str;
			else
				lineno := lineno + 1;
				insert into anal_temp values (lineno, x_str);
				return '';
			end if;
		else
			if x_force = 0
			then
				return x_lin||x_str;
			else
				lineno := lineno + 1;
				insert into anal_temp values (
					lineno, x_lin||x_str);
				return '';
			end if;
		end if;
	end vwri;

	function format_owner_table (the_owner in varchar2,
		the_table in varchar2) return varchar2 is
	begin
		n := length(the_owner) + length(the_table);
		if n < 40 then
			return rpad(the_owner || '.' || the_table, 40);
		else
			return '..' || substr(the_owner || '.' || the_table,
				n-36, 38);
		end if;
	end format_owner_table;

	procedure wri (my_txt in varchar2) is
	begin
		lineno := lineno + 1;
		insert into anal_temp values (lineno, my_txt);
	end wri;

begin
	lineno := 0;
	recno := 0;
	/* Analyze all of the desired tables and indexes */
	open tab_cursor;
	loop
		open time_cursor;
		fetch time_cursor into now;
		close time_cursor;
		fetch tab_cursor into
			lv_owner,
			lv_table_name,
			lv_num_rows,
			lv_chain_cnt,
			lv_avg_row_len;
		exit when tab_cursor%notfound;

		dbms_output.put_line('Starting analysis of ' ||
			format_owner_table(lv_owner, lv_table_name) ||
			' at ' || now);

		/* Analyze each table */
		dbms_ddl.analyze_object('TABLE', lv_owner, lv_table_name,
			'COMPUTE');

		open ind_cursor(lv_owner, lv_table_name);
		loop
			fetch ind_cursor into
				now,
				lv_index_name,
				lv_uniqueness,
				lv_distinct_keys;
			exit when ind_cursor%notfound;

			dbms_output.put_line('Starting analysis of ' ||
				rpad(lv_index_name, 40) || ' at ' || now);

			/* Analyze each index for this table */
			dbms_ddl.analyze_object('INDEX', lv_owner,
				lv_index_name, 'COMPUTE');

		end loop;
		close ind_cursor;
	end loop;
	close tab_cursor;

	dbms_output.put_line('Done with analysis at ' || now);

	/* Rerun all the cursors again to create the report */

	wri('                                                            ' ||
		'   Avg');
	wri('                                                       Chain' ||
		'   Row   Distinct');
	wri('Object Owner / Name                       Num Rows     Count' ||
		'   Len     Keys');
	wri('---------------------------------------- --------- ---------' ||
		' -----  ---------');

	open tab_cursor;
	loop
		fetch tab_cursor into
			lv_owner,
			lv_table_name,
			lv_num_rows,
			lv_chain_cnt,
			lv_avg_row_len;
		exit when tab_cursor%notfound;

		recno := recno + 1;
		if recno > 1 then
			wri(' ');
		end if;

		wri(format_owner_table(lv_owner, lv_table_name) ||
			to_char(lv_num_rows, '999999999') ||
			to_char(lv_chain_cnt, '999999999') ||
			to_char(lv_avg_row_len, '99999'));

		open ind_cursor(lv_owner, lv_table_name);
		loop
			fetch ind_cursor into
				now,
				lv_index_name,
				lv_uniqueness,
				lv_distinct_keys;
			exit when ind_cursor%notfound;

			wri(rpad('  ' || lv_uniqueness || ' ' || lv_index_name,
				40) || '                           ' ||
				to_char(lv_distinct_keys, '999999999'));

			a_lin := '             Indexed columns: ';
			open col_cursor(lv_owner, lv_index_name);
			loop
				fetch col_cursor into lv_column_name;
				exit when col_cursor%notfound;
				a_lin := vwri(a_lin, lv_column_name, 0);
			end loop;
			close col_cursor;
			a_lin := vwri(a_lin, '', 1);

			/* Analyze for proper index type */
			if lv_uniqueness = 'Non-Unique' then
				if lv_distinct_keys < 21 then
					wri('');
					wri('  *************************' ||
						'**************************' ||
						'***************************');
					wri('  ** The above non-unique index' ||
						' might not be appropriate,' ||
						' since non-unique    **');
					wri('  ** indexes should be created' ||
						' on columns which return' ||
						' no more than 2-4% of   **');
					wri('  ** the total number of rows' ||
						' in the table - Assuming' ||
						' an average distribution **');
					x := to_char(trunc(100 /
						lv_distinct_keys));
					wri('  ** of values, this index will' ||
						' return ' || x ||
						'% of the rows' || substr(
						'                         ',
						1, 26 - length(x)) || '**');
					wri('  *************************' ||
						'**************************' ||
						'***************************');
					wri('');
				end if;
			elsif lv_uniqueness = '    Bitmap' then
				if lv_num_rows > 0 then
					if lv_distinct_keys / lv_num_rows > .001
					then
						wri('');
						wri('  ********************' ||
							'******************' ||
							'******************' ||
							'**********************'
							);
						wri('  ** The above bitmap' ||
							' index might not' ||
							' be appropriate,' ||
							' since bitmap' ||
							' indexes    **');
						wri('  ** should be created' ||
							' on columns having' ||
							' no more than 1' ||
							' unique value' ||
							' per      **');
						x := to_char(trunc(lv_num_rows /
							lv_distinct_keys));
						wri('  ** 1000 rows - This' ||
							' index currently' ||
							' has 1 unique' ||
							' value per ' || x ||
							' rows' || substr(
							'           ', 1, 12 -
							length(x)) || '**');
						wri('  ********************' ||
							'******************' ||
							'******************' ||
							'**********************'
							);
						wri('');
					end if;
				end if;
			end if;

		end loop;
		close ind_cursor;

	end loop;
	close tab_cursor;

	commit;
exception
        when others then
                rollback;
                raise_application_error(-20000,
                        'Unexpected error on ' || lv_owner || '.' ||
			lv_table_name || ': ' || to_char(SQLCODE) || chr(10) ||
			sqlerrm || chr(10) || 'Aborting...');
end;
/

set termout off
spool analyze.lis
select text from anal_temp order by lineno;
spool off
drop table anal_temp;
set termout on
select 'Created analyze.lis report for your viewing pleasure...' from dual;
exit