Home » RDBMS Server » Server Administration » DBMS_JOB Broken = Y
DBMS_JOB Broken = Y [message #213927] Fri, 12 January 2007 15:41 Go to next message
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
Re: DBMS_JOB Broken = Y [message #213935 is a reply to message #213927] Fri, 12 January 2007 18:13 Go to previous messageGo to next message
BlackSwan
Messages: 25046
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.
Re: DBMS_JOB Broken = Y [message #214465 is a reply to message #213935] Tue, 16 January 2007 09:39 Go to previous messageGo to next message
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
Re: DBMS_JOB Broken = Y [message #215503 is a reply to message #214465] Mon, 22 January 2007 11:01 Go to previous messageGo to next message
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
Re: DBMS_JOB Broken = Y [message #215509 is a reply to message #215503] Mon, 22 January 2007 11:20 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Is the job still getting broke? If so, check the alert log for any errors.
Re: DBMS_JOB Broken = Y [message #215514 is a reply to message #215509] Mon, 22 January 2007 11:36 Go to previous messageGo to next message
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
Re: DBMS_JOB Broken = Y [message #215516 is a reply to message #215514] Mon, 22 January 2007 11:43 Go to previous messageGo to next message
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;
/


Re: DBMS_JOB Broken = Y [message #215708 is a reply to message #215516] Tue, 23 January 2007 10:39 Go to previous messageGo to next message
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
Re: DBMS_JOB Broken = Y [message #215731 is a reply to message #215708] Tue, 23 January 2007 12:46 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You can try the INTERVAL as 'trunc(sysdate)+1'.
DBMS_JOB Broken / STATS % to be Taken [message #221383 is a reply to message #215731] Mon, 26 February 2007 09:16 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
Re: DBMS_JOB Broken / STATS % to be Taken [message #221398 is a reply to message #221388] Mon, 26 February 2007 10:06 Go to previous messageGo to next message
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

Re: DBMS_JOB Broken / STATS % to be Taken [message #221578 is a reply to message #221398] Tue, 27 February 2007 09:22 Go to previous message
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:
  


Current Time: Thu Dec 08 23:51:25 CST 2016

Total time taken to generate the page: 0.25027 seconds