운영중인 DB가 느려진 것 같아서 인덱스 리빌드를 결정했다.

대상 선정을 위한 쿼리를 만들어 보았다.

SELECT
    PSUI.schemaname
    , PSUI.relname AS table_name
    , PSUI.indexrelname AS index_name
    , PSUI.idx_scan /* 인덱스가 사용된 횟수 */
    , PSUI.idx_tup_read /* 인덱스 튜플 수 */
    , PSUI.idx_tup_fetch /* 인덱스에서 실제 검색된 튜플 수 */
    , pg_size_pretty(pg_relation_size(PSAI.indexrelid)) AS index_size /* 인덱스의 크기 */
    , 'REINDEX INDEX CONCURRENTLY ' || PSUI.schemaname || '.' || PSUI.indexrelname || ';'
FROM pg_stat_user_indexes PSUI
    LEFT OUTER JOIN pg_stat_all_indexes PSAI
        ON PSAI.relname = PSUI.relname
        AND PSAI.indexrelname = PSUI.indexrelname
ORDER BY PSUI.idx_tup_read DESC, PSUI.idx_scan DESC, pg_relation_size(PSAI.indexrelid) DESC
;
------------------------------------------------------------------------------------------------------------------------
-- 거의 사용되니 않는 인덱스 -> 삭제하는 게 나을 수 있겠다
SELECT
    PSUI.schemaname
    , PSUI.relname AS table_name
    , PSUI.indexrelname AS index_name
    , PSUI.idx_scan /* 인덱스가 사용된 횟수 */
    , PSUI.idx_tup_read /* 인덱스 튜플 수 */
    , PSUI.idx_tup_fetch /* 인덱스에서 실제 검색된 튜플 수 */
    , pg_size_pretty(pg_relation_size(PSAI.indexrelid)) AS index_size /* 인덱스의 크기 */
    , 'REINDEX INDEX CONCURRENTLY ' || PSUI.schemaname || '.' || PSUI.indexrelname || ';'
FROM pg_stat_user_indexes PSUI
    LEFT OUTER JOIN pg_stat_all_indexes PSAI
        ON PSAI.relname = PSUI.relname
        AND PSAI.indexrelname = PSUI.indexrelname
WHERE PSUI.idx_scan < 100
ORDER BY PSUI.idx_scan DESC
;
------------------------------------------------------------------------------------------------------------------------
-- 크기가 너무 큰 인덱스
SELECT
    PSUI.schemaname
    , PSUI.relname AS table_name
    , PSUI.indexrelname AS index_name
    , PSUI.idx_scan /* 인덱스가 사용된 횟수 */
    , PSUI.idx_tup_read /* 인덱스 튜플 수 */
    , PSUI.idx_tup_fetch /* 인덱스에서 실제 검색된 튜플 수 */
    , pg_size_pretty(pg_relation_size(PSAI.indexrelid)) AS index_size /* 인덱스의 크기 */
    , 'REINDEX INDEX CONCURRENTLY ' || PSUI.schemaname || '.' || PSUI.indexrelname || ';'
FROM pg_stat_user_indexes PSUI
    LEFT OUTER JOIN pg_stat_all_indexes PSAI
        ON PSAI.relname = PSUI.relname
        AND PSAI.indexrelname = PSUI.indexrelname
ORDER BY pg_relation_size(PSAI.indexrelid) DESC
LIMIT 50
;
------------------------------------------------------------------------------------------------------------------------
-- 테이블 싸이즈에 비해 크기가 너무 큰 인덱스
SELECT
    PSUI.schemaname
    , PSUI.relname AS table_name
    , PSUI.indexrelname AS index_name
    , PSUI.idx_scan /* 인덱스가 사용된 횟수 */
    , PSUI.idx_tup_read /* 인덱스 튜플 수 */
    , PSUI.idx_tup_fetch /* 인덱스에서 실제 검색된 튜플 수 */
    , pg_size_pretty(pg_relation_size(PSAI.indexrelid)) AS index_size /* 인덱스의 크기 */
    , pg_size_pretty(pg_relation_size(T.oid)) AS table_size /* 테이블의 크기 */
    , ROUND(pg_relation_size(PSAI.indexrelid)::numeric / pg_relation_size(T.oid) * 100, 2) AS INDEX_RATIO
    , 'REINDEX INDEX CONCURRENTLY ' || PSUI.schemaname || '.' || PSUI.indexrelname || ';'
FROM pg_stat_user_indexes PSUI
    INNER JOIN pg_class T
        ON PSUI.relname = T.relname
    LEFT OUTER JOIN pg_stat_all_indexes PSAI
        ON PSAI.relname = PSUI.relname
        AND PSAI.indexrelname = PSUI.indexrelname
WHERE pg_relation_size(T.oid) > 0
AND pg_relation_size(PSAI.indexrelid) > 0
AND PSUI.idx_scan > 0
AND ROUND(pg_relation_size(PSAI.indexrelid)::numeric / pg_relation_size(T.oid) * 100, 2) > 100
ORDER BY INDEX_RATIO DESC
LIMIT 50
;
------------------------------------------------------------------------------------------------------------------------
-- 오래된 인덱스
SELECT
    PSUI.schemaname
    , PSUI.relname AS table_name
    , PSUI.indexrelname AS index_name
    , PSUI.idx_scan /* 인덱스가 사용된 횟수 */
    , PSUI.idx_tup_read /* 인덱스 튜플 수 */
    , PSUI.idx_tup_fetch /* 인덱스에서 실제 검색된 튜플 수 */
    , pg_size_pretty(pg_relation_size(PSAI.indexrelid)) AS index_size /* 인덱스의 크기 */
    , pg_stat_get_last_vacuum_time(PSAI.indexrelid) AS last_vacumm
    , 'REINDEX INDEX CONCURRENTLY ' || PSUI.schemaname || '.' || PSUI.indexrelname || ';'
