Oracle 10g SQL , PL/SQL 튜닝

Transcription

1 Special Key Note Oracle 9i &10g New features SQL & PL/SQL & DBMS Tuning ( 주 ) 오픈메이드컨설팅 오동규책임컨설턴트 1

2 목차 PART 1 개요 DBMS 시스템튜닝의목표 DBMS 시스템튜닝의주체 DBMS 시스템튜닝의순서 DBMS 시스템튜닝을위한기본사항 PART 2 9i & 10g New features ANSI SQL Join Partition Outer Join Rollup Cube Grouping Set Multi Table Insert Merge With Regular Expression Model DBA 1D DBA 1D DBA 1D DBA 1D DBA 1D DBA 1D DBA 1D DBA 1D Using Inline view In DML DBA 1D Array Processing Using Returning Clause Analytic Function New Connect By Functionality External Table Partition New Features User-Specified Quote Character Assignment NLS_SORT DBA 1D DBA 1D DBA 1D DBA 1D Bulk Bind Improvements Sorted Hash Clusters Nested table function DBA 1D DBA 1D DBA 1D DBA 1D DBA 1D 2

3 목차 PART 4 SQL 튜닝 튜닝툴의사용 (explain plan, autotrace, tkprof, 실행계획의이해 ) sql 엑세스패턴 sql 조인패턴 DBA 1D DBA 1D 서브쿼리를이용한데이터연결 Optimizer( 구조, 작동방식 ANALYZE, DBMS_STAT) Query Transform DBA 1D > M-VIEW QUERY REWRITE > SUB QUERY UNNESTING > VIEW MERGING > PUSH PREDICATE > OR-EXPANSION > STAR QUERY TRANSFORMATION > Partition Pruning ( 이미설명 ) > EVENT 부분범위처리 BIND 변수를사용하라.( 예외사항 ) IN 을이용한엑세스효율화 DBA 1D 제약조건이 PLAN 에미치는영향 HINT DBA 1D DBA 1D DBA 1D DBA 1D DBA 1D 3

4 목차 PART 5 PL/SQL 튜닝 PL/SQL 을쓰는이유. 패키지의사용의당위성. %TYPE 과 %ROWTYPE 을이용하라. NATIVE COMPILER 의사용. DBMS_SHARED_POOL 의사용 결과집합을 CLIENT 에반환하는것은 REF CURSOR 를사용하라. BULK COLLECT. FORALL FORALL 및 BULK COLLECT 와의연동 DML 후의 SELECT 는 RETURNNING 절로대체하라. 인자를대량으로넘길때는 NOCOPY Hint 를사용하라. 대용량데이터처리 function 에서는 PARALLEL_ENABLE 힌트를사용하라 compile-time warnings Pipelined Function DBA 1D DBA 1D DBA 1D DBA 1D DBA 1D DBA 1D DBA 1D DBA 1D DBA 1D DBA 1D DBA 1D 4

5 PART1 개요 PART 1 개요 > DBMS 시스템튜닝의목표 > DBMS 시스템튜닝의주체 > DBMS 시스템튜닝의순서 > DBMS 시스템튜닝을위한기본사항 5

6 튜닝의목표 PART1 개요 > 구축및운영 System 이최적의자원으로최적의성능 ( 시간 / 응답속도 ) 을발휘할수있도록설계와구현응용시스템을조화롭게유지개선하는기술 즉, 문제가되는요인을조기에발견하여데이터베이스로부터사용자가만족할만한정보를얻게하기위해정보시스템을개발하고유지하기위한것 > 시스템종류에따라세부목표가다를수있다 OLTP 에서는동시사용자수의최대화 OLAP MART 에서는 Best Throughtput DW 에서는 ETL 최적화 6

7 DBMS 시스템튜닝의주체 PART1 개요 > 요구사항분석가 (Business Analyst) > 데이터베이스설계자 (Database Designer) > 응용프로그램개발자 (Application Developer) > 데이터베이스관리자 (Database Administrator) > 소프트웨어 / 하드웨어관리자 (System Administrator) 7

8 DBMS 시스템튜닝의순서 ( 효과가큰순 ) PART1 개요 1. 비즈니스튜닝실제적인데이터베이스에서일어나는튜닝이아니고업무의흐름에대한튜닝을의미함. 불필요한공정등을제거함으로서생산시간과생산단가, 생산인력을줄이는것을비즈니스튜닝이라고한다. 2. 디자인튜닝데이터베이스가분석되고설계되는단계에서만들어지는데이터베이스의논리적구조 ( 테이블의구조, 테이블의크기, 인덱스여부및종류등 ) 가좋은성능을기대할수없게만들어진경우의튜닝을의미함. 또는, 애플리케이션프로그램이개발될때프로그램이좋은성능을발휘할수있도록만들어지지못한경우이다. 3. 애플리케이션튜닝데이터베이스에서사용자가실행한 SQL 문 (SELECT, UPDATE, INSERT, DELETE) 의실행원리와실행방법을제대로모르고사용한다면좋은성능은기대할수없는것임. 이러한 SQL 문을분석하여성능을발휘할수있도록조율하는것을애플리케이션튜닝이라고한다. 4. 서버튜닝데이터베이스의메모리영역과물리적구조에관해튜닝하는방법. 이러한원리와마찬가지로데이터베이스메모리영역의크기가처리하려고하는테이블의데이터크기보다작아서성능이저하되는문제가발생하는경우에메모리를더크게할당해주게되는데이런문제를조율하는것을서버튜닝이라고한다. 5. 시스템튜닝 UNIX, 윈도우와같은운영체제에서시스템의성능향상을위해조율하는방법을의미함. 8

9 PART1 개요 DBMS 시스템튜닝을위한기본사항 > 모델및업무파악 > SQL, PL/SQL 에정통 > 집합이론에충실 > DBMS 구조및신기능 (9i, 10g) > 스키마생성및관리전략 > 옵티마이져아키텍쳐및옵티마이져에영향을미치는 Factor > 조인및 Acces 패턴 > 튜닝툴의사용 9

10 PART 2 New features PART 2 9i & 10g New features > ANSI SQL Join > Partition Outer Join > Rollup > Cube > Grouping Set > Multi Table Insert > Merge > With > Regular Expression > Model > Using Inline view In DML > Array Processing > Using Returning Clause > Analytic Function > New Connect By Functionality > External Table > Partition New Features > User-Specified Quote Character Assignment > NLS_SORT > Bulk Bind Improvements > Sorted Hash Clusters > Nested Table Functions 10

11 ANSI SQL Join(SQL 1999) PART 2 New features > JOIN TYPE (9i) INNER : 두테이블을조인하여양쪽에모두만족하는결과를반환 ( default ) > from emp e join dept d > on ( e.deptno= d.deptno ) LEFT OUTER : INNER 조인결과와왼쪽에대응되지않는행들을반환 > (LEFT 가기준 ) > from emp e left outer join dept d > on ( e.deptno= d.deptno ) RIGHT OUTER : INNER 조인결과와오른쪽에대응되지않는행들을반환 > (RIGHT 가기준 ) > from emp e right outer join dept d > on ( e.deptno= d.deptno ) FULL OUTER : INNER, LEFT OUTER, RIGHT OUTER 결과를모두반환 > 첫번째집합을기준으로 outer join 을한다음, 두번째집합을기준으로 ANTI 조인을한다. > from emp e full outer join dept d > on ( e.deptno= d.deptno ) Cross Join, Natural Join, Using 11

12 ANSI SQL Join(SQL 1999) PART 2 New features > 2.ON 절 : 조인컬럼들간의조인관계및조인하기위한조건 Equal Join 이아니더라도상관없음 Example: SELECT p.name part_name, c.inv_class inv_class FROM part p JOIN inventory_class c ON ( p.unit_cost BETWEEN c.low_cost AND c.high_cost) ; 1. ON 절에 Driving Table 의조건있는경우 select empno, ename, dname, d.deptno from emp_1 e right outer join dept d on ( e.deptno= d.deptno and d.deptno > 10 ) 조건에만족하지않아도드라이빙집합에참여하지만조인은하지않는다. Rows Execution Plan SELECT STATEMENT GOAL: CHOOSE 15 NESTED LOOPS (OUTER) 6 TABLE ACCESS (FULL) OF 'DEPT' 11 VIEW 11 TABLE ACCESS (FULL) OF 'EMP_1' 12

13 ANSI SQL Join(SQL 1999) PART 2 New features > 2.ON 절 2. WHERE 절에 Driving Table 의조건이있는경우 select empno, ename, dname, d.deptno from emp_1 e right outer join dept d on ( e.deptno= d.deptno ) where d.deptno > 10 조건에만족하지않는건은결과에서제외됨 Rows Execution Plan SELECT STATEMENT GOAL: CHOOSE 14 HASH JOIN (OUTER) 5 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' 5 INDEX (RANGE SCAN) OF 'PK_DEPT' (UNIQUE) 15 TABLE ACCESS (FULL) OF 'EMP_1' 13

14 ANSI SQL Join(SQL 1999) PART 2 New features > 2.ON 절 3. ON 절에 Drived Table 의조건이있는경우 select empno, ename, dname, d.deptno from emp_1 e right outer join dept d on ( e.deptno= d.deptno and e.empno > 7600 ) 조건에만족하는건만조인한다 Rows Execution Plan SELECT STATEMENT GOAL: CHOOSE 13 HASH JOIN (OUTER) 6 TABLE ACCESS (FULL) OF 'DEPT' 11 TABLE ACCESS (BY INDEX ROWID) OF 'EMP_1' 11 INDEX (RANGE SCAN) OF 'EMP_1_PK' (UNIQUE) * ON 절에있는조건을만족하는건을추출후 outer join 을하므로 e.empno is null 조건은필요없다 14

15 ANSI SQL Join(SQL 1999) PART 2 New features > 2.ON 절 4. WHERE 절에 Drived Table 의조건이있는경우 select empno, ename, dname, d.deptno from emp_1 e right outer join dept d on ( e.deptno= d.deptno ) where e.empno > 7600 or e.empno is null 조인한후에조건을체크하므로 empno is null 조건이있어야한다. Rows Execution Plan SELECT STATEMENT GOAL: CHOOSE 13 FILTER 17 HASH JOIN (OUTER) 6 TABLE ACCESS (FULL) OF 'DEPT' 15 TABLE ACCESS (FULL) OF 'EMP_1' 결론 * Drived Table 의조건은 WHERE 절보다 ON 절에명시하는것이유리하다. 15

16 ANSI SQL Join(SQL 1999) PART 2 New features > Cross Join Cartesian Product 와같다. 조인방법은힌트로조정가능하며, 플랜은 CROSS JOIN 을명시하지않은 SQL 과같다. select empno, ename, dname, dept.deptno from emp cross join dept Plan : SELECT STATEMENT MERGE JOIN CARTESIAN TABLE ACCESS FULL BUFFER SORT TABLE ACCESS FULL DEPT 16

17 ANSI SQL Join(SQL 1999) PART 2 New features > Natural Join 2 개의테이블에서같은이름을가진컬럼들로 euqal join 을한다. ( 동일이름의컬럼이여러개있을경우, 모두 euqal join 함 ) 조인키인동일컬럼들은 alias 사용할수없다. 조인방법은힌트사용으로조정가능하며, Natural join syntax 사용한쿼리와그렇지않은쿼리의플랜은동일하다. select empno, ename, dname, deptno from emp natural join dept Plan : SELECT STATEMENT Hint=CHOOSE NESTED LOOPS TABLE ACCESS FULL EMP TABLE ACCESS BY INDEX ROWID DEPT INDEX UNIQUE SCAN PK_DEPT 82 17

18 ANSI SQL Join(SQL 1999) PART 2 New features > Using 절 조인할키를지정하는것으로, 2 개의테이블에동일컬럼중에조인할키들을명시하면, 이키들로 equal join 한다. Natural join 시모든동일컬럼이조인키가아닐때사용한다. Using 에사용된동일컬럼은 alias 와같이사용할수없다. NATURAL JOIN 과같이사용할수없다. SELECT p.name, pt.name, product_type_id FROM products p INNER JOIN product_types pt USING (product_type_id); 18

19 Partition Outer Join(10g) PART 2 New features > 개념 년별또는월별집계데이터를나타낼때특정년혹은특정월에데이터가없더라도생성하여나타냄. 주로 OLAP 에서많이활용함 요구사항 테이블구조 Select yymm From Year_month Where yymm = 2002 Select deptno, yymm, sale_amt from dept_sale_history 19

20 Partition Outer Join(10g) PART 2 New features 1. 부서번호별로빠진년월을생성 SELECT e.deptno, m.yymm, NVL(e.sale_amt,0) FROM year_month m LEFT OUTER JOIN dept_sale_history e PARTITION BY (e.deptno) ON (m.yymm = e.yymm ) WHERE m.yymm like '2002% ; 2. 기존방식 SELECT e.deptno, m.yymm, NVL(e.sale_amt,0) FROM year_month m LEFT OUTER JOIN dept_sale_history e ON (m.yymm = e.yymm AND e.deptno = 10) WHERE m.yymm like '2002% Union all SELECT e.deptno, m.yymm, NVL(e.sale_amt,0) FROM year_month m LEFT OUTER JOIN dept_sale_history e ON (m.yymm = e.yymm AND e.deptno = 20) WHERE m.yymm like '2002% ; 해결방법 20

21 Rollup :n+1 LEVEL 의 SUBTOTAL 생성 PART 2 New features > Rollup Basic(8i) select deptno, job, class, sum(sal) from emp group by rollup(deptno, job, class) > 1.Composite rollup (9i) select deptno, job, class, sum(sal) from emp group by rollup( (deptno, job), class) ; > 기존방식 select deptno, job, class, sum(sal) from emp group by deptno, job, class union all select deptno, job, null, sum(sal) from emp group by deptno, job union all select deptno, null, null, sum(sal) from emp group by deptno union all select null, null, null, sum(sal) from emp; *(deptno, job) 을한컬럼처럼처리함. > 2. 기존방식 select deptno, job, class, sum(sal) from emp group by deptno, job, class union all select deptno, job, null, sum(sal) from emp group by deptno, job union all select null, null, null, sum(sal) from emp; 21

22 Rollup PART 2 New features select deptno, job, class, sum(sal) from emp group by rollup(deptno, job, class) select deptno, job, class, sum(sal) from emp group by rollup( (deptno, job), class) 22

23 Rollup Concatenated Groupings(9i) PART 2 New features select deptno, job, class, sum(sal) from emp group by rollup(deptno, job), rollup(class) select deptno, job, class, sum(sal) from emp group by rollup(deptno), rollup( job, class) > 해석 group by rollup(deptno,job) union all group by class, deptno,job union all group by class, deptno union all group by class > 해석 group by rollup( job, class) union all group by deptno, job, class union all group by deptno, job union all group by deptno 23

