Microsoft Word - SQL튜닝_실습교재_.doc

* 실습환경 * 1. 오라클데이터베이스의튜닝실습을하기위해서는기본적인테이블과데이터가필요합니다. 다음과같은절차에의해환경설정을하십시오. 1) 강사가제공하는 Export 된파일 (scott.dmp) 을자신의 ORACLE 경로에저장하십시오. [C: ] cd C: ORACLE ORA92 BIN [C: ] dir scott.dmp scott.dmp 2) SYSTEM 사용자로접속하여 SCOTT 사용자계정을재생성하십시오. [C: ] SQLPLUS system/manager SQL> DROP USER scott cascade; SQL> CREATE USER scott IDENTIFIED BY tiger; SQL> GRANT connect, resource, dba TO scott; SQL> connect scott/tiger SQL> @c: oracle ora92 rdbms admin utlxplan.sql SQL> EXIT 실행계획을저장할파일 3) Import 툴을사용하여 scott.dmp 파일을 Restore 하십시오. [C: ] imp system/manager full=y file=scott.dmp.. importing table "ACCOUNT" 28969 rows imported.. importing table "ACCOUNT1" 28969 rows imported.. importing table "BIG_DEPT" 289 rows imported.. importing table "BIG_EMP" 28955 rows imported.. importing table "DEPT" 4 rows imported.. importing table "EMP" 14 rows imported 2. Import된각테이블에대한구조를분석해봅시다. 테이블구조에대한정확한이해는튜닝을보다쉽게해줄수있습니다. [C: ] SQLPLUS scott/tiger

FROM TAB; TNAME TABTYPE ------------------------------ ------- BIG_DEPT TABLE 개인고객부서테이블 BIG_EMP TABLE 개인고객테이블 DEPT TABLE 기업고객부서테이블 EMP TABLE 기업고객테이블 Import 된각테이블의구조와행수에대해분석하십시오. SQL> DESC BIG_EMP Name Null? Type -------------------------------------------------- EMPNO NOT NULL NUMBER(5) 개인고객코드 ENAME JOB NOT NULL VARCHAR2(10) 고객명 VARCHAR2(9) 직무 MGR NUMBER(4) 담당관리자사번 HIREDATE DATE 가입일 SAL COMM NUMBER(7,2) 월급여금액 NUMBER(7,2) 커미션 DEPTNO NUMBER(3) 부서코드 GROUPNO CHAR(1) 그룹코드 (1,2) SQL> DESC BIG_DEPT Name Null? Type ------------------------------------------------- DEPTNO NOT NULL NUMBER(3) 고객부서코드 DNAME NOT NULL VARCHAR2(14) 부서명 LOC VARCHAR2(13) 지역 SQL> DESC EMP BIG_EMP 와동일한컬럼구조 SQL> DESC DEPT

1. 튜닝도구. SET AUTOTRACE ON 명령어이명령어는오라클버전 7.3 이후에추가된기능입니다. 방금설명했던 EXPLAIN PLAN 명령어는 PLAN_TABLE 테이블을생성하고 EXPLAIN PLAN 명령어에의해실행계획을분석한다음 SELECT문에의해결과를참조하게됩니다. SET AUTOTRACE ON 명령어는 PLAN_TABLE을생성한후한번만설정해주면 SQL문이실행될때마다실행계획을화면에출력해줍니다. 이기능을해제할때는 SET AUTOTRACE OFF 명령어를실행하면됩니다. 다음은 SET AUTOTRACE 명령어문법입니다. < 문법 > SET AUTOTRACE [ON OFF TRACE TRACEONLY] [ON] 은 SQL문의실행결과와실행계획그리고통계정보를보여주는옵션입니다. [OFF] 는어떤결과도보여주지않습니다. [TRACEONLY] 는실행계획과통계정보만을보여줍니다. 그럼, 다음예제와같이따라해보세요. [C: ] sqlplus /as sysdba SQL>@C: ORACLE ORA90 sqlplus admin plustrce.sql SQL> grant plustrace to scott; SQL> connect scott/tiger SQL>@C: ORACLE ORA90 rdbms admin utlxplan.sql SQL> set autotrace trace; SQL> select * from big_emp where deptno = 10; ID Operation Options Object -- ------------- ----------- --------------- 0 SELECT STATEMENT Cost = 1 TABLE ACCESS1 FULL BIG_EMP SQL> truncate table plan_table; SQL> set autotrace off;

