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

dead.letter_at_xs4all.nl (Erik Ykema) wrote in message news:<8a96bbb4.0301200952.70585b94_at_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.

Can you tell us what values are under ID1 and ID2 columns in v$lock where type = 'CI'? Too much CI lock seems to be a result of other problems, if I understand correctly. You may want to tell us what's in v$session_wait for the session in question. If it comes and goes so fast, capture it in your code. Also, what's significant in v$system_event (look at time_waited)?

Yong Huang Received on Mon Jan 20 2003 - 21:17:29 CST