Re: analyze script

Norman Dunbar <Norman.Dunbar_at_lfs.co.uk> wrote in message news:<E2F6A70FE45242488C865C3BC1245DA7038CB708_at_lnewton.leeds.lfs.co.uk>...
> 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.
>

Hegyvari, Besides paralleling the task as Norman did, your other option in case you have not configured paraller query or your system is resouce constrained so that parallel execution is not desirable then would be to provide a sample size such that a full compute would not be attempted on any large, heavily updated query. Sampling should reduce the time to analyze.

Norman made a very good point on not analyzing unless necessary.

HTH -- Mark D Powell -- Received on Mon Apr 14 2003 - 10:52:38 CDT