24 Cube > 특징 :group by 절의컬럼에대해모든가능한조합생성 PART 2 New features group by 절의컬럼에대해모든가능한조합생성 group by 절의컬럼의수가 n 이라면, 2n LEVEL의 SUBTOTAL를생성 > 사용법 SELECT deptno, job, COUNT (*) cnt FROM emp GROUP BY CUBE (deptno, job) > 해석 GROUP BY deptno, job Union all GROUP BY deptno Union all GROUP BY job Union all Group by null 24

25 Cube :group by 절의컬럼에대해모든가능한조합생성 PART 2 New features > 결과 GROUP BY DNAME LEVEL JOB LEVEL GRAND TOTAL DNAME JOB Total CNT ACCOUNTING MANAGER 1 ACCOUNTING PRESIDENT 1 ACCOUNTING All Jobs 2 RESEARCH ANALYST 2 RESEARCH CLERK 2 RESEARCH MANAGER 1 RESEARCH All Jobs 5 SALES CLERK 1 SALES MANAGER 1 SALES SALESMAN 4 SALES All Jobs 6 OPERATIONS CLERK 1 OPERATIONS All Jobs 1 All Departments ANALYST 2 All Departments CLERK 4 All Departments MANAGER 3 All Departments PRESIDENT 1 All Departments SALESMAN 4 All Departments All Jobs 14 25

26 Rollup & Cube PART 2 New features GROUPING FUNCTION > GROUP BY 에지정된 EXPRESSION 중하나와일치해야함 > Grouping 함수를사용하지않으면 SubTotal 의구분과 Level 을알수없음. > GROUP BY 에의한 STANDARD AGGREGATION 은 0, > ROLLUP 과 CUBE 에의하여생성된 SUBTOTAL 은 1 을반환 SELECT DECODE(a.deptno deptno,1,'all Departments',b.dname dname) dname, job, cnt Total CNT FROM ( SELECT DECODE(grouping grouping(deptno), ),1,1,deptno) AS deptno, DECODE(grouping grouping(job), 1, 'All Jobs', job) AS job, COUNT(*) cnt FROM EMP GROUP BY rollup (deptno, job) ) a, dept b WHERE a.deptno=b.deptno(+) 26

27 Rollup & Cube PART 2 New features 유의사항 > GROUPPING COLUMNS 의수는 255 까지가능하나지나친 COLUMN 의지정은 RESOURCE 에많은부담을주므로피해야함 > HAVING 절은 ROLLUP 과 CUBE 에영향을받지않으며 RESULT SET 의 SUBTOTAL 과 NON SUBTOTAL 에모두적용가능. > ORDER BY 절은 ROLLUP 이나 CUBE 의사용에영향을받지않으며, 모든 RESULT SET 에적용가능 > PL/SQL 이하에서사용하기위해서는 Dynamic SQL 를사용 27

28 Groupping Set PART 2 New features > 다차원분석을위한집합생성시사용자가원하는집합을 선택적 으로생성할수있음. (rollup 이나 cube 는집합이강제된다.) > Rollup 이나 Cube 보다해석이쉬움 > 사용법 GROUP BY GROUPING SETS (a, b) GROUP BY a UNION ALL GROUP BY b GROUP BY GROUPING SETS ((a, b, c), (a, b), ()) GROUP BY (a, b, c) UNION ALL GROUP BY (a, b) UNION ALL GROUP BY () 28

29 Groupping Set PART 2 New features 사용예제 SELECT GROUPING(DEPTNO) NO, DECODE(GROUPING(DEPTNO),0,TO_CHAR(DEPTNO),JOB) as GRP, SUM(SAL) FROM EMP GROUP BY GROUPING SETS (DEPTNO, JOB) ; JOB Grouping SELECT 0 no, to_char(deptno), SUM(SAL) FROM EMP GROUP BY DEPTNO UNION ALL SELECT 1 no, job, SUM(SAL) FROM EMP GROUP BY JOB; DEPT Grouping 29

30 Groupping Set PART 2 New features 사용예제 SELECT GROUPING_ID(DEPTNO, JOB) NO, DECODE(GROUPING_ID(DEPTNO, JOB),1,TO_CHAR(DEPTNO), 2, JOB, 3, 'TOTAL SUM') as GRP, SUM(SAL) FROM EMP GROUP BY GROUPING SETS (DEPTNO, JOB, ()) ; Job Grouping Dept Grouping Grand Total SELECT 1 no, to_char(deptno), SUM(SAL) FROM EMP GROUP BY DEPTNO UNION ALL SELECT 2 no, job, SUM(SAL) FROM EMP GROUP BY JOB UNION ALL SELECT 3 no, 'TOTAL SUM', SUM(SAL) FROM EMP 30

31 Groupping Set PART 2 New features GROUPING_ID() Division_id Job_id Bit Vector GROUPING_ID() Non-null Non-null 00 0 Non-null Null 01 1 Null Non-null 10 2 Null Null 11 3 SQL> SELECT division_id, job_id, 2 GROUPING(division_id) AS div_grp, 3 GROUPING(job_id) AS job_grp, 4 GROUPING_ID (division_id, job_id) AS grp_id, 5 SUM (salary) 6 FROM employees2 7 GROUP BY CUBE(division_id, job_id); 31

32 Groupping Set PART 2 New features GROUPING_ID() DIV JOB DIV_GRP JOB_GRP GRP_ID SUM(SALARY) ENG MGR PRE TEC WOR BUS BUS MGR BUS PRE BUS WOR OPE OPE ENG OPE MGR OPE WOR SAL SAL MGR SAL WOR SUP SUP MGR SUP TEC SUP WOR

33 Groupping Set PART 2 New features 정리 33

34 Multi Insert(9i) > 개념 하나의 Insert 문에서여러개의 table 에동시에 Insert 할수있음 FIRST 일경우첫번째로만족하는 WHEN 절만타고 RETURN 된다. WHEN 조건절도생략가능. 생략할경우무조건 INSERT 됨 PART 2 New features INSERT ALL/FIRST -- ALL 이나 FIRST 중선택. 생략시 DEFAULT 로 ALL [when 조건절1 then into [table1] values (col1,col2,,,) ] [when 조건절2 then into [table2] values (col1,col2,..) ] [else into [table2] values (col1,col2, )] [SUB-QUERY]; INSERT ALL/FIRST WHEN ottl < THEN INTO small_orders VALUES(oid, ottl, sid, cid) WHEN ottl > and ottl < THEN INTO medium_orders VALUES(oid, ottl, sid, cid) ELSE INTO large_orders VALUES(oid, ottl, sid, cid) /* SOURCE-QUERY 구문 */ SELECT o.order_id oid, o.customer_id cid, o.order_total ottl, o.sales_rep_id sid, c.credit_limit cl, c.cust_ cem FROM orders o, customers c WHERE o.customer_id = c.customer_id; 34

35 Merge(9i) > 개념 한번의 SELECT 로 INSERT 와 UPDATE 를동시에할수있음 when matched then 절에 delete 문사용가능 (v10g) PART 2 New features merge into [Target table][target table명의 alias] 1 using [ Source table/view/subquery][source table/view/subquery의 alias] 2 on [join 조건 ] 3 when matched then 4 update set col1=value1 col2=value2 delete where col3 like 10% and when not matched then 5 insert (col명, ) values (value1, ); 1 Insert나 Update가될Target table명및alias 를정의한다 2 다른table data를검색및비교한다면 Source table명을 alias를함께정의 (view 및 subquery도사용가능 ) 3 Target table 과 Source table 간 join 조건을 on 절에정의 4 조인조건에의해만족하는 data가존재한다면 (matched) 미리정의된 update문을실행 5 만약만족하는 data가없다면 (not matched) insert문에의해 data는새롭게입력 35

36 Merge(9i) PART 2 New features MERGE INTO products p USING product_changes pc ON ( p.product_id = pc.product_id ) WHEN MATCHED THEN UPDATE SET p.product_type_id = pc.product_type_id, p.name = pc.name, p.description = pc.description, p.price = pc.price WHEN NOT MATCHED THEN INSERT ( p.product_id, p.product_type_id, p.name, p.description, p.price ) VALUES ( pc.product_id, pc.product_type_id, pc.name, pc.description, pc.price ); < SQL 예제 > 36

37 WITH(9i) PART 2 New features > 개념 WITH 구문을이용한 SUB-QUERY 의활용 WITH [inline_view이름1] AS (SELECT col1 FROM t1 where col1), [inline_view이름2] AS (SELECT col1 FROM t2 where col1) SELECT col1 FROM inline_view이름1 WHERE col1 > (SELECT col1 FROM inline_view이름2 WHERE col1); WITH dept_costs AS ( SELECT department_name, SUM(salary) dept_total FROM employees e, departments d WHERE e.department_id = d.department_id GROUP BY department_name ), avg_cost AS ( SELECT SUM(dept_total)/COUNT(*) avg FROM dept_costs ) SELECT * FROM dept_costs WHERE dept_total > ( SELECT avg FROM avg_cost ) ORDER BY department_name; 37

38 Using Inlinview In DML PART 2 New features > 제한사항 DISTINCT 처리가포함된경우 그룹함수를사용한경우 : SUM, MIN, MAX, AVG, COUNT, STDDEV, VARIANCE, GLB등 analytic function을사용한경우 집합처리 : UNION, UNION ALL, INTERSECT, MINUS MODEL절을사용한경우 GROUP BY나 HAVING을사용한경우 순환관계전개처리 : CONNECT BY START WITH 구문 ROWNUM을사용한경우 여러개의뷰와조인한뷰는실행시반드시최종뷰내로병합 (Merge) 이가능해야한다. 조인된테이블중에서반드시 키보존 (Preserve) 테이블 만수정할수있다. 38

39 Array Processing PART 2 New features > 한번 DBMS 호출에여러건의 DATA 를처리하는방법 > DBMS 호출 (CALL) 은시스템 OVERHEAD 의주범 > ARRAY PROCESSING 은시스템 OVERHEAD 를감소 > ARRAY 단위는사용자가지정가능, 지나치면 OVERHEAD 발생 > 한번 FETCH 시여러건을동시에액세스 > 액세스와동시에데이터의가공처리 > 여러건을동시에 INSERT 혹은 UPDTAE > 8i 부터사용가능. 39

40 Array Processing PART 2 New features declare type ridarray is table of rowid; type vcarray is table of t2.object_name%type; 배열선언 l_rids ridarray; l_names vcarray; 배열변수선언 cursor c is select rowid, object_name from t2; begin open c; loop fetch c bulk collect into l_rids, l_names LIMIT 100; forall i in 1.. l_rids.count update t2 set object_name = lower(l_names(i)) where rowid = l_rids(i); 배열단위지정한번에 100 건 fetch exit when c%notfound; end loop; close c; commit; end; / 40

41 Using Returning Clause PART 2 New features > DML 문의변경값을 SELECT 하지않고메모리에서읽어온다. RETURNING : 단일값이아닌여러값 (ROWS) 을 RETURN 한다면 BULK COLLECT 를사용하여야한다. CREATE OR REPLACE PROCEDURE update_salary IS TYPE ename IS TABLE OF scott.emp.ename%type; TYPE sal IS TABLE OF scott.emp.sal%type; t_ename ename; t_sal sal; i number; BEGIN UPDATE emp SET sal = sal * 1.1 RETURNING ename, sal bulk collect INTO t_ename, t_sal; dbms_output.put_line('update rows :' t_ename.last); V9i for i in 1.. t_ename.count loop dbms_output.put_line('ename :' t_ename(i) ' sal :' t_sal(i)); end loop; end; / 41

42 Using Returning Clause PART 2 New features > V10g 부터집계함수사용가능. CREATE OR REPLACE PROCEDURE update_salary1 IS V_AVG_SAL NUMBER(12); t_ename ename; t_sal sal; i number; BEGIN UPDATE emp SET sal = sal * 1.1 RETURNING SUM(sal) INTO V_AVG_SAL; V10g dbms_output.put_line( avg(sal) = to_char(v_avg_sal)); end; / 42

43 Analytic Function PART 2 New features 작년대비올해의매출실적을비교하시오. A 직원의판매순위는전체에서의몇퍼센트에포함이되는가? 연간판매실적에서 1 월달의판매실적은전체에서의몇퍼센트인가? > 위의쿼리결과를얻기는간단해보이나 self-join등비싼연산을필요로한다. > Analytic function을쓰는이유 쿼리성능향상 개발자의생산성향상 사용하기위해배우는노력의최소화 표준화된문법 일반 SQL로의표현한계 디버깅, 유지관리의어려움 43

44 Analytic Function PART 2 New features > 분석함수의순서 JOIN, WHERE, GROUP BY, HAVING Analytic Functions Last ORDER BY of query SELECT job, deptno, sum(sal) s1, (2) RANK() OVER (PARTITION BY job ORDER BY SUM(sal) DESC) s2 (3) FROM emp WHERE sal < 5000 GROUP BY job, deptno HAVING sum(sal) > 1000 (1) ORDER BY job, s2 (4) 44

45 Analytic Function PART 2 New features 종류 > 랭킹 (Ranking) 함수 - RANK, DENSE_RANK, ROW_NUMBER() - CUME_DIST, PERCENT_RANK - Ntile/Percentile > 윈도우 (window) 함수 (Running summary, Moving Average ) > REPORTING 함수 : 서로다른두가지의 Aggregation level을비교하고자하는목적으로사용하는 Function family -RATIO_TO_REPORT - LAG/LEAD() - Hypothetical Rank/Distribution 45

46 Analytic Function PART 2 New features 문법 > analytic clause - partitioning -ordering -windowing <FUNCTION> (<argument>) OVER (<analytic clause>) 46

47 Analytic Function PART 2 New features RANK() and DENSE_RANK() > 사용자가지정한범위 ( 파티션 ) 내에서순위를리턴해준다. > RANK() 와 DENSE_RANK() 의차이 - RANK() : 동률을이루는값이있을경우해당하는숫자만큼은비워두고다음순위를매긴다. - DENSE_RANK() : 동률을이루는값이있더라도연속된순위를매긴다. > 이때 NULL은 default로가장큰값으로취급한다. > NULLS LAST/ NULLS FIRST 로널값의순위를변경할수있다. 47

48 Analytic Function PART 2 New features SQL> SELECT 2 prd_type_id, SUM(amount), 3 RANK() OVER (ORDER BY SUM(amount) DESC) AS rank, 4 DENSE_RANK() OVER (ORDER BY SUM(amount) DESC) 5 AS dense_rank 6 FROM all_sales 7 WHERE year= GROUP BY prd_type_id 9 ORDER BY prd_type_id; PRD_TYPE_ID SUM(AMOUNT) RANK DENSE_RANK

49 Analytic Function PART 2 New features SQL> SELECT 2 prd_type_id, SUM(amount), 3 RANK() OVER (ORDER BY SUM(amount) DESC NULLS 4 LAST) AS rank, 5 DENSE_RANK() OVER (ORDER BY SUM(amount) DESC 6 NULLS LAST) 7 AS dense_rank 8 FROM all_sales 9 WHERE year= GROUP BY prd_type_id 11 ORDER BY prd_type_id; PRD_TYPE_ID SUM(AMOUNT) RANK DENSE_RANK

