Oracle SQL Tuning 교육 정리 - Day 2

Practice 3. Tracing Applications

recursive SQL

recursive depth : 1 

*dynamic SQL이란, String형 변수에 담아서 기술하는 SQL문 (http://wiki.gurubee.net/display/DBSTUDY/Static+vs.+Dynamic+SQL)

 

Case 4. 과도한 fetch

parse=1, execute=1, fetch=918844 → 레코드를 하나씩 읽어서 보낸다

disk=879 → 파티션 관련 정보를 읽는다

[array size 변경]

fetch=92 → array size는 client가 설정한다. array size가 커지면 fetch 횟수 및 시간 감소한다.

※ 관련 문서: http://wiki.gurubee.net/pages/viewpage.action?pageId=26279999 

 

Case 5. Extent 관리 방법

insert into ... select ... from ....

extent 할당을 위해 recursive call 과도하게 발생

extent를 확보하기 위해서는 비어 있는 block을 찾아야 한다

Execute cpu=3.04, elapsed=4.09 → 공간을 확보하기 위해 wait 발생

[extent management을 Dictionary → Local로 변경]

Execute cpu=1.53, elapsed=1.76

block들의 정보를 읽는건 dictionary 관리 TBS에서 일거야 한다

※ 관련 문서: https://myjamong.tistory.com/218 

Q. recursive call이 발생했다는 건 어떻게 알 수 있나요?

A. tkprof 레포트를 만들 때 sys='yes'로 설정하면 recursive call 정보를 얻을 수 있다.

DML문의 Execute에는 자원 확보를 위한 recursive call 시간도 포함되어 있다.

 

Case 6. update, delete 작업 중인 데이터를 읽는 경우

SQL ID란 shared pool에 파싱 결과를 저장할 때 사용하는 SQL에 대한 식별자

Fetch query=978282, rows=7059 → 결과 레코드보다 과도하게 block을 검색하고 있어

조회하는 데이터를 현재 update, delete 하고 있어update, delete하는 레코드를 검색하면 data block을 읽는게 아니라 (commit 하기 전의) undo block을 읽어야 해

Fetch query가 많을 땐 update, delete 작업을 의심해야 해

disk=4047 vs query=978282 → 과도한 차이 발생 시 불필요한 buffer를 읽는거라고 의심의심

 

Case 7. order by와 index 그리고 EXPLAIN 옵션

order by column1 / column1: 인덱스 컬럼

인덱스를 통해 데이터를 검색하면 sort를 수행하지 않는다

그러나 인덱스 생성 후에도 table access full scan을 한다

옵티마이저가 index 사용할지 검토했으나, 

index 사용 비용과 table full scan 비용이 비슷한 상황에서

mode=ALL_ROWS인 경우 table full scan 방법을 선택한다.

...

두번째 레포트를 만들 때 

EXPLAIN 옵션을 사용(p.42)하면 trace file에 있는 실행계획을 재검토한다.

 

 

4. Understanding Basic Tuning Techniques

SQL문 성능을 향상 시키는 법- 통계정보 수집, 실행 계획 리뷰, SQL문/인덱스/데이터 restructure, trigger 수정, 안정적인 실행계획- 최소한의 데이터를 검색하는 것이 제일 중요

 

Example 1. Table design

· original

  인덱스 컬럼에 함수를 적용하면 인덱스 사용 불가

  CUST_POSTAL_CODE에 대한 인덱스 사용 못함

· 변환하는 대신 컬럼 추가하여 인덱스 생성한 경우  query=3479, rows=2733  인덱스 사용해서 성능 향상

· 변환하는 대신 컬럼 추가하여 인덱스 생성하고 힌트 사용한 경우

 

Example 2. Index usage

· 인덱스를 사용하지 못하는 경우: <>, between 

Selectivity 전체 테이블 데이터 중 검색하고자 하는 데이터 비율 (selectivity는 작은 경우 인덱스를 사용하는게 좋아)

 

Example 3. Transformed Index

인덱스 컬럼을 연산하여 검색하면 인덱스 사용 불가

검색하고자 하는 데이터 비율이 높으면 인덱스가 있더라도 table full scan 가능

 

Example 4. Data Type Mismatch

varchar2 타입 컬럼에 조건 값은 number 유형으로

묵시적 형 변환 발생(number→varchar2) 성능 감소

 

Example 5. Order by

sort 연산은 PGA에서 수행

인덱스 컬럼을 사용하면 sort 연산 하지 않음

(a) 인덱스가 있으나 옵티마이저는 table full scan하고 sort 연산 함 (필요 시 힌트 줄 수 있음)

(b) PK임에도 옵티마이저는 table full scan하고 sort 연산 함

(c) cust_city='Paris' 조건 추가해서 검색하는 데이터 범위가 줄어들었으나 여전히 sort 연산 함

(d) where cust_id < 200 order by cust_id → 인덱스 컬럼으로 조건 검색하고 order 해야 하므로, sort 연산 없음

 

Example 6. Retrieving a MAX value

(a) max(cust_credit_limit)

  인덱스 컬럼 max 검색

  max와 같은 그룹함수가 나오면 실행 계획에 sort aggregate라고 뜸 

(b) max(cust_credit_limit+1000)

  컬럼을 연산하더라도 인덱스로 sort aggragate 한다

(c) max(cust_credit_limit*2)

  연산 비용이 많이 드는 경우 인덱스 사용하지 않을 수도 있음

 

Example 7. Retrieving a MAX value

결합 인덱스SQL문 의도: 가장 최근 구매일자에 원하는 고객, 상품 조건에 대한 판매 이력현재 SQL문: 원하는 고객, 상품의 가장 최근 구매일자에 대한 판매 이력 correlated subquery → 성능에 안좋음. outer table을 full table scan 한다 (단, 조건이 있을 시 filter된 데이터만 scan 함)어떻게 튜닝할래?  - 힌트 사용: /*+ index_desc(sales sales_pk) */*  time_id의 max를 검색하고자 하므로 index_desc로 힌트를 준다

 

Example 8. Correlated Subquery· original  outer table과 avg(salary)를 row 단위로 비교· inline view로 재작성

  avg(salary)를 임시 view로 미리 계산해둔다

 

Example 9. UNION and UNION ALL

· UNION

  sort 연산 수행 (sort unique), buffer를 좀더 많이 읽음

· UNION ALL

  sort 연산 수행 안함

 

Example 10. Avoiding the Use of HAVING

having 절: 그룹함수 사용 시 조건을 줄 때 

(a) group by cust_city having cust_city = 'Paris';

  전체 데이터로 group by 수행 후 having절은 마지막에 수행

(b) where cust_city = 'Paris' group by cust_city; 

  원하는 조건 데이터만 검색하므로 읽는 데이터 범위가 줄어든다

 

Example 11. Tuning the BETWEEN operator

between 52788 and 52790 

  full table scan

between 52788 and 52790 & 힌트로 인덱스 강제 사용 /*+ index(c

  index skip scan 결합 인덱스 경우 일부 컬럼만 사용하더라도 검색 가능

  검색하는 데이터 범위를 줄여서 성능 조금 향상

in (52788, 52789, 52790) & 힌트로 인덱스 강제 사용 /*+ index(c

  index range scan in절을 사용

 

실습 환경

My training environment > eg10039.us.oracle.com

oracle/OU99389204

 

Example 12. Tuning the Join Order

먼저 접근하는 테이블: driving table (outer table)

먼저 사용되는 인덱스: driving index

- 적은 rows 수의 테이블 먼저 검색하게 한다

- 인덱스 컬럼을 조건을 사용하는 테이블을 driving table로 먼저 검색하게 한다

FROM절에 작성하는 테이블 순서는 중요하지 않다. 실제 접근 방법은 옵티마이저가 선택한다.

 

Example 13. Testing for Existence of Rows

EXISTS는 언제 사용하나?

- 조건의 데이터가 존재하는지 확인할 때 (데이터가 1건인지 10건인지 노 상관, 있는지만 확인하고 싶을 때 사용)

- semi join: exist 수행하면 세미 조인을 수행한다

- count stopkey: 존재하면 검색 그만

· original

· EXISTS 사용1 vs 사용2

  사용1. from sales s where s.prod_id=120  사용2. from customers c where c.cust_credit_limit > 10000  사용1보다 사용2의 데이터 범위가 적기 때문에 사용2의 쿼리가 가장 효율적이다

 

Example 14. LIKE '%STRING'LIKE 연산자 사용 시 %를 앞에 넣어 사용하면 인덱스 사용 못함 ('STRING%'은 사용 가능)

REVERSE 연산자를 사용하면 인덱스를 사용하게 할 수 있다

· '%ING'

  index fast full scan 

· reverse(cust_last_name) like 'GNI%' (단, 인덱스는 reverse(cust_last_name)으로 생성)

  index range scan

 

Example 15. Using Caution When Managing Views★다시 확인

뷰뷰뷰뷰

complex views 조인은 주의하세용

complex view? 2개 이상 테이블을 조인하여 가공한 view, 조인, 함수, GROUP BY, UNION 등을 사용하는 view...

왜? 뷰에 대한 데이터 집합 생성되고. 임시 테이블처럼 만들어지는거고. 뷰에 대해서는 테이블의 인덱스를 사용할 수 없어. 뷰의 select 튜닝 중요해. 뷰는 통계정보가 없음. 뷰는 꼭 필요한 데이터들로만 구성한다.

뷰 재사용 금지

서브 쿼리에서 view를 사용하는 경우?

unnesting subqueries 옵티마이저 쿼리 변환 시, 서브 쿼리를 메인 쿼리에 병합한다. 근데 병합되지 않는 쿼리들은 (correlated subquery) 문제가 되니...? 뷰를 이용해서 성능 향상될 수도.outer join을 view로 만드는 경우도 주의하래. 뷰 안에 있으면 성능이 원인 분석이 어렵지. 안보이니까.

 

Example 16. Writing a Combined SQL StatementDECODE를 이용해서 3개 쿼리를 1개 쿼리로 합칠 수 있져

 

Example 17. Writing a Multitable INSERT Statement

two INSERT statements → a multitable INSERT statement로 변환 (테이블 스캔을 한번에)

 

insert all 

when cust_marital_status='single' then into cust_single

when cust_marital_status='married' then into cust_married

select * from customers; (오 처음 봄)

 

merge into costs C using (select * from products where prod_category='Photo') P

on (C.prod_id=P.prod_id)

when matched then

  update 

  delete

when not matched then

  insert

  values

 

Example 18. Using a Temporary Table

(a), (b), (c)에서 모두 sales, times를 공통적으로 사용 (s.time_id = t.time_id)

create GLOBAL TEMPORARY TABLE sales_friday .. on commit preserve rows as ...

임시 테이블: 메모리에 만들어지는 테이블, 저 조건을 사용하면 commit 후에 데이터 없어질 수도 있음

일종의 뷰의 형태인데, 뷰는 select문장을 가지는 객체지 데이터를 가지는 객체는 아님, temp table은 ... 뭐래는거야 ㅋㅋㅋㅋㅋ 테이블 구조를 가져간다는게.... -_- 데이터를 만들어놓고 쓰는 방식이래. 오 그게 다르군. 뷰는 사용할 때 select문이 수행되는거고, 템프 테이블은 데이터를 저장해두는 거고... 인덱스 사용 가능하대.

※ 관련 문서: https://goddaehee.tistory.com/165

※ 관련 문서: https://www.oracletutorial.com/oracle-basics/oracle-global-temporary-table/

 

Example 19. Using the WITH Clause

WITH 임시 테이블의 일종, 테이블 구조를 가지고 데이터를 저장

임시 테이블은 drop 하기 전까지 구조는 남아 있다 (데이터는 사라짐)

view는 select 문장 안에서만 사용하고 반복되어서 사용하는 경우에 좋음 굳

유의할 점: 같은 시간에 여러 with절을 사용하게 되면 임시 테이블 공간을 다같이 사용하므로 성능 저하 발생

view -- with절 -- 임시 테이블 둘의 중간 어디 즈음

SQL문이 심플해짐실행 계획 측면에서는 반복되는 조인을 with절로 만들어두면 효율적임※ 관련 문서: https://superkong1.tistory.com/35

 

Example 20. Partition Pruning결합 인덱스(전체 테이블이 아니면서) 데이터 조건 범위가 넒은 경우 인덱스가 있더라도 full table scanDB_FILE_MULTIBLOCK_READ_COUNTfull table scan하는 경우 저 파라미터 값을 사용하여 여러 block을 읽는다 (그래서 full table scan이 항상 나쁜건 아님)이런 경우 파티션 테이블을 만들어보렴보통 파티션 테이블에 대해 병렬 처리를 많이 한다 (오홍)각 파티션을 동시에 읽게 하는거지 (오홍 빠름)

 

Example 21. Using a Bind Variable실행 계획이 공유되므로 hard parse 최소화조건에 따라 별도 실행 계획이 필요할 수도 있음 즉 데이터 분포가 비슷한 경우면 실행 계획이 동일하니까 좋아상황에 따라 장단장단 그래서 오라클은 아래 기능을 만들었긔bind variable peeking (9i) 옵티마이저가 판단하여 바인드 변수 사용 시에도 hard parse이 일어날 수도 있음

Adaptive Cursor Sharing (11g) 기술이 더 좋아짐

 

Example 22. NULL Usage

B-tree index는 NULL을 저장하지 않음

Null 조건을 검색하면 인덱스 힌트를 주더라도 인덱스 사용하지 않음

 

bitmap index는 NULL을 저장함

관리가 어려움 (insert, update, delete 작업이 느려)

변경이 거의 없는 즉 insert,update,delete 하지 않고 select만 하는 경우에는 bitmap index가 좋대

low cardinality (남/여)에도 좋대

 

Example 23. Tuning a Star Query by Using the Join Operationfact table : 여러 dimension 테이블로부터 데이터가 만들어지는 형태, FK가 많음 dimension table : 기준 정보 테이블sales : 이력 테이블 / products : 기준 정보 테이블 이래

case 1) 검색하는 데이터 범위가 좁은 경우

· original

  select ... from sales s where ...

  table full scan

· 인덱스 힌트

  index skip scan

· Join 

  select ... from products p, sales s where ...

  dimension table을 driving table로 사용하는 것 → 검색하는 데이터 범위를 좁힌당

· Join + ordered use_nl(p s) 힌트 

  데이터 범위가 좁은 경우엔 위 방법이 유리햐

case 2) 검색하는 데이터 범위가 넓은 경우

· original

  table full scan

· 인덱스 힌트

  table full scan

· Join 

  select ... from products p, sales s where ...

  dimension table을 driving table로 사용하는 것 → 검색하는 데이터 범위를 좁힌당

· Join + ordered use_nl(p s) 힌트

  데이터 범위가 넓은 경우에는 nested loops join 성능이 좋지 않음

  엄청 느려짐 ㅋㅋㅋㅋ

 

Example 24. Creating a New Index

결합 인덱스가 있지만 table full scan 하고 있음왜? unit_cost 컬럼을 조회하기 때문. 결합 인덱스에 포함되지 않은 컬럼을 조회하고 있음.그치만 동일 컬럼들에 대해 여러 인덱스를 만드는 건 유의해야 한다. 한정된 리소스 내에서 효율적으로 써야 하니까용?

 

Example 25. Join Column and Index조인 컬럼에 대한 인덱스가 있을 때 옵티마이저는?sales은 partition access, customers를 table full scan, 두 테이블을 hash join (메모리에서 조인 작업, 검색하는 범위가 넓은 경우에 많이 쓴대, 데이터 양이 많은 경우, 작은 테이블에서도 검색 범위가 넓은 경우) ... 인덱스 사용 안함그럼 인덱스 사용하지 않는게 유리할까? 옵티마이저가 항상 답은 아니얌. 테스트가 필요해.hash join  sales을 table full scan, customers도 table full scannested loop  인덱스 힌트 주면 보통 nested loop 조인으로 풀릴거래, 그리고 인덱스 사용하는 테이블을 driving table로 사용할거야  sales을 sales_cust_id로 index full scan, customers도 table full scan해서 nested loop  인덱스를 강제로 사용하면서 nested loop로 바뀐거야  수행 시간이 짧아짐  아래 두 줄은 파티션 푸르닝? ㅇㅅㅇ?hash join  cust_year_of_birth 조건 컬럼에 인덱스 만들었듬 (검색 범위가 좁은 경우엔 좋은 선택)  sales는 table full scan, customers는 index range scan해서, hash join