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 : Sélectionner tout - Visualiser dans une fenêtre à part
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:
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.