50 Analytic Function PART 2 New features Partition by > PARTITON BY 절에명시한컬럼이작업처리의단위가된다. > 제품종류별매출액순위? SQL> SELECT prd_type_id, month, SUM(amount), 2 RANK() OVER(PARTITION BY prd_type_id ORDER BY 3 SUM(amount) DESC) AS rank 4 FROM all_sales 5 WHERE year= AND amount IS NOT NULL 7 GROUP BY prd_type_id, month 8 ORDER BY prd_type_id, month; 50

51 Analytic Function PART 2 New features Partition by PRD_TYPE_ID MONTH SUM(AMOUNT) RANK

52 Analytic Function PART 2 New features Ntiles ( Equiheight buckets ) > 전체행들을분석함수내에서의 ORDER BY 절에서지정한순서에맞춰사용자가지정한버킷수만큼나눈다. > 매출액 TOP 25% 를구하라. select empno, ename, sal, NTILE(4) OVER (ORDER BY sal desc) sales_quartile from emp ; 52

53 Analytic Function PART 2 New features Percentile > 사용자가퍼센트를입력하여주면해당하는값을리턴한다. PERCENTILE_XXXX ( < 수치값 > ) WITHIN GROUP(ORDER BY 컬럼 ) > 수치값은 0~1 사이임. > PERCENTILE_CONT: 입력받은수치값이어느두값의사이일경우두값의사이에서계산된값을리턴한다. > PERCENTILE_DISC: 입력받은수치값이어느두값의사이일경우다른계산과정없이작은값을그대로리턴한다. > 단일 ROW 만 RETURN 한다. 53

54 Analytic Function PART 2 New features Percentile > 하위 40% 에해당하는급여는얼마인가? select PERCENTILE_CONT(0.4) WITHIN GROUP (ORDER BY (sal) ) CONT, PERCENTILE_DISC(0.4) WITHIN GROUP (ORDER BY (sal) ) DISC from emp 54

55 Analytic Function PART 2 New features Window 함수 > 전체행이아닌사용자가지정한범위내에서만연산이일어나도록하게한다. > Window 의범위는partition by 범위를벗어날수없다. FUNCTION OVER (ORDER BY 컬럼 ROWS BETWEEN XXX PRECEDING AND XXX FOLLOWING CURRENT ROW) COL1 COL2 COL XXXX XXXX XXXXX YYYY YYYY YYYYY ZZZZ ZZZZ ZZZZZ LLLL LLLL LLLLL MMMM MMMM MMMMM NNNN NNNN NNNNN QQQQ QQQQ QQQQQ PPPP PPPP PPPPP <- START_1 <- START_2 <- current row <- END_2 <- END_1 55

56 Analytic Function PART 2 New features Window 함수 SQL> SELECT month, SUM(amount) as month_amount, 2 SUM(SUM(amount)) OVER 3 (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING 4 AND CURRENT ROW) 5 as cumulative_amount 6 FROM all_sales 7 WHERE year= GROUP BY month 9 ORDER BY month; MONTH MONTH_AMOUNT CUMULATIVE_AMOUNT

57 Analytic Function PART 2 New features Window 함수 SQL> SELECT month, SUM(amount) as month_amount, 2 AVG(SUM(amount)) OVER 3 (ORDER BY month ROWS BETWEEN 1 PRECEDING 4 AND 1 FOLLOWING) 5 AS moving_average 6 FROM all_sales 7 WHERE year= GROUP BY month 9 ORDER BY month; MONTH MONTH_AMOUNT MOVING_AVERAGE <- CURRENT 57

58 Analytic Function PART 2 New features FIRST_VALUE() and LAST_VALUE() > 해당윈도우에서첫번째행의값과마지막행의값을찾는함수 SQL> SELECT month, SUM(amount) as month_amount, 2 FIRST_VALUE(SUM(amount)) OVER 3 (ORDER BY month ROWS BETWEEN 1 PRECEDING 4 AND 1 FOLLOWING)AS previous_month_amount, 5 LAST_VALUE(SUM(amount)) OVER 6 (ORDER BY month ROWS BETWEEN 1 PRECEDING 7 AND 1 FOLLOWING)AS next_month_amount 8 FROM all_sales 9 WHERE year= GROUP BY month 11 ORDER BY month; 58

59 Analytic Function PART 2 New features FIRST_VALUE() and LAST_VALUE() MONTH MONTH_AMOUNT PREVIOUS_MONTH_AMOUNT NEXT_MONTH_AMOUNT

60 Analytic Function PART 2 New features Reporting 함수 -RATIO_TO_REPORT() > 사용자가지정한범위내에서각값들이차지하는퍼센트율 SQL> SELECT month, prd_type_id, 2 SUM(amount) as prd_type_amount, 3 RATIO_TO_REPORT(SUM(amount)) OVER 4 (PARTITION BY month) AS prd_type_ratio 5 FROM all_sales 6 WHERE year= AND month <= 3 8 GROUP BY month, prd_type_id 9 ORDER BY month, prd_type_id; 60

61 Analytic Function PART 2 New features RATIO_TO_REPORT() MONTH PRD_TYPE_ID PRD_TYPE_AMOUNT PRD_TYPE_RATIO

62 Analytic Function PART 2 New features LAG() and LEAD() > Order by 컬럼기준으로이전값 / 다음값을구한다. > 전월대비판매실적과같은비교에유용하게쓰일수있다. SQL> SELECT month, SUM(amount) as month_amount, 2 LAG(SUM(amount), 1) OVER (ORDER BY month) 3 AS previous_month_amount, 4 LEAD(SUM(amount), 1) OVER (ORDER BY month) 5 AS next_month_amount 6 FROM all_sales 7 WHERE year= GROUP BY month 9 ORDER BY month; 62

63 Analytic Function PART 2 New features LAG() and LEAD() MONTH MONTH_AMOUNT PREVIOUS_MONTH_AMOUNT NEXT_MONTH_AMOUNT MONTH MONTH_AMOUNT PREVIOUS_MONTH_AMOUNT NEXT_MONTH_AMOUNT

64 Analytic Function PART 2 New features Hypothetical Rank and Distribution SQL> SELECT prd_type_id, SUM(amount), 2 RANK() OVER (ORDER BY SUM(amount) DESC) AS rank, 3 PERCENT_RANK() OVER (ORDER BY SUM(amount) DESC) 4 AS percent_rank 5 FROM all_sales 6 WHERE year= AND amount IS NOT NULL 8 GROUP BY prd_type_id 9 ORDER BY prd_type_id; PRD_TYPE_ID SUM(AMOUNT) RANK PERCENT_RANK

65 Analytic Function PART 2 New features Hypothetical Rank and Distribution > 매출액 50 만원이면몇등인가? SQL> SELECT RANK(500000) WITHIN GROUP(ORDER BY 2 SUM(amount) DESC)AS rank 3 FROM all_sales 4 WHERE year= AND amount IS NOT NULL 6 GROUP BY prd_type_id 7 ORDER BY prd_type_id; 65

66 New Connect By Functionality PART 2 New features > ORDER SIBLINGS BY(9i) 계층구조안에서레벨별로정렬한다 > SYS_CONNECT_BY_PATH(9i) FULL PATH 를나타낸다 ORDER SIBLINGS BY SELECT LEVEL, last_name, SYS_CONNECT_BY_PATH(last_name, '/') FROM employees START WITH employee_id = 100 CONNECT BY PRIOR employee_id = manager_id ORDER SIBLINGS BY last_name; SYS_CONNECT_BY_PATH 66

67 New Connect By Functionality(10g) PART 2 New features > connect_by_root ename 최상위계층의이름을나타낸다 > connect_by_isleaf 말단사원이면 1 아니면 0 을 return 한다 > connect_by_iscycle cycle 이면 1 아니면 0 을 return 한다 > NOCYCLE cycle 일경우에 cycle 을방지하고 cycle 에러를막는다주 ) connect_by_iscycle 과 NOCYCLE 은 connect by 의 LOOP 에러를방지하기위해사용 ORA-01436: CONNECT BY loop in user data CONNECT_BY_ISLEAF CONNECT_BY_ISCYCLE SELECT last_name "Employee", CONNECT_BY_ROOT last_name "Manager", DECODE(CONNECT_BY_ISLEAF, 1, 'Y', 'N') as LEAF_YN, DECODE(CONNECT_BY_ISCYCLE, 1, 'Y', 'N') as CYCLE_YN, SYS_CONNECT_BY_PATH(last_name, '/') "Path" FROM employees WHERE LEVEL > 1 and department_id = 110 CONNECT BY NOCYCLE PRIOR employee_id = manager_id CONNECT_BY_ROOT 67

68 Bulk Bind Improvements(10g) PART 2 New features > 배열값중하나또는여러개가삭제될경우에에러방지. > Exception 처리가필요없음. 예제 ) DECLARE TYPE typ_famtyp IS TABLE OF families%rowtype; v_fam typ_famtyp; BEGIN SELECT * BULK COLLECT INTO v_fam FROM families; FOR rec IN 1..v_fam.LAST() LOOP IF v_fam(rec).emp_dep_code!=0 THEN v_fam.delete(rec); END IF; END LOOP; FORALL inds IN INDICES OF v_fam INSERT INTO employees VALUES v_fam(inds); END; / 68

69 Nested Table Functions PART 2 New features Function Return value Description x MULTISET INTERSECT [DISTINCT] y NESTED TABLE Performs an INTERSECT set operation on nested tables x and y, returning a nested table whose elements are in both x and y. x, y and the returned nested table must all be of the same type. The DISTINCT keyword forces the elimination of duplicates from the returned nested table. x MULTISET UNION [DISTINCT] y NESTED TABLE Performs a UNION set operation on nested tables x and y returning a nested table whose elements include all those in x as well as those in y. x, y and the returned nested table must all be of the same type. The DISTINCT keyword forces the elimination of duplicates from the returned nested table. SET(x) NESTED TABLE Returns nested table x without duplicate elements. x IS [NOT]A SET BOOLEAN Returns TRUE [FALSE] if the nested table x is composed of unique elements x IS [NOT] EMPTY BOOLEAN Returns TRUE [FALSE] if the nested table x is empty e [NOT] MEMBER [OF] x BOOLEAN Returns TRUE[FALSE] if an expression e is a member of the nested table x y [NOT] SUBMULTISET [OF] x BOOLEAN Returns TRUE [FALSE] if nested table y contains only elements that are also in nested table x. 69

70 PART 2 New features Nested Table Functions Cast(MultiSet(Scalar_subquery)) > Scalar_subquery 사용자함수와거의동일한개념으로사용되며단지사용자가생성한함수가아닌 SQL 문을사용한다. > MULTISET Scalar_Subquery 의결과집합을특정 Collection Type 으로변경한다. > CAST Multiset 과함께사용되며지정된 Type 의 Datatype 으로변환작업을수행한다. > 사용 DBMS Version : oracle 부터사용가능 > 기본 Syntax CAST( MULTISET(<select_statement select_statement>) AS <collection_type>) 70

71 PART 2 New features Nested Table Functions Cast(MultiSet(Scalar_subquery)) CREATE OR REPLACE TYPE SCOTT.EMP_INFO_TYPE AS OBJECT ( DEPTNO NUMBER(2), EMPNO NUMBER(4), ENAME VARCHAR(10), SAL NUMBER(7,2) ) ; CREATE OR REPLACE TYPE SCOTT.EMP_INFO_ARRAY_TYPE AS TABLE OF EMP_INFO_TYPE ; SELECT X.DNAME, Y.EMPNO, Y.ENAME, Y.SAL FROM (SELECT D.DEPTNO, DNAME, CAST(MULTISET(SELECT A.DEPTNO, A.EMPNO, A.ENAME, A.SAL FROM EMP A WHERE A.DEPTNO = D.DEPTNO) AS EMP_INFO_ARRAY_TYPE) AS SAL_INFO FROM DEPT D WHERE D.DEPTNO > 0 ) X, TABLE(SAL_INFO) Y WHERE X.DEPTNO = Y.DEPTNO(+) > 수행원리 1) multiset 으로서브쿼리를 Collection 으로 ( 집합생성 ) 2) CAST 타입변환 ( DEPTNO NUMBER(2), EMPNO NUMBER(4), ENAME VARCHAR(10), SAL NUMBER(7,2) ) 71

72 Nested Table Functions PART 2 New features Cast(MultiSet(Scalar_subquery)) 특징 : 주로온라인화면에서수행해야함. 1. 사용자함수를대체가능하며수행속도는사용자함수보다더유리 ( 조인으로수행한것과거의동일한수행속도를얻을수있다 ) 2. 사용자함수의 Single Rows, Column Return 의제한사항을해결함. (Multi Rows, Columns Return) 3. 수행된결과집합은조인에의해수행된것과동일하게나타남. (Subquery 에대한조인실패시 Main 결과집합을추출할수없지만 Outer 조인으로해결할수있음. 1*M=M, M:M =MM, 1:1 =1, M*1 =1 ) 4. 부분범위처리가가능수행방식은조인방식과동일하지만 Nest Loop 조인으로수행되어야만효율을극대화할수있음 ( 반드시선행집합의결과를받아서수행하는것이유리할경우 ) 5.Subquery 의결과컬럼을동일 SQL 문에반복사용해도 Subquery 는한번수행 (Internal Write 수행 ) 72

73 Nested Table Functions PART 2 New features Cast(MultiSet(Scalar_subquery)) 주의사항 1.Subquery 는선행집합과연결되는컬럼은인덱스가존재해야빠르다. ( 속도에영향 ) 2.Type 에정의컬럼의수와 Datatype 이반드시동일해야만한다. ( 또한 Subquery 결과컬럼을재사용하기위해서는 TYPE 에정의컬럼명과동일해야만한다.) 3.Subquery 결과를얻기위해서는반드시 Table(type 명 ) 을 From 절에정의를내려야만한다. 4.Scalar_subquery 의 SQL Statement 에 Inline View 를사용할수가없다. 5. 대용량데이터처리시조인보다훨씬불리하다. (HASH 나 MERGE 가유리함 ) 73

74 PART 2 New features Nested Table Functions Cast(MultiSet(Scalar_subquery)) > COLLECT is new to Oracle10g and operates on a column in a SQL statement: CREATE TYPE _list_t AS TABLE OF VARCHAR2(64); -- COLLECT operates on a column SELECT CAST(COLLECT(cust_ )AS _list_t) FROM oe.customers; -- which is equivalent to SELECT CAST(MULTISET(SELECT cust_ FROM oe.customers) AS _list_t) FROM dual; 74

75 Nested Table Functions PART 2 New features > Examples of the other nested table functions DECLARE TYPE nested_type IS TABLE OF NUMBER; nt1 nested_type := nested_type(1,2,3); nt2 nested_type := nested_type(3,2,1); nt3 nested_type := nested_type(2,3,1,3); nt4 nested_type := nested_type(1,2,4); answer nested_type; BEGIN answer := nt1 MULTISET UNION nt4; -- (1,2,3,1,2,4) answer := nt1 MULTISET UNION nt3; -- (1,2,3,2,3,1,3) answer := nt1 MULTISET UNION DISTINCT nt3; -- (1,2,3) answer := nt2 MULTISET INTERSECT nt3; -- (3,2,1) answer := nt2 MULTISET INTERSECT DISTINCT nt3; -- (3,2,1) answer := nt3 MULTISET EXCEPT nt2; -- (3) answer := nt3 MULTISET EXCEPT DISTINCT nt2; -- () answer := SET(nt3); -- DUP 제거 (2,3,1) IF (nt1 IS A SET) AND (nt3 IS NOT A SET) THEN UNIQUE 검사 dbms_output.put_line('nt1 has unique elements'); dbms_output.put_line('but nt3 does not'); END IF; IF (nt3 MULTISET EXCEPT DISTINCT nt2) IS EMPTY THEN dbms_output.put_line('empty set'); END IF; IF 3 MEMBER OF (nt3 MULTISET EXCEPT nt2) THEN dbms_output.put_line('3 is in the answer set'); END IF; IF nt1 SUBMULTISET nt3 THEN dbms_output.put_line('nt1 is a subset of nt3'); END IF; IF SET(nt3) IN (nt1,nt2,nt3) THEN dbms_output.put_line( 'expression is IN the list of nested tables'); END IF; END; 75

76 PART 4 SQL 튜닝 PART 4 SQL 튜닝 튜닝툴의사용 (explain plan, autotrace, tkprof, 실행계획의이해 ) sql 엑세스패턴 sql 조인패턴 서브쿼리를이용한데이터연결 Optimizer( 구조, 작동방식 ANALYZE, DBMS_STAT) Query Transform > M-VIEW QUERY REWRITE > SUB QUERY UNNESTING > VIEW MERGING > PUSH PREDICATE > OR-EXPANSION > STAR QUERY TRANSFORMATION > Partition Pruning ( 이미설명 ) > EVENT 부분범위처리 BIND 변수를사용하라.( 예외사항 ) IN 을이용한엑세스효율화 제약조건이 PLAN 에미치는영향 HINT 76

77 Explain Plan 명령어 PART 4 SQL 튜닝 > 실행계획 (EXPLAIN PLAN) 을확인할때사용하는튜닝도구 실행계획 (EXPLAIN PLAN) > SQL 문이처리되는구문분석단계에서서버프로세스에의해해당 SQL 문이어떻게실행되는것이가장빠른지를결정하는과정 EXPLAIN PLAN 명령어를사용하여확인할수있는내용 > 데이터베이스에서 SQL 문이실행될때의상태정보 > 실행경로에대한정보 > SQL 문의 WHERE 절과 FROM 절의변화에대한정보제공 > EXPLAIN PLAN 명령어를실행하기전에분석결과를저장하기위한 PLAN_TABLE을생성해야함 PLAN_TABLE 은 UTLXPLAN.SQL 스크립트를사용하여생성 77

78 Explain Plan 명령어 PART 4 SQL 튜닝 > EXPLAIN PLAN 명령어구문 EXPLAIN PLAN [ INTO 테이블 _ 이름 ] FOR SQL_ 문 ; EXPLAIN PLAN [ INTO 테이블 _ 이름 ] SET STATEMENT_ID = 식별자 FOR SQL_ 문 ; STATEMENT_ID 절 > 여러명의사용자가동시에분석작업을하는경우분석된결과를구분하기위한식별자로 30자까지정의가능 INTO 절 > 분석결과를다른테이블에저장하기위해사용 78

79 Explain Plan 명령어 PART 4 SQL 튜닝 > EXPLAIN PLAN 명령어를사용하여분석하기 1. UTLXPLAIN.SQL 스크립트를실행하여 PLAN_TABLE 생성 WINDOWS 환경 : SQL> START c:\oracle\ora90\rdbms\admin\utlxplan.sql (UNIX 환경에서는 $ORACLE_HOME/rdbms/admin 에위치 ) 2. EXPLAIN PLAN 명령을사용하여 SQL 문의실행계획분석 > 실제쿼리가실행되는것이아님.( 평가만함 ) 79

80 Explain Plan 명령어 PART 4 SQL 튜닝 3. 실행계획에대한분석이끝나면분석결과를보기쉽도록만들기위해 PLAN_TABLE 을참조하기위한스크립트작성 WINDOWS 환경 : SQL> START c:\oracle\ora90\rdbms\admin\utlxplan.sql (UNIX 환경에서는 $ORACLE_HOME/rdbms/admin 에위치 ) 80

81 Explain Plan 명령어 PART 4 SQL 튜닝 4. 결과분석 EXPLAIN PLAN 명령어를사용하여분석된결과테이블 실행계획내부처리과정 81

82 Explain Plan 명령어 PART 4 SQL 튜닝 OPERATIONS 설명 FILTER SQL 문의 WHERE 절에정의된조건으로테이블의행을검색해줍니다. INDEX/RANGE 인덱스를이용하여테이블에있는데이터를검색합니다. INDEX/UNIQUE Unique-Index 또는 Primary-Key 에의한인덱스의테이블에있는데이터를검색합니다. SORT/MERGE 두개의테이블을조인하여분류 (SORTING) 합니다. SORT/GROUP BY SQL 문장의 GROUP BY 절에정의된컬럼으로데이터를분류 (SORTING) 합니다. SORT/JOIN 조인된각테이블의데이터를분류 (SORTING) 합니다. SORT/ORDER SQL 문장의 ORDER BY 절에정의된컬럼으로데이터를분류합니다. TABLE ACCESS/FULL 테이블전체스캔방법으로검색합니다. 82

83 AUTO TRACE PART 4 SQL 튜닝 > SET AUTOTRACE ON 명령어 EXPLAIN PLAN 명령과다른점은실재쿼리가수행된다는것. PLAN_TABLE 을생성한후한번만설정하면 SQL 문이실행될때마다실행계획을화면에출력 SET AUTOTRACE 명령어의문법 SET AUTOTRACE [ ON OFF TRACEONLY ] [EXP[LAIN]] [STAT[ISTICS]] ON > SQL 문의실행결과와실행계획, 통계정보를보여주는옵션 OFF > AUTOTRACE를해지하는옵션 TRACEONLY 옵션 > 실행계획과통계정보만을제공 83

84 AUTO TRACE PART 4 SQL 튜닝 SET AUTOTRACE 명령어실행결과 84

85 SQL*Trace PART 4 SQL 튜닝 > SQL*Trace EXPLAIN PLAN 명령어와함께애플리케이션튜닝에서자주사용되는기능 SQL 문의실행계획과더불어시스템을튜닝하는데필요한실행소요시간과디스크나메모리로부터읽은데이터블록수등에대한정보제공. > SQL*Trace 에의해생성된결과 이진 (binary) 형태의파일로운영체제의디렉토리에생성 생성된파일은 TKPROF 유틸리티를사용하면텍스트형태로변환하여참조가능 EXPLAIN PLAN 과병행하여사용하는것이좋다. > SQL*Trace 를사용하여분석된결과에포함되는분석정보 1. parse, execute, fetch 수 2. CPU 시간 / 경과된시간 3. 물리적 / 논리적 reads 4. 처리된로우수 5. 라이브러리캐시 misses 6. 파싱이발생할때의사용자 7. 커밋 / 롤백 85

86 SQL*Trace PART 4 SQL 튜닝 > SQL*TRACE 와관련된동적파라미터 (dynamic parameter) timed_statistics > CPU 시간, 실행시간등시간에관련된정보를표시하기위해사용 > 지속적인설정을위해파라미터파일 (init.ora) 에설정하거나세션에서만임시로설정하기위해 alter session set timed_statistics = true 로사용가능 max_dump_file_size > 트레이스파일의최대크기 ( 단위 : OS 블럭수 ) 로기본값은 unlimited > 지속적인설정을위해파라미터파일 (init.ora) 에설정하거나세션에서만임시로설정하기위해 alter session set max_dump_ file_ size =500 로사용가능. user_dump_dest > 트레이스파일이생성될디렉토리 > 지속적인설정을위해패러미터파일 (init.ora) 에설정하거나인스턴스에서만임시로설정하기위해 alter system set user_dump_dest=/tmp 로사용가능. 86

87 SQL*Trace PART 4 SQL 튜닝 > 사용절차 1. SQL_TRACE 파라미터설정 Init.ora 파일에 SQL_TRACE = TRUE 또는 SQL> ALTER SESSION SET SQL_TRACE = TRUE; 2. 분석할 SQL 문실행 SQL> SELECT * FROM emplyoess; 3. EXIT 명령어를사용하여세션을종료하고, USER_DUMP_DEST 파라미터에설정된경로에분석결과인트레이스파일이생성되었는지확인 > 세션이종료되는순간지정한디렉터리에 *.TRC 확장자를가진파일이생성 SQL> EXIT; user_dump_dest 에있는경로에생성된.trc 파일확인 87

88 TKPROF PART 4 SQL 튜닝 > TKPROF 유틸리티를사용하여텍스트파일로변환 TKPROF trace_file 명 output_file 명 [explain=user/passwd] [table=schema.tablename] [print=integer] [insert=filename] [sys=yes/no] [sort=option] > TKPROF 유틸리티옵션 옵션설명 trace_file output_file explain=user/passwd table=schema.tablename print=integer insert=filename sys=yes/no record=filename sort=option 생성된트레이스파일이름 tkprof 가출력하는텍스트파일이름 ( 확장자 :prf) 해당트레이스파일이수행된세션의사용자및패스워드 실행계획 (execution plan) 을저장할 TKPROF 임시테이블의이름 트레이스파일별로출력시킬 SQL 문의수 INSERT 문안의데이터와 SQL 문리스트 트레이스파일내에생성된 SQL 문중에서오라클서버가내부적인작업을위해수행된 SQL 문을출력할것인지설정 트레이스파일에분석된 SQL 문을지정한파일에저장 트레이스파일에분석된 SQL 문을지정한옵션에의해분류하여출력 88

89 TKPROF PART 4 SQL 튜닝 > TKPROF 분석결과 89

90 TKPROF PART 4 SQL 튜닝 > 분석결과에나타난컬럼설명 로우 / 컬럼 설명 Parse Execute Fetch count SQL 문이파싱되는단계에대한통계새로파싱했거나 Shared SQL Pool 에서찾아온것도포함 SQL 문의실행단계에대한통계 Update, Insert, Delete 문장들은여기에수행한결과만표시 SQL 문이실행되면서 fetch 된통계 SQL 문이 parse/execute/fetch 가수행된횟수 cpu parse, execute, fetch 가실제로사용한 CPU 시간 (1/100 초단위 ) elapsed disk query current rows 작업의시작에서종료시까지실제소요된시간 디스크에서읽혀진데이터블록의수 메모리내에서변경되지않은블록을읽거나다른세션에의해변경되었으나아직커밋되지않아복사해둔스냅샷블록을읽은블록수 SELECT 문에서는거의여기에해당하며 Update,Insert,Delete 작업시에는소량만발생 현세션에서작업한내용을커밋하지않아오로지자신에게만유효한블록 (Dirty Block) 을액세스한블록수주로 Update, Insert, Delete 작업시많이발생 SQL 문을수행한결과에의해최종적으로액세스된로우의수 ( 서브쿼리에서추출된로우는제외 ) 90

91 TKPROF PART 4 SQL 튜닝 > 결과분석 (Count) Call Parse 100 Execute Fetch Count LOOP 에서수행된 SQL 이거나 100 번수행된프로그램 기본키로처리되는 SQL HOLD_CURSOR 지정되지않음 Call Parse 1 Execute Fetch Count LOOP 에서수행된 SQL 100 번수행된프로그램 기본키로처리되는 SQL HOLD_CURSOR 지정, PL/SQL Call Parse 1 Execute Fetch Count 한번수행된 SQL 한번수행된프로그램 Pro*C CURSOR 로지정된 SQL PL/SQL CURSOR 지정된 SQL 91

92 TKPROF PART 4 SQL 튜닝 > 결과분석 (CPU) Call Parse 0.01 Execute Fetch CPU Rows 전체범위처리 ORDER BY, GROUP BY 넓은처리범위 비효율적인인덱스 Call CPU Parse 0.01 Rows 0 부분범위처리 넓은처리범위 Execute Fetch

93 TKPROF PART 4 SQL 튜닝 > Overall 의분석 (CPU) (Parse count * 0.01) 보다 cpu 가크면라이브러리캐쉬튜닝검토 (Parse count *.0.03) 보다 disk 가크면딕셔너리캐쉬튜닝검토 > Overall 의분석 (MEMORY) (Execute disk + Fetch disk) 가 (Execute query + Fetch query + Execute current + Fetch current) 의 10% 이상이면버퍼캐쉬튜닝검토 93

94 SQL Accses Pattern PART 4 SQL 튜닝 Full Table Scan > 발생 아무런조건없이 Table 을읽게한경우 인덱스가걸려있지않은컬럼에대해서조건주고 Table 을읽게한경우 인덱스가걸려있는컬럼에조건을부여했을지라도 Optimizer 가 Full Table Scan 이유리하다고판단한경우 > Access 방식 테이블의첫 Row 가들어있는 Block 부터 HWM(High Water Mark) 까지읽는다 한번에 DB_FILE_MULTIBLOCK_READ_COUNT 에서정한크기만큼읽는다 94

95 SQL Accses Pattern PART 4 SQL 튜닝 Index Unique Scan > 발생 Unique Index 를구성하고있는모든 Key 값에대해서 Equal(=) 로조건이공급된경우발생한다 > Access 방식 해당조건을만족하는값하나만읽는다 95

96 SQL Accses Pattern PART 4 SQL 튜닝 Index Range Scan > 발생 Non-Unique Index 를 Access 하는경우 Unique Index 를구성하고있는컬럼중일부컬럼에만값이공급된경우 Unique Index 에 Range 조건 (like, between, >, <, >=, <=) 으로값이공급되는경우 > Access 방식 해당조건을만족하는범위 + 아닌값하나 (1PlusScan) 를읽게된다. Range 조건이들어온경우 Index 구성순서상이후에있는컬럼에공급된조건들은작업범위를줄이는데작용하지못한다 96

97 INDEX RANGE SCAN DESCENDING 역순으로데이터를액세스한다는것을제외하면인덱스범위스캔과동일하다. 스캔의시작점은최대값이고, 역순으로리프블록을스캔하여최소값이될때까지수행한다. 정렬처리작업을대신해서사용하면매우효율적이다. INDEX_DESC(table_alias index_name) 을사용한다. SELECT /*+ INDEX_DESC(emp EMP_EMPNO_IDX) */ empno, ename FROM emp WHERE empno BETWEEN 10 AND 20 Execution Plan SELECT STATEMENT ALL_ROWS-Cost : 4 TABLE ACCESS BY INDEX ROWID SCOTT.EMP(1) INDEX RANGE SCAN DESCENDING SCOTT.EMP_EMPNO_IDX (EMPNO) 97

98 SQL Accses Pattern PART 4 SQL 튜닝 Index Full Scan > 발생 Optimizer 가 Full Table Scan 하고 Sort 하는것보다는 Index Full Scan 해 Sort 작업을따로수행하지않는것이유리하다고판단한경우 > Access 방식 해당인덱스의모든 Block 을한번에한 Block 씩순차적으로읽어내려간다.(Single Block I/O) 98

99 SQL Accses Pattern PART 4 SQL 튜닝 Index Fast Full Scan > 발생 Where 절이나 Select 절에사용된컬럼이모두하나의인덱스에구성된컬럼인경우 결합 Index 의경우최소한한 Column 이 NOT Null 로지정되어있어야한다 > Access 방식 인덱스 Leaf Block 을한번에 DB_FILE_MULTIBLOCK_READ_COUNT 에서정한크기씩끝까지읽어내려가며결과값의 Sort 가보장되지않는다 Parallel 로수행가능하다 Full Table Scan 보다읽어야할 Block 의수가적어유리하다 99

100 INDEX SKIP SCAN 결합인덱스에서첫번째컬럼이사용되지않으면무조건인덱스스캔이불가능하였다. 하지만 9i 부터인덱스스킵스캔으로가능해짐. Index Skip Scan 의원리 1 어떤테이블의인덱스가 sal_typ( 매출유형 ), ), item_cd( 상품코드 ), ), sal_dt( 매출일자 ) 로구성되어있다고가정했을경우 2 쿼리의조건에서 item_cd 와 sal_dt 만사용되었다. 3 sal_typ 의값이 D( 내수 ),E( 수출 ),L( 로컬 ) 이렇게세종류만있다고가정을하였을경우. Index Skip Scan 을적용하면.... 4sal_typIN ( D, E, L ) 의조건을추가한것과동일한효과를얻을수있다. - 결국생락된첫번째컬럼의값이조건절에자동으로추가되는것과유사한효과가난다. -WHERE sal_typ= D and item_cd = UNION ALL WHERE sal_typ = E and item_cd = UNION ALL WHERE sal_typ = L and item_cd = 여기서 D, E, L 을논리적서브인덱스라고한다. (Page 199 밑에줄 ) 결국 Index Skip Scan 은서브인덱스의종류가많지않고, 뒤에오는컬럼의종류가많을때가장좋은결과를얻을수있다. INDEX_SS, INDEX_SS_ASC, INDEX_SS_DESC 힌트를사용하여유도할수있다. Index Skip Scan 을하지않도록하고싶다면 NO_INDEX_SS 힌트를사용한다. SELECT /*+ INDEX_SS(miod_div miod_div_idx) */ i_bugt_cd FROM miod_div WHERE d_io = ' ' AND o_io = 6 AND i_io = '20' AND i_io_div = '6200' 첫번째컬럼의분포도가좋을때. Index Skip Scan을하면속도가느려짐 SELECT STATEMENT CHOOSE-Cost : 4519 TABLE ACCESS BY INDEX ROWID MATS.MIOD_DIV(1) INDEX SKIP SCAN MATS.MIOD_DIV_IDX(NU) (I_MATR,D_IO,O_IO,I_IO,I_IO_DIV) 100

101 SQL Accses Pattern PART 4 SQL 튜닝 Rowid > 발생 Rowid 가조건으로공급된경우 > Access 방식 Rowid 를이용해서특정 Block 의특정 Row 를찾아간다 가장빠른 Access 방식이다 101

102 SQL Join Pattern -Nested Loops Join PART 4 SQL 튜닝 > 발생 연결고리에이상이없을경우발생한다. 즉후행집합이연결고리를선행컬럼으로하는인덱스가있을경우발생한다. > Access 방식 Driving 집합의조건을만족하는한 Row 를가지고후행집합을 Access 해가는과정이반복적으로수행된다 부분범위처리에유리함 SELECT A.FLD1,,B.COL1 FROM TAB1 A, TAB2 B WHERE A.KEY1 = B.KEY2 AND A.FLD1 = AB AND B.FLD2 =

103 SQL Join Pattern -Nested Loops Join PART 4 SQL 튜닝 > 실행계획 select a.empno, a.ename, a.sal, a.deptno, b.dname from emp a, dept b where a.deptno = b.deptno and a.sal > 150 > plan SELECT STATEMENT GOAL: CHOOSE NESTED LOOPS TABLE ACCESS (FULL) OF 'EMP' TABLE ACCESS (BY [INDEX] ROWID) OF 'DEPT' INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE) 103

104 SQL Join Pattern - Merge Join PART 4 SQL 튜닝 > 발생 > Access 방식 연결고리에이상이있을경우발생한다. 즉후행집합이연결고리를선행컬럼으로하는인덱스가없을경우발생한다. 조인에참여하는양쪽집합을각각에주어진조건에 만족하는것만걸러내고그집합을 Merge 한다. 관련파레메터 : sort_area_size SELECT A.FLD1,,, B.COL1 FROM TAB1 A, TAB2 B WHERE A.KEY1 = B.KEY2 AND A.FLD1 = AB AND B.FLD2 =

105 SQL Join Pattern - Merge Join PART 4 SQL 튜닝 > 실행계획 SELECT /*+ use_merge(a b) */ a.empno, a.ename, a.sal, a.deptno, b.dname FROM emp a, dept b WHERE a.deptno = b.deptno AND a.sal > 20 > plan SELECT STATEMENT GOAL: CHOOSE MERGE JOIN SORT (JOIN) <-- 1 TABLE ACCESS (FULL) OF 'EMP' SORT (JOIN) <-- 2 TABLE ACCESS (FULL) OF 'DEPT 105

106 SQL Join Pattern -Hash Join PART 4 SQL 튜닝 > 발생 대량의데이터를조인하거나연결고리에이상이있을경우발생한다. > Access 방식 드라이빙테이블을먼저읽어서조건을만족하는것으로Hash Table을생성하고후행테이블을읽어가면서조인을수행한다. 관련파레메터 : hash_area_size, hash_multiblock_io_count(8i 이하 ) SELECT STATEMENT GOAL: CHOOSE HASH JOIN TABLE ACCESS (FULL) OF 'EMP' TABLE ACCESS (FULL) OF 'DEPT' 106

107 Hash join 의특징 1. Random Access 를하지않고 1 회의 scan 으로처리 (hash_area_size) 2. 메모리집중적인조인방식이다. (hash_area_size) 3. Sort 를하지않고 Hash Function 을이용한연산에의해조인을한다.(equal 비교 ) 4. 중간집합이작은집합이먼저 Hashing 되어야유리하다. (/*+ordered */) 107

108 Hash join 의원리 HASH AREA build input Y = F(X) SWAPPING Y = F(X) probe input 108

109 다른 join 과의비교 Nested Loop PART 4 SQL 튜닝 SELECT /*+ORDERED USE_NL(X Y) */ COUNT(Y.ACC_NM) FROM AC_ACC_MST X, (SELECT USR_CMPNY, CHNL_GB, ACC_UNT, ACC_CD, ACC_NM FROM AC_SLP_DTL WHERE USR_CMPNY = 'TOONI' AND CHNL_GB = 'CH_A' AND ACC_UNT = 'A' ) Y WHERE X.ACC_CD = Y.ACC_CD AND X.YEAR_NO = '07' 155초 Rows Execution Plan SELECT STATEMENT GOAL: CHOOSE 1 SORT (AGGREGATE) NESTED LOOPS (OUTER) 1057 INDEX (RANGE SCAN) OF 'SYS_C006306' (UNIQUE) TABLE ACCESS (BY INDEX ROWID) OF 'AC_SLP_DTL' INDEX (RANGE SCAN) OF 'DTL_IDX' (NON-UNIQUE) 109

110 다른 join 과의비교 Merge PART 4 SQL 튜닝 SELECT /*+ORDERED USE_MERGE(X Y) */ COUNT(Y.ACC_NM) FROM AC_ACC_MST X, (SELECT USR_CMPNY, CHNL_GB, ACC_UNT, ACC_CD, ACC_NM FROM AC_SLP_DTL WHERE USR_CMPNY = 'TOONI' AND CHNL_GB = 'CH_A' AND ACC_UNT = 'A' ) Y WHERE X.ACC_CD = Y.ACC_CD AND X.YEAR_NO = '07' 42초 Rows Execution Plan SELECT STATEMENT GOAL: CHOOSE 1 SORT (AGGREGATE) MERGE JOIN (OUTER) 1057 INDEX (RANGE SCAN) OF 'SYS_C006306' (UNIQUE) SORT (JOIN) TABLE ACCESS (BY INDEX ROWID) OF 'AC_SLP_DTL' INDEX (RANGE SCAN) OF 'DTL_IDX' (NON-UNIQUE) 110

111 다른 join 과의비교 Hash PART 4 SQL 튜닝 SELECT /*+ORDERED USE_HASH(X Y) */ COUNT(Y.ACC_NM) FROM AC_ACC_MST X, (SELECT USR_CMPNY, CHNL_GB, ACC_UNT, ACC_CD, ACC_NM FROM AC_SLP_DTL WHERE USR_CMPNY = 'TOONI' AND CHNL_GB = 'CH_A' AND ACC_UNT = 'A' ) Y WHERE X.ACC_CD = Y.ACC_CD AND X.YEAR_NO = '07' 5초 Rows Execution Plan SELECT STATEMENT GOAL: CHOOSE 1 SORT (AGGREGATE) HASH JOIN (OUTER) 1056 INDEX (RANGE SCAN) OF 'SYS_C006306' (UNIQUE) TABLE ACCESS (BY INDEX ROWID) OF 'AC_SLP_DTL' INDEX (RANGE SCAN) OF 'DTL_IDX' (NON-UNIQUE) 111

112 Hash join Tip PART 4 SQL 튜닝 Hash join 이불가능할경우강제하는방법 SELECT /*+ USE_HASH (T1 T2) */ * FROM TBL_A T1, TBL_B T2 WHERE NVL(SUBSTR(T1.COL1,0,0), ) = NVL(SUBSTR(T1.COL2,0,0), ) AND.. AND SELECT /*+USE_HASH(V1 V2) */ * FROM (SELECT 1 SW, ROWNUM,. FROM TBL_1 T1 WHERE..) V1, (SELECT 1 SW, ROWNUM,. FROM TBL_2 T2 WHERE..) V2 WHERE T1.SW = T2.SW SELECT /*+ NO_MERGE(V1) NO_MERGE(V2) USE_HASH(V1 V2) */ * FROM (SELECT 1 SW,. FROM TBL_1 T1 WHERE ) V1, (SELECT 1 SW,. FROM TBL_2 T2 WHERE ) V2 WHERE V1.SW = V2.SW 112

113 서브쿼리를이용한데이터연결 PART 4 SQL 튜닝 조인과서브쿼리 TAB1 와 TAB2 는동격 조 인 SELECT x.col1, x.col2, y.col1, y.col2,... FROM TAB1 x, TAB2 y WHERE x.key = y.key and other_conditions.. ; 관계형태에따라 전혀다른집합생성 TAB1 TAB2 조인 서브쿼리 M m 1 M 1 m m 서브쿼리 SELECT COL1, COL2, FROM TAB1 WHERE KEY1 IN ( SELECT KEY2 FROM TAB2 WHERE conditions ) ; M M m*m TAB1 ( 主 ), TAB2( 副 ) m 113

114 서브쿼리를이용한데이터연결 먼저수행하는서브쿼리 ( 제공자역할 ) PART 4 SQL 튜닝 서브쿼리수행결과를메인쿼리의처리주관인덱스에제공할수있어야함. SELECT COL1, COL2, FROM TAB1 x WHERE KEY1 IN ( SELECT KEY2 FROM TAB2 y WHERE y.col1 and y.col2 ) and COL1 IN ( SELECT COL2 FROM TAB3 z WHERE z.col1 z ); 서브쿼리내에메인쿼리컬럼이없어야한다. 서브쿼리가 1쪽 ( 부모 ) 일때는경우에따라나중에수행되는조인이되기도함 Sort_Merge,, Hash 조인으로수행될때는제공자역할을하지못함 만약이컬럼이체크기능역할을한다면서브쿼리는확인자가됨 114

115 서브쿼리를이용한데이터연결 제공자서브쿼리의실행계획 PART 4 SQL 튜닝 TAB1 1 : M TAB2 1:M 의연결을 1:1 로만들기위해서 M쪽집합을 UNIQUE 하게만들어제공 SELECT COL1, COL2, FROM TAB1 x WHERE KEY1 IN ( SELECT KEY2 FROM TAB2 y WHERE y.col1 and y.col2 ) ; NESTED LOOPS VIEW SORT(UNIQUE) TABLE ACCESS BY ROWID OF TAB2 INDEX RANGE SCAN OF COL1_IDX TABLE ACCESS BY ROWID OF TAB1 INDEX RANGE SCAN OF KEY1_INX 그러므로서브쿼리사용시 M:M 의연결은 M:1 로연결된결과가생성 115

116 서브쿼리를이용한데이터연결 PART 4 SQL 튜닝 제공자서브쿼리사용시주의사항 인덱스가 KEY1 + KEY2 로되어있다면 SELECT COL1, COL2, FROM TAB1 WHERE KEY1 LIKE ABC% ABC% and KEY2 IN ( SELECT COL2 and COL2 = 111 ; FROM TAB2 y WHERE y.col1 and y.col2 ) NESTED LOOPS VIEW SORT(UNIQUE) TABLE ACCESS BY ROWID OF TAB2 INDEX RANGE SCAN OF COL1_IDX TABLE ACCESS BY ROWID OF TAB1 INDEX RANGE SCAN OF INDEX1 KEY1 LIKE, KEY2 = 형태의범위처리가 서브쿼리가제공한만큼반복수행됨 즉, 인덱스선두컬럼이 = 이아니므로 KEY2 는단지체크기능만담당함 그러나 그러므로 실행계획형태상으로는문제가없음 KEY1 LIKE 범위가다량반복수행됨 116

117 서브쿼리를이용한데이터연결 나중에수행하는서브쿼리 ( 확인자역할 ) PART 4 SQL 튜닝 SELECT COL1, COL2, FROM TAB1 x WHERE KEY1 IN ( SELECT KEY2 FROM TAB2 y and EXISTS ( SELECT WHERE y.col1 >= x.fld1 and y.col2 ) FROM TAB3 z WHERE z.col = x.fld... ); EXISTS 를사용한경우는대부분의경우 서브쿼리내에메인쿼리컬럼이존재하고 결과를제공받을메인쿼리컬럼이존재하 지않으므로대개확인자역할을하게됨 서브쿼리내에메인쿼리컬럼이 있으면논리적으로제공자역할을할수없으므로확인자역할 SORT_MERGE 조인으로수행되는경우도있음 제공자역할을기대한서브쿼리가확인자역할을하면심각한오버헤드가발생 117

118 서브쿼리를이용한데이터연결 확인자서브쿼리의실행계획 PART 4 SQL 튜닝 사원 1 : M 가족 1:M 의연결을 1:1 로만들기위해서 M쪽집합을 EXISTS 개념으로수행 SELECT 사번, 성명, 직급, 입사일, FROM 사원 x WHERE 부서 = 경리과 and 사번 IN ( SELECT 사번 FROM 가족 y WHERE y. 사번 = x. 사번 and y. 생년월일 < ) ; FILTER TABLE ACCESS (BY ROWID) OF 사원 ' INDEX (RANGE SCAN) OF 부서 _INDEX' TABLE ACCESS (BY ROWID) OF 가족 ' INDEX (RANGE SCAN) OF PK_INDEX' 조건을만족하는첫번째로우를만나면종료 마찬가지로서브쿼리사용시 M:M 은 M:1 로연결된결과가생성 118

119 서브쿼리를이용한데이터연결 SORT_MERGE 형태의수행 서브쿼리도조인처럼 SORT_MERGE 형태로수행될수있다. PART 4 SQL 튜닝 SELECT 사번, 성명, 직급, 입사일, FROM 사원 WHERE 직책 = 과장 and 부서 IN ( SELECT 부서 M : M FROM 근태 1 : M WHERE 일자 LIKE % % and 근태유형 = 무단결근 )) ; M:M 조인이므로서브쿼리결과는 UNIQUE 을만든후조인수행 연결조건인 부서 컬럼에 양쪽모두인덱스가없다면 SORT_MERGE 처리될확률이높다. MERGE JOIN SORT (JOIN) TABLE ACCESS (FULL) OF ' 사원 ' SORT (JOIN) VIEW SORT (UNIQUE) TABLE ACCESS (BY ROWID) OF ' 근태 ' INDEX (RANGE SCAN) OF 'IDX1' 119

120 서브쿼리를이용한데이터연결 부정형 (ANTI) 조인 PART 4 SQL 튜닝 COL1 의처리범위가매우넓다면 서브쿼리가다량의랜덤액세스를발생 SELECT COUNT(*) FROM TAB1 WHERE COL1 like ABC% ABC% and COL2 NOT IN (SELECT FLD2 FROM TAB2 FILTER TABLE ACCESS BY ROWID OF 'TAB1' INDEX (RANGE SCAN) OF 'COL1_INDEX' TABLE ACCESS (BY ROWID) OF 'TAB2' INDEX (RANGE SCAN) OF 'FLD3_INDEX' WHERE FLD3 LIKE 1998% 1998% ) ; SORT_MERGE ANTI 조인으로유도 HASH ANTI 조인으로유도 120

121 서브쿼리를이용한데이터연결 MERGE ANTI 조인 PART 4 SQL 튜닝 SELECT COUNT(*) FROM TAB1 WHERE COL1 like ABC% ABC% and COL2 IS NOT NULL and COL2 NOT IN (SELECT /*+ MERGE_AJ */ FLD2 FROM TAB2 WHERE FLD3 LIKE 1998% 1998% and FLD2 IS NOT NULL 힌트사용 ) ; MERGE JOIN (ANTI) SORT (JOIN) TABLE ACCESS (BY ROWID) OF 'TAB1' INDEX (RANGE SCAN) OF 'COL1_IDX' SORT (UNIQUE) VIEW TABLE ACCESS (BY ROWID) OF 'TAB2' INDEX (RANGE SCAN) OF 'FLD3_IDX' NOT NULL 지정 NOT IN 사용시만가능 121

122 서브쿼리를이용한데이터연결 HASH ANTI 조인 PART 4 SQL 튜닝 SELECT COUNT(*) FROM TAB1 WHERE COL1 like ABC% ABC% and COL2 IS NOT NULL and COL2 NOT IN (SELECT /*+ HASH_AJ */ FLD2 FROM TAB2 WHERE FLD3 LIKE 1998% 1998% and FLD2 IS NOT NULL 힌트사용 ) ; HASH JOIN (ANTI) TABLE ACCESS (BY ROWID) OF 'TAB1' INDEX (RANGE SCAN) OF 'COL1_IDX' VIEW TABLE ACCESS (BY ROWID) OF 'TAB2' INDEX (RANGE SCAN) OF 'FLD3_IDX' NOT NULL 지정 NOT IN 사용시만가능 122

123 서브쿼리를이용한데이터연결 액세스경로와수행속도 UPDATE REQT x 500 만회 SET IN_AMT = IN_AMT + :AMT 반복수행 WHERE REQ_YM = AND CUSTNO IN ( SELECT CUSTNO FROM CUST y 2000 만건 WHERE PAY_CUST = :CUST Full Scan AND x.cust = y.cust ) REQT 2000 만 ROWS ( 월500 만건 ) TABLE PART 4 SQL 튜닝 CUST_NO INDEX 수행시간 : 16,000 초 + REQ_YM UPDATE REQT x 1 회 SET IN_AMT = IN_AMT + :AMT 먼저수행 WHERE REQ_YM = AND CUSTNO IN ( SELECT CUSTNO FROM CUST y 해당고객만 WHERE PAY_CUST = :CUST ) 처리 수행시간 : 0.1 초 123

124 서브쿼리를이용한데이터연결 SELECT x.col1, x.col2, min(x.col4), min(x.col5) FROM TAB1 x, TAB2 y WHERE x.col1 = y.fld1 and x.col2 = y.fld2 and x.col3 between 11 and 99 and y.fld3 like % GROUP BY COL1, COL2 HAVING sum(fld4) > 0 ; 서브쿼리를이용한부분범위처리 전체범위처리 부분범위처리 SELECT COL1, COL2, COL4, COL5 FROM TAB1 WHERE COL3 between 11 and 99 and EXISTS (SELECT FROM TAB2 WHERE FLD1 = COL1 and FLD2 = COL2 and FLD3 like % GROUP BY FLD1, FLD2 HAVING sum(fld4) > 0 ) ; 만약 TAB2 처리범위가보다좁다면 서브쿼리에서결과를제공하도록 SELECT * FROM TAB1 WHERE (COL1, COL2) IN (SELECT FLD1, FLD2 FROM TAB2 WHERE FLD3 like % GROUP BY FLD1, FLD2 HAVING sum(fld4) > 0 ) and COL3 between 11 and 99 ; 만약 TAB1 처리범위가보다좁다면 서브쿼리에서필터처리하도록 PART 4 SQL 튜닝 124

125 서브쿼리를이용한데이터연결 MIN, MAX 값을가진로우액세스 SELECT 종목, 고객번호, 변경회차, 변경일, 금액 FROM 변경내역 x WHERE 변경회차 = ( SELECT MAX(y. 변경회차 ) FROM 변경내역 y WHERE y. 고객번호 = x. 고객번호 and y. 변경일 LIKE % ) and 종목 = 15 and 변경일 LIKE % ; PART 4 SQL 튜닝 서브쿼리는확인자역할을담당 모든처리대상에대해중복액세스 종목 + 변경일, 고객번호 + 변경일 2개의인덱스필요 부분범위처리시유리할수도있음 SELECT 15 종목, 고객번호, substr(val,1,3) 변경회차, substr(val,4,8) 변경일, substr(val,12,15) 금액 FROM (SELECT 고객번호, FROM 변경내역 WHERE 종목 = 15 MAX(RPAD( 변경회차,3) 변경일 금액 ) VAL and 변경일 LIKE % % GROUP BY 고객번호 ) ; 결합한컬럼의 MAX 를취해분할 모든처리대상에대해한번액세스 종목 + 변경일인덱스만필요 전체범위처리 125

126 Optimizer 아키텍쳐 PART 4 SQL 튜닝 > 1. 파싱 (Parser) > 2. 옵티마이저 (Query Optimizer) > 3. 로우소스생성 (Row Source Generator) > 4.SQL 실행 (SQL Execution Engine) 옵티마이져아키텍쳐 < 그림 2> 1. 파싱단계에서 SQL 은구문 (syntax) 과의미 (semantics) 검사를수행한다. 예를들어, SQL 구문이정확한지를검사하고, 참조된테이블에대해사용자의접근권한등을검사한다. 이단계가끝나면, SQL 문은파싱트리 (parsed tree) 형태로변형되어옵티마이저에게넘겨진다. 2. 옵티마이저단계는앞에서넘겨받은파싱트리를이용해최적의실행계획을고른다. < 그림 2> 에서점선형태의사각형으로표시된부분이옵티마이저의주요구성요소를보여주고있는데, 뒤에서각구성요소의역할에대해자세히설명하겠다. 3. 로우소스생성단계는옵티마이저에서넘겨받은실행계획을내부적으로처리하는자세한방법을생성하는단계이다. 로우소스 란실행계획을실제로구현하는인터페이스각각을지칭하는말로, 테이블액세스방법, 조인방법, 그리고블럭등을위한다양한로우소스가제공된다. 따라서이단계에서는실행계획에해당하는트리구조의로우소스가생성된다. 4. SQL 실행단계는앞서생성된로우소스를 SQL 수행엔진에서수행해결과를사용자에게돌려주는과정이다. 126

127 Optimizer 아키텍쳐 PART 4 SQL 튜닝 > 옵티마이져는크게세가지모듈로진행된다. 1. 질의변환 (Query Rewriter) 2. 실행계획생성 (Plan Generator) 3. 비용산정 (Estimator) > 1. 질의변환 (Query Rewriter 또는 Transformer) 질의변환 (Query Rewriter 또는 Transformer) 단계는파싱트리를받아들여서질의변환을수행한다. 이변환과정을통해의미적으로같은결과를수행하지만, 더나은실행계획을찾을수있는 SQL 문으로변환함으로써질의의수행처리속도를높이는데그목적이있다. 오라클옵티마이저가수행하는질의변환은크게두종류로구분할수있다. 1. 휴리스틱 (Heuristic based) 질의변환 : 이변환의종류로는크게 View Merging, Subquery Unnesting, Push Predicate, Partition Pruning 등이있는데, 이들변환은가능한경우에항상질의변환을수행한다. 왜냐하면이와같은변환은경험적으로거의항상원래질의보다더빠른수행속도를보장하기때문이다. 2. 비용기반 (Cost based) 질의변환 : 이변환의예로는 MV Rewrite, Star Query Transformation, OR-expansion 등을들수있다. 그런데이방법을사용해변환된 SQL 문이원래 SQL 문보다속도가더빠르다는보장이없다. 따라서변환전후의두 SQL 문에대해각각최선의실행계획을구하고, 이들의비용을비교해더효율적인실행계획을최종적으로선택한다. 3. 질의변환단계가끝나면, 오라클옵티마이저는실행계획생성과비용산정모듈을수행하기앞서질의에서사용된모든테이블과각테이블에정의된인덱스에관한기본적인통계정보 ( 예를들어, 테이블의블럭개수, 로우평균길이, 인덱스의높이, 인덱스리프블럭의개수등 ) 과각테이블에대한다양한액세스경로 ( 예를들어풀테이블스캔, 인덱스스캔등 ) 에대한비용정보를미리구해둔다. 127

128 Optimizer 아키텍쳐 PART 4 SQL 튜닝 > 2. 실행계획생성 (Plan Generator) 이모듈은옵티마이저가새로운실행계획을만드는것이다. 오라클옵티마이저는제일먼저각테이블의레코드수를기준으로오름차순으로결정한다. 예를들어, SQL 질의의 from 절에서 T1, T2, T3 순서로참조한경우, 각테이블의카디널러티 (cardinality : 테이블의튜플수 ) 가 T1 > T2 > T3 순이라면제일처음고려하는조인순서는 (T3xT2)xT1 이된다. 이조인순서에대해다음단계인비용산정모듈을호출해이조인순서에따르는실행계획과각실행계획의비용을구한다. 그리고더이상의새로운조인순서가없을때까지계속새로운조인순서를만들어서비용을계산한다. 이모듈은지금까지찾아낸가장좋은실행계획과그비용을저장하고있다. 이단계는최종적으로구해진최적의실행계획을 < 그림 2> 의로우생성단계에넘겨준다. 한 sql 에테이블개수가많으면가능한조인순서의조합이기하급수적으로늘어나게된다. 이렇게되면옵티마이저시간이너무많이걸리기때문에, 옵티마이저는일정한수 ( 디폴트로는최대 80,000) 의인순서에대해서만비용을계산하고이중에서가장최선의실행계획을찾게된다. 즉, 모든가능한조인순서조합들중에서일부분만비용을계산하고, 나머지는고려하지않는것이다. 이를실행계획탐색에대한가지치기 (pruning) 또는컷오프 (cutoff) 라부른다. 그런데고려되지않은조인순서중에서실제로최선의실행계획을포함하고있을수있다. 옵티마이저가제일처음고려하는조인순서를테이블레코드수의오름차순순서로정하는이유는경험적으로이순서를근처로실제로최적의실행계획이존재하기때문이다. 이와같이초기조인순서를선택하는휴리스틱을사용함으로써임으로조인순서를시작했을때최적의좋은실행계획이컷오프되는것을막을수있다. 오라클옵티마이저실행계획생성모듈 ( 오라클 9i 부터도입된 ) 의또다른특징은, 조인순서를바꿔가면서지금까지구한최적의실행계획의예상비용이그리크지않은경우에최적화단계를일찍끝내버린다. 예를들어, 어떤질의에대해 10 초동안최적화를수행해서찾은최적실행계획의예상수행시간이 1 분이면남은조인순서가더있더라도옵티마이저단계를종료한다. 반면에, 지금까지구한최적예상수행시간이 2 시간이면더나은실행계획을찾기위해새로운조인순서에대해계속탐색할필요가있다. 이를적응성탐색전략 (adaptive search strategy) 이라부른다. 128

129 Optimizer 아키텍쳐 PART 4 SQL 튜닝 > 3. 비용산정 (Estimator) 실행계획생성모듈에서넘겨받은특정조인순서의각조인에대해중첩루프, 블럭병합, 해시조인방식과각테이블의다양한액세스방법을반복적용하면서각단계별로비용을계산한다. 그리고궁극적으로해당조인순서에서찾을수있는최선의실행계획과그예상비용을구해실행계획생성모듈에게넘겨준다. 현재의조인순서에대해중간단계까지의수행비용이실행계획생성모듈에서지금까지구한최선의예상비용보다더크다면해당조인순서에대해서는더이상비용산정을수행하지않고끝낸다. 예를들어, T1, T2, T3 에대해 (T1xT2)xT3 순서의비용이 1000 이었는데, (T1xT3)xT2 순서의 (T1xT3) 비용이 1200 이었다면더이상비용을계산할필요가없다. < 그림 2> 에나와있는것처럼옵티마이저는실행계획의비용을계산하기위한비용모델을갖고있고, 이비용모델은오라클데이터딕셔너리에서관리하는다양한통계정보를기반으로크게다음과같은세가지값 (measure) 의예상치를계산한다. 선택도 (selectivity) : where 절에있는다양한조건들의선택도계산 카디널러티 (cardinality) : 실행계획상의각연산결과카디널러티수계산 비용 (cost) : 실행계획상의각연산을수행하는데소요되는시간비용계산 129

