Re: Snapshot refreshes

Jack Wang wrote:

>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)
>>
>>
>>

If by exp you mean the exp.exe export utility the only way you could do it with DBMS_JOB
is with JSQL. Alternatively go to http://asktom.oracle.com and look at Tom's numerous
suggestions for running SQL*Loader from a procedure.

My comments above do not relate to exp. But generally I have come to dislike and distrust
scheduling that is run outside the database such as cron jobs, etc. If the job can be done inside
the database the results are always better in that you can perform all error reporting inside the
database which gives you the ability to query and report, gives you the ability to develop without
involving SysAdmins and having to learn secondary products and scripting languages. Plus, and
it is a big plus, it never tries to run when the database is down.

-- 
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)