Oracle-L: RE: Dealing with 3rd Party Applications

From: Darrell Landrum <dlandrum_at_zalecorp.com>
Date: Wed, 26 Feb 2003 16:43:54 -0800
Message-ID: <F001.0055ABED.20030226164354@fatcity.com>

Yes, read the vendor documentation, then scrutinize it. One serious thing to look for is the documentation up to date. We're implementing a new application right now, which includes a healthy amount of docs from the vendor regarding Oracle. Their docs said I cannot use LMT. Their docs said I cannot use partitioning. The docs said I cannot use newer Oracle than 8.1.7.1. After I counted to 100 (or 1000, I lost count) and got more coffee, I wrote a firm, but nice e-mail to the project manager asking him to confirm with the vendor just what they were trying to prove. No, seriously, I wanted to know specifically what problems they encountered with LMT and partitioning in their testing. I suggested that without specific results, I would use LMT anyway. Their reply was that the documention was outdated and that we could use 8.1.7.4, could use LMT, and could use partitioning. This case was easy.
Another such circumstance has a vendor telling us that we cannot use archivelog mode because it will kill performance. They didn't say it might affect performance if not configured properly, they said (more than once) it will kill performance. It's still early, this too shall pass.

The point is not to bash vendor documentation, but to encourage folks to read it. Read it for what it says and for what it doesn't say.

Darrell Landrum

>>> DWILLIAMS_at_LIFETOUCH.COM 02/26/03 05:19PM >>> Jeff -

I think we all have 3rd party apps to wrestle with, and I'm hoping you will get some good responses that I can also use.

Here is my tip. The vendor probably has a chapter in a manual about how they interface with Oracle. Read this, but don't skim it like most of us do because we have way too much to read. No, ponder each word and try online experiments to try and learn what every detail means. In a former life I worked for a vendor and I wrote that Oracle chapter. Now I participate in an online group and explain to other users what I wrote back then. For some reason it is hard to write that sort of thing so everyone can understand it.

Of course, some vendors wrote their code to interface to MS Access then later ported it to Oracle. ;-)

Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----

Sent: Wednesday, February 26, 2003 2: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: DENNIS WILLIAMS
INET: DWILLIAMS_at_LIFETOUCH.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com 
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

--

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

Author: Darrell Landrum
INET: dlandrum_at_zalecorp.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------