Caching in – adventures in Oracle Tuning

Given the job of tuning a problem statement, I’ll usually try to work on it on a lightly used database. More importantly, where practical, I’ll execute the statement twice, one after the other, and use the response time for the second run as the starting point – the timing that my tuning changes to the statement need to improve upon.

My reason for doing this is simple – I want to filter out the effects of database caching on performance of this individual statement.

Obviously, this approach tends to work best for relatively short running queries. If you’re lumbered with a 12-hour Batch behemoth, then this isn’t going to be an appealing plan of attack.

Compound Triggers – Managing the Mutant Menace

Now I’ve got 11g up and running, I’ve finally had the chance to mess about with some of the new features.
Anyone who has done any amount of work with Oracle triggers will be familiar with the dreaded ORA-04091 mutating table error.
You’ll be relieved to hear that I’ve managed to resist the temptation to start talking about X-Men. The medication must be working.

Instead, here’s a quick demo of an 11g way of getting around this particular problem.

Express Yourself – String Comparison in Oracle using REGEXP_REPLACE

As a programmer, one of the absolute pleasures of Unix ( especially when compared to Windows) is the flexibility offerred by the use of Regular Expressions. Not only do tools such as Sed and Awk offer almost unlimited options for manipulating text files, but Regular Expression syntax offers an unparalleled opportunity to exercise the top row of your keyboard.
Regular Expressions have been available in Oracle for a while now, but it’s one of those incredibly useful features that doesn’t seem to be widely used.
This is no doubt due, in part, to the fairly involved syntax, but help is at hand. There’s a particularly good explaination of most of the Oracle Regular Expression functions ( and syntax) on the excellent PSOUG reference site ( formerly known as Morgan’s Library).

I want to concentrate here on the REGEXP_REPLACE function and how you can use it to help when comparing strings in the database that have come from different sources and are, as a result, in different formats.

Installing Oracle 11gR2 on Ubuntu 9.10

Finally, I’ve got everything in place for the big one – installing 11gR2 on my Ubuntu server.

NOTE – if you’ve stumbled across this post in search of instructions for installing Oracle XE 11g on Ubuntu, you can find installation steps here.

PL/SQL Syntax Highlighting in Textpad – for when you can’t play with Penguins

When I’m mucking around at home, it’s Linux all the way. Unfortunately, at work ( in desktop terms, at least) I’m still stuck with being a Microsofty.

This leaves me with Textpad as my program editor of choice.

As promised then, here’s the quick and dirty guide to enabling syntax highlighting for PL/SQL in Textpad ( works for versions 4 and 5) …

Dropping quoted users – why sometimes Oracle can’t bear to say goodbye

After messing about with various database objects with unusual usernames, I discovered a little quirk in Oracle XE.
Obviously, you can create usernames containing quotation marks simply by quoting them on creation :

CREATE USER “MR SPOCK” IDENTIFIED BY x;
CREATE USER “MILES O'BRIEN” IDENTIFIED BY x;

And you can drop them in the same way, can’t you ?

DROP USER “MR SPOCK”;

User dropped

Logical

SQL> drop user "MILES O'BRIEN"; 
drop user "MILES O'BRIEN" 
* 
ERROR at line 1: 
ORA-00604: error occurred at recursive SQL level 1 
ORA-00933: SQL command not properly ended 
ORA-06512: at line 7 

Sneak attack by the Klingons ? Sabotage by the Romulans ? Orion Syndicate after revenge ?

Self-Inflicted SQL Injection – don’t quote me !

After my recent post about escaping quotes in SQL scripts, I was surprised and delighted to receive a mail from Alexander Kornbrust, CEO of Red Database Security.
In it, he said he’d read the post and pointed out that the code therin was vulnerable to SQL-Injection.
I was fortunate enough to work with Alex, before he went off to become famous, so I know that he’s a bona fide expert in all things Oracle, especially security. Even so, I was initially puzzled by his assertion.
After all, the code I’d posted was an example where the code is held in a script and NOT in the database so wouldn’t be vulnerable to being executed by someone who’d hacked into the database itself. After all, SQL Injection happens interactively doesn’t it ? The hacker needs to be probing for weaknesses via a web front-end or similar. Don’t they have to be physically typing stuff in somewhere for this to work ? Er….apparently not.
Having asked around a number of Oracle developers, this would seem to be a widely held misconception. In terms of SQL Injection attacks, we’re all familiar with the classic HTML login form which POSTS to some mid-tier script or program which in turn, simply concatenates the username and password strings supplied by the user into a query then fires it off unthinkingly at the database. But how can you be attacked when the attacker isn’t even around at the time ?

Customising gedit for PL/SQL on Ubuntu 9.10

As I’ve said before, when it comes to doing serious Database programming, I prefer to work in SQL*Plus.

Yes, SQLDeveloper ( and Toad for that matter) are great for looking at stored program units, running ad-hoc queries etc, but I find there’s no substitute for being “close to the metal” when it comes to in-depth or complex programming in PL/SQL.

Apart from anything else, the feedback you get at the prompt is the actual error, and not masked by something the IDE is objecting to.
Line numbering also helps a lot. If the compiler reports an error at line 100, I want to be able to go to line 100 in my code directly, no messing. Text editors tend to do this stuff really well.

At work, being stuck on Windows, I have to rely on the trusty Textpad, which is perfectly adequate.
Fortunately, when I get home, it’s Ubuntu all the way. This means getting to play with another of those unobtrusive Linux gems – gedit.

Reverse-Engineering a CRUD Matrix in Oracle

I think that title may need some explaining, especially if, like me, you spend your days trying to keep your database free of crud.
CRUD stands for Create Read Update and Delete. A CRUD Matrix contains details of which programs interact with which tables and in what way.
In the context of an application running on Oracle, it’s useful to know which stored program units interact with which tables for a whole host of reasons.
These mainly resolve themselves down to being able to analyse the impact of any structural changes you may want to make to your database such as changing a table definition or adding an index.

Incidentally, being such a useful concept, CRUD inevitably has many synonyms, ranging from the brutally frank ( Create Retrieve Alter Purge) to the positively hallucinogenic ( Add Change Inquire Delete) so I’m sure you can find an acronym suitable for whatever kind of day you happen to be having.

Slave Driving – Getting SQL*Plus to do it for you

In my haste to play around in my shiny new Oracle XE instance, I’ve forgotten to set the Default Tablespace for any new users I create.
As a result I’ve got few tables and indexes in the SYSTEM tablespace. Fortunately, I don’t have to go through the drudgery of moving all of them by hand, I can get SQL*Plus to do it for me.