7 Replies Latest reply: Apr 11, 2014 2:27 AM by GregV RSS

    Scheduler job not working as expected in 10.2.0.5

    SagiGal

      Hi Guys,

       

      I have create a scheduler job which will execute a stored procedure to kill the "SNIPED" database session.

      However, the scheduler job is not running as per expect.

      Based on my testing, I have drop and recreate the scheduler jobs a few times, sometimes it will get executed for first time, then no more.

      But from the dba_scheduler_jobs view,  LAST_START_DATE and LAST_START_DATE keep updated, showing that the scheduler job is running fine, however it does not kill my "SNIPED" session.

      Please advise. My scheduler jobs working fine in another 10.2.0.4 database.

       

      FYI, I have try a few option like disable and enable the window scheduler job as below but not working.

      Fix dbms_scheduler jobs after upgrade | Raheel's Blog

       

      OWNER      JOB_NAME             JOB_ACTION           START_DATE           REPEAT_INTERVAL      LAST_START_DATE      NEXT_RUN_DATE

      ---------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------

      SYS        KILL_SESSION_JOB     KILL_SESSION_PROC    07-APR-14 03.38.00.0 FREQ=MINUTELY; INTER 09-APR-14 09.38.00.1 09-APR-14 09.53.00.0

                                                           00000 PM +08:00      VAL=15;              25172 AM +08:00      00000 AM +08:00

        • 1. Re: Scheduler job not working as expected in 10.2.0.5
          GregV

          Hi,

           

           

          Maybe your job runs as scheduled but fails. What is the STATUS of your job runs from the dba_scheduler_job_log view?    

          What does the ADDITIONAL_INFO column say?

          • 2. Re: Scheduler job not working as expected in 10.2.0.5
            SagiGal

            Hi GregV,

             

            It show succeeded. But my sniped session since this morning still exist.

            Do u have any idea? 

            I search online and found that there is scheduler job cases in 10.2.0.5 database. I tried their solution but still not working.

             

            ---------- ---------------------------------------- ---------- -------------------- --------------- --------------- --------------------

                140566 09-APR-14 03.08.00.138819 PM +08:00      SYS        KILL_SESSION_JOB     RUN             SUCCEEDED

                140572 09-APR-14 03.23.00.133837 PM +08:00      SYS        KILL_SESSION_JOB     RUN             SUCCEEDED

                140578 09-APR-14 03.38.00.140533 PM +08:00      SYS        KILL_SESSION_JOB     RUN             SUCCEEDED

                140582 09-APR-14 03.53.00.136421 PM +08:00      SYS        KILL_SESSION_JOB     RUN             SUCCEEDED

                140589 09-APR-14 04.08.02.135780 PM +08:00      SYS        KILL_SESSION_JOB     RUN             SUCCEEDED

                140595 09-APR-14 04.23.00.136267 PM +08:00      SYS        KILL_SESSION_JOB     RUN             SUCCEEDED

                140601 09-APR-14 04.38.00.135356 PM +08:00      SYS        KILL_SESSION_JOB     RUN             SUCCEEDED

             

             

                LOG_ID LOG_DATE                                 OWNER      JOB_NAME             OPERATION       STATUS          ADDITIONAL_INFO

            ---------- ---------------------------------------- ---------- -------------------- --------------- --------------- --------------------

                140605 09-APR-14 04.53.00.141754 PM +08:00      SYS        KILL_SESSION_JOB     RUN             SUCCEEDED

            • 3. Re: Scheduler job not working as expected in 10.2.0.5
              GregV

              SNIPED sessions are like KILLED sessions. They'll disappear from V$SESSION only after they try to execute any SQL statement.

              • 4. Re: Scheduler job not working as expected in 10.2.0.5
                SagiGal

                Yes. But if they didn't, the session would be forever there, occupying the session and if number of session exceeded, new session will not able to coming in.

                Hence i create a job to kill the sniped session. but the job not working as per expected.

                Do you have idea on this?

                • 5. Re: Scheduler job not working as expected in 10.2.0.5
                  GregV

                  The job cannot work as you expect, because it can't kill sessions that are already killed/sniped.

                  You can try to implement DCD (Dead Connection Detected) to get rid of abandoned sessions. This is done by setting SQLNET.EXPIRE_TIME = x (minutes) in the server sqlnet.ora file.

                   

                   

                  Another possibility, if this doesn't work, is to kill the process at OS level. So you could have an external Scheduler job that calls a shell to terminate those sessions.

                   

                   

                  What is your platform ?

                  • 6. Re: Scheduler job not working as expected in 10.2.0.5
                    SagiGal

                    Hi GregV,

                     

                    My platform is AIX.

                     

                    The external scheduler option, if the current scheduler job which I have created is not working. I don't think new scheduler will works.

                     

                    SQLNET.EXPIRE_TIME = x (minutes) , how it works differently compared to kill the sniped session?

                    I assume that this parameter is to check if the client process is still alive, but not inactive session.

                    Please advise.


                    • 7. Re: Scheduler job not working as expected in 10.2.0.5
                      GregV

                      Hi,

                       

                      DCD sends a "probe"packet to the expired client sessions to signal the OS to release the connection's resource.

                      Killing the processes at OS level should be done in last resort.