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.

2 Comments »

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

  2. [...] . 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 BST Nov 8,2007 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,530 other followers