Re: cursor: pin S wait on X querying data dictionary

Hey Chris,
this should be a pretty easy one as you are already on an Oracle version >= 12.1.0.2.160419 (PSUApr2016) - fix_control #16923858. An example of this enhancement can be found on one of my slides (slide 19): http://www.soocs.de/public/talk/160616_DOAG_Regio_NUE_Identifying_Performance_Issues_Beyond_The_Oracle_Wait_Interface_PPT.pdf

Just create a cost based optimizer trace for this SQL and you see where the time is lost while parsing. Franck Pachot has also published some nice parsing script for this enhancement: https://blog.dbi-services.com/cbo-parse-time-match-time-with-transformation/

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher Website: http://www.soocs.de
Twitter: _at_OracleSK

> Chris Stephens <cstephens16_at_gmail.com> hat am 29. Juni 2018 um 16:26 geschrieben:
>
> 3-node RAC 12.2 database on Centos7
>  
> We have an application which makes use of SQLAlchemy that is suffering mightily from "cursor: pin S wait on X" executing the following SQL:
>  
> SELECT col.column_name, col.data_type, col.char_length,
>     col.data_precision, col.data_scale, col.nullable,
>     col.data_default, com.comments
> FROM all_tab_columns col
>     LEFT JOIN all_col_comments com
>     ON col.table_name = com.table_name
>     AND col.column_name = com.column_name
>     AND col.owner = com.owner
>     WHERE col.table_name = :table_name
>     AND col.owner = :owner  ORDER BY col.column_id
>
> The application will launch ~200 concurrent sessions on startup. There is a long initial period where many of them are waiting on the event then things start clearing up and processing proceeds as expected. There are several different plan hash values for the SQL, one of which didn't seem to suffer from the issue so I created a sql baseline and crossed my fingers. I've collected dictionary statistics as well but that made no difference.
>  
> While I do my own searching, does any one have any suggestions on how to remove the delays?
> Anyone have any ideas?
>  
> Thanks as always!

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