SQL 성능 관리 프로그램

SQL 성능 관리 프로그램

SQL 인스펙션

Oracle 19c SQL 실행 계획 저장 및 관리 프로그램

실행 계획 저장용 테이블 생성

-- 실행 계획 히스토리 테이블
CREATE TABLE sql_plan_history (
    plan_id NUMBER GENERATED ALWAYS AS IDENTITY,
    statement_id VARCHAR2(100),
    sql_text CLOB,
    plan_hash_value NUMBER,
    execution_time TIMESTAMP DEFAULT SYSTIMESTAMP,
    username VARCHAR2(128) DEFAULT USER,
    module VARCHAR2(64),
    action VARCHAR2(64),
    optimizer_cost NUMBER,
    cardinality NUMBER,
    bytes_size NUMBER,
    cpu_cost NUMBER,
    io_cost NUMBER,
    elapsed_seconds NUMBER,
    rows_processed NUMBER,
    execution_type VARCHAR2(20), -- EXPLAIN, ACTUAL
    notes VARCHAR2(4000),
    CONSTRAINT pk_sql_plan_history PRIMARY KEY (plan_id)
);

-- 실행 계획 상세 정보 테이블
CREATE TABLE sql_plan_details (
    detail_id NUMBER GENERATED ALWAYS AS IDENTITY,
    plan_id NUMBER,
    operation VARCHAR2(128),
    options VARCHAR2(128),
    object_name VARCHAR2(128),
    object_type VARCHAR2(30),
    optimizer VARCHAR2(255),
    position NUMBER,
    cost NUMBER,
    cardinality NUMBER,
    bytes_size NUMBER,
    partition_start VARCHAR2(255),
    partition_stop VARCHAR2(255),
    cpu_cost NUMBER,
    io_cost NUMBER,
    temp_space NUMBER,
    access_predicates VARCHAR2(4000),
    filter_predicates VARCHAR2(4000),
    projection VARCHAR2(4000),
    plan_step NUMBER,
    parent_step NUMBER,
    depth NUMBER,
    CONSTRAINT pk_sql_plan_details PRIMARY KEY (detail_id),
    CONSTRAINT fk_plan_details FOREIGN KEY (plan_id) 
        REFERENCES sql_plan_history(plan_id) ON DELETE CASCADE
);

-- 인덱스 생성
CREATE INDEX idx_plan_history_stmt ON sql_plan_history(statement_id);
CREATE INDEX idx_plan_history_hash ON sql_plan_history(plan_hash_value);
CREATE INDEX idx_plan_history_time ON sql_plan_history(execution_time);
CREATE INDEX idx_plan_details_plan ON sql_plan_details(plan_id);

실행 계획 저장 프로시저

CREATE OR REPLACE PROCEDURE save_sql_plan(
    p_sql_text IN CLOB,
    p_statement_id IN VARCHAR2 DEFAULT NULL,
    p_notes IN VARCHAR2 DEFAULT NULL,
    p_plan_id OUT NUMBER
) AS
    v_statement_id VARCHAR2(100);
    v_plan_hash_value NUMBER;
    v_plan_id NUMBER;
    v_sql_id VARCHAR2(13);
