Working with database performance troubleshooting means dealing with challenging and exciting problems. But it also means doing certain things over and over. And over. Like typing the same query or clicking the same button zillion times a day. And when you’re doing something very frequently, anything that can be done to simplify or automate such tasks becomes very important. So I decided to share some of the tricks I use to optimize my working experience in a short mini-series. This post will be on GUI, and the part II will be on command line.
Occasionally one might want to know what a segment is made of in terms of block types. For example, you notice that the number of blocks in an index segment is somewhat larger than the number of branch and leaf blocks, and wonder what kind of blocks accounts for the difference. The only way to do this is by dumping index blocks (e.g. as described in Richard Foote’s blog here). Dumping blocks is easy, but analyzing them — not so much. Sure, there exists a plethora of tools that allow to parse text from the OS side (awk, perl, sed and whatnot), but this leads to usual problems: OS access, scripting skills, certain platforms may not have the scripting tool you’re most comfortable with, and even more importantly: scripts cannot do cool stuff that Oracle can (like joining data to other data) . Fortunately, those difficulties can be circumvented by using regexp + external files as I already posted in my blog here. This time, I’d like to show how this technique can be adjusted for index block dumps.