Mohamed Houri’s Oracle Notes

July 30, 2014

On table constraint execution order

Filed under: Oracle — hourim @ 8:03 pm

Introduction

Before to be left behind due to my infrequent blogging activity these days, here it is a simple note about the order of execution of constraints and triggers placed on a given table. Basically, I am inclined, according to my very recent tests done on 11.2.0.3 and shown below, to claimn that that order of execution obeys the following steps:

  1. Before (insert) trigger fires first
  2. Then CHECK or UNIQUE constraint follows. We will see herein after in what order
  3. Then it will be the turn of after (insert) trigger
  4.  And finally checking Foreign Key integrity constraint comes at the trailing edge of the process

The execution order of the check and unique constraints seems to be closely related to the order of the columns (in the table) with which the constraints has been implemented, as shown by Karthick_App in the above mentioned otn thread.

 Details

This is the model on which I have made the above observations:


drop table c purge;
drop table p purge;

create table p as select rownum n1, trunc((rownum-1)/3) n2
from dual connect by level <= 10;

alter table p add constraint p_pk primary key (n1);

create table c (m1 number not null, m2 number, m3 number);

alter table c add constraint child_fk foreign key (m2) references p(n1);

alter table c add constraint child_ck check (m3 in (1,2));

alter table c add constraint child_uk unique (m1);
  
create or replace trigger c_bfi_trg before insert on c
for each row
begin
    dbms_output.put_line('Trigger before insert fired');
end;
/

create or replace trigger c_afi_trg after insert on c
for each row
begin
    dbms_output.put_line('Trigger after insert fired');
end;
/       

The following select shows the order of the column within the table


select table_name, column_name, column_id
from user_tab_columns
where table_name = 'C'
order
by 1, 3;

TABLE_NAME                     COLUMN_NAME                     COLUMN_ID
------------------------------ ------------------------------ ----------
C                              M1                                      1
C                              M2                                      2
C                              M3                                      3

Note particularly that I have:

  • A not null CHECK constraint on the first column (m1) because it has been declared as the primary key of the child table c
  • A UNIQUE constraint on the first column (m1) because it has been declared as the primary key of the child table c
  • A FOREIGN Key constraint on the second column (m2) because it has been declared as a foreign key to the parent table p
  • A CHECK constraint on the third column(m3) to allow only 1 and 2 as possible values

A check constraint (being it unique or check on possible values) other than a foreign key constraint is executed according to the column order in the table. For example, the not null CHECK constraint will be the first to be verified because it has been implemented on column m1 which is the leading column of the table

SQL> set serveroutput on

SQL> insert into c values (null, 11, 3);

Trigger before insert fired
insert into c values (null, 11, 3)
                      *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("XXX"."C"."M1")

Note that in the above insert, I have 3 reasons for which the insert ought to be refused

  • Inserting a null value into the primary key (m1)
  • Inserting a non-existing parent key (m2 = 11)
  • Inserting a non-acceptable value into m3 (m3 in (1,2))

Among all those refusal reasons, it is the ORA-01400 that has fired up because the NOT NULL constraint has been implemented on the first column of the table (m1). The same observation can be made when the unique constraint placed on the first column is violated as shown below:

SQL> insert into c values (1, 10,1);
Trigger before insert fired
Trigger after insert fired
1 row created.

SQL> insert into c values (1, 11,3);
Trigger before insert fired
insert into c values (1, 11,3)
*
ERROR at line 1:
ORA-00001: unique constraint (XXX.CHILD_UK) violated

When we work around the not null refusal reason by supplying a not null value to the m1 column and re-insert the same statement, this time it is the check constraint m3 in (1,2) that raises up

SQL> insert into c values (1, 11,3);
Trigger before insert fired
insert into c values (1, 11,3)
*
ERROR at line 1:
ORA-02290: check constraint (XXX.CHILD_CK) violated

Despite the Foreign key is placed on the second column of the table, it is the check constraint on the third column that fires up.

And when all constraints are valid, the foreign key integrity constraint is finally checked as shown below:

SQL> insert into c values (1, 11,1);

Trigger before insert fired
Trigger after insert fired

insert into c values (1, 11,1)
*
ERROR at line 1:
ORA-02291: integrity constraint (XXX.CHILD_FK) violated - parent key not found

Conclusion

