alter table X monitoring

From: <mokat67_at_hotmail.com>
Date: Sat, 22 Feb 2003 13:43:57 GMT
Message-ID: <3e57793b.8575490@news.hccnet.nl>

I 'm using the followoing command to analyse the tables;

1. CREATE OR REPLACE PROCEDURE ANALYZE_XXX AS t_owner varchar2(30) := 'XXX'; CURSOR obj_cur IS select 'ANALYZE TABLE ' ||owner||'.'||table_name||' COMPUTE STATISTICS FOR ALL INDEXES' CMDSQL FROM ALL_TABLES WHERE owner = t_owner UNION select 'ANALYZE TABLE ' ||owner||'.'||table_name||' COMPUTE STATISTICS ' CMDSQL FROM ALL_TABLES WHERE owner = t_owner ; drop_cursor INTEGER; out_str VARCHAR2(1000); BEGIN FOR obj_rec IN obj_cur LOOP drop_cursor := DBMS_SQL.OPEN_CURSOR; out_str := obj_rec.cmdsql; DBMS_SQL.PARSE (drop_cursor, out_str, DBMS_SQL.NATIVE); DBMS_SQL.CLOSE_CURSOR (drop_cursor); END LOOP; END;

Now I want to use the following command

2) dbms_stats.gather_schema_stats('XXX', cascade => true,options => 'GATHER STALE'); I read that you have to : alter table X monitoring; for all the tables you want to analyze.

Questions:
-Is there any overhead to put monitoring on all tables? (performance
issues)?
-The number of tables are changing ..... so i have to find out which
one are new? Which view?
- Are there advantages / disadvantages for the 2nd methode compare
with the first?
- Is method (2) making use the parameter: " optimizer_mode =
FIRST_ROWS " in the ini file?
- I want to get rid on method (1) because it is causing heavy log
switching everytime I run it. So is this a good reason for changing to methode(2)?

Thanks   Received on Sat Feb 22 2003 - 07:43:57 CST