Real-Time SQL Monitoring in Oracle 11g

Greg Rahn, a database performance engineer with Oracle's Real-World Performance group posted a blog entry in January that details a new feature in Oracle 11g that should be extremely useful; real-time SQL monitoring.

There have been multiple occurrences of a developer coming up to me and asking "My query has been running for a long time, can you see where it is?" The answer has always been no (not entirely true if it is classified by the database as a long-running query but then it still just gives you an estimate of what percentage of the query is complete. With Oracle 11g, you can see what portion of the execution plan has been executed and where the time has been spent so far. It is also quite useful for analysis after the query has completed to see what steps are the most expensive, both in terms of CPU and IO, as well as allowing you to compare the estimated rows for each step to the actual rows.

Take a look at Greg's post on his blog Structered Data

Lock Code on n800 - Followup

Okay, it turns out that performing a user-level OS flash does unlock the device, it just doesn't reset the lock code. Now I am off to see if I can figure out where to find the lock code in /dev/mtd* per a posting on the Internet Tablet Talk forums.

EDIT:

Here is a script that can be used to retrieve the lock code if you have access to xterm on the device (as in it isn't currently locked) or if you have SSH access. If the device is locked and you don't have SSH, you will probably have to reflash your device (unless you can come up with another way to unlock it - I couldn't).

#!/usr/bin/perl
#
# Extract lock code from n800 - requires access to /dev/mtd1
#
# This is so that if you set your lock code and then forget it, you can recover
# it. This will require that either the n800 is not locked or you have access
# to the n800 via SSH. If you have locked the device and you don't have
# access via SSH, you will need to reflash the device in order to unlock it
# (which will mean that you will lose any data on the device) and then you can
# use this script to determine what the code is (since it is not reset by
# flashing the device).
#
# Usage:
# getLockCode.pl (MTD1_DUMP)
#
# MTD1_DUMP - Dump of /dev/mtd1 (use cat /dev/mtd1). If not specified,
# the script will simply look at /dev/mtd1
#
# Mysticode
# http://mysticode.blogspot.com/
# February 12, 2008

use strict;
use POSIX;

# Constants
my $LOCK_OFFSET = 0x41028;
my $LOCK_LENGTH = 10;

# Determine if Dump File Specified
my $mtd1File = "/dev/mtd1";
if ($#ARGV >= 0)
{
$mtd1File = $ARGV[0];
}

# Open File
my $openRet = open(INFILE, $mtd1File);
if (!$openRet)
{
print "Error opening " . $mtd1File . "\n";
exit -1;
}

# Move to Offset
seek(INFILE, $LOCK_OFFSET, POSIX::SEEK_SET);

# Read Lock Code
my $lockCode;
read(INFILE, $lockCode, $LOCK_LENGTH);

# Display Lock Code
printf("Lock Code: %s\n", $lockCode);

# Close File
close(INFILE);

Lock Codes

I have an n800. Yesterday, I managed to hit "Lock Device" instead of "Lock Touchscreen" and now it requires a lock code to unlock it. Either I changed the lock code months ago and forgot it or it isn't set to the default because I can't unlock it and apparently the only way around this is to send it to Nokia and pay them to unlock it (by flashing the whole thing - which means I lose anything that I have added since the last backup - great!).

Unfortunately, apparently the lock code is stored outside of the normal image so even doing a user-level OS flash won't wipe it out. I am going to try and see if it at least unlocks the device because I have found details of how to determine the lock code once you are on the device - but that doesn't help me right now.

I think this is simply a money-grab for Nokia since they designed the device such that it is easy to lock it without being able to unlock it except by knowing the code which you may have forgotten. This isn't going to stop theft as: 1. the thieves are unlikely to know whether the device is locked or not, 2. the thieves are unlikely to know that they would have to send it to Nokia to get the lock code removed, 3. Nokia isn't going to do any sort of check to ensure that they are the proper owner. In terms of protecting the data, well if you had to perform a user-level OS flash that would wipe out any data anyway (except on the SD cards - but a thief could put the SD card in a card reader and get the data off of it anyway).

2 Weeks!

Somehow it has already been over two weeks since I created this blog and I haven't managed to even get a second post up so I thought I would throw one up with some details on moving LOB segments in an Oracle database (since that is what I am currently doing - gotta love having to perform maintenance outside of normal work hours).

Anyway, moving a LOB segment is pretty easy. First, you need to determine what table it relates to. You can do this with the following query:

SELECT
   dl.owner,
   dl.table_name,
   dl.column_name
FROM
   dba_lobs dl
WHERE
   dl.segment_name = &log_segment_name;

or to find all LOB segments within a tablespace you can adjust the WHERE clause to look at the tablespace_name column. Once you have the table name, you simply use something similar to the following (you can omit the TABLESPACE &table_tablespace if you don't want to move the table to a new tablespace).

ALTER TABLE &table_name
  MOVE TABLESPACE &table_tablespace
  LOB(&column_name) STORE AS (TABLESPACE &lob_tablespace);

That's all there is to it. See, something useful.

Welcome

Welcome weary web-travellers (and if you’re not weary then welcome anyway). This blog is a space for me to share my thoughts on technology, life, politics, news stories and anything else that crosses my mind as well as I’ll throw up links to freebies or other interesting sites that I think others might like to see. I can’t promise that everything will be interesting or even make complete sense (and even if I could, I wouldn’t), but I will try to provide some useful content some of the time at least. If you have comments or suggestions, feel free to let me know. Anyway, happy reading :-)