Oracle 실행 계획을 보는 방법-Oracle-php.cn

집

데이터 베이스

Oracle

Oracle 실행 계획을 보는 방법

尚

May 11, 2020 pm 05:29 PM

oracle

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은 이를 분석하지 않습니다. Oracle 실행 계획을 보는 방법

3. 수집 방법은 유연합니다. 파티션 테이블의 특정 파티션에 대해 수행할 수 있으며 병렬 메커니즘을 사용하여 테이블 및 인덱스 정보를 수집할 수 있습니다.

통계 정보 수집 방법:

--테이블 통계 수집

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

--인덱스 통계 수집;

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

- - 테이블 및 인덱스의 통계 정보 수집

exec dbms_stats.gather_table_stats(ownname => &#39;AAA&#39;,tabname => &#39;TEST02&#39;,estimate_percent =>
10,method_opt => &#39;for all indexed columns&#39;,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,&#39;allstats last&#39;));
 
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(&#39;&sql_id&#39;,0));
select * from table(dbms_xplan.display_cursor(&#39;&sql_id&#39;,1));
*/
SQL> select * from table(dbms_xplan.display_cursor(&#39;5hkd01f03y43d&#39;));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 5hkd01f03y43d, child number 0
-------------------------------------
select * from test where table_name = &#39;LOG$&#39;
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"=&#39;LOG$&#39;)
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’看一下文件，执行结束后再看哪个是多出来的文件即可）