[Further Reading on Statspack]
While teaching a session on explain plan and associated technology today, I mentioned the sprepsql.sql script that can be used to report execution plans. At the end of the session one of the attendees asked me if I was aware of a bug with v$sql_plan that could cause the statspack snapshot call to hang the database on a library cache lock until you killed the process.
In fact I was aware of it, but had completely forgotten about it. So at the end of the day I did a quick search on Metalink to see if I could find a reference. It looks like bug 3778541, opened on 20th July 2004, last updated 20th July 2006 (with predicted fix in version 9.2.0.99 – so that looks like a long lifetime for 9.2 ;-)). However this bug is, in fact, reported as fixed in the patch list for 9.2.0.7.
Comments and related questions are welcome.