Same operation, more CPU time

  • From: Paul Baumgartel <treegarden@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Wed, 5 May 2004 08:39:40 -0700 (PDT)

Hi all.  

I'm doing some performance testing--the purpose is to see how the
response time of our interactive Web application changes when a
batch-mode data load process runs concurrently.

The response time of the GUI does increase.  A 10046 trace does not
show significant additional wait events that account for the extra
time--instead, it appears that CPU usage for the same operations
increases.  For example, in the GUI test with nothing else running, a
particular query  might take .03 seconds of CPU per row; with the data
load running, the same query might take .055 seconds.  The optimizer
plan lines in the tkprof output show greater CPU time for each step
(e.g., block gets via rowid, hash join, etc.).  I emphasize that the
difference is in CPU consumption, not just elapsed time--again, there
is no significant different in wait time between the two executions. 
Also, the database server's overall CPU usage never exceeded 46% during
the tests.

So, why is this happening?  My theory (which may be completely
half-baked; that's why I'm posting it here) is that, due to the data
load's heavy insert/update activity on tables that the GUI is querying,
the GUI process's queries require much more construction of
read-consistent block versions than in the GUI-only test.  This, in
turn, consumes CPU that is not otherwise broken out in a trace.

Is this theory plausible?  Is there anything else in the trace file
that I might be missing, or any other way to dig deeper into this?


Paul Baumgartel
Transcentive, Inc.

Do you Yahoo!?
Win a $20,000 Career Makeover at Yahoo! HotJobs 
Please see the official ORACLE-L FAQ:
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
Archives are at
FAQ is at

Other related posts: