데이터베이스2025. 3. 4. 13:52

[SQLP 요점정리] 3과목. SQL 고급활용 및 튜닝 - 4. 조인튜닝

SQLP : 제3과목은 SQL 고급활용 및 튜닝 이다.
 
 
3과목. SQL 고급활용 및 튜닝
1. SQL 수행 구조

2. SQL 분석 도구

3. 인덱스 튜닝

4. 조인 튜닝

5. SQL 옵티마이저

6. 고급 SQL 튜닝

7. Lock과 트랜잭션 동시성 제어

 

 

 

 

 

NL(Nested-Loop) 조인
- 중첩된 Loop 로 조인을 하는 구조이다.

for( ... )
{
     for( ...)
     {
        ...
     }
}


- 랜덤 액세스 위주의 조인 방식 : 대량 데이터에서 불리
- 소량데이터 조인시 유리
- DB버퍼캐시에서 읽으므로, 블록에 래치 획득 및 체인 스캔 과정을 거친다. : 대량 데이터에서 불리
- 인덱스를 이용한 조인으로, 인덱스 구성 전략에 의해 성능이 크게 달라진다.
- 후행 테이블의 인덱스 효율성이 중요하다.
- 소량 데이터에 유리, OLTP 시스템에 적합
- 한 레코드씩 순차적으로 진행 : "부분범위 처리"가 가능
- 조인 대상 레코드가 많아도, ArraySize 에 해당하는 최초 n 건을 빠르게 출력 가능


소트머지조인
- 실시간으로 인덱스를 생성한다.
- 첫번째 집합은 조인컬럼에 인덱스가 있으면 인덱스를 사용하고, 없으면 PGA 영역에 저장한다.
- 두번째 집합은 정렬해서 PGA 영역에 저장한다.
- 조인조건이 = 이 아닐때, 조인조건이 없을때도 사용가능하다.
- 버퍼영역이 아닌 PGA 영역에서 조회하므로, 대량 데이터에서 NL 조인보다 빠르다. : 래치 획득 과정이 없어서

Hash 조인
- 작은 집합을 읽어서 PGA 에 해시맵으로 저장한다.
- 해시맵을 사용하여 탐색하기 때문에 조인컬럼에 인덱스가 없어도 상관이 없다. : 인덱스가 성능에 영향을 주지 않음
- 소트머지조인보다 Temp테이블스페이스 사용량이 적다.
- 해시키로 조회하므로 조인 조건이 = 일때만 사용가능하다.
- 양쪽 모두 대량 데이터에서 NL 조인보다 빠르다.
- 수행빈도가 낮고, 쿼리 수행시간이 오래 걸리는 대량 데이터 조인시 유리하다.
- DW/OLAP / Batch 프로그램에 사용.

 


join 힌트

Oracle

ordered

/*+ ordered */
- FROM 절에 나열된 순서대로 조인

leading

/*+ leading(a b c) */
- a b c 순서대로 조인


use_nl( a )
- NL 조인 힌트


use_hash( a )
- 해시 조인 힌트


use_merge( a )
- 소트 머지 조인 힌트


nl_sj
- NL 세미조인 : 서브쿼리에서 사용


swap_join_inputs( a )
- a 를 build input 으로 한다. (조인의 첫번째 집합) : 해시조인에서 사용

no_swap_join_inputs ( a )
- a 를 probe input 으로 지정한다. (조인의 두번째 집합) : 해시조인에서 사용


NO_MERGE
- 메인쿼리와 인라인뷰를 병합하지 말고, 인라인뷰부터 실행한다. : 인라인뷰에 함수 등이 쓰일때, 한번만 실행하도록 유도

PUSH_PRED
- 메인테이블과 인라인뷰의 JOIN 시에, 조인조건을 인라인뷰 안으로 유도한다. : 인라인뷰 성능향상


NO_UNNEST
- 서브쿼리를 필터 방식으로 동작하도록 처리 : 부분범위 처리가 가능해서, 해시 조인으로 처리되던거를 NL 조인으로 변경이 된다.
 
 FULL 
 - 테이블 풀 스캔

 PUSH_SUBQ
 - 서브쿼리가 먼저 실행되도록 제어

QB_NAME( name )
- 현재 쿼리블록의 이름을 지정


index(table index)
- table 의 index 를 사용하도록 한다.

