Hi,
in the PLOG.log you call the procedure getNextID(pCTX) (which calls SLOG.nextval) even if the DEFAULT_LEVEL is smaller than the pLevel and no logging should be done!
If you call getNextID(pCTX) after the If..Then-clause you get a 16x (tested on my 9.2-database) better performance for your test with workload1.
PROCEDURE log
(
pCTX IN OUT NOCOPY LOG_CTX , -- Context
pLEVEL IN TLOG.LLEVEL%type , -- log level
pTEXTE IN TLOG.LTEXTE%type default DEFAULTEXTMESS -- log text
) IS
lId TLOG.ID%type ;--:= getNextID(pCTX); -- BAD PERFORMANCE
lLSECTION TLOG.LSECTION%type := getSection(pCTX);
lLHSECS TLOG.LHSECS%type ;
m varchar2(100);
BEGIN
IF pLEVEL > getLevel(pCTX) THEN
RETURN;
END IF;
lId := getNextID(pCTX); -- GOOD PERFORMANCE
Do you agree or not?
Thanks
Detlef
