[log4plsql] Performance problems

[<log...@li...>]

I have modify the following function of the PLOG package:

FUNCTION getTextInLevel (
     pCode TLOGLEVEL.LCODE%type
) return  TLOG.LLEVEL%type
IS
     ret TLOG.LLEVEL%type ;
BEGIN
   case pCode
     when 'ALL' then ret := 70;
     when 'DEBUG' then ret := 60;
     when 'INFO' then ret := 50;
     when 'WARN' then ret := 40;
     when 'ERROR' then ret := 30;
     when 'FATAL' then ret := 20;
     when 'OFF' then ret := 10;
   end case;
--    SELECT LLEVEL into ret
--    FROM TLOGLEVEL
--    WHERE LCODE = pCode;
     RETURN ret;
EXCEPTION
     WHEN OTHERS THEN
             return PLOGPARAM.DEFAULT_LEVEL;
END getTextInLevel;
/

Since my trace give me that the most performed simple query was:

SELECT LLEVEL FROM TLOGLEVEL
     WHERE LCODE = :b1

call     count       cpu    elapsed       disk      query    current 
rows
------- ------  -------- ---------- ---------- ---------- 
----------  ----------
Parse        0      0.00       0.00          0          0          0 
    0
Execute  15655      0.53       0.66          0          0          0 
    0
Fetch    15655      0.90       2.09        100      46965          0 
15655
------- ------  -------- ---------- ---------- ---------- 
----------  ----------
total    31310      1.43       2.75        100      46965          0 
15655

The code that forced this execution was the one that tested if the loging 
at level (Debug or Info) was enabled and
no logging was performed since the level was error.

The code  could be modified in a better way implementing a caching 
structure at session level on table TLOGLEVEL.

Regards,
         Francesco