꿈을 향하여 질주하기

  자율주행 관련 비즈니스 도메인 분석이 필요하여 정리하였던 자료입니다.
  여기 저기에서 퍼온 내용을 모아둔 것이라 문체가 일관성없고 설명이 빈약한 경우도 있지만
  그냥 참고용으로 포스팅해봅니다.

  아래 쿼리를 살펴보도록 하겠습니다.

SELECT CNT1, CNT2, CNT3, CNT4, CNT5, CNT6, CNT7, CNT8 
 FROM (
       SELECT (SELECT COUNT(*) FROM CHG_LOGS WHERE REG_DT=:B1 AND ST_CD='0' AND LOG_LVL='1') CNT1
            , (SELECT COUNT(*) FROM CHG_LOGS WHERE REG_DT=:B1 AND ST_CD='1' AND LOG_LVL='1') CNT2
            , (SELECT COUNT(*) FROM CHG_LOGS WHERE REG_DT=:B1 AND ST_CD='2' AND LOG_LVL='1') CNT3
            , (SELECT COUNT(*) FROM CHG_LOGS WHERE REG_DT=:B1 AND ST_CD='3' AND LOG_LVL='1') CNT4
            , (SELECT COUNT(*) FROM CHG_LOGS WHERE REG_DT=:B1 AND ST_CD='4' AND LOG_LVL='1') CNT5
            , (SELECT COUNT(*) FROM CHG_LOGS WHERE REG_DT=:B1 AND ST_CD='5' AND LOG_LVL='1') CNT6
            , (SELECT COUNT(*) FROM CHG_LOGS WHERE REG_DT=:B1 AND ST_CD='6' AND LOG_LVL='1') CNT7
            , (SELECT COUNT(*) FROM CHG_LOGS WHERE REG_DT=:B1 AND ST_CD='7' AND LOG_LVL='1') CNT8
         FROM DUAL
    ) A;

  CHG_LOGS 테이블의 조건별 건수를 출력하는 쿼리입니다.
  ST_CD 값 별 데이터 건수를 계산하기 위해 스칼라 서브 쿼리 안에서 CHG_LOGS 테이블을 액세스하고 있습니다. 문제는 유사한 스칼라 서브 쿼리가 총 8개가 존재한다는 점입니다. 다시 말해서, CHG_LOGS 테이블을 8번 중복해서 액세스 한다는 뜻입니다.

  위 쿼리의 AUTOTRACE 결과를 확인해보겠습니다.

  위 쿼리의 실행계획을 살펴보면 아래와 같은 패턴(인덱스 스캔 및 테이블 랜덤 액세스)을 8번 반복해서 수행한 것을 확인할 수 있습니다.

  위와 같은 반복적인 액세스 작업 때문에 과도한 블럭 읽기(109,718블럭)가 발생하고 이로 인하여 쿼리 수행 시간도 길어지게 되었습니다.

  그럼, 어떤 방식으로 중복 액세스를 제거하여 쿼리 성능을 높일 수 있는지 확인해보도록 하겠습니다.

  아래는 튜닝 후 쿼리입니다.

SELECT NVL( SUM( CASE WHEN ST_CD='0' THEN 1 ELSE 0 END), 0) AS CNT1
     , NVL( SUM( CASE WHEN ST_CD='1' THEN 1 ELSE 0 END), 0) AS CNT2
     , NVL( SUM( CASE WHEN ST_CD='2' THEN 1 ELSE 0 END), 0) AS CNT3
     , NVL( SUM( CASE WHEN ST_CD='3' THEN 1 ELSE 0 END), 0) AS CNT4
     , NVL( SUM( CASE WHEN ST_CD='4' THEN 1 ELSE 0 END), 0) AS CNT5
     , NVL( SUM( CASE WHEN ST_CD='5' THEN 1 ELSE 0 END), 0) AS CNT6
     , NVL( SUM( CASE WHEN ST_CD='6' THEN 1 ELSE 0 END), 0) AS CNT7
     , NVL( SUM( CASE WHEN ST_CD='7' THEN 1 ELSE 0 END), 0) AS CNT8
  FROM CHG_LOGS A
 WHERE REG_DT=:B1
   AND LOG_LVL='1';

  중복 액세스를 피하기 위해 스칼라 서브 쿼리를 제거하였습니다. CHG_LOGS 테이블은 한 번만 액세스 하도록 변경하였으며, WHERE 절에는 각 스칼라 서브 쿼리에서 동일하게 적용되었던 조건(REG_DT=:B1 AND LOG_LVL='1')을 입력하였습니다.
  그리고 CASE문을 이용하여 ST_CD 별 데이터 건수를 계산하였습니다.

  위와 같이 변경된 쿼리의 AUTOTRACE 결과는 아래와 같습니다.

  실행계획을 살펴보면 IX_CHG_LOG 인덱스와 CHG_LOGS 테이블을 한번씩 액세스하고 있는 것을 확인하실 수 있습니다. 액세스 중복이 제거되어 실행시간(0.02초)과 블럭 읽기(13,714블럭)가 감소된 것을 확인하실 수 있을 것입니다. 물론, 중복 제거의 방법으로 함수를 사용하여 CPU의 사용량이 증가하게 됩니다. 하지만, 쿼리에서 가장 큰 부하는 블럭 읽기이기 때문에 부하 측면에서 CPU 사용량은 상대적으로 낮은 비용이라고 판단할 수 있습니다.

  지금까지 살펴봤던 SQL 튜닝 방법이 집합적 사고의 한 예라고 볼 수 있습니다. 데이터 블럭 하나하나가 퍼즐 조각이며, 각 퍼즐 조각을 최대한 적게 활용하여 멋진 작품인 결과물을 만들어내는 게 집합적 사고에 의한 쿼리문 작성인 거죠. 많은 분들이 시간적 여유를 갖고 적은 퍼즐 조각으로 멋진 작품을 탄생시키는 기쁨을 느껴보셨으면 좋겠습니다.

  데이터의 가치가 높아지고 데이터 저장/관리 기술이 발전하면서 다양한 데이터베이스 소프트웨어가 출시되고 있습니다.

  Hadoop 클러스터링 환경에 기반한 Hive, HBase, Spark SQL는 빅데이터 수집/분석에 대한 요구사항의 증대와 같이 사용량도 늘어가고 있습니다.
  인프라 운영에 대한 리스크를 줄여주고 빠른 서비스 구축이 가능하다는 장점을 내세워 급속도로 성장을하고 있는 클라우드 서비스 플랫폼이 증가하면서 클라우드 환경의 데이터베이스도 사용이 증가하고 있습니다. 기존의 데이터베이스가 클라우드 환경에서도 서비스를 시작하는 경우도 있지만, Amazon DynamoDB, Google BigQuery, Microsoft Azure SQL Database 처럼 클라우드 플랫폼 전용 데이터베이스 서비스도 생겨나고 있습니다.
  그 외에도 다양한 형태의 데이터를 관리하기 위해 NoSQL, Graph DB, Time Series DB 등의 데이터베이스도 사용량이 증가하고 있습니다.

  하지만, 아직도 DBMS 시장에서 우위를 차지하고 있는 것은 역시 RDBMS입니다.

  제가 예상하건데 표 형태로 데이터를 보고 싶어하는 사용자의 욕망이 바닥으로 추락하지 않는 한 RDBMS는 끝까지 건재할 것입니다.

  아무튼, 이번 포스팅에서는 이 RDBMS의 성능 최적화 전략에 대해서 정리해보고자 합니다. 

  우선, 'RDBMS'의 성능이 좋다는 기준은 무엇일까요?
  RDBMS의 목적이 동시성 제어로 다수의 사용자가 빠르게 원하는 데이터를 저장하고 추출하는 것이니, 얼마나 많은 사용자가 얼마나 빨리 데이터를 처리할 수 있느냐가 성능의 기준이 되겠습니다.
  실제로 SI 프로젝트에서도 위와 같은 기준으로 성능 부하 테스트를 진행하고 있습니다.

  그럼 쿼리 실행 속도가 매우 빠르면 성능상으로 문제가 없는 걸까요?
  실행 속도가 빠르다는 건 표면상의 성능 기준이라고 할 수 있습니다. 정확하게 얘기하면  쿼리 실행 시 내부적으로 얼마나 많은 블럭을 I/O했는지가 성능을 판가름하는 중요한 지표가 됩니다. 모든 시스템에서 디스크 I/O는 상대적으로 느린 속도로 인하여 많은 비용을 유발하는 원인으로 인식하고 있습니다. 그래서 디스크 I/O를 줄이기 위해 버퍼에 데이터를 로딩하여 사용합니다. 하지만 버퍼는 크기의 한계가 있기 때문에 디스크 I/O를 완전히 제거할 수는 없습니다.
  최소한의 블럭 I/O로 버퍼의 활용도를 높이고 디스크 I/O를 줄이는 것이 실행 속도를 빠르게 하고 성능을 높일 수 있는 궁극적인 방법이 되겠습니다.

  이 포스팅에서 언급하는 내용들은 거의 다 블럭 I/O를 줄이기 위한 방법들이라고 이해하시면 됩니다.
  그럼 본격적으로 RDBMS의 성능에 대해 얘기해보겠습니다. 

  필자가 생각하는 RDBMS에서의 성능 최적화 전략은 다음과 같습니다.

- RDBMS 성능 최적화 전략 -

1. 파라미터 튜닝 (Parameter tuning)
2. 조인 전략 (Join strategy)
3. 인덱스 전략 (Index strategy)
4. 데이터 아키텍처 전략 (Data architecture strategy)
5. SQL 튜닝 (SQL tuning)

1. 파라미터 튜닝 (Parameter tuning)

  파라미터(또는 Config, 환경설정) 튜닝을 제일 먼저 언급하는 이유는 시스템 구축 초기에 완료해야 할 작업이기 때문입니다.

  모든 솔루션은 설정 파일을 이용해 각 사이트 환경에 적합한 파라미터 값을 설정할 수 있는 기능을 제공합니다. (예를 들어 오라클은 init.ora, MySQL는 my.cnf 파일이 이에 해당됩니다.) 일반적인 파라미터 항목은 버퍼/캐쉬 크기, 로그 기록 위치, 블럭(페이지) 크기, 부가 기능 사용여부 또는 설정 값 등이 있을 수 있습니다.

  여러 파라미터 중에서 초기 데이터베이스 생성 시에만 설정이 가능한 항목들이 있고, 데이터베이스 생성 후에도 변경이 가능한 항목들도 존재합니다. 그렇다고 추후에 변경 가능한 항목들을 마음껏 변경할 수 있다는 뜻은 아닙니다. 특히, 운영 중인 시스템의 설정 값은 더욱 더 심사숙고 해야 합니다.
  검증되지 않은 설정 값 변경으로 기존에 잘 수행되던 쿼리의 실행계획이 변경되어 악성 쿼리가 난무하는 시스템을 만나게 될 수 도 있기 때문입니다.

  그러면 최적화를 위해 파라미터를 어떻게 변경해야 할까요?

  최선의 방법은 reference를 참조를 해서 연구하고 테스트 후 적용하는겁니다. 각 DBMS 솔루션은 사용자들을 위한 커뮤니티나 서포트 페이지를 통해서 고객 문의를 지원해주고 있습니다. 그곳에서 빈번하게 수정해야 된다고 언급되는 파라미터가 있다면 테스트 후 적용해 볼 가치가 높은 편이라고 볼 수 있습니다.

  물론 설정 값을 변경하는 것은 그저 타이핑을 하면 되는 쉬운 것이지만, 그 값을 어떤 항목으로 채워야 하는지 알아내는 것은 쉬운 일이 아닙니다. 이럴 때는 솔루션 고객센터나 데이터베이스 컨설팅 업체의 도움을 받아야 할 것입니다. 이것도 비용상의 문제로 어렵다면 DBMS 선택 시 안정화된 버전을 설치하는 것이 쉬운 방법이 될 것입니다.
  파라미터를 변경하는 주된 이유는 아직 안정화가 안된 버전에서 신규 기능에 의해 잘 알려지지 않은 버그를 해결하기 위한 경우가 많기 때문입니다.

2. 조인 전략 (Join strategy)

  일반적으로 우리가 알고 있는 조인 종류(Type)는 다음과 같습니다.

  위 조인 종류는 두 테이블을 조인 시 한 쪽에만 존재하는 데이터를 결과 데이터 셋에 포함시킬지 여부에 따라 조인을 구분한다고 생각하시면 됩니다. 자세한 내용은 아래 포스팅을 참고 바랍니다.

  그리고 조인 방식(Method)에 의해서도 조인을 구분할 수 있습니다.

  데이터베이스 내부에서 각각의 Row를 매핑 시 사용되는 방식에 의해 조인을 구분할 수 있는겁니다.
  쿼리 실행 시 옵티마이저가 다양한 조인 방식 중에서 어떤 방식을 채택하느냐에 따라 쿼리 성능이 크게 달라질 수 있게 됩니다. 일반적으로 데이터 건수가 적은 경우에는 Nested loops join을 사용하고, 데이터 건수가 많은 경우에는 Hash join을 사용합니다. 그리고 조인 대상 테이블 중에 데이터 건수가 적은 테이블부터 액세스를 해야 더 좋은 성능을 보장할 수 있습니다.

  자세한 조인 방식에 대한 설명은 아래 포스팅을 참고 바랍니다.

