Re: Snapshot refreshes

Daniel,

Could you share your experience in scheduling schema exp job via dbms_job. I had to use Windows batch script.

Thanks.
- Jack

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1066938877.956663_at_yasure...
> 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)
>
Received on Thu Oct 23 2003 - 22:07:41 CDT