Tuning #1

[낯선풍경0]

/* =====================================================================
   SQL 튜닝 포인트 자동 점검 스크립트 (Tibero 기준, Oracle 호환)
   ---------------------------------------------------------------------
   STEP 1. SQL 텍스트 정적 분석     : :sql_text  에 SQL 전체를 입력
   STEP 2. 공유 풀에서 SQL_ID 찾기  : :search_key 에 SQL의 특징적인 일부 입력
   STEP 3. 실행계획 튜닝 포인트     : :sql_id    에 STEP 2 결과 입력
   STEP 4. 관련 테이블 인덱스 확인  : :sql_id    동일
   ※ 버전에 따라 V$ 뷰 컬럼명/단위가 다를 수 있으니 오류 시 DESC로 확인
   ===================================================================== */


/* ---------------------------------------------------------------------
   STEP 1. SQL 텍스트 정적 분석 (실행 이력 없어도 사용 가능)
   --------------------------------------------------------------------- */
WITH src AS (
    SELECT ' ' || UPPER(REGEXP_REPLACE(:sql_text, '[[:space:]]+', ' ')) || ' ' AS t
    FROM   DUAL
),
rules AS (
    SELECT  1 no, '높음' lvl, 'SELECT * 사용'            item, 'SELECT (DISTINCT )?\*|[A-Z0-9_$#]\.\*' pat,
           '필요한 컬럼만 명시 (I/O 감소, 인덱스만 읽고 끝낼 기회 확보)' advice FROM DUAL UNION ALL
    SELECT  2, '높음', 'LIKE 앞 % 검색',                 'LIKE ?''%',
           '인덱스 범위 스캔 불가 → 앞자리 고정 검색 또는 전문검색/역인덱스 검토' FROM DUAL UNION ALL
    SELECT  3, '높음', '조건 좌변 컬럼에 함수 사용',
           '(WHERE|AND|OR|ON) \(?(TO_CHAR|TO_DATE|TO_NUMBER|SUBSTR|NVL|NVL2|TRIM|LTRIM|RTRIM|UPPER|LOWER|DECODE|TRUNC|COALESCE|REPLACE|CONCAT) ?\(',
           '컬럼 가공 시 인덱스 사용 불가 → 상수 쪽을 가공하거나 함수기반 인덱스 검토' FROM DUAL UNION ALL
    SELECT  4, '높음', '조건 좌변 컬럼에 연산 사용',
           '(WHERE|AND|OR|ON) [A-Z0-9_$#.]+ ?(\+|-|\*|/|\|\|) ?[A-Z0-9_$#.:'']+ ?(=|<|>|LIKE|BETWEEN|IN)',
           'COL + 1 = :B → COL = :B - 1 형태로 변경' FROM DUAL UNION ALL
    SELECT  5, '중간', 'NOT IN 서브쿼리',                'NOT IN ?\(',
           'NULL 존재 시 결과 오류 가능 + 비효율 → NOT EXISTS 또는 안티조인 검토' FROM DUAL UNION ALL
    SELECT  6, '중간', '부정 조건 (<>, !=, NOT LIKE)',  '<>|!=|\^=|NOT (LIKE|BETWEEN) ',
           '인덱스 범위 스캔 불가 → 긍정 조건(IN, 범위)으로 전환 가능한지 검토' FROM DUAL UNION ALL
    SELECT  7, '중간', 'IS NULL / IS NOT NULL',          ' IS (NOT )?NULL',
           'B-Tree 인덱스는 전체 NULL 키 미저장 → NOT NULL 제약/결합인덱스/기본값 검토' FROM DUAL UNION ALL
    SELECT  8, '중간', 'OR 조건',                        ' OR ',
           '인덱스 효율 저하 가능 → IN 또는 UNION ALL 분리 검토' FROM DUAL UNION ALL
    SELECT  9, '중간', 'DISTINCT 사용',                  'DISTINCT',
           '불필요한 정렬/해시 발생 → 조인 중복 원인 제거 또는 EXISTS 사용 검토' FROM DUAL UNION ALL
    SELECT 10, '중간', 'UNION (ALL 아님)',               'UNION (SELECT|\()',
           '중복 제거 정렬 발생 → 중복이 없으면 UNION ALL 사용' FROM DUAL UNION ALL
    SELECT 11, '중간', '서브쿼리 존재',                  '\( ?SELECT ',
           '스칼라 서브쿼리/FILTER 반복 수행 여부를 실행계획에서 확인 (조인 전환 검토)' FROM DUAL UNION ALL
    SELECT 12, '중간', '리터럴 상수 사용',               '(=|<|>|IN ?\() ?(''|[0-9])',
           '하드 파싱 증가 → 반복 실행 SQL이면 바인드 변수 사용' FROM DUAL UNION ALL
    SELECT 13, '낮음', 'HAVING 사용',                    'HAVING',
           '집계 함수와 무관한 조건은 WHERE로 옮겨 집계 전 건수 축소' FROM DUAL UNION ALL
    SELECT 14, '낮음', 'ORDER BY 사용',                  'ORDER BY',
           '정렬 부하 → 인덱스 순서로 정렬 생략 가능한지 확인' FROM DUAL UNION ALL
    SELECT 15, '낮음', 'ROWNUM 사용',                    'ROWNUM',
           'ORDER BY와 함께 쓰면 인라인뷰 안에서 정렬 후 ROWNUM 적용했는지 확인 (Top-N)' FROM DUAL UNION ALL
    SELECT 16, '낮음', 'COUNT(*) 사용',                  'COUNT ?\( ?\* ?\)',
           '존재 여부 확인 목적이면 EXISTS 또는 ROWNUM <= 1 로 대체' FROM DUAL UNION ALL
    SELECT 17, '참고', '힌트 사용',                      '/\*\+',
           '힌트가 실제 실행계획에 반영됐는지, 지금도 유효한지 확인' FROM DUAL
)
SELECT r.no   AS "번호",
       r.lvl  AS "중요도",
       r.item AS "튜닝 포인트",
       r.advice AS "개선 방안"
