OraFAQ Forum: Server Administration » DBMS

Home » RDBMS Server » Server Administration » DBMS_JOB Broken = Y

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

DBMS_JOB Broken = Y [message #213927]

Fri, 12 January 2007 15:41

faiz_hyd
Messages: 294
Registered: February 2006

Senior Member

hi,

We have DBMS schedule job for stats , it ran till 1/9 then i noticed since 2 days it failed, in USER_JOBS it showed Broken =Y
here is the code

DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
    ( job       => X 
     ,what      => '/* job_name: JOB_DAILY_GATHER_STATS */BEGIN dbms_stats.gather_schema_stats(ownname => ''SCHEMA_NAME'', estimate_percent => NULL, cascade => TRUE); END;'
     ,next_date => to_date('12/01/2007 23:00:00','dd/mm/yyyy hh24:mi:ss')
     ,interval  => 'TRUNC(SYSDATE + 1, ''MI'')'
     ,no_parse  => TRUE
    );
  SYS.DBMS_JOB.BROKEN
   (job    => X,
    broken => TRUE);
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
END;
/

commit;

Again i submitted it to run for 1/12 night let's c,
but why did it failed where can i find the reason.

i don't understang why this line is there here, in other region TEST and DEV this line is not there

SYS.DBMS_JOB.BROKEN
   (job    => X,
    broken => TRUE);
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));

Thanks

Report message to a moderator

