Mohamed Houri’s Oracle Notes

January 20, 2018

NESTED LOOP and full/right outer join in modern RDBMS

Filed under: CBO,explain plan — hourim @ 11:36 am

The Oracle Cost Based Optimizer, the MS-SQL Server optimizer and the PostgreSQL query planner cannot use a NESTED LOOP physical operation to execute FULL OUTER and RIGHT OUTER joins logical operations. They all address the RIGHT OUTER join limitation by switching the inner and the outer row source so that a LEFT OUTER JOIN can be used. While the first two optimizer turn the FULL OUTER join into a LEFT OUTER join concatenated with an ANTI-join, PostgreSQL query planner will always use a HASH/MERGE JOIN to do a FULL OUTER join.

Let’s make this less confusing by starting with the basics. The algorithm of a NESTED LOOP physical operation is:

for each row ro in the outer row source 
loop
   for each row ri in the inner row source
   loop
     if ro joins ri then return current row
   end loop
end loop

1.Oracle 12cR2

A simple execution of the above algorithm can be schematically represented via the following Oracle execution plan:

select /*+ use_nl(t1 t2) */ 
      t1.*
from t1 inner join t2
on t1.n1 = t2.n1;

----------------------------------------------------------------
| Id  | Operation          | Name   | Starts | E-Rows | A-Rows |
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |      1 |        |      3 |
|   1 |  NESTED LOOPS      |        |      1 |      4 |      3 |
|   2 |   TABLE ACCESS FULL| T1     |      1 |      3 |      3 |
|*  3 |   INDEX RANGE SCAN | T2_IDX |      3 |      1 |      3 |
----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 – access("T1"."N1"="T2"."N1")

As you can see, in accordance with the algorithm, for each row in T1 table (A-Rows=3 operation id n°2) we scanned 3 times (Starts = 3 operation id n°3) the T2_IDX index.

Let’s now try a FULL OUTER join but without any hint:

select  
      t1.*
from t1 full outer join t2
on t1.n1 = t2.n1;

---------------------------------------------------
| Id  | Operation             | Name     | Rows  |
---------------------------------------------------
|   0 | SELECT STATEMENT      |          |       |
|   1 |  VIEW                 | VW_FOJ_0 |     4 |
|*  2 |   HASH JOIN FULL OUTER|          |     4 |
|   3 |    TABLE ACCESS FULL  | T1       |     3 |
|   4 |    TABLE ACCESS FULL  | T2       |     4 |
---------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1"."N1"="T2"."N1")

So far so good. A HASH JOIN FULL OUTER to honor a full outer join between two tables.

But what if I want to use a NESTED LOOP FULL OUTER instead of HASH JOIN FULL OUTER join ?

select  /*+ use_nl(t1 t2) */
      t1.*
from t1 FULL outer join t2
on t1.n1 = t2.n1;

-------------------------------------------------------
| Id  | Operation            | Name   | Rows  | Bytes |
-------------------------------------------------------
|   0 | SELECT STATEMENT     |        |       |       |
|   1 |  VIEW                |        |     6 |   120 |
|   2 |   UNION-ALL          |        |       |       |
|   3 |    NESTED LOOPS OUTER|        |     4 |    40 |
|   4 |     TABLE ACCESS FULL| T1     |     3 |    21 |
|*  5 |     INDEX RANGE SCAN | T2_IDX |     1 |     3 |
|   6 |    NESTED LOOPS ANTI |        |     2 |    12 |
|   7 |     TABLE ACCESS FULL| T2     |     4 |    12 |
|*  8 |     TABLE ACCESS FULL| T1     |     2 |     6 |
-------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("T1"."N1"="T2"."N1")
   8 - filter("T1"."N1"="T2"."N1")

What the heck is this execution plan of 8 operations?

Instead of having a simple NESTED LOOP FULL OUTER I got a concatenation of NESTED LOOPS OUTER and a NESTED LOOPS ANTI join.That’s an interesting transformation operated by the CBO.

Should I have tried to reverse engineer the query that sits behind the above execution plan I would have very probably obtained the following query:

select  
      t1.*
from 
      t1 
     ,t2 
where t1.n1 = t2.n1(+)
union all
select 
     t2.*
from t2
where not exists (select /*+ use_nl(t2 t1) */ 
                      null 
                  from t1 
                  where t1.n1 = t2.n1);

------------------------------------------------------
| Id  | Operation           | Name   | Rows  | Bytes |
------------------------------------------------------
|   0 | SELECT STATEMENT    |        |       |       |
|   1 |  UNION-ALL          |        |       |       |
|   2 |   NESTED LOOPS OUTER|        |     4 |    40 |
|   3 |    TABLE ACCESS FULL| T1     |     3 |    21 |
|*  4 |    INDEX RANGE SCAN | T2_IDX |     1 |     3 |
|   5 |   NESTED LOOPS ANTI |        |     2 |    20 |
|   6 |    TABLE ACCESS FULL| T2     |     4 |    28 |
|*  7 |    TABLE ACCESS FULL| T1     |     2 |     6 |
------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T1"."N1"="T2"."N1")
   7 - filter("T1"."N1"="T2"."N1")

In fact when I have directed Oracle to use a NESTED LOOP to FULL OUTER JOIN T1 and T2 it has turned out my instruction into:

 T1 LEFT OUTER JOIN T2 UNION ALL T2 ANTI JOIN T1

Which is nothing else than :

  • select all rows from T1 and T2 provided they join
  • add to these rows, rows from T1 that don’t join (LEFT OUTER)
  • add to these rows, all rows from T2 that don’t join (ANTI) with rows from T1

Do you know why Oracle did all this somehow complicated gymnastic?

It did it because I asked it to do an impossible operation: NESTED LOOP doesn’t support FULL OUTER join.

It doesn’t support RIGHT OUTER join as well as shown below:

select  /*+ use_nl(t1 t2) */
      t1.*
from t1 
RIGHT outer join t2
on t1.n1 = t2.n1;

---------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |
---------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |
|   1 |  NESTED LOOPS OUTER|      |     4 |    40 |
|   2 |   TABLE ACCESS FULL| T2   |     4 |    12 |
|*  3 |   TABLE ACCESS FULL| T1   |     1 |     7 |
---------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("T1"."N1"="T2"."N1")

Don’t be confused here. The above RIGHT OUTER JOIN has been turned into a LEFT OUTER JOIN by switching the inner and the outer table. As such, T2 being placed in the left side of the join Oracle is able to use a NESTED LOOP to operate a LEFT OUTER JOIN. You will see this clearly explained in the corresponding SQL Server execution plan I will show later in this article.

2. PostgreSQL 10.1

Since there are no hints in PostgreSQL to make a join using a NESTED LOOP I will start by cancelling hash and merge join operations as shown below:

postgres=# set enable_mergejoin=false;
SET
postgres=# set enable_hashjoin=false;
SET

And now I am ready to show you how the PostgreSQL query planner turns a right outer join into a left outer join when a NESTED LOOP operation is used:

postgres=# explain
postgres-# select
postgres-#       t1.*
postgres-# from t1 right outer join t2
postgres-# on t1.n1 = t2.n1;

                            QUERY PLAN
-------------------------------------------------------------------
 Nested Loop Left Join  (cost=0.00..95.14 rows=23 width=42)
   Join Filter: (t1.n1 = t2.n1)
   ->  Seq Scan on t2  (cost=0.00..1.04 rows=4 width=4)
   ->  Materialize  (cost=0.00..27.40 rows=1160 width=42)
         ->  Seq Scan on t1  (cost=0.00..21.60 rows=1160 width=42)
