Oracle SQL Tuning 교육 정리

IT 공부

Oracle SQL Tuning 교육 정리 - Day 3

한라봉봉봉봉 2022. 5. 18. 16:22

https://ouconnect.oracle.com

username: 99389204.user11

password: wX945N0DeA

pw: Seoul_2022##

Example 26. Ordering Keys for Composite Index

- 결합 인덱스는 컬럼 순서가 중요하다

- 첫 번째 컬럼에 대해서는 검색하는 범위가 좁으면 좋다, 상대적으로 인덱스 block을 적게 읽는다

- 결합 인덱스가 많은 경우 유지 보수가 어렵다. (테이블 변경 점에 대해 인덱스 리빌딩, 저장 공간)

그럼에도 불구하고 성능 향상을 위해 인덱스가 필요하면 만들어야 한다.

index skip scan: 결합 인덱스 첫 번째 컬럼이 조건절에 사용하지 않은 경우

*Selectivity : 컬럼에 대해 검색하는 데이터 비율 → selectivity가 낮은 컬럼을 인덱스 선행 컬럼으로 사용하는게 좋아

Example 27. Bitmap Join Index

products 테이블을 index range scan으로, sales 테이블은 full table scan으로 검색하여 hash join, sum 계산하니까 sort aggregate

Bitmap index

1과 0으로 관리한다

자주 사용되는 조인 조건 및 검색 조건 결과에 대해 bitmap index로 미리 만들어둔다 (아래 예시)

create bitmap index sales_subcat_city_bjix on sales(p.prod_subcategory, c.cust_city)
  from sales s, products p, customers c
 where s.prod_id = p.prod_id
   and s.cust_id = c.cust_id

단점: B-tree index에 비해 유지 관리가 어렵다 (인덱스 리빌딩 하는데 오래 걸린다)

Example 28. Tuning a Complex Logic

여러 SQL문을 1개의 SQL문으로 변경해보자

이런거 싱기방기

Example 29. Using a Materialized View

materialized view

일반적인 뷰는 select문장을 가지고 있는거야. 데이터를 가지고 있는게 아니야.

얘는 데이터를 가지고 있는 뷰야. 그래서 인덱스 만들수 있음. 여러 SQL문에서 재사용 할 수 있음.

M-view가 사용하지 않았을 경우,

M-view를 사용한 경우, MAT_VIEW REWRITE ACCESS FULL 계획을 보여주고 시간도 0.04으로 단축됨

-- M-view 생성
create materialized view mv1
enable query rewrite
as
select p.prod_name, t.week_ending_day, sum(s.amount_sold) sum_amount
  from sales s, products p, times t
 where s.time_id = t.time_id 
   and s.prod_id = p.prod_id
 group by p.prod_name, t.week_ending_day;

-- rewrite 활성화
alter session set query_rewrite_enabled = true;

-- M-view의 SQL문 실행
select p.prod_name, t.week_ending_day, sum(s.amount_sold) sum_amount
  from sales s, products p, times t
 where s.time_id = t.time_id 
   and s.prod_id = p.prod_id
 group by p.prod_name, t.week_ending_day;

Example 30. Star Transformation

커피 내리느라 못 들었다 :D

query=3232 (current=39, update,insert하는 데이터 또는 구조가 변경되는 데이터, 보통 파티션 테이블을 검색할 땐 current>0이더라)

Practice 4.

Case 1. Correlated Subquery

Case 2. Join Conditions

Case 3. Simple Predicate

Case 4. Union

Case 5. Combining SQL Statements

Case 6. Database Connection Management

10개 update문을 수행하기 위해, 10번의 connection을 맺는 건 비효율적이다

5. Optimizer Fundamentals

SQL문장 처리 과정

* cursor 관리

PGA에서 SQL문에 대한 cursor는 50개 정도 잡을 수 있다. (OPEN_CURSORS로 설정)

SQL문의 실행 코드(p코드?)를 계속 잡을건지 release 할건지? PL/SQL로 관리할 수 있대

왜 잡고 있나요? 사용자가 동일한 SQL문장을 사용할 때 point를 통해 SGA의 실행 코드를 바로 찾아갈 수 있대

soft parse도 안할 수 있지 즉 실행 코드가 있는지 검색하는 과정을 스킵할 수 있지

동일한 문장을 looping으로 실행하는 경우에는 point 정보를 잡고 있으면 soft parse 과정을 생략할 수 있음

PL/SQL로 cursor를 관리할 수 있져

* cursor란,

1) shared cursor

라이브러리 캐시에 공유되어 있는 shared SQL area

2) session cursor

라이브러리 캐시에 공유되어 있는 cursor를 실행하기 위해서 PGA 영역에 할당하는 메모리 = Private SQL Area

= persistent area + runtime area

shared SQL area를 읽어 cursor를 실행하는 데 필요한 정보들(파싱된 SQL문, 문장을 수행하는데 필요한 기타 정보)을 private SQL area에 담고,

shared cursor를 가리키는 포인터를 유지

