DB CPU and USER I/O Conflicts

[saranya_pg]

Hi,

I am Testing an application in shared Database with some set of users 'x'.
we ran 3 loads with with increased number users like 1x ,3x and 4x

we found other application users during 1x and 4x load testing, except in 3x.

Below are user I/O and DB CPU values for the same:

Database Time % of	1X-Load Run	3X-Load Run	4X-Load Run
User I/O	        55.47         	5.26	        46.28
DB CPU	                42.11	        89.81	        22.37
sql%execelapsedtime     99.5            98.21           69.49

I know DB CPU is mostly utilized in SQL execution 3x which is very good. And in others loads CPU was consumed in waiting.

But i didnt understand the variations in User I/O.

direct path read temp was common wait event consumed >50% db time in 1x and 3x.

Please help me to get good explanation for this variations to explain my dumb clients

[gandolf989]

User I/O would likely be explained by the amount of data cached in the database. If something happened before
or during the second test to cause the cache to get flushed, then the database would need to read all of the
data in again.

Rather than running the same test with different numbers of users for each test, and potentially a small or
large load placed on the database unrelated to the test, you would be better off turning on tracing and
looking for badly performing SQL that can get tuned. i.e. you should tune for logical IO not physical IO or CPU time.

Pete Finnigan has a link for turning on tracing for other sessions:

http://www.petefinnigan.com/rambling..._set_trace.htm

and if you can run the individual SQL statements by hand, this will allow you to turn on tracing for the current session:

http://oracle-base.com/articles/misc...and-tkprof.php

If you aren't sure where trace files are written the Pete Finnigan link has a query near the top that will tell
you where the trace files will get written. Just run the first set of alter session commands, then in the same
session execute all of the SQL that you application uses. When you have run all of the SQL then run the
second set of commands and use tkprof for each trace file and see what took the most time.

alter session set timed_statistics=TRUE;
ALTER SESSION SET TRACEFILE_IDENTIFIER = "MY_TEST_SESSION";
ALTER SESSION SET sql_trace=TRUE;
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
alter session set events '10053 trace name context forever, level 1;
SET TIMING ON

ALTER SESSION SET EVENTS '10046 trace name context off';
alter session set events '10053 trace name context off';
ALTER SESSION SET sql_trace=FALSE;

tkprof my_trace_file.trc my_trace_file.out explain=priv_user/pwd table=sys.plan_table sys=no waits=yes sort=execu, exeela

[saranya_pg]

Thanks for the reply