Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle Query Performance, Indexes, and Optimization

Re: Oracle Query Performance, Indexes, and Optimization

From: Robert Klemme <>
Date: Thu, 22 Mar 2007 11:14:43 +0100
Message-ID: <>

On 21.03.2007 17:56, Mark D Powell wrote:

> On Mar 21, 12:48 pm, Brian Peasland <> wrote:

>> wrote:
>>> I've got an issue that I'm sure the posters here can answer regarding
>>> Oracle databases. I know it is probably a silly issue, but I really
>>> need assistance.
>>> I'm a MS SQL Server guru, which is all we've got in-house. Works great
>>> for our small and mid-sized apps.
>>> For our accounting system, we've outsourced in an ASP model. The
>>> provider is using Oracle 9 for the back end. We're seeing a certain
>>> job (which consists of numerous SQL queries) which takes 45 minutes to
>>> run. IMO, it doesn't seem like it is the type of process that should
>>> take as long as it does.
>>> However, the ASP says they do not have any tools to diagnose which of
>>> the queries are poorly performing, and can't figure out if the
>>> application would be enhanced by additional indexes, etc. I find that
>>> very hard to believe. In SQL Server, there are numerous tools to
>>> perform those tasks, most of which are extremely simple to use (i.e.,
>>> Profiler, Idera's tools, Quest's tools, etc).
>>> Do these types of tools exist for Oracle? Do they come with base
>>> Oracle database administration tools? Query analysis, index analysis,
>>> poorly performing queries, table scans, etc.
>>> I need some information to be able to battle their DBA's, of which I'm
>>> doubting their sincerity as well as ability. I can't believe there
>>> aren't any tools out there that can help an Oracle DBA with their
>>> performance analysis.
>>> Thanks in advance for your help.
>> Normally, I don't offer this kind of advice, but it seems to me like
>> it's time to dump that ASP and move on to someone else. Oracle does have
>> such tools. And the ASP should be able to have the technical resources
>> on hand to be able to solve this type of problem. After all, then main
>> reason you outsourced this to the ASP was because your company did not
>> have technical expertise in-house and did not desire to bring in this
>> type of expertise to your staff.
>> As I said, Oracle has all of these tools available. And if one doesn't
>> like using these tools that Oracle provides, many vendors (Quest, etc)
>> will be willing to sell tools to do similar tasks.
>> HTH,
>> Brian
>> --
>> ===================================================================
>> Brian Peasland
>> d...@nospam.peasland.net
>> Remove the "nospam." from the email address to email me.
>> "I can give it to you cheap, quick, and good.
>> Now pick two out of the three" - Unknown
>> --
>> Posted via a free Usenet account from Hide quoted text -
>> - Show quoted text -
> Every version of Oracle since at least 7.0 (maybe earlier) has a
> builtin SQL trace facility and since 8.1 (if not 8.0) statspack has
> come with the database.
> If the ASP does not know how to use these tools then you really should
> rethink your outsourcer choice as Brian suggested.  It could be the
> ASP considers the time acceptable and just does not want to try to
> tune it for one customer, but if that is the case they should tell you
> this.

As additional ammunition: you can find Oracle's documentation online, for example the "Performance Tuning Guide" which also lists tools available:

Index to Oracle 9 docs:

An alternative approach to switching ASP might be to have an Oracle consultant on site at *their* cost.

Kind regards

        robert Received on Thu Mar 22 2007 - 05:14:43 CDT

Original text of this message