My Dev Blog

접속자가 몰려 서버가 느려지는 상황에서 주로 발생할 수 있는 문제 중 하나가 데이터베이스에 Lock이 걸리거나,

쿼리가 비효율적으로 실행되는 경우이다. 아래에  Lock 관련 문제를 확인하고 해결할 수 있는 쿼리를 정리하였다.

<MySQL>

-- 현재 활성화된 프로세스와 상태 확인
SHOW PROCESSLIST;

실행 중인 쿼리와 그 상태를 확인할 수 있다. 여기에서 State 값이 Locked 상태로 표시된 세션이

Lock을 유발하고 있을 가능성이 크다.

-- 특정 세션 종료
KILL [SESSION_ID];

Lock을 유발한 세션을 종료하기 위해 KILL 명령어를 사용한다.

[SESSION_ID] 자리에 SHOW PROCESSLIST; 쿼리에서 조회된 Id 값을 적는다.

<Oracle>

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

OBJECT_NAME이 테이블명

-- Lock이 걸린 테이블의 SID와 SERIAL# 조회(테이블별 확인)
SELECT 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 = '테이블명';

v$ 뷰는 관리자 권한 필요

-- 조회된 SID와 SERIAL# 값으로 세션 종료
ALTER SYSTEM KILL SESSION '[SID], [SERIAL#]';

<MySQL>

-- 총 허용 가능한 커넥션 수
SHOW VARIABLES LIKE 'max_connections';

-- 현재 커넥션 수
SHOW STATUS LIKE 'Threads_connected';

<Oracle>

-- 총 허용 가능한 커넥션 수
SELECT VALUE AS max_connections
FROM v$parameter
WHERE NAME = 'processes';

-- 현재 커넥션 수
SELECT COUNT(*) AS current_connections
FROM v$session
WHERE status = 'ACTIVE';

v$ 뷰는 관리자 권한 필요

<Oracle>

DEFINE EX_CD = '1200'; -- 전시코드
DEFINE EX_YEAR = '2024'; -- 전시년도

INSERT INTO TEX_YYEX_CRG_ETC (SITE_CD ,EX_CD ,EX_YEAR ,USER_SEQ ,INSERT_ID ,INSERT_IP) VALUES ('100', &EX_CD, &EX_YEAR, '2453', '2023014', '1.1.1.1');
INSERT INTO TEX_YYEX_CRG_ETC (SITE_CD ,EX_CD ,EX_YEAR ,USER_SEQ ,INSERT_ID ,INSERT_IP) VALUES ('100', &EX_CD, &EX_YEAR, '2454', '2023014', '1.1.1.1');
INSERT INTO TEX_YYEX_CRG_ETC (SITE_CD ,EX_CD ,EX_YEAR ,USER_SEQ ,INSERT_ID ,INSERT_IP) VALUES ('100', &EX_CD, &EX_YEAR, '2455', '2023014', '1.1.1.1');
INSERT INTO TEX_YYEX_CRG_ETC (SITE_CD ,EX_CD ,EX_YEAR ,USER_SEQ ,INSERT_ID ,INSERT_IP) VALUES ('100', &EX_CD, &EX_YEAR, '2456', '2023014', '1.1.1.1');

SELECT EX_CD FROM TEX_YYEX_CD WHERE SITE_CD = '100' AND EX_YEAR = '2024'; -- 전시코드 '1200' 포함 2024년 등록된 모든 전시코드 조회

DECLARE
    CURSOR EX_CD_CURSOR IS -- TEX_YYEX_CD 테이블에서 SITE_CD가 '100'이고 EX_YEAR이 '2024'인 레코드를 조회하는 커서 EX_CD_CURSOR를 선언
        SELECT EX_CD
          FROM TEX_YYEX_CD
         WHERE SITE_CD = '100'
           AND EX_YEAR = '2024';

    V_EX_CD TEX_YYEX_CD.EX_CD%TYPE; -- 변수 V_EX_CD의 데이터 타입을 TEX_YYEX_CD 테이블의 EX_CD 컬럼과 동일하게 설정
