🗓 22-08-23

SQLP 이론 복습

(1) 고도화

SQLP 실기 풀이

(1) [SQLP실기풀이]4장 조인튜닝(4)-고급조인기법 51번
(2) [SQLP실기풀이]4장 조인튜닝(4)-고급조인기법 52번
(3) [SQLP실기풀이]4장 조인튜닝(4)-고급조인기법 53번
(4) [SQLP실기풀이]4장 조인튜닝(4)-고급조인기법 54번

SQLP 필기 풀이

(1) [SQLP필기풀이]2장 SQL 분석 도구

👀 8/23 복기

[1] 튜닝 쿼리에 괄호가 너무 많으면 줄여야 한다.

[SQLP실기풀이]4장 조인튜닝(4)-고급조인기법 52번

• index fast full scan의 경우 그냥 해시조인하고나서 group by 해도 괜찮다.
• group by 를 하기 위해 서브쿼리로 한번 더 감싸서 2개 이상의 괄호를 만들지 말고 그냥 합칠 것 !

[2] NVL2문과 DECODE문

[SQLP실기풀이]4장 조인튜닝(4)-고급조인기법 53번

• nvl2문 : NVL2 함수는 NULL이 아닌 경우 지정값1을 출력하고, NULL인 경우 지정값2를 출력한다.

NVL2("값", "지정값1", "지정값2")
NVL2("값", "NOT NULL", "NULL") 

• decode문

DECODE(컬럼, 조건1, 결과1, 조건2, 결과2, 조건3, 결과3..........) 

[3] 조건 구분값에 따라 조인 대상 테이블이 바뀔 때

[SQLP실기풀이]4장 조인튜닝(4)-고급조인기법 53번

• union all을 이용한다.
• 문제 조건 값을 꼭 읽어라!!( 이건 힌트 사용과 인덱스 재구성을 못하게 했다.)

[4] 오라클 AutoTrace에서 확인할 수 있는 정보

✅ 오라클 AutoTrace에서 확인할 수 있는 정보

• 예상 실행계획
• 실제 디스크에서 읽은 블록 수
• 실제 기록한 Redo 크기
  
• ⭐️ 오라클 AutoTrace에서 실제 사용한 CPU Time은 알 수 없다.

✅ 오라클 기본 Trace에서 확인할 수 있는 정보

• 예상 실행계획
• 실제 디스크에서 읽은 블록 수
• 실제 사용한 CPU Time

1. 오라클의 기본 SQL 트레이스(10046 트레이스)에서 실제 기록한 Redo 크기는 알 수 없다.
2. autotrace에서는 실제 사용한 CPU Time을 알 수 없고 오라클의 기본 SQL 트레이스에서는 실제 기록한 Redo 크기를 알 수 없다.

[5] 오라클 AutoTrace에서 사용할 수 있는 옵션

✅ AutoTrace에서 사용할 수 있는 옵션

[키워드 다섯가지를 조합하는 것이다.]

• 공통 문법 : set autotrace
• 선택 문법1(결과출력 여부)
  • 결과 출력 : on
  • 결과 미출력 : traceonly
• 선택 문법2(실행계획/실행통계)
  • 실행계획 : explain
  • 실행통계 : statistics

[결과 출력 O (당연히 SQL 실행)]

1. set autotrace on
   • SQL을 실행
   • 결과집합 있음
   • 예상 실행계획 및 실행통계를 출력
2. set autotrace on explain
   • SQL을 실행
   • 결과집합 있음
   • 예상 실행계획을 출력
3. set autotrace on statistics
   • SQL을 실행하고
   • 결과집합 있음
   • 실행통계를 출력

[결과 출력 X]

1. set autotrace traceonly
   • SQL을 실행하고
   • 결과는 출력하지 않고
   • 예상 실행계획과 실행통계 출력
2. set autotrace traceonly explain
   • ⭐️ SQL을 실제로 실행하지 않고
   • 예상 실행계획만 출력
3. set autotrace traceonly statistics
   • SQL을 실행하고
   • 결과는 출력하지 않고
   • 실행통계만 출력

[6] 오라클 SGA 메모리에 기록한 SQL 트레이스 정보를 출력하기

