select empno,ename, sal
from emp
where empno=7788;
파싱(parsing) :
- 구문 분석: SQL 문법의 오류가 없는지 검사
- 의미 확인: emp 테이블이 db 에 존재하는 테이블인지 확인
- 권한 확인: emp 테이블을 select 할 수 있는 권한이 있는지 확인
parsing 후 parsing 한 sql을 오라클 메모리인 Shared pool 에 올림
올리는 이유는 다음번에 또 위의 sql을 실행하면, 파싱과정을 생략하기 위함
< soft parse : 파싱과정 생략되는 것 >
위와 똑같은 sql이 공유풀에 있으면 바로 실행하고
< hard parse >
없으면 파싱을 해서 공유풀에 올려놓습니다.
EXECUTE : 검색하고자 하는 데이터를 buffer cache 에서 찾고 없으면 data file에서 찾아서 복사본을 buffer cache 에 올려놓는다.
즉, 검색하고자 하는 데이터를 db에서 찾는 과정이 execute
찾을 때 오라클 메모리에 있는 buffer cache에서 먼저 찾고 없으면 data file에서 찾아서 복사본을 buffer cache에 올려놓음
FETCH : 찾은 데이터를 server process ---> user process
오라클 메모리 구조
오라클 DB 전체 구조
오라클 내부적으로 우리가 sql 실행문이 어떻게 작동하는지 알아보기
Client(User process 존재) : sql문 작성해서 날림
SERVER Process : Pasing(구문분석, 의미확인 권한검사)해서 공유풀에 올림
공유풀에 왜 올림?
parsing이 cpu를 많이 잡아먹으므로 다음에 또 파싱하지 않으려고
Memory
Shared Pool(공유풀) : Parsing 후 처음에 올려지는 곳(sql문장, parse tree가)
Buffer Cache — EXECUTE하는 부분
조회하는 데이터가 buffer cache에 있는지 찾음
없으면 그림 아래에 보이는 데이터베이스에서 찾아서 조회한 데이터를 buffer cache에 올려놓음
왜 올려놓음? 다음번에 이 데이터를 요청하면, 메모리에서 바로 찾으려고 올려놓음(ex. scott의 이름과 월급을 buffer cache에 올려놓음)
Log buffer
FETCH
result set 즉, 찾은 데이터를 server process ---> user process
실습 - SHARED POOL
실습1 ) 내가 지금 수행하는 sql문이 공유풀에 있는지 확인하는 방법?
#1. 다음 sql을 수행
select ename, sal from emp where empno =7788;
#2. 위의 sql이 공유풀에 있는지 확인
select sql_text
from v$sql--다이나믹 퍼포먼스 뷰where sql_text like'select ename,%'; --찾고자 하는 sql문 일부 넣기
SQL_TEXT
------------------------------------------------------------------select ename, sal from emp where empno =7788select ename, sal, pcg_rank.fun179(ename) from emp where pcg_rank.fun179(ename) ='고소득'select ename, sal, pcg_rank.fun179(ename) from emp where pcg_rank.fun179(ename) ='고소득'select ename, fun179(ename) from emp
select ename, domain(ename) from emp18
문제풀며 익히기
Q1. select를 할 때 select된 데이터를 왜 메모리에 올려두는가?
답: 빠르게 데이터를 검색하기 위해서 메모리에 올려두는 것
select-> DB에서 찾음 -> 결과를 MEMORY에 올려둠
왜 올려두는가?
예를 들어 모르는 단어를 백과사전에서 찾았다.
많은 단어들이 있으므로 찾는데 오랜시간이 걸렸음
그래서 찾아놓은 단어를 나만의 단어장에 따로 적어두는 개념과 비슷한것
그럼 빠르게 단어를 다시 찾을 수 있기 때문
답: 빠르게 데이터를 검색하기 위해서 메모리에 올려두는 것
Q2. 메모리에 올려주는 것이 무엇무엇 입니까?
수행한 SQL문장, 파싱된 결과(parse tree)———> Shared Pool Memory에 Load
결과 데이터———> DB Buffer cache Memory에 Load
1.**수행한 SQL문장, 파싱된 결과(parse tree)**—————> Shared Pool Memory에 Load
파싱된 결과의 기계어인 parse tree가 함께 shared pool에 올려짐
Share Pool 에 SQL문장을 올려두게 되면, 다음번에 Parsing을 생략할 수 있음
이는 soft parse라고 함 ( soft parse )
Q3.
다음번에 똑같은 sql문장이 들어와야 파싱과정을 생략할 수 있다. 똑같은 sql의 기준은 무엇인가?
답 :
대소문자 구분, 공백과 들여쓰기 구분, 리터럴(Literal) SQL 구분하므로 정확히 일치해야함.
따라서 개발자들에게 지침을 정해주어야함.
(ex. 소문자로만 작성하세요, 대문자로 작성하세요)
단, 리터럴은 DBA가 제어할 수 있는 부분임 (바인드 변수 사용)
--바인드 변수 선언
variable b number;
--바인드 변수를 활용해서 항상 같은 sql만들기select ename, sal
from emp
where empno = :b ;
--새로운 리터럴 값 바인딩exec :b :=7566;
===============================================================
이렇게 되면 파싱과정이 생략될 수 있음!
대소문자 구분
< 처음실행 >select ename, sal
from emp
where empno =7788;
< 두번째 실행 >SELECT ENAME, SAL
FROM EMP
WHERE EMPNO =7788;
두 SQL문은 다른 문장으로 인식되어 파싱과정이 생략되지 않음
공백과 들여쓰기를 구분
< 처음실행 >select ename, sal
from emp
where empno =7788;
< 두번째 실행 >select ename, sal
from emp
where empno =7788;
두 SQL문은 다른 문장으로 인식되어 파싱과정이 생략되지 않음
리터럴(Literal) SQL을 구분
< 처음실행 >select ename, sal
from emp
where empno =7788;
< 두번째 실행 >select ename, sal
from emp
where empno =7902;
뒤의 상수값이 달라지면 다른 SQL로 인식되어 파싱과정이 생략되지 않음
튜닝2. 옵티마이져가 뭔지 먼저 알아야해요-면접에서 자주 물어보는 질문 ★ ( 교재 3장. 옵티마이 소개)
💡
면접질문:
옵티마이져가 무엇인가요 ?
답변:
SQL을 가장 효율적이고 빠르게 수행할 수 있는 최적의 처리경로를
선택해주는 오라클 DBMS 의 핵심엔진입니다.
최적의 실행계획을 생성하는 오라클 DBMS의 핵심엔진입니다.
📖
▣ 옵티마이져가 필요한 이유 ?
Optimizer
옵티마이져가 실행계획 만드는 과정
과정 설명
SQL 실행
select ename, sal, job
from emp
where job in('SALSEMAN', 'ANALYST');
문법 받아서 PARSING (문법검사, 의미검사, 권한체크)
Opimaizer에 들어감—옵티마이져가 실행계획 만드는 단계
쿼리 TRANSFORMER : 쿼리문을 바꿔줌
좀 더 효율적인 쿼리문으로 바꿔줌
select ename, sal, job
from emp
where job ='SALSEMAN'OR
job ='ANALYST';
----------------------------------------
바뀌는 sql이 있고, 안바꾸는 sql이 있음
예시와 같은 경우는 바뀌는 경우임
==========================================** 왜 쿼리 트랜스포머가 쿼리문을 변경하는가? **
자기가 봤을 때 더 나은 쿼리문이라서
최적의 실행계획을 만들기 위해서 변경하는게 더 적당하기 때문
** 그럼 왜 우리는 쿼리트랜스포머를 알아야하는가? **
오라클 힌트가 안 먹히는 경우가 있음
그이유의 대부분은 Query의 변형이 내부적으로 발생했기 때문
Estimator
데이터 사전의 통계정보가 담긴 dictionary( Statistics )를 확인해서 통계정보가 존재하는지 확인
select ename, sal, job
from emp
where job in('SALSEMAN', 'ANALYST');
----------------------------------------------------
즉 job에 index여부와 emp테이블의 전체 건수를 확인해봐야함
테이블 건수를 확인할 수 있는 방법
= 테이블 통계정보(Statistics)확인
테이블의 정보도 담겨있음
=================================================** emp 테이블의 통계정보 확인 방법 **--테이블 통계정보 수집할 수 있게 emp 테이블 분석하라는 명령SQL> analyze table emp compute statistics;
테이블이 분석되었습니다.
--조회해보기select table_name, num_rows, avg_row_len, last_analyzed
from user_tables
where table_name ='EMP';
TABLE_NAME|NUM_ROWS|AVG_ROW_LEN|LAST_ANALYZED |----------+--------+-----------+-----------------------+
EMP |14|43|2023-12-2912:07:19.000|
num_rows : 테이블 건수
avg_row_len : 테이블의 가로사이즈 (BYTE)
user_tables : dictionary 즉 데이터 사전
즉
num_rows * avg_row_len = 테이블의 크기
14개의 행이 있으니까 14개만 만들면 되겠구나!
그 다음에 Estimator가 테이블 통계정보를 이용해서 emp테이블의 크기와 데이터 건수 등을 확인함. emp 테이블에 대해서 옵티마이져가 알고 있어야 좋은 실행계획을 만들 수 있기 때문
emp테이블의 통계정보 확인하여, Estimator는 Plan Generator가 plan을 만들 수 있게 해줌
Plan Generator
: Execution Plan
실습
Q1.
아래의 sql을 쿼리 변형기가 어떻게 변형했는지 확인하시오
select ename, sal, job
from emp
where job in('SALSEMAN', 'ANALYST');
explain plan for/////- 확인하고자 하는 sql문 넣기 ;
select*fromtable( dbms_xplan.display );
실제 실행계획이란 ?
sql을 실제로 실행할 때 사용한 실제 실행 계획
select/*+ gather_plan_statistics */ ename, sal
from emp
where sal =1300;
SELECT*FROMTABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
======================/*+ gather_plan_statistics */
: plan 통계정보를 모아서 결과를 보여주라는 힌트
실행계획 읽는 방법
실행계획을 읽을 때는 안쪽에 → 바깥으로 읽어가면 됨
예제로 확인하기
예제1)
아래의 실행획의 읽는 순서를 적으시오
3->2->5->4->1
예제2) - hash 조인
아래의 실행계획의 읽는 순서를 적으시오
2->4->3->1
예제3) -in line view를 포함하는 select문장
아래의 실행계획을 읽는 순서를 적으시오
5->4->3->2->6->1
인라인뷰를 읽고나서 item테이블을 full scan한 것
예제4) -서브쿼리를 뷰로 만들어 nested loop조인으로 쿼리 변형
아래의 실행계획을 읽는 순서를 적으시오
4->3->2->5->1
예제
예제1) 실행계획을 먼저 확인하자!
(단, 예상실행계획에는 BUFFER의 개수가 나오지 않음)
SQL>@demo-- 예제1. 예상 실행계획 확인하는 방법
explain plan forselect ename, sal
from emp
where sal =1300;
select*fromtable( dbms_xplan.display );
-- dbeaver에서 확인하는 방법select ename, sal
from emp
where sal =1300;
----------------------------------------------------
dbms_xplan : 오라클에서 미리 만들어놓은 내장 패키지
display : 함수
즉, dbms_xplan이라는 오라클 내장 패키지에 display라는 함수를
사용하고 테이블 바로 아래에 실행하게 되면
예상 실행계획이 나옴
DBeaver 플로그램에서 확인 방법(단축키 ctrl + shift + e )
예제2) 실제 실행계획 확인하는 방법
-- 예제2. 실제 실행계획 확인하는 방법select/*+ gather_plan_statistics */ ename, sal
from emp
where sal =1300;
SELECT*FROMTABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
SQL_ID 4xfutz2n62sa6, child number 0-------------------------------------select/*+ gather_plan_statistics */ ename, sal
from emp
where sal =1300
Plan hash value: 3956160932------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows| A-Rows| A-Time| Buffers |------------------------------------------------------------------------------------|0|SELECT STATEMENT ||1||1|00:00:00.01|7||*1|TABLE ACCESS FULL| EMP |1|1|1|00:00:00.01|7|------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------1-filter("SAL"=1300)
===================================================================
hint의 의미:
실행계획을 만드는 일을 하는 옵티마이져에게 이렇게 실행계획을 만들면
좋겠다고 명령을 하는 것
/*+ gather_plan_statistics */
: plan 통계정보를 모아서 결과를 보여주라는 힌트
E-Rows : 예상건수
A-Rows : 실제 건수
A-Time : SQL문 수행시간
Buffers : 오라클 메모리 BUFFER CACHE 메모리에서 읽은 BUFFER의 개수.
작을 수록 실행계획을 잘 짠것(?) SQL을 잘 만든것(?)
예제3) full table scan 일때 —실제 비교해보기
-- 예제3. full table scan 일때select/*+ gather_plan_statistics */ ename, sal
from emp
where sal =1300;
SELECT*FROMTABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
SQL_ID 4xfutz2n62sa6, child number 0-------------------------------------select/*+ gather_plan_statistics */ ename, sal
from emp
where sal =1300
Plan hash value: 3956160932------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows| A-Rows| A-Time| Buffers |------------------------------------------------------------------------------------|0|SELECT STATEMENT ||1||1|00:00:00.01|7||*1|TABLE ACCESS FULL| EMP |1|1|1|00:00:00.01|7|------------------------------------------------------------------------------------
- 결과 하나 보려고 BUFFER 7개 사용한 것
예제4) 위의 sql이 인덱스 스캔 일 때
create index emp_sal
on emp(sal);
select/*+ gather_plan_statistics index(emp emp_sal) */ ename, sal
--INDEX를 타라!([테이블명] [인덱스이름])from emp
where sal =1300;
SELECT*FROMTABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
SQL_ID 89vkprz7bmg9p, child number 0-------------------------------------select/*+ gather_plan_statistics index(emp emp_sal) */ ename, sal
from emp where sal =1300
Plan hash value: 1285969232---------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows| A-Rows| A-Time| Buffers |---------------------------------------------------------------------------------------------------------|0|SELECT STATEMENT ||1||1|00:00:00.01|2||1|TABLE ACCESS BY INDEX ROWID BATCHED| EMP |1|1|1|00:00:00.01|2||*2| INDEX RANGE SCAN | EMP_SAL |1|1|1|00:00:00.01|1|---------------------------------------------------------------------------------------------------------
튜닝을 했더니 BUFFER의 개수가 2개로 줄어들었음
문제풀며 익히기
Q1. 아래의 SQL을 인덱스 스캔으로 실행계획이 나오게 튜닝하시오
select empno,ename, sal, job
from emp
where empno =7788;
튜닝전:
select/*+ gather_plan_statistics */ empno,ename, sal, job
from emp
where empno =7788;
SELECT*FROMTABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
튜닝후:
/* 인덱스 생성 */createunique index emp_empno
on emp(empno);
--사원번호는 중복되지 않기 때문에 index걸 때 unique index를-- 걸어주는게 더 좋음/* 힌트 넣기 */select/*+ gather_plan_statistics */ empno,ename, sal, job
from emp
where empno =7788;
/* 실행계획 */SELECT*FROMTABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
/* 결과 */---------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows| A-Rows| A-Time| Buffers |---------------------------------------------------------------------------------------------------|0|SELECT STATEMENT ||1||1|00:00:00.01|2||1|TABLE ACCESS BY INDEX ROWID| EMP |1|1|1|00:00:00.01|2||*2| INDEX UNIQUE SCAN | EMP_EMPNO |1|1|1|00:00:00.01|1|---------------------------------------------------------------------------------------------------
튜닝4. 테이블 통계정보를 수집하세요( 교재 7장. 옵티마이저 통계)
📖
▣ 테이블 통계 정보란 ?
: 테이블의 데이터의 전체 건수 and 테이블 크기에 대한 정보 등을 말한다
▣ 통계 정보 수집 이유?
: 옵티마이저로 하여금 더 좋은 실행계획을 생성하게 하기 위함
주기적 통계정보 수집
dba는 평상시에 통계정보를 수집 해야하는데 보통 잘 안함.. 왜?
실행계획이 자꾸 바뀌게 되어서 느려져 계속 튜닝 해줘야함
그래서 튜닝에 자신있는 dba나 튜너들은 지속적으로 수집함
옵티마이져가 정확하게 좋은 실행계획을 주려면 주기적으로 통계정보 수집해주는게 좋음
통계정보 수집 방법
< 방법1>
analyze table [테이블명] compute statistics;
또는
< 방법2>// 오라클에서는 아래 방법을 더 권장 //exec dbms_stats.gather_table_stats('[유저명]','[테이블명]');
--유저이름(대문자), 테이블명(대문자)--스키마(유져) 단위로 수집 - 유져레벨로 통계정보 수집하는 방법exec dbms_stats.gather_schema_stats('[유저명]');
====================================
# 특정 테이블 통계정보 수집 여부 확인하는 방법
select table_name, last_analyzed
from user_tables --데이터 사전where table_name='EMP';
TABLE_NAME LAST_ANALYZED
----------- --------------
EMP <--- 옵티마이져 통계정보가 없음
TABLE_NAME|LAST_ANALYZED |----------+-----------------------+
EMP |2024-01-1116:31:34.000|<----- 통계정보가 수집되었을 때
없으면 좋은 실행계획을 만들어 낼 수가 없습니다.
emp 테이블에 대하여 옵티마이져 통계정보를 생성합니다.
-----------------------------------
# 유저레벨로 수집했을 때 유저 단위로 통계정보 수집 여부 확인하는 방법
select table_name, last_analyzed
from user_tables;
TABLE_NAME |LAST_ANALYZED |-----------------------+-----------------------+
ORDER2 |2024-01-0316:49:09.000|
ORDER3 |2024-01-0316:49:09.000|
CRIME_TIME2 |2024-01-0316:49:03.000|
((이하생략))
💡
통계정보의 종류 4가지
테이블 통계정보
인덱스 통계정보
컬럼 통계정보
시스템 통계정보
예제
예제1) 테이블 통계정보 수집하기
SQL>@DEMO
#1. 오래되거나 누락된 옵티마이져 통계
select ename, sal
from emp
where sal =1200;
유져 프로세서 ->SQL전달
-> 서버 프로세서 1. 파싱(실행계획 생성) 2. EXEXUTE 3.FETCH
EMP 통계정보가 있다면 참고해서 실행계획 생성
서버 프로세서가 위의 SQL을 파싱하고 실행계획을 생성하기 위해서
emp 테이블에 대한 정보를 확인하는데 emp 테이블에 대한 정보가
옵티마이져 통계정보입니다.
===============================================================================select table_name, last_analyzed
from user_tables
where table_name='EMP';
TABLE_NAME LAST_ANALYZED
-------------------- -------------------
EMP <--- 옵티마이져 통계정보가 없음
없으면 좋은 실행계획을 만들어 낼 수가 없습니다.
emp 테이블에 대하여 옵티마이져 통계정보를 생성합니다.
analyze table emp compute statistics;
또는
// 오라클에서는 아래 방법을 더 권장 //--스키마(유져) 단위로 수집 - 유져레벨로 통계정보 수집하는 방법exec dbms_stats.gather_table_stats('C##SCOTT','EMP');
--유저이름(대문자), 테이블명(대문자)select table_name, last_analyzed
from user_tables --데이터 사전where table_name='EMP';
TABLE_NAME LAST_ANALYZED
------------------------------ -------------------
EMP 23/12/29(시간 왜 안나오지?)
* 스키마(유져) 단위로 수집 - 유져레벨로 통계정보 수집하는 방법
SQL>exec dbms_stats.gather_schema_stats('C##SCOTT');
SCOTT 이 가지고 있는 모든 테이블에 대해서 통계정보를 수집
SCOTT이 가지고 있는 테이블들에 대해서 통계정보가 잘 수집되었는지 확인
select table_name, last_analyzed
from user_tables;
--NULL로 나오는 얘들은 TEMP_TABLE
문제풀며 익히기
Q1.
DEPT 테이블에 대하여 옵티마이져 통계정보를 수집하고 잘 수집되었는지
확인하시오
analyze table dept compute statistics;
또는
exec dbms_stats.gather_table_stats('C##SCOTT','DEPT');
select table_name, last_analyzed
from user_tables
where table_name='DEPT';
Q2.
HR 계정이 소유하고 있는 모든 테이블들에 대해서 통계정보를 수집하시오.
#1. HR 계정이 DB에 존재하는지 확인
select username
from dba_users
where username='HR';
#2. HR 계정의 모든 테이블들에 대해서 통계정보를 수집
exec dbms_stats.gather_schema_stats('HR');
#3.select table_name, last_analyzed
from user_tables;
튜닝5. 컬럼 단위로 통계정보를 수집할 수 도 있어요 (교재 7장. 옵티마이저 통계)
📖
💡
통계정보의 종류 4가지
테이블 통계정보
인덱스 통계정보
컬럼 통계정보
시스템 통계정보
예제1) 컬럼의 통계정보 수집방법
analyze table emp compute statistics for columns ename ;
--컬럼명
▣ 왜 컬럼 통계정보 수집이 필요한가 ?
:컬럼중에 유독 변경이 많은 컬럼이 있습니다.
예를 들어서 emp 테이블에서 다른 컬럼을 전혀 변경되는게 없는데
sal 이 자꾸 변경이 된다면
그러면 테이블 전체의 통계정보를 수집하기 보다는 특정 컬럼만
통계정보 수집을 해주는게 바람직합니다.
문제풀며 익히기
Q1.
emp 테이블에 sal 에 대해서 컬럼 통계정보를 수집하시오
analyze table emp compute statistics for columns sal;
Q2.
수집된 컬럼 통계정보를 확인하시오
select table_name, column_name, num_distinct, num_nulls,
low_value, high_value, density, num_buckets, last_analyzed
from dba_tab_col_statistics --수집된 컬럼 통계정보를 확인할 수 있는 where owner ='C##SCOTT'and table_name ='EMP'and column_name ='SAL';
// 결과확인
table_name, column_name, num_distinct, num_nulls, low_value, high_value, density, num_buckets, last_analyzed
EMP SAL 120 C209 C233 0.03571428571428571223/12/29
튜닝6. 인덱스 통계정보도 수집할 수 있어요( 교재 7장. 옵티마이저 통계)
📖
▣ 인덱스의 통계정보를 수집하는 이유 ?
: 테이블처럼 인덱스에 대한 자세한 분석정보를 옵티마이져가 알고 있어야 좋은 실행계획을 생성할 수 있기 때문
(인덱스는 만드는 순간 자동으로 통계정보가 수집 됨)
테이블 처럼 인덱스도 계속 변경작업이 일어납니다.
그래서 변경된 인덱스에 대한 정보를 옵티마이저에게 알려주기 위해 필요합니다.
# 따로 통계정보 수집하는 방법
exec dbms_stats.gather_index_stats('[유저명]','[인덱스명]');
-- 유저명과 인덱스명은 모두 대문자로 작성해줘야함
# 확인하기
select index_name, num_rows, last_analyzed
from user_indexes
where index_name='[인덱스명]';
-- 인덱스명 대문자로 작성하기
예제1) 인덱스 통계정보 수집하기
SQL>create index emp_sal on emp(sal);
--만드는 순간 자동으로 통계정보가 수집이 됨SQL>exec dbms_stats.gather_index_stats('C##SCOTT','EMP_SAL');
--따로 통계정보를 수집하고 싶을 때--자주 변경되는 컬럼의 인덱스를 따로 --통계정보 자주 수집해줘야 좋은 실행계획을 볼 수 있음SQL>select index_name, num_rows, last_analyzed
from user_indexes
where index_name='EMP_SAL';
LAST_ANALYZED의 값이 NULL이 아닌 경우 제대로 통계정보가 수집된 것
문제풀며 익히기
Q1.
사원 테이블에 직업에 인덱스를 생성하고 직업에 걸린 인덱스의 통계정보가
수집되었는지 확인하세요. (인덱스를 생성 하기만 해도 통계 정보가 수집 되었는지 확인하기. LAST_ANALYZED의 값이 NULL이 아닌 경우 제대로 통계 정보가 수집 된 것)
create index emp_job on emp(job);
col index_name for a10 -- INDEX_NAME 열의 열 제목이 최대 10자로 제한됩니다.select index_name, num_rows, last_analyzed
from user_indexes
where index_name='EMP_JOB'; --대문자
튜닝7. 시스템 통계정보도 수집할 수 있어요( 교재 7장. 옵티마이저 통계)
📖
▣ 시스템 통계정보를 수집하는 이유 ?
옵티마이져를 개발한 오라클 조지아주 개발실의 서버의 사양이 우리회사의 서버의 사양과 서로 다름.
조지아주 서버만큼 좋지 않은 우리회사 서버..
그리고 회사마다 또 서버 마다 서버의 사양이 다 다름
따라서 우리 회사의 서버 사양과 상황을 오라클에게 알려줄 필요가 있음
그럴 때 시스템 통계정보를 수집.
즉, 내가 사용하고 있는 DB의 상태와 DB가 설치되어 있는 서버의 상태를 오라클에게 알려주어, 옵티마이저가 더 좋은 실행계획을 만들어줄 수 있게 하기 위함
예제) 시스템 통계정보 수집 예제
-- 시스템 통계 정보 수집 (EX) 미리 아침 9:00 에 걸어두고 일 하다가 나중에 종료)BEGIN
DBMS_STATS.gather_system_stats('START');
END;
/-- 시스템 통계 정보 중간 수집 (실제로는 시작과 종료 사이의 작업 수행 후)BEGIN
DBMS_STATS.gather_system_stats('INTERVAL');
END;
/-- 시스템 통계 정보 종료BEGIN
DBMS_STATS.gather_system_stats('STOP');
END;
/-- 잘 수집되었는지 확인하는 쿼리select PNAME, PVAL2 from SYS.AUX_STATS$ where pname ='STATUS';
예제2) 데이터베이스의 모든 객체들에 대해서 전부 통계정보 수집
( 데이터 베이스의 모든 객체들에 대해서 전부 통계정보를 수집하는 것입니다.
이렇게 수집하게되면, 수집하는 동안 성능이 느려지므로 주의해서 수행해야합니다.)
따라서 DB도 느려지고 수집이 오래걸림.
즉, 주의해야할게 이렇게 수집하게되면, 수집하는 동안 성능이 느려지기 때문에
DB안쓰는 늦은 밤에 돌리는게 좋음
SQL>exec dbms_stats.gather_database_stats;
-- 중단하는 방법SQL> Ctrl + c
문제풀며 익히기
Q1.
시스템 통계정보를 수집하는 이유가 무엇인가요?
DB의 상태 뿐만 아니라 DB가 설치되어있는 서버의 상태를 오라클에게 알려주게되면
더 좋은 실행계획을 옵티마이져가 만들어주기 때문입니다.
튜닝8. 매일 밤 10시에 통계정보가 자동으로 수집되요( 교재 7장. 옵티마이저 통계 + 자세한 내용은 관리수업 교재에)
📖
▩ 밤 10시에 테이블 통계정보가 자동으로 수집되고 있는지 SQL로 확인하는 방법
단, 모든 테이블에 대해서 수집하는 것은 아니고, 가장 마지막 수집 이후 20%이상 변경된 테이블들에 대해서만 통계정보를 수집함
select w.window_name, w.repeat_interval, w.duration
, cast(w.last_start_date astimestampwithlocaltime zone) last_start_date
, cast(w.next_start_date astimestampwithlocaltime zone) next_start_date
from dba_scheduler_wingroup_members m
, dba_scheduler_windows w
where m.window_group_name ='MAINTENANCE_WINDOW_GROUP'and w.window_name = m.window_name;
컴퓨터를 끄고가면 LAST_START_DATE가 안나타남
월요일과 토요일만 6시에 돌게 됨 나머지는 22시
▣오라클에서 어떤 기능이 자동으로 수행되고 있는지 확인하시오 !
select client_name, status
from dba_autotask_client;
< 결과 >
auto optimizer stats collection ENABLED --통계정보 수집 기능
auto space advisor ENABLED --여유 공간 확보하기sql tuning advisor ENABLED
--오라클이 스스로 SQL튜닝 수행(그 날 하루에 수행되었던 악성SQL 10개만)/*
튜너가 있는 곳은 켜두는데(대표적으로 포스코는 켜둠), 보통은 안켜놓음
왜냐면 SQL TUNING ADVISOR가 이상하게 튜닝해서 악성SQL을 만들어버릴 수도..
다시 해결하는데 힘들기 때문
*/===============================================* 밤10시에 테이블 통계수집을 자동 수집되게하는것을 끄고 싶다.
(오라클 설치할때 꺼드릴까요? 하고 고객한테 꼭 물어봐야함)
begin
dbms_auto_task_admin.disable(
client_name =>'auto optimizer stats collection',
operation =>null,
window_name =>null );
end;
/select client_name, status
from dba_autotask_client;
문제풀며 익히기
Q1.
그럼 다시 밤 10시에 도는 통계정보 수집 기능을 켜시오
begin
dbms_auto_task_admin.enable(
client_name =>'auto optimizer stats collection',
operation =>null,
window_name =>null );
end;
/select client_name, status
from dba_autotask_client;
- SQL TRACE를 봐야 지금 이 SQL이 느린 이유가 이 SQL이 악성 SQL이기 때문인지 아니면 다른 시스템의 이유 때문인지 파악할 수 있음
-그리고 SQL TRACE를 생성하게 되면, 이 SQL이 느린 진짜이유를 알 수 있게 됨
즉, 오라클의 의사이며, 오라클을 진단할 수 있는 것
또한 어느 하나의 SQL을 튜닝하기 위해서도 SQL trace를 볼 필요가 있는데,
여러개의 SQL 중에서 가장 느린 SQL이 무엇인지 튜닝의 우선순위를 정하고자 할때도 SQL trace가 필요합니다.
sql튜너나 db엔지니어가 직접 튜닝해야할 sql들을 찾아야 할 때 필요하다.
결론
sql이 느린 이유를 알 수 있다(악성 sql 인건지, 시스템 상 문제인지)
하나의 sql을 튜닝하기 위해서도 필요
여러개의 sql중 가장 느린것을 찾아 튜닝의 우선순위를 정할 수 있다.
▩ SQL 트레이스 생성하기
#1. 시간 정보가 SQL트레이스에 포함될 수 있도록 설정
alter session set timed_statistics =true; --그래서 TRUE로 둠
#2. 생성되는 trace file의 제한을 주지 않게 설정
alter session set max_dump_file_size = unlimited; -- UNLIMITED로 제한 없게
#3.10046 트레이스 이벤트를 활성화 한다.(오라클 7.3 이상)
alter session set events '10046 trace name context forever, level 12';
--LEVEL 12까지 줄 수 있음. 큰 LEVEL을 줄수록 더 많은 정보가 수집됨
#4. 튜닝이 필요한 SQL을 실행합니다.(이 sql이 왜 느린지 원인파악 가능)
--즉 튜닝이 필요한 sql을 이때 실행해야함select/*+ full(emp) */ ename, sal
from emp
where ename='SCOTT';
--emp table full scan하기 위해 일부러 힌트 넣어둔 것
ENAME SAL
-------------------- ----------
SCOTT 3000
#5. 트레이스를 중단한다
alter session set events '10046 trace name context off'; --off
#6. 트레이스 설정이후 생성된 트레이스 확인하는 방법
SQL>showparameter user_dump_dest --생성된 trace파일의 위치가 나옴
NAME TYPE VALUE----------------- ------------- ------------------------------
user_dump_dest string C:\APP\ITWILL\PRODUCT\21C\HOMES\ORADB21HOME1\RDBMS\TRACE
-- 위치 : C:\APP\ITWILL\PRODUCT\21C\HOMES\ORADB21HOME1\RDBMS\TRACE/* 아래꺼는 오류남..
또는
SQL> select * from v$parameter name='user_dump_dest';
--생성된 trace파일의 위치가 나옴
cd /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace
ls -lrt
orcl2_ora_19218.trc */
#7. 윈도우 탐색기를 열고, 위의 위치로 이동 후 날짜순으로 정렬하고
--맨 마지막에 생성된 trace file열기
악성 sql이 들어있음. 알아보기 힘들게
파일이름(내 기준) : xe_ora_11232.trc
#8. 관련 트레이스 파일을 보기쉽게 보는 방법
SQL> exit;
Oracle Database 21c Express Edition Release21.0.0.0.0- Production
Version 21.3.0.0.0에서 분리되었습니다.
C:\Users\itwill>
$ tkprof explain=c##scott/tiger sys=no/*
sys=no를 써야 오라클 sys유저가 수행한 여러 sql에 대한 정보가
나오지 않게 할 수 있음 */
trace= C:\APP\ITWILL\PRODUCT\21C\HOMES\ORADB21HOME1\RDBMS\TRACE\xe_ora_11232.trc
result= result01.txt --result결과 파일명은 마음대로/* trace = [파일위치]\[파일명] */
프롬프트 보면 itwill아래에 생겼다는 걸 알수있다.
▣ SQL TRACE LEVEL
Call Statistics : 기본정보
Bind : 바인드 정보
기본적으로 12로 주는게 좋음
문제풀며 익히기
Q1.
위와같이 SQL trace 를 뜨는것을 빠르고 쉽게 수행할 수 있도록 스크립트를
이용해서 수행하시오
trace_on.sql
trace_off.sql
trace_file.sql
tkprof.sql
실행순서:
@trace_on.sqlselect/*+ full(emp) */ ename, sal
from emp
where sal =3000;
@trace_off.sql@trace_file.sql@tkporf
PART2. Trace File 생성
튜닝9. SQL trace 를 생성할 줄 알아야해요( 교재 10장. 응용프로그램 추적)-스크립트 생성 포함
📖
▣ SQL 트레이스
-SQL튜닝을 위한 모든 정보가 들어있는 TRACE FILE
필요한 이유
SQL TRACE를 봐야 지금 이 SQL이 느린 이유가 이 SQL이 악성 SQL이기 때문인지 아니면 다른 시스템의 이유 때문인지 파악할 수 있음
그리고 SQL TRACE를 생성하게 되면, 이 SQL이 느린 진짜이유를 알 수 있게 됨
즉, 오라클의 의사이며, 오라클을 진단할 수 있는 것
어느 하나의 sql을 튜닝하기 위해서도 sql trace를 볼 필요가 있는데, 여러개의 sql 중에서 가장 느린 sql이 무엇인지 튜닝의 우선순위를 정하고자 할때도 sql trace가 필요
sql 튜닝을 개발자가 직접 의뢰 할 수도 있는데, sql튜너나 db엔지니어가 직접 튜닝해야할 sql들을 찾아야함
▣ SQL TRACE LEVEL
Call Statistics : 기본정보
Bind : 바인드 정보
기본적으로 12로 주는게 좋음
▩ SQL 트레이스 생성위치 변경하기
#0. 생성 위치 확인 방법
showparameter user_dump_dest
#1. SYS 유저로 접속하기
sqlplus sys/oracle_4U as sysdba
#2. user_dump_dest의 위치를 다른 위치로 변경(권한이 있는 디렉토리로 변경)
altersystemset
user_dump_dest='[바꿀경로]' scop=spfile;
아래와 같이 실행(diag아래로)
altersystemset
user_dump_dest='C:\app\itwill\product\21c\diag\rdbms\xe\xe\trace'scope=spfile;
#3. db를 내렸다 올리기 --dba만이 할 수 있음
startup force
#4. c##scott으로 변경
connect c##scott/tiger
#5. 아래 생성하기 3번부터 재실행
▩ SQL 트레이스 생성하기(c##scott에서 실행)
#1. 시간 정보가 SQL트레이스에 포함될 수 있도록 설정
alter session set timed_statistics =true; --그래서 TRUE로 둠
#2. 생성되는 trace file의 제한을 주지 않게 설정
alter session set max_dump_file_size = unlimited; -- UNLIMITED로 제한 없게
#3.10046 트레이스 이벤트를 활성화 한다.(오라클 7.3 이상)
--진짜 trace file을 생성하는 것은 이 단계부터임--활성화 한 순간 trace file이 생성되기 시작하는 것alter session set events '10046 trace name context forever, level 12';
--LEVEL 12까지 줄 수 있음. 큰 LEVEL을 줄수록 더 많은 정보가 수집됨
#4. 튜닝이 필요한 SQL을 실행합니다.(이 sql이 왜 느린지 원인파악 가능)
--즉 튜닝이 필요한 sql을 이때 실행해야함select/*+ full(emp) */ ename, sal
from emp
where ename='SCOTT';
--emp table full scan하기 위해 일부러 힌트 넣어둔 것
ENAME SAL
-------------------- ----------
SCOTT 3000select empno, ename, sal, job
from emp
where job ='SALESMAN';
select ename, job, mgr
from emp
where deptno =10;
#5. 트레이스를 중단한다
alter session set events '10046 trace name context off'; --off
#6. 트레이스 설정이후 생성된 트레이스 확인하는 방법
SQL>showparameter user_dump_dest --생성된 trace파일의 위치가 나옴
NAME TYPE VALUE----------------- ------------- ------------------------------
user_dump_dest string C:\APP\ITWILL\PRODUCT\21C\DIAG\RDBMS\XE\XE\TRACE
-- 위치 : C:\APP\ITWILL\PRODUCT\21C\DIAG\RDBMS\XE\XE\TRACE/* 아래꺼는 오류남..
또는
SQL> select * from v$parameter name='user_dump_dest';
--생성된 trace파일의 위치가 나옴
cd /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace
ls -lrt
orcl2_ora_19218.trc */
#7. 윈도우 탐색기를 열고, 위의 위치로 이동 후 날짜순으로 정렬하고
--맨 마지막에 생성된 trace file열기(emp테이블에 대한 select문이 들어있는)--악성 sql이 들어있음. 알아보기 힘들게
파일이름(내 기준) : xe_ora_10348.trc
#8. 관련 트레이스 파일을 보기쉽게 보는 방법
-- c드라이브에 trace라는 폴더를 만들고 해당 파일을 복사해 넣어둠SQL> exit;
Oracle Database 21c Express Edition Release21.0.0.0.0- Production
Version 21.3.0.0.0에서 분리되었습니다.
C:\Users\itwill>cd .. --cd(한 칸 띄고)..
C:\Users>cd ..
C:\>cd trace
C:\trace>dir
C 드라이브의 볼륨에는 이름이 없습니다.
볼륨 일련 번호: A4CD-4883
C:\trace 디렉터리
2024-01-02 오전 10:48<DIR> .
2024-01-02 오전 10:4240,021 xe_ora_10348.trc
1개 파일 40,021 바이트
1개 디렉터리 181,997,989,888 바이트 남음
C:\Users\itwill>
$ tkprof explain=c##scott/tiger sys=no--sys=no는 sys유저가 oracle유지하기 위해 수행한 수많은 sql이 있는데--그거 보지 않고 깔끔하게 scott이 한것만 보려고 설정한것/*
sys=no를 써야 오라클 sys유저가 수행한 여러 sql에 대한 정보가
나오지 않게 할 수 있음 */
trace= xe_ora_10348.trc
output= result02.txt --result결과 파일명은 마음대로/* trace = [파일위치]\[파일명]
혹은 미리 디렉토리로 이동 후 바로 파일명 입력 */
문제풀며 익히기
Q1. (빠르게 trace하기)
위와같이 SQL trace 를 뜨는것을 빠르고 쉽게 수행할 수 있도록 스크립트를
이용해서 수행하시오
반드시 c:\trace > 에서 sqlplus c##scott/tiger 에서 접속해야함!!
그래야 스크립틀를 쉽게 돌릴 수 있음
아래 4개의 스크립트를 미리 만들어 빠르게 trace하기 위함
아래의 파일들을 trace폴더에 이동
ed trace_on.sql
/* alter session set events '10046 trace name context forever, level 12'; */
ed trace_off.sql
/* alter session set events '10046 trace name context off'; */
ed trace_file.sql
/* 트레이스 파일이 생성된 위치를 빠르게 찾아주는 스크립트
select d.value ||'\'||p.value||'_ora_'||s.spid||'.trc' as trace_file_name
from ( select value
from v$parameter
where name='instance_name') p,
(
select value
from v$parameter
where name ='user_dump_dest' ) d,
(
select spid
from v$process
where addr = (
select paddr
from v$session
where sid = ( select sid
from v$mystat
where rownum=1)
)
) s ;
*/
tkprof.sql
/* ho tkprof &trace_file &1 sys=no */
ho : host의 약자.
tkprof를 수행하려면 ho를 붙여줘야함
실행순서:
@trace_on.sqlselect/*+ full(emp) */ ename, sal
from emp
where sal =3000;
@trace_off.sql@trace_file.sql@tkprof===============================================================
C:\trace>sqlplus c##scott/tiger
SQL*Plus: Release21.0.0.0.0- Production on 화 1월 211:05:382024
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
마지막 성공한 로그인 시간: 화 1월 02202410:52:45+09:00
다음에 접속됨:
Oracle Database 21c Express Edition Release21.0.0.0.0- Production
Version 21.3.0.0.0SQL>@trace_on
세션이 변경되었습니다.
SQL>select/*+ full(emp) */ ename, sal
2from emp
3where sal =3000;
ENAME SAL
-------------------- ----------
FORD 3000
SCOTT 3000SQL>@trace_off
세션이 변경되었습니다.
SQL>@trace_file
TRACE_FILE_NAME
--------------------------------------------------------------------------------
C:\APP\ITWILL\PRODUCT\21C\DIAG\RDBMS\XE\XE\TRACE\xe_ora_14440.trc
SQL>@tkprof
trace_file의 값을 입력하십시오: C:\APP\ITWILL\PRODUCT\21C\DIAG\RDBMS\XE\XE\TRACE\xe_ora_14440.trc
1의 값을 입력하십시오: result03.txt --output을 의미함. 반드시 확장자명 txt로!
TKPROF: Release21.0.0.0.0- Development on 화 1월 211:09:132024
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
Q2.
아래의 SQL을 SQL trace 를 생성하시오 ! 레포트 이름은 report07.txt로 하세요 !
select/*+ index(emp emp_sal) */ ename, sal
from emp
where sal =3000;
C:\trace>sqlplus c##scott/tiger
SQL*Plus: Release21.0.0.0.0- Production on 화 1월 211:13:182024
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
마지막 성공한 로그인 시간: 화 1월 02202411:13:00+09:00
다음에 접속됨:
Oracle Database 21c Express Edition Release21.0.0.0.0- Production
Version 21.3.0.0.0SQL>@trace_on
세션이 변경되었습니다.
SQL>select/*+ index(emp emp_sal) */ ename, sal
2from emp
3where sal =3000;
ENAME SAL
-------------------- ----------
FORD 3000
SCOTT 3000SQL>@trace_off
세션이 변경되었습니다.
SQL>@trace_file
TRACE_FILE_NAME
--------------------------------------------------------------------------------
C:\APP\ITWILL\PRODUCT\21C\DIAG\RDBMS\XE\XE\TRACE\xe_ora_7056.trc
SQL>@tkprof
trace_file의 값을 입력하십시오: C:\APP\ITWILL\PRODUCT\21C\DIAG\RDBMS\XE\XE\TRACE\xe_ora_7056.trc
1의 값을 입력하십시오: report07.txt
TKPROF: Release21.0.0.0.0- Development on 화 1월 211:14:112024
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
튜닝10. SQL trace 를 볼 줄 알아야해요
📖
보고서는 이렇게 메모장에 정리해서 메일로 보내주면 끝남튜닝 정보
튜 너 : 유연수 차장
튜닝 내용 : 8월 4일 16시 11분 부산지원 청구 1호기에서 DB FILE SCATTRED READ 대기 이벤트를 일으키면서 47초 이상 수행되는 SQL 발견하여 관련된 SQL 튜닝.(튜닝전 47초 --> 튜닝후 1초)
튜닝 전
☞ 문제의 SQL 을 수행한 프로그램 정보 및 DB 유져정보
DB SCHEMA : HIRA_LINK
OS USER : BWJF01
SYSTEM ID: ORA8
MACHINE : bonbu01
PROGRAM : @bonbu01 (TNS V1-V3)
☞ 튜닝전 SQL 과 TRACE 정보
SELECT "RECV_NO" ,
"RECV_YYYY" ,
"BRCH_CD" ,
"RECV_DATA_TYPE" ,
"YKIHO" ,
"DMD_TYPE_CD" ,
"PAY_SYS_TYPE" ,
"RECV_DT" ,
"EDPS_RECV_CLOS_YN" ,
"DIAG_YYYYMM" ,
"TOT_DMD_CNT" ,
"RETN_TYPE"
FROM "TBJFC02" "TBJFC02"
WHERE TO_NUMBER( "RECV_DT" ) >=20060724--index colum인 문자형 컬럼 recv_dt를 to_number로 가공해서 오래걸린것AND TO_NUMBER( "RECV_DT" ) <=20060805AND "RECV_DATA_TYPE" ='1'AND ( "DMD_TYPE_CD" ='2'OR "DMD_TYPE_CD" ='3' )
AND "PAY_SYS_TYPE" ='A'AND "RETN_TYPE" ISNULLAND "EDPS_RECV_CLOS_YN" ='Y'AND SUBSTR( "YKIHO" , 3 , 1 ) <>'9';
//sql trace 정보
call count cpu elapsed disk query currentrows mis Wait Ela
------- ------ -------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Parse 10.000.01000000.00Exec10.000.00000000.00Fetch35619.0047.647095875211638884046.52------- ------ -------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Total 35819.0047.657095875211638884046.52===================== 가장 중요한 정보 ★★★========================- elapsed time : 총 걸린 시간(서비스 시간) = cpu time+ wait time- wait time : 기다린 시간(ex. 맛집 대기 시간)
- cpu time-> elapsed - cpu time=28초 즉, wait time
왜 오래 기다리게 되었는지 이유가 아래 대기 이벤트(Event waited on)에 나옴
====================================================================//대기 이벤트
Event waited on Count Zero Ela Elapse AVG(Ela) MAX(Ela) Blocks
---------------------------------------- -------- -------- ---------- ---------- ---------- --------
db file sequential read 5014500.570.000.05501SQL*Net message to client 35700.000.000.000SQL*Net message from client 357011.550.030.220
db file scattered read 9847693733.820.000.0970457
file open900.000.000.000SQL*Net more data to client 22700.140.000.010
latch free2600.520.020.020===================== 가장 중요한 정보 ★★★========================- elapsed time과 Count가 가장 높은 것을 확인해보면 db file scattered read
- db file scattered read : 즉, Fulltable scan 해서 느려진것.
따라서 이를 해결해주면 됨. 아래 실행계획을 확인해도 fulltable scan함
====================================================================//실행계획
RowsRow Source Operation
---------- ---------------------------------------------------8884TABLE ACCESS FULL TBJFC02
튜닝 후
☞ 튜닝후 SQL 과 TRACE 정보
SELECT "RECV_NO" ,
"RECV_YYYY" ,
"BRCH_CD" ,
"RECV_DATA_TYPE" ,
"YKIHO" ,
"DMD_TYPE_CD" ,
"PAY_SYS_TYPE" ,
"RECV_DT" ,
"EDPS_RECV_CLOS_YN" ,
"DIAG_YYYYMM" ,
"TOT_DMD_CNT" ,
"RETN_TYPE"
FROM "TBJFC02" "TBJFC02"
WHERE "RECV_DT" >='20060724'--to_number를 빼고 그냥 싱글쿼테이션 마크로 둘러줌AND "RECV_DT" <='20060805'AND "RECV_DATA_TYPE" ='1'AND ( "DMD_TYPE_CD" ='2'OR "DMD_TYPE_CD" ='3' )
AND "PAY_SYS_TYPE" ='A'AND "RETN_TYPE" ISNULLAND "EDPS_RECV_CLOS_YN" ='Y'AND SUBSTR( "YKIHO" , 3 , 1 ) <>'9';
call count cpu elapsed disk query currentrows mis Wait Ela
------- ------ -------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Parse 10.010.04090010.00Exec10.000.00000000.00Fetch3560.751.0812484108884011.72------- ------ -------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Total 3580.761.1212485008884111.72
Event waited on Count Zero Ela Elapse AVG(Ela) MAX(Ela) Blocks
---------------------------------------- -------- -------- ---------- ---------- ---------- --------
db file sequential read 1290.040.000.0212global cache cr request 3600.110.000.010SQL*Net message to client 35700.010.000.010SQL*Net message from client 357011.530.030.380
file open300.000.000.000SQL*Net more data to client 23400.090.000.010
latch free200.040.020.020RowsRow Source Operation
---------- ---------------------------------------------------8884TABLE ACCESS BY INDEX ROWID TBJFC02
10716 INDEX RANGE SCAN
튜닝11. SQL trace를 편하게 분석할 수 있는 툴 존재(Lite plus)
📖
엑셈 라이트 플러스
엑셈 회사에서 만든 툴 중에 lite plus 가 존재함
이 툴을 이용하면, 편하게 SQL trace 를 분석할 수 있음
PART3. 튜닝 기법 및 옵티마이저
튜닝12. Full Table Scan 하지 말고 Index Scan 을 하세요
📖
Full Table Scan vs Index Scan
full table scan을 하게 되면, 테이블을 처음부터 끝까지 스캔하게됨
index range scan은 인덱스를 통해서 테이블 엑세스 하므로 빠르게 데이터를 엑세스 할 수 있음
예제1) emp테이블에 sal 에 인덱스를 생성하기
C:\trace> 에는 demo파일이 없기 때문에 새로 생성하기
C:\trace>sqlplus c##scott/tiger
SQL*Plus: Release21.0.0.0.0- Production on 화 1월 214:08:472024
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
마지막 성공한 로그인 시간: 화 1월 02202411:13:18+09:00
다음에 접속됨:
Oracle Database 21c Express Edition Release21.0.0.0.0- Production
Version 21.3.0.0.0SQL> ed demo.sql
SQL>@demo
예제2) emp테이블에 sal 에 인덱스를 생성하기
SQL>create index emp_sal on emp(sal);
인덱스가 생성되었습니다.
SQL>select sal, rowid
2from emp
3where sal>0; -- rowid 검색 시 index 컬럼이 조건으로 들어가야하던가..? 확인해보쟝
SAL ROWID
---------- ------------------800 AAATc7AAHAAAT/jAAK
950 AAATc7AAHAAAT/jAAH
1100 AAATc7AAHAAAT/jAAM
1250 AAATc7AAHAAAT/jAAE
1250 AAATc7AAHAAAT/jAAI
1300 AAATc7AAHAAAT/jAAN
1500 AAATc7AAHAAAT/jAAG
1600 AAATc7AAHAAAT/jAAF
2450 AAATc7AAHAAAT/jAAC
2850 AAATc7AAHAAAT/jAAB
2975 AAATc7AAHAAAT/jAAD
SAL ROWID
---------- ------------------3000 AAATc7AAHAAAT/jAAJ
3000 AAATc7AAHAAAT/jAAL
5000 AAATc7AAHAAAT/jAAA
===============================================
index는 컬럼값과 rowid로 구성되어있음
예제3)
아래의 sql이 frull table scan 을 했을 때와 인덱스 스캔을 했을 때의 차이를 이해하시오
select ename, sal
from emp
where sal =3000;
--------------------------------<fulltable scan >
emp 테이블의 sal컬럼 전체를 읽음
< index scan >
인덱스에서 3000을 바로 찾아서 rowid를 읽고
emp테이블에서 rowid 매칭해서 찾음.
찾은 후 인덱스에서 바로 아래꺼 까지 읽고 종료
(그림 기준으로 보면, 인덱스 테이블에서 5000도 읽고 종료)
예제4) 아래의 sql이 인덱스를 엑세스 할 수 있도록 힌트를 주고 실행하시오.
select ename, sal
from emp
where sal =3000;
# 인덱스 강제로 타게하는 힌트 사용
select/*+ index(emp emp_sal) */ ename, sal
from emp
where sal =3000;
----------------------------< lite plus 사용 >sql쿼리 넣은 후 Fn + F4를 누르면,
관련된 테이블의 구조와 인덱스 정보가 출력됨
즉, 테이블의 인덱스 정보를 바로 확인 가능
----------------------------< DBeaver 사용 시 >
테이블명에 마우스 커서를 두고 F4를 누르면,
테이블 description 과 인덱스 정보 확인 가능
ctrl + shift + e 를 누르면, 실행계획 확인 가능
예제5) 위의 sql이 인덱스 스캔이 아니라 full table scan하게 하시오
select/*+ full(emp) */ ename, sal
from emp
where sal =3000;
문제풀며 익히기
Q1. (문자형 컬럼의 인덱스) - 추가적인 설명은 링크 참조
아래의 sql인덱스 range 스캔을 할 수 있도록 인덱스를 걸고 힌트를 주시오
//ename index생성
create index emp_ename on emp(ename);
//select문에 힌트 넣기
select/*+ index(emp emp_ename) +*/ ename, sal, job
from emp
where ename ='SCOTT';
-------------------------------
a,b,c,d 순으로 정렬되어있으니까 바로 scott을 찾아서 rowid를 가지고
테이블 access해서 값을 찾음
smith까지 읽고 끝남
튜닝13. Where 절에 인덱스 컬럼을 가공하지 마세요!
📖
Where 절에 인덱스 컬럼을 가공하지 마세요!
검색하는 select문의 where절에 인덱스 컬럼을 가공하면, 인덱스를 엑세스 하지 못하게 됨
예제) 아래의 sql을 튜닝하시오
@democreate index emp_sal on emp(sal);
< 튜닝 전 >select ename, sal
from emp
where sal *12=36000;
인덱스 컬럼을 *12로 가공했기 때문에 fulltable scan하게 됨
< 튜닝 후 >select ename, sal
from emp
where sal =36000/12;
문제풀며 익히기
Q1.
아래의 SQL을 튜닝하시오
@democreate index emp_job on emp(job);
<튜닝전>select ename, sal, job
from emp
where substr(job, 1, 5) ='SALES';
<튜닝 후>select ename, sal, job
from emp
where job like'SALES%';
Q2.
위의 튜닝 전과 튜닝 후 SQL을 실제 실행계획으로 보면서 BUFFER의 개수를 확인하시오
< 튜닝후 >select/*+ gather_plan_statistics */ ename, hiredate
from emp
where hiredate between to_date('1980/01/01', 'RRRR/MM/DD')
and to_date('1980/12/31', 'RRRR/MM/DD');
@p/*
Plan hash value: 3234534543
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 |
|* 1 | FILTER | | 1 | | 1 |00:00:00.01 | 3 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 3 | INDEX RANGE SCAN | EMP_HIREDATE | 1 | 1 | 1 |00:00:00.01 | 2 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_DATE('1980/12/31','RRRR/MM/DD')>=TO_DATE('1980/01/01','RRRR/MM/DD'))
3 - access("HIREDATE">=TO_DATE('1980/01/01','RRRR/MM/DD') AND
"HIREDATE"<=TO_DATE('1980/12/31','RRRR/MM/DD')) */===============================================================================filter-81년 1월 1일보다 크면서, 80년 12월 31일보다 작을것을 찾았다는 것
문제풀며 익히기
Q1.
위의 sql은 between .. and를 사용하여 검색하는 sql로 데이터 검색이 1980년 1월 1일 00시 00분 00초 ~ 1980년 12월 31일 00시 00분 00초까지만 검색하는 것이기 때문에,
1980년 12월 31일에 입사한 사원들은 검색되지 않음. 그래서 12월 31일에 입사한 사원도 검색되게 sql을 고치시오
select/*+ gather_plan_statistics */ ename, hiredate
from emp
where hiredate between to_date('1980/01/01', 'RRRR/MM/DD')
and to_date('1980/12/31', 'RRRR/MM/DD') +1;
ENAME HIREDATE
-------------------- --------
SMITH 80/12/09
튜닝15. Where절에서 컬럼을 연결하지 마세요!(연결연산자)- 실행계획 buffer자동으로 보는 스크립트
📖
Where절에서 컬럼을 연결하지 마세요!
단일 컬럼 인덱스가 걸려있는 컬럼들을 서로 연결해서 데이터를 검색하게 되면, full table scan 을 하게 됨
단일 컬럼 인덱스? : 인덱스를 구성할 때 컬럼이 1개인 인덱스를 의미
create index emp_ename
on emp(ename);
튜닝 전 환경구성
</*+ gather_plan_statistics */ 자동으로 쓰게하기 >showparameter statistics_level
-- statistics_level이라는 parameter를 all로 바꿔줘야함altersystemset statistics_level=allscope=both;
설명 : 이렇게 설정하면 앞으로 /*+ gather_plan_statistics */ 힌트를
사용하지 않아도 됨(both로 넣었기 때문. spfile은 세션 종료시 사라짐)
gather_plan_statistics안쓰면 buffer안나옴
예제1)
@democreate index emp_ename on emp(ename);
create index emp_sal on emp(sal);
<튜닝전>select/*+ gather_plan_statistics */ ename, sal, job
from emp
where ename || sal ='SCOTT3000';
/* 버퍼의 개수 8개, full scan
Plan hash value: 3956160932
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 8 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 1 | 1 |00:00:00.01 | 8 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ENAME"||TO_CHAR("SAL")='SCOTT3000') */
< 튜닝 후 >select/*+ gather_plan_statistics */ ename, sal, job
from emp
where ename ='SCOTT'and sal =3000;
/* 고유값만 있는 ename의 index를 타고(그걸 더 선호)
index range scan함. 버퍼의 개수 3개
Plan hash value: 2855689319
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 2 | INDEX RANGE SCAN | EMP_ENAME | 1 | 1 | 1 |00:00:00.01 | 2 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SAL"=3000)
2 - access("ENAME"='SCOTT') */
문제풀며 익히기
Q1. 아래의 sql을 튜닝하시오
@democreate index emp_job on emp(job);
create index emp_deptno on emp(deptno);
--------------------------------------------------------</*+ gather_plan_statistics */ 자동으로 쓰게하기 >showparameter statistics_level
-- statistics_level이라는 parameter를 all로 바꿔ㅓ줘야함altersystemset statistics_level=allscope=both;
설명 : 이렇게 설정하면 앞으로 /*+ gather_plan_statistics */ 힌트를
사용하지 앟아도 됨
<튜닝전>select ename, sal
from emp
where sal like'30%';
ENAME SAL
-------------------- ----------
FORD 3000
SCOTT 3000@p/* full scan
Plan hash value: 3956160932
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.01 | 8 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 2 | 2 |00:00:00.01 | 8 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_CHAR("SAL") LIKE '30%') */======================================================================
가공한것도 없는데 왜 full scan?
sal like'30%'
위에서 sal = 숫자형, '30%'는 문자형인데, %때문에 숫자형으로 변환할 수 없어서
어쩔 수 없이 sal을 문자형으로 암시적 형변환해버림 즉, 가공해버림
filter부분을 보면 to_char한것을 확인할 수 있음
<튜닝 후 : 함수 기반 인덱스를 생성해야함 >create index emp_sal_func
on emp(to_char(sal));
--즉, 함수자체를 인덱스로 만들어버림select ename, sal
from emp
where sal like'30%';
@p
Plan hash value: 2924302369--------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows| A-Rows| A-Time| Buffers |--------------------------------------------------------------------------------------------------------------|0|SELECT STATEMENT ||1||2|00:00:00.01|4||1|TABLE ACCESS BY INDEX ROWID BATCHED| EMP |1|2|2|00:00:00.01|4||*2| INDEX RANGE SCAN | EMP_SAL_FUNC |1|2|2|00:00:00.01|2|--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------2- access("EMP"."SYS_NC00009$" LIKE'30%')
filter("EMP"."SYS_NC00009$" LIKE'30%')
문제풀며 익히기
Q1. 아래의 환경을 만들고, 아래의 sql을 튜닝하시오
//환경
droptable emp9000;
create table emp9000
( ename varchar2(10),
sal varchar2(10) );
insert into emp9000 values('scott', '3000');
insert into emp9000 values('smith', '1000');
insert into emp9000 values('allen', '2000');
commit;
create index emp9000_sal on emp9000(sal);
<튜닝전>select ename, sal
from emp9000
where sal =3000; # sal = 문자형, 3000= 숫자형
ENAME SAL
-------------------- --------------------
scott 3000/* full table scan 왜? 문자-> 숫자로 가공
Plan hash value: 4108122212
---------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 8 |
|* 1 | TABLE ACCESS FULL| EMP9000 | 1 | 1 | 1 |00:00:00.01 | 8 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("SAL")=3000) */
<튜닝후>select ename, sal
from emp9000
where sal ='3000';
ENAME SAL
-------------------- --------------------
scott 3000/* index range scan
Plan hash value: 500502602
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 2 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP9000 | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 2 | INDEX RANGE SCAN | EMP9000_SAL | 1 | 1 | 1 |00:00:00.01 | 1 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SAL"='3000') */=====================================================3000에 싱글쿼테이션 마크를 둘러 문자형으로 변환해주면 됨
튜닝18. 어쩔 수 없다면, 함수기반 인덱스를 생성하세요!
📖
함수기반 인덱스 생성
함수기반 인덱스 생성
예제 환경 구성)
@demoinsert into emp(empno, ename, sal) values(1111, ' jack ', 3000);
create index emp_ename on emp(ename);
예제)
<튜닝전>select ename, sal
from emp
wheretrim(ename) ='jack';
ENAME SAL
-------------------- ----------
jack 3000/* trim으로 가공했기 때문에 full scan
Plan hash value: 3956160932
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 7 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 1 | 1 |00:00:00.01 | 7 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TRIM("ENAME")='jack') */
<잘못된 튜닝>select ename, sal
from emp
where ename like'%jack%';
ENAME SAL
-------------------- ----------
jack 3000--값은 출력되지만 와일드 카드가 문자의 앞에 와있을 경우 full scan함--뒤에오는건 괜찮음<튜닝후>create index emp_ename_trim
on emp(trim(ename));
select ename, sal
from emp
wheretrim(ename) ='jack';
ENAME SAL
-------------------- ----------
jack 3000/* 함수기반 인덱스를 생성해서 index scan
Plan hash value: 2898567891
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 2 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 2 | INDEX RANGE SCAN | EMP_ENAME_TRIM | 1 | 1 | 1 |00:00:00.01 | 1 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMP"."SYS_NC00009$"='jack') */
문제풀며 익히기
Q1.
이름에 EN 또는 IN을 포함하는 사원들의 이름과 월급을 출력하시오
@democreate index emp_ename on emp(ename);
<튜닝 전>select ename, sal
from emp
where ename like'%EN%'or ename like'%IN%';
ENAME SAL
-------------------- ----------
KING 5000
MARTIN 1250
ALLEN 1600/* 문자의 앞에 있는 와일드카드에 의해 full scan
Plan hash value: 3956160932
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 |00:00:00.01 | 8 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 3 | 3 |00:00:00.01 | 8 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter((("ENAME" IS NOT NULL AND "ENAME" IS NOT NULL AND "ENAME"
LIKE '%EN%') OR ("ENAME" IS NOT NULL AND "ENAME" IS NOT NULL AND "ENAME"
LIKE '%IN%'))) */
<튜닝후>select ename, sal
from emp
where regexp_like( ename, 'EN|IN');
ENAME SAL
-------------------- ----------
KING 5000
MARTIN 1250
ALLEN 1600/*
Plan hash value: 3956160932
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 |00:00:00.01 | 8 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 3 | 3 |00:00:00.01 | 8 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( REGEXP_LIKE ("ENAME",'EN|IN',HEXTORAW('E002F8C6FB7F000020966D
1AF67F00000000000000000000D0976D1AF67F0000000000000000000000000000000000001C
000000000000002003F8C6FB7F00000200000000000000000000008100000080141711F67F00
000000000000000000')))*/==============================================
수직바 하나(|) =OR을 의미함
Q2.
아래의 SQL을 튜닝하시오
<튜닝 전>select ename, sal
from emp
where regexp_like(ename, 'EN|IN' );
<튜닝후..?>create index emp_ename_fun2
on emp( regexp_like( ename, 'EN|IN'));
설명 : regexp_like와 같은 정규 표현식 함수는 따로 함수 기반 인덱스를 생성할 수 없음
위의 튜닝은 다른 방법으로 튜닝해야함. 뒤에서 튜닝할 예정
💡
★인덱스를 통해서 테이블 엑세스를 할 때 가장 염두하여야 할 것★
“테이블 랜덤 엑세스를 줄일 수 있는 방법”을 생각해야 한다는 것
튜닝19. 인덱스를 이용해서 order by 절 사용을 피하세요.
📖
인덱스를 이용해서 order by 절 사용을 피하세요
이유
order by절을 사용해서 sql을 작성하게 되면, 오라클은 내부적으로 정렬 작업을 수행하기 위해 오라클 메모리인 pga영역에서 정렬작업을 수행하게 됨
그런데, pga 영역이 한정된 메모리 영역이기 때문에 너무 과도한 정렬작업을해야한다면, DB에 부하를 주게 됨
개별 메모리 공간(pga)를 거쳐 정렬 후 결과를 출력
개별 메모리 공간을 거칠 필요 없이 이미 정렬된 emp_sal을 활용 가능
인덱스 구조의 전체를 보려면?
WHERE절에 인덱스 컬럼의 검색조건이 있어야함(예제1과 같이)
★ DBA가 평상시에 신경쓰고 있어야하는 DB관리 TIP
아래 2가지를 항상 신경쓰고 있어야합니다
공간관리
메모리 관리
⇒ DB부하의 근본적인 원인 해결은 SQL튜닝
그럼 order by 절을 사용하지 않고 어떻게 정렬된 결과를 볼 수 있는가?
인덱스를 이용하면 됨
select ename, sal
from emp
orderby sal desc;
emp테이블이 1억건짜리 테이블이면, 과도한 정렬 발생(아래의 그림 설명)
따라서 TEMP DISK가 가득차서 TEMP용량을 추가 부여하는 방법이 있는데,
그건 사후조치이며, 좋은 조치는 아니기 때문에
DB부하에 근본적인 원인 해결은 SQL튜닝임.
어떻게 SQL 튜닝?
인덱스를 사용하면 됨!!/*
위의 sql문 실행시
user process -sql전달 > server process ( parsing, execute, fetch )
server process -> user process
정렬된 결과 전달.
서버 프로세스가 개별적으로 가지고있는 메모리 영역에서 꺼내서 전달 */
그림으로 오라클 메모리 구조 설명
emp 테이블의 데이터를 읽어와서 복사본을 → DB buffer cache영역에 올림(왜? 똑같은 것 읽을때 바로 여기서 찾으려고)
PGA : 서버 프로세스들이 공유하는 메모리 영역인 SGA와 그림으로 비교해봐도 용량이 크지 않은 영역이라는 것을 알 수 있음. PGA는 SQL정렬을 시행함
따라서 TEMP DISK와 DISK I/O 수행
PGA가 버거울때 TMEP DISK에도 보내서 정렬하고 PGA에 보내주는..
그런데 과도한 정렬을 발생시키는 SQL을 시행하면, PGA도 가득차고, TEMP DISK도 가득차서 업무가 마비되게 됨. TEMP DISK는 가득차면 업무가 마비됨..
오라클 db전체구조
PGA Memory에서 정렬작업을 하는데, 다 못하면 temp에 보내고 temp는 pga에게 다시 보내주는 작업을 하게됨.
예제
예제)
ed demo.sql
set lines 3000set pages 4000--이 두 줄 추가하기@democreate index emp_sal on emp(sal);
select sal, rowid
from emp
where sal >=0; --모두 출력하기 위해 0을 기준으로 함. --인덱스 구조 전체를 보려면 WHERE절에 인덱스 컬럼의 검색조건이 있어야함<튜닝전>select ename, sal
from emp
orderby sal asc;
<튜닝후>select/*+ index_asc(emp emp_sal) */ ename, sal
from emp
where sal >=0;
=======================================================/*+ index_asc(emp emp_sal) */
index를 ascending하게 출력하라는 힌트.
괄호안에는 [테이블명] [인덱스명]
예제2) 튜닝 전
exit;
cd /
cd trace
sqlplus c##scott/tiger
<튜닝전>@trace_onselect ename, sal
from emp
orderby sal asc;
@trace_off@trace_file@tkprof
select ename, sal
from emp
orderby sal asccall count cpu elapsed disk query currentrows------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 10.000.0001270Execute10.000.000000Fetch20.000.0007014------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 40.000.00019714
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 109
Number of plan statistics captured: 1Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------141414 SORT ORDERBY (cr=7 pr=0 pw=0time=50 us starts=1 cost=4 size=280 card=14)
141414TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0time=28 us starts=1 cost=3 size=280 card=
cr : Comsistemcy Read - PGA메모리에서 읽은 블록의 개수
pr : Physical Read - DB에서 읽은 블록의 개수
즉, temp disk와 disk i/o가 발생했는지
!!cr과 pr이 높으면 좋은게 아님!!
즉, 실행계획에 SORT가 들어가면 좋은게 아님. 정렬자체가 좋지 않음
CR = TABLES ACCESS 에서부터 누적되는 것(안에서부터 누적)
따라서 SORT에 만약 6이 아니라 8이라면,
ORDERBY할때 블록 2개를 더 ORDERBY를 위해 블록을 읽은것
-sort는 pga에서 하는 것
-pr=0 : temp disk와 disk i/o가 발생하지 않았다는 것.
sga만으로 정렬 메모리가 충분했다는 것
빨간 블록이 CR=6개! 부족할 경우 TEMP와 왔다갔다함
예제3) 튜닝후
@trace_onselect/*+ index_asc(emp emp_sal) */ ename, sal
from emp
where sal >=0;
@trace_off@trace_file@tkprof
********************************************************************************select/*+ index_asc(emp emp_sal) */ ename, sal
from emp
where sal >=0call count cpu elapsed disk query currentrows------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 10.000.000000Execute10.000.000000Fetch20.000.0004014------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 40.000.0004014
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 109
Number of plan statistics captured: 1Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------141414TABLE ACCESS BY INDEX ROWID BATCHED EMP (cr=4 pr=0 pw=0time=65 us starts=1 cost=2 size=280 card=14)
141414 INDEX RANGE SCAN EMP_SAL (cr=2 pr=0 pw=0time=16 us starts=1 cost=1 size=0 card=14)(object id 79738)
문제풀며 익히기
Q1. 아래의 sql을 튜닝하시오
<튜닝전>@trace_onselect ename, sal
from emp
orderby sal desc;
@trace_off@trace_file@tkprof///********************************************************************************select ename, sal
from emp
orderby sal desccall count cpu elapsed disk query currentrows------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 10.000.000210Execute10.000.000000Fetch20.000.0007014------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 40.000.0009114
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 109
Number of plan statistics captured: 1Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------141414 SORT ORDERBY (cr=7 pr=0 pw=0time=54 us starts=1 cost=4 size=280 card=14)
141414TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0time=36 us starts=1 cost=3 size=280 card=14)
<튜닝후>@trace_onselect/*+ index_desc(emp emp_sal) */ ename, sal
from emp
where sal >=0;
@trace_off@trace_file@tkprof////select/*+ index_desc(emp emp_sal) */ ename, sal
from emp
where sal >=0call count cpu elapsed disk query currentrows------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 10.000.000200Execute10.000.000000Fetch20.000.0004014------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 40.000.0006014
Q2. 아래의 sql을 튜닝하시오
@democreate index emp_sal on emp(sal);
<튜닝전>select ename, sal
from emp
where job ='SALESMAN'orderby sal desc;
<튜닝후>select/*+ index_desc(emp emp_sal) */ ename, sal
from emp
where job ='SALESMAN'AND SAL >=0; --무조건 인덱스 컬럼이 조건에 들어가야함
--실행 확인하기 @trace_onselect ename, sal
from emp
where job ='SALESMAN'orderby sal desc;
select/*+ index_desc(emp emp_sal) */ ename, sal
from emp
where job ='SALESMAN'AND SAL >=0;
@trace_off@trace_file@tkprof
********************************************************************************select ename, sal
from emp
where job ='SALESMAN'orderby sal desccall count cpu elapsed disk query currentrows------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 10.000.0009220Execute10.000.000000Fetch20.000.000604------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 40.000.00015224
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 109
Number of plan statistics captured: 1Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------444 SORT ORDERBY (cr=6 pr=0 pw=0time=43 us starts=1 cost=4 size=45 card=3)
444TABLE ACCESS FULL EMP (cr=6 pr=0 pw=0time=27 us starts=1 cost=3 size=45 card=3)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------SQL*Net message to client 20.000.00SQL*Net message from client 20.000.00********************************************************************************
********************************************************************************select/*+ index_desc(emp emp_sal) */ ename, sal
from emp
where job ='SALESMAN'AND SAL >=0call count cpu elapsed disk query currentrows------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 10.000.000000Execute10.000.000000Fetch20.000.000404------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 40.000.000404
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 109
Number of plan statistics captured: 1Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------444TABLE ACCESS BY INDEX ROWID BATCHED EMP (cr=4 pr=0 pw=0time=18 us starts=1 cost=2 size=45 card=3)
141414 INDEX RANGE SCAN DESCENDING EMP_SAL (cr=2 pr=0 pw=0time=10 us starts=1 cost=1 size=0 card=14)(object id 79748)
/* 누적이기 때문에 index scan할때 cr=2 즉 블록 2개 사용
table access할때 cr=4는 즉, 블록 2개 사용했다는 의미 */
Q3. 아래의 sql을 튜닝하시오
@democreate index emp_hiredate on emp(hiredate);
<튜닝전>select ename, hiredate
from emp
orderby hiredate desc;
<튜닝후>select/*+ index_desc(emp emp_hiredate) */ ename, hiredate
from emp
where hiredate < to_date('9999/12/31', 'RRRR/MM/DD');
--where절에 인덱스 컬럼이 검색조건으로 있어야 함
@trace_onselect ename, hiredate
from emp
orderby hiredate desc;
select/*+ index_desc(emp emp_hiredate) */ ename, hiredate
from emp
where hiredate < to_date('9999/12/31', 'RRRR/MM/DD');
@trace_off@trace_file@tkprof
select ename, hiredate
from emp
orderby hiredate desccall count cpu elapsed disk query currentrows------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 10.000.0004100Execute10.000.000000Fetch20.000.0006014------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 40.000.000101014
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 109
Number of plan statistics captured: 1Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------141414 SORT ORDERBY (cr=6 pr=0 pw=0time=46 us starts=1 cost=4 size=168 card=14)
141414TABLE ACCESS FULL EMP (cr=6 pr=0 pw=0time=27 us starts=1 cost=3 size=168 card=14)
select/*+ index_desc(emp emp_hiredate) */ ename, hiredate
from emp
where hiredate < to_date('9999/12/31', 'RRRR/MM/DD')
call count cpu elapsed disk query currentrows------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 10.000.000000Execute10.000.000000Fetch20.000.0004014------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 40.000.0004014
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 109
Number of plan statistics captured: 1Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------141414TABLE ACCESS BY INDEX ROWID BATCHED EMP (cr=4 pr=0 pw=0time=27 us starts=1 cost=2 size=168 card=14)
141414 INDEX RANGE SCAN DESCENDING EMP_HIREDATE (cr=2 pr=0 pw=0time=18 us starts=1 cost=1 size=0 card=14)(object id 79751)
튜닝20. 그룹함수 대신에 인덱스를 사용해서 sql을 튜닝하세요★
📖
그룹합수 정렬 기능
그룹함수도 정렬작업의 내부적으로 발생.
따라서 정렬작업을 최소화하기 위해서 인덱스를 활용(부분범위 처리를 하라는 의미)
그룹함수도 정렬작업이 내부적으로 발생합니다.
정렬작업을 최소화하기 위해서 인덱스를 활용하면 됩니다
★전체범위처리가 → 부분범위 처리가 되도록 튜닝해줘야함 ★
▩ SQL을 실행하는 방식 2가지
전체 범위 처리
테이블 전체를 다 읽어야 결과를 볼 수 있는 SQL
EX.
selectmax(sal)
from emp;
-------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows| A-Rows| A-Time| Buffers |-------------------------------------------------------------------------------------|0|SELECT STATEMENT ||1||1|00:00:00.01|6||1| SORT AGGREGATE ||1|1|1|00:00:00.01|6||2|TABLE ACCESS FULL| EMP |1|14|14|00:00:00.01|6|-------------------------------------------------------------------------------------
EMP테이블을 FULL SCAN함
부분 범위 처리
테이블이나 인덱스의 일부분만 읽어도 결과를 볼 수 있는 SQL
ex.
select/*+ index_desc(emp emp_sal) */ sal
from emp
where sal >=0and rownum =1;
SQL>select/*+ index_desc(emp emp_sal) */ sal
2from emp
3where sal >=0;
SAL
----------500030003000297528502450160015001300125012501100950800SQL>select/*+ index_desc(emp emp_sal) */ sal
2from emp
3where sal >=04and rownum =1;
SAL
----------5000
--------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows| A-Rows| A-Time| Buffers |--------------------------------------------------------------------------------------------------|0|SELECT STATEMENT ||1||1|00:00:00.01|1||*1| COUNT STOPKEY ||1||1|00:00:00.01|1||*2| INDEX RANGE SCAN DESCENDING| EMP_SAL |1|1|1|00:00:00.01|1|--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------1-filter(ROWNUM=1)
2- access("SAL">=0)
filter("SAL">=0)
==================================
COUNT STOPKEY
: INDEX를 다 읽지 않고 딱 하나 읽고 멈췄다는 것
왜? ROWNUM=1을 사용했기 때문임!=> 부분 범위 처리
COUNT STOPKEY
이는 조건절에 부합하는 레코드가 아무리 많아도
그 중 ROWNUM으로 지정한 건수만큼 결과 레코드를 얻으면
거기서 바로 멈춘다는 뜻이다.
예제1) 최대 월급을 출력하시오
selectmax(sal)
from emp;
@p/* sort aggregate가 발생한 것을 확인할 수 있다
SQL_ID 9s9zc5np91nw3, child number 0
-------------------------------------
select max(sal) from emp
Plan hash value: 2083865914
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 6 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 6 |
| 2 | TABLE ACCESS FULL| EMP | 1 | 14 | 14 |00:00:00.01 | 6 |
-------------------------------------------------------------------------------------
*/===================================
Sort aggregate가 발생하면서 정렬작업 발생.
버퍼의 개수가 6개나 사용되어버림
문제풀며 익히기
Q1. 아래의 SQL이 부분범위 처리가 되도록 튜닝하시오
SQL>CREATE INDEX EMP_SAL ON EMP(SAL);
<튜닝전>select ename, sal
from emp
where sal = (selectmax(sal) from emp );
ENAME SAL
-------------------- ----------
KING 5000---------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows| A-Rows| A-Time| Buffers |---------------------------------------------------------------------------------------------------------|0|SELECT STATEMENT ||1||1|00:00:00.01|3||1|TABLE ACCESS BY INDEX ROWID BATCHED| EMP |1|1|1|00:00:00.01|3||*2| INDEX RANGE SCAN | EMP_SAL |1|1|1|00:00:00.01|2||3| SORT AGGREGATE ||1|1|1|00:00:00.01|1||4| INDEX FULL SCAN (MIN/MAX) | EMP_SAL |1|1|1|00:00:00.01|1|---------------------------------------------------------------------------------------------------------============================
INDEX FULL SCAN
: 전체 범위 처리 했다는 의미
------SUB QUERY 부분-------(앞의 수는 실행계획 읽는 순서)2 SORT AGGREGATE
1 INDEX FULL SCAN (MIN/MAX)
------MAIN QUERY 부분-------5SELECT STATEMENT
4TABLE ACCESS BY INDEX ROWID BATCHED
3 INDEX RANGE SCAN
<튜닝 후>select/*+ index_desc(emp emp_sal) */ ename, sal
from emp
where sal >=0and rownum =1;
----------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows| A-Rows| A-Time| Buffers |----------------------------------------------------------------------------------------------------------|0|SELECT STATEMENT ||1||1|00:00:00.01|2||*1| COUNT STOPKEY ||1||1|00:00:00.01|2||2|TABLE ACCESS BY INDEX ROWID BATCHED| EMP |1|1|1|00:00:00.01|2||*3| INDEX RANGE SCAN DESCENDING | EMP_SAL |1|14|1|00:00:00.01|1|----------------------------------------------------------------------------------------------------------
Q2.
아래의 sql이 부분범위 처리를 할 수 있도록 튜닝하시오! (인덱스 필요시 생성)
<튜닝전>select ename, hiredate
from emp
where hiredate = ( selectmax(hiredate) from emp );
ENAME HIREDATE
-------------------- --------
ADAMS 83/01/15--------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows| A-Rows| A-Time| Buffers |--------------------------------------------------------------------------------------|0|SELECT STATEMENT ||1||1|00:00:00.01|13||*1|TABLE ACCESS FULL| EMP |1|1|1|00:00:00.01|13||2| SORT AGGREGATE ||1|1|1|00:00:00.01|6||3|TABLE ACCESS FULL| EMP |1|14|14|00:00:00.01|6|--------------------------------------------------------------------------------------
<튜닝후>create index emp_hiredate on emp(hiredate);
select/*+ index_desc(emp emp_hiredate) */ ename, hiredate
from emp
where hiredate < to_date('9999/12/31', 'RRRR/MM/DD')
and rownum =1;
ENAME HIREDATE
-------------------- --------
ADAMS 83/01/15---------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows| A-Rows| A-Time| Buffers |---------------------------------------------------------------------------------------------------------------|0|SELECT STATEMENT ||1||1|00:00:00.01|2||*1| COUNT STOPKEY ||1||1|00:00:00.01|2||2|TABLE ACCESS BY INDEX ROWID BATCHED| EMP |1|1|1|00:00:00.01|2||*3| INDEX RANGE SCAN DESCENDING | EMP_HIREDATE |1|14|1|00:00:00.01|1|---------------------------------------------------------------------------------------------------------------=================TABLE ACCESS BY INDEX ROWID BATCHED
: ename도 가져와야 하기 때문에 table access
튜닝21. 인덱스를 엑세스 하지 못하는 검색조건
📖
💡
▩WHERE절에 다음과 같은 검색조건이면, 인덱스를 엑세스 하지 못함
IS NULL, IS NOT NULL
LIKE검색 시 와일드 카드(%)를 앞에 사용한 경우
부정 연산자(≠ , <>,^= ) 를 사용한 경우
인덱스 컬럼을 가공한 경우
예제
IS NULL 예제)
커미션이 NULL인 사원들의 이름과 커미션을 출력하라
@democreate index emp_comm on emp(comm);
select ename, comm
from emp
where comm isnull;
ENAME COMM
-------------------- ----------
KING
BLAKE
CLARK
JONES
JAMES
FORD
SMITH
SCOTT
ADAMS
MILLER
@p------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows| A-Rows| A-Time| Buffers |------------------------------------------------------------------------------------|0|SELECT STATEMENT ||1||10|00:00:00.01|7||*1|TABLE ACCESS FULL| EMP |1|10|10|00:00:00.01|7|------------------------------------------------------------------------------------
IS NOT NULL 예제)
커미션이 null이 아닌 사원들의 이름, 커미션 출력 후 실행계획을 확인하시오
@democreate index emp_comm on emp(comm);
select ename, comm
from emp
where comm isnot null;
ENAME COMM
-------------------- ----------
TURNER 0
ALLEN 300
WARD 500
MARTIN 1400@P----------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows| A-Rows| A-Time| Buffers |----------------------------------------------------------------------------------------------------------|0|SELECT STATEMENT ||1||4|00:00:00.01|4||1|TABLE ACCESS BY INDEX ROWID BATCHED| EMP |1|4|4|00:00:00.01|4||*2| INDEX FULL SCAN | EMP_COMM |1|4|4|00:00:00.01|2|----------------------------------------------------------------------------------------------------------========================
INDEX FULL SCAN
전체 범위 처리한 것
LIKE 예제)
이름의 끝 글자가 T로 끝나는 사원들의 이름과 월급을 출력하시오.
@democreate index emp_ename on emp(ename);
select ename, sal
from emp
where ename like'%T';
ENAME SAL
-------------------- ----------
SCOTT 3000@p-----------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows| A-Rows| A-Time| Buffers |-----------------------------------------------------------------------------------------------------------|0|SELECT STATEMENT ||1||1|00:00:00.01|3||1|TABLE ACCESS BY INDEX ROWID BATCHED| EMP |1|1|1|00:00:00.01|3||*2| INDEX FULL SCAN | EMP_ENAME |1|1|1|00:00:00.01|2|-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------2-filter(("ENAME" LIKE'%T'AND "ENAME" ISNOT NULL))
부정 연산자 예제)
월급이 3000이 아닌 사원들의 이름과 월급을 출력하시오.
@democreate index emp_sal on emp(sal);
select ename, sal
from emp
where sal <>3000;
ENAME SAL
-------------------- ----------
KING 5000
BLAKE 2850
CLARK 2450
JONES 2975
MARTIN 1250
ALLEN 1600
TURNER 1500
JAMES 950
WARD 1250
SMITH 800
ADAMS 1100
MILLER 1300@p------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows| A-Rows| A-Time| Buffers |------------------------------------------------------------------------------------|0|SELECT STATEMENT ||1||12|00:00:00.01|7||*1|TABLE ACCESS FULL| EMP |1|13|12|00:00:00.01|7|------------------------------------------------------------------------------------
인덱스 컬럼 가공 예제)
1980년도에 입사한 사원들의 이름과 입사일을 출력하시오
@democreate index emp_hiredate on emp(hiredate);
select ename, hiredate
from emp
where to_char(hiredate,'RRRR') ='1980';
ENAME HIREDATE
-------------------- --------
SMITH 80/12/09@p------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows| A-Rows| A-Time| Buffers |------------------------------------------------------------------------------------|0|SELECT STATEMENT ||1||1|00:00:00.01|7||*1|TABLE ACCESS FULL| EMP |1|1|1|00:00:00.01|7|------------------------------------------------------------------------------------
문제풀며 익히기
Q1.
직업에 인덱스를 걸고 직업이 SALESMAN이 아닌 사원들의 이름과 직업을 출력하는 실행계획을 확인하시오
@democreate index emp_job on emp(job);
select ename, job
from emp
where job !='SALESMAN';
ENAME JOB
-------------------- ------------------
KING PRESIDENT
BLAKE MANAGER
CLARK MANAGER
JONES MANAGER
JAMES CLERK
FORD ANALYST
SMITH CLERK
SCOTT ANALYST
ADAMS CLERK
MILLER CLERK
@p------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows| A-Rows| A-Time| Buffers |------------------------------------------------------------------------------------|0|SELECT STATEMENT ||1||10|00:00:00.01|7||*1|TABLE ACCESS FULL| EMP |1|11|10|00:00:00.01|7|------------------------------------------------------------------------------------
튜닝22. 두개의 단일 컬럼 인덱스를 merge scan 하시오.
📖
▩ 두 개의 단일 컬럼 인덱스 INDEX MERGE SCAN
where 절에 2개의 성능 좋은 인덱스 컬럼이 2개가 있다면,
하나의 인덱스만 엑세스 했을 때 보다 2개의 인덱스를 동시에 사용해서
INDEX MERGE SCAN으로 스캔하면 더 좋은 성능을 보이는 경우가 있다.
/*+ and_equal(emp emp_job emp_deptno) */
인덱스 merge scan에 대한 HINT
정리
where 절에 2개의 성능 좋은 인덱스 컬럼이 2개가 있다면
하나의 인덱스만 엑세스 했을 때 보다 2개의 인덱스를 동시에 사용해서
index merge scan 으로 스캔하면 더 좋은 성능을 보이는 경우가 있습니다.
인덱스를 통해서 테이블 엑세스를 할 때 가장 염두해 두어야 할것은
"테이블 엑세스를 줄일 수 있는 방법" 을 생각해야 한다는 것입니다.
2개의 인덱스를 같이 스캔해서 테이블 엑세스를 현저하게 줄이는 스캔 방법이 index merge scan 입니다.
▩ INDEX MERGE
<인덱스가 있는 COL1과 COL2 >
INDEX SCAN 시 가급적 테이블 RANDOM ACCESS 를 줄여야함
RANDOM ACCESS
COL1 INDEX에서 ROW ID를 가지고 TAB1 TABLE에서 찾을 때
어디에 ROW ID가 있는지 모르기 때문에 마구자비로 찾으러 가서 RANDOM ACCESS임
두 개 중 한 개를 선택한다면, COL1인덱스가 더 좋은 인덱스
왜? COL1 은 ABC가 6개인데 (TABLE ACCESS 6번)
CO2는 123가 12개 이기 때문에 RANGE SCAN범위가 넓어짐! (TABLE ACCESS 12번)
RANGE SCAN범위가 적은게 더 좋은 인덱스
하지만 인덱스를 짧게 읽는 것 보다 더 중요한 것은
table access를 적게 하는게 중요.
따라서 index merge 사용
INDEX MERGE
두 인덱스를 함께 사용해 시너지 효과를 얻자!
두 개의 인덱스를 같이 스캔해서 테이블 엑세스를 현저하게 줄이는 스캔방법
두 인덱스 테이블을 먼저 스캔 함(조건에 맞는 값 중 col1의 처음 rowid확인 col2의 처음 rowid확인)
더 작은 ROWID를 가진 인덱스CO2의 ROWID를 스캔하다가
CO1의 ROWID보다 값이 커지면 CO1로 넘어가 ROWID스캔
스캔하다가 CO2의 값과 같은걸 찾으면, TABLE ACCESS
그 후 다시 CO1로 돌아와 스캔하는데, CO2에서 멈췄던 ROWID보다 값이 커서 멈춤
COL2로 넘어가 ROWID 스캔을 .~~
결과적으로table access를 2번만 하게 됨
실습
실습 환경 구성
@democreate index emp_job on emp(job);
create index emp_deptno on emp(deptno);
예제1) 아래의 sql은 2개의 인덱스 중에 어느 인덱스를 엑세스 하겠는가?
select ename, sal, job, deptno
from emp
where job='ANALYST'and deptno =20;
ENAME JOB DEPTNO
-------------------- ------------------ ----------
FORD ANALYST 20
SCOTT ANALYST 20** job인덱스와 deptno 인덱스 중 어느 인덱스를 엑세스 하겠는가? **@p/* job의 인덱스를 엑세스 한 것을 확인할 수 있음
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.01 | 4 |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 1 | 1 | 2 |00:00:00.01 | 4 |
|* 2 | INDEX RANGE SCAN | EMP_JOB | 1 | 3 | 2 |00:00:00.01 | 2 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPTNO"=20)
2 - access("JOB"='ANALYST')
*/=======================
< job과 deptno의 인덱스 비교>select job, rowid
from emp
where job >' ';
JOB |ROWID |---------+------------------+
ANALYST |AAATejAAHAAAUNjAAJ|---
ANALYST |AAATejAAHAAAUNjAAL|---
CLERK |AAATejAAHAAAUNjAAH|--- 총 3건 확인
CLERK |AAATejAAHAAAUNjAAK|
CLERK |AAATejAAHAAAUNjAAM|
CLERK |AAATejAAHAAAUNjAAN|
MANAGER |AAATejAAHAAAUNjAAB|
MANAGER |AAATejAAHAAAUNjAAC|
MANAGER |AAATejAAHAAAUNjAAD|
PRESIDENT|AAATejAAHAAAUNjAAA|
SALESMAN |AAATejAAHAAAUNjAAE|
SALESMAN |AAATejAAHAAAUNjAAF|
SALESMAN |AAATejAAHAAAUNjAAG|
SALESMAN |AAATejAAHAAAUNjAAI|
따라서
/*
select ename, sal, job, deptno
from emp
where job='ANALYST'
and deptno = 20; */
이 sql 수행 시 emp 테이블 access를 2번 진행
============================================================select deptno, rowid
from emp
where deptno >=0;
DEPTNO|ROWID |------+------------------+10|AAATejAAHAAAUNjAAA|10|AAATejAAHAAAUNjAAC|10|AAATejAAHAAAUNjAAN|20|AAATejAAHAAAUNjAAD|---20|AAATejAAHAAAUNjAAJ|---20|AAATejAAHAAAUNjAAK|---20|AAATejAAHAAAUNjAAL|---20|AAATejAAHAAAUNjAAM|---30|AAATejAAHAAAUNjAAB|--- 총 6건 읽음 (다음 행이 조건에 맞지 않는것 까지 확인)30|AAATejAAHAAAUNjAAE|30|AAATejAAHAAAUNjAAF|30|AAATejAAHAAAUNjAAG|30|AAATejAAHAAAUNjAAH|30|AAATejAAHAAAUNjAAI|
따라서
/*
select ename, sal, job, deptno
from emp
where job='ANALYST'
and deptno = 20; */
이 sql 수행 시 emp 테이블 random access를 5번 진행
=====================================================
인덱스가 생성된 순간 인덱스 통계정보가 생성되기 때문에
옵티마이저가 어떤 인덱스를 탈지 고를 수 있는 것
예제2)
두 개의 인덱스를 같이 사용해서 더 큰 시너지 효과를 보게하시오. ( 테이블 엑세스를 줄일 수 있으면, 인덱스 merge scan이 되게 하시오)
select/*+ and_equal(emp (emp_job emp_deptno)) */ ename, job, deptno
from emp
where job='ANALYST'and deptno =20;
@p----------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows| A-Rows| A-Time| Buffers |----------------------------------------------------------------------------------------------------|0|SELECT STATEMENT ||1||2|00:00:00.01|8||*1|TABLE ACCESS BY INDEX ROWID| EMP |1|1|2|00:00:00.01|8||2|AND-EQUAL ||1||2|00:00:00.01|6||*3| INDEX RANGE SCAN | EMP_JOB |1|3|2|00:00:00.01|4||*4| INDEX RANGE SCAN | EMP_DEPTNO |1|5|2|00:00:00.01|2|----------------------------------------------------------------------------------------------------
■ Buffer 의 개수가 8개..
따라서 이 실습에서는 index merge scan 을 하는 것 보다
emp_job인덱스 만 엑세스 하는게 더 비용이 적게 드는 실행계획이다.
왜? buffer의 개수가 4개 이므로
하지만, merge scan이 더 좋은 경우가 많음
문제풀며 익히기
Q1.
mgr 컬럼과 deptno컬럼에 각각 단일 컬럼 인덱스를 생성하고, 아래의 sql이 index merge scan을 할 수 있도록 힌트를 주시오.
@democreate index emp_mgr on emp(mgr);
create index emp_deptno on emp(deptno);
select empno, ename, deptno, mgr
from emp
where mgr =7698and deptno =30;
EMPNO ENAME DEPTNO MGR
---------- -------------------- ---------- ----------7654 MARTIN 3076987499 ALLEN 3076987844 TURNER 3076987900 JAMES 3076987521 WARD 307698@p---------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows| A-Rows| A-Time| Buffers |---------------------------------------------------------------------------------------------------------|0|SELECT STATEMENT ||1||5|00:00:00.01|4||*1|TABLE ACCESS BY INDEX ROWID BATCHED| EMP |1|1|5|00:00:00.01|4||*2| INDEX RANGE SCAN | EMP_MGR |1|2|5|00:00:00.01|2|---------------------------------------------------------------------------------------------------------
< INDEC MERGE SCAN >select/*+ and_equal(emp emp_mgr emp_deptno) */ empno, ename, deptno, mgr
from emp
where mgr =7698and deptno =30;
@P----------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows| A-Rows| A-Time| Buffers |----------------------------------------------------------------------------------------------------|0|SELECT STATEMENT ||1||5|00:00:00.01|7||*1|TABLE ACCESS BY INDEX ROWID| EMP |1|1|5|00:00:00.01|7||2|AND-EQUAL ||1||5|00:00:00.01|5||*3| INDEX RANGE SCAN | EMP_MGR |1|2|5|00:00:00.01|3||*4| INDEX RANGE SCAN | EMP_DEPTNO |1|5|5|00:00:00.01|2|----------------------------------------------------------------------------------------------------
튜닝23. 인덱스를 통해 TABLE RANDOM ACCESS를 줄이기 위해 결합컬럼인덱스를 사용하세요.
📖
▦결합 컬럼 인덱스
단일컬럼 인덱스보다 결합 컬럼 인덱스를 사용하는 것이 테이블 랜덤 엑세스를 줄일 수 있음
INDEX MERGE(결합 인덱스와 비교)
COL1는 6번, CO2는 12번 테이블 ACCESS를 했어야 했는데
INDEX MERGE 를 통해 ACCESS를 2번만 해도 됐다.
그런데, 우측 상자에 있는 것 처럼 결합 인덱스를 사용할 수 있다.
▩ 결합 인덱스
COL1을 ASC하게 정렬하고
COL1을 ASC하게 정렬한 것 기준으로 CO2를 ASC하게 정렬하여 MERGE함
그렇게 되면 인덱스도 짧게 읽을 뿐 아니라
TABLE ACCESS도 2번으로 짧게 함
COL1 + COL2로 결합 컬럼 인덱스를 구성하니까 인덱스 RANGE SCAN도 짧게하고, 테이블 엑세스도 2번만 하게됨
만약 COL1 단일 컬럼 인덱스만 사용한다면 테이블 랜덤 엑세스를 6번 해야하고,
COL2 단일 컬럼인덱스만 사용한다면 테이블 랜덤 액세스를 12번 해야한다
COL1 + COL2로 결합 컬럼 인덱스를 구성하니까 인덱스 RANGE SCAN도 짧게하고, 테이블 엑세스도 2번만 하게됨
만약 COL1 단일 컬럼 인덱스만 사용한다면 테이블 랜덤 엑세스를 6번 해야하고,
COL2 단일 컬럼인덱스만 사용한다면 테이블 랜덤 액세스를 12번 해야한다
실습예제
실습 환경 구성
@democreate index emp_deptno on emp(deptno);
create index emp_job on emp(job);
create index emp_job_deptno on emp(job, detpno);
예제1) 아래의 3개의 sql 중 가장 buffer 의 개수가 작은 것은?
# 1. emp_deptno 인덱스 활용
select/*+ idex(emp emp_deptno) */ ename, sal, job, deptno
from emp
where job ='ANALYST'and deptno =20;
ENAME SAL JOB DEPTNO
-------------------- ---------- ------------------ ----------
FORD 3000 ANALYST 20
SCOTT 3000 ANALYST 20@p------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows| A-Rows| A-Time| Buffers |------------------------------------------------------------------------------------------------------------|0|SELECT STATEMENT ||1||2|00:00:00.01|4||*1|TABLE ACCESS BY INDEX ROWID BATCHED| EMP |1|1|2|00:00:00.01|4||*2| INDEX RANGE SCAN | EMP_DEPTNO |1|5|5|00:00:00.01|2|------------------------------------------------------------------------------------------------------------
버퍼의 개수 : 4개
# 2. emp_job 인덱스 활용
select/*+ idex(emp emp_job) */ ename, sal, job, deptno
from emp
where job ='ANALYST'and deptno =20;
@p------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows| A-Rows| A-Time| Buffers |------------------------------------------------------------------------------------------------------------|0|SELECT STATEMENT ||1||2|00:00:00.01|4||*1|TABLE ACCESS BY INDEX ROWID BATCHED| EMP |1|1|2|00:00:00.01|4||*2| INDEX RANGE SCAN | EMP_DEPTNO |1|5|5|00:00:00.01|2|-----------------------------------------------------------------------------------------------------------
버퍼의 개수 : 4개
# 3. emp_job_deptno 인덱스 활용
select/*+ idex(emp emp_job_deptno) */ ename, sal, job, deptno
from emp
where job ='ANALYST'and deptno =20;
@p------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows| A-Rows| A-Time| Buffers |------------------------------------------------------------------------------------------------------------|0|SELECT STATEMENT ||1||2|00:00:00.01|4||*1|TABLE ACCESS BY INDEX ROWID BATCHED| EMP |1|1|2|00:00:00.01|4||*2| INDEX RANGE SCAN | EMP_DEPTNO |1|5|5|00:00:00.01|2|------------------------------------------------------------------------------------------------------------
위의 결과는 전부 버퍼의 개수가 4개여서 차이가 없음.
예제2) 이번에는 sql trace로 확인하시오
@trace_onselect/*+ idex(emp emp_deptno) */ ename, sal, job, deptno
from emp
where job ='ANALYST'and deptno =20;
select/*+ idex(emp emp_job) */ ename, sal, job, deptno
from emp
where job ='ANALYST'and deptno =20;
select/*+ idex(emp emp_job_deptno) */ ename, sal, job, deptno
from emp
where job ='ANALYST'and deptno =20;
@trace_off@trace_file@tkprofcall count cpu elapsed disk query currentrows------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 10.000.000000Execute10.000.000000Fetch20.000.000402------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 40.000.000402
전부 4이므로 큰 차이 없음..
테이블이 작아서 그런듯!
새로운 환경 구성
create table mcustsum
asselect rownum custno
, '2008'|| lpad(ceil(rownum/100000), 2, '0') salemm
, decode(mod(rownum, 12), 1, 'A', 'B') salegb
, round(dbms_random.value(1000,100000), -2) saleamt
from dual
connectby level <=1200000 ;
create index m_salegb on mcustsum(salegb);
create index m_salemm on mcustsum(salemm);
create index m_salegb_salemm on mcustsum(salegb,salemm);
exec dbms_stats.gather_table_stats('c##scott', 'mcustsum', cascade=>true);
-- cascade => true 이걸 써줘야 index도 같이 통계정보가 수집됨===================================================exec dbms_stats.gather_table_stats('c##scott', 'mcustsum', cascade=>true);
설명:
mcustsum 테이블의 통계정보를 수집하는데(수집해야 옵티마이저가 똑똑해짐 ㅜ)
cascade =>true를 써야 인덱스의 통계정보도 같이 수집됨
예제1)
아래의 sql이 m_salegb 인덱스를 엑세스 할 수 있도록 힌트를 주고 실행하시오
selectcount(*)
from mcustsum t
where salegb ='A'and salemm between'200801'and'200812';
A.
select/*+ index(t m_salegb) */count(*)
from mcustsum t
where salegb ='A'and salemm between'200801'and'200812';
COUNT(*)
----------100000@p-----------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows| A-Rows| A-Time| Buffers |-----------------------------------------------------------------------------------------------------------|0|SELECT STATEMENT ||1||1|00:00:00.03|4013||1| SORT AGGREGATE ||1|1|1|00:00:00.03|4013||*2|TABLE ACCESS BY INDEX ROWID BATCHED| MCUSTSUM |1|600K|100K|00:00:00.02|4013||*3| INDEX RANGE SCAN | M_SALEGB |1|600K|100K|00:00:00.01|184|-----------------------------------------------------------------------------------------------------------
버퍼의 개수 4013=============================================
[[주의]]
테이블 별칭을 주었으면, 힌트 내에도 테이블 별칭으로 줘야함
예제2) 아래의 sql이 m_salemm인덱스를 엑세스 할 수 있도록 힌트를 주고 실행하시오
selectcount(*)
from mcustsum t
where salegb ='A'and salemm between'200801'and'200812';
A.
select/*+ index(t m_salemm) */count(*)
from mcustsum t
where salegb ='A'and salemm between'200801'and'200812';
-----------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows| A-Rows| A-Time| Buffers |-----------------------------------------------------------------------------------------------------------|0|SELECT STATEMENT ||1||1|00:00:00.26|6839||1| SORT AGGREGATE ||1|1|1|00:00:00.26|6839||*2|TABLE ACCESS BY INDEX ROWID BATCHED| MCUSTSUM |1|600K|100K|00:00:00.26|6839||*3| INDEX RANGE SCAN | M_SALEMM |1|1200K|1200K|00:00:00.10|3010|-----------------------------------------------------------------------------------------------------------
버퍼의 개수 6839개
예제3) - 결합컬럼인덱스
아래의 sql의 실행계획이 m_salegb_salemm의 인덱스를 하도록 힌트를 주시오
selectcount(*)
from mcustsum t
where salegb ='A'and salemm between'200801'and'200812';
A.
select/*+ index(t m_salegb_salemm) */count(*)
from mcustsum t
where salegb ='A'and salemm between'200801'and'200812';
-----------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows| A-Rows| A-Time| Buffers |-----------------------------------------------------------------------------------------------|0|SELECT STATEMENT ||1||1|00:00:00.01|281||1| SORT AGGREGATE ||1|1|1|00:00:00.01|281||*2| INDEX RANGE SCAN| M_SALEGB_SALEMM |1|600K|100K|00:00:00.01|281|-----------------------------------------------------------------------------------------------
버퍼의 개수 281개
튜닝24. 결합 컬럼 인덱스 구성 시 컬럼 순서 중요!
📖
결합 컬럼 인덱스 구성 시 컬럼 순서 중요
첫번째 박스
col1, col2순서로 결합했을 때
첫번째 컬럼을 기준으로 정렬 후 두번재 컬럼 정렬됨
a를 찾고 111을 찾아서 113까지 찾으면서 table access 후 빠져나옴 (3번 table access)
두번째 박스
col2, col1 순서로 넣었을 경우
col1을 앞에 둔 것 보다 range를 더 길게 봐야함
결론
COL1과 COL2를 가지고 결합 컬럼 인덱스를 구성한다면,
점조건인(=, IN)이 있는 컬럼을 선두 컬럼으로 두고, 선분조건(BETWEEN, LIKE)을 후행 컬럼으로 두어야 좋은 성능을 보인다.
■ 어떤 컬럼을 선두 컬럼으로 둬야하나?
우선순위 1 : 점 조건 (ex. equal, IN)
우선순위 2 : 선분 조건(ex. between, like)
실습
튜닝 환경 구성
droptable mcustsum purge;
create table mcustsum
asselect rownum custno
, '2008'|| lpad(ceil(rownum/100000), 2, '0') salemm
, decode(mod(rownum, 12), 1, 'A', 'B') salegb
, round(dbms_random.value(1000,100000), -2) saleamt
from dual
connectby level <=1200000 ;
create index m_salegb_salemm on mcustsum(salegb, salemm);
create index m_salemm_salegb on mcustsum(salemm, salegb);
예제1) 아래의 sql이 m_salemm_salgb 인덱스를 엑세스 할 수 있도록 힌트를 주시오
selectcount(*)
from mcustsum t
where salegb ='A'and salemm between'200801'and'200812';
select/*+ index(t m_salemm_salgb) */count(*)
from mcustsum t
where salegb ='A'and salemm between'200801'and'200812';
@p---------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows| A-Rows| A-Time| Buffers |---------------------------------------------------------------------------------------------------|0|SELECT STATEMENT ||1||1|00:00:00.02|3367||1| SORT AGGREGATE ||1|1|1|00:00:00.02|3367||*2| INDEX FAST FULL SCAN| M_SALEGB_SALEMM |1|600K|100K|00:00:00.02|3367|---------------------------------------------------------------------------------------------------======================================
INDEX FAST FULL SCAN
힌트에 salgb라고 오타가 나서 , 옵티마이저가 열일해서 나름대로 최선책을 찾은
버퍼의 개수 : 3367
select/*+ no_index_ss(t m_salemm_salegb) index(t m_salemm_salgb) */count(*)
from mcustsum t
where salegb ='A'and salemm between'200801'and'200812';
----
no_index_ss(t m_salemm_salegb)
인덱스 스킵 스캔 하지 말라는 힌트
/*+ no_index_ss(t m_salemm_salegb) index(t m_salemm_salgb) */
인덱스 스킵 스캔 하지 말고 인덱스 range 스캔 하라는 힌트
인덱스 스킵 스캔이 성능이 더 좋은것
하지만 수업용으로 range하는 것 보려고 하는 힌트
Q2.
점조건을 선두 컬럼에 둔 m_salegb_salemm 결합 컬럼 인덱스를 엑세스 하게 힌트를 주시오
select/*+ index(t m_salegb_salemm) */count(*)
from mcustsum t
where salegb ='A'and salemm between'200801'and'200812';
-----------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows| A-Rows| A-Time| Buffers |-----------------------------------------------------------------------------------------------|0|SELECT STATEMENT ||1||1|00:00:00.01|281||1| SORT AGGREGATE ||1|1|1|00:00:00.01|281||*2| INDEX RANGE SCAN| M_SALEGB_SALEMM |1|600K|100K|00:00:00.01|281|-----------------------------------------------------------------------------------------------
문제풀며 익히기
Q1.
m_salegb_salemm인덱스를 drop 하시오
drop index m_salegb_salemm;
즉 성능 좋은 인덱스를 삭제해본 것
Q2.
선두 컬럼이 선분 조건인 결합 컬럼 인덱스만 있는 상태에서, 아래의 sql을 튜닝하시오
(매번 인덱스를 많이 만들어 둘 순 없기 때문.)
<튜닝전>select/*+ index(t m_salemm_salgb) */count(*)
from mcustsum t
where salegb ='A'and salemm between'200801'and'200812';
<튜닝후: 선분 조건 -> 점 조건으로 변경(인덱스 스킵 스캔도 있지만 이걸로 해보기)>select/*+ index(t m_salemm_salegb) */count(*)
from mcustsum t
where salegb ='A'and salemm in ('200801', '200802', '200803', '200804', '200805', '200806',
'200807', '200808', '200809', '200810', '200811', '200812');
------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows| A-Rows| A-Time| Buffers |------------------------------------------------------------------------------------------------|0|SELECT STATEMENT ||1||1|00:00:00.01|304||1| SORT AGGREGATE ||1|1|1|00:00:00.01|304||2| INLIST ITERATOR ||1||100K|00:00:00.01|304||*3| INDEX RANGE SCAN| M_SALEMM_SALEGB |12|600K|100K|00:00:00.01|304|------------------------------------------------------------------------------------------------======
버퍼의 개수 : 304개로 줄어들게 됨
위의 그림은 BETWEEN ..AND를 IN으로 변경했을 때에 대한 설명이다
<인덱스 스킵 스캔(INDEX SKIP SCAN)>select/*+ index_ss(t m_salemm_salegb) */count(*)
from mcustsum t
where salegb ='A'and salemm between'200801'and'200812';
----------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows| A-Rows| A-Time| Buffers |----------------------------------------------------------------------------------------------|0|SELECT STATEMENT ||1||1|00:00:00.01|300||1| SORT AGGREGATE ||1|1|1|00:00:00.01|300||*2| INDEX SKIP SCAN| M_SALEMM_SALEGB |1|600K|100K|00:00:00.01|300|----------------------------------------------------------------------------------------------==============
점 조건으로 바꾸지 않아도 옵티마이저가 알아서 최선을 다함
튜닝25. 결합 컬럼 인덱스에서 컬럼을 추가하세요.
📖
결합 컬럼 인덱스에서 컬럼을 추가하세요.
결합 컬럼 인덱스에서 컬럼을 추가하면 테이블 랜덤 엑세스를 줄일 수 있음
좌측 박스 = 기존에 있는 INDEX
우측 박스 = 바꾼 INDEX
설명
특정 SQL을 만족할만한 좋은 인덱스를 만들어줘야하는데,
인덱스를 여러개 만들 수는 없음
기존 INDEX에는 C에 대한 정보가 없고 A,B컬럼만 있기 때문에
C 때문에 계속 테이블 엑세스를 해줬어야함.
따라서 A=2인 것을 RANGE SCAN하면서 테이블 엑세스를 5번 하게 됨
따라서 테이블 엑세스를 줄이자! 라는 마음으로
기존 INDEX에 C를 추가함
따라서 A=2인것을 RANGE SCAN하고 C=51인것을 찾아 TABLE ACCESS
덕분에 테이블 ACCESS를 한 번만 하게 됨
제일 좋은 인덱스는 A+C로 구성된 인덱스이지만
운영되고 있는 DB에는 인덱스를 함부로 생성하지 못함
개발 DB는 괜찮아도…
왜? 새로운 인덱스를 생성하면, 옵티마이저가 기존의 인덱스를 사용하지 않고 새로 생성한 인덱스를 사용하려고 하게 됨.
select/*+ index(t m_salegb_salemm) */count(*)
from mcustsum t
where salegb ='A'and salemm between'200801'and'200812'and saleamt between1000and2000;
COUNT(*)
----------1036SQL>@p---------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows| A-Rows| A-Time| Buffers |Reads|---------------------------------------------------------------------------------------------------------------------------|0|SELECT STATEMENT ||1||1|00:00:00.09|4110|4121||1| SORT AGGREGATE ||1|1|1|00:00:00.09|4110|4121||*2|TABLE ACCESS BY INDEX ROWID BATCHED| MCUSTSUM |1|6666|1036|00:00:00.09|4110|4121||*3| INDEX RANGE SCAN | M_SALEGB_SALEMM |1|600K|100K|00:00:00.02|281|287|---------------------------------------------------------------------------------------------------------------------------
버퍼의 개수 : 4110
문제풀며 익히기
Q1.
m_salegb_salemm 결합 컬럼 인덱스를 drop 하고 다시 결합 컬럼 인덱스를 생성하는데, salegb+salemm+saleamt로 구성하시오
drop index m_salegb_salemm;
create index m_indx1
on mcustsum(salegb, salemm, saleamt);
--현업에서는 컬럼명을 그대로 인덱스명에 포함시키지 않고--보통 위와 같이 만들게 됨
Q2.
아래의 sql의 버퍼의 개수를 확인하시오
select/*+ no_index_ss(t m_indx1) index(t m_indx1) */count(*)
from mcustsum t
where salegb ='A'and salemm between'200801'and'200812'and saleamt between1000and2000;
------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows| A-Rows| A-Time| Buffers |Reads|------------------------------------------------------------------------------------------------|0|SELECT STATEMENT ||1||1|00:00:00.02|308|214||1| SORT AGGREGATE ||1|1|1|00:00:00.02|308|214||*2| INDEX RANGE SCAN| M_INDX1 |1|6666|1036|00:00:00.02|308|214|------------------------------------------------------------------------------------------------
버퍼의 개수 : 308개
select/*+ index(t m_indx1) */count(*)
from mcustsum t
where salegb ='A'and salemm between'200801'and'200812'and saleamt between1000and2000;
-----------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows| A-Rows| A-Time| Buffers |Reads|-----------------------------------------------------------------------------------------------|0|SELECT STATEMENT ||1||1|00:00:00.01|17|107||1| SORT AGGREGATE ||1|1|1|00:00:00.01|17|107||*2| INDEX SKIP SCAN| M_INDX1 |1|6666|1036|00:00:00.01|17|107|-----------------------------------------------------------------------------------------------
버퍼의 개수 : 17개
튜닝26. INDEX SKIP SCAN을 사용하세요.
📖
INDEX SKIP SCAN 사용하기
INDEX SKIP SCAN을 이해하려면, 결합 컬럼 인덱스가 어떤 SQL에서 사용할 수 있는지를 먼저 이해해야함
▩ 결합컬럼 인덱스 사용 가능 SQL
결합 컬럼 인덱스의 첫번째 컬럼이 WHERE절에 있어야지만, 그 SQL이 결합 컬럼 인덱스를 사용할 수 있음
▩ INDEX SKIP SCAN
INDEX FAST FULL SCAN(or INDEX FULL SCAN)
:인덱스를 처음부터 끝까지 전부 읽는 것
INDEX SKIP SCAN
A컬럼의 데이터를 쭉 읽다가 KKK를 찾으면 테이블 엑세스 후, 뒤에 KKK아닌거 확인하고 A LLL은 SKIP즉 안하고 나가버림
그러다가 B를 찾으로 들어감. KKK찾으면, 뒤에 아무리 많은 B가 있어도 그냥 SKIP하고 나가버림(B KKKK인게 아닌걸 확인하고 B TTTT부터 생략)
즉, 인덱스 스킵 스캔은
인덱스를 FULL로 스캔하는게 아니라 중간중간 SKIP하면서 INDEX를 스캔하는 것
(스킵하는 양이 많을수록 성능이 좋아지게 됨)
★ 스킵하는 양이 많아지려면?(즉, 인덱스 SKIP SCAN의 효과를 보려면?) ★
결합 컬럼 인덱스의 선두컬럼의 데이터의 종류가 몇가지 되지 않아야 좋은 성능을 보이게 됨.
ex)
create index emp_deptno on emp(deptno, job);
위와 같이 걸게 되면, 부서번호가 앞이고 job이 뒤에 있는 형태
select ename, deptno, job
from emp
where job ='B'
즉, 앞에 있는 컬럼의 데이터 종류가 몇가지 되지 않는게 좋음.
부서번호는 100까지 있고
JOB은 5개 있음.
DEPTNO
JOB
10
A
10
B
10
C
10
D
10
E
20
A
20
B
20
C
…
…
100
E
예제1) 아래의 SQL은 emp_deptno_job 인덱스를 사용할 수 있을까요?
@democreate index emp_deptno_job on emp(deptno, job);
select/*+ no_index_ss(emp emp_deptno_job) */ ename, deptno, job
from emp
where job ='SALESMAN';
@p----------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows| A-Rows| A-Time| Buffers |----------------------------------------------------------------------------------------------------------------|0|SELECT STATEMENT ||1||4|00:00:00.01|4||1|TABLE ACCESS BY INDEX ROWID BATCHED| EMP |1|3|4|00:00:00.01|4||*2| INDEX FULL SCAN | EMP_DEPTNO_JOB |1|3|4|00:00:00.01|2|----------------------------------------------------------------------------------------------------------------=========================
INDEX FULL SCAN :인덱스를 처음부터 끝까지 다 읽었다는 것
결합 컬럼 인덱스의 첫 번째 컬럼인 deptno가 where절에 없으면,
index range scan을 할 수 없고, index full scan이나 table full scan을 하게 됨
call count cpu elapsed disk query currentrows mis Wait Ela
------- ------ -------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Parse 10.030.03000010.00Exec10.000.00000000.00Fetch14.1716.46357913583001013.96------- ------ -------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Total 34.2016.49357913583001113.96
ELAPSED(총걸린시간) : CPU TIME+ WAIT TIME16=4+12
대기 시간이 (12)긴 것.. 원인은 아래에서 찾아보면 됨
Event waited on Count Zero Ela Elapse AVG(Ela) MAX(Ela) Blocks
---------------------------------------- -------- -------- ---------- ---------- ---------- --------
db file sequential read 600.010.000.016global cache cr request 448806.450.000.190SQL*Net message to client 200.000.000.000SQL*Net message from client 200.010.010.010
db file scattered read 224707.490.000.0935785
latch free1400.000.000.000================================
db file scattered read : FULLTABLE SCAN
이거 때문에 느린
인덱스가 8개나 있는데 느린것..
TQC_POSTITEM_RESULTM
IDX_QC_POSTITEM_RESULTM_01 1 NU B*T ITEM_CODE
IDX_QC_POSTITEM_RESULTM_02 2 NU B*T QC_GB_CODE + QC_POSTITEM_NO
IDX_QC_POSTITEM_RESULTM_03 3 NU B*T QC_GB_CODE + BALJU_NO + ITEM_CODE
IDX_QC_POSTITEM_RESULTM_04 4 NU B*T QC_GB_CODE + ITEM_CODE + BALJU_NO
IDX_QC_POSTITEM_RESULTM_05 5 NU B*T QC_GB_CODE + RECEIPT_NO + ITEM_CODE
IDX_QC_POSTITEM_RESULTM_06 6 NU B*T QC_GB_CODE + QC_POSTITEM_DATE + ITEM_CODE
IDX_QC_POSTITEM_RESULTM_07 7 NU B*T QC_POSTITEM_DATE + QC_GB_CODE + ITEM_CODE
PK_TQC_POSTITEM_RESULTM 8 UN B*T QC_POSTITEM_NO
위에 쿼리문 보면 인라인 뷰 때문에 느려짐
where조건에 a.balju_no 가 들어있으므로 인덱스에서 찾아보기
IDX_QC_POSTITEM_RESULTM_03 3 NU B*T QC_GB_CODE + BALJU_NO + ITEM_CODE
여기서 3번 결합 컬럼 인덱스가 문제였음
BALJU_NO 이게 앞으로 나와야함
-------------------------------
IDX_QC_POSTITEM_RESULTM_03 3 NU B*T BALJU_NO + QC_GB_CODE + ITEM_CODE
위와 같이 변경하거나
INDEX SKIP SCAN힌트를 주면 됨
결론
문제현황 및 원인)
- 인덱스 부족
인라인뷰 내부에서 조회건수는 0건이나 조회 조건 인덱스 부족으로
200 만건 가량 되는 테이블의 FULL SCAN으로 인해 성능이 저하됨
0TABLE ACCESS FULL OBJ#(97236) TQC_POSTITEM_RESULTM
개선안)
- 인덱스 추가 필요
TQC_POSTITEM_RESULTM 테이블에 BALJU_NO 컬럼 인덱스 추가 필요
IDX_QC_POSTITEM_RESULTM_08 : BALJU_NO + QC_POSTITEM_NO
- INDEX_SS 힌트를 사용
인라인 뷰 내부에 INDEX_SS (M IDX_QC_POSTITEM_RESULTM_03 ) 힌트를 추가 하여 인덱스
추가 없이 개선 가능
selectcount(*)
from mcustsum t
where salegb='A';
@p--결합컬럼 인덱스의 두번째 컬럼이 where절에 있는 상태------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows| A-Rows| A-Time| Buffers |Reads|------------------------------------------------------------------------------------------------------------|0|SELECT STATEMENT ||1||1|00:00:00.07|3367|3353||1| SORT AGGREGATE ||1|1|1|00:00:00.07|3367|3353||*2| INDEX FAST FULL SCAN| M_SALEGB_SALEMM |1|600K|100K|00:00:00.06|3367|3353|------------------------------------------------------------------------------------------------------------
버퍼의 개수 : 3367
예제2) 아래의 SQL을 index skip scan 으로 유도하세요.
select/*+ index_ss(t m_salemm_salegb)*/count(*)
from mcustsum t
where salegb ='A';
-------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows| A-Rows| A-Time| Buffers |Reads|-------------------------------------------------------------------------------------------------------|0|SELECT STATEMENT ||1||1|00:00:00.03|302|440||1| SORT AGGREGATE ||1|1|1|00:00:00.03|302|440||*2| INDEX SKIP SCAN| M_SALEMM_SALEGB |1|600K|100K|00:00:00.02|302|440|-------------------------------------------------------------------------------------------------------
버퍼의 개수 : 302개
문제풀며 익히기
Q1.
사원 테이블의 부서번호의 종류가 몇개가 있는지 출력하시오
selectcount( distinct deptno )
from emp;
COUNT(DISTINCTDEPTNO)
---------------------3--이 종류가 작을수록 idex skip scan의 효과가 증대
![[TUNING-1]튜닝 1(1~3장)](https://image.inblog.dev?url=https%3A%2F%2Finblog.ai%2Fapi%2Fog%3Ftitle%3D%255BTUNING-1%255D%25ED%258A%259C%25EB%258B%259D%25201%281%7E3%25EC%259E%25A5%29%26logoUrl%3Dhttps%253A%252F%252Finblog.ai%252Finblog_logo.png%26blogTitle%3DHye-Min%2520Son&w=2048&q=75){kind=logo}
