Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: data block cache algorithm and cache hit or not

Re: data block cache algorithm and cache hit or not

From: FaheemRao <faheemrao_at_yahoo.com>
Date: 28 Sep 2001 01:03:54 -0700
Message-ID: <43b58913.0109280003.1d688d02@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

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 - 03:03:54 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US