성능 개선을 위한 기본 지식

K·2022년 6월 8일
0

SQL BOOSTER

목록 보기
4/12

1. 실행계획

1.1 유의사항

  • 좌변은 절대 가공하지말라, 컬럼을 가공하면 인덱스를 사용못한다는 뜻인데 원리를 파악해야함
  • NL조인에서 크기가 작은 집합이 선행집합이면 일반적으로 효율이 좋지만 페이징SQL에서는 꼭 그렇지 않다
    페이징 기준이 크기가 큰 테이블쪽에있다면 선행집합으로 큰테이블 선택하는것이 NL조인성능에 좋다
  • 절대 결론만 외우지말고 원리를 파헤치고 고민할것.

1.2 실행계획

  • 구문분석 > 실행계획생성 > 실행계획대로 SQL처리
  • SQL실행전 데이터를 어떤 방법과 순서를 처리할지 실행계획을 생성, 그러므로 실행계획에 따라 성능이 달라진다.
  • SQL성능을 개선하려면 실행계획에 대한 이해가 필요하다.

1.3 실행계획 확인하기(예상 실행계획)

  • 여러가지 방법이있다.
    • SQL DEVELOPER에서 F10
    • TOAD CTRL+E
    • EXPLAIN PLAN FOR
      쿼리;
      SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

1.4 실행계획의 순서

  • 오퍼레이션 실행순서는 SQL성능을 좌우하는 중요한 요소중 하나.
  • 부모는 여러 자식을 가질 수 있다. 자식은 부모보다 들여쓰기 되어있다
  • 형제는 같은 들여쓰기 수준을 하고있다.
  • 형제중에 위쪽의 오퍼레이션이 형이다
  • 자식이 부모보다 먼저수행
  • 형제간에는 형이 먼저수행(위쪽)

1.5 실제 실행계획 확인하기

  • GATHER_PLAN_STATISTICS 힌트사용
  • 권한이 필요한테이블 : V$SQL, VSQLPLANSTATISTICSALL,VSQL_PLAN_STATISTICS_ALL, VSQL_PLAN, V$_SESSION
  • SQL_ID, CHILDNUMBER 찾는 쿼리

    SELECT T1.SQL_ID, T1.CHILD_NUMBER, T1.SQL_TEXT
    FROM V$SQL T1
    WHERE T1.SQL_TEXT LIKE '%GATHER_PLAN_STATISTICS%'
    ORDER BY T1.LAST_ACTIVE_TIME DESC;

  • 실제 실행계획 조회하기

    SELECT *
    FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('SQL_ID',CHILD_NUMBER,'ALLSTATS LAST'));

  • 실행계획 조회
  • 항목 설명
    • ID : 실행계획의 오퍼레이션 ID
    • OPERATION : 해당 단계에서 수행한 작업
    • NAME :해당 단계에 작업을 수행한 대상 오브젝트(테이블 OR 인덱스)
    • STARTS : 해당단계 수행 횟수
    • E-ROWS : 해당 단계의 예상 데이터 건수
    • A-ROWS : 해당 단계의 실제 데이터 건수
    • A-TIME : 해당 단계까지 수행된 실제 시간(누적)
      (A-TIME은 복잡한 실행계획이나 병렬 쿼리에서는 부정확하게 나오는 경우도 있다)
    • BUFFERS : 해당 단계까지 메모리 버퍼에서 읽은 블록수(논리적 IO횟수, 누적)
    • READS : 해당 단계까지 디스크에서 읽은 블록 수(물리적 IO횟수, 누적)
    • OMEM, 1MEM, USED-MEM : SQL처리를 위해 사용한 메모리 수치
  • A-ROWS, A-TIME, BUFFERS가 성능 개선을 위해 주의깊게 볼 항목이다
    이항목들이 눈에띄게 수치가 높아진단계가 있다면 해당 부분의 원인을 찾아 개선하면된다.
  • 운영서비스 SQL은 GATHER_PLAN_STATISTICS 힌트를 제외해야한다, 불필요한 수치 수집으로 성능상 손해 볼수있다.

2. 성능 개선을 위한 최소한의 지식

2.1 옵티마이저

  • 성능 최적화를위해 실행계획을 만드는 역할
  • CBO, RBO가 있으며대부분의 RDBMS는 CBO를 사용
  • CBO의 비용은 I/O횟수, CPU TIME, 메모리 사용량을 의미
  • 비용산출시 가장중요한것은 테이블의 통계정보이며 매우중요하다!!!!

2.2 소프트 파싱, 하드 파싱

  • SQL처리순서
    • 구문분석 : 문법검사, 의미검사(실제존재하는지), 권한검사
    • 소프트 파싱 : SQL실행계획이 메모리에있는지 검색, 있으면 만들어놓은 실행계획 재사용
    • 하드 파싱 : 메모리에없으면 옵티마이저가 실행계획을 새로 만드는 작업
  • 실행계획 생성은 제법 큰 비용이 소모
  • 바인드 변수사용시 동일 SQL을 소포트 파싱하여사용가능. (텍스트가 하나라도 다를경우 다른 SQL로 인식함)
  • 바인드변수사용시에도 성능이 나빠질수있지만 OLTP시스템은 소프트파싱으로 개발해야한다.
    하드파싱이 많은 CPU자원을 사용하기때문

2.3 IO(INPUT, OUTPUT)

  • 성능개선의 핵심은 IO, 가장중요한부분이라 볼수 있음
  • 불필요한 I/O를 찾아야한다
  • I/O개선을 위한방법
    • 불필요한 테이블 사용하는지
    • 불필요한 데이터를 조회하는지
    • 불필요한 부분 제거
    • 인덱스 고민(최소의 I/O처리를위해) > 찾으려는 데이터가 너무많을경우에는 인덱스가 비효율

2.4 블록

  • 오라클 I/O처리 최소단위, MS-SQL/MY-SQL은 페이지
  • 최소 크기로 데이터가 저장되도록 테이블 설계필요
  • 불필요하게 큰 데이터가 저장되면 블록하나에 저장할수있는 데이터 건수가 작아지며, 이로인해 I/O횟수가 늘어난다.
  • I/O최적화를 위해 하나의 블록에 많은 데이터가 들어가도록 설계가 필요.

2.5 논리적 I/O와 물리적I/O

  • 논리적 I/O는 BUFFERS(메모리), 물리적 I/O는 READ(DISK)로 표시
  • 논리적I/O : 버퍼캐시에서 필요한 데이터를 가져오는 것을
  • 물리적 I/O : 버퍼캐시에 원하는 데이터가 없으면 디스크에서 데이터블록을 가져옴
  • 처음 SQL실행시 느리지만 같은 SQL을 바로실행하면 빠르게 결과가나오는데 첫번째는 디스크 두번째는 버퍼캐시에서 가져오기때문이다.
  • 버퍼캐시는 제한된자원이라 LRU알고리즘. 가장오래전 사용된 데이터를 날림.
  • DB성능향상을 위해 메모리 추가하는것을 고려해볼수있음(H/W적 방법)
  • 실행계획에서 BUFFER항목을 항상 주의깊게 봐야함, 성능이 나빠진 증거를 찾을 수 있다

2.6 부분 범위 처리

  • SQL을 실행시 조건에 맞는 데이터를 필요한 만큼만 찾아서 내보내는 처리방식
  • SQL결과 집합의 총건수가 500건이고 화면에 보여줄 데이터가 50건일때, 화면에 먼저보여줄 50건만 우선처리하여 전송.
  • 부분범위 처리는 빠른 응답시간을 보장해준다.
  • 처리과정
      1. SQL조건에 맞는 데이터를 요청건수만큼 찾을때까지 읽는다
      1. 요청건수만큼 데이터 찾으면 결과를 사용자에게 보냄
      1. 첫번째 결과보낸상태에서 사용자의 다음 데이터 요청을 기다린다.
      1. 다음데이터 요청하면, 1에서 멈춘지점부터 읽으면서 조건에 맞는 데이터를 요청건수만큼 채워 사용자에게 전송
  • GROUP BY나 ORDER BY, 그룹함수(SUM, AVG~등)을 사용할때는 데이터 일부분만 처리하기 쉽지않다.
  • ORDER BY는 정렬된 집합을 얻으려면 전체데이터를 읽어야함.
    하지만 SQL과 인덱스에 따라 데이터 찾는과정을 손쉽게 범위처리할수도있음.
profile
늙어가면서 기억을 남기는 개발자

0개의 댓글