Re: Lookup/Code table as hot block
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: JayMiller@xxxxxxxxxxxxxxxx
- Date: Wed, 19 Apr 2006 23:18:45 +0200
Ranko,
I can only echo Jay's question. In particular, one thing that matters is
whether this table is accessed through complex joins or through a lookup
function.
One way to have multiple copies (assuming that you don't have zillion of
concurrent users and that memory isn't a major issue) is to have a
PL/SQL array as a package variable, and to load this table (it depends
of course on what you mean by 'small', but for a few hundred lines it is
likely to be OK) into the initialization section of your package. You
will have one copy per session.
PL/SQL arrays are not necessarily indexed by binary integers, you can
use them as associative memory (e.g. my_array('old_code') :=
'new_code';) it can be quite helpful for small lookup tables.
HTH
Stéphane Faroult
JayMiller@xxxxxxxxxxxxxxxx wrote:
First question is how are they accessing it? If via PK then making it
into an index organized table can cut your lio in half since you won’t
be doing an index block read.
Thanks,
Jay Miller
Sr. Oracle DBA
x68355
-----Original Message-----
*From:* oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
*Sent:* Wednesday, April 19, 2006 4:47 PM
*To:* _oracle_L_list
*Subject:* Lookup/Code table as hot block
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 <mailto:mosicr@xxxxxxxxxx>
http://ca.geocities.com/mosicr@xxxxxxxxxx/ContractSeniorOracleDBARankoMosicMain.html
<http://ca.geocities.com/mosicr@xxxxxxxxxx/ContractSeniorOracleDBARankoMosicMain.html>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
