Oracle - Plan d'exécution estimatif

Oracle | Plan d'exécution estimatif

Notes
Introduction
La commande EXPLAIN PLAN
La commande AUTOTRACE
La table PLAN_TABLE
1. Structure de la table PLAN_TABLE
2. Afficher le plan d'exécution estimatif à partir de la table PLAN_TABLE

Notes
This table (and its data) is used in the examples bellow:
CREATE TABLE table_explain_plan_1 ( id1 NUMBER(5,0) NOT NULL , id2 NUMBER(5,0) NOT NULL , type1 NUMBER(1,0) NOT NULL );
DECLARE indexLoop NUMBER(5,0); BEGIN FOR indexLoop IN 1..50000 LOOP IF MOD(indexLoop,2) = 0 THEN INSERT INTO table_explain_plan_1 (id1, id2, type1) VALUES (indexLoop, DBMS_RANDOM.VALUE(1,50000), 1); ELSE INSERT INTO table_explain_plan_1 (id1, id2, type1) VALUES (indexLoop, DBMS_RANDOM.VALUE(1,50000), 2); END IF; END LOOP; END; / COMMIT;
CREATE INDEX ix_table_explain_plan_1_type1 ON table_explain_plan_1 (type1); ANALYZE INDEX ix_table_explain_plan_1_type1 COMPUTE STATISTICS;
Introduction
Le plan d'exécution estimatif d'une requête donne une idée globale sur le vrai plan d'exécution qui sera utilisé par Oracle lorsque la requête sera exécutée. Le plan d'exécution estimatif prend en considération les statistiques disponibles au moment de son génération. Le plan d'exécution estimatif peut être utilisé pour déboguer une requête qui prend beaucoup de temps à s'exécuter.
La commande EXPLAIN PLAN
La commande EXPLAIN PLAN permet de générer un plan d'exécution estimatif.

Voici un exemple de son utilisation :
EXPLAIN PLAN FOR SELECT * FROM table_explain_plan_1 WHERE type1 = 1;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Plan hash value: 3358088968 ------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 24721 | 941K| 50 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TABLE_EXPLAIN_PLAN_1 | 24721 | 941K| 50 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IX_TABLE_EXPLAIN_PLAN_1_TYPE1 | 208 | | 49 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("TYPE1"=1)
La commande AUTOTRACE
La commande AUTOTRACE permet de générer un plan d'exécution estimatif dans un programme où cette commande est supportée (par exemple: sqlplus). La différence avec la commande EXPLAIN PLAN, et qu'avec la commande AUTOTRACE on n'a pas besoin d'exécuter une deuxième requête pour sélectionner le plan d'exécution.

Voici un exemple de son utilisation :
SET AUTOTRACE TRACEONLY EXPLAIN SELECT * FROM table_explain_plan_1 WHERE type1 = 1 /
Plan hash value: 3358088968 ------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 24721 | 941K| 50 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TABLE_EXPLAIN_PLAN_1 | 24721 | 941K| 50 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IX_TABLE_EXPLAIN_PLAN_1_TYPE1 | 208 | | 49 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("TYPE1"=1)
La table PLAN_TABLE
1. Structure de la table PLAN_TABLE
  Pour les deux commandes, le plan d'exécution estimatif est sauvegardé dans la table PLAN_TABLE.
  En réalité, PLAN_TABLE est actuellement est un SYNONYM qui pointe vers la table PLAN_TABLE$.
  SELECT object_type, owner FROM dba_objects WHERE object_name = 'PLAN_TABLE';
  Résultat de la requête : | OBJECT_TYPE | OWNER | +--------------+--------+ | SYNONYM | PUBLIC |
  SELECT table_name, table_owner, owner FROM dba_synonyms WHERE synonym_name = 'PLAN_TABLE';
  Résultat de la requête : | table_name | table_owner | owner | +-------------+-------------+--------| | PLAN_TABLE$ | SYS | PUBLIC |
  Autre détail à connaitre est que la table PLAN_TABLE$ est une table temporaire Oracle.
  SELECT temporary, duration FROM dba_tables WHERE table_name = 'PLAN_TABLE$';
  Résultat de la requête : | temporary | duration | +-----------+-------------+ | Y | SYS$SESSION |
  Le script complet de la création de la table peut être retrouvé dans le fichier "catplan.sql" (qui se trouve dans le dossier "$ORACLE_HOME\rdbms\admin\") create global temporary table plan_table$ ( statement_id varchar2(30), plan_id number, timestamp date, remarks varchar2(4000), operation varchar2(30), options varchar2(255), object_node varchar2(128), object_owner varchar2(30), object_name varchar2(30), object_alias varchar2(65), object_instance numeric, object_type varchar2(30), optimizer varchar2(255), search_columns number, id numeric, parent_id numeric, depth numeric, position numeric, cost numeric, cardinality numeric, bytes numeric, other_tag varchar2(255), partition_start varchar2(255), partition_stop varchar2(255), partition_id numeric, other long, other_xml clob, distribution varchar2(30), cpu_cost numeric, io_cost numeric, temp_space numeric, access_predicates varchar2(4000), filter_predicates varchar2(4000), projection varchar2(4000), time numeric, qblock_name varchar2(30) ) on commit preserve rows /
  NOTE :
  $ORACLE_HOME fait référence au chemin où les produits Oracle sont installés.
  Sur Windows, cette valeur est sauvegardée dans la base de registre.
  Dans mon cas, avec Windows 7 et Oracle 11.2 :
  - le chenin dans la base de registre est : HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\OracleMTSRecoveryService\Setup\Current Version.
  - la clé est : Home.
  - la valeur est : C:\oracle_11_2\app\oracle\product\11.2.0\server.
2. Afficher le plan d'exécution estimatif à partir de la table PLAN_TABLE
  EXPLAIN PLAN SET STATEMENT_ID = 'table_explain_plan_1_1' FOR SELECT * FROM table_explain_plan_1 WHERE type1 = 1;
  SELECT id , parent_id , cardinality AS "ROWS" , lpad(' ',level-1)||operation||' '||options||' '||object_name AS "STEP" FROM PLAN_TABLE CONNECT BY prior id = parent_id AND prior statement_id = statement_id START WITH id = 0 AND statement_id = 'table_explain_plan_1_1' ORDER BY id;
  Résultat de la requête : | id | parent_id | ROWS | STEP | +----+-----------+-------+---------------------------------------------------| | 0 | | 24721 | SELECT STATEMENT | | 1 | 0 | 24721 | TABLE ACCESS BY INDEX ROWID table_explain_plan_1 | | 2 | 1 | 208 | INDEX RANGE SCAN ix_table_explain_plan_1_type1 |