Oracle Scratchpad

April 17, 2010

Row count

Filed under: Troubleshooting — Jonathan Lewis @ 11:13 am GMT Apr 17,2010

Here’s a quick and dirty script I wrote some time ago when looking at a problem relating to “buffer busy waits”. We were seeing a large number of waits on a particular table (which we could identify easily from the “Segments by …” part of the AWR report , and I wanted to be able to see how much data was in the block while it was under stress. The point of being able to query the block (repeatedly) in near real time was that it always seemed to be empty if you waited a couple of minutes.  

Update: I’ve realised that I’ve also used this query more recently to demonstrate that some blocks which were behaving “unreasonably” weren’t empty at the time. (Of course “empty” is slightly ambiguous term in this context, even when a block appears to be empty it may have rows in it that are invisible for reasons of read-consistency; in this case I just needed to know that there were rows that could be seen by other sessions.) 

The query was cunningly constructed to access all rows that might appear (in read-consistent mode) in the given block; the /*+ rowid(t) */ hint ought to be redundant, but I like to play safe when I do this type of thing on a production system – even after testing it on a development system.  The “magic number” 40189 was the data_object_id of table as recorded in the view dba_objects, and the two input parameters were the p1 and p2 values given in v$session_wait as the target buffer. The other “magic number” of 4,096 is there because that’s the maximum number of row directory entries you can have in a single block. 

select
        /*+ rowid(t) */
        &1, &2,
        count(*)
from
        tableX t
where
        rowid   between (
                        select
                                dbms_rowid.rowid_create(
                                        1,
                                        40189,
                                        &1,
                                        &2,
                                        0
                                )
                        from
                                dual
                )
                and     (
                        select
                                dbms_rowid.rowid_create(
                                        1,
                                        40189,
                                        &1,
                                        &2,
                                        4096
                                )
                        from
                                dual
                )
;

Update:

I’ve realised that there’s a possible complication to using this script. The file number you supply to dbms_rowid.rowid_create() is supposed to be the relative file number, but if you’ve been using the p1 parameter on “buffer busy waits” to get a file number then you may be looking at the absolute file number.

If this is the case (and it’s only a hypothesis at present, I haven’t confirmed it) then it isn’t actually going to matter in a large number of systems because Oracle has cunningly rigged things so that the absolute and relative file numbers are the same until you have more than (roughly) 1024 files in the database. If you do go above the limit then you ought to use the object_id to identify the tablespace and use the tablespace id to check what file number you should be using in the call to dbms_rowid.create_rowid().

Update on update:

If you are using bigfile tablespaces then the relative file number for the single file you’re allowed in the tablespace always seems to be 1024. You can always cross-check with a simple query:

select file#, rfile#, ts#, name from v$datafile;

1 Comment »

  1. [...] — Jonathan Lewis @ 7:42 pm UTC May 5,2010 Following my notes about counting rows in blocks, someone emailed me to ask how the query I’d published would deal with migrated rows. [...]

    Pingback by Row count 2 « Oracle Scratchpad — May 5, 2010 @ 7:43 pm GMT May 5,2010 | 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

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,422 other followers