5 Replies Latest reply: Jun 26, 2014 9:17 AM by GregV RSS

    DBMS Scheduler ORA$AT_SQ_SQL_SW show error result

    1878292

      Hi All,

      From the trace log of oracle, i found 1 file that mention ORA$A_SQ_SQL_SW running and give the below information. One of them information said status=(pst=ERROR). What does it mean? from the alert log didnt show anything about this error.

       

       

      *** 2014-06-23 20:31:37.336

      *** MODULE NAME:(DBMS_SCHEDULER) 2014-06-23 20:31:37.336

      *** ACTION NAME:(ORA$AT_SQ_SQL_SW_753) 2014-06-23 20:31:37.336

       

        status=(pst=SUCCESS)

      **************************************************************

      Execution Statistics

        Exec count:         1

        CR gets:            24004

        CU gets:            1

        Disk Reads:         0

        Disk Writes:        0

        IO Read Requests:   0

        IO Write Requests:  0

        Bytes Read:         0

        Bytes Written:      0

        Bytes Exchanged with Storage:  0

        Bytes Exchanged with Disk:  0

        Bytes Simulated Read:  0

        Bytes Simulated Returned:  0

        Elapsed Time: 23 (s)

        CPU Time: 19 (s)

        User I/O Time: 0 (us)

       

      *** 2014-06-23 20:32:42.449

        status=(pst=SUCCESS)

      **************************************************************

      Execution Statistics

        Exec count:         1

        CR gets:            24003

        CU gets:            0

        Disk Reads:         0

        Disk Writes:        0

        IO Read Requests:   0

        IO Write Requests:  0

        Bytes Read:         0

        Bytes Written:      0

        Bytes Exchanged with Storage:  0

        Bytes Exchanged with Disk:  0

        Bytes Simulated Read:  0

        Bytes Simulated Returned:  0

        Elapsed Time: 21 (s)

        CPU Time: 19 (s)

        User I/O Time: 0 (us)

        status=(pst=SUCCESS)

      **************************************************************

      Execution Statistics

        Exec count:         1

        CR gets:            62

        CU gets:            0

        Disk Reads:         0

        Disk Writes:        0

        IO Read Requests:   0

        IO Write Requests:  0

        Bytes Read:         0

        Bytes Written:      0

        Bytes Exchanged with Storage:  0

        Bytes Exchanged with Disk:  0

        Bytes Simulated Read:  0

        Bytes Simulated Returned:  0

        Elapsed Time: 246 (us)

        CPU Time: 200 (us)

        User I/O Time: 0 (us)

        status=(pst=ERROR)

        status=(pst=ERROR)

        status=(pst=SUCCESS)

      **************************************************************

      Execution Statistics

        Exec count:         1

        CR gets:            30

        CU gets:            0

        Disk Reads:         0

        Disk Writes:        0

        IO Read Requests:   0

        IO Write Requests:  0

        Bytes Read:         0

        Bytes Written:      0

        Bytes Exchanged with Storage:  0

        Bytes Exchanged with Disk:  0

        Bytes Simulated Read:  0

        Bytes Simulated Returned:  0

        Elapsed Time: 103 (us)

        CPU Time: 199 (us)

        User I/O Time: 0 (us)

        status=(pst=ERROR)

        status=(pst=SUCCESS)

      **************************************************************

      Execution Statistics

        Exec count:         1

        CR gets:            17

        CU gets:            0

        Disk Reads:         0

        Disk Writes:        0

        IO Read Requests:   0

        IO Write Requests:  0

        Bytes Read:         0

        Bytes Written:      0

        Bytes Exchanged with Storage:  0

        Bytes Exchanged with Disk:  0

        Bytes Simulated Read:  0

        Bytes Simulated Returned:  0

        Elapsed Time: 93 (us)

        CPU Time: 100 (us)

        User I/O Time: 0 (us)

       

        • 1. Re: DBMS Scheduler ORA$AT_SQ_SQL_SW show error result
          GregV

          Hi,

           

          Why do you check that file? Any particular reason for that?

          • 2. Re: DBMS Scheduler ORA$AT_SQ_SQL_SW show error result
            1878292

            Hi GregV,

            Because arround that time my server got High Utilization until 3 minutes. Then i found those file. Bellow is the comparation when i got high utilization and not.

             

            [Didnt Hit High Utilization:]

            SQL> select log_date,OPERATION,JOB_NAME,STATUS from dba_scheduler_job_log where LOG_DATE like '%24-JUN-14%' order by log_date asc;

             

            LOG_DATE                                OPERATION         JOB_NAME                           STATUS          

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

            24-JUN-14 01.30.00.526080 AM +06:30     RUN               PURGE_LOG                          SUCCEEDED

            24-JUN-14 01.30.01.290499 AM +06:30     RUN               ORA$AUTOTASK_CLEAN                 SUCCEEDED

            24-JUN-14 08.30.00.318246 PM +06:30     UPDATE            ORA$AT_SA_SPC_SY_754

            24-JUN-14 08.30.00.322498 PM +06:30     UPDATE            ORA$AT_SQ_SQL_SW_755

            24-JUN-14 08.30.00.324589 PM +06:30     ENABLE            ORA$AT_SA_SPC_SY_754

            24-JUN-14 08.30.00.328471 PM +06:30     ENABLE            ORA$AT_SQ_SQL_SW_755

            24-JUN-14 08.30.04.811445 PM +06:30     RUN               DRA_REEVALUATE_OPEN_FAILURES       SUCCEEDED

            24-JUN-14 08.30.15.224849 PM +06:30     RUN               ORA$AT_SQ_SQL_SW_755               SUCCEEDED

            24-JUN-14 08.30.15.226600 PM +06:30     COMPLETED         ORA$AT_SQ_SQL_SW_755

            24-JUN-14 08.30.15.227810 PM +06:30     DROP              ORA$AT_SQ_SQL_SW_755

            24-JUN-14 08.30.25.413826 PM +06:30     RUN               ORA$AT_SA_SPC_SY_754               SUCCEEDED

            24-JUN-14 08.30.25.414386 PM +06:30     COMPLETED         ORA$AT_SA_SPC_SY_754

            24-JUN-14 08.30.25.414662 PM +06:30     DROP              ORA$AT_SA_SPC_SY_754

            24-JUN-14 10.30.01.319006 PM +06:30     RUN               RSE$CLEAN_RECOVERABLE_SCRIPT       SUCCEEDED

            24-JUN-14 10.30.01.574489 PM +06:30     RUN               SM$CLEAN_AUTO_SPLIT_MERGE          SUCCEEDED

            24-JUN-14 11.31.02.599735 PM +06:30     RUN               MGMT_CONFIG_JOB                    SUCCEEDED

            24-JUN-14 11.31.03.182764 PM +06:30     RUN               MGMT_CONFIG_JOB_1                  SUCCEEDED

             

             

            [Hit High Utilization]

            SQL> select log_date,OPERATION,JOB_NAME,STATUS from dba_scheduler_job_log where LOG_DATE like '%23-JUN-14%' order by log_date asc;

             

            LOG_DATE                                OPERATION         JOB_NAME                           STATUS          

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

            23-JUN-14 01.30.01.129627 AM +06:30     RUN               PURGE_LOG                          SUCCEEDED

            23-JUN-14 01.30.01.741843 AM +06:30     RUN               ORA$AUTOTASK_CLEAN                 SUCCEEDED

            23-JUN-14 08.30.00.335090 PM +06:30     UPDATE            ORA$AT_SA_SPC_SY_752

            23-JUN-14 08.30.00.344096 PM +06:30     UPDATE            ORA$AT_SQ_SQL_SW_753

            23-JUN-14 08.30.00.348848 PM +06:30     ENABLE            ORA$AT_SA_SPC_SY_752

            23-JUN-14 08.30.00.355197 PM +06:30     ENABLE            ORA$AT_SQ_SQL_SW_753

            23-JUN-14 08.30.01.906591 PM +06:30     RUN               DRA_REEVALUATE_OPEN_FAILURES       SUCCEEDED

            23-JUN-14 08.30.18.046788 PM +06:30     RUN               ORA$AT_SA_SPC_SY_752               SUCCEEDED

            23-JUN-14 08.30.18.049568 PM +06:30     COMPLETED         ORA$AT_SA_SPC_SY_752

            23-JUN-14 08.30.18.050954 PM +06:30     DROP              ORA$AT_SA_SPC_SY_752

            23-JUN-14 08.32.42.921135 PM +06:30     RUN               ORA$AT_SQ_SQL_SW_753               SUCCEEDED

            23-JUN-14 08.32.42.921625 PM +06:30     COMPLETED         ORA$AT_SQ_SQL_SW_753

            23-JUN-14 08.32.42.921875 PM +06:30     DROP              ORA$AT_SQ_SQL_SW_753

            23-JUN-14 10.30.00.803129 PM +06:30     RUN               SM$CLEAN_AUTO_SPLIT_MERGE          SUCCEEDED

            23-JUN-14 10.30.01.427449 PM +06:30     RUN               RSE$CLEAN_RECOVERABLE_SCRIPT       SUCCEEDED

            23-JUN-14 11.31.02.782886 PM +06:30     RUN               MGMT_CONFIG_JOB                    SUCCEEDED

            23-JUN-14 11.31.03.199356 PM +06:30     RUN               MGMT_CONFIG_JOB_1                  SUCCEEDED

            • 3. Re: DBMS Scheduler ORA$AT_SQ_SQL_SW show error result
              GregV

              Some of these look like Automatic Maintenance Task's related jobs. It's hard to say which one caused high CPU usage, but you shouldn't bother about these tasks, unless they clash with some tasks of yours. Try first to identify which process consumed most of the resources.

              What is your DB version?

              • 4. Re: DBMS Scheduler ORA$AT_SQ_SQL_SW show error result
                1878292

                Hi GregV,

                Ok i will try to find which job caused high CPU usage, My DB version is Oracle 11g Version 11.2.0.4.0. But can you explain to me, what is the report said in my first post. Why it shows 'status=(pst=ERROR)' ?

                • 5. Re: DBMS Scheduler ORA$AT_SQ_SQL_SW show error result
                  GregV

                  I don't know what these traces mean, but I think they are informational only, so you don't need to worry about them.