I have been very often asking myself (or being asked) the same question when confronted to such a kind of situation: what is the order of the constraint/trigger execution? And for each time I was obliged to spend few minutes creating a simple model in order to come up with a reliable answer. From now and on, I am sure that I will not redo this work as far as I definitely know that I wrote the answer into an article that has been published somewhere on the net.

In passing, spot why I am encouraging people to write:

  •  They will definitely not redo the same work again
  •  They will definitely find easily what they have done in this context
  • They  will definitely let other readers validate or correct their answer
Advertisements

July 2, 2014

Execution plan of a cancelled query

Filed under: explain plan — hourim @ 2:47 pm

I have been reminded by this otn thread that I have to write something about the usefulness of an execution plan pulled from memory of a query that has been cancelled due to its long running execution time. Several months ago I have been confronted to the same issue where one of my queries was desperately running sufficiently slow so that I couldn’t resist the temptation to cancel it.

It is of course legitimate to ask in such a situation about the validity of the corresponding execution plan? Should we wait until the query has finished before starting investigating where things went wrong? What if the query takes more than 20 minutes to complete? Will we wait 20 minutes each time before getting an exploitable execution plan?

The answer of course is no. You shouldn’t wait until the end of the query. Once the query has started you could get its execution plan from memory using its sql_id. And you will be surprised that you might already be able to found a clue of what is wrong with your query even in this ‘’partial’’ execution plan.

Let me model an example and show you what I am meaning.

    create table t1
       as
       with generator as (
           select  --+ materialize
               rownum id
           from dual
           connect by
               level <= 10000)
       select
          rownum                  id,
          trunc(dbms_random.value(1,1000))    n1,
          lpad(rownum,10,'0') small_vc,
          rpad('x',100)       padding
      from
          generator   v1,
          generator   v2
      where
          rownum <= 1000000;

   create index t1_n1 on t1(id, n1);

   create table t2
       as
       with generator as (
           select  --+ materialize
               rownum id
           from dual
           connect by
               level <= 10000)
       select
          rownum                  id,
          trunc(dbms_random.value(10001,20001))   x1,
          lpad(rownum,10,'0') small_vc,
          rpad('x',100)       padding
      from
          generator   v1,
          generator   v2
      where
          rownum <= 1000000;
    
   create index t2_i1 on t2(x1);

   exec dbms_stats.gather_table_stats(user, 't2', method_opt => 'FOR ALL COLUMNS SIZE 1');
   exec dbms_stats.gather_table_stats(user, 't1', method_opt => 'FOR ALL COLUMNS SIZE 1');
   

I have two simple indexed tables t1 and t2 against which I engineered the following query

    select *
       from t1
        where id in
                (Select id from t2 where x1 = 17335)
       order by id ;
   

The above query contains an order by clause on an indexed column (id). In order to make this query running sufficiently slow so that I can cancel it I will change the optimizer mode from all_rows to first_rows so that the CBO will prefer doing an index full scan and avoid the order by operation whatever the cost of the index full scan operation is.

   SQL> alter session set statistics_level=all;

   SQL> alter session set optimizer_mode=first_rows;

   SQL> select *
       from t1
        where id in
                (Select id from t2 where x1 = 17335)
       order by id ;

   107 rows selected.

   Elapsed: 00:02:14.70 -- more than 2 minutes

   SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

   SQL_ID  9gv186ag1mz0c, child number 0
  -------------------------------------
  Plan hash value: 1518369540
  ------------------------------------------------------------------------------------------------
  | Id  | Operation                    | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
  ------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT             |       |      1 |        |    107 |00:02:39.85 |     108M|
  |   1 |  NESTED LOOPS SEMI           |       |      1 |    100 |    107 |00:02:39.85 |     108M|
  |   2 |   TABLE ACCESS BY INDEX ROWID| T1    |      1 |   1000K|   1000K|00:00:01.03 |   20319 |
  |   3 |    INDEX FULL SCAN           | T1_N1 |      1 |   1000K|   1000K|00:00:00.31 |    2774 |
  |*  4 |   TABLE ACCESS BY INDEX ROWID| T2    |   1000K|      1 |    107 |00:02:37.70 |     108M|
  |*  5 |    INDEX RANGE SCAN          | T2_I1 |   1000K|    100 |    106M|00:00:30.73 |    1056K|
  ------------------------------------------------------------------------------------------------

  Predicate Information (identified by operation id):
  ---------------------------------------------------
     4 - filter("ID"="ID")
     5 - access("X1"=17335)
 

And the plan_hash_value2 of this query is:

  SQL> @phv2 9gv186ag1mz0c

  SQL_ID        PLAN_HASH_VALUE CHILD_NUMBER       PHV2
  ------------- --------------- ------------ ----------
  9gv186ag1mz0c      1518369540            0 2288661314
  

The query took more than 2 minutes to complete and for the purpose of this blog I didn’t cancelled it in order to get its final execution plan shown above. Now I am going to re-execute it again and cancel it after few seconds.

    SQL> select *
       from t1
        where id in
                (Select id from t2 where x1 = 17335)
       order by id ;

   ^C
   C:\>
   

And here below is the corresponding partial plan pulled from memory

  SQL> select * from table(dbms_xplan.display_cursor('9gv186ag1mz0c',0,'ALLSTATS LAST'));

  SQL_ID  9gv186ag1mz0c, child number 0
  -------------------------------------
  Plan hash value: 1518369540

  ------------------------------------------------------------------------------------------------
  | Id  | Operation                    | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
  ------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT             |       |      1 |        |     17 |00:00:18.13 |      13M|
  |   1 |  NESTED LOOPS SEMI           |       |      1 |    100 |     17 |00:00:18.13 |      13M|
  |   2 |   TABLE ACCESS BY INDEX ROWID| T1    |      1 |   1000K|    129K|00:00:00.12 |    2631 |
  |   3 |    INDEX FULL SCAN           | T1_N1 |      1 |   1000K|    129K|00:00:00.04 |     361 |
  |*  4 |   TABLE ACCESS BY INDEX ROWID| T2    |    129K|      1 |     17 |00:00:18.26 |      13M|
  |*  5 |    INDEX RANGE SCAN          | T2_I1 |    129K|    100 |     13M|00:00:03.63 |     136K|
  ------------------------------------------------------------------------------------------------

  Predicate Information (identified by operation id):
  ---------------------------------------------------
     4 - filter("ID"="ID")
     5 - access("X1"=17335)

  SQL>  @phv2 9gv186ag1mz0c

  SQL_ID        PLAN_HASH_VALUE CHILD_NUMBER       PHV2
  ------------- --------------- ------------ ----------
  9gv186ag1mz0c      1518369540            0 2288661314
  

The main observations are:

  1.   Both queries followed the same plan hash value
  2.   Both queries have the same phv2
  3.   Both execution plans contain the same operations at the same order of execution
  4.   Both execution plans contain the same Estimations (E-Rows) which is a clear indication that this is obtained at hard parse time

Either from the complete plan or from the ”partial” one we can have the same clue of what is going wrong here (even thought that in this particular case the performance penalty is due to the first_rows mode which has the tendency to avoid an order by on an indexed column at any price). Indeed it is the operation number 4 which is the most time consuming operation and from where there is the most important deviation between the CBO estimations and the Actual rows

So there is nothing that impeaches hurried developers from cancelling a query and getting its execution plan from memory. They can end up with valuable information from the cancelled query plan as well as from the complete plan.

This article is also a reminder for experienced Oracle DBA performance specialists that, there are cases where a difference between estimations done by the CBO and the actual rows obtained during execution time, does not necessary imply an absence of fresh and representative statistics. This difference could be due to a plan coming from a cancelled query.

By the way, I know another situation where an excellent optimal plan is showing a value of Starts*E-Rows diverging greatly from A-Rows and where statistics are perfect.  That will be explained, I hope,  in a different blog article.

July 1, 2014

DDL optimization : french version

Filed under: Oracle — hourim @ 7:16 pm

I wrote an article about DDL optimization which I have submitted to Oracle otn and which will be hopefully published very soon. As you might also know I have been invited to be a member of the Oracle World team in order to spread the use of the Oracle technology in Latin America. This is why the English (original) article has been translated into Portuguese and published in the otn Latin America. The Spanish version will be published very soon as well.

I have also translated the original article to French and have been looking unsuccessfully for a known place where I would have published it so that it will have reached many persons in several countries like France, Belgium, Luxembourg, Switzerland,  North Africa and several other countries too. Having been unable to find that place, I decided to publish it here in my blog. So here it is  DDL Optimization

Blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)