Re: Lookup/Code table as hot block

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