AutoTrace
이는 SQL의 실행계획과 성능 통계를 자동으로 출력해 주는 기능입니다
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
SQL> set autotrace on
SQL> select * from emp where empno = 7900;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7900 JAMES CLERK 7698 25/01/16 950 30
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 38 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=7900)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
1125 bytes sent via SQL*Net to client
398 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
해당 집합의 결과와 Execution Plan 실행 계획과 Statistics 성능 통계가 나오게 됩니다
실행계획
실행계획은 컬럼은 다음의 데이터를 가리킵니다
Id - 실행 단계의 ID Operation - 수행된 SQL 연산 (FULL SCAN , INDEX RANGE SCAN 등) Name - 접근한 테이블 또는 인덱스 이름 Rows - 예상 결과 행 개수 Bytes - 예상 결과 바이트 크기 Cost - 비용 이때는 낮은 비용일수록 성능이 좋다는 것을 의미 Time - 실행 시간
이를 바탕으로 위의 결과를 해석해 보면 select * from emp where empno = 7900; 을 읽을 때 Table Full Access 방식으로 스캔을 했으며 접근한 테이블은 EMP 테이블이고 예상 ROW는 1개 예상 결과 크기는 38Bytes 비용은 3이며 걸린 시간은 약 1초를 뜻합니다
성능계획
사실 실행계획은 최적의 상황을 고려하지만 이는 결국 물리적인 한계를 가지고 있습니다 그래서 성능 통계를 같이 보게 됩니다 성능은 컴퓨터의 물리적인 성능에 대해서 분석한 결과입니다
recursive calls - 재귀 호출 (데이터 딕셔너리 조회, 자동 시퀀스 증가, 내부 트리거 실행 등등) consistent gets - 테이블에서 직접 읽은 블록 수 consistent gets - 데이터 조회를 위해 읽은 블록 수 physical reads - 디스크에서 읽은 블록 수 (높으면 I/O 문제 가능) , 이 부분이 0으로 나오는 것은 버퍼캐시에 이미 결과 셋이 저장되어 있다는 뜻임 redo size - 트랜잭션 로그에 기록된 크기 sorts (memory) - 메모리에서 정렬 작업 횟수 높으면 성능 이슈 sorts (disk) - 디스크에서 정렬 작업 회수 높으면 성능 이슈 rows processed - 처리된 ROW 수
bytes sent via SQLNet to client - 클라이언트로 전송한 바이트 데이터양이 커지면 커질수록 네트워크 부하가 커질 수 있음 bytes received via SQLNet from client - 클라이언트에서 수신한 바이트 이 수가 커지는 것은 클라이언트의 insert , update SQL 을 호출할 가능성이 있음, 또는 바인드 변수도 포함 SQL*Net roundtrips to/from client - 클라이언트와, 서버 간의 왕복 회수 한 번에 여러 데이터를 처리하지 못할 때는 여러 번 왕복하게 되는데 마찬가지로 네트워크 이슈가 발생할 수 있음
SQL 트레이스
이는 AutoTrace 만으로 도저히 성능에 대한 이슈를 해결할 수 없을 때 좀 더 상세한 정보가 필요할 때 사용하게 됩니다
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SQL> alter session set sql_trace=true;
SQL> select * from emp where empno = 7900;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7900 JAMES CLERK 7698 25/01/16 950 30
SQL> select * from dual;
D
-
X
SQL> alter session set sql_trace = false;
일부로 2개의 테이블을 select 하는 문구를 사용했습니다 이때 session 상으로 sql_trace를 사용하고 끄는 쿼리를 했습니다 이전과 다르게 무엇인가 나오지 않았지만 이 결과는 파일로 저장이 됩니다
트레이스 파일 찾기
1
2
3
4
5
6
7
SQL> select value from v$diag_info where name = 'Default Trace File';
VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
/opt/oracle/diag/rdbms/orale_kimdongy1000/ORCL/trace/ORCL_ora_119673.trc
이렇게 위치가 나오는데 아마 각자의 oracle 홈이 다르므로 자신의 oracle 파일을 찾으러 가보자
1
2
3
4
5
[oracle@localhost trace]$ ls -al ORCL_ora_119673.trc
-rw-r-----. 1 oracle dba 3453 2월 2 20:24 ORCL_ora_119673.trc
해당 위치로 가면. trc 파일이 있긴 한데 이를 vi로 읽을 수는 없다 설사 열리더라도 우리가 알 수는 없다 그래서 다른 소프트웨어의 도움을 받아야 하는데 oracle 을 설치했으면 다음 경로로 찾아가 보자
tkprof
이는 위와 같은. trc 파일을 분석해서 우리가 읽을 수 있게 만들어주는 소프트웨어입니다 그래서 해당 파일을 분석하기 위해서는 다음과 같은 명령어를 사용합니다
1
2
$ORACLE_HOME/bin/tkprof ./ORCL_ora_119673.trc report.prf sys=no
ORCL_ora_119673.trc 이 파일을 report.prf 변환해서 보겠다는 뜻입니다. prf 같은 경우는 vi 편집기로 열면 우리가 볼 고 이해할 수 있는 내용들이 적혀 있습니다
결과
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
TKPROF: Release 19.0.0.0.0 - Development on 일 2월 2 20:33:10 2025
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Trace file: ./ORCL_ora_119673.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
The following statement encountered a error during parse:
select * from where empno = 7900
Error encountered: ORA-00903
********************************************************************************
SQL ID: 8am3c6wqp3upw Plan Hash: 3956160932
select *
from
emp where empno = 7900
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 2 0.00 0.00 0 7 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 7 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 109
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0 time=81 us starts=1 cost=3 size=38 card=1)
********************************************************************************
SQL ID: a5ks9fhw2v9s1 Plan Hash: 272002086
select *
from
dual
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
"report.prf" 139L, 5868C
여기서 알 수 있는 내용들은 다음과 같습니다
call - SQL 문 처리 단계 (Parse, Execute, Fetch) count - 해당 단계가 실행된 횟수 cpu CPU - 사용 시간 (초) elapsed - 전체 실행 시간 (초) disk - 디스크 I/O 발생 횟수 (물리적 읽기) query - 논리적 읽기 횟수 (일반 SELECT 문 수행 시) current - SELECT FOR UPDATE 같은 현재 블록 읽기 횟수 rows - 반환된 행 개수
이때 call 을 보면 세 가지 단계가 있습니다 Parse, Execute, Fetch
Parse - SQL 파싱 (SQL 문구를 분석해서 분해하는 파싱 트리 생성 문법적 오류가 있는지 체크 그리고 여러 실행계획을 검토)
Execute - SQL 최적화 및 실행 (옵티마이저는 미리 수집한 시스템 및 통계정보를 바탕으로 다양한 실행 경로를 비교 후 그중 제일 비용이 낮은 방식을 채택 후 실행)
Fetch - 실행 결과를 가져옵니다
그래서 parse 가 성능이 안 나온다면 바인드 변수를 활용하는지 확인이 필요함 Execute 가 성능이 안 나온다면 쿼리 실행 자체가 오래 걸리는 것으로 트랜잭션 lock이나 대량 데이터 스캔으로 인한 시간 소요 등이 있습니다 Fetch 가 오래 걸린다면 한 번에 데이터를 많이 가져오는데 무리가 있음으로 적절하게 잘라서 가져오는 것들이 필요합니다
앞으로 모든 쿼리들은 다음과 같이 분석을 할 것이며 예제 데이터는 chat gpt를 통해서 만들 것입니다 그리고 대량 데이터가 필요해서 chatgpt 스크립트 만으로 불가능하다면 프로그래밍을 해서 자동으로 데이터를 만드는 스크립트를 공유해서 최대한 대량의 데이터를 다뤄보면서 내용을 진행을 해 나갈 것입니다