EC2 + Docker Compose 기반 GitHub Actions CI/CD 자동배포 플로우

이번 프로젝트에서의 배포 자동화는 develop 브랜치에 PR 시 GitHub Actions가 Docker 이미지를 새로 빌드하고, Docker Hub에 push한 뒤, EC2 서버에서 Docker Compose를 통해 최신 이미지로 재배포하는 구조로 구성해 보았다.

GitHub Actions를 선택한 이유

1. GitHub PR 흐름과 바로 연결됨

우리는 프로젝트에서 이미 GitHub를 통해 코드를 관리하고 있었음.

PR 생성 → CI 테스트
develop merge → Docker 빌드/푸시 → EC2 배포

이 플로우를 코드 관리하는 환경인 Github에서 그대로 사용할 수 있기에 편할 거 같았다.

2. 별도 서버 운영이 필요 없음

Jenkins 나 다른 자동화 CI/CD 환경을 비교해 생각해봤을 때 우리의 프로젝트 규모에 맞으려면 서버를 띄우지 않는게 맞다고 판단했다. GitHub Actions는 GitHub-hosted runner를 쓰면 GitHub가 실행 환경을 제공해줘서, 별도 CI 서버를 직접 운영하지 않아도 됨.

• 깃허브 호스팅 Docs

3. Secret 키 관리가 편함

Docker Hub 계정, EC2 SSH 키, 서버 IP 같은 민감한 값은 GitHub Secrets에 넣고 workflow에서 참조하면 됨. 그래서 코드에 비밀번호나 pem 키를 직접 올리지 않고도 배포 자동화를 구성할 수 있었음.

• 깃허브 시크릿 키 Docs

CI/CD 자동화 환경세팅 중 발생한 이슈들

1. Docker Hub 이미지 이슈

처음 docker compose pull을 했을 때 not found 에러가 발생했다. docker-compose.yml에 image: jinsungzz/hoppin만 적혀 있어서 기본 태그가 latest로 해석됐다. 그리고 Docker Hub에도 아직 이미지가 없었다.

해결 방안

• docker hub에 이미지 태그를 staging으로 통일 시켜 hoppin repository를 생성했다.

  image: jinsungzz/hoppin:staging

2. Dockerfile 이슈

먼저 프로젝트 루트에 Dockerfile을 만들었다.

FROM eclipse-temurin:17-jdk

ARG JAR_FILE=build/libs/*.jar
COPY ${JAR_FILE} app.jar

ENTRYPOINT ["java", "-jar", "/app.jar"]

처음 빌드할 때 아래 에러가 발생했다.

COPY build/libs/*.jar app.jar
lstat /build/libs: no such file or directory

해결 방안

• Dockerfile은 build/libs/*.jar가 있다고 가정한다. 그런데 ./gradlew bootJar를 먼저 안 해서 jar가 없었음

  ./gradlew clean bootJar
  docker build -t jinsungzz/hoppin:staging .

  jar 파일 생성 후 빌드하도록 수정했다.

3. Mac 아키텍쳐 이슈

로컬에서 이미지를 push한 뒤 EC2에서 이미지를 pull 했더니 아래 에러가 발생했다.

no matching manifest for linux/amd64

해결 방안

나는 맥북 M2 아키텍쳐 모델을 사용 중이다.

• M2의 기본 빌드는 amd64
• 에러를 보면 EC2의 빌드는 linux/amd64

• M2와 EC2의 빌드를 통일해 주어야 한다.

  docker buildx build --platform linux/amd64 -t jinsungzz/hoppin:staging --push .

  이 코드를 통한 빌드로 로컬에서 push 할 때 이미지를 linux/amd64 아키텍쳐로 수정 후 빌드하게헀다.

4. Github Secrets 이슈

나는 분명 Secrets 키 값을 입력했는데 Actions 로그인했는데 아래 에러가 발생했다.

Must provide --username with --password-stdin
Run echo "" | docker login -u "" --password-stdin

시크릿 값이 비어있다는 에러이다. 그래서 다시 시크릿 값을 잘못 넣었나? 생각해 계속 수정해서 넣어도 똑같은 에러를 받았다. 원초적으로 값이 비어있다는 것에 관점을 두고 생각해봤다.

• 원래의 입력은 아래처럼 하나의 시크릿에 모든 환경변수를 다 넣었다.

해결 방안

Name과 Secret에 1:1로 환경변수를 적용해 주었다. 위의 STAGING_SSH_KEY 값에는 .pem의 키 값을 넣어줘야되는데

-----BEGIN OPENSSH PRIVATE KEY-----
중간 키 내용 전체
-----END OPENSSH PRIVATE KEY-----

이런 형태로 되어 있다. 이때 BEGIN / END 내용까지 모두 시크릿 값에 넣어줘야 한다!!!!!

CI scripts

1. 언제 실행되는가 ?

pr 시 브랜치가 main, develop인지 확인하기 (pr 브랜치 기준으로 실행)

on:
  pull_request:
    branches: [ develop, main ]

2. 실행환경

Github가 제공하는 ubuntu 서버에서 CI가 실행됨

runs-on: ubuntu-latest

3. 코드 가져오기

PR에 올라온 코드를 GitHub Actions 실행 환경으로 가져옴

- name: Checkout
  uses: actions/checkout@v4

4. 파일 구조 확인

파일이 실제로 있는지 디버깅

- name: Show files
  run: find . -maxdepth 3 -type f | sort

5. JDK 17 세팅

Spring Boot 빌드에 필요한 Java 17을 설치함

 - name: Set up JDK 17
   uses: actions/setup-java@v4
   with:
      distribution: temurin
      java-version: 17

6. gradlew 실행 권한 부여

리눅스 환경에서는 gradlew에 실행 권한이 없으면 실행이 안 돼서 ./gradlew 실행 전에 권한을 부여했음

 - name: Grant execute permission
   run: chmod +x ./gradlew

7. 테스트 실행

 - name: Run tests
   run: ./gradlew clean test

8. 빌드 확인

7번에 테스트를 진행했기 때문에 -x test로 테스트를 제외하고 빌드만 확인함

- name: Build check
  run: ./gradlew build -x test

CI 코드

name: PR to Develop CI

on:
  pull_request:
    branches: [ develop, main ]

jobs:
  test:
    runs-on: ubuntu-latest

    steps:
      - name: Checkout
        uses: actions/checkout@v4

      - name: Show files
        run: find . -maxdepth 3 -type f | sort

      - name: Set up JDK 17
        uses: actions/setup-java@v4
        with:
          distribution: temurin
          java-version: 17

      - name: Grant execute permission
        run: chmod +x ./gradlew

      - name: Run tests
        run: ./gradlew clean test

      - name: Build check
        run: ./gradlew build -x test

CD scripts

1. 언제 실행되는가?

develop 브랜치에 push가 발생하면 실행됨 즉 PR이 merge돼 develop이 업데이트되면 자동 배포가 시작됨

on:
  push:
    branches:
      - develop

2. 실행환경

GitHub가 제공하는 Ubuntu runner에서 실행됨 GitHub Actions 서버에서 빌드하고 이미지를 push함

runs-on: ubuntu-latest

3. 코드가져오고 JDK 17 세팅

현재 develop 브랜치의 코드를 Actions runner로 가져와 스프링 부트를 실행하기 위해 JDK 17을 세팅함

- name: Checkout
  uses: actions/checkout@v4

- name: Set up JDK
  uses: actions/setup-java@v4

4. gradlew 실행 권한 부여 후 jar 빌드/검증

Linux 환경에서 ./gradlew를 실행할 수 있도록 권한을 부여하고 Spring Boot 실행 jar 파일을 만들고 검증함 검증 파일은 개발한 파일 넣으면 됨

- name: Grant execute permission
  run: chmod +x ./gradlew

- name: Build jar
  run: ./gradlew clean bootJar -x test

- name: Verify jar contents
  run: |
      jar tf build/libs/*.jar | grep BOOT-INF/classes/application
      jar tf build/libs/*.jar | grep -E "AiController|AuthController|MeController"

5. Docker Hub 로그인 및 Docker Buildx 세팅

도커 허브 로그인 후 환경을 맞추기위해 어느 환경에서도 빌드해도 상관없는 Buildx 로 세팅

 - name: Docker login
   run: echo "${{ secrets.DOCKER_PASSWORD }}" | docker login -u "${{ secrets.DOCKER_USERNAME }}" --password-stdin

 - name: Set up Docker Buildx
   uses: docker/setup-buildx-action@v3

6. Docker 이미지 빌드 후 push + 이미지 내부 검증

검증 시 개발한 파일을 넣으면 되는데 일단 임시로 넣어둠

docker buildx build \
  --no-cache \
  --platform linux/amd64 \
  -t jinsungzz/hoppin:staging \
  --push .

- name: Verify docker image contents
  run: |
      docker run --rm --entrypoint sh jinsungzz/hoppin:staging -c 'jar tf /app.jar | grep BOOT-INF/classes/application'
      docker run --rm --entrypoint sh jinsungzz/hoppin:staging -c 'jar tf /app.jar | grep -E "AiController|AuthController|MeController"'

• --no-cache : Docker 캐시 사용 안 함
• --platform linux/amd64 : EC2에서 실행 가능한 amd64 이미지로 빌드
• -t jinsungzz/hoppin:staging : 이미지 이름과 태그 지정
• --push : 빌드 후 Docker Hub에 바로 push
• . : 현재 프로젝트 루트의 Dockerfile 사용

7. EC2 배포에 사용되는 명령어

- name: Deploy to staging EC2
  uses: appleboy/ssh-action@v1.0.3

GitHub Actions가 EC2에 SSH로 접속해서 배포 명령을 실행함 접속 정보는 Secrets에서 가져옴

script: |
    echo "${{ secrets.DOCKER_PASSWORD }}" | docker login -u "${{ secrets.DOCKER_USERNAME }}" --password-stdin
    cd /home/ubuntu/app

EC2에서도 Docker Hub에서 private/public 이미지를 pull할 수 있도록 로그인하고, 배포 디렉토리로 이동

docker compose down
docker rm -f hoppin 2>/dev/null || true
docker image rm jinsungzz/hoppin:staging 2>/dev/null || true
docker system prune -af

이걸 넣은 이유는 배포했는데 이전 이미지/컨테이너가 계속 남아서 안 바뀌는 문제를 막기 위해 이전 컨테이너와 이미지를 정리하는 것임

docker network inspect hoppin-net >/dev/null 2>&1 || docker network create hoppin-net

docker pull jinsungzz/hoppin:staging

docker compose up -d --force-recreate

네트워크 확인/생성 + 최신 이미지 pull + Docker Compose 재기동

CD 코드

name: Deploy Staging

on:
  push:
    branches:
      - develop

jobs:
  deploy:
    runs-on: ubuntu-latest

    steps:
      - name: Checkout
        uses: actions/checkout@v4

      - name: Set up JDK
        uses: actions/setup-java@v4
        with:
          java-version: '17'
          distribution: 'temurin'

      - name: Grant execute permission
        run: chmod +x ./gradlew

      - name: Build jar
        run: ./gradlew clean bootJar -x test

      - name: Verify jar contents
        run: |
          jar tf build/libs/*.jar | grep BOOT-INF/classes/application
          jar tf build/libs/*.jar | grep -E "AiController|AuthController|MeController"

      - name: Docker login
        run: echo "${{ secrets.DOCKER_PASSWORD }}" | docker login -u "${{ secrets.DOCKER_USERNAME }}" --password-stdin

      - name: Set up Docker Buildx
        uses: docker/setup-buildx-action@v3

      - name: Build and Push Docker image
        run: |
          docker buildx build \
            --no-cache \
            --platform linux/amd64 \
            -t jinsungzz/hoppin:staging \
            --push .

      - name: Verify docker image contents
        run: |
          docker run --rm --entrypoint sh jinsungzz/hoppin:staging -c 'jar tf /app.jar | grep BOOT-INF/classes/application'
          docker run --rm --entrypoint sh jinsungzz/hoppin:staging -c 'jar tf /app.jar | grep -E "AiController|AuthController|MeController"'

      - name: Deploy to staging EC2
        uses: appleboy/ssh-action@v1.0.3
        with:
          host: ${{ secrets.STAGING_HOST }}
          username: ${{ secrets.STAGING_USER }}
          key: ${{ secrets.STAGING_SSH_KEY }}
          script: |
            echo "${{ secrets.DOCKER_PASSWORD }}" | docker login -u "${{ secrets.DOCKER_USERNAME }}" --password-stdin
            cd /home/ubuntu/app

            docker compose down
            docker rm -f hoppin 2>/dev/null || true
            docker image rm jinsungzz/hoppin:staging 2>/dev/null || true
            docker system prune -af

            docker network inspect hoppin-net >/dev/null 2>&1 || docker network create hoppin-net

            docker pull jinsungzz/hoppin:staging
            docker compose up -d --force-recreate

위 workflow는 자동 배포 파이프라인이다

develop 업데이트 → jar 빌드 → Docker 이미지 빌드 → Docker Hub push → EC2 pull → Docker Compose 재실행

배포 환경에서의 소셜 로그인

로컬에서는 localhost 기준으로 Redirect URI를 등록하면 소셜 로그인을 테스트할 수 있다. 하지만 실제 배포 환경 소셜로그인을 사용하기 위해서 각 플랫폼마다 요구하는 조건이 있다. 이번 프로젝트에서는 네이버, 카카오, 구글 로그인을 붙이면서 그 조건들의 차이를 확인했다.

먼저 Spring Security의 Oauth2를 사용해 리다이렉트(콜백) URL을 {BaseURL}/login/oauth2/code/{registrationId} 로 설정했다.

인증/검증은 JWT accessToken사용 중이다.

1. Naver

- Naver Developers 사이트

위 사이트에 들어가서 먼저 Application을 등록해야 한다. 등록하면 주는 클라이언트 ID, 시크릿 키는 API 호출 시 환경변수 등록을 위해 저장해 준다.

등록 후 Redirect URL이 가장 중요한데 나는 로컬 테스트용 하나 배포용 하나 이렇게 등록했다. 도메인까지 입력 후 뒤에 /login/oauth2/code/naver을 추가해준다.

네이버는 배포환경에서 소셜로그인을 사용하기 위해서는 검수 요청을 받아야한다. 웹 사이트의 로그인이 완료되기까지의 플로우를 캡쳐해서 네이버 개발자 센터에 검수 받으면 된다.

네이버 개발자 검수 요청 가이드

2. Google

- Google Cloud 사이트 위 사이트 들어가서 새 프로젝트를 생성한다. OAuth 2.0 클라이언트 ID를 만들어야되는데 API 및 서비스 -> 사용자 인증 정보 들어가서 만들면 된다. 여기서도 구글 클라이언트 아이디랑 시크릿 키 주니까 잘 저장해 준다.

여기서도 Redirect URL 가장 중요한데 구글에서는 특히나 중요하다. 구글은 도메인을 ip주소로 입력하면 리다이렉션을 허용해주지 않는다.

예를 들어 EC2로 배포 시 x.x.x.x:8080 이라는 도메인을 주는데 이렇게 입력하면 안되고 도메인 주소를 구매 후 리다이렉션 창에 입력해야 승인해준다.

우리는 가비아에서 도메인 하나를 구매 후 입력했다. (로컬용 하나, 배포용 하나) 마찬가지로 도메인 + /login/oauth2/code/google

3. Kakao

- 카카오 개발자 사이트 카카오가 소셜로그인 중에서는 가장 간단하다. 얘는 위 사이트 들어가서 앱 만들고 비즈앱 등록하면 사실상 끝이다.

먼저 앱을 만들고 비즈앱을 등록한다. 그리고 앱 -> 플랫폼 키를 들어가서 클라이언트 아이디와 시크릿 키를 저장한다.

여기도 리다이렉트 URL이 가장 중요하다. 도메인 + /login/oauth2/code/kakao 추가해주면 된다.

카카오는 또 하나 필요한게 카카오 로그인 사용 설정을 ON으로 바꿔줘야 한다.

소셜로그인 구현 플로우

1. 프론트 로그인 버튼 클릭 ↓
2. 프론트가 백엔드 로그인 시작 URL로 이동 https://api.musicpeak.site/oauth2/authorization/{registrationId} ↓
3. Spring Security가 소셜 로그인 페이지로 redirect ↓
4. 사용자가 플랫폼에서 로그인/동의 ↓
5. 플랫폼이 백엔드 callback URL로 code 전달 https://api.musicpeak.site/login/oauth2/code/{registrationId} ↓
6. Spring Security가 code로 플랫폼 access token 요청 ↓
7. Spring Security가 플랫폼 사용자 정보 조회 ↓
8. CustomOAuth2UserService 실행
   • 기존 회원인지 확인
   • 없으면 회원가입
   • musicianId를 OAuth2User attributes에 넣음 ↓
9. OAuth2SuccessHandler 실행
   • musicianId 꺼냄
   • JWT accessToken/refreshToken 생성
   • 쿠키에 저장 ↓
10. 백엔드가 프론트 성공 페이지로 redirect https://www.musicpeak.site/auth/success ↓
11. 프론트 AuthSuccess 페이지에서 /api/me 호출 credentials: "include" ↓
12. 백엔드 JwtAuthenticationFilter가 쿠키의 accessToken 검증 ↓
13. /api/me 성공 응답 ↓
14. 프론트가 메인페이지로 이동 https://www.musicpeak.site

뮤지션이 자신의 음악과 콘텐츠를 효과적으로 홍보할 수 있도록 돕는 팀 프로젝트를 진행 중이다. 홍보 페이지에는 활동명, 곡 제목, 발매일, 스트리밍 링크, 소개 문구뿐 아니라 대표 이미지도 함께 노출되어야 했다. 사용자가 직접 이미지를 업로드하고, 이를 안정적으로 저장한 뒤 홍보 페이지에 노출할 수 있는 구조가 필요했다.

내가 했던 고민

• 서버 로컬 디스크에 저장하면 배포 환경에서 파일 유실 가능성은 없는가?
• 이미지 요청이 많아질수록 백엔드 서버 부하가 커지지 않는가?
• 여러 사용자가 동시에 업로드해도 안정적인가?
• 사용자가 이미지 선택 후 취소하면 불필요한 파일이 남지 않는가?

이러한 이유로 이미지 저장소를 애플리케이션 서버와 분리하고, 확장성과 운영 안정성이 높은 AWS S3를 선택하게 되었다. 또한 단순 업로드가 아니라 Presigned URL 방식을 적용해 프론트엔드가 S3에 직접 업로드하도록 설계했다.

AWS 내에서의 S3 설정

S3를 사용하기 위해 해줘야되는 설정들이 많은데 이거 하는데만 한시간은 넘게 걸렸다.

1. S3 버킷 생성

2. CORS 설정

3. IAM 사용자 권한

4. Access Key 발급

IAM → Users → 사용자 선택 → Security credentials → Access keys → Create access key

발급받는 값:

AWS_ACCESS_KEY_ID=... AWS_SECRET_ACCESS_KEY=...

5. 환경변수 설정

로컬 .env 또는 배포 서버 환경변수:

AWS_ACCESS_KEY_ID=발급받은_ACCESS_KEY AWS_SECRET_ACCESS_KEY=발급받은_SECRET_KEY AWS_S3_BUCKET=버킷 이름 AWS_S3_BASE_URL=버킷 url

cloud:
  aws:
    region: ap-northeast-2
    s3:
      bucket: ${AWS_S3_BUCKET}
      base-url: ${AWS_S3_BASE_URL}

6. 배포 서버 Docker 환경변수 주입

이미지 업로드 플로우

S3 Configuration / Service

• S3Config

  S3 presigned URL을 발급할 수 있는 S3Presigner를 Spring Bean으로 등록

  @Configuration
  public class S3Config {
  
    @Value("${cloud.aws.region}")
    private String region;
  
    @Bean
    public S3Presigner s3Presigner() {
        return S3Presigner.builder()
                .region(Region.of(region))
                .credentialsProvider(DefaultCredentialsProvider.create())
                .build();
    }
  }

• S3Service

  프론트가 S3에 직접 이미지를 업로드할 수 있도록, 30분짜리 임시 업로드 URL을 발급하는 서비스 코드

@Service
@RequiredArgsConstructor
public class S3ImageService {

    private static final Set<String> ALLOWED_EXTENSIONS =
            Set.of("jpg", "jpeg", "png", "webp");

    private final S3Presigner s3Presigner;

    @Value("${cloud.aws.s3.bucket}")
    private String bucket;

    @Value("${cloud.aws.s3.base-url}")
    private String s3BaseUrl;

    public PresignedUrlResponse createMusicPromotionImageUploadUrl(String originalFilename) {
        String extension = extractExtension(originalFilename);
        String contentType = resolveContentType(extension);

        String imageKey = "music-promotions/" + UUID.randomUUID() + "." + extension;

        PutObjectRequest putObjectRequest = PutObjectRequest.builder()
                .bucket(bucket)
                .key(imageKey)
                .contentType(contentType)
                .build();

        PutObjectPresignRequest presignRequest = PutObjectPresignRequest.builder()
                .signatureDuration(Duration.ofMinutes(30))
                .putObjectRequest(putObjectRequest)
                .build();

        PresignedPutObjectRequest presignedRequest =
                s3Presigner.presignPutObject(presignRequest);

        String imageUrl = s3BaseUrl + "/" + imageKey;

        return new PresignedUrlResponse(
                presignedRequest.url().toString(),
                imageKey,
                imageUrl
        );
    }

    private String extractExtension(String filename) {
        if (filename == null || !filename.contains(".")) {
            throw new IllegalArgumentException("파일 확장자가 없습니다.");
        }

        String extension = filename.substring(filename.lastIndexOf(".") + 1).toLowerCase();

        if (!ALLOWED_EXTENSIONS.contains(extension)) {
            throw new IllegalArgumentException("지원하지 않는 이미지 형식입니다.");
        }

        return extension;
    }

    private String resolveContentType(String extension) {
        return switch (extension) {
            case "jpg", "jpeg" -> "image/jpeg";
            case "png" -> "image/png";
            case "webp" -> "image/webp";
            default -> throw new IllegalArgumentException("지원하지 않는 이미지 형식입니다.");
        };
    }
}

1. 사용자가 이미지 선택하면 프론트에서는 미리보기만 보여준다.

** -> 이때 S3 업로드는 하지 않는다.**

2. 사용자가 “홍보 만들기” 버튼을 누르면 먼저 업로드 URL을 발급받는다.

POST /api/uploads/music-promotion-image?filename=파일명.jpg

응답:

{
  "uploadUrl": "S3 업로드용 임시 URL",
  "imageKey": "music-promotions/uuid.jpg",
  "imageUrl": "https://hoppin-s3-bucket.s3.ap-northeast-2.amazonaws.com/music-promotions/uuid.jpg"
}

3. 받은 uploadUrl로 S3에 PUT 업로드한다.

await fetch(uploadUrl, {
  method: "PUT",
  headers: {
    "Content-Type": file.type
  },
  body: file
});

4. 업로드 성공 후 홍보 생성 API를 호출한다.

POST /api/music-promotions

{
  "activityName": "첫 싱글 발매 프로모션",
  "instagramAccount": "@artist",
  "songTitle": "Blue Night",
  "releaseDate": "2026-04-25",
  "streamingLinks": [
    {
      "url": "https://musicpeak.site"
    }
  ],
  "imageUrl": "위에서 받은 imageUrl",
  "shortDescription": "첫 싱글 발매 홍보입니다."
}

정리

• 이미지 선택 시 업로드 X
• 홍보 만들기 클릭 시 업로드 URL 발급 → S3 PUT 업로드 → 홍보 생성 API 호출

KT x Goorm 백엔드 부트캠프

☁️ 해커톤 참여

이번 4월 4일부터 4월 5일까지 강남 구름스퀘어에서 진행된 해커톤에 참여했다. 짧은 시간 안에 아이디어를 실제 서비스 형태로 구현해 보는 경험을 해보고 싶었고, 혼자 개발할 때와는 다른 협업 환경에서 얼마나 빠르게 문제를 정의하고 해결할 수 있는지를 직접 느껴보고 싶었다.

☁️ 해커톤 일정

13시에 도착하니 너무 많아서 당황했다. 한 100명은 넘게 모였다. 도착하고 30분 정도 지나니 개화식과 팀별 소개하는 시간을 갖고 개발하는 자리로 이동했다.

여긴 내 개발 자리다. 팀원별로 모여있고 인당 모니터 한개가 주어진다. 여기서 첫 스프린트를 6시까지 진행하고 석식을 먹으러 갔다.

도시락을 하나씩 주셨고 일단 돈가스가 아닌 닭돈가스 느낌이었는데 소스가 맛있었음. 도시락은 많이 남아서 먹고싶으면 더 먹어도 됐다. 뭔가 스프린트 시간이 너무 순식간에 지나가 배가 딱히 고프지 않아서 이것도 남겼음.. 이후 다시 복귀해서 10시까지 2차 스프린트 후 야식타임을 가졌다. 나는 이때까지만해도 개발에 이슈가 없어서 별 사고없이 잘 마무리될 줄 알았다 ㅎㅎ.

야식은 햄버거였는데 나는 저녁을 조금만 먹어서 굉장히 배고픈 상태였다. 다들 새벽이라 입맛이 없었는지 얼마 안먹어서 햄버거가 많이 남았다. 그래서 석식시간에 햄버거를 두개 먹고 하나는 가져가서 개발하면서 먹었다.

이제 이슈가 발생하는데.... 석식먹고 11시였는데 이때부터 이 문제를 멘토링전까지 해결하지 못하게 된다. 그 이슈에 대해서 적어보겠다.

이번 해커톤의 가장 큰 이슈

이번 해커톤에서 가장 오래 붙잡고 있었던 문제는 AI 서버 연동이었다. 우리 서비스에서는 Spring 서버와 Python 기반 AI 서버를 연결해야 했는데, 이 과정이 생각보다 훨씬 쉽지 않았다. Python 쪽 AI를 담당하신 분이 모델이나 분석 자체는 잘 알고 계셨지만, 서버를 띄우고 외부 요청을 받아서 처리하는 방식에는 익숙하지 않으셔서, 단순히 Python 코드를 실행하는 것과 API 서버를 열어 통신하는 것은 완전히 다른 문제라는 점부터 같이 맞춰가야 했다.

나는 Spring에서 DTO를 JSON 형태로 보내고, Python에서는 FastAPI 같은 서버를 띄워 그 요청을 받아 분석한 뒤 다시 JSON으로 응답하는 구조를 생각하고 있었다. 그런데 실제 구현 단계에서는 localhost의 의미, 포트 설정, 어느 컴퓨터를 기준으로 요청을 보내는지, URL 뒤에 어떤 경로를 붙여야 하는지 같은 기본적인 부분에서 계속 막혔다.

결국 이 문제 하나로 거의 4시간 가까이 시간을 썼다. 계속해서 설정을 바꾸고, 요청 형식을 바꿔 보고, 포트를 바꿔 가며 시도했지만, 원하는 응답이 제대로 오지 않았다. 그 시간을 돌이켜 보면 단순히 기술이 부족했다기보다, 문제의 핵심 원인을 정확히 좁히지 못했던 것이 더 컸던 것 같다. 이것저것 다 의심하고 있었지만 정작 어디가 본질적인 문제인지 파악하지 못했던 것이다. 해커톤처럼 시간이 중요한 환경에서는 이런 문제 해결 능력의 차이가 훨씬 크게 느껴졌다.

가장 인상 깊었던 순간은 개발 멘토링 시간이었다. 멘토님이 오셔서 현재 상황을 보시더니, 우리가 4시간 동안 해결하지 못하고 있던 문제를 거의 5분 만에 잡아내셨다. 포스트맨에서 URL 뒤에 필요한 경로를 정확히 붙여서 요청해 보라고 하셨고, 정말 그 한 번의 수정으로 바로 통신이 됐다. 그 순간은 놀랍기도 했고, 동시에 굉장히 강한 인상을 받았다.

우리는 오랫동안 구조 전체를 의심하고 있었는데, 시니어 개발자는 문제를 넓게 보지 않고 빠르게 핵심으로 좁혀 들어갔다. 결국 개발 실력이라는 것은 단순히 문법이나 프레임워크를 잘 아는 것이 아니라, 막혔을 때 원인을 빠르게 파악하고 해결 방향을 제시할 수 있는 능력이라는 생각이 들었다.

이 뒤로는 그냥 이슈 없이 무난하게 아침이 되었고, 발표를 진행하고 나서는 너무 졸려서 구석에서 잤던거 같다. 이후 시상식때는 아쉽게도 4등을 해 장려상을 받았다 ㅠㅠ 밤을 새서 다들 컨디션이 엉망이었다..

☁️ 해커톤 느낀점

짧은 시간 안에 개발한다는 것의 의미

해커톤의 가장 큰 특징은 역시 시간이다. 우리 해커톤같은 경우 무박 2일 안에 기획, 개발, 디자인, 발표 준비까지 모두 해야 하기 때문에, 한 가지 문제에 오래 붙잡혀 있으면 전체 일정이 바로 흔들린다. 실제로 개발을 하면서도 “이게 더 좋은 구조일까?”를 길게 고민하기보다는 “지금 바로 동작하게 만들 수 있는가?”를 우선으로 생각하게 됐다. 그런 점에서 해커톤은 평소의 개발과는 전혀 다른 감각을 요구했다. 빠르게 결정하고, 빠르게 구현하고, 빠르게 검증하는 흐름 속에서 지금 당장 필요한 것이 무엇인지 계속 판단해야 했다.

배운 점

이번 해커톤을 통해 기술적으로도 많이 배웠지만, 그보다 더 크게 남은 것은 협업과 문제 해결 방식에 대한 배움이었다. 내가 알고 있는 것이 다른 사람에게는 당연하지 않을 수 있고, 반대로 내가 당연하게 생각했던 개념이 실제로는 전혀 당연하지 않을 수 있다는 점을 많이 느꼈다.

해커톤에서의 이틀은 굉장히 짧았지만, 그 안에서 얻은 배움은 생각보다 훨씬 컸다. 힘들었지만 그만큼 값진 경험이었고, 그래서 더 오래 기억에 남을 것 같다.

#구름딥다이브 #구름부트캠프 #부트캠프후기 #KDT후기 #구름서포터즈 #DEEPDIVE

2차 팀 프로젝트에서 스크럼을 통해 지도를 추가하자고 했다. 네이버는 유료여서 카카오 지도 API를 선택했고 그 과정을 적어보려 한다.

✅ 카카오 지도를 선택한 이유

국내 사용자 기준 접근성과 구현 효율이 가장 좋다고 판단했다. 또한 공연장 위치를 직관적으로 제공하기에 적합했고 프론트엔드와 연동이 매우 간단했다.

✅ 카카오 지도 사전 설정

1. API 키 발급받기

카카오 지도 API 발급 받기

2. 앱 만들기

학습용이라 프로젝트이름을 입력하면 된다.

3. API 키 받고 도메인 입력하기

플렛폼 키 들어가면 자바스크립트 API 키를 제공해주는데 프론트에 카카오 지도를 제공해주는데 사용하면 된다. SDK url은 도메인까지만 입력하면 된다.

4. 카카오맵 사용 설정 ON

마지막으로 카카오맵 사용 설정 여부만 ON으로 바꿔주면 된다. 이게 있는지 모르고 좀 지도가 왜 안 나오지 하면서 좀 헤맸다.. 여기까지 하면 코드짜기 전 사전 작업 끝이다.

✅ 카카오 지도 플로우 코드로 이해하기

1. 백단에서 프론트단으로 API 내려주기

@Value("${kakao.map.app-key}")
    private String kakaoMapAppKey;

@GetMapping("/config/map")
    public Map<String, String> getMapConfig() {
        return Map.of("kakaoMapAppKey", kakaoMapAppKey);
    }

아까 받은 API를 환경변수화 해서 키에 넣은 후 프론트단에 내려준다.

2. Json 응답보기

{
  "id": 1,
  "imageUrl": "https://operation-grape.s3.ap-northeast-2.amazonaws.com/p1.jpeg",
  "performanceName": "뮤지컬 데스노트",
  "venue": "디큐브 링크아트센터",
  "startDate": "2026-04-20",
  "endDate": "2026-06-30",
  "performanceTime": 165,
  "startedAt": "2026-03-26T19:00:49.359692",
  "price": 95000,
  "remainingSeatLimit": null,
  "address": "서울특별시 구로구 경인로 662 (신도림동, 디큐브시티)",
  "latitude": 37.5089149,
  "longitude": 126.888417
}

카카오 지도 위치를 찍기 위해 위경도를 같이 내려줬다.

3. 카카오 지도 API 불러와서 연동하기

    let kakaoMap = null;
    let kakaoMarker = null;

    async function loadKakaoSdk() {
        if (window.kakao && window.kakao.maps) {
            return;
        }

        const res = await fetch('/api/performances/config/map');
        if (!res.ok) {
            throw new Error('카카오 지도 설정을 불러오지 못했습니다.');
        }

        const config = await res.json();
        const appKey = config.kakaoMapAppKey;

        if (!appKey) {
            throw new Error('카카오 지도 키가 없습니다.');
        }

        await new Promise((resolve, reject) => {
            const existing = document.querySelector('script[data-kakao-map="true"]');
            if (existing) {
                existing.addEventListener('load', resolve, { once: true });
                return;
            }

            const script = document.createElement('script');
            script.src = `https://dapi.kakao.com/v2/maps/sdk.js?appkey=${appKey}&libraries=services&autoload=false`;
            script.dataset.kakaoMap = 'true';
            script.onload = () => kakao.maps.load(resolve);
            script.onerror = () => reject(new Error('카카오 SDK 로드 실패'));
            document.head.appendChild(script);
        });
    }

4. 카카오 지도에 특정 위치를 직접 표시

async function openMapModal() {
        try {
            if (!data) return;

            const venueName = data.venue || '공연장';
            const address = data.address;
            const latitude = data.latitude;
            const longitude = data.longitude;

            if ((latitude == null || longitude == null) && !address) {
                alert('공연장 위치 정보가 없습니다.');
                return;
            }

            await loadKakaoSdk();

            document.getElementById('mapTitle').textContent = `${venueName} 위치`;
            document.getElementById('mapModal').classList.add('active');

            const mapContainer = document.getElementById('kakaoMap');
            const defaultCenter = new kakao.maps.LatLng(37.5665, 126.9780);

            if (!kakaoMap) {
                kakaoMap = new kakao.maps.Map(mapContainer, {
                    center: defaultCenter,
                    level: 3
                });
            }

            kakaoMap.relayout();

            if (kakaoMarker) {
                kakaoMarker.setMap(null);
            }

            // 위도/경도 있으면 바로 지도 표시
            if (latitude != null && longitude != null) {
                const coords = new kakao.maps.LatLng(latitude, longitude);

                kakaoMap.setCenter(coords);
                kakaoMap.setLevel(3);

                kakaoMarker = new kakao.maps.Marker({
                    map: kakaoMap,
                    position: coords
                });

                const infowindow = new kakao.maps.InfoWindow({
                    content: `
                            <div style="
                                padding:8px 12px;
                                font-size:13px;
                                font-weight:600;
                                color:#191919;
                                background:#ffffff;
                                border:1px solid #ddd;
                                border-radius:8px;
                                white-space:nowrap;
                            ">
                                ${venueName}
                            </div>
    `
                });
                infowindow.open(kakaoMap, kakaoMarker);
                return;
            }

            // 주소 fallback
            const geocoder = new kakao.maps.services.Geocoder();

            geocoder.addressSearch(address, function(result, status) {
                if (status === kakao.maps.services.Status.OK) {
                    const coords = new kakao.maps.LatLng(result[0].y, result[0].x);

                    kakaoMap.setCenter(coords);
                    kakaoMap.setLevel(3);

                    kakaoMarker = new kakao.maps.Marker({
                        map: kakaoMap,
                        position: coords
                    });

                    const infowindow = new kakao.maps.InfoWindow({
                        content: `<div style="padding:6px 10px;font-size:13px;">${venueName}</div>`
                    });
                    infowindow.open(kakaoMap, kakaoMarker);
                } else {
                    alert('공연장 위치를 찾을 수 없습니다.');
                }
            });
        } catch (error) {
            console.error('지도 열기 실패:', error);
            alert(error.message || '지도를 불러오지 못했습니다.');
        }
    }

JSON 응답 → 위도/경도 꺼냄 → 카카오 좌표 생성 → 지도 중심 이동 → 마커 표시

실제 JSON 값의 위치가 나오는 모습이다.

✅ 개요

2차 팀 프로젝트에서 결제 기능 개발을 맡게 되었다. 스크럼을 통해 토스페이먼츠 api로 구현하기로 결정했고 그 과정을 적어보려 한다.

Toss Payments API 가이드

✅ Toss Payments API를 선택한 이유

1. 깔끔한 UI Docs와 설명

특정 API를 도입할 때 Docs를 읽곤 하는데 토스페이먼츠의 Docs는 가독성면에서 최고라고 볼 수 있다.

2. 테스트 코드 제공

3. 모든 결제 수단 사용 가능

결제위젯을 사용해 모든 결제수단 (카카오페이, 토스뱅크 및 각종 카드사들) 을 한 번에 연동할 수 있어 좋았다.

✅ 결제 플로우 코드로 이해하기

0. 먼저 API 키를 받아야 한다.

API 키 받기

1. 먼저 구매자가 클라이언트에 결제를 요청

결제 하기 버튼을 누른다.

2. 프론트엔드에서 결제창 호출 전 준비 및 결제창 띄우기

Toss SDK 설치

npm install @tosspayments/payment-widget-sdk

• 준비 단계

  // 토스 클라이언트 키 꺼내기
  const clientKey = window.TOSS_CLIENT_KEY;
  // 토스 SDK 초기화
  const tossPayments = TossPayments(clientKey);
  // 고객 키 생성
  const customerKey = `member_${Date.now()}`;
  // 결제 객체 생성
  const payment = tossPayments.payment({ customerKey });

• 결제 창 띄우기

  await payment.requestPayment({
    method: "CARD",
    amount: {
        currency: "KRW",
        value: Number(currentDraft.totalPrice)
    },
    orderId: currentDraft.draftId,
    orderName: `${currentDraft.performanceTitle} 예매`,
    successUrl: window.location.origin + `/payments/toss/success?draftId=${currentDraft.draftId}`,
    failUrl: window.location.origin + `/payments/toss/fail?draftId=${currentDraft.draftId}`,
    customerName: `회원 ${currentDraft.memberId}`
  });

  결제 창 띄우기 성공 시 successUrl로 가고, 실패 시 failUrl로 가게 설정했다.

  3. 리다이렉션 URL로 이동

  PaymentController에서 성공 -> tossPaymentSerice에서 로직 실행

  @GetMapping("/success")
    public String success(
            @RequestParam String paymentKey,
            @RequestParam String orderId,
            @RequestParam Long amount,
            @RequestParam UUID draftId
    ) {
        tossPaymentService.handleSuccess(draftId, paymentKey, orderId, amount);
  
        return "redirect:/reservation";
    }

  토스 페이먼츠는 인증 성공 시 amount 값 검증과 payment 엔터티에 paymentKey, amount, orderId 값을 저장하는 것을 권고한다.

PaymentController에서 실패 -> 메시지 보내기

@GetMapping("/fail")
public String fail(
        @RequestParam(required = false) String code,
        @RequestParam(required = false) String message,
        @RequestParam(required = false) String orderId
) {
    System.out.println("결제 실패 code = " + code);
    System.out.println("결제 실패 message = " + message);
    System.out.println("결제 실패 orderId = " + orderId);

    if (orderId != null && !orderId.isBlank()) {
        return "redirect:/reservationConfirm2?draftId=" + orderId;
    }

    return "redirect:/performance-list";
}

실패 시 각 메시지 구매자에게 보여주기

4. 결제 승인 및 응답 확인

tossPaymentService.handleSuccess(draftId, paymentKey, orderId, amount); 성공 시 이 로직 안에서 결제를 승인한다.

String encodedAuth = Base64.getEncoder()
        .encodeToString((secretKey + ":").getBytes(StandardCharsets.UTF_8));

String responseBody = restClient.post()
        .uri("https://api.tosspayments.com/v1/payments/confirm")
        .header(HttpHeaders.AUTHORIZATION, "Basic " + encodedAuth)
        .header(HttpHeaders.CONTENT_TYPE, MediaType.APPLICATION_JSON_VALUE)
        .body(Map.of(
                "paymentKey", paymentKey,
                "orderId", orderId,
                "amount", amount
        ))
        .retrieve()
        .body(String.class);

KT x Goorm 백엔드 부트캠프

☁️ 해커톤 참여 동기

먼저 이번 3월부터 프로젝트 시작이라 바쁜와중에 해커톤까지 병행하는 것에 참여에 대해 고민을 많이 했다. 아직 해커톤 경험이 한 번도 없어서 "이런 무박 해커톤을 통해 개발에 열정을 갖는 사람들과 함께 경험해 보고 싶다, 나 자신도 이 분야에 대해서 레벨업을 해보고 싶다"라는 생각에 참여하게 되었다. 또한 수상을 통해 경력사항에 한 줄이라도 더 넣고 싶었고 처음이자 마지막인 부트캠프에서 해보고 싶은건 다 해보자라는 마음이 나를 이끌었다. 사실 작년에는 제주도에서 해커톤을 3박4일 진행했었는데, 이번년은 강남에서 무박2일로 진행해 엄청 부담이되진 않았다. (이번 제주 3박4일은 개최되는 대회가 없어서 가지 않았음)

☁️ 해커톤 준비 일정

☁️ 팀 빌딩

먼저 팀빌딩이다. 나는 우연히 이 부트캠프에 고등학교 친구때 친구를 발견해서 친구 두명과 같이 신청했다. 물론 나머지는 신청하신 분들과 랜덤 매칭됐다. 우리 팀은 백엔드 1, 프론트 1, 풀스택 1, 디자이너 2, PM 1, 생성형AI 1 명으로 구성되었으며 굉장히 분포가 잘 돼있어서 만족했다. 사실 첫 회의때 다들 말씀들을 잘 하셔서 나만 잘하면 될 거 같다고 느꼈다.

☁️ 특강

특강은 두 가지 특강으로 진행되었다. 첫 특강은 현업자분의 해커톤 경험을 공유받는 시간을 가졌다. 해커톤에서 기획이 반이다 라는 말이 있을 정도로 주제 선정이 굉장히 중요하고 발표할 때는 시간이 굉장이 없으므로 이미지 위주로 ppt를 짜고 그 이미지에 대해 부가설명을 해라 라고 말씀해주셨다. 그리고 리허설은 필수로 진행해야한다를 알려주셨다.

두 번째 특강은 구름 개발자분의 GitHub 협업 가이드라는 이름으로 GitHub 사용법에 대해 간단히 알려주셨다. 평소에 GitHub 사용은 꾸준히 해서 잘 알아들을 수 있었다. 간략히 요약하자면 Git Organization 파고 repository 파고 프로젝트 초기 설정 및 만드는 법과 각 포지션 별 깃허브에서의 역할 등을 설명해주셨다.

이것은 나의 역할인 개발자의 GitHub 활용법을 가져와보았다.

☁️ 기획 멘토링

우리는 3조라서 가장 빠르게 기획 멘토링을 진행했다. 두 분이 멘토로 와주셨는데 한 분은 기획 쪽 피드백을 해주시고 한 분은 개발 쪽 피드백을 해주셨다. 우리의 주제가 뭔지 우리의 주제와 비슷한 서비스와의 차별점을 어떻게 더 극대화 할수 있을까. 이 서비스로 수익성을 얻을 수 있는 구조 및 해커톤의 주제와 잘 어우러지는 포인트가 뭔지에 대해 이야기를 나눠봤다. 약 40분간 멘토링을 진행한 이후에 우리 팀도 느끼는게 많아 이후 약 두시간동안 회의를 진행했다.

이 zep이라는 공간에서 멘토링을 진행했는데 뭔가 캐릭터가 있는 디스코드 느낌이라 신선했다.

☁️ 해커톤

벨로그를 작성하는 오늘은 3/26일이다. 해커톤 진행하기 전까지의 기획과 개발에 관한 회의가 아직 끝나지 않았지만 이제 일주일정도 밖에 남지 않았다. 좀 더 우리 주제와 개발 방향에 대해 회고해보고 해커톤을 어떻게 잘 진행하고 당일 날 터지지않게 진행을 어디까지 해 놓을 지 회의를 통해 더 정해봐야겠다. 다음에는 해커톤 후기나 우승해서 오겠습니다.

#구름딥다이브 #구름부트캠프 #부트캠프후기 #KDT후기 #구름서포터즈 #DEEPDIVE

Undo Retention

트랜잭션이 완료된 후에 바로 Undo 데이터를 재사용하지 말아라고 오라클에게 힌트를 주는 것

슬롯 ITL -> 트랜잭션 ID, status, UBA(Undo Block Address), 커밋 SCN(트랜잭션 성공 시) 슬롯 부족 해결 -> inittrans, maxtrans, pctfree

consistent 읽기(select) = 논리적 읽기(cr) = query = consistent gets current 읽기(dml) = 물리적 읽기(pr) = disk = db block gets 다만, 갱신할 대상 레코드를 식별하는 작업만큼은 Consistent 모드로 이루어진다

autonomous 트랜잭션 - 메인 트랜잭션말고 서브 트랜잭션만 따로 커밋

ash = direct path i/o 랑 특징 같음 (래치 생략, 너무 빠름 등)

result 캐시에서 쿼리집합을 캐싱하지 못 하는 경우

Dictionary 오브젝트를 참조할 때 Temporary 테이블을 참조할 때 시퀀스로부터 CURRVAL, NEXTVAL Pseudo 컬럼을 조회할 때 쿼리에서 SQL 실시간 날짜 함수를 사용할 때

result 캐싱 힌트 - RESULT_CACHE

oracle 7i

rowid = 블록번호(8자리) + 로우번호(4자리) + 데이터파일번호(4자리) => 6byte

oracle 8i

rowid = 데이터오브젝트번호(6자리) + 데이터파일번호(3자리) + 블록번호(6자리) + 로우번호(3자리) => 10byte

두 개 이상의 인덱스를 함께 사용

and-equal, index Combine, index join

손익분기점 극복

IOT, 클러스터 테이블, 파티셔닝

Logical Rowid = PK + physical guess

소트머지조인

• outer만 부분범위처리 가능 (인덱스 소트연산 생략가능)

해시조인

• probe inputs 만 부분범위처리 가능

build inputs가 커서 메모리 범위 초과할 때

grace 해시조인, hybrid 해시조인, recursive 해시조인, 비트 벡터 필터링

아우터 조인은 힌트 안 먹힘

프로세스 - PGA 1:1 세션 - UGA 1:1

병렬 update 시 qc 위에 update 오퍼레이션 시 qc가 처리

옵티마이저 힌트 시 주의 사항

• 힌트 사이에 콤마(,) 사용 금지

• 스키마명 사용 금지 (JI.EMP)

• Alias 지정 시 반드시 사용

하나의 블록을 두 개 이상의 프로세스가 동시에 접근하려고 할 때 문제 발생 ->버퍼 Lock을 통해 직렬화, 데이터 정합성 문제를 해결하면서도 캐시 경합을 줄이려면, SQL 튜닝을 통해 쿼리 일량 자체를 줄여야한다.

서브쿼리 조인 (no_unnest = filter)

필터 오퍼레이션 = Nested Loops 와 같은 원리로 작동 부분범위처리도 가능

-> 다른점은

1. 필터 오퍼레이션은 메인쿼리의 한 로우가 서브쿼리의 한 로우와 조인에 성공하는 순간 종료 후, 다음 로우 처리
2. 필터 오퍼레이션은 캐싱기능을 가짐
3. 조인 순서 고정 - 메인 쿼리가 항상 outer 집합 위의 다른점은 nl_sj도 이 다른점을 가짐.

• 그럼 왜 unnest를 쓰냐 ? 서브쿼리를 outer집합으로 쓸 수도 있고 다양한 조인 기법을 사용할 수 있어서.

조인 조건 pushdown 기능은 메인쿼리의 조인 조건을 from 절 인라인 뷰에 넣음 -> 부분범위처리 가능

Lateral, Outer/Cross Apply 조인 사용하면 from 절에서 메인쿼리 컬럼 참조 가능 => 이 방식들은 조인 조건 pushdown 기능이 잘 작동하지 않을 때만 사용

order by 없이 group by만 사용 시 sort group by 오퍼레이션 발생하지만 정렬은 아님 소팅 알고리즘을 사용해 그룹핑 했다! 여기서 정렬된 결과집합을 원하면 order by 명시해주면됨

unnesting된 서브쿼리가 m쪽 집합이거나 1쪽집합이여도 조인 컬럼에 unique 인덱스가 없을 시 중복을 제거해 주어야 되는데 이 때 Sort Unique 오퍼레이션 발생

중복 없을 시 Sort Unique 오퍼레이션은 생략됨

커밋 옵션

default : commiit write immediate wait; -> 커밋 명령을 받을 때 마다 LGWR가 로그 버퍼를 파일에 즉시 기록 / 기록할 때 까지 기다림 option : immediate -> batch / wait -> nowait

다중 트랜잭션에 의한 동시 채번이 많지 않으면 아무거나 써도 괜찮

1. 채번 테이블이나 오브젝트 관리 부담 -> max +1 방식 사용

2. 다중 트랜잭션에 의한 동시 채번이 많고 pk가 단일컬럼 일련번호면 시퀀스 방식 사용

3. 다중 트랜잭션에 의해 동시 채번이 많고 pk 구분 속성에 값 종류 개수가 많으면 시퀀스 보다는 max+1 단, 성능 문제 시 시퀀스 오브젝트 활용

인덱스 블록 경합 해소 -> 인덱스 해시 파티셔닝 리버스 키 인덱스로 전환하는 방법도 고려

온라인 트랜잭션이 없는 야간 대량 데이터 일괄 insert 처리 문법

1. 인덱스 비활성화

   alter table ji_table modify constraint ji_table_index disable drop index;

2. 병렬 DML 활성화

alter session enable parallel dml;

3. 테이블 nologging 활성화

   alter table ji_table nologging;

4. insert 시 append 힌트 + 병렬처리 힌트

   insert /*+ append parallel(ji_table 4) */ into ji_table
   select ~

5. 인덱스 다시 활성화

   alter table ji_table modify constraint ji_table_index enable novalidate;

6. 테이블 다시 로깅

   alter table ji_table logging;

7. 병렬 DML 비활성화

   alter table disable parallel dml;

   파티션 Exchange 기법 / 대량 데이터 변경

8. 임시(temp) 테이블 생성 (nologging 모드면 좋음)

   create table emp_temp
   nologging
   as
   select * from emp where 1 = 2;

9. emp 데이터를 읽어 임시 테이블에 입력하면서 변경 값을 수정

   insert /*+ append */ into emp_temp e
   select empno, edate, ...
        , case when 상태코드 <> 'AAA' then 'AAA' else 상태코드 end) 상태코드
   from emp
   where edate < '20260101';

10. 임시 테이블에 원본 테이블과 같은 구조로 인덱스 생성 (nologging모드면 좋음)

    create unique index emp_temp_pk on emp_temp (empno) nologging;
    create index emp_temp_x1 on emp_temp (edate) nologging;

11. 바꿀 파티션과 임시 테이블을 exchange

    alter table emp
    exchange partition p202512 with table emp_temp
    including indexes without validation;

12. 테이블을 Drop

    drop table emp_temp;

13. (nologging 모드로 작업했다면) 파티션을 logging 모드로 전환

    alter table emp modify partition p202512 logging;
    alter table emp_pk modify parition p202512 logging;
    alter table emp_x1 modify parition p202512 logging;

대용량 파티션 데이터 삭제

1. nologging 모드로 tmp 테이블 생성

   create table emp_temp
   nologging
   as
   select * from emp where 1 = 2;

2. 조건의 나머지 데이터만 tmp 테이블에 삽입

   insert /*+ append */ into emp_temp
   select * from emp partition (202402) where c2 != 'Y';

3. 원본 테이블 파티션 truncate 후 nologging 모드로 임시테이블 데이터를 원본테이블에 삽입

   alter table emp truncate partition(202402); 
   

alter table emp nologging; insert /*+ append */ into emp select * from emp_temp;

4. temp 테이블 삭제,emp 테이블 logging
```sql
drop table emp_temp;

alter table emp logging;

px_join_filter / no_px_join_filter (+ no_merge, where절 필터 조건)

SELECT /*+ LEADING(T1) NO_MERGE(V1) PX_JOIN_FILTER(V1) */ 
          T1.PRDT_ID
         ,V1.SALES_AMT
FROM TB_PRDT T1
       ,(
           SELECT PRDT_ID , SUM(TRD_AMT) AS SALES_AMT
           FROM TN_TRADE
           GROUP BY PRDT_ID --group by
        ) V1
WHERE T1.PRDT_ID IN (1000, 2000, 3000) -- 필터 조건
   AND T1.PRDT_ID = V1.PRDT

index skew 한쪽으로 치우치는 현상 index sparse 밀도가 떨어지는 현상

create index emp_x1 on emp(empno, job);

CTAS문 시 nologging 모드로 테이블 만들기

create table ji_table
nologging
as
select ~

update 수정 사항 table 만들 때 그냥 넣어버리는 거 고려 -> update 안 해도됨.

PL/SQL for loop -> one sql로 구현하기

merge into table_a using (table_b) on ( ) when matched then ~ when not matched then ~

update -> merge 문 변경 시 윈도우 함수 사용 가능.

set transaction isolation level serializable; 
-- 트랜잭션 고립화 수준 serializable 까지 올리기

해시조인 -> build input - 전체범위처리, probe input - 부분범위처리 가능 소트머지조인 -> 전체 일량은 검색조건에 의해 결정 exists ->semi join 변환 not exists -> anti join 변환

non-repeated read -> 다른 트랜잭션에서 수정 phantom read -> 다른 트랜잭션에서 삽입

sqlserver -> 변경을 기다렸다가 변경 후 값을 수정함 oracle -> 변경을 기다리지 않고 변경 전값을 수정함

1. 모델링의 특징

추상화 : 현실세계를 일정한 형식에 맞추어 표현을 한다는 의미 단순화 : 복잡한 현실세계를 약속된 규약에 의해 제한된 표기법이나 언어로 표현 명확화 : 누구나 이해하기 쉽게 대상에 대한 애매모호함을 제거

2. 모델링의 세 가지 관점

데이터관점 + 프로세스과점 + 데이터프로세스 상관관점

3. 데이터 모델링의 정의

정보시스템을 구축하기 위한 데이터 관점의 업무 분석 기법 현실세계의 데이터에 대해 약속된 표기법에 의해 표현하는 과정 데이터베이스를 구축하기 위한 분석, 설계의 과정

4. 데이터 모델의 기능

명세화, 구조화, 문서화, 다양한 관점, 상세수준의 표현

5. 데이터 모델링의 중요성

파급효과(Leverage) 복잡한 요구사항의 간결한 표현(Conciseness) 데이터 품질(Data Quailty)

6. 데이터 모델링의 3단계

개념적 데이터 모델링 : 추상화, 업무중심적, 포괄적, 전사적, EA수립시 사용 논리적 데이터 모델링 : KEY, 속성, 관계 표현, 재사용성 높음 물리적 데이터 모델링 : 실제 DBMS에 이식, 물리적인 성격 고려

7. 데이터 독립성

논리적 독립성 : 개념스키마 변경, 외부스키마 영향 없음, 논리적 구조가 변경되어도 응용프로그램 영향없음

물리적 독립성 : 내부스키마 변경, 외부/개념스키마 영향 없음, 저장장치의 구조변경은 응용프로그램과 개념스키마에 영향없음

8. 사상

외부/개념적 사상(논리적 사상) : 외부적 뷰와 개념적 뷰의 상호 관련성 정의(개념적 뷰의 필드 타입은 변화 없음)

개념/내부적 사상(물리적 사상) : 개념적 뷰와 DBMS간의 상호 관련성 정의(DB구조 변경시 개념적/내부적 사상이 바뀌어야 개념적 스키마가 그대로 남음)

9. 데이터 모델링의 세가지 요소

어떤것(Things), 성격(Attributes), 관계(Relationships)

10. 데이터 모델 이해 관계자

DBA, 개발자(가장중요), 현업업무전문가, 전문 모델러

11. 좋은 데이터 모델 요소

완전성(Completeness) 중복배제(Non-Redundancy) 업무규칙(Business Rules) 데이터재사용(Data Reusuability) 의사소통(Communication) 통합성(Integration)

12. 엔터티의 특징

반드시 필요, 식별가능, 영속적으로 존재(두개이상), 업무프로세스 이용, 속성필수, 관계필수

13. 엔터티 분류

유무형 : 유형엔터티, 개념엔터티, 사건엔터티 발생시점 : 기본엔터티, 중심엔터티, 행위엔터티

14. 속성의 분류

기본속성(이름), 설계속성(코드), 파생속성(합계)

15. 주식별자의 특징

유일성, 최소성, 불변성, 존재성

16. 식별자 분류

대표성 여부 (주식별자, 보조식별자) 스스로생성 여부 (내부식별자, 외부식별자) 단일속성 여부(단일 식별자, 복합식별자) 대체 여부(본질식별자, 인조식별자)

17. 식별자/비식별자 관계

식별자 관계 : 부모로 받은 식별자를 자신엔터티의 주식별자로 이용 비식별자 관계 : 부모로 부터 속성을 받았지만 주식별자로 사용하지 않고 일반 속성으로 사용

18. 비식별자 관계 설정 고려사항

약한 관계, 독립 PK구성, PK속성 단순화를 위해서 고려

19. 식별자 관계 설정 고려사항

강한 관계, 주식별자 PK사용

20. 함수 종속성 이란?

데이터들이 어떤 기준값에 의해 종속되는 현상을 지칭한다. 기준값을 결정자, 종속되는 값을 종속자 라고 한다.

ex) 주민등록번호 -> (이름,출생지,주소)

21. 반정규화 기법

1) 테이블 반정규화 테이블 병합(1:1, 1:M, 슈퍼/서브타입), 테이블 분할(수직,수평), 테이블 추가(중복, 통계, 이력, 부분)

2) 칼럼 반정규화 중복칼럼 추가, 파생칼럼 추가, 이력테이블 칼럼 추가, PK에 의한 칼럼 추가, 응용시스템 오작동을 위한 칼럼 추가

3) 관계 반정규화 중복관계 추가

22. 슈퍼서브타입 모델

공통의 부분을 슈퍼타입으로 모델링, 다른 엔터티와 차이가 있는 속성에 대해서 별도의 서브 엔터티로 구분

1) OneToOne Type 개별테이블, 확정성 우수, 조인성능 나쁨, I/O 좋음, 관리안좋음

2) Plus Type 슈퍼서브타입테이블, 확정성 보통, 조인성능 나쁨, I/O좋음, 관리안좋음

3) Single Type 하나의 테이블, 확정성 나쁨, 조인성능 우수, I/O나쁨, 관리좋음

23. 분산 데이터베이스의 투명성

분할투명성, 위치투명성, 지역사상 투명성, 중복투명성, 장애투명성, 병행투명성

24. 분산 데이터베이스의 장단점

장점 : 지역자치성, 점증적시스템 용량확장, 신뢰성,가용성, 효용성, 융통성, 빠른 응답, 통신비용절감, 데이터가용성, 신뢰성, 시스템규모조절, 요구수용 증대

단점 : 비용, 오류잠재성증대,처리비용,설계관리복잡성,불규칙한응답속도,통제어려움,데이터무결성위협

25. 분산 데이터베이스 적용 기법

1) 위치분산

2) 분할분산 : 수평분할, 수직분할

3) 복제분산 : 부분복제(통합된건 본사, 각지사별로 해당로우), 광역복제(본사,지사모두 동일한 데이터 가지고있음)

4) 요약분산 : 분석요약(각지사별로 요약, 본사에 통합), 통합요약(각지사별로 존재하는 다른 내용이 정보요약, 본사에 통합)

26. 외래키 추가 SQL

ALTER TABLE PLAYER ADD CONSTRAINT PLAYER_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID);

27. 트랜잭션의 특성

원자성(Atomicity), 일관성(Consistency), 고립성(Isolation), 지속성(Durability)

28. 각종 함수 결과값

SELECT RTRIM('XXXYYY   ', ' ') FROM DUAL; -> "XXXYYY"

SELECT LTRIM('XXXYYY   ', 'X') FROM DUAL; -> ""YYY   "

SELECT SIGN(-00) FROM DUAL; -> 0

SELECT SIGN(-100) FROM DUAL; -> -1

SELECT SIGN(100) FROM DUAL; -> 1

SELECT CEIL(38.123) FROM DUAL; -> 39

SELECT CEIL(-38.123) FROM DUAL; -> -38

SELECT FLOOR(38.123) FROM DUAL; -> 38

SELECT FLOOR(-38.123) FROM DUAL; -> -39

SELECT ROUND(38.5235, 3) FROM DUAL; -> 38.524

SELECT ROUND(38.5235, 1) FROM DUAL; -> 38.5

SELECT ROUND(38.5235, 0) FROM DUAL; -> 39

SELECT ROUND(38.5235) FROM DUAL; -> 39

SELECT TRUNC(38.5235, 3) FROM DUAL; -> 38.523

SELECT TRUNC(38.5235, 1) FROM DUAL; -> 38.5

SELECT TRUNC(-38.5235, 1) FROM DUAL; -> -38.5

SELECT TRUNC(38.5235, 0) FROM DUAL; -> 38

SELECT TRUNC(38.5235) FROM DUAL; -> 38

SELECT NULLIF('1', '1') FROM DUAL; -> NULL

SELECT COALESCE(NULL, '2', '1') FROM DUAL; -> '2'

29. 뷰의 장점

독립성, 편리성, 보안성

30. 일반 집합연산자와 현재의 SQL 비교

UNION 연산은 UNION 기능으로

INTERSECTION 연산은 INTERSECT 기능으로

DIFFERENCE 연산은 EXCEPT, MINUS 기능으로

PRODUCT 연산은 CROSS JOIN 기능으로

31. 순수관계 연산자와 현재의 SQL 비교

SELECT연산은 WHERE절로 구현

PROJECT연산은 SELECT 절로 구현

NATURAL JOIN연산은 다양한 JOIN기능으로 구현

DIVIDE연산은 현재 사용되지 않는다.

32. 계층형 쿼리

순방향

SELECT
LEVEL, LPAD(' ', 4*(LEVEL-1)) || EMPNO, MGR, CONNECT_BY_ISLEAF
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;

역방향

SELECT
LEVEL,
LPAD(' ', 4*(LEVEL-1))||EMPNO,
MGR, CONNECT_BY_ISLEAF
FROM EMP
START WITH EMPNO = 7876
CONNECT BY PRIOR MGR = EMPNO;

패스함수

SELECT
CONNECT_BY_ROOT EMPNO,
SYS_CONNECT_BY_PATH(EMPNO, '/') ,
EMPNO, MGR
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;

33. ROLL UP, CUBE, GROUPING SETS, GROUPING

SELECT
CASE GROUPING(DNAME) WHEN 1 THEN '모든부서' ELSE DNAME END AS DNAME,
CASE GROUPING(JOB  ) WHEN 1 THEN '부서별' ELSE JOB   END AS JOB,
--,
COUNT(*),
SUM(SAL)
FROM EMP, DEPT
WHERE EMP.DEPTNO =  DEPT.DEPTNO
--GROUP BY ROLLUP(DNAME, JOB)
--GROUP BY DNAME, ROLLUP(JOB)
--GROUP BY ROLLUP(DNAME, (JOB,MGR))
--GROUP BY CUBE(DNAME, JOB)
GROUP BY GROUPING SETS(DNAME, JOB)
ORDER BY DNAME, JOB;

34. RAC(Real Application Cluster)

여러 인스턴스가 하나의 데이터베이스를 액세스 할수 있다. 하나의 인스턴스가 여러 데이터베이스를 액세스 할수 없다.

35. Fast Commit 매커니즘

사용자의 갱신내용이 메모리상의 버퍼 블록에만 기록된 채 아직 디스크에 기록되지 않았더라도 Redo로그를 믿고 빠르게 커밋을 완료, 인스턴스 장애가 발생하더라도 로그파일을 이용해 언제든 복구가 가능하므로 안심하고 커밋을 완료

36. Write Ahead Logging

버퍼 캐시 블록을 갱신하기 전에 변경사항을 먼저 로그 버퍼에 기록, Dirty 버퍼를 디스크에 기록하기 전에 해당 로그 엔트리를 먼저 로그 파일에 기록

37. Recursive Call

DBMS내부에서 발생하는 Call, SQL파서와 최적화 과정에서 발생하는 데이터 사전 조회, 사용자 정의함수/프로시저 내에서 SQL수행, 해당 콜을 최소화하기 위해서는 바인드변수를 사용하여 하드파싱 줄이고, 사용자정의함수 및 프로시저의 무분별한 사용 금지

38. 사용자정의함수/프로시저 특징

사용자정의함수/프로시저는 내장함수처럼 native코드로 완전 컴파일된 형태가 아니어서 가상머신같은 별도의 실행엔진 사용, 실행될 때마다 문맥전환이 일어나며, 내장함수를 호출할때와 비교해 성능을 떨어뜨린다. 그러므로 소량의 데이터 일때 혹은 부분범위처리 상황에서 제한적으로 사용한다.

39. 트랜잭션의 특징

원자성, 일관성, 격리성, 영속성

40. 트랜잭션 격리성 수준 상향

set transaction isolation level read serializable;

41. snap shot too old 방지

udno 영역 크기 증가, 커밋 자주 X, fetch across commit X, 트랜잭션 시간 조정, 테이블 나누어 단계적으로 코딩, NL조인형태 지양, 소트연산 발생, 대량업데이트후 full스캔

42. Secondary 인덱스로부터 IOT레코드를 가리킬때

오라클은 Logical Rowid = PK + Physical Guess 를 사용한다.

Physical Guess = Secondary index를 최초 생성하거나 재생성한 시점의 DBA

Physical Guess를 찾아갔다가 없으면 PK로 탐색

43. 형변환

숫자형과 문자형이 만나면 -> 문자형을 숫자형으로 형변환

44. Direct Path Insert

insert select 문장에 /*+ append */ 힌트 사용 병렬모드 insert CTAS 문장을 수행

• nologging 모드 Insert (Direct Path Insert모드일때만 사용가능) alter table t nologging; -> redo 로그까지 최소화

주의사항 : Direct Path Insert시 테이블 Lock걸림

1. REDO 로그의 목적

A. Database Recovery

Media Fail 발생시 DBMS 복구 Archived Redo Log 이용

B. Cache Recovery

Instance Recovery 라고도 한다 버퍼캐시에 저장된 변경사항이 디스크에 저장 되지 않은채 정전이 발생하면 Online Redo Log를 읽어들여 마지막 체크포인트이후부터 사고 발생 직전 까지 수행되었던 트랜잭션 재현한다. 다른말로 Roll Forward 단계라고 한다.

C. Fast Commit

사용자의 갱신내용이 메모리상의 버퍼 블록에만 기록된채 아직 디스크에 기록되지 않았지만 Redo Log를 믿고 빠르게 커밋을 완료

2. Write Ahead Logging

버퍼 캐시에 있는 블록 버퍼를 갱신하기 전에 Redo 엔트리 로그버퍼에 기록해야 하며, DBWR이 버퍼캐시로 부터 Dirty 블록들을 디스크에 기록하기 전에 먼저 LGWR이 해당 Redo 엔트리를 모두 Redo로그 파일에 기록했음이 보장되어야 한다.

3. Undo 로그의 목적

A. Transaction Rollback

트랜잭션에 의한 변경사항을 최종 커밋하지 않고 롤백하고자 할때 Undo 데이터를 이용

B. Transaction Recovery

Instance Crash 발생후 Redo를 이용해 Roll Forward 단계완료후 시스템이 셧다운 시점에 아직 커밋되지 않았던 트랜잭션들을 모두 롤백할때 Undo 세그먼트에 저장된 Undo데이터를 사용한다.

C. Read Consistency 읽기 일관성을 위해서 사용된다.

4. 문장 수준 읽기 일관성

단일 SQL문이 수행되는 도중에 다른 트랜잭션에 의해 데이터의 추가, 변경, 삭제가 발생하더라도 일관성있는 결과 집합을 리턴하는 것이다.

5. Consistent, Current 모드 읽기

SELECT는 Consistent 모드로 읽는다.

INSERT, UPDATE, DELETE, MERGE 는 Current 모드로 읽고 쓴다. 다만, 갱신할 대상 레코드를 식별하는 작업은 Consistent 모드로 이루어진다.

6. 블록 클린아웃

트랜잭션에 의해 설정된 Row Lock을 해제하고 블록헤더에 커밋 정보를 기록하는 오퍼레이션

A. Delayed 블록 클린 아웃

트랜잭션이 갱신한 블록 개수가 총 버퍼 블록개수의 10%를 초과시 ITL슬록에 커밋 정보저장하고 레코드에 기록된 Lock Byte 해제하고 Online Redo에 Logging

B. 커밋 클린아웃

Online Redo로그를 남기지 않고 로깅시점을 뒤로 미룬후 해당 블록을 갱신하려고 Current모드로 읽는 시점에 Lock Byte를 해제하고 완전한 클린아웃을 수행한다.

7. SnapShot too old 발생원인

A. 데이터를 읽어 내려가다가 쿼리 SCN이후에 변경된 블록을 만나 과거시점으로 롤백한 Read Consistent 이미지를 얻으려고 하는데 Undo블록이 다른 트랜잭션에 의해 재사용돼 필요한 Undo정보를 얻을수 없는경우 발생한다.

B. 커밋된 트랜잭션 테이블 슬롯이 다른 트랜잭션에 의해 재사용돼 커밋정보를 확인할수 없는 경우 발생한다. (Undo 세그먼트 개수가 적음)

8. Snap Shot too old 회피 방법

커밋 자주 하지 말것, 트랜잭션 몰리는 시간대를 피해서 돌린다, 큰 테이블을 일정위로 나눠서 작업

오랜시간에 걸쳐 같은 블록을 여러번 방문하는 NL 조인 형태에서 인덱스를 경유한 테이블 액세스가 있는지 확인하여 풀 테이블 스캔 혹은 조인방식 변경으로 유도한다.

Order by를 강제로 삽입해 소트연산이 일어나도록 한다. Temp 세그먼트에 저장한후에는 아무리 같은 블록을 재방문하더라도 상관없다

대량의 업데이트후 곧바로 해당 테이블에 대해 Full Scan을 한번 날려준다.

9. 트랜잭션의 특징

원자성(Automicity), 일관성(Consistency), 격리성(Isolation), 영속성(Durability)

10. 트랜잭션 수준 읽기 일관성

트랜잭션이 시작된 시점을 기준으로 일관성있게 데이터를 읽어들이는 것을 말한다.

11. Autonomous Transaction

메인 트랜잭션에 영향을 주지 않고 서브 트랜잭션만 따로 커밋하는 기능

12. Latch

SGA에 공유되어 있는 갖가지 자료구조를 보호할 목적으로 사용하는 가벼운 Lock

13. Buffer Lock

버퍼 블록에 대한 액세스를 직렬화

14. 교착상태

두세션이 각각 Lock을 설정한 리소스를 서로 액세스 하려고 마주보고 진행하는 상황

15. Soft Parsing

메모리에 Caching 돼 있는 SQL을 찾아서 바로 실행

16. Hard Parsing

메모리에서 SQL을 찾는데 실패해 최적화 및 Row-Source생성 단계를 거치는 것

17. 커서의 종류 정리

공유커서(Shared Cusor) : SGA내에 Shared Pool에 존재하는 라이브러리 캐시에 공유되있는 Shared SQL Area

세션커서(Session Cursor) : PGA내에 Private SQL Area에 저장된 커서

애플리케이션 커서(Application Cursor) : 세션 커서를 가리키는 핸들

18. 바인드변수 사용시 부작용

바인드 변수 사용시 통계정보는 활용, 컬럼 히스토그램 정보는 사용못함

19. 세션커서 캐싱

SQL구문 분석 후 해시값 계산, Library Lacth 획득한 후 라이브러리 탐색 과정을 없애버린다.

20. 어플리케이션 커서 캐싱

세선커서 캐싱한 상태에서 공유커서 힙을 Pin하고 실행에 필요한 메모리 공간을 PGA에 할당하는 작업까지 없애버린다

21. ROWID의 구성

데이터 오브젝트 번호(6자리) + 데이터파일 번호(3자리) + 블록번호(6자리) + 로우번호(3자리)

22. y.대상년월(+) = substr(x.파트너지원요청일자, 1,6) -1

x.파트너지원요청일자는 varchar2 형이다. varchar2 컬럼에 숫자 값을 더하거나 빼는 연산을 가하면 내부적으로 숫자형으로 형변환이 일어난다. 이럴 경우 y.대상년월 까지도 자동으로 형변환이 되므로 인덱스 스캔이 불가능해진다.

아래처럼 되는 것이다.

to_number(y.대상년월(+)) = to_number(substr(x.파트너지원요청일자,1, 6)) -1

이렇게 되므로 y.대상년월 컬럼의 인덱스가 먹히지 않는다.

아래와 같이 튜닝한다.

y.대상년월(+) = to_char(add_months(to_date(x.파트너지원요청일자, 'yyyymmdd') -1), 'yyyymm')

23. decode함수 사용시 주의점

decode(a, b, c, d) : a와 b가 같으면 c를 반환 아니면 d반환

여기서 c인자가 null값이면 varchar2로 취급한다.

그렇게 되면 d의 인자값을 varchar2로 형변환 시킨다.

max(decode(job, 'presidendt', null, sal)) max_sal2 -> 여기서 4번째 인자인 sal의 max값을 구할때 varchar2로 형변환 상태에서 max값을 구하므로 950이 3000보다 더 큰값이라고 잘못 나와 버린다.

아래와 같이 수정한다.

max(decode(job, 'president', to_number(null), sal)) max_sal2 -> 이렇게 하면 제대로 sal값의 max값이 나온다.

24. 비용 구하는 공식

비용 = blevel + (리프블록수 * 유효인덱스 선택도) + (클러스터링팩터 * 유효테이블 선택도)

25. IOT 적용할 테이블 유형

크기가 작고 NL조인으로 반복 룩업하는 테이블

폭이 좁고 로우수가 많은 테이블

넓은 범위를 주로 검색하는 테이블

데이터 입력과 조회패턴이 서로 다른 테이블

26. Physical guess

secondary index를 최초 생성하거나 재생성한 시점에 IOT레코드가 위치했던 데이터 블록 주소

27. Logical ROWID

Logical Rowid = PK + Physical guess

28. 비트맵 인덱스를 스캔하면서 테이블 레코드를 찾아가는 방법

오라클은 한 블록에 저장할수 있는 최대 레코드수를 제한한다.

비트맵 인덱스 9500번째 비트가 1인 값의 레코드는?

최대레코드 개수는 : 730 9500/730 = 13 -> 14번째 블록 9500%730 = 10 -> 10번째 레코드

29. 테이블 Prefetch

한번의 Disk I/O를 통해서 곧이어 읽을 가능성이 큰 블록들을 캐시에 미리 적재 하는 기능

30. 테이블 Prefetch 가 일어나는 상황

Inner쪽 Non-Unique 인덱스를 Range Scan할 때

Inner쪽 Unique 인덱스를 Non-Unique 조건으로 Range Scan 할때

Inner쪽 Unique 인덱스를 Unique조건으로 실행할때 나타날수있는데 이때는 Range Scan으로 액세스

31. Buffer Pinning일어나는 상황

테이블 블록에 대한 Buffer Pinning

Inner쪽 인덱스 루트 블록에대한 Buffer Pinning

하나의 Outer레코드에 대한 Inner쪽과의 조인을 마치고 Outer쪽으로 돌아오더라도 테이블 블록에 대한 Buffer Pinning

User Rowid로 테이블 액세스시 Buffer Pinning

Inner쪽 루트아래 인덱스 블록들도 Buffer Pinng

32. 확장된 ROWID 포맷

데이터오브젝트번호(6)+데이터파일번호(3)+블록번호(6)+로우번호(3)

33. 인덱스 설계

조건절에 사용, '='조건으로 조회

34. QBNAME 힌트의 사용

SELECT /*+ LEADING(DEPT@QB1) */ *
FROM EMP
WHERE DEPTNO IN
(
SELECT /*+ UNNEST QB_NAME(QB1) */ DEPTNO
FROM DEPT
);

35. NL_SJ 의 사용

SELECT /*+ LEADING(EMP) */ *
FROM EMP
WHERE DEPTNO IN
(
SELECT /*+ UNNEST NL_SJ */ DEPTNO
FROM DEPT
);

36. NL_AJ 의 사용

SELECT *
FROM DEPT D
WHERE NOT EXSITS
( SELECT /*+ UNNEST NL_AJ */ 'X'
FROM EMP
WHERE DEPTNO = D.DEPTNO
);   

37. NO_UNNEST와 PUSH_SUBQ의 사용

SELECT /*+ LEADING(E1) USE_NL(E2) */ SUM(E1.SAL), SUM(E2.SAL)
FROM EMP1 E1, EMP2 E2
WHERE E1.NO = E2.NO
AND EXISTS
( 
SELECT /*+ NO_UNNSET PUSH_SUBQ */ 'X' FROM DEPT
WHERE DEPTNO = E1.DEPTNO
AND LOC = 'NEW YORK'
);

38. 뷰머징이 되지 않는 상황

집합연산자, CONNECT BY 절, ROWNUM 사용, GROUP BY 없이 집계함수 사용, 분석함수

39. 병렬 DML 처리

ALTER SESSION ENABLE PARALLEL DML;

EXPLAIN PLAN FOR
UPDATE /*+ PRRALLEL(T 4)*/ T SET NO2 = LPAD(NO, 5, '0');

ALTER SESSION ENABLE PARALLEL DML;

INSERT /*+ APPEND PARALLEL(T1 4) */INTO T1
SELECT /*+ FULL(T2) PARALLEL(T2 4) */ * FROM T2;

40. Granule

블록기반 Granule : PX BLOCK ITERATOR

파티션기반 Granule : PX PARTITION RANGE ALL(파티션 전체), PX PARTITION RANGE ITERATOR(부분 파티션)

41. 파티션 조인

• FULL PARTITION WISE 조인 : 두개의 테이블이 같은 기준으로 파티셔닝 되어 있는 경우

• PARTIAL PARTITION WISE 조인 : 둘중 하나만 파티션 되어 있는 경우

• 동적 파티셔닝 : 양쪽 테이블을 동적으로 파티셔닝하고서 FULL PARTITION WISE 조인, 한쪽테이블을 BROADCAST 하고 나서 조인

• BROADECAST 방식

42. 카디널리티 구하는 공식

카디널리티 = 선택도 * 전체레코드 수

43. 세션 커서 캐싱

sql구문분석,해시값계산,라이브러리캐시 래치 획득,라이브러리커서 탐색 들을 생략

44. 어플리케이션 커서 캐싱

세션커서캐싱에다가 공유커서 힙을 pin하고 실행에 필요한 메모리 공간을 pga할당하는 과정을 생략

KT x Goorm 백엔드 부트캠프

☁️ 구름 부트캠프 2개월차 후기 - 팀스터디

구름 부트캠프를 시작한 지 벌써 2개월 차에 접어들었다.

시간이 정말 빠르게 흘러가는거 같다. 정신 차려보니 어느새 하루 루틴에 코딩이 자연스럽게 녹아들어 있다.

수업 듣기, 코딩테스트 스터디, 프로젝트 준비, SQLP 자격증 준비등 바쁜 취준 생활을 보내고 있지만 나름 하루하루가 빠르게 지나가는 게 취준이 실감이 난다.

현재 구름 부트캠프에서는 본격적인 팀 프로젝트에 들어가기 전 단계로, 스프링 부트와 스프링 MVC를 중심으로 웹 기능 구현 연습을 집중적으로 진행하고 있다.

• Spring Boot로 프로젝트 구조 잡기

• Controller / Service / Repository 흐름 이해

• Spring MVC로 요청–응답 처리

• JSP / Thymeleaf 같은 뷰 템플릿 연동

• 간단한 CRUD 기능 구현

처럼 웹 애플리케이션의 기본 뼈대를 직접 만들어보는 단계를 진행 중이다.

이런 웹 개발 학습과 병행해서, 팀원들과 함께 알고리즘 스터디를 주 2회 진행하고 있다.

웹 기능 구현도 중요하지만, 코딩 테스트나 문제 해결 능력은 꾸준히 손을 놓지 않는 것 또한 중요하다고 느꼈기 때문이다.

이번 글에서는 내가 참여하고 있는 팀 스터디 활동 방식, 그중에서도 코딩 테스트 알고리즘 스터디를 어떻게 운영하고 있는지에 대해 정리해보려고 한다.

🐸 왜 팀 스터디 인가?

혼자 문제를 풀면 막히면 바로 답을 보게 되고 풀이 과정이 머릿속에서만 끝나기 쉽다.

반면 팀 스터디에서는 “왜 이렇게 풀었는지 설명해야 하는 상황”이 생긴다.

이 과정에서 생각이 정리되고 로직의 빈틈이 드러나고 다른 사람의 접근 방식도 자연스럽게 흡수하게 된다.

그래서 지금 이 시점에서의 팀 알고리즘 스터디는 프로젝트를 대비하는 과정이자, 기본기를 다지는 중요한 루틴이 되고 있다.

🌈 팀 스터디 운영 방식

우리 팀 알고리즘 스터디는 주 2회, 화요일과 금요일에 진행하고 있다. 시간은 16시부터 18시까지, 하루 2시간 동안 집중해서 진행하는 방식이다.

문제는 백준 코딩테스트를 기준으로 선정하며, 무작위로 푸는 것이 아니라 알고리즘 유형별로 나누어 학습하고 있다.

우리 팀의 계획 일부를 보여주자면 이러한 알고리즘을 선정하여 고정 문제 한 문제 + 그 알고리즘에 관한 문제 1문제 총 두 문제를 풀어와 각 요일에 리뷰하는 식으로 진행하고 있다.

문제 수로 봤을 때 많지는 않지만 문제를 많이 푸는 것보다 각 알고리즘을 깊게 이해하고 설명하고 피드백하는 것을 더 중요하게 생각했기 때문에 이렇게 구성했다.

나또한 팀원들에게 코드를 설명하면서 얻어가는 것이 많고, 팀원들의 질문을 받아 내가 몰랐던 부분을 알게 되면서 여기서도 얻어가는 것이 많다.

이 과정에서 단순히 “정답을 맞혔다” 보다 문제 접근 방식과 사고 흐름을 공유하는 데 더 많은 시간을 쓴다.

팀원의 코드를 보면서도 궁금한 점을 피드백하는 것도 많이 도움이 된다.

노션으로 활동일지를 매 주 작성하고 있다. 문제를 풀고 넘기는 것이 아니라 기록하는 것이 중요하다고 생각했기 때문이다.

인텔리제이에서 또한 하나의 클래스로 문제를 풀고 지우고 새 문제를 다시 푸는 것이 아니라 각 문제 별로 내가 문제를 어떻게 풀었는 지 주석을 달면서 내가 풀었던 코드들을 기록 중이다.

스터디 기간은 한 달이 고정이지만 나는 매 달마다 스터디를 진행할 생각이다.

👍 팀 스터디 방식의 장점 및 개선하고 싶은 점

매 주 시간과 일정이 정해져 있다 보니 강제성 있는 루틴으로 꾸준함을 유지할 수 있는 점은 정말 좋은 것 같다.

실력 차이가 있어도 설명과 질문을 통해 서로 보완하는 점에서 서로가 실력이 늘어가는 것이 보인다.

특히 스터디 덕분에 “이번 주는 그래도 알고리즘 손 놓지는 않았다”는 안정감이 생긴다.

앞으로는 하루를 더 늘려 실전 코테처럼 시간 제한 엄격하고 한 문제당 접근 전략 먼저 말하고 코드 작성하면서 풀이를 노션, 벨로그에 간단히 정리하는 것도 좋아보인다.

스터디도 그냥 하면 흐지부지되기 쉬운데, 이렇게 구조를 잡아두니까 오래 가져갈 수 있을 것 같다.

🐈 2개월차 느낀 점

아직 프로젝트에 들어가기 전이지만, 이 시기에 팀 알고리즘 스터디를 병행하고 있는 것은 앞으로의 학습과 프로젝트 모두에 긍정적인 영향을 주고 있다고 느낀다.

웹 개발 역량을 쌓는 동시에 문제 해결 능력과 사고력을 계속 유지할 수 있었고, 무엇보다 팀원들과 함께 공부하는 리듬을 만들 수 있었다는 점이 가장 크다.

앞으로 프로젝트 단계에 들어가더라도 이 스터디 경험이 분명 큰 도움이 될 것이라 생각한다.

#구름딥다이브 #구름부트캠프 #부트캠프후기 #KDT후기 #구름서포터즈 #DEEPDIVE

1. pq_distribute 힌트의 용도

pq_distribute 힌트를 사용해 옵티마이저의 선택을 무시하고 사용자가 직접 조인을 위한 데이터 분배 방식을 결정할 수 있다. Parallel Query Distribution => 병렬 쿼리 분배라는 의미

pq_distribte 힌트를 사용할 경우

• 옵티마이저가 파티션된 테이블을 적절히 활용하지 못하고 동적 재분할을 시도할 때

• 기존 파티션 키를 무시하고 다른 키 값으로 동적 재분할하고 싶을 때

• 통계정보가 부정확하거나 통계정보를 제공하기 어려운 상황에서 실행계획을 고정시키고자 할 때

• 기타 여러 가지 이유로 데이터 분배 방식을 변경하고자 할 때

pq_distribute 힌트는 분배와 조인으로 봤을 때 조인에 앞서 데이터를 분배하는 과정에만 관여하는 힌트이다. pq_distrubute(x hash hash) 일 때, hash로 데이터를 재분배하면 끝나는 것이지 이후에, 해시조인을 하는 게 아니라는 뜻이다.

2. 구문 이해하기

select /*+ pq_distribute (table,                  -- inner 테이블명, 또는 alias
                    outer_distribution,    -- outer 테이블의 distribution 방식
                    inner_distribution)    -- inner 테이블의 distribution 방식
                    */ *                 

pq_distribute 힌트도 ordered 나 leading 힌트에 의해 먼저 처리되는 outer 테이블을 기준으로 그 집합과 조인하는 inner 테이블을 첫 번째 인자로 지정하면 된다.

조인 순서를 먼저 고정시키는 것이 중요하므로 ordered 나 leading 힌트를 같이 사용하는 것이 중요하다.

3. 분배방식 지정

• pq_distribute(inner, none, none)

  Full Partition Wise 조인으로 유도할 때 사용한다. 양쪽 테이블 모두 조인 컬럼에 대해 같은 기준으로 파티셔닝 돼 있을 때만 사용가능하다.

• pq_distribute(inner, partition, none)

  Partial Partition Wise 조인으로 유도할 때 사용한다. outer 테이블을 inner 테이블 파티션 기준에 따라 파티셔닝하라는 의미이다. inner 테이블이 조인 키 컬럼에 파티셔닝 돼 있을 때만 사용가능하다.

• pq_distribute(inner, none, partition)

  Partial Partition Wise 조인으로 유도할 때 사용한다. inner 테이블을 outer 테이블 파티션 기준에 따라 파티셔닝하라는 의미이다. outer 테이블이 조인 키 컬럼에 대해 파티셔닝 돼 있을 때만 사용가능하다.

• pq_distribute(inner, hash, hash)

  조인 키 컬럼을 해시 함수에 적용하고 반환된 값을 기준으로 양쪽 테이블을 동적으로 파티셔닝하라는 의미이다.

• pq_distribute(inner, broadcast, none)

  outer 테이블을 broadcast 하라는 의미이다.

• pq_distribute(inner, none, broadcast)

  inner 테이블을 broadcast 하라는 의미이다.

5. 병렬 처리에 관한 기타 상식

1. Direct Path I/O

오라클은 병렬 방식으로 Full Scan 할 때 버퍼 캐시를 거치지 않고 곧바로 PGA 영역으로 읽어들이는 Direct Path I/O 방식을 사용한다. 병렬도를 2로 뒀을 때 쿼리 속도는 2배보다 훨씬 더 향상되는 이유이다.

2. 병렬 DML

병렬 처리가 가능해지려면 쿼리, DML, DDL을 수행하기 전에 각각 아래와 같은 명령을 수행해야 한다.

alter session enable parallel query; -- 병렬 쿼리 활성화
alter session enable parallel dml;   -- 병렬 dml 활성화
alter session enable parallel ddl;   -- 병렬 ddl 활성화

병렬 쿼리와 병렬 ddl은 기본적으로 활성화 돼있어서 괜찮지만 병렬 dml은 사용자가 명시적으로 활성화해 주어야 한다.

오라클은 9iR1까지 병렬 DML은 파티션 기반 Granule 이었다. 한 세그먼트를 두 개 이상 프로세스가 동시에 갱신할 수 없었고, 파티션되지 않은 테이블이라면 병렬로 갱신할 수 없었다.

오라클은 9iR2부터 병렬 DML이 블록 기반 Granule로 바뀌었는데, 주의할 점은 병렬 DML을 수행할 때 Exclusive 모드 TM Lock이 걸린다는 것이다.

성능은 빨라져도 해당 테이블을 다른 트랜잭션이 DML을 수행하지 못하게 되므로 트랜잭션이 빈번한 주간에 병렬 DML을 사용해서는 안된다.

3. 병렬 인덱스 스캔

Index Fast Full Scan이 아닌 한 인덱스는 기본적으로 병렬로 스캔할 수 없다. 파티션된 인덱스일 때는 병렬 스캔이 가능하고, 파티션 기반 Granule이므로 병렬도는 파티션 개수 이하로만 지정할 수 있다.

4. 병렬 NL 조인

병렬 NL 조인은 Outer 테이블과 Inner 테이블이 둘 다 초대용량 테이블이고, Outer 테이블에 사용된 특정 조건의 선택도가 매우 낮은데 그 컬럼에 대한 인덱스가 없고, 수행빈도가 낮으며 Inner 쪽 조인 컬럼에는 인덱스가 있는 경우 사용하면 좋다.

5. 병렬 쿼리와 스칼라 서브쿼리

스칼라 서브쿼리를 기술하는 위치에 따라 QC가 수행하기도 하고 병렬 서버가 수행하기도 하며, 이는 병렬 쿼리 수행 속도에 큰 영향을 미친다. 병렬 처리 효과를 높이려면 부분범위처리, 전체범위처리 여부에 따라 스칼라 서브쿼리 위치를 옮기거나 아예 일반 조인문으로 변환하는 튜닝을 실시해야 한다.

6. 병렬 쿼리와 사용자 정의 함수

SQL 수행 결과는 병렬로 수행하는지와 상관없이 항상 일관된 결과를 보장해야 한다. 그런데 패키지 세션 변수를 참조하는 함수는 병렬로 실행했을 때 일관성이 보장되지 않기 때문에 오라클은 기본적으로 병렬 수행을 거부한다.

그럼에도, 사용자가 병렬 수행을 원할 때 사용하는 키워드가 parallel_enable이다. 이 키워드를 사용하면 함수 수행 결과가 달리질 수 있기 때문에, 세션 변수를 참조 하지 않았다면 굳이 이 키워드를 사용하지 않아야 한다. 사용하지 않아도 병렬 수행이 가능하기 때문이다.

7. 병렬 쿼리와 ROWNUM

병렬 쿼리, 병렬 DML 문장에 rownum을 사용하는 순간 병렬 처리에 제약을 받게 되므로 주의가 필요하다.

8. 병렬 처리 시 주의사항

병렬 쿼리의 적절한 사용 기준

• 동시 사용자 수가 적은 애플리케이션 환경 (야간 배치 프로그램, DW, OLAP)에서 직렬로 처리할 때보다 성능 개선 효과가 확실할 때

• OLTP성 환경이라도 작업을 빨리 완료함으로써 직렬로 처리할 때보다 오히려 전체적인 시스템 리소스 사용량을 감소시킬 수 있을 때

주의사항

• 병렬도는 반드시 지정하자.

• 실행계획에 P->P가 나타날 경우 지정한 병렬도의 2배수만큼 병렬 프로세스가 필요한 것이다.

• 쿼리 작성 시 병렬도를 모두 같게 지정하는 것이 바람직하다.

• parallel 힌트 사용 시 반드시 Full 힌트도 함께 사용 => 인덱스 스캔이 선택될 경우 parallel 힌트가 무시될 수 있다.

• parallel_index 힌트 사용 시 반드시 index 또는 index_ffs 힌트를 함께 사용 => Full Table Scan 이 선택될 경우 parallel_index 힌트 무시됨

• 병렬 DML 시 Exclusive 모드 TM Lock이 걸리므로 업무 트랜잭션이 발생하는 주간에는 자제

• 테이블이나 인덱스를 빠르게 생성하려고 parallel 힌트를 썼다면 작업 완료후 noparallel 힌트로 원상복구 시켜놔야 한다.

• 부분범위처리 방식으로 조회하면서 병렬 쿼리를 사용한 경우에는 필요한 만큼 데이터를 Fetch 한 후 곧바로 커서를 닫아 주어야 한다.

1. 병렬 Order by

order by를 병렬로 수행하려면 테이블 큐를 통한 데이터 재분배가 필요한데, 쿼리 수행이 완료된 직후에 같은 세션에서 v$pq_tqstat를 쿼리해 보면 테이블 큐를 통한 데이터 전송 통계를 확인해 볼 수 있다.

병렬 쿼리 수행 속도가 빠르지 않다면 테이블 큐를 통한 데이터 전송량에 편차가 크지 않은지 확인할 필요가 있는데, 이때 v$pq_tqstat가 유용하다.

2. 병렬 Group by

order by와 group by를 병렬로 처리하는 내부 수행원리는 기본적으로 같다.

병렬 hash group by (group by)와 병렬 sort group by (group by + order by)는 데이터 분배 방식의 차이가 있다. group by 키 정렬 순서에 따라 분배하느냐, 해시 함수 결과 값에 따라 분배하느냐의 차이이다.

결과 집합을 QC에게 전달할 때도, sort group by는 값 순서대로 QC(ORDER)이 나타나고 hash group by는 먼저 처리가 끝난 순서대로 QC(RANDOM)가 나타난다.

✅ Group by가 두 번 나타날 때의 처리 과정

병렬 group by 시 실행계획에 group by가 두 번 나타나는 경우가 있다. 그 이유는 group by 하는 컬럼의 선택도에 있다.

선택도가 높은 컬럼을 group by 컬럼에 두면 첫 번째 서버 집합이 읽은 데이터를 먼저 group by 하고 나서 두 번째 서버 집합에 전송해 프로세스 간 통신량을 줄여 병렬 처리 과정에서 생기는 병목을 줄일 수 있다.

선택도가 낮은 컬럼으로 group by 할 때도 강제로 이 방식을 사용하게 하려면 _groupby_nopushdown_cut_ratio 파라미터를 0으로 하면 된다.

11g부터는 gby_pushdown, no_gby_pushdown 힌트가 추가돼 파라미터 변경 없이도 사용자가 group by 방식을 조정할 수 있다.

3. 병렬 조인

병렬 조인 메커니즘은, 병렬 프로세스들이 서로 독립적으로 조인을 수행할 수 있도록 데이터를 분배하는 것이 중요하다. 분배작업이 완료되면 프로세스 간에 서로 방해받지 않고 각자 할당받은 범위 내에서 조인을 완료한다.

병렬 조인 방식은 두 가지가 있다.

1. 파티션 방식

Partition-Pair끼리 조인을 수행한다.

1. Full Partition Wise 조인 - 둘 다 같은 기준으로 파티션된 경우

조인에 참여하는 두 테이블이 조인 컬럼에 대해 같은 기준으로 파티셔닝돼 있는 경우이다.

다른 병렬 조인은 두 개의 서버집합이 필요하지만 , 여기서는 하나의 서버집합만 필요하다. Full Partition Wise 조인은 파티션 기반 Granule이므로 서버 프로세스 개수는 파티션 개수 이하로 제한된다.

파티션 방식은 리스트이든 Range이든 해시이든 두 테이블이 조인 컬럼에 대해 같은 방식, 같은 기준으로 파티셔닝 돼 있다면 서로 방해받지 않고 Partition Pair 끼리 독립적인 병렬 조인이 가능하기 때문에 상관없다.

조인 방식도 NL 조인, 소트 머지 조인, 해시 조인 모두 사용 가능하다.

2. Partial Parition Wise 조인 - 둘 중 하나만 파티셔닝된 경우

둘 중 한 테이블만 조인 컬럼에 대해 파티셔닝된 경우, 다른 한쪽 테이블을 같은 기준으로 동적으로 파티셔닝하고 나서 각 Partition-Pair를 독립적으로 병렬 조인하는 것을 말한다. 둘 다 파티셔닝 돼 있지만 파티션 기준이 서로 다른 경우도 이 방식으로 조인한다.

한쪽을 동적으로 파티셔닝하기 위해서는 데이터 재분배가 선행되어야 한다. 즉, Inter-Operation parallelism을 위해 두 개의 서버 집합이 필요하다.

3. 동적 파티셔닝 - 둘 다 파티셔닝되지 않은 경우

이 경우 오라클은 두 가지 방식 중 하나를 사용한다.

• 양쪽 테이블을 동적으로 파티셔닝하고 Full Partition Wise 조인

  첫 번째 서버 집합은 데이터를 분배하는 역할이고, 두 번쨰 서버 집합은 받은 데이터를 파티셔닝하는 역할을 한다. 메모리 공간이 가득차면 Temp 테이블스페이스를 활용해 파티셔닝을 한다. 양쪽을 동적으로 파티셔닝하니 때문에 두 테이블 다 데이터 재분배가 일어난다.

1. 첫 번째 서버 집합이 테이블을 읽어 두 번째 서버집합에게 전송한다.

2. 첫 번째 서버 집합이 다른 테이블을 읽어 두 번째 서버집합에게 전송한다.

3. 양쪽 테이블 모두의 파티셔닝을 담당한 두 번째 서버집합이 각 Partition-Pair에 대해 독립적으로 병렬 조인을 수행한다.

양쪽 동적 파티셔닝의 특징은 조인을 본격적으로 수행하기전 메모리 자원과 Temp 테이블스페이스 공간을 많이 사용하는 것이다. 양쪽 다 파티셔닝 하므로 양쪽 테이블 모두에 대한 전체범위처리가 불가피하다.

결론적으로 양쪽 동적 파티셔닝은 어느 한 쪽도 조인 컬럼 기준으로 파티셔닝되지 않은 상황에서 두 테이블 모두 대용량 테이블이고 조인 컬럼의 데이터 분포가 균일할 때 유용한 병렬 조인 방식이다.

• 한쪽 테이블을 Broadcast 하고 나서 조인

  두 테이블 중 작은 쪽을 반대편 서버 집합의 "모든" 프로세스에 Broadcast하고 나서 조인을 수행하는 방식이다.

1. 첫 번째 서버 집합에 속한 프로세스들이 각자 읽은 작은 테이블의 레코드를 두 번째 서버 집합에 속한 모든 병렬 프로세스에게 전송한다.

2. 두 번째 서버 집합에 속한 프로세스들이 각자 맡은 범위의 큰 테이블을 읽으면서 병렬로 조인을 수행한다. 1번이 완료되면 두 번째 서버집합에 속한 프로세스 모두 작은 테이블의 완전한 집합을 갖게 되므로 프로세스 간 상호간섭 없이 독립적으로 조인 수행이 가능하다.

양쪽 테이블 모두 파티션되지 않았을 때 1차적으로 Broadcast 방식을 고려한다. =>한쪽 테이블이 매우 작을 때 Broadcast 이후 조인할 때 NL, 소트 머지, 해시 조인 모두 가능하다. 작은 테이블은 Broadcast 하기 때문에 전체범위처리가 불가피하지만 큰 테이블은 부분 범위 처리가 가능하다.

병렬 처리란, SQL 문이 수행해야 할 작업 범위를 여러 작은 단위로 나누어 여러 프로세스나 쓰레드가 동시에 처리하는 것을 말한다.

1. Query Coordinator과 병렬 서버 프로세스

QC (Query Coordinator) 는 병렬 SQL문을 발행한 세션을 말하고, 병렬 서버 프로세스는 실제 작업을 수행하는 개별 세션들을 말한다.

QC의 역할

1. 병렬 SQL이 시작되면 QC는 사용자가 지정한 병렬도(DOP)와 오퍼레이션 종류에 따라 하나 또는 두 개의 병렬 서버 집합을 할당한다. 먼저 서버 풀로부터 필요한 만큼 서버 프로세스를 확보하고, 부족한 서버 프로세스는 새로 생성한다.

2. QC는 각 병렬 서버에게 작업을 할당한다.

3. 병렬로 처리하도록 사용자가 지시하지 않은 테이블은 QC가 직접 처리한다.

4. QC는 각 병렬 서버로부터 결과를 통합하는 작업을 수행한다.

5. QC는 쿼리의 최종 결과집합을 사용자에게 전송하며, DML 일 때는 갱신 건수를 집계해서 전송해준다. 쿼리 결과를 전송하는 단계에서 수행되는 스칼라 서브쿼리도 QC가 수행한다.

병렬 처리에서 실제 QC 역할을 담당하는 프로세스는 SQL문을 발행한 사용자 세션 자신이다.

2. Operation Parallelism

Intra-Operation Parallelism과 Inter-Operation Parallelism으로 나뉜다.

1. Intra-Operation Parallelism

한 병렬 서버 집합에 속한 여러 프로세스가 서로 배타적인 범위를 독립적으로 동시에 처리하는 것으로, 하나의 오퍼레이션을 동시에 병렬처리 하는 것이다. 이때는 절대로 통신이 발생하지 않는다.

2. Inter-Operation Parallelism

서로 다른 오퍼레이션을 동시에 병렬 처리하는 것이다. 이때는 항상 프로세스 간 통신이 발생된다.

3. 테이블 큐

쿼리 서버 집합 간(P->P) 또는 QC와 쿼리 서버 집합 간(P->S, S->P) 데이터 전송을 위해 연결된 파이프 라인을 '테이블 큐' 라고 한다.

테이블 큐에 부여된 :TQ10000, :TQ10001 같은 이름을 '테이블 큐 식별자' 라고 한다.

쿼리 서버 집합 간 (P->P) Inter-Parallelism이 발생할 때는 병렬도의 배수(x2)만큼 서버 프로세스가 필요하다.

테이블 큐에는 병렬도의 제곱(^2)만큼 파이프 라인이 필요하다. (P->P)

✅ 소비자 / 생산자 모델

테이블 큐에는 항상 생산자와 소비자가 존재한다. 파이프라인의 시작이 생산자이고 도착이 소비자이다. select문장의 최종 소비자는 항상 QC가 될 것이다.

Inter-Operation Parallelism이 나타날 때, 소비자 서버 집합은 from절에 테이블 큐를 참조하는 서브 SQL을 가지고 작업을 수행한다.

✅ 병렬 실행계획에서 생산자와 소비자 식별

10g 이후부터는 생산자에 'PX SEND' , 소비자에 'PX RECEIVE' 가 표시되므로 테이블 큐를 통한 데이터 분배 과정을 좀 더 쉽게 알 수 있게 되었다.

각 오퍼레이션이 어떤 서버 집합에 속한 병렬 프로세스에 의해 수행되는지는 'TQ' 컬럼이 보이는 서버 집합 식별자를 통해 확인할 수 있다.

통신이 일어날 때마다 'NAME' 컬럼에 테이블 큐가 표시된다.

4. 'IN-OUT' 컬럼 정보

IN-OUT 컬럼의 정보는 plan_table을 쿼리할 때 other_tag 컬럼에서 가져온 것이며, 병렬 쿼리를 이해하는 데에 매우 중요한 정보를 제공한다.

✅ S->P : PARALLEL_FROM_SERIAL

QC가 읽은 데이터를 테이블 큐를 통해 병렬 서버 프로세스에게 전송하는 것이다.

✅ P->S : PARALLEL_TO_SERIAL

각 병렬 서버 프로세스가 처리한 데이터를 QC에게 전송하는 것이다. 병렬 프로세스로부터 QC로 통신이 발생하므로 Inter-Operation Parallelism에 속한다.

'PQ Distrib' 컬럼에 QC(ORDER) 라고 표시된 것은 QC에게 결과 데이터를 전송할 때 첫 번째 병렬 프로세스부터 마지막 병렬 프로세스까지 순서대로 진행함을 의미하며, SQL이 order by절을 포함할 때 나타난다.

order by가 없을 때는 QC(RANDOM)이라고 표시되며 병렬 프로세스들이 무순위로 QC에게 데이터를 전송함을 의미한다.

✅ P->P : PARALLEL_TO_PARALLEL

P->P가 나타날 때면 해당 오퍼레이션을 두 개의 서버 집합이 처리한다. 따라서 사용자가 지정한 병렬도보다 2배수만큼 병렬 프로세스가 필요하다.

데이터를 정렬 또는 그룹핑하거나 조인을 위해 동적으로 파티셔닝할 때 사용되며, 첫 번째 병렬 서버 집합이 읽거나 가공한 데이터를 두 번째 병렬 서버 집합에 전송하는 과정에서 병렬 프로세스간 통신이 발생하므로 Inter-Operation Parallelsim에 속한다.

✅ PCWP : PARALLEL_COMBINED_WITH_PARENT

한 서버 집합이 현재 스텝과 그 부모 스텝을 모두 처리함을 의미한다. PCWP도 병렬 오퍼레이션이지만 한 서버 집합 내에서는 프로세스 간 통신이 발생하지 않으므로 Intra-Operation Parallelism에 속한다.

즉, 한 서버 집합에 속한 서버 프로세스들이 각자 맡은 범위 내에서 두 스텝 이상의 오퍼레이션을 처리하는 것이며, 자식 스템의 처리 결과를 부모 스텝에서 사용할 뿐 프로세스 간 통신은 필요하지 않다.

✅ PCWC : PARALLEL_COMBINED_WITH_CHILD

한 서버 집합이 현재 스텝과 그 자식 스텝을 모두 처리함을 의미한다. PCWC도 병렬 오퍼레이션이지만 한 서버 집합 내에서는 프로세스 간 통신이 발생하지 않으므로 Intra-Operation Parallelism에 속한다.

정리

1. S->P, P->P, P->S는 프로세스 간 통신 발생

2. PCWP, PCWC는 프로세스간 통신이 발생하지 않고, 각 병렬 서버가 독립적으로 여러 스텝을 처리할 떄 나타남. 하위 스텝의 출력 값이 상위 스텝의 입력 값으로 사용

3. P->P, P->S, PCWP, PCWS는 병렬 오퍼레이션인 반면 S->P는 직렬 오퍼레이션이다.

병렬 쿼리 실행계획에 S->P가 나타난다면 해당 오퍼레이션이 병목 지점인지 확인해 봐야 한다. 대용량 데이터량을 처리한다면 병렬 오퍼레이션으로 바꾸는 것을 고려해야 한다.

5. 데이터 재분배

IN-OUT 오퍼레이션 중에서 S->P, P->P가 데이터 재분배와 연관이 있다. 데이터 재분배 방식은 5가지가 있다.

1. RANGE (P->P)

order by 또는 sort group by를 병렬로 처리할 때 사용된다. 정렬 작업을 맡은 두 번째 서버 집합의 프로세스마다 처리 범위를 지정하고 나서, 데이터를 읽는 첫 번째 서버 집합이 두 번째 서버 집합의 정해진 프로세스에게 "정렬 키 값에 따라" 분배하는 방식이다.

2. HASH (P->P, S->P)

조인이나 hash group by를 병렬로 처리할 때 사용된다. 조인 키나 group by 키 값을 해시 함수에 적용하고 리턴된 값에 데이터를 분배하는 방식이다.

3. BROADCAST (P->P, S->P)

QC나 첫 번째 서버 집합에 속한 프로세스들이 각각 읽은 데이터를 두 번째 서버 집합에 속한 "모든" 병렬 프로세스에게 전송하는 방식이다. 병렬 조인에서 크기가 매우 작은 테이블이 있을 때 사용된다.

4. KEY

특정 컬럼들을 기준으로 테이블 또는 인덱스를 파티셔닝할 때 사용하는 분배 방식이다. 실행계획에는 PARTITION(KEY) 로 표시된다.

• Partial Partition-Wise 조인

• CTAS문장으로 파티션 테이블을 만들 때

• 병렬로 글로벌 파티션 인덱스를 만들 때

5. ROUND-ROBIN

파티션키, 정렬 키, 해시 함수 등에 의존하지 않고 반대편 병렬 서버에 무작위로 골고루 분배시켜 데이터를 분배할 때 사용된다.

6. Granule

데이터를 병렬로 처리할 때 일의 최소 단위를 'Granule' 이라고 하며, 병렬 서버는 한 번에 하나의 Granule씩만 처리한다.

✅ 블록 Granule

블록 기반 Granule은, 파티션 테이블 여부와 상관없이 병렬 오퍼레이션에 적용되는 기본 작업 단위이다. 9iR2부터는 파티션 여부, 파티션 개수와 무관하게 병렬도를 지정할 수 있다. 실행계획 상에는 'PX BLOCK ITERATOR' 라고 표시된다. Granule 크기와 총 개수는 오브젝트 사이즈와 병렬도에 따라 QC가 동적으로 결정한다.

✅ 파티션 Granule

파티션 기반 Granule은, 각 병렬 서버 프로세스들이 할당받은 테이블 파티션 전체를 처리할 책임을 진다. 한 파티션을 두 개 프로세스가 함께 처리할 수 없으니 병렬도는 파티션 개수 이하로만 지정할 수 있다. 실행계획 상에는 'PX PARTITION RANGE ALL' 이나 'PX PARTITION RANGE ITERATOR' 이라고 표시된다. ALL은 파티션 전체, ITERTOR은 일부 파티션만 읽는다.

Granule 개수가 테이블과 인덱스의 파티션 구조에 의해 정적으로 결정된다. 병렬도보다 파티션 개수가 상당히 많을 때 유용하다. (약 3배)

파티션 Granule의 작업 수행

• Partition-Wise 조인 시

• 파티션 인덱스를 병렬로 스캔하거나 갱신할 때

• 9iR2 이전에서의 병렬 DML

• 파티션 테이블 또는 파티션 인덱스를 병렬로 생성할 때

1. 인덱스 파티션 유형

인덱스는 파티션 인덱스와 비파티션 인덱스로 나뉘고 파티션 인덱스는 로컬과 글로벌로 나뉜다.

• 비파티션 인덱스

• 파티션 인덱스

  • 글로벌 파티션 인덱스

  • 로컬 파티션 인덱스

파티션이 안된 비파티션 테이블은 비파티션 인덱스와 글로벌 파티션 인덱스를 가질 수 있다. 파티션된 파티션 테이블은 모든 인덱스가 비파티션 인덱스 및 모든 파티션 인덱스를 가질 수 있다.

2. 로컬 파티션 인덱스

로컬 파티션 인덱스는 각 인덱스 파티션이 테이블 파티션과 1:1 대응 관계를 가지며, 테이블 파티션 속성을 그대로 상속받는다. 파티셔닝을 전체로 하기 때문에 '로컬 인덱스' 라고도 부른다.

로컬 파티션 인덱스는 항상 파티션 테이블과 1:1 관계를 형성하므로 만약 테이블이 결합 파티셔닝돼 있다면 인덱스도 같은 단위로 파티셔닝된다.

로컬파티션 인덱스가 갖는 장점은 무엇보다 관리적 편의성에 있다. => 테이블 파티션에 변경이 생겨도 오라클이 알아서 인덱스 파티션과의 1:1 관계를 맞춰준다.

3. 비파티션 인덱스

비파티션 인덱스는 말 그대로 파티션하지 않은 인덱스를 말한다. 비파티션 인덱스와 비파티션 테이블은 1:1 관계지만, 파티션 테이블과는 1:M 관계를 갖는다. 즉, 테이블 파티션과는 하나의 인덱스 세그먼트가 여러 테이블 파티션 세그먼트와 관계를 갖는다. 그런 의미에서 비파티션 인덱스를 '글로벌 비파티션 인덱스' 라고도 부른다.

비파티션 인덱스는 기준 테이블의 파티션 구성이 변경되면 그때마다 인덱스가 unusable 상태로 바뀌고 인덱스를 재생성해줘야 한다.

4. 글로벌 파티션 인덱스

글로벌 파티션 인덱스는 테이블 파티션과 독립적인 구성을 갖도록 파티셔닝하는 것을 말한다. 글로벌 파티션 인덱스도 비파티션 인덱스와 같이 기준 테이블의 파티션 구성이 변경되면 그때마다 인덱스가 unusable 상태로 바뀌고 인덱스를 재생성해줘야 한다.

✅테이블 파티션과의 관계

인덱스를 테이블 파티션과 같은 키 컬럼으로 글로벌 파티셔닝한다면 파티션 기준 값을 어떻게 정의하느냐에 따라 1:M, M:1, M:M 관계가 모두 가능하다. (본질은 M:M)

즉, 하나의 인덱스 파티션이 여러 테이블 파티션과 관계를 갖고, 반대로 하나의 파티션 테이블이 여러 인덱스 파티션과 관계를 갖는다.

인덱스를 테이블 파티션과 다른 키 컬럼으로 글로벌 파티셔닝할 수도 있는데, 이때는 항상 M:M 관계이다.

✅ 글로벌 해시 파티션 인덱스

9i까지는 글로벌 Range 파티션 인덱스만 가능했지만 10g부터는 글로벌 해시 파티션 인덱스도 가능해졌다. 즉, 테이블과 독립적으로 인덱스만 해시 키 값에 따라 파티셔닝할 수 있게 되었다. 글로벌 해시 파티션 인덱스는 Right Growing 인덱스 처럼 Hot 블록이 발생하는 인덱스의 경합을 분산할 목적으로 주로 사용된다. 글로벌 결합 인덱스 파티셔닝은 불가능하다.

5. Prefixed vs NonPrefixed

1. Prefixed

파티션 인덱스를 생성할 때, 파티션 키 컬럼을 인덱스 키 컬럼 왼쪽 선두에 두는 것을 말한다.

2. NonPrefixed

파티션 인덱스를 생성할 때, 파티션 키 컬럼을 인덱스 키 컬럼 왼쪽 선두에 두지 않는 것을 말한다. 파티션 키가 인덱스 컬럼에 아예 속하지 않을 때도 여기에 속한다.

로컬과 글로벌, Prefixed와 Nonprefixed를 조합하면 4가지 파티션 인덱스가 나온다. (조합 3 + 비파티션)

✅비파티션 인덱스

제약 없음

✅글로벌 Prefixed 파티션 인덱스

테이블 파티션 키와 인덱스 파티션 키가 같아도 되고 다를 수 있다. 인덱스 선두 컬럼에는 인덱스 파티션 키가 와야 된다.

✅로컬 Prefixed 파티션 인덱스

테이블 파티션 키와 인덱스 파티션 키가 같아야 한다. 인덱스 선두 컬럼에는 인덱스 파티션 키가 와야 한다.

✅로컬 NonPrefixe 파티션 인덱스

테이블 파티션 키와 인덱스 파티션 키가 같아야 한다. 인덱스 선두 컬럼에는 인덱스 파티션 키가 올 수 없다.

Unique 파티션 인덱스를 만들 때는 파티션 키 컬럼이 인덱스 컬럼에 반드시 포함돼 있어야 한다. 비파티션 인덱스는 제약이 없어서 상관없다.

6. 글로벌 파티션 인덱스의 효용성

글로벌 파티션 인덱스는 경합을 분산시키려고 글로벌 해시 파티셔닝하는 외에는 거의 사용되지 않는다.

비파티션 테이블에 대한 글로벌 파티션 인덱스는 테이블을 파티셔닝하지 않을 정도로 중소형급 테이블이면 굳이 인덱스만을 따로 파티셔닝 할 이유는 별로 없다.

파티션 테이블에 대한 글로벌 파티션 인덱스도 글로벌 파티션 인덱스보단 로컬 파티션 인덱스가 주로 사용되고 있다. (관리의 편의성, 인덱스 높이 조절 측면)

NL 조인에서 넓은 범위 조건을 가지고 Inner 테이블 액세스를 위해 자주 사용된다면 비파티션 인덱스가 가장 좋은 선택이다.

7. 로컬 Nonprefixed 파티션 인덱스의 효용성

로컬 Nonprefixed 파티션 인덱스는 이력성 데이터를 효과적으로 관리할 수 있게 해주고, 인덱스 스캔 효율성을 높이는 데에도 유리하다.

인덱스는 등치조건을 선두 컬럼에 두고 between같은 범위 조건 컬럼을 뒤쪽에 위치시켜야 한다. 그런 측면에서 로컬 Nonprefixed는 범위 조건 컬럼을 앞쪽에 위치시키지 않아도 되기 때문에 각 인덱스 파티션마다 필요한 최소 범위만 스캔하고 멈출 수 있다.

✅ 글로벌 Prefixed 파티션 인덱스와의 비교

글로벌 파티션 인덱스는 Prefixed 파티션만 허용되므로 거래일자처럼 범위검색 조건으로 자주 사용되는 컬럼이 선두일 때 로컬 Prefixed 파티션과 마찬가지로 인덱스 스캔 효율이 나쁘다. 또한, 과거 파티션을 제거하고 신규 파티션을 추가해야하는 관리적 부담이 크다.

✅ 비파티션 인덱스와의 비교

비파티션 인덱스를 이용하더라도 관리적 부담은 글로벌 파티션과 동일하게 발생한다. 비파티션 인덱스는 병렬 쿼리를 허용하지 않아, 이때 로컬 Nonprefixed 파티션 인덱스라면 여러 병렬 프로세스가 각각 하나의 인덱스 세그먼트를 스캔하도록해 쿼리의 응답속도를 향상 시킬 수 있다.

8. 액세스 효율을 고려한 인덱스 파티셔닝 선택 기준

✅ DW성 애플리케이션 환경

DW/DSS 애플리케이션에는 날짜 컬럼 기준으로 파티셔닝된 이력성 대용량 테이블이 많다. 따라서 관리적 측면뿐만 아니라 병렬 쿼리 활용 측면에서도 로컬 파티션 인덱스가 좋은 선택이다. 로컬 인덱스 중에는 Nonprefixed 파티션 인덱스가 성능적으로 유리할 때가 많다.

✅ OLTP성 환경

OLTP성 환경에서는 비파티션 인덱스가 대게 좋은 선택이다. 만약 테이블이 파티셔닝돼 있다면 인덱스 파티셔닝을 고려할 수 있는데, 특히 로컬 파티션 인덱스는 테이블 파티션에 대한 DDL 작업 후 인덱스를 재생성하지 않아도 돼 가용성 측면에서 유리하다.

OLTP 환경에서 로컬 파티션 인덱스를 선택했다면 Prefixed 파티션이든 Nonprefixed 파티션이든 검색 조건에 항상 사용되는 컬럼을 파티션 키로 선정하려고 해야 한다.

9. 인덱스 파티셔닝 제약을 고려한 데이터베이스 설계

중요한 제약 두 가지

1. Unique 파티션 인덱스를 정의할 때는 인덱스 파티션 키가 모두 인덱스 구성 컬럼에 포함돼야 한다.

2. 글로벌 파티션 인덱스는 Prefixed 파티션이어야 한다.

파티셔닝은 테이블과 인덱스 데이터를 파티션단위로 나누어 저장하는 것을 말한다. 테이블을 파티셔닝하면 하나의 테이블일지라도 파티션 키에 따라 물리적으로는 별도의 세그먼트에 데이터가 저장되며, 인덱스도 마찬가지다.

✅ 파티셔닝이 필요한 이유

• 관리적 측면

  파티션 단위 백업, 추가, 삭제, 변경

• 성능적 측면

  파티션 단위 조회 및 DML 수행

파티셔닝도 클러스터, IOT와 마찬가지로 관련 있는 데이터가 흩어지지 않고 물리적으로 인접하도록 저장하는 클러스터링 기술에 속한다. 클러스터와 다른 점은 세그먼트에 저장한다는 것이다.

1. 파티션 기본 구조

✅ 수동 파티셔닝

오라클 버전 8부터 파티션 테이블이 처음 제공됐다. 이전 버전에서 파티션 뷰를 통해 직접 파티션 기능을 구현했으며, 이를 '수동 파티셔닝' 이라고 부른다.

파티션 뷰는 Base 테이블 정의, 각 테이블 별 체크 제약 및 인덱스 생성, 통계수집 후 테이블들을 union all 연산을 통해 생성하면 된다.

파티션 뷰의 핵심 기능은 뷰 쿼리에 사용된 조건절에 부합하는 테이블만 읽어오는 것이고 이를 '파티션 Purning' 이라고 한다.

✅ 파티션 테이블

오라클 버전 8에서 도입된 파티션 테이블 기능을 이용하면 훨씬 간단하게 파티션을 정의할 수 있고 기능적으로도 더 낫다.

create table partition_table
partition by range(deptno) (
    partition p1 values less than(20),
    partition p2 values less than(30),
    partition p3 values less than(40)
)
as
select * from emp;

create index pt_empno_idx on partition_table(empno) LOCAL; -> 각 파티션별 개별적 인덱스

partiton by 절은 파티션 뷰의 Base 테이블에 체크 제약을 설정하는 것과 같은 역할을 한다. 파티션 테이블을 위처럼 정의하면, 세 개의 세그먼트가 생성되어 앞의 파티션 뷰와 구조적으로 같다.

이처럼 파티셔닝은, 내부에 몇 개의 세그먼트를 생성하고 그것들이 논리적으로 하나의 오브젝트임을 메타 정보로 딕셔너리에 저장해 두는 것이다.

파티션되지 않은 일반 테이블일때는 테이블과 세그먼트는 1:1 관계이지만, 파티션 테이블일 때는 1:M 관계이다. 인덱스를 파티셔닝할 때도 마찬가지다.

2. Range 파티셔닝

오라클 8 버전부터 제공된 가장 기초적인 파티셔닝 방식으로서, 주로 날짜 컬럼을 기준으로 한다.

create table order (ordernumber number, orderdate varchar2(8), customerid varchar(5), ...)

partition by range(orderdate) (
    partition p2009_q1 values less than ('20090401'),
    partition p2009_q2 values less than ('20090701'),
    partition p2009_q3 values less than ('20091001'),
    partition p2009_q4 values less than ('20100101'),
    partition p2010_q5 values less than ('20100401'),
    partition p9999_max values less than (MAXVAULE) -- orderdate > '20100401'
);

위 쿼리처럼 파티셔닝 테이블에 값을 입력하면 각 레코드를 파티션 키 컬럼 값에 따라 분할 저장하고, 읽을 때도 검색 조건을 만족하는 파티션만 읽을 수 있어 이력성 데이터 조회 시 성능이 크게 향상된다.

파티션 키는 하나 이상의 컬럼을 지정할 수 있고, 최대 16개까지 허용된다.

과거 데이터가 저장된 파티션만 백업하고 삭제하는 등 데이터 관리 작업을 효율적이고 빠르게 수행할 수 있는 큰 장점이 있다.

11g부터는 Range 파티션을 생성할 때 interval 기준을 정의함으로써 정해진 간격으로 파티션이 자동 추가되도록 할 수 있다.

3. 해시 파티셔닝

오라클 8i부터 Range 파티셔닝에 이어 해시파티셔닝을 제공한다.

파티션 키에 해시 함수를 적용한 결과 값이 같은 레코드를 같은 파티션 세그먼트에 저장해 두는 방식이며, 주로 고객 ID처럼 변별력이 좋고 데이터 분포가 고른 컬럼을 파티션 기준으로 선정해야 효과적이다.

검색할 때는 조건절 비교값에 해시 함수를 적용해 읽어야 할 파티션을 결정하며, 해시 알고리즘 특성상 등치조건 또는 IN-List 조건으로 검색할 때만 파티션 Pruning이 작동한다.

create table 고객 (고객id varchar2(5), 고객명 varchar2(10), ...)
partition by hash(고객id) partitions 4;

해시 파티셔닝의 테이블 파티셔닝 여부를 결정할 때는 데이터가 얼마나 고르게 분산될 수 있느냐가 가장 중요한 포인트이다. 해시 파티셔닝할 때 특히 데이터 분포를 신중히 고려해야 하는데, 사용자가 직접 파티션 기준을 정하는 Range과 리스트 파티셔닝과 다르게 해시 파티셔닝은 파티션 개수만 사용자가 결정하고 데이터를 분산시키는 해싱 알고리즘은 오라클에 의해 결정되기 때문이다.

오라클은, 특정 파티션에 데이터가 몰리지 않도록 하려면 파티션 개수를 2의 제곱으로 설정할 것을 권고한다. 이 규칙을 따르더라도 파티션 키 컬럼의 Distinct Value 개수가 적다면 데이터가 고르게 분산되지 않을 가능성이 높으므로, 이때는 리스트 파티션을 이용해 파티션 기준을 사용자가 수동으로 결정해 주는 것이 좋다.

✅ 병렬 쿼리 성능 향상

데이터가 모든 파티션에 고르게 분산돼 있거나, 각 파티션이 서로 다른 디바이스에 저장돼 있을 때 해시파티셔닝을 사용하면 병렬 I/O 성능을 극대화 시킬 수 있다. 반대로 말하면, 데이터가 고르게 분산되지 않을 때, 병렬 쿼리 효과는 반감된다.

✅ DML 경합 분산

병렬 쿼리 성능 향상뿐 아니라 동시 입력이 많은 대용량 테이블이나 인덱스에 발생하는 경합을 줄일 목적으로도 해시 파티셔닝을 사용한다. 대용량 거래 테이블일수록 DML 발생량이 많아 경합 발생 가능성도 그만큼 크다.

Right Growing 인덱스도 맨 우측 끝 블록에만 값이 입력되기 때문에 자주 경합지점이 되곤 하는데, 이때도 인덱스를 해시 파티셔닝함으로써 경합 발생 가능성을 줄일 수 있다.

위의 병렬 쿼리 성능 향상이나 DML 경합 분산 이 두 가지 모두 트랜잭션이 많이 발생하는 대용량 거래 테이블일 때야 효과가 있다. 단일 해시 파티셔닝보다는 [ Range + 해시 ]를 조합한 결합 파티셔닝을 주로 사용하는 이유이다.

4. 리스트 파티셔닝

오라클 9i부터 제공된 리스트 파티셔닝은, 사용자에 의해 미리 정해진 그룹핑 기준에 따라 데이터를 분할 저장하는 방식이다.

create table 인터넷매물 (물건코드 varchar2(5), 지역분류 varchar2(4), ...)
partition by list(지역분류) (
    partition p_지역1 values ('서울'),
    partition p_지역2 values ('경기', '인천'),
    partition p_지역3 values ('부산', '대구', '대전', '광주'),
    partition p_기타 values (DEFAULT)
);

Range 파티션에선 값의 순서에 따라 저장할 파티션이 결정되지만, 리스트 파티션에서는 순서와 상관없이 불연속적인 값의 목록으로써 결정된다.

해시 파티션과 비교하면, 해시 파티션은 오라클이 정한 해시 알고리즘을 사용하지만, 리스트 파티션은 사용자가 정의한 논리적인 그룹에 따라 분할한다.

리스트 파티셔닝은 오직 단일 컬럼으로만 파티션 키를 지정할 수 있다. 그리고 dafault 파티션을 생성해 두어야 한다.

5. 결합 파티셔닝

결합 파티셔닝을 구성하면 서브 파티션마다 세그먼트를 하나씩 할당하고, 서브 파티션 단위로 데이터를 저장한다.

즉, 주 파티션 키에 따라 1차적으로 데이터를 분배하고, 서브 파티션 키에 따라 최종적으로 저장할 위치를 결정한다.

8i에서는 [ Range + 해시 ] 형태만 가능했지만 9i부터는 [ Range + 리스트 ] 형태도 지원한다. 11g 부터는 주 파티션이 해시만 아니면 모든 조합을 지원한다.

✅ [ Range + 해시 ] 결합 파티셔닝

create table 주문 (주문번호 number, 주문일자 varchar2(8), 고객id varchar2(5), ...)
partition by range(주문일자)
    partition by hash(고객id) subpartition 8 (
      partition p2009_q1 values less than('20090401'),
      partition p2009_q2 values less than('20090701'),
      partition p2009_q3 values less than('20091001'),
      partition p2009_q4 values less than('20100101'),
      partition p2010_q5 values less than('20100401'),
      partition p9999_q1 values less than(MAXVAULE)
);

위 쿼리는 각 Range 파티션 내에서 다시 해시 알고리즘을 사용해 각 서브 파티션으로 데이터를 분할 저장한다.

select *
from 주문
where 주문일자 between '20090701' and '20090930'

이 쿼리로 주문 테이블을 탐색하면 Range 파티션 p2009_q3에 속한 8개의 서브 파티션을 탐색한다.

select *
from 주문
where 고객id = :custid

이 쿼리로 주문일자 조건없이 고객 id로만 조회하면, 각 Range 파티션당 하나씩 총 6개 서브 파티션을 탐색한다.

✅ [ Range + 리스트 ] 결합 파티셔닝

create table 판매 ( 판매점 varchar2(10), 판매일자 varchar2(8), ...)
partition by range(판매일자)
subpartition by list(판매점)
subpartition templete (
    subpartition 1st_01 values ('강남', '강북', '강서', '강동'),
    subpartition 1st_02 values ('부산', '대전'),
    subpartition 1st_03 values ('인천', '제주', '의정부'),
    subpartition 1st_99 values (DEFAULT))
(
partition p2009_q1 values less than('20090401'),
partition p2009_q2 values less than('20090701'),
partition p2009_q3 values less than('20091001'),
partition p2009_q4 values less than('20100101'));

위 쿼리는 판매 테이블을 판매일자 기준으로 분기별 Range 파티셔닝하고 그 안에서 다시 판매점 기준으로 리스트 파티셔닝하는 방법이다.

각 Range 파티션 내에서 사용자가 지정한 그룹핑 기준에 따라 리스트 서브 파티션으로 데이터를 분할 저장해서 Range와 리스트 파티셔닝의 이점을 둘 다 누릴 수 있다.

이 결합 파티셔닝은 초대형 이력성 테이블을 Range 파티셔닝하고, 각 파티션을 업무적으로 다시 분할하고자 할 때 주로 사용한다.

✅ 기타 결합 파티셔닝

11g부터 네 가지 형태의 결합 파티셔닝 기능이 추가되었다. 따라서 주 파티션이 해시 파티셔닝만 아니라면 모든 조합이 가능해졌다.

• Range - Range

• 리스트 - 해시

• 리스트 - 리스트

• 리스트 - Range

6. 11g에 추가된 파티션 유형들

✅ Reference 파티셔닝

반정규화가 필요한 데이터 모델에서 부모 파티션 테이블 키를 이용해 자식 테이블을 파티셔닝하는 기능이 도입되었는데, 이를 'Reference 파티션' 이라고 부른다. 이 기능을 사용하려면 자식 테이블의 컬럼에 not null과 fk 제약이 있어야 한다.

✅ Interval 파티셔닝

Range 파티션을 생성할 때 interval 기준을 정의함으로써 정해진 간격으로 파티션이 자동 추가되도록 할 수 있다. 특히 테이블을 일 단위로 파티셔닝했을 때 유용하다. inverval 수치만큼을 넘을 때마다 테이블에 파티션이 추가되도록 할 수 있다.

이 외에도 시스템 파티셔닝, 가상 컬럼 기반 파티셔닝 등이 11g에 추가되었다.

2. 파티션 Pruning

파티션 Pruning은 하드파싱이나 실행 시점에 SQL 조건절을 분석하여 읽지 않아도 되는 파티션 세그먼트를 액세스 대상에서 제외시키는 기능이다.

파티션 테이블에 대한 쿼리나 DML을 수행할 때 극적인 성능 개선을 가져다주는 핵심 원리가 파티션 Pruning에 있다.

1. 기본 파티션 Pruning

• 정적 파티션 Pruning

  파티션 키 컬럼을 상수 조건으로 조회하는 경우에 작동하며, 액세스할 파티션이 쿼리 최적화 시점에 미리 결정되는 것이 특징이다. 실행계획의 Pstart와 Pstop 컬럼에는 액세스할 파티션 번호가 출력된다.

• 동적 파티션 Pruning

  파티션 키 컬럼에 바인드 변수로 조회하면 쿼리 최적화 시점에는 액세스할 파티션을 미리 결정할 수 없다. 실행 시점이 돼서야 사용자가 입력한 값에 따라 결정되며, 실행계획의 Pstart와 Pstop 컬럼에는 'KEY' 라고 표시된다.

• NL 조인 할 때도 Inner 테이블이 조인 컬럼 기준으로 파티셔닝 돼 있다면 동적 Pruning이 작동한다.*

파티션 컬럼에 IN-List 조건을 사용하면 상수 값이라도 Pstart,Pstop 컬럼에 KEY(I)가 표시된다.

파티션 키 컬럼을 함수를 가공하거나 타입을 안 맞춰줘서 묵시적 형변환이 일어나면 파티션 Pruning이 일어나지 않아, 파티션 키 컬럼도 함부로 가공해서는 안 된다.

✅ 동적 파티션 Purning 시 테이블 레벨 통계 사용

바인드 변수를 사용하면 최적화 시점에 파티션을 확정할 수 없어 동적 파티션 Pruning이 일어나서, 쿼리 최적화에 테이블 레벨 통계가 사용된다. 반면, 정적 파티션 Pruning 일 때는 파티션이 확정되기 때문에 파티션 레벨 통계가 사용된다.

테이블 레벨 통계는 파티션 레벨 통계보다 부정확하기 때문에 옵티마이저가 잘못된 실행계획을 수립하는 경우가 생기며, 이는 바인드 변수 때문에 생기는 대표적인 부작용 중 하나이다.

조인에 사용되는 고급 파티션 Pruning 기법으로는 두 가지가 있다.

2. 서브쿼리 Pruning (8i~)

서브쿼리 Pruning이 일어나면 액세스해야 할 파티션 번호 목록이 구해지며, 필요한 파티션만 스캔할 수 있다. 실행계획 상 Pstart, Pstop에는 KEY(SQ)가 나타나며 SQ는 SubQuery를 뜻한다. 이 방식으로 파티션 Pruning 을 하면 Outer 테이블을 한 번 더 읽게 되므로, 서브쿼리 Pruning 적용 여부는 옵티마이저가 비용을 고려해 내부적으로 결정한다.

제거될 것으로 예상되는 파티션 개수가 상당히 많고, where 조건절을 가진 Outer 테이블이 파티션 테이블에 비해 상당히 작을 때만 서브쿼리 Pruning이 작동한다.

3. 조인 필터(=블룸 필터) Pruning (11g~)

서브쿼리 Pruning은 Outer 테이블을 한 번 더 액세스하는 추가 비용이 발생하기 때문에, 오라클은 11g부터 블룸 필터 알고리즘을 기반으로 한 조인 필터 Pruning 방식을 도입했다.

조인 필터 Pruning의 기능은 파티션 테이블과 조인할 때, 읽지 않아도 되는 파티션을 제거해 주는 것이다.

이 기능을 적용하면 실행계획에 part join filter create와 partition range join-filter를 포함하는 두 개 오퍼레이션 단계가 나타난다. 블룸 필터를 생성해서 블룸 필터를 이용해 파티션 Pruning을 하는 것이다.

블룸 필터의 역할은, 교집합이 아닌 원소를 찾는 역할이다. 조인 필터 Pruning 에서도 조인 대상 집합을 포함하는 파티션을 찾는 게 아니라, 포함하지 않는 즉 읽지 않아도 되는 파티션을 찾는 것이다.

4. SQL 조건절 작성 시 주의사항

partition m09 values less than('20091001'),          -- 0901 ~ 0930
partition m10 values less than('20091101'),. ...      -- 1001 ~ 1031

select *
from 고객
where 가입일 like '200910%'

위 쿼리에서 m10 파티션만 스캔하지 않고 m09 파티션도 스캔한다. 왜냐하면 m09 파티션에 '200910~~', '20091000$' 이런 문자열 형태로의 데이터가 있을 수 있기 때문이다.

따라서 위와 같이 일자로써 파티션 키 값을 정의했다면 between 연산자를 이용해 정확한 값 범위를 주고 쿼리해야 한다.

select *
from 고객
where 가입일 between '20091001' and '20091031'

파티션 설계와 상관없이 옵티마이저가 효율적인 선택을 할 수 있도록 하려면 between 연산자를 사용해야 한다. 만일 일일이 SQL을 수정하기 곤란하다면 연월로써 파티션 키 값을 다시 정의해 주면 like 연산자를 쓸 수 있다.

만약 소트 오퍼레이션 처리가 불가피하다면 메모리 내에서 처리를 완료할 수 있게 해야하고, Sort Area의 크기를 늘리는 방법도 있지만 그 전에 Sort Area를 적게 사용하도록 하는 방법을 찾아야 한다.

1. 소트를 완료 후 데이터 가공하기

가공하지 않은 상태로 정렬을 완료하고 나서 최종 출력할 때 가공하는 것이 Sort Area 사용을 최소화하는 방법이다.

2. Top-N 쿼리

✅ Top-N 쿼리 작동 시

select *
from (
    select wdate, wcount, wno, wprice
    from w
    where wcode = 'JY123'
    and wdate >= '20020625'
    order by wdate
)
where rownum <= 10;

오라클에서 위 쿼리를 [wcode + wdate] 인덱스를 사용한다면 옵티마이저는 인덱스를 이용해 order by 연산을 대체할 수 있다.

또한 rownum 조건을 사용해 n건에서 멈추도록 했으므로 조건절에 부합하는 레코드가 아무리 많아도 매우 빠른 속도를 낼 수 있다. (실행계획에 sort order by stopkey 표시)

✅ Top-N 쿼리 작동 안할 시

select *
from  (
    select a.*, rownum no
    from (
        select wdate, wcount, wno, wprice
        from w
        where wcode = 'JY123'
        and wdate >= '20020625'
        order by wdate
      ) a
 where no <= 10;

이 경우는 같은 양의 데이터를 읽고 정렬을 수행했지만 Top-N 쿼리 알고리즘이 작동하지 않아 디스크 소트를 이용해야 될 수도 있다. 실행계획상에는 stopkey가 표기되지 않는다.

3. 분석함수에서의 Top-N 쿼리

window sort 시에도 rank()나 row_number()을 쓰면 Top-N 쿼리 알고리즘이 작동해 max() 등의 함수를 쓸 때보다 소트 부하를 경감시켜 준다.

7. Sort 크기 조정

Sort Area 크기 조정을 통한 튜닝의 핵심은, 디스크 소트가 발생하지 않도록 하는 것을 1차 목표로 삼고 불가피할 때는 Onepass 소트로 처리되도록 하는 것이다.

오라클 9i부터 PGA 메모리 관리 방식을 지원한다.

Work Area

데이터 정렬, 해시 조인, 비트맵 머지, 비트맵 생성 등을 위해 사용하는 메모리 공간이다. 8i까지는 이들 Work Area의 기본 값을 관리자가 지정하고 조정했지만, 9i부터는 '자동 PGA 메모리 관리' 기능이 도입돼어 사용자가 일일이 그 크기를 조정하지 않아도 된다.

SGA는 sga_max_size 파라미터로 설정된 크기만큼 공간을 미리 할당한다. PGA는 자동 PGA 메모리 관리 기능을 사용한다고 해서 pga_aggregate_target 크기만큼의 메모리를 미리 할당해 두지는 않는다. 이 파라미터는 workarea_size_policy를 auto로 설정한 모든 프로세스들이 할당받을 수 있는 Work Area의 총량을 제한하는 용도로 사용된다.

오라클 8i 이전에는 프로세스를 위해 할당된 PGA 공간을 프로세스가 해제될 때까지 OS에 반환하지 않았다. 9i에서 자동 PGA 메모리 관리 방식이 도입되면서 프로세스가 더 이상 사용하지 않는 공간을 즉각 반환함으로써 다른 프로세스가 사용할 수 있도록 한다.

불합리한 데이터 모델이 소트 오퍼레이션을 유발하는 경우를 흔히 접할 수 있다.

✅ 사례 1 - M:M 관계를 갖도록 설계된 테이블

한쪽을 group by 해서 1:M 관계로 만들어주었지만 과거 데이터이관 시 발생한 예외 케이스 때문에 다시 M:M 모델이 되었다고 한다.

=> 데이터를 정제하고 1:M 관계를 갖도록 모델을 수정하였다. 그 결과, 불필요한 group by 연산을 제거할 수 있어 쿼리가 간단해지고 성능도 좋아졌다.

✅ 사례 2 - 자식 테이블에 통합시키는 경우

그 테이블의 조회가 빈번하다면, 조회할 때마다 테이블을 group by 해야 하기 때문에 성능이 좋을 리 없다.

4. 소트가 발생하지 않도록 SQL 작성

union을 사용한 쿼리는 두 집합 간 중복을 제거하려고 sort unique 연산을 수행한다. 만약 결과 값에 PK 컬럼을 포함하면 중복은 무조건 없기 때문에 union all 을 사용해야 한다. union all은 중복을 확인하지 않고 두 집합을 단순히 결합하므로 소트 부하가 없기 때문이다.

distinct를 사용하는 경우도 대부분 exists 서브쿼리로 대체함으로써 소트 연산을 없앨 수 있다. 소량의 데이터를 가지는 테이블을 Outer 테이블로 지정해 큰 테이블을 exists 서브쿼리로 필터링하는 방식을 사용하면 된다. 여기서 exists 서브쿼리의 가장 큰 특징은, 메인 쿼리로부터 건건이 입력 받은 값에 대한 조건을 만족하는 첫 번째 레코드를 만나는 순간 true를 반환하고 서브쿼리 수행을 마친다는 점이다. => 따라서 서브쿼리의 조건절에 맞게 인덱스만 구성해주면 최적으로 수행된다.

5. 인덱스를 이용한 소트 연산 대체

인덱스는 항상 키 컬럼 순으로 정렬된 상태를 유지하므로 이를 이용해 소트 오퍼레이션을 생략할 수 있다.

1. Sort Order By 대체

select custid, name, resno, status, tell
from customer
where region = 'A'
order by custid

위 쿼리를 수행할 때 [region + custid] 순으로 구성된 인덱스를 사용한다면 sort order by를 대체 할 수 있다.

이 방식으로 수행한다면 region = 'A' 조건을 만족하는 전체 로우를 읽지 않고도 결과집합 출력을 시작할 수 있어 OLTP 환경에서 극적인 성능 개선 효과를 가져다 준다.

물론, 소트해야 할 대상 레코드가 무수히 많고 그 중 일부만 읽고 멈출 수 있는 업무에서만 이 방식이 유리하다.

2. Sort Group By 대체

select region, avg(age), count(*)
from customer
group by region

region이 선두 컬럼인 결합 인덱스나 단일 컬럼 인덱스를 사용한다면 위 쿼리에 필요한 sort group by 연산을 대체할 수 있다. (실행계획은 sort group by nosort 표기)

인덱스를 이용한 nosort 방식으로 수행될 때는 group by 오퍼레이션에도 불구하고 부분범위처리가 가능해져 OLTP 환경에서 매우 극적인 성능 개선 효과를 얻을 수 있다.

3. 인덱스가 소트 연산을 대체하지 못하는 경우

select *
from emp
order by sal

만약 sal을 선두로 시작하는 인덱스가 있는 경우에도 sal을 기준으로 정렬을 하게된다면, 옵티마이저는 인덱스를 사용하지 않는 편이 낫다고 판단 해 all_rows 모드를 선택한다.

create index emp_deptno_sql on emp(deptpno,sal);

select *
from emp
where deptno = 10
order by sal null first  

위의 경우도 소트 오퍼레이션이 나타난다. 단일 인덱스일 때는 null 값을 저장하지 않지만, 결합 인덱스일 때는 null 값을 가진 레코드를 맨 뒤에 저장한다. 따라서 null 값부터 출력하려고 할 떄는 인덱스를 이용하더라도 소트가 발생한다.

SQL 튜닝에서 빠질 수 없는 요소가 소트 튜닝이다. 소트 오퍼레이션은 수행과정에서 CPU와 메모리를 많이 사용하고, 데이터량이 많을 때는 디스크 I/O 까지 일으킨다.

많은 서버 리소스를 사용하는 것도 문제지만 부분범위처리를 불가능하게 해 OLTP 환경에서 애플리케이션 성능을 저하시키는 주요인이다.

1. 소트 수행 과정

SQL 수행 도중 데이터 정렬이 필요할 때면 오라클은 PGA 메모리에 Sort Area를 할당하는데, 그 안에서 처리를 완료할 수 있는지 여부에 따라 소트를 두 가지 유형으로 나눈다.

• 메모리 소트

  전체 데이터의 정렬 작업을 메모리 내에서 완료하는 것을 말하며, 'Internal Sort' 라고도 한다.

• 디스크 소트

  할당받은 Sort Area 내에서 정렬을 완료하지 못해 디스크 공간까지 사용하는 경우를 말한다. 'External Sort' 라고도 한다.

소트를 하는 세 가지 방법이 있다.

1. Optimal 소트

소트 오퍼레이션이 메모리 내인 Sort Area 내에서만 이루어진다. => 최적

데이터의 양이 많을 때 정렬된 중간 결과집합을 Temp 테이블스페이스의 Temp 세그먼트에 임시 저장한다. => 이것이 디스크 소트

Sort Area가 찰 때마다 Temp 영역에 저장해 둔 중간 단계의 집합을 'Sort Run' 이라고 한다. 정렬 완료 후 결과 집합을 머지해야 최종 결과 집합이 나온다.

이처럼, 정렬된 결과를 Temp 영역에 임시 저장했다가 다시 읽어들이는 디스크 소트가 발생하는 순간 SQL 수행 성능은 급격하게 나빠진다.

2. Onepass 소트

Sort Area에서의 정렬이 꽉차 정렬 대상 집합이 디스크에 한 번만 쓰인다. => 나쁘진 않음

3. Multipass 소트

크기가 너무 커서 정렬 대상 집합이 디스크에 여러 번 쓰인다. => 최악

2. 소트 오퍼레이션 측정

autotrace의 sorts (memory) 가 메모리 소트이고, sorts (disk) 가 디스크 소트이다.

소트과정에서 발생하는 디스크 I/O는 Direct Path I/O 방식을 사용하므로 버퍼 캐시를 경유하는 일반적인 디스크 I/O에 비해 무척 가볍다.

3. Sort Area

1. PGA (Process/Program/Private Global Area)

각 오라클 서버 프로세스는 자신만의 PGA 메모리 영역을 할당받고, 이를 프로세스에 종속적인 고유 데이터를 저장하는 용도로 사용한다.

PGA는 다른 프로세스와 공유되지 않는 독립적인 메모리 공간으로서, 래치 매커니즘이 필요 없어 똑같은 개수의 블록을 읽더라도 SGA 버퍼 캐시에서 읽는 것보다 훨씬 빠르다.

2. UGA (User Global Area)

전용 서버 방식으로 연결할 때는 프로세스와 세션이 1:1 관계를 갖지만, 공유 서버 방식으로 연결할 때는 1:M 관계를 갖는다. 즉, 세션이 프로세스 개수보다 많아질 수 있는 구조로서, 하나의 프로세스가 여러 개 세션을 위해 일한다. 이때, 각 세션을 위한 독립적인 메모리 공간이 필요한데, 이를 UGA라고 한다.

UGA는 서버 프로세스와의 연결 방식에 따라 그 위치가 달라지는데, 전용 서버 방식으로 연결할 때는 PGA, 공유 서버 방식으로 연결할 때는 SGA에 할당한다. SGA에 할당했을 때는 Large Pool이 설정됐으면 Large Pool에, 그 외에는 Shared Pool에 할당된다.

• 하나의 프로세스는 하나의 PGA만을 갖는다.

• 하나의 세션은 하나의 UGA만을 갖는다.

• PGA에는 세션과 독립적인 프로세스만을 관리한다.

• UGA에는 프로세스와 독립적인 세션만을 관리한다.

• 거의 대부분 전용 서버 방식을 사용하므로 세션과 프로세스는 1:1 관계이고, 따라서 UGA도 PGA 내에 할당된다고 이해하면 된다.

3. CGA (Call Global Area)

PGA에 할당되는 메모리 공간으로는 CGA도 있다. 오라클은 하나의 데이터베이스 Call을 넘어서 다음 Call까지 계속 참조되어야하는 정보는 UGA에 담고, Call이 진행되는 동안에만 필요한 데이터는 CGA에 담는다.

Sort Area는 어떤 메모리 영역에 할당할까??

=> Sort Area가 할당되는 위치는 SQL문 종류와 소트 수행 단계에 따라 다르다.

DML 문장은 하나의 Execute Call 내에서 모든 데이터 처리를 완료하며, Execute Call이 끝나는 순간 자동으로 커서가 닫힌다. 따라서 DML 도중 정렬한 데이터를 Call을 넘어서까지 참조할 필요가 없으므로 Sort Area를 CGA에 할당한다.

SELECT 문의 데이터 정렬은 상황에 따라 다르다. SELECT 문장이 수행되는 가장 마지막 단계에서 정렬된 데이터는 계속 이어지는 Fetch Call에서 사용되어야 한다.

✅ 요약

1. DML 문장 수행 시 발생하는 소트 -> CGA에서 수행

2. SELCT 문장 수행 시

   => 쿼리 중간 단계의 소트 CGA에서 수행. sort_area_retained_size의 제약이 있다면, CGA에서 소트 수행 이 제약만큼의 UGA를 할당해 정렬된 결과를 담았다가 이후 Fetch Call에서 Array 단위로 전송

   => 결과집합을 출력하기 직전 단계에서 수행하는 소트 sort_area_retaines_size 제약이 있다면, CGA에서 소트 수행 이 제약만큼의 UGA를 할당해 정렬된 결과를 담았다가 이후 Fetch Call에서 Array 단위로 전송 sort_area_retains-size 제약이 없다면, 곧바로 UGA에서 소트 수행

CGA에 할당된 Sort Area는 하나의 Call이 끝나자마자 PGA에 반환된다. UGA에 할당된 Sort Area는 마지막 로우가 Fetch 될 때 비로소 UGA Heap에 반환되고, 거의 대부분 그 부모 Heap에도 즉각 반환된다.

4. 소트 튜닝 요약

소트 오퍼레이션은 메모리 집약적일뿐만 아니라 CPU 집약적이기도 하며, 데이터량이 많을 때는 디스크 I/O까지 발생시키므로 쿼리 성능을 좌우하는 가장 중요한 요소다. 특히, 부분범위처리를 할 수 없게 만들어 OLTP 환경에서 성능을 떨어뜨리는 주 요소이다. 따라서 소트가 발생하지 않도록 SQL을 작성해야 되고, 소트가 불가피하다면 메모리 내에서 수행을 완료할 수 있도록 해야 한다.

2. 소트를 발생시키는 오퍼레이션

1. Sort Aggregate

로우를 대상으로 집계를 수행할 때 나타나는데, 'sort'라는 표현을 사용하지만 실제 소트가 발생하진 않는다.

2. Sort Order By

데이터 정렬을 위해 order by 오퍼레이션을 수행할 때 나타난다.

3. Sort Group By

sort group by는 소팅 알고리즘을 사용해 그룹별 집계를 수행할 때 나타난다. => group by + order by

✅ Hash Group By 와 비교

10gR2에서 hash group by 방식이 도입되면서, order by 절과 함께 명시하지 않으면 대부분 hash group by 방식으로 처리된다. => group by

hash group by는 정렬을 수행하지 않고 해싱 알고리즘을 사용해 데이터를 그룹핑한다. 읽는 로우마다 group by 컬럼의 해시 버킷을 찾아 그룹별로 집계항목을 갱신하는 방식이다. sort group by와 hash group by의 차이는 그룹을 찾아가는 방식이 해시 알고리즘이냐 소팅 알고리즘이냐의 차이이다.

사실 오라클은 9i부터 이미 group by 결과가 보장되지 않는다고 여러 문서를 통해 공표했다.

결론적으로 정렬된 group by 결과를 얻고자 한다면, 실행계획에 'sort group by'라고 표시돼도 반드시 order by를 명시해줘야 한다. => group by만 써도 sort group by라고 뜨는 경우가 있다. ex) group by + distinct count 연산

4. Sort Unique

Unnesting된 서브쿼리가 M쪽 집합이거나 Unique 인덱스가 없다면, 그리고 세미 조인으로 수행되지도 않는다면 메인 쿼리와 조인되기 전에 sort unique 오퍼레이션이 먼저 수행된다.

만약 PK/Unique 제약 또는 Unique 인덱스를 통해, Unnesting된 서브쿼리의 Uniqueness이 보장된다면 sort unique 오퍼레이션은 생략된다.

union, minus, intersect 같은 집합 연산자도 sort unique 오퍼레이션이 나타난다.

distinct 연산도 sort unique 오퍼레이션이 나타난다. => 오라클 10gR2부터는 distinct 연산에서 order by 생략하면 hash unique 오퍼레이션이 나타난다.

5. Sort Join

sort join 오퍼레이션은 소트 머지 조인을 수행할 때 나타난다.

6. Window Sort

window sort는 분석함수를 수행할 때 나타난다.

Outer 조인문을 작성하면서 일부 조건절에 Outer 기호(+)를 빠뜨리면 Inner 조인할 때와 같은 결과가 나온다. 이럴 때 옵티마이저는 Outer 조인을 Inner 조인문으로 바꾸는 쿼리 변환을 시행한다.

옵티마이저가 굳이 이런 쿼리 변환을 시행하는 이유는 조인 순서를 자유롭게 결정하기 위해서다. =>Outer 조인 시 Outer 테이블은 (+) 기호가 없는 테이블로 고정됐었기 때문이다.

Outer 조인을 써야 하는 상황이라면 Outer 기호를 정확히 구사해야 올바른 결과집합을 얻을 수 있다.

10. 실체화 뷰 쿼리로 재작성

뷰는 쿼리만 저장하고 있을 뿐 자체적으로 데이터를 갖지는 않는다. 반면, 실체화 뷰(Materialized View = MV)는 물리적으로 실제 데이터를 갖는다.

MV는 과거에 분산 환경에서 실시간 또는 일정 주기로 데이터를 복제하는 데 사용하던 Snapshot 기술을 DW 분야에 적응시킨 것이며, 여전히 데이터 복제 용도로 사용할 수 있다.

MV를 활용하는 이유는 기준 테이블이 그만큼 대용량이기 때문인데, Join View는 같은 데이터를 중복으로 저장하는 비효율이 있어 활용도가 낮고, 주로 Aggregate View 형태로 활용되는 편이다.

✅ MV의 두 가지 특징

• Refresh 옵션을 통해서 오라클이 집계 데이터를 자동 관리하도록 할 수 있다.

• 옵티마이저에 의한 Query Rewrite가 지원된다.

MV의 가장 큰 장점은, 자동으로 쿼리가 재작성된다는 것이다. MV는 사용자가 집계 테이블의 존재를 몰라도, 옵티마이저가 알아서 MV를 액세스하도록 쿼리를 변환해 준다.

쿼리 재작성 기능이 작동하려면 MV를 정의할 때 enable query rewrite 옵션을 주어야 하고, 세션이나 시스템 레벨에서 파라미터도 변경해 주어야 한다. 9i는 기본 값이 false, 10g 부터는 true 이다.

alter session set query_rewrite_enalbed = true;

11. 집합 연산을 조인으로 변환

intersect, minus 같은 집합 연산을 조인 형태로 변환하는 것을 말한다.

_convert_set_to_join 파라미터를 true로 설정하면 조인으로 변환된다.

12. 기타 쿼리 변환

1. 조인 컬럼에 IS NOT NULL 조건 추가

조인 시 NULL 값은 조인에 실패하기 때문에 is not null 로 필터 조건을 추가해 불필요한 테이블 액세스 및 조인 시도를 줄일 수 있어 쿼리 성능 향상에 도움이 된다.

=> 만약 컬럼 통계를 수집하고 조인 시, 조인 컬럼의 null 값 비중이 5% 이상일 때 옵티마이저가 is not null 조건절을 자동으로 생성해 준다.

이처럼 조인 컬럼에 is not null 조건을 추가해 주면 NL 조인뿐만 아니라 해시 조인, 소트 머지 조인 시에도 효과를 발휘한다.

조인 컬럼에 대한 is not null 조건을 추가한다고 손해 볼 일은 전혀 없지만, 옵타마이저는 null 값 비중이 5%를 넘을때만 이런 쿼리 변환을 시행한다. 따라서 필요하다면 옵티마이저 기능에 의존하지 말고 사용자가 직접 is not null 조건을 추가해줌으로써 불필요한 액세스를 줄일 수 있다.

그리고 조인 컬럼에 만약 null 값이 많을 때, 임의의 default 값으로 채우는 방식으로 설계하면 조인 성능을 떨어뜨릴 수 있다.

2. 필터 조건 추가

select *
from emp
where sal between :mn and :mx;

만약 :mx 값보다 :mn 값이 큰 경우 쿼리 결과는 공집합이다. 이 경우 8i에서는 한참 기다렸어야 결과가 나왔지만 9i부터는 이를 방지하기 위해 옵티마이저가 임의로 필터 조건식을 추가한다.

1 - filter (TO_NUMBER(:MN) <= TO_NUMBER(:MX))

또한 실행계획 상에는 emp 테이블을 Full Scan 하고 나서 필터 처리가 일어나는 것 같지만 실제로는 Table Full Scan 자체를 생략해 버린다.

3. 조건절 비교 순서

select *
from jy
where y = 2
and j = 99;

만약 만약 y컬럼의 2 비중이 크고, j 컬럼의 99 비중이 적을 때는 적은 컬럼부터 조건식을 먼저 평가하는 것이 유리하다. => 대부분의 레코드가 j = 99 의 조건을 만족하지 않아 y컬럼의 비교 연산을 수행하지 않아도 되기 때문이다.

select /*+ full(도서) */ 도서번호, 도서명, 가격, 저자, 출판사, isbn
from 도서
where 도서명 > :last_book_nm
and 도서명 like :book_nm || '%';

위의 조건절을 처리할 때도 부등호(>)를 먼저 처리하느냐 like 연산을 먼저 처리하느냐에 따라 일량의 차이가 생긴다.

옵티마이저는, 테이블 전체를 스캔하거나 인덱스를 수평적으로 스캔할 때의 필터 조건식을 평가할 때 선택도가 낮은 컬럼을 먼저 처리하도록 순서를 조정한다.

이런 쿼리 변환이 작동하려면 9i, 10g를 불문하고 옵티마이저에게 시스템 통계를 제공함으로써 CPU 비용 모델을 활성화해야 한다. I/O 비용 모델에서는 where절에 기술한 순서대로 조건식 비교가 일어난다. RBO 모드는 where절에 기술한 순서 반대로 조건식 비교가 일어난다.

order_predicates 힌트를 사용하면 CPU 비용 모델에서도 조건절 비교 순서를 제어할 수 있다. 이 힌트를 사용하면 where절에 기술한 순서대로 조건식 비교가 일어난다.

✅ order_predicates 힌트의 또 다른 용도

10g 에서는 OR 또는 IN-LIST 조건에 대한 OR-Expansion이 일어날 때 실행 순서를 제어할 목적으로 order_predicates 힌트를 사용할 수 있다.

9i까지는 비용 모델의 종류에 관계없이 IN-LIST를 OR-Expansion방식으로 처리할 때 뒤쪽에 있는 값을 먼저 실행한다.

10g의 CPU 비용 모델에서는 카디널리티가 낮은 쪽을 먼저 실행한다.

10g에서 ordered_predicates 힌트를 주면 9i처럼 뒤쪽 값부터 실행된다.

이떤 이유에서건 뷰 Merging 을 실패했을 때, 옵티마이저는 포기하지 않고 2차적으로 조건절 Pushing을 시도한다. 조건절 Pushing은 참조하는 쿼리 블록의 조건절을 뷰 쿼리 블록 안으로 Pushing 하는 기능을 말한다.

조건절이 가능한 빨리 처리되도록 뷰 안으로 밀어 넣는다면, 뷰 안에서의 처리 일량을 최소화하게 됨은 물론 리턴되는 결과 건수를 줄임으로써 다음 단계에서 처리해야 할 일량을 줄일 수 있다.

☑️ 조건절 Pushing 종류

1. 조건절 Pushdown

쿼리 블록 밖에 있는 조건들을 쿼리 블록 안쪽으로 밀어넣는 것을 말한다.

2. 조건절 Pullup

쿼리 블록 안에 있는 조건들을 쿼리 블록 밖으로 내오는 것을 말하며, 그것을 다시 다른 쿼리 블록에 Pushdown 하는 데 사용한다.

3. 조인조건 Pushdown

NL 조인 수행 중에 Outer 테이블에서 읽은 값을 건건이 Inner 쪽 뷰 쿼리 블록 안으로 밀어 넣는 것을 말한다.

조인 조건 Pushdown은 NL 조인을 전제로 하기 때문에 성능이 더 나빠질 수 있다. 따라서 오라클은 push_pred, no_push_pred 힌트를 제공한다.

9i 에서 use_nl 힌트를 push_pred 와 함께 사용하면 조인 조건 Pushdown 기능이 작동하지 않는 현상이 나타난다. 이때는 push_pred 힌트만을 사용해야 하며, 조인 조건 Pushdown은 NL 조인을 전제로 하므로 굳이 use_nl 힌트를 쓸 필요가 없다.

✅ Outer 조인 뷰에 대한 조인 조건 Pushdown

Outer 조인에서 Inner 쪽 집합이 뷰 쿼리 블록 일 때, 뷰 안에서 참조하는 테이블 개수에 따라 옵티마이저는 두 가지 방법 중 하나를 선택한다.

1. 뷰 안에 참조하는 테이블이 단 하나일 때, 뷰 Merging을 시도한다.

2. 뷰 내에서 참조하는 테이블이 두 개 이상일 때, 조인 조건식을 뷰 안쪽으로 Pushing하려고 시도한다.

5. 조건절 이행

조건절 이행은 A=B 이고 B=C 이면 A=C라는 추론을 통해 새로운 조건절을 내부적으로 생성해 주는 쿼리 변환이다. (<, > 부등호도 상관없다.)

상수 및 변수에 대한 조건절은 조인문을 타고 다른 쪽 테이블로 전이된다. 하지만 조인문 자체는 전이되지 않는다.

조인조건은 상수와 변수 조건처럼 전이되지 않으므로 최적의 조인순서를 결정하고 그 순서에 따라 조인문을 기술해 주는 것이 매우 중요하다.

6. 조인 제거

1:M 관계인 두 테이블을 조인하는 쿼리문에서 조인문을 제외한 어디에서도 1쪽 테이블을 참조하지 않는다면, 쿼리 수행시 1쪽 테이블은 읽지 않아도 된다. 결과집합에 영향을 주지 않기 때문이다.

옵티마이저는 이 특성을 이용해 M쪽 테이블만 읽도록 쿼리를 변환하는데, 이를 '조인 제거' 나 ' 테이블 제거' 라고 한다.

alter session set "_optimizer_join_elimination_enabled" = true;

이 기능을 제어하는 파라미터는 위와 같고, eliminate_join, no_eliminate_join 힌트를 통해 제어한다.

조인 제거 기능이 작동하려면 PK/FK 제약이 설정돼 있어야한 한다.

7. OR-Expansion

select *
from emp
where job = 'CLERK' or deptno = 20;

위 쿼리가 그대로 수행된다면 or 조건이므로 Full Table Scan 으로 처리되거나 Index Combine이 작동할 수 있다.

만약 job과 deptno에 각각 생성된 인덱스를 사용하고 싶다면 union all 형태로 바꿔주면 된다.

사용자가 쿼리를 직접 바꿔주지 않아도 옵티마이저가 그런 작업을 대신해 주는 경우가 있는데 이를 'OR-Expansion' 이라고 한다.

OR-Expansion을 제어하기 위해 사용하는 힌트는 use_concat, no_expand 두가지가 있다. use_concat은 OR-Expansion을 유도할 때 사용하고, no_expand는 방지할 때 사용한다.

9i까지는 같은 컬럼에 대한 or 조건이나 in-list도 or-expansion으로 작동할 수 있었다. 10g 부터는 기본적으로 in-list iterator 방식으로 작동한다. (비교연산자가 '=' 일 시)

nvl 또는 decode를 여러 컬럼에 대해 사용했을 때는 그 중 변별력이 가장 좋은 컬럼으로 한 번만 분기가 일어난다. 옵션 조건이 복잡할 때는 이 방식에만 의존하기 어려운 이유가 여기에 있고, 그럴 때는 수동으로 union all로 분기 해 줘야만 한다.

8. 공통 표현식 제거

같은 조건식이 여러 곳에 반복 사용될 경우, 오라클은 해당 조건식이 각 로우당 한 번씩만 평가되도록 쿼리를 변환하는데 이를 '공통 표현식 제거' 라고 한다. _eliminate_common_subexpxr 파라미터를 통해 제어한다.

쿼리 변환은 쿼리 옵티마이저가 SQL을 분석해 의미적으로 동일하면서도 더 나은 성능이 기대되는 형태로 재작성하는 것을 말한다. 본격적으로 최적화를 하기 전에 사전 정지 작업을 하는 것이라고 말할 수 있다.

쿼리 변환은 크게 두 가지로 나뉜다.

1. 휴리스틱 쿼리 변환

결과만 보장된다면 무조건 쿼리 변환을 수행한다. 일종의 규칙 기반 최적화 기법이라고 할 수 있으며, 경험적으로 항상 더 나은 성능을 보일 것이라는 옵티마이저 개발팀의 판단이 반영되는 것이다.

2. 비용기반 쿼리 변환

변환된 쿼리의 비용이 더 낮은 때만 그것을 사용하고, 그렇지 않을 때는 원본 쿼리 그대로 두고 최적화를 수행한다.

필요한 부분에 대해서는 이미 모두 비용기반으로 개선이 이뤄졌다.

2. 서브쿼리 Unnesting

1. 서브쿼리의 분류

1. 인라인 뷰

from 절에 나타나는 서브쿼리를 말한다

2. 중첩 서브쿼리

결과집합을 한정하기 위해 where 절에 사용된 서브쿼리를 말한다. 서브쿼리가 메인쿼리에 있는 컬럼을 참조하는 형태를 가져 '상관관계 서브쿼리' 라고도 부른다.

3. 스칼라 서브쿼리

한 레코드당 하나의 컬럼 값만을 리턴한다는 특징을 가지고 있다. 주로 select 절에 사용된다.

이들 서브쿼리를 참조하는 메인 쿼리도 하나의 쿼리 블록이며, 옵티마이저는 쿼리 브록 단위로 최적화를 수행한다. 즉, 쿼리 블록 단위로 액세스 경로와 조인 순서, 조인 방식을 선택하는 것을 목표로 한다.

2. 서브쿼리 Unnesting의 의미

'nunest'란 "중첩된 상태를 풀어낸다" 는 뜻을 말하며 서브쿼리 Unnesting 이란 중첩된 서브쿼리를 풀어내는 것을 말한다.

서브쿼리를 처리하는 데 있어 필터 방식이 항상 최적의 수행속도를 보장하지 못하므로 옵티마이저는 아래 둘 중 하나의 방법을 선택한다.

1. 동일한 결과를 보장하는 조인문으로 변환하고 나서 최적화한다. => 서브쿼리 Unnesting

   => '서브쿼리 Flattening' 이라고도 부르고, 쿼리 변환 후 일반 조인문처럼 다양한 최적화 기법을 사용할 수 있게 된다.

2. 서브쿼리를 Unnesting 하지 않고 원래대로 둔 상태에서 최적화한다. 메인쿼리와 서브쿼리를 별도의 서브 플랜으로 구분해 각각 최적화를 수행하며, 이때 서브쿼리에 Filter 오퍼레이션이 나타난다.

   => Plan Generator가 고려대상으로 삼을만한 다양한 실행계획을 생성해 내는 작업이 매우 제한적인 범위 내에서만 이루어진다.

3. 서브쿼리 Unnesting의 이점

서브쿼리를 메인쿼리와 같은 레벨로 풀어낸다면 다양한 액세스 경로와 조인 메소드를 평가할 수 있고, 조인 형태로 변환했을 때 더 나은 실행계획을 찾을 가능성이 높아진다.

이런 이점 때문에 옵티마이저는 서브쿼리 Unnesting을 선호한다. 오라클 9i 에서는 결과집합이 보장되면 무조건 서브쿼리 Unnesting을 사용했다 -> 휴리스틱 쿼리변환 10g 부터는 서브쿼리 Unnesting이 비용기반으로 전환돼 비용이 더 낮을 때만 Unnesting된 버전을 사용하고, 그렇지 않을 때는 원본 쿼리 그대로 필터 방식으로 최적화한다.

4. Unnesting된 쿼리의 조인 순서 조정된 쿼리의 조인 순서 조정

Unnesting에 의해 일반 조인문으로 변환된 후에는 어느 테이블이든 Outer 테이블이 될 수 있다 어느 테이블이 될 지는 옵티마이저가 통계정보를 보고 판단한다.

=> 힌트로 서브쿼리 테이블을 먼저 Outer 테이블로 지정하고 싶다면 order힌트를 사용해야 된다. 10g 부터는 쿼리 블록마다 이름을 지정하는 qb_name 힌트가 제공되므로 더 정확하게 제어할 수 있다.

5. 서브쿼리가 M쪽 집합이거나 Nonunique 인덱스 일 때

옵티마이저는 이럴 때 두 가지 방식 중 하나를 선택하는데, Unnesting 후 어느 쪽 집합이 먼저 드라이빙 되느냐에 따라 달라진다.

1. 1쪽 집합임을 확신할 수 없는 서브쿼리 쪽 테이블이 드라이빙된다면, 먼저 sort unique 오퍼레이션을 수행함으로써 1쪽 집합으로 만든 다음에 조인한다.

2. 메인 쿼리 쪽 테이블이 드라이빙된다면 세미 조인 방식으로 조인한다.

6. 필터 오퍼레이션과 세미조인의 캐싱 효과

서브쿼리를 Unnesting해 조인문으로 바꾸고 나면 NL 조인은 물론 해시 조인, 소트 머지 조인 방식을 선택할 수 있고, 조인 순서도 자유롭게 선택할 수 있다.

오라클은 필터 조건 최적화 기법을 한 가지 가지고 있는데, 서브쿼리 수행 결과를 버리지 않고 내부 캐시에 저장하고 있다가 같은 값이 입력되면 저장된 값을 출력하는 방식이다. 스칼라 서브쿼리의 캐싱 동작과 똑같다. (서브쿼리리를 필터 조건으로 둬도 캐싱한다.)

9i에서의 NL 세미 조인은 캐싱효과가 없지만, 10g 부터는 NL 세미 조인도 캐싱 효과를 갖는다.

7. Anti 조인

not exists, not in 서브쿼리도 Unnesting 하지 않으면 필터 방식으로 처리된다.

8. 집계 서브쿼리 제거

10g에서 집계 함수를 포함하는 서브쿼리를 Unnesting 하고, 이를 다시 윈도우 함수로 대체하는 쿼리변환이 도입되었다.

집계 함수를 서브쿼리를 Unnesting하면 조인문을 만든 쿼리를 한번 더 쿼리변환을 시도해 인라인 뷰를 Merging 하거나 그대로 둔 채 최적화할 수 있다.

10g부터는 서브쿼리로부터 전환된 인라인 뷰를 제거하고 메인 쿼리에 윈도우 함수를 사용하는 형태로 변환하는 것이 가능하다. 이 기능은 _remove_aggr_subquery 파라미터에 의해 제어되며, 비용기반으로 작동한다. 집계 서브쿼리 제거 기능이 작동했을 때는 실행계획에 window buffer 오퍼레이션이 작동한다.

9. Pushing 서브쿼리

Pushing 서브쿼리는 실행계획상 가능한 앞 단계에서 서브쿼리 필터링이 처리되도록 강제하는 것을 말하며, 이를 제어하기 위해 push_subq 힌트를 사용한다. Pushing 서브쿼리는 unnesting 되지 않은 서브쿼리에만 작동한다. 따라서 no_unnest + push_subq 힌트를 같이 사용하는 것이 올바른 사용 방법이다.

3. 뷰 Merging

뷰 Merging 이란 뷰를 참조하는 쿼리 블록과의 머지 과정을 거쳐 조인문 형태로 변환하는 것이다. 뷰 Merging을 거친 쿼리는 옵티마이저가 더 다양한 액세스 경로를 조사 대상으로 삼을 수 있게 된다. merge, no_merge 힌트로 제어한다.

1. 단순 뷰(Simple View) Merging

조건절과 조인문만을 포함하는 단순 뷰는 no_merge 힌트를 사용하지 않는 한 무조건 Merging이 일어난다.

2. 복합 뷰(Complex View) Merging

group by절이나 distinct 연산을 포함하는 복합 뷰는 파라미터 설정 또는 힌트 사용에 의해서만 뷰 Merging 이 가능하다. => _complex_view_merging = true (8i default = false / 9i default = true)

✅ 복합 뷰 중 뷰 Merging 이 불가능한 경우

• 집합 연산자

• connect by 절

• ROWNUM pseudo 컬럼

• group by 없이 쓰는 전체 집계 함수

• 윈도우 함수 (Analytic Function)

3. 비용기반 쿼리 변환의 필요성

9i 에서 복합 뷰를 무조건 Merging 하도록 구현한 것은, 보편적으로 더 나은 성능을 보였기 때문이다. 그런데 다른 쿼리 변환은 더 나은 성능을 제공하지만, 복합 뷰 Merging은 그렇지 못할 때가 많다.

그래서 10g 부터는 비용기반 쿼리 변환 방식으로 전환하게 되었고,
_optimizer_cost_based_transformation 파라미터를 사용해 제어한다.

• on, off, exhaustive, linear, iterative

각 쿼리 변환마다 제어할 수 있는 힌트가 따로 있고, 필요하다면 10gR2 부터 opt_param 힌트를 이용해 쿼리 레벨에서 파라미터를 변경할 수있다.

실제 비용기반 쿼리 변환으로 바뀌면서 쿼리 성능이 느려지는 경우가 있는데 이럴 때 opt_param 힌트를 이용해 파라미터를 false로 변경하면 된다.

4. Merging 되지 않은 뷰의 처리방식

10g 부터 뷰 Merging을 시행했을 때 비용이 오히려 더 증가한다고 판단되거나 부정확한 결과집합이 만들어질 가능성이 있을 때 옵티마이저는 뷰 Merging을 포기한다.

뷰 Merging을 포기했을 땐 2차적으로 조건절 Pushing을 시도한다.

이마저도 실패한다면 뷰 쿼리 블록을 개별적으로 최적화하고, 거기서 생성된 서브플랜을 전체 실행계획을 생성하는 데 사용한다. 실제 쿼리를 수행할 때도 뷰 쿼리의 수행 결과를 액세스 쿼리에 전달하는 방식을 사용한다.