This discussion is archived
14 Replies Latest reply: Dec 4, 2012 2:19 AM by user522961 RSS

jobs run slowly

user522961 Newbie
Currently Being Moderated
Hi,
on 11g R2 on Win 2008. The jobs were slow last night between 7PM and 11PM. I run AWR and I have :
Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DB CPU                                            5,222          56.2
resmgr:cpu quantum                    8,190         860    105    9.3 Scheduler
db file sequential read              43,985         397      9    4.3 User I/O
db file scattered read                9,692         169     17    1.8 User I/O
direct path read                     13,689         107      8    1.1 User I/O
What is the main problem ? CPU ? Or db file sequential read ?

On the other hand , I have :
SQL> show parameter target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
archive_lag_target                   integer     0
db_flashback_retention_target        integer     1440
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     0
memory_max_target                    big integer 900M
memory_target                        big integer 900M
parallel_servers_target              integer     8
pga_aggregate_target                 big integer 240947670
sga_target                           big integer 0
It is because in init.ora I have :
pga_aggregate_target=240947670
I wonder if it would be better to delete this from init.ora to have pga_aggregate_target=0

Thank for help.
  • 1. Re: jobs run slowly
    Mohamed Houri Pro
    Currently Being Moderated
    Event                                 Waits     Time(s)   (ms)   time Wait Class
    ------------------------------ ------------ ----------- ------ ------ ----------
    DB CPU                                            5,222          56.2
    resmgr:cpu quantum                    8,190         860    105    9.3 Scheduler
    db file sequential read              43,985         397      9    4.3 User I/O
    db file scattered read                9,692         169     17    1.8 User I/O
    direct path read                     13,689         107      8    1.1 User I/O
    There is not enough information to make a conclusion. How long is the snapshot of this report? and did this report corresponds to the period where your jobs were experiencing poor perfomance? Remember that whatever the performance of your application is a AWR report will always present a Top 5 Timed Events.


    For the event resmgr:cpu quantum see the MetaLink Note: Large Waits With The Wait Event "Resmgr:Cpu Quantum" [ID 806893.1]
    it concerns the ressource manager

    For the CPU, you need to give us the number of the CPU of your system and the duration of the AWR reports

    Best regards

    Mohamed Houri
    www.hourim.wordpress.com

    Edited by: Mohamed Houri on 08-nov.-2012 4:48
  • 2. Re: jobs run slowly
    user522961 Newbie
    Currently Being Moderated
    Thank for reply.

    -For the CPU, number of the CPU of system : 1

    - the duration of the AWR reports : the jobs were slow from 7 PM to 9 PM. I chose the duration first snapshot at 7 PM and last at 11 PM.

    From AWR report :
    Host Name        Platform                         CPUs Cores Sockets Memory(GB)
    ---------------- -------------------------------- ---- ----- ------- ----------
    server   Microsoft Windows x86 64-bit        1     1       1       4.00
    
                  Snap Id      Snap Time      Sessions Curs/Sess
                --------- ------------------- -------- ---------
    Begin Snap:      1223 07-Nov. -12 19:00:5       47       2.9
      End Snap:      1227 07-Nov. -12 23:00:2       50       2.7
       Elapsed:              239.50 (mins)
       DB Time:              154.89 (mins)
    Regards.
  • 3. Re: jobs run slowly
    CKPT Guru
    Currently Being Moderated
    on 11g R2 on Win 2008. The jobs were slow last night between 7PM and 11PM. I run AWR and I have :
    This below AWR report belongs for 4 hours of snap? It is not a good practice. Just take an 30 minuets or 1 hour of AWR. If you took 4 hours of AWR snap then we may miss clues.

    >
    Event                                 Waits     Time(s)   (ms)   time Wait Class
    ------------------------------ ------------ ----------- ------ ------ ----------
    DB CPU                                            5,222          56.2
    resmgr:cpu quantum                    8,190         860    105    9.3 Scheduler
    db file sequential read              43,985         397      9    4.3 User I/O
    db file scattered read                9,692         169     17    1.8 User I/O
    direct path read                     13,689         107      8    1.1 User I/O
    What is the main problem ? CPU ? Or db file sequential read ?
    Are you using resource manager? However you can refer Houri mentioned MOS note.
    DB file sequential read can be expected for physical I/O's. So overall take a 1 hour max snap to generate AWR report.
    pga_aggregate_target=240947670
    I wonder if it would be better to delete this from init.ora to have pga_aggregate_target=0
    Why you want to delete it? you got chance to look at PGA target advisory and it shows any adequate results? (or) you can check by v$pga_target_advice
  • 4. Re: jobs run slowly
    Mohamed Houri Pro
    Currently Being Moderated
    Host Name        Platform                         CPUs Cores Sockets Memory(GB)
    ---------------- -------------------------------- ---- ----- ------- ----------
    server   Microsoft Windows x86 64-bit        1     1       1       4.00
     
                  Snap Id      Snap Time      Sessions Curs/Sess
                --------- ------------------- -------- ---------
    Begin Snap:      1223 07-Nov. -12 19:00:5       47       2.9
      End Snap:      1227 07-Nov. -12 23:00:2       50       2.7
       Elapsed:              239.50 (mins)
       DB Time:              154.89 (mins)
    As already mentioned by CKPT, that is not an AWR report from which you can make viable conclusions. 4 hours duration is enough long so that many crucial information are hidden because of averages done by Oracle. Get a shorter AWR period and analyse again.

    Best regards

    Mohamed Houri
    www.hourim.wordpress.com
  • 5. Re: jobs run slowly
    user522961 Newbie
    Currently Being Moderated
    Ok, I will run one for 1 hour.

    Here is the out put of :
    SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
           ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
           ESTD_OVERALLOC_COUNT
      FROM V$PGA_TARGET_ADVICE;
    
    TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT
    --------- -------------- --------------------
           34             80                 5374
           68             80                 5374
          136             81                 5133
          204             89                 1059
          272             98                  210
          326             99                   85
          381             99                   25
          435             99                    6
          490             99                    0
          544             99                    0
          816             99                    0
    
         1088             99                    0
         1632             99                    0
         2176             99                    0
    seems I do not need to change pga_aggregate_target.

    We do not use Resource manager. But how to verify if it is not enabled ?
  • 6. Re: jobs run slowly
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi,

    your database is CPU-starved. There are two pieces of evidence from your report. People's exhibit A:
    resmgr:cpu quantum                    8,190         860    105    9.3 Scheduler
    this event is a signature for CPU starvation.

    People's exhibit B:
    Event                                 Waits     Time(s)   (ms)   time Wait Class
    ------------------------------ ------------ ----------- ------ ------ ----------
    DB CPU                                            5,222          56.2
    4 hours x 1 CPU = 7,200 CPU seconds. Your database consumed 5,222 out of it, i.e. 72%. While this doesn't look like an extreme number, think about it this way: it could have been that your database server was at 100% for almost 3 hours out of 4 and at 0% during the remaining hour.

    So obviously, your problem #1 is CPU -- look at top SQL ordered by CPU time to find out who's responsible (and think of buying more CPUs, since 1 CPU is nonsense for a serious database).

    Best regards,
    Nikolay
  • 7. Re: jobs run slowly
    user522961 Newbie
    Currently Being Moderated
    Hi again.

    How to to check the threads from the Windows side with the OS, to pin point which thread exactly is utilizing the CPU if Oracle is running ?

    Thank you.
  • 8. Re: jobs run slowly
    jgarry Guru
    Currently Being Moderated
    You need to determine some things:

    Were the plans the jobs used different from the ones they normally used? If they were, they might use more cpu, leading to a resource constraint.

    Is the PGA adequate for the jobs? If not, you might consider using manual work areas for those jobs. You need to find out whether the memory usage is one-pass or multi-pass, and it could be different for plans that are wrong.

    There is a gross measure of memory usage (for example, a dbconsole screen that shows how many multi-pass operations), but you need to distinguish between normal operations, where it tells you about pga aggregates, and special things like batch jobs. You don't have to tune the system the same for both and be happy with neither. You are likely to be better off tuning the system for normal operations and using special settings when necessary.
  • 9. Re: jobs run slowly
    user522961 Newbie
    Currently Being Moderated
    Thank you.

    How to :
    -use manual work areas for those jobs ?
    - find out whether the memory usage is one-pass or multi-pass ?

    Thank you.
  • 10. Re: jobs run slowly
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi,

    if programs other than Oracle consume significant amounts of CPU, then you need OS-level tools to see what programs these are. I am not familiar with Windows system administration that well so I cannot advise you which tool to use -- I know Resource Manager is fine for troubleshooting ongoing problems, but I'm not sure if it has an option of recording performance history.

    But in your case it looks like it's Oracle that is taking all the CPU available. I don't see any need to pinpoint any threads. Just run an AWR report for the problem period and look at SQL ordered by CPU time.

    Best regards,
    Nikolay
  • 11. Re: jobs run slowly
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi
    jgarry wrote:
    You need to determine some things:

    Were the plans the jobs used different from the ones they normally used?
    Jobs are PL/SQL procedures. They don't have plans. Each procedure can have tens or hundreds SQL statements -- do you suggest that the OP manually go through each of the SQL's plan and see if it changed? What if it did -- does it necessarily mean it went bad?
    If they were, they might use more cpu, leading to a resource constraint.

    Is the PGA adequate for the jobs?
    Just a few days before the OP posted evidence that in fact yes, the PGA is adequate:
    pga_aggregate_target=240947670
    TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT
    --------- -------------- --------------------
    34 80 5374
    68 80 5374
    136 81 5133
    204 89 1059
    272 98 210
    326 99 85
    If not, you might consider using manual work areas for those jobs.
    On a CPU starved database that is more likely to make things worse, than better.
    You need to find out whether the memory usage is one-pass or multi-pass, and it could be different for plans that are wrong.
    He needs to address obvious issues first, before going on a wild goose chase.

    Best regards,
    Nikolay
  • 12. Re: jobs run slowly
    jgarry Guru
    Currently Being Moderated
    Nikolay Savvinov wrote:
    Hi
    jgarry wrote:
    You need to determine some things:

    Were the plans the jobs used different from the ones they normally used?
    Jobs are PL/SQL procedures. They don't have plans. Each procedure can have tens or hundreds SQL statements -- do you suggest that the OP manually go through each of the SQL's plan and see if it changed? What if it did -- does it necessarily mean it went bad?
    Well, that's the most common cause of sudden performance changes. We don't know how many the procedures have. I'm not disagreeing with you, just saying sometimes the dumb things stand out.

    >
    If they were, they might use more cpu, leading to a resource constraint.

    Is the PGA adequate for the jobs?
    Just a few days before the OP posted evidence that in fact yes, the PGA is adequate:
    pga_aggregate_target=240947670
    TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT
    --------- -------------- --------------------
    34 80 5374
    68 80 5374
    136 81 5133
    204 89 1059
    272 98 210
    326 99 85
    If not, you might consider using manual work areas for those jobs.
    On a CPU starved database that is more likely to make things worse, than better.
    I was pointing out the difference between looking at overall usage (this evidence), as opposed to special batchy things. Is that wrong? Am I wrong in assuming the batch jobs are not during normal operations? I was attempting to say "no, don't set pga target to 0."

    >
    You need to find out whether the memory usage is one-pass or multi-pass, and it could be different for plans that are wrong.
    He needs to address obvious issues first, before going on a wild goose chase.
    Some things seem obvious to me that don't to others. That's fine by me. You give good advice, well worth following. I have no problem being corrected. I don't see any correction here though.

    I do think we agree there is a problem with the sql. I agree that ordering by cpu and looking at that sql is a reasonable avenue on which to proceed. But I definitely don't agree that you should consider buying hardware before finding and fixing the problem.

    >
    Best regards,
    Nikolay
  • 13. Re: jobs run slowly
    jgarry Guru
    Currently Being Moderated
    user522961 wrote:
    Thank you.

    How to :
    -use manual work areas for those jobs ?
    - find out whether the memory usage is one-pass or multi-pass ?

    Thank you.
    Please note, you should carefully check and understand any mysterious code people tell you to download from the intertubes. That said, I googled oracle multipass operations, and found this:
    set lines 180
    set pages 66
    SELECT S.SQL_FULLTEXT,
           W.OPERATION_TYPE,
           W.ESTIMATED_OPTIMAL_SIZE,
           W.ESTIMATED_ONEPASS_SIZE,
           W.LAST_MEMORY_USED,
           W.LAST_EXECUTION,
           W.OPTIMAL_EXECUTIONS,
           W.ONEPASS_EXECUTIONS,
           W.MULTIPASSES_EXECUTIONS,
           W.LAST_TEMPSEG_SIZE,
           W.MAX_TEMPSEG_SIZE
      FROM V$SQL_WORKAREA W, V$SQL S
     WHERE     W.ADDRESS = S.ADDRESS
           AND W.HASH_VALUE = S.HASH_VALUE
           AND W.CHILD_NUMBER = S.CHILD_NUMBER
           AND (W.ONEPASS_EXECUTIONS > 0 OR W.MULTIPASSES_EXECUTIONS > 0);
    Ignoring my own advice, I've hardly glanced at it, but it seemed to work on my system.

    You can alter a session to set WORKAREA_SIZE_POLICY to MANUAL, and set sort_area_size and hash_area_size. You can figure those out by looking the v$process parameters that start with pga when you are in your testing environment. The assumptions here are that you've figured out the correct plan, and the normal pga size causes the sql in the jobs to put out multipass executions in the above script. You don't want to turn off automatic pga, though it can burn you if you set it too high - and sometimes just adding one more user can push it to be too high, so that's why you run the advisors to see.

    So, you need to figure out if the sql in your jobs are using the correct plan, if so are they using it efficiently. As Nikolai pointed out, this answer could be the exact opposite of what you want to do, as sometimes you want things to be pushed towards using slower i/o rather than faster memory, as the latter could cause more pounding on the scarce cpu resource (but then again, i/o needs cpu too...). It's entirely possible that the sudden slower job is due to Oracle saying "oh, this job would be better costed if I did more stuff in memory, which this plan would do." Oracle is supposed to take this into account, but... Sometimes you can fix such things by temporarily setting back statistics to an earlier set, sometimes you need to hint the code, sometimes you can play with plan stability, sometimes some completely different thing in the OS is actually at fault. So you have to follow the clues starting with a reasonable length awr. If it is indeed the SQL, follow the directions in the "how to post a performance request" directions.
  • 14. Re: jobs run slowly
    user522961 Newbie
    Currently Being Moderated
    Thanks.

Legend

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