Hello,
we are using dbms_pipe for writing log messages from many sessions to one session which is reading this log-pipe.
So in our system we have about 50 sessions producing these messages and only one process who is reading them.
The performance is generally good with this infrastructure, but when the load is to high, we have troubles and all the sessions are hanging with event "library cache pin".
Can anybody explain why this happens? It is not clear to me, because "library cache pin" has nothing to do with writing or reading from pipes.
I have seen this issue on 11.2.0.4 and on 12.1.0.2 Databases.
Top Waits:
SQLs ordered by Elapsed Time:
Program unit generating the messages (SQL_ID: 2kyyjnc6skvht):
declare
retVal# number;
begin
for i in 1 .. 100 loop
dbms\_pipe.pack\_message ('Hello File Debug World ('||i||')'||systimestamp);
retVal# := dbms\_pipe.send\_message (user||'.SQL\_EVENT', 120, 1048576);
end loop;
commit;
end;
Program unit reading the messages (SQL_ID: 0hpc3k7z0m8sq):
DECLARE
name$ VARCHAR2 (255) := :pipeName;
cacheSize# NUMBER := :cacheSize;
TYPE stringtable IS TABLE OF VARCHAR2 (4096)
INDEX BY PLS\_INTEGER;
messages\_ stringtable;
i# NUMBER := 0;
status# NUMBER;
BEGIN
LOOP
IF i# = 0 THEN
status# := DBMS\_PIPE.receive\_message (name$);
ELSE
status# := DBMS\_PIPE.receive\_message (name$, 0);
END IF;
IF status# = 0
THEN
i# := i# + 1;
DBMS\_PIPE.unpack\_message (messages\_ (i#));
END IF;
EXIT WHEN status# > 0 OR i# >= cacheSize#;
END LOOP;
:messages := messages\_;
END;
In the attachment is an AWR-Report where the exact Problem is visible. I have five sessions producing messages (sql_id: 2kyyjnc6skvht) and one session consuming them (sql_id: 0hpc3k7z0m8sq)
Regards,
Bernhard