BEGIN
    -- Statement ID 생성 (제공되지 않은 경우)
    IF p_statement_id IS NULL THEN
        v_statement_id := 'PLAN_' || TO_CHAR(SYSTIMESTAMP, 'YYYYMMDDHH24MISSFF');
    ELSE
        v_statement_id := p_statement_id;
    END IF;
    
    -- EXPLAIN PLAN 실행
    EXECUTE IMMEDIATE 'EXPLAIN PLAN SET STATEMENT_ID = ''' || v_statement_id || 
                      ''' FOR ' || p_sql_text;
    
    -- Plan Hash Value 조회
    SELECT MAX(plan_hash_value)
    INTO v_plan_hash_value
    FROM plan_table
    WHERE statement_id = v_statement_id;
    
    -- 히스토리 테이블에 저장
    INSERT INTO sql_plan_history (
        statement_id,
        sql_text,
        plan_hash_value,
        execution_time,
        username,
        optimizer_cost,
        cardinality,
        bytes_size,
        cpu_cost,
        io_cost,
        execution_type,
        notes
    )
    SELECT 
        v_statement_id,
        p_sql_text,
        plan_hash_value,
        SYSTIMESTAMP,
        USER,
        MAX(cost),
        MAX(cardinality),
        MAX(bytes),
        MAX(cpu_cost),
        MAX(io_cost),
        'EXPLAIN',
        p_notes
    FROM plan_table
    WHERE statement_id = v_statement_id
    RETURNING plan_id INTO v_plan_id;
    
    -- 상세 정보 저장
    INSERT INTO sql_plan_details (
        plan_id,
        operation,
        options,
        object_name,
        object_type,
        optimizer,
        position,
        cost,
        cardinality,
        bytes_size,
        partition_start,
        partition_stop,
        cpu_cost,
        io_cost,
        temp_space,
        access_predicates,
        filter_predicates,
        projection,
        plan_step,
        parent_step,
        depth
    )
    SELECT 
        v_plan_id,
        operation,
        options,
        object_name,
        object_type,
        optimizer,
        position,
        cost,
        cardinality,
        bytes,
        partition_start,
        partition_stop,
        cpu_cost,
        io_cost,
        temp_space,
        access_predicates,
        filter_predicates,
        projection,
        id,
        parent_id,
        depth
    FROM plan_table
    WHERE statement_id = v_statement_id
    ORDER BY id;
    
    -- PLAN_TABLE 정리
    DELETE FROM plan_table WHERE statement_id = v_statement_id;
    
    COMMIT;
    
    p_plan_id := v_plan_id;
    
    DBMS_OUTPUT.PUT_LINE('실행 계획이 저장되었습니다. Plan ID: ' || v_plan_id);
    DBMS_OUTPUT.PUT_LINE('Statement ID: ' || v_statement_id);
    
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('오류 발생: ' || SQLERRM);
        RAISE;
END save_sql_plan;
/

실행 계획 조회 프로시저

CREATE OR REPLACE PROCEDURE display_sql_plan(
    p_plan_id IN NUMBER
) AS
    v_sql_text CLOB;
    v_statement_id VARCHAR2(100);
BEGIN
    -- SQL 텍스트 조회
    SELECT sql_text, statement_id
    INTO v_sql_text, v_statement_id
    FROM sql_plan_history
    WHERE plan_id = p_plan_id;
    
    DBMS_OUTPUT.PUT_LINE('========================================');
    DBMS_OUTPUT.PUT_LINE('Plan ID: ' || p_plan_id);
    DBMS_OUTPUT.PUT_LINE('Statement ID: ' || v_statement_id);
    DBMS_OUTPUT.PUT_LINE('========================================');
    DBMS_OUTPUT.PUT_LINE('SQL Text:');
    DBMS_OUTPUT.PUT_LINE(v_sql_text);
    DBMS_OUTPUT.PUT_LINE('========================================');
    DBMS_OUTPUT.PUT_LINE('');
    
    -- 실행 계획 트리 형태로 출력
    FOR rec IN (
        SELECT 
            LPAD(' ', depth * 2) || operation || ' ' || 
            NVL(options, '') || ' ' || NVL(object_name, '') AS operation_tree,
            cost,
            cardinality,
            bytes_size,
            access_predicates,
            filter_predicates
        FROM sql_plan_details
        WHERE plan_id = p_plan_id
        ORDER BY plan_step
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(
            RPAD(rec.operation_tree, 60) || 
            ' | Cost: ' || NVL(TO_CHAR(rec.cost), 'N/A') ||
            ' | Rows: ' || NVL(TO_CHAR(rec.cardinality), 'N/A')
        );
        
        IF rec.access_predicates IS NOT NULL THEN
            DBMS_OUTPUT.PUT_LINE('  Access: ' || rec.access_predicates);
        END IF;
        
        IF rec.filter_predicates IS NOT NULL THEN
            DBMS_OUTPUT.PUT_LINE('  Filter: ' || rec.filter_predicates);
        END IF;
    END LOOP;
    
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Plan ID ' || p_plan_id || '를 찾을 수 없습니다.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('오류 발생: ' || SQLERRM);
END display_sql_plan;
/

실행 계획 비교 프로시저

CREATE OR REPLACE PROCEDURE compare_sql_plans(
    p_plan_id1 IN NUMBER,
    p_plan_id2 IN NUMBER
) AS
    v_cost1 NUMBER;
    v_cost2 NUMBER;
    v_rows1 NUMBER;
    v_rows2 NUMBER;
BEGIN
    -- Plan 1 정보
    SELECT optimizer_cost, cardinality
    INTO v_cost1, v_rows1
    FROM sql_plan_history
    WHERE plan_id = p_plan_id1;
    
    -- Plan 2 정보
    SELECT optimizer_cost, cardinality
    INTO v_cost2, v_rows2
    FROM sql_plan_history
    WHERE plan_id = p_plan_id2;
    
    DBMS_OUTPUT.PUT_LINE('========================================');
    DBMS_OUTPUT.PUT_LINE('실행 계획 비교');
    DBMS_OUTPUT.PUT_LINE('========================================');
    DBMS_OUTPUT.PUT_LINE('Plan ID 1: ' || p_plan_id1);
    DBMS_OUTPUT.PUT_LINE('  Cost: ' || v_cost1 || ', Rows: ' || v_rows1);
    DBMS_OUTPUT.PUT_LINE('');
    DBMS_OUTPUT.PUT_LINE('Plan ID 2: ' || p_plan_id2);
    DBMS_OUTPUT.PUT_LINE('  Cost: ' || v_cost2 || ', Rows: ' || v_rows2);
    DBMS_OUTPUT.PUT_LINE('');
    
    -- 차이 분석
    IF v_cost1 < v_cost2 THEN
        DBMS_OUTPUT.PUT_LINE(' Plan 1이 더 효율적입니다 (Cost 차이: ' || 
                            ROUND((v_cost2 - v_cost1) / v_cost1 * 100, 2) || '%)');
    ELSIF v_cost2 < v_cost1 THEN
        DBMS_OUTPUT.PUT_LINE(' Plan 2가 더 효율적입니다 (Cost 차이: ' || 
                            ROUND((v_cost1 - v_cost2) / v_cost2 * 100, 2) || '%)');
    ELSE
        DBMS_OUTPUT.PUT_LINE('동일한 Cost입니다');
    END IF;
    
    DBMS_OUTPUT.PUT_LINE('========================================');
    
    -- 상세 비교
    DBMS_OUTPUT.PUT_LINE('');
    DBMS_OUTPUT.PUT_LINE('상세 차이점:');
    
    FOR rec IN (
        SELECT 
            p1.operation AS op1,
            p1.object_name AS obj1,
            p1.cost AS cost1,
            p2.operation AS op2,
            p2.object_name AS obj2,
            p2.cost AS cost2,
            p1.plan_step
        FROM sql_plan_details p1
        FULL OUTER JOIN sql_plan_details p2 
            ON p1.plan_step = p2.plan_step 
            AND p2.plan_id = p_plan_id2
        WHERE p1.plan_id = p_plan_id1
          AND (p1.operation != p2.operation 
               OR NVL(p1.object_name, 'N/A') != NVL(p2.object_name, 'N/A')
               OR p1.cost != p2.cost)
        ORDER BY p1.plan_step
    ) LOOP
        DBMS_OUTPUT.PUT_LINE('Step ' || rec.plan_step || ':');
        DBMS_OUTPUT.PUT_LINE('  Plan 1: ' || rec.op1 || ' ' || NVL(rec.obj1, '') || 
                            ' (Cost: ' || NVL(TO_CHAR(rec.cost1), 'N/A') || ')');
        DBMS_OUTPUT.PUT_LINE('  Plan 2: ' || rec.op2 || ' ' || NVL(rec.obj2, '') || 
                            ' (Cost: ' || NVL(TO_CHAR(rec.cost2), 'N/A') || ')');
    END LOOP;
    
END compare_sql_plans;
/

실행 계획 검색 뷰

CREATE OR REPLACE VIEW v_sql_plan_summary AS
SELECT 
    h.plan_id,
    h.statement_id,
    h.execution_time,
    h.username,
    h.optimizer_cost,
    h.cardinality AS estimated_rows,
    h.cpu_cost,
    h.io_cost,
    SUBSTR(h.sql_text, 1, 100) AS sql_preview,
    h.notes,
    COUNT(d.detail_id) AS plan_steps
FROM sql_plan_history h
LEFT JOIN sql_plan_details d ON h.plan_id = d.plan_id
GROUP BY 
    h.plan_id,
    h.statement_id,
    h.execution_time,
    h.username,
    h.optimizer_cost,
    h.cardinality,
    h.cpu_cost,
    h.io_cost,
    h.sql_text,
    h.notes
ORDER BY h.execution_time DESC;

사용 예제

-- 예제 1: SQL 실행 계획 저장
DECLARE
    v_plan_id NUMBER;
    v_sql CLOB := 'SELECT e.employee_id, e.first_name, e.last_name, d.department_name
                   FROM employees e
                   JOIN departments d ON e.department_id = d.department_id
                   WHERE e.salary > 5000
                   ORDER BY e.last_name';
BEGIN
    save_sql_plan(
        p_sql_text => v_sql,
        p_statement_id => 'EMP_DEPT_JOIN_001',
        p_notes => '급여 5000 이상 직원 조회',
        p_plan_id => v_plan_id
    );
    
    DBMS_OUTPUT.PUT_LINE('저장된 Plan ID: ' || v_plan_id);
END;
/

-- 예제 2: 저장된 실행 계획 조회
BEGIN
    display_sql_plan(p_plan_id => 1);
END;
/

-- 예제 3: 실행 계획 비교
BEGIN
    compare_sql_plans(p_plan_id1 => 1, p_plan_id2 => 2);
END;
/

-- 예제 4: 실행 계획 요약 조회
SELECT * FROM v_sql_plan_summary
WHERE execution_time >= SYSDATE - 7
ORDER BY optimizer_cost DESC;

실행 계획 분석 함수들

-- Full Table Scan 발생 여부 확인
CREATE OR REPLACE FUNCTION has_full_table_scan(
    p_plan_id IN NUMBER
) RETURN VARCHAR2 AS
    v_count NUMBER;
BEGIN
    SELECT COUNT(*)
    INTO v_count
    FROM sql_plan_details
    WHERE plan_id = p_plan_id
      AND operation = 'TABLE ACCESS'
      AND options = 'FULL';
    
    IF v_count > 0 THEN
        RETURN 'YES (' || v_count || ' occurrences)';
    ELSE
        RETURN 'NO';
    END IF;
END;
/

-- 인덱스 사용 여부 확인
CREATE OR REPLACE FUNCTION get_indexes_used(
    p_plan_id IN NUMBER
) RETURN VARCHAR2 AS
    v_indexes VARCHAR2(4000);
BEGIN
    SELECT LISTAGG(object_name, ', ') WITHIN GROUP (ORDER BY object_name)
    INTO v_indexes
    FROM (
        SELECT DISTINCT object_name
        FROM sql_plan_details
        WHERE plan_id = p_plan_id
          AND operation LIKE '%INDEX%'
          AND object_name IS NOT NULL
    );
    
    RETURN NVL(v_indexes, 'No indexes used');
END;
/

-- Cost 기반 성능 등급
CREATE OR REPLACE FUNCTION get_performance_grade(
    p_plan_id IN NUMBER
) RETURN VARCHAR2 AS
    v_cost NUMBER;
    v_grade VARCHAR2(1);
BEGIN
    SELECT optimizer_cost
    INTO v_cost
    FROM sql_plan_history
    WHERE plan_id = p_plan_id;
    
    CASE 
        WHEN v_cost < 10 THEN v_grade := 'A';
        WHEN v_cost < 100 THEN v_grade := 'B';
        WHEN v_cost < 1000 THEN v_grade := 'C';
        WHEN v_cost < 10000 THEN v_grade := 'D';
        ELSE v_grade := 'F';
    END CASE;
    
    RETURN v_grade || ' (Cost: ' || v_cost || ')';
END;
/

실행 계획 리포트 생성

CREATE OR REPLACE PROCEDURE generate_plan_report(
    p_plan_id IN NUMBER
) AS
    v_sql_text CLOB;
    v_cost NUMBER;
    v_rows NUMBER;
    v_statement_id VARCHAR2(100);
    v_execution_time TIMESTAMP;
BEGIN
    -- 기본 정보 조회
    SELECT sql_text, optimizer_cost, cardinality, statement_id, execution_time
    INTO v_sql_text, v_cost, v_rows, v_statement_id, v_execution_time
    FROM sql_plan_history
    WHERE plan_id = p_plan_id;
    
    DBMS_OUTPUT.PUT_LINE('╔═══════════════════════════════════════════════════════════════╗');
    DBMS_OUTPUT.PUT_LINE('║          SQL 실행 계획 분석 리포트                            ║');
    DBMS_OUTPUT.PUT_LINE('╚═══════════════════════════════════════════════════════════════╝');
    DBMS_OUTPUT.PUT_LINE('');
    DBMS_OUTPUT.PUT_LINE(' 기본 정보');
    DBMS_OUTPUT.PUT_LINE('  Plan ID: ' || p_plan_id);
    DBMS_OUTPUT.PUT_LINE('  Statement ID: ' || v_statement_id);
    DBMS_OUTPUT.PUT_LINE('  생성 시간: ' || TO_CHAR(v_execution_time, 'YYYY-MM-DD HH24:MI:SS'));
    DBMS_OUTPUT.PUT_LINE('  성능 등급: ' || get_performance_grade(p_plan_id));
    DBMS_OUTPUT.PUT_LINE('');
    
    DBMS_OUTPUT.PUT_LINE(' SQL 문장');
    DBMS_OUTPUT.PUT_LINE(v_sql_text);
    DBMS_OUTPUT.PUT_LINE('');
    
    DBMS_OUTPUT.PUT_LINE(' 통계 정보');
    DBMS_OUTPUT.PUT_LINE('  Optimizer Cost: ' || v_cost);
    DBMS_OUTPUT.PUT_LINE('  Estimated Rows: ' || v_rows);
    DBMS_OUTPUT.PUT_LINE('  Full Table Scan: ' || has_full_table_scan(p_plan_id));
    DBMS_OUTPUT.PUT_LINE('  Indexes Used: ' || get_indexes_used(p_plan_id));
    DBMS_OUTPUT.PUT_LINE('');
    
    DBMS_OUTPUT.PUT_LINE(' 실행 계획 상세');
    display_sql_plan(p_plan_id);
    
    DBMS_OUTPUT.PUT_LINE('');
    DBMS_OUTPUT.PUT_LINE('═══════════════════════════════════════════════════════════════');
    
END generate_plan_report;
/

정리 및 유지보수 프로시저

-- 오래된 실행 계획 삭제
CREATE OR REPLACE PROCEDURE cleanup_old_plans(
    p_days_to_keep IN NUMBER DEFAULT 30
) AS
    v_deleted_count NUMBER;
BEGIN
    DELETE FROM sql_plan_history
    WHERE execution_time < SYSTIMESTAMP - p_days_to_keep
    RETURNING COUNT(*) INTO v_deleted_count;
    
    COMMIT;
    
    DBMS_OUTPUT.PUT_LINE(v_deleted_count || '개의 오래된 실행 계획을 삭제했습니다.');
END cleanup_old_plans;
/

활용 쿼리 모음

-- 최근 저장된 실행 계획 TOP 10
SELECT * FROM (
    SELECT 
        plan_id,
        statement_id,
        execution_time,
        optimizer_cost,
        SUBSTR(sql_text, 1, 50) || '...' AS sql_preview
    FROM sql_plan_history
    ORDER BY execution_time DESC
)
WHERE ROWNUM <= 10;

-- Cost가 높은 SQL TOP 10
SELECT * FROM (
    SELECT 
        plan_id,
        optimizer_cost,
        cardinality,
        SUBSTR(sql_text, 1, 60) AS sql_preview
    FROM sql_plan_history
    ORDER BY optimizer_cost DESC
)
WHERE ROWNUM <= 10;

-- Full Table Scan이 발생하는 SQL
SELECT DISTINCT
    h.plan_id,
    h.statement_id,
    d.object_name AS table_name,
    h.optimizer_cost
FROM sql_plan_history h
JOIN sql_plan_details d ON h.plan_id = d.plan_id
WHERE d.operation = 'TABLE ACCESS'
  AND d.options = 'FULL'
ORDER BY h.optimizer_cost DESC;

-- 인덱스별 사용 빈도
SELECT 
    object_name AS index_name,
    COUNT(*) AS usage_count,
    AVG(cost) AS avg_cost
FROM sql_plan_details
WHERE operation LIKE '%INDEX%'
  AND object_name IS NOT NULL
GROUP BY object_name
ORDER BY usage_count DESC;