BEGIN
    FOR EX_CD_RECORD IN EX_CD_CURSOR LOOP -- EX_CD_CURSOR 커서에서 조회된 EX_CD 값을 하나씩 반복하여 가져와 V_EX_CD 변수에 넣기
        V_EX_CD := EX_CD_RECORD.EX_CD;

        INSERT INTO TEX_YYEX_CRG_ETC (SITE_CD, EX_CD, EX_YEAR, USER_SEQ, INSERT_ID, INSERT_IP) VALUES ('100', V_EX_CD, '2024', '2453', '2023014', '1.1.1.1'); -- V_EX_CD 변수 사용
        INSERT INTO TEX_YYEX_CRG_ETC (SITE_CD, EX_CD, EX_YEAR, USER_SEQ, INSERT_ID, INSERT_IP) VALUES ('100', V_EX_CD, '2024', '2454', '2023014', '1.1.1.1'); -- V_EX_CD 변수 사용
        INSERT INTO TEX_YYEX_CRG_ETC (SITE_CD, EX_CD, EX_YEAR, USER_SEQ, INSERT_ID, INSERT_IP) VALUES ('100', V_EX_CD, '2024', '2455', '2023014', '1.1.1.1'); -- V_EX_CD 변수 사용
        INSERT INTO TEX_YYEX_CRG_ETC (SITE_CD, EX_CD, EX_YEAR, USER_SEQ, INSERT_ID, INSERT_IP) VALUES ('100', V_EX_CD, '2024', '2456', '2023014', '1.1.1.1'); -- V_EX_CD 변수 사용
    END LOOP;
END;

<Oracle>

WITH RANKED_ATTENDANCE AS (
	SELECT CUST_CD
	     , EX_YEAR
	     , ACT_EXHBT_YN
	     , ROW_NUMBER() OVER (PARTITION BY CUST_CD ORDER BY EX_YEAR DESC) AS RN
	  FROM TEX_YYEX_CUST_INFO
	 WHERE SITE_CD = '100'
	   AND EX_CD = '1000'
	   AND ACT_EXHBT_YN = 1
	 ORDER BY EX_YEAR DESC
),

RANKED_ATTENDANCE_SUB AS (
	SELECT A.CUST_CD
	     , B.COMP_NM
	     , A.EX_YEAR
	     , A.ACT_EXHBT_YN
	     , A.RN
	     , A.EX_YEAR + A.RN AS GROUP_YEAR
	  FROM RANKED_ATTENDANCE A
	     , TAC_CUST_INFO B
	 WHERE A.EX_YEAR + A.RN = TO_CHAR(SYSDATE, 'YYYY') + 1
	   AND A.EX_YEAR > TO_CHAR(SYSDATE, 'YYYY') - 5
	   AND A.CUST_CD = B.CUST_CD
	 ORDER BY A.CUST_CD, A.EX_YEAR
)

SELECT COMP_NM, MAX(RN) AS MAX_CNT
  FROM RANKED_ATTENDANCE_SUB
 GROUP BY COMP_NM

전시 코드 1000 전시회

작년까지 실제 참가 여부가 Y인 업체들의 과거 연속 참가 최대 카운트 조회

(작년 참가를 포함하여 최대 5년 연속 참가까지만 확인)

<MySQL>

-- MySQL의 경우 시간 비교를 위한 시간컬럼명이 필수로 존재해야 조회 가능
SELECT * FROM 테이블명 WHERE 시간컬럼명 >= DATE_ADD(NOW(), INTERVAL -10 SECOND); -- 10초 전 해당 테이블의 모든 데이터를 조회
SELECT * FROM 테이블명 WHERE 시간컬럼명 >= DATE_ADD(NOW(), INTERVAL -10 MINUTE); -- 10분 전 해당 테이블의 모든 데이터를 조회
SELECT * FROM 테이블명 WHERE 시간컬럼명 >= DATE_ADD(NOW(), INTERVAL -10 HOUR); -- 10시간 전 해당 테이블의 모든 데이터를 조회
SELECT * FROM 테이블명 WHERE 시간컬럼명 >= DATE_ADD(NOW(), INTERVAL -10 DAY); -- 10일 전 해당 테이블의 모든 데이터를 조회
-- WHERE 조건 추가 가능

<Oracle>

SELECT * FROM 테이블명 AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '10' SECOND) WHERE 1 = 1; -- 10초 전 해당 테이블의 모든 데이터를 조회
SELECT * FROM 테이블명 AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '10' MINUTE) WHERE 1 = 1; -- 10분 전 해당 테이블의 모든 데이터를 조회
SELECT * FROM 테이블명 AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '10' HOUR) WHERE 1 = 1; -- 10시간 전 해당 테이블의 모든 데이터를 조회
SELECT * FROM 테이블명 AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '10' DAY) WHERE 1 = 1; -- 10일 전 해당 테이블의 모든 데이터를 조회
-- WHERE 조건 추가 가능

<MySQL>

SHOW CREATE FUNCTION 함수명;

