Re: Snapshot refreshes

Don wrote:

>Hello,
>
>When we refresh the snapshots on our Oracle Server (v9.0.1), it slows
>down data access so much that users running queries on the database
>via web apps regularly receive timeout messages.
>
>The refreshes are automated and run every 3 hours using windows task
>scheduler to execute a script on SQLPlus which contains the snapshot
>refresh code.
>
>eg, batch file to run sqlplus...
> sqlplus /nolog @\\[server]\[script].sql
>
>contents of [script].sql (script may contain multiple snapshots)...
> execute dbms_snapshot.refresh([snapshotname]);
>
>If they take 15 minutes to refresh then the users basically can't use
>the system for 15 minutes.
>
>Does anyone know how to increase snapshot refresh speed (I tried
>removing logging) or how to lower the priority of this task so it
>doesn't affect other queries as much.
>
>cheers for any advice you can give.
>
>

There are a lot of ways to increase speed. The first one would be to stop using Beta software
in production and upgrade to 9.2.0.4. The second would be to run Explain Plan on the SQL
statements being used in the refresh and determine whether appropriate indexes exist and
whether they are being used. Likely you are not running DBMS_STATS regularly so there are
no statistics being generated for the optimizer.

Likely the biggest problem will come down to elementary SQL statement tuning.

Finally I'd get Windows whatever that is out of the picture and use DBMS_JOB for job
scheduling.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)