Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, sanjiv.

Asked: September 13, 2017 - 10:58 am UTC

Last updated: August 27, 2020 - 1:35 pm UTC

Version: 11.2.0.3.0

Viewed 10K+ times! This question is

You Asked

Greetings,
As per my AWR report::


Event                          Waits         Time(s)    Avg wait (ms) % DB time Wait Class
-----------------------------
SQL*Net more data from client  35,972         9,805    273                 65.90         Network
cursor: pin S wait on X          159         5,688    35770         38.23         Concurrency
DB CPU                                   1,629                   10.95  
enq: TX - row lock contention  9         136    15133         0.92         Application
direct path write temp          63,834         82    1                 0.55         User I/O


Wait class concurrency is the major issue i begin a DBA should solve. So, what does it refer to and what should i do to remove the wait class from
the top 5 wait list?

Thank you in advance.

and Chris said...

How many of your application's users are complaining about "cursor: pin S wait on X" waits?

If you said anything more than zero, I'd be utterly amazed.

You see it's not wait events you need to tune. It's business transactions. Find out which of these are slow. Then trace them to see what they're doing.

Now, it may turn out these waits causing these transactions to be slow. But unless you take the user centred view, you don't know if what you're doing makes the application faster for the users.

But even if it does turn out slow business transactions are spending lots of time waiting for this, you still don't know enough to take action. This wait is more of a symptom than a root cause. It's related to parsing. But there are lots of things that could cause "cursor: pin S wait on X" as MOS note 1349387.1 discusses:

What is a 'Cursor: pin S wait on X' wait?

A cursor wait is associated with parsing in some form. A session may wait for this event when it is trying to get a mutex pin in Share mode but another session is holding the mutex pin on the same cursor object in exclusive.  Frequently, waits for 'Cursor: pin S wait on X' is a symptom and not the cause.  There may be underlying tuning requirements or known issues.

What causes 'Cursor: pin S wait on X' waits?

* Firstly, ensure that the shared pool is sized correctly. 

If the shared pool is under sized or under load generally, this may manifest itself as 'Cursor: pin S wait on X'. If Automatic Memory Management is being used then this should not normally be an issue. See:

Document 443746.1 Automatic Memory Management (AMM) on 11g
 
* Frequent Hard Parses
If the frequency of Hard Parsing is extremely high, then contention can occur on this pin.
* High Version Counts
When Version counts become excessive, a long chain of versions needs to be examined and this can lead to contention on this event
* Known bugs
* Parse Errors, as following note indicates:

Document 1353015.1 How to Identify Hard Parse Failures

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

standalone storage procedure may cause 'Cursor: pin S wait on X'.

A reader, July 23, 2020 - 11:07 am UTC

Did your standalone storage procedure become INVALID (need to be recompiled)?
If so, as Tom said: Don't use standalone procedures! Use packages. Packages break the dependency chain.
Quoted from: https://asktom.oracle.com/pls/apex/asktom.search?tag=compile-procedure-automatically-how-to-avoid-cascading-invalidations

Notice that, standalone storage procedure (espatially dependency chain exists) may cause 'Cursor: pin S wait on X'.

for instance, modify table structure (event add a column) may result in 'Cursor: pin S wait on X'.
[EXAMPLE]
SQL> create table a2(id1 int,cc varchar2(200));

Table created.

SQL> insert into a2 values (1,'one');

1 row created.

SQL> insert into a2 values (2,'two');

1 row created.

SQL> create table b2(id1 int);

Table created.

SQL> insert into b2 values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> create or replace procedure test1 is
2 mname varchar2(200);
3 begin
4 select cc into mname from a2 join b2 on a2.id1=b2.id1 where a2.id1=1;
5 dbms_lock.sleep(3000); -- SUPPOSE THAT TEST1 TAKES 3000 SECONDS TO FINISH RUNNING.
6 end;
7 /

Procedure created.

SQL>



>> SESSION 1:
SQL> SELECT SID, SERIAL# FROM V$SESSION WHERE SID = SYS_CONTEXT('USERENV', 'SID');

