Pipes

A question about seeing what’s in a pipe came up on the comp.databases.oracle.server newsgroup a few days ago – it was quite a simple question and one which really ought to have a simple reply:

I’m trying to work out how many messages are queued in a pipe and how long said messages are. Anyone got any ideas?

So far, the best I can come up with is something silly like receiving DBMS_PIPE messages, unpacking them, counting and adding lengths and then repacking and requeuing the messages.

What I would like is something like this:  select * from DBA_ALL_ABOUT_PIPES;

And DBA_ALL_ABOUT_PIPES has the name, type of pipe (public or private),  size, number of queued messages and length of messages.

Well, there is a view gv$db_pipes, which lists some of the rows from x$kglob for namespace 7. So that’s a starting point.

Space Checks

At least twice in the last few weeks, there’s been a question on OTN about a mismatch between dba_extents and dba_segments  after rebuilding some indexes.

Here’s one of the threads, where Randolf Geist supplies the explanation for the anomaly.

Analysing Statspack (11)

[Further Reading on Statspack]

Here’s an interesting example of a Statspack report that appeared recently on the OTN Database General Forum. It’s from a user who wants to know why an update to seg$ appears in second place in the “SQL ordered by …” sections of the report. I’ve set the link to open in a new window so that you can read the report and my comments at the same time.

Concurrency

When you get into big, busy, systems one of the final barriers you have to overcome is the concurrency issue; and after you’ve designed and fiddled and tweaked everything else it’s latch acquisition that is often the final barrier to extreme levels of concurrency.

Books

When I was in Salt Lake City a few months ago, Barbara Matthews (one of the organisers of the SLC Oracle User Group) asked me for my “Top 10” books about Oracle.

FBI Stats

It’s very easy to forget that 10g creates statistics on an index as it builds (or rebuilds) the index – and this can lead to some oddities when you add a function-based index (or, as I tend to name them, index on virtual columns) to a table.

This point (along with a couple of other observations) came up in a discussion on OTN a little while ago, with some useful responses from Richard Foote (the “index king”), Christian Antognini (author of  “Trouble-shooting Oracle Performance”) and a few others, including me.

Graphics

David Kurtz (an Oak Table member, and specialist in Peoplesoft) has justed posted a couple of interesting items on his blog.

The first is a note, with examples,  on how easily you can pull AWR into an Excel spreadsheet for graphing.

The second is an item about using this graphic approach to investigate a performance issue that started with spikes in waits on the “cache buffers chains” latches.

Motivation

There are many things that people do to Oracle systems to try to improve performance – adding hardware, tweaking parameters, adding indexes, changing block sizes, tuning the SQL.

When all else fails, you might even consider shouting at it – but don’t, it won’t work, as demonstrated in this video clip.

Thanks to Oak Table member Mario Broodbakker for sending me the link.

Index rebuild – again

Someone posted a very pertinent question about blocks splits and index rebuilds on the OTN Database forum yesterday covering a detail of the cost/benefit equation that I don’t think I’ve mentioned before.

Since I answered the question on the forum I’ve posted a link here to make it available to a wider audience. 

(This is a strategy I may adopt more frequently in the future – there’s a lot of useful material of mine all over the internet, and I really ought to make sure I don’t spend time repeating myself when  simple pointer would do).

[Updated May 2009: The forum item has since gone missing - so maybe linking to notes I've written in public groups is not so smart after all]. Fortunately I happen to have been saving copies of the thread as it progressed, and the little point that I wanted to highlight was just the following:

“When you create or rebuild an index Oracle does not honour the pctfree setting in the branch blocks. Since the branch blocks are effectively 100% packed, the very first leaf block split in each branch block (except the last one) will almost certainly result in a branch block split. This means a single row insert into an 8KB block could result in 16KB+ of redo for the leaf block plus 16KB+ for the branch block split.”

[Further reading on rebuilding indexes]

Index Collapse

There’s been an interesting discussion on the OTN Database forum about a specific issue that someone saw as a result of running a daily bulk delete/ bulk insert cycle on a specific  table.

The nature of the data, and the way they were working, resulted in a fairly serious collapse in the quality of the indexes on that table.

I made a number of points in the conversation that I’ve probably not published elsewhere, so I thought I’d post a link to the entire thing, rather than trying to collate and rewrite the details.

[Updated May 2009]: This thread has since gone missing from the OTN forum.