index_desc
- 인덱스를 역순으로 사용

index_ffs
- 인덱스 Fast Full Scan 사용


use_concat
- Concatenation  오퍼레이션 : OR 조건을 풀어서, Union All 조건의 쿼리로 변환한다. : or_expand

no_expand
- use_concat 이 작동하지 않도록 한다. : no_or_expand 



MS-SQL

force order

option( merge join)
- FROM 절에 나열된 순서대로 조인

merge join
option( merge join )
- 소트 머지 조인 힌트


loop join
- NL 조인 힌트

inner loop join

from A
inner loop join B on A.col1 = B.col2

inner hash join
 



스칼라 서브 쿼리

- 스칼라 서브 쿼리는 단일 값만을 반환해야 한다. (스칼라)
- 오라클에서는 서브쿼리 입출력값을 내부캐시에 저장해 두기 때문에 성능향상에 좋다.
- 서브쿼리의 효율은 코드명이나 상품명을 변환시 사용 : 카디널러티가 적은 테이블에 사용하면 좋다.

스칼라 서브쿼리 Execution Plan
- 서브쿼리가 위에 나오고, 메인쿼리가 아래에 나온다.


부분범위처리
- 조건을 만족하는 전체범위를 처리하는 것이 아니라 일단 운반단위(Array Size) 까지만 처리 하여 추출하는 처리방식
- TOP n / ROWNUM 사용 등
- 인덱스 사용이 가능하도록 조건절을 구사하고, 조인은 NL위주로 처리하고, Order By절이 있어도 소트연산을 생략할 수 있도록 인덱스를 구성 : index 의 2번째 컬럼부터 order by 컬럼을 추가
- 페이징 처리 (게시판 등)

 

 

저작자표시 비영리 변경금지

'데이터베이스' 카테고리의 다른 글

[SQLP 요점정리] 3과목. SQL 고급활용 및 튜닝 - 6. 고급SQL튜닝  (1) 2025.03.06
[SQLP 요점정리] 3과목. SQL 고급활용 및 튜닝 - 5. SQL옵티마이저  (0) 2025.03.05
[SQLP 요점정리] 3과목. SQL 고급활용 및 튜닝 - 3. 인덱스튜닝  (0) 2025.03.03
[SQLP 요점정리] 3과목. SQL 고급활용 및 튜닝 - 2. SQL 분석도구  (0) 2025.03.02
[SQLP 요점정리] 3과목. SQL 고급활용 및 튜닝 - 1. SQL 수행 구조  (0) 2025.03.01
Posted by 헝개
데이터베이스2025. 3. 3. 11:44

[SQLP 요점정리] 3과목. SQL 고급활용 및 튜닝 - 3. 인덱스튜닝

SQLP : 제3과목은 SQL 고급활용 및 튜닝 이다.

 

 

3과목. SQL 고급활용 및 튜닝

1. SQL 수행 구조
2. SQL 분석 도구
3. 인덱스 튜닝
4. 조인 튜닝
5. SQL 옵티마이저
6. 고급 SQL 튜닝
7. Lock과 트랜잭션 동시성 제어

 

 

 

 

B*Tree 인덱스 (Balanced Tree) 구조
- 브랜치 블록의 각 로우는 하위블록에 대한 주소값을 갖는다.
- 리프 블록의 키 값은 테이블 로우의 키 값과 같다.
- 리프 블록 끼리는 이중연결리스트(double linked list) 구조이다.

인덱스 ROWID
- (오브젝트번호 + ) 데이터파일번호 + 블록번호 + 블록내로우번호
- 데이터파일 상의 블록 위치를 가리킨다. 논리적인 주소 (물리적인 주소는 아니다.)
- 버퍼 캐시에서 찾을때는 해시 알고리즘을 이용한다.


인덱스 클러스터링 팩터
- 인덱스에 저장된 순서와 테이블에 저장된 데이터의 저장 순서가 얼마나 일치하는지를 나타내는 값 
- 최대값은 인덱스에 저장된 항목의 개수 
- 최소값은 테이블 블록의 개수

테이블 블록 수에 가까울수록 좋고, 테이블 레코드 수에 가까울수록 나쁘다.

인덱스 클러스터링 팩터가 좋으면, 테이블 액세스 단계에서 읽어들이는 블록(데이터) 가 줄어든다.
인덱스 리빌드 해도 클러스터링 팩터가 좋아지지 않는다. 테이블을 재생성 해야 한다.