FROM pg_stat_user_indexes PSUI
    LEFT OUTER JOIN pg_stat_all_indexes PSAI
        ON PSAI.relname = PSUI.relname
        AND PSAI.indexrelname = PSUI.indexrelname
WHERE PSUI.idx_scan = 0
OR PSAI.idx_scan = 0
ORDER BY last_vacumm ASC NULLS FIRST
;

2025년 SQLD 자격증 시험 일정과 준비 방법

SQLD (SQL Developer) 자격증은 데이터베이스 개발 및 관리에 필요한 SQL과 데이터베이스 관련 기술을 평가하는 자격증으로, IT 분야에서 데이터베이스 관련 업무를 담당하는 데 중요한 역할을 합니다. 2025년 SQLD 자격증 시험 일정과 함께, 효과적인 준비 방법에 대해 안내드리겠습니다.

2025년 SQLD 자격증 시험 일정

SQLD 자격증은 주기적으로 실시되며, 2025년 시험 일정은 다음과 같습니다:

• 시험 일정은 대체로 매 분기마다 실시되며, 응시 등록은 해당 접수 기간 내에만 가능합니다.
• 시험 방식은 객관식으로 진행되며, 온라인 또는 오프라인 시험을 선택할 수 있습니다.

SQLD 자격증 준비 방법

SQLD 자격증 시험은 데이터베이스와 SQL에 대한 기초적인 지식과 실무 경험을 평가합니다. 시험 준비를 위해서는 이론적인 학습과 실습을 병행하는 것이 중요합니다. 아래는 각 항목별로 준비 방법을 제시합니다.

(1) SQLD 시험 준비 개요

SQLD 시험은 데이터베이스의 기본적인 이해, SQL 사용법, 관계형 데이터베이스 설계, 성능 최적화 등 다양한 영역을 다룹니다. 시험 준비는 기본 이론과 실제 SQL 활용 능력을 키우는 데 집중해야 합니다.

(2) 주요 시험 과목

• SQL 기본 문법: SELECT, INSERT, UPDATE, DELETE와 같은 기본적인 SQL 명령어에 대한 이해.
• 데이터베이스 설계: ERD(Entity-Relationship Diagram) 설계, 정규화(Normalization)와 역정규화(Denormalization), 데이터 모델링.
• SQL 성능 최적화: 인덱스, 조인, 서브쿼리 등의 성능 최적화 기법.
• 트랜잭션 관리: ACID 속성, 트랜잭션 처리 및 관리.
• 데이터베이스 관리 시스템(DBMS): DBMS의 기본 개념, 종류, 사용법.

(3) 준비 방법

1. 기본적인 SQL 문법 학습

   • SQL의 기본적인 문법을 철저히 이해하고 연습해야 합니다. 실습을 통해 각 SQL 명령어를 활용할 수 있는 능력을 기르는 것이 중요합니다. SELECT 문을 이용해 데이터 조회, JOIN을 이용한 다중 테이블 연산 등을 충분히 연습하세요.

2. 관계형 데이터베이스 이해

   • ERD 설계: 데이터베이스를 설계할 때 중요한 엔터티, 속성, 관계를 이해해야 합니다. 기본적인 정규화와 관계형 모델링을 학습하세요.
   • 정규화: 1NF, 2NF, 3NF에 대해 이해하고 실제로 이를 적용하는 연습을 해보세요.

3. 실습을 통한 SQL 활용 능력 강화

   • SQL을 이론만으로 학습하지 말고, 실제로 MySQL, PostgreSQL 또는 Oracle 등 DBMS에서 SQL을 실행하며 실습을 진행하세요. 다양한 데이터베이스와 상호작용하면서 문제 해결 능력을 키울 수 있습니다.

4. 성능 최적화 학습

   • SQL 쿼리의 성능을 최적화하는 방법을 공부하세요. 인덱스의 사용, 서브쿼리 최적화, 조인 방식에 따른 성능 차이 등을 이해하고, 실제 환경에서 이를 적용하는 방법을 학습하세요.

5. 이론과 실습 병행

   • 이론 공부와 실습을 병행하면서 문제 해결 능력을 키워야 합니다. SQLD 시험에서는 이론적인 부분뿐만 아니라 실무에서 겪을 수 있는 다양한 시나리오를 다루기 때문에, 기본 문제 풀이와 함께 실전 연습을 충분히 해야 합니다.

공부 방법과 팁

1. 공식 교재와 참고서 활용

   • SQLD 자격증을 준비할 때는 공식 교재를 활용하는 것이 좋습니다. 교재에는 시험에 나오는 기본적인 내용들이 포함되어 있으며, 실제 기출 문제를 통해 시험의 스타일을 익힐 수 있습니다.
   • SQLD 관련 서적이나 온라인 강의도 활용하면 도움이 됩니다.

2. 문제 풀이 및 모의 시험

   • 기출 문제나 모의 시험을 풀어보세요. 실제 시험에 나오는 문제 유형을 파악하고, 시간 관리 능력을 키울 수 있습니다.
   • SQL 문제는 실습을 통해 연습하는 것이 중요하므로, DBMS에 접속하여 쿼리 문제를 풀어보며 실력을 점검하세요.

3. 온라인 커뮤니티와 포럼 활용

   • SQLD 자격증 준비를 하면서 온라인 커뮤니티나 포럼을 활용하여 다른 사람들과 정보 교류를 하는 것도 유익합니다. 실무에서 겪은 다양한 SQL 관련 문제를 해결하고 서로 질문하고 답변하는 과정이 큰 도움이 됩니다.

4. SQL 실습 환경 구축

   • 시험 준비를 할 때는 반드시 DBMS 환경을 구축하여 실습해보세요. MySQL, PostgreSQL, Oracle 등 다양한 DBMS에서 SQL을 실행해보며 각 시스템의 차이점을 이해하고 SQL 실력을 높일 수 있습니다.

결론

SQLD 자격증은 데이터베이스에 대한 기본적인 이해와 SQL 활용 능력을 증명할 수 있는 중요한 자격증입니다. 2025년 SQLD 시험 일정에 맞춰 체계적으로 준비하면 좋은 결과를 얻을 수 있을 것입니다. 기본적인 이론을 탄탄히 하고 실습을 충분히 하며, 문제 해결 능력을 키워 시험에 임하세요. 좋은 결과가 있기를 바랍니다!

PostgreSQL AccessShareLock 이해 및 해제 가이드

AccessShareLock이란?

• Oracle의 TM LOCK과 유사: 데이터를 읽는 동안(SELECT) 테이블 구조 변경을 막기 위해 걸리는 락입니다.
• 성능에 미치는 영향: 일반적으로 성능 저하를 유발하지 않지만, 테이블 구조 변경 작업 시 방해가 될 수 있습니다.
• PostgreSQL에서의 역할: 데이터의 일관성 유지에 기여합니다.

Lock 조회 및 해제

1. Lock 조회:

SELECT  t.relname,
        l.locktype,
        page,
        virtualtransaction,
        pid,
        mode,
        granted
FROM pg_locks l,
     pg_stat_all_tables t
WHERE l.relation = t.relid
ORDER BY relation ASC;

• relname: 락이 걸린 테이블 이름
• locktype: 락 종류 (AccessShareLock 등)
• pid: 프로세스 ID
• mode: 락 모드

2. Lock 해제:

• 단일 프로세스 종료:

  SELECT pg_cancel_backend(PID);

  • 해당 PID의 프로세스만 종료합니다.
• 상위 쿼리까지 종료:

  SELECT pg_terminate_backend(PID) FROM pg_stat_activity;

  • PID와 관련된 모든 상위 쿼리까지 종료합니다.

3. 실행 중인 쿼리 상태 조회:

select * from pg_stat_activity;

주의 사항

• pg_terminate_backend는 강제 종료이므로 데이터 손실 가능성이 있습니다. 신중하게 사용해야 합니다.
• 락 해제 전 반드시 원인을 파악하고 해결하는 것이 좋습니다. 락이 자주 발생하는 경우, 쿼리 최적화나 데이터베이스 설정 변경이 필요할 수 있습니다.

요약

PostgreSQL의 AccessShareLock은 데이터 일관성을 위해 필요한 락입니다. 일반적으로 문제가 되지 않지만, 테이블 구조 변경 등 특정 상황에서 방해가 될 수 있습니다. 위의 SQL 쿼리를 사용하여 Lock을 조회하고 해제할 수 있으며, pg_terminate_backend를 사용할 때는 주의가 필요합니다.

더 자세한 정보는 PostgreSQL 공식 문서를 참고하세요.

핵심:

• AccessShareLock은 SELECT 시 걸리는 락으로, 일반적으로 성능에 영향을 주지 않습니다.
• Lock을 조회하고 해제하는 방법을 알아두면 문제 해결에 도움이 됩니다.
• pg_terminate_backend는 강력한 명령어이므로 신중하게 사용해야 합니다.

array_agg is an aggregate function 에러는 pg_get_functiondef가 aggregate 함수나 window 함수에 대해서 동작하지 않을 때 발생할 수 있습니다. 이 문제를 해결하려면 함수 정의를 얻는 다른 방법을 사용하거나, pg_proc에서 특정 조건을 추가로 설정해줘야 할 수 있습니다.

아래는 이 문제를 해결할 수 있는 개선된 쿼리입니다:

1. Aggregate 및 Window 함수 제외:

PostgreSQL에서는 집계 함수와 일반 함수가 모두 pg_proc에 저장되지만, 집계 함수와 window 함수는 pg_get_functiondef로 정의를 조회할 수 없습니다. 이를 필터링하려면 proisagg와 proiswindow 컬럼을 활용할 수 있습니다.

SELECT
    n.nspname AS schema_name,
    p.proname AS function_name,
    pg_get_functiondef(p.oid) AS function_definition
FROM
    pg_proc p
JOIN
    pg_namespace n ON p.pronamespace = n.oid
WHERE
    NOT p.proisagg -- 집계 함수가 아닌 것
    AND NOT p.proiswindow -- 윈도우 함수가 아닌 것
    AND pg_get_functiondef(p.oid) LIKE '%검색할_문자열%';

2. PostgreSQL 11 이상 (prokind 사용):

PostgreSQL 11 이상에서는 pg_proc에 prokind 컬럼이 추가되어, 함수 유형을 더 쉽게 필터링할 수 있습니다. prokind = 'f'는 일반 함수를 의미합니다.

SELECT
    n.nspname AS schema_name,
    p.proname AS function_name,
    pg_get_functiondef(p.oid) AS function_definition
FROM
    pg_proc p
JOIN
    pg_namespace n ON p.pronamespace = n.oid
WHERE
    p.prokind = 'f' -- 일반 함수만 선택
    AND pg_get_functiondef(p.oid) LIKE '%검색할_문자열%';

3. 특정 스키마에 대해서만 검색 (옵션):

SELECT
    n.nspname AS schema_name,
    p.proname AS function_name,
    pg_get_functiondef(p.oid) AS function_definition
FROM
    pg_proc p
JOIN
    pg_namespace n ON p.pronamespace = n.oid
