Re: Lookup/Code table as hot block
- From: Tanel Põder <tanel.poder.003@xxxxxxx>
- To: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 20 Apr 2006 20:03:53 +0900
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@xxxxxxxxx ; _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@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
Other related posts:
