analyze script

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Mon, 14 Apr 2003 08:48:29 +0100
Message-ID: <E2F6A70FE45242488C865C3BC1245DA7038CB708@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.

---

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 Mon Apr 14 2003 - 02:48:29 CDT