130 Optimizer 아키텍쳐 PART 4 SQL 튜닝 > 3. 비용산정 (Estimator) - 계속 선택도 : d.loc = SEOUL 라는조건의선택도는 dept 테이블전체중에서 loc 의값이 SEOUL 인레코드의비율을일컫는다. 옵티마이저는선택도계산을통해해당조건을만족하는레코드가몇건정도가되는지를예측하게된다. 옵티마이저는만일 DBA_TABLES 에 dept 테이블의 loc 컬럼의 distinct column values 가 10 이라면옵티마이저는선택도가 0.1 이라고판단하게된다. 이때선택도를이와같이정하는이유는 dept 테이블의 loc 컬럼이골고루분포되어있다고가정할때성립한다. 히스토그램의필요성 : 실제로 loc 컬럼의값들이스큐 (skew) 돼분포할수도있다. 예를들어, 전체레코드의 50% 가 loc 값으로 SEOUL 을갖는다면잘못된선택도값을얻게된다. 이와같이데이터분포가스큐돼있는경우, 해당컬럼에대한히스토그램정보를 DBA_HISTOGRAM 테이블에만들어주어야정확한선택도값을계산할수있다 ( 이경우는 0.5). 오라클옵티마이저는다양한조건식의종류에대해선택도를통계정보에기반해계산하는수식을내부적으로갖고있다. 그렇지만어떤경우에는 dept 테이블이아직분석되지않아서통계정보가없다면옵티마이저는내부적으로갖고있는디폴트값을선택도로지정한다 ( 예를들어, 0.01). 히스토그램 (Histogram) Min Max 130

131 Optimizer 아키텍쳐 > 3. 비용산정 (Estimator) - 계속 카디널러티 : 앞의 dept 테이블의전체레코드건수가 1000 일때, 앞서설명한 loc = SEOUL 의선택도가 0.1 로계산되면조건을만족하는레코드건수는 1000 x 0.1, 즉 100 개로예상할수있다. 이와같이어떤연산을수행한결과로나오는레코드건수를카디널러티라하는데, 정확한카디널러티를계산하는것은좋은실행계획을만드는데있어서굉장히중요하다. 예를들어, (T1xT2)xT3 순서로테이블을조인할경우 (T1xT2) 의결과와 T3 를조인하면어떤조인방법을선택하는것이좋을지를결정하기위해서는 (T1xT2) 의크기를정확하게알아야한다. 이를위해서는 (T1xT2) 조인의결과레코드가몇개인지를예상할수있어야한다. 이를위해오라클옵티마이저는다양한연산결과레코드의카디널러티를통계정보와수식에의해계산한다. T1 과 T2 의조인조건이 T1.c1 =T2.c2( 이를 P 표기 ) 라했을때, 앞서설명한선택도계산공식에의해이조건식의선택도 Sel(P) 를먼저계산하면조인의결과카디널러티는 Card(T1) x Card(T2) x Sel(P) 가된다. 예를들어, T1, T2 의튜플수가각각 1000, 5000 이고 Sel(P) 가 0.01 이면, 조인결과로생기는튜플수는 1000 x 5000 x 0.01 = 5000 이된다. 그런데 Sel(P) 가조금이라도틀리면이후의전체적인비용산정이잘못되게된다. 오라클옵티마이저는다양한종류의연산에대해카디널러티를계산하는내부공식을사용한다. 131

132 Optimizer 아키텍쳐 PART 4 SQL 튜닝 > 3. 비용산정 (Estimator) - 계속 비용 : 비용은테이블액세스, 조인등을수행하는데걸리는시간을의미하는데, 시간은주로디스크 I/O 수와 CPU 사용시간을고려한다. 비용은앞서계산한통계정보와내부계산식에의해계산된다. 예를들어, T1xT2 를중첩루프방식으로조인할경우조인비용은 (T1 의데이터블럭수 )+ ((T1 의레코드건수 ) T2 의액세스비용 )) 이된다. 이처럼오라클옵티마이저는모든연산에대해소요되는비용을계산하는수식을갖고있다. 이비용산정을위한통계정보를저장하는데이터딕셔너리테이블은 DBA_TABLES, DBA_INDEXES, DBA_TAB_COL_STATISTICS, DBA_HISTOGRAMS 등이다. 이통계정보는앞서언급한테이블액세스경로의비용정보를결정하는데도사용된다. 이들테이블정보는사용자가 ANALYZE 명령어나 DBMS_STATS 패키지를이용해관리하게된다. 만일에테이블과인덱스에대한통계정보가존재하지않는경우옵티마이저는해당테이블과인덱스에대해디폴트로가정하는값들이있다. ( 참고자료 Surajit Chaudhuri, An Overview of Query Optimization in Relational Systems, AMM PODS Tutorial, 1998) 132

133 서브쿼리의이용 (UNNESTING QUERY) PART 4 SQL 튜닝 > UNNESTING QUERY 서브쿼리를메인쿼리와합쳐조인형태로변형하도록하는것 EXISTS를사용하는경우대부분 FILTER 처리방식으로실행계획이수립되는데메인쿼리에비교할컬럼 ( 연결고리 ) 이정렬되어있지않고, 서브쿼리에존재할확률이낮을때랜덤액세스가증가할가능성이높아불리할경우사용한다 (Sort Merge형이나해쉬조인으로유도 ) UNNEST 힌트를이용해 UNNESTING을유도한다 SELECT /*+ RULE */ * FROM EMP E WHERE COMM = 500 AND EXISTS ( SELECT D1.DEPTNO FROM DEPT D1 WHERE D1.DEPTNO = E.DEPTNO) SELECT /*+ UNNEST (@qb) */* FROM EMP E WHERE COMM = 500 AND EXISTS ( SELECT /*+ QB_NAME (qb) */ D1.DEPTNO FROM DEPT D1 WHERE D1.DEPTNO = E.DEPTNO) SELECT STATEMENT Optimizer Mode=HINT: RULE FILTER TABLE ACCESS BY INDEX ROWID SCOTT.EMP INDEX RANGE SCAN SCOTT.EMP_IDX1 INDEX UNIQUE SCAN SCOTT.PK_DEPT SELECT STATEMENT Optimizer Mode=ALL_ROWS NESTED LOOPS SEMI TABLE ACCESS BY INDEX ROWID SCOTT.EMP INDEX RANGE SCAN SCOTT.EMP_IDX1 INDEX UNIQUE SCAN SCOTT.PK_DEPT 133

134 인라인뷰 (VIEW MERGING) PART 4 SQL 튜닝 > VIEW MERGING 뷰나인라인뷰의액세스를최적화하기위해서뷰쿼리에사용된원래의테이블을최적으로액세스하도록변환 VIEW 생성 (avg_salary_view) CREATE VIEW avg_salary_view AS SELECT deptno, AVG(sal) ASavg_sal_dept FROM emp GROUP BY deptno; SELECT dept.loc, avg_sal_dept FROM dept, avg_salary_view WHERE dept.deptno = avg_salary_view.deptno AND dept.loc = 'London'; 134

135 인라인뷰 (VIEW MERGING) PART 4 SQL 튜닝 SELECT /*+ MERGE(avg_salary_view) */ dept.loc, avg_sal_dept FROM dept, avg_salary_view WHERE dept.deptno = avg_salary_view.deptno AND dept.loc = 'London'; VIEW MERGING SELECT dept.loc, AVG(sal) FROM dept, emp WHERE dept.deptno = emp.deptno AND dept.loc = 'London' GROUP BY dept.rowid, dept.loc; 135

136 PUSH PREDICATE( 조건절진입 ) PART 4 SQL 튜닝 > 발생하는경우 > 예제 VIEW MERGING 이안되는경우에조인이 NESTED LOOP 로풀릴때 VIEW 안에조건절이진입한다. VIEW 구성이 UNION ALL 로되어있는경우. VIEW 에아우터조인이걸릴경우. create or replace view v1 as select t2.id1, t2.id2, t3.small_vc, t3.padding from t2, t3 where t3.id1 = t2.id1 and t3.id2 = t2.id2; > 예제 select t1.*, v1.* from t1, v1 where t1.n1 = 5 and t1.id1 between 10 and 50 and v1.id1(+) = t1.id1; 136

137 PUSH PREDICATE( 조건절진입 ) PART 4 SQL 튜닝 The 10g execution plan Predicate Information (identified by operation id): filter("t1"."n1"=5) 3 - access("t1"."id1">=10 AND "T1"."ID1"<=50) 7 - access("t3"."id1"="t1"."id1") 8 - access("t2"."id1"="t1"."id1" AND "T3"."ID2"="T2"."ID2") Notice particularly that the 10g plan no longer has a FILTER operation, and all those redundant filter_predicates have disappeared. 137

138 OR-EXPANSION PART 4 SQL 튜닝 > 여러개의 Union all 로 Query 를재작성한다. Concatenation USE_CONCAT hint 로유도가능하다. PLAN 에 CONCATNATION > In list 를그대로두고 Filter 로사용한다. Iterate : 數 컴퓨터 < 공식 조작을 > 반복적용하다 In-list 에있는 Distinct 한값만큼 Inlist Iterator 아래에있는 Operation 을반복수행한다 Oracle7 > Index 를사용하지못하고 Full Table Scan 으로수행된다. Oracle8.0+ > Inlist Iterator 로수행된다. PLAN 에 Inlist Iterator NO_EXPAND hint 로유도가능하다. 어떤 Column 에대해서관계연산자 In 을이용해값 (Value) 이공급되거나, 동일컬럼에대한값이 OR 연산자로연결되고, 값을받는 Column 을선두로하는사용가능한 Index 가존재하는경우발생한다. CBO 에서만나타난다. 값이집합으로공급되는경우는발생하지않는다. SELECT empno, ename, deptno SELECT empno, ename, deptno FROM t_emp FROM t_emp WHERE empno = 7521 WHERE empno IN (7521,7902,7788) OR empno = 7902 OR empno =

139 OR-EXPANSION PART 4 SQL 튜닝 > Inlist Iterator 와 Concatenation 비교 Inlist Iterator > Sub-query > Concatenation Unique Index 를사용할경우 Range Scan 이발생하는 Inlist Iterator 가 Concatenation 보다비효율적일것같지만실제수행결과를보면 Inlist Iterator 가가장효율적이다. In-list Subquery Concatenation 값의건수성공건수 CPU ELAPSED CPU ELAPSED CPU ELAPSED

140 OR-EXPANSION PART 4 SQL 튜닝 > Inlist Iterator 발생유도하기 Index hint > 직접적으로발생시키는 Hint 는존재하지않으며, 발생할가능성을높이기위해서 Index Hint 를사용할수있다. NO_EXPAND hint > Concatenation 으로풀리지않도록해준다. SELECT /*+ no_expand */ empno, ename, deptno FROM t_emp WHERE empno IN (7521,7902,7788) 실행계획 SELECT STATEMENT Hint=CHOOSE INLIST ITERATOR TABLE ACCESS BY INDEX ROWID T_EMP INDEX (RANGE SCAN) PK_T_EMP 140

141 PART 4 SQL 튜닝 STAR QUERY TRANSFORMATION > 개념 > 제약사항 소량의데이터를가진여러개의디맨젼테이블과팩트테이블의개별비트맵인덱스를이용하여처리범위를줄이는조인방식 카티젼곱을만들지않는것이 STAR 조인과다름. B-TREE 인덱스와는다르게독립적인 BIT MAP INDEX 로 LIKE, BETWEEN 등범위검색에서도 BIT MAP 인덱스머지가일어남. 내부적으로옵티마이져가질의를변형하여실행계획을생성함. FROM 절에여러 FACT 테이블이조인된것을 WHERE 절에서브쿼리조인으로바꿈. 하나의팩트테이블에최소한 2개이상의디맨션테이블이있어야한다. 팩트테이블의외부키에는반드시비트맵인덱스가존재해야한다. 팩트테이블에반드시통계정보가생성되어있어야한다. 파라메터 (STAR_TRANSFORMATION_ENABLED) 가 TRUE 이거나쿼리에힌트 (STAR_TRANSFORMATION) 를주어야한다. 바인드변수를사용하면안된다.( 반드시상수인경우에발생됨 ) 141

142 STAR QUERY TRANSFORMATION PART 4 SQL 튜닝 > STAR_TRANSFORMATION 예제 Select A.QUTER, B,STATE, SUM(C.AMOUNT) FROM CARENDAR A, CUSTOMER B, SALES C WHERE A.SALE_DATE = C.SALE_DATE AND B.CUST_ID = C.CUST_ID AND B.STATE = CA AND A.QUTER = STAR TRANSFORMATION SELECT FROM CARENDAR A, CUSTOMER B, SALES C WHERE SALES_DATE IN (SELECT SALES_DATE FROM CARENDAR WHERE QUTER = ) AND CUST_ID IN (SELECT CUST_ID FROM CUSTOMER WHERE STATE = CA ) 142

143 PART 4 SQL 튜닝 STAR QUERY TRANSFORMATION 143

144 PART 4 SQL 튜닝 STAR QUERY TRANSFORMATION 144

145 PART 4 SQL 튜닝 STAR QUERY TRANSFORMATION 145

146 PART 4 SQL 튜닝 STAR QUERY TRANSFORMATION 146

147 부분범위처리 (Partial range scan) PART 4 SQL 튜닝 액세스경로를이용한 SORT의대체 인덱스만액세스하는부분범위처리 MIN, MAX의처리 FILTER형부분범위처리 ROWNUM의활용 인라인뷰를이용한부분범위처리 저장형함수를이용한부분범위처리 쿼리의분리를이용한부분범위처리 147

148 액세스경로를이용한 SORT 의대체 PART 4 SQL 튜닝 전체범위스캔 운반단위 DESENDING SORT 부분범위스캔 운반단위 INDEX (ITEM_CD) TABLE (PRODUCT) CATEGORY LIKE C% INDEX (ITEM_CD) TABLE (PRODUCT) CATEGORY LIKE C% SELECT * FROM PRODUCT WHERE ITEM_CD LIKE AB% AND CATEGORY LIKE C% ORDER BY ITEM_CD DESC SELECT /*+INDEX_DESC(PRODUCT item_index)*/ * FROM PRODUCT WHERE ITEM_CD LIKE AB% AND CATEGORY LIKE C% - ORDER BY 는전체범위처리가된다 - 정렬하고자하는순서와엑세스를주관하는인덱스의컬럼앞부분이같다면인덱스를사용하여부분범위처리유도 - 힌트를사용하면역순으로정렬하는것도가능 - CBO인경우힌트를사용하여 ORDER BY 대신인덱스를이용한부분범위로처리 (RBO일때는인덱스를역순으로엑세스하는부분범위처리가능 ) - 결합인덱스를생성하여부분범위처리유도가능 - ORDER BY를없애기위한목적으로인덱스에필요한컬럼을추가하는것도부분범위처리의좋은활용법 148

149 부분범위처리 (Index 만처리 ) SELECT DEPT, SUM(QTY) FROM PRODUCT WHERE DEPT LIKE '12%' GROUP BY DEPT; SELECT DEPT, SUM(QTY). FROM PRODUCT WHERE DEPT LIKE '12%' GROUP BY DEPT; G R O U P B Y 운반단위 G R O U P B Y 운반단위 INDEX (DEPT) TABLE INDEX (DEPT+QTY) 149

150 MIN, MAX 의처리... PART 4 SQL 튜닝... S O R T 운반단위 MAX(SEQ)+1 운반단위 SEQ + 1 INDEX (ORDER) TABLE INDEX (ORDER+SEQ) SELECT MAX(SEQ) + 1 FROM ORDER WHERE DEPTNO = ; SELECT /*+ INDEX_DESC(order pk_order) */ NVL(MAX(SEQ),0) + 1 FROM ORDER WHERE DEPT_NO = AND ROWNUM = 1; - MIN 을처리한다면 INDEX 를순차적 (Ascending) 으로스캔하여첫번쨰로우만추출, MAX 는역순 (Desending) 으로액세스하여한건추출 - 위의방법은완벽한부분범위처리를하므로주어진조건을만족하는범위가넓어도빠른속도를보장 - NVL 함수를사용한이유는조건을만족하는데이터가없을때 ROWNUM=1 을얻기위해사용 ( 최초번호부여시문제점발생예방조치 ) 150

151 FILTER 형부분범위처리 PART 4 SQL 튜닝 x o o o o SORT (aggregate) 운반단위.... x 운반단위 INDEX (DEPT) TABLE (ITEM_TAB) INDEX (DEPT) TABLE (ITEM_TAB) SELECT COUNT(*) INTO:CNT FROM ITEM_TAB WHERE DEPT = 101 AND SEQ>100 IF CNT>0 SELECT 1 INTO:CNT FROM DAUL WHERE EXISTS (SELECT X FROM ITEM_TAB WHERE DEPT = 101 AND SEQ > 100) IF CNT>0 - 존재여부만판단하는경우에는조건을만족하는첫번째로우를만나는순간실행을종료하여도충분 - EXISTS 는수행결과의존재여부를체크하여성공과실패만을확인하는불린 (Boolean) 함수이므로존재여부판단에적절 151

152 ROWNUM 의활용 PART 4 SQL 튜닝 2 x 1 SELECT * FROM ITEM_TAB WHERE DEPT = 101 AND SEQ > 100 AND ROWNUM <= 운반단위 - ROWNUM 은일종의가상 (Pseudo) 의컬럼 - 전체를처리하지않고일부만처리하도록유도하는방법 ( 일종의부분범위처리 ) SEQ > 100 CHECK INDEX (DEPT) TABLE (ITEM_TAB) - 인덱스를통해액세스한테이블의로우들중에서체크조건을만족하는로우들만 ROWNUM이부여되어운반단위로이동 - 운반단위가채워지거나 ROWNUM이만족될때 (10보다클때) 수행을멈춤 - ROWNUM은로우의번호가아니라조건을만족한데이터에일련번호가부여된것 - 조건이만족되지못하면 ROWNUM이부여되지않기때문에 ROWNUM은조건으로사용할수있으면서결과값이되기도함 Execution Plan SELECT STATEMENT COUNT (STOPKEY) TABLE ACCESS (FULL) OF PRODUCT 테이블을부분범위처리로액세스하여 ROWNUM을 COUNT하다가주어진 ROWNUM에도달하면멈춤 (Stopkey) 를하겠다는것을확인 확실한일정범위스캔확인 152

153 ROWNUM 의활용 PART 4 SQL 튜닝 1 3 SELECT ROWNUM, item_cd, FROM product 2 5 WHERE deptno like 120% INDEX (DEPT) TABLE (PRODUCT) x... QTY>0 CHECK 운반단위 AND qty >0 AND ROWNUM <= 10 ORDER BY item_cd; -ORDER BY 절이수행되기전에 WHERE 절에있는조건을만족하는로우마다 ROWNUM 이부여되어임시공간에저장, 10 건만정렬되어운반단위로보내진다. 답이틀림. -SQL은조건에맞는데이터를액세스하여내부적으로저장한다음이를정렬한다. ( 내부적인저장이일어날때 ROWNUM이생성되어저장됨 ) SQL을부분범위처리가되도록바꾸어주는방법으로는 ORDER BY 를없애고인덱스를이용해부분범위처리유도후 ROWNUM <= 10 추가또는아래와같은형식의 ORDER BY 를인라인뷰에넣은다음 ROWNUM을체크하여도부분범위처리를할수있다. 주의 SELECT ROWNUM, item_cd, category_cd, FROM ( SELECT * FROM product WHERE deptno like 120% AND qty>0 ORDER BY item_cd) WHERE ROWNUM <= 10; 153

154 서브쿼리를이용한부분범위처리 SELECT x.cust_no, x.addr, x.name,... FROM CUST x, REQT y WHERE x.cust_no = y.cust_no AND x.cust_stat in ('A', 'C', 'F') AND y.un_pay > 0 GROUP BY x.cust_no HAVING SUM(y.UN_PAY) between :VAL1 and :VAL2 1:M 1:M JOIN JOIN의부분범위유도유도 전체범위 SUB_QUERY 의수행결과를 MAIN_QUERY 에서 사용할수없음 부분범위 SELECT x.cust_no, x.addr, x.name,... FROM CUST x WHERE CUST_STAT in ('A', 'C', 'F') AND EXISTS ( SELECT 'X' FROM REQT y WHERE y.cust_no = x.cust_no AND UN_PAY > 0 GROUP BY x.cust_no HAVING SUM(y.UN_PAY) between :VAL1 and a :VAL2 ) 154

155 인라인뷰를이용한부분범위처리 PART 4 SQL 튜닝 - 원리 : 전체범위처리가되는부분을인라인뷰로묶어서다른부분들은부분범위처리를하도록유도 * 전체쿼리를전체범위처리로만들어버리는요인을인라인뷰로격리함으로써부분처리 SELECT a.dept_name, b.empno, b.emp_name, c.sal_ym, c.sal_tot FROM department a, employee b, salary c WHERE b.deptno = a.deptno AND c.empno = b.empno AND a.location = SEOUL AND b.job = MANAGER AND c.sal_ym = ORDER BY a.dept_name, b.hire_date, c.sal_ym; SELECT /*+ ORDER, USE_NL(x y) */ a.dept_name, b.empno, b.emp_name, c.sal_ym, c.sal_tot FROM (SELECT a.dept_name,b.hire_date, b.empno, b.emp_name FROM DEPT a, EMPLOYEE b WHERE b.deptno = a.deptno AND a.location = SEOUL AND b.job = MANAGER ORDER BY a.dept_name, b.hire_date) x, SALARY y WHERE y.empno = x.empno AND c.sal_ym = ; SELEC a.product_cd, product_name, avg_stock FROM PRODUCT a, (SELECT product_cd, SUM(stock_qty) / (:b2-:b1) avg_stock FROM PROD_STOCK WHERE stock_date between :b1 and :b2 GROUP BY product_cd ) b WHERE b.product_cd = a.product_cd AND a.category_cd = 20 ; - 적은집합인 DEPT 와 EMPLOYEE 테이블만먼저조인하고그결과만먼저정렬시킨다음대량의집합인 SALARY 테이블은기본키인 empno + sal_ym 인덱스를경유 이상적방법 - 힌트를사용한것은인라인뷰를먼저수행하고그결과와 SALARY 테이블이 Nested Loops조인이되도록하기위함 -처리대상이적은테이블을먼저처리하여전체범위처리가되도록함으로, 대량의데이터를가지집합은부분범위처리유도 - SQL은 CATEGOR_CD가 20인 PRODUCT_CD들에대해주어진기간내의평균재고수량을구하고있는SQL 이다. 155

156 저장형함수를이용한부분범위처리 SELECT x.cust_no, x.addr, x.name,... FROM CUST x, REQT y WHERE x.cust_no = y.cust_no AND x.cust_stat in ('A', 'C', 'F') AND y.un_pay > 0 GROUP BY x.cust_no HAVING SUM(y.UN_PAY) between :VAL1 and :VAL2 ; Create or replace Function unpay_sum (v_custno in varchar2) return number is sum_unpay number ; begin... select sum(un_pay) into sum_unpay from reqt where cust_no = v_custno and un_pay > 0 ;... return sum_unpay ; end unpay_sum ; 156 1:M JOIN 의부분범위유도 전체범위 부분범위 Select cust_no, addr,, un_pay,... from ( select cust_no, addr, unpay_sum(cust_no) as un_pay,... from cust where cust_stat in in ('A', 'C', 'F') ) where un_pay between :VAL1 and :VAL2

157 쿼리의분리를이용한부분범위처리 PART 4 SQL 튜닝 - Master 와 Detail 관계를가진경우에는 SQL 을분리함으로수행효과를높인다. 연간개인별급여현황 부서코드기준일 2005/12 11% 부서코드성명사번직책본봉수당입사일 1110 홍길동 부장 42,000,000 5,000,000 82/10/ 박문수 과장 34,000,000 2,400,000 85/01/ 김태수 대리 27,000,000 91/08/ 홍민철 사원 24,000,000 94/09/ 이수형 사원 22,000,000 95/01/ 김명훈 부장 43,100,000 5,000,000 1/03/ 정창수 과장 35,240,000 2,000,000 84/10/20 SELECT 부서코드, 사번, min( 직책 ), sum( 본봉 ), sum( 수당 )... FROM 급여테이블 x, 사원테이블 y WHERE x. 사번 = y. 사번 AND x. 부서코드 LIKE 11% 11% AND x. 급여일 between and GROUP BY 부서코드, x. 사번 SELECT 부서코드 into :DEPTNO FROM 부서테이블 WHERE 부서코드 like 11% 11% ; 여기서 :DEPTNO 는배열변수로지정된호스트변수이며부서코드는인덱스를가짐 SELECT :DEPTNO, 사번, min( 직책 ), sum( 본봉 ), sum( 수당 )... FROM 급여테이블 x, 사원테이블 y WHERE x. 사번 = y. 사번 AND x. 부서코드 = :DEPTNO AND x. 급여일 between and GROUP BY x. 사번 157

158 IN 을활용한액세스효율화 인덱스컬럼순서와연산자 COL1 COL2 ROWID SELECT * FROM TAB1 WHERE COL1 = C and COL2 between 111 and 113 COL2 COL1 ROWID PART 4 SQL 튜닝 B C D A 21 C C C B C D 65 C C A B 70 C C 5 C D 76 C A 48 C B 44 C C C D 49 C B 77 INDEX 158 INDEX

159 IN 을활용한액세스효율화 PART 4 SQL 튜닝 IN 연산자의특성 수학적의미 A * (B + C) = (A * B) + (A * C) 기하학적의미 A and (B or C) = (A and B) or (A and C) A = and B in ( 1, 3 ) = (A = and B = 1 )) or (A = and B = 3 ) IN 은 n개의 = 이다! A 선분 G COL BETWEEN A and G A B C D E F G COL IN ( A, B, C, D, E, F, G )( 159

160 IN 을활용한액세스효율화 - IN 의결합처리실행계획 PART 4 SQL 튜닝 SELECT * FROM TAB1 WHERE COL1 = B and COL2 between 111 and 112 COL2 COL1 ROWID 110 A B A B C D A B C D 22 INDEX1 TABLE ACCESS BY ROWID TAB1 INDEX RANGE SCAN INDEX1 160 SELECT * FROM TAB1 WHERE COL1 = B and COL2 in ( 112( 112, 111 ) COL2 COL1 ROWID 110 A B A B C D A B C D 22 INDEX1 CONCATENATION TABLE ACCESS BY ROWID TAB1 INDEX RANGE SCAN INDEX1 TABLE ACCESS BY ROWID TAB1 INDEX RANGE SCAN INDEX1

161 IN 을활용한액세스효율화 - IN 의효율화사례 TAB1 의인덱스 상품 부서코드매출일자 PRINTER PRINTER PRINTER PRINTER PRINTER PRINTER PRINTER PRINTER PRINTER PRINTER PRINTER PRINTER PRINTER PRINTER PRINTER PRINTER PRINTER MOUSE MOUSE MOUSE 테이블액세스 엑세스효율개선? 2일간의자료만인덱스스캔 161 SELECT... FROM TAB1 WHERE 상품 = PRINTER AND 매출일자 between and SELECT... FROM TAB1 WHERE 상품 = PRINTER AND 부서코드 like % AND 매출일자 between and 해당상품의모든범위를인덱스스캔 SELECT... FROM TAB1 WHERE 상품 = PRINTER AND 부서코드 IN ( SELECT 부서코드 FROM 부서 WHERE 부서구분 = S ) AND 매출일자 between and PART 4 SQL 튜닝

162 IN 을활용한액세스효율화 - IN 의효율화사례 PART 4 SQL 튜닝 모조 (Dummy) 테이블을이용하는방법 YMD_DUAL YMD YMD_DATE JAN JAN JAN JAN JAN JAN DEC DEC-2049 YM_DUAL YM6 YM COPY_T NO NO 각테이블의컬럼마다 UNIQUE 인덱스를생성해둘것 YMD_DUAL 은일자기간을점으로만들어주기위해사용 YM_DUAL 은월별기간을점으로만들어주기위해사용 COPY_T 는데이터복제나임의의값을생성해주기위해사용 162

163 IN 을활용한액세스효율화 - IN 의효율화사례 PART 4 SQL 튜닝 SELECT * FROM TAB1 WHERE 상품 = PRINTER and 판매일자 between and and 부서 LIKE :VAL1 % ; SELECT * FROM TAB1 WHERE 상품 = PRINTER and 판매일자 IN ( SELECT YMD and 부서 like :VAL1 % ; 상품 + 판매일자 + 부서인덱스사용한다고가정 FROM YMD_DUAL WHERE YMD between and ) SELECT * FROM TAB2 WHERE 상품 = PRINTER and 구분 like :TYPE % and 생산일자 = ; 구분은 A01,..,A10, B01,..B15... 상품 + 구분 + 생산일자인덱스사용한다고가정 SELECT * FROM TAB2 WHERE 상품 = PRINTER and 구분 IN ( SELECT :TYPE NO FROM COPY_T WHERE NO <= decode(:type, A,10,15),10,15) ) and 생산일자 = ; 163