Was testing on 8.1.7.2, Sun Solaris 7. Need to
revisit, though. Couldn't get our partition fact table
to analyze in parallel.
- "Rodrigues, Bryan" <BRodrigues_at_elcom.com> wrote:
> 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
> 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).
Do You Yahoo!?
Yahoo! Shopping - Mother's Day is May 12th!
http://shopping.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 Thu May 09 2002 - 10:23:28 CDT