This discussion is archived
1 2 Previous Next 29 Replies Latest reply: Nov 16, 2012 2:22 AM by Dom Brooks Go to original post RSS
  • 15. Re: CPU intensive query
    PavanKumar Guru
    Currently Being Moderated
    Hi,

    Can you check statstics (last updated date) table/index on all the segments which it was dealt across.

    - Pavan Kumar N
  • 16. Re: CPU intensive query
    user10274093 Explorer
    Currently Being Moderated
    Thank Pavan,
    SQL> select to_char(last_analyzed,'DD-MON-RR HH:MI AM') from all_tables where table_name in ('PS_PERSONAL_PHONE','PS4DEPT_TBL');
    
    TO_CHAR(LAST_ANALYZE
    --------------------
    08-NOV. -12 09:10 PM
    And on the both databases are the same.

    Regards.
  • 17. Re: CPU intensive query
    Dom Brooks Guru
    Currently Being Moderated
    At the minute, all we have are estimates.
    We need some actual numbers to compare to the estimates, metioned several times previously.

    It's difficult to tell whether the execution plan decisions are poor unless you can see if the estimates are accurate.

    If the statement and execution plan are too big for you to analyse, then simplify the investigation. Run a smaller part of the statement and see if you can compare actuals to estimates and the difference between environments.
  • 18. Re: CPU intensive query
    user10274093 Explorer
    Currently Being Moderated
    Pavan,
    really thank you.

    And thanks to all.
    Statistics was the problem.
    I ran :

    analyze table ps_job ESTIMATE STATISTICS ;

    And no more buffer sort and query is done in about 4 mins.

    Thanks to all.
    ---------------------------------------------------------------------------------------------                                               
    | Id  | Operation                         | Name               | Rows  | Bytes | Cost (%CPU)|                                               
    ---------------------------------------------------------------------------------------------                                               
    |   0 | SELECT STATEMENT                  |                    |     1 |   238 |   691   (0)|                                               
    |   1 |  TABLE ACCESS BY INDEX ROWID      | PS_NAME_PREFIX_LNG |     1 |    12 |     1   (0)|                                               
    |*  2 |   INDEX UNIQUE SCAN               | PS_NAME_PREFIX_LNG |     1 |       |     0   (0)|                                               
    |   3 |  TABLE ACCESS BY INDEX ROWID      | PS_PERSONAL_PHONE  |     1 |    29 |     2   (0)|                                               
    |*  4 |   INDEX UNIQUE SCAN               | PS_PERSONAL_PHONE  |     1 |       |     1   (0)|                                               
    |   5 |  TABLE ACCESS BY INDEX ROWID      | PS_EMAIL_ADDRESSES |     1 |    37 |     2   (0)|                                               
    |*  6 |   INDEX UNIQUE SCAN               | PS_EMAIL_ADDRESSES |     1 |       |     1   (0)|                                               
    |   7 |  SORT UNIQUE                      |                    |     1 |    24 |     8  (13)|                                               
    |   8 |   TABLE ACCESS BY INDEX ROWID     | PS_WKF_CNT_TYPE    |     1 |    24 |     3   (0)|                                               
    |*  9 |    INDEX RANGE SCAN               | PS_WKF_CNT_TYPE    |     1 |       |     2   (0)|                                               
    |  10 |     SORT AGGREGATE                |                    |     1 |    20 |            |                                               
    |* 11 |      INDEX RANGE SCAN             | PS_WKF_CNT_TYPE    |     1 |    20 |     2   (0)|                                               
    |* 12 |  INDEX RANGE SCAN                 | PS_WKF_CNT_TYPE    |     1 |    20 |     2   (0)|                                               
    |  13 |   SORT AGGREGATE                  |                    |     1 |    20 |            |                                               
    |* 14 |    INDEX RANGE SCAN               | PS_WKF_CNT_TYPE    |     1 |    20 |     2   (0)|                                               
    |* 15 |  TABLE ACCESS BY INDEX ROWID      | PS_DEPT_TBL        |     1 |    28 |     3   (0)|                                               
    |* 16 |   INDEX RANGE SCAN                | PS4DEPT_TBL        |     1 |       |     2   (0)|                                               
    |  17 |    SORT AGGREGATE                 |                    |     1 |    21 |            |                                               
    |* 18 |     INDEX RANGE SCAN              | PS_DEPT_TBL        |     1 |    21 |     2   (0)|                                               
    |* 19 |  TABLE ACCESS BY INDEX ROWID      | PS_DEPT_TBL        |     1 |    28 |     3   (0)|                                               
    |* 20 |   INDEX RANGE SCAN                | PS4DEPT_TBL        |     1 |       |     2   (0)|                                               
    |  21 |    SORT AGGREGATE                 |                    |     1 |    21 |            |                                               
    |* 22 |     INDEX RANGE SCAN              | PS_DEPT_TBL        |     1 |    21 |     2   (0)|                                               
    |* 23 |  TABLE ACCESS BY INDEX ROWID      | PS_DEPT_TBL        |     1 |    28 |     3   (0)|                                               
    |* 24 |   INDEX RANGE SCAN                | PS4DEPT_TBL        |     1 |       |     2   (0)|                                               
    |  25 |    SORT AGGREGATE                 |                    |     1 |    21 |            |                                               
    |* 26 |     INDEX RANGE SCAN              | PS_DEPT_TBL        |     1 |    21 |     2   (0)|                                               
    |* 27 |  TABLE ACCESS BY INDEX ROWID      | PS_DEPT_TBL        |     1 |    28 |     3   (0)|                                               
    |* 28 |   INDEX RANGE SCAN                | PS4DEPT_TBL        |     1 |       |     2   (0)|                                               
    |  29 |    SORT AGGREGATE                 |                    |     1 |    21 |            |                                               
    |* 30 |     INDEX RANGE SCAN              | PS_DEPT_TBL        |     1 |    21 |     2   (0)|                                               
    |  31 |  SORT AGGREGATE                   |                    |     1 |    28 |            |                                               
    |* 32 |   TABLE ACCESS BY INDEX ROWID     | PS_DEPT_TBL        |     1 |    28 |     8   (0)|                                               
    |* 33 |    INDEX RANGE SCAN               | PS_DEPT_TBL        |     1 |       |     7   (0)|                                               
    |  34 |     SORT AGGREGATE                |                    |     1 |    21 |            |                                               
    |* 35 |      INDEX RANGE SCAN             | PS_DEPT_TBL        |     1 |    21 |     2   (0)|                                               
    |* 36 |  TABLE ACCESS BY INDEX ROWID      | PS_DEPT_TBL        |     1 |    28 |     3   (0)|                                               
    |* 37 |   INDEX RANGE SCAN                | PS4DEPT_TBL        |     1 |       |     2   (0)|                                               
    |  38 |    SORT AGGREGATE                 |                    |     1 |    21 |            |                                               
    |* 39 |     INDEX RANGE SCAN              | PS_DEPT_TBL        |     1 |    21 |     2   (0)|                                               
    |  40 |  NESTED LOOPS                     |                    |     1 |   238 |    78   (0)|                                               
    |  41 |   NESTED LOOPS                    |                    |     1 |   212 |    76   (0)|                                               
    |  42 |    NESTED LOOPS                   |                    |     1 |   186 |    74   (0)|                                               
    |  43 |     NESTED LOOPS                  |                    |     1 |   112 |    73   (0)|                                               
    |  44 |      NESTED LOOPS                 |                    |     1 |    75 |    69   (0)|                                               
    |  45 |       NESTED LOOPS                |                    |     3 |   165 |    63   (0)|                                               
    |  46 |        TABLE ACCESS BY INDEX ROWID| PS_NAMES           |     3 |   120 |    60   (0)|                                               
    |* 47 |         INDEX SKIP SCAN           | PSANAMES           |     3 |       |    57   (0)|                                               
    |  48 |          SORT AGGREGATE           |                    |     1 |    19 |            |                                               
    |* 49 |           INDEX RANGE SCAN        | PS_NAMES           |     1 |    19 |     2   (0)|                                               
    |  50 |        TABLE ACCESS BY INDEX ROWID| PS_PERSON          |     1 |    15 |     1   (0)|                                               
    |* 51 |         INDEX UNIQUE SCAN         | PS_PERSON          |     1 |       |     0   (0)|                                               
    |  52 |       TABLE ACCESS BY INDEX ROWID | PS_PERS_DATA_EFFDT |     1 |    20 |     2   (0)|                                               
    |* 53 |        INDEX RANGE SCAN           | PS_PERS_DATA_EFFDT |     1 |       |     1   (0)|                                               
    |  54 |         SORT AGGREGATE            |                    |     1 |    15 |            |                                               
    |* 55 |          INDEX RANGE SCAN         | PS_PERS_DATA_EFFDT |     1 |    15 |     2   (0)|                                               
    |  56 |      TABLE ACCESS BY INDEX ROWID  | PS_JOB_CL_I        |     4 |   148 |     4   (0)|                                               
    |* 57 |       INDEX RANGE SCAN            | PS_JOB_CL_I        |     4 |       |     1   (0)|                                               
    |  58 |     TABLE ACCESS BY INDEX ROWID   | PS_JOB             |     1 |    74 |     1   (0)|                                               
    |* 59 |      INDEX UNIQUE SCAN            | PS_JOB             |     1 |       |     0   (0)|                                               
    |* 60 |       TABLE ACCESS BY INDEX ROWID | PS_JOB             |     1 |    20 |     3   (0)|                                               
    |* 61 |        INDEX RANGE SCAN           | PS_JOB             |     1 |       |     2   (0)|                                               
    |  62 |         SORT AGGREGATE            |                    |     1 |    14 |            |                                               
    |* 63 |          INDEX RANGE SCAN         | PSAJOB             |     7 |    98 |     2   (0)|                                               
    |  64 |         SORT AGGREGATE            |                    |     1 |    16 |            |                                               
    |* 65 |          INDEX RANGE SCAN         | PSAJOB             |     1 |    16 |     2   (0)|                                               
    |  66 |       SORT AGGREGATE              |                    |     1 |    14 |            |                                               
    |* 67 |        INDEX RANGE SCAN           | PSAJOB             |     7 |    98 |     2   (0)|                                               
    |  68 |       SORT AGGREGATE              |                    |     1 |    16 |            |                                               
    |* 69 |        INDEX RANGE SCAN           | PSAJOB             |     1 |    16 |     2   (0)|                                               
    |  70 |    TABLE ACCESS BY INDEX ROWID    | PS_DEPT_TBL        |     1 |    26 |     2   (0)|                                               
    |* 71 |     INDEX RANGE SCAN              | PS_DEPT_TBL        |     1 |       |     1   (0)|                                               
    |  72 |      SORT AGGREGATE               |                    |     1 |    21 |            |                                               
    |* 73 |       INDEX RANGE SCAN            | PS_DEPT_TBL        |     1 |    21 |     2   (0)|                                               
    |  74 |   TABLE ACCESS BY INDEX ROWID     | PS_JOBCODE_TBL     |     1 |    26 |     2   (0)|                                               
    |* 75 |    INDEX RANGE SCAN               | PS_JOBCODE_TBL     |     1 |       |     1   (0)|                                               
    |  76 |     SORT AGGREGATE                |                    |     1 |    20 |            |                                               
    |* 77 |      INDEX RANGE SCAN             | PS_JOBCODE_TBL     |     1 |    20 |     2   (0)|                                               
    ---------------------------------------------------------------------------------------------  
                                                                                                                                                
    Predicate Information (identified by operation id):                                                                                         
    ---------------------------------------------------                                                                                         
                                                                                                                                                
      
  • 19. Re: CPU intensive query
    Dom Brooks Guru
    Currently Being Moderated
    Statistics was the problem.
    Which implies that the estimates were inaccurate and that if you'd done any basic follow-up on cardinality feedback you might have noticed this.

    Or perhaps gathering statistics flushed the cache, parsed the statement with different bind variables, different plan but problem still exists.
    Might not be the case but could be
    analyze table ps_job ESTIMATE STATISTICS ;
    You should be using DBMS_STATS.

    Edited by: Dom Brooks on Nov 14, 2012 1:31 PM
  • 20. Re: CPU intensive query
    user10274093 Explorer
    Currently Being Moderated
    Dom,
    what is the exact syntax ?
    I thought that since 10 g , statistics are gathered by default. How to be sure if it is done ?

    Thanks again.
  • 21. Re: CPU intensive query
    Mohamed Houri Pro
    Currently Being Moderated
    what is the exact syntax ? 
    I thought that since 10 g , statistics are gathered by default. How to be sure if it is done ? 
    use as an example
    dbms_stats.gather_table_stats (user, 'table_name', cascade => true
    , method_opt => 'FOR ALL COLUMNS SIZE 1');
    See the following thread in which are presented few difference between analyse and dbms_stats.

    Re: Analyze vs dbms_stats

    Mohamed Houri
    www.hourim.wordpress.com
  • 22. Re: CPU intensive query
    user10274093 Explorer
    Currently Being Moderated
    Thank you.
    But my question
    - is how to verify if there are already the gathering statistics jobs on a schema ?

    And create the less source intensive for gathering statistics on a schema ?

    Regards.
  • 23. Re: CPU intensive query
    sb92075 Guru
    Currently Being Moderated
    user10274093 wrote:
    Thank you.
    But my question
    - is how to verify if there are already the gathering statistics jobs on a schema ?

    And create the less source intensive for gathering statistics on a schema ?
    why do you think that YOU can be more efficient than Oracle?
  • 24. Re: CPU intensive query
    jgarry Guru
    Currently Being Moderated
    user10274093 wrote:
    Thank you.
    But my question
    - is how to verify if there are already the gathering statistics jobs on a schema ?
    The easiest way is to use dbconsole to look at the jobs. There is a command line way also.

    You can use dbconsole to look at the objects and see when their statistics were gathered. If the auto job is running, it uses a staleness idea to decide when to collect statistics within the window. This can be silly. Your version also has a plan evaluation feature. http://docs.oracle.com/cd/E11882_01/server.112/e16638/stats.htm#i41282

    >
    And create the less source intensive for gathering statistics on a schema ?
    That becomes the more manually intensive "figure out the best statistics for each object and lock them," which some people advocate. Or the various plan stability features. There is plenty of room for debate about it.
    - 'PLAN_TABLE' is old version
    You should google how to fix this.
  • 25. Re: CPU intensive query
    PavanKumar Guru
    Currently Being Moderated
    Hi,

    Nice to know that, the issue is dealt with statistics and issue got resolved. Do follow the oracle doc for statistics collection based on version of your oracle database.
    Happy working with your database

    - Pavan Kumar N
  • 26. Re: CPU intensive query
    user10274093 Explorer
    Currently Being Moderated
    sb92075 wrote :
    
    
    why do you think that YOU can be more efficient than Oracle? 
    Oracle is efficient. But our tables were not automatically analyzed then not recent statistics and the result a CPU intensive query, some lost days just for that.
  • 27. Re: CPU intensive query
    Dom Brooks Guru
    Currently Being Moderated
    Oracle is efficient. But our tables were not automatically analyzed then not recent statistics and the result a CPU intensive query, some lost days just for that.
    The default stats join analyses tables when deemed necessary based on thresholds of modifications (default 10% ).

    Who knows what the critical change was, proper root cause analysis was avoided.
  • 28. Re: CPU intensive query
    user10274093 Explorer
    Currently Being Moderated
    Thank you Dom.

    Do you mean that in our case thresholds of modifications (default 10% ) was not enough ?

    How to change it to avoid problems in future ?
     
    SQL> SELECT client_name, status, mean_job_duration 
      2  FROM dba_autotask_client; 
    
    CLIENT_NAME                              STATUS   MEAN_JOB_DURATION 
    ---------------------------------------- -------- ------------------------------ 
    auto optimizer stats collection          ENABLED  +000000000 00:06:55.661290323 
    Regards.
  • 29. Re: CPU intensive query
    Dom Brooks Guru
    Currently Being Moderated
    Do you mean that in our case thresholds of modifications (default 10% ) was not enough ?
    How to change it to avoid problems in future ?
    We don't even know what the problem was.
    Do nothing until you understand what the actual problem was.
1 2 Previous Next

Legend

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