Re: Oracle 8i and poor query performance in some queries

From: Heikki Siltala <heikki.siltala_at_stakes.nojunk.fi>
Date: Thu, 30 Oct 2003 21:50:28 +0200
Message-ID: <bnrq1f$8f2$1@phys-news1.kolumbus.fi>

I wrote:
> The documentation does not explain why it does not work this way. I have
> opened iTAR for this and so we'll see what Oracle's technical stuff
> thinks about it.

Tech support has investicated the issue. There has been some minor changes in the buffer cache algorithm between Oracle releases. In this case (8i) the FTS was done on the table that was larger than Oracle's idea of a small table (can be controlled with parameter _small_table_threshold) and so the blocks went to the LRU end of the LRU list. And the database wasn't actually "fresh": we had some auditing triggers and other activity that runs always after instance startup and after user login. So the buffers weren't "fresh" when we started the query. So it was not optimizer problem, not software problem, not hardware problem. Just a stupid SQL over unindexed tables. :-)

Case closed.

--
Heikki

Received on Thu Oct 30 2003 - 13:50:28 CST