1. SQL에 gather_plan_statistics 힌트를 지정하면, SQL 트레이스 정보를 서버 파일이 아닌 SGA 메모리에 기록한다.
2. SGA 메모리에 저장된 트레이스 정보를 dbms_xplan.display_cursor 함수를 이용하면 분석하기 쉬운 형태로 포매팅해 준다.
   • 첫 번째와 두 번째 인자에는 SQL 커서의 ID와 CHILD_NUMBER를 입력해야 한다.
   • 첫 번째와 두 번째 인자에 null, null을 입력하면 바로 직전에 수행한 커서 ID와 CHILD_NUMBER를 내부에서 자동 선택해 준다.

[7] _DBMS_XPLAN.DISPLAY_CURSOR와 SQL 트레이스 항목 비교

✅ _DBMS_XPLAN.DISPLAY_CURSOR 함수를 통해 추출한 SQL 트레이스 정보

• Starts : 각 오퍼레이션 단계별 실행 횟수
• E-Rows : 옵티마이저가 예상한 Rows
• A-Rows : 각 오퍼레이션 단계에서 읽거나 갱신한 로우 수
  • SQL 트레이스 항목에서는 rows
• A-Times : 각 오퍼레이션 단계별 소요시간
  • SQL 트레이스 항목에서는 times
• Buffers : 캐시에서 읽은 버퍼 블록 수
  • SQL 트레이스 항목에서는 query(=cr) , current
• Reads : 디스크에서 읽은 블록수
  • SQL 트레이스 항목에서는 pr

[8] sql server에서 SQL 트레이스 옵션

✅ sql server에서 SQL 트레이스를 확인하고자 설정하는 옵션

• 공통 문법 : set statistics ? on
• 선택 문법
  • profile
  • io
  • time
• 선택 문법2(실행계획/실행통계)
  • 실행계획 : explain
  • 실행통계 : statistics

1. set statistics profile on
   • 각 쿼리가 일반 결과집합을 반환하고 그 뒤에는 쿼리 실행
   • 프로필을 보여 주는 추가 결과집합을 반환
   • 출력에는 다양한 연산자에서 처리한 행 수 및 연산자의 실행 횟수에 대한 정보도 포함
2. set statistics io on
   • Transact-SQL 문이 실행되고 나서 해당 문에서 만들어진 디스크 동작 양에 대한 정보를 표시
3. set statistics time on
   -각 Transact-SQL 문을 구문 분석, 컴파일 및 실행하는 데 사용 한 시간을 밀리초 (0.001초) 단위로 표시

[9] 대기이벤트

1. 프로세스가 버퍼캐시, 라이브러리 캐시 등 공유 메모리에서 래치를 획득할 때마다 나타난다. 👉 ❌
   • 래치를 획득하는 과정에 경합이 발생하면 대기 이벤트가 나타나지만, 경합없이 바로 읽으면 대기 이벤트가 나타나지 않는다.
2. library cache lock , library cache pin 대기이벤트는 주로 SOL 수행 도중 DDL을 수행할 때 나타난다.
3. free buffer waits 대기 이벤트는 서버 프로세스가 버퍼 캐시에서 Free Buffer를 찾지못해 DBWR에게 공간을 확보해 달라고 신호를 보낸 후 대기할 때 나타난다.
4. log file sync 대기 이벤트는 커밋 명령을 전송받은 서버 프로세스가 LGWR에게 로그 버퍼를 로그 파일에 기록해 달라고 신호를 보낸 후 대기할 때 나타난다.
5. Latch: shared pool 대기 이벤트는 shared pool 래치를 할당받는 과정에 발생하는 경합과 관련 있으며, 하드 파싱을 동시에 심하게 일으킬 때 주로 나타난다.

[10] 인스턴스 효율성 항목들

1. Soft Parse : 실행계획이 라이브러리 캐시에서 찾아져 하드파싱을 일으키지 않고 SQL 을수행한 비율이다. 구하는 공식은 아래와 같다.
   (전체 Parse Call 횟수 - 하드파싱 횟수) / (전체 Parse Call 횟수)* 100
2. Execute to Parse : Parse Call 없이 곧바로 SQL을 수행한 비율, 즉 커서를 애플리케이션에서 캐싱한 채 반복 수행한 비율이다.
3. Parse CPU to Parse Elapsed : 파싱 총 소요 시간 중 CPU time이 차지한 비율이다. 파싱에 소요된 시간 중 실제 일을 수행한 시간 비율을 말하며, 이 값이 낮다면 파싱 도중 대기가 많이 발생했음을 의미한다.