Oracle Database Performance Tuning — Rules of Thumb, Part 1

In today’s article, we will analyze a few guidelines that are usually enforced during SQL statement generation, no matter which DBMS is used. Let’s take a look at three of the general guiding principles for optimal SQL development.

Rule 1: Database performance may vary, depending on multiple factors. Ask any experienced DBA or programmer any question related to database performance, and their answer most likely will be “It depends”, followed by whatever the database in particular does depend on in terms of performance.

For instance, someone may inquire about the most optimal path for their SQL statements during Oracle database performance tuning, and a good DBA knows the best answer — “It depends’’. The why of it is slightly more complicated to explain.

Indexed access won’t be as efficient as a table scan in case each row requires to be returned. However, primary index lookup will suffice, probably even be ideal, for fetching a single row. In short, access paths and their performance may vary with the manner in which data clustering occurs, if parallelism can be enforced, the DBMS version in use, and many other such factors.

Be wary of optimizing tips that include words like “always” or “never.” Almost everything depends on something or the other.

Rule 2: Be Careful Regarding Your Requests and Requirements

Query performance may get altered when the arrangement of elements is shifted in any manner. The extent of this change is decided by the DBMS in use and whether rule-based SQL tuning for Oracle has been applied.

A helpful thumb rule that works irrespective of DBMS, is to set the most limiting predicate in a position where it is read first by the query tuner or optimizer. One good example for this is the use of restrictive requirements in WHERE clause — in Oracle, these are placed at the bottom of the statement, since the optimizer such clauses from the bottom up. In case of DB2, this is reversed.

When we place the most Placing the most inhibiting predicate in such a way that enables the optimizer to read it first, it helps narrow down the first set of results before the optimizer moves to the proceeding predicate. This way, the upcoming predicate doesn’t get implemented to the entire table, but only to the subset of information fetched after applying the most selective condition.

Also, it is important to remember that such situations may change as more releases of a DBMS are introduced. It is why you need to remain updated in terms of versions, releases, and fix packs when considering Oracle database and sql , in order to ensure comprehension about all the changes that have occurred and their impact on your SQL.

Rule 3: Keep it Simple, Silly

The KISS principle applies in almost every kind of IT activity. At the same time, simplicity may not always lead to ideal performance in the world of SQL.

Simple SQL undoubtedly eases development and maintenance related tasks. That’s because a simple SQL query is easier to interpret and alter as required. Application developers have an easier time when they have to deal with more basic SQL queries rather than complicated SQL.

Nevertheless, simple SQL cannot equal complex SQL in terms of performance. A DBMS and an optimizer that can do more tasks is likely to result in enhanced performance as well. Consider this instance — sometimes, developers refrain from using joins by utilizing several SELECT queries and then join the information via program logic.

Although the SQL is more basic because the programmer doesn’t have to comprehend how to write SQL to join tables, SQL joins generally perform better than program joins since less information is fetched for the program.

Moreover, the relational optimizer can modify the join methodology on its own when performing SQL tuning for MySQL, in case the database or its data change. Conversely, program logic will have to be altered by an expert programmer manually.

Stay tuned to know more rules of thumb in this series of SQL performance optimizing and tuning tips for Oracle database and SQL.