RE: Estimating User Load on the system

[Anil Sikri]

Hello folks,

                Recently, I employed a similar approach in our test 

                database to figure out the problematic queries during

                a certain load test. I used a logon trigger with just 2

                statements :

                                alter session set max_dump_file_size = unlimited;

                                alter session set sql_trace = true;

 

                The init.ora had to be modified to set _public_trace_files =

                 true to allow the database userid used by the application

                 to be able to write to the user_dump_dest.

 

                 Regarding the specifics of the test, I coordinated with the

                 testers and created the trigger just before the load test

                 began and dropped it after the test was over. Of course, I

                 had to make sure that this load test was the only application

                 running. There were around 90 trace files generated which

                 gave me much more information that I ever envisioned.

                 It showed that the connection pooling of the application

                 was implemented poorly since 90 files were generated

                during a 1 hour load test. Also, I consolidated all the trace

                files into 1 huge trace file using a shell script and ran it

                through TKPROF. The information obtained was priceless.

                It not only provided the culprit queries, it also provided the

                amount was time being spent in the database out of a total

                load test of 1 hour.

 

                Just wanted to share it with you folks.

                Let me know if you have any questions.

 

Anil

                  

"Jamadagni, Rajendra" <[EMAIL PROTECTED]> wrote:

Thanks John ... this is precisely (the logoff trigger) is what I am in middle ot writing down ... except that I am taking everything from v$sesstat instead of selective stuff. I can then filter out what I don't need.

Raj
--------------------------------------------------------------------------------
Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !

-----Original Message-----
From: John Kanagaraj [mailto:[EMAIL PROTECTED]]
Sent: Monday, July 14, 2003 1:55 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Estimating User Load on the system

Raj,
 
Assuming that these users will logoff normally (for the most cases), you
could have a database level LOGOFF trigger that captures the following
'stats' from these sessions (out of v$MYSTAT):
 
CPU used by this session  (Cpu time used)
user commits (Number of transactions - figuratively!)
physical reads + physical reads (direct, lob, etc.) + db block changes
(former is reads, latter is the number of changes that would cause writes on
behalf of that process for Log, Undo and DBFile)
session pga/uga memory max (Memory usage)
SQL*Net roundtrips to/from client and dblink (Network usage)
 
Collect and summari! ze system wide (via STATSPACK) and for individual users
via LOGOFF just to compare.
 
John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002

Disappointment is inevitable, but Discouragement is optional!

** The opinions and statements above are entirely my own and not those of my
employer or clients **

 -----Original Message-----
Sent: Monday, July 14, 2003 10:14 AM
To: Multiple recipients of list ORACLE-L

Thanks Dennis,

I have periodic (10 mins) snapshots taken of following views ...

1. gv$session
2. gv$sesstat
3. gv$transaction
4. gv$sess_io
5. gv$sysstat

Now, I really _can't_ use Statspack, because it tells me overall score. I
need to compute the load put on system by a set list of users ... it is kind
of computation of possible charge back.

So, I have the underlying system stats. but I am having tough time to put
them in perspective for management types. I'd like to show them %CPU usage,
%IO load, %Memory being used ...

any ideas?
Raj
----------------------------------------------------------------------------
----
Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !

-----Original Message-----
<mailto:[EMAIL PROTECTED]> ]
Sent: Monday, July 14, 2003 12:49 PM
To: Multiple recipients of list ORACLE-L

Raj
   I'll second Jared's idea. Don Burleson in his book Oracle9i
High-Performance Tuning with STATSPACK makes a strong point that looking at
system load must start with the underlying system. Any look at Oracle
performance must begin with an understanding of what the system load was at
that time. As Jared points out, the three components are CPU, I/O, and
memory. For example, you may find that one of these target users is the high

CPU consumer from an Oracle perspective at a point in time. Now, if you
discovered the system CPUs weren't being taxed at that time your conclusions

might be different than if you discovered the system CPUs were pegged at
100% at that time.

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED]

-----Original Message-----
Sent: Monday, July 14, 2003 10:19 AM
To: Multiple recipients of list ORACLE-L

Load on a system is in general comprised of three components:
CPU, memory and IO.

If you have something in place to track those on a per user
basis, then you're doing well.  You might consider also tracking
other users, so that you have a basis for comparison.

Jared

On Monday 14 July 2003 07:04, Jamadagni, Rajendra wrote:
> I have been asked to compute the load put on the system by a 'select group

> of userids'. I know these users and have put something in place where I
> sample periodically following
>
> 1. session stats
> 2. session io
> 3. system stats
> 4. number of sessions
> 5. v$transaction
>
> Am I missing something? Has anyone done this before?  If so, what have you

> computed ?
>
> TIA
> Raj
>
---------------------------------------------------------------------------
>- ----
> Rajendra dot Jamadagni at nospamespn dot com
> All Views expressed in this email are strictly personal.
> QOTD: Any clod can have facts, having an opinion is an art !

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
<http://www.orafaq.net> 
--
Author: Jared Still
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
<http://www.fatcity.com> 
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
<http://www.orafaq.net> 
--
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
<http://www.fatcity.com> 
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: John Kanagaraj
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).

********************************************************************This e-mail message is confidential, intended only for the named recipient(s) ab! ove and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*********************************************************************2

---

Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo.