RE: Lookup/Code table as hot block
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <bdbafh@xxxxxxxxx>, <ranko.mosic@xxxxxxxxx>
- Date: Wed, 19 Apr 2006 18:12:42 -0400
Um, exactly what do you mean by ?hot block in database cache??
If this is an essentially read only lookup block, why do you care that it is
?hot?? If your meaning is that the block stays in cache all the time, that?s
good. Heck, if it is read only, I?d put it in the keep cache to make sure it
doesn?t page out in some strangely idle period. If it is truly a single
block table, no problem, but if it is multiple blocks you might be able to
make it denser to just be one block or fewer blocks.
If it is indeed a single block, the IOT idea may also help, especially if
the column references to get a particular row are always the same and/or
compatible to a single index order.
If you have several column sets that are requested, then you can put many
indexes on the table (they will be very small, right? and rarely updated?)
so that each reference is wholly resolved by the single reference to the
index (that is, add non-key columns that resolve the each query?s column
needs).
If not for the affect on plan generation, you could drop all the indexes ?
but Oracle does row cardinality, not block cardinality, so that fact that it
is one block won?t make it into the calculation, and if the row is very
short, you may have quite a few rows in the calculation with it still being
a single block.
Now if your meaning of ?hot block? is at the OS layer where the references
to that memory location are so fast and furious that the OS is bottlenecked
satisfying them, then the only bit of this message that may be helpful is
the bit about many indexes designed to satisfy each query?s need from the
index leaf. (And I?m impressed.)
Regards,
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On
Behalf Of Paul Drake
Sent: Wednesday, April 19, 2006 5:10 PM
To: ranko.mosic@xxxxxxxxx
Cc: _oracle_L_list
Subject: Re: Lookup/Code table as hot block
On 4/19/06, Ranko Mosic < ranko.mosic@xxxxxxxxx
<mailto:ranko.mosic@xxxxxxxxx> > wrote:
Hi List,
I have couple of thousand of users all accessing the same, very small lookup
table.
This is hot block in database cache.
How can I avoid this ? Multiple table copies ? How to do this - all users
connect under single name .
--
Regards,
Ranko Mosic
Contract Senior Oracle DBA
B. Eng, Oracle 10g, 9i Certified Database Professional
Phone: 416-450-2785
email: mosicr@xxxxxxxxxx <mailto:mosicr@xxxxxxxxxx>
http://ca.geocities.com/mosicr@xxxxxxxxxx/ContractSeniorOracleDBARankoMosicM
ain.html
This first question will come as quite a surprise:
What version of the Oracle database server software are you dealing with?
Its unlikely that you are going to want to have private copies of this
table.
Perhaps you want to store such data in an array as a package variable?
Some have advised the use of an indexed organized table.
Some have advised the use of a single table hash cluster for such tables.
Can this table be put into a tablespace that can be made read only?
Might you consider putting this table into a 2048 byte tablespace?
(fewer rows per block)
You might consider setting pctfree to a higher value so that fewer rows per
block are used.
hth.
Paul
Other related posts:
