"select count(*)" is poor on cached table

[Giani]

Hi all,

Any idea why "select count(*)" is poor on cached table?

Thanks

SQL>alter table EBUSS1.RATE_ENGINE cache ;



Table altered.



Elapsed: 00:00:00.48

SQL>select count(*) from EBUSS1.RATE_ENGINE ;



  COUNT(*)

----------

    792856



1 row selected.



Elapsed: 00:03:39.66

SQL>alter table EBUSS1.RATE_ENGINE nocache ;



Table altered.



Elapsed: 00:00:00.48

SQL>select count(*) from EBUSS1.RATE_ENGINE ;



  COUNT(*)

----------

    792856



1 row selected.



Elapsed: 00:01:54.57

[DQuinn]

I'm not sure, but the reason the second one is faster may be because it does not have to reparse the SQL?

[slimdave]

CACHE is not a magic go-faster command. Here is what I think happened ...

i) You set the table to CACHE, requesting that blocks me kept in memory after they have been read.
ii) You performed a full table scan, requiring that the blocks be read from disk, hence the long query time, but Oracle obviously will not then purge the blocks from memory straight away.
iii) You set the table to NOCACHE
iv) The next full scan found the blocks in memory, hence the short query time

[DaPi]

I think Slimdave is probably right (this happens often).

He is also right to say "CACHE is not a magic go-faster command". It only increases the PROBABILITY of data from a full table scan staying in buffers. If you need ALL 3/4 mio rows kept in memory you might want to rethink design . . . ?

[grjohnson]

Also CACHING is meant for small lookup tables, no point on flooding the Buffer Cache with unneeded information via a Full Table Scan on a large table. I also agree with Slim Dave, and if it wasn't inthe Buffer Cache (Which it was), it'd be in the Unix Buffer Cache or Disk Cache.