Fwd: Lookup/Code table as hot block
- From: "Ranko Mosic" <ranko.mosic@xxxxxxxxx>
- To: _oracle_L_list <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 19 Apr 2006 22:13:47 -0400
---------- Forwarded message ----------
From: Ranko Mosic <ranko.mosic@xxxxxxxxx>
Date: Apr 19, 2006 9:09 PM
Subject: Re: Lookup/Code table as hot block
To: "Mark W. Farnham" <mwf@xxxxxxxx>
Just to add, by caching being considered I mean PL/SQL array ( like
Stephanie explained ).
And by block 'is cached anyway' I mean it is in buffer cache.
Regards, Ranko
On 4/19/06, Ranko Mosic <ranko.mosic@xxxxxxxxx> wrote:
>
> 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@xxxxxxxx> 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@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> 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
> > http://ca.geocities.com/mosicr@xxxxxxxxxx/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@xxxxxxxxxx
> http://ca.geocities.com/mosicr@xxxxxxxxxx/ContractSeniorOracleDBARankoMosicMain.html
>
>
--
Regards,
Ranko Mosic
Contract Senior Oracle DBA
B. Eng, Oracle 10g, 9i Certified Database Professional
Phone: 416-450-2785
email: mosicr@xxxxxxxxxx
http://ca.geocities.com/mosicr@xxxxxxxxxx/ContractSeniorOracleDBARankoMosicMain.html
--
Regards,
Ranko Mosic
Contract Senior Oracle DBA
B. Eng, Oracle 10g, 9i Certified Database Professional
Phone: 416-450-2785
email: mosicr@xxxxxxxxxx
http://ca.geocities.com/mosicr@xxxxxxxxxx/ContractSeniorOracleDBARankoMosicMain.html
Other related posts:
- » Fwd: Lookup/Code table as hot block
