Maîtriser complètement l'apprentissage avancé d'Oracle pour visualiser le plan d'exécution

Cet article vous apporte des connaissances pertinentes sur Oracle, qui présente principalement les problèmes liés à l'affichage des plans d'exécution. J'espère qu'il sera utile à tout le monde.

Tutoriel recommandé : "Tutoriel vidéo Oracle"

Aujourd'hui, nous parlerons de la façon dont Oracle visualise le plan d'exécution et de la manière d'afficher le plan d'exécution.

1. Comment afficher le plan d'exécution

1.1. Définir l'autotrace

La commande autotrace est la suivante

1 Il s'agit de la valeur par défaut, ce qui signifie désactiver AutotraceAfficher uniquement l'exécution plan Afficher uniquement les statistiques d'exécutionContient deux articles Semblable à ON, mais n'affiche pas les résultats d'exécution de l'instruction

Explication		SET AUTOTRACE OFF
	2	SET AUTOTRACE ON EXPLAIN
	3	SET AUTOTRACE ON STATISTICS
	4	SET AUTOTRACE ON
	5	SET AUTOTRACE TRACEONLY
	1.2. Utiliser des outils tiers tels que la fenêtre d'explication de PL/SQL Develop 1.3 EXPLAIN PLAN FOR On dit que l'ajout d'EXPLAIN PLAN FOR avant que le SQL exécuté puisse voir le plan d'exécution. , mais je ne l'ai pas encore compris, ajoutez exemple plus tard : SQL> EXPLAIN PLAN FOR SELECT * FROM EMP; a été expliqué. SQL> SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY(&#39;PLAN_TABLE&#39;)); Ou : SQL> select * from table(dbms_xplan.display); 2. Videz le cache SGA Parce que lorsque SQL est exécuté, le plan d'exécution SQL, la base de données lue sur le disque et d'autres informations seront enregistrées dans certains caches de SGA pendant un certain temps. Afin d'afficher le numéro d'instruction, l'effet d'une exécution nécessite de vider ces caches. ALTER SYSTEM FLUSH SHARED_POOL; ALTER SYSTEM FLUSH BUFFER_CACHE; ALTER SYSTEM FLUSH GLOBAL CONTEXT; 3. Analyser le plan d'exécution 3.1. Créer des tables de test Créer deux nouvelles tables, cust_info et cst_tran (uniquement pour les tests, sans signification pratique) CREATE TABLE CUST_INFO (CST_NO NUMBER, CST_NAME VARCHAR2(50), AGE SMALLINT); CREATE TABLE CST_TRAN ( CST_NO NUMBER, TRAN_DATE VARCHAR2(8), TRAN_AMT NUMBER(19,3) ); Insérer des données, table CUST_INFO 10 000, table CST_TRAN 1 million. . INSERT INTO CUST_INFO SELECT 100000+LEVEL, &#39;test&#39;||LEVEL, ROUND(DBMS_RANDOM.VALUE(1,100)) FROM DUAL CONNECT BY LEVEL<=10000; INSERT INTO CST_TRAN WITH AA AS (SELECT LEVEL FROM DUAL CONNECT BY LEVEL<=100) SELECT T.CST_NO, TO_CHAR(SYSDATE - DBMS_RANDOM.VALUE(1,1000),&#39;yyyymmdd&#39;), ROUND(DBMS_RANDOM.VALUE(1,999999999),3) FROM CUST_INFO T INNER JOIN AA ON 1=1; 3.2. Afficher le plan d'exécution Voir le plan d'exécution associé à ces deux tables SQL> SELECT T.CST_NO, T.CST_NAME, G.TRAN_DATE, G.TRAN_AMT FROM CUST_INFO T INNER JOIN CST_TRAN G ON G.CST_NO = T.CST_NO; 1000000 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2290587575 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 996K| 68M| 1079 (2)| 00:00:13 | |* 1 | HASH JOIN | | 996K| 68M| 1079 (2)| 00:00:13 | | 2 | TABLE ACCESS FULL | CUST_INFO | 10000 | 390K| 11 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL | CST_TRAN | 1065K| 32M| 1064 (1)| 00:00:13 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("G"."CST_NO"="T"."CST_NO") Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 561 recursive calls 0 db block gets 70483 consistent gets 4389 physical reads 0 redo size 45078003 bytes sent via SQL*Net to client 733845 bytes received via SQL*Net from client 66668 SQL*Net roundtrips to/from client 10 sorts (memory) 0 sorts (disk) 1000000 rows processed 3.2.1 Plan d'exécution Jetons d'abord un œil à la première partie -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 996K| 68M| 1079 (2)| 00:00:13 | |* 1 | HASH JOIN | | 996K| 68M| 1079 (2)| 00:00:13 | | 2 | TABLE ACCESS FULL | CUST_INFO | 10000 | 390K| 11 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL | CST_TRAN | 1065K| 32M| 1064 (1)| 00:00:13 | -------------------------------------------------------------------------------- Explication des champs. dans le plan d'exécution : ID : Un numéro de série, mais pas l'ordre d'exécution. L'ordre d'exécution est jugé en fonction de l'indentation. Opération : Le contenu de l'opération en cours. Lignes : Cardinalité de l'opération en cours, Oracle estime l'ensemble de résultats de retour de l'opération en cours. Coût (CPU) : Une valeur (coût) calculée par Oracle pour illustrer le coût d'exécution de SQL. Heure : Oracle estime l'heure de l'opération en cours. Instructions : 1. Opération Enregistrez les opérations de chaque étape, et jugez de l'ordre d'exécution en fonction du degré d'indentation. Dans les bases de données OLAP, il existe de nombreuses connexions HASH JOIN, surtout lorsque l'ensemble de données renvoyé est volumineux, il s'agit essentiellement de HASH JOIN. 2. Lignes La valeur des lignes indique le nombre d'enregistrements que CBO s'attend à renvoyer à partir d'une source de ligne. Cette source de ligne peut être une table, un index ou une sous-requête. Dans le plan d'exécution d'Oracle 9i, Cardinality est abrégé en Card. En 10g, les valeurs des Cartes sont remplacées par des lignes. La valeur de rows est cruciale pour que CBO élabore le bon plan d'exécution. Si la valeur des lignes obtenue par CBO n'est pas suffisamment précise (généralement en raison d'un manque d'analyse ou de données d'analyse obsolètes), il y aura des écarts dans le calcul des coûts du plan d'exécution, ce qui conduira le CBO à formuler de manière incorrecte un plan d'exécution. Lorsqu'il existe une requête associée à plusieurs tables ou une sous-requête en SQL, la valeur des lignes de chaque table ou sous-requête associée a un grand impact sur la requête principale. On peut même dire que CBO dépend des lignes de chaque table associée. ou une sous-requête pour calculer le plan d'exécution final. Pour les requêtes multi-tables, CBO utilise le nombre de lignes (lignes) renvoyées par chaque table associée pour déterminer la méthode d'accès à utiliser pour l'association de tables (telle que la jointure par boucles imbriquées ou la jointure par hachage) 3, Coût (CPU) et Temps C'est une valeur de référence importante pour le plan d'exécution 3.2.2 Description du prédicat : Informations sur le prédicat (identifiées par l'identifiant de l'opération) : ---------------. ----------- ---------------------------------- 1 - accès ("G"."CST_NO"="T"." CST_NO") Note ----- - échantillonnage dynamique utilisé pour cette instruction (niveau=2) Accès : Indique que la valeur de ce prédicat Cette condition affectera le chemin d’accès aux données (table ou index).          Filtre : indique que la valeur de la condition du prédicat n'affectera pas le chemin d'accès aux données, mais joue uniquement un rôle de filtrage. (Pas dans cet exemple) Remarque : faites attention à access dans le prédicat Vous devez considérer les conditions du prédicat et si le chemin d'accès utilisé est correct. 3.2.3、统计信息 Statistics ---------------------------------------------------------- 561 recursive calls 0 db block gets 70483 consistent gets 4389 physical reads 0 redo size 45078003 bytes sent via SQL*Net to client 733845 bytes received via SQL*Net from client 66668 SQL*Net roundtrips to/from client 10 sorts (memory) 0 sorts (disk) 1000000 rows processed 参数说明： recursive calls   :递归调用。一般原因：dictionary cache未命中；动态存储扩展；PL/SQL语句 db block gets    :bufer中读取的block数量，用于insert,update,delete,selectfor update consistent gets   :这里是一致读次数（一个block可能会被读多次），bufer中读取的用于查询（除掉select forupdate）的block数量。                 physical reads    :从磁盘上读取的block数量,敬请关注每周五晚免费网络公开课。 redo size      :bytes，写到redo logs的数据量 bytes sent via SQL*Net to client ：发送给客户端的字节数 bytes received via SQL*Net from client ：从客户端接收的字节数 SQL*Net roundtrips to/from client ：与客户端的交互次数（个人理解接收一条SQL语句，执行结果分多次发送给客户端，如有问题请指正） sorts (memory)    :内存排序次数 sorts (disk)     :磁盘排序次数；与sort_area_size有关  rows processed ：执行完SQL后返回结果集的行数 四、部分信息解释 4.1、SQL*Net roundtrips to/from client的计算方式 这个指标的计算方式和一个参数息息相关，arraysize。 arraysize是什么呢？ 请查阅大牛博文：Oracle arraysize 和 fetch size 参数 与 性能优化 说明 arraysize定义了一次返回到客户端的行数，取值范围【1-5000】，默认15。 使用命令在数据库中查看arraysize的值。 show arraysize 还可以修改这个值 set arraysize 5000; 明白了arraysize这个参数就可以计算SQL*Net roundtrips to/from client的值了。上例中，返回客户端结果集的行数是1000000，默认arraysize值是15，1000000/15向上取整等于66667。 为啥要向上取整？ 举个栗子，如果有10个苹果，一个只能拿3个，几次可以拿完，3次可以拿9个，还剩1个，所以还需要再拿一次，共4次。 统计分析中的值是66668，为什么我们计算的值是66667？ 就要看这个指标本身了，再粘贴一次：SQL*Net roundtrips to/from client  重点看from，意思是我们还要接受一次客户端发来的SQL语句，因此是：66667+1，本问题纯属个人臆断，无真凭实据，受限于本人的知识水平，如有误，请指出。 将arraysize的值修改为5000后，再观察SQL*Net roundtrips to/from client的变化，结果为201。 前面提到 arraysize的取值范围是【1-5000】，我们可以试一下改为不在这个区间的值，比如改为0，结果报错了 SQL> set arraysize 0; SP2-0267: arraysize option 0 out of range (1 through 5000) 4.2、consistent gets 译为中文就是：一致性读， 好抽象的一个指标，啥叫一致性读，心中无数羊驼驼在大海中狂奔。 官网对consistent gets 的解释： consistent gets：Number of times a consistent read wasrequested for a block. 通常我们执行SQL查询时涉及的每一block都是Consistent Read, 只是有些CR(Consistent Read)需要使用undo 来进行构造, 大部分CR(Consistent Read)并不涉及到undo block的读. 还有就是每次读这个block都是一次CR(可能每个block上有多个数据row), 也就是如果某个block被读了10次, 系统会记录10个Consistent Read. 如果想深入学习，请参考大佬博文：Oracle 有关 Consistent gets 的测试 -- cnDBA.cn_中国DBA社区 接来下测试下， consistent gets是从哪来的，需要使用有sysdba权限的用户，因为oradebug工具需要sysdba权限。 oradebug工具介绍：oracle实用工具：oradebug 使用10046对同一条数据跟踪两次，注意观察 consistent gets的不同 为了不影响测试结果，首先清空缓存 SQL> ALTER SYSTEM FLUSH SHARED_POOL; System altered. SQL> ALTER SYSTEM FLUSH BUFFER_CACHE; System altered. SQL> ALTER SYSTEM FLUSH GLOBAL CONTEXT; System altered. 第一次执行 SQL> set tim on timing on 00:42:30 SQL> set autot trace stat 00:42:36 SQL> oradebug setmypid Statement processed. 00:42:42 SQL> alter session set tracefile_identifier=&#39;chf1&#39;; Session altered. Elapsed: 00:00:00.01 00:42:50 SQL> oradebug event 10046 trace name context forever,level 12; Statement processed. 00:42:57 SQL> SELECT T.CST_NO, T.CST_NAME, G.TRAN_DATE, G.TRAN_AMT FROM CHF.CUST_INFO T INNER JOIN CHF.CST_TRAN G ON G.CST_NO = T.CST_NO; 1000000 rows selected. Elapsed: 00:00:22.71 Statistics ---------------------------------------------------------- 547 recursive calls 0 db block gets 70368 consistent gets 3898 physical reads 0 redo size 45078003 bytes sent via SQL*Net to client 733845 bytes received via SQL*Net from client 66668 SQL*Net roundtrips to/from client 10 sorts (memory) 0 sorts (disk) 1000000 rows processed 00:44:24 SQL> oradebug event 10046 trace name context off; Statement processed. 00:45:54 SQL> oradebug tracefile_name /u01/app/oracle/diag/rdbms/orcl/bpas/trace/bpas_ora_7715_chf1.trc 第二次执行 00:46:04 SQL> alter session set tracefile_identifier=&#39;chf2&#39;; Session altered. Elapsed: 00:00:00.00 00:46:35 SQL> oradebug event 10046 trace name context forever,level 12; Statement processed. 00:46:43 SQL> SELECT T.CST_NO, T.CST_NAME, G.TRAN_DATE, G.TRAN_AMT FROM CHF.CUST_INFO T INNER JOIN CHF.CST_TRAN G ON G.CST_NO = T.CST_NO; 1000000 rows selected. Elapsed: 00:00:21.62 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 70301 consistent gets 3850 physical reads 0 redo size 45078003 bytes sent via SQL*Net to client 733845 bytes received via SQL*Net from client 66668 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1000000 rows processed 00:47:11 SQL> oradebug event 10046 trace name context off; Statement processed. 00:49:03 SQL> oradebug tracefile_name /u01/app/oracle/diag/rdbms/orcl/bpas/trace/bpas_ora_7715_chf2.trc 通过对比两次执行，发现consistent gets、physical reads、sorts (memory)都有变化，这是因为SGA中已经缓存了部分数据块。 再对比下我们刚才生产的两个跟踪日志，为方便查看，先将其格式转换以下 [oracle@localhost ~]$ tkprof /u01/app/oracle/diag/rdbms/orcl/bpas/trace/bpas_ora_7715_chf1.trc /u01/chf1.trc TKPROF: Release 11.2.0.1.0 - Development on Wed Dec 8 00:53:37 2021 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. [oracle@localhost ~]$ tkprof /u01/app/oracle/diag/rdbms/orcl/bpas/trace/bpas_ora_7715_chf2.trc /u01/chf2.trc TKPROF: Release 11.2.0.1.0 - Development on Wed Dec 8 00:53:48 2021 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. 打开 /u01/chf1.trc，下面贴出部分重要信息 OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.03 0.03 8 67 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 66668 0.76 3.24 3890 70301 0 1000000 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 66670 0.79 3.28 3898 70368 0 1000000 Misses in library cache during parse: 1 Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 66670 0.01 0.14 SQL*Net message from client 66670 64.54 79.11 db file sequential read 5 0.00 0.00 Disk file operations I/O 1 0.00 0.00 db file scattered read 5 0.00 0.00 asynch descriptor resize 4 0.00 0.00 direct path read 69 0.00 0.02 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 12 0.00 0.00 0 0 0 0 Execute 24 0.01 0.01 0 0 0 0 Fetch 30 0.00 0.00 8 67 0 18 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 66 0.02 0.02 8 67 0 18 打开 /u01/chf2.trc，下面贴出部分重要信息 OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 66668 1.57 3.73 3850 70301 0 1000000 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 66670 1.57 3.73 3850 70301 0 1000000 Misses in library cache during parse: 0 Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 66670 0.00 0.10 SQL*Net message from client 66670 6.83 19.93 asynch descriptor resize 4 0.00 0.00 direct path read 69 0.00 0.01 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 0 0.00 0.00 0 0 0 0 Execute 0 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 0 0.00 0.00 0 0 0 0 比较发现，第一次执行解析SQL语句，生产执行计划时，consistent gets发生67次，执行SQL语句时发生70301。第一次执行解析SQL语句，生产执行计划时，因已经有缓存，所以consistent gets发生0次，执行SQL语句时发生70301。 推荐教程：《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!