인덱스에는 구성 컬럼값이 모두 NULL 인 값은 저장하지 않는다.


IOT (index organizaion table)
- Table Random 엑세스가 발생하지 않도록 테이블을 아예 인덱스 구조로 생성
- PK 순으로 정렬상태를 유지한다.
- IOT 테이블의 일반 인덱스에는 PK 컬럼도 함께 저장된다.

- MS-SQL 의 Clustered Index 와 유사한 개념


인덱스 스캔 방식

Index Range Scan
- 인덱스 루트에서 리프 블록까지 수직적 탐색을 하고, 필요한 범위만큼 수평적 탐색하는 스캔 방식
- 인덱스 선두컬럼이 조건절에 있어야 한다.

Index Unique Scan
- 수직적 탐색으로만 데이터를 찾는 스캔 방식
- 인덱스 컬럼이 모두 조건절에 있는 경우( = 조건)

Index Skip Scan
- 루트 또는 브랜치 블록에서 읽은 컬럼 값 정보를 이용해서 조건절에 부합하는 레코드를 포함할 가능성이 있는 리프 블록만 액세스
- 인덱스 선두 컬럼이 조건절에 없어도 인덱스를 활용하는 새로운 스캔 방식
- 조건절에 빠진 선행 인덱스 컬럼의 Distinct Value 개수가 적고, 후행 컬럼의 Distinct Value 개수가 많을 때 유용
(선행컬럼이 datetime 일때는 불리, 날짜값(년월일)만 있을때 유리)

- 힌트 : index_ss
- 선두 컬럼이 부등호, between, like 같은 범위 조건일때도 사용 가능
- 선두 컬럼이 in ( list ) 조건일때는 사용 불가

Index Full Scan
- 수직적 탐색 없이 인덱스 리프 블록 처음부터 끝까지 수평적으로 탐색하는 방식
- 인덱스 힌트 사용하고, 해당 인덱스의 선두컬럼이 조건절에 없는 경우 사용된다.
- 인덱스 필터 조건을 만족하는 데이터가 적어야 효과적이다.

Index Fast Full Scan
- 논리적인 인덱스 트리 구조를 무시하고, 인덱스 세그먼트 전체를 Multiblock I/O 방식으로 스캔
- 물리적으로 디스크에 저장된 순서대로 인덱스 리프 블록을 읽음.
- 결과 레코드의 순서는 실제 데이터 순서와 다르게 나옴
- 힌트 : index_ffs

Index Range Scan Descending
- Index Range Scan과 기본적으로 동일한 스캔, 인덱스를 뒤에서부터 앞쪽으로 스캔
- 힌트 : index_desc


인덱스 컬럼의 가공
- 인덱스 컬럼이 가공되면, index range scan 이 불가해 진다.
- 부정형 비교는 index range scan 이 불가하다.
- is not null 조건은 index range scan 이 불가하다.

where substr(업체명, 1, 2) = '대한'
=>
where 업체명 like '대한%'
where 월급여 * 12 = 36000000
=>
where 월급여 = 36000000 / 12




묵시적 형변환

문자와 숫자를 비교하는 경우, 자동으로 숫자로 형변환이 일어난다.

where 숫자형DeptNo = '20'
=> 숫자형DeptNo = to_number('20') 로 형변환이 일어나고, 인덱스 사용 가능
where 문자형DeptNo = 20
=> to_number(문자형DeptNo) = 20 로 형변환이 일어나고, 인덱스 사용 불가




테이블 Random 액세스

인덱스 Leaf 노드에는 ROWID 가 있어, 인덱스에 없는 컬럼을 참조시,
테이블 ROWID 로 Random 액세스를 하게 된다.

실행계획에는 아래와 같이 표시된다.

TABLE ACCESS (BY INDEX ROWID) OF '테이블명' (TABLE)



테이블 랜덤 액세스가 많아지면, 전체 테이블 스캔(Full Table Scan) 보다 느려질 수 있다.
(인덱스 손익 분기점)



배치 I/O
- 테이블 랜덤 액세스시, 버퍼에서 블록을 찾지 못하면, 디스크에서 읽게 된다.
이때, 매번 디스크에서 읽지 않고, 모았다가, 일괄로 찾아오는 방법으로 I/O 효율을 높이는 방법이다.

