Re: vendor sql tuning

On 07/15/2005 12:23:57 AM, Chris Stephens wrote:
> This is a 9.2 database on HP 11i.
>
> There is some sql from a 3rd party app that is supporting one of our
> call centers.

Hopefully, you are not responding to 911 calls, are you?

> It is taking approximately 3 minutes to complete.

In geological terms, this is exceptionally quickly. You cannot even finish a can of Heineken in 3 minutes. What are you complaining about? That response time gives your service personnel the necessary time to self-reflect and meditate. As any person consecrated in fine spirits will tell you, it is a very good thing indeed. You need to discover your spiritual side.

> They
> are not using bind variables and we cannot change the sql. They also
> have no integrity constraints defined.

Symptoms of the truly portable application which "works with any database platform", probably written in Java. Congratulations! Your application will suck equally, regardless of what database you use. Be a pal and buy a mainframe from IBM, they still make them in the US, so it will help the situation with jobs. I believe that IBM mainframes are produced in the lovely state of Maine, thus the name.

>
> ...again, i can't change the sql.

So tuning it will make whole lot of difference? I love pointless exercises in futility!

>
> i know where the execution time is being spent. i know i need to have
> an index access to case_id. the query will never return more than
> three or 4 rows.

But you can't change anything or you'll void the warranty? Have you ever heard of gentleman by the name Joseph Heller? He wrote a book called Catch-22. It's an exquisite book, you'll enjoy reading it. You shouldn't alter it in any way, shape or form, though.

>
> even the explain plan shows that i am using the unique index on
> case_id,address_id,company_Id but when i trace the session it is a
> full table scan.

Your table is just being thoroughly read, in a way supported by any database. Stop whining! Rewrite your application using this: http://www.gotw.ca/publications/mill03.htm

>
>
> any suggestions?

Let me bring up a quote from one of the most sacred classic movies, the National Lampoon's Animal House:

Bluto: My advice to you is to start drinking heavily. Otter: Better listen to him, Flounder. He's pre-med.

I am grateful to another esteemed member of this list for sending me this quote earlier today. It is clearly visible that I followed the advice from the sages. Hic! So should you. You can fine-tune your vendor by using the tools produced by this fine company: http://smithwesson.com. Go ahead, make my data.

-- 
Mladen Gogala
http://www.mgogala.com


--
http://www.freelists.org/webpage/oracle-l