728x90
테이블 정보
TEST.EMP_210825
-- TEST.EMP_210825
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
/* 데이터 1,000,000건 추가
EMPNO : 숫자 4자리
ENAME : 영어 6자리
JOB : 영어 9자리
MGR : 숫자 4자리
HIREDATE : SYSDATE
SAL : 숫자 4자리 + 첫글자만 랜덤
COMM : NULL
DEPNO = 10 */
INSERT INTO TEST.EMP_210825
SELECT ROUND(DBMS_RANDOM.VALUE(1000,9999)) AS EMPNO,
DBMS_RANDOM.STRING('U',6) AS ENAME,
DBMS_RANDOM.STRING('U',9) AS JOB,
ROUND(DBMS_RANDOM.VALUE(1000,9999)) AS MGR,
SYSDATE AS HIREDATE,
ROUND(DBMS_RANDOM.VALUE(1, 9)) || '000' AS SAL,
NULL AS COMM,
10 AS DEPNO
FROM DUAL
CONNECT BY LEVEL < 1000000;
인덱스 구성
- EMPNO
- EMPNO + ENAME
- EMPNO + SAL
CREATE INDEX INDEX_1 ON TEST.EMP_210825(EMPNO);
CREATE INDEX INDEX_2 ON TEST.EMP_210825(EMPNO, ENAME);
CREATE INDEX INDEX_3 ON TEST.EMP_210825(EMPNO, SAL);실행 계획 분석 - 10046 TRACE
-- 카디널리티 확인
SELECT COUNT(DISTINCT(EMPNO)),
COUNT(DISTINCT(ENAME)),
COUNT(DISTINCT(SAL))
FROM TEST.EMP_210825;
C_EMPNO C_ENAME C_SAL
-------- ------- -----
9000 998396 19
(1) SQL 실행
아래 경우에 대해
- FULL TABLE SCAN 했을 때 10046 TRACE
- INDEX SCAN(INDEX_1) 했을 때 10046 TRACE
-- 1. INDEX_1 : FULL TABLE SCAN
SELECT /*+ NO_INDEX(A) */ *
FROM TEST.EMP_210825 A
WHERE EMPNO = '7369';
-- 2. INDEX SCAN(INDEX_1)
SELECT *
FROM TEST.EMP_210825 A
WHERE EMPNO = '7369';10046 TRACE 확인
-- SERVER PROCESS ID 확인
SELECT P.SPID
FROM V$PROCESS P,
V$SESSION S
WHERE P.ADDR = S.PADDR
AND S.AUDSID = USERENV('SESSIONID');
ALTER SESSION SET
EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 1';
-- SQL 실행 . . .
ALTER SESSION SET SQL_TRACE=FALSE;앞서 확인한 SPID로 TRACE 파일을 찾는다. TRACE 파일을 쉽게 볼 수 있게 변경
(ORCL)/oracle/app/oracle/diag/rdbms/orcl/ORCL/trace> tkprof ORCL_ora_2460.trc /tmp/2460.txt
(2) TRACE 파일 내용
1. FULL TABLE SCAN
-- 1.
SELECT /*+ NO_INDEX(A) */ *
FROM TEST.EMP_210825 A
WHERE EMPNO = '7369'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 4 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.01 0 4900 0 101
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.01 0 4904 0 101
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 9
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
101 101 101 TABLE ACCESS FULL EMP_210825 (cr=4900 pr=0 pw=0 time=23 us starts=1 cost=1716 size=17400 card=200)2. INDEX_1
-- 2.
SELECT *
FROM TEST.EMP_210825 A
WHERE EMPNO = '7369'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 4 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 104 0 101
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 108 0 101
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 9
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
101 101 101 TABLE ACCESS BY INDEX ROWID EMP_210825 (cr=104 pr=0 pw=0 time=40 us starts=1 cost=2 size=17400 card=200)
101 101 101 INDEX RANGE SCAN INDEX_1 (cr=4 pr=0 pw=0 time=635 us starts=1 cost=1 size=0 card=200)(object id 29305)→ query (메모리에서 읽은 블록의 수)가
full table scan 시 4904개, index 사용시 108개로
index를 타고 실행을 했을 때 cost 면에서 더 효율적인 것을 알 수 있다.
(3) TRACE 파일 내용 분석
SQL
SELECT *
FROM TEST.EMP_210825 A
WHERE EMPNO = '7369'Call statistics
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 4 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 104 0 101
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 108 0 101- Call - Parse : SQL을 Parsing
- Execute : SQL을 실제 Execute. DML 일 경우
- Fetch : SQL을 통해 나온 값을 사용자에게 반환. DB Buffer Cache에 복사된 블록 중에서 사용자가 요청한 원하는 데이터만 고름
- Count : SQL문이 parse, execute, fetch 가 수행된 횟수
- CPU : call 작업 (parse, execute, fetch) 이 실제로 사용한 CPU 시간
- Elapsed : 각 구간에서 시작과 종료까지 총 수행한 시간
- Disk : Disk에서 읽은 Block의 양(Physical Read)
- Query : Memory에 읽은 Block의 양(Logical Read)
- Current : 현재의 Session에서 작업한 내용을 Commit하지 않아 자신에게만 유효한 Block(Dirty Block)을 액세스한 블럭 양
- Rows : 각 단계별 액세스한 Rows
→ disk, query, current의 숫자는 적을수록 좋다.
이 숫자들이 크면 메모리 공유 영역의 적중률 (Hit ratio)이 낮다는 것을 의미
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 9
Number of plan statistics captured: 1- Misses in library cache during parse : parse 하는데 library cache에서 해당 SQL을 찾지 못함
- 1 : Hard parse
- 0 : Soft parse
- Optimizer mode
- first_rows : 처음 몇개의 행을 빠르게 전달하는 best plan 찾기
- first_rows_n : 처음 n개의 행을 반환하는데 가장 response time이 좋도록 최적화
- all_rows : 전체 SQL문을 완료하는데 최소 리소스 사용을 목표로 함
- Parsing user id : parsing 을 수행한 user의 id
- Number of plan statistics captured : Oracle이 계획에 대한 정보를 기록한 실행 횟수
Row Source Operation
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
101 101 101 TABLE ACCESS BY INDEX ROWID EMP_210825 (cr=104 pr=0 pw=0 time=40 us starts=1 cost=2 size=17400 card=200)
101 101 101 INDEX RANGE SCAN INDEX_1 (cr=4 pr=0 pw=0 time=635 us starts=1 cost=1 size=0 card=200)(object id 29305)- cr = Logical block read -- > db buffer cache에서 읽은 블록 수
- pr = 디스크 블록 읽기. physical block read
- pw = 디스크 블록 쓰기. physical block write
- time = 소요 시간 ( us = microsecond )
- starts = the number of times that line in the plan started.
- cost = 비용. (cpu 점유, disk 1/o, 수행시간 등 을 추정한 값) 클수록 성능상 좋지는 않음
- size = 데이터 크기
- card = (Computed) Cardinality . The estimated number of rows returned by operation
728x90
'Dev > Oracle' 카테고리의 다른 글
| [Oracle] RMAN으로 아카이브 로그 삭제하기 (0) | 2022.08.20 |
|---|---|
| [Oracle] 10046 TRACE - LEVEL별 (0) | 2022.07.22 |
| [Oracle] 실행 계획 - EXPLAIN PLAN, SET AUTOTRACE (0) | 2022.07.20 |
| [Oracle] Oracle Database 생성 (0) | 2022.07.14 |
| [Oracle] 인스턴스 추가 (0) | 2022.07.13 |
댓글