| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Dealing with 3rd Party Applications
Dennis,
The lowest common denomintor is a good description. I have seen in the third party apps they don't use/require (or necessarily test) their application with items that would be found in the Oracle Enterprise version and unlikely with the extras such as partitioning.
The documentation from the vendors is always out of date. Leaving in configuration options they had from Oracle 7, yet the application is no longer supported on that version.
We had a DBA who was hell bent on making major changes to the application so that it suited his performance/configuration guidelines. The DBA's lack of knowledge of how the application uses the database could have resulted in the application ending up crippled. An example, the database was switched to use CBO even though the vendor advised Rule Based to be used. A minor change took a simple lookup from a < 2 seconds (or sub-second) to 20 minutes response time. The clients loved that.
As you say, understand the application and how it interacts with the database before making changes.
David
>From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: RE: Dealing with 3rd Party Applications
>Date: Wed, 26 Feb 2003 18:24:41 -0800
>
>Darrell said: "Yes, read the vendor documentation, then scrutinize it."
>
>Tim said: "Don't neglect the possibility that the application is somehow
>misconfigured or configured inappropriately somehow."
>
>Amen to both. The vendor may have a stupid method of using Oracle. But you
>must understand their stupid method before you can work around it. Often
>vendors assume the lowest common denominator in the DBA, and many companies
>buy Oracle and an application without hiring a DBA. Neither Oracle or the
>3rd party vendor will refuse their money. Sometimes the vendor will
>discourage the use of something like partitioning because they fear the
>novice DBA would be over his/her head with that and then the vendor reaps
>the blame. But once you understand how the vendor uses Oracle, you are in a
>position to be creative.
>
>Dennis Williams
>DBA, 40%OCP, 100% DBA
>Lifetouch, Inc.
>dwilliams_at_lifetouch.com
>
>
>-----Original Message-----
>Sent: Wednesday, February 26, 2003 7: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: 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
>---------------------------------------------------------------------
>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: david davis INET: ddoralist_at_hotmail.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).Received on Thu Feb 27 2003 - 09:59:30 CST
 |
 |