헝그리개발자

2024년 러닝 입문용으로 밸롭 윈디 R2 2켤레를 구입했다.

입문용으로 좀 신다가, 러닝용으로는 부족해서

호카와 아식스로 갈아타고,

이 신발은 헬스장용으로 용도 변경했다.

2025년 3월 신발 두쪽이 모두 앞쪽 접히는 부분이 갈라지면서, 구멍이 생겼다.

구입한지 9개월만에 구멍이 생긴것이다.

내구성이 완전 쓰레기 폐급이다.

게다가, 보통 다른 신발들은 워런티가 1년인데, 이 회사는 6개월이라고 한다.

(상품 설명에도 없다.)

재활용도 안될듯 하여, 그냥 버려야만 한다.

결론, 입문용으로라도, 이름있고 좋은 신발로 시작하자...

3과목. SQL 고급활용 및 튜닝
1. SQL 수행 구조

2. SQL 분석 도구

3. 인덱스 튜닝

4. 조인 튜닝

5. SQL 옵티마이저

6. 고급 SQL 튜닝

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

Lock
- 같은 자원을 액세스 하는 다중 트랜잭션 환경에서 데이터베이스의 일관성과 무결성을 유지하면서 순차적인 진행을 보장하는 직렬화 수단

공유 Lock (Shared Lock)
- 데이터를 읽기 위해 사용하는 Lock
- 한 자원에 대해 같은 공유 Lock 끼리는 여러개 설정이 가능하다. (동시에 읽기 가능)
- 배타적 Lock 과는 동시 진행 불가.

배타적 Lock (Exclusive Lock)
- 데이터를 변경하고자 할 때 사용하는 Lock
- 트랜잭션이 종료될때까지 유지되며, 다른 공유 Lock / 배타적 Lock 이 동시에 설정 불가하다.

블로킹 (Blocking)
- Lock 경합으로, 다른 트랜잭션에서 Lock 이 걸려서, 대기하고 있는 상태

교착상태 (Deadlock)
- 두개의 세션이 각각 Lock 을 설정한 리소스를 서로 액세스 하려고 블로킹 상태에 있는 상태



SET TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }

READ UNCOMMITTED
- 다른 트랜잭션에 의해 수정되었지만 아직 커밋되지 않은 행을 문이 읽을 수 있도록 지정

READ COMMITTED
- 다른 트랜잭션에 의해 수정되었지만 아직 커밋되지 않은 데이터를 문이 읽을 수 없도록 지정
- MS-SQL Select : Row 레코드를 읽는 동시에 Lock 을 획득하고, 다음 레코드로 이동하는 순간 Lock 이 해제된다.

REPEATABLE READ
- 다른 트랜잭션에 의해 수정되었지만 아직 커밋되지 않은 데이터를 문이 읽을 수 없도록 지정하고 현재 트랜잭션이 완료될 때까지 현재 트랜잭션이 읽은 데이터를 다른 트랜잭션이 수정할 수 없도록 지정
- MS-SQL Select : Row 레코드를 읽는 동시에 Lock 을 획득하고, 최종커밋 또는 롤백 하는 순간 Lock 이 해제된다.

SNAPSHOT
- 트랜잭션의 문이 읽은 데이터가 트랜잭션별로 트랜잭션을 시작할 때 존재한 데이터 버전과 일관성이 유지되도록 지정

SERIALIZABLE
- 가장 엄격한 격리수준. 트랜잭션이 완료될 때까지 다른 사용자는 그 영역에 해당되는 데이터에 대한 수정 및 입력이 불가능

트랜잭션의 4가지 특징 (ACID)

Lock Escalation
- 로우 레벨 Lock 이 페이지, 익스텐트, 테이블 레벨 Lock 으로 점점 확장되는 것.
- 오라클은 로우 속성으로 Lock 을 관리하므로, Lock Escalation 이 발생하지 않는다.



동시성
- DML 수행 시, ROW 단위로 배타적 Lock 이 걸린다.

오라클
- DML 문이 실행되는 동안 해당 ROW 는 update/delete 불가하다. select 는 가능.

MS-SQL
- DML 문이 실행되는 동안 해당 ROW 는 select/update/delete 불가하다.
- 제약조건이나 unique 인덱스 설정이 없으면, insert 하는 동안에 다른 insert 도 가능하다.
- insert 하는 동안에, 다른 select 절이 full scan 하게 되면, select 도 동시에 실행이 불가능하다. => index range 스캔을 하게 되면 select 가능.


TX Lock (Transaction Lock)
- 행 수준(Row-level) 잠금

TM Lock(Table Lock)
- 테이블 잠금, 테이블에 대한 구조적 변경(DDL) 이나, DML(데이터 조작 언어) 작업 시 데이터 무결성을 보장
- SELECT for UPDATE 문 에서도 TM Lock 이 설정된다.


이상현상

Dirty Read
- 다른 트랜잭션이 변경중인 데이터를 읽어서 사용했는데. 다른 트랜잭션이 rollback 되는 경우.

Non-Repeatable Read
- 한 트랜잭션 내에서 값을 두번이상 읽어서 사용하는 중간에 다른 트랜잭션이 해당값을 변경(update / delete) 해서, 값이 달라지는 경우.

Phantom Read
- 한 트랜잭션 내에서 일정 범위의 값을 두번이상 읽어서 사용하는 중간에 다른 트랜잭션이 새로운 값을 추가(insert) 함으로써, 처음 읽을때 없던 값이 나중에 생긴 경우.



비관적 동시성 제어
- 데이터를 읽는 시점부터 Lock 을 설정한다.

낙관적 동시성 제어
- 데이터를 읽는 시점에 Lock 을 설정하지 않는다.
- 다만, 읽은 데이터를 다시 읽거나, 수정하는 시점에 반드시 변경 여부를 확인한다.


SELECT FOR UPDATE
- 해당 행을 읽으면서, 하단에서 변경하기 위해 lock 을 건다.
- Lock 이 걸린 행을 만나면, Lock 이 해제될 때까지 무작정 기다린다.

SELECT FOR UPDATE WAIT n
- Lock 이 걸린 행을 만나면, n 초간 기다리고, 해제되지 않으면 SELECT 문 전체를 종료한다. (Exception)

SELECT FOR UPDATE NOWAIT
- Lock 이 걸린 행을 만나면, 즉시 SELECT 문 전체를 종료한다. (Exception)

snapshot too old 에러
- Table Full Scan 및 해시 조인을 사용하면 에러를 줄일 수 있다.
- 데이터 조회시 Order by 를 사용하면 에러를 줄일 수 있다.
- 튜닝을 통해 줄일수는 있어도 완전히 해소하기는 어렵다.
- Undo 영역의 크기를 증가시키고, commit 을 자주 수행하지 말아야 한다.

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

2. SQL 분석 도구

3. 인덱스 튜닝

4. 조인 튜닝

5. SQL 옵티마이저

6. 고급 SQL 튜닝

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

소트
- Sort 양이 많을때 중간 정렬된 집합을 Temp 영역에 저장해둔다. 이 집합을 Sort Runs 라고 한다.

Optimal 소트
- 정렬 대상 집합이 크지 않아 Sort Area 에서 정렬 작업을 완료하는 것

Onepass 소트
- 정렬  대상 집합을 디스크에 한번만 기록하는 것

Multipass 소트
- 정렬  대상 집합을 디스크에 여러번 기록하는 것


Sort 실행계획

SORT (ORDER BY) : Order By 절
SORT (GROUP BY) : Group By 절 + Order By 절
SORT (UNIQUE) : Group By 와 Unique 또는 Distinct 연산, Minus, Intersect, Union 연산
SORT (AGGREGATE) : Count, Avg, Sum, Min, Max 등의 연산
HASH (GROUP BY) : Group By 절

페이징처리 표준 패턴

First Row Min/Max
- min/max 연산을 사용할때, 조건절와 min/max 컬럼값이 모두 인덱스에 있어야 효율적이다.
- First Row Min/Max 이 안되는 경우, order by 와 rownum <= 절로 변경하는것이 효율적이다.

DML 성능에 영향을 미치는 요소
- 인덱스, 무결성제약, 조건절, 서브쿼리, redo/undo 로깅, Lock, 커밋

Array Processing기능
- 한 번의 SQL 수행으로 다량의 로우를 동시에 insert/update/delete
- 네트워크 부하를 줄이고, 데이터베이스 Call 횟수를 줄여준다.
- OLTP 시스템에서 성능 개선

Direct Path Insert
- 버퍼캐시를 거치지 않고, 작업 데이터파일에 기록
- Exclusive 모드 TM Lock 을 설정
- Freelist 를 참조하지 않고 HWM 바깥 영역에 데이터를 순차적으로 입력
- Undo 로깅을 최소화

Recursive Call 은 Parse Call / Execute Call / Fetch Call 단계에서 모두 나타날 수 있다.
Array Size 를 늘리면, Fetch Call 을 줄일 수 있다.
Array Processing 을 늘리면, Execute Call 을 줄일 수 있다.

병렬처리 Insert

인덱스 비활성화 / 활성화
alter index PK_index_name on tablename DISABLE;
alter index PK_index_name on tablename ENABLE;

Merge Into 사용법

대용량 데이터 UPDATE 튜닝
- update 구문을 truncate / insert 로 대체

어플리케이션 커서를 캐싱 하면, Parse Call 이 1번만 나타난다.
Array Size 를 늘리면, Fetch Call 이 줄어든다 : fetch call * array size = rows
Array Processing 을 하면, Execute Call 을 줄인다.


DB저장형 사용자정의 함수/프로시저
- 가상머신 상에서 실행되는 Interpreting 언어다.
- 함수가 실행될때마다 Recursive Call 이 발행한다.
- Join 대신, 함수를 사용하여 코드명 등을 조회하면, 성능이 나빠진다.

DB저장형 함수 성능개선
- Join 문으로 변경
- Deterministic 함수로 전환
- Result 캐시 사용

DB저장형 함수 Recursive Call 횟수 개선
- 1. 함수 호출을 스칼라 서브쿼리로 변환한다 (스칼라 서브쿼리 캐싱) : ( select code_nm(1, 2) from dual ) cd_name
- 2. Deterministic 함수 캐싱
- 3. Result 캐시
- 4. Native 컴파일




파티셔닝 (Partitioning)
- 테이블데이터 또는 인덱스 데이터를 나누어서 저장하는 기능
- 다른 파티션에 대량 변경/백업/복구 중에도, 조회/변경/삭제 가능

Range 파티션
- 여러 컬럼 지정 가능
- 숫자,문자,날짜 컬럼 사용 가능
- 범위에 없는 값이 들어왔을 때 오류를 방지하기 위해 MAXVALUE 파티션을 사용해야 한다.
- 신규 파티션을 추가해야 하는 관리 이슈가 있다.

List 파티션
- 단일 컬럼만 지정 가능
- 불연속적인 값의 목록으로 분할할때 사용
- 범위에 없는 값이 들어왔을 때 오류를 방지하기 위해 DEFAULT 파티션을 사용해야 한다.

Hash 파티션
- 여러 컬럼 지정 가능
- DML 경합을 분산하는데 효과적이다.
- 값의 범위가 고르지 않으면, 특정 파티션에 데이터가 몰리게 된다.
- Hash 알고리즘으로 분산이 되기 때문에, 입력공간을 못 찾는 오류는 발생하지 않는다.

Pruning
- SQL 파드파싱이나 실행시점에 조건절을 분석해서, 읽지말아야 할 파티션을 제외하는 기능
- 문자형 날짜값에 Like 조건을 사용하면 불필요한 파티션을 읽게 된다. Between 조건으로 변경하는것이 좋다.
- 문자형 날짜값에 숫자로 검색하면, 형변환이 발생해 전체 파티션을 조회하게 된다.
- 정적 Pruning / 동적 Pruning 성능 차이는 없다.

PARTITION RANGE ALL
- 파티션 컬럼을 가공하면, 파티션 Pruning 이 불가능 해, 전체 파티션을 조회 하게 된다.
- 파티션 컬럼 조건에 다른 타입의 값을 넣을경우, 강제 형변환이 발생하여, 전체 파티션을 조회 할수 있으니 주의해야 한다.

정적(Static) 파티션 Pruning
- 조건절에 파티션 키 컬럼이 상수로 들어있는 경우
- 쿼리 최적화 시점에 액세스할 파티션 번호를 알 수 있다.
- 실행계획 Pstart, Pstop 에 파티션 번호가 표시된다.

동적(Dynamic) 파티션 Pruning
- 조건절에 파티션 키 컬럼이 바인드 변수로 들어있는 경우, 다른 테이블과의 Join 조건일 경우.
- 실행 시점에 액세스할 파티션을 알 수 있다.
- 실행계획 Pstart, Pstop 에 KEY 라고 표시된다.


Partition Index
- Local Partition Index 와 Global Partition Index 로 구성된다.

Local Partition Index
- 파티션 테이블 내의 데이터로만 인덱스가 구성된다.
- Prefixed Index 와 Non-Prefixed Index 로 구성된다.
- 테이블 파티션을 재구성하면, Local Index 는 자동으로 Rebuild 되어 관리의 편의성이 있다.

create index dept_local_ix1 on dept(deptno) LOCAL;


Local Prefixed Index
- 파티션 컬럼이 인덱스 인 경우 (인덱스가 여러 컬럼일때 선두 컬럼)

Local Non-Prefixed Index
- 인덱스된 컬럼이 파티션 컬럼이 아닌 경우 (인덱스가 여러 컬럼일때, 선두 컬럼이 파티션 컬럼이 아닐 때)


Global Partition Index
- 파티션 테이블과는 별도로 인덱스 파티션 규칙으로 파티션을 구성한다.
- Non-Partition Table 에도 생성 가능하다.
- Prefixed Index 만 존재한다. (Non-Prefixed Index 불가)
- 테이블 파티션을 재구성하면, Index Unusable 상태가 되어, Rebuild 가 필요하다.

create index sales_global_ix1 on sales (cust_nm) GLOBAL
partition by hash (cust_nm)
partitions 4;



Index Unusable(IU) 상태
- Partition Index / Non-Partition Index 가 사용불가 상태가 되는 경우.
- IU 파티션을 사용하려고 하면 오류가 발생한다. IU 파티션을 Rebuild 해야 한다.
- 1. Direct Path Load 시 index 가 해당 테이블 data 보다 이전것인 경우
- 2. Alter Table Move Partiton : ROWID 를 변화시키는 작업
- 3. Alter Table Truncate Partition / Drop Partition 과 같은 테이블의 행을 지우는 작업
- 4. Alter Table Split Partition
- 5. Alter Index Split Partition



배치(Batch) 프로그램
- 일련의 대량 작업을 묶어서 일괄 처리

배치프로그램 튜닝
- 전체 처리속도 최적화에 목표
- Array Processing 을 활용
- 임시 테이블을 활용
- 병렬처리를 적절히 활용. 단, 필요 이상의 병렬도(DOP) 를 지정하지 않아야 한다.

병렬처리
- 병렬 처리란 SQL문이 수행해야 할 작업 범위를 여러 개의 작은 단위로 나누어 여러 프로세스가 동시에 처리

QC(Query Coordinator)
- 병렬 SQL문을 발행한 세션
- 병렬 서버 집합을 할당
- 각 병렬 서버에게 작업을 할당
- 병렬로 처리하도록 사용자가 지시하지 않은 테이블은 QC가 처리

Intra-Operation Parallelism
- 병렬 처리에서 서로 배타적인 범위를 독립적으로 동시에 처리하는 것

Inter-Operation Parallelism
- 프로세스 간의 통신을 뜻한다.
- 다른 서버집합에 분배하거나 정렬된 결과를 QC에서 전송하는 작업을 병렬로 동시에 진행하는 것
- 병렬도(DOP) 의 2배의 프로세스가 필요하고, 병렬도(DOP) 의 제곱만큼 파이프 라인(테이블 큐)이 필요

S->P (Parallel from Serial)
- QC가 읽은 데이터를 테이블 큐를 통해 모든 병렬 서버 프로세스에게 전송
- BROADCAST 라고 표시하며, Inter-Operation Parallelism 에는 속하지 않는다.
- 데이터 크기가 작을수록 유리

P->S (Parallel to Serial)
- 각 병렬 서버 프로세스가 처리한 데이터를 QC에게 전송하는 것
- Inter-Operation Parallelism 에 속한다.
- order by가 있을 때는 QC(ORDER)
- order by가 없을 때는 QC(RANDOM)

P->P (Parallel to Parallel) 오퍼레이션
- 한 서버 집합이 반대편 서버 집합에 데이터를 전송하는 것
- 데이터를 재분배하는 오퍼레이션으로 데이터를 정렬하거나 그룹핑, 조인을 위한 동적으로 파티셔닝을 할 때 발생
- Inter-Operation Parallelism 에 속한다.

PCWP (Parallel Combined with Parent)
- 한 서버 집합이 현재 스텝과 그 부모 스텝을 모두 처리
- Intra-Operation Parallelism 에 속한다.

PCWC (Parallel Combined with Child)
- 한 서버 집합이 현재 스텝과 그 자식 스텝을 모두 처리
- Intra-Operation Parallelism에 속한다.


