-- --Original Message-- -- From: oracle-l-bounce@(protected) [ mailto:oracle-l-bounce@(protected)] Sent: Thu 7/28/2005 10:51 AM To: maxim.pakhutkin@(protected); oracle-l@(protected) Subject: RE: monitoring jobs (dbms_job)
Max,
The following is the sql that I use to watch jobs. It is also used to other things as well so there is a little bit more here that I think you'll not need.
Encrypt is a package created here as a wrapper around dbms_obfuscation. Much longer history too.
DBA_REPRESH_PASS is a table where we store the username and encrypted password of the job's owner.
I think the RUN_TIME column will be of interest. We generate warning messages for any job that exceeds 30 minutes of run_time.
SELECT /*+ RULE */ SCHEMA_USER, ENCRYPT.CODE(NVL(RPASS,'8('))RPASS, E.JOB, NVL(ROUND((SYSDATE-E.THIS_DATE)*1440),0)RUN_TIME, ROUND((SYSDATE - E.NEXT_DATE )*1440) REFRESH_TIME, SPID, BROKEN, E.WHAT, REPLACE(UPPER(INTERVAL), 'SYSDATE','TO_DATE('||CHR(39)||NVL(E.LAST_DATE, SYSDATE)||CHR(39)||')'), TO_NUMBER(TO_CHAR(E.NEXT_DATE,'J')) FROM DBA_REFRESH_PASS A, (SELECT D.JOB, SPID FROM V$PROCESS B, V$SESSION C, DBA_JOBS_RUNNING D WHERE PADDR = ADDR AND C.SID = D.SID)F, DBA_JOBS E WHERE A.RJOWNER(+) = rtrim(SCHEMA_USER) AND F.JOB(+) = E.JOB AND E.LAST_DATE IS NOT NULL;
-- --Original Message-- -- From: oracle-l-bounce@(protected) [ mailto:oracle-l-bounce@(protected)] On Behalf Of Pakhutkin, Maxim (Max) Sent: Thursday, July 28, 2005 10:31 AM To: oracle-l@(protected) Subject: monitoring jobs (dbms_job)
Has anyone tried monitoring the run duration of jobs scheduled with dbms_jobs over time to determine trends? I'm thinking of running some pl/sql in intervals that will, in general, do the following: looking at jobs that are scheduled to run at least that often, note their next_date and then, when the monitored job runs next time, compared the noted next time to the values in last_date. The description of the dba_jobs view gives me doubts however, as it doesn't seem to correspond to reality in respect to last_date, last_sec columns. My observation has been that last_sec shows when the job last completed, but the view description says it shows when the job last started.
The business need is to prevent jobs from running into the scheduled cold backups and interfering with them. I've searched the archive and ask tom, but couldn't come up with any direct hits. If anyone has tried this before, I would appreciate if you could point out any non-trivial issues you've encountered, thanks.
mostly 9i's with some 8i's thrown in on solaris.
Max Pakhutkin -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1"> <TITLE>RE: monitoring jobs (dbms_job)</TITLE>
<META content="MSHTML 6.00.2800.1505" name=GENERATOR></HEAD> <BODY> <DIV><SPAN class=990425315-28072005><FONT face=Arial color=#0000ff size=2>Or, you can add some writes to a table or to alert/user dump using dbms_system.ksdwrt:</FONT></SPAN></DIV> <DIV><SPAN class=990425315-28072005><FONT face=Arial color=#0000ff size=2></FONT></SPAN> </DIV> <DIV><SPAN class=990425315-28072005><FONT face=Arial color=#0000ff size=2>-- job:</FONT></SPAN></DIV> <DIV><SPAN class=990425315-28072005><FONT face=Arial color=#0000ff size=2></FONT></SPAN> </DIV> <DIV><SPAN class=990425315-28072005><FONT color=#0000ff size=2><FONT color=#0000f0 size=2> </DIV> <DIV><FONT face=Arial color=#0000ff size=1>DECLARE</FONT></DIV></FONT> <DIV><FONT size=1><FONT face=Arial>X <FONT color=#ff0000>NUMBER</FONT></FONT></FONT><FONT color=#0000f0><FONT face=Arial size=1>;</FONT></DIV> <DIV><FONT face=Arial color=#0000ff size=1>BEGIN</FONT></DIV></FONT> <DIV><FONT size=1><FONT face=Arial>SYS<FONT color=#0000f0>.DBMS_JOB.SUBMIT</DIV></FONT></FONT></FONT> <DIV><FONT size=1><FONT face=Arial><FONT color=#0000f0>(</FONT> job <FONT color=#0000f0>=></FONT> X </FONT></FONT></DIV> <DIV><FONT size=1><FONT face=Arial><FONT color=#0000f0>,</FONT>what <FONT color=#0000f0>=></FONT> <FONT color=#ff0000>'begin</DIV></FONT></FONT></FONT> <DIV><FONT size=1><FONT face=Arial>sys<FONT color=#0000f0>.</FONT>dbms_system<FONT color=#0000f0>.</FONT>ksdddt<FONT color=#0000f0>;</DIV></FONT></FONT></FONT> <DIV><FONT size=1><FONT face=Arial>sys<FONT color=#0000f0>.</FONT>dbms_system<FONT color=#0000f0>.</FONT>ksdwrt<FONT color=#0000f0>(</FONT><FONT color=#800000>1</FONT><FONT color=#0000f0>,</FONT><FONT color=#ff0000>''</FONT>starting job<FONT color=#ff0000>''</FONT><FONT color=#0000f0>);</DIV></FONT></FONT></FONT> <DIV><FONT size=1><FONT face=Arial><FONT color=#808000>my_sp</FONT><FONT color=#0000f0>;</DIV></FONT></FONT></FONT> <DIV><FONT size=1><FONT face=Arial>sys<FONT color=#0000f0>.</FONT>dbms_system<FONT color=#0000f0>.</FONT>ksdwrt<FONT color=#0000f0>(</FONT><FONT color=#800000>1</FONT><FONT color=#0000f0>,</FONT><FONT color=#ff0000>''</FONT>ending job<FONT color=#ff0000>''</FONT><FONT color=#0000f0>);</DIV></FONT></FONT></FONT> <DIV><FONT size=1><FONT face=Arial>sys<FONT color=#0000f0>.</FONT>dbms_system<FONT color=#0000f0>.</FONT>ksdddt<FONT color=#0000f0>;</DIV></FONT></FONT></FONT> <DIV><FONT size=1><FONT face=Arial><FONT color=#0000f0>end;</FONT><FONT color=#ff0000>'</DIV></FONT></FONT></FONT> <DIV><FONT face=Arial><FONT size=1><FONT color=#0000f0>,</FONT>next_date <FONT color=#0000f0>=></FONT> <SPAN class=990425315-28072005><FONT color=#0000f0>sysdate</FONT></SPAN><FONT color=#0000f0></DIV></FONT></FONT></FONT> <DIV><FONT size=1><FONT face=Arial><FONT color=#0000f0>,interval</FONT> <FONT color=#0000f0>=></FONT> <FONT color=#ff0000>'sysdate+1'</DIV></FONT></FONT></FONT> <DIV><FONT color=#0000f0 size=2><FONT face=Arial size=1>);</FONT></DIV> <DIV><FONT face=Arial color=#0000ff size=1>END;</FONT></DIV> <DIV><FONT face=Arial color=#0000ff size=1>/</FONT></DIV> <DIV><FONT face=Arial color=#0000ff></FONT></FONT></FONT></SPAN> </DIV> <DIV><SPAN class=990425315-28072005><FONT face=Arial color=#0000ff size=2>-- output to dump</FONT></SPAN></DIV> <DIV><SPAN class=990425315-28072005><FONT face=Arial color=#0000ff size=2></FONT></SPAN> </DIV> <DIV><SPAN class=990425315-28072005><FONT face=Arial color=#0000ff size=2>*** 2005-07-28 10:51:03.818<BR>starting job<BR>ending job<BR>*** 2005-07-28 10:51:13.865<BR></FONT></SPAN></DIV> <DIV><SPAN class=990425315-28072005><FONT face=Arial color=#0000ff size=2></FONT></SPAN> </DIV> <DIV><SPAN class=990425315-28072005><FONT face=Arial color=#0000ff size=2>regards,</FONT></SPAN></DIV> <DIV><SPAN class=990425315-28072005><FONT face=Arial color=#0000ff size=2>GAP</FONT></SPAN></DIV> <DIV><SPAN class=990425315-28072005><FONT face=Arial color=#0000ff size=2></FONT></SPAN> </DIV> <DIV><FONT size=2><BR>-- --Original Message-- --<BR>From: oracle-l-bounce@(protected) [<A href="mailto:oracle-l-bounce@(protected)">mailto:oracle-l-bounce@(protected) .org</A>]<BR>Sent: Thu 7/28/2005 10:51 AM<BR>To: maxim.pakhutkin@(protected); oracle-l@(protected)<BR>Subject: RE: monitoring jobs (dbms_job)<BR><BR>Max,<BR><BR> The following is the sql that I use to watch jobs. It is also<BR>used to other things as well so there is a little bit more here that I<BR>think you'll not need.<BR><BR>Encrypt is a package created here as a wrapper around dbms_obfuscation.<BR>Much longer history too.<BR><BR>DBA_REPRESH_PASS is a table where we store the username and encrypted<BR>password of the job's owner.<BR><BR>I think the RUN_TIME column will be of interest. We generate warning<BR>messages for any job that exceeds 30 minutes of run_time.<BR><BR>SELECT /*+ RULE */ SCHEMA_USER, ENCRYPT.CODE(NVL(RPASS,'8('))RPASS,<BR>E.JOB,<BR> NVL(ROUND((SYSDATE-E.THIS_DATE)*1440),0)RUN_TIME,<BR> ROUND((SYSDATE - E.NEXT_DATE )*1440) REFRESH_TIME, SPID,<BR>BROKEN, E.WHAT,<BR> REPLACE(UPPER(INTERVAL),<BR>'SYSDATE','TO_DATE('||CHR(39)||NVL(E.LAST_DATE, SYSDATE)||CHR(39)||')'),<BR> TO_NUMBER(TO_CHAR(E.NEXT_DATE,'J'))<BR> FROM DBA_REFRESH_PASS A, (SELECT D.JOB, SPID<BR>   ; FROM V$PROCESS B, V$SESSION C,<BR>DBA_JOBS_RUNNING D<BR> WHERE PADDR = ADDR AND C.SID =<BR>D.SID)F, DBA_JOBS E<BR> WHERE A.RJOWNER(+) = rtrim(SCHEMA_USER)<BR> AND F.JOB(+) = E.JOB<BR> AND E.LAST_DATE IS NOT NULL;<BR><BR>-- --Original Message-- --<BR>From: oracle-l-bounce@(protected)<BR>[<A href="mailto:oracle-l-bounce@(protected)">mailto:oracle-l-bounce@(protected) .org</A>] On Behalf Of Pakhutkin, Maxim<BR>(Max)<BR>Sent: Thursday, July 28, 2005 10:31 AM<BR>To: oracle-l@(protected)<BR>Subject: monitoring jobs (dbms_job)<BR><BR>Has anyone tried monitoring the run duration of jobs scheduled with<BR>dbms_jobs over time to determine trends? I'm thinking of running some<BR>pl/sql in intervals that will, in general, do the following: looking at<BR>jobs that are scheduled to run at least that often, note their next_date<BR>and then, when the monitored job runs next time, compared the noted next<BR>time to the values in last_date. The description of the dba_jobs view<BR>gives me doubts however, as it doesn't seem to correspond to reality in<BR>respect to last_date, last_sec columns. My observation has been that<BR>last_sec shows when the job last completed, but the view description<BR>says it shows when the job last started.<BR><BR>The business need is to prevent jobs from running into the scheduled<BR>cold backups and interfering with them. I've searched the archive and<BR>ask tom, but couldn't come up with any direct hits. If anyone has tried<BR>this before, I would appreciate if you could point out any non-trivial<BR>issues you've encountered, thanks.<BR><BR>mostly 9i's with some 8i's thrown in on solaris.<BR><BR>Max Pakhutkin<BR>--<BR><A href="http://www.freelists.org/webpage/oracle-l">http://www.freelists.org /webpage/oracle-l</A><BR>--<BR><A href="http://www.freelists.org/webpage/oracle-l">http://www.freelists.org /webpage/oracle-l</A><BR><BR><BR></FONT></DIV></BODY></HTML>
