Oracle 실행 계획을 보는 방법

실행계획이란 무엇인가요?

SQL은 바보 같은 언어입니다. 각 조건은 서로 다른 액세스 순서에 따라 서로 다른 실행 계획을 형성합니다. Oracle은 선택을 해야 하며 한 번에 하나의 액세스 경로만 가질 수 있습니다. 실행 계획은 Oracle에서 쿼리문의 실행 프로세스나 액세스 경로에 대한 설명입니다.

실행 계획 선택:

일반적으로 SQL에는 여러 실행 계획이 있는데 어떻게 선택합니까? 실행 오버헤드가 낮은 것은 더 나은 성능과 더 빠른 속도를 의미합니다. 이 프로세스를 Oracle의 구문 분석 프로세스라고 하며 Oracle은 나중에 동일한 SQL을 실행하기 위해 더 나은 실행 계획을 넣습니다. 공유 풀에서만 가져오면 되며 다시 분석할 필요가 없습니다.

실행 계획 선택 기준:

통계 정보를 기반으로 실행 계획을 선택합니다.

통계 정보:

통계 정보란 무엇입니까: 레코드 수, 블록 수 등, 자세한 내용은 dba_tables / dba_indexes를 참조하세요.

동적 샘플링:

Oracle은 일반적으로 매일 특정 시간에 통계 정보를 수집합니다. , 신규 Oracle은 테이블에 대한 통계 정보를 어떻게 수집합니까? 동적 샘플링을 사용합니다.

set autotrace on
set linesize 1000
--SQL 문 실행
--이 문(레벨=2)에 사용된 동적 샘플링 키가 나타납니다

6가지 실행 계획

Oracle은 6가지 실행 계획을 제공합니다. 획득 방법마다 초점이 다릅니다.

선택 시 일반적으로 다음 규칙을 따르세요.

1. SQL 실행이 결과를 생성하는 데 오랜 시간이 걸리거나 결과를 반환할 수 없는 경우 방법 1: 계획 설명

2을 사용하세요. 추적 특정 SQL에 대한 가장 간단한 방법은 방법 1: explain plan for, 이어서 방법 2: set autotrace on

3입니다. 특정 SQL의 여러 실행 계획을 확인하려면 방법 4: dbms_xplan만 사용할 수 있습니다. display_cursor 또는 방법 6: awrsqrpt.sql

4. sql에 함수가 포함되어 있고 함수에 sql이 포함되어 있는 경우, 즉 여러 계층의 호출이 있는 경우 정확하게 분석하려면 방법 5: 10046만 사용할 수 있습니다. 추적

5. 실제 실행 계획을 보려면 방법 1: 계획 설명 및 방법 2: 자동 추적 설정을 사용할 수 없습니다.

6. 방법 3만 사용하세요. 일요일: 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 관련 통계 정보가 출력되지 않습니다. , 생성된 논리적 읽기 수, 실제 읽기 수 및 재귀 호출 수 등

2. 테이블이 몇 번 처리되었는지 판단하는 것은 불가능합니다.

(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를 켜고 STATISTICS 정보만 표시 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

장점:

1. 런타임 시 관련 통계 정보(생성된 논리적 읽기 수, 재귀 호출 수, 물리적 읽기 수 등)를 출력할 수 있습니다.

2. 명령문이 실행될 때까지 출력할 수 없습니다. Traceonly 스위치를 사용하면 화면 출력을 인쇄하지 않고 반환 결과를 제어할 수 있습니다.

단점:

1 결과를 사용할 수 있으려면 SQL 문이 실행될 때까지 기다려야 합니다.

2. 테이블에 몇 번 액세스했는지 확인할 수 없습니다.

(3) Statistics_level=all

1단계: ALTER SESSION SET STATISTICS_LEVEL=ALL;

2단계: 분석할 SQL을 실행합니다

3단계: 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. 행: 실행 계획에서 반환될 것으로 예상되는 행 수

4. A-Time: 각 단계의 실행 시간 :MM:SS.FF), 이 줄을 통해 SQL이 시간을 소비하는 위치를 알 수 있습니다.

5. 버퍼: 각 단계에서 실제로 실행되는 논리적 읽기 또는 일관된 읽기

. 장점:

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 중국어 웹사이트의 기타 관련 기사를 참조하세요!