parallel_index
- 블록 기반 Granule : Full Table Scan 일때는 병렬도(DOP) 를 파티션 사이즈보다 크게 지정할 수 있다.
- 파티션 기반 Granule : Index Range Scan 일때는 병렬도(DOP) 를 파티션 사이즈만큼 지정해야 효율적이다.


Hint

PARALLEL
- 병렬 처리 하도록 한다.
- PARALLEL(table, dop)
- 반드시 FULL 힌트를 같이 사용한다.

PARALLEL_INDEX
- 인덱스 범위 스캔을 병렬로 처리
- PARALLEL_INDEX(table, index, /* dop */)
- 반드시 INDEX 또는 INDEX_FFS 힌트를 같이 사용한다.


group by / order by / hash join 을 사용하면, 병렬프로세스가 dop 의 2배 만큼 필요하다.



PQ_DISTRIBUTE
- 병렬 Join 시에 데이터 분배 방식을 직접 결정
- PQ_DISTRIBUTE(inner_table, outer_distribution, inner_distribution)

pq_distribute(inner_table, hash, hash)
- 병렬조인을 위해 해시함수를 사용하도록 지정
- 실행계획에 PX SEND HASH 가 나타난다.

pq_distribute(inner_table, broadcast, none)
- outer table 의 모든 데이터를 병렬서버에 전송
- outer table 의 데이터 크기가 작을때 사용
- 실행계획에 PX SEND BROADCAST 가 나타난다.

pq_distribute(inner_table, none, broadcast)
- inner table 의 모든 데이터를 병렬서버에 전송
- inner table 의 데이터 크기가 작을때 사용
- 실행계획에 PX SEND BROADCAST 가 나타난다.

pq_distribute(inner_table, partition, none)
- Partial Partition-wise Join
- inner table 이 조인 키 컬럼에 대해 파티셔닝 돼 있을 때 사용
- 실행계획에 PX SEND PARTITION 이 나타난다.

pq_distribute(inner_table, none, partition)
- Partial Partition-wise Join
- outer table 이 조인 키 컬럼에 대해 파티셔닝 돼 있을 때 사용
- 실행계획에 PX SEND PARTITION 이 나타난다.

pq_distribute(inner_table, none, none)
- Full Partition-wise Join
- join 되는 두 테이블 모두 Join Key 컬럼으로 파티션되어 있는 경우에 사용
- 실행계획에 PX PARTITION RANGE ALL 이 나타난다.
- 하나의 서버 집합만 필요.



ROWNUM
- 병렬처리 쿼리에 ROWNUM 을 사용하면, 병렬프로세스에 병목이 발생한다.

rownum 을 row_number() 바꿔서 사용해야 한다.

데이터 복제 기법


데이터 복제를 이용한 소계 구하기
- 카티션 곱(Cartesian Product) 을 이용

unpivot (열데이터를 행데이터로 변환)

pivot (행데이터를 열데이터로 변환)

LISTAGG

행을 문자열로 합치는 기능 (pivot)

LISTAGG([합칠 컬럼명], [구분자]) WITHIN GROUP(ORDER BY [정렬 컬럼명])
(하단에 group by 사용시)

LISTAGG([합칠 컬럼명], [구분자]) WITHIN GROUP(ORDER BY [정렬 컬럼명]) OVER(PARTITION BY [그루핑 컬럼명])
(하단에 group by 미사용시)





분석함수 : 누적값

sum(매출금액) over(partition by 지점코드 order by 판매월
    range between unbounded preceding and current row)
) 누적매출

부등호조인 : 누적값

select A.지점코드, A.판매월, min(A.매출금액) 매출금액
   , sum(B.매출금액) 누적매출
from 월별지점매출 A, 월별지점매출 B
where A.지점코드 = B.지점코드
   and A.판매월 >= B.지점코드
group by A.지점코드, A.판매월
order by A.지점코드, A.판매월



with 구문 (CTE)

materialize 방식
- 임시 테이블을 사용하여 반복 재사용, 실행계획에 TEMP TABLE TRANSFORMATION 이 나타남.
- 사이즈가 큰 데이터를 읽어 GROUP BY, 조인 등으로 집합크기를 줄일 수 있을때 사용.
- /*+ materialize */

inline 방식
- 참조 횟수만큼 런타임에서 반복 실행됨
- /*+ inline */