Re: Oracle Query Performance, Indexes, and Optimization

On Mar 21, 12:48 pm, Brian Peasland <d..._at_nospam.peasland.net> wrote:
> jasonlichtent..._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
>
> --
> ===================================================================
>
> Brian Peasland
> d...@nospam.peasland.nethttp://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 fromhttp://www.teranews.com- 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.

IMHO -- Mark D Powell -- Received on Wed Mar 21 2007 - 11:56:28 CDT