14 Replies Latest reply: Oct 1, 2013 3:30 AM by Purvesh K RSS

    DBMS Job timing going wrong

    1008468


      Hi

       

      I created a materialized view as follows

       

      CREATE MATERIALIZED VIEW PUBLIC_ALL_ACTIVE_EMPLOYEES_MV

      REFRESH complete

      start with sysdate next  sysdate+interval '30' minute

      as select * from PUBLIC_ALL_ACTIVE_EMPLOYEES

       

      select sysdate,mview_name,last_refresh_date from

      from user_mviews where mview_name='PUBLIC_ALL_ACTIVE_EMPLOYEES_MV';

      1 29/09/2013 01:57:19 ã            PUBLIC_ALL_ACTIVE_EMPLOYEES_MV                     29/09/2013 01:34:32 ã

       

       

      select * from all_refresh;

      1 AAGOSI PUBLIC_ALL_ACTIVE_EMPLOYEES_MV 60 Y Y N  1497 29/09/2013 01:44:03 ã sysdate + interval '30' minute   N 1 0 0

       

      why next date column give a back time

       

       

       

      select * FROM dba_jobs;

      8 1497 AAGOSI AAGOSI AAGOSI     29/09/2013 01:44:03 ã 13:44:03 0 N sysdate + interval '30' minute    dbms_refresh.refresh('"AAGOSI"."PUBLIC_ALL_ACTIVE_EMPLOYEES_MV"'); NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY' 0102000200000000 0

       

      why next date gives back time here also

       

       

      Thanks

      Tony

        • 1. Re: DBMS Job timing going wrong
          Solomon Yakobson

          This is normal expected behavior. Main thing to understand is DBMS_JOB is never guaranteed to start job at exact time. Oracle checks for jobs to start, finds it and starts it. And this can take time, so job is guaranteed to start within seconds of scheduled time. Also, it depends on number of DBMS_JOBS jobs and number of job background processes. If it is too low for number of DBMS_JOBS there will be delay. But main thing is NEXT_DAY is calculated based on ACTUAL job start date. So shift keeps accumulating. To prevent that you "anchor" start date. For example, you want job to execute every 30 minutes. So use:

           

          TRUNC(SYSDATE) + (ROUND((SYSDATE - TRUNC(SYSDATE)) * 48) / 48) + 1 / 48

           

          This way job will be sheduled to run at exact 00 or 30 minutes. But again, it is not guaranteed it will start exactly at that time. Actual start time column be + few seconds.

           

          SY.

          • 2. Re: DBMS Job timing going wrong
            1008468

            Hi SY,

             

            Thank you for your response,

             

            But first of all if DBMS_JOB delays, then fine, but how come the next excecution time is early

            Let us leave DBMS_JOB for time being

             

            why would the all_refresh table has a BACK time, this is populated when i create a materlized View

             

            Thanks

            Tony

            • 3. Re: DBMS Job timing going wrong
              Purvesh K

              SolomonYakobson wrote:

               

              To prevent that you "anchor" start date. For example, you want job to execute every 30 minutes. So use:

               

              TRUNC(SYSDATE) + (ROUND((SYSDATE - TRUNC(SYSDATE)) * 48) / 48) + 1 / 48

               

              This way job will be sheduled to run at exact 00 or 30 minutes. But again, it is not guaranteed it will start exactly at that time. Actual start time column be + few seconds.

               

              SY.

               

              Can you explain the calculation part? I am finding it difficult to understand, especially the multiplication and division by 48. Is the use of digit 48, because the job is scheduled to execute twice every hour?

              • 4. Re: DBMS Job timing going wrong
                1008468

                Hi Purvesh,

                 

                Pls focus on my first question

                 

                Thanks

                Tony

                • 5. Re: DBMS Job timing going wrong
                  Purvesh K

                  Sorry, I did not mean to hijack the thread. If SY deems it fit to response, I will wait for an answer, else can always move my question to another thread.

                   

                  But a question for you, Did you understand the logic for the refresh interval?

                  • 6. Re: DBMS Job timing going wrong
                    1008468

                    Yes, I do understand the logic of refresh interval,  That is why i raised this question itself!

                     

                    Anyway, i am doing some testing, after which i can elaborate my question even more

                     

                    Thanks

                    Tony

                    • 7. Re: DBMS Job timing going wrong
                      1008468

                      Hi,

                       

                      Step1

                      ----------

                       

                      CREATE  MATERIALIZED VIEW PUBLIC_ALL_ACTIVE_EMPLOYEES_MV

                      REFRESH COMPLETE

                      start with sysdate  next   sysdate+1

                      as

                      select * from PUBLIC_ALL_ACTIVE_EMPLOYEES

                       

                      Materialized view created

                       

                      step2

                      ---------

                      select LAST_REFRESH_DATE from user_mviews where mview_name='PUBLIC_ALL_ACTIVE_EMPLOYEES_MV';

                       

                      30/09/2013 11:59:45 AM

                      step3

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

                       

                      select next_date from all_refresh;

                       

                      30/09/2013 11:58:28 AM

                       

                      step 4

                       

                       

                      select next_date FROM dba_jobs;

                       

                      30/09/2013 11:58:28 AM

                       

                      Question

                       

                      Why next refresh date is less than first refresh date?

                       

                      first refresh time:    30/09/2013 11:59:45 AM

                      next refresh time:   30/09/2013 11:58:28 AM

                       

                      hope my question is well understood now

                       

                      Thanks

                      Tony

                       

                       

                       

                       

                      • 8. Re: DBMS Job timing going wrong
                        Purvesh K


                        Between your Step1 and Step3, did you refresh the MView? Because the ALL_REFRESH will maintain the refresh time as the one on which MView was created (in this situation).

                         

                        See the below test to verify:

                         

                        SQL> drop materialized view mv_test_view;

                        Materialized view dropped.

                         

                        SQL> create materialized view mv_test_view
                          2  refresh complete
                          3  start with sysdate
                          4  next sysdate + 1
                          5  as select * from dual;

                        Materialized view created.

                         

                        SQL> set linesize 200

                        SQL> alter session set nls_date_format = 'DD-Mon-YYYY HH24:MI:SS';

                        Session altered.

                         

                        SQL> select log_user, last_date, last_sec, next_date, next_sec from dba_jobs order by log_user;

                        LOG_USER                       LAST_DATE            LAST_SEC                         NEXT_DATE         NEXT_SEC
                        ------------------------------ -------------------- -------------------------------- -------------------- --------------------------------
                        XXX_XXX                        30-Sep-2013 10:46:52 10:46:52                         01-Oct-2013 10:46:52 10:46:52

                        SQL> select rowner, rname, next_date from all_refresh;

                        ROWNER                         RNAME                          NEXT_DATE
                        ------------------------------ ------------------------------ --------------------
                        XXX_XXX                        MV_TEST_VIEW                   01-Oct-2013 10:46:52

                         

                        SQL> select owner, mview_name, last_refresh_date from dba_mviews order by owner;

                        OWNER                          MVIEW_NAME                     LAST_REFRESH_DATE
                        ------------------------------ ------------------------------ --------------------
                        XXX_XXX                        MV_TEST_VIEW                   30-Sep-2013 10:46:52

                        SQL> begin
                          2  dbms_mview.refresh('MV_TEST_VIEW', 'C');
                          3  end;
                          4  /

                        PL/SQL procedure successfully completed.

                         

                        SQL> select owner, mview_name, last_refresh_date from dba_mviews order by owner;

                        OWNER                          MVIEW_NAME                     LAST_REFRESH_DATE
                        ------------------------------ ------------------------------ --------------------
                        XXX_XXX                        MV_TEST_VIEW                   30-Sep-2013 10:49:04

                        SQL> select rowner, rname, next_date from all_refresh;

                        ROWNER                         RNAME                          NEXT_DATE
                        ------------------------------ ------------------------------ --------------------
                        XXX_XXX                        MV_TEST_VIEW                   01-Oct-2013 10:46:52

                         

                        SQL> select log_user, last_date, last_sec, next_date, next_sec from dba_jobs order by log_user;

                        LOG_USER                       LAST_DATE            LAST_SEC                         NEXT_DATE         NEXT_SEC
                        ------------------------------ -------------------- -------------------------------- -------------------- --------------------------------
                        XXX_XXX                        30-Sep-2013 10:46:52 10:46:52                         01-Oct-2013 10:46:52 10:46:52

                         

                        SQL>

                        • 9. Re: DBMS Job timing going wrong
                          1008468

                          Hi

                           

                          this is from your query after you created the MView

                           

                          select log_user, last_date, last_sec, next_date, next_sec from dba_jobs order by log_user;

                          LOG_USER                       LAST_DATE            LAST_SEC                         NEXT_DATE         NEXT_SEC
                          ------------------------------ -------------------- -------------------------------- -------------------- --------------------------------
                          XXX_XXX                        30-Sep-2013 10:46:52 10:46:52                         01-Oct-2013 10:46:52 10:46:52

                           

                          You are getting 01-OCT-2013 directly before you did the dbms_refresh

                          This is what i am not getting!

                           

                          meanwhile i tried as you suggested,but with same result

                           

                          Thanks

                          Tony

                          • 10. Re: DBMS Job timing going wrong
                            Purvesh K

                            Can you post the details in similar fashion, as I did, with your tables? You need not post the MV query or can mask the query tables.

                            • 11. Re: DBMS Job timing going wrong
                              Solomon Yakobson

                              SCOTT@pdborcl12 > alter session set nls_date_format = 'DD-Mon-YYYY HH24:MI:SS'
                                2  /

                              Session altered.

                              SCOTT@pdborcl12 > column log_user format a30
                              SCOTT@pdborcl12 > column rname format a30
                              SCOTT@pdborcl12 > column mview_name format a30
                              SCOTT@pdborcl12 > set linesize 132
                              SCOTT@pdborcl12 > create materialized view mv_test_view
                                2                 refresh complete
                                3                 start with TRUNC(SYSDATE) + (ROUND((SYSDATE - TRUNC(SYSDATE)) * 48) / 48) + 1 / 48
                                4                 next TRUNC(SYSDATE) + (ROUND((SYSDATE - TRUNC(SYSDATE)) * 48) / 48) + 1 / 48
                                5                 as select * from dual
                                6  /

                              Materialized view created.

                              SCOTT@pdborcl12 > select  last_ddl_time
                                2                 from  user_objects
                                3                 where object_type = 'MATERIALIZED VIEW'
                                4                 and object_name = 'MV_TEST_VIEW'
                                5  /

                              LAST_DDL_TIME
                              --------------------
                              30-Sep-2013 09:06:37

                              SCOTT@pdborcl12 > select  mview_name,
                                2                       last_refresh_date
                                3                 from  user_mviews
                                4  /

                              MVIEW_NAME                     LAST_REFRESH_DATE
                              ------------------------------ --------------------
                              MV_TEST_VIEW                   30-Sep-2013 09:06:37

                              SCOTT@pdborcl12 > select  log_user,
                                2                       last_date,
                                3                       last_sec,
                                4                       next_date,
                                5                       next_sec
                                6                 from  user_jobs
                                7                 order by log_user
                                8  /

                              LOG_USER                       LAST_DATE            LAST_SEC NEXT_DATE            NEXT_SEC
                              ------------------------------ -------------------- -------- -------------------- --------
                              SCOTT                                                        30-Sep-2013 09:30:00 09:30:00

                              SCOTT@pdborcl12 > select  rname,
                                2                       next_date
                                3                 from  user_refresh
                                4  /

                              RNAME                          NEXT_DATE
                              ------------------------------ --------------------
                              MV_TEST_VIEW                   30-Sep-2013 09:30:00

                              SCOTT@pdborcl12 >

                               

                              As you can see, materialized view was created 30-Sep-2013 09:06:37 and this what shows up as last refresh date initially. Refresh job last date is null since job didn't run yet (mview refresh date = mview create date). Next refresh date is 30-Sep-2013 09:30:00 regardless if you check corresponding dbms_job or user_refresh. I'll wait till 9:30 to show what happens next:

                               

                              SCOTT@pdborcl12 > select sysdate from dual
                                2  /

                              SYSDATE
                              --------------------
                              30-Sep-2013 09:33:30

                              SCOTT@pdborcl12 > select  mview_name,
                                2                       last_refresh_date
                                3                 from  user_mviews
                                4  /

                              MVIEW_NAME                     LAST_REFRESH_DATE
                              ------------------------------ --------------------
                              MV_TEST_VIEW                   30-Sep-2013 09:30:03

                              SCOTT@pdborcl12 > select  log_user,
                                2                       last_date,
                                3                       last_sec,
                                4                       next_date,
                                5                       next_sec
                                6                 from  user_jobs
                                7                 order by log_user
                                8  /

                              LOG_USER                       LAST_DATE            LAST_SEC NEXT_DATE            NEXT_SEC
                              ------------------------------ -------------------- -------- -------------------- --------
                              SCOTT                          30-Sep-2013 09:30:00 09:30:00 30-Sep-2013 10:00:00 10:00:00

                              SCOTT@pdborcl12 > select  rname,
                                2                       next_date
                                3                 from  user_refresh
                                4  /

                              RNAME                          NEXT_DATE
                              ------------------------------ --------------------
                              MV_TEST_VIEW                   30-Sep-2013 10:00:00

                              SCOTT@pdborcl12 >

                               

                              As you can see, now materialized view was refreshed at 9:30:03 (even though job says 9:30:00) but next refresh date is 10:00:00.

                               

                              SY.

                              • 12. Re: DBMS Job timing going wrong
                                1008468

                                Hi SY,

                                until now i was trying using PL/SQL Developer, So this time i used SQL*Plus, only additional script i ran was

                                alter session set nls_date_format = 'DD-Mon-YYYY HH24:MI:SS'

                                The Next date/Time comes perfect(So i am not adding the results here)

                                 

                                But i am confused why through PL/SQL in PROD only i got a back date/Time, In CLN instance it was correct,

                                I will test it again in PL/SQL developer and see.

                                 

                                Anyway thank you for the Help

                                 

                                Thanks

                                Tony

                                • 13. Re: DBMS Job timing going wrong
                                  1008468

                                  Hi

                                   

                                  Yes the problem was of PL/SQL Developer tool which is old Version  for the Database,

                                  It is fine with SQL* PLUS

                                   

                                  Thanks

                                  Tony

                                  • 14. Re: DBMS Job timing going wrong
                                    Purvesh K

                                    1008468 wrote:

                                     

                                    Hi

                                     

                                    Yes the problem was of PL/SQL Developer tool which is old Version  for the Database,

                                    It is fine with SQL* PLUS

                                     

                                    Thanks

                                    Tony

                                     

                                    Ideally it should not (tool to display wrong data), but now that you have found the issue, it would be better if you can close the thread.