Oracle-L: Re: HIGH CPU WITH MULTIPLE CONCURRENT USERS (long)

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 19 Apr 2002 06:28:30 -0800
Message-ID: <F001.004495D9.20020419062830@fatcity.com>

I've got a little lost about who has said what in answer to whom about what - so apologies if I'm repeating comments, answering non-questions and giving incorrect attributions.

The primary problem appears to be that performance plunges dramatically when concurrent increases. Three tkprof outputs for single use and multiple use are quoted below. The examples taken from 20 concurrent users are the best case and worst case of several reported.

The first point is one that someone has already made, you have a hard limit on the number of CPU seconds available per second.

NOTE - there are 4 CPUs on the system.

Single user - takes 1.75 CPU seconds to run the query. Therefore 20 users should take about 35 CPU seconds to run all 20 copies of the query.

Sharing this time across 4 CPUs you get an elapsed time of 8.75 seconds per user to complete. The worst case takes 9.81 seconds elapsed, the best takes 6.48. The range is a little surprising, but not totally insane.

The fact that CPU usage (which is only accurate to the 1/100 sec per call) goes up from 1.76 to about 1.90 seconds is also not entirely ridiculous, especially when the CPUs are all operating under a run-queue of 5 tasks.

The second point is the one I made earlier - I think your problem is excessive CPU in the optimisation phase, and I think this is related to the 50 values in your IN-list. Is your execution path an "in-list iterator" or a concatenation. Can you try running the query with the "no_expand" hint and seeing what the difference in CPU is ? Until you can reduce the CPU time to something like 0.01 seconds, your application will not scale.

(Your simple table isn't partitioned is it ?)

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Author of:
Practical Oracle 8i: Building Efficient Databases

Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html

1 USER:

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

Parse 1 0.03 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 4 1.73 1.74 0 334 0 31
------- ------ -------- ---------- ---------- ---------- ----------

total 6 1.76 1.76 0 334 0 31

20 Simultaneous Users:

USER #1:

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 4 1.91 6.48 0 334 0 31
------- ------ -------- ---------- ---------- ---------- ----------

total 6 1.91 6.48 0 334 0 31

USER #3:

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 4 1.86 9.81 0 334 0 31
------- ------ -------- ---------- ---------- ---------- ----------

total 6 1.87 9.82 0 334 0 31

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Fri Apr 19 2002 - 09:28:30 CDT