why did query behavior change ?

Hi,
    You just recently experienced a performance issue with queries
accessing a particular table. Response time went from 15-20 seconds,
to 2-3 minutes.The table has around 500,000 rows, and is a "core" table
in the app.

  I did run 1 problem query interactively to get the explain plan. The query 
did a table lookup
via an index, and it returned 165 rows.  The "consistent gets" though were 
around 1 million.
This is a 3rd party app, so what we can do with the query itself is limited.

    The temporary workaround was to cache the table. This brought
the response time back to normal.  By caching the table, I assume the table 
will be
handled differently in cache.The better results show that.

    But why did the query behavior worsen ?   I am trying to track down the 
root cause.

    Any ideas are welcome.


    thanks        ed

  
solaris 10
oracle 10.2.0.4  - 2 node cluster-
 asm 10.2.0.4
clusterware 10.2.0.4

Other related posts: