Re: vendor sql tuning

  • From: Mladen Gogala <gogala@xxxxxxxxxxxxx>
  • To: cstephens16@xxxxxxxxx
  • Date: Fri, 15 Jul 2005 04:52:59 +0000

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


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

Other related posts: