Massive Wait Events "Cursor: pin S wait on X" with PL/SQL in

  • From: "Martin Klier" <Martin.Klier@xxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 6 May 2010 14:43:19 +0200

Hi listers,

I've got massive wait events Cursor: pin S wait on X in Oracle DB
on AIX when users are starting PL/SQL packages in parallel to a PL/SQL
scheduler job.

Since the wait event seems to point into massive manipulation in cursor
cache, I tried to see which statements are most seen in v$sql, just to get
a feeling what might stress the cursor cache most (statement made by Tanel

SELECT hash_value, COUNT(*)
FROM v$sql
GROUP BY hash_value

Only one SQL matched the criteria, with about 30 occurences in v$sql. So
lets see what statement it is:
select * from v$sql where hash_value in ('2920152077');

the SQL is:
select procedure#,procedurename,properties,itypeobj# from procedureinfo$
where obj#=:1 order by procedurename desc, overload# desc;

This one is nothig that can come from the software running here, I guess
its a select from PL/SQL core, but I may be mistaken.

So two questions:
1) Where does this SELECT come from?
2) Why does it cuase THAT massive Cursor: pins S wait on X?

One theory: We had problems with Adaptive Cursor Sharing in this DB.

Oracle Support stated, that setting SESSION_CACHED_CURSORS=0 on a
per-session basis where needed might fix that PL/SQL cursors don't adapt.
is it possible/known that this does bit us back now? Does somebody know any
facts on this issue?

Thanks a lot and best regards
Mit freundlichem Gruß

Martin Klier
Senior Oracle Database Administrator

Klug GmbH integrierte Systeme
Lindenweg 13, D-92552 Teunz
Tel.:  +49 9671/9216-245
Fax.: +49 9671/9216-112
mailto: martin.klier@xxxxxxxxxx

Geschäftsführer: Johann Klug, Roman Sorgenfrei
Sitz der Gesellschaft: Teunz, USt-ID-Nr. DE175481608,
HRB Nr. 2037, Amtsgericht Amberg


Other related posts: