how to use analyze with MONITORING and dba_tab_modifications

From: Jack Silvey <jack_silvey_at_yahoo.com>
Date: Wed, 08 May 2002 13:43:29 -0800
Message-ID: <F001.0045C1C7.20020508134329@fatcity.com>

Howdy all,

We had some problems with dbms_stats, so we went back to good ol analyze until dbms_stats firms up a little. But, I sure liked that ALTER TABLE MONITORING and dba_tab_modifications, so I figured out a way to enable us to remove rows from dba_tab_modifications once the analyze is complete.

(BTW, Larry Elkins is working with us here, and just LOVES the fact that we are hacking the DD in production. ;) )

Here it is:

--THIS CURSOR GETS THE NON-PARTITIONED TABLE NAMES
THAT ARE ON DBA_TAB_MODIFCATIONS: cursor c_tab is
select u.name table_owner, o.name table_name,m.obj# from sys.mon_mods$ m, sys.obj$ o, sys.tab$ t,sys.user$ u
where o.obj# = m.obj#

and o.obj# = t.obj# 
and o.owner# = u.user#
and u.name not in ('SYS','SYSTEM','OUTLN','PERFSTAT');

--THIS CURSOR GETS THE TABLE PARTITIONS THAT ARE ON
DBA_TAB_MODIFCATIONS LIST: cursor c_part is
select u.name table_owner, o.name table_name, o.subname partition_name,m.obj#
from sys.mon_mods$ m, sys.obj$ o, sys.user$ u where o.obj# = m.obj# and o.owner# = u.user# and o.obj# in (select obj# from sys.tabpart$ union select obj# from sys.tabsubpart$)
and u.name not in ('SYS','SYSTEM','OUTLN','PERFSTAT');

begin

        for v_tab in c_tab loop

--DO THE ANALYZE HERE:

		v_tabname := v_tab.table_name;
		v_own := v_tab.table_owner;

		v_sql := 'analyze table
'||'"'||v_tab.table_owner||'"'||
		'.'||'"'||v_tab.table_name||'"'||' estimate
statistics
		sample 35 percent for table';

		execute immediate v_sql;

--REMOVE THE TABLES FROM DBA_TAB_MODIFICATIONS HERE:

		delete sys.mon_mods$
		where obj# = v_tab.obj#;

		commit;

	end loop;

end;

begin

        for v_part in c_part loop

		v_tabname := v_part.table_name;
		v_own := v_part.table_owner;
		v_partname := v_part.partition_name;

--DO THE ANALYZE HERE:
                        v_sql := 'analyze table '||                 

'"'||v_part.table_owner||'"'||'.'||'"'||v_part.table_name||'"'||

                        ' partition('||v_part.partition_name||') estimate statistics

                        sample 35 percent for table';

                        execute immediate v_sql;

--REMOVE THE PARTITIONS FROM DBA_TAB_MODIFICATIONS
HERE:

			delete sys.mon_mods$
			where obj# = v_part.obj#;

			commit;

		end loop;

end;

good luck,

Jack Silvey

---

Do You Yahoo!?
Yahoo! Health - your guide to health and wellness http://health.yahoo.com
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Jack Silvey
  INET: jack_silvey_at_yahoo.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 08 2002 - 16:43:29 CDT