Scheduler
Alcune nozioni base dello Scheduler di Oracle 11gR2.
La seguente query individua lo stato di tutti i JOB schedulati (si parla di JOB TABLE )
SQL> select owner, job_name, state
from dba_scheduler_jobs
where job_name in ('PURGE_LOG','GATHER_STATS_JOB');
Il processo di background che gestisce e controlla i job schedulati è CJQ0 e si chiama "job coordinator". Carica i dati del job nella memory cache per migliorare le performance quando il job è in esecuzione. A questo punto il job coordinator passa tutte le informazioni a processo job-slave che per ogni job raccoglie i relativi metadati e avvia una sessione dell'owner del job. Una volta eseguito il job il porcesso slave esegue il commit della transazione e chiude la sessione. Successivamente aggiorna lo stato della job table in "completed" come visibile dalla vista dba_scheduler_job_log.
Le viste contenente informazioni relative allo scheduler e agli oggetti associati possono essere recuperati dalla seguente query.
SQL> select * from dba_views
where view_name like 'DBA_SCHEDULER%';
Come si implementa uno Scheduler?
Attraverso Enterprise Manager oppure mediante il package pl/sql chiamato DBMS_SCHEDULER che contiene una serie di procedure che creano e gestiscono oggetti schedulati come:
- jobs
- programs
- schedules: definiscono quando un job può inziare e quante volte può essere ripetuto. Definisce anche quando si apre un oggetto window.
- windows
- job classes
- window groups
- chains o job chain: è una serie di nomi di tasks che si richiamano tra loro e serve per implementare una pianificazione basata sulle dipendenze, in cui un job si avvia in base al risultato di uno o più job precedenti.
Di seguito le procedure DBMS_SCHEDULER:
enable --> abilita un programma, job, chain, window o window group
disable
set_attribute --> cambia un attributo del job, schedule e schedule object
set_attribute_null --> imposta a null gli attributi di qualsiasi tipo di Scheduler object
Scheduler JOB
Scheduliamo un job che inserisce una riga ogni minuto in una tabella in un intervallo di tempo.
SQL> select * from bibi.Contratti;
begin
dbms_scheduler.create_job (
job_name => 'JOB_INSERISCI_CONTRATTI',
job_type => 'PLSQL_BLOCK',
job_action => 'insert into bibi.Contratti values ( 100 , ''Contratto'', sysdate, null, sysdate, 2);',
start_date => TO_TIMESTAMP('2019/05/13 11:20','yyyy/mm/dd hh24:mi:ss'),
repeat_interval => 'FREQ=MINUTELY',
comments => 'Test popolamento tabella',
end_date => TO_TIMESTAMP('2019/05/13 11:30','yyyy/mm/dd hh24:mi:ss')
);
end;
/
Di default il job creato è disabilitato.
begin
dbms_scheduler.enable ( 'JOB_INSERISCI_CONTRATTI');
end;
/
select owner, job_name, state , run_count , enabled
from dba_scheduler_jobs
where job_name in ('JOB_INSERISCI_CONTRATTI');
Di seguito il risultato del job schedulato. Ha effettuato una insert ogni minuto.
Se si vuole eseguire immediatamente il job.
begin
dbms_scheduler.run_job ('JOB_INSERISCI_CONTRATTI',TRUE);
end;
/
Se si vuole modificare un attributo come le date di start e stop usare la procedura SET_ATTRIBUTE.
Questa procedura svolge anche la funzione di abilitare un job o un programma.
begin
dbms_scheduler.set_attribute(
name => 'JOB_INSERISCI_CONTRATTI',
attribute => 'START_DATE',
value => TO_TIMESTAMP('2019/05/13 11:40','yyyy/mm/dd hh24:mi:ss'),
);
end;
/
begin
dbms_scheduler.set_attribute(
name => 'JOB_INSERISCI_CONTRATTI',
attribute => 'END_DATE',
value => TO_TIMESTAMP('2019/05/13 11:45','yyyy/mm/dd hh24:mi:ss')
);
end;
/
Se si vuole cancellare un job.
begin
dbms_scheduler.drop_job (
job_name =>'JOB_INSERISCI_CONTRATTI',
force =>TRUE);
end;
/
Se si vuole fermare un job usare la procedura STOP_JOB. Oralce esegue il rollback della transazione in corso e quindi solo dei dati non committati. Di conseguenza potrebbero esserci dati inconsistenti.
begin
dbms_scheduler.stop_job (
job_name => 'JOB_INSERISCI_CONTRATTI',
force => TRUE );
end;
/
Dalla seguente query non escono più record relativi al job stoppato.
select owner, job_name, state , run_count , enabled
from dba_scheduler_jobs
where job_name in ('JOB_INSERISCI_CONTRATTI');
Per recuperare le informazioni dell'istanza completa del job
select *
from dba_scheduler_job_run_details
where job_name in ('JOB_INSERISCI_CONTRATTI')order by 1 desc;
Per vedere i log del job
select log_id, trunc(log_date) log_date, owner, job_name, operation
from dba_scheduler_job_log
where job_name in ('JOB_INSERISCI_CONTRATTI')order by 1 desc
Nell'esempio che segue invece è stato schedulato l'esecuzione di una procedura pl/sql ogni minuto impostando una data di start e uno di stop.
begin
dbms_scheduler.create_job (
job_name => 'JOB_INSERISCI_CONTRATTI_2',
job_type => 'PLSQL_BLOCK',
job_action => 'begin INSERISCIRIGHEINTABELLA(35,38); end;',
start_date => TO_TIMESTAMP('2019/05/13 12:40','yyyy/mm/dd hh24:mi:ss'),
repeat_interval => 'FREQ=MINUTELY',
comments => 'Test popolamento tabella',
end_date => TO_TIMESTAMP('2019/05/13 12:42','yyyy/mm/dd hh24:mi:ss')
);
end;
/
Per i dettagli della procedura INSERISCIRIGHEINTABELLA() cliccare su questo post.
Nell'esempio precedente i parametri passati al blocco pl/sql sono fissi ma se si vuole cambiarli al tempo di esecuzione del job usare la procedura SET_JOB_ARGUMENT_VALUE.
begin
dbms_scheduler.create_job (
job_name => 'JOB_INSERISCI_CONTRATTI_3',
job_type => 'STORED_PROCEDURE',
job_action => 'INSERISCIRIGHEINTABELLA',
number_of_arguments => 2,
start_date => TO_TIMESTAMP('2019/05/16 10:45','yyyy/mm/dd hh24:mi:ss'),
repeat_interval => 'FREQ=MINUTELY',
comments => 'Test popolamento tabella passando argomenti',
end_date => TO_TIMESTAMP('2019/05/16 10:48','yyyy/mm/dd hh24:mi:ss')
);
end;
/
begin
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
job_name => 'JOB_INSERISCI_CONTRATTI_3',
argument_position => 1,
argument_value => 203
);
end;
/
begin
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
job_name => 'JOB_INSERISCI_CONTRATTI_3',
argument_position => 2,
argument_value => 206
);
end;
/
begin
dbms_scheduler.enable ('JOB_INSERISCI_CONTRATTI_3');
end;
/
Scheduler Program
Definiamo un programma che calcola le statistiche di un qualsiasi schema, che verrà passato come argomento, e che può essere richiamato da un JOB.
begin
dbms_scheduler.create_program (
program_name => 'AGGIORNAMENRO_STATISTICHE',
program_type => 'STORED_PROCEDURE',
program_action => 'DBMS_STATS.GATHER_SCHEMA_STATS',
number_of_arguments => 1,
comments => 'Raccolta statistiche per uno schema');
end;
/
begin
dbms_scheduler.define_program_argument(
program_name => 'AGGIORNAMENRO_STATISTICHE',
argument_position => 1,
argument_type => 'VARCHAR2'
);
end;
/
begin
DBMS_SCHEDULER.enable ('AGGIORNAMENRO_STATISTICHE');
end;
/
Query di verifica:
SQL> select * from dba_scheduler_programs
where program_name ='AGGIORNAMENRO_STATISTICHE';
begin
DBMS_SCHEDULER.drop_program (
program_name => 'AGGIORNAMENRO_STATISTICHE',
force => TRUE);
end;
/
I programmi vengono chiamati dagli oggetti Job; pertanto il programma può essere richiamato dallo stesso job per ogni schema, cambiando semplicemente il valore dell'argomento passato al Job.
begin
dbms_scheduler.create_job (
job_name => 'JOB_AGG_STAT',
program_name => 'AGGIORNAMENRO_STATISTICHE',
repeat_interval => 'FREQ=DAILY;BYHOUR=13'
);
end;
/
La seguente query individua lo stato di tutti i JOB schedulati (si parla di JOB TABLE )
SQL> select owner, job_name, state
from dba_scheduler_jobs
where job_name in ('PURGE_LOG','GATHER_STATS_JOB');
Il processo di background che gestisce e controlla i job schedulati è CJQ0 e si chiama "job coordinator". Carica i dati del job nella memory cache per migliorare le performance quando il job è in esecuzione. A questo punto il job coordinator passa tutte le informazioni a processo job-slave che per ogni job raccoglie i relativi metadati e avvia una sessione dell'owner del job. Una volta eseguito il job il porcesso slave esegue il commit della transazione e chiude la sessione. Successivamente aggiorna lo stato della job table in "completed" come visibile dalla vista dba_scheduler_job_log.
Le viste contenente informazioni relative allo scheduler e agli oggetti associati possono essere recuperati dalla seguente query.
SQL> select * from dba_views
where view_name like 'DBA_SCHEDULER%';
Attraverso Enterprise Manager oppure mediante il package pl/sql chiamato DBMS_SCHEDULER che contiene una serie di procedure che creano e gestiscono oggetti schedulati come:
- jobs
- programs
- schedules: definiscono quando un job può inziare e quante volte può essere ripetuto. Definisce anche quando si apre un oggetto window.
- windows
- job classes
- window groups
- chains o job chain: è una serie di nomi di tasks che si richiamano tra loro e serve per implementare una pianificazione basata sulle dipendenze, in cui un job si avvia in base al risultato di uno o più job precedenti.
Di seguito le procedure DBMS_SCHEDULER:
enable --> abilita un programma, job, chain, window o window group
disable
set_attribute --> cambia un attributo del job, schedule e schedule object
set_attribute_null --> imposta a null gli attributi di qualsiasi tipo di Scheduler object
Scheduler JOB
Scheduliamo un job che inserisce una riga ogni minuto in una tabella in un intervallo di tempo.
SQL> select * from bibi.Contratti;
begin
dbms_scheduler.create_job (
job_name => 'JOB_INSERISCI_CONTRATTI',
job_type => 'PLSQL_BLOCK',
job_action => 'insert into bibi.Contratti values ( 100 , ''Contratto'', sysdate, null, sysdate, 2);',
start_date => TO_TIMESTAMP('2019/05/13 11:20','yyyy/mm/dd hh24:mi:ss'),
repeat_interval => 'FREQ=MINUTELY',
comments => 'Test popolamento tabella',
end_date => TO_TIMESTAMP('2019/05/13 11:30','yyyy/mm/dd hh24:mi:ss')
);
end;
/
Di default il job creato è disabilitato.
begin
dbms_scheduler.enable ( 'JOB_INSERISCI_CONTRATTI');
end;
/
select owner, job_name, state , run_count , enabled
from dba_scheduler_jobs
where job_name in ('JOB_INSERISCI_CONTRATTI');
Di seguito il risultato del job schedulato. Ha effettuato una insert ogni minuto.
Se si vuole eseguire immediatamente il job.
begin
dbms_scheduler.run_job ('JOB_INSERISCI_CONTRATTI',TRUE);
end;
/
Se si vuole modificare un attributo come le date di start e stop usare la procedura SET_ATTRIBUTE.
Questa procedura svolge anche la funzione di abilitare un job o un programma.
begin
dbms_scheduler.set_attribute(
name => 'JOB_INSERISCI_CONTRATTI',
attribute => 'START_DATE',
value => TO_TIMESTAMP('2019/05/13 11:40','yyyy/mm/dd hh24:mi:ss'),
);
end;
/
begin
dbms_scheduler.set_attribute(
name => 'JOB_INSERISCI_CONTRATTI',
attribute => 'END_DATE',
value => TO_TIMESTAMP('2019/05/13 11:45','yyyy/mm/dd hh24:mi:ss')
);
end;
/
Se si vuole cancellare un job.
begin
dbms_scheduler.drop_job (
job_name =>'JOB_INSERISCI_CONTRATTI',
force =>TRUE);
end;
/
Se si vuole fermare un job usare la procedura STOP_JOB. Oralce esegue il rollback della transazione in corso e quindi solo dei dati non committati. Di conseguenza potrebbero esserci dati inconsistenti.
begin
dbms_scheduler.stop_job (
job_name => 'JOB_INSERISCI_CONTRATTI',
force => TRUE );
end;
/
Dalla seguente query non escono più record relativi al job stoppato.
select owner, job_name, state , run_count , enabled
from dba_scheduler_jobs
where job_name in ('JOB_INSERISCI_CONTRATTI');
Per recuperare le informazioni dell'istanza completa del job
select *
from dba_scheduler_job_run_details
where job_name in ('JOB_INSERISCI_CONTRATTI')order by 1 desc;
Per vedere i log del job
select log_id, trunc(log_date) log_date, owner, job_name, operation
from dba_scheduler_job_log
where job_name in ('JOB_INSERISCI_CONTRATTI')order by 1 desc
Nell'esempio che segue invece è stato schedulato l'esecuzione di una procedura pl/sql ogni minuto impostando una data di start e uno di stop.
begin
dbms_scheduler.create_job (
job_name => 'JOB_INSERISCI_CONTRATTI_2',
job_type => 'PLSQL_BLOCK',
job_action => 'begin INSERISCIRIGHEINTABELLA(35,38); end;',
start_date => TO_TIMESTAMP('2019/05/13 12:40','yyyy/mm/dd hh24:mi:ss'),
repeat_interval => 'FREQ=MINUTELY',
comments => 'Test popolamento tabella',
end_date => TO_TIMESTAMP('2019/05/13 12:42','yyyy/mm/dd hh24:mi:ss')
);
end;
/
Per i dettagli della procedura INSERISCIRIGHEINTABELLA() cliccare su questo post.
Nell'esempio precedente i parametri passati al blocco pl/sql sono fissi ma se si vuole cambiarli al tempo di esecuzione del job usare la procedura SET_JOB_ARGUMENT_VALUE.
begin
dbms_scheduler.create_job (
job_name => 'JOB_INSERISCI_CONTRATTI_3',
job_type => 'STORED_PROCEDURE',
job_action => 'INSERISCIRIGHEINTABELLA',
number_of_arguments => 2,
start_date => TO_TIMESTAMP('2019/05/16 10:45','yyyy/mm/dd hh24:mi:ss'),
repeat_interval => 'FREQ=MINUTELY',
comments => 'Test popolamento tabella passando argomenti',
end_date => TO_TIMESTAMP('2019/05/16 10:48','yyyy/mm/dd hh24:mi:ss')
);
end;
/
begin
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
job_name => 'JOB_INSERISCI_CONTRATTI_3',
argument_position => 1,
argument_value => 203
);
end;
/
begin
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
job_name => 'JOB_INSERISCI_CONTRATTI_3',
argument_position => 2,
argument_value => 206
);
end;
/
begin
dbms_scheduler.enable ('JOB_INSERISCI_CONTRATTI_3');
end;
/
Scheduler Program
Definiamo un programma che calcola le statistiche di un qualsiasi schema, che verrà passato come argomento, e che può essere richiamato da un JOB.
begin
dbms_scheduler.create_program (
program_name => 'AGGIORNAMENRO_STATISTICHE',
program_type => 'STORED_PROCEDURE',
program_action => 'DBMS_STATS.GATHER_SCHEMA_STATS',
number_of_arguments => 1,
comments => 'Raccolta statistiche per uno schema');
end;
/
begin
dbms_scheduler.define_program_argument(
program_name => 'AGGIORNAMENRO_STATISTICHE',
argument_position => 1,
argument_type => 'VARCHAR2'
);
end;
/
begin
DBMS_SCHEDULER.enable ('AGGIORNAMENRO_STATISTICHE');
end;
/
Query di verifica:
SQL> select * from dba_scheduler_programs
where program_name ='AGGIORNAMENRO_STATISTICHE';
begin
DBMS_SCHEDULER.drop_program (
program_name => 'AGGIORNAMENRO_STATISTICHE',
force => TRUE);
end;
/
I programmi vengono chiamati dagli oggetti Job; pertanto il programma può essere richiamato dallo stesso job per ogni schema, cambiando semplicemente il valore dell'argomento passato al Job.
begin
dbms_scheduler.create_job (
job_name => 'JOB_AGG_STAT',
program_name => 'AGGIORNAMENRO_STATISTICHE',
repeat_interval => 'FREQ=DAILY;BYHOUR=13'
);
end;
/
Inserire lo schema come argomento del job e quindi del programma.
begin
dbms_scheduler.set_job_argument_value(
job_name => 'JOB_AGG_STAT',
argument_position => 1,
argument_value => 'BIBI'
);
end;
/
begin
dbms_scheduler.set_job_argument_value(
job_name => 'JOB_AGG_STAT',
argument_position => 1,
argument_value => 'GESTIONE_LINK'
);
end;
/
begin
dbms_scheduler.enable (
'JOB_AGG_STAT');
end;
/
Per eseguire il job/program in questo istante.
begin
dbms_scheduler.run_job ('JOB_AGG_STAT',TRUE);
end;
/
Query di verifica:
select * from dba_scheduler_jobs where job_name ='JOB_AGG_STAT';
select * from DBA_SCHEDULER_JOB_ARGS
select * from DBA_SCHEDULER_JOB_LOG where job_name ='JOB_AGG_STAT'
select * from DBA_SCHEDULER_JOB_RUN_DETAILS where job_name ='JOB_AGG_STAT'
