RE: Same operation, more CPU time

Paul,

>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.

I believe what you are seeing is due to this: A 'latch free' wait does not register /unless and until/ the process requesting the latch 'sleeps', i.e. while it is spinning a Wait event is not registered. However, at this time, spinning consumes CPU without a context switch. So, what are seeing are the GUI processes spinning more than they would otherwise have (possibly) to acquire the cache buffer chain latch so that they can get to the blocks. This should happen only if the blocks required by both the GUI and the Batch program are in same set of chains protected by the chain latches, at which point there is contention adequate enough for spinning, but not for sleeping. I assume that the server you are running this on is 'wide' enough and powerful enough to accommodate this apparently wasteful spinning..... The best way to see this is to compare the values of V$SESSTAT (or the flatter V$SESS_IO) for those session in question. Look for changes in 'CPU used by this session' in V$SESSTAT and the number of LIOs in V$SESS_IO.

>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.

This is also possible, and in fact would add to the Cpu consumption noted above.

>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?

Of course, I may be missing something or even misstating the reason, and I am sure that the Gurus will step in here!

John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)

Disappointment is inevitable, but Discouragement is optional!

• The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers **

  ---

  Please see the official ORACLE-L FAQ: http://www.orafaq.com

  ---

  To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

  ---