lock CI: cross-instance function invocation instance

From: Erik Ykema <dead.letter_at_xs4all.nl>
Date: 20 Jan 2003 09:52:06 -0800
Message-ID: <8a96bbb4.0301200952.70585b94@posting.google.com>

Oracle 8.0.5 parallel server, RS/6000 AIX Unix V4. I have rewritten a multi-threaded pro*c interface application which calls a pl/sql procedure in a package. Some globals are used and declared in the packages specification. The procedure is selecting values from some joined tables (of course), and also calling (in the select list) a function, using one of the selected columns as an argument, the globals are the other arguments. (See code example below.) I had to fiddle with PRAGMA RESTRICT REFERENCES in order to get the package compiled (WNDS, WNPS).
Performance is 'sub-optimal'. Since tuning is a little new to me, I used TOAD's Session Monitor, which tells me that usually 4-6 of the thirteen concurrent threads of the program are waiting for a lock type CI to be released. Since the constantly changing behaviour I have not been able to grasp the statement in execution by the lock-holder. The dynamic performance views manual gives me: CI - cross-instance function invocation instance. I have roamed Google but cannot get a clue, nor OraDoc.
The (pl/)sql that waits for the lock to be released is a plain insert (DDL directly in the pl/sql block). Slowly the process comes to an end.
What should I look for in V$SESSION and related dynamic performance views.
Thanks in advance,
Erik Ykema
Oracle Developer
(Forms 4.5/Reports 2.5, RDBMS 7.3.4/8.0.5 - time for some upgrades...)

--The cursor using the function:
CURSOR c_boh (

b_dept DEPS.dept%TYPE
, b_wh WH.WH%TYPE

) IS
select ib.region
, dl.class
, dl.subclass
, its.season_id
, sum (nvl(ib.boh_qty, 0)) as qty
, sum (f_unit_retail(g_chain, g_region, ib.sku)) * sum
(nvl(ib.boh_qty, 0)) as amt

  from nb_mak_ib_sku_wh ib

, desc_look dl
, item_seasons its

where ib.sku = its.item

  and ib.sku = dl.sku 
  and dl.dept = b_dept
  and ib.wh = b_wh

group by ib.region
, dl.class
, dl.subclass
, its.season_id

;
-- the function
BEGIN

c_unit_retail is a plain select from some tables OPEN c_unit_retail( p_chain , p_region , p_sku ); FETCH c_unit_retail INTO l_unit_retail; CLOSE c_unit_retail; ; return l_unit_retail ; END f_unit_retail;

Received on Mon Jan 20 2003 - 11:52:06 CST

lock CI: cross-instance function invocation instance - how to approach?