Tuning oracle

Pour voir le plan d'exécution d'une requête on utilise la commande :

SQL> EXPLAIN PLAN FOR
2 SELECT *
3 FROM hr.employees JOIN hr.departments USING (department_id);


Explained.


Pour voir le plan vous pouvez utiliser la requête suivante:

SELECT RTRIM (LPAD (' ', 2 * LEVEL) ||
RTRIM (operation) || ' ' ||
RTRIM (options) || ' ' ||
object_name) query_plan,
cost,
cardinality
FROM plan_table
CONNECT BY PRIOR id = parent_id
START WITH id = 0


SQL>
SQL> SELECT RTRIM (LPAD (' ', 2 * LEVEL) ||
2 RTRIM (operation) || ' ' ||
3 RTRIM (options) || ' ' ||
4 object_name) query_plan,
5 cost, cardinality
6 FROM plan_table
7 CONNECT BY PRIOR id = parent_id
8 START WITH id = 0 ;

Le résultat de la requête est le suivant :


QUERY_PLAN COST CARDINALITY
---------------------------------------------- ------ -----------
SELECT STATEMENT 4 106
    NESTED LOOPS 4 106
       TABLE ACCESS FULL EMPLOYEES 3 107
       TABLE ACCESS BY INDEX ROWID DEPARTMENTS 1 1
           INDEX UNIQUE SCAN DEPT_ID_PK 0 1



Une autre solution plus conviviale est l'utilisation de dbms_xplan

SQL> SELECT * FROM TABLE(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 4201152682
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 9328 | 4 (0)|
| 1 |  NESTED LOOPS | | 106 | 9328 | 4 (0)|
| 2 |     TABLE ACCESS FULL |EMPLOYEES | 107 | 7276 | 3 (0)|
| 3 |     TABLE ACCESS BY INDEX ROWID|DEPARTMENTS | 1 | 20 | 1 (0)|
|* 4 |              INDEX UNIQUE SCAN |DEPT_ID_PK | 1 | | 0 (0)|
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMPLOYEES"."DEPARTMENT_ID"="DEPARTMENTS"."DEPARTMENT_ID").

• V$SQL_PLAN contient les plans d’exécution des requêtes sql actuellement dans le cache.
• V$SQL_PLAN_STATISTICS on peut voir le nombre d'execution les I/O et les lignes traitées par chaque étape du plan d'exécution.

on peut requêter la vue V$SQL pour voir les requêtes qui consommatrices de CPU ou IO ou avec un temps d’exécution long.

par exemple on peut avoir le top 10 des requêtes les plus consommatrices :

SQL> SELECT sql_id,child_number,sql_text, elapsed_time

       2 FROM (SELECT sql_id, child_number, sql_text, elapsed_time,

       3 cpu_time,disk_reads,

       4 RANK () OVER (ORDER BY elapsed_time DESC) AS elapsed_rank

       5 FROM v$sql)

       6 WHERE elapsed_rank <= 10

       7 /