Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: vendor sql tuning

Re: vendor sql tuning

From: Chris Stephens <cstephens16_at_gmail.com>
Date: Fri, 15 Jul 2005 08:18:34 -0400
Message-ID: <d95e860705071505181dea355@mail.gmail.com>


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_at_gmail.com> 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_at_sbcglobal.net> 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
> >
> >
> > --
> > http://www.freelists.org/webpage/oracle-l
> >

>

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Jul 15 2005 - 07:20:35 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US