3 Replies Latest reply: Nov 29, 2012 3:17 AM by spajdy RSS

    Slow Count(*) response on USER_SCHEDULER_JOB_RUN_DETAILS

    975567
      Hi Folks,

      It takes almost two minutes each on the following two queries,

      select count(*) from USER_SCHEDULER_JOB_RUN_DETAILS;
      162 records

      select count(*) from USER_SCHEDULER_JOB_LOG;
      171 records

      All other USER_* views are fine and come back within miliseconds.

      We are running Oracle Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit.

      USER_SCHEDULER_JOB_LOG and USER_SCHEDULER_JOB_RUN_DETAILS did gro to millions and purged them about a week ago.

      Your comments and suggestions are much appreciated on this.

      Thanks,
        • 1. Re: Slow Count(*) response on USER_SCHEDULER_JOB_RUN_DETAILS
          spajdy
          run following in SQL*Plus and put there result
          spool trace.log
          set linesize 2000
          select count(*) from USER_SCHEDULER_JOB_RUN_DETAILS;
          select * from table(dbms_xplan.display_cursor());
          select count(*) from USER_SCHEDULER_JOB_LOG;
          select * from table(dbms_xplan.display_cursor());
          spool off;
          • 2. Re: Slow Count(*) response on USER_SCHEDULER_JOB_RUN_DETAILS
            975570
            Thanks for the reply. Here is the log.

            SQL>
            SQL> spool trace.log
            SQL> set linesize 2000
            SQL> select count(*) from USER_SCHEDULER_JOB_RUN_DETAILS;

            COUNT(*)
            ----------
            85

            SQL> select * from table(dbms_xplan.display_cursor());

            PLAN_TABLE_OUTPUT
            -------------------------------------------------------------------------------------------------------------------------
            SQL_ID gvv1mxmbqwrm3, child number 0
            -------------------------------------
            select count(*) from USER_SCHEDULER_JOB_RUN_DETAILS

            Plan hash value: 564584594

            -------------------------------------------------------------------------------------------------------------
            | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
            -------------------------------------------------------------------------------------------------------------
            | 0 | SELECT STATEMENT | | | | 20 (100)| |
            | 1 | SORT AGGREGATE | | 1 | 23 | | |

            PLAN_TABLE_OUTPUT
            -------------------------------------------------------------------------------------------------------------------------
            | 2 | MERGE JOIN | | 6 | 138 | 20 (5)| 00:00:01 |
            |* 3 | TABLE ACCESS BY INDEX ROWID| SCHEDULER$_EVENT_LOG | 6 | 102 | 17 (0)| 00:00:01 |
            | 4 | INDEX FULL SCAN | SCHEDULER$_INSTANCE_PK | 24 | | 3 (0)| 00:00:01 |
            |* 5 | SORT JOIN | | 21 | 126 | 3 (34)| 00:00:01 |
            | 6 | INDEX FAST FULL SCAN | I_SCHEDULER_JOB_RUN_DETAILS | 21 | 126 | 2 (0)| 00:00:01 |
            -------------------------------------------------------------------------------------------------------------

            Predicate Information (identified by operation id):
            ---------------------------------------------------

            3 - filter(("E"."OWNER"=SYS_CONTEXT('USERENV','CURRENT_SCHEMA') AND "E"."TYPE#"=66))

            PLAN_TABLE_OUTPUT
            -------------------------------------------------------------------------------------------------------------------------
            5 - access("J"."LOG_ID"="E"."LOG_ID")
            filter("J"."LOG_ID"="E"."LOG_ID")


            25 rows selected.

            SQL> select count(*) from USER_SCHEDULER_JOB_LOG;

            COUNT(*)
            ----------
            88

            SQL> select * from table(dbms_xplan.display_cursor());

            PLAN_TABLE_OUTPUT
            -------------------------------------------------------------------------------------------------------------------------
            SQL_ID cachtraxbvnqc, child number 0
            -------------------------------------
            select count(*) from USER_SCHEDULER_JOB_LOG

            Plan hash value: 1199204923

            --------------------------------------------------------------------------------------------
            | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
            --------------------------------------------------------------------------------------------
            | 0 | SELECT STATEMENT | | | | 251K(100)| |
            | 1 | SORT AGGREGATE | | 1 | 20 | | |

            PLAN_TABLE_OUTPUT
            -------------------------------------------------------------------------------------------------------------------------
            | 2 | NESTED LOOPS OUTER| | 6 | 120 | 251K (1)| 00:50:18 |
            |* 3 | TABLE ACCESS FULL| SCHEDULER$_EVENT_LOG | 6 | 90 | 251K (1)| 00:50:18 |
            |* 4 | INDEX UNIQUE SCAN| I_OBJ1 | 1 | 5 | 0 (0)| |
            --------------------------------------------------------------------------------------------

            Predicate Information (identified by operation id):
            ---------------------------------------------------

            3 - filter(("OWNER"=SYS_CONTEXT('USERENV','CURRENT_SCHEMA') AND "E"."TYPE#"=66))
            4 - access("E"."CLASS_ID"="CO"."OBJ#")


            22 rows selected.

            SQL> spool off;
            • 3. Re: Slow Count(*) response on USER_SCHEDULER_JOB_RUN_DETAILS
              spajdy
              First query -1s good enought
              Second query - 50s it is a little bit lazy.

              On my system ORACLE 11gR1
              select count(*) from USER_SCHEDULER_JOB_LOG                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
              -----------------------------------------------------------------------------------------------
              | Id  | Operation              | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
              -----------------------------------------------------------------------------------------------
              |   0 | SELECT STATEMENT       |                      |       |       |   311 (100)|          |
              |   1 |  SORT AGGREGATE        |                      |     1 |    31 |            |          |
              |*  2 |   HASH JOIN OUTER      |                      |  4214 |   127K|   311   (2)| 00:00:04 |
              |*  3 |    TABLE ACCESS FULL   | SCHEDULER$_EVENT_LOG |  4214 |   106K|   242   (2)| 00:00:03 |
              |   4 |    INDEX FAST FULL SCAN| I_OBJ1               | 38091 |   185K|    68   (2)| 00:00:01 |
              -----------------------------------------------------------------------------------------------
              Predicate Information (identified by operation id):
              ---------------------------------------------------
                 2 - access("E"."CLASS_ID"="CO"."OBJ#")
                 3 - filter(("OWNER"=SYS_CONTEXT('USERENV','CURRENT_SCHEMA') AND "E"."TYPE#"=66 AND
                            "E"."DBID" IS NULL))
              So I see than JOIN on mys system is done by HASH JOIN on your system by NESTED LOOP.
              But you say:
              USER_SCHEDULER_JOB_LOG and USER_SCHEDULER_JOB_RUN_DETAILS did gro to millions and purged them about a week ago.
              So FULL TABLE SCAN read lots of empty block.

              I suggest to reduce size of SCHEDULER$_EVENT_LOG table. But this table is part of ORACLE datadictionary. I'm not sure if it is save to shrink it or move it.
              You have to test this on some test DB.