3. 인덱스 전략 (Index strategy)

  인덱스는 테이블의 데이터를 빠르게 액세스하기 위한 목적으로 만들어진 객체입니다.
  흔히 비유하기를 책 분류기호를 이용해 서가에 순서대로 정리되어 있는 책을 찾는 방법과 비슷하다고 설명합니다.

  인덱스에서는 책의 분류기호 대신에 테이블의 컬럼 값을 사용해 데이터 위치를 검색합니다. 컬럼의 값을 B-Tree 구조체에 입력하고, 루트노드부터 리프노드까지 짧은 액세스 단계를 거쳐 검색 대상 컬럼의 값이 위치한 로우의 위치(Rowid)를 빠르게 찾을 수 있게 됩니다. ( 이 포스팅의 주 목적이 B-Tree가 아닌 관계로 상세한 입력/검색 절차는 생략하겠습니다. )

  데이터를 빠르게 검색할 수 있는 인덱스, 그렇다고 테이블 하나에 다수의 인덱스를 생성하게 되면 테이블에 데이터 입력/변경/삭제 시 인덱스의 변경 작업량이 증가하여 성능 부하의 원인이 될 수 있습니다.

  인덱스를 무조건 많이 생성하는 것보다, 인덱스의 활용도가 높아지도록 조인이나 필터링 조건으로 많이 사용되는 컬럼(흔히 액세스 패턴을 확인한다고 합니다.) 중심으로 인덱스를 생성하는 것이 중요합니다.
  즉, 자주 사용되는 쿼리문을 분석한 액세스 패턴 결과를 토대로 인덱스 컬럼을 선정해야 합니다.

  인덱스 컬럼을 선정할 때도 단일 컬럼을 선정하는 것보다는 복합 컬럼을 선정하는 것이 성능상 유리합니다. 일반적인 조회 쿼리를 보면 조인이나 필터링 조건에 하나의 컬럼만 사용되는 경우는 극히 드물기 때문입니다. 최대한 인덱스에서 조건에 맞지 않는 로우를 필터링해야만 인덱스에서 rowid를 이용해 테이블에 액세스(Random access)하는 횟수를 줄일 수 있기 때문입니다. 인덱스를 거쳐 테이블의 블럭을 읽은 횟수가 전체 테이블의 블럭 숫자보다 많다면 Full table scan이 더 유리할 것입니다.

  그리고 인덱스 컬럼의 순서도 중요합니다. 선행 컬럼의 분포도가 좋다면 인덱스 블록 액세스를 최소화 할 수 있기 때문입니다. 선행 컬럼의 분포도가 좋지 않아서 검색 조건의 값이 여러 리프 노드 블럭에 존재하는 것보다는, 좋은 분포도로 인해 적은 리프 노드 블록에 존재하는 것이 인덱스의 블럭 액세스를 줄이는 방법입니다.

  이 외에도 인덱스 스캔 방법 선택도 성능을 위해 고려해야할 사항입니다.
  오라클에서 제공하는 인덱스 스캔 방법은 아래와 같습니다.

   위 인덱스 스캔 방법에 대해서는 추후에 별도로 포스팅하도록 하겠습니다.

4. 데이터 아키텍처 전략 (Data architecture strategy)

  우선 데이터 아키텍처의 정의부터 살펴보겠습니다.

[ dbguide.net ]
데이터아키텍처란 기업의 모든 업무를 데이터 측면에서 처음부터 끝까지 체계화하는 것이다.
데이터아키텍처 전문가란 효과적인 데이터아키텍처 구축을 위해 전사아키텍처와 데이터품질관리에 대한 지식을 바탕으로 데이터 요건분석, 데이터 표준화, 데이터 모델링, 데이터베이스 설계와 이용 등의 직무를 수행하는 실무자를 말한다.

[ wikipedia.org ]
Data architecture is composed of models, policies, rules or standards that govern which data is collected, and how it is stored, arranged, integrated, and put to use in data systems and in organizations.

  지극히 학문적인 정의라서 쉽게 가슴에 와닿지는 않을 수도 있습니다.
  그럼 위 정의를 기반으로 데이터 아키텍처에서 고려해야 할 항목들을 정리해 보도록 하겠습니다. 제가 지금 생각나는 대로 기술하는 것이기 때문에 아래 항목들이 100% 정답이라고 단정 지을 수는 없습니다.

데이터 요구분석
데이터베이스 선택 (ex : RDBMS, NoSQL, 그래프 DB, 임베디드DB, etc.)
서버/클러스터 구성 (ex: 단일 서버, HA구성, 클러스터 구성, RAC, Max scale, etc.)
파일 시스템 선택 (Oracle : OS Filesystem vs ASM vs Raw device)
데이터베이스 저장 엔진 선택 (MariaDB : Innodb, Aria, XtraDB, etc.)
데이터 표준화 (단어/용어/도메인)
데이터 모델링 (개념/논리/물리, 주제영역)
데이터 간 관계 정의
데이터 인터페이스 정의
데이터 수집 방법 및 범위 (ex : App. 생성 데이터, 외부 연계, 통계 데이터, 시스템 운영 데이터, etc.)
오브젝트 사용 정책 (ex: 트리거 사용 여부, PL/SQL 사용 여부, FK 설정 여부, 최대 인덱스 생성 허용 갯수, etc.)
명명 규칙 (ex: 테이블명, 테이블스페이스명, 인덱스명, 사용자계정명, etc.)
데이터베이스 사용자 계정/권한 정책
데이터베이스 접근 제어 정책
데이터 보관 주기
데이터 로그 관리
데이터 백업 전략
...

  위 항목들 외에도 제가 지금 생각하지 못한 데이터 아키텍처 항목들이 더 많을 수도 있습니다.

  그리고 위 항목들은 하나의 데이터베이스를 사용한다는 전제하에 List-up을 한 것입니다. 요즘에는 시스템 규모가 커지고 있기 때문에 한 시스템에서도 다양한 데이터베이스를 사용하도록 설계하는 곳도 많습니다.

  예를 들어, MariaDB/MySQL의 replication기능으로 데이터를 여러 서버에 복제 해두고 특정 사용자는 특정 서버의 데이터베이스를 액세스 하도록 구성하여 부하가 분산되게 구성할 수 있습니다. 이 때, 사용자가 어떤 서버의 데이터베이스를 사용해야 하는지 알려주는 역할은 동시 접근성이 좋은 오라클이 할 수 있는것입니다.

  사용자가 처음 시스템에 접속하면 오라클한테 '어느 서버를 이용하면 되나요?'라고 문의하고, 오라클이 'N번 서버의 데이터베이스를 이용하세요.'라고 응답하면, 그 후로 사용자는 계속 N번 서버의 데이터베이스와 통신을 하게 되는 겁니다.

  위의 데이터베이스 구성 방법은 단순한 예시일 뿐입니다. 요즘 MariaDB에서도 MaxScale으로 부하 분산 기능이 강화되었으니 MariaDB만으로도 안정적으로 운영이 가능 할 것입니다.

  아무튼, 데이터 아키텍처 설계라는게 단순히 데이터 모델만 그려내는 작업만이 아니라 더 포괄적으로 정책이나 성능 향상을 위한 방법까지도 정의해야 합니다.

  본론으로 들어가서, 데이터 아키텍처의 성능 향상 방법, 구체적으로 물리 데이터 모델의 성능 향상 방법에 대해 논의하고자 합니다.

  필자가 생각하는 물리 데이터 모델의 성능 향상 방법(의 일부)은 아래와 같습니다.

4-1) Partitioning

  데이터베이스에서 파티션은 크기가 큰 테이블이나 인덱스를 관리하기 용이하도록 작은 단위로 쪼개서 저장하는 단위입니다.

  한 회사에서 10년간의 거래 내역을 엑셀로 관리하고 있는데, 주로 거래 내역을 입력하고 확인하는건 최근 한달간의 데이터라고 가정해보겠습니다.
  만약, 전체 거래 내역이 하나의 엑셀 파일에 저장되어 있다면 어떻게 될까요? 엑셀 파일이 너무 커서 원하는 데이터를 찾는 것도 어려울 것이고, 컴퓨터의 자원을 많이 소비하여 처리 속도도 느려질 것입니다.
  이 때, 엑셀 파일을 월 단위로 나눠서 저장하게 되면 어떻게 될까요? 전체 테이터를 저장한 파일에 비해 경량의 엑셀 파일이 될 것입니다. 물론 데이터를 찾고 입력하는 속도도 빨라질 것이고요.

  위와 같이 데이터베이스에서 기준에 의해 데이터를 분리하여 저장/관리하는 것이 파티션의 역할입니다.
  기준에 의해 데이터가 분리되어 있기 때문에 검색 조건에 기준 컬럼 조건이 포함되어 있다면 전체 테이블 데이터가 아니라 조건에 맞는 파티션 데이터만 액세스(Partition pruning이라고 합니다.)하게 되므로 블록 액세스가 감소됩니다.

  아래 예제를 살펴보겠습니다.

  테이블에서 YYYYMM 컬럼 값이 '201903'인 데이터를 조회하는 쿼리를 실행했습니다. 일반 테이블에서 YYYYMM='201903'인 값을 찾기 위해서는 전체 테이블을 탐색해야 합니다. 
  만약, YYYYMM 컬럼 값을 기준으로 파티션이 된 테이블에서 YYYYMM='201903'인 값을 찾는다면 YYYYMM='201903'에 해당하는 파티션만 탐색하게되므로 블록 액세스가 감소됩니다.

  데이터베이스에서 성능 향상을 위한 기본이 블록 액세스를 줄이는 것이니, 파티션을 사용하면 데이터베이스의 성능이 향상 될 수 있게됩니다.

  그리고 오라클에서 테이블이나 인덱스를 파티셔닝하게되면 파티션 별로 세그먼트라는 논리적 저장 구조가 분리되어 관리되고, 데이터 파일도 분리(파티션 별 테이블스페이스 설정 작업이 별도로 필요합니다.)되게 됩니다. 물리적으로도 데이터 I/O에 대한 부하가 감소되는 겁니다.

  그러나 파티셔닝으로 인해 성능 향상이 기대 된다고 모든 테이블을 파티션 테이블로 생성해야 하는 것은 아닙니다.
  크기가 작은 테이블을 파티셔닝하게 되면 관리 포인트가 증가하게 되고, 여러 세그먼트에 분산되어 저장되기 때문에 테이블의 전체 블럭 갯수가 증가할 수 있게 됩니다. 데이터가 대용량이고 기준 조건(파티션 키 컬럼)으로 조회가 빈번한 테이블만 파티션 대상이 됩니다.

  만약 파티션 대상 테이블을 선정했다면 테이블 별 파티션 전략도 수립해야 합니다.
  아래 목록은 오라클에서 지원하는 파티션의 종류입니다. 데이터 요건과 테이블 액세스 패턴을 고려하여 각 테이블에 적합한 파티션 전략을 선택하면 됩니다.

  인덱스도 파티셔닝에 따라 아래와 같이 구분되어 질 수 있습니다.

  인덱스 파티션도 테이블 파티션과 마찬가지로 데이터 요건과 테이블 액세스 패턴을 고려하여 각 테이블에 적합한 파티션 전략을 선택해야 합니다. 
  일반적으로 Local Partitioned Index나 Nonpartitioned Index의 생성 비율이 높은 편이며, Local Nonprefixed Index는 실무에서는 거의 볼 수 없는 구조입니다.

