#32_140728_TUNING_옵티마이져 연산자

# 옵티마이져 연산자

l data를 엑세스 하는 방법

 

• FULL TABLE SCAN

l full table scan (힌트 : /*+ full(테이블명) */ )

1.     high water mark까지 스캔하는 방법

2.     full table scan이 유리한 이유

: 테이블에서 엑세스 하고자 하는 데이터가 많을 때

3.     full table scan을 할 수 밖에 없는 경우

1)     인덱스가 없을 때

2)     테이블의 통계정보를 수집할 때

3)     인덱스를 생성할 때

4)     full 힌트를 생성할 때

l full table scan을 빠르게 하는 방법 2가지

1.     full table scan에 관련된 오라클 파라미터의 값을 조정한다

- db_file_multiblock_read_count : 이 파라미터는 full table scan을 할 때 한번에 읽어들이는 블록의 개수를 결정

2.     병렬로 작업한다 : 힌트사용(/*+ parallel(emp,4) */)

l 파라미터를 조절하여 full table scan 속도 테스트

1.     SQL> set timing on

2.     SQL> create table sales300

   as

   select * from sh.sales;

3.     SQL> insert into sales300

   select * from sh.sales;

4.     SQL> insert into sales300

   select * from sales300;

5.     SQL> commit;

6.     SQL> alter session set db_file_multiblock_read_count=16;

7.     SQL> create table sales400

as select * from sales300;

8.     SQL> @flush.sql

9.     SQL> drop table sales400 purge;

10.   SQL> connect scott/tiger

11.   SQL> alter session set db_file_multiblock_read_count=128;

12.   SQL> set timing on

13.   SQL> create table sales400

as select * from sales300;

SQL> connect /as sysdba SQL> alter session set db_file_multiblock_read_count=16; SQL> connect scott/tiger SQL> create table sales400 as select * from sales300; Elapsed: 00:00:54.73 SQL> @flush.sql SQL> drop table sales400 purge; SQL> connect scott/tiger SQL>alter session set db_file_multiblock_read_count=128; SQL> create table sales400 as select * from sales300; Elapsed: 00:00:49.83 - 위의 수행결과를 보면 db_file_multiblock_read_count을 수행하면 속도가 빨라진 것을 확인 할 수 있다

• ROWID SCAN

l rowid에 의한 스캔 (힌트 : /*+ rowid(테이블명) */ )

- rowid : 해당 row의 물리적 주소(file# + block# + row#)

SQL> SQL> select rowid, empno, ename sal from emp; ROWID                   EMPNO SAL ------------------ ---------- ---------- AAAVl7AAFAAAAG7AAA       7839 KING AAAVl7AAFAAAAG7AAB       7698 BLAKE ………………………………………………….

- rowid에 의한 스캔은 하나의 로우를 스캔하는 가장 빠른 방법이다

l 인덱스의 구조

- 컬럼값 + rowid

- 컬럼값은 데이터가 ascending으로 저장되어있다.

- 예 : SQL> select ename, rowid

       from emp

       where ename > ‘   ‘;

SQL> SQL> select rowid, empno, ename sal from emp; ROWID                   EMPNO SAL ------------------ ---------- ---------- AAAVl7AAFAAAAG7AAA       7839 KING AAAVl7AAFAAAAG7AAB       7698 BLAKE AAAVl7AAFAAAAG7AAC       7782 CLARK AAAVl7AAFAAAAG7AAD       7566 JONES AAAVl7AAFAAAAG7AAE       7654 MARTIN ………………………………………………

문제8. 사원 테이블에서 이름에 EN 또는 IN을 포함하고 있는 사원들의 이름과 월급과 직업을 출력하시오

CREATE INDEX emp_ename ON EMP(ename);

SELECT ename, sal, job

FROM EMP

WHERE ename like '%EN%' OR ename LIKE '%IN%';

l index 를 만들어줬지만 수행결과 full table scan을 하는 것을 확인할 수 있다. 결국 위의 쿼리는 악성 SQL이다

문제10. 이름이 EN 또는 IN을 포함하고 있는 사원들의 rowid 만 출력하는 쿼리가 아래와 같은데 이 쿼리를 rowid를 이용해서 사원의 이름과 월급과 직업을 출력하는 쿼리를 다시 작성하시오

SELECT ROWID

FROM EMP

WHERE ename like '%EN%' OR ename LIKE '%IN%';

  

SELECT ename, job, sal FROM EMP WHERE ROWID IN ('AAAVl7AAFAAAAG7AAF', 'AAAVl7AAFAAAAG7AAA', 'AAAVl7AAFAAAAG7AAE');

SELECT ename, job, sal FROM EMP WHERE ROWID IN - 알아낸 rowid로 스캔  (SELECT ROWID FROM EMP  WHERE ename like '%EN%' OR ename LIKE '%IN%'); - 인덱스만 scan해서 rowid 조회

l 변형기가 아래와 같이 쿼리를 변경했기 때문에 위의 쿼리가 full table scan을 확인할 수 있다

- SELECT ename, sal, job FROM EMP WHERE ename like '%EN%' OR ename LIKE '%IN%';

SELECT ename, job, sal

FROM EMP

WHERE ROWID IN

 (SELECT /*+ no_query_transformation */ ROWID FROM EMP

 WHERE ename like '%EN%' OR ename LIKE '%IN%');

l no_query_transformation 힌트를 이용하면 변형기가 변경하여 수행하는 것을 막을 수 있고 속도를 빠르게 할 수 있다

l 위의 수행결과를 최종적으로 조인으로 변경해야 한다

l 여기서 filter 수행의 의미는 메인 쿼리를 수행했다는 것이다.

• 샘플 테이블 스캔

SQL> SELECT COUNT(*) FROM sales100;   COUNT(*) ----------     918843 SQL> SELECT COUNT(*) FROM sales100 SAMPLE BLOCK(10);   COUNT(*) ----------      52623 - sample block(10)은 조회된 데이터의 10%만 조회하겠다는 의미이다

• B-TREE INDEX

문제11. 아래의 SQL을 수행하여 sales500 테이블을 생성하고 sales500의 SQL 속도를 높이기 위해서RN 컬럼의 인덱스를 생성하고 아래의 퀴리를 조회하시오(sales500는  7350744개의 데이터가 들어있는 테이블이다)

SQL> create table sales400

as

select * from sh.sales;

SQL> INSERT INTO sales400

 SELECT * FROM sales400;

SQL> INSERT INTO sales400

 SELECT * FROM sales400;

SQL> DROP TABLE sales500 purge;

SQL> CREATE  TABLE sales500

AS

SELECT  ROWNUM  AS rn , sales400.*,

 ROUND(DBMS_RANDOM.VALUE *1000) AS  profit

 FROM sales400;

#쿼리 조회

SQL> SELECT * FROM sales500

WHERE rn = 2929153;

l 튜닝 전 SELECT * FROM sales500 WHERE rn = 2929153; - 이 쿼리를 수행하면 6초가 걸린다 - 결국 위 쿼리는 아래의 쿼리와 같다 SELECT select /*+ full(sales500) */ * FROM sales500 WHERE rn = 2929153; l 튜닝 후 alter session set db_file_multiblock_read_count=128; alter session set workarea_size_policy = manual; alter session set sort_area_size=1000000000; CREATE INDEX sales500_rn ON sales500(rn); SELECT * FROM sales500 WHERE rn = 2929153; - 위 설정을 수행한 후 조회하면 1초도 안걸리는 것을 확인할 수 있다 - 위 과정은 아래의 쿼리와 같다 SELECT /* index(sales500 sales500_rn) */ * FROM sales500 WHERE rn = 2929153;

l SQL gate에서 자동 추적

- 튜닝을 위해서는 시간과 블록정보를 비교할 수 있는데 정확한 것은 블록정보를 가지고 비교하는 것이다

- 왜냐하면 DB의 부하정도에 따라서 걸리는 시간은 달라질 수 있기 때문이다

1.     index table scan

SQL> SELECT /*+ index(sales500, sales500_rn) */ * FROM sales500

WHERE rn = 2929153;

db block gets        0	메모리에서 읽은 버퍼의 개수
consistent gets       45	메모리에서 읽은 버퍼의 개수
physical reads        0	디스크에서 읽은 블록의 개수

2.     full table scan

SELECT /*+ full(sales500) */ *

FROM sales500

WHERE rn = 2929153;

문제12. 아래의 SQL의 튜닝전후를 통계지표로 비교하시오

select /* full(emp) */ ename, sal, job from emp where sal = 3000;	db block gets        0 consistent gets       62 physical reads        0
select /*+ index(emp, emp_sal) */ ename, sal, job from emp where sal = 3000;	db block gets        0 consistent gets       61 physical reads        0