WHERE
    p.prokind = 'f'
    AND n.nspname = '특정_스키마명'
    AND pg_get_functiondef(p.oid) LIKE '%검색할_문자열%';

위 쿼리 중 하나를 사용하면 pg_get_functiondef에서 발생하는 문제를 방지하고, 함수 정의를 성공적으로 검색할 수 있을 것입니다.

특정 기간 중, CPU 사용율이 높은 쿼리를 조회한다.
CPU 점유율이 높은 쿼리를 찾아서 튜닝하기 위해서 사용한다. 기본적으로 oracle에서 제공하는 view를 활용한다.

사용 테이블

• DBA_HIST_SQLSTAT : SQL Historical Statixtics Information
  DB에서 실행된 SQL에 대한 성능 통계치 view
• DBA_HIST_SNAPSHOT : SnapShot Information
  워크로드 저장소의 스냅샷에 대한 정보 view
• DBA_HIST_SQLTEXT : SQL Text
  워크로드 저장소에 캡쳐된 공유 SQL 커서에 속한 SQL 문의 텍스트 표시.
  이 view는 주로 V$SQL view와 함께 사용됨.

최종 쿼리

  WITH REF_DATE AS (
      /* 대상 기간 지정 */
      SELECT
          TO_DATE('20220401' || ' 000000', 'YYYYMMDD HH24MISS') AS BGN_DE
          , TO_DATE('20230420' || ' 235959', 'YYYYMMDD HH24MISS') AS END_DE
      FROM DUAL
  )
  SELECT
      X.SQL_ID
      , X.CPU_TIME
      , X.EXECUTIONS_DELTA
      , X.CPU_TIME_PER_EXECUTIONS
      , DBMS_LOB.SUBSTR(SUBSTR(D.SQL_TEXT, 1, 200)) AS SQL_TEXT
      , D.SQL_TEXT AS SQL_FULLTEXT
  FROM (
          SELECT
              SQL.DBID
              , SQL.SQL_ID
              , SUM(SQL.CPU_TIME_DELTA) / 1000000 AS CPU_TIME
              , SUM(SQL.EXECUTIONS_DELTA) AS EXECUTIONS_DELTA
              , ROUND((SUM(SQL.CPU_TIME_DELTA) / 1000000) / DECODE(SUM(SQL.EXECUTIONS_DELTA), 0, 1, SUM(SQL.EXECUTIONS_DELTA)) / DECODE(SQL.PX_SERVERS_EXECS_DELTA, 0, 1, SQL.PX_SERVERS_EXECS_DELTA)) AS CPU_TIME_PER_EXECUTIONS
          FROM DBA_HIST_SQLSTAT SQL
              , (
                  SELECT
                      MIN(SNAP_ID) AS START_SNAP_ID
                      , MAX(SNAP_ID) AS END_SNAP_ID
                      , MIN(BEGIN_INTERVAL_TIME) AS BEGIN_INTERVAL_TIME
                      , MAX(END_INTERVAL_TIME) AS END_INTERVAL_TIME
                  FROM DBA_HIST_SNAPSHOT, REF_DATE
                  WHERE BEGIN_INTERVAL_TIME BETWEEN REF_DATE.BGN_DE
                                              AND REF_DATE.END_DE
              ) SNAP
          WHERE SQL.SNAP_ID BETWEEN SNAP.START_SNAP_ID AND SNAP.END_SNAP_ID
          GROUP BY SQL.DBID, SQL.SQL_ID, SQL.PX_SERVERS_EXECS_DELTA
          HAVING SUM(SQL.EXECUTIONS_DELTA) >= 0
          ORDER BY CPU_TIME_PER_EXECUTIONS DESC
  ) X
  INNER JOIN DBA_HIST_SQLTEXT D
      ON D.SQL_ID = X.SQL_ID
      AND D.DBID = X.DBID
  WHERE ROWNUM <= 40
  ;

분리

  SELECT
      SQL.DBID
      , SUM(SQL.CPU_TIME_DELTA) / 1000000 AS CPU_TIME
      , SUM(SQL.EXECUTIONS_DELTA) AS EXECUTIONS_DELTA
      , ROUND((SUM(SQL.CPU_TIME_DELTA) / 1000000) / DECODE(SUM(SQL.EXECUTIONS_DELTA), 0, 1, SUM(SQL.EXECUTIONS_DELTA)) / DECODE(SQL.PX_SERVERS_EXECS_DELTA, 0, 1, SQL.PX_SERVERS_EXECS_DELTA)) AS CPU_TIME_PER_EXECUTIONS
  FROM DBA_HIST_SQLSTAT SQL
      , (
          SELECT
              MIN(SNAP_ID) AS START_SNAP_ID
              , MAX(SNAP_ID) AS END_SNAP_ID
              , MIN(BEGIN_INTERVAL_TIME) AS BEGIN_INTERVAL_TIME
              , MAX(END_INTERVAL_TIME) AS END_INTERVAL_TIME
          FROM DBA_HIST_SNAPSHOT
          WHERE BEGIN_INTERVAL_TIME BETWEEN TO_DATE('20230401' || ' 000000', 'YYYYMMDD HH24MISS')
                                      AND TO_DATE('20230419' || ' 235959', 'YYYYMMDD HH24MISS')
      ) SNAP
  WHERE SQL.SNAP_ID BETWEEN SNAP.START_SNAP_ID AND SNAP.END_SNAP_ID
  GROUP BY SQL.DBID, SQL.SQL_ID, SQL.PX_SERVERS_EXECS_DELTA
  HAVING SUM(SQL.EXECUTIONS_DELTA) >= 0
  ORDER BY CPU_TIME_PER_EXECUTIONS
  ;

  SELECT
      MIN(SNAP_ID) AS START_SNAP_ID
      , MAX(SNAP_ID) AS END_SNAP_ID
      , MIN(BEGIN_INTERVAL_TIME) AS BEGIN_INTERVAL_TIME
      , MAX(END_INTERVAL_TIME) AS END_INTERVAL_TIME
  FROM DBA_HIST_SNAPSHOT
  WHERE BEGIN_INTERVAL_TIME BETWEEN TO_DATE('20230401' || ' 000000', 'YYYYMMDD HH24MISS')
                              AND TO_DATE('20230419' || ' 235959', 'YYYYMMDD HH24MISS')
  ;

