Re: Massive Wait Events "Cursor: pin S wait on X" with PL/SQL in 11.1.0.7
- From: Dion Cho <ukja.dion@xxxxxxxxx>
- To: Martin.Klier@xxxxxxxxxx
- Date: Thu, 6 May 2010 23:55:58 +0900
Querying V$MUTEX_SLEEP_HISTORY view would be another starting point - who is
blocking mutex to execute which codes?
================================
Dion Cho - Oracle Performance Storyteller
http://dioncho.wordpress.com (english)
http://ukja.tistory.com (korean)
http://dioncho.blogspot.com (japanese)
http://ask.ex-em.com (q&a)
================================
2010/5/6 Martin Klier <Martin.Klier@xxxxxxxxxx>
>
> 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
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
Other related posts:
