Re: pinning a table in memory

Just a question related to this

if one assigns a table to the KEEP pool and PQ is involved, will it still read from the pool
or will it read it from disk ?

especially in the case of lookup join to a large table this is relevant since PQ normally goes after direct IO.
"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:1TGF9.86275$g9.242929_at_newsfeeds.bigpond.com...
> And the second you try putting a 6 million record table into the keep
pool,
> kiss goodbye to whatever remnants of performance you thought you had.
>
> Cache is for sissy Oracle 7 users. It has no use in an 8i or 9i database.
> None. Whatsoever. (And it didn't do too much in 7, either).
>
> You can't "pin" a table in memory, ever. And for a 7 million row table,
you
> shouldn't try.
>
> You need to get down and dirty as to precisely WHAT is making the access
to
> it "a tad slow". No indexes? Lots of physical I/O? Parse time? Crap
> hardware? Naff operating system?
>
> Get your statspack report, pipe it to www.oraperf.com, and report back.
Then
> we can take it further.
>
> Regards
> HJR
>
>
>
>
>
>
> "Sybrand Bakker" <gooiditweg_at_sybrandb.demon.nl> wrote in message
> news:pm8duugc290g580an3qps9q6tto792kisq_at_4ax.com...
> > On Thu, 28 Nov 2002 22:58:40 -0000, "David Sharples"
> > <david.sharples3_at_ntlworld.com> wrote:
> >
> > >We have a largeish table (6 million records) and the queries we have on
> this
> > >table are a tad slow and the disk is the bottleneck.
> > >
> > >We have considered putting the table in cache (alter table xyz cache)
> > >
> > >Was wondering if this would help very much seeing as though the indexes
> for
> > >this table is still held on disk.
> > >
> > >Any opinions??
> > >
> > >Thanks
> > >
> >
> > cache only comes into play with full table scans.
> > Blocks being read during full table scans are always put at the lower
> > end of the LRU list (so they are paged out immediately), with cache
> > you can avoid.
> > The cache mechanism has been replaced by the keep buffer pool in 8.0,
> > and the keep buffer pool works for all types of queries.
> >
> > Hth
> >
> >
> > Sybrand Bakker, Senior Oracle DBA
> >
> > To reply remove -verwijderdit from my e-mail address
>
>
Received on Sat Nov 30 2002 - 08:21:05 CST