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

Hi listers,

I've got massive wait events Cursor: pin S wait on X in Oracle DB 11.1.0.7
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
Poder)

SELECT hash_value, COUNT(*)
FROM v$sql
GROUP BY hash_value
HAVING COUNT(*) > 10;

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.
(
http://www.usn-it.de/index.php/2010/03/08/oracle-11g-adaptive-cursor-sharing-does-not-work-with-plsql/
)

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
www.klug-is.de
------------------------------------------------------------------------------

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

--
http://www.freelists.org/webpage/oracle-l


Other related posts: