Forum Stats

  • 3,853,303 Users
  • 2,264,203 Discussions
  • 7,905,317 Comments

Discussions

use scheduler or use java thread?

659232
659232 Member Posts: 15
edited Sep 10, 2008 8:10PM in Scheduler
Hi I am new to Oracle database, please forgive me if this question is naive.

I have a java thread which purge records in a oracle table every 30 seconds. The thread calls a prepared statement which is like "delete from MyTable where status=99"

can I use DB's scheduled job to do the same thing? I can create a DB schedule which calls a job in plsql every 30 seconds.
I am thinking that by doing this I can save on DB connections.

Which one is better in terms of performance and memory usage?
What if there a lots of records with status=99 every 30 seconds. What if there is just a few.
Please advise.

-G.
Tagged:

Answers

  • Rnr-Oracle
    Rnr-Oracle Member Posts: 1,269 Employee
    edited Sep 10, 2008 8:10PM
    Hi,

    For any repeating job which does a purely database task, using dbms_scheduler is the best way to do it and is Oracle's recommended method. You can do this simply as follows. Alternatively you can set this up using the Enterprise Manager Interface (DB Console or Grid Console).
      -- allow your user to create a job
    grant create job to scott;
    connect scott/tiger

    begin
    dbms_scheduler.create_job('my_job',
    job_type=>'plsql_block',
    job_action=>'delete from MyTable where status=99;',
    repeat_interval=>'freq=secondly;bysecond=0,30',
    comments => 'purge mytable every 30 seconds',
    enabled=>true);
    end;
    /
    There will be a significantly reduced memory and performance overhead of doing it this way since the Oracle server will be doing everything internally on the server.

    See the dbms_scheduler documentation for more information. You can view the runs using the all_scheduler_job_run_details view.

    Hope this helps,
    Ravi.
This discussion has been closed.