Re: Dealing with 3rd Party Applications

Jeff,

If you look at the elapsed time for this SQL statements, you'll see that the 1,696 executions are consuming a whopping 0.89 seconds. You had indicated that the users were complaining about startup times lasting a full minute or so. So the big question is: where are the other 59 seconds being consumed?

There is an excellent chance that the database has nothing to do with the performance problem. Since it is a 2-tier application, then we might be able to see where some of the time disappears to by looking at the wait events. Still, your initial indications are showing that the server side (i.e. the RDBMS) of the client-server connection is only taking 1 out of the 60 seconds, so this should lead the investigation toward either the client side (i.e. the 3rd-party application) of the client-server connection or the network connecting the client to the server. However, I'd be very surprised if the network were a factor -- lean toward the client.

---

You can get more info by looking at the database wait events.  TKPROF
v8.1.7.0.0 does not summarize wait event information, even if your SQL trace
supplied it.  To get another trace with wait event information, you'll need
to change the ALTER SESSION SET statement from ALTER SESSION SET SQL_TRACE =
TRUE to ALTER SESSION SET EVENTS '10046 trace name context forever, level
8'.  You can do this inside the TRACETRG trigger.

Once you've collected SQL trace with wait event information, I'd suggest one
of a number of approaches to analyze it:

    * consider purchasing the Hotsos Profiler at www.hotsos.com
    * consider trying the iTRprof Analyzer at www.ubtools.com
    * use the TKPROF belonging to an Oracle9i installation to analyze the
".trc" file, if you have a 9i database installed somewhere

Another possibility is examining the V$SESSTAT and V$SESSION_EVENT views for
the session while they are waiting for logon.  Query both views for the SID
of the session.  I've posted a script named "sesstime.sql" at
http://www.EvDBT.com/tools.htm which queries these views based on USERNAME.
The output is roughly organized into categories of "cpu" (service) time,
"wait" time, and "idle" time.

However you analyze the data, I'll take a wild guess and suggest that the
session is probably waiting on "SQL*Net message from client", which is
normally considered "idle" time.  In this case, however, I'd suggest that it
is an indication that the client program is consuming the other 59 seconds
of the 60 seconds that the users are waiting on startup.

Hope this helps...

-Tim

----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
Sent: Thursday, February 27, 2003 9:54 AM




> Thanks Tim.  I think I will always consider my tuning skills basic until I

> can at least get to the point that when someone mentions a buzzword

> (waits,ratios,tkprof,latches,fetches,etc.) I will be able to recall to

> memory a unhazy idea of the concept they are referring to instead of

> rummaging through all my notes and books (although I think I pretty

> efficient at that).

>


> Anyway, answers to some of your inquiries.  It is a two-tier application

> where every user session spawns a

> corresponding database session.  Here are the results of the TKPROF (wow,

> the sql I was questioning is at the top of the rpoert):

>


> TKPROF: Release 8.1.7.0.0 - Production on Thu Feb 27 09:09:35 2003

>


> (c) Copyright 2000 Oracle Corporation.  All rights reserved.

>


> Trace file: ora02148.trc

> Sort options: prsela  exeela  fchela

>
****************************************************************************


> ****

> count    = number of times OCI procedure was executed

> cpu      = cpu time in seconds executing

> elapsed  = elapsed time in seconds executing

> disk     = number of physical reads of buffers from disk

> query    = number of buffers gotten for consistent read

> current  = number of buffers gotten in current mode (usually for update)

> rows     = number of rows processed by the fetch or execute call

>
****************************************************************************


> ****

>


> SELECT PARENTID FROM PROC_

> WHERE PROCEDUREID=:1

>


> call     count       cpu    elapsed       disk      query    current

> rows

> ------- ------  -------- ---------- ---------- ---------- ----------

> ----------

> Parse        2      0.00       0.00          0          0          0

> 0

> Execute   1696      0.36       0.75          0          0          0

> 0

> Fetch     1696      0.11       0.14          0       5136          0

> 1696

> ------- ------  -------- ---------- ---------- ---------- ----------

> ----------

> total     3394      0.47       0.89          0       5136          0

> 1696

>


> Misses in library cache during parse: 1

> Misses in library cache during execute: 1

> Parsing user id: 19  (WINSPC)

>


> Rows     Execution Plan

> -------  ---------------------------------------------------

>       0  SELECT STATEMENT   GOAL: CHOOSE

>       0   TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 'PROC_'

>       0    INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'PROC_KEY' (UNIQUE)

>
>
****************************************************************************


> ****

>
>
>


> Thanks,

> Jeff

>


> -----Original Message-----

