실행 계획을 확인하기 위해 Oracle 고급 학습을 완전히 마스터하세요.

이 기사는 Oracle에 대한 관련 지식을 제공하며, 실행 계획 보기와 관련된 문제를 주로 소개합니다. 모든 분들께 도움이 되기를 바랍니다.

추천 튜토리얼: "Oracle Video Tutorial"

오늘은 오라클이 실행 계획을 보는 방법과 실행 계획을 보는 방법에 대해 이야기하겠습니다.

1. 실행계획 보는 방법

1.1. Autotrace 설정

autotrace 명령은 다음과 같습니다

일련번호	명령	설명
1	SET AUTOTRACE OFF	기본값으로 Autotrace를 끕니다
2	SET AUTOTRACE ON EXPLAIN	실행만 표시 계획
3	SET AUTOTRACE ON STATISTICS	실행 통계만 표시
4	SET AUTOTRACE ON	두 개의 항목이 포함되어 있습니다.
5	SET AUTOTRACE TRACEONLY	ON과 유사하지만 문의 실행 결과를 표시하지 않습니다

1.2. PL/SQL 개발의 설명 창과 같은 타사 도구

를 사용하세요. , 하지만 아직 파악하지 못했습니다. 나중에

예를 추가하세요.

SQL> EXPLAIN PLAN FOR SELECT * FROM EMP;

가 설명되었습니다.

SQL> SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));

또는:

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. 테스트 테이블 생성

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,
       'test'||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. 실행 계획 보기

이 두 테이블과 관련된 실행 계획 보기

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. 실행 계획

첫 번째 부분을 살펴보겠습니다.

--------------------------------------------------------------------------------
| 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. 행

행 값은 CBO가 행 소스에서 반환할 것으로 예상하는 레코드 수를 나타냅니다. 이 행 소스는 테이블, 인덱스 또는 하위 쿼리일 수 있습니다. Oracle 9i의 실행 계획에서 Cardinality는 Card로 축약됩니다. 10g에서는 카드 값이 행으로 대체됩니다.

rows

가치는 CBO

가 올바른 실행 계획을 세우는 데 매우 중요합니다.

CBO가 얻은 행 값이 충분히 정확하지 않은 경우(일반적으로 분석 부족 또는 오래된 분석 데이터로 인해) 실행 계획 비용 계산에 편차가 발생하여 CBO가 실행 계획을 잘못 수립하게 됩니다.

SQL에 다중 테이블 관련 쿼리나 하위 쿼리가 있는 경우, 각 관련 테이블이나 하위 쿼리의 행 값이 메인 쿼리에 큰 영향을 미칩니다. 심지어 CBO는 각 관련 테이블의 행에 따라 달라집니다. 또는 하위 쿼리 값을 사용하여 최종 실행 계획을 계산합니다. 다중 테이블 쿼리의 경우 CBO는 연결된 각 테이블에서 반환된 행(행) 수를 사용하여 테이블 연결에 사용할 액세스 방법(예: 중첩 루프 조인 또는 해시 조인)을 결정합니다. 3, 비용(CPU) and Time 실행 계획에 중요한 참고 값입니다

3.2.2. 술어 설명:

술어 정보(작업 ID로 식별):

--------------- ----------- ---------------------

1 - 접속 ("G"."CST_NO"="T"." CST_NO")

Note -----

- 이 명령문에 사용된 동적 샘플링(레벨=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='chf1';

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='chf2';

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