배치 I/O 가 수행되면, 랜덤 액세스 방식이 아래처럼 변경된다.

TABLE ACCESS BY INDEX ROWID
=>
TABLE ACCESS BY INDEX ROWID BATCHED



배치 I/O 가 수행되면, 버퍼에 없는 블록끼리 모아서 처리 되므로, 결과집합의 정렬 순서가 바뀌게 되고,

실행결과에 SORT ORDER BY 가 나타나면, 부분범위 처리가 불가능해 진다.




인덱스 튜닝

col1 이 인덱스 선두 컬럼일 때, in (list) 방식이 유리하다.

where col1 <> 3
=> where col1 in (0, 1, 2, 4, 5, 6)
where col1 between 'A' AND C
=> where col1 in ('A', 'B', 'C')



col2 가 인덱스 후행 컬럼일 때는 in(list) 보다 filter 방식이 유리하다.

where col1 = :val1 and col2 in(0, 1, 2, 4, 5, 6)
=> where col1 = :val1 and col2 <> 3




선두컬럼 "or" 조건은 union all 로 처리하는게 유리하다.

select /*+ use_concat */
from
where col1 like 'A%' or col1 like 'B%'



후행컬럼 or 조건은 union all 로 처리하는게 불리하다.

select /*+ no_expand */
from
where col1 = 'ABC' and ( col2 like 'A%' or col2 like 'B%' )






LIKE 검색

like :val || '%'

시작값으로 조회할때만 index range scan 이 가능하다.
null 허용 컬럼에서 null 값을 찾을때는 like '%' 로 변환되어, null 인 값을 못 찾게 된다.
숫자형 컬럼일때 또는 가변 컬럼일때..   123 인 값을 찾고자 해도, 앞에 123으로 시작하는 모든 데이터를 찾게 된다. (결과의 오류)
index 선두 컬럼에 대해 like 검색시 null 값으로 조회하면, 모든 index 를 찾아야 하므로, 비효율이 발생한다.



소트연산 생략

WHERE col1 = 'ABC'
ORDER BY col2, col3



실행계획에 ORDER BY 가 나타나야 하지만, 인덱스에 있으면, ORDER BY 가 생략된다.
인덱스에 2번째부터 순서대로 order by 컬럼이 나와야 한다.

인덱스 구성 : col1 + col2 + col3

소트 연산이 생략되면, 부분범위 처리가 가능해진다.





MS-SQL 인덱스 힌트

PK 인덱스 사용, PK(Clustered Index) 가 없는 테이블은 테이블 풀 스캔
select * from tablename with(index(0))

PK 인덱스 사용, PK(Clustered Index) 가 없는 테이블은 오류
select * from tablename with(index(1))

인덱스명 사용
select * from tablename with(index=table_ix1)
select * from tablename with(index(table_ix1))

테이블 풀 스캔
select *
from
where ...
option( forcescan )

select *
from tablename
where ...
option( table hint( tablename, index(table_ix1) ) )

select *
from tablename with ( forceseek(table_ix1 (col1) ) )

저작자표시 비영리 변경금지

'데이터베이스' 카테고리의 다른 글

[SQLP 요점정리] 3과목. SQL 고급활용 및 튜닝 - 5. SQL옵티마이저  (0) 2025.03.05
[SQLP 요점정리] 3과목. SQL 고급활용 및 튜닝 - 4. 조인튜닝  (0) 2025.03.04
[SQLP 요점정리] 3과목. SQL 고급활용 및 튜닝 - 2. SQL 분석도구  (0) 2025.03.02
[SQLP 요점정리] 3과목. SQL 고급활용 및 튜닝 - 1. SQL 수행 구조  (0) 2025.03.01
[SQLD/SQLP 요점정리] 2과목. SQL 기본 및 활용  (0) 2025.02.26
Posted by 헝개
데이터베이스2025. 3. 2. 14:26

[SQLP 요점정리] 3과목. SQL 고급활용 및 튜닝 - 2. SQL 분석도구

SQLP : 제3과목은 SQL 고급활용 및 튜닝 이다.

 

 

3과목. SQL 고급활용 및 튜닝

1. SQL 수행 구조
2. SQL 분석 도구
3. 인덱스 튜닝
4. 조인 튜닝
5. SQL 옵티마이저
6. 고급 SQL 튜닝
7. Lock과 트랜잭션 동시성 제어

 

 

 

 

 

