Oracle-L: RE: how to use analyze with MONITORING and dba_tab

From: Rodrigues, Bryan <BRodrigues_at_elcom.com>
Date: Thu, 09 May 2002 05:33:24 -0800
Message-ID: <F001.0045C8B4.20020509053324@fatcity.com>

What version of Oracle and what platform are you using?

Bryan

-----Original Message-----

Sent: Wednesday, May 08, 2002 5:43 PM
To: Multiple recipients of list ORACLE-L

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).
--

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

Author: Rodrigues, Bryan
INET: BRodrigues_at_elcom.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

RE: how to use analyze with MONITORING and dba_tab_modifications