GATHER PLAN STATISTICS

오라클 DB의 예상 실행 계획만으로는 성능 개선에 어려움을 겪을 때가 있음. 실제 실행 계획을 보고 문제점을 진단할 수 있어야 함.
gather_plan_statistics 힌트는 Oracle SQL에서 SQL 문의 실제 실행 통계를 수집할 수 있는 기능.
이 힌드를 사용하면 Oracle DB는 SQL 실행 계획의 각 단계에서 처리된 행 수, 사용된 메모리 양 및 각 단계에서 사용된 시간과 같은 자세한 통계를 수집함.

GATHER_PLAN_STATISTICS으로 수집 가능한 정보

• 각 단계에서 수행되는 레코드 수, 시간 및 I/O 통계
• 각 단계에서 사용된 실행 계획
• SQL 문의 최적 실행 계획과 실제 실행 계획의 차이를 나타내는 비교 정보

GATHER PLAN STATISTICS 사용 법

SQL 문에 다음과 같이 간단히 추가하면 됨.

  SELECT /*+ gather_plan_statistics */ column1, column2, ...
  FROM TABLE
  WHERE ...

DBMS_XPLAN.DISPLAY_CURSOR

성능 문제를 진단하고 SQL 문을 최적화하는데 유용함.
/*+ gather_plan_statistics */ SQL을 실행한 후, DBMS_XPLAN.DISPLAY_CURSOR 함수를 사용하여 실행 계획과 관련된 통계를 볼 수 있음.
DISPLAY_CURSOR 함수는 실행 계획과 통계의 자세한 보고서를 반환하므로 SQL 문의 성능을 분석하고 최적화할 부분을 식별하는 데 사용할 수 있음.

• 최적 실행 계획과 실제 실행 계획의 비교
• 각 실행 계획 단계에서 수행된 레코드 수, 시간 및 I/O 통계
• 각 실행 계획 단계에서 사용된 인덱스 및 조인 방법 등의 정보

PREDICATE INFORMATION 섹션에서는 WHERE절과 JOIN 조건에 대한 추가 정보도 제공함

  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT => 'ALLSTATS LAST'));
  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null, null, 'ALLSTATS LAST'));

DISPLAY_CURSOR 매개 변수

• SQL_ID : 실행 계획과 통계를 검색하려는 SQL 문의 SQL_ID
• CURSOR_CHILD_NO : [OPTIONAL] 실행 계획을 검색하려는 SQL 문의 부모 커서 번호, 생략시 첫 번째 커서(부모 커서)가 대상이 됨
• FORMAT : 출력 형식 지원, 여러가지 출력 옵션이 있음.
  FORMAT=>'ALLSTATS LAST'를 사용하면 모든 실행계획 정보와 통계를 검색함.

SQL_ID 찾기

SQL_ID를 가져오기 위해 현재 DB에 접속한 SESSION에서 실행한 SQL 문의 히스토리를 검색하여 정보를 가져옴.

  --------------------------------------------------------------------------------
  -- SQL_ID, CHILD_NUMBER 추출
  --------------------------------------------------------------------------------
  SELECT
    SA.SQL_ID,
    S.CHILD_NUMBER,
    SA.SQL_TEXT,
    SA.MODULE,
    SA.LAST_LOAD_TIME,
    SA.LAST_ACTIVE_TIME,
    SA.PLAN_HASH_VALUE,
    SA.OPTIMIZER_COST,
    SA.FETCHES,
    SA.EXECUTIONS,
    SA.cpu_time,
    SA.ELAPSED_TIME,
    S.DISK_READS,
    S.PARSE_CALLS,
    S.BUFFER_GETS,
    S.ROWS_PROCESSED,
    SA.PARSING_USER_ID,
    SA.PARSING_SCHEMA_ID,
    SA.PARSING_SCHEMA_NAME
  FROM V$SQLAREA SA
    /* V$SQLAREA : 공유 SQL 영역 */
    INNER JOIN V$SESSION SS
      /* V$SESSION : 현재 세션에 대한 정보 */
      ON SA.PARSING_USER_ID = SS.USER#
      AND SA.PARSING_SCHEMA_ID = SS.SCHEMA#
    INNER JOIN V$SQL S
      /* V$SQL : 공유 SQL 영역 내 쿼리에 대한 정보 */
      ON SA.SQL_ID = S.SQL_ID
  WHERE SS.AUDSID = USERENV('SESSIONID')
    /* 현재 세션과 같은 SESSION ID */
    AND SS.SID = USERENV('SID')
    /* 특정 스키마 */
    AND SA.PARSING_SCHEMA_NAME = 'IDLOOK'
    /* 약 15분 이내에 실행한 쿼리만 조회 */ 
    AND SA.LAST_ACTIVE_TIME >= SYSDATE - 0.01
    /* 제외 */
    AND sa.PARSING_SCHEMA_NAME NOT IN ('SYS', 'SYSTEM')
    AND sa.SQL_TEXT NOT LIKE '%DBMS%'
    AND sa.SQL_TEXT NOT LIKE '%V$%'
    /* 실행 모듈이 운영 또는 타 시스템에서 실행한 쿼리라면 제외 */
    AND SA.MODULE NOT IN ('DBMS_SCHEDULER', 'JDBC Thin Client', 'w3wp.exe')
    /* 특정 문자열 제외*/
    AND NOT REGEXP_LIKE(UPPER(SA.SQL_TEXT), 'V\$SQL|PLAN_TABLE|DBMS_XPLAN|EXTRACTVALUE\(|XMLSEQUENCE\(|CURRENT_SCHEMA|DBA_|DBMS_UTILITY|CONSTRAINT')
  ORDER BY SA.LAST_ACTIVE_TIME DESC, SA.SQL_ID, S.CHILD_NUMBER
;

권한 문제

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));
쿼리 실행 시 권한 부족한 경우 DBA 계정으로 로그인하여 권한 부여 작업을 수행하거나 DBA 권한이 있는 사용자에게 권한을 부여해야 함.
DBMS_XPLAN.DISPLAY_CURSOR 함수를 사용하려면 V$SESSION, V$SQL_SESSION, V$SQL(OPTIONAL), V$SQL_PLAN_STATISTICS_ALL 권한이 필요. (해당 권한이 없는 경우 ORA-01031: insufficient privileges 오류가 발생)

작동 조건

• PLAN_STATISTICS 정보는 다음 조건 중 하나를 만족해야 함.
  • STATISTICS_LEVEL PARAMETER 값을 ALL로 변경 한 경우
    ALTER SESSION SET STATISTICS_LEVEL = ALL;
  • _ROWSOURCE_EXECUTION_STATISTICS PARAMETER 값을 TRUE로 변경한 경우
  • GATHER_PLAN_STATISTICS HINT를 사용

조회 결과 분석

• Id, Operation, Name :
  흔히 봐온 플랜 정보, 자원에 대한 접근 순서와 방법을 나타냄. 접근 순서를 변경할 수 있는 힌트 절은 ORDERED, LEADING이 있음.
  또한 접근 방법을 변경할 수 있는 힌트절은 USE_NL, USE_HASH, USE_MERGE가 있음.
• Starts :
  오퍼레이션을 수행한 횟수를 의미한다. Starts * E-Rows 의 값이 A-Rows 값과 비슷하다면, 통계정보의 예측 Row 수와 실제 실행 결과에 따른 실제 Row 수가 유사하다고 함.
  만약 값에 큰 차이가 있다면 통계정보가 실제의 정보를 제대로 반영하지 못했다고 봐야 한다고 함.
  이로 인해 오라클의 Optimizer가 잘못된 실행 계획을 수립할 수도 있음을 염두에 둬야 함.
• E-Rows (Estimated Rows) :
  통계정보에 근거한 예측 Row 수를 의미. 통계정보를 갱신할수록 값이 매번 다를 수 있으며, 대부분의 DB 운영에서는 통계정보를 수시로 갱신하지 않으므로 해당 값에 큰 의미를 둘 필요는 없음.
  하지만 E-Rows 값과 A-Rows 값이 현격하게 차이가 있다면 오라클이 잘못된 실행 계획을 세울 수도 있음을 인지해야 하며 통계정보 생성을 검토해 보아야 함.
• A-Rows (Actual Rows) :
  쿼리 실행 결과에 따른 실제 Row 수를 의미.
  A-Rows 에서 중요한 여러 정보를 추정 할 수 있음.
• A-Time (Actual Elapsed Time) :
  쿼리 실행 결과에 따른 실제 수행 시간을 의미.
  실행 시점의 여러 상황이 늘 가변적이고 또한 메모리에 올라온 Block의 수에 따라서 수행 시간이 달라지므로 해당 값에 큰 의미를 두지 않는게 좋음.
• Buffers (Logical Reads) :
  논리적인 Get Block 수를 의미.
  해당 값은 오라클 옵티마이저가 일한 총량을 의미하므로, 튜닝을 진행할 때 중요한 요소로 보임.

위의 헤더에서 튜닝 시 가장 중요하게 활용되는 부분은 Buffers, A-Rows.
Buffers 값을 통해서 Get Block의 총량을 알 수 있고, A-Rows를 통해 플랜 단계별로 실제 Row 수를 알 수 있음.

DBMS_XPLAN.DISPLAY_CURSOR 조회 결과

  SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR('3YTNQSYC1PXJ8', NULL, 'ADVANCED ALLSTATS ALL -PROJECTION +ROWS +BYTES +PREDICATE'));

• 관련 정보 원문 : https://docs.oracle.com/database/121/ARPLS/d_xplan.htm#ARPLS70132
• 참고
  • http://jmkjb.blogspot.com/2015/06/gatherplanstatistics.html
  • https://m.blog.naver.com/itperson/220840180396
  • https://argolee.tistory.com/8
  • http://gnujava.com/board/article_view.jsp?article_no=1898&menu_cd=30&board_no=17&table_cd=EPAR06&table_no=06
  • https://argolee.tistory.com/108
  • https://chat.openai.com/

SELECT 시에는 꼭 필요한 column 만 불러오기

많은 필드를 불러 올수록 DB는 더 많은 로드를 부담하게 되기 때문에 꼭 필요한 열만 물러오도록 한다.

  -- WORST
  SELECT * FROM TABLE;
  -- BETTER
  SELECT COLUMN1, COLUMN2, COLUMN4, COLUMN8 FROM TABLE;

WHERE절에서 연산을 걸지 않는다.

연산이 들어가게 되면 TABLE FULL SCAN을 하면서 모든 값을 탐색, 계산 한 뒤 조건 충족 여부를 판단하기 때문에 좋지 않다.

  -- WORST
  SELECT COLUMN1, COLUMN4
  FROM TABLE
  WHERE FLOOR(COLUMN4) = 2;
  -- BETTER
  SELECT COLUMN1, COLUMN4
  FROM TABLE
  WHERE COLUMN4 BETWEEN 4 AND 5;

LIKE 조회시 와일드카드 % 는 가급적 뒤에만 붙이자

COLUMN6 LIKE %DF 는 TABLE FULL SCAN을 유발한다.
COLUMN5 IN ('ASDF', 'ERDF'), COLUMN5 = 'ASDF' OR COLUMN5 = 'ERDF' 같은 형태가 낫다.

SELECT DISTINCT, UNION ALL 과 같이 중복을 제거하는 연산은 자제한다

중복을 제거하는 연산은 시간이 많이 걸린다. 불가피하게 사용해야 할 경우 EXISTS, GROUP BY를 활용하는게 낫다.
DISTINCT는 원하는 컬럼에 대해서 중복을 제거하는 것이 아니라 SELECT 해온 모든 ROW에서 중복을 제거하므로 속도가 느려진다.

같은 내용의 조건이라면 GROUP BY 연산의 HAVING 보다는 WHERE 절을 사용하는 것이 좋다

쿼리 실행 순서에서 WHERE 절이 HAVING 절 보다 먼저 실행된다. 따라서 WHERE 절로 미리 데이터를 작게 만들면 GROUP BY 절에서 다뤄야 하는 데이터 크기가 작기 때문에 효율적인 연산이 가능하다.

VIEW VS MVIEW (MATERIALIZED VIEW)

뷰는 질의 할때마다 해당 쿼리를 재 실행하는 것과 같음. 속도가 느림. 다만 데이터는 LIVE 함.
MVIEW는 세팅을 어떻게 하느냐에 따라 질의 할 쿼리를 재사용하여 가져옴. 스냅샷 처럼 이전에 만들어 놓은 엠뷰 테이블에서 데이터를 가져옴.
비용이 많이 들어가고 데이터가 고정적인 경우 엠뷰를 만들어서 사용하는게 좋음. 인덱스도 생성 가능.
리프레시 타임이 많이 드는 경우 데이터가 LIVE하지 않은 문제가 있음.

CHAR VS VARCHAR

4byte 이하는 CHAR, 그 이상은 VARCHAR가 나음.

• CHAR : 고정 길이 문자열
• VARCHAR : 가변 길이 문자열

BLOB VS TEXT

많은 양의 데이터 저장, 디폴트 값 지정 안됨, 문자열 뒷부분 공백 제거 안되는 공통점이 있음.
차이점으로 BLOB은 대소문자를 구분, TEXT는 구분 안함.

PRIMARY KEY, UNIQUE KEY

PK와 Unique Key는 자동으로 인덱스가 생성됨.

쿼리 실행 순서

SUB-QUERY -> MAIN-QUERY

INNER JOIN시 테이블 배치 순서

3개 이상의 테이블을 INNER JOIN을 할 때는 크기가 가장 큰 테이블을 FROM 절에 배치하고 INNER JOIN 절에 남은 테이블을 작은 순서대로 배치하는 것이 좋다.
테이블 한두개차이는 상관 없지만 많은 테이블을 JOIN 할 경우 JOIN의 경우의 수가 생기고 OPTIMIZER가 PLANNING을 하면서 비용이 증가된다.

실행 계획 type이 index라면 한 번 더 살펴보기

EXPLAIN으로 실행 계획을 확인 했을 때 type이 index인 부분이 있다. where 절 조건문에서 사용하는 테이블의 컬럼에 인덱스가 제대로 걸려있지 않기 때문에 TABLE FULL SCAN을 한다.
해당 테이블에 index를 걸고 다시 실행 계획을 INDEX RANGE SCAN으로 변경되었고 실제 실행 속도가 1/4로 줄었다. 아래는 type의 종류로 성능이 낮은 것 부터 높은 순으로 정렬했다.

• ALL : 테이블을 처음부터 끝까지 탐색하여 데이터를 찾음 (TABLE FULL SCAN)
• INDEX : 인덱스를 처음부터 끝까지 탐색하여 데이터를 찾는 방식 (INDEX FULL SCAN)
• RANGE : 특정 범위 내에서 인덱스를 사용하여 원하는 데이터를 추출, 데이터가 방대하지 않다면 준수함. (INDEX RANGE SCAN)
• REF : 조인 할 때 Primary Key 혹은 Unique Key 가 아닌 Key로 매칭 한 경우
• EQ_REF : 조인 할 때 Primary Key로 매칭

-- 락 걸린 테이블 확인
SELECT
    DO.OBJECT_NAME
  , DO.OWNER
  , DO.OBJECT_TYPE
  , DO.OWNER
  , VO.XIDUSN
  , VO.SESSION_ID
  , VO.LOCKED_MODE
FROM
    V$LOCKED_OBJECT VO
  , DBA_OBJECTS DO
WHERE VO.OBJECT_ID = DO.OBJECT_ID
;


-- 해당 테이블에 락이 걸렸는지 확인
SELECT
  A.SID
  , A.SERIAL#
  , B.TYPE
  , C.OBJECT_NAME
FROM
    V$SESSION A
  , V$LOCK B
  , DBA_OBJECTS C
WHERE A.SID = B.SID
  AND B.ID1 = C.OBJECT_ID
  AND B.TYPE='TM'
  AND C.OBJECT_NAME IN (${위 쿼리에서 가져온 OBJECT_NAME});