2. Rule-Based Optimizer 1) 실행하고자하는 SQL문을 Rule-Based 옵티마이저로실행한다. (Ranking에의한우선순위원칙 ) [C: ] SQLPLUS scott/tiger SQL> CREATE INDEX i_big_emp_deptno ON big_emp(deptno); normal SQL> CREATE UNIQUE INDEX I_big_emp_empno ON big_emp(empno); unique SQL> alter session set optimizer_mode = rule; SQL> set autotrace trace; SQL> select ename from big_emp and empno between 100 and 200 3 2 INDEX (RANGE SCAN) OF 'I_BIG_EMP_DEPTNO' (NON-UNIQUE) 우선순위가가장높은 'I_BIG_EMP_DEPTNO' 선택단일열컹럼의인덱스 : 9 범위로검색하는인덱스 : 10 테이블전체스캔 : 15 SQL> select ename from big_emp where deptno >= 20 and deptno <= 30 and empno = 100 0 SELECT STATEMENT Optimizer=RULE

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'BIG_EMP' 2 1 INDEX (UNIQUE SCAN) OF 'I_BIG_EMP_EMPNO' (UNIQUE) 우선순위가가장높은 'I_BIG_EMP_EMPNO' 선택 UNIQUE 인덱스 : 4 단일열컬럼의인덱스 : 9 테이블전체스캔 : 15 2) 같은동등조건의경우에는모든인덱스를사용한후결합한다. SQL> DROP INDEX i_big_emp_empno; SQL> CREATE INDEX I_big_emp_empno ON big_emp(empno); SQL> select ename from big_emp and empno = 100 0 SELECT STATEMENT Optimizer=RULE 3 2 AND-EQUAL 4 3 INDEX (RANGE SCAN) OF 'I_BIG_EMP_DEPTNO'(NON-UNIQUE) 5 3 INDEX (RANGE SCAN) OF 'I_BIG_EMP_EMPNO' (NON-UNIQU) WHERE 절에정의된조건부터읽어서각각의결과를결합하여검색한다. 3) 동등조건이아닌여러개의부분범위조건은가장나중에만들어진인덱스를사용한다 SQL> DROP INDEX i_big_emp_deptno; SQL> DROP INDEX I_big_emp_empno; SQL> CREATE INDEX I_big_emp_empno ON big_emp(empno); 이번엔 EMPNO 인덱스를먼저생성한다.

SQL> CREATE INDEX i_big_emp_deptno ON big_emp(deptno); SQL> select ename from big_emp where deptno < 20 and empno > 100 0 SELECT STATEMENT Optimizer=RULE 3 2 INDEX (RANGE SCAN) OF 'I_BIG_EMP_DEPTNO' (NON-UNIQUE) 비동등조건중에서나중에만들어진 I_BIG_EMP_DEPTNO 만검색한다. 4) 같은검색조건이지만하나의조건이변형되면나쁜실행계획을기준으로실행된다. SQL> CREATE INDEX I_emp_ename ON big_emp (ename); FROM BIG_EMP WHERE ename = MARTIN OR SUBSTR(ename,1,1) = F; 0 SELECT STATEMENT Optimizer=RULE 1 0 TABLE ACCESS (FULL) OF 'BIG_EMP' 인덱스조건을 SUBSTR 함수로변형했기때문에앞의조건도인덱스를사용하지못하는경우입니다. SQL> exit;

4. Hint 절에의한실행계획의변경 [C: ] sqlplus scott/tiger SQL> CRATE INDEX I_big_emp_deptno ON big_emp(deptno); SQL> CRATE UNIQUE INDEX I_big_emp_empno ON big_emp(empno); SQL> ANALYZE TABLE big_emp COMPUTE STATISTICS; SQL> ANALYZE INDEX I_big_emp_deptno COMPUTE STATISTICS; SQL> ANALYZE INDEX I_big_emp_empno COMPUTE STATISTICS; Sql> alter session set optimizer_mode = choose; Sql> set autotrace trace; SQL> select ename from big_emp and empno between 100 and 200 0 SELECT STATEMENT Optimizer=HINT: ALL_ROWS 3 2 INDEX (RANGE SCAN) OF 'I_BIG_EMP_EMPNO' (UNIQUE) 가장작은비용의인덱스 'I_BIG_EMP_EMPNO' 를통해데이터를검색합니다. 1) RULE : 공식기반옵티마이저에의한실행계획으로 SQL 문을실행할때사용합니다. SQL> select /*+RULE*/ ename from big_emp and empno between 100 and 200

SELECT STATEMENT Optimizer=RULE 3 2 INDEX (RANGE SCAN) OF 'I_BIG_EMP_DEPTNO' (NON-UNIQUE) HINT절에의한공식기반옵티마이저가 I_BIG_EMP_DEPTNO 인덱스를선택합니다. 앞에서실행된비용기반옵티마이저의 HINT절결과와비교해보십시오. 2) FIRST_ROWS : 비용기반옵티마이저에의한실행계획으로 SQL문을실행할때조건을만족하는첫번째행을가장빠르게검색할수있는방법으로실행계획을결정합니다. SQL> select /*+FIRST_ROWS*/ ename from big_emp and empno between 100 and 200 0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS 3 2 INDEX (RANGE SCAN) OF 'I_EMP_EMPNO' (UNIQUE) HINT 절에의해비용기반옵티마이저로실행계획을결정합니다. SQL> drop index I_big_emp_deptno; SQL> drop index I_big_emp_empno; 3) ALL_ROWS : 비용기반옵티마이저에의한실행계획으로 SQL문을실행할때조건을만족하는모든행을가장빠르게검색할수있는방법으로실행계획을결정합니다. SQL> select /*+ALL_ROWS*/ ename from big_emp

and empno between 100 and 200 0 SELECT STATEMENT Optimizer=HINT: ALL_ROWS 3 2 INDEX (RANGE SCAN) OF 'I_BIG_EMP_EMPNO' (UNIQUE) HINT 절에의해비용기반옵티마이저로실행계획을결정합니다. SQL> drop index I_big_emp_deptno; SQL> drop index I_big_emp_empno; 4) FULL : 해당 SQL 문은무조건 FULL TABLE SCAN 방법으로실행계획을결정합니다. SQL> select /*+FULL(big_emp)*/ ename from big_emp and empno between 100 and 200 2 1 TABLE ACCESS (FULL) OF 'BIG_EMP' HINT 절에의해테이블전체스캔으로데이터를검색합니다. 5) INDEX_DESC SQL> select /*+INDEX_DESC(big_emp i_big_emp_deptno)*/ ename

from big_emp and empno between 100 and 200 3 2 INDEX (RANGE SCAN DESCENDING) OF 'I_BIG_EMP_DEPTNO' 'I_BIG_EMP_DEPTNO' 인덱스를내림차순으로변경하여실행계획을결정합니다. 6) INDEX_ASC : 인덱스의타입에는올림차순인덱스도있습니다. INDEX_DESC HINT와마찬가지로반드시올림차순인덱스로검색을해야하는경우사용할수있습니다. SQL> select /*+INDEX_ASC(big_emp i_big_emp_deptno)*/ ename from big_emp and empno between 100 and 200 3 2 INDEX (RANGE SCAN) OF 'I_BIG_EMP_DEPTNO' (NON-UNIQUE)

5. 비용기반옵티마이저 1) 실행하고자하는 SQL 문을 Cost-Based 옵티마이저로실행한다. [C: ] SQLPLUS scott/tiger SQL> ANALYZE TABLE big_emp COMPUTE STATISTICS; <- 통계정보수집 SQL> ANALYZE INDEX i_big_emp_deptno COMPUTE STATISTICS; SQL> ANALYZE INDEX i_big_emp_empno COMPUTE STATISTICS; SQL> alter session set optimizer_mode = choose; SQL> set autotrace trace; <- Cost-Based 환경 SQL> select ename from big_emp and empno between 100 and 200 3 2 INDEX (RANGE SCAN) OF 'I_BIG_EMP_EMPNO' (UNIQUE) 가장작은비용의인덱스 'I_BIG_EMP_EMPNO' 선택 2) 이번에는테이블의통계정보를삭제한후비용기반옵티마이저로실행해봅시다. SQL> ANALYZE TABLE big_emp DELETE STATISTICS; SQL> ANALYZE INDEX i_big_emp_deptno DELETE STATISTICS; SQL> ANALYZE INDEX i_big_emp_empno DELETE STATISTICS; SQL> alter session set optimizer_mode = choose; SQL> set autotrace trace; <- 통계정보삭제 <- Cost-Baed 환경 SQL> select ename from big_emp and empno between 100 and 200

SELECT STATEMENT Optimizer=CHOOSE 3 2 INDEX (RANGE SCAN) OF 'I_BIG_EMP_DEPTNO' (NON-UNIQUE) 통계정보가없으면정확한비용계산을할수없기때문에공식기반옵티마이저를적용한결과와동일하게됩니다. 비용기반옵티마이저환경에서는반드시 ANALYZE 명령어에의해통계를생성해주어야합니다.

6. INDEX 를사용하지못하는경우 1) 인덱스가있는컬럼을표현식또는함수로변형을시키면인덱스를사용할수없습니다. [C: ] sqlplus scott/tiger SQL> CREATE INDEX i_dept_dname ON dept(dname); SQL> SET AUTOTRACE TRACE; FROM DEPT WHERE SUBSTR(DNAME,1,3) = 'ABC' ; ------------------------------------------------- 1 0 TABLE ACCESS (FULL) OF 'DEPT' 그렇다면인덱스컬럼을변형시키지않고검색할수있는방법은없을까요? 다음예제는인덱스를사용할수있도록적절한 SQL문을작성한경우입니다. FROM DEPT WHERE DNAME LIKE 'ABC%'; 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' 2 1 INDEX (RANGE SCAN) OF 'I_DEPT_DNAME' (NON-UNIQUE) SQL> DROP INDEX i_dept_dname; 2)!= ( 부정연산자 ) 를사용하면인덱스를사용할수없습니다. 왜냐하면, 인덱스는검색하고자하는컬럼의데이터가전체데이터의약 10% 의범위에있을때가장빠르게검색할수있는데부정 (!=, <>) 의의미는 4~10% 의범위를벗어난범위를의미하기때문에인덱스가있다하더라도오라클서버는전체테이블스캔으로검색하며인덱스를사용하지않습니다.

SQL> CREATE INDEX i_emp_job ON emp(job); FROM EMP WHERE ENAME LIKE 'F%' AND JOB <> 'SALES' ; ------------- 1 0 TABLE ACCESS (FULL) OF 'EMP' 다음은부정문을사용하지않고행을검색할수있도록문장을최적화시킨경우입니다. FROM EMP a WHERE a.ename LIKE 'F%' AND NOT EXISTS ( SELECT '' FROM EMP b WHERE (a.ename = b.ename) AND (b.job = 'SALESMAN')); 1 0 FILTER 2 1 TABLE ACCESS (FULL) OF 'EMP' 3 1 INDEX (RANGE SCAN) OF 'I_EMP_JOB' (NON-UNIQUE) SQL> DROP INDEX i_emp_job; 3) IS NULL을사용하면인덱스를사용할수없습니다. 왜냐하면, 인덱스를생성하면인덱스에는 NULL 값은포함되지않기때문에 IS NULL의의미는인덱스가없음을의미하고전체테이블스캔으로데이터를검색합니다. SQL> CREATE INDEX i_emp_ename ON emp(ename); FROM EMP

WHERE ENAME IS NOT NULL ; 1 0 TABLE ACCESS (FULL) OF 'EMP' 다음과같이인덱스를사용할수있도록 SELECT 문을변형해보십시오. FROM EMP WHERE ENAME > ' '; 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' 2 1 INDEX (RANGE SCAN) OF 'I_EMP_ENAME' (NON-UNIQUE) SQL> DROP INDEX i_emp_ename; 4) 와일드카드로시작하는 LIKE문은인덱스를사용할수없습니다. 왜냐하면, 검색해야할범위를알수없기때문에오라클서버는전체테이블스캔이더빠른검색을해줄수있다고판단하여인덱스를사용하지않습니다. 단, 와일드카드 (%) 로끝나는검색조건에는인덱스가사용됩니다.( 예, SELECT ~ WHERE name LIKE ' 주 % ) SQL> CREATE INDEX i_emp_job ON emp(job); FROM EMP WHERE JOB LIKE '%AB'; ------------------------------------------- 1 0 TABLE ACCESS (FULL) OF 'EMP' 만약, 반드시와일드카드를사용해야하는경우라면전체테이블스캔을보다빠르게수행

할수있도록 DB_FILE_MULTIBLOCK_READ_COUNT 파라메터를높게설정하십시오. SQL> alter session set db_file_multiblock_read_count = 32; SQL> DROP INDEX i_emp_job; 5) 오라클 8i 이전버전까지는인덱스가생성되어있는컬럼을다른컬럼과함께산술실, 함수를사용하면인덱스를사용할수없었습니다. SQL> CREATE INDEX i_emp_sal ON emp(sal); FROM EMP WHERE nvl(sal, 0) < 4000; ------------------------------------------ 1 0 TABLE ACCESS (FULL) OF 'EMP' SAL 컬럼의인덱스를사용하지못함 자 ~ 이번에는함수기반인덱스를생성해봅시다. SQL> CREATE INDEX i_emp_sal_nvl ON emp(nvl(sal, 0)); 함수기반인덱스의생성 SQL> alter session set QUERY_REWRITE_ENABLED = true; SQL> alter session set QUERY_REWRITE_INTEGRITY = trusted; 환경설정을해야사용가능 SQL> ANALYZE TABLE emp COMPUTE STATISTICS; FROM EMP WHERE (nvl(sal, 0)) < 4000; SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' 2 1 INDEX (RANGE SCAN) OF 'I_EMP_SAL_NVL' (NON-UNIQUE) SQL> DROP INDEX i_emp_sal_nvl;