Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Library Cache Pins Waits

Status
Not open for further replies.

diepa

Programmer
Dec 28, 1998
16
0
0
US
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top