RE: Dealing with 3rd Party Applications

Jeff,

> 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'll assume you have reproduced this time delay on more than one machine. If this is so (and that you want to continue to assist with this situation) then I would contact the vendor to determine if this experienced time is expected. You may get an easy answer!! If not...

>From a glance, it certainly appears to be an application related issue. As
others mentioned, I would not create any additional database objects (i.e. indexes) unless directed to by the vendor - assuming it is a vendor problem.

Your situation reminded me of a previous experience. I worked for a vendor which re-executed a SQL statement similar to what you are experiencing. This was part of a batch process which was running 6 hours. It was determined that we could capture the data with one fetch and keep the array of values in memory for processing. The batch process went from 6 hours to about 30 minutes. My point being that the customer could do very little to improve performance, certainly not anywhere near what the vendor can - once, again considering it is a vendor problem.

Eric Harrington

-----Original Message-----
Sent: Wednesday, February 26, 2003 3:44 PM To: Multiple recipients of list ORACLE-L

Hi,

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

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.

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?

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.

Thanks,
Jeff Eberhard

-- 
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: Harrington, Eric
  INET: Eric.Harrington_at_maine.gov

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