Demo script

Imagine that you’re on a desert island where some pirates hid their treasure. You don’t know where exactly it is hidden, but you want to find it. How should you approach this – e.g. should you dig randomly? Or should you follow some sort of a system – and if yes, then which?

I think most readers have smelled a trap right away (it’s not a very sophisticated trap anyway) – of course it doesn’t matter how you do it. If it takes you X minutes to examine 1 square foot of the surface, and the total surface is Y square feet, it will take you anywhere from 0 to Y/X minutes (so, Y/X/2 on the average) to explore the entire island and find the treasure, no matter which system you’re following (or using no system at all). When there is no information whatsoever about the object(s) sought, all search strategies are equally (in)efficient.

Continue reading “SQL performance, Part III. Data storage strategies” →

While query performance depends on a large number of things, overall scale of query performance for a given database is generally set by disk I/O speed. The most common type of a storage device used in databases is still a hard disk drive (HDD), so let’s consider how it works.

Continue reading “SQL Performance, Part II — Disk I/O: metrics and scales” →

Database performance optimization deals with the problem of reducing time (and/or other resources) required to complete database queries by resolving various inefficiencies. There are many kinds of performance issues, and they can be categorized in many different ways. One important way is by scope: statement-level or instance-level. Instance-level performance tuning (which we are not covering in this mini-series) deals with such problems as excessive parsing, cache efficiency, concurrency etc. Statement-level performance tuning deals mostly with inefficient data access.

Continue reading “SQL Performance, Part I. Data access efficiency.” →

Occasionally, I receive requests to explain stuff I talk about in my blog posts on a level accessible to beginners. Unfortunately, it’s not always possible to do so. You start to explain one little thing, and then you realize that in order to understand that thing, one would to understand a dozen other little things, and before you know it, instead of writing a short blog post you find yourself writing something of the size of Encyclopedia Britannica. I decided to try to go in a different direction. I’ve accumulated some beginner-to-intermediate-level material that hopefully should make certain topics in database performance optimization clearer. I will be posting them in form of short “lessons”. I don’t have how many such lessons will be in total — I guess this depends a lot on the feedback that I’ll be receiving. My plan is to start with statement-level optimization topics, and then, if this goes well, to move on to instance-level stuff. I will try to: 1) provide a high-level overview without getting caught up into details 2) present a holistic view of Oracle database performance rather than asystematic assembly of performance-related topics 3) “derive” facts from higher principles rather than simply state them 4) use analogies to make basic performance concepts more intuitive 5) be as quantitative and specific as possible 6) heavily use demos. Hope you’ll find it useful.

 

Links:

Part I, Data access efficiency

Part II, Disk I/O: metrics and scales

Part III, Data storage strategies