예상 실행계획 표시

 

DBMS_XPLAN.DISPLAY
- 단일 SQL문에 대해 "예측" 실행계획

explain plan for
SELECT * FROM Product WHERE ProductID = 1;

select * from table(dbms_xplan.display(null, null, 'typical'));

 

 

TYPICAL 포맷

--------------------------------------------------------------------------------------
| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |         |       1 |      20 |       1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |       1 |      20 |       1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN        | PK_EMP |       1 |         |       1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("EMPNO"=7900)

14 rows selected.

 

 

ALL 포맷

--------------------------------------------------------------------------------------
| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |         |       1 |      20 |       1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |       1 |      20 |       1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN        | PK_EMP |       1 |         |       1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / EMP@SEL$1
   2 - SEL$1 / EMP@SEL$1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("EMPNO"=7900)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10]
   2 - "EMP".ROWID[ROWID,10], "EMPNO"[NUMBER,22]
 
26 rows selected.

 

 

 

DBMS_XPLAN.DISPLAY_CURSOR
- 단일 SQL문에 대해 "실제" 수행된 실행계획

 

ALTER SESSION SET STATISTICS_LEVEL = ALL;
SELECT * FROM Product WHERE ProductID = 1;
SELECT * FROM TABLE(dbms_xplan.display_cursor(null, null, 'typical');


SELECT /*+ gather_plan_statistics */ * FROM Product WHERE ProductID = 1;
SELECT * FROM TABLE(dbms_xplan.display_cursor(null, null, 'allstats last');

 

 

TYPICAL 포맷

--------------------------------------------------------------------------------------
| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |         |         |         |       1 (100)|         |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |       1 |      20 |       1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN        | PK_EMP |       1 |         |       1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("EMPNO"=7900)
 
 
20 rows selected.

 

 

ALL 포맷

--------------------------------------------------------------------------------------
| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |         |         |         |       1 (100)|         |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |       1 |      20 |       1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN        | PK_EMP |       1 |         |       1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / EMP@SEL$1
   2 - SEL$1 / EMP@SEL$1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("EMPNO"=7900)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10]
   2 - "EMP".ROWID[ROWID,10], "EMPNO"[NUMBER,22]
 
 
32 rows selected. 

 

 

 

Id
실행계획에서의 구분자
Operation
각 단계에서의 어떤 작업이 일어났는지 표시
Name
테이블명이나 index 명을 표시함
Rows
해당 쿼리 계획 단계에서 나올 것으로 예상되는 행의 수
Byte
실행 계획의 각 단계가 반환할 것으로 예상 되는 데이터의 크기를 바이트로 나타낸 수. 이 수는 행의 수와 행의 예상 길이에 따라 결정됨
Cost
CBO가 쿼리 계획의 각 단계에 할당한 비용. CBO는 동일한 쿼리에 대해 다양한 실행 경로/계획을 생성하며 모든 쿼리에 대해 비용을 할당함
​
위의 실행 계획에선 전체 비용이 13인것을 알수 있음
Time
 각 단계별 수행 시간
Query Block Name
 SQL 문장을 Query Block이라는 단위로 나눔. inline view 와 subquery 등
Outline Data
 실행 계획을 수립 하는데 필요한 내부적으로 적용된 Hint들의 목록
Predicate
 조건절
Column Projection
 실행 계획의 특정 단계에서 어떤 Column을 추출하는가를 의미

 

 

 

ADVANCED ALLSTATS LAST 포맷

 

 

Starts
말 그대로 해당 오퍼레이션이 "시작"된 횟수를 의미함
이 개념은 Nested Loops Join을 생각하시면 쉽게 이해할 수 있음 
Nested Loops Join은 선행 테이블에서 읽는 로우수만큼 후행 테이블을 탐색하는 구조임 
만일 선행 테이블에서 100건이 나온다면 후행 테이블을 100번 액세스하게됨
이럴 경우에 후행 테이블에서 대한 읽기 작업의 Starts 값이 "100"이 되는것
E-Rows / E-Bytes / E-Time
예측값
A-Rows / A-Time
실제 실행된 값

 

 

 

 

 

SQL 트레이스

- SQL Trace는 실행되는 SQL문의 실행통계를 세션별로 모아서 Trace 파일을 만듬
- SQL Trace는 세션과 인스턴스 레벨에서 SQL문장들을 분석할 수 있음
- .trc 파일로 저장되고, tkprof 유틸리티를 이용하면 분석

 

 

 

call 커서 상태에 따라 Parse, Execute, Fetch 세 개의 Call로 나누어 각각에 대한 통계정보를 보여준다.
Parse 커서를 파싱하고 실행계획을 생성하는 데 대한 통계
Execute 커서의 실행 단계에 대한 통계
Fetch 레코드를 실제로 Fetch하는데 대한 통계
count Parse, Execute, Fetch 각 단계가 수행된 횟수
cpu 현재 커서가 각 단계를 수행하는 데 소요된 시간
Elapsed Time CPU Time + Wait Time = Response시점 - Call 시점
CPU 시간에 비해 Elapsed 시간이 현저히 큰 경우는 디스크 I/O 때문이다.
disk 디스크로부터 읽은 블록 수
query Consistent 모드에서 읽은 버퍼 블록 수
current Current모드에서 읽은 버퍼 블록 수
rows 각 단계에서 읽거나 갱신한 처리건 수
Misses in library cache during parse Parse 구간에서 해당 SQL을 Library Cache에서 읽지 못하고 잃어버린 횟수
값이 1이면 Hard Parse, 0이면 Soft Parse를 의미함
Optimizer mode 옵티마이저 모드

 

 

 

 

버퍼캐시 히트율

(1 - disk / (query+current)) * 100
또는
( (query+current) -disk) / (query+current) * 100

 

 

 

Row Source Operation 통계 출력

 

 

Rows
각 수행단계에 출력된 row수
Cr
consistent 모드 블록 읽기
Pr(r)
디스크 블록 읽기
Pw(w)
디스크 블록 쓰기
Time
소요시간(us= microsecond)
Cost
Cost Based Optimizer(CBO)에서 예측한 비용
Size
리턴한 데이터 size(bytes)
Card
Cardinality, Cost Based Optimizer(CBO)에서 예측한 row수

 

 

 

 

현재 세션에 SQL 트레이스 거는 방법

 

alter session set sql_trace = true;

select 절;

=> .trc 파일이 생성됨

 

 

 

AutoTrace
- SQL Plus에서 실행계획을 가장 쉽고 빠르게 확인해 볼 수 있는 방법

 

grant plustrace to scott;
- plustrace 권한부여
set autotrace on
- SQL 실제 수행 => SQL 실행결과, 실행계획 및 실행통계 출력
set autotrace on explain
- SQL 실제 수행 => SQL 실행결과, 실행계획 출력
set autotrace on statistics
- SQL 실제 수행 => SQL 실행결과, 실행통계 출력
set autotrace traceonly
- SQL 실제 수행 => 실행계획 및 실행통계 출력
set autotrace traceonly explain
- SQL 실제 수행 X => 실행계획 출력
set autotrace traceonly statistics
- SQL 실제 수행 => 실행통계 출력

 

--------------------------------------------------------------------------------------
| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |         |       1 |      20 |       1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |       1 |      20 |       1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN        | PK_EMP |       1 |         |       1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("EMPNO"=7900)

 

 

통계

Statistics
----------------------------------------------------------
      1  recursive calls
      0  db block gets
      2  consistent gets
      0  physical reads
      0  redo size
    596  bytes sent via SQL*Net to client
    523  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

 

recursive calls
사용자의 SQL문을 실행하기 위하여 수행된 SQL 문의 수
db block gets
현재 모드(current mode)에서 버퍼 캐시로부터 읽어온 블록의 총 수
consistent gets
버퍼 캐시의 블록에 대한 일관된 읽기의 요청 횟수. 일관된 읽기는 언두 정보, 즉 롤백 정보에 대한 읽기를 요구할 수도 있으며 이들 언두에 대한 읽기도 계산됨
physical reads
물리적으로 데이터 파일을 읽어 버퍼 캐시에 넣은 횟수
redo size
해당 문이 실행되는 동안 생성된 리두의 전체 크기를 바이트 단위로 나타낸 수
byte sent via SQL*Net to client
서버로부터 클라이언트에 전송된 총 바이트 수
byte recevied via SQL*Net from client
클라이언트로부터 받은 총 바이트 수
SQL*Net roundtrips to/from client
클라이언트로(부터) 전송된 SQL*Net 메시지의 총 수. 다중 행 결과 집합으로부터 꺼내오기 위한 왕복을 포함함
sorts(memory)
사용자의 세션 메모리(정렬 영역)에서 수행된 정렬 sort_area_size 데이터베이스 매개변수에 의해 제어됨
sorts(disk)
사용자의 정렬 영역의 크기를 초과하여 디스크(임시 테이블 영역)를 사용하는 정렬
rows processed
수정되거나 select 문으로부터 반환된 행

 

 

 

 

MS-SQL Server

 

트레이스 확인 방법

set statistics profile on
set statistics io on
set statistics time on

- 구문분석 및 컴파일 시간
- 논리직/물리적 읽기 수
- 각 오퍼레이션 단계별 실행 횟수

 

 

set statistics profile on
- 실제 실행계획 표시
Rows : 각 연산자에서 만든 실제의 행 수를 보여줍니다.
Executes : 각 연산자가 몇 번 실행이 되었는지 보여줍니다
StmtText : 세부적으로 어떤 실행이 있었는지를 보여주거나, 실제로 실행된 쿼리를 보여줍니다
Argument : 수행되는 작업의 추가정보를 제공합니다. TOP 절의 행 수나, Group By 조건, Where 조건, Index Seek 혹은 Scan사항 등

 

set statistics io on
- I/O 통계 표시
테이블 'emp'. 스캔 수 1, 논리적 읽기 28, 실제 읽기 0, 페이지 서버 읽기 0, 미리 읽기 읽기 0, 페이지 서버 미리 읽기 읽기 0, lob 논리적 읽기 0, lob 실제 읽기 0, lob 페이지 서버 읽기 0, lob 미리 읽기 읽기 0, lob 페이지 서버 미리 읽기 읽기 0.

 

set statistics time on
- 시간 통계 표시
SQL Server 구문 분석 및 컴파일 시간: 
   CPU 시간 = 0ms, 경과 시간 = 0ms.

(100개 행이 영향을 받음)

 SQL Server 실행 시간: 
 CPU 시간 = 0ms, 경과 시간 = 222ms

 

 

SET SHOWPLAN_ALL ON
또는
SET SHOWPLAN_TEXT ON
- 예상 실행계획 표시

 

 

 

 

 

쿼리별로 실행계획에 나타나는 형태

SELECT distinct / UNION
HASH (UNIQUE)

GROUP BY
HASH (GROUP BY)

ORDER BY
SORT (ORDER BY)

집계함수 SUM(), COUNT()
SORT (AGGREGATE)

윈도우함수 SUM() over(partition by xx)
WINDOW (SORT)

ROWNUM 사용시
COUNT (STOPKEY)

 

 

Response Time Analysis 방법론
- 세션 또는 시스템 전체에 발생하는 병목 현상과 그 원인을 찾아 문제를 해결하는 방법
- CPU time과 Wait time을 각각 break down하면서 서버의 일량과 대기 시간을 분석

Response Time = Servic Time + Wait Time
또는
Response Time = CPU time + Queue Time

 

 

AWR (Automatic Workload Repository) 보고서
- DB 의 상태를 진단하는 정보
- DBA에게 데이터베이스 실행 시간과 관련된 스냅샷과 상세한 정보를 제공
- Statpack 을 업그레이드 해서 개발 되었다.

 

 

DB 대기 이벤트 및 통계 정보
- 시스템 통계 정보
- 데이터베이스 부하 정보
- SQL 수행 정보
- 활동 세션 정보

 

 

저작자표시 비영리 변경금지

'데이터베이스' 카테고리의 다른 글

[SQLP 요점정리] 3과목. SQL 고급활용 및 튜닝 - 4. 조인튜닝  (0) 2025.03.04
[SQLP 요점정리] 3과목. SQL 고급활용 및 튜닝 - 3. 인덱스튜닝  (0) 2025.03.03
[SQLP 요점정리] 3과목. SQL 고급활용 및 튜닝 - 1. SQL 수행 구조  (0) 2025.03.01
[SQLD/SQLP 요점정리] 2과목. SQL 기본 및 활용  (0) 2025.02.26
[SQLD/SQLP 요점정리] 1과목. 데이터 모델링의 이해  (0) 2025.02.25
Posted by 헝개

티스토리툴바