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 좀 안 썼으면 좋겠다.

계층형 쿼리

개발을 하다보면 메뉴구성, 조직도 등 같은 테이블 내에서 계층적으로 데이터를 처리해야 하는 경우가 많다.
자주 사용 되기도 하고, 이번 프로젝트에서 이 부분에 대한 삽질을 많이 했기 때문에 정리를 해본다.
계층형(hirarchical) 구조는 계급적, 수직적인 관계로 2진 트리 관계라고 할 수 있다.
실습은 Oracle emp 를 이용하며 설치 여건이 안되므로 livesql를 이용한다.
사용법은 여기서 보면 된다.

계층 구조와 용어

(계층 구조와 용어 이미지 만들어 올리기.)

• 노드 (node) : 테이블 상의 각각의 row를 노드라고 한다.
• 루트 (root) : 트리 구조의 최상위에 있는 노드를 의미한다.
• 부모 (parent) : 부모노드. 트리 구조에서 상위에 있는 노드를 부모라고 한다.
• 자식 (child) : 자식노드. 트리 구조에서 하위에 있는 노드를 자식이라고 한다.
• 리프 (leaf) : 리프노드 혹은 말단노드. 하위에 연결된 노드가 없는, 자식이 없는 노드이다.
• 레벨 (level) : 트리구조에서 각각의 계층을 의미한다. 루트부터 순차적으로 1씩 올라간다.

SELF JOIN

테이블 개수가 1개이며 각 ROW가 상위 코드로 부모 자식 관계로 연결 되어 셀프 조인을 사용한다.
최상단 노드의 경우 상위 코드가 NULL 이므로 외부 조인도 사용한다.

SELECT
    C.ENAME
    , C.EMPNO
    , P.ENAME AS MGR_NN
FROM
    SCOTT.EMP C
INNER JOIN
    SCOTT.EMP P
ON
    C.MGR = P.EMPNO(+)

결과

ENAME   EMPNO  MGR_NN
- - - - - - - - - - - -
BLAKE   7698    KING
CLARK   7782    KING
JONES   7566    KING
ALLEN   7499    BLAKE
WARD    7521    BLAKE
MARTIN  7654    BLAKE
TURNER  7844    BLAKE
JAMES   7900    BLAKE
MILLER  7934    CLARK
SCOTT   7788    JONES
FORD    7902    JONES
ADAMS   7876    SCOTT
SMITH   7369    FORD
KING    7839     - 

동일한 테이블 EMP 를 각각 C, P 로별칭을 주어 셀프 조인 하였고,
C가 하위 ROW, P가 상위 ROW임을 알 수 있다.
계층적인 결과가 나왔지만, 계층형 구조(TREE)와는 다른 결과이다.

START WITH & CONNECT BY

START WITH ... CONNECT BY 절은 계층형 정보를 표현하기 위한 목적으로 나온 조건이다.

문법

SELECT
    [컬럼 ...]
FROM
    테이블
START WITH [최상위 조건]
CONNECT BY [NOCYCLE] [PRIOR] [계층 구조 조건];

예제

SELECT
    C.ENAME
    , C.EMPNO
    , P.ENAME AS MGR_NN
FROM
    SCOTT.EMP C
INNER JOIN
    SCOTT.EMP P
ON
    C.MGR = P.EMPNO(+)
START WITH C.MGR IS NULL
CONNECT BY PRIOR C.EMPNO = C.MGR

결과

ENAME   EMPNO  MGR_NN
- - - - - - - - - - - -
KING    7839     - 
JONES   7566    KING
SCOTT   7788    JONES
ADAMS   7876    SCOTT
FORD    7902    JONES
SMITH   7369    FORD
BLAKE   7698    KING
ALLEN   7499    BLAKE
WARD    7521    BLAKE
MARTIN  7654    BLAKE
TURNER  7844    BLAKE
JAMES   7900    BLAKE
CLARK   7782    KING
MILLER  7934    CLARK

PRIOR 키워드는 이전 결과의 컬럼을 참조하는 키워드로 현재 행의 MGR을 이전 행의 ENPNO와 연결한다는 의미이다.

LEVEL

계층 레벨(depth level)을 나타낸다.

SELECT
    C.ENAME
    , C.EMPNO
    , P.ENAME AS MGR_NN
    , LEVEL
FROM
    SCOTT.EMP C
INNER JOIN
    SCOTT.EMP P
ON
    C.MGR = P.EMPNO(+)
START WITH C.MGR IS NULL
CONNECT BY PRIOR C.EMPNO = C.MGR;

ENAME  EMPNO  MGR_NN  LEVEL
- - - - - - - - - - - - - - -
KING    7839     -      1
JONES   7566    KING    2
SCOTT   7788    JONES   3
ADAMS   7876    SCOTT   4
FORD    7902    JONES   3
SMITH   7369    FORD    4
BLAKE   7698    KING    2
ALLEN   7499    BLAKE   3
WARD    7521    BLAKE   3
MARTIN  7654    BLAKE   3
TURNER  7844    BLAKE   3
JAMES   7900    BLAKE   3
CLARK   7782    KING    2
MILLER  7934    CLARK   3