Re: DBMS_JOB Broken = Y [message #213935 is a reply to message #213927]

Fri, 12 January 2007 18:13

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

select view_name from dba_views where view_name like '%JOB%';

The results might give you some clue as to which view to query.

Report message to a moderator

Re: DBMS_JOB Broken = Y [message #214465 is a reply to message #213935]

Tue, 16 January 2007 09:39

faiz_hyd
Messages: 294
Registered: February 2006

Senior Member

Thanks for follow up,
i have run the select statement it gives 4 views

dba_jobs
user_jobs
dba_jobs_running
ku$_job_view

Still not able to find the reason for the failure , i schedule the job to run so for the first time after resubmitting it works and next day it doesn't, why is it getting Broken, any clues or area where i can look for this.

Thanks

Report message to a moderator

Re: DBMS_JOB Broken = Y [message #215503 is a reply to message #214465]

Mon, 22 January 2007 11:01

faiz_hyd
Messages: 294
Registered: February 2006

Senior Member

Hi,
Still struggling on this issue,
Once the job is completed it starts again even though we gave
next_Date as trunc (sysdate+1,'MI') where
as the same code is working fine in other regions like DEV and Test it fires every day at 12 a.m morning but i don't no why
it is behaving strange in PRODUCTION.

Please guide me more on this.

Thanks

Report message to a moderator

Re: DBMS_JOB Broken = Y [message #215509 is a reply to message #215503]

Mon, 22 January 2007 11:20

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Is the job still getting broke? If so, check the alert log for any errors.

Report message to a moderator

Re: DBMS_JOB Broken = Y [message #215514 is a reply to message #215509]

Mon, 22 January 2007 11:36

faiz_hyd
Messages: 294
Registered: February 2006

Senior Member

Thanks Ebrian for follow up,

When i first noticed 1 week back the job was getting BROKEN,and then the Log file showed this, but now it is not Broken but executing once it completes i mean not on 12.00.00 am every night as the interval specified, any how the log for last week is this

ORA-01555 caused by SQL statement below (Query Duration=2500 sec, SCN: 0x0000.5e9ad4df):
Sun Jan 14 03:53:41 2007
select  /*+ cursor_sharing_exact dynamic_sampling(0) no_monitoring*/  count(*),sum(vsize("IMG_ID")),substrb(dump(min("IMG_ID"),16,0,32),1,120),substrb(dump(max("IMG_ID"),16,0,32),1,120),count("IMG_CRE_TIME"),count(distinct "IMG_CRE_TIME"),substrb(dump(min("IMG_CRE_TIME"),16,0,32),1,120),substrb(dump(max("IMG_CRE_TIME"),16,0,32),1,120),count("IMG_HGHT_NB"),count(distinct "IMG_HGHT_NB"),sum(vsize("IMG_HGHT_NB")),substrb(dump(min("IMG_HGHT_NB"),16,0,32),1,120),substrb(dump(max("IMG_HGHT_NB"),16,0,32),1,120),count("IMG_WDTH_NB"),count(distinct "IMG_WDTH_NB"),sum(vsize("IMG_WDTH_NB")),substrb(dump(min("IMG_WDTH_NB"),16,0,32),1,120),substrb(dump(max("IMG_WDTH_NB"),16,0,32),1,120),count("IMG_FMT_TX"),count(distinct "IMG_FMT_TX"),sum(vsize("IMG_FMT_TX")),substrb(dump(min("IMG_FMT_TX"),16,0,32),1,120),substrb(dump(max("IMG_FMT_TX"),16,0,32),1,120),count("IMG_SIDE_CD"),count(distinct "IMG_SIDE_CD"),substrb(dump(min("IMG_SIDE_CD"),16,0,32),1,120),substrb(dump(max("IMG_SIDE_CD"),16,0,32),1,120),count("IMG_SZ_NB"),count(d
Sun Jan 14 03:53:41 2007
Errors in file /u01/app/oracle/admin/neor1p1/bdump/neor1p1_j001_20576.trc:
ORA-12012: error on auto execute of job 741
ORA-01555: snapshot too old: rollback segment number 1 with name "_SYSSMU1$" too small
ORA-06512: at "SYS.DBMS_STATS", line 9643
ORA-06512: at "SYS.DBMS_STATS", line 10137
ORA-06512: at "SYS.DBMS_STATS", line 10324
ORA-06512: at "SYS.DBMS_STATS", line 10378
ORA-06512: at "SYS.DBMS_STATS", line 10355
ORA-06512: at line 1
Sun Jan 14 03:58:47 2007

Thanks again

Report message to a moderator

Re: DBMS_JOB Broken = Y [message #215516 is a reply to message #215514]

Mon, 22 January 2007 11:43

ebrian
Messages: 2794
Registered: April 2006

Senior Member

I guess the reason

SYS.DBMS_JOB.BROKEN
(job => X,
broken => TRUE);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));

is included is so the job doesn't run immediately after being submitted, but this can be controlled with the NEXT_DATE parameter.

Try to re-run the code with the COMMIT inside the procedure and an appropriate NEXT_DATE parameter:

DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
    ( job       => X 
     ,what      => '/* job_name: JOB_DAILY_GATHER_STATS */BEGIN dbms_stats.gather_schema_stats(ownname => ''SCHEMA_NAME'', estimate_percent => NULL, cascade => TRUE); END;'
     ,next_date => to_date('12/01/2007 23:00:00','dd/mm/yyyy hh24:mi:ss')
     ,interval  => 'TRUNC(SYSDATE + 1, ''MI'')'
     ,no_parse  => TRUE
    );
COMMIT;
END;
/

Report message to a moderator

Re: DBMS_JOB Broken = Y [message #215708 is a reply to message #215516]

Tue, 23 January 2007 10:39

faiz_hyd
Messages: 294
Registered: February 2006

Senior Member

Thanks for follow up,

I resubmitted the job with the changed code to run at 1/23/2007 12:00:01 a.m and it ran and as per interval it should schedule it for 1/24/2007 12:00:00 am next morning but early morning i noticed the next date is 1/23/2007 11:55:00 AM which i don't want so i changed and altered the job manually to submit again for 1/24 morning 12.
why is it not following next interval, any Bug or any thing do with Day light savings time no idea ..
any clues please.

Thanks

Report message to a moderator

Re: DBMS_JOB Broken = Y [message #215731 is a reply to message #215708]

Tue, 23 January 2007 12:46

ebrian
Messages: 2794
Registered: April 2006

Senior Member

You can try the INTERVAL as 'trunc(sysdate)+1'.

Report message to a moderator

DBMS_JOB Broken / STATS % to be Taken [message #221383 is a reply to message #215731]

Mon, 26 February 2007 09:16

faiz_hyd
Messages: 294
Registered: February 2006

Senior Member

Hi,

This is the code which we are running, as per the interval is should run every night 11 PM, but what is happening when ever it's finishes it starts again and as a result of it is running
through out the day which is not good, Still i am not able to trace out why it doen't runs at fixed interval.
And surprisingly what happened it shows it's gathering stats , As far as i know it starts the table in Alphabetical order
since it's running through out the day i don't know just guess after seeing tables with names starting with
character P , Q , R , S, T actually were not gathered, since
the last_analyzed column shows 1/1/2007 , which has effected lot of queries, since some of these are huge partitioned
tables,

DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
    ( job       => X 
     ,what      => '/* job_name: JOB_DAILY_GATHER_STATS */BEGIN dbms_stats.gather_schema_stats(ownname => ''APP'', estimate_percent => NULL, cascade => TRUE); END;'
     ,next_date => to_date('25/02/2007 23:00:00','dd/mm/yyyy hh24:mi:ss')
     ,interval  => 'TRUNC(SYSDATE + 1, ''MI'')'
     ,no_parse  => TRUE
    );
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
END;
/

commit;

-----------

Another question is regarding gathering STATS whether it should be whole schema or some % or ?
we are running this stats gathering PRODUCTION Database,so what should be the option, I know it depends on
number of rows we add / delete daily..

Just Rough figures

Tables No of Rows Added Daily Deleted Daily

Bill 2.5 mil 1000-1500 50-100
Documents 40 mil 10000-15000 1000
images 60 mil 15000-20000 1000
references 90 mil 25000-35000 5000

There are many tables (220 +) but i am giving only some figures, so Please tell me whether it should be a whole schema STATS gathering or % level, if so what % should i opt for,
and there are some12 Range Partitioned tables also, should it differ .. Please put some expertise on this.

Is this a suggested option if we are not sure of % to be gathered

exec dbms_stats.gather_schema_stats('APP', estimate_percent=>dbms_stats.auto_sample_size, 
method_opt=>'for all columns size repeat');

Thanks

[Updated on: Mon, 26 February 2007 09:28]

Report message to a moderator

Re: DBMS_JOB Broken / STATS % to be Taken [message #221388 is a reply to message #221383]

Mon, 26 February 2007 09:32

ebrian
Messages: 2794
Registered: April 2006

Senior Member

As mentioned previously, the COMMIT should be INSIDE the anonymous block. In addition, you should consider Gathering Automated Statistics instead of calculating statistics blindly.

Report message to a moderator

Re: DBMS_JOB Broken / STATS % to be Taken [message #221398 is a reply to message #221388]

Mon, 26 February 2007 10:06

faiz_hyd
Messages: 294
Registered: February 2006

Senior Member

Thanks for follow up,

Yes commit is inside the loop, actually i coped that code from my old file, any how will still look with it,

how about this will this help

exec dbms_stats.gather_schema_stats('APP', estimate_percent=>dbms_stats.auto_sample_size);

this is from same link of Documentation you refered

Oracle Corporation recommends setting the ESTIMATE_PERCENT parameter of the DBMS_STATS gathering procedures to DBMS_STATS.AUTO_SAMPLE_SIZE to maximize performance gains while achieving necessary statistical accuracy. AUTO_SAMPLE_SIZE lets Oracle determine the best sample size for good statistics. For example, to collect table and column statistics for all tables in the OE schema with auto-sampling:

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('OE',DBMS_STATS.AUTO_SAMPLE_SIZE);

Can we use this auto sample size option.
will look for options with Gathering Automated Statistics, do you have any example or sample statement to use this

Thanks

Report message to a moderator

Re: DBMS_JOB Broken / STATS % to be Taken [message #221578 is a reply to message #221398]

Tue, 27 February 2007 09:22

faiz_hyd
Messages: 294
Registered: February 2006

Senior Member

Hi,
Please help me in this STATS job issue,
I submitted it to run for today 12:01:00 AM, with this option

 EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('OE',DBMS_STATS.AUTO_SAMPLE_SIZE);

Tilll now it is running, and moreover i just didn't gave any interval, since i wanted it to run for only once, but still it shows next_date as 2/27/2008 8:08:37 AM,
from where did it picked that next date, and
select last_analyzed from user_Tables shows strange result as i said earlier it didn't came upto tables starting with P ,Q,R,S,T,...Z and rest of other tables got analyzed till 2.am and started again at the above mentioned Next_date (8 a.m)
which is effecting since some huge tables are not gathered, i am lost what is going wrong,any clues please.

Now i found this error in Log file, since this a very huge table 110 millions and it's an OLTP application,definately is accessed 24/7, what can we do to avoid this SNAPSHOT error while gathering STATS

ue Feb 27 08:06:37 2007
ORA-01555 caused by SQL statement below (Query Duration=3396 sec, SCN: 0x0000.6ec1219b):
Tue Feb 27 08:06:37 2007
select  /*+ cursor_sharing_exact dynamic_sampling(0) no_monitoring*/  count(*),count("REF_ITEM_ROW_SEQ_NB"),cou
nt(distinct "REF_ITEM_ROW_SEQ_NB"),count("ALNUM_VAL_TX"),count(distinct "ALNUM_VAL_TX"),count("NUM_VAL_AM"),cou
nt(distinct "NUM_VAL_AM"),count("REF_ITEM_DT"),count(distinct "REF_ITEM_DT"),count(distinct "TXN_ID"),count("DO
C_ID"),count(distinct "DOC_ID"),count(distinct "PROC_DT"),count("INV_ID"),count(distinct "INV_ID"),sum(vsize("I
NV_ID")),substrb(dump(min("INV_ID"),16,0,32),1,120),substrb(dump(max("INV_ID"),16,0,32),1,120) from "APP"."RE
F_ITEM" t
Tue Feb 27 08:06:37 2007
Errors in file /u01/app/oracle/admin/PROD/bdump/neor1p1_j000_16158.trc:
ORA-12012: error on auto execute of job 921
ORA-01555: snapshot too old: rollback segment number 4 with name "_SYSSMU4$" too small
ORA-06512: at "SYS.DBMS_STATS", line 9643
ORA-06512: at "SYS.DBMS_STATS", line 10137
ORA-06512: at "SYS.DBMS_STATS", line 10324
ORA-06512: at "SYS.DBMS_STATS", line 10378
ORA-06512: at "SYS.DBMS_STATS", line 10355
ORA-06512: at line 1

Thanks

[Updated on: Tue, 27 February 2007 10:37]

Report message to a moderator

Previous Topic:	oracel startup problem
Next Topic:	ORA-00607

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 02 15:30:15 CST 2024