(5 lignes)

However, in contrast to Oracle and MS-SQL Server, PostgreSQL query planner is unable to transform a full outer join into a combination of an NESTED LOOP LEFT OUTER join and an ANTI-join as the following demonstrates:

explain 
select
      t1.*
from t1 full outer join t2
on t1.n1 = t2.n1;
                                QUERY PLAN
--------------------------------------------------------------------------
 Hash Full Join  (cost=10000000001.09..10000000027.27 rows=1160 width=42)
   Hash Cond: (t1.n1 = t2.n1)
   ->  Seq Scan on t1  (cost=0.00..21.60 rows=1160 width=42)
   ->  Hash  (cost=1.04..1.04 rows=4 width=4)
         ->  Seq Scan on t2  (cost=0.00..1.04 rows=4 width=4)

Spot in passing how disabling the hash join option (set enable_hashjoin=false) is not an irreversible action. Whenever the query planner is unable to find another way to accomplish its work it will use all option available even those being explicitely disabled.

3. MS-SQL Server 2016



4. Summary
In several if not all modern Relational DataBase Management Systems, NESTED LOOP operation doesn’t support right outer and full outer join. Oracle, MS-SQL Server and PostgreSQL turn “T1 right outer join T2” into “T2 left outer join T1” by switching the inner and the outer row source. Oracle and SQL Server turn a full outer join between T1 and T2 into a T1 left outer join T2 union-all T2 anti-join T1. PostgreSQL will always use a hash/merge to full outer join T1 and T2.

Model

--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
-- NESTED LOOP and full/right outer join : Oracle
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
drop table t1;
drop table t2;
create table t1(t1_id int , t1_vc varchar2(10));

insert into t1 values (1, 't1x');
insert into t1 values (2, 't1y');
insert into t1 values (3, 't1z');
 
create index t1_idx on t1(t1_id);

create table t2 (t2_id int, t2_vc varchar(10));

insert into t2 values (2, 't2x');
insert into t2 values (3, 't2y');
insert into t2 values (3, 't2yy');
insert into t2 values (4, 't2z');

create index t2_idx on t2(t2_id);
Advertisements

October 13, 2017

Execution plans : blocking and non-blocking operations

Filed under: explain plan — hourim @ 6:52 pm

Below are two 12cR2 row-source execution plans of the same query:

SQL_ID  9yqrqpgux93zk, child number 0
--------------------------------------------------------------------
| Id  | Operation                | Name | Starts | E-Rows | A-Rows |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |      1 |        |      5 |
|*  1 |  VIEW                    |      |      1 |   1000 |      5 |
|*  2 |   WINDOW SORT PUSHED RANK|      |      1 |   1000 |      5 |
|   3 |    TABLE ACCESS FULL     | T1   |      1 |   1000 |   1000 |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RN"<=5)
   2 - filter(RANK() OVER ( ORDER BY "N1")<=5)
SQL_ID  9yqrqpgux93zk, child number 0
--------------------------------------------------------------------------
| Id  | Operation              | Name         | Starts | E-Rows | A-Rows |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |              |      1 |        |      5 |
|*  1 |  VIEW                  |              |      1 |   1000 |      5 |
|*  2 |   WINDOW NOSORT STOPKEY|              |      1 |   1000 |      5 |
|   3 |    INDEX FULL SCAN     | IDX_T1_N1_UK |      1 |   1000 |      6 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RN"<=5)
   2 - filter(RANK() OVER ( ORDER BY "N1")<=5)
 

Forget the switch from a FULL TABLE scan to an INDEX FULL scan at line n° 3 and the switch from a SORT to a NOSORT operation at line n°2. What other remarkable difference we can still spot out there? Have you noticed that operation n°3 produced 1000 rows in the first execution plan and only 6 rows in the second one?

Here’s the model with which I obtained the above execution plans respectively:

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

create unique index idx_t1_n1_uk on t1(n1);

exec dbms_stats.gather_table_stats(user, 't1');

select /*+ gather_plan_statistics */ n1
from 
   (select 
       n1
	  ,rank() over (order by n1) rn
	from t1)
where
   rn <=5;

select * from table(dbms_xplan.display_cursor(null,null, 'allstats last'));
alter table t1 modify (n1 not null);

-- execute the same query

select * from table(dbms_xplan.display_cursor(null,null, 'allstats last'));

What happened here so that an operation without a filter predicate produces 6 rows where it is supposed to generate the entire set of rows in the table i.e. 1,000 rows?

The explanation comes from the way Oracle handles blocking and non-blocking operations. A blocking operation requires receiving all rows from its child operation before a single row can be processed. A non-blocking operation consumes and produces rows at the same time.

Applied to the current situation, we see that the two previous execution plans have both a blocking operation at line n°2 which is the VIEW operation.

Right?

In fact, as magnificiently explained by Jonathan Lewis in this post, the blocking nature of the VIEW operation is function of its first child operation. If this one is a blocking operation then the VIEW is a blocking operation as well. If, however, the child operation is not blocking then its VIEW parent operation is a non-blocking operation that can consume and produce rows at the same time.

The first child of the two VIEW operations in the above execution plans is respectively:

|*  2 |   WINDOW SORT PUSHED RANK|

|*  2 |   WINDOW NOSORT STOPKEY| 

The WINDOW SORT PUSHED RANK child operation of the VIEW is a sort operation. Sorts,as you know, are blocking operations. This is why the VIEW in the first execution plan is a blocking operation. Oracle has to retrieve all rows from table t1 (A-Rows = 1000) at operation n°3, sort them totally at operation n°2 before applying the filter predicate n°2 reducing as such the number of retrieved rows to only 5.

In the second execution plan the WINDOW NOSORT STOPKEY child operation of the VIEW is a non-blocking operation thanks to the ordered list of keys it receives from its INDEX FULL SCAN child operation. The VIEW asks for the first row from its first child operation which, at its turn, asks for the same row from its child operation the INDEX FULL SCAN at line 3. The index gives the first ordered key it has found to its parent operation, the WINDOW NOSORT STOPKEY,which feeds back again it parent operation, the VIEW, provided the index key survives the filter predicate n°2. The VIEW asks then for the second row, for the third row, and so on until the WINDOW NOSORT STOPKEYdeclares the end of the query. Indeed, the WINDOW NOSORT STOPKEY knows the upper bound it can’t go beyound. This is why when it receives the 6th row from the index full scan operation (A-Rows= 6) it realises that this row spans its upper bound and that it is now time to signal to the coordinator (select statement) the end of the query.

Here’s another example demonstrating that even with FULL segment scans Oracle is able to stop generating rows at the appropriate moment. All what it requires from the parent operation is to be a non-blocking operation able to consume and produce rows at the same time:

-- n1  is declared nullable

select /*+ gather_plan_statistics */ n1
from
   t1
fetch next 5 rows only;

------------------------------------------------------------------
| Id  | Operation              | Name | Starts | E-Rows | A-Rows |
------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |      1 |        |      5 |
|*  1 |  VIEW                  |      |      1 |      5 |      5 |
|*  2 |   WINDOW NOSORT STOPKEY|      |      1 |   1000 |      5 |
|   3 |    TABLE ACCESS FULL   | T1   |      1 |   1000 |      6 |
------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=5)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY  NULL )<=5)
-- n1 is declared not null

select /*+ gather_plan_statistics */ n1
from
   t1
fetch next 5 rows only;

--------------------------------------------------------------------------
| Id  | Operation              | Name         | Starts | E-Rows | A-Rows |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |              |      1 |        |      5 |
|*  1 |  VIEW                  |              |      1 |      5 |      5 |
|*  2 |   WINDOW NOSORT STOPKEY|              |      1 |   1000 |      5 |
|   3 |    INDEX FAST FULL SCAN| IDX_T1_N1_UK |      1 |   1000 |      6 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=5)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY  NULL )<=5)

Summary

Sometimes we need to read and interpret execution plans not only in their order of operation using the “first child first, recursive descent” rule, but also in their order of data flow propagation (or row-source generation). In the latter case, knowing how to identify blocking and non-blocking operations is very important.

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.

February 17, 2014

12c display_awr

Filed under: explain plan — hourim @ 7:13 pm

It is well known that display_awr function is unable to show the predicate part of a captured execution plan. Is this still the case with the 12c advent?

 select * from v$version;

BANNER                                                                           CON_ID
 -------------------------------------------------------------------------------- ----------
 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production     0
 PL/SQL Release 12.1.0.1.0 - Production                                           0
 CORE 12.1.0.1.0 Production                                                       0
 TNS for 64-bit Windows: Version 12.1.0.1.0 - Production                          0
 NLSRTL Version 12.1.0.1.0 - Production                                           0

 create table t2
  (col1 number
  ,col2 varchar2(50)
  ,flag varchar2(2));

 var n varchar2(2);
 exec :n := 'Y1';

 select count(*), max(col2) from t2 where flag = :n;

 COUNT(*) MAX(COL2)
 --------- ------------------------------------------
 0

 select * from table(dbms_xplan.display_cursor);

 SQL_ID 47n9zu0w7ht8d, child number 0
 -------------------------------------
 select count(*), max(col2) from t2 where flag = :n

 Plan hash value: 3321871023
 ---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    30 |            |          |
|*  2 |   TABLE ACCESS FULL| T2   |     1 |    30 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

 Predicate Information (identified by operation id):
 ---------------------------------------------------
  2 - filter("FLAG"=:N)

 Note
 -----
  - dynamic statistics used: dynamic sampling (level=2)
 

Let’s “color” the above sql query so that it will be captured by the next AWR snapshot and check the captured execution plan to see if it reports the predicate part or not

exec dbms_workload_repository.add_colored_sql('47n9zu0w7ht8d');

exec dbms_workload_repository.create_snapshot;

select * from table(dbms_xplan.display_awr('47n9zu0w7ht8d'));

SQL_ID 47n9zu0w7ht8d
--------------------
select count(*), max(col2) from t2 where flag = :n

Plan hash value: 3321871023
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    30 |            |          |
|   2 |   TABLE ACCESS FULL| T2   |     1 |    30 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
 Note
 -----
  - dynamic statistics used: dynamic sampling (level=2)
 

The predicate part is still not shown in the execution plan taken from AWR. Damn!!

December 25, 2013

Tuning via row source execution plan

Filed under: explain plan — hourim @ 4:25 pm

Browsing over draft blogs I have not finished and forget to come back to, I found one article about a sub optimal execution plan of a query suffering terrible performance problem. If my memory is still servicing me well, I think this execution plan has been sent to me by one of my ex-colleague with whom I have had several e-mail exchange and have supplied him with a proposition. I was waiting for his answer before publishing this blog article. It seems he has never answered since then :-). Anyway, I thought it is, nevertheless, worth sharing with you at least my starting investigations on this query performance problem.

This is the execution plan with row source statistics showing the estimations done by the CBO based on available statistics

 ----------------------------------------------------------------------------------------------------------------------
 | Id  | Operation                            | Name                          | Starts | E-Rows | A-Rows |   A-Time   |
 ----------------------------------------------------------------------------------------------------------------------
 |   1 |  SORT UNIQUE                         |                               |      1 |      1 |    109 |00:00:35.22 | --> 35sec
 |   2 |   WINDOW BUFFER                      |                               |      1 |      1 |    109 |00:00:35.22 |
 |*  3 |    FILTER                            |                               |      1 |        |    109 |00:00:35.21 |
 |   4 |     SORT GROUP BY                    |                               |      1 |      1 |    760 |00:00:35.21 |
 |*  5 |      FILTER                          |                               |      1 |        |   9938 |00:00:35.00 |
 |   6 |       NESTED LOOPS OUTER             |                               |      1 |      1 |  14546 |00:00:34.93 |
 |   7 |        NESTED LOOPS                  |                               |      1 |      1 |  14546 |00:00:33.58 |
 |   8 |         NESTED LOOPS OUTER           |                               |      1 |      1 |    952 |00:00:00.52 |
 |   9 |          NESTED LOOPS OUTER          |                               |      1 |      1 |    952 |00:00:00.31 |
 |* 10 |           TABLE ACCESS BY INDEX ROWID| XXX_TABLE1                    |      1 |      2 |    760 |00:00:00.09 |
 |* 11 |            INDEX RANGE SCAN          | XXX_TAB1_IND_1                |      1 |      2 |   8766 |00:00:00.02 |
 |  12 |           TABLE ACCESS BY INDEX ROWID| XXX_TABLE2                    |    760 |      1 |    248 |00:00:00.22 |
 |* 13 |            INDEX RANGE SCAN          | XXX_TAB2_IND_FK               |    760 |      4 |    248 |00:00:00.19 |
 |* 14 |          TABLE ACCESS BY INDEX ROWID | XXX_TABLE3                    |    952 |      1 |    952 |00:00:00.20 |
 |* 15 |           INDEX RANGE SCAN           | XXX_TABLE3_PK                 |    952 |      1 |   4833 |00:00:00.12 |
 |* 16 |         TABLE ACCESS BY INDEX ROWID  | XXX_TABLE1                    |    952 |      1 |  14546 |00:00:33.04 |
 |* 17 |          INDEX RANGE SCAN            | XXX_TAB1_IND_1                |    952 |      1 |   7980K|00:00:00.05 |
 |* 18 |        TABLE ACCESS BY INDEX ROWID   | XXX_TABLE5                    |  14546 |      1 |     15 |00:00:01.29 |
 |* 19 |         INDEX RANGE SCAN             | XXX_TAB5_IND_1                |  14546 |      1 |     15 |00:00:01.21 |
 |  20 |       SORT AGGREGATE                 |                               |      3 |      1 |      3 |00:00:00.01 |
 |* 21 |        TABLE ACCESS BY INDEX ROWID   | XXX_TABLE2                    |      3 |      1 |     11 |00:00:00.01 |
 |* 22 |         INDEX RANGE SCAN             | XXX_TAB2_IND_FK               |      3 |      4 |     11 |00:00:00.01 |
 ----------------------------------------------------------------------------------------------------------------------
 

where I have deliberately omitted the predicate part for clarity (I will show it in a moment).

Have you already pointed out the most time consuming operation?

If not yet then look at the different operations (from 1 to 22) and isolate  the most consuming child/parent operation  by looking at the A-Time column.

Have you already found it?

Here it is together with its predicate part:

 ----------------------------------------------------------------------------------------------------------------------
 | Id  | Operation                            | Name                          | Starts | E-Rows | A-Rows |   A-Time   |
 ----------------------------------------------------------------------------------------------------------------------
 |* 16 |         TABLE ACCESS BY INDEX ROWID  | XXX_TABLE1                    |    952 |      1 |  14546 |00:00:33.04 |
 |* 17 |          INDEX RANGE SCAN            | XXX_TAB1_IND_1                |    952 |      1 |   7980K|00:00:00.05 |
 ----------------------------------------------------------------------------------------------------------------------
 16 - filter(("TAB1_2"."N1">=20000 AND "TAB1"."XXX_ID"="TAB1_2"."XXX_ID"))
 17 - access("TAB1_2"."DATH">SYSDATE@!-.041666 AND "TAB1"."DAT_TRD"="TAB1_2"."DAT_TRD" AND "TAB1_2"."DATH" IS NOT NULL)
      filter("TAB1"."DAT_TRD"="TAB1_2"."DAT_TRD")
 

This is how to proceed generally to identify the most consuming operation in an execution plan. By the way, this is also my method of tuning a query not performing in a client acceptable response time i.e. (a) I get the row source execution plan including the estimations and actuals (b) and I then scrutinize or scan this plan looking through the A-Time column for the most consuming operation. Thanks to this method, I end up in the majority of cases (there are of course exceptions) by isolating the operation on which attention should be concentrated.

So back to my execution plan. The most consuming operation being found, what observations can be done? Well, without bothering yourself trying to understand the above filter operation, you can, however, obviously point out two major facts

  1. Looking at the high number (7980,000) of rowid the index range scan operation 17 has supplied its parent operation 16 and finally to the very small number of those row-rowids (14,546) that survived the filter operation number 16, I end up by realizing that this is an enormous waste of time and resource spent discarding rows that would have not been sent to the table access operation at all.
  2. Looking at the estimations done by the CBO and the actuals rows generated I ended up by realizing that there is obviously a statistics problem my colleague should look at very urgently.
  3. Almost all E-Rows of the above execution plan has a cardinality equal to one. This particular cardinality is typically suspicious. It is a clear indication of the absence of fresh statistics

Whether the most consuming operation is due to the not up-to-date statistics or to an imprecise index (XXX_TAB1_IND_1) depends on my colleague answer that will probably not come at all.

Bottom line: when you have to tune a query you can proceed using the following steps:

  1. get the row source execution plan from memory using dbms_xplan package that includes Estimations and Actuals, then track the most consuming operation and the accuracy of statistics (table and columns statistics)
  2. get a SQL monitoring report and analyze it if you have a license for
  3. use Tanel Poder snapper
  4. use Carlos Sierra SQLTXPLAN
  5. etc…

October 7, 2013

Intelligent CBO

Filed under: explain plan — hourim @ 6:06 pm

Just a small note to show you a situation I’ve recently encountered which shows how the CBO is intelligent. I have the following two pieces of SQL

UPDATE t1 a
SET
a.padding = 'yyyyyyyy'
WHERE
a.id1 in
(SELECT
     b.id2
FROM t2  b
WHERE a.id1 = a.n1   ---> spot this
);

And the second one

UPDATE t1 a
SET
a.padding = 'yyyyyyyy'
WHERE
a.id1 in
(SELECT
 b.id2
 FROM t2  b
)
AND a.id1 = a.n1;       ---> spot this

I would not have written the first SQL in order to restrict the updates only to records in t1 having identical id1 and n1. I would have logically issued the second one instead.

But to my surprise the CBO recognized that the where clause in the subquery (WHERE a.id1 = a.n1 ) should be applied to the main update by replacing it with the AND clause outside the brackets. Here below are the corresponding execution plans

First query

Plan hash value: 1788758844
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |      |     1 |    91 |   899   (2)| 00:00:03 |
|   1 |  UPDATE             | T1   |       |       |            |          |
|*  2 |   HASH JOIN SEMI    |      |     1 |    91 |   899   (2)| 00:00:03 |
|*  3 |    TABLE ACCESS FULL| T1   |     1 |    78 |   447   (2)| 00:00:02 |
|   4 |    TABLE ACCESS FULL| T2   |   104K|  1320K|   449   (1)| 00:00:02 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."ID1"="B"."ID2")
3 - filter("A"."ID1"="A"."N1")

Note
-----
- dynamic sampling used for this statement (level=2)

Second query

Plan hash value: 1788758844
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |      |     1 |    91 |   899   (2)| 00:00:03 |
|   1 |  UPDATE             | T1   |       |       |            |          |
|*  2 |   HASH JOIN SEMI    |      |     1 |    91 |   899   (2)| 00:00:03 |
|*  3 |    TABLE ACCESS FULL| T1   |     1 |    78 |   447   (2)| 00:00:02 |
|   4 |    TABLE ACCESS FULL| T2   |   104K|  1320K|   449   (1)| 00:00:02 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."ID1"="B"."ID2")
3 - filter("A"."ID1"="A"."N1")

Note
-----
- dynamic sampling used for this statement (level=2)

The same plan hash value and the same predicate part. It’s funny enough.

If you want to play with the test here is the model (borrowed from Jonathan Lewis)

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

select
 rownum                  id2,
 trunc(dbms_random.value(10001,20001))   x1,
 lpad(rownum,10,'0') small_vc,
rpad('x',100)       padding
from
 generator   v1,
 generator   v2
where
rownum <= 100000;

June 19, 2013

Different sql id, different force matching signature, different rows processed with the same plan hash value

Filed under: explain plan — hourim @ 9:07 am

Very recently, two interesting blog articles, here and here, have been published to emphasize the possibility of having a same plan hash value for actually two different execution plans.

Since then, I started opening my eyes for any plan hash value that is shown for two or more execution plans. That’s way, the last week, when I was modeling an example for an outer join in response to a question that came up in the French forum, I was immediately attracted by the following sql I have engineered:

SQL> select
2             d.deptno
3            ,d.dname
4            ,e.hiredate
5      from
6            dept d, emp e
7      where
8            d.deptno = e.deptno(+)
9      AND EXISTS
10                 ( SELECT  NULL
11                   FROM    emp e2
12                   WHERE   e.deptno    = e2.deptno
13                   HAVING  MAX(e2.hiredate) = e.hiredate
14                  -- or e.hiredate is null
15                   )
16       ;

DEPTNO DNAME      HIREDATE
------ ---------- ---------
20    RESEARCH    12/01/83
30    SALES       03/12/81
10    ACCOUNTING  23/01/82

Plan hash value: 2339135578  --> note this plan hash value

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |      3 |00:00:00.01 |
|*  1 |  FILTER              |      |      1 |        |      3 |00:00:00.01 |
|*  2 |   HASH JOIN OUTER    |      |      1 |     14 |     15 |00:00:00.01 |
|   3 |    TABLE ACCESS FULL | DEPT |      1 |      4 |      4 |00:00:00.01 |
|   4 |    TABLE ACCESS FULL | EMP  |      1 |     14 |     14 |00:00:00.01 |
|*  5 |   FILTER             |      |     15 |        |      3 |00:00:00.01 |
|   6 |    SORT AGGREGATE    |      |     15 |      1 |     15 |00:00:00.01 |
|*  7 |     TABLE ACCESS FULL| EMP  |     15 |      5 |     70 |00:00:00.01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
2 - access("D"."DEPTNO"="E"."DEPTNO")
5 - filter(MAX("E2"."HIREDATE")=:B1) --> note this crucial point here
7 - filter("E2"."DEPTNO"=:B1)

And to this a little bit different sql:

SQL> select
2             d.deptno
3            ,d.dname
4            ,e.hiredate
5      from
6            dept d, emp e
7      where
8            d.deptno = e.deptno(+)
9      AND EXISTS
10            ( SELECT  NULL
11              FROM    emp e2
12              WHERE   e.deptno    = e2.deptno
13              HAVING  MAX(e2.hiredate) = e.hiredate
14              or e.hiredate is null  --> this part has been uncommented
15             )
16       ;

DEPTNO DNAME          HIREDATE
---------- -------------- --------
20 RESEARCH       12/01/83
30 SALES          03/12/81
10 ACCOUNTING     23/01/82
40 OPERATIONS

Plan hash value: 2339135578  --> the same plan hash value
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |      4 |00:00:00.01 |
|*  1 |  FILTER              |      |      1 |        |      4 |00:00:00.01 |
|*  2 |   HASH JOIN OUTER    |      |      1 |     14 |     15 |00:00:00.01 |
|   3 |    TABLE ACCESS FULL | DEPT |      1 |      4 |      4 |00:00:00.01 |
|   4 |    TABLE ACCESS FULL | EMP  |      1 |     14 |     14 |00:00:00.01 |
|*  5 |   FILTER             |      |     15 |        |      4 |00:00:00.01 |
|   6 |    SORT AGGREGATE    |      |     15 |      1 |     15 |00:00:00.01 |
|*  7 |     TABLE ACCESS FULL| EMP  |     15 |      5 |     70 |00:00:00.01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
2 - access("D"."DEPTNO"="E"."DEPTNO")
5 - filter((MAX("E2"."HIREDATE")=:B1 OR :B2 IS NULL)) --> for a different predicate
7 - filter("E2"."DEPTNO"=:B1)

Well, nothing new per regard to the two related blog articles mentioned above.  However it is worth pointing out how two different sql_id with two different force matching signature and producing two different result sets, could end up sharing the same plan hash value of two execution plans differencing by their predicate part as shown below:

select
  sql_id
 ,child_number
 ,force_matching_signature
 ,rows_processed
 ,plan_hash_value
from
v$sql
where sql_text like '%MAX(e2.hiredate)%'
and   sql_text not like '%v$sql%';

SQL_ID        CHILD_NUMBER FORCE_MATCHING_SIGNATURE ROWS_PROCESSED PLAN_HASH_VALUE
------------- ------------ ------------------------ -------------- ---------------
ba39fv7txcsbk            0 6256287308517838235              4      2339135578
b2kggnvz02ctk            0 1563627505656661161              3      2339135578

In this context of plan hash value inspection, Carlos sierra from the Oracle support published a blog article showing that his sqltxplain tool has the ability to consider execution plan using not only the plan hash value but two additional pieces of information which are SQLT Plan Hash Value1 and SQLT Plan Hash Value2. The last information is related to the predicate part which is here the central point of difference between “my” two execution plans.

Well, why not try sqltxtract for this case using sql_id ba39fv7txcsbk (click on the picture to enlarge it)?

Plan hash value2

Spot how the sqltxtract module shows the presence of two execution plans having the same plan hash value (2339135578) but different plan hash value2(62199 and 22135). This plan hash value2 concerns a difference into the access and/or the filter predicates.

But wait, this doesn’t mean that the sql_id ba39fv7txcsbk has necessarily got two different execution plans. All what it clearly indicates is that the plan hash value of this parent sql_id has been seen two times, each time with a different access and/or filter predicates. This is confirmed by the sql scripts given by Carlos sierra which when applied to this particular case gives this:


SQL> start c:\psql_id
Enter value for sql_id: ba39fv7txcsbk

no rows selected

Meaning that this sql_id has not got a difference in the predicate part of its execution plan.


SQL> start c:\phash

Enter value for plan_hash_value: 2339135578

ID  TYPE     SQL_ID         CHILD_NUMBER  PREDICATES
---- -------- -------------- ------------- ------------------------------------------
5   filter  ba39fv7txcsbk    0             (MAX("E2"."HIREDATE")=:B1 OR :B2 IS NULL)
5   filter  b2kggnvz02ctk    0              MAX("E2"."HIREDATE")=:B1

Meaning that this plan_hash_value has got two execution plans having two different predicate parts for two different sql_ids

April 18, 2013

Interpreting Execution Plan

Filed under: explain plan — hourim @ 12:11 pm

I have been confronted to a performance issue with a query that started performing badly (6 sec.  instead of the usual 2 sec. ) following a change request that introduces a new business requirement. Below is the new execution plan stripped to the bare minimum and where table and index names have been a little bit disguised to protect the innocent.  I have manually introduced two aliases (MHO and YAS) in this execution plan so that the predicate part will be easily linked to its corresponding table (I know there is a difference between E-Rows and A-Rows for certain operations; that’s not my intention to deal with  here in this blog post)

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                        | Starts | E-Rows | A-Rows |   A-Time   |
---------------------------------------------------------------------------------------------------------------------------
|*  8 |         HASH JOIN OUTER                    |                              |      1 |      1 |   2218 |00:00:05.45 |
|   9 |--->      NESTED LOOPS                      |                              |      1 |      1 |   2218 |00:00:03.44 |
|  10 |           NESTED LOOPS                     |                              |      1 |      1 |   2218 |00:00:03.43 |
|  11 |       ---> VIEW                            | XXX_DEMANDE_MANAGMENT_V      |      1 |    251 |    125K|00:00:02.43 |
|  12 |             UNION-ALL                      |                              |      1 |        |    125K|00:00:02.43 |
|  13 |              NESTED LOOPS                  |                              |      1 |      1 |      0 |00:00:00.01 |
|  14 |               INDEX FULL SCAN              | XXX_CLS_BUR_OPR_UK           |      1 |      1 |      0 |00:00:00.01 |
|  15 |               TABLE ACCESS BY INDEX ROWID  | XXX_ASPECT                   |      0 |      1 |      0 |00:00:00.01 |
|* 16 |                INDEX UNIQUE SCAN           | XXX_BUR_PK                   |      0 |      1 |      0 |00:00:00.01 |
|* 17 |              FILTER                        |                              |      1 |        |    125K|00:00:02.31 |
|* 18 |               HASH JOIN                    |                              |      1 |    126K|    125K|00:00:01.18 |
|  19 |                NESTED LOOPS                |                              |      1 |    251 |    251 |00:00:00.01 |
|  20 |                 VIEW                       | index$_join$_054             |      1 |    251 |    251 |00:00:00.01 |
|* 21 |                  HASH JOIN                 |                              |      1 |        |    251 |00:00:00.01 |
|  22 |                   INDEX FAST FULL SCAN     | XXX_BUR_SOM_FK_I             |      1 |    251 |    251 |00:00:00.01 |
|  23 |                   INDEX FAST FULL SCAN     | XXX_BUR_MSF_BUR_FK_I         |      1 |    251 |    251 |00:00:00.01 |
|* 24 |                 INDEX UNIQUE SCAN          | XXX_SOM_PK                   |    251 |      1 |    251 |00:00:00.01 |
|  25 |                VIEW                        | index$_join$_053             |      1 |    126K|    125K|00:00:00.79 |
|* 26 |                 HASH JOIN                  |                              |      1 |        |    125K|00:00:00.67 |
|  27 |                  INDEX FAST FULL SCAN      | XXX_RIP_PK                   |      1 |    126K|    125K|00:00:00.01 |
|  28 |                  INDEX FAST FULL SCAN      | XXX_RIP_BUR_FK_I             |      1 |    126K|    125K|00:00:00.01 |
|* 29 |               INDEX RANGE SCAN             | XXX_CLS_RIP_FK_I             |    125K|      1 |      0 |00:00:00.66 |
|* 30 |       ---> TABLE ACCESS BY INDEX ROWID     | XXX_DEMANDE_ORDINAIR (MHO)   |    125K|      1 |   2218 |00:00:02.97 |
|* 31 |             INDEX UNIQUE SCAN              | XXX_RIP_PK                   |    125K|      1 |    125K|00:00:00.89 |
|  33 |--->      VIEW (YAS)                        |                              |      1 |     82 |   1218 |00:00:00.08 |
|  34 |           SORT UNIQUE                      |                              |      1 |     82 |   1218 |00:00:00.08 |
--------------------------------------------------------------------------------------------------------------------------

8 - access("YAS"."PK_ID"="MHO"."PK_ID")
30 – filter(("MHO"."CLOSED"<>3 AND TRUNC(INTERNAL_FUNCTION("MHO"."CREATION_DATE"))=TRUNC(TO_DATE('12042013','ddmmrrrr')))
OR (TRUNC(TO_DATE('12042013','ddmmrrrr'))=TO_DATE('01010001','ddmmrrrr') ))

Well, from where am I going to start here?

Hmmm… always the same question when trying to interpret an execution plan.

After looking carefully to that execution plan and to its predicate part (always consider the predicate part) I ended up asking myself the following question:

There is HASH JOIN OUTER (Id 8) between a NESTED LOOPS (Id 9) and the VIEW (YAS – id 33)

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Starts | E-Rows | A-Rows |   A-Time   |
--------------------------------------------------------------------------------------
|*  8 |         HASH JOIN OUTER     |        |      1 |      1 |   2218 |00:00:05.45 |
|   9 |--->      NESTED LOOPS       |        |      1 |      1 |   2218 |00:00:03.44 |
|  33 |--->      VIEW (YAS)         |        |      1 |     82 |   1218 |00:00:00.08 |
--------------------------------------------------------------------------------------

On which a filter operation is applied in order to filter the result by comparing the YAS view with the MHO table via their ”primary key” (PK_ID)

8 - access("YAS"."PK_ID"="MHO"."PK_ID")

That’s seems a little bit strange. Why not a direct HASH JOIN OUTER between the YAS view and the MHO table instead of a JOIN between the YAS view and that NESTED LOOPS (where an access to MHO table is made)?

Have you already pointed out how the predicate part can make you asking good questions?

My second step has been to look at the predicate part of the MHO table access (operation 30) re-printed here below:

30 – filter(("MHO"."CLOSED"<>3 AND TRUNC(INTERNAL_FUNCTION("MHO"."CREATION_DATE"))=TRUNC(TO_DATE('12042013','ddmmrrrr')))
OR (TRUNC(TO_DATE('12042013','ddmmrrrr'))=TO_DATE('01010001','ddmmrrrr') ))

I have an adequate function based index in place that should have been used provided this INTERNAL_FUNCTION has not being used by the CBO

create index XXX_RIP_CREATION_DATE_I on MHO(trunc(creation_date)) ;

This is why I was tempted to force my query to use this index via the appropriate hint. Here below is the resulting execution plan

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                        | Starts | E-Rows | A-Rows |   A-Time    |
---------------------------------------------------------------------------------------------------------------------------
|   8 |         NESTED LOOPS                        |                              |      1 |      1 |   2218 |00:00:02.57 |
|*  9 |--->      HASH JOIN OUTER                    |                              |      1 |      1 |   2218 |00:00:02.48 |
|* 10 |      ---> TABLE ACCESS BY INDEX ROWID       | XXX_DEMANDE_ORDINAIR(MHO)    |      1 |      1 |   2218 |00:00:02.34 |
|  11 |            INDEX FULL SCAN                  | XXX_RIP_CREATION_DATE_I      |      1 |    126K|    125K|00:00:01.82 |
|  12 |      ---> VIEW(YAS)                         |                              |      1 |     82 |   1218 |00:00:00.08|
|  13 |            SORT UNIQUE                      |                              |      1 |     82 |   1218 |00:00:00.08 |
|  41 |--->      VIEW                               | XXX_DEMANDE_MANAGMENT_V      |   2218 |      1 |   2218 |00:00:00.08 |
|  42 |           UNION-ALL PARTITION               |                              |   2218 |        |   2218 |00:00:00.08 |
|  43 |            NESTED LOOPS                     |                              |   2218 |      1 |      0 |00:00:00.01 |
|  44 |             TABLE ACCESS BY INDEX ROWID     | XXX_DEMANDE_RESPONSABLE      |   2218 |      1 |      0 |00:00:00.01 |
|* 45 |              INDEX RANGE SCAN               | XXX_CLS_RIP_FK_I             |   2218 |      1 |      0 |00:00:00.01 |
|  46 |             TABLE ACCESS BY INDEX ROWID     | XXX_ASPECT                   |      0 |      1 |      0 |00:00:00.01 |
|* 47 |              INDEX UNIQUE SCAN              | XXX_BUR_PK                   |      0 |      1 |      0 |00:00:00.01 |
|  48 |            NESTED LOOPS                     |                              |   2218 |      1 |   2218 |00:00:00.06 |
|  49 |             NESTED LOOPS                    |                              |   2218 |      1 |   2218 |00:00:00.05 |
|  50 |              TABLE ACCESS BY INDEX ROWID    | XXX_DEMANDE_ORDINAIR         |   2218 |      1 |   2218 |00:00:00.03 |
|* 51 |               INDEX UNIQUE SCAN             | XXX_RIP_PK                   |   2218 |      1 |   2218 |00:00:00.02 |
|* 52 |                INDEX RANGE SCAN             | XXX_CLS_RIP_FK_I             |   2218 |      1 |      0 |00:00:00.01 |
|  53 |              TABLE ACCESS BY INDEX ROWID    | XXX_ASPECT_                  |   2218 |    251 |   2218 |00:00:00.01 |
|* 54 |               INDEX UNIQUE SCAN             | XXX_BUR_PK                   |   2218 |      1 |   2218 |00:00:00.01 |
|* 55 |             INDEX UNIQUE SCAN               | XXX_SOM_PK                   |   2218 |     36 |   2218 |00:00:00.01 |
---------------------------------------------------------------------------------------------------------------------------
9 - access("YAS"."PK_ID"="MHO"."PK_ID")
10 - filter(("MHO"."CLOSED"<>3) AND "PRI"."SYS_NC00047$"=TRUNC(TO_DATE('12042013','ddmmrrrr'))) OR
(TRUNC(TO_DATE('12042013','ddmmrrrr'))=TO_DATE('01010001','ddmmrrrr')))

That is a better step accomplished (from 5,45 to 2,57 seconds). Isn’t it?  Look now how my HASH OUTER JOIN became

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                     | Starts | E-Rows | A-Rows |   A-Time   |
------------------------------------------------------------------------------------------------------------------
|   8 |         NESTED LOOPS                  |                          |      1 |      1 |   2218 |00:00:02.57 |
|*  9 |--->      HASH JOIN OUTER              |                          |      1 |      1 |   2218 |00:00:02.48 |
|* 10 |      ---> TABLE ACCESS BY INDEX ROWID | XXX_DEMANDE_ORDINAIR(MHO)|      1 |      1 |   2218 |00:00:02.34 |
|  11 |            INDEX FULL SCAN            | XXX_RIP_CREATION_DATE_I  |      1 |    126K|    125K|00:00:01.82 |
|  12 |      ---> VIEW(YAS)                   |                          |      1 |     82 |   1218 |00:00:00.08 |
------------------------------------------------------------------------------------------------------------------

As I wanted it to be: directly between the MHO table and the YAS view.

But wait a moment please. It seems for me that this INDEX FULL SCAN operation is still to be tuned.  Do you know why? Because this operation is feeding back its parent operation (id 10) with 125,000 rowids of which only 2218 records are kept. 98% of those rowids are thrown away by the filter operation n° 10

10 - filter(("MHO"."CLOSED"<>3) AND "PRI"."SYS_NC00047$"=TRUNC(TO_DATE('12042013','ddmmrrrr'))) OR
(TRUNC(TO_DATE('12042013','ddmmrrrr'))=TO_DATE('01010001','ddmmrrrr')))

Clearly this is a waste of time and resource. It is better to have a more precise index or to not use that XXX_RIP_CREATION_DATE_I index at all.  But how can I  (see this is another question again) make the CBO generating the HASH JOIN OUTER I want without forcing the use of that function based index?

Okay…

It’s time to look to the content of the query. The part of the query involving my two tables join looks like this

select
 mho.*
,abc.col1
,abc.col2
from
  XXX_DEMANDE_ORDINAIR       mho
, XXX_DEMANDE_MANAGMENT_V    abc
, my_view                    yas
where  mho.pk        = abc.pk
and    mho.pk_id     = yas.pk_id(+)
etc…

The view YAS is not selected from. It should be taken out from the join and put into the where clause as an EXISTS condition.

select
mho.*
,abc.col1
,abc.col2
from
 XXX_DEMANDE_ORDINAIR       mho
,XXX_DEMANDE_MANAGMENT_V    abc
where  mho.pk        = abc.pk
and  exists (select null
             from  my_view yas
             where mho.pk_id  = yas.pk_id)
etc…

I was going to change this when one of my colleagues suggested me to change the above query as follows (please spot the difference there is only a (+) added)

select
 mho.*
,abc.col1
,abc.col2
from
 XXX_DEMANDE_ORDINAIR       mho
,XXX_DEMANDE_MANAGMENT_V    abc
,my_view                    yas
where  mho.pk        = abc.pk(+) --> This will not change the result because I know there is always a record in abc table
and    mho.pk_id     = yas.pk_id(+)
etc…

Doing so, the ABC table will not be considered by the CBO as the driving table because it is the table that is outer joined (is this correct? I have to admit that I need to test it deeply in order to be sure enough about that fact).  As such, the CBO will directly join the MHO table with the YAS view first (this is what I want in fact) and then outer join the result to the third table.

Anyway, I did as suggested and here below what I ended up with

---------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                     | Starts | E-Rows | A-Rows |   A-Time   |
--------------------------------------------------------------------------------------------------------
|   9 |         NESTED LOOPS          |                          |      1 |      1 |   2218 |00:00:01.07 |
|* 10 |--->      HASH JOIN OUTER      |                          |      1 |      1 |   2218 |00:00:00.99 |
|  11 |       -->  TABLE ACCESS FULL  | XXX_DEMANDE_ORDINAIR(MHO)|      1 |      1 |   2218 |00:00:00.27 |
|  12 |       -->  VIEW(YAS)          |                          |      1 |     82 |   1218 |00:00:00.08 |
---------------------------------------------------------------------------------------------------------
9 - access("YAS"."PK_ID"="MHO"."PK_ID")
10 – filter(("MHO"."CLOSED"<>3 AND TRUNC(INTERNAL_FUNCTION("MHO"."CREATION_DATE"))=TRUNC(TO_DATE('12042013','ddmmrrrr')))
OR (TRUNC(TO_DATE('12042013','ddmmrrrr'))=TO_DATE('01010001','ddmmrrrr') ))

The query is now responding better than before the change request. Doing a full table scan on MHO table in this case is better than to access it via the existing non precise function based index and then filter the returned rows(by rowid) to through 97% of them.

Bottom line: the goal of this article is to show how important the predicate part can be in tuning a query via its execution plan. I started questioning myself from the join predicate part followed by the use of an adequate index and finally I ended up by searching the best  order of the table  join

March 14, 2012

ORDER BY and FIRST_ROWS

Filed under: explain plan — hourim @ 11:56 am

Recently an interesting question came up on a French oracle forum about a costly order by operation. The original poster said that he has a query like this:

select *
from t1 where id in (select id from t2 where x1 = :b1) ;

which is returning 39 records in a very acceptable response time. However, when he adds a simple order by to that query, the response time become very nasty and not acceptable at all. When I asked about the value of the optimizer mode he is using, I was not really surprised when he answered “I am running under FIRST_ROWS mode”.   The aim of this blog is exactly to explain the nasty side effect the FIRST_ROWS optimizer can produce particularly in presence of an order by clause.

The model (borrowed from Jonathan Lewis) is:

mhouri.world> create table t1
  2  as
  3  with generator as (
  4      select  --+ materialize
  5          rownum id
  6      from dual
  7      connect by
  8          level <= 10000)
  9  select
 10      rownum                  id,
 11      trunc(dbms_random.value(1,1000))    n1,
 12      lpad(rownum,10,'0') small_vc,
 13      rpad('x',100)       padding
 14  from
 15      generator   v1,
 16      generator   v2
 17  where
 18      rownum <= 1000000;
Table created.

mhouri.world> create index t1_n1 on t1(id, n1);
Index created.

mhouri.world> create table t2
  2  as
  3  with generator as (
  4      select  --+ materialize
  5          rownum id
  6      from dual
  7      connect by
  8          level <= 10000)
  9  select
 10      rownum                  id,
 11      trunc(dbms_random.value(10001,20001))   x1,
 12      lpad(rownum,10,'0') small_vc,
 13      rpad('x',100)       padding
 14  from
 15      generator   v1,
 16      generator   v2
 17  where
 18      rownum <= 1000000;
Table created.

mhouri.world> create index t2_i1 on t2(x1);
Index created.

mhouri.world> exec dbms_stats.gather_table_stats(user, 't2', method_opt => 'FOR ALL COLUMNS SIZE 1');
PL/SQL procedure successfully completed.

mhouri.world> exec dbms_stats.gather_table_stats(user, 't1', method_opt => 'FOR ALL COLUMNS SIZE 1');
PL/SQL procedure successfully completed.

mhouri.world> show parameter optimiz%
NAME                                 TYPE        VALUE                                                                 
------------------------------------ ----------- ------------------------                
optimizer_mode                       string      ALL_ROWS                  
                                             

mhouri.world> select *
  2  from t1 where id in (select id from t2 where x1 = 17335)
  3  order by id
  4  ;

107 rows selected.

mhouri.world> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT                                                                                                      
-----------------------------------------------------------------------------------------
SQL_ID  d2wym19g2n196, child number 1                                                                                  
-------------------------------------                                                                                  
select * from t1 where id in (select id from t2 where x1 = 17335) order by id                                          
Plan hash value: 153456901                                                                                             
-----------------------------------------------------------------------------------------                              
| Id  | Operation                       | Name  | Rows  | Bytes | Cost (%CPU)| Time     |                              
-----------------------------------------------------------------------------------------                              
|   0 | SELECT STATEMENT                |       |       |       |   255 (100)|          |                              
|   1 |  SORT ORDER BY                  |       |   100 | 13000 |   255   (1)| 00:00:02 |                              
|   2 |   TABLE ACCESS BY INDEX ROWID   | T1    |     1 |   120 |     3   (0)| 00:00:01 |                              
|   3 |    NESTED LOOPS                 |       |   100 | 13000 |   254   (1)| 00:00:02 |                              
|   4 |     SORT UNIQUE                 |       |   100 |  1000 |   103   (0)| 00:00:01 |                              
|   5 |      TABLE ACCESS BY INDEX ROWID| T2    |   100 |  1000 |   103   (0)| 00:00:01 |                              
|*  6 |       INDEX RANGE SCAN          | T2_I1 |   100 |       |     3   (0)| 00:00:01 |                              
|*  7 |     INDEX RANGE SCAN            | T1_N1 |     1 |       |     2   (0)| 0:00:01                                 
-----------------------------------------------------------------------------------------                              
Predicate Information (identified by operation id):                                                                    
---------------------------------------------------                                                                    
   6 - access("X1"=17335)                                                                                              
   7 - access("ID"="ID")                                                                                         
25 rows selected.

Ok that’s nice and acceptable response time. But let’s try the same query under first_rows mode

mhouri.world> alter session set optimizer_mode=FIRST_ROWS;
Session altered.

mhouri.world> select *
  2  from t1 where id in (select id from t2 where x1 = 17335)
  3  order by id
  4  ;

107 rows selected.

mhouri.world> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT                                                                                                      
--------------------------------------------------------------------------------------
SQL_ID  d2wym19g2n196, child number0                                                                                   
-------------------------------------                                                                                  
select * from t1 where id in (select id from t2 where x1 = 17335) order by id                               
Plan hash value: 3283237002                                                                                            

--------------------------------------------------------------------------------------                                 
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |                                 
--------------------------------------------------------------------------------------                                 
|   0 | SELECT STATEMENT             |       |       |       |   102M(100)|          |                                 
|   1 |  NESTED LOOPS SEMI           |       |   101 | 13130 |   102M  (1)|168:20:17 |                                 
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |  1000K|   114M| 20385   (1)| 00:02:01 |                                 
|   3 |    INDEX FULL SCAN           | T1_N1 |  1000K|       |  2800   (1)| 00:00:17 |                                 
|*  4 |   TABLE ACCESS BY INDEX ROWID| T2    |     1 |    10 |   102   (0)| 00:00:01 |                                 
|*  5 |    INDEX RANGE SCAN          | T2_I1 |   100 |       |     2   (0)| 00:00:01 |                                 
--------------------------------------------------------------------------------------                                 
Predicate Information (identified by operationid):                                                                     
---------------------------------------------------                                                                    
   4 - filter("ID"="ID")                                                                                               
   5 - acces("X1"=17335)                                                                                               
23 rows selected.

Woo!!! Note the apparition of the costly operation 3 INDEX FULL SCAN, the enormous cost difference between all_rows and first_rows and the absence of the order by operation. Under the later mode, Oracle optimizer prefers using a costly INDEX FULL SCAN operation instead of doing a sort operation which in this case will generate a catastrophic response time.

December 29, 2011

Getting Explain Plan

Filed under: explain plan — hourim @ 3:04 pm

1. Introduction

In order to tune a query you need first to get its real execution plan that describes the operations dictated by the Oracle optimizer and followed by the SQL engine to execute this query. It can’t be enough emphasized that getting the exact explain plan (and not the expected one) represents an important step when troubleshooting query performance problems. This document aims to describe in detail how to obtain the real explain plan followed by the SQL engine.

2. Obtaining Explain plans

First of all it’s worth mentioning to avoid using tools like TOAD to get the explain plan of your query because, more often than not, it does not provide all the information you need for a thorough analysis. This is why I am going to present only tools that have been developed by Oracle Corporation. Among those tools the first one is the classical explain plan for as shown in the below example:

     2.1 Explain plan for command and its limitation          

Unfortunately there exist situations where the ‘explain plan for’ command may not report the real explain plan followed by the Oracle Optimizer to execute the query. This situation occurs particularly when using bind variables.  You should know that the ‘explain plan for’ command treats all bind variables as of a VARCHAR2 data type; this is why an implicit data type conversion might happen during the ‘explain plan for’ command while there is no implicit data type conversion when the query is executed. And that’s where the limit of the ‘explain plan for’ command comes from.

mhouri.world> create table t (id varchar2(10), name varchar2(100));

Table created.

mhouri.world> insert into t select to_char(object_id), object_name from user_objects;

1004 rows created.

mhouri.world> create index i on t(id);

Index created.

mhouri.world> exec dbms_stats.gather_table_stats(user,'T',cascade=>true);

PL/SQL procedure successfully completed.
mhouri.world> var x number

mhouri.world> exec x:= 99999

PL/SQL procedure successfully completed.

mhouri.world> explain plan for select sum(length(name)) from t where id >:x;

Explained.

mhouri.world> select * from table(dbms_xplan.display) ;

-------------------------------------------------------------------------------------
Plan hash value: 1188118800
-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     1 |    24 |     4   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |      |     1 |    24 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |    50 |  1200 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I    |     9 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID">:X)

Remember that the id column has been declared as varchar2 and that we issued our ‘explain plan for’ command using a bind variable declared as number (var x number). As far as the ‘explain plan for’ command treats all bind variables as VARCHAR2 it has reported that the index has been used; which is, unfortunately, completely false. And that is the topic of the next paragraph.

            2.2     dbms_xplan.display_cursor             

In order to get the real explain plan we need to use the display_cursor function of the dbms_xplan package as shown below:

mhouri.world> select sum (length (name)) from t where id >:x;
SUM (LENGTH (NAME))
-----------------
8145

mhouri.world> select * from table (dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
SQL_ID  7zm570j6kj597, child number 0
-------------------------------------
select sum(length(name)) from t where id > x
Plan hash value: 1842905362
--------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     5 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    24 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |    50 |  1200 |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_NUMBER("ID")>:X)

Notice how the dbms_xplan.display_cursor() function has correctly reported a TABLE ACCESS FULL instead of the INDEX RANGE SCAN path (initially reported by the ‘explain plan for’ command) due to the TO_NUMBER (see the predicate information) conversion that occurred during the query execution.

            2.3     dbms_xplan.display_cursor (null, null, ‘ALLSTATS LAST’)            

Now that we are quite confident with the use of dbms_xplan.display_cursor function instead of the approximate ‘explain plan for’ function, It is worth mentioning that the function dbms_xplan.display_cursor when invoked via specific parameters, can give extra valuable information that might be of a great help for explaining query performance problems and very often to show stale statistics. Let’s look to that through the following example:

mhouri.world> select /*+ gather_plan_statistics */
2  ename, hiredate, sal, deptno
3  from emp
4  where deptno = 20;

ENAME      HIREDATE         SAL     DEPTNO
---------- --------- ---------- ----------
allen      30-MAR-10        815         20
jones      02-APR-81       2975         20
scott      09-DEC-82       3000         20
adams      12-JAN-83       1100         20
ford       03-DEC-81       3000         20

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  7xs5xf4bnkmgs, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ ename, hiredate, sal, deptno from emp where
deptno = 20
Plan hash value: 2872589290
----------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers| Reads  |
--------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| EMP  |      1 |      5 |      5 |00:00:00.01 |    16 |    14 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPTNO"=20)

The new extra information together with their explanations are shown here below:

  1. Starts   : the number of time the operation has been started or executed
  2. E-Rows : the number of Estimated Rows for the current Operation
  3. A-Rows : the number of Actual Rows retrieved by the current Operation

It is well known that the Optimizer will do a good job only if E-Rows and A-Rows have close values. When, instead, these two cardinalities present two largely different values, then this is a clear indication of inefficient execution plan probably due to stale statistics on related tables and indexes.  However, be aware that, before jumping to conclusions, you need always to compare E-Rows with round (A-Rows/Starts).

 3 Grants and privileges

In order to be able to call the dbms_xplan.display_cursor() function you need to access to the following dynamic performance views

  • v$session
  • v$sql
  • v$sql_plan
  • v$sql_plan_statistics_all

And if you don’t supply the sql_id or the child_number to the dbms_xplan.display_cursor() function, it will reports the last executed SQL statement.

Next Page »

Create a free website or 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)