SID SERIAL#
---------- ----------
428 131

SQL> exec test1;

>> SESSION 2:
SQL> alter table b2 add c int;

Table altered.

SQL> SELECT object_name, object_type, STATUS FROM dba_objects WHERE object_name='TEST1' AND owner='N2ADMIN';

OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE STATUS
----------------------- -------
TEST1
PROCEDURE INVALID

-- NOTICE THAT: the procedure became INVALID!!!!

SQL>
SQL> exec test1; -- HANGS

>> SESSION 3:
SQL> select sid,event,p1raw from gv$session where wait_class<>'Idle' order by event;

SID EVENT
---------- ----------------------------------------------------------------
P1RAW
----------------
20 SQL*Net message to client
0000000054435000

294 library cache pin
00000000749B8910


SQL> exec test1;

>> SESSION 4
SQL> exec test1;

>> SESSION 5
SQL> exec test1;

-- ALL THE EXCUTION OF TEST1 WOULD HANG!!!

>> SESSION 6:
SQL> set linesize 160
SQL> select sid,final_blocking_session,event,p1raw from gv$session where wait_class<>'Idle' order by event;

SID FINAL_BLOCKING_SESSION EVENT P1RAW
---------- ---------------------- ---------------------------------------------------------------- ----------------
19 SQL*Net message to client 0000000054435000
20 428 cursor: pin S wait on X 00000000AFF613A7
434 428 cursor: pin S wait on X 00000000AFF613A7
305 428 cursor: pin S wait on X 00000000AFF613A7
294 428 library cache pin 00000000749B8910

-- PLZ NOTICE THAT, SESSION 3/4/5 WERE BLOCKED BY SESSION 1(SID=428), CAME ALONG WITH WAIT EVENT 'cursor: pin S wait on X'.
SQL>

SQL> ALTER SYSTEM KILL SESSION '428,131';

System altered.

SQL> select sid,final_blocking_session,event,p1raw from gv$session where wait_class<>'Idle' order by event;

SID FINAL_BLOCKING_SESSION EVENT P1RAW
---------- ---------------------- ---------------------------------------------------------------- ----------------
19 SQL*Net message to client 0000000054435000

SQL>
-- AFTER SESSION 1 BEING KILLED, THE WAIT EVENT JUST GONE...TO AVOID FROM THIS SITUATION, USE PACKAGES RATHER THAN STANDALONE PROCEDURES!

REFERENCES FROM: Oracle内核技术揭秘 (吕海波) -- book written by Lv Haibo

Chris Saxon
July 23, 2020 - 11:20 am UTC

Thanks for sharing.

library cache pin and packages

Mikhail Velikikh, August 27, 2020 - 11:43 am UTC

> TO AVOID FROM THIS SITUATION, USE PACKAGES RATHER THAN STANDALONE PROCEDURES!

Can you please demonstrate how you avoid that situation using packages?
You will get the same 'library cache pin' events with packages. While the package is being executed by the first session, a library cache pin is held on the package body in share mode. When the second session tries to compile that package, it attempts to get the pin in exclusive mode - it is obviously incompatible with share mode and has to wait till the first session releases the pin.

Here is the code that I ran:
-- session 1

create table a2(id1 int,cc varchar2(200));

insert into a2 values (1,'one');
insert into a2 values (2,'two');

create table b2(id1 int);
insert into b2 values (1);
commit;

create or replace package pkg is
  procedure test1;
end;
/
create or replace package body pkg is
  procedure test1 is
  mname varchar2(200);
  begin
  select cc into mname from a2 join b2 on a2.id1=b2.id1 where a2.id1=1;
  dbms_session.sleep(3000); -- SUPPOSE THAT TEST1 TAKES 3000 SECONDS TO FINISH RUNNING.
  end;
end;
/

SELECT SID, SERIAL# FROM V$SESSION WHERE SID = SYS_CONTEXT('USERENV', 'SID');

