Re: data block cache algorithm and cache hit or not

Case 1
What you say is basically correct. If you queried the recently modified rows you would get a high cache hit ratio. But this is dependent on the size of your database buffers and your block size.

>
> Case2 : Select Operation

Results from Select are cached. So when run for the second time a query will have a higher hit ratio unless it is long running and has a large result set in which case some blocks would have been placed in the LRU (least Recently Used) heap and therefore would have to be re-read. Also, on subsequent runs of the query a parsed version would exist in the Shared Sql Area so it wont be parsed again.

You can calculate your hit ratio with the formula given in the Tuning Manual

e.g.
SELECT NAME, VALUE
FROM V$SYSSTAT
WHERE upper(NAME) IN ('DB BLOCK GETS', 'CONSISTENT GETS', 'PHYSICAL READS')

and then

Hit Ratio = 1 - (physical reads / (db block gets + consistent gets))

Hit ratio should be at least 0.9

If less then consider increasing your DB_BLOCK_BUFFERS parameter since database buffers = DB_BLOCK_BUFFERS * db_block_size

Hope this helps

No need to apologise, your English is very good... my Japanese is non-existent save for a few Karate terms. Lawrence

"mako" <makoml_at_jcom.hone.ne.jp> wrote in message news:dHos7.1808$kn.215839_at_news1.rdc1.ky.home.ne.jp...
>
> Case1 : Insert/Update/Delete Operation
>
> 1) insert,update,delete command by user
> 2) a foreground process reads data blocks from datafile
> and store them in database buffer cache on SGA.
> 3) a foreground process changes these data blocks.
> 4) a foreground process writes these changes in redo buffer
> as redo entry.
> 5) if commited, LGWR process reads redo entries from redo buffer
> and write them on redo log file.
> 6) if above procedures are succeeded, return "Commited" message
> to user.
>
> So, If I requested just same data, We can expect CACHE HIT because they
are
> already stored in cache (if not purged).

>
> Case2 : Select Operation
>
> 1) select command by user
> 2) a foreground process reads data blocks from datafile
> and "STORE(?)" them in database buffer cache on SGA.
>
> 3) if above procedures are succeeded, return results to user.
>
> 4) AND, if users requested just same data, forground process will
> get them from database buffer cache (?) and NO DISK I/Os (?).
>
> I'm not sure about because I don't know foreground process will store
> data blocks in database buffer cache or not.
> If NOT, I cannot expect cache hit and Disk I/O will happen every time.
>
> Please let me know cache algorithm when select statement was given,
> and can i expect cache hit or not?
>
> Sorry for my poor english.
>
> Regards.
>
> Mako
>
>
>
Received on Wed Sep 26 2001 - 20:27:58 CDT