4-2) View와 UDF 사용 최소화 (At least view and UDF)

  View는 테이블과는 다르게 별도의 저장 공간이 존재하지 않는 객체입니다. 단지 View 생성 시 정의한 SQL문이 데이터베이스에 저장되어 있으며, 사용자가 뷰를 조회 할 때 마다 저장된 SQL문을 실행하여 결과를 보여주게 됩니다.

  View를 사용하게 되면 아래와 같은 장점이 있습니다.
  첫 번째, 정보 보안성을 강화할 수 있습니다. 예를 들어, 고객 테이블에서 주민등록번호 등과 같은 개인 정보는 일반 데이터베이스 사용자에게 공개할 수 없다는 보안 정책이 존재할 수 있습니다. 이런 경우 고객 테이블에서 주민등록번호 컬럼을 제외한 나머지 컬럼만 조회하는 View를 생성하고 일반 사용자는 View를 통해 주민등록번호가 제외된 고객 정보를 조회할 수 있도록 처리할 수 있습니다.
  두 번째, 쿼리 작성의 복잡도를 줄여줍니다. 아주 복잡한 수식이 사용된 쿼리문이나 길이가 긴 쿼리문을 View로 생성할 수 있습니다. 복잡한 쿼리문 대신에 View를 사용하게 되면 쿼리문이 간결해져서 가독성이 좋아집니다.

  물론, View의 단점도 존재합니다.
  첫 번째, 관리 포인트가 많아집니다. 너무 많은 View 생성을 남발하게되면 당연히 관리해야할 객체가 증가하게 되어 이 또한 관리 포인트가 됩니다. 더 큰 문제는 View에서 액세스하고 있는 테이블이 변경되면 View에도 영향이 있을 수 있다는 겁니다. View 쿼리문에 직접 명시된 컬럼이 변경 대상이 아니라면 큰 이슈는 없을 것입니다. 하지만, 'SELECT * FROM 테이블명'과 같이 전체 컬럼 조회를 뜻하는 *(Asterisk)를 사용한 경우에는 관련 쿼리문을 수정해야 하는 사태가 발생할수도 있습니다.
  두 번째, 성능 부하의 원인이 될 수 있습니다. View 조회 시 데이터 필터링을 위해 WHERE 조건을 사용 할 수 있습니다. 정상적인 경우라면 Optimizer가 View의 Where 조건절도 테이블의 필터링 조건으로 인식하여 인덱스 사용 등의 최적화된 실행 계획을 생성하게 될 것입니다. 하지만 View안의 쿼리가 복잡한 경우 Optimizer는 우선 View를 실행 한 후 마지막에 Where 조건을 필터링하여 결과를 출력할 수도 있습니다. 출력 결과는 1~2건인데 내부적으로는 Full table scan이 발생할 수도 있다는 겁니다.

  위와 같은 이유로 필자는 보안을 위한 View외의 무분별한 View 생성은 최소화해야 된다는 원칙을 가지고 있습니다.

  UDF(User Defined Function, PL/SQL)도 마찬가지입니다.
  복잡한 계산 수식을 절차적으로 해결하기 위해 PL/SQL을 사용하여 UDF을 생성하는 경우가 있습니다. 물론, UDF를 사용하면 쿼리문이 간결해지는 장점이 있습니다.

  하지만, UDF는 View처럼 성능 부하를 유발하는 폭탄이 될 수도 있습니다.
  SELECT절에 사용된 UDF의 호출 횟수는 데이터 결과 셋의 건수와 동일합니다. 데이터 결과 셋의 건수가 10만건이라면 UDF는 10만번 수행이 되게 됩니다. 수행 횟수가 증가하니 UDF내 쿼리의 실행 횟수도 증가하여 결국 과도한 블럭 액세스를 유발하게 됩니다.

  UDF처럼 SELECT절에서 사용되는 서브 쿼리(스칼라 서브 쿼리)의 사용 여부에 따른 블럭 읽기 횟수를 비교해보도록 하겠습니다.

  위의 두 쿼리는 PROD_SALE 테이블에서 SALE_DT 값이 '20200301'인 데이터를 추출하는 쿼리입니다. PROD_SALE 데이터 중 PROD_NO에 매핑되는 PROD_NM 값을 추출하기 위해 PROD 테이블을 액세스 합니다.
  왼쪽 튜닝 전의 쿼리에서는 스칼라 서브 쿼리를 이용해 PROD 테이블의 PROD_NM 컬럼 값을 추출합니다.
  오른쪽 튜닝 후의 쿼리에서는 PROD 테이블을 조인하여 PROD_NM 컬럼 값을 추출합니다.

  위 쿼리의 AUTOTRACE 실행 결과는 아래와 같습니다.

  왼쪽 스칼라 서브 쿼리를 사용한 쿼리의 consistent gets 값은 '169,858'입니다.
  오른쪽 조인을 사용한 쿼리의 consistent gets 값은 '44,835'입니다.
  스칼라 서브 쿼리 사용 시 블럭을 읽은 횟수가 약 4배정도 많은 것을 확인 할 수 있습니다.

  위 스칼라 서브 쿼리처럼 SELECT 절에 사용되는 FUNCTION과 PROCEDURE도 마찬가지입니다. 데이터의 출력 결과 건수만큼 실행되게 되어 블럭 읽기가 증가하게 됩니다. 만약, FUNCTION이나 PROCEDURE 내 쿼리에서 Full table scan이 발생한다면 데이터 건수만큼 Full table scan이 발생하게 됩니다. 심각한 성능 저하의 원인이 되는거죠.

   이 문제를 해결하는 방법은 위 스칼라 서브쿼리처럼 FUNCTION과 PROCEDURE을 제거입니다.
  구체적인 예제는 아래 포스트를 참고하시면 됩니다.

4-3) 데이터 모델 변경(Change data model)

  데이터 모델 설계가 중요한 이유 중의 하나는 설계가 완료된 이후에는 변경이 어렵다는 점입니다.

  건축 설계를 예를 들어보겠습니다. 높은 빌딩이 완공되어 완공식을 하는데, 건물주가 1층 로비의 천장이 너무 낮다는 의견을 내놓았습니다. 완공된 건물의 1층 로비 천장을 높이기 위해 2층 바닥을 뚫기라도 해야 한다는 건가요? 절대 불가능한 일인 것입니다.

  데이터 모델도 마찬가지입니다. 오픈된 시스템의 설계 오류를 발견하더라도 큰 구조의 변경은 쉽지 않은게 현실입니다. 더군다나 24시간 365일  서비스를 하는 시스템이라면 더더욱 힘든 일이죠.

  그래도 성능 이슈를 해결하기 위해 어쩔수 없이 데이터 모델을 변경을 선택해야 하는 경우도 발생합니다.

  집계성 테이블의 경우는 데이터 모델 관점에서는 데이터 중복에 해당되므로 설계 시 최대한 생성을 배제해야 할 대상입니다. 하지만, OLTP시스템에서도 월 판매 현황 같은 집계성 데이터를 자주 조회하는 경우도 존재합니다. 이런 요건이 있다면 데이터가 중복이더라도 데이터베이스 성능을 위해 집계 테이블을 생성하는 것을 고려할 수 있습니다.  집계 테이블을 생성하고 데이터베이스 사용량이 적은 새벽 시간에 배치를 수행하면 사용량이 많은 peek 시간에 데이터 집계 쿼리로 인한 부하를 피할 수 있게 됩니다.

  그리고 거래 내역 등 크기가 큰 테이블에 파티션이 적용되지 않았거나 파티션 키가 잘못 정의된 경우도 조심스럽게 모델 변경을 고려 할 수도 있습니다.
  이런 규모의 테이블은 시스템 내에서 활용도가 높을 수 있으므로, 단순한 테이블 변경으로 작업을 마무리 할 수는 없을 것입니다.
  테이블이 변경되면 테이블과 관련된 SQL의 모든 실행 계획이 변경될 가능성이 큽니다. 어제까지만해도 잘 수행되던 쿼리가 테이블 변경으로 순식간에 악성쿼리로 변경될 가능성이 있기 때문입니다.
  또한 기존의 데이터도 유지해야 하므로 이전 테이블에서 변경된 테이블로 데이터를 이관하는 작업도 필요합니다.

  따라서, 이러한 대용량의 테이블의 구조를 변경하기 위해서는 사전 작업이 필요합니다. 우선 임시 테이블에 데이터를 적재하여 쿼리 테스트를 진행해야 합니다. 기존의 쿼리를 테스트하여 실행 계획 상에 문제가 없는지 확인이 필요합니다. 쿼리상의 문제가 없음이 확인된 후에야 현행 데이터 이관까지 포함된 테이블 변경작업을 진행할 수 있습니다. 추가로 테이블 통계 정보를 생성하거나 기존 테이블에서 데이터를 이관하는 것도 잊지 말아야 합니다.

5. SQL 튜닝 (SQL tuning)

  운영중인 데이터베이스에서 성능 저하가 발생하였을 때, 성능 최적화를 위해 선택할 수 있는 가장 큰 대안은 SQL 튜닝일 것입니다. 위에서 언급한 것처럼 운영 중인 시스템의 모델을 변경하거나 인덱스를 추가/변경하는 작업은 영향도가 큰 작업이므로 실제로 적용할 수 있는 기회는 많지 않습니다.

  SQL을 튜닝하는 구체적인 방법은 아래와 같습니다.

1) Use hint

  옵티마이저가 항상 최적의 실행 계획을 제시하는 것은 아닙니다. 조인 대상 데이터 건수가 적은데 NL조인이 아닌 Hash 조인을 수행하거나, 엉뚱한 인덱스를 액세스하는 실행 계획이 수립 될 수도 있습니다.
  이런 경우 SQL에 힌트를 사용하여 쿼리의 실행계획을 변경할 수 있습니다.

  오라클에서 제공하는 주요 힌트는 아래와 같습니다.

2) Rewrite SQL

  누누히 얘기하지만 SQL의 성능 판단 기준은 실행 시간과 블럭 액세스 횟수입니다.

  위 기준으로 악성 쿼리를 검출해보면 데이터를 절차적으로 처리하도록 작성된 쿼리를 발견하기도 합니다. 동일한 테이블을 여러 번 액세스하거나, 데이터를 줄일 수 있는 부분을 놓치고 있는 경우가 있는거죠. 물론 개발 시간이 촉박한 상황에서는 절차적인 방식으로 쿼리를 작성하고 쿼리 수행 결과가 정상이면 바로 쿼리 작성을 완료하고 넘어가는 일이 비일비재하다는 것을 알고 있습니다. 이러한 상황적 한계로 SQL 튜너가 별도로 필요한 것이고요.

  아무튼 SQL 쿼리문은 집합적인 사고로 데이터 액세스를 최소화 할 수 있는 방법을 사용해 작성되어야 합니다. 절차적으로 작성된 쿼리문은 최대한 집합적 관점에서 다시 재작성이 필요합니다.

  SQL 튜닝 예제는 아래 포스트를 참고 바랍니다. 

3) Remove UDF

 위 4-2) View와 UDF 사용 최소화 (At least view and UDF)에서 언급했듯이 UDF는 쿼리문을 간결하게 만들어주지만, 잘못 사용되면 성능 부하의 큰 원인이 됩니다. UDF사용으로 성능 부하를 일으키는 SQL문은 당연히 해당 UDF를 제거하는 작업을 해야 합니다.

  구체적인 UDF 제거 방법은 위 "4-2) View와 UDF 사용 최소화 (At least view and UDF)"에서 링크한 포스트를 참고 바랍니다.

  감사합니다.

  테이블 조인 방식은 테이블의 각 로우를 매핑 시 어떤 메커니즘을 사용하느냐에 따라 조인을 구분하는 겁니다.

  데이터 셋의 건수 등을 기반으로 옵티마이져가 적합한 조인 메커니즘을 선택하여 실행 계획을 생성하게 됩니다. 이때, 테이블 통계정보의 오류 등으로 인하여 적합하지 않은 조인 방식이 선택될 수도 있습니다. 잘못 선택된 조인 방식은 때때로 성능상의 부하를 유발하게 됩니다.
  따라서, 각 조인 방식에 대한 메카니즘을 파악하여 조회 쿼리에 적합한 조인 방식이 선택되도록 유도하는 것이 데이터베이스 성능 향상을 위한 중요한 방법 중의 하나가 되겠습니다.

  오라클에서 제공하는 조인 방식(Join methods)은 아래와 같습니다.

1. NL(Nested Loops) 조인

  Nested Loops 조인(이하 NL 조인)을 한국어로 직역하면 '중첩 반복 조인'입니다. 뜻 그대로 거듭된 반복 작업으로 조인을 수행하는 방식이 바로 NL 조인입니다.

  NL 조인은 드라이빙 테이블*의 한 로우와 매칭되는 드리븐 테이블**내 로우를 찾기위해 드리븐 테이블의 모든 데이터를 탐색하고 조인 컬럼 값이 일치하는 경우 양쪽 테이블의 두 로우를 연결합니다. 드라이빙 테이블에서 조회 조건에 부합하는 로우가 여러 건인 경우에는 로우 수만큼 위 작업을 반복적으로 수행하여 조인을 완료하게 됩니다.

* 드라이빙 테이블(Driving table) : 조인 대상 테이블 중 먼저 액세스하는 테이블을 말하며 Outer table이라고도 표현 가능합니다.
** 드리븐 테이블(Driven-to table) : 조인 대상 테이블 중 나중에 액세스하는 테이블을 말하며 Inner table이라고도 표현 가능합니다.

1.1. NL 조인 작동 방식

  오라클 문서에서 슈도(pseudo) 코드로 표현한 NL 조인 로직은 아래와 같습니다. NL 조인과 중첩된 For loop 수행이 개념적으로 동일한 로직이라고 설명하고 있습니다. 

  우선 드라이빙 테이블(위 로직에서는 employees 테이블)에서 조건에 맞는 (where X=Y) 로우를 (바깥쪽, Outer) For문을 이용해 모두 추출하게 됩니다. 이때, 드라이빙 테이블에서 로우가 추출될 때마다 (안쪽, Inner) For문을 수행하게 됩니다. For문을 수행하면서 드리븐 테이블(위 로직에서는 departments 테이블)의 로우를 탐색하는데, 드라이빙 테이블의 조인 컬럼 값과 매칭 된다면 output에 조인 결과를 저장하게 됩니다.

  위 로직을 그림으로 표현하면 아래와 같습니다.

  위에서 설명했듯이 NL 조인은 드라이빙 테이블에서 추출되는 데이터 건수만큼 드리븐 테이블을 반복해서 탐색하게 됩니다. 드라이빙 테이블에서 10건의 로우가 추출되면 드리븐 테이블을 10번 반복해서 액세스를 하게되고, 10만 건의 로우가 추출되면 드리븐 테이블을 10만 번 반복해서 액세스를 하게 됩니다.

  이와 같은 NL 조인의 특성 때문에 아래 조건을 충족해야만 NL 조인 시 좋은 성능을 보장 받을 수 있습니다.

  1) 조인 테이블 중 추출 건수가 적은 테이블을 드라이빙 테이블로 선정.
  드라이빙 테이블의 추출 건수가 곧 드리븐 테이블의 액세스 반복 횟수가 되므로 건수가 더 적은 테이블이 드라이빙 테이블로 선정되어야 합니다.     

  2) 드리븐 테이블에 조인 조건 컬럼으로 구성된 인덱스가 존재.
  드리븐 테이블은 드라이빙 테이블의 추출 건수만큼 반복해서 액세스하게 됩니다. 이때 조인 컬럼으로 구성된 인덱스가 존재한다면 드리븐 테이블의 모든 데이터를 액세스 하지 않아도 인덱스를 이용해 조인 조건에 맞는 로우를 추출할 수 있게 됩니다. 만약 조인 컬럼으로 구성된 인덱스가 존재하지 않는다면 Full table scan이 반복적으로 발생하여 성능상 이슈가 발생할 수 있습니다.

1.2. NL 조인 실행 계획

  아래 쿼리는 EMPLOYEES 테이블과 DEPARTMENTS 테이블을 조인하는 쿼리 예제입니다. 
  두 테이블의 조인 컬럼은 DEPARTMENT_ID이며, EMPOLYEES 테이블의 LAST_NAME이 'A'로 시작하는 데이터만 추출하고자 합니다. 
  EMPLOYEES 테이블에서 LAST_NAME이 'A'로 시작하는 데이터는 4건이며, DEPARTMENTS 테이블의 데이터 건수는 27건입니다.

SELECT E.EMPLOYEE_ID 
         , E.FIRST_NAME 
         , E.LAST_NAME 
         , D.DEPARTMENT_NAME 
    FROM EMPLOYEES E 
         , DEPARTMENTS D 
 WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID 
     AND E.LAST_NAME LIKE 'A%' ;

위 쿼리의 실행 계획은 아래와 같습니다.

  1) 1차 Nested Loops ( Id = 2 )
    1-1) EMP_NAME_IX 인덱스를 탐색하여 "E.LAST_NAME LIKE 'A%'" 조건에 부합하는 rowid를 취득합니다. (Id = 4)
    1-2) 1-1)에서 얻은 rowid로 EMPLOYEES 테이블을 액세스하여 EMPLOYEE_ID와 DEPARTMENT_ID값을 취득합니다. (Id = 3)
    1-3) 1-2)의 데이터가 NL조인의 Outer가 됩니다. For문처럼 Outer의 로우를 하나씩 인출하여 DEPT_ID_PK 인덱스를 탐색하고 DEPATMENT_ID 값과 매칭 되는 rowid를 취득합니다. EMPLOYEES 테이블과 DEPARTMENTS 테이블의 인덱스와 NL조인을 하게 됩니다.  (id = 5,1)

  2) 2차 Nested Loops ( Id = 1 )
    2-1) 1-3)의 데이터가 NL 조인의 Outer가 됩니다. For문처럼 Outer의 로우를 하나씩 인출하여 rowid로  DEPARTMENTS 테이블에 액세스하여 DEPARTMENT_NAME 값을 취득합니다. (Id = 6,1)

2. 해시(Hash) 조인

  해시 조인은 조인 키 컬럼 값의 해시 함수 결과를 이용하여 조인하는 방식입니다. NL 조인과는 상대적으로 큰 데이터 셋을 조인하기 위한 방식입니다.

2.1. Hash 조인 작동 방식

  해시 조인에서도 조인 대상 테이블 중 크기가 더 작은 테이블부터 액세스 합니다.
  해시 조인에서는 먼저 액세스 하는 테이블을 빌드(Build) 테이블, 나중에 액세스 하는 테이블은 프로브(Probe) 테이블이라고 부릅니다.

  ① 빌드 테이블을 읽어 각 로우의 조인 키 컬럼 값을 해시 함수에 대입합니다. 
  ② 해시 함수의 결과 값을 이용해 메모리(PGA)에 해시 테이블을 생성합니다. 해시 함수 결과 값은 해시 테이블에서 각 로우가 저장된 슬롯의 위치를 나타냅니다. 만약, 조인 키 컬럼 값이 상이하지만 동일한 해시 함수 결과 값이 나온 로우는 동일한 슬롯(Slot)에 저장되며 각 데이터는 링크드 리스트로 연결됩니다.

  ③ 빌드 테이블의 모든 로우가 해시 테이블에 저장이 완료되면, 프로브 테이블의 데이터를 한 로우씩 읽기 시작합니다. 프로브 테이블도 빌드 테이블과 마찬가지로 조인 키 컬럼 값을 ②에서 사용된 동일한 해시 함수에 입력하여 결과 값을 계산합니다.
  ④ 계산된 해시 함수 결과 값을 이용해 해시 테이블 내 관련된(해시 결과 값이 동일한) 슬롯을 찾아갑니다. 만약, 해시 결과 값이 동일한 슬롯이 없다면 조인에 실패한 로우이므로 프로브 테이블의 다음 로우를 읽어 조인 연산을 계속 진행합니다.
  ⑤ 해시 결과 값으로 관련 슬롯을 찾았으면 슬롯 안에 저장된 빌드 테이블의 로우를 확인합니다. 로우의 조인 컬럼 값이 프로브 테이블 로우의 조인 컬럼 값과 일치하면 두 로우를 매핑하여 결과 셋(Result set)에 저장합니다. 슬롯 내에는 동일한 조인 컬럼 값을 가진 로우가 여러 건 존재할 수 있으므로 슬롯 내 링크드 리스트를 끝까지 탐색하여 조인을 완료합니다.

  해시 조인에서는 아래 조건을 충족하는 경우 좋은 성능을 보장 받을 수 있습니다.

  1) 조인 테이블 중 추출 건수가 적은 테이블을 빌드 테이블로 선정.
    조인을 위해 빌드 테이블의 데이터는 PGA 메모리의 해시 테이블에 저장됩니다. 해시 테이블의 크기가 커서 PGA 메모리가 부족해지면 가상 메모리를 사용하기 떄문에 DISK I/O가 발생하게 됩니다. 따라서, 데이터 건수가 적은 테이블이 빌드 테이블로 선정되어야 하며, 쿼리문 상에 결과 셋에 저장될 컬럼명만 명시하여 해시 테이블의 크기를 최소화해야 합니다.

  2) 프로브 테이블은 Full table scan이 유리.
    일반적으로 해시 조인에서 프로브 테이블은 대용량의 테이블입니다. 만약 인덱스를 이용해 프로브 테이블을 액세스하면 테이블 랜덤 액세스가 과도하게 발생하여 블록 I/O가 증가하게 됩니다. 테이블 액세스 없이 INDEX SCAN만으로 데이터 추출이 가능한 경우는 제외하고, 되도록이면 인덱스 스캔 없이 테이블만 스캔하여 블럭 I/O를 줄여야 합니다.

  참고로, 오라클 문서에서 슈도(pseudo) 코드로 표현한 해시 조인 로직은 아래와 같습니다.

2.2. Hash 조인 실행 계획

  아래 쿼리는 PROD 테이블과 PROD_SALE 테이블을 조인하는 쿼리 예제이며, 조인 컬럼은 PROD_NO입니다. PROD 테이블의 전체 테이블 건수는 71,734건입니다. PROD_SALE 테이블은 SALE_DT 컬럼으로 파티셔닝 되어 있으며, SALE_DT = '20191231'인 데이터 건수는 287,290건으로 대용량 테이블입니다.

SELECT S.SALE_DT
           , P.PROD_NM
           , S.PROD_QTY 
  FROM PROD P
           , PROD_SALE S 
 WHERE P.PROD_NO = S.PROD_NO 
     AND S.SALE_DT = '20191231' ;

  위 쿼리의 실행 계획은 아래와 같습니다.

  1) 두 테이블 중에서 크기가 작은 PROD 테이블이 빌드 테이블이 되며, FULL TABLE SCAN을 하게 됩니다. PROD 테이블의 각 ROW를 읽으면서 조인 칼럼의 값인 PROD_NO 값을 해시 함수에 대입한 결과 값으로 해시 테이블을 메모리에 생성합니다. (Id = 2)

  2) 해시 테이블 생성이 완료되면 프로브 테이블인 PROD_SALE 테이블을 FULL TABLE SCAN하기 시작합니다.(Id = 4) PROD_SALE 테이블은Where 절의 파티션 컬럼 필터링 조건으로 인하여 하나의 파티션만 액세스 하게 됩니다.(Id = 3)

  3) PROD_SALE 테이블의 로우를 하나씩 읽으면서 조인 컬럼 값인 PROD_NO 값을 해시 함수에 대입한 결과 값으로 해시 테이블 내 관련 슬롯을 찾아가게 됩니다. 슬롯에 저장된 빌드 테이블의 데이터와 조인 키 칼럼 값을 비교하면서 컬럼 값이 일치하는 데이터와 매핑하여 조인을 완료하게 됩니다. (Id = 1)

3. 소트 머지(Sort Merge) 조인

  소트 머지 조인은 조인 대상 테이블 데이터의 조인 키 컬럼 값을 기준으로 각 데이터 셋을 정렬한 후 작은 값부터 양쪽의 조인 키 컬럼 값이 매칭 되는지 확인하여 조인하는 방법입니다.

  오라클의 옵티마이저는 조인 조건이 동등 조건(=)이 아니거나(해시 조인은 동등 조건만 가능) 인덱스 사용으로 정렬 작업을 생략 할 수 있는 경우에 해시 조인 대신 소트 머지 조인을 선택합니다.
  또한, 해시 조인에서 해시 테이블이 너무 커서 PGA 메모리 부족으로 가상 메모리(디스크)를 사용하게 되면 디스크 I/O가 빈번하게 발생하게 됩니다. 프로브 테이블의 조인 키 칼럼 값은 정렬된 상태가 아니기 때문에 해시 테이블의 블록을 여러 번 읽어야 할 수도 있습니다. 하지만 소트 머지 조인은 조인 키 컬럼 값이 정렬된 상태이기 때문에 가상 디스크를 사용하더라도 디스크 I/O 횟수가 해시에 비해 훨씬 적다는 장점이 있습니다.

 3.1. Sort Merge조인 작동 방식

  소트 머지 조인을 수행하기 위해서 각 조인 대상 테이블은 조인 키 컬럼 기준으로 정렬되어 메모리(PGA)에 저장됩니다.

  정렬된 첫 번째 데이터 셋에서 첫 번째 로우를 읽어 조인 키 컬럼 값이 두 번째 데이터 셋에 존재하는지 확인합니다. 
  만약, 매칭되는 조인 키 컬럼 값이 두 번째 데이터 셋에 존재하지 않는다면 첫 번째 데이터 셋을 순차적으로 읽어 먼저 읽은 로우의 칼럼 값 보다 큰 로우의 컬럼 값을 찾습니다. 

  위 그림에서 첫 번째 데이터 셋의  '10'은 두 번째 데이터 셋의 첫 번째 값인 '20'보다 작은 값이므로 매칭되는 로우가 없으므로 첫 번째 데이터 셋의 '10' 다음의 값인 '20'을 읽게 됩니다.

  두 번째 테이블에서 아직 탐색이 진행되지 않았으므로 첫 번째 컬럼 값부터 데이터를 읽습니다. 이 값이 첫 번째 데이터 셋에서 읽은 값 '20'과 동일하다면 해당 값보다 큰 데이터가 나올 때까지 두 번째 데이터 셋을 읽어 나가면서 매칭 된 데이터를 결과 셋에 저장하게 됩니다. 
  두 번째 테이블에서 '20'보다 큰 값을 발견하면 해당 로우의 위치를 기억해놓고 첫 번째 테이블로 돌아가 다음 로우를 읽습니다. 로우 값 '30'을 읽어 두 번째 테이블에서 마지막 탐색 위치의 값과 비교합니다. 두 번째 테이블의 값도 '30'이므로 두 데이터를 결과 셋에 저장합니다. 마찬가지로 두 번째 테이블에서의 탐색을 계속 진행하는데 다음 로우의 값은 '30'보다 크므로 탐색을 중지하고 다시 첫 번째 데이터셋으로 돌아가 다음 로우를 읽습니다.

  첫 번째 데이터 셋의 다음 로우의 값 '40'은 두 번째 데이터 셋의 마지막 탐색 로우의 값인 '50'보다 작으므로 매칭 되는 로우가 없는 것입니다.
  다시 첫 번째 데이터 셋의 다음 로우인 '50'을 읽습니다. 두 번째 데이터 셋의 마지막 탐색 로우의 값인 '50'과 동일하므로 두 번째 데이터 셋을 계속 탐색하면서 매칭되는 데이터를 결과 셋에 저장하게 됩니다.
  두 데이터 셋의 탐색이 종료되면 조인 작업이 완료된 것입니다.

참고로, 오라클 문서에서 슈도(pseudo) 코드로 표현한 소트 머지 조인 로직은 아래와 같습니다.

3.2. Sort Merge조인 실행 계획

  아래 쿼리는 EMPLOYEES 테이블과 DEPARTMENTS 테이블을 DEPARTMENT_ID 조인 키 컬럼 기준으로 조인하는 쿼리 예제입니다. 두 테이블을 인덱스 사용 없이 Full table scan을 하기 위해 NO_INDEX 힌트를 사용하였으며, 소트 머지 조인을 하기 위해 USE_MERGE 힌트를 사용하였습니다.

