Intermediate SQL Color Coded SQL, UNIX and Database Essays


How to add a hint to ORACLE query without touching its text

If you’ve been a DBA long enough, you’ve probably seen multiple cases where certain SQL queries just refuse to cooperate.

I.e. you KNOW that the query is supposed to use index IDX1, but ORACLE stubbornly decides to take IDX2. Rats!

The usual suspect here is bad statistics and thus the usual advise is: start re-collecting them. But while this might help, the success is far from guaranteed! Moreover, with a ‘real production’ data statistics collection could take hours (days?) and you already have people screaming about slow performance … In other words, you need to fix the problem NOW and waiting until stats collection maybe fixes the problem a few hours down the road is just not an option!


How to find SPM baseline by sql_id

When you start working with SQL Plan baselines, one of the annoying things that you might find is that the main “baseline” dictionary view dba_sql_plan_baselines does not have sql_id column.


What is the difference between SQL Profile and SPM Baseline ?

SQL Profiles

   - SQL profile "SYS_SQLPROF_012ad8267d9c0000" used FOR this statement

and SPM Baselines

   - SQL plan baseline "SQL_PLAN_01yu884fpund494ecae5c" used FOR this statement

are both relatively new features of ORACLE Optimizer with Profiles first appearing in version 10 and SPM Baselines in version 11.

Both SQL Profiles and SPM Baselines are designed to deal with the same problem: Optimizer may sometimes produce a very inefficient execution plan, and they are both doing it by essentially abandoning the idea that “all SQLs are created equal”. Instead, another idea is put forward: “Some SQLs are special and deserve individual treatment”.


What are SQL Profiles and why do we need them ?

If you use DBMS_XPLAN package to analyze execution plans for your SQL statements (and you really should these days), you might have noticed that at times the following line might be displayed along with your execution plan:

   - SQL profile "SYS_SQLPROF_012ad8267d9c0000" used FOR this statement

This seems to mean that:

  1. An external ‘helper’ object was used during evaluation of your SQL statement
  2. This ‘helper’ object changed (or, at least, influenced) its execution plan

While having some extra help is nice, one has to wonder: what exactly is this “profile” object ? What does it do ? And, in a bigger picture, why do we need “external” help evaluating SQL statements at all ?


How ORACLE Implicit Type Conversion Works. Part 2

Those of you who read my previous post would remember that ORACLE implicit type conversion approach
contradicts common sense, yet, you would probably agree, that there are some excellent reasons for ORACLE to go with it.

This post will explore implicit type conversion further and, in a way, turn everything you read about in part 1 upside down.

If the motto of the first part of this post was: Always question your common sense, the motto of the second part will be: Do NOT accept anything as dogma (even if your read it on this blog 🙂 )


How ORACLE Implicit Type Conversion Works. Part 1

It is said that when you hear the words this is common sense or everybody knows that you should take notice because when enough people say it – it is likely that the results are exactly the opposite. Especially, when you are dealing with ORACLE 🙂

Here is a short puzzle to prove the point.

Imagine that you have a table with ID column that stores, say, 100000 unique numbers from 1 to 100000. The column has a unique constraint (and a unique underlying index to enforce it).

Assume also that the hardware is regular (we are not yet on Exadata), table is large enough, all ORACLE metadata is correct, statistics for both table and indexes are collected and are up-to-date, there are no unusual optimizer directives or nasty bugs etc

Here is the question:

What would be the expected execution plan for the following query: