Re: Response and performance issue

> I have a problem with a 8.1.6.2.0 database. When I run a query, the responce is
> about 25s. But when I run the same query in an other window while the first
> query is executing, I get an immediate (1-2s) response, even before the first query is
> finished. I can easily reproduce these results. It does not matter if I run the query from
> sqlplus or from the VB-client. There is no or litle CPU and disk activity.
> Load is about 0.3. After the first query finishes the performace dies again, so this
> should not be a caching issue. I have to start another 'slow' query before I will get
> fast responses again.

Are you sure that this is not a caching issue? It sounds like it to me. But I'd need to see the results of TKPROF on both sessions to be sure. Have you checked you buffer cache hit ratio?

It sounds like the first query is doing a full table scan and the second query takes advantage of the fact that the first query has cached the data blocks. After both queries complete, the data blocks are aged out of the cache. Running the queries again exhibits the same symptoms.

Run a SQL trace on both sessions and use TKPROF to see what's going on. Then check your buffer cache hit ratio.

HTH,
Brian

-- 
========================================
Brian Peasland
Raytheons Systems at
  USGS EROS Data Center
These opinions are my own and do not
necessarily reflect the opinions of my 
company!
========================================