DBMS_XPLAN versus Autotrace

Since Oracle 10g, there is a nice BUILT-IN package named DBMS_XPLAN that gives a very nicely formatted explain plan. This blog article is just to show that you can still continue using the ‘set autotrace on’ SQL command in order to get your explain plan but you need to be aware that this SQL command use the estimated part of the DBMS_XPLAN package. In other words when you use autotrace Oracle is using behind the scene the  explain plan for plus select * from table (dbms_xplan. display).

Let’s see this in action:

mhouri> cl scr
mhouri> desc t

Name                    Null?    Type
----------------------- -------- ----------------
ID                               VARCHAR2(10)
NAME                             VARCHAR2(100)

mhouri> set linesize 150

mhouri> var x number

mhouri> exec : x :=99999

PL/SQL procedure successfully completed.

mhouri> explain plan for

2  select sum(length(name)) from t where id >  : x;

Explained.

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

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

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

mhouri> set autotrace on
mhouri> select sum(length(name)) from t where id >  : x;

SUM(LENGTH(NAME))
-----------------
10146

Execution Plan
----------------------------------------------------------
Plan hash value: 1188118800
-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     1 |    23 |     4   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |      |     1 |    23 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |    58 |  1334 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I    |    11 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID">:X)

Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
15  consistent gets
0  physical reads
0  redo size
232  bytes sent via SQL*Net to client
243  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed

mhouri> set autotrace off

mhouri> select sum(length(name)) from t where id >  : x;

SUM(LENGTH(NAME))
-----------------
10146

mhouri> 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 |    23 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |    59 |  1357 |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

19 rows selected.

Séminaire Oracle Lille 14-04-2011 : Cloud computing et Exadata

La présentation était plus axée sur ce que les ingénieurs avant vente d’Oracle appellent le cloud computing ;  exadata ne représente qu’une partie de cette nouvelle architecture qui sera ‘bientôt’ annoncée par Oracle.

Grosso-modo, le représentant parisien d’Oracle a parlé de la stratégie Oracle qui est passée de (8i, 9i) vers (10g, 11g) et bientôt vers 12c (c venant du cloud computing). Il a forcé le trait sur les concepts suivants :

1. Saas : Software as a service
2. Paas : Platform as a service        ——>        à c’est à ce niveau là qu’intervient exadata
3. Iaas : Infrastructure as a service

Quant à Exadata, il faut principalement savoir que c’est une machine créé suite au rachat de SUN. Cette machine a une capacité de stockage modulable qui peut atteindre 20 Térabytes.  Elle est livrée avec une base de données 11gR2 déjà préinstallée.  En somme, elle contient la base de données et la machine Unix de stockage qui va avec. Lorsqu’on achète cette machine Exadata, vu qu’elle est paramétrée préalablement par Oracle, sa configuration est donc connue du support ce qui facilite la résolution des problèmes.

Un détail important : cette machine coute 400.000€ (si j’ai bien entendu le prix annoncé lors de cette présentation).  On peut se contenter d’une machine exadata de 4T ou de 9T ; dans ce cas je ne sais pas si le prix sera revu à la baisse ou pas. Il me semble aussi qu’on peut augmenter la capacité de stockage très facilement.

Commercialement, il y a trois clients dans le nord de la France qui ont déjà migré vers exadata, carrefour (qui a multiplié par 5 ses temps de réponses), la redoute et un client de la SNCF. Il a parlé de certains clients en Belgique aussi.

Au niveau national, la BNP Paribas a multiplié par 17 la performance de ses applications.

Enfin, ramené à notre niveau de technicien, notre intérêt devra être concentré sur la compréhension du fonctionnement d’exadata par rapport à une base de données classique. Il semblerait qu’exadata favorise les fulls table scan par rapport aux index scans. Une des questions qu’il va falloir donc se la poser lors d’une migration vers exadata est la suivante : faut-il migrer les indexes ?

Le représentant d’Oracle a passé du temps à parler d’Amazon EC2 qui est compatible avec Oracle 11gR2 XE pour ceux qui veulent s’initier au cloud computing

SQL-Loader direct path and duplicate key

Very often I see on the OTN site a thread where a poster is wondering why, despite the presence of a primary key on his table, this one contains duplicate key when it is loaded via sqlloader using direct path load.

The answer to this question is that when you direct path load, the unique index supporting the primary key is disabled behind the scene allowing duplicate key to be inserted. Here below is the demonstration of this behaviour :

mhouri.world> create table t1 (id number);

Table created.

mhouri.world> alter table t1 add constraint t1_pk primary key (id);

Table altered.

mhouri.world> select table_name, index_name, status
 2  from user_indexes
 3  where table_name = 'T1';

TABLE_NAME                     INDEX_NAME                     STATUS
------------------------------ ------------------------------ --------
T1                             T1_PK                          VALID

mhouri.world> select count(1) from t1;

 COUNT(1)
----------
 0

The control file and the  sqlloader call are shown here below:

LOAD DATA
INFILE *
REPLACE
INTO TABLE T1
(
 id position(1:1) TERMINATED BY ","
)
BEGINDATA
1;
2;
2;
3;
c:\sqlldr mhouri/mhouri@mhouri control=c.ctl direct=true

mhouri.world> select count(1) from t1;</pre>
COUNT(1)
 ----------
 4

mhouri.world> select * from t1;

ID
 ----------
 1
 2
 2
 3

mhouri.world> select table_name, index_name, status
 2  from user_indexes
 3  where table_name = 'T1';

TABLE_NAME                     INDEX_NAME                     STATUS
 ------------------------------ ------------------------------ --------
 T1                             T1_PK                          UNUSABLE

mhouri.world> alter index t1_pk rebuild;
 alter index t1_pk rebuild
 *
 ERROR at line 1:
 ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

This is one of the thing that we all have to be aware of when using direct path load : uniqueness is ignored.