SHOW CREATE PROCEDURE 프로시저명;

SHOW CREATE TRIGGER 트리거명;

SHOW CREATE SEQUENCE 시퀀스명;

SHOW CREATE VIEW 뷰명;

<Oracle>

SELECT DBMS_METADATA.GET_DDL('FUNCTION', '함수명') AS FUNCTION_SRC
FROM DUAL;

SELECT DBMS_METADATA.GET_DDL('PROCEDURE', '프로시저명') AS FUNCTION_SRC
FROM DUAL;

SELECT DBMS_METADATA.GET_DDL('TRIGGER', '트리거명') AS FUNCTION_SRC
FROM DUAL;

SELECT DBMS_METADATA.GET_DDL('SEQUENCE', '시퀀스명') AS FUNCTION_SRC
FROM DUAL;

SELECT DBMS_METADATA.GET_DDL('VIEW', '뷰명') AS FUNCTION_SRC
FROM DUAL;

컬럼1, 컬럼2가 PK일 때

<MySQL>

INSERT INTO 테이블명
(
    컬럼1
  , 컬럼2
  , 컬럼3
  , 컬럼4
  , 컬럼5
)
VALUES
(
    값1
  , 값2
  , 값3
  , 값4
  , 값5
)
ON DUPLICATE KEY UPDATE
(
    컬럼3 = 값3
  , 컬럼4 = 값4
  , 컬럼5 = 값5
);

<Oracle>

MERGE INTO 테이블명
USING DUAL ON (컬럼1 = 값1 AND 컬럼2 = 값2)
WHEN MATCHED THEN
UPDATE SET
(
    컬럼3 = 값3
  , 컬럼4 = 값4
  , 컬럼5 = 값5
)
WHEN NOT MATCHED THEN
INSERT
(
    컬럼1
  , 컬럼2
  , 컬럼3
  , 컬럼4
  , 컬럼5
)
VALUES
(
    값1
  , 값2
  , 값3
  , 값4
  , 값5
);

Function : 매개변수를 받아 특정 계산(작업)을 수행하고 결과를 반환하는 기능

Procedure : 특정 작업을 위한 쿼리들의 블록(함수와 거의 비슷)
장점 : 1. 하나의 요청으로 여러 SQL문을 실행(네트워크 부하를 줄일 수 있음)
           2. 네트워크 소요 시간을 줄여 성능 개선
           3. 여러 어플리케이션과 공유 가능(API처럼 제공 가능)
           4. 기능 변경이 편함
단점 : 1. 문자나 숫자열 연산에 사용하면 오히려 C, Java보다 느린 성능을 보일 수 있음
           2. 유지보수가 어려움(프로시져가 앱의 어디에 사용되는지 확인 어려움)

Trigger : 사전적 뜻은 총의 방아쇠로 총의 방아쇠를 당기는 것과 같이 어떤 이벤트의 자동 실행

View : 하나 이상의 테이블에서 원하는 모든 데이터를 선택하여 간단하게 나타낸 것
장점 : 1. 각 사용자에 따라 알맞게 데이터를 보여줄 수 있음
           2. 자주 쓰이는 쿼리문을 간단하게 사용하기 위해 사용
           3. 뷰에 나타나지 않는 데이터를 제어하여 데이터 보안이 가능
단점 : 1. 뷰의 정의를 변경할 수 없음
           2. INSERT문은 원본 테이블에서 실행해야 하며, PRIMARY KEY, NOT NULL 등의 제약사항이 위배되는 경우

              삽입이 불가한 등 많은 제약이 따름
           3. 독자적인 인덱스를 가질 수 없음

Package : 패키지는 프로시저 또는 함수 등 서브 프로그램을 하나로 묶어놓은 객체
장점 : 1. 모듈화(함수와 프로시저 등 서브 프로그램을 한곳에 모아서 사용할 수 있으므로 관리 및 사용하기가 수월)
           2. 캡슐화(선언부만 외부에 공개되고, 본문은 감춰져 있기 때문에 접근을 제어하여 오용을 방지)
           3. 성능(패키지의 서브 프로그램 호출 시 패키지 전체가 메모리에 올라가 다른 서브 프로그램 호출 시 훨씬 나은

               성능을 보임)

Index : 테이블의 검색 속도를 향상시키는 자료구조

Synonyms : 사전적 뜻은 동의어이다. 데이터베이스 객체의 NickName

Cursor : 특정 SQL 문장을 처리한 결과를 담고 있는 영역을 가리키는 일종의 포인터