Re: database slow after upgrade. advice ?

From: Jim Strzyzewski <jimski_at_pacificnet.net>
Date: Sun, 28 Feb 1999 14:40:00 -0800
Message-ID: <7bcgan$ehb$1@remarQ.com>

Make sure you analyze your schema at regular intervals if you plan to use the cost based optimizer. Cost generally is more efficient because it is based on actual data in the database. The interval you use to analyze should be based on the dynamics of your own database. A warehouse set to read-only needs only be analyzed AFTER loading the latest batch of data. A highly transactional system might be analyzed once a day during quiet hours. I have included a SQL script which should be useful to the masses which will generate the ANALYZE statements for each TABLE and INDEX in a SCHEMA.

!ana.sql ------------------------
Script to create ANALYZE TABLE --
statements for a given schema ---
From SQLPlus: @!ana -------------
From SQLPlus: @tmp --------------
jimski_at_pacificnet.net -----------

SET HEADING off
SET FEEDBACK off
SET VERIFY off
SET PAGESIZE 0 SPOOL tmp.sql

SELECT

       'ANALYZE TABLE '||
       INITCAP(owner)||'.'||RPAD(INITCAP(table_name),20)||
       ' ESTIMATE STATISTICS SAMPLE 20 PERCENT;'

FROM sys.dba_tables
WHERE owner = UPPER('&schema_owner');

SELECT

       'ANALYZE INDEX '||
       INITCAP(table_owner)||'.'||RPAD(INITCAP(index_name),20)||
       ' COMPUTE STATISTICS;'

FROM sys.dba_indexes
WHERE owner = UPPER('&schema_owner')
ORDER BY table_name, index_name;
--||'--'||LOWER(table_name)

SPOOL off

SET HEADING on
SET FEEDBACK on
SET VERIFY on
SET PAGESIZE 60

Andrew Gilkes wrote in message ...
>I would set the optimizer_mode back to rule until you have had the chance
to
>review the tunning of your application using the cost based optimizer.
>
>Our experience has been that switching over to the cost based optimizer
>required a lot of code to be retuned even the get back to the level of
>performance prior the switching optimization methods.
>
>If you application worked ok before, leave it using the rule optimiser.
>srallapally_at_netscape.net wrote in message
><7b2hpn$t62$1_at_nnrp1.dejanews.com>...
>>Hello,
>>I recently upgraded my database from 7.2.3 t 7.3.3.0. The upgrade went
>okay,
>>but the users have been complaining that queries which used to run fast
>>prior to the upgrade run much slower !! the only change that i made before
>>starting the instance and opening the database (after the upgrade) was to
>>change the optimizer_mode to choose instead of rule.
>>Any suggestions anyone ?
>>
>>-----------== Posted via Deja News, The Discussion Network ==----------
>>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>
>
Received on Sun Feb 28 1999 - 16:40:00 CST