No question at this time
DBA Top 10
1 M. Cadot 9500
2 B. Vroman 7200
3 A. Kavsek 4600
4 P. Wisse 2000
5 J. Péran 1700
6 J. Schnackenberg 1200
7 T. Boles 800
8 D. Johnson 600
9 D. Dave 400
10 S. Spb 300
About
DBA-Village
Advertise here
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48624
Total active users1393
Act. users last 24h1
Act. users last hour0
Registered user hits last week48
Registered user hits last month117
Go up

Scheduled jobs
Next thread: Oracle rac internal steps
Prev thread: Restore

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...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts 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

Follow up by mail Click here

Subject: Scheduled jobs
Author: James Alcroft, United Kingdom
Date: Feb 24, 2021, 10:16, 162 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, 161 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
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, 160 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, 152 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