SELECT /*+ USE_MERGE(d e) NO_INDEX(e) NO_INDEX(d) */ 
           D.DEPARTMENT_NAME 
         , E.FIRST_NAME 
         , E.LAST_NAME 
   FROM EMPLOYEES E 
         , DEPARTMENTS D 
 WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID ;

  위 쿼리의 실행 계획은 아래와 같습니다.

  1) 첫 번째 테이블 DEPARTMENTS 테이블을 Full table scan 하여 조인 키 컬럼 기준으로 정렬하는 Sort join 작업을 진행합니다. ( Id = 3,2 )

  2) 두 번째 테이블 EMPLOYEES 테이블도 Full table scan하여 조인 키 컬럼 기준으로 정렬하는 Sort join 작업을 진행합니다. ( Id = 5,4 )

  3) 정렬된 키 컬럼 값을 순차적으로 읽어 두 테이블 양쪽 모두에 조인 컬럼 키 값이 존재하면 매핑하여 데이터 결과 셋에 추가합니다. ( Id = 1 )

  이상으로 조인 방식에 대해 설명드렸습니다.

  참고로 데이터베이스에서 조인을 구분하는 방법은 위에서 언급한 조인 방식 외에 조인 종류도 존재합니다.
  우리가 쿼리 작성 시 사용하는 Inner join, Outer join 등의 문법으로 구분하는 조인 방법입니다. (깊게 살펴보면 한 쪽 테이블에만 존재하는 데이터 출력 방법이라고 설명할 수 있습니다.)

  조인 종류에 대해서는 아래 포스트를 참고하시면 됩니다.

  데이터베이스에서 데이터는 다수의 테이블에 나뉘어 저장되어 있습니다. 데이터의 중복을 제거하고 무결성을 보장하기 위해서 데이터 성격에 따라 분류하여 테이블에 저장을 하는 겁니다.
  이렇게 테이블별로 분리되어 있는 데이터를 연결하여 하나의 결과 데이터 셋으로 출력해야 할 때가 반드시 존재합니다. 이럴때 사용하는 것이 조인(Join)입니다. 

  기본적인 조인문 사용 방법은 아래와 같습니다.

  위 예제에서는 EMPLOYEES 테이블과 DEPARTMENTS 테이블을 조인하고 있습니다. 여기서 조인 조건은 양쪽 테이블의 조인 키 컬럼인 DEPARTMENT_ID 컬럼 값이 동일한 경우이며, EMPLOYEES 테이블의 LAST_NAME 컬럼과 DEPARTMENTS 테이블의 DEPARTMENT_NAME 컬럼의 값이 결과로 출력됩니다.

  위 예제와 같이 두 개의 테이블을 조인하기 위해서는 각 테이블에 조인 키로 사용 할 수 있는 컬럼이 존재해야 합니다. 두 테이블 간의 연결고리인 조인 키 컬럼을 사용해 테이블 내 각 데이터들의 짝꿍을 찾아갈 수 있게 됩니다.
  ( 물론 조인 조건절을 생략하여 한 로우와 상대쪽 테이블의 모든 로우를 매핑시키는 Cartesian join도 존재합니다. 하지만 이 포스트에서는 기본 조인 조건인 등호 기호(=)를 이용한 동등 조인(equi-join)을 기준으로 설명하겠습니다. )

  그런데 이 조인 키 컬럼의 값이 양 쪽 테이블에 모두 존재하는 것이 아니라 한쪽 테이블에만 존재하는 경우(짝꿍이 없는 경우)가 발생할 수 있습니다.
  '상품' 테이블과 '고객주문상품' 테이블을 예로 들어보겠습니다.
  신규 상품인 망고(상품ID : 4)가 '상품' 테이블에 입력되었습니다. 하지만 신규 상품인 망고의 고객 주문이 발생하지 않는다면, '고객주문상품' 테이블에는 망고의 상품ID 값 '4'가 존재할 수 없습니다. 상품ID '4'는 오직 '상품' 테이블에만 존재하게 됩니다.

  이런 경우에 짝궁이 없는 데이터를 출력 결과에 포함할 수도 있고 제외할 수도 있습니다. 
  바꿔서 말하면 우리가 어떤 조인 종류를 선택함에 출력 결과가 달라질 수 있습니다. 

  우리가 수학시간에 배웠던 집합의 종류를 생각해보면 이해가 더 쉬워질 수 있습니다.
  데이터 집합 A와 B가 존재하는 경우, 결과 데이터 집합은 다양한 집합 연산으로 추출할 수 있습니다. 전체 데이터 집합을 추출하기 위해 합집합( A ∪ B ) 연산을 할 수 있고요. 공통인 데이터 집합을 추출하기 위해 교집합( A ∩ B ) 연산을 할 수도 있습니다. 이렇듯 집합 연산에 따라 결과 데이터 집합은 달라지게 됩니다.

  그럼, 조인 종류(Join Type)에 대해 자세히 살펴보겠습니다. 대표적인 조인 종류는 아래와 같이 4가지가 존재합니다.

1. Inner join

  Inner join은 교집합( A ∩ B ) 연산과 같습니다. 조인 키 컬럼 값이 양쪽 테이블 데이터 집합에서 공통적으로 존재하는 데이터만 조인해서 결과 데이터 집합으로 추출하게 됩니다.

  Inner join 구문은 아래와 같습니다.

  위 ANSI-SQL 구문에서 'INNER' 키워드는 생략이 가능합니다. 우리가 일반적으로 사용하는 조인문( 'JOIN' 키워드만 사용하는 구문)이 바로 Inner join 구문입니다.

  아래는 조인 종류를 설명할 예제 데이터입니다. 

  고객주문상품 테이블의 상품ID가 '5'인 데이터는 부모 테이블인 상품 테이블에 존재하지 않는 값으로 FK 위반입니다. 하지만, OUTER JOIN의 예를 설명하기 위한 억지스러운 설정일 뿐입니다. 무결성에 위배되는 데이터지만 설명을 위해 이해 부탁드립니다.

  위 [예제 1] 상품 테이블과 고객주문상품 테이블을 Inner join하는 예제를 살펴보겠습니다.

-- ANSI-SQL
SQL> SELECT p.상품ID, p.상품명, o.상품ID, o.고객ID, o.주문일자, o.주문수량
    2>   FROM 상품 p
    3>    JOIN 고객주문상품 o
    4>        ON p.상품ID = o.상품ID;

  Inner join의 결과는 아래와 같습니다.

  상품 테이블과 고객주문상품에서 조인 키 컬럼 값이 모두 존재하는 데이터만 출력된 것을 확인하실 수 있습니다.

2. Left outer join

  Left outer join은 교집합 연산 결과와 차집합 연산 결과를 합친 것( (A ∩ B) ∪ (A - B) )과 같습니다.
  조인 키 컬럼 값이 양쪽 테이블 데이터 집합에서 공통적으로 존재하는 데이터와 Left outer join 키워드 왼쪽에 명시된 테이블에만 존재하는 데이터를 결과 데이터 집합으로 추출하게 됩니다.

  Left outer join 구문은 아래와 같습니다.

  ANSI-SQL 구문에서는 FROM 절에 조인하고자 하는 두 테이블명 사이에 'LEFT OUTER JOIN' 키워드를 입력합니다.
  ORACLE에서는 WHERE 절에서 오른쪽 테이블의 조인 키 컬럼명 뒤에 '(+)' 기호를 입력해줍니다.

  위 [예제 1] 상품 테이블과 고객주문상품 테이블을 Left outer join 하는 예제를 살펴보겠습니다.

-- ANSI-SQL
SQL> SELECT p.상품ID, p.상품명, o.상품ID, o.고객ID, o.주문일자, o.주문수량
    2>    FROM 상품 p
    3>     LEFT OUTER JOIN 고객주문상품 o
    4>        ON p.상품ID = o.상품ID;

   조인하고자 하는 테이블명을 LEFT OUTER JOIN 키워드를 기준으로 양쪽에 입력하고, 조인 조건은 ON 키워드 다음에 작성해줍니다.

-- ORACLE SQL
SQL> SELECT p.상품ID, p.상품명, o.상품ID, o.고객ID, o.주문일자, o.주문수량
    2>   FROM 상품 p
    3>             , 고객주문상품 o
    4>  WHERE p.상품ID = o.상품ID(+);

  Oracle에서는 FROM 키워드 이후에 조인할 테이블명을 콤마(,)로 구분하여 나열해주고, WHERE절에 조인 조건을 작성하되, 오른쪽 테이블의 조인 키 컬럼명 뒤에 '(+)' 기호를 추가로 입력해줍니다.

  Left outer join의 결과는 아래와 같습니다.

  상품 테이블과 고객주문상품에서 조인 키 컬럼 값이 모두 존재하는 데이터와 왼쪽에 명시되었던 테이블의 상품 테이블에만 존재하는 데이터(상품ID가 4인 데이터)가 출력된 것을 확인하실 수 있습니다.
  왼쪽 상품 테이블에만 데이터가 존재하는 로우의 오른쪽 고객주문상품 테이블 컬럼 값은 NULL로 표시됩니다.

3. Right outer join

 Right outer join도 교집합 연산 결과와 차집합 연산 결과를 합친 것( (A ∩ B) ∪ (A - B) )과 같습니다. 차집합의 기준 집합이 Left outer join과 반대일 뿐입니다.
  아무튼 조인 키 컬럼 값이 양쪽 테이블 데이터 집합에서 공통적으로 존재하는 데이터와 Right outer join 키워드 오른쪽에 명시된 테이블에만 존재하는 데이터를 결과 데이터 집합으로 추출하게 됩니다.

  Right outer join 구문은 아래와 같습니다.

  ANSI-SQL 구문에서는 FROM 절에 조인하고자 하는 두 테이블명 사이에 'RIGHT OUTER JOIN' 키워드를 입력합니다.
  ORACLE에서는 WHERE 절에서 왼쪽 테이블의 조인 키 컬럼명 뒤에 '(+)' 기호를 입력해줍니다.

  위 [예제 1] 상품 테이블과 고객주문상품 테이블을 Right outer join 하는 예제를 살펴보겠습니다.

-- ANSI-SQL
SQL> SELECT p.상품ID, p.상품명, o.상품ID, o.고객ID, o.주문일자, o.주문수량
    2>    FROM 상품 p
    3>   RIGHT OUTER JOIN 고객주문상품 o
    4>        ON p.상품ID = o.상품ID;

   조인하고자 하는 테이블명을 RIGHT OUTER JOIN 키워드를 기준으로 양쪽에 입력하고, 조인 조건은 ON 키워드 다음에 작성해줍니다.

-- ORACLE SQL
SQL> SELECT p.상품ID, p.상품명, o.상품ID, o.고객ID, o.주문일자, o.주문수량
    2>   FROM 상품 p
    3>            , 고객주문상품 o
    4>  WHERE p.상품ID(+) = o.상품ID;

  Oracle에서는 FROM 키워드 이후에 조인할 테이블명을 콤마(,)로 구분하여 나열해주고, WHERE절에 조인 조건을 작성하되, 왼쪽 테이블의 조인 키 컬럼명 뒤에 '(+)' 기호를 추가로 입력해줍니다.

  Right outer join의 결과는 아래와 같습니다.

  상품 테이블과 고객주문상품에서 조인 키 컬럼 값이 모두 존재하는 데이터와 오른쪽에 명시되었던 테이블의 고객주문상품 테이블에만 존재하는 데이터(상품ID가 5인 데이터)가 출력된 것을 확인하실 수 있습니다.
  오른쪽 고객주문상품 테이블에만 데이터가 존재하는 로우의 왼쪽 상품 테이블 컬럼 값은 NULL로 표시됩니다.

4. Full outer join

  Full outer join은 합집합 연산 결과와 같습니다. 
  조인 키 컬럼 값이 양쪽 테이블 데이터 집합에서 공통적으로 존재하는 데이터와 한쪽 테이블에만 존재하는 데이터도 모두 결과 데이터 집합으로 추출하게 됩니다.

  Full outer join 구문은 아래와 같습니다.

  ANSI-SQL 구문에서는 FROM 절에 조인하고자 하는 두 테이블명 사이에 'FULL OUTER JOIN' 키워드를 입력합니다.
  ORACLE에서 '(+)' 기호를 이용한 Full outer join은 지원하고 있지 않습니다. Full outer join은 ANSI-SQL 구문 형식과 동일하게 사용하면 됩니다.

  위 [예제 1] 상품 테이블과 고객주문상품 테이블을 Right outer join 하는 예제를 살펴보겠습니다.

-- ANSI-SQL & ORACLE SQL
SQL> SELECT p.상품ID, p.상품명, o.상품ID, o.고객ID, o.주문일자, o.주문수량
    2>    FROM 상품 p
    3>     FULL OUTER JOIN 고객주문상품 o
    4>        ON p.상품ID = o.상품ID;

   조인하고자 하는 테이블명을 FULL OUTER JOIN 키워드를 기준으로 양쪽에 입력하고, 조인 조건은 ON 키워드 다음에 작성해줍니다.

  Full outer join의 결과는 아래와 같습니다.

  상품 테이블과 고객주문상품에서 조인 키 컬럼 값이 모두 존재하는 데이터와 상품 및 고객주문상품 테이블에만 존재하는 데이터(상품ID가 4,5인 데이터)가 출력된 것을 확인하실 수 있습니다.
  왼쪽 상품 테이블에만 데이터가 존재하는 로우의 오른쪽 고객주문상품 테이블 컬럼 값은 NULL로, 오른쪽 고객주문상품 테이블에만 데이터가 존재하는 로우의 왼쪽 상품 테이블 컬럼 값도 NULL로 출력됩니다.

  이상으로 조인의 종류에 대해 알아봤습니다.

  다음 포스트에서는 조인 방식(Methods)에 대해 설명드리도록 하겠습니다.
  조인 종류는 한쪽 테이블에만 존재하는 로우의 출력 여부를 기준으로 조인을 구분한 것이라면,
  조인 방식은 각 로우를 매핑을 하는 매커니즘에 따라 조인을 구분한다고 보시면 됩니다.
  더 자세한 내용은 아래 포스트를 참고해주세요.

  윈도우 10 환경에서 최신 버전의 오라클 19c 버전을 설치해보겠습니다.

1. Oracle 설치 프로그램 다운로드

  먼저, 아래 오라클 홈페이지에 접속합니다.

> URL : https://www.oracle.com/

  오라클 홈페이지 상단 로고 옆에 메뉴 버튼을 클릭해보면 위와 같이 오라클에서 제공하는 다양한 서비스들을 확인할 수 있을 것입니다.

  오라클 데이터베이스가 국내 데이터베이스 점유 시장 1위인 관계로 많은 분들이 오라클 사가 단지 데이터베이스 회사로만 알고 있을 수 있습니다. 하지만, 위 메뉴에서 확인되는 것처럼 오라클은 데이터베이스 외에도 다양한 솔루션을 개발하여(물론 자바와 MySQL처럼 인수 합병에 의해 자사 솔루션으로 종속시킨 경우도 있긴합니다.) 시장에 선보이고 있습니다.

  오라클도 역시 시대 흐름에 따라 클라우드 플랫폼 전쟁에 본격적으로 참여한 상태입니다. 후발주자로 타 플랫폼을 따라잡기 위해 가성비를 내세우며 클라우드 플랫폼 마케팅을 진행하고 있는 상황입니다. 공식 홈페이지 메뉴 구성에서만 봐더라도 그동안의 주력 상품이었던 On-Premise 오라클 데이터베이스가 메인 자리를 Cloud 플랫폼에 내어주게되었네요. (뭐 이정도는 당연지사로 느껴질 수 있겠죠.)

  아무튼, 이 포스팅에서 우리는 데스크탑에서 테스트 용으로 사용 할 데이터베이스를 설치할 계획이니, 위 메뉴에서 빨간색으로 표시된 'On-Premise Infrastructure' 아래에 'Oracle Database'를 선택하면 됩니다.

