Comment interpreter les recommendations d'oracle Tunning

Bonjour;

je souhaite interpreter la solution que propose l'outil Oracle Tunning Advisor sur une requete consommatrice de temps.
Le rapport ADDM a identifié la requête suivante :

Citation:

RECOMMENDATION 1: SQL Tuning, 100% benefit (6487 seconds)
ACTION: Examinez la possibilité d'améliorer les performances de
l'instruction SQL où SQL_ID = "8rv158cfpqfda".
RELEVANT OBJECT: SQL statement with SQL_ID 8rv158cfpqfda and
PLAN_HASH 1772850126
SELECT
CONCAT(CONCAT(CONCAT(CONCAT(TO_CHAR(( Date_INDIC_TRIER.VA_SEMAINE
),'00'),'/'),( Date_INDIC_TRIER.VA_ANNEE )),' - Date début de semaine
: '),TO_CHAR(( Date_INDIC_TRIER.DT_DEBUT_SEMAINE ),'dd/mm/YYYY'))
FROM
( SELECT DISTINCT QS_INDIC_TOURNEE.VA_ANNEE ,
QS_INDIC_TOURNEE.VA_SEMAINE , QS_INDIC_TOURNEE.DT_DEBUT_SEMAINE
FROM
QS_INDIC_TOURNEE
ORDER BY
QS_INDIC_TOURNEE.VA_ANNEE , QS_INDIC_TOURNEE.VA_SEMAINE )
Date_INDIC_TRIER
WHERE
(
( ( Date_INDIC_TRIER.VA_ANNEE ) > TO_NUMBER(TO_CHAR(EXTRACT(YEAR
FROM SYSDATE) - 2 , '0000'))
OR
(( Date_INDIC_TRIER.VA_SEMAINE ) > TO_NUMBER(TO_CHAR(SYSDATE, 'IW'))
AND
( Date_INDIC_TRIER.VA_ANNEE ) = TO_NUMBER(TO_CHAR(EXTRACT(YEAR FROM
SYSDATE) - 2 , '0000'))) )
)

RATIONALE: L'instruction SQL où SQL_ID = "8rv158cfpqfda" a été exécutée
1458 fois, avec un temps écoulé moyen de 0.76 secondes.

L'outil Tunning Advisor propose la soultion suivante :

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
Un meilleur plan d'exécution éventuel a été trouvé pour cette instruction.

Recommendation (estimated benefit: 72,14%)
------------------------------------------
- Envisagez d'accepter le profil SQL recommandé.
execute dbms_sqltune.accept_sql_profile(task_name => '8rv158cfpqfda',
replace => TRUE);

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 1772850126

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18 | 630 | 325 (7)| 00:00:04 |
| 1 | VIEW | | 18 | 630 | 325 (7)| 00:00:04 |
| 2 | SORT UNIQUE | | 18 | 270 | 323 (7)| 00:00:04 |
|* 3 | TABLE ACCESS FULL| QS_INDIC_TOURNEE | 10396 | 152K| 322 (6)| 00:00:04 |
----------------------------------------------------------------------------------------

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

1 - SEL$2 / DATE_INDIC_TRIER@SEL$1
2 - SEL$2
3 - SEL$2 / QS_INDIC_TOURNEE@SEL$2

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

3 - filter("QS_INDIC_TOURNEE"."VA_ANNEE">TO_NUMBER(TO_CHAR(EXTRACT(YEAR FROM
SYSDATE@!)-2,'0000')) OR "QS_INDIC_TOURNEE"."VA_SEMAINE">TO_NUMBER(TO_CHAR(SYSDA
TE@!,'IW')) AND "QS_INDIC_TOURNEE"."VA_ANNEE"=TO_NUMBER(TO_CHAR(EXTRACT(YEAR
FROM SYSDATE@!)-2,'0000')))

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

1 - "DATE_INDIC_TRIER"."VA_ANNEE"[NUMBER,22],
"DATE_INDIC_TRIER"."VA_SEMAINE"[NUMBER,22],
"DATE_INDIC_TRIER"."DT_DEBUT_SEMAINE"[DATE,7]
2 - (#keys=3) "QS_INDIC_TOURNEE"."VA_ANNEE"[NUMBER,22],
"QS_INDIC_TOURNEE"."VA_SEMAINE"[NUMBER,22],
"QS_INDIC_TOURNEE"."DT_DEBUT_SEMAINE"[DATE,7]
3 - "QS_INDIC_TOURNEE"."VA_SEMAINE"[NUMBER,22],
"QS_INDIC_TOURNEE"."VA_ANNEE"[NUMBER,22],
"QS_INDIC_TOURNEE"."DT_DEBUT_SEMAINE"[DATE,7]

2- Original With Adjusted Cost
------------------------------
Plan hash value: 1772850126

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18 | 630 | 1233 (4)| 00:00:15 |
| 1 | VIEW | | 18 | 630 | 1233 (4)| 00:00:15 |
| 2 | SORT UNIQUE | | 18 | 270 | 1222 (3)| 00:00:15 |
|* 3 | TABLE ACCESS FULL| QS_INDIC_TOURNEE | 174K| 2550K| 322 (6)| 00:00:04 |
----------------------------------------------------------------------------------------

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

1 - SEL$2 / DATE_INDIC_TRIER@SEL$1
2 - SEL$2
3 - SEL$2 / QS_INDIC_TOURNEE@SEL$2

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

3 - filter("QS_INDIC_TOURNEE"."VA_ANNEE">TO_NUMBER(TO_CHAR(EXTRACT(YEAR FROM
SYSDATE@!)-2,'0000')) OR "QS_INDIC_TOURNEE"."VA_SEMAINE">TO_NUMBER(TO_CHAR(SYSDA
TE@!,'IW')) AND "QS_INDIC_TOURNEE"."VA_ANNEE"=TO_NUMBER(TO_CHAR(EXTRACT(YEAR
FROM SYSDATE@!)-2,'0000')))

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

1 - "DATE_INDIC_TRIER"."VA_ANNEE"[NUMBER,22],
"DATE_INDIC_TRIER"."VA_SEMAINE"[NUMBER,22],
"DATE_INDIC_TRIER"."DT_DEBUT_SEMAINE"[DATE,7]
2 - (#keys=3) "QS_INDIC_TOURNEE"."VA_ANNEE"[NUMBER,22],
"QS_INDIC_TOURNEE"."VA_SEMAINE"[NUMBER,22],
"QS_INDIC_TOURNEE"."DT_DEBUT_SEMAINE"[DATE,7]
3 - "QS_INDIC_TOURNEE"."VA_SEMAINE"[NUMBER,22],
"QS_INDIC_TOURNEE"."VA_ANNEE"[NUMBER,22],
"QS_INDIC_TOURNEE"."DT_DEBUT_SEMAINE"[DATE,7]

3- Using SQL Profile
--------------------
Plan hash value: 1495258575

-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18 | 630 | 344 (11)| 00:00:05 |
| 1 | VIEW | | 18 | 630 | 344 (11)| 00:00:05 |
| 2 | SORT UNIQUE | | 18 | 270 | 332 (8)| 00:00:04 |
| 3 | CONCATENATION | | | | | |
|* 4 | TABLE ACCESS BY INDEX ROWID| QS_INDIC_TOURNEE | 33345 | 488K| 4 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IXT_INDIC_TOURNEE_01 | 1 | | 3 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | QS_INDIC_TOURNEE | 140K| 2062K| 317 (5)| 00:00:04 |
-------------------------------------------------------------------------------------------------------

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

1 - SEL$2 / DATE_INDIC_TRIER@SEL$1
2 - SEL$2
4 - SEL$2_1 / QS_INDIC_TOURNEE@SEL$2
5 - SEL$2_1 / QS_INDIC_TOURNEE@SEL$2
6 - SEL$2_2 / QS_INDIC_TOURNEE@SEL$2_2

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

4 - filter("QS_INDIC_TOURNEE"."VA_ANNEE"=TO_NUMBER(TO_CHAR(EXTRACT(YEAR FROM
SYSDATE@!)-2,'0000')))
5 - access("QS_INDIC_TOURNEE"."VA_SEMAINE">TO_NUMBER(TO_CHAR(SYSDATE@!,'IW')))
6 - filter("QS_INDIC_TOURNEE"."VA_ANNEE">TO_NUMBER(TO_CHAR(EXTRACT(YEAR FROM
SYSDATE@!)-2,'0000')) AND (LNNVL("QS_INDIC_TOURNEE"."VA_SEMAINE">TO_NUMBER(TO_CHAR(SYSDATE@!,'I
W'))) OR LNNVL("QS_INDIC_TOURNEE"."VA_ANNEE"=TO_NUMBER(TO_CHAR(EXTRACT(YEAR FROM
SYSDATE@!)-2,'0000')))))

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

1 - "DATE_INDIC_TRIER"."VA_ANNEE"[NUMBER,22], "DATE_INDIC_TRIER"."VA_SEMAINE"[NUMBER,22],
"DATE_INDIC_TRIER"."DT_DEBUT_SEMAINE"[DATE,7]
2 - (#keys=3) "QS_INDIC_TOURNEE"."VA_ANNEE"[NUMBER,22],
"QS_INDIC_TOURNEE"."VA_SEMAINE"[NUMBER,22], "QS_INDIC_TOURNEE"."DT_DEBUT_SEMAINE"[DATE,7]
3 - "QS_INDIC_TOURNEE".ROWID[ROWID,10], "QS_INDIC_TOURNEE"."VA_SEMAINE"[NUMBER,22],
"QS_INDIC_TOURNEE"."VA_ANNEE"[NUMBER,22], "QS_INDIC_TOURNEE"."DT_DEBUT_SEMAINE"[DATE,7]
4 - "QS_INDIC_TOURNEE".ROWID[ROWID,10], "QS_INDIC_TOURNEE"."VA_SEMAINE"[NUMBER,22],
"QS_INDIC_TOURNEE"."VA_ANNEE"[NUMBER,22], "QS_INDIC_TOURNEE"."DT_DEBUT_SEMAINE"[DATE,7]
5 - "QS_INDIC_TOURNEE".ROWID[ROWID,10], "QS_INDIC_TOURNEE"."VA_SEMAINE"[NUMBER,22]
6 - "QS_INDIC_TOURNEE".ROWID[ROWID,10], "QS_INDIC_TOURNEE"."VA_SEMAINE"[NUMBER,22],
"QS_INDIC_TOURNEE"."VA_ANNEE"[NUMBER,22], "QS_INDIC_TOURNEE"."DT_DEBUT_SEMAINE"[DATE,7]

Merci

Lisez Subquery with OR .
Je n'aime pas trop l'order by dans la sous-interogation.

Citation:

Envoyé par mnitu

Lisez Subquery with OR .
Je n'aime pas trop l'order by dans la sous-interogation.

Salut;

Que proposes tu à la place ? Tu penses que ca a impact sur les perf ?

En te remerciant

Pour l’instant je vous propose de ré écrire la requête avec « union all » à la place de « or » et de vérifier les plan d’exécution.

Bonjour,

Citation:

RATIONALE: L'instruction SQL où SQL_ID = "8rv158cfpqfda" a été exécutée
1458 fois, avec un temps écoulé moyen de 0.76 secondes.

C'est pas bien moins d'une seconde pour cette requête ?
Par contre, pourquoi est-elle exécutée 1458 ? en combien de temps ?

Citation:

Recommendation (estimated benefit: 72,14%)

Ce sera tellement mieux 0.76 * 0.7214 = 0.548264 secondes au lieu de 0.76 ?

Je ne suis pas sur que ce rapport soit très utile :?

Cordialement,
Franck.

Citation:

Envoyé par pachot

Bonjour,

C'est pas bien moins d'une seconde pour cette requête ?
Par contre, pourquoi est-elle exécutée 1458 ? en combien de temps ?

Ce sera tellement mieux 0.76 * 0.7214 = 0.548264 secondes au lieu de 0.76 ?

Je ne suis pas sur que ce rapport soit très utile :?

Cordialement,
Franck.

Bonjour Franck;

Ok par contre je n'arrive pas à optimiser ma requete ! En gros je ne vois pas qu'est ce qui pose problème sur cette requete :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 SELECT CONCAT(CONCAT(CONCAT(CONCAT(TO_CHAR(( Date_INDIC_TRIER.VA_SEMAINE ),'00'),'/'),( Date_INDIC_TRIER.VA_ANNEE )),' - Date début de semaine : '),TO_CHAR(( Date_INDIC_TRIER.DT_DEBUT_SEMAINE ),'dd/mm/YYYY')) FROM ( SELECT DISTINCT QS_INDIC_TOURNEE.VA_ANNEE , QS_INDIC_TOURNEE.VA_SEMAINE , QS_INDIC_TOURNEE.DT_DEBUT_SEMAINE FROM QS_INDIC_TOURNEE ORDER BY QS_INDIC_TOURNEE.VA_ANNEE , QS_INDIC_TOURNEE.VA_SEMAINE ) Date_INDIC_TRIER WHERE ( ( ( Date_INDIC_TRIER.VA_ANNEE ) > TO_NUMBER(TO_CHAR(EXTRACT(YEAR FROM SYSDATE) - 2 , '0000')) OR (( Date_INDIC_TRIER.VA_SEMAINE ) > TO_NUMBER(TO_CHAR(SYSDATE, 'IW')) AND ( Date_INDIC_TRIER.VA_ANNEE ) = TO_NUMBER(TO_CHAR(EXTRACT(YEAR FROM SYSDATE) - 2 , '0000'))) ) )
Merci

Citation:

je ne vois pas qu'est ce qui pose problème

Peut-être rien. Tu pense avoir un pb de performance ?

Citation:

Envoyé par mnitu

Pour l’instant je vous propose de ré écrire la requête avec « union all » à la place de « or » et de vérifier les plan d’exécution.

Bonjour;

J'ai remplacé le "OR" par un "UNION ALL" mais pas de gain de performance ?

A+;

Combien de lignes retourne votre requête sur combien de lignes dans la table QS_INDIC_TOURNEE ? La clause ORDER BY dans la subquery est-elle vraiment utile ?

Concernant l'article de Jonathan Lewis pointé par MNITU, il s'agit plus d'une astuce pour éviter d'avoir des FILTER clause pour les semi-jointures (clauses EXISTS/IN). En effet, lorsqu'on a une clause OR dans une subquery le CBO est incapable d'unnester la sous-requête pendant la phase de "query transformation" du parsing de la requête et donc un filter subquery est effectué à la place d'un NESTED LOOP SEMI (ou HASH JOIN SEMI). Selon le nombre de lignes processées dans la table directrice du FILTER (car un FTS est effectué sur cette table) les performances de la requête peuvent être catastrophique.

Toutefois, concernant la requête de Yanis et selon le nombre de lignes retournées, il pourrait être effectivement intéressant de tester un UNION ALL au lieu d'avoir un OR dans la clause WHERE.

A ce que je comprends l’optimiseur estime que la requête en question va ramène 10 396 lignes depuis la table QS_INDIC_TOURNEE mais en réalité elle ramène 174 000.
Donc il vous propose un autre plan où une partie des données provient d’un accès full de la table et une autre à partir d’un index.
Vous allez gagnez en gros 0.2 * 1458 secondes. Pachot a tout à fait raison de demander si ça vaut la peine.

Mauvaise gestion des dates.
Peut-on connaître la structure de la table qs_indic_tournee ?

Et avoir un petit extrait sur la semaine en cours par exemple ?