Oracle Database Discussions

Hi there,

I have a table in a 11.1.0.7 DB with a few million rows which I need to aggregate after performing a lookup operation on each row. The operation consists in determining from a small, constant lookup table the largest value that is smaller than some column. So I have this so-called transation table, with a few hundred tows :

CREATE TABLE ref_trad (
  entry     varchar2(50) PRIMARY KEY,
  id        integer      NOT NULL );

and a small function that gets the desired value ( after some additional mapping ):

CREATE OR REPLACE FUNCTION translate_num(num IN varchar2)
  RETURN integer RESULT_CACHE RELIES_ON (ref_block)
AS
  nm_id integer;
BEGIN
  SELECT m.number_id
    INTO nm_id
    FROM ref_number_map m INNER JOIN ref_trad t ON (m.block_id = t.id)
   WHERE t.entry = (SELECT max(entry) FROM ref_trad WHERE entry <= num);

  RETURN nm_id;
  
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RETURN 0;
END;

Now when I'm trying to aggregate the table using parallelism, I get a lot of "latch free" waits. With 4 parallel sessions for instance, looking at the graphs in EM, I get about 2.5 sessions on CPU and 1.5 on "latch free" wait. I have the feeling from reading around that the waits could be due to a hot block in the ref_trad table index or something similar. I have tried several things, like using index_organized tables or partitioning the tiny ref_table, hoping to get more parallelism, with no spectacular results. So my questions to those who might know are the following :

- How to determine precisely the cause for the latch_free wait events ? (e.g., the hot block, the piece of PL/SQL code, etc)
- Is there some kind of standard, efficient way of implementing a lookup table ? (I heard about loading the table in a PL/SQL array, would it help ? Any pointer on how to do that ? )
- Why should there be contention at all in the first place in this case, since all the lookup logic is read only ?

Thanks all for your help !
Chris