Oracle 実行計画を表示する方法

#実行計画とは何ですか?

SQL は愚かな言語です。各条件は要件であり、アクセス順序が異なれば、実行計画も異なります。 Oracle は選択を行う必要があり、一度に設定できるアクセス パスは 1 つだけです。

実行プランは、Oracle におけるクエリ ステートメントの実行プロセスまたはアクセス パスの記述です。

実行プランの選択:

通常、SQL には複数の実行プランがありますが、どのように選択すればよいでしょうか?実行オーバーヘッドが低い方がパフォーマンスが良く、速度が速いことを意味します。どちらを選択しますか。このプロセスは Oracle の解析プロセスと呼ばれ、Oracle はより適切な実行計画を SGA の共有プールに入れます。後で同じ SQL を実行するには、共有プールから取得するだけでよく、再度分析する必要はありません。

実行計画の選択基準:

統計情報に基づいて実行計画を選択します。

統計情報:

統計情報とは: レコード数、ブロック数など。dba_tables / dba_indexesを参照してください。

動的サンプリング:

Oracle は通常、毎日特定の時刻に統計情報を収集します。Oracle は新しく作成されたテーブルの統計情報をどのように収集しますか?動的サンプリングを使用します。

set autotrace on

set linesize 1000
--SQL ステートメントを実行
--このステートメント (レベル = 2) キーに使用される動的サンプリングが表示されます

6 つの実行プラン

Oracle では 6 つの実行プラン取得方法が提供されており、それぞれ重点が異なります:

通常、選択する場合は次のルールに従います:

1. SQL の実行で結果が生成されるまでに時間がかかる場合、または結果を返せない場合は、方法 1: プランの説明を使用します。

2. 特定の SQL を追跡する最も簡単な方法は、方法 1: プランの説明を使用します。 for の後に、方法 2: set autotrace on

3 が続きます。特定の SQL の複数の実行プランを確認したい場合は、方法 4: dbms_xplan.display_cursor または方法 6: awrsqrpt.sql## のみを使用できます。

#4. SQL に関数が含まれており、その関数に SQL が含まれている場合、つまり呼び出しの階層が複数ある場合、正確に分析したい場合は、方法 5: 10046 追跡

5. 実際の実行計画を確認するには、方法 1: Explain plan for と方法 2: set autotrace on

6 は使用できません。テーブルのアクセス回数を取得したい場合は、方法 3 のみを使用できます:statistics_level = all

Oracle による統計の収集方法:

1. Oracle はテーブルを収集することを選択します。特定の期間 (デフォルトは月曜日から金曜日: 22:00、土曜日と日曜日: 06:00) の統計をインデックス化するため、ユーザーは主にピーク期間を避けるために自分で調整できます;

2.テーブルとインデックスの分析にはしきい値制限があり、しきい値を超えた場合にのみ分析が自動的に実行されます。データの変更が大きくない場合、Oracle はデータを分析しません;

3. 収集方法は柔軟です。これはパーティション テーブルの特定のパーティションに対して実行でき、並列メカニズムを使用してテーブルとインデックスの情報を収集できます。

統計情報の収集方法:

--テーブル統計の収集情報

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

--インデックス統計の収集

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

--テーブルとインデックス統計の収集

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

(1)

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行。

の計画の説明 利点: いいえ実際に実行する必要がある、高速で便利;

欠点:

1. 論理読み取りの数、物理読み取りの数、再帰呼び出しの数など、関連する統計情報は出力されません。 generated;

2. 何行処理されたかを判断することは不可能です;

3. テーブルが何回実行されたかを判断することは不可能です

( 2) set autotrace on

使用法:

コマンド機能:

SET AUTOT[RACE] OFF は AutoTrace を停止します

SET AUTOT[RACE] ON はオンになりますAutoTrace、AUTOTRACE 情報と SQL 実行結果を表示します

SET AUTOT[RACE] TRACEONLY AutoTrace をオンにし、AUTOTRACE 情報のみを表示します
SET AUTOT[RACE] ON EXPLAIN AutoTrace をオンにし、AUTOTRACE EXPLAIN 情報のみを表示します
SET AUTOT [RACE] ON STATISTICS AutoTrace をオンにすると、AUTOTRACE STATISTICS 情報のみが表示されます

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

利点:

1. 実行時に関連する統計情報を出力できます (論理読み取りの数、生成方法など)。再帰呼び出しの数、物理読み取りの数など);

2. 待機する必要がありますが、実行計画はステートメントの実行後にのみ出力できますが、traceonly スイッチを使用してリターンを制御できます。画面出力を印刷せずに結果を表示;

欠点:

1. 結果が出力される前に SQL ステートメントが実行されるまで待つ必要があります;

2. それは不可能ですテーブルがアクセスされた回数を確認するには;

(3)statistics_level=all

ステップ 1: ALTER SESSION SET STATISTICS_LEVEL=ALL;

ステップ 2:分析対象の SQL

ステップ 3: select * 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行。

キーワードの解釈:

1、開始: SQL 実行の数;

2、E-Rows: 実行計画によって返されることが予想される行の数;

3、R-Rows:実行プランによって実際に返される行数;

4. A-Time: 各ステップの実行時間 (HH:MM:SS.FF) この行に基づいて、SQL がどこで実行されたかを知ることができます。時間がかかる;

5. バッファ: 各ステップで実際に実行される論理読み取りまたは一貫性読み取り;

6. 読み取り: 物理読み取り;

利点:

1. テーブルが最初からアクセスされた回数を取得することは明らかです;

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数据库学习教程

以上がOracle 実行計画を表示する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。