No question at this time
The DBA-Village forum
as RSS feed
Site StatisticsEver registered users | 48600 | Total active users | 1396 | Act. users last 24h | 1 | Act. users last hour | 0 | Registered user hits last week | 51 | Registered user hits last month | 385 |
|
Go up
Scheduled jobs
Message |
Score |
Author |
Date |
Hi
I am having a problem with a schedule job I ...... |
|
James Alcroft |
Feb 24, 2021, 10:16 |
Hello James,
-1- please forgive me as of course...... |
     |
Bruno Vroman |
Feb 24, 2021, 18:07 |
Thanks for this Bruno
Meant to reply yesterday ...... |
|
James Alcroft |
Feb 26, 2021, 10:47 |
Hi James,
indeed, <<<i>though, this very same s...... |
|
Bruno Vroman |
Mar 05, 2021, 14:47 |
Subject: |
Scheduled jobs |
Author: |
James Alcroft, United Kingdom |
Date: |
Feb 24, 2021, 10:16, 57 days ago |
Os info: |
Windows 2016 |
Oracle info: |
Oracle 12.2 |
Message: |
Hi
I am having a problem with a schedule job I have created to gather table stats every Sunday. This is scripted and I have run the same script on 14 databases, but two of them refuse to run - well, they run once, the next run date is the following Sunday as expected, but it never runs again. If I delete the job and recreate it, again it runs the first Sunday, but that is it. My script (shortened, there is more to it)
Initially as SYS, set some configuration and ACL
alter system set smtp_out_server='smtp_server_ip_address' scope=both;
@?\rdbms\admin\utlmail.sql
@?\rdbms\admin\prvtmail.plb
GRANT EXECUTE ON UTL_MAIL TO PUBLIC;
EXEC DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('send_mail.xml', 'Allow mail to be send', 'Job_owner', TRUE, 'connect');
----Grant the connect and resource privilege as below
EXEC DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('send_mail.xml', 'Job_owner', TRUE, 'connect');
EXEC DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('send_mail.xml', 'Job_owner', TRUE, 'resolve');
EXEC DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('send_mail.xml', '*', 25);
*/
grant create job to Job_owner;
As my job owner, the job script is:
BEGIN
DBMS_SCHEDULER.CREATE_JOB (JOB_NAME => 'ORACLE_STATS',
JOB_TYPE => 'PLSQL_BLOCK',
JOB_ACTION =>
'DECLARE
sDatabase VARCHAR2(20);
sStart VARCHAR2(30);
sEnd VARCHAR2(30);
sAttach VARCHAR2(10000);
BEGIN
SELECT LOWER(NAME) INTO sDatabase FROM V$DATABASE;
sAttach := ''Stats process started at ''||TO_CHAR(SYSTIMESTAMP, ''dd-MON-yyyy hh24:mi:ss'')||UTL_TCP.CRLF;
FOR cur IN (SELECT owner
FROM dba_tables
WHERE table_name in (''XXX'', ''YYY''))
LOOP
SELECT TO_CHAR(SYSTIMESTAMP,''HH24:MI:SS'') INTO sStart FROM DUAL;
DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME => cur.owner, ESTIMATE_PERCENT => 20, BLOCK_SAMPLE => true, METHOD_OPT => ''for all columns size auto'', DEGREE => 4, GRANULARITY => ''ALL'', CASCADE => true);
SELECT TO_CHAR(SYSTIMESTAMP,''HH24:MI:SS'') INTO sEnd FROM DUAL;
sAttach := sAttach||cur.owner||'' - started: ''||sStart||'' finished: ''||sEnd||UTL_TCP.CRLF;
END LOOP;
sAttach := sAttach||''Stats process completed at ''||TO_CHAR(SYSTIMESTAMP, ''dd-MON-yyyy hh24:mi:ss'')||UTL_TCP.CRLF;
UTL_MAIL.SEND_ATTACH_VARCHAR2(SENDER => ''do_not_reply@email.com'',
RECIPIENTS => ''jalcroft@email.com'',
SUBJECT => ''Oracle Stats : ''||sDatabase,
MESSAGE => ''Stats log for database ''||sDatabase,
ATTACHMENT => sAttach,
ATT_FILENAME => sDatabase||''_stats.log'');
END;',
REPEAT_INTERVAL => 'FREQ=WEEKLY;BYTIME=030000;BYDAY=SUN',
COMMENTS => 'Gather schema table stats');
DBMS_SCHEDULER.ENABLE(NAME => 'ORACLE_STATS');
END;
/
As I have said, I have run this very same script against 12 other database, and they are all working fine. Can anyone tell me what I can look at on the two problematic databases so try and get these working.
Many thanks
James |
Goto: |
Reply - Top of page |
If you think this item violates copyrights, please click here
Subject: |
Re: Scheduled jobs |
Author: |
Bruno Vroman, Belgium |
Date: |
Feb 24, 2021, 18:07, 57 days ago |
Score: |
       |
Message: |
Hello James,
-1- please forgive me as of course I start with not a reply to your question, and I know how annoying (to stay polite) it is to receive always these "why?" and the "good advice", in the forums, but...
... but I cannot avoid falling in this practice, forgive me... I hope that you have good reasons but it is still better to raise a friendly flag ;-)
It seems that you want to define a job to run 1 / week to refresh the stats for N schemas, with various parameters hardcoded, like estimate_percent => 20 for example
At first sight (so I acknowledge that you have knowledge that we don't have) this really seems to be sad.
I'm so happy that Oracle computes itself the sample size for years!
And I'm also very happy that the statistics are maintained automatically, I never have to worry about this (except in some cases with applications doing things like "load large amount of data in TBL, process data, purge TBL" that goes really bad with standard statistics...)
And there are several possibilities to set up "PREFERENCES" for parameters for the automatic job if you don't like the defaults...
So I assume that you know what you do ((Maybe you might have a look at DBA_TAB_MODIFICATIONS)) and I stop this point.
-2- coming back to the question:
-2A- for easier tests: try with a mini-job: adding "AND 1=2" in your where clause will result in "no schema to work on", so you might run this each hour, in one of the databases "OK" and in one of the databases "KO"
If all is fine with hourly, try to make it daily, and lastly weekly. At some point it should be KO in the "KO database" otherwise the cause is not the job scheduling but its content?? ((crazy idea: maybe the job takes more than 1 week to complete?))
-2B- there must be something different between an OK and a KO database... Maybe job is auto-dropped in KO database?
For info, here is the way I define jobs (that work like a charm ;-) DBMS_SCHEDULER.CREATE_JOB(
job_name => 'NNN'
, job_type => 'PLSQL_BLOCK'
, job_action => 'BEGIN ...END;'
, repeat_interval => '...'
, enabled => TRUE
, auto_drop => FALSE
, comments => '...'
); Best regards,
Bruno. |
Your rating?: |
This reply is Good Excellent |
Goto: |
Reply - Top of page |
If you think this item violates copyrights, please click here
Subject: |
Re: Scheduled jobs |
Author: |
James Alcroft, United Kingdom |
Date: |
Feb 26, 2021, 10:47, 55 days ago |
Message: |
Thanks for this Bruno
Meant to reply yesterday and but got side tracked.
Firstly, yes I know the stats options are probably not the best, but with the size of our database they work for us and keep the customer happy.
I will try your test options to see if i can get it working, better than waiting a week. I have never set the AUTO_DROP to anything but default. Bottom line is, though, this very same script works perfectly OK on 12 other databases.
James |
Your rating?: |
This reply is Good Excellent |
Goto: |
Reply - Top of page |
If you think this item violates copyrights, please click here
Subject: |
Re: Scheduled jobs |
Author: |
Bruno Vroman, Belgium |
Date: |
Mar 05, 2021, 14:47, 48 days ago |
Message: |
Hi James,
indeed, <<though, this very same script works perfectly OK on 12 other databases.>> can seem puzzling, but there might be different preferences defined, so "defaults" are maybe not the same...
Anyway, any feedback?
Bruno
|
Your rating?: |
This reply is Good Excellent |
Goto: |
Reply - Top of page |
If you think this item violates copyrights, please click here
|