Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2002
    Posts
    2

    Unhappy Unanswered: 100% CPU utilization

    We have a 2-550MHz processor Win2K Server box with 3 GB RAM. It has 4 Oracle instances all of them serving as DataWarehouses.
    On one instance we have "on-demand" reporting appication where users pick report filters from their web-based front-end apps and click submit. In almost all cases, this runs a stored procedure on the database. The procedures (queries) have been optimized for performance but they return a significant amount of data (Plan costs are relative low but execution time is high)Each such user requests takes up 50% of CPU so that 2 processes take up 100%. We could have 10 such concurrent users.
    1. Is CPU maxing out a disaster in the making ?
    2. Will reducing the number of instances help ?
    Asuming the database and applications have been tuned, what other options do we have.

    TIA

  2. #2
    Join Date
    Dec 2002
    Posts
    11

    Re: 100% CPU utilization

    Been there, done that, it gets very slooooooooooooow............

  3. #3
    Join Date
    Oct 2002
    Location
    greenwich.ct.us
    Posts
    279
    If you have 10 users using 50% of available CPU each, you probably need more CPU or ask the server to do less work (less instances).

  4. #4
    Join Date
    Dec 2002
    Posts
    11

    Re: 100% CPU utilization

    More seriously...

    Analyse the schema and check the execution paths of the SQL being used. It is worth going to the SGA (v$sql) and looking at what SQL is being executed (users_executing > 1), is it the expected statement?

    With our SQL it seems to be the sorts that use the most CPU, tune them out, or cut down on the sorts if possible. You say that the SQL is tuned, check it is still using the expected path - I have had situations where SQL has run properly for a while and then apparently decided that indexes are for whimps! - In fact the example illustrated below.

    When the queries are run on other DB's with the same data do they max out in the same way? Don't laugh, we were maxing out the processors on a 12 processor beast, it worked fine on our single processor box and operated 100 times faster or so... We never did get to the bottom of that one, but forcing the stats from our box onto the beast solved the problem suggesting that the beast had problems generating the correct statistics. (8.1.6.3)

    Experiment with SGA size, too big can be as much of a problem as too small, although with the memory you have too big seems unlikely.

    What is the disc activity like? I really do not trust Oracle on NTFS...

    If any of the applications poll, check that the poll frequency is not excessive. Often happens when code cannot see a variable it is supposed to read, plays havoc with the CPU... (watch executions rising in v$sql)

    Sorry I'm not a DBA, just a developer that works closely with DBA's sorting mine and other problems, the above are some of the things we have seen in that pursuit...

    Bob

  5. #5
    Join Date
    Sep 2002
    Location
    ITALY
    Posts
    53

    Re: 100% CPU utilization

    Originally posted by 10auto
    Asuming the database and applications have been tuned, what other options do we have.
    I don't believe what you have asserted but after all having tens/tons of users doing long running query on a couple of CPUs doest not seems to be a fair play.

    Good luck.
    Franco Ceotto
    SIPTI srl
    OnSite Services
    9i OCP DBA, Performance Engineer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •