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_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