-- 락 발생 사용자, SQL, OBJECT 조회
SELECT
  DISTINCT X.SESSION_ID
  , A.SERIAL#
  , D.OBJECT_NAME
  , A.MACHINE
  , A.TERMINAL
  , A.PROGRAM
  , B.ADDRESS
  , B.PIECE
  , B.SQL_TEXT
FROM
    V$LOCKED_OBJECT X
  , V$SESSION A
  , V$SQLTEXT B
  , DBA_OBJECTS D
WHERE X.SESSION_ID = A.SID
  AND X.OBJECT_ID = D.OBJECT_ID
  AND A.SQL_ADDRESS = B.ADDRESS 
ORDER BY B.ADDRESS, B.PIECE;


-- 현재 접속자의 SQL 확인 
SELECT
    DISTINCT A.SID
  , A.SERIAL#
  , A.MACHINE
  , A.TERMINAL
  , A.PROGRAM
  , B.ADDRESS
  , B.PIECE
  , B.SQL_TEXT
FROM
    V$SESSION A
  , V$SQLTEXT B
WHERE A.SQL_ADDRESS = B.ADDRESS
ORDER BY A.SID, A.SERIAL#, B.ADDRESS, B.PIECE;


-- 락 세션 죽이기위한 sid 와 serial
SELECT
    A.SID
  , A.SERIAL#
  , A.SID || ', ' || A.SERIAL#
FROM
    V$SESSION A
  , V$LOCK B
  , DBA_OBJECTS C
WHERE A.SID = B.SID
  AND B.ID1 = C.OBJECT_ID
  AND B.TYPE = 'TM'
-- AND C.OBJECT_NAME IN ('${락 걸린 테이블 명}');


-- 해당 세선 kill
SELECT
    DISTINCT X.SESSION_ID
  , A.SERIAL#
  , D.OBJECT_NAME
  , A.MACHINE
  , A.TERMINAL
  , A.PROGRAM
  , A.LOGON_TIME
  , 'ALTER SYSTEM KILL SESSION '''||A.SID||', '||A.SERIAL#||''';'
FROM
    GV$LOCKED_OBJECT X
  , GV$SESSION A
  , DBA_OBJECTS D
WHERE X.SESSION_ID = A.SID
  AND X.OBJECT_ID = D.OBJECT_ID
ORDER BY LOGON_TIME
;


-- 최근 실행된 쿼리 찾기
SELECT 
    SQL_FULLTEXT
  , SQL_ID
  , PARSING_SCHEMA_NAME
  , SERVICE
  , MODULE
  , FIRST_LOAD_TIME
  , LAST_LOAD_TIME
FROM V$SQL
WHERE 1 = 1
  AND PARSING_SCHEMA_NAME = '${스키마 명}'
  AND SERVICE = '${서비스 명}'
ORDER BY LAST_LOAD_TIME DESC
;


-- 최근 실행된 쿼리에 바인팅 변수 찾기
SELECT
    NAME
  , POSITION
  , DATATYPE_STRING
  , VALUE_STRING 
FROM V$SQL_BIND_CAPTURE
WHERE 1=1
  AND SQL_ID = '${위 쿼리에서 가져온 SQL_ID}'
;

SELECT * FROM V$SQL;

SELECT 
  last_active_time
  ,parsing_schema_name
  ,sql_text 
FROM v$sqlarea
WHERE 
  parsing_schema_name <> 'SYS'
  AND parsing_schema_name <> 'SYSMAN'
  AND parsing_schema_name <> 'DBSNMP'
  AND parsing_schema_name <> 'MDSYS'
  AND parsing_schema_name <> 'EXFSYS'
ORDER BY 
  last_active_time DESC;

안타깝게도 어떤 쿼리가 락을 걸었는지는 알기 쉽지 않다.

특히나 Oracle DB S/W update를 주기적으로 하지 않는다면 유지보수가 쉽지 않다.

인덱스 리빌드

DB의 쿼리 수행 속도 저하의 문제 중 하나는 쿼리 튜닝이 문제일 수도 있지만, 인덱스의 밸런스가 깨졌을 경우의 가능성도 있다.
인덱스 밸런스가 깨졌다는게 무슨 의미냐 하면,
DB는 ArrayList와 트리를 합쳐 놓은 구조와 비슷한 B+트리 구조인데, 트리 아래의 데이터들이 무작위로 수정되거나 삭제 되었을 경우 트리 구조가 불균형을 이루게 된다.

이런 경우 인덱스를 재구성 해주면 쿼리 수행 속도를 향상 시킬 수 있다.
(물론 튜닝이 잘 된 쿼리를 짜는게 중요하다.)

오라클의 인덱스 재설정 쿼리는 ALTER INDEX 인덱스명 REBUILD;이고,
인덱스 명을 조회하는 쿼리는 SELECT INDEX_NAME FROM USER_INDEXED;이다.
그리고 인덱스의 데이터 타입이 LOB이라면 인덱스 리빌드는 불가능 하다.

따라서 이 두 쿼리를 조합하고, 조건을 추가하면 리빌드 하는 쿼리는 다음과 같다.

SELECT 'ALTER INDEX ' || INDEX_NAME || ' REBUILD ;'
FROM USER_INDEXES
WHERE INDEX_TYPE != 'LOB'
;

경험상 CUBRID나 TIBERO 도 동일했던 것 같다.

참고 : https://docs.oracle.com/database/121/SPATL/alter-index-rebuild.htm#SPATL1017

DBeaver로 cubrid 연결하기

dbeaver 설정 파일을 연다.
dbeaver를 기본 설정으로 설치했다면 C:\Program Files\DBeaver에 위치한다.

마지막 줄에 다음 설정을 넣어준다.

-Dfile.encoding=utf8

아니 근데 국산 DB 좀 안 썼으면 좋겠다.