Experiences like this one [Ed: Nov 2008 - the blog has become private since I wrote this note]are always worth reading about to remind yourself what you can do with the dbms_stats package when it’s really necessary.
And while I’m pointing to other URLs, here’s another one worth knowing about – event “Cursor: pin S wait on X”. It’s not surprising to see this wait event occasionally in a busy 10g system, but if you’re losing a significant amount of time, it could be a bug.
Hi Jonathan,
We had problems with the “Cursor: pin S wait on X” today, but under slightly different circumstances to the post you linked to. I thought you’d like to know about it, for completeness!
We’ve recently upgraded to 10.2.0.3 from 10.2.0.2, and we’ve been having some problems with long-running queries (not related to the upgrade) for a while. So, we got the DBA’s involved today, having come to the end of our own investigations.
He tried to trace the session using DBMS_MONITOR.SESSION_TRACE_ENABLE, but couldn’t get any results due to the session already running the query. Anyway, a while later, we noticed that the query was hanging with the aforementioned wait event.
Some digging around on metalink came up with this document suggesting dbms_monitor was the cause, and suggesting that the root cause might be use of SQL_TRACE.
There is a patch for this issue, but we’ve not had time to try it out, yet.
Comment by Boneist — August 29, 2007 @ 1:22 pm UTC Aug 29,2007 |
[...] . Jonathan Lewis also picked it up http://jonathanlewis.wordpress.com/2007/08/20/intelligent-stats.There is however in my (reproducible) case no call to dbms_stats. As some of the related bugs refer [...]
Pingback by Can an explain plan on a SELECT self deadlock? Yes it can … but it is a bug « Christian Bilien’s Oracle performance and tuning blog — November 8, 2007 @ 11:49 am UTC Nov 8,2007 |