Re: vendor sql tuning

on the one hand you're correct.  on the other hand, i see know reason
why the optimizer should be full scanning the index.  ...and i also
see know reason why it's not possible to convince the optimizer to use
the index as a lookup.  ...the last thing i tried was setting
optimizer_index_cost_adj = 1 ...still no luck.

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1    136.30     333.73     367616   17004541          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3    136.31     333.74     367616   17004541          0           0

---------------------------------------------------------------------------------------
| Id  | Operation                                               | 
Name                                 | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                  
                         |   281K|    22M|   826 |
|*  1 |  FILTER                                                 |     
                                      |            |       |       |
|   2 |   TABLE ACCESS BY INDEX ROWID     | CASE_ID                   
         |   281K|    22M|   826 |
|*  3 |    INDEX FULL SCAN                             | CASE_ID_IDX1 
                  |  2889K|       |    26 |
|*  4 |   TABLE ACCESS BY INDEX ROWID     | ADDRESS                   
        |     1     |    25 |     2 |
|*  5 |    INDEX UNIQUE SCAN                          | ADDRESS_PK1   
               |     1     |       |     1 |
|   6 |   NESTED LOOPS                                  |             
                               |     1     |    41 |     3 |
|*  7 |    INDEX RANGE SCAN                           |
ASSOCIATED_ADDRESS_PK  |     1 |    16 |     2 |
|*  8 |    TABLE ACCESS BY INDEX ROWID     | ADDRESS                  
         |     1 |    25 |     1 |
|*  9 |     INDEX UNIQUE SCAN                          | ADDRESS_PK1  
                |     1 |       |       |
----------------------------------------------------------------------------------------


On 7/15/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
> 
> 
>
--
http://www.freelists.org/webpage/oracle-l

Other related posts: