Question optimisation requête

Bonjour,
J'ai une requête ci-dessous qui consomme beaucoup des io logiques sur une base de données 10.2.0.4. :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 select * from ( select this_.dieudo_event_id as dieudo1_3_2_, this_.version as version3_2_, this_.batch_id as batch3_3_2_, this_.event_date as event4_3_2_, this_.event_type_id as event5_3_2_, this_.object_id as object6_3_2_, this_.object_type_id as object7_3_2_, dieudoe2_.event_type_id as event1_4_0_, dieudoe2_.name as name4_0_, dieudoo3_.object_type_id as object1_5_1_, dieudoo3_.name as name5_1_ from dieudo_event this_, dieudo_event_type dieudoe2_, dieudo_object_type dieudoo3_ where , and this_.object_type_id=dieudoo3_.object_type_id and this_.event_date>=:1 and this_.event_date<=:2 order by this_.dieudo_event_id asc ) where rownum <= :3
Voici ci-dessous le plan d'execution:

Citation:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 91933269

--------------------------------------------------------------------------------
--------------------------

| Id | Operation | Name | Rows | Bytes
| Cost (%CPU)| Time |

--------------------------------------------------------------------------------
--------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 425 (100)| |

|* 1 | COUNT STOPKEY | | |
| | |

| 2 | VIEW | | 10 | 17460
| 425 (1)| 00:00:06 |

|* 3 | FILTER | | |
| | |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

| 4 | NESTED LOOPS | | 10 | 880
| 425 (1)| 00:00:06 |

| 5 | NESTED LOOPS | | 10 | 700
| 415 (1)| 00:00:05 |

|* 6 | TABLE ACCESS BY INDEX ROWID| dieudo_EVENT | 7643 | 440
K| 405 (1)| 00:00:05 |

| 7 | INDEX FULL SCAN | SYS_C0044096 | 4000 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 35 (0)| 00:00:01 |

| 8 | TABLE ACCESS BY INDEX ROWID| dieudo_OBJECT_TYPE | 1 | 11
| 1 (0)| 00:00:01 |

|* 9 | INDEX UNIQUE SCAN | SYS_C0044100 | 1 |
| 0 (0)| |

| 10 | TABLE ACCESS BY INDEX ROWID | dieudo_EVENT_TYPE | 1 | 18
| 1 (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 11 | INDEX UNIQUE SCAN | SYS_C0044098 | 1 |
| 0 (0)| |

--------------------------------------------------------------------------------
--------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
2 - SEL$2 / from$_subquery$_001@SEL$1
3 - SEL$2
6 - SEL$2 / THIS_@SEL$2
7 - SEL$2 / THIS_@SEL$2
8 - SEL$2 / dieudoO3_@SEL$2
9 - SEL$2 / dieudoO3_@SEL$2
10 - SEL$2 / dieudoE2_@SEL$2
11 - SEL$2 / dieudoE2_@SEL$2

Outline Data
-------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$1")
NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")
INDEX(@"SEL$2" "THIS_"@"SEL$2" ("dieudo_EVENT"."dieudo_EVENT_ID"))
INDEX_RS_ASC(@"SEL$2" "dieudoO3_"@"SEL$2" ("dieudo_OBJECT_TYPE"."OBJ

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
ECT_TYPE_ID"))

INDEX_RS_ASC(@"SEL$2" "dieudoE2_"@"SEL$2" ("dieudo_EVENT_TYPE"."EVEN
T_TYPE_ID"))

LEADING(@"SEL$2" "THIS_"@"SEL$2" "dieudoO3_"@"SEL$2" "dieudoE2_"@"SE
L$2")

USE_NL(@"SEL$2" "dieudoO3_"@"SEL$2")
USE_NL(@"SEL$2" "dieudoE2_"@"SEL$2")
END_OUTLINE_DATA

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
*/

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

1 - filter(ROWNUM<=:3)
3 - filter(:1<=:2)
6 - filter(("THIS_"."EVENT_DATE">=:1 AND "THIS_"."EVENT_DATE"<=:2))
9 - access("THIS_"."OBJECT_TYPE_ID"="dieudoO3_"."OBJECT_TYPE_ID")
11 - access("THIS_"."EVENT_TYPE_ID"="dieudoE2_"."EVENT_TYPE_ID")

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "from$_subquery$_001"."dieudo1_3_2_"[VARCHAR2,128],
"from$_subquery$_001"."VERSION3_2_"[NUMBER,22], "from$_subquery$_001"."BA
TCH3_3_2_"[VARCHAR2,1020]

, "from$_subquery$_001"."EVENT4_3_2_"[TIMESTAMP,11],
"from$_subquery$_001"."EVENT5_3_2_"[NUMBER,22], "from$_subquery$_001"."OB
JECT6_3_2_"[VARCHAR2,128]

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
, "from$_subquery$_001"."OBJECT7_3_2_"[NUMBER,22],
"from$_subquery$_001"."EVENT1_4_0_"[NUMBER,22], "from$_subquery$_001"."NA
ME4_0_"[VARCHAR2,1020],

"from$_subquery$_001"."OBJECT1_5_1_"[NUMBER,22], "from$_subquery$_001"."N
AME5_1_"[VARCHAR2,1020]

2 - "from$_subquery$_001"."dieudo1_3_2_"[VARCHAR2,128],
"from$_subquery$_001"."VERSION3_2_"[NUMBER,22], "from$_subquery$_001"."BA
TCH3_3_2_"[VARCHAR2,1020]

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
, "from$_subquery$_001"."EVENT4_3_2_"[TIMESTAMP,11],
"from$_subquery$_001"."EVENT5_3_2_"[NUMBER,22], "from$_subquery$_001"."OB
JECT6_3_2_"[VARCHAR2,128]

, "from$_subquery$_001"."OBJECT7_3_2_"[NUMBER,22],
"from$_subquery$_001"."EVENT1_4_0_"[NUMBER,22], "from$_subquery$_001"."NA
ME4_0_"[VARCHAR2,1020],

"from$_subquery$_001"."OBJECT1_5_1_"[NUMBER,22], "from$_subquery$_001"."N
AME5_1_"[VARCHAR2,1020]

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
3 - "THIS_"."dieudo_EVENT_ID"[VARCHAR2,128], "THIS_"."VERSION"[NUMBER,22],

"THIS_"."BATCH_ID"[VARCHAR2,1020], "THIS_"."EVENT_DATE"[TIMESTAMP,11],
"THIS_"."EVENT_TYPE_ID"[NUMBER,22], "THIS_"."OBJECT_ID"[VARCHAR2,128],
"THIS_"."OBJECT_TYPE_ID"[NUMBER,22], "dieudoO3_"."OBJECT_TYPE_ID"[NUMB
ER,22],

"dieudoO3_"."NAME"[VARCHAR2,1020], "dieudoE2_"."EVENT_TYPE_ID"[NUMB
ER,22],

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
"dieudoE2_"."NAME"[VARCHAR2,1020]
4 - "THIS_"."dieudo_EVENT_ID"[VARCHAR2,128], "THIS_"."VERSION"[NUMBER,22],

"THIS_"."BATCH_ID"[VARCHAR2,1020], "THIS_"."EVENT_DATE"[TIMESTAMP,11],
"THIS_"."EVENT_TYPE_ID"[NUMBER,22], "THIS_"."OBJECT_ID"[VARCHAR2,128],
"THIS_"."OBJECT_TYPE_ID"[NUMBER,22], "dieudoO3_"."OBJECT_TYPE_ID"[NUMB
ER,22],

"dieudoO3_"."NAME"[VARCHAR2,1020], "dieudoE2_"."EVENT_TYPE_ID"[NUMB
ER,22],

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

"dieudoE2_"."NAME"[VARCHAR2,1020]
5 - "THIS_"."dieudo_EVENT_ID"[VARCHAR2,128], "THIS_"."VERSION"[NUMBER,22],

"THIS_"."BATCH_ID"[VARCHAR2,1020], "THIS_"."EVENT_DATE"[TIMESTAMP,11],
"THIS_"."EVENT_TYPE_ID"[NUMBER,22], "THIS_"."OBJECT_ID"[VARCHAR2,128],
"THIS_"."OBJECT_TYPE_ID"[NUMBER,22], "dieudoO3_"."OBJECT_TYPE_ID"[NUMB
ER,22],

"dieudoO3_"."NAME"[VARCHAR2,1020]

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
6 - "THIS_"."dieudo_EVENT_ID"[VARCHAR2,128], "THIS_"."VERSION"[NUMBER,22],

"THIS_"."BATCH_ID"[VARCHAR2,1020], "THIS_"."EVENT_DATE"[TIMESTAMP,11],
"THIS_"."EVENT_TYPE_ID"[NUMBER,22], "THIS_"."OBJECT_ID"[VARCHAR2,128],
"THIS_"."OBJECT_TYPE_ID"[NUMBER,22]
7 - "THIS_".ROWID[ROWID,10], "THIS_"."dieudo_EVENT_ID"[VARCHAR2,128]
8 - "dieudoO3_"."OBJECT_TYPE_ID"[NUMBER,22], "dieudoO3_"."NAME"[VARCHAR
2,1020]

9 - "dieudoO3_".ROWID[ROWID,10], "dieudoO3_"."OBJECT_TYPE_ID"[NUMBER,22

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
]

10 - "dieudoE2_"."EVENT_TYPE_ID"[NUMBER,22], "dieudoE2_"."NAME"[VARCHAR2
,1020]

11 - "dieudoE2_".ROWID[ROWID,10], "dieudoE2_"."EVENT_TYPE_ID"[NUMBER,22]

117 ligne(s) sélectionnée(s).

Vu le plan d'execution, comment puis-je faire pour l'optimiser ? que ce qui pose problème ? y a t-il un autre moyen pour le reécrire ?

Merci de votre analyse.

Vos noms tordus de tables, colonnes, et même d'alias rendent la lecture de la requête assez imbitable. Passons...

La requête que vous avez postée ne dois pas pouvoir fonctionner telle quelle : il n'y a rien derrière le WHERE, et un AND est précédé d'une virgule.

Sinon, la première chose qui interpelle est que la table "dieudo_event_type" n'est jointe à aucune autre, ce qui engendre automatiquement un produit cartésien, et donc à coup sûr déteriore les performances.

C'est difficile d'optimiser une requête incorrecte. Corrigez-la d'abord.
Code:

1 2 3 4 5 ... WHERE , AND ...

Autant pour moi, voici la requête:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
select * from ( 
select this_.dieudo_event_id as dieudo1_3_2_, this_.version as version3_2_,
this_.batch_id as batch3_3_2_, this_.event_date as event4_3_2_, 
this_.event_type_id as event5_3_2_, this_.object_id as object6_3_2_, this_.object_type_id as object7_3_2_,
dieudoe2_.event_type_id as event1_4_0_, dieudoe2_.name as name4_0_,
dieudoo3_.object_type_id as object1_5_1_, dieudoo3_.name as name5_1_ 
from
dieudo_event this_, dieudo_event_type dieudoe2_, dieudo_object_type dieudoo3_ 
where this_.event_type_id=dieudoe2_.event_type_id and this_.object_type_id=dieudoo3_.object_type_id 
and this_.event_date>='02/10/09 02:17:17,655000' and this_.event_date<='02/10/09 02:17:21,328000' 
order by this_.dieudo_event_id asc ) 
where rownum <= 3
/

La bonne d'origine avec les varibales liées:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
select * from ( 
select this_.dieudo_event_id as dieudo1_3_2_, this_.version as version3_2_,
this_.batch_id as batch3_3_2_, this_.event_date as event4_3_2_, 
this_.event_type_id as event5_3_2_, this_.object_id as object6_3_2_, this_.object_type_id as object7_3_2_,
dieudoe2_.event_type_id as event1_4_0_, dieudoe2_.name as name4_0_,
dieudoo3_.object_type_id as object1_5_1_, dieudoo3_.name as name5_1_ 
from
dieudo_event this_, dieudo_event_type dieudoe2_, dieudo_object_type dieudoo3_ 
where this_.event_type_id=dieudoe2_.event_type_id and this_.object_type_id=dieudoo3_.object_type_id 
and this_.event_date>=:1 and this_.event_date<=:2 
order by this_.dieudo_event_id asc ) 
where rownum <= :3
/

Que voulez-vous optimiser ?
Le plan d'exécution, si j'ai bien réussi à le lire, m'a l'air efficace.

Sinon comme le faisait remarquer dgi77, un peu de mise en page et des alias plus court ne font pas de mal, mais les performances ne seront pas impactées :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
WITH SR AS
( 
  SELECT ev.dieudo_event_id AS dieudo1_3_2_,
         ev.version         AS version3_2_,
         ev.batch_id        AS batch3_3_2_,
         ev.event_date      AS event4_3_2_, 
         ev.event_type_id   AS event5_3_2_,
         ev.object_id       AS object6_3_2_,
         ev.object_type_id  AS object7_3_2_,
         et.event_type_id   AS event1_4_0_,
         et.name            AS name4_0_,
         ot.object_type_id  AS object1_5_1_,
         ot.name            AS name5_1_ 
    FROM dieudo_event ev
         INNER JOIN dieudo_event_type et
           ON et.event_type_id = ev.event_type_id
         INNER JOIN dieudo_object_type ot
           ON ot.object_type_id = ev.object_type_id
   WHERE ev.event_date between :1 AND :2 
ORDER BY ev.dieudo_event_id ASC
) 
SELECT *
  FROM SR
 WHERE rownum <= :3;

Est-ce que les cardinalités du plan d'exécution correspondent à la réalité ?
C'est quoi l'index SYS_C0044096? Le full scan n'indique pas un optimum.
| 7 | INDEX FULL SCAN | SYS_C0044096 | 4000 |

Et une simple exécution de la requête avec auto trace devrait montrer les statistiques concernant le IO qui vous enquête.