Comment afficher le plan d'exécution Oracle

Qu'est-ce qu'un plan d'exécution ?

SQL est un langage idiot. Chaque condition est une exigence. Différents ordres d'accès forment différents plans d'exécution. Oracle doit faire un choix et ne peut disposer que d'un seul chemin d'accès à la fois. Le plan d'exécution est une description du processus d'exécution ou du chemin d'accès d'une instruction de requête dans Oracle.

Sélection du plan d'exécution :

Habituellement, un SQL a plusieurs plans d'exécution, alors comment choisir ? Celui avec une surcharge d'exécution inférieure signifie de meilleures performances et une vitesse plus rapide. Nous choisirons lequel est appelé processus d'analyse d'Oracle, puis Oracle mettra le meilleur plan d'exécution dans le pool partagé de SGA pour exécuter le même SQL plus tard. il vous suffit de l'obtenir à partir du pool partagé et il n'est pas nécessaire de l'analyser à nouveau.

Base de sélection du plan d'exécution :

Sélectionnez un plan d'exécution basé sur des informations statistiques.

Informations statistiques :

Qu'est-ce que les informations statistiques : nombre d'enregistrements, nombre de blocs, etc., voir dba_tables/dba_indexes pour plus de détails

Dynamique échantillonnage :

Oracle collecte normalement des informations statistiques à une certaine heure chaque jour. Comment Oracle collecte-t-il des informations statistiques pour les tables nouvellement créées ? Utilisez l'échantillonnage dynamique.

définir la trace automatique sur
définir la taille de ligne 1000
--exécuter l'instruction SQL
--l'échantillonnage dynamique utilisé pour cette clé d'instruction (niveau=2)

Six plans d'exécution

Oracle propose 6 méthodes d'acquisition de plans d'exécution, chacune avec une emphase différente :

Suivez généralement les règles suivantes lors de la sélection :

1. Si l'exécution de SQL prend beaucoup de temps pour produire des résultats ou ne peut pas renvoyer de résultats, utilisez la méthode 1 : expliquer le plan pour

2 Le moyen le plus simple de suivre un certain SQL est la méthode 1 : expliquer le plan pour. , suivi de la méthode 2 : définir l'autotrace sur

3 Si vous souhaitez vérifier plusieurs plans d'exécution d'un certain SQL, vous ne pouvez utiliser que la méthode 4 : dbms_xplan.display_cursor ou la méthode 6 : awrsqlrpt.sql

.

4. Si le SQL contient une fonction et que la fonction contient du SQL, c'est-à-dire qu'il existe plusieurs couches d'appels. Si vous souhaitez analyser avec précision, vous ne pouvez utiliser que la méthode 5 : 10046 suivi

5. . Idée pour voir le plan d'exécution réel, vous ne pouvez pas utiliser la méthode 1 : expliquer le plan et la méthode 2 : définir l'autotrace sur

6. Si vous souhaitez obtenir le nombre de fois où la table a été consultée, vous pouvez. utilisez uniquement la méthode 3 : Statistics_level = all

Comment Oracle collecte les statistiques :

1. Oracle choisira de collecter la table et l'index. statistiques sur une période spécifique (par défaut du lundi au vendredi : 22h00, samedi et dimanche : 06h00), les utilisateurs peuvent s'ajuster eux-mêmes, principalement pour éviter les périodes de pointe

2. Les index ont un seuil et l'analyse sera automatiquement effectuée uniquement lorsque le seuil est dépassé. Si les modifications des données ne sont pas importantes, Oracle ne les analysera pas

3. La méthode de collecte est flexible ; Cela peut être fait pour une certaine partition de la table de partition, et un mécanisme parallèle peut être utilisé pour collecter des informations sur la table et l'index

Comment collecter des informations statistiques :

--Collecter les statistiques de la table ; informations

exec dbms_stats.gather_table_stats(ownname => 'AAA', tabname => 'TEST02',estimate_percent =>
10,method_opt => 'for all indexed columns');

--Collecter les statistiques d'index

exec dbms_stats.gather_index_stats(ownname => 'AAA',indname => 'ID_IDX',estimate_percent =>
10,degree => '4');

--Collecter les statistiques de table et d'index

exec dbms_stats.gather_table_stats(ownname => 'AAA',tabname => 'TEST02',estimate_percent =>
10,method_opt => 'for all indexed columns',cascade => true);

(1) expliquer le plan pour

SQL> show user
     USER 为 "HR"
SQL> set linesize 1000
SQL> set pagesize 2000
SQL> explain plan for
2 select *
3 from employees,jobs
4 where employees.job_id=jobs.job_id
5 and employees.department_id=50;
已解释。
 
SQL> select * from table(dbms_xplan.display());
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------
Plan hash value: 303035560
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 45 | 4590 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 45 | 4590 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| JOBS | 19 | 627 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | JOB_ID_PK | 19 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 45 | 3105 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMPLOYEES | 45 | 3105 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")
filter("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")
5 - filter("EMPLOYEES"."DEPARTMENT_ID"=50)
已选择19行。

Avantages : Non besoin d'exécuter réellement, rapide et pratique ;

Inconvénients :

1. Aucune information statistique pertinente n'est générée, telle que le nombre de lectures logiques, le nombre de lectures physiques et le nombre d'appels récursifs. généré;

2. Il est impossible de juger combien de lignes ont été traitées

3. Il est impossible de juger combien de fois la table a été exécutée

( 2) activer l'autotrace

Utilisation :

Fonction de commande :

SET AUTOT[RACE] OFF pour arrêter AutoTrace

SET AUTOT[RACE] ON pour activez AutoTrace et affichez les informations AUTOTRACE et les résultats de l'exécution SQL
SET AUTOT[RACE] TRACEONLY pour activer AutoTrace, affichez uniquement les informations AUTOTRACE
SET AUTOT[RACE] ON EXPLAIN Activez AutoTrace, affichez uniquement les informations EXPLAIN de AUTOTRACE
SET AUTOT[RACE] ON STATISTICS Activez AutoTrace, affichez uniquement les informations sur les STATISTIQUES AUTOTRACE

SQL> set autotrace on
SQL> select * from employees,jobs where employees.job_id=jobs.job_id and employees.department_id=50;
--输出结果（略）
-- ...
已选择45行。
 
执行计划
----------------------------------------------------------
Plan hash value: 303035560
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 45 | 4590 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 45 | 4590 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| JOBS | 19 | 627 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | JOB_ID_PK | 19 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 45 | 3105 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMPLOYEES | 45 | 3105 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")
filter("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")
5 - filter("EMPLOYEES"."DEPARTMENT_ID"=50)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
13 consistent gets
0 physical reads
0 redo size
5040 bytes sent via SQL*Net to client
433 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
45 rows processed

Avantages :

1. Les informations statistiques pertinentes pendant l'exécution peuvent être générées (combien de logiques). les lectures sont générées, combien d'appels récursifs, combien de lectures physiques, etc.);

2 Bien qu'il doive attendre, le plan d'exécution ne peut être généré qu'après l'exécution de l'instruction, mais le commutateur traceonly peut être généré. utilisé pour contrôler que les résultats renvoyés ne soient pas affichés à l'écran

Inconvénients :

1 Vous devez attendre que l'instruction SQL soit exécutée avant que les résultats ne soient affichés ; 🎜>2. Il est impossible de voir combien de fois la table a été consultée

(3) Statistics_level=all

Étape 1 : ALTER SESSION SET STATISTICS_LEVEL=ALL;

Étape 2 : Exécutez le SQL à analyser

Étape 3 : sélectionnez * from table(dbms_xplan.display_cursor('sql_id/hash_value',null,'allstats last'));

SQL> alter session set statistics_level=all;
SQL> select * from employees,jobs where employees.job_id=jobs.job_id and employees.department_id=50;
--输出结果
--...
已选择45行。
 
SQL> set linesize 1000
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
-----------
SQL_ID d8jzhcdwmd9ut, child number 0
-------------------------------------
select * from employees,jobs where employees.job_id=jobs.job_id and
employees.department_id=50
Plan hash value: 303035560
------------------------------------------------------------------------------------------------------------------------
----------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem |
1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
----------------
| 0 | SELECT STATEMENT | | 1 | | 45 |00:00:00.01 | 13 | 8 | |
| |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
-------------
| 1 | MERGE JOIN | | 1 | 45 | 45 |00:00:00.01 | 13 | 8 | |
| |
| 2 | TABLE ACCESS BY INDEX ROWID| JOBS | 1 | 19 | 19 |00:00:00.01 | 6 | 2 | |
| |
| 3 | INDEX FULL SCAN | JOB_ID_PK | 1 | 19 | 19 |00:00:00.01 | 3 | 1 | |
| |
|* 4 | SORT JOIN | | 19 | 45 | 45 |00:00:00.01 | 7 | 6 | 6144 |
6144 | 6144 (0)|
|* 5 | TABLE ACCESS FULL | EMPLOYEES | 1 | 45 | 45 |00:00:00.01 | 7 | 6 | |
| |
------------------------------------------------------------------------------------------------------------------------
----------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
-----
filter("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")
5 - filter("EMPLOYEES"."DEPARTMENT_ID"=50)
已选择25行。

Interprétation des mots clés :

1. start : le nombre d'exécutions SQL

2. E-Rows : le nombre de lignes attendues par le plan d'exécution ; >3. R-Rows : Le nombre réel de lignes renvoyées par le plan d'exécution

4 A-Time : Le temps d'exécution de chaque étape (HH:MM:SS.FF). , vous pouvez savoir où est passé le temps SQL ;

5. Buffers : lecture logique ou lecture cohérente effectivement exécutée à chaque étape

6. Lectures : lecture physique ; >Avantages :

1. Peut être clair Obtenez combien de fois la table a été consultée depuis les démarrages

2、可以从E-Rows和A-Rows得到预测的行数和真实的行数，从而可以准确判断Oracle评估是否准确；

3、虽然没有准确的输出运行时的相关统计信息，但是执行计划中的Buffers就是真实的逻辑读的数值；

缺点：

1、必须要等执行完后才能输出结果；

2、无法控制结果打屏输出，不像autotrace可以设置traceonly保证不输出结果；

3、看不出递归调用，看不出物理读的数值

（4）dbms_xplan.display_cursor获取

步骤1：select * from table( dbms_xplan.display_cursor('&sql_id') ); --该方法是从共享池得到

注释：

1、还有1种方法，select * from table( dbms_xplan.display_awr('&sql_id') ); --该方法是从awr性能视图里面获取

2、如果有多个执行计划，可用以下方法查出：

select * from table(dbms_xplan.display_cursor('&sql_id',0));
select * from table(dbms_xplan.display_cursor('&sql_id',1));
*/
SQL> select * from table(dbms_xplan.display_cursor('5hkd01f03y43d'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 5hkd01f03y43d, child number 0
-------------------------------------
select * from test where table_name = 'LOG$'
Plan hash value: 2408911181
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 241 | 2 (0)|
|* 2 | INDEX RANGE SCAN | IDX_TEST_1 | 1 | | 1 (0)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TABLE_NAME"='LOG$')
19 rows selected

注释：如何查看1个sql语句的sql_id，可直接查看v$sql

优点：

1、知道sql_id即可得到执行计划，与explain plan for一样无需执行；

2、可得到真实的执行计划

缺点：

1、没有输出运行的统计相关信息；

2、无法判断处理了多少行；

3、无法判断表被访问了多少次；

（5）事件10046 trace跟踪

步骤1：alter session set events '10046 trace name context forever,level 12'; --开启追踪

步骤2：执行sql语句；

步骤3：alter session set events '10046 trace name context off'; --关闭追踪

步骤4：找到跟踪后产生的文件（开启10046前先用‘ls -lrt’看一下文件，执行结束后再看哪个是多出来的文件即可）

步骤5：tkprof trc文件 目标文件 sys=no sort=prsela,exeela,fchela --格式化命令

优点：

1、可以看出sql语句对应的等待事件；

2、如果函数中有sql调用，函数中有包含sql，将会被列出，无处遁形；

3、可以方便的看处理的行数，产生的逻辑物理读；

4、可以方便的看解析时间和执行时间；

5、可以跟踪整个程序包

缺点：

1、步骤繁琐；

2、无法判断表被访问了多少次；

3、执行计划中的谓词部分不能清晰的展现出来

推荐：Oracle数据库学习教程

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!