레벨별로 들여쓰기를 하면 쿼리 결과 볼때 편하다.

SELECT
    LPAD( ' ', 2 * (LEVEL - 1 ) ) || C.ENAME AS ENAME
    , C.EMPNO
    , P.ENAME AS MGR_NN
    , LEVEL
FROM
    SCOTT.EMP C
INNER JOIN
    SCOTT.EMP P
ON
    C.MGR = P.EMPNO(+)
START WITH C.MGR IS NULL
CONNECT BY PRIOR C.EMPNO = C.MGR;

ENAME       EMPNO  MGR_NN  LEVEL
- - - - - - - - - - - - - - - - -
KING         7839   -        1
  JONES      7566  KING      2
    SCOTT    7788  JONES     3
      ADAMS  7876  SCOTT     4
    FORD     7902  JONES     3
      SMITH  7369  FORD      4
  BLAKE      7698  KING      2
    ALLEN    7499  BLAKE     3
    WARD     7521  BLAKE     3
    MARTIN   7654  BLAKE     3
    TURNER   7844  BLAKE     3
    JAMES    7900  BLAKE     3
  CLARK      7782  KING      2
    MILLER   7934  CLARK     3

ORDER SIBLINGS BY

계층형 쿼리에서 ORDER BY 절을 사용하면 계층의 상관 관계가가 유지되지 않은체 정렬이 되어 버린다.
이 문제로 한참 삽질을 했었는데, ORDER SIBLINGS BY를 사용하면 계층 구조를 유지하면서 정렬을 할 수 있다.

일반 ORDER BY를 사용했을 경우

SELECT
    LPAD( ' ', 2 * (LEVEL - 1 ) ) || C.ENAME AS ENAME
    , C.EMPNO
    , P.ENAME AS MGR_NN
FROM
    SCOTT.EMP C
INNER JOIN
    SCOTT.EMP P
ON
    C.MGR = P.EMPNO(+)
START WITH C.MGR IS NULL
CONNECT BY NOCYCLE PRIOR C.EMPNO = C.MGR
ORDER BY EMPNO;

ENAME         EMPNO  MGR_NN
- - - - - - - - - - - - - - -
      SMITH   7369    FORD
    ALLEN     7499    BLAKE
    WARD      7521    BLAKE
  JONES       7566    KING
    MARTIN    7654    BLAKE
  BLAKE       7698    KING
  CLARK       7782    KING
    SCOTT     7788    JONES
KING          7839     - 
    TURNER    7844    BLAKE
      ADAMS   7876    SCOTT
    JAMES     7900    BLAKE
    FORD      7902    JONES
    MILLER    7934    CLARK

ORDER SIBLINGS BY를 할 경우

SELECT
    C.ENAME
    , C.EMPNO
    , P.ENAME AS MGR_NN
FROM
    SCOTT.EMP C
INNER JOIN
    SCOTT.EMP P
ON
    C.MGR = P.EMPNO(+)
START WITH C.MGR IS NULL
CONNECT BY NOCYCLE PRIOR C.EMPNO = C.MGR
ORDER SIBLINGS BY EMPNO;

ENAME         EMPNO  MGR_NN
- - - - - - - - - - - - - - -
KING          7839   - 
  JONES       7566  KING
    SCOTT     7788  JONES
      ADAMS   7876  SCOTT
    FORD      7902  JONES
      SMITH   7369  FORD
  BLAKE       7698  KING
    ALLEN     7499  BLAKE
    WARD      7521  BLAKE
    MARTIN    7654  BLAKE
    TURNER    7844  BLAKE
    JAMES     7900  BLAKE
  CLARK       7782  KING
    MILLER    7934  CLARK

CONNECT_BY_ISLEAF

계층 구조에서 ROW의 최하위 레벨 여부의 반환이다.
해당 ROW가 말단 노드라면 1 아니라면 0을 반환한다.

SELECT
    LPAD( ' ', 2 * (LEVEL - 1 ) ) || C.ENAME AS ENAME
    , C.EMPNO
    , P.ENAME AS MGR_NN
    , CONNECT_BY_ISLEAF LEAF
FROM
    SCOTT.EMP C
INNER JOIN
    SCOTT.EMP P
ON
    C.MGR = P.EMPNO(+)
START WITH C.MGR IS NULL
CONNECT BY NOCYCLE PRIOR C.EMPNO = C.MGR
ORDER SIBLINGS BY EMPNO;

ENAME        EMPNO  MGR_NN  LEAF
- - - - - - - - - - - - - - - - -
KING          7839   -        0
  JONES       7566  KING      0
    SCOTT     7788  JONES     0
      ADAMS   7876  SCOTT     1
    FORD      7902  JONES     0
      SMITH   7369  FORD      1
  BLAKE       7698  KING      0
    ALLEN     7499  BLAKE     1
    WARD      7521  BLAKE     1
    MARTIN    7654  BLAKE     1
    TURNER    7844  BLAKE     1
    JAMES     7900  BLAKE     1
  CLARK       7782  KING      0
    MILLER    7934  CLARK     1

CONNECT_BY_ROOT

계층구조 쿼리에서 LEVEL이 0인 최상위 로우의 정보를 얻어 올 수 있다.

SELECT
    LPAD( ' ', 2 * (LEVEL - 1 ) ) || C.ENAME AS ENAME
    , C.EMPNO
    , P.ENAME AS MGR_NN
    , CONNECT_BY_ROOT C.ENAME ROOTNANME
FROM
    SCOTT.EMP C
INNER JOIN
    SCOTT.EMP P
ON
    C.MGR = P.EMPNO(+)
START WITH C.MGR IS NULL
CONNECT BY NOCYCLE PRIOR C.EMPNO = C.MGR
ORDER SIBLINGS BY EMPNO;

ENAME        EMPNO  MGR_NN  ROOTNANME
- - - - - - - - - - - - - - - - - - - -
KING          7839   -        KING
  JONES       7566  KING      KING
    SCOTT     7788  JONES     KING
      ADAMS   7876  SCOTT     KING
    FORD      7902  JONES     KING
      SMITH   7369  FORD      KING
  BLAKE       7698  KING      KING
    ALLEN     7499  BLAKE     KING
    WARD      7521  BLAKE     KING
    MARTIN    7654  BLAKE     KING
    TURNER    7844  BLAKE     KING
    JAMES     7900  BLAKE     KING
  CLARK       7782  KING      KING
    MILLER    7934  CLARK     KING

SYS_CONNECT_BY_PATH

계층 구조에서 ROOT노드부터 현제 노드까지의 PATH 정보를 가져올 수 있다.

SELECT
    LPAD( ' ', 2 * (LEVEL - 1 ) ) || C.ENAME AS ENAME
    , C.EMPNO
    , P.ENAME AS MGR_NN
    , SYS_CONNECT_BY_PATH( C.ENAME, '/') PATH
FROM
    SCOTT.EMP C
INNER JOIN
    SCOTT.EMP P
ON
    C.MGR = P.EMPNO(+)
START WITH C.MGR IS NULL
CONNECT BY NOCYCLE PRIOR C.EMPNO = C.MGR
ORDER SIBLINGS BY EMPNO;

ENAME         EMPNO  MGR_NN  PATH
- - - - - - - - - - - - - - - - - - - - - - - - - - - -
KING          7839     -      /KING
  JONES       7566    KING    /KING/JONES
    SCOTT     7788    JONES   /KING/JONES/SCOTT
      ADAMS   7876    SCOTT   /KING/JONES/SCOTT/ADAMS
    FORD      7902    JONES   /KING/JONES/FORD
      SMITH   7369    FORD    /KING/JONES/FORD/SMITH
  BLAKE       7698    KING    /KING/BLAKE
    ALLEN     7499    BLAKE   /KING/BLAKE/ALLEN
    WARD      7521    BLAKE   /KING/BLAKE/WARD
    MARTIN    7654    BLAKE   /KING/BLAKE/MARTIN
    TURNER    7844    BLAKE   /KING/BLAKE/TURNER
    JAMES     7900    BLAKE   /KING/BLAKE/JAMES
  CLARK       7782    KING    /KING/CLARK
    MILLER    7934    CLARK   /KING/CLARK/MILLER

MongoDB 설치하기

NoSQL 을 사용 해보려고 했는데 구글링으로 나오는 방법으론 MacOS Catalina 버전부터는 root 디렉토리가 Read Only 로 바뀌면서 설치 할 수 없게 됬음.
MongoDB의 공식 설명으로 Catalina에서 MongoDB를 설치하려면 다음과 같은 순서로 Terminal 명령을 내리면 됨.
(선수 조건으로 brew가 설치되어 있어야 함.)

Install

    sudo chown -R $(whoami) $(brew --prefix)/*

    brew tap mongodb/brew

    brew install mongodb-community@4.2

세 줄이면 설치가 되는데.. 한참 헤매고 있었음.
뭔가 설치가 안될 땐 공식 문서를 확인 하도록 하자.

구성 파일

설정 파일 /usr/local/etc/mongod.conf
log 파일 /usr/local/var/log/mongodb
저장 경로 /usr/local/var/monogb

(추가) Run MongoDB Community Edition

Run

    brew services start mongodb-community@4.2

Stop

    brew serveced stop mongodb-community@4.2

컴퓨터 실행시 MongoDB 자동 실행 설정

    mongod --config /usr/local/etc/mongd.conf --fork

자동 실행을 중지 시키려면 mongod 에 연결해서 shutdown을 실행하라는데 한두번 테스트 하고 지울거니 패스.
설치하면서 생성된 /usr/local/etc/mongod.conf 파일로 구성 옵션을 설정할 수 있다고 함.

MongoDB가 실행중인지 확인 하려면

    PS AUX | grep -v grep | grep mongod

실행중인 MongoDB에 접속, Mongo Shell을 실행하려면

    mongo