Thanks a lot.

Hi Tom,
The Oracle documentation says the following about the V$SESSION_LONGOPS stats gathering:

<quote>
V$SESSION_LONGOPS
This view displays the status of various operations that run for longer than 6 seconds (in absolute time). These operations currently include many backup and recovery functions, statistics gathering, and query execution, and more operations are added for every Oracle release.

To monitor query execution progress, you must be using the cost-based optimizer and you must:

Set the TIMED_STATISTICS or SQL_TRACE parameter to true
Gather statistics for your objects with the ANALYZE statement or the DBMS_STATS package
You can add information to this view about application-specific long-running operations by using the DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS procedure.
</quote>

It says that you must set TIMED_STATISTICS to TRUE to get meaningful information in this view. Is there any overhead for setting this to TRUE permanently in a production system?

Can it be set to TRUE always, or is it used only when there is a performance problem (to evaluate the long running queries)?

Do you think it would be possible to convince the DBA's to set it to TRUE for a production system?
Thanks,

What can go wrong when killing the session behind a job?

Hi Tom,

I have had trouble finding out a session id, for purposes of killing it later, in case the user decides to abort the job, if it happens to run longer or some other reason. As the job is submitted through a dbms_job, I have to store the session id so that I use it for killing the job, when my main process is killed. 
(I am using the approach of 
a) Setting the job to broken
b) kill session
c) dbms_job remove)


However I am unable to get the session id in my procedure as it gives me the following error.

SQL> exec job_submit;
BEGIN job_submit; END;

*
ERROR at line 1:
ORA-20001: ORA-06550: line 2, column 64:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 2, col
ORA-06512: at "SRS_STAGE.JOB_SUBMIT", line 21
ORA-06512: at line 1

The tables and stored procedures that I am using are below.
create table job_parameters_log (job_id NUMBER, sid NUMBER);


CREATE OR REPLACE PROCEDURE job_submit

IS

   l_job PLS_INTEGER := 0;
 
BEGIN

         dbms_job.submit(l_job, 'declare l_job_id number := JOB; l_sid NUMBER; 
                                    begin select sid into l_sid from v$mystat where rownum = 1;
                                    p(JOB,l_sid);
                                 end;'
                          );
 
                      
         commit;                      

EXCEPTION
     WHEN OTHERS THEN
         ROLLBACK;
         RAISE_APPLICATION_ERROR (-20001, SUBSTR(SQLERRM,1,100));

END;
/

CREATE OR REPLACE PROCEDURE p
(
   p_job_id_in          IN NUMBER,
   p_sid_in             IN NUMBER
)
IS


BEGIN
   INSERT INTO job_parameters_log(job_id, sid)
   VALUES(p_job_id_in,p_sid_in);
   
   COMMIT;

EXCEPTION
     WHEN OTHERS THEN
         ROLLBACK;
         RAISE_APPLICATION_ERROR (-20001, SUBSTR(SQLERRM,1,100));

END;
/

What I understand is the I need to have access to v_$session granted to me directly, not via role? Is that correct, if it is, then how should I get my DBA to get it for me, because I don't think he will provide me access to the underlying view in a production environment.

Another question is my approach of killing a session mentioned earlier OK?

Thanks 
Prashant 

Hi Tom,

Thanks for the reply. I should be able to convince the DBA's.
I would also like to know that if I have launched a main procedure which in turn submits say 3 dbms_jobs. And waits for their completions using the logic (as you mentioned in one of your followup's sleep and check the status of jobs)
loop
dbms_lock.sleep(10);
check;
exit when condition met;
end loop;

So the main process only completes when all of the child processes have completed.
However if the jobs are running for an indefinitely long time, I will abort the main process which in turn should abort all the child processes. After figuring out the problem and perhaps a code fix I will re-start the main procedure again (of course in production ideally this shouldn't happen, but anyway I would like to take care of this eventuality).

Can I use the following logic to achieve the same?

<<declare section in main procedure>>
e_user_cancelled_operation EXCEPTION;
PRAGMA EXCEPTION_INIT(e_user_cancelled_operation,-1013);

<<exception handler>>
when e_user_cancelled_operation THEN
kill_dbms_jobs_child_proc

The kill_dbms_jobs_child_proc will have the logic as I mentioned in my last followup (of course taken from your followups), which is,
a) Identifying the child sessions (dbms_job sessions) I need to kill. (I have a parameter table that stores job name, parameters, sid for the processes spawned)
a) Setting the jobs to broken
b) killing the sessions
c) dbms_job remove.

Please let me know whether its possible for me to achieve this.

Thanks
Prashant

Hi Tom,

Thanks for the response....but sorry to be bothering you yet again. So can I come to know if a user has manually aborted a main process? So that i can trigger off the chain of events needed to kill the child processes.

Thanks
Prashant

Hi Tom,

Thanks for responding...you mentioned
<<why not give them a nice little interface
with a push button or something to cancel the job>>

This functionality that you are talking about would actually call the code that I have written for kill_dbms_jobs_child_proc (I mentioned this in my earlier followup) as well I need to kill the parent proc?

Thanks again
Prashant

If I'm understanding the example you gave in answer to the original question, you would periodically have to check waitone or waitany to see if there were a msg waiting for you, right? (obviously, this is my first foray into dbms_alert). So aside from actually killing the process from OEM or the shell, is there anyway to "signal" oh, say, a long running insert to die?

The problem I'm having is that if I kill a long running job from the shell, it seems to bypass the exception handler w/in the job (meaning that it dies right then and there). It'd be nice to be able to kill something, but at least record that something bad happened.

Thanks

Hi Tom,

I've just read this article and miss one detail in your first answer. How would the session calling dbms_job.submit now for what SID to look in the v$session_longops table when trying to "poll" for the current progress status?

Thank you for all the great help,

Dieter

Hi Tom,

I've just read this article and miss one detail in your first answer. How would the session calling dbms_job.submit now for what SID to look in the v$session_longops table when trying to "poll" for the current progress status?

Thank you for all the great help,

Dieter

Hi Tom,
I was wondering, if you could have a look at my last feedback.
Thank you
Dieter

Tom,

I am currently working on an application dealing with huge data[TB].This DB get data very frequently and few procedures run after every 5 minutes to rollup the data to corresponding tables.

Now while providing a upgrade script, I am currently stuck with a peculiar issue.

Firstly I go ahead and drop all jobs and procedures.
If by then one of the procedure is started, it would lock involved tables.
If my upgrade procedure also tries to alter this table then it fails while waiting for the locked object.


What strategy should I follow to ensure none of rolling-up procedure are running before upgrade procedure starts running.
I know killing a procedure is not a good solution here. Please provide pointers on this...Thanks

I would not have the liberty to wait for my executing procedures to end [They might take hours in some extreme cases].

Since I don't have redolog files being maintained, can I go ahead and kill the job. I agree that this will result in have processed data, but I my customer is ok with that. He simply doesn't want to wait for long for upgrade.

Is killing the job the only alternative I have or is there any other better option.