"select count(*)" is poor on cached table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

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

  1. #1
    Join Date
    Nov 2002
    Location
    Dublin, Ireland
    Posts
    133

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

    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



    Giani

  2. #2
    Join Date
    Mar 2001
    Posts
    82
    I'm not sure, but the reason the second one is faster may be because it does not have to reparse the SQL?

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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 . . . ?
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  5. #5
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    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.
    OCP 8i, 9i DBA
    Brisbane Australia

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width