Re: Oracle Query Performance, Indexes, and Optimization

From: Brian Peasland <dba_at_nospam.peasland.net>
Date: Thu, 22 Mar 2007 12:02:42 -0500
Message-ID: <4602aa36$0$16353$88260bb3@free.teranews.com>

Brian Peasland wrote:

> jasonlichtenthal_at_gmail.com 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
> 

One thing I should have mentioned is that it would be wise to look over the contract with the ASP. What does the contract say the ASP is responsible for? It could be that database tuning was not in the contract, as such, the ASP will not perform this function. Or it could be part of the contract and in that case, they would be in breach of said contract.

None of this does not excuse the information passed on by the ASP. A reliable ASP would say something like "yes, those tools exist, however it is outside the scope of our current contract. For $XXX more, we will be happy to provide the requested services".

Cheers,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.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 http://www.teranews.com

Received on Thu Mar 22 2007 - 12:02:42 CDT