참고로 On-Premise Infrastrcture는 사용자 소유의 서버나 데스크탑에 설치하여 사용하는 환경을 의미합니다. 아마존 AWS나 마이크로소프트 Azure와 같은 클라우드 환경과는 상반되는 의미라고 생각하면 되겠습니다.

  오라클 메인 화면에서 'Oracle database'를 클릭하면 위와 같이 다운로드 웹페이지로 연결되는 'Download Oracle Database 19c' 버튼을 확인 할 수 있을 것입니다. 위 버튼을 클릭하면 아래와 같이 버전 및 OS 환경별로 오라클을 다운로드 받을 수 있는 페이지가 표시 됩니다.

만약, 위 두 과정이 귀찮다면 아래 URL로 직접 접속하시면 됩니다.

>URL : https://www.oracle.com/database/technologies/oracle-database-software-downloads.html#19c

  위 웹페이지에 접속하여 본인 OS 환경에 맞는 설치파일을 다운로드 받으면 됩니다. 이 포스트에서는 Windows 10 환경에 오라클을 설치 할 계획이므로 'Microsoft Windows x64(64-bit)'를 선택하겠습니다.

  참고로, 오라클은 2018년부터 오라클 네이밍 방식이 변경되었습니다. 12c까지는 메이저 버전을 사용하였는데요, 2018년도부터는 출시 년도를 사용하는 명명법으로 바뀌게 되었다고 합니다.

  아직 20c 버전의 출시 예정일은 공지되지 않은 상태이고요. 현재 최신 버전은 작년에 선보인 19c입니다.

  그리고 '19c'처럼 숫자 뒤에 붙쳐진 알파벳에도 의미가 존재합니다.
  19c에 사용된 'c'는 Clould의 약자입니다. 12c 버전부터 'c'를 사용하기 시작했는데요. 새로운 메이저 버전에 적용된 클라우드 컴퓨팅 기술을 표현하는 방식이라고 이해하시면 되겠습니다.
  이런 네이밍 방식이 적용된 것은 오라클 8i (8.1.5.0) 버전부터였는데요. 8i에 사용된 'i'는 Internet을 뜻합니다. 메이저 버전인 8 뒤에 'i'를 붙여 인터넷 환경을 제공하는 데이터베이스라는 의미를 버전을 통해 표현을 한 것입니다.
  메이저 버전이 10으로 업그레이드되면서 숫자 뒤에 붙여지는 알파벳이 'g'로 변경되었습니다. 'g'는 'Grid'를 뜻하고요. 마찬가지로 그리드 컴퓨팅 기술을 적용한 데이터베이스라고 생각하시면 됩니다. 

다시 본론으로 돌아와서 설치파일 다운로드를 계속 진행해보겠습니다. 

다운로드 버튼을 클릭하면 아래와 같이 라이센스 정책 동의에 관한 창이 뜨게 됩니다. 오라클 라이센스 정책을 확인 후 동의한다는 체크 박스를 클릭하고 Download 버튼을 클릭하면 본격적으로 설치 파일이 로컬에 다운로드 됩니다. 이 때, 오라클 계정으로 로그인 되어 있어야만 다운로드가 가능합니다.

2. 오라클 소프트웨어 설치

  다운로드한 파일을 압축 해제하면 아래와 같은 설치 파일을 볼 수 있을 것입니다.

  setup.exe 파일을 실행하면 아래와 같이 설치 다이얼로그가 나타날 것입니다.

  첫 화면에서는 두 가지 설치 옵션을 확인할 수 있습니다. 

- 단일 인스턴스 데이터베이스 생성 및 구성 : 다이얼로그에서의 설명처럼 소프트웨어 설치 뿐만 아니라 데이터베이스 생성까지 한번에 진행하게 됩니다.
- 소프트웨어만 설정 : 우선 소프트웨어만 설치합니다. 데이터베이스 생성은 추후에 DBCA(Database Configuration Assistant) 프로그램을 실행하여 작업을 할 수 있습니다.

  여기서는 DBCA를 사용하여 데이터베이스를 생성 할 계획이므로 '소프트웨어만 설정' 옵션을 선택하도록 하겠습니다.
  다음 버튼을 클릭하면 아래와 같이 왼쪽 목록에 '소프트웨어만 설정'에 맞도록 설치 순서가 변경되어 있음을 확인 할 수 있을 것입니다. 

  그리고 이 단계에서는 데이터베이스 설치 유형을 선택해야 합니다.

  - 단일 인스턴스 데이터베이스 설치 : 하나의 데이터베이스를 한 서버에서 실행시키는 방법입니다. 하나의 서버로 운영되기 때문에 서버 장애가 발생하면 서비스가 중지되는 문제점이 있습니다. 이 문제점을 보완하기 위해 두 대의 서버를 HA(High ability) 또는 이중화로 구성하여 사용하기도 합니다. 

  - Oracle Real Application Clusters 데이터베이스 설치 : RAC 설치. 클러스터링된 다수의 서버에 각각 인스턴스를 실행하여 데이터를 처리하는 구성 방법입니다. 단일 인스턴스로 서버 이중화를 구성하게 되면 동시에 한 서버만 Active 상태가 되고, 남은 서버는 Active 서버가 Failover되어 서비스가 넘어 올 때까지(절체 될 때까지) Standby하게 됩니다. 서버 자원의 부하가 Active인 하나의 서버에만 집중되기에 자원의 낭비가 심하게 됩니다. 이러한 이중화 구성의 문제점을 해결하고자 나온 기술이 RAC입니다.

  RAC 설치를 위해서는 다수의 서버가 필요하고, 이 과정은 테스트 용도이기 때문에 '단일 인스턴스 데이터베이스 설치'를 선택하여 데이터베이스를 설치하도록 하겠습니다.

  설치하고자 하는 데이터베이스 버전을 선택한 후 다음 버튼을 클릭합니다.

  오라클 홈 사용자 계정은 오라클에서 권장하는 '가상 계정 사용'을 선택하도록 하겠습니다.

  Oracle Base 경로를 지정해줍니다.

  메모리 크기 등 설치 환경에 대한 검사를 진행하게 됩니다. 검사가 완료되고 설치 환경상의 문제가 없다면 아래와 같이 소프트웨어 설치 정보가 출력됩니다.

  이제 '설치' 버튼을 클릭하면 소프트웨어 설치가 시작됩니다.

  소프트웨어가 정상적으로 설치되었다면 아래와 같은 성공 메세지를 확인 할 수 있을 것입니다.

  그리고 윈도우 시작메뉴를 보면 아래와 같이 새로 생성된 'Oracle - OraDB19Home1' 디렉토리와 설치된 오라클 소프트웨어를 확인하실 수 있을것입니다.

3. 데이터베이스 생성

  Windows 환경에서 신규 오라클 데이터베이스는 DBCA(Database Configuration Assistant) 프로그램을 사용하여 생성합니다. 시작 메뉴 에서 'Oracle - OraDB19Home1' 디렉토리에 있는 Database Configuration Assistant를 선택하여 실행해 보겠습니다.

  첫 화면에서는 사용자가 수행 할 작업을 선택하게 됩니다. 여기서는 신규로 데이터베이스를 생성 할 계획이므로 '데이터베이스 생성'을 선택 한 후 다음을 클릭합니다.

  데이터베이스 생성 모드는 일반 구성과 고급 구성 중에서 선택 할 수 있습니다. 여기서는 일반 구성 모드를 선택하여 설치하도록 하겠습니다. 일반 구성 모드를 선택하여도 기본적인 데이터베이스 정보 입력이 필요합니다.

  - 전역 데이터베이스 이름 : Oracle System Identifier(SID)를 뜻합니다. 단일 인스턴스 구성 모드에서도 데이터베이스는 여러 개 생성이 가능합니다. 단지 특정 시점에 하나의 데이터베이스만 서비스가 가능 할 뿐입니다. 데이터베이스를 시작할 때 오라클 소프트웨어는 ORACLE_SID라는 환경 변수에 명시된 데이터베이스를 실행하게 됩니다. 이렇듯 SID는 데이터베이스를 Unique하게 구별해주는 식별자 역할을 하게됩니다.

  - 저장 영역 유형 : 데이터베이스 파일 등을 어떤 형태로 저장 할 지 결정하는 옵션입니다. 오라클에서는 ASM(Automatic Storage Management, 자동 저장 영역 관리)으로 디스크 저장 영역을 관리해주는 기능을 제공해주고 있습니다. 이 기능을 사용하면 파일 경합 등에 의한 부하가 감소되는 장점이 있습니다. 하지만, 별도의 디스크 볼륨이 필요하므로 데스크탑 환경에서는 선택하기 힘든 기능입니다. 여기서는 운영체제 '파일 시스템'을 선택하도록 하겠습니다.

- 데이터베이스 파일 위치 : 데이터베이스 파일이 위치하게 될 기본 경로를 설정하게 됩니다.

- FRA(빠른 복구 영역) : 아카이브 로그, flashback 로그 및 RMAN 백업 공간으로 FRA 영역을 사용할 수 있습니다. FRA 영역에서 파일을 저장 할 기본 경로를 설정해줍니다.

- 데이터베이스 문자 집합 : 데이터베이스의 캐릭터 셋을 설정합니다. 호환을 위해 기본값인 UTF-8을 선택하겠습니다.

- 관리 비밀번호 및 비밀번호 확인 : 관리자 계정의 비밀번호를 입력합니다.

- 컨테이너 데이터베이스로 생성 : 이번 설치작업에서는 테스트 용도이므로 이 항목은 비활성화 합니다.

  위와 같이 입력되었으면 다음 버튼을 클릭합니다.

  최종 설치 전 설정한 데이터베이스 생성 정보를 마지막으로 확인하게 됩니다. '완료'버튼을 클릭하면 데이터베이스 생성을 시작하게 됩니다.

  데이터베이스 생성이 정상적으로 완료되면 아래와 같은 화면을 볼 수 있을 것입니다.

  완료 화면에서 '비밀번호 관리' 버튼을 클릭하면 아래와 같이 데이터베이스 사용자 계정의 잠금 여부와 비밀번호 설정이 가능합니다.

4. 오라클 환경 변수 확인

  Windows 환경에서 오라클의 환경 변수는 레지스트리를 통해 관리되고 있습니다.

  윈도우 시작 메뉴 옆 검색 창에서 'regedit'를 입력하면 레지스트리 편집기를 확인 할 수 있습니다. 레지스트리 편집기를 클릭하여 실행해보겠습니다.

  레지스트리 편집기가 실행되면 왼쪽 탐색 트리에서 '컴퓨터\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDB19Home1'를 선택하여 이동합니다.

  그러면 위와 같이 오라클 환경 변수 값들을 확인 할 수 있을 것입니다.

5. 오라클 데이터베이스 실행 및 테스트

  지금까지 설치한 데이터베이스에 접속해보도록 하겠습니다.

  시작 메뉴 에서 'Oracle - OraDB19Home1' 디렉토리에 있는 'SQL Plus'를 선택하여 실행해 보겠습니다.

sys 사용자로 로그인합니다. sys 계정은 시스템 계정이므로 사용자명 입력 시 뒤에 '/as sysdba'를 입력해줘야 합니다.

정상적으로 데이터베이스에 접속되었으면 테스트를 위해 임의의 테이블 tab1을 생성하고 데이터를 입력해보겠습니다.

SQL> create table tab1 (
    2   col1 number(4),
    3   col2 varchar2(20)
    4   );

SQL> insert into tab1 vlaues (1, 'ABCD');

SQL> select * from tab1;

정상적으로 데이터가 입력되고 조회되는 것을 확인하실 수 있을 것입니다.

아마존 창업자 제프 베이조스(Jeff Bezos)의 고객 경험 전략

1) 사람이 인간으로서 가지고 있는 본능에 응답하는 것
2) 기술의 진화로 일어나는 문제와 스트레스를 해결하는 것
3) 사용자 한 명 한명에 맞춘 권유와 추천으로 '빅데이터 AI'가 실시간으로 일대일 마케팅을 실행하는 것
4) 고객이 거래하고 있다고 느끼지 않게 하는 것

- 트랜드코리아2020 중에서 -

  판매 시장 경쟁이 심화되면서 판매업체들의 마케팅 전략이 바뀌어가고 있다고 한다.
  과거에는 단순히 제품에 대한 장점을 내세운 광고를 통해 다수의 고객의 시선을 사로 잡기 위한 전략이 주를 이뤘다.
  하지만, 날로 경쟁이 치열해지면서 개인만을 위한 마케팅이 부각되고 있다.
  여기서 더 나아가 시시각각 변화하는 고객의 심리를 분석하는 초개인화 마케팅으로 고객의 구매도를 높이는 전략을 사용한다고 한다.

  이러한 초개인화 마케팅이 가능한 이유가 무엇일까?
  현대의 소비자들은 네트워크 접점에서 수많은 데이터를 생산해내고 있다. 기업들은 고객이 생산한 데이터들을 수집하여 자신들의 데이터베이스에 차곡차곡 쌓아두게 된다. 이렇게 저장된 다양한 데이터를 기반으로 각 고객의 소비 패턴을 분석하고 소비자의 심리를 파악하여 향후의 소비를 예측할 수 있게 되었다.
  데이터 수집/분석으로 초개인화 마케팅을 위한 발판을 마련할 수 있게 된 것이다.
  이러한 사업 생태계에 적응하기 위해 기업들은 데이터의 중요성을 인식하고 최대한 다양하고 많은 데이터를 수집하려고 한다.

  하지만, 수많은 데이터를 무조건 수집한다고 경영진이 원하는 매출을 높일 수 있는 고품질의 마케팅 재료가 될 수는 없을 것이다.
  IT업계에서 오래전부터 명언으로 여겨지는 한 문장이 있다. 'Garbege In, Garbege Out.'. 프로그램의 입력 데이터가 쓰레기이면 결과도 쓰레기라는 뜻이다. 데이터 분석에도 당연히 적용되는 문장이다.

  만약, 고객의 사용 구매 패턴을 분석하여, 정기적으로 의류를 구매하던 고객의 구매 주기가 점점 길어지게 되자 캠페인의 일환으로 쿠폰을 제공해주게 되었다고 하자.
  쿠폰 발급을 알리기 위해 고객에게 전화로 Outbounding(외부 영업. 텔레마케팅도 이에 속한다. 반대로 Inbounding은 고객이 고객센터에 문의하는 경우라 생각하면 된다.) 실시하였다.
  "사랑합니다. OOO고객님. ***쇼핑몰입니다."
  이에 대한 고객의 반응.
  "전화 잘못 거셨습니다. 저는 OOO가 아니고요. ***쇼핑몰은 처음 들어보는데요."
  어디서부터 문제였을까?
  물론 고객이 최근에 전화번호를 변경했을 수도 있다. 하지만, 시스템 운영의 에러로 고객 데이터가 Garbege로 전락해버리는 경우가 전혀 발생하지 않는다고 장담할 수 없다는 게 현실이다.

  위의 경우에서 알 수 있듯이, 데이터를 활용하기 위해서는 우선 데이터의 품질을 믿을 수 있어야한다. 다른 말로 표현하면 데이터의 가치를 높이기 위해서는 기본적으로 데이터의 무결성이 보장되어야 한다는 것이다. 그리고 그 배경에는 무결점인 데이터를 양산해내는 정확하고 유연하게 설계된 시스템이 존재해야 한다.
  원론적인 이야기이지만, 모든 일에 기초가 중요하듯이 가치있는 데이터를 수집하기 위해서는 기초 작업인 데이터베이스 설계가 중요하다는 뜻이다. RDBMS를 기준으로는 데이터 표준을 준수하여 정규화된 데이터 모델을 설계하는 작업이 중요한 초석이 된다는 것이다.

  여기에서 한걸음 더 나아가 생각해보자.
  데이터를 활용하기 위해서는 데이터의 품질도 중요하지만, 다양하고 많은 데이터가 필요하다. 빅데이터의 특징인 3V(Volume: 규모, Variety: 다양성, Velocity: 속도)에 충족하는 데이터가 준비되어야 한다는 뜻이다. 고객의 구매 패턴을 분석하고자 하는데, 비회원으로 1개의 상품을 주문한 기록만으로는 구매 패턴을 유추할 수는 없는 노릇이다.

  그러면 어떤 방식으로 더 다양한 데이터를 많이 수집할 수 있을까?
  최근에는 사용자의 성향을 파악하기 위해 다양한 데이터를 수집을 한다고 한다.
  예를 들어, '장바구니에 보관된 상품은 무엇인가?', '검색이나 문의사항에 대한 텍스트 마이닝 결과는 무엇인가?', '목록을 스크롤 하다가 정지한 시점에 표출된 게시물은 무엇인가?', '유입 경로는 어떻게 되는가?' 등 이러한 질문의 결과를 파악할 수 있는 백데이터도 중요한 수집대상이 되고 있다고 한다. 이러한 데이터를 수집하기 위해서는 당연히 아이디어가 선행되어야 하고, 그에 따른 데이터를 수집할 수 있는 환경을 사용자에게 제공해줘야 한다.

  사용자 활동 분석을 위해 사용자의 스크롤 이벤트를 저장한다고 가정을 해보자. 이 작업을 위해서는 스크롤 이벤트에 관한 정보를 실시간으로 저장하는 기능을 구현해야 하는 것은 기본이다. 더 나아가 사용자가 마음껏 스크롤을 할 수 있는 환경을 제공해줘야 한다. 사용자가 원하는 물품을 찾고자 상품 목록을 스크롤 중인데, 상품 정보가 스크롤 후 1초 뒤에나 표시된다면 사용자의 스크롤 액션은 이어짐 없이 매 순간마다 끊김이 발생할 것이다. 더 아나가 인내심이 부족한 사용자는 검색을 포기하고 다른 사이트를 찾아갈 것이다.

  시스템의 느린 성능으로 더 많은 데이터를 수집 할 수 있는 기회를 놓쳐버린 것이다. 역으로 이야기하면 다양하고 많은 데이터를 수집하기 위해서는 고성능의 서비스를 제공할 수 있는 시스템이 구축되어야 한다는 것이다.

  지금까지 설명한 데이터를 제대로 활용하기위한 2가지 전제 조건은 다음과 같다.

1) 데이터 무결성을 보장할 수 있는 정확하고 유연한 시스템 설계
2) 사용자가 제약없이 마음껏 이용할 수 있는 고성능의 시스템 제공

  이 2가지 전제 조건 중에서 두 번째 조건인 고성능의 시스템, 그중에서도 데이터베이스의 성능 향상에 대해 이야기를 해보고자 한다.

  필자는 신규 시스템을 구축하는 SI(System Integration) 프로젝트 중심으로 업무를 진행해왔다. SI 프로젝트의 특징 상 여러 사이트를 돌아다니면서 업무를 수행하였고, 다양한 사이트에서 근무를 하다 보니 많은 사람들을 만나게 되었다. 다수의 분들은 데이터 설계와 성능의 중요성을 정확히 인지하고 계셔서 고품질의 결과물을 생산하기 위해 적극적으로 협력하고자 하였다.

  하지만, 아쉽게도 데이터베이스 성능에 대한 오해를 하시는 분들도 간혹 만나기도 하였다. 그분들이 갖고 있는 데이터베이스 성능에 대한 오해의 예는 다음과 같다.

'Optimizer가 자동 SQL튜닝을 수행하는데요. 정규화나 SQL 튜닝 작업이 왜 필요한 거죠?'
'디스크/메모리가 부족하면 추가하면 돼요. 하드웨어 가격이 저렴 하잖아요.'

  위 정보가 100% 잘못된 것은 아니다. 

  자동 SQL 튜닝을 자사 제품의 특징으로 내세우는 RDBMS가 존재하고, Optimizer의 기능을 향상하기 위해 RDBMS 솔루션 업체들이 연구/개발을 하고 있는 것은 사실이기 때문이다. 필자도 Query rewrite 기능에 의해 쿼리 성능이 좋아진 경우도 실제로 확인한 적도 있었다.

  하지만, 필자가 SQL 튜닝 업무를 담당했을 때 검출되었던 악성 쿼리들을 보고 나면 데이터베이스 최적화라는 업무 영역은 앞으로도 영원히 살아남을 수밖에 없겠다는 생각을 하게 되었다. 불필요하게 중복 액세스 하는 쿼리, 개발상 편의를 이유로 Select절에서 사용자 정의 함수를 과도하게 호출하는 복잡한 쿼리들을 보면 RDBMS의 자동 튜닝 기능의 한계도 같이 보이는 것 같았다. 어차피 Optimizer와 자동 SQL 튜닝 기능도 인간의 작품이라는 한계점이 있을 것이다.
  인공지능이 발달한다 해도 이 세상의 SQL을 모두 다 최적화하지는 못할 것이다.  SQL이 정해진 문법이라는 공식을 준수하여 작성되지만, 작성 패턴에는 공식이 없으며 다수의 개발자들 각각의 스타일대로 작성되기 때문이다. 그리고 개발 기간이라는 시간적 제약에 의해 깊이 생각하지 못하고 빠르게 결과물을 출력하기 위해 절차적인 방식으로 쿼리를 작성하여 쿼리 문장이 더 복잡해지는 경우가 많기 때문이다.

  빅데이터 시스템의 경우도 생각해보자. 대표적인 빅데이터 시스템인 하둡은 분산 저장하고 분산 처리하여 대용량의 데이터를 빠르게 처리하는 시스템이다. 작업을 분산 처리하는 방식이니 자원이 부족하면 데이터 노드를 추가하여 성능을 높여주면 된다고 한다.
  하지만, 고사양(RAM 256GB에... 더 이상 기억 안 난다. 나는 숫자 외우는 거에 약하다...) 데이터 노드가 30개 이상이었던 시스템에서 쿼리 하나가 실패되어 후속 배치 작업도 도미노처럼 줄줄이 실패되는 현상을 목격했었다. 데이터 노드가 고사양이어도, 하둡에서 Fail 된 Task를 일정 횟수만큼 Retry 하더라도 최적화가 안된 악성 쿼리는 결국 실패할 가능성이 존재하는 것이다.

  앞에서도 잠깐 언급했지만, 힘들게 구축된 시스템이 최적의 성능을 내지 못하는 가장 큰 이유는 촉박한 개발 일정으로 결과물을 빨리 보여주는 것에만 집중할 수밖에 없는 현실 때문이라고 본다. 장인 정신으로 시스템의 최적화를 고려해보는 것은 시간적 사치라고 생각하시는 분들도 더러 있을 수도 있다.

  시간에 쫓겨 급하게 처리해서 대충 마무리 짓는 쳇바퀴 도는듯한 업무 방식에 익숙해져 있다면, 본인의 업무 역량의 한계점을 정해놓고 앞으로 달려가고 있는 것이 아닐까? 

  "대학 경 1장"에서는 멈추고 생각을 한 후에야 얻을 수 있다고 하였다. 잠시 여유를 가지고 본인이 담당하고 있는 시스템의 최적화를 고민해보는 시간을 갖는다면 본인의 역량을 키우는 또 다른 첫걸음을 내딛을 수 있을 것이다.

  SQL 튜닝은 어렵다? 데이터 아키텍처 최적화가 가능한가? 처음부터 이런 의문으로 최적화에 대한 두려움을 느끼시는 분들도 있을 수 있다. 무엇이든 방향성 없이 시작하는 것은 어렵게 느껴지니까 말이다. 그럴 때 가장 좋은 방법은 따라 하기라고 생각한다. 다양한 케이스의 예제를 접해보고 이해한다면 언젠가는 직접 적용해보고 응용해 볼 수 있는 기회를 만날 수 있을 것이다.

  필자는 앞으로 이 블로그에서 SQL 튜닝 & 데이터 아키텍처에 대한 최적화 예제를 포스팅하려고 한다. 미리 예고하지만 빠른 시간 내에 많은 예제를 보여줄 수 있다는 자신감은 없다. 이렇게 글을 쓰고 있지만 나에게는 아직도 글쓰기가 힘들게 느껴지는 일이다. 그리고, 보여줄 수 있는 소재의 한계도 있으니까 말이다.

  그래도 SQL 튜닝에 대한 성취감을 많은 분들이 느낄 수 있도록 최선을 다해 포스팅해보도록 하겠다. 글을 쓰는 나에게도 다시 한번 정리의 기회가 되고, 다른 분들한테도 영감을 줄 수 있는 즐거운 일이 될 테니까 말이다.

  Hive 설치 후 간단한 DML 문을 실행하였는데, 아래와 같이 OutOfMemoryError: PermGen space가 발생하였습니다. 실행했던 HQL이 단순하게 하나의 row를 삽입하는 문장이었는데 메모리 부족이 발생하다니, 초기 설정 그대로 테스트를 해보는 것은 욕심이었나봅니다.

  우선, 위에서 메모리 부족이라고 알려 준 PermGen space가 무엇인지 궁금해서 찾아보았습니다.
(참고로, 난 java 개발자가 아니라 데이터쟁이라서 Java 아키텍처에 대한 지식이 깊지않습니다. PermGen이 기본 중의 기본이라면 부끄러울 따름입니다.)

  구글링해서 찾은 Perm 영역에 대한 설명을 그대로 옮겨보았습니다.

Java 1.7 기준의 Perm 영역은 보통 Class의 Meta 정보나 Method의 Meta 정보, Static 변수와 상수 정보들이 저장되는 공간이다. 이 영역은 Java 1.8부터는 Native 영역으로 이동하여 Metaspace 영역으로 변경되었다.(다만, 기존 Perm 영역에 존재하던 Static Object는 Heap 영역으로 옮겨져 최대한 GC 대상이 될 수 있도록 하였다.)

조금 더 1.7과 1.8 메모리 구조의 변경에 대해 설명하자면, Java 1.8에서 JVM 메모리의 구조적인 개선 사항으로 Perm 영역이 Metaspace 영역으로 전환되고 기존 Perm 영역은 사라지게 되었다. Metaspace 영역은 Heap이 아닌 Native 메모리 영역으로 취급하게 된다. (이 말은 기존의 Perm은 Heap 안에 존재하는 것으로 알 수 있다.) Native 메모리는 Heap과 달리 OS레벨에서 관리 하는 영역이다.

출처: https://coding-start.tistory.com/205 [코딩스타트]

JDK 7: PermGen

Permanent Generation still exists in JDK 7 and its updates, and is used by all the garbage collectors. In JDK7, the effort to remove the permanent generation was started and some parts of the data residing in the permanent generation were moved to either the Java Heap or to the native heap. Permanent generation was not completely removed and it still exists in JDK 7 and its updates. Here's the list of things that were moved out of the permanent generation in JDK7:

출처 : https://blogs.oracle.com/poonam/about-g1-garbage-collector%2c-permanent-generation-and-metaspace

  간단하게 요약을 해보면 Garbage collector를 위해 Meta 정보를 저장하거나 Static 변수나 상수가 저장되는 공간이라고 합니다.

  아무튼, Permanent 공간이 부족하다고 하니 이 영역의 크기를 확장해 줄 필요가 있겠습니다.

  현재 설정된 상태를 살펴봅니다. 아래 명령어를 사용하면 Permanent 크기를 확인 할 수 있습니다.

> java -XX:+PrintFlagsFinal -version -server | grep PermSize

  현재 시스템에서 최대 Permanent 크기가 85,983,232 byte(82MB)로 설정되어 있는 것을 확인 할 수 있습니다.

  이 Permanet의 크기를 조정해주기 위해서는 HADOOP_OPTS에 관련 속성 설정 값을 추가해주면 됩니다. Hive 구동시마다 적용되도록 $HIVE_HOME/conf/hive-env.sh에 아래 옵션을 추가해줍니다.

export HADOOP_OPTS="$HADOOP_OPTS -XX:PermSize=512M -XX:MaxPermSize=1024M"

  옵션 적용을 위해서 hive 서비스를 재기동합니다.
  그리고 설정이 제대로 적용되었는지 확인하기 위해서 hive 서비스 프로세스를 확인해 보았습니다.

  위에 Hive를 실행하는 자바프로세스 옵션에 우리가 방금 설정한 PermSize와 MaxPermSize가 추가되어 있는 것을 확인할 수 있을 것입니다.
  위와 같이 설정된 상태에서 hive 클라이언트에 접속하여 수차례 Insert 구문을 실행해봐도 에러 없이 정상적으로 수행되었음을 확인 할 수 있을 것입니다.

  CentOS 환경에서 Hive 설치&테스트를 위해서 MySQL 설치가 선행되어야 했었습니다. 
  MySQL 사용이 주목적이 아닌 관계로 yum을 이용해 최신 버전의 MySQL(8.0.17)을 쉽게 설치할 수 있었지만, 지금은 Hive metastore로 사용할 목적이므로 Hive와의 안정적인 호환성을 고려하여 5.7 버전을 다운로드 받아 설치하고자 합니다.

  자세한 설치 환경 정보는 아래와 같습니다.

- MySQL : 5.7.28
- OS : CentOS 8

1. 설치 파일 다운로드

  이전 버전의 MySQL community server를 다운로드하기 위해 아래 MySQL Product Archives 사이트에 접속합니다.

* URL : https://downloads.mysql.com/archives/community/

  설치하고자 하는 MySQL 버전과 OS 정보를 선택하면 다운로드 가능한 rpm 파일 목록을 볼 수 있습니다. 이 중 모든 패키지가 포함된 bundle을 wget을 이용하여 다운로드합니다. 다운로드 URL은 위 웹페이지의 Download 버튼을 우클릭하여 나타난 팝업 메뉴에서 Link 주소 복사(웹 브라우저별 상이함)를 선택하면 됩니다. 

> wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar

2. 설치 파일 압축해제 및 설치

다운로드받은 bundle 파일을 압축해제하고,

> tar xvf mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar

아래와 같이 yum localinstall 명령어를 이용해서 패키지들을 설치해줍니다. rpm 명령어를 사용하여 하나씩 설치해줄 수도 있지만, yum을 사용하면 패키지간의 의존 관계를 참조하여 한번에 설치 가능하므로 여기서는 yum을 이용하여 설치하도록 하겠습니다.

> sudo yum localinstall mysql-community-*

3. my.cnf

  설치가 정상적으로 완료되면 /etc 디렉토리내 my.cnf 설정 파일이 생성된 것을 확인 할 수 있습니다.

  my.cnf내 기본적으로 설정된 서버 옵션 항목들에 대한 설명은 아래와 같습니다.

  그 외 설정 가능한 옵션은 아래 MySQL 홈페이지를 참고하면 됩니다.

 * URL : https://dev.mysql.com/doc/refman/5.7/en/server-option-variable-reference.html

4. mysql 서비스구동

  각 사용 환경에 맞게 서버 옵션 설정이 완료가 되었으면, mysql 서비스를 구동해 보겠습니다.

> sudo systemctl start mysqld

  mysql 서비스가 정상적으로 시작되었다면 별도의 메세지 없이 다음 명령 입력을 대기하는 prompt가 표시될 것이며, 아래 명령으로 서비스 상태를 확인 할 수 있습니다.

> sudo systemctl status mysqld

  mysql이 처음으로 구동되면 my.cnf의 datadir 옵션에서 설정한 경로에 mysql운영에 필요한 기본 데이터베이스 관련 파일 등이 생성된 것을 확인 할 수 있을 것입니다.

5. root 계정 비밀번호 변경

* 참고 URL : https://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html

  mysql의 root 계정의 비밀번호는 설치 시 임의의 값으로 임시 지정되어 있습니다. mysql을 안정적으로 사용하기 위해서는 우선 root 계정의 비밀번호 변경작업을 선행해야 합니다.

  참고로, 임시 비밀번호는 아래와 같이 my.cnf에서 log-error로 설정한 에러로그 파일에서 확인 가능합니다.

> sudo grep 'password' /var/log/mysqld.log

  root계정의 비밀번호를 변경하기 위해서는 무제약모드로 mysql 서비스를 구동해야 합니다. 만약, mysql 서비스가 구동중인 상태라면 먼저 아래 명령을 사용하여 서비스를 중지합니다.

> sudo systemctl stop mysqld

그리고 무제약모드로 서비스를 구동하기 위해 아래와 같이 옵션을 설정해줘야 합니다.

> sudo systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"

  --skip-grant-tables 옵션은 용어 그대로 mysql 시스템 데이터베이스 내의 모든  grant table 읽기를 skip하겠다는 설정입니다. 권한 시스템이 없이 서비스가 구동되므로 데이터베이스에 아무런 제약없이 액세스가 가능한 것입니다.

  옵션 변경이 완료되었으면, mysql 서비스를 시작하고 서비스가 제대로 구동되었는지 확인해 보겠습니다.

> sudo systemctl start mysqld
> sudo systemctl status mysqld

  서비스가 정상적으로 올라왔으면 mysql 클라이언트를 사용해 root계정으로 MySQL 서버에 접속해 보겠습니다.

> mysql -u root

  접속에 성공하면 우선 서버가 계정관리명령을 수행할 수 있게 grant table을 재적재하도록 flust privileges 명령을 수행합니다.

mysql> FLUSH PRIVILEGES;

  그리고 ALTER USER 명령을 이용하여 root 계정의 비밀번호를 변경합니다.

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '신규비밀번호';

  ALTER USER 명령문 대신에 아래와 같이 직접 USER 테이블 데이터를 UPDATE하는 방법도 존재합니다.

mysql> UPDATE mysql.user SET authentication_string = PASSWORD('신규비밀번호')
      -> WHERE User = 'root' AND Host = 'localhost';

mysql> FLUSH PRIVILEGES;

  비밀번호 설정이 완료되었으면 --skip-grant-tables 옵션을 비활성화하기 위해 mysql 서비스를 재가동해줘야 합니다.

> sudo systemctl stop mysqld
> sudo systemctl unset-environment MYSQLD_OPTS
> sudo systemctl start mysqld
> sudo systemctl status mysqld

6. mysql 서버 접속 및 테스트

  이제 본격적으로 mysql을 사용해 보겠습니다. mysql 클라이언트를 사용해 root 계정으로 접속합니다. 
  참고로 -u 옵션은 로그인 할 사용자 계정을 명시하기 위한 옵션이며, -p 옵션은 로그인 시 비밀번호를 사용하여 접속을 하겠다는 뜻입니다. 정상적으로 접속이 완료되면 아래와 같이 'mysql>' 프로프트를 확인할 수 있을 것입니다.

  mysql이 정상적으로 작동하는지 점검하기 위해 데이터베이스와 테이블을 생성하고, 데이터를 입력한 뒤 조회하는 쿼리문을 실행해 봅니다.

mysql> show databases;                -- 데이터베이스 목록 출력
mysql> create database test;         -- 이름이 test인 데이터베이스 생성
mysql> use test;                            -- 사용할 기본 데이터베이스로 test 데이터베이스를 선택
mysql> create table tab1 (            -- tab1 테이블을 생성
       ->   col1 int,                            -- tab1 테이블은 정수를 저장할 수 있는 int형의 col1 컬럼과
       ->   col2 varchar(20)               -- 20자리의 문자열을 저장할 수 있는 varchar형의 col2 컬럼으로 구성됨.
       ->  );
mysql> insert into tab1               -- tab1 테이블에 데이터 삽입
       -> values (1, 'ABCDE');
mysql> select * from tab1;          -- tab1 테이블의 데이터 조회

  기본 SQL문을 수행하여 MySQL이 정상적으로 동작함을 확인할 수 있습니다.

  필자의 부끄러운 삽질 고백이 되는 포스팅이 되겠습니다. 작성하고 나서 처음에는 비공개로 저장 했다가, 그래도 누군가에게 작은 도움을 줄 수도 있겠다는 생각에 공개로 전환하였습니다. 아.. 삽질은 진짜 싫습니다.. 그래도 그것 때문에 더 깊이 배우게 됩니다.

  CentOS 8에서 MySQL 5.7 버전을 설치하고 구동하는 과정에서 아래와 같은 에러가 발생했습니다.

Job for mysqld.service failed because the control process exited with error code.
See "systemctl status mysqld.service" and "journalctl -xe" for details.

  위 메세지에서 설명한대로 systemctl status와 journalctl 명령을 수행해보았지만 간략한 상태 설명만으로는 정확한 원인 파악은 불가능하였습니다.

● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: activating (start-pre) since Wed 2020-03-11 14:57:26 KST; 1s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 12993 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=1/FAILURE)
Cntrl PID: 13009 (mysqld_pre_syst)
    Tasks: 2 (limit: 23999)
   Memory: 51.9M
   CGroup: /system.slice/mysqld.service
           ├─13009 /bin/bash /usr/bin/mysqld_pre_systemd
           └─13026 /usr/libexec/platform-python -Es /usr/sbin/semanage fcontext -a -e /var/lib/mysql /var/lib/mysql-files

 3월 11 14:57:26 hmng systemd[1]: Starting MySQL Server...

  자세한 원인 파악을 위해 mysql 로그를 확인해 보았습니다.
  mysql의 로그 파일위치는 my.cnf 설정에서 확인 가능합니다. (기본 위치 : /etc/my.cnf)

  mysqld.log 로그파일 내용을 살펴보니 아래 내용이 기록되어 있었습니다.

2020-03-11T06:10:21.372491Z 0 [ERROR] InnoDB: Unsupported redo log format. The redo log was created with MariaDB 10.3.17. Please follow the instructions at http://dev.mysql.com/doc/refman/5.7/en/upgrading-downgrading.html
2020-03-11T06:10:21.372510Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2020-03-11T06:10:21.994333Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2020-03-11T06:10:21.994443Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2020-03-11T06:10:21.994484Z 0 [ERROR] Failed to initialize builtin plugins.
2020-03-11T06:10:21.994709Z 0 [ERROR] Aborting

  지원하지않는 redo log format이라고 하네요. MySQL 설치 후 처음으로 구동하는 과정이라 redo log가 존재하면 안되는 상황이었습니다. 그것도 MariaDB 10.3.17 에서 생성된 redo log가 존재한다고 로그가 친절히 알려줬습니다.

  이실직고하면 Hive metastore로 사용하기 위해 MariaDB 최신버전인 10.3.17를 설치했다가 호환성 등의 이유로 삭제하고 downgrade하여 MySQL 5.7 버전을 설치하다가 이런 에러를 만나게 된 것입니다.
  MariaDB를 삭제할 때 단순하게 yum remove 명령을 사용하였는데, 이 명령만으로는 MariaDB에서 생성한 데이터 파일과 로그 파일 등은 삭제되지 않고 계속 존재하였던 것입니다.

  my.cnf 설정에서 데이터파일 저장 경로를 확인한 후

  해당 디렉토리에 저장된 파일을 살펴보았습니다.

  위 파일의 최종 수정 시간을 확인해보니 MySQL 설치 시점 이전에 생성된(3월 7일) 파일들도 존재하였습니다. 이전에 설치 후 삭제된 Mariadb와 관련된 파일이 삭제되지 않고 그대로 유지된 채 몇몇 파일은 Overwrite된 것입니다.

  참고로 위에 저장된 파일을 살펴보도록 하겠습니다.

  위 데이터 디렉토리 내 파일들이 이전버전의 파일들과 혼재되어 있는 상태이기 때문에 복구가 불가능한 상황입니다. 필자의 경우에는 신규 설치하는 경우라서, 다시 Mysql을 Uninstall하고 재설치 하였습니다. 물론, Uninstall 후 데이터 파일 등을 수동으로 삭제한 뒤 재설치 작업을 진행해줘야 합니다.

  PS. datadir내 파일을 삭제하고 다시 설치를 완료한 뒤,  바로 datadir에 명시된 디렉토리를 확인해보았는데 생성된 파일이 없었습니다. mysql을 처음 구동하는 시점에 관련 파일들이 새로 생성되는 것이었습니다. 위 에러 상황에서 datadir 내 파일을 삭제하고 다시 서비스 재시작을 했으면 정상적으로 mysql 서비스가 시작될 수 있었을거라 예상되지만, 그냥 깔끔하게 재설치하는게 마음 편하네요.