Re: alter table X monitoring

comments embedded

On Sat, 22 Feb 2003 13:43:57 GMT, mokat67_at_hotmail.com wrote:

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

Incorrect. If you want to use the GATHER STALE option, sure, but the default is to analyze everything.
>Questions:
>-Is there any overhead to put monitoring on all tables? (performance
>issues)?

Depends on how frequently your tables change.
>-The number of tables are changing ..... so i have to find out which
>one are new? Which view?

dba_objects has a creation time and a last_ddl_time But if you are using gather_schema_stats you don't have to worry
>- Are there advantages / disadvantages for the 2nd methode compare
>with the first?

ANALYZE is being deprecated and provided for backward compatibility only.
dbms_stats provides more accurate statistics and can use multiple processors.
>- Is method (2) making use the parameter: " optimizer_mode =
>FIRST_ROWS " in the ini file?

Doesn't matter, you can use first_rows with both ANALYZE and dbms_stats
>- 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)?

The best reason is that the ANALYZE command is fraught with bugs, and Oracle decided to depecrate it.

>
>Thanks
>
>

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Sat Feb 22 2003 - 12:41:00 CST