RE: Lookup/Code table as hot block

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Paul Drake
Sent: Wednesday, April 19, 2006 5:10 PM
To: ranko.mosic_at_gmail.com
Cc: _oracle_L_list
Subject: Re: Lookup/Code table as hot block

On 4/19/06, Ranko Mosic < ranko.mosic_at_gmail.com <mailto:ranko.mosic_at_gmail.com> > 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_at_rogers.com <mailto:mosicr_at_rogers.com> http://ca.geocities.com/mosicr@rogers.com/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

--

http://www.freelists.org/webpage/oracle-l Received on Wed Apr 19 2006 - 17:12:42 CDT