> Sent: Wednesday, February 26, 2003 6:19 PM

> To: Multiple recipients of list ORACLE-L

>
>


> Jeff,

>


> Comments inline...

>


> > So my boss comes over this morning and tells me that the users are

having


> a

> > performance problem with a 3rd party application that have recently

began


> > using.  This is an oracle database where they bought the software and

had


> > the system admin install the software which included the vendors

> instruction

> > of creating and setting up the database (basically use the defaults).

It


> is

> > an Oracle 8.1.7 database on Windows 2000.  He wants me to find out "if

you


> > can create some indexes or something", etc. (he likes to give solutions

> > before the cause if discovered).

>


> Good observation about your boss -- never forget it!

>


> > Anyway,  I decide to take a look at it.  The performance they are

> > complaining about is when they log into the application it takes about a

> > minute for their initial screen (which includes a list of values) to

> appear.

> > I use the tool that someone posted here a while ago, SQL Monitor from

> > www.fastalgo.com, and find that during the time the user is waiting for

> the

> > first screen the application is executing a sql statement about 2200

> times.

>


> Excellent technique.  Never used the tool, but you are responding to the

> facts and symptoms, not conjecture and guesses...

>


> > The SQL is:  SELECT PARENTID FROM PROC_  WHERE PROCEDUREID=:1

> > The bind variable is different for each execution with appears to be the

> > procedureid values from the table proc_.  Table proc_ has 2203 rows.

> > I check the executions for the sql text in v$sqlarea.  Executions =

> 58,825.

> > (aha, I think this is the problem).

> > I explain plan the query and find that it is using the primary key

index.


> >

> > My tuning skills are still pretty basic.  Since I have no control over

the


> > application is there anything I can do to increase the performance of

> > running the query thousands of times?

>


> Your tuning skills are not basic.  You know enough to look before assuming

> and you appear to know your way around an Oracle database.

>


> It is not easy to tune something that is executing thousands of times,

> except to work on reducing the cost per execution.  How many "logical

reads"


> is each execution performing?  Or, at least how many logical reads are

> performed in total and what is the number of executions?

>


> I have posted an Oracle8i AFTER LOGON database-event trigger in a script

> named "tracetrg.sql" at http://www.EvDBT.com/tools.htm.  You can use that

> trigger to initiate SQL Tracing immediately upon connection by the user.

> Are you familiar with SQL Trace and TKPROF?  Hopefully, the parameter

> TIMED_STATISTICS is set to TRUE in this database;  if it isn't, you can

> enable it in the TRACETRG trigger for the session (i.e. "execute immediate

> 'alter session set timed_statistics = true';") or using ALTER SYSTEM to

set


> it to TRUE for the instance.  If you can set TIMED_STATISTICS to TRUE,

then


> please use the "sort=prsela,exeela,fchela" clause with the TKPROF command;

> if it is FALSE, then please use "sort=exeqry,execu,fchqry,fchcu" clause.

> This way, the worst SQL statements will percolate to the top of the TKPROF

> report...

>


> If you can get a TKPROF report, would you like to paste the relevant

section


> for the offending SQL statement back to the list?  That way, you'll get

lots


> of ideas from the best tuning folks in the world.

>


> And don't forget to DISABLE or DROP that trigger when you've gotten your

> trace!  Coming from personal experience, it is *VERY* embarrassing to have

> to explain why the file-system housing the USER_DUMP_DEST keeps filling

> up... :-(

>


> > Also how do you usually deal with 3rd party application issues like

this?


> > 95% of our databases/applications are from 3rd party vendors and it's a

> pain

> > trying to get them performing better.

>


> We never have "control" over the application and it is always a pain.

> However, don't neglect the possibility that the application is somehow

> misconfigured or configured inappropriately somehow.  It's not easy to ask

> questions about this without triggering the knee-jerk "No!  Everything's

> fine!" response, but is it reasonable that an application session would

need


> to perform those several-thousand queries at each login?  Is it a two-tier

> (a.k.a. "client-server") application where every user session spawns a

> corresponding database session, or is it an N-tier application where the

> app-server is creating a "pool" of database connections.  A huge up-front

> load like you describe is more characteristic of the N-tier app-server,

and


> less characteristic of "client-server" connections.  Just some food for

> thought...

>


> > Thanks,

> > Jeff Eberhard

>


> Good luck!

>


> --

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

> --

> Author: Tim Gorman

>   INET: Tim_at_SageLogix.com

>


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

> --

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

> --

> Author: Eberhard, Jeff

>   INET: Jeff.Eberhard_at_Rolls-RoyceGS.com

>


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

>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Gorman
  INET: Tim_at_SageLogix.com

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