Hi,
We are running a VB application, all "sql code" is performed at the oracle database using packages and functions. We have a VERY HIGH AMOUNT of waits on the library cache pin. 26 boxes are running the same code. That wait is over a package that contains a procedure with the following:
PROCEDURE p_sel_Vocabulary(p_ABANumber Vocabulary.ABANumber%TYPE, p_AccountNumber Vocabulary.AccountNumber%Type, p_cursor OUT TableCursor) IS
BEGIN
OPEN p_cursor FOR
SELECT payee FROM vocabulary
WHERE ABANumber = p_ABANumber
AND AccountNumber = p_AccountNumber;
END;
That Vocabulary table is a Table containing 22 million rows, each select is retrieving an average of 80 rows. We don't perform inserts or deletes over that table. This is the query to see the waits and its results:
select a.sid, c.event, c.seconds_in_wait,
b.sql_text, c.p1, c.p1text, c.p2, c.p2text
from v$session a, v$sqlarea b, v$session_wait c
where a.sql_address = b.address
and a.sid=c.sid
and c.event not like 'rdbms%'
and c.event not like 'SQL%'
and c.event not like '%timer%'
order by 3 desc;
SID EVENT SEC_WAIT SQL_TEXT P1 P1TEXT P2 P2TEXT
17 "library cache pin" 2 "begin AR_SELECT.p_sel_Vocabulary
V00001,:V00002); end;" 2572623144 "handle address" 2479990176 "pin address"
22 "library cache pin" 2 "begin AR_SELECT.p_sel_Vocabulary
V00001,:V00002); end;" 2572623144 "handle address" 2504204344 "pin address"
23 "library cache pin" 2 "begin AR_SELECT.p_sel_Vocabulary
V00001,:V00002); end;" 2572623144 "handle address" 2507373304 "pin address"
8 "library cache pin" 2 "begin AR_SELECT.p_sel_Vocabulary
V00001,:V00002); end;" 2572623144 "handle address" 2480330008 "pin address"
55 "library cache pin" 2 "begin AR_SELECT.p_sel_Vocabulary
V00001,:V00002); end;" 2572623144 "handle address" 2503314376 "pin address"
56 "library cache pin" 2 "begin AR_SELECT.p_sel_Vocabulary
V00001,:V00002); end;" 2572623144 "handle address" 2508153824 "pin address"
65 "enqueue" 2 "begin AR_SELECT.p_sel_Vocabulary
V00001,:V00002); end;" 1129644038 "name|mode" 2572623144 "id1"
78 "library cache pin" 2 "begin AR_SELECT.p_sel_Vocabulary
V00001,:V00002); end;" 2572623144 "handle address" 2503316320 "pin address"
81 "library cache pin" 2 "begin AR_SELECT.p_sel_Vocabulary
V00001,:V00002); end;" 2572623144 "handle address" 2502627008 "pin address"
85 "library cache pin" 2 "begin AR_SELECT.p_sel_Vocabulary
V00001,:V00002); end;" 2572623144 "handle address" 2478213548 "pin address"
96 "enqueue" 2 "begin AR_SELECT.p_sel_Vocabulary
V00001,:V00002); end;" 1129644038 "name|mode" 2572623144 "id1"
99 "library cache pin" 2 "begin AR_SELECT.p_sel_Vocabulary
V00001,:V00002); end;" 2572623144 "handle address" 2479987728 "pin address"
103 "library cache pin" 2 "begin AR_SELECT.p_sel_Vocabulary
V00001,:V00002); end;" 2572623144 "handle address" 2479311164 "pin address"
106 "library cache pin" 2 "begin AR_SELECT.p_sel_Vocabulary
V00001,:V00002); end;" 2572623144 "handle address" 2479990968 "pin address"
Any help on this???? this is CRITICAL!!!
Thanks
We are running a VB application, all "sql code" is performed at the oracle database using packages and functions. We have a VERY HIGH AMOUNT of waits on the library cache pin. 26 boxes are running the same code. That wait is over a package that contains a procedure with the following:
PROCEDURE p_sel_Vocabulary(p_ABANumber Vocabulary.ABANumber%TYPE, p_AccountNumber Vocabulary.AccountNumber%Type, p_cursor OUT TableCursor) IS
BEGIN
OPEN p_cursor FOR
SELECT payee FROM vocabulary
WHERE ABANumber = p_ABANumber
AND AccountNumber = p_AccountNumber;
END;
That Vocabulary table is a Table containing 22 million rows, each select is retrieving an average of 80 rows. We don't perform inserts or deletes over that table. This is the query to see the waits and its results:
select a.sid, c.event, c.seconds_in_wait,
b.sql_text, c.p1, c.p1text, c.p2, c.p2text
from v$session a, v$sqlarea b, v$session_wait c
where a.sql_address = b.address
and a.sid=c.sid
and c.event not like 'rdbms%'
and c.event not like 'SQL%'
and c.event not like '%timer%'
order by 3 desc;
SID EVENT SEC_WAIT SQL_TEXT P1 P1TEXT P2 P2TEXT
17 "library cache pin" 2 "begin AR_SELECT.p_sel_Vocabulary
22 "library cache pin" 2 "begin AR_SELECT.p_sel_Vocabulary
23 "library cache pin" 2 "begin AR_SELECT.p_sel_Vocabulary
8 "library cache pin" 2 "begin AR_SELECT.p_sel_Vocabulary
55 "library cache pin" 2 "begin AR_SELECT.p_sel_Vocabulary
56 "library cache pin" 2 "begin AR_SELECT.p_sel_Vocabulary
65 "enqueue" 2 "begin AR_SELECT.p_sel_Vocabulary
78 "library cache pin" 2 "begin AR_SELECT.p_sel_Vocabulary
81 "library cache pin" 2 "begin AR_SELECT.p_sel_Vocabulary
85 "library cache pin" 2 "begin AR_SELECT.p_sel_Vocabulary
96 "enqueue" 2 "begin AR_SELECT.p_sel_Vocabulary
99 "library cache pin" 2 "begin AR_SELECT.p_sel_Vocabulary
103 "library cache pin" 2 "begin AR_SELECT.p_sel_Vocabulary
106 "library cache pin" 2 "begin AR_SELECT.p_sel_Vocabulary
Any help on this???? this is CRITICAL!!!
Thanks