Observing the NoSQL hype through the eyes of an SQL performance consultant is an interesting experience. It is, however, very hard to write about NoSQL because there are so many forms of it. After all, NoSQL is nothing more than a marketing term. A marketing term that works pretty well because it goes to the heart of many developers that struggle with SQL every day.
My unrepresentative observation is that NoSQL is often taken for performance reasons. Probably because SQL performance problems are an everyday experience. NoSQL, on the other hand, is known to “scale well”. However, performance is often (more...)
There was an interesting index related performance problem on Stack Overflow recently. The problem was to check an input string against a table that holds about 2000 prefix patterns (e.g.,
LIKE 'xyz%'). A fast select is needed that returns one row if any pattern matches the input string, or no row otherwise.
I believe my solution is worth a few extra words to explain it in more detail. Even though it’s a perfect fit for Use The Index, Luke it’s a little early to put it as an exercise there. It is, however, a very good complement (more...)
Today, I’d like to introduce my new Web-Book Use The Index, Luke. Use The Index, Luke is a guide to Oracle database performance for developers.
I started the book because I noticed that almost every existing book or online document on that topic is stuffed with plenty of information that is not relevant to developers.
Although a certain know-how of the database’s internals is required to get the best performance, Use The Index, Luke keeps that information at a minimum and presents it from a developers perspective.
One of the more advanced tricks I like to exploit are analytic Top-N queries. Although I am using them for quite a while, I recently discovered a “limitation” that I was not aware of. Actually—to be honest—it’s not a limitation; it is a missing optimization in a rarely used feature that can easily worked around. I must admit that I ask for quite a lot in that case.
The article starts with a general introduction into Top-N queries, applies that technique to analytic queries and explains the case where I miss an optimization. But is is really worth all that (more...)
You might have noticed that this blog never had a kickoff post that explains what this blog is about. Time has come to spend some words on the topic of software quality—as I see it—and how this blog covers some aspects of software quality.
First of all, I’d like to introduce the two most frequently considered software quality aspects:
The software must produce correct results. There is no point in using software, which produces results that you need to verify before you can trust them.
If you know the task you want to perform, the software should make (more...)
As Jonathan Lewis commented on my article Clustering Factor: Row Migrtion’s Victim, there is even more to say about the difference between the good, old, and deprecated
ANALYZE statement and the
DBMS_STATS package. Jonathan mentioned that the CBO is using the
CHAIN_CNT value in the statistics, if present, and suggested to try my “trapQL” after analyzing the base table in the old fashion.
For easy C&P I the complete new script to try:
CREATE TABLE row_mig1 ( a CHAR(2000), b CHAR(2000), c CHAR(2000), x NUMBER NOT NULL, filter NUMBER NOT NULL, CONSTRAINT row_mig1_pk PRIMARY KEY (x) ) ENABLE ROW (more...)
My scope for this release was rather limited: implement
-v switches to
halockrun to make them more communicative.
The reason for that is quite simple; hatools will be easier to learn and use if they talk more. I also noticed that there are two “new” tools in the ever growing locking tool zoo—not to mention flock(1)—that talk more than hatools:
My original (more...)
This article describes the effects of a high row migration rate on the clustering factor and the optimizer’s ability to select the best execution plan.
In my previous article—Row Migration and Row Movement—I have demonstrated that the “insert empty, update everything” anti-pattern can lead to 100% row migration. This article continues the research on row migration and unveils surprising effects on the clustering factor. To be precise, the clustering factor can become completely bogus in presence of a very high row migration rate. Once the clustering factor is “wrong”, it’s just a finger exercise to construct an optimizer (more...)
The Oracle database knows three distinct processes that are easily mixed up: Row Chaining, Row Migration and Row Movement.
For the impatient, I provide some very short definitions:
- Row Chaining
- Distribution of a single table row across multiple data blocks.
- Row Migration
- Relocation of an entire table row to a new place, without updating the indexes.
- Row Movement
- Relocation of an entire table row to a new place and updating the indexes.
This article was inspired by the (more...)