Oracle の高度な学習を完全にマスターして実行計画を表示する

この記事では、Oracle に関する関連知識を提供し、主に実行計画の表示に関連する問題について紹介します。

推奨チュートリアル: 「Oracle ビデオ チュートリアル 」

今日は、Oracle の実行計画の見方とその表示方法について説明します。実行計画。

1. 実行計画の表示方法

1.1. autotrace の設定

autotrace コマンドは次のとおりです

##4SET AUTOTRACE ON##5

#シリアル番号	コマンド	#説明
1	SET AUTOTRACE OFF	これはデフォルト値ですこれは、Autotrace がオフになっていることを意味します
2	SET AUTOTRACE ON EXPLAIN	実行計画のみを表示
##3	SET AUTOTRACE ON STATISTICS
	## の実行のみの統計を表示します # には 2 と 3 の項目が含まれています
##SET AUTOTRACE TRACEONLY	ON と似ていますが、ステートメントの実行結果は表示されません	1.2. サードパーティ ツールを使用する PL/SQL Develop の Explain ウィンドウなど ##1.3. EXPLAIN PLAN FOR SQLを実行する前にEXPLAIN PLAN FORを入れると実行計画が確認できるとのことですが、まだよく分かりませんので、例えば SQL> EXPLAIN PLAN FOR SELECT * FROM EMP; のように ##を追加してみます。 について説明しました。 SQL> SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY(&#39;PLAN_TABLE&#39;)); または: SQL> select * from table(dbms_xplan.display); 2. SGA キャッシュをクリアします SQL が実行されると、SQL 実行プラン、ディスクから読み取られたデータベース、およびその他の情報が消去されるためです。 SGA では、一部のキャッシュは一定期間保存されるため、ステートメントの最初の実行の効果を確認するには、これらのキャッシュをクリアする必要があります。 ALTER SYSTEM FLUSH SHARED_POOL; ALTER SYSTEM FLUSH BUFFER_CACHE; ALTER SYSTEM FLUSH GLOBAL CONTEXT; 3. 実行計画の分析 3.1. テスト テーブルの作成 2 つの新しいテーブル cust_info と cst_tran を作成します (テスト専用で、実用的な意味はありません) 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) ); データを挿入します。CUST_INFO テーブル 10,000、CST_TRAN テーブル 100 万。 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. 実行計画の表示 これら 2 つのテーブルに関連付けられた実行計画の表示 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. 実行計画 最初に見てみましょうパート 1 -------------------------------------------------------------------------------- | 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 | -------------------------------------------------------------------------------- #実行計画のフィールドの説明: ## ID: シリアル番号 (順序ではありません)実行の。実行順序はインデントに基づいて判断されます。 操作: 現在の操作の内容。 行: 現在の操作のカーディナリティ。Oracle は、現在の操作の返される結果セットを推定します。 コスト (CPU): SQL 実行のコストを示すために Oracle によって計算された数値 (コスト)。 時間: Oracle は現在の操作の時間を推定します。 手順: 1. 操作各ステップの操作を記録し、程度に応じて実行順序を判断します。インデント。 OLAP データベースには多くの HASH JOIN 接続があり、特に返されるデータ セットが大きい場合、基本的に HASH JOIN になります。 2. Rows rows の値は、CBO が行ソースから返すと予想されるレコードの数を示します。この行ソースは、テーブル、インデックス、またはサブクエリの場合があります。 Oracle 9i の実行プランでは、カーディナリティは Card と省略されます。 10gでは、カードの値が行に置き換えられます。 rows 値は、CBO が正しい実行計画を作成するために重要です。 CBO によって取得された行の値が十分に正確でない場合 (通常、分析が不足しているか、分析データが古いため)、実行計画のコスト計算に誤差が生じ、CBO が誤って実行計画を策定することになります。 SQL に複数テーブル関連のクエリやサブクエリがある場合、関連する各テーブルやサブクエリの rows 値がメインクエリに大きな影響を与え、CBO は関連するそれぞれのテーブルやサブクエリに依存するとさえ言えます。または、サブクエリ行の値によって最終的な実行計画が計算されます。 複数テーブルのクエリの場合、CBO は、関連付けられた各テーブルから返された行数 (行数) を使用して、テーブルの関連付けに使用するアクセス方法 (ネストされたループ結合やハッシュ結合など) を決定します。 3. コスト(CPU)と時間は実行計画の重要な参考値です 3.2.2. 述語の説明: 述語情報(で識別されます)操作 ID):------------------------------------------ ----- --- 1 - アクセス("G"."CST_NO"="T"."CST_NO") 注 ----- - このステートメントに使用される動的サンプリング (レベル=2) アクセス: この述語条件の値がデータ (テーブルまたはインデックス) のアクセス パスに影響を与えることを示します。 フィルター: 述語条件の値がデータのアクセス パスに影響を与えず、フィルターの役割のみを担うことを示します。 (この例ではありません) 注: 述語の access に注意してください。述語の条件とアクセス パスが適切であるかどうかを考慮する必要があります。使用されているのが正しいです。 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视频教程》

以上がOracle の高度な学習を完全にマスターして実行計画を表示するの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。