SQL tips: Failsave Inserts – never mix up column orders again!

Imagine you have an Insert statement like this:

INSERT INTO target_table
    (measure_id,
     value_01,
     value_02,
     value_03,
     value_04,
     value_05,
     value_06,
     value_07)
SELECT load_id * 100 AS measure_id,
       val_1 AS value_01,
       CASE
           WHEN val_2 < 10 THEN 1
           WHEN val_2 < 100 THEN 2
           WHEN val_2 < 1000 THEN 3
           ELSE 4 
       END  AS value_02,
       val_3 AS value_03,
       val_4 AS value_04,
       CASE
           WHEN val_5 = 1 THEN 1
           WHEN val_5 = 2 THEN 20
           WHEN val_5 > 2 THEN 300
           ELSE null
       END AS value_05,
       CASE
           WHEN val_5 IN (1, 5, 7, 9, 11) THEN 21
           WHEN val_5 IN (2, 3) THEN 22
           WHEN val_5 IN (4, 6, 8, 10) THEN 23
           ELSE 24
        END AS value_06,
       val_6 * 100 + val_1 AS value_07
  FROM measure_table
 WHERE load_day > trunk(sys_date);

A common mistake

I have already added the names of the target columns, but it is still hard to make sure that all columns in the Select are at the right position!

Now I have added a new row  but made a mistake!
Do you see what went wrong?

INSERT INTO target_table
    (measure_id,
     value_01,
     value_02,
     value_03,
     value_04,
     value_05,
     value_06,
     value_07,
     value_08)
SELECT load_id * 100 AS measure_id,
       val_1 AS value_01,
       CASE
           WHEN val_2 < 10 THEN 1
           WHEN val_2 < 100 THEN 2
           WHEN val_2 < 1000 THEN 3
           ELSE 4 
       END  AS value_02,
       val_3 AS value_03,
       val_4 AS value_04,
       CASE
           WHEN val_5 = 1 THEN 1
           WHEN val_5 = 2 THEN 20
           WHEN val_5 > 2 THEN 300
           ELSE null
       END AS value_05,
       CASE
           WHEN val_5 IN (1, 5, 7, 9, 11) THEN 21
           WHEN val_5 IN (2, 3) THEN 22
           WHEN val_5 IN (4, 6, 8, 10) THEN 23
           ELSE 24
        END AS value_06,
       val_6 * 100 + val_1 + val_02 AS value_08,
       val_6 * 100 + val_1 AS value_07
  FROM measure_table
 WHERE load_day > trunk(sys_date);

I got confused and added the new line above the column for value_07. I should have put it below!
Now the values meant for column value_08 will be inserted into column value_07, and vice versa.

 

Solution

Here is a simple but effective solution, that prevents mistakes like that:

INSERT INTO target_table
    (measure_id,
     value_01,
     value_02,
     value_03,
     value_04,
     value_05,
     value_06,
     value_07,
     value_08)
SELECT measure_id,
       value_01,
       value_02,
       value_03,
       value_04,
       value_05,
       value_06,
       value_07,
       value_08
  FROM (SELECT load_id * 100 AS measure_id,
               val_1 AS value_01,
               CASE
                   WHEN val_2 < 10 THEN 1
                   WHEN val_2 < 100 THEN 2
                   WHEN val_2 < 1000 THEN 3
                   ELSE 4 
               END  AS value_02,
               val_3 AS value_03,
               val_4 AS value_04,
               CASE
                   WHEN val_5 = 1 THEN 1
                   WHEN val_5 = 2 THEN 20
                   WHEN val_5 > 2 THEN 300
                   ELSE null
               END AS value_05,
               CASE
                   WHEN val_5 IN (1, 5, 7, 9, 11) THEN 21
                   WHEN val_5 IN (2, 3) THEN 22
                   WHEN val_5 IN (4, 6, 8, 10) THEN 23
                   ELSE 24
                END AS value_06,
               val_6 * 100 + val_1 + val_02 AS value_08,
               val_6 * 100 + val_1 AS value_07
          FROM measure_table
         WHERE load_day > trunk(sys_date));

I added an outer Select around the values Select and copied the column list from the Insert part right to the outer Select.
Just copy and past – no misplacement possible!

The inner Select still has to deliver all the columns, but now even with the right names assigned.
The order of the columns in the inner Select is now without any meaning, so my “misplacement” is fixed.

Missing statements in SQL Monitoring

Since Oracle 11g, the database offers real-time SQL Monitoring, a great feature for insights of currently and previously running queries.
In most cases this works great without effort, but sometimes your statements won’t show.
This can be caused by statements with very large execution plans or statements with short execution times.

Monitor statements with very large execution plans

To include statements with very large execution plans in SQL Monitoring, we need to increase two thresholds. The following values served me well, but your millage may vary:

ALTER SESSION SET "_SQLMON_MAX_PLAN"=4020;
ALTER SESSION SET "_SQLMON_MAX_PLANLINES"=4000;

Remember to execute these commands in the same session and before the statements you want to monitor!

Monitor short running statements

To include short running statements into SQL Monitoring, we can use the MONITOR hint.

example:
SELECT /*+ MONITOR*/ * FROM DUAL;

Remeber that this hint is only valid when the parameter CONTROL_MANAGEMENT_PACK_ACCESS is set to DIAGNOSTIC+TUNING.

Execlude statements from SQL Monitoring

To exclude statements from SQL Monitoring, we can use the NO_MONITOR hint.

example:
SELECT /*+ NO_MONITOR*/ count(*) FROM DBA_OBJECTS CROSS JOIN DBA_OBJECTS;

Access SQL Monitoring

The SQL Monitoring informations can be accessed through

  • the tables V$SQL_MONITOR and V$SQL_PLAN_MONITOR
    (RAC:  GV$SQL_MONITOR and GV$SQL_PLAN_MONITOR)
  • a PL/SQL procedure:
    variable l_rep CLOB;
    exec :l_rep := DBMS_SQLTUNE.REPORT_SQL_MONITOR();
    print :l_rep
  • or with Oracle Enterprise Manager