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]
Partager