Re: vendor sql tuning

  • From: Chris Stephens <cstephens16@xxxxxxxxx>
  • To: oracle-l <Oracle-L@xxxxxxxxxxxxx>
  • Date: Fri, 15 Jul 2005 08:18:34 -0400

in spite of the odd responses, i'm still working on this if anyone has
any suggestions.

i simply cannot get it to use the index without full scanning it. 
...when i take the order by part out and hint it to use the index it
still full scans

i broke the sql down:

SQL> SELECT address.address_id
  2    FROM clorox_prod.address
  3    WHERE (address.company_id = 'SYS'
  4   AND address.last_name = 'Plaugher');

ADDRESS_ID
----------
  1861557
  2238779
  2638869
  2925218
  3011411
  4087853
   964310

7 rows selected.
Elapsed: 00:00:00.01
SQL> 
SQL> SELECT DISTINCT associated_address.case_id
  2                   FROM clorox_prod.associated_address, clorox_prod.address
  3                               WHERE (address.company_id =
associated_address.company_id
  4                                 AND address.address_id =
associated_address.address_id
  5            AND address.company_id = 'SYS'
  6                                 AND address.last_name = 'Plaugher');     
 
no rows selected
Elapsed: 00:00:00.00
SQL> select count(*) from(
  2  SELECT case_id.*, case_id.ROWID
  3    FROM case_id
  4    WHERE (case_id.company_id = 'SYS'
  5      AND (case_id.address_id IN
(1861557,2238779,2638869,2925218,3011411,964310)
  6      OR case_id.case_id IN (NULL)
  7           )) order by company_id, case_id);
  COUNT(*)
----------
         6
 
Elapsed: 00:00:00.02

i removed the postal_code predicat just to get some rows back.
the last query uses th


On 7/15/05, Walt Weaver <weaver1308@xxxxxxxxx> wrote:
> How the hell did I get involved with this?
> 
> It showed up in my Spam folder on Gmail.
> 
> When I was flying a gunship in Vietnam we responded to "911 calls"
> with massive firepower -- 20mm gatling guns was the preferred method,
> usually worked well for TICs.
> 
> My older daughter is currently in ICU on a ventilator.
> 
> I don't give a shit about HP.
> 
> Send me beer, Bluto.
> 
> Thanks,
> --Walt
> 
> On 7/14/05, Mladen Gogala <gogala@xxxxxxxxxxxxx> wrote:
> >
> > 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
> >
>
--
//www.freelists.org/webpage/oracle-l

Other related posts: