SQLP 6 - SQL Tunning

parse는 soft parse와 hard parse가 존재

1. syntax(필수)

2. semantic & privilege 체크는 항상 sql 문장에 대해서 진행한다.(필수)

--- 내 sql 문장이 이미 만들어져 있는지 체크하면 soft parse 아니여서 3단계 진행하면 hard parse

3. p_code 만들고 실행계획까지 

 

* 가급저 soft parse를 하기를 원한다. 그래서 sql도 많이 공유하는 것이다.

 

튜닝은 두 가지

1. db 서버에 대한 튜닝

2. sql 문장에 대한 튜닝

-> sql 문장이 잘 짜 있다고 가정하고 하는 것(적당한 블록을 이용)

 

인덱스가 사용되도록 Where 절 기술하기 => 검색을 보다 빠르게.

optimizer 종류에 따라 실행계획을 만드는 방법이 완전히 달라진다.

 

쿼리변형은 내가 짠 쿼리도 다른 문장으로 쿼리변형이 된다.

DB 구조를 변경하지 못하면 DB 구조에 맞는 SQL 작성하기

 

적은 DB Block으로 속도 빠르게

Index로 속도 빠르게

Sort를 인덱스로 검색해서 속도 빠르게

 

index 한 블록 읽고 바로 return 해주면서 테이블 데이터 내보내주는 작업하기.

정렬을 index를 이용해서 속도가 빠르다고 느끼게 만들기

 

OR 논리합의 사용을 최소화 => or을 이용하면 인덱스를 사용하지 않을 가능성이 높다. full table을 사용할 가능성이 높다.

 

단순한 존재의 확인은 rownum = 1 을 사용하자. rownum이라는 칼럼은 존재하지 않지만 데이터는 보이는데 이걸 pseudo column이라고 부른다. rownum이 where 절에서 사용될때 사용할 수 있는 비교 연산자(<=, <, =) 만 존재하는데 이것 외에 사용하면 데이터의 값이 리턴되지 않는다. 

데이터를 읽을 때에는 우리가 쓴 방식과 상관없이 행 전체를 일단 다 읽는다. 결과로 output을 반환해주는 작업을 한다.

rownum이 쓰이면 from -> where -> select 순으로 읽는다. 이때 rownum은 일종의 stop key의 역활을 하게 된다. 테이블 데이터를 딱 정해진 위치까지만 읽고 더 이상 읽지 않겠다는 의미가 된다.

 

where 조건 조건에서 rownum = 을 사용한다면 올 수 있는 상수는 1 밖에 없다. 왜냐하면 출력하는 순서 기준이다보니 2부터는 내보낼려고 해도 기준에 안 맞는다.

 

그래서 rownum은 데이터가 많을때 데이터 하나만을 빠르게 뽑아내 다음 쿼리를 실행하기에 좋다.

결과치 한 건만을 보기 위해서 사용하는 테이블 == DUAL Table, 데이터 한 건만 가지고 있는 테이블

select 절에 쓰여진 칼럼들이 테이블에 존재하는 칼럼이 아닌 명시적인 우리가 제공해준 데이터를 활용한 결과치 한 건만 리턴 받으라고 쓰여진다. 하지만 그렇다고 불필요한 DUAL Table 사용은 하지 말자

 

중복된 데이터를 보아도 상관없다면 굳이 DISTINCT를 쓰지 말자

 

HINT

`--+ hint`

`/*+*/`

hint를 지정해주면 hint 대로 실행계획을 짜준다. 단 하드코딩이기 때문에 sql을 하드코딩 해주어야 한다.

 

데이터를 조인할때 정렬되어서 나오는 것을 확인할 수 있다.

 

소량의 데이터는 Nested Loop Join 이용하기 => 오라클 optimizer가 알아서 적용해준다.

행의 건수가 적은경우 내부적으로 데이터 조회에는 내부적으로 Nested Loop Join을 사용한다.

 

많은 데이터는 Hash Join으로 데이터를 조회한다. 단 많고 적고의 기준은 이후의 비용을 계산하기 때문에 그걸보고 Nested와 Hash 중에 선택하면 된다. 많은 양의 데이터를 조회하는 작업을 수행하면서 '='의 경우 Hash Join을 사용한다.

 

'=' 이 아니라면 Sort Merge Join을 이용한다. 100% 정렬된 순서로 반환된다.

 

sub query 작성시 IN 보다 EXISTS가 유리하다. 요즘은 둘이 비슷하다.

 

from 절에 서브 쿼리를 여러번 사용하기 위해 view, with라는 것을 이요하면 해결할 수 있다. 바깥쪽 메인 쿼리에서 round(avg(salary))와 같은 특수문자가 있는것을 쓸 수가 없다.

 

 

from 절의 서브 쿼리를 간단하게 쓰기 위해서 view라는 객체를 사용한다.

위와같이 create or replace view 명령어로 서브쿼리를 우선 제작해준다.

 

view 를 이용하는 방법 이외에도 with를 사용하는 방법도 존재한다.

 

하지만 이 세 가지 방법 모두 성능적으로 큰 차이는 없다. with의 성능이 좋아지는 경우가 있는데 반복적으로 select 문장에서 쓰여지면 with 절이 쓰여지는게 좋다. 

 

많은 데이터를 분석하는 data warehousing의 경우에 이걸 많이 사용하게 된다.

 

???

 

Sub Query 작성시 IN 보다는 EXISTS가 유리

 

simple 쿼리에서 쓸떼없이 distinct 키워드 사용금지

in과 exists는 상호 연동된다. exists는 다른 비교 연산자와 다르게 exists는 왼쪽에 테이블 칼럼 안 온다. sub query밖에 오지 못 한다. exists는 상관의 형태로만 사용할 수 있다.

비상관 서브쿼리는 서브쿼리가 독자적으로 실행이 되어서 비교연산자 우측에 값으로 제공될 수 있다는 것이다.

 

상관 서브쿼리는 이와 다르다. 바깥쪽 서브쿼리가 영향을 미치어서 처음부터 시작할 수 없는 것이 상관서브쿼리이다.

 

Exists 서브 쿼리는 내가 메인 쿼리에서 읽었던 행의 데이터 값이 서브 쿼리쪽에 존재하는지 체크해주는 비교 연산자로 상관의 형태로 쓰여질 수 있다. Exists는 내가 메인 쿼리에서 읽은 데이터 값이 서브 쿼리에 한 건이라도 존재하는지 체크하고 정확히 찾아내면 그 자리에서 스탑한다.

 

존재 유무를 체크해주다보니 상관의 형태로 쓰여질 수 밖에 없다.

하지만 not in과 not exists는 호환되지 않는다. 이유는 not in에서 주의해야하는 점 때문인데 이 서브쿼리에서 null 값이 아닌 것만 return 할 수 있도록 해야만 값이 나온다.

 

 

[ Union 보다는 Union All을 사용하여 sort를 방지 ]

AUB = union

AnB = intersecs

A-B = minus

union all은 중복된 데이터 그대로 반환해주는 합집합은 union all이다. 

 

union all 을 쓰면 select가 쓰여진 순서대로 output을 내며 결과도 중복된다.

 

하지만 union은 정렬과 중복제거까지 되어 있다.

 

[ 가능하면 sub query 대신 join을 사용 ]

???

특히나 상관 서브 쿼리에서는 join을 사용하는게 더 좋다.

 

[ Select 절에서 Function의 사용을 최소화 ]

Select 절에서 함수를 사용하면 이걸 해석하기 위해서 시간이 오래걸리기 때문에 함수를 덜 쓰는 것이 좋다.

 

case와 decode 함수를 사용하면 리포트를 만들 수 있다. decode와 case는 성능 차이가 없다.

 

상품 sum 자료 리포트를 뽑아내기 위한 쿼리다(20010은 오타이니 2010으로 바꾸자)

 

 

 

[ Outer Join 사용시는 + 사용을 주의 ]

 

규칙기반은 별로 효과적이지 못해서 Cost Based Optimizer만 사용한다. 쿼리에 따라 다른 비용 계산이 달라지기 때문에 상황에 잘 적용해서 데이터를 처리할 수 있다. 

CBO도 업무에 따라 optimizer을 바꾸어야 한다. all_rows(기본), first_rows(정렬된 첫번째 data가 나에게 return되기까지 쓰여지는 것)

 

실행계획 성능 확인하기 위한 방법

 

[ Explain Plan For ]

Parse만 한다. 실행은 안 한다.

위와같이 explain plan for을 앞에 붙여주면 해석이 된다.

 

`set autot on`

 

이렇게 해도 되고 dbeaver에서도 실행결과 확인이 있으니 그걸써도 되고

 

`set autot traceonly explain` -> 실행계획과 통계만 보여줘(결과 말고)

 

 

여기에서 rownum이 pseudo col이다.

??? 붙이는 순서값이 rownum이다. 이것의 의미가 where 절에서 사용시에 '<=','<','='만 존재한다.

 

 

 

SELECT cust_city, cust_year_of_birth FROM CUSTOMERS ORDER BY CUST_CITY, CUST_YEAR_OF_BIRTH;

어디에서도 테이블에 가서 데이터를 읽어왔다는 실행계획이 없다.

 

SELECT cust_city, count(*) FROM CUSTOMERS c GROUP BY cust_city;

where 절을 쓰지 않았음에도 어디에서도 테이블 가서 데이터를 읽어왔음에 대한 실행계획이 없다.

 

 

dbeaver에 가서 확인하면 index가 어디에 매칭되어 있는지 확인할 수 있다.

 

set timing on

set linesize 250

set pagesize 500

col index_name for a20

col column_name for a20

 

SELECT index_name, column_name, column_position
FROM user_ind_columns
WHERE TABLE_NAME ='CUSTOMERS'
ORDER BY index_name;

인덱스가 어떻게 매핑되어 있는지 확인가능하다.

 

alter session set statistics_level=all;

sql 튜닝과 관련된 모든 정보를 저장하겠다는 의미이다.

 

SELECT /*+ INDEX(sales SALE S _TIME_IDX) */
prod_id, channel_id, sum(quantity_sold), sum(amount_sold)
FROM sales
WHERE time_id BETWEEN TO_DATE('2000/01/01', 'yyyy/mm/dd')
AND TO_DATE('2001/12/31', 'yyyy/mm/dd')
GROUP BY prod_id, channel_id;

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

이걸 실행시켜서 기록이 어떻게 되었는지 확인하자

 

실행결과를 확인할 수 있다.

 

select max(time_id), min(time_id)
from (select min(time_id) time_id
from sales
union all
select max(time_id)
from sales);


select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));