FROM   src s, rules r
WHERE  REGEXP_LIKE(s.t, r.pat)
ORDER  BY DECODE(r.lvl, '높음', 1, '중간', 2, '낮음', 3, 4), r.no;


/* ---------------------------------------------------------------------
   STEP 2. 공유 풀에서 SQL_ID + 실행 통계 찾기
   - 전체 텍스트보다 테이블명/특정 조건 등 특징적인 일부로 검색 권장
   - GETS_PER_ROW(1건당 블록 읽기)가 크면 비효율 SQL일 가능성 높음
   --------------------------------------------------------------------- */
SELECT sql_id,
       executions                                                  AS execs,
       ROUND(elapsed_time   / GREATEST(executions, 1), 2)          AS avg_elapsed,
       ROUND(cpu_time       / GREATEST(executions, 1), 2)          AS avg_cpu,
       ROUND(buffer_gets    / GREATEST(executions, 1))             AS avg_gets,
       ROUND(disk_reads     / GREATEST(executions, 1))             AS avg_reads,
       ROUND(rows_processed / GREATEST(executions, 1))             AS avg_rows,
       ROUND(buffer_gets    / GREATEST(rows_processed, 1))         AS gets_per_row,
       CASE
         WHEN buffer_gets / GREATEST(rows_processed, 1) > 1000 THEN '★ 1건당 읽기 과다'
         WHEN disk_reads  / GREATEST(buffer_gets, 1)    > 0.3  THEN '★ 디스크 읽기 비중 높음'
         WHEN executions > 10000 AND buffer_gets / GREATEST(executions,1) > 100
                                                                THEN '★ 빈번 실행 + 고비용'
       END                                                         AS check_point,
       SUBSTR(sql_text, 1, 100)                                    AS sql_text
FROM   v$sqlarea
WHERE  UPPER(sql_text) LIKE '%' || UPPER(:search_key) || '%'
AND    UPPER(sql_text) NOT LIKE '%V$SQLAREA%'          -- 이 조회문 자신 제외
ORDER  BY elapsed_time DESC;


/* ---------------------------------------------------------------------
   STEP 3. 실행계획에서 튜닝 포인트 찾기
   - Tibero는 OPERATION에 옵션까지 포함됨 (예: TABLE ACCESS (FULL))
   - Oracle이면 op 정의를 p.operation || ' ' || p.options 로 변경
   --------------------------------------------------------------------- */
WITH plan AS (
    SELECT p.*,
           p.operation AS op                              -- Oracle: p.operation || ' ' || p.options
    FROM   v$sql_plan p
    WHERE  p.sql_id = :sql_id
)
SELECT p.id,
       LPAD(' ', 2 * p.depth) || p.op                      AS operation,
       p.object_name,
       p.cost,
       p.cardinality,
       t.num_rows                                          AS tab_rows,
       CASE
         WHEN p.op LIKE '%CARTESIAN%'
              THEN '★ 카테시안 조인 → 조인 조건 누락 확인'
         WHEN p.op LIKE '%TABLE ACCESS%FULL%' AND NVL(t.num_rows, 0) >= 100000
              THEN '★ 대용량 FULL SCAN → 조건 컬럼 인덱스 검토'
         WHEN p.op LIKE '%SKIP%'
              THEN '★ 인덱스 선두 컬럼 조건 없음 → 인덱스 컬럼 순서 검토'
         WHEN p.op LIKE '%INDEX%FULL%'
              THEN '인덱스 전체 스캔 → 범위 스캔 가능한 조건/인덱스 검토'
         WHEN p.op LIKE '%NESTED%' AND p.cardinality > 10000
              THEN '★ 대량 NL 조인 → 해시 조인 또는 조인 순서 검토'
         WHEN p.op LIKE '%FILTER%'
              THEN '서브쿼리 반복 수행 가능 → 조인/세미조인 전환 검토'
         WHEN p.op LIKE '%SORT%'
              THEN '정렬 발생 → 인덱스로 정렬 생략 가능한지 확인'
         WHEN UPPER(p.filter_predicates) LIKE '%TO_NUMBER%'
           OR UPPER(p.filter_predicates) LIKE '%TO_CHAR%'
           OR UPPER(p.filter_predicates) LIKE '%INTERNAL_FUNCTION%'
              THEN '★ 형변환/가공 조건 → 암시적 형변환, 컬럼 가공 확인'
         WHEN p.op LIKE '%TABLE ACCESS%' AND
              (t.last_analyzed IS NULL OR t.last_analyzed < SYSDATE - 30)
              THEN '통계정보 없음/오래됨 → 통계 수집 검토'
       END                                                 AS tuning_point,
       p.access_predicates,
       p.filter_predicates
FROM   plan p
LEFT   JOIN dba_tables t
       ON  t.owner      = p.object_owner
       AND t.table_name = p.object_name
ORDER  BY p.id;


/* ---------------------------------------------------------------------
   STEP 4. 실행계획에 나온 테이블의 인덱스 구성 확인
   - WHERE/JOIN 조건 컬럼과 비교해서 인덱스 누락, 컬럼 순서 문제 확인
   --------------------------------------------------------------------- */
SELECT ic.table_owner,
       ic.table_name,
       ic.index_name,
       i.uniqueness,
       LISTAGG(ic.column_name, ', ') WITHIN GROUP (ORDER BY ic.column_position) AS index_columns,
       i.last_analyzed
FROM   dba_ind_columns ic
JOIN   dba_indexes i
       ON  i.owner      = ic.index_owner
       AND i.index_name = ic.index_name
WHERE  (ic.table_owner, ic.table_name) IN (
           SELECT DISTINCT p.object_owner, p.object_name
           FROM   v$sql_plan p
           WHERE  p.sql_id = :sql_id
           AND    p.operation LIKE 'TABLE ACCESS%'
       )
GROUP  BY ic.table_owner, ic.table_name, ic.index_name, i.uniqueness, i.last_analyzed
ORDER  BY ic.table_name, ic.index_name;