Oracle Scratchpad

August 20, 2007

Intelligent Stats

Filed under: CBO,Partitioning,trace files,Troubleshooting — Jonathan Lewis @ 5:43 pm BST Aug 20,2007

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.


  1. 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 from, 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 BST Aug 29,2007 | Reply

  2. […] . Jonathan Lewis also picked it up 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 BST Nov 8,2007 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by