cursor의 상태 open, bound execute, close 정보도 관리 (cursor open = 커서를 실행하기 위한 준비 과정)

Aggregated PGA : 전체 PGA 크기 지정

Parse를 조금 더 구체적으로 본다면...

Parse call: 실행 계획을 PGA에 저장하는 호출?

최적화 작업 대상은 DML문. 무조건 최소 1번의 hard parse를 해야 한다.

SQL Row Source Generation

실행 계획들을 보고 가장 좋은 계획을 선택해야 한다

어느 정도의 데이터가 검색되는지가 중요

Row source generator : 각 계획에 대해 어느 정도의 데이터를 읽어야 하는지 예측하는 역할

얘는 얼마나 정확할까? 통계정보에 얼마나 정확하느냐에 따라 달렸지

Example - Execution Plan

employees 테이블을 index range scan하고, jobs 테이블에 대해 nested loops join하고,

위 결과에 대해, departments 테이블을 index unique scan하면서 nested loops join 한다

*는 사용된 조건에 대해 Predicate Information

각 step 별로 Rows: 검색되는 레코드 수 예측값

schema 정보 기반, 여러 plan을 만든다

statistics 정보 기반, 가장 좋은 plan을 선택한다

Query Transformer → Estimator → Plan Generator → Execution Plan

비용 계산을 정확하게 하기 위해 또는 더 효율적인 plan을 만들기 위해 옵티마이저가 쿼리를 변형

변형되는 경우

- OR 연산 (각각 검색하므로 비효율적), subquery unnesting (가능하면 병합하려고 함), complex view merging (view를 main query에 붙여서 하나의 쿼리문으로 병합하려고 하나 항상 가능한 건 아님), join predicate push down (조인 조건을 변형), transitive closure (where 조건 변형), IN을 EXISTS로 바꾸려고 함 (semi-join으로, 왜냐면 IN은 OR 연산이야), NOT IN을 NOT EXISTS로.

변형된 SQL문을 보는 방법은 실습에서 확인할 수 있듬

예시) (단, 비용이 비슷하거나 적은 문장으로 변형)

· OR → UNION ALL

· Subquery → JOIN

· view 생성문 + select문 → 1개의 select문

· view 생성문(UNION) + select문(where 조건) → 조건 추가 후 UNION하는 select문

· e.department_id=20 and e.department_id=d.department_id

→ e.department_id=20 and e.department_id=d.department_id and d.department_id=20

쿼리 변환에 대해 힌트를 줄 수 있어

p.202 Quiz -- plan을 통해 쿼리 변형이 일어나는지 알 수 있다

VIEW PUSHED PREDICATE → 쿼리 변형이 일어났따

쿼리 변형을 통해 sales 테이블의 인덱스를 사용하게 되었다

Query Transformer → Estimator → Plan Generator → Execution Plan

* Selectiviry = (검색하려는 row 수) / (전체 row 수)

* Cardinality = (전체 row 수) * Selectivity

즉, 원하는 rows수 비율도 중요하고, 원하는 rows 수 자체도 중요하다.

selectivity 계산 할 때

통계 정보가 없는 경우, dynamic statistics - 옵티마이저가 실행 계획을 만들 때 통계 정보를 수집하는 것

히스토그램이 없는 경우, row수 분포를 uniform dist으로 가정

selectivity는, I/O cost 추정할 때 사용하고 sort cost에도 영향을 준다

cardinality는 join, filter, sort 방법을 정할 때 참고하지

Cost의 단위는 SRd (Single-block Random read)

즉, 1000 costs = single-block 읽기를 1,000번 한다

