Oracle Scratchpad

June 2, 2009

Fixed Stats

Filed under: Execution plans,Performance,Statistics,Troubleshooting,Tuning — Jonathan Lewis @ 7:19 pm BST Jun 2,2009

There was a question about the notorious dba_extents view on the comp.databases.oracle.server newsgroup a little while ago.

This is a view that has been popularly abused over the years despite warnings such as a note of mine in 2006 and even back as far as 2001 this one from Connor McDonald.

There are many reasons why this is a nasty view – and the number of reasons has grown as Oracle has evolved – but David FitzJarell has highlighted an important point in one of his blog postings that was worth a mention. So here it is.

6 Comments »

  1. Hi,
    Here is a view that is similar to dba_extents and dba_free_space, but optimized for an access by file_id and block_id :

    http://www.dba-village.com/village/dvp_scripts.ScriptDetails?ScriptIdA=2543

    You can also see it in Metalink Note Id: 379408.1 – but poorly formatted
    Regards,
    Franck.

    Comment by Franck Pachot — June 3, 2009 @ 8:37 am BST Jun 3,2009 | Reply

  2. Hi,

    I started the thread but it was not a question but just something I wanted to share.
    Sometimes you have to use dba_extents unless you want to dig in the x$-tables.
    A couple of weeks I was called in to help recover as much as possible from a large database with corruptions and from which a number of archives where missing.
    To be able to determine what segments where involved we needed dba_extents.

    Regards Hans-Peter

    Comment by Hans-Peter Sloot — June 3, 2009 @ 12:09 pm BST Jun 3,2009 | Reply

    • Hans-Peter,
      You’re right, of course, it didn’t start as a question – I had just jotted a few details, URLs etc. into a draft note, and then wrote it up some days later without checking back the exact context.

      Comment by Jonathan Lewis — June 3, 2009 @ 6:49 pm BST Jun 3,2009 | Reply

  3. @Franck: for dba_free_space there was already a good alternative:

    http://www.jlcomp.demon.co.uk/faq/free_space.html

    regards Hans-Peter

    Comment by Hans-Peter Sloot — June 3, 2009 @ 12:18 pm BST Jun 3,2009 | Reply

    • Hans-Peter,
      One of the things that I’m really proud of with that whole FAQ is that every page has a date and version number on it. And in that particular conrtibution you’ve even got the explicit warning that: “Of course, as versions of Oracle change, you may have to keep updating the definitions of your views to cater for new features.” The approach, and the comment, should appear much more frequently all over Oracle community.

      Comment by Jonathan Lewis — June 3, 2009 @ 6:52 pm BST Jun 3,2009 | Reply

  4. [...] Oracle Tips by David Fitzjarrell Filed under: Blogroll — coskan @ 9:58 pm I just discovered the Oracle Tips blog of David Fitzjarrell, by the help of this post from Jonathan Lewis . [...]

    Pingback by Oracle Tips by David Fitzjarrell « Coskan’s Approach to Oracle — June 3, 2009 @ 9:59 pm BST Jun 3,2009 | 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 4,015 other followers