Re: analyze script

If one is going to use GATHER_SCHEMA_STATS, then one should look at only gather stats on stale, or new objects. This saves on tons of work and can leave your system resources for more important things.

HTH,
Brian

Norman Dunbar wrote:
>
> Morning Hegyvari,
>
> if you are runniing on Unix then set up a cron job as follows :
>
> 00 06 * * 2 su - unix_user -c "sqlplus oracle_user/password
> @scripts/analyse_schema QASL_REG"
>
> for which you will need a script called analyse_schema.sql, located in
> the scripts directory of the appropriate unix_user :
>
> BEGIN
> DBMS_STATS.GATHER_SCHEMA_STATS (
> ownname => '&1',
> DEGREE => 4,
> CASCADE => TRUE);
> END;
> /
>
> The first parameter on the command line, QASL_REG above, is the schema
> name which is to be analysed. You could, if you so desired, set this up
> as a DBMS_JOB and let the database run it periodically.
>
> Of course, analysing (with or without a 'Z') is a fairly intensive
> operation and should really only be carried out whenever it needs to be.
> Just analysing for the sake of it causes a lot of work on the database
> and may not return any decent results in the end. See some of Howards
> previous posts on Google Groups for valid reasons.
>
> Regards,
> Norman.
>
> -------------------------------------
> Norman Dunbar
> Database/Unix administrator
> Lynx Financial Systems Ltd.
> mailto:Norman.Dunbar_at_LFS.co.uk
> Tel: 0113 289 6265
> Fax: 0113 289 3146
> URL: http://www.Lynx-FS.com
> -------------------------------------
Received on Wed Apr 16 2003 - 13:52:58 CDT