exec pkg.test1

-- session 2
alter table b2 add c int;

SELECT object_name, object_type, STATUS FROM obj WHERE object_name='PKG';

exec pkg.test1

-- session 3

select sid,event,p1raw from gv$session where wait_class<>'Idle' order by event;

exec pkg.test1

-- session 4

exec pkg.test1

-- session 5

exec pkg.test1



Session 6:
SQL> select sid,final_blocking_session,event,p1raw from gv$session where wait_class<>'Idle' order by event;

       SID FINAL_BLOCKING_SESSION EVENT                          P1RAW
---------- ---------------------- ------------------------------ ----------------
        39                        library cache pin              000000006EE33310
       412                        library cache pin              000000006EE33310
       278                        library cache pin              000000006EE33310
       160                     26 library cache pin              000000006EE33310

SQL> r
  1  select s1.sid, s1.event, s2.sid blocker, oc.name, oc.type object_type,
  2         l.type lock_type,
  3         decode(l.mode_held, 1, 'null', 2, 'share', 3, 'exclusive') mode_held,
  4         decode(l.mode_requested, 1, 'null', 2, 'share', 3, 'exclusive') mode_requested
  5    from v$session s1,
  6         v$libcache_locks l,
  7         v$db_object_cache oc,
  8         v$session s2
  9   where l.holding_user_session = s1.saddr
 10     and oc.addr = l.object_handle
 11     and oc.owner = 'TC'
 12     and oc.type in ('PACKAGE', 'PACKAGE BODY')
 13     and s1.blocking_session = s2.sid(+)
 14     and s1.username = 'TC'
 15*  order by s1.logon_time, lock_type, object_type

       SID EVENT                             BLOCKER NAME       OBJECT_TYPE     LOCK_TYPE MODE_HELD MODE_REQU
---------- ------------------------------ ---------- ---------- --------------- --------- --------- ---------
        26 PL/SQL lock timer                         PKG        PACKAGE         LOCK      null
        26 PL/SQL lock timer                         PKG        PACKAGE BODY    LOCK      null
        26 PL/SQL lock timer                         PKG        PACKAGE         PIN       share
        26 PL/SQL lock timer                         PKG        PACKAGE BODY    PIN       share
       160 library cache pin                      26 PKG        PACKAGE         LOCK      null
       160 library cache pin                      26 PKG        PACKAGE BODY    LOCK      exclusive
       160 library cache pin                      26 PKG        PACKAGE         PIN       share
       160 library cache pin                      26 PKG        PACKAGE BODY    PIN                 exclusive
       278 library cache pin                         PKG        PACKAGE         LOCK      null
       278 library cache pin                         PKG        PACKAGE BODY    LOCK      null
       278 library cache pin                         PKG        PACKAGE         PIN       share
       278 library cache pin                         PKG        PACKAGE BODY    PIN                 share
       412 library cache pin                         PKG        PACKAGE         LOCK      null
       412 library cache pin                         PKG        PACKAGE BODY    LOCK      null
       412 library cache pin                         PKG        PACKAGE         PIN       share
       412 library cache pin                         PKG        PACKAGE BODY    PIN                 share
        39 library cache pin                         PKG        PACKAGE         LOCK      null
        39 library cache pin                         PKG        PACKAGE BODY    LOCK      null
        39 library cache pin                         PKG        PACKAGE         PIN       share
        39 library cache pin                         PKG        PACKAGE BODY    PIN                 share

20 rows selected.


The last query is a small modification of Oren Nakdimon's query from http://db-oriented.com/2017/12/05/ebr-part-2-changing-a-package-body-the-problems/ . I just replaced X$KGLOB with V$DB_OBJECT_CACHE which is more accessible.
Chris Saxon
August 27, 2020 - 1:35 pm UTC

Good point. Packages break the dependency chain, so invalidations won't cascade all the way up like they do with standalone procedures. So it's less likely you'll need to recompile an object.

But it doesn't completely remove this issue as you say.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database