Blooms

In Oracle releases 10.2.0.x and later join processing can be made more efficient by the use of Bloom filters, primarily to reduce traffic between parallel query slaves. What is a Bloom filter? Named after Burton Howard Bloom, who came up with the concept in the 1970s, it’s an efficient data structure used to quickly determine if an element has a high probability of being a member of a given set. It’s based (more...)

“You’re A Real ACE”

Recently I was declared, for want of a better word, an Oracle ACE. I was nominated by someone I respect and admire; just the nomination itself was enough, really, to make my day. When I received notification that I had been selected I was truly surprised. I immediately thanked my nominator, who told me:


"Still, it is just a nomination form, and you did all the work that
made it so impressive. Congratulations!"

Honestly, (more...)

That’s … Huge!

Recently I’ve noticed the occasional thread in Oracle newsgroups and lists asking about hugepages support in Linux, including ‘best practices’ for hugepages configuration. This information is out on that ‘world-wide web’ in various places; I’d rather put a lot of that information in this article to provide an easier way to get to it. I’ll cover what hugepages are, what they do, what they can’t do and how best to allocate them for your particular (more...)

It Pays To Be Smart

Exadata is a powerful system, able to provide exceptional performance. Much of this peformance is due to Smart Scans, Exadata’s mechanism for shifting the workload to the storage cells and having them filter and reduce the amount of data the database servers must process. Not every Smart Scan that starts ends up completing, though. Oracle may decide that a different path, one that doesn’t include a Smart Scan, may be more efficient. Certain conditions must (more...)

What A Performance!

Performance is one of the big watchwords in tht IT industry; nowhere is it more often used than in the database arena, and it’s usually not a good sign. End users know nothing, really, of the inner workings of the database yet they are the first to point the ‘finger of blame’ at the database when the processing speed slows or queries take longer than they would like to return data. [For end users performance (more...)

“A Ponderous Chain”

Chained and migrated rows in an Oracle database can seriously impact I/O performance, especially with Exadata. In a ‘normal’ Oracle database [read that as "non-Exadata, non-ASM" database] chained rows, in small numbers where that usually means 1% or less of the total rows in a table, are generally a nuisance. The performance hit they generate in such a situation is small and may not even be noticed by the end users. Add more chained/migrated rows (more...)

When Life Gives You Lemons…

Some of the strangest ideas (to me, anyway) can be found in the depths of PL/SQL code, be it a package, a procedure, a function or an anonymous block. Granted, such decisions may be the result of budgetary considerations which prevented a more robust and reliable application design. I was looking at some code just recently that made me scratch my head in wonder and disbelief. Let’s look at a similar piece of PL/SQL that (more...)

Ooops!

A recent IT-related survey had in its list of questions one rather interesting offering:

“Are there any safeguards preventing the DBA from accidentally dropping a table?”

That is probably not a commonly considered aspect of data security however it is an issue that should be voiced and addressed. How do you ensure that the DBA doesn’t, or can’t, drop a table accidentally? Oracle has at least two ways to ensure that a table cannot (more...)

Let’s All Join In

While investigating a question on returing unmatched rows between two tables I set up an example where both tables had indexes which could be used to speed up the query and hopefully return the result set in less time than required for a full table scan. To my surprise when the index was used on the table with the missing record the query returned no rows. Not understanding this behavior (as I KNEW there was (more...)

Hide And Seek (After A Fashion)

Indexes can be very useful tools to improve query performance, but it can be difficult to test indexes in an environment that exactly mirrors production. Although not the preferred method, Oracle offers in 11.2 and later releases the option of invisible indexes. What are invisible indexes? An invisible index can’t be ‘seen’ by any session by default, so it can be created and remain unavailable until testing is scheduled. This can be confusing since (more...)