1 2 3 Previous Next 44 Replies Latest reply: May 15, 2008 8:31 AM by 108476 RSS

    excessive LOCAL=NO oracle processes

    622296
      Hi

      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?

      2) While running prstat -a command on Sun Solaris 10, we get 100% CPU utilization by oracle user. 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?
        • 1. Re: excessive LOCAL=NO oracle processes
          108476
          Hi Swati,
          database hangs due to excessive amount of processes: oracle oracle (LOCAL=NO).
          How do you mean "hangs"? These are legitimate processes not using the bequeath protocol.
          While running prstat -a command on Sun Solaris 10, we get 100% CPU utilization by oracle user.
          100% utilization is the optimal state. If you want to look for CPU bottlenecks, use vmstat and check the "r" (runqueue) column:

          http://www.dba-oracle.com/t_high_cpu.htm
          • 2. Re: excessive LOCAL=NO oracle processes
            orafad
            1) When trying to run application, database hangs due
            to excessive amount of processes:
            How did you arrive at that conclusion and how many is excessive?
            oracle oracle (LOCAL=NO). How to kill those
            processes? Why these much processes are generated
            automatically?
            Probably regular server processes (appl. sessions). How many? How many sessions show up via select from v$session?
            What is the real memory usage in the
            same server by oracle?
            Well, for an estimate, you could take a look at memory related instance parameters (for e.g. SGA settings), number of server processes, and perhaps some amount for file system cache (in case of "double buffering").
            • 3. Re: excessive LOCAL=NO oracle processes
              Jonathan Lewis
              How do you mean "hangs"? These are legitimate
              processes not using the bequeath protocol.
              Well done for questioning the use of the term "hangs", but ...

              >
              100% utilization is the optimal state.
              No. The person is trying to run a multi-user system, repeating this "100% optimal" claim does not make it come true. See: Re: system statistics not being used .


              Jonathan Lewis
              http://jonathanlewis.wordpress.com
              http://www.jlcomp.demon.co.uk
              • 4. Re: excessive LOCAL=NO oracle processes
                277993
                If it is really hanging, then check your alert log for any related errors.

                If you are simply hitting maximum number of processes exceed error, then you can either increase your processes init parameters, or debug your application config to ensure that it is not making excessive conections to the database(e.g. if it is not using the connection pool you have defined).
                • 5. Re: excessive LOCAL=NO oracle processes
                  Jonathan Lewis
                  user959,

                  (LOCAL=NO) are usually oracle processes that are 'shadow' processes for end-user programs coming in across the network, and running at 100% would usually indicate some inefficient SQL coming into the system from those users. But from your 'generated automatically' question it sounds as if you aren't expecting to see any end-users talking to your database.

                  I think we need a little more background to your system before we can offer any intelligent comment.

                  Regards
                  Jonathan Lewis
                  http://jonathanlewis.wordpress.com
                  http://www.jlcomp.demon.co.uk
                  • 6. Re: excessive LOCAL=NO oracle processes
                    108476
                    100% utilization is the optimal state.
                    No.
                    Yes, it is.

                    Tell us, do you have ANY qualifications in science or engineering to back-up this claim? Even one class? Ever?

                    If so, please post them, because, not being an engineer myself, I rely 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.

                    claim does not make it come true.
                    It's not a claim, it's a fact, according to the folks who built their servers!

                    The vendors who build the servers say that 100% CPU utilization is optimal, and they wrote both the OS and the underlying hardware.

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

                    Lewis, "using" all of your CPU is a good thing, and it DOES NOT mean that there is a problem.

                    Please note that it is not uncommon to see the CPU approach 100 percent even when the server is not overwhelmed with work. This is because the UNIX internal dispatchers will always attempt to keep the CPUs as busy as possible. This maximizes task throughput, but it can be misleading for a neophyte.

                    The OS does not provide full CPU metrics to Oracle's executing tasks (it would slow-down the processors), so by definition, Oracle's "perception" of CPU is limited by what the external environment can offer. Oracle may only be a small percentage of the CPU load, and CPU utilization is wholly external to the instance.

                    Message was edited by:
                    burleson
                    • 7. Re: excessive LOCAL=NO oracle processes
                      Charles Hooper
                      While running prstat -a command on Sun Solaris 10,
                      we get 100% CPU utilization by oracle user.

                      100% utilization is the optimal state. If you want to
                      look for CPU bottlenecks, use vmstat and check the
                      "r" (runqueue) column:
                      Hello Mr. Burleson,

                      In another thread you asked that I site references to books or articles that suggest that CPU utilization at 100% is not normal, and that some sources suggest that utilization above 90% may cause excessive latching, and excessive time on LOG FILE SYNC waits, and other problems. In that thread, I provided ten references to books and Oracle documentation that seemed to indicate that 100% CPU utilization is not optimal in a multi-user system. I stated that I would also check Cary Millsap's book to see if it mentions anything related to queuing theory and high utilization.

                      Interestingly, "Oracle Performance Tuning 101" (2001) by Gaja Vaidyanatha states:
                      "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)."

                      The above quote seems to at least partially support your suggestion.

                      However, just a handful references that state 100% utilization is not optimal:
                      "Optimizing Oracle Performance" page 264, by Cary Millsap:
                      http://books.google.com/books?id=mvJW6t7mYU0C&pg=PA264&lpg=PA264&dq=bad+cpu+utilization+targets+millsap&source=web&ots=p3tlPqhbj3&sig=MNBEc9CbNeGzq5TLuK0zNB_mA8M&hl=en
                      "But be careful: pegging CPU utilization at 100% over long periods often causes OS scheduler thrashing, which can reduce throughput. On interactive-only systems, CPU utilization that stays to the right of the knee over long periods is bad. The goal of an interactive-only system user is minimized response time. When CPU utilization excees the knee in the response time curve, response time fluctuations become unbearable."

                      "Forecasting Oracle Performance" page 71 by Craig Shallahamer:
                      http://books.google.com/books?id=456LnTeHhWcC&pg=PA71&dq=oracle+cpu+utilization+bottleneck+90%25&ei=RSMeSLUckaiyA5et-eoG&sig=4VARYwUTQmE_ChIW21YrCHvPInw
                      Page 71) "With the CPU subsystem shown in Figure 3-7, queuing does not set in (that is response time does not significantly change) until utilization is around 80% (150% workload increase). The CPU queue time is virtually zero and then skyrockets because there are 32 CPUs. If the system had fewer CPUs, the slope, while still steep, would have been more gradual."

                      "Forecasting Oracle Performance" page 195 by Craig Shallahamer:
                      "The high-risk solution would need to contain at least 22 CPUs. Because the reference ratios came from a 20 CPU machine, scalability is not significant. However, recommending a solution at 75% utilization is significant and probably reckless. At 75% utilization, the arrival rate is already well into the elbow of the curve. It would be extremely rare to recommend a solution at 75% utilization."

                      Metalink Note:148176.1 Diagnosing hardware configuration induced performance problems (very short snippet):
                      "In general your utilization on anything should never be over 75-80%..."

                      Mr. Burleson quoting an IBM document here:
                      Re: system statistics not being used
                      "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."
                      Original source:
                      http://www.ncsa.uiuc.edu/UserInfo/Resources/Hardware/IBMp690/IBM/usr/share/man/info/en_US/a_doc_lib/aixbman/prftungd/2365c61.htm

                      The question then becomes, who should be believed regarding excessive CPU utilization. Should it be a book from 2001, a book from 2007, an IBM document, various Oracle documentation, or some other source? The quote that you provided from the IBM document seems to support the suggestion that exceeding 80% CPU utilization in a multi-user system is not a good idea.

                      Charles Hooper
                      IT Manager/Oracle DBA
                      K&M Machine-Fabricating, Inc.
                      • 8. Re: excessive LOCAL=NO oracle processes
                        Jonathan Lewis
                        >
                        Does your narcissism know no bounds?

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


                        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.

                        It's not a claim, it's a fact, according to the folks
                        who built their servers!

                        The vendors who build the servers say that 100% CPU
                        utilization is optimal, and they wrote both the OS
                        and the underlying hardware.

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

                        Now I ask you, since you hide all evidence of having
                        any real-world job experience in this area, not to
                        mention your unsavory history of introducing bias
                        into your phony "proofs" to falsely discredit real
                        experts.

                        Why should anyone believe you?
                        Don,

                        Of course you were bound to respond with ad hominem attacks, innuendo, and appeals to invisible authority figures.

                        You presented what you thought were supporting technical documents in Re: system statistics not being used. But as I pointed out there, both the documents you quoted actually explained why your '100% is optimal" is wrong.

                        Here are a couple of thoughts for you, however:

                        First: check the following <a href="http://jonathanlewis.wordpress.com/2008/05/10/cpu-usage/">simple example of how wrong you can be</a> in saying {"using" all of your CPU is a good thing} especially in a multi-user, shared memory environment such as an active Oracle instance. You see, Don, although "using" all of your CPU may be desirable if you don't waste any of it, in a multi-user system you can waste a lot of CPU very easily - even when nobody goes off the run queue.

                        Secondly: you love quoting Metalink and dredging up any Oracle documents, no matter how old, that might offer the slightest hint that you are right about one of your assertions; you love quoting Oracle marketing material as "proof" that one of your ideas must be good because Oracle mentions some planned feature that you can claim mimics your idea.

                        So why is it so hard to notice that Oracle's latest aid to performance automation, the ADDM report, makes comments like the following:
                        FINDING 1: 100% impact (1585 seconds)
                        -------------------------------------
                        Host CPU was a bottleneck and the instance was consuming 52% of the host CPU.
                        All wait times will be inflated by wait for CPU.
                        
                        ADDITIONAL INFORMATION: Host CPU consumption was 88%.  
                        
                        CPU runqueue statistics are not available from the host's OS. 
                        This disables ADDM's ability to estimate the impact of this finding.
                        The CPU is running at only 88%, and ADDM warns you about it being a possible threat !
                        So much for 100% being normal or desirable or optimal.



                        Jonathan Lewis
                        http://jonathanlewis.wordpress.com
                        http://www.jlcomp.demon.co.uk
                        • 9. Re: excessive LOCAL=NO oracle processes
                          108476
                          Hi Charles,
                          The question then becomes, who should be believed regarding excessive CPU utilization.
                          Good! Thank you!

                          - It's not about opinion
                          - It's not about Oracle
                          - It's not about "word games"

                          It's about that the metric "100% CPU utlization" means. I would think that the most authoritative cite would be a platform where the same vendor made the OS and the hartdware, that's why I chose the IBM AIX citation.

                          Also, notes that in UNIX/Linux, all CPU metrics are measured the same, regardless of hardware, so perhaps the best citations would be those for the top. sar, watch, glance and vmstat utilities.

                          I did some research on this:

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

                          Interestingly, even the server load averages are incomplete, as they do not compute with every tick.
                          some sources suggest that utilization above 90% may cause excessive latching, and excessive time on LOG FILE SYNC waits, and other problems.
                          But Oracle does not have direct access to the processor dispatching throttles!

                          It's an instrumentation issue, and it's all about how Oracle captures the CPU consumption information from the OS.

                          Remember, unless you are employing a vmware or processor affinity tool, any instance shares the same pool of CPU resources as the application and dozens of other instances.

                          - It is possible to have a "server-side" CPU bottleneck (runqueue > cpu count), and the instance is not even aware of it.

                          - It is possible that an instance may bee experiencing a "application side" CPU bottleneck, even though the OS runqueues are low.
                          Metalink Note:148176.1 Diagnosing hardware configuration induced performance problems (very short snippet): "In general your utilization on anything should never be over 75-80%..."
                          Interesting. How does Oracle "percieve" it's own CPU consumption? He has no idea where his processes are in the runqueue, no idea of their relative dispctching priority, only end-to-end service times.

                          I ask you Charles, how can Oracle "know" whether the CPU's are twiddling their registers at 100%?
                          • 10. Re: excessive LOCAL=NO oracle processes
                            108476
                            you love quoting Metalink and dredging up any Oracle documents, no matter how old, that might offer the slightest hint that you are right about one of your assertions;
                            Yeah, it's called "evidence". Got any?

                            Every 1st year DBA noob panics at some point when they go into top and see that the CPU is at pegged at 100%.

                            Neither you or I are qualified computer engineers, so how about we find some, and cite them?
                            • 11. Re: excessive LOCAL=NO oracle processes
                              108476
                              The CPU is running at only 88%, and ADDM warns you about it being a possible threat ! So much for 100% being normal or desirable or optimal.
                              In my experience ADDM commonly make silly recommendations, ridiculous or dead wrong, and ADDM makes even more ludicrous suggestions that those that I hear from you. They should have named it JONATHAN.

                              ADDM makes lotsa funny mistakes, all the time, . . . Consider:

                              GOOD - Instance consuming 52% of CPU (Oracle the only thing on the server)

                              BAD - Instance consuming 52% of CPU (43 instances on the server)

                              Message was edited by:
                              burleson to remove error.
                              • 12. Re: excessive LOCAL=NO oracle processes
                                Jonathan Lewis
                                you love quoting Metalink and dredging up any
                                Oracle documents, no matter how old, that might offer
                                the slightest hint that you are right about one of
                                your assertions;

                                Yeah, it's called "evidence". Got any?

                                Every 1st year DBA noob panics at some point when
                                they go into top and see that the CPU is at pegged at
                                100%.

                                Neither you or I are qualified computer engineers, so
                                how about we find some, and cite them?
                                But that's what you tried to do in the previous thread (Re: system statistics not being used and managed to cite a couple of articles that showed how inappropriate your claim was in the Oracle context.

                                And in an earlier posting in this thread you said:
                                > 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."
                                and yet they stayed anonymous and uncited.

                                Jonathan Lewis
                                http://jonathanlewis.wordpress.com
                                http://www.jlcomp.demon.co.uk
                                • 13. Re: excessive LOCAL=NO oracle processes
                                  Jonathan Lewis
                                  The CPU is running at only 88%, and ADDM warns you
                                  about it being a possible threat ! So much for 100%
                                  being normal or desirable or optimal.

                                  Hey' I've got a bridge that I'd like to sell you!

                                  Did I really just hear you suggest that ADDM is
                                  infallible, or even good? ADDM commonly make silly
                                  recommendations, ridiculous or dead wrong, and ADDM
                                  makes even more ludicrous suggestions that those that
                                  I hear from you. They should have named it
                                  JONATHAN.

                                  ADDM makes lotsa funny mistakes, all the time, . . .
                                  Consider:
                                  GOOD - Instance consuming 52% of CPU (Oracle the only
                                  thing on the server)

                                  BAD - Instance consuming 52% of CPU (43 instances on
                                  the server)

                                  Message was edited by:
                                  burleson to remove error.
                                  and

                                  > >> you love quoting Metalink and dredging up any Oracle documents,
                                  no matter how old, that might offer the slightest hint that you are
                                  right about one of your assertions;
                                  Yeah, it's called "evidence". Got any?

                                  So, putting back together the two halves of the comparison that you've separated into separate postings, your considered opinion can be summed up as:

                                  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;

                                  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'.

                                  I don't think there's any need to continue this conversation - your contributions to the discussion will tell anyone reading these two threads all they need to know about the value of your original suggestion.

                                  Jonathan Lewis
                                  http://jonathanlewis.wordpress.com
                                  http://www.jlcomp.demon.co.uk
                                  • 14. Re: excessive LOCAL=NO oracle processes
                                    277993
                                    user959
                                    Let me know if you have resolved this problem ?
                                    1 2 3 Previous Next