Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: CPU spike and contention

    Whenever we encounter contention in the database, even if it's contention for a single row, the CPU plateaus between 80-90%, up from about 10%. Today, a table was locked and probably 10 - 20 requests for INSERTs pretty much stopped all activity in the system. Is there something that we've got configured that might cause behavior like this?

    Thanks
    Chuck
    10g

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    select sid,
    decode(block ,0,'NO','YES') BLOCKER,
    decode(request,0,'NO','YES') WAITER
    from v$lock
    where request > 0 or block > 0 order by block desc
    /
    Might help. Then again, might not.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    look in the udump directory & see if there's anything in the trc files.
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    How is the app doing locking, optimistic, pessamistic or something nasty like full table locks?

    Alan

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    My question was poorly worded, as I'm fine with PESSIMISTIC locking:

    Why would the CPU spike during row-level contention? It's almost like Oracle is checking the row's status as often as the CPU's capacity will allow, until there's no more contention.

    (The table-level lock was due to an ALTER TABLE stmt, which queued up users behind it.)

    If this helps:
    I'm using OEM to check CPU activity. The performance graph is indicating the CPU activity. If the graph is not reliable for assessing CPU activity, then this posting has no basis. ColdFusion is the tool reporting a problem, and it reports that sessions are timing out. In my mind, this is due to 1) the CPU is unable to respond to requests within the timeout limit OR 2) there's something going on with the connection pooling mechanism in ColdFusion, and it's unable to create new connections when one is tied up due to contention.

    -Chuck

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    I know this doesn't answer your question but:
    how about FOR UPDATE NOWAIT
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Just curious, what is wrong with 90% utilization. Oracle will typically grab as much as it wants on it's database server. Most oracle servers (unless the server is a real monster) will be fully utilized when oracle is busy. It grabs as much memory and resorce as you are willing to give it.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    If your saying that the table level lock due to the alter table was causing the issue, it shouldnt really be a problem as how often do you do an alter table statement? Also if you are doing a DDL statement like this I would expect it to be done in a maintainance window or during a quiet period like during the night.

    Alan

  9. #9
    Join Date
    Dec 2003
    Posts
    1,074
    I'm starting to test whether the CPU is churning away, or if there's another issue. I just locked a record in one session, and then tried to update it another. I ran the "top" utility in LINUX and the %CPU didn't go up at all for the oracle user.

    Now I'm going to force ColdFusion into a locking situation and see what the result is.

    I guess I used the wrong term. I said spiked, but I meant plateau-ed at 90%.

    -Chuck

  10. #10
    Join Date
    Dec 2003
    Posts
    1,074
    So using the LINUX utility "top", CPU utilization on our development server remained at 98% free during an example of row lock contention. The graph in OEM, however, showed the CPU at about 50%.

    ColdFusion is the weak link here after all. It's unable to add additional connections to Oracle under these circumstances:

    2 existing connections in the ColdFusion connection pool.
    those 2 connections are consumed by 2 separate requests for data that is being held by another session.

    Sorry for the false alarm on the database side of things.
    -Chuck

Posting Permissions

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