This discussion is archived
3 Replies Latest reply: Nov 29, 2012 1:17 AM by spajdy RSS

Slow Count(*) response on USER_SCHEDULER_JOB_RUN_DETAILS

975567 Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points