This discussion is archived
1 2 3 Previous Next 44 Replies Latest reply: May 15, 2008 6:31 AM by 108476 Go to original post RSS
  • 15. Re: excessive LOCAL=NO oracle processes
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    > 100% utilization is the optimal state.

    Incorrect. It alone indicates a percentage usage. It does not indicate whether that usage is optimal or whether it caused by a process load demand that cannot be met.

    Just as 0% CPU utilisation alone does not state whether there is no demand (all processes idle, waiting for work) or a huge I/O problem (all process having work to do, but idle and waiting on the I/O subsystem to deliver the data to work on).

    If you meant that 100% CPU alone is not an indication of a problem and can indicate an optimal state, then that I can agree with. But if this is a blanket statement that is always optimal (as I read and understood your response), then that is most definitely incorrect.

    PS. If your statement is intended to be a theoretical response in that "on a perfect system a 100% CPU utilisation is optimal", then note that we're not dealing with theory or perfect systems here.

    PPS. Your personal attacks on Jonathan is unwarranted (but given your typical knee-jerk reactions, very much expected). How about this? Argue the topic - not the person.
  • 16. Re: excessive LOCAL=NO oracle processes
    108476 Journeyer
    Currently Being Moderated
    First you pretend to be a scientist and now you pretend to be an engineer?
    1) Any piece of text, even if it's a note written 8 years ago by a front-line Oracle support analyst about an old version of Oracle, for example, is evidence;
    Yes, CPU dispatching was optimized years ago, there is nothing new about scheduling and dispatching tasks in an SMP environment.

    2) A piece of text that's been included in the ADDM after careful though by some high-level designers, architects and programmers is only there to take part in 'silly mistakes'.

    Remember that POS that Oracle called "Oracle Expert", the precursor to ADDM?

    http://download.oracle.com/docs/cd/B10500_01/em.920/a86647/using.htm

    Unlike you, I've actually written expert systems and decision support systems (and had my work published in academic journals), and I know firsthand what a huge challenge it is to incorporate decision rules that will always make correct recommendations.

    My own tool, RPM, does a far better job than ADDM at preventative monitoring, and that's why people line-up to use it . . . .
    I don't think there's any need to continue this conversation
    Agreed, not until you fess-up to not have any relevent training, knowledge or experience in engineering CPU architectures.

    ****************************************************
    all they need to know about the value of your original suggestion.
    We ARE NOT talking opinions here.

    Gaja says that you are prepetuating an Oracle myth. That says it all:

    "One of the classic myths about CPU utilization is that a system with 0 percent idle is categorized as a system undergoing CPU bottlenecks...

    It is perfectly okay to have a system with 0 percent idle, so long as the average runnable queue for the CPU is less than (2 x number of CPUs)."

    As I note on all of my web pages:

    "Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications."

    You are clearly an expert in CBO, but you DO NOT appear to have expertise in many other Oracle areas like DBA, and pretending to be something that you are not (a scientist, an engineer) is yet another sign of narccissm:

    http://www.dba-oracle.com/t_narcissistic_personality_disorders.htm

    Lewis, folks who claim to be an expert in everything are generally called a "legend in their own mind". . . . .

    Message was edited by:
    burleson
  • 17. Re: excessive LOCAL=NO oracle processes
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    > Lewis, folks who claim to be an expert in everything are generally called a "legend in their own
    mind". . . .

    .. and ..

    > Unlike you, I've actually written expert systems and decision support systems (and had my work
    published in academic journals), and I know firsthand what a huge challenge it is to incorporate
    decision rules that will always make correct recommendations.

    Seems to me to be very much the case of the pot calling the kettle black.

    Burleson, folks who claims always to be the expert and attempt to belittle and question the qualifications and experiences of their peers, are generally called a "legend in their own mind".
  • 18. Re: excessive LOCAL=NO oracle processes
    108476 Journeyer
    Currently Being Moderated
    Hi Billy,

    First. I'm not an engineer, and I don;t pretend to be. I rely on verified experts, people who are not afraid to publish their credentials.
    If you meant that .
    Yes, that's what I mean to say, I'll check my semantics. I like yours best, may I quote you?

    *****************************************************
    then note that we're not dealing with theory or perfect systems here.
    Agreed, good point.

    ****************************************************
    Your personal attacks on Jonathan is unwarranted
    Hey, he started the attack, not me! He popped in here and arrogantly pontificated on something he has no knowledge about, that's just wrong . . . . .

    Treat me with respect, and I will be respectful back, that's my credo.
  • 19. Re: excessive LOCAL=NO oracle processes
    108476 Journeyer
    Currently Being Moderated
    Hi Billy,
    question the qualifications and experiences of their peers
    But, questioning authority is a good thing:

    http://www.google.com/search?&q=oracle+%22question+authority%22

    "It taught me to question experts, to question authority figures. Don't assume they're right just because they're in authority, or just because they're experts." - Larry Ellison

    After Larry said this, a whole bunch of Oracle employees copied his mantra. Rumpkissers, yes, but it's a good rule to follow . . . . .

    ****************************************************
    attempt to belittle and question the qualifications and experiences of their peers
    Whoa, Lewis is NOT my peer. I'm a working DBA, he is not.

    To my knowledge, Lewis has never published his credentials, education, experience, nor qualifications.

    There is nothing to belittle.
  • 20. Re: excessive LOCAL=NO oracle processes
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Seems like this thread has been hijacked again.. as is sometimes the case here..

    Anyway, to try and give basic and clear answers to your questions.

    > 1) When trying to run application, database hangs due to excessive amount of processes:
    oracle oracle (LOCAL=NO). How to kill those processes? Why these much processes are generated
    automatically?

    These are not "caused" by Oracle. Every client connection made, needs to be serviced by an Oracle session. Oracle supports two service model in terms of client-server architecture. And these can be mixed.

    The default is a dedicated server model. Which means each client connection to the database is serviced by a dedicated server process (also called a shadow process - as in "the shadow of the client on the server"). A 100 client connections? A 100 dedicated (shadow) processes.

    The optional model is the shared server pool one. Typically found these days in application servers. With this architecture, a pool of processes exist - the first free (idle) process in the pool will serve the next client request. In the Oracle context, this is usually well suited for OLTP applications.

    What you are seeing are likely shadow servers though. And each of these is responding to a client connection. Whether or not these server processes are idle or busy, can be determined by looking at the V$SESSION virtual (performance) view in Oracle.

    Note that a single process can cause 100% CPU utilisation on a single CPU. (ignoring Posix threads as Oracle does not use these on Unix flavours)

    > 2) While running prstat -a command on Sun Solaris 10, we get 100% CPU utilization by oracle
    user.

    100% CPU utilisation is just a usage number. It alone does not indicate good or bad.

    > At the same time, running top command gives us 5G free physical memory. What is the real
    memory usage in the same server by oracle?

    That largely depends on how you have configured Oracle - the size of the SGA, the number and sizes of the PGAs, etc.

    Suggest that you consult the [url http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/toc.htm]Oracle® Database Concepts manual. Both the processing and memory architecture of the Oracle server (instance) is very well explained there.
  • 21. Re: excessive LOCAL=NO oracle processes
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Not going to bother to respond to your responses, but to say:

    How about in future argue the subject matter and leaving your opinions (and URLs to your opinions) about the poster, his experience and qualification (or lack thereof) aside? Keep it technical. Not personal.

    None of us (and I do speak for the vast majority of the forum members here) are NOT interested in personal tiffs - we are here to share and consume technical knowledge.
  • 22. Re: excessive LOCAL=NO oracle processes
    311441 Employee ACE
    Currently Being Moderated
    If so, please post them, because, not being an
    engineer myself, I reply on "real" experts. My
    engineers (with degrees in electrical and computer
    engineering from great universities) say that you
    don't know what you are talking about.
    Hi Don

    OK, so are you now claiming when you originally said in this thread:

    system statistics not being used

    "BTW, it's normal to see CPU at 100%, it's designed that way."

    this was not so much your "expert" opinion but that of your "engineers" ?

    That being the case, you might want to ask them why the IBM quote you mentioned in the same thread contradicts their claim:

    "Optimum use would have the CPU working 100 percent of the time. This holds true in the case of a single-user system with no need to share the CPU. Generally, if us + sy time is below 90 percent, a single-user system is not considered CPU constrained. However, if us + sy time on a multiuser system exceeds 80 percent, the processes may spend time waiting in the run queue. Response time and throughput might suffer."

    Note the bit about only holding true in single-user environment with no need to share CPU.

    Note the bit about it needing to be below 90 percent even for a single user system for it not to be considered CPU constrained.

    Note the bit about how on a multiuser system if exceeds 80 percent, response times and throughput might suffer.

    Do you really believe it's normal to see CPU at 100%, really ?

    Cheers

    Richard Foote
    http://richardfoote.wordpress.com/
  • 23. Re: excessive LOCAL=NO oracle processes
    108476 Journeyer
    Currently Being Moderated
    "BTW, it's normal to see CPU at 100%, it's designed that way." this was not so much your "expert" opinion but that of your "engineers" ?
    Just my own observations of real-world server environments. . . .

    But to answer your question, yes, I have extensive experience in monitoring servers. I took several upper-division computer science classes in college, and monitoring Oracle servers was my full-time job for several years.

    How about you? Do you have any experience with this? Are you trained in engineering?
    Do you really believe it's normal to see CPU at 100%, really ?
    Yup. I support hundreds of servers, and I see it every day.

    Check your own servers. I'll bet you see 100% utilization.

    Haven't you ever seen a DBA newbie getting concerned when they see 100% CPU utilization in glance?

    We have to define what "100% CPU" means, however. I am referring to 100% CPU as seen at the OS level, not within Oracle:

    http://www.dba-oracle.com/t_monitoring_unix_cpu.htm

    Remember, "utilization" only means "busy", not bottlenecked . . . .
  • 24. Re: excessive LOCAL=NO oracle processes
    108476 Journeyer
    Currently Being Moderated
    Hi Billy,
    his experience and qualification (or lack thereof) aside? Keep it technical.
    Experience and qualifications are part-and-parcel of any technical discussion.

    If someone says with authority "it works this way", it becomes an issue of credibility, nothing mnore.
    None of us . . . are NOT interested in personal tiffs
    Ah, duly noted (and remarks removed). . . .

    Message was edited by:
    burleson
  • 25. Re: excessive LOCAL=NO oracle processes
    311441 Employee ACE
    Currently Being Moderated
    Hi Don

    Again the quote:

    "Optimum use would have the CPU working 100 percent of the time. This holds true in the case of a single-user system with no need to share the CPU. Generally, if us + sy time is below 90 percent, a single-user system is not considered CPU constrained. However, if us + sy time on a multiuser system exceeds 80 percent, the processes may spend time waiting in the run queue. Response time and throughput might suffer."

    Note the bit about only holding true in single-user environment with no need to share CPU.

    Note the bit about it needing to be below 90 percent even for a single user system for it not to be considered CPU constrained.

    Note the bit about how on a multiuser system if exceeds 80 percent, response times and throughput might suffer.

    Do you have any comments regarding what the IBM quote is actually stating ?

    Interestingly, when our AIX servers are at their usual 60-75% load, general database performance is fine. On the odd occasion when an application inefficiency sneaks through the testing process and causes our CPUs to max out, not only are the processes in question problematic but the overall performance of the system suffers and everyone complains about response times.

    Funny that ...

    Cheers

    Richard Foote
    http://richardfoote.wordpress.com/
  • 26. Re: excessive LOCAL=NO oracle processes
    108476 Journeyer
    Currently Being Moderated
    Interestingly, when our AIX servers are at their usual 60-75% load
    Ah, here is the core issue, you compare CPU load to CPU utilization.

    They are different metrics, Richard!
    us + sy time on a multiuser system exceeds 80 percent
    Ah, "CPU Time" is VERY DIFFERENT from "CPU utilization"!!!!

    Actually, I use usr+sys > 70 for my own alerts, check it out:

    http://www.dba-oracle.com/t_oracle_unix_linux_vmstat_capture.htm
    Wed Dec 20                                                             page    1
                                   user+system > 70%
                 Indicates periods with a fully-loaded CPU sub-system.
                        Periods of 100% utilization are only a
          concern when runqueue values exceeds the number of CPUs on the server.

    SERVER_NAME          date       hour      runq pg_in pg_ot  usr  sys  idl      
    -------------------- -------------------- ---- ----- ----- ---- ---- ----      
    AD-01                01/12/13    14          0     0     2   75    2   22      
    AD-01                01/12/13    17          3     0     0   87    5    8      
    AD-01                01/12/15    15          0     0     0   50   29   22      
    AD-01                01/12/15    16          0     0     0   48   33   20      
    AD-01                01/12/19    07          0     0     0   77    4   19      
    AD-01                01/12/19    10          0     0     0   70    5   24      
    AD-01                01/12/19    11          1     0     0   60   17   24      
    PROD1                01/12/19    12          0     0     1   52   30   18      
    PROD1                01/12/19    13          0     0     0   39   59    2      
    PROD1                01/12/19    14          0     0     0   39   55    6      
    PROD1                01/12/19    15          1     0     0   57   23   20 
  • 27. Re: excessive LOCAL=NO oracle processes
    311441 Employee ACE
    Currently Being Moderated
    Interestingly, when our AIX servers are at their
    usual 60-75% load

    Ah, here is the core issue, you compare CPU load to
    CPU utilization.

    They are different metrics, Richard!
    us + sy time on a multiuser system exceeds 80
    percent

    Ah, "CPU Time" is VERY DIFFERENT from "CPU
    utilization"!!!!

    Actually, I use usr+sys > 70 for my own alerts, check
    it out:

    http://www.dba-oracle.com/t_oracle_unix_linux_vmstat_c
    apture.htm
    Hi Don

    OK, please educate me.

    Based on this report you posted, can you please show me what the "CPU Load" equates to ?

    Can you then please show me what the "CPU utilisation" equates to and how it differs?

    Finally, can you please show me how the CPU in the report is at the "usual" 100% that it's designed to be if this is only meant to be some kinda exception report ?

    Cheers

    Richard Foote
    http://richardfoote.wordpress.com/
  • 28. Re: excessive LOCAL=NO oracle processes
    ji li Pro
    Currently Being Moderated
    I also monitor several servers (as I have done for several years in the past), and I regularly see one or more CPUs at 100%. This is every day normal stuff.

    Try running gzip --best on a 30 Gb file and take a look at the CPU.  Try running export or import and notice the cpu.

    When we run 'compute burden', or 'compute payroll', it is common for one or more CPUs to be at 100%.
  • 29. Re: excessive LOCAL=NO oracle processes
    108476 Journeyer
    Currently Being Moderated
    Hi Ji,
    I regularly see one or more CPUs at 100%. This is every day normal stuff.
    Yes, obviously!

    As you state, any real-world working DBA would know this . . . .