Re: data block cache algorithm and cache hit or not

"FaheemRao" <faheemrao_at_yahoo.com> wrote in message news:43b58913.0109280003.1d688d02_at_posting.google.com...
> One important thing you are missing that when you say Commit , all the
> Dirt duffer(changed)in the Database buffer cache are written to disk
> as well.
>
> just want to add some thing to Lawrence's answer
>

It would, however, be really nice if you added something that was correct. What you have posted here is about as inaccurate as it is possible to get. Specifically, a commit most definitely does NOT induce DBWR to flush anything at all out of the Buffer Cache.

Apart from being 100% wrong, I guess you were close.

HJR
> Faheem
>
>
> "Lawrence Simela" <lsimela_at_mahalini.prestel.co.uk> wrote in message
news:<9otuu4$22c$2_at_phys-ma.sol.co.uk>...
> > 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 Fri Sep 28 2001 - 08:43:16 CDT