Re: alter table X monitoring

On Sun, 23 Feb 2003 11:00:01 GMT, mokat67_at_hotmail.com wrote:

>Thanks for your response Sybrand
>
>I have got a few more questions:
>
>- exec dbms_stats.gather_schema_stats('XXX', cascade => true);
>Does this command exactly the same as the one I show in the previous
>decribed method (1)?
>- Under which user should I run this command (schema owner or the
>sytem user (as SYSDBA) )?
>-I thought that some results were written to the table
>DBA_TAB_MODIFICATIONS? Is this true because no records can be found.
>- I did not understand yor answer about the parameter optimizer_mode
>FIRST_ROWS " in the ini file. Does it make use of this parameter?
>
>Thanks

1 it will analyze *all* tables in the schema, instead in the stale ones only
2 the user doesn't matter as long as you have analyze privilege on the table, so you don't really need to be a DBA for that let alone SYSDBA. I know most shops don't setup users for this purpose. You get the idea probably
3 That I don't know
4 The CBO either operates in first_rows mode or in all_rows mode. In Pl/SQL it operates in all_rows mode always, regardless of the init.ora setting. Whether the statistiscs have been gathered using ANALYZE or dbms_stats doesn't matter. The default is ALL_ROWS. The default applies when the parameter is CHOOSE and your tables have statistics.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Sun Feb 23 2003 - 07:55:12 CST