Re: Lookup/Code table as hot block

When you cache your data into PL/SQL array, it will be stored in your UGA, which is private for your session, thus no latching required for reading it.

Depending on your cursor management strategy (and possibly cursor_space_for_time) parameter value you might need library cache & library cache pin latches for executing query against lookup table. But those you need anyway irrespective whether the query accesses PL/SQL array or buffer cache buffer.

Tanel.

• Original Message ----- From: Ranko Mosic To: Mark W. Farnham Cc: bdbafh_at_gmail.com ; _oracle_L_list Sent: Thursday, April 20, 2006 10:06 AM Subject: 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