1 2 3 Previous Next 44 Replies Latest reply: May 15, 2008 8:31 AM by 108476 Go to original post RSS
      • 15. Re: excessive LOCAL=NO oracle processes
        Billy~Verreynne
        > 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
          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
            Billy~Verreynne
            > 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
              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
                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
                  Billy~Verreynne
                  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
                    Billy~Verreynne
                    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
                      Richard Foote
                      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
                        "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
                          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
                            Richard Foote
                            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
                              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
                                Richard Foote
                                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
                                  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
                                    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 . . . .