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: @OracleSK
Chris Stephens <cstephens16@xxxxxxxxx> 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!
