Re: Lookup/Code table as hot block

Thanks all for their responses. I apologize for not having more detail - this is yet another
4 pm issue. Version is 9.2.0.x. This table has only 3 ( three ) rows, so any points relating to pk/indexes/IOT etc don't apply here. Why is this bad thing ? I don't know, I asked client the same question, but they seem sure
they are seeing problem. I didn't have chance yet to have direct look at the problem.
Caching is current idea and also suggested by Oracle Consulting. I am not sure how much it will help though - block is cached anyway, so buffer cache latch will be replaced with shared pool latch. Spreading accross 3 blocks is also considered.

On 4/19/06, Mark W. Farnham <mwf_at_rsiz.com> wrote:
>
> 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> 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
>

--
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

--
http://www.freelists.org/webpage/oracle-l