cost = ( #single-block reads*single-block read time + #multiblock reads*multiblock read time + #CPU cycles*millions instruction per second) / (single-block read time)

단, 병렬 처리 등으로 공식이 달라질 수 있음

Query Transformer → Estimator → Plan Generator → Execution Plan

Plan 생성 - 모든 plan들을 비교할 수 없으므로 옵티마이저가 가장 좋은 plan을 만든다는 보장은 없음

Plan generator 레포트 생성은 부하가 큰 작업이므로 유의해야 한다.

p.213 Quiz

일반적으로 single-block read time=12ms이므로 (시스템 성능에 따라 다르다)

옵티마이저는 2000 cost * 12ms = 24 sec로 예측했다.

실제 수행 시간은 10 min이므로 옵티마이저의 plan은 정확하지 않다고 말할 수 있다.

Adaptive Query Optimization

parse 과정에서 실행 계획을 생성한다

→ 문제다! 왜? SQL문을 실행하는 중에 데이터 및 테이블 구조가 변경되면 실행하는 중에 plan을 변경해야 한다. hard parse을 하기 전까지 이전 plan을 사용해야 한다.

이에 대한 방안으로 adpative query optimization (12.2부터는 default로 활성화 된 것 같아)

SQL문 실행하면서 서버 프로세스가 통계정보를 계속 확인한다 overhead 발생할 수 있듬 그러므로 선택적으로 사용하는게 좋다

Adaptive plans

join methods, parallel dist methods는 동적으로 변경될 수 있다

Adaptive statistics

dynamic statistics (이걸 계속 강조하네) 하드 파싱할 때 통계 정보 재수집 했었는데 서버 프로세스가 통계 정보가 변경되었다고 판단되면 통계 정보 재수집 한다, SQL Plan directives 지시어? 힌트? 통계 정보에 대한 지시어. plan management를 DB 안에 저장. 통계 정보가 잘 수집되었는지 확인해라. dynamic statistics로 통계 수집 해라. SQL문 단위로.

옵티마이저 설정값

· cursor_sharing

- EXACT (바인드 변수를 사용한 경우 plan을 공유해라)

- FORCE (리터럴을 사용하는 경우에도 plan을 공유해라)

· db_file_multiblock_read_count : full scan 할 때 여러 block을 한번에 읽도록

· pga_aggraget_target : PGA 전체 크기 설정

· star_transformation_enabled

· result_cache_mode

- MANUAL (select한 결과 데이터를 메모리가 올려둬라) shared pool에 올리면 LRU 알고리즘 no 적용. 자주 참조하면서 변경을 자주 하지 않고 데이터가 크지 않은.

· optimizer%plan_baselines : plan을 DB에 저장해두고 쓰는 것과 관련된

6. Generating and Displaying Execution Plan

SQL문 실행 계획 확인하는 도구들

- EXPLAIN PLAN 명령어 --> SQL문 실행 없이 예상 실행 계획을 확인하는 도구

- PLAN_TABLE에 실행 계획 저장- AUTOTRACE - 실행 계획 및 통계 정보 제공 (AUTOTRACE ⊃ EXPLAIN PLAN) --> 실행 계획 확인하는 기본 도구- DBMS_SQL_MONITOR 구간 설정하여 실시간 성능 모니터링한 실행 계획을 V$SQL_PLAN_MONITOR에 저장- V$SQL_PLAN 라이브러리 캐시에 있는 실행 계획- AWR 1시간 단위의 성능 snap shot- SQL Monitoring Bad SQL문으로 의심되는 SQL문을 1초 단위로 모니터링하여 실행 계획을 v$sql_plan_monitor에 저장

여러 building block으로 구성

계층 구조 (parent/child)

데이터 접근 방법이나 join 방법을 알 수 있다

EXPLAIN PLAN

SQL문을 실행하지 않고 예상 계획을 보여준다 (실제 실행 계획은 아님) 통계 정보는 없음

stetement_id: SQL문 식별자

임시 테이블인 PLAN_TABLE에 저장

adaptive plans 기능으로 예상 계획을 보장하지 못함 정확한 건 trace을 떠야한다

SQL> EXPLAIN PLAN
2 SET STATEMENT_ID = 'demo01' FOR
3 SELECT e.last_name, d.department_name
4 FROM hr.employees e, hr.departments d
5 WHERE e.department_id = d.department_id;

select * from table(DBMS_XPLAN.DISPLAY());

예상 계획은 shared pool에 저장되므로 Plan hash value를 가진다?

옵티마이저가 항상 최고의 plan을 만들어주지 않으므로 사람이 검토해야 한다

AUTOTRACE

SQL문 실행 후, plan과 statistics를 제공

PLAN_TABLE 필요

PLUSTRACE role 필요

bind peeking은 바인드 변수에 따라 동적으로 plan을 세우는 것

DBMS_SQL_MONITOR

실시간 SQL 모니터, DB 모니터 정보를 제공

구간 설정 -- BEGIN_OPERATION ... END_OPERATION

V$SQL_MONITOR, V$SQL_PLAN_MONITOR에 저장된다

V$SQL_PLAN

v$sqlarea: 라이브러리 캐시 내 있는 각 parent cursor, child cursor를 group by 한 것

v$sql: 라이브러리 캐시 내 있는 각 child cursor

v$sql_workarea

v$sql_plan: 라이브러리 캐시 내에 있는 plan에 대한 통계 정보 예측값

v$sql_plan_statistics: plan에 대한 실제 통계 정보

SQL 식별자로 v$sql_plan의 hash_value 값을 쓴다

1개 SQL문에 대해 여러 plan이 있다 (main-sub, parent-child?)

sub plan을 가지는 main plan들이 있다 main은 실제 사용되는 plan인데 적절하지 않다고 판단되면 sub가 main이 되기도 한다

바인드 변수에 따라 plan이 달라질 수 있지

동일한 SQL문에 대해서 3명의 사용자가 사용함에 따라 hash value가 다를 수 있다

1개 cursor에는 1개의 plan만

V$SQL_PLAN_STATISTICS

V$SQL_PLAN의 확장 버전 통계 정보들을 볼 수 있지

요 정보를 옵티마이저가 참고하기도 하지 adaptive plan 기능으로

AWR

별도 라이선스 필요

60분 단위 스냅샷 저장하여 self-tuning, problem-detection을 위한 목적

과거 실행되었던 SQL문 성능 분석