고급IT실무교육 (구로디지털단지역2분,자바9학원/스프링학원/SQL학원/오라클학원/ETL학원/임베디드학원/C#학원/닷넷학원/JSP학원/안로이드학원/JPA학원/제안서학원/펌웨어학원)

2016년 12월 1일 목요일

[오라클교육,SQL교육,튜닝교육학원추천_탑크리에듀][mview, group by 튜닝]다음은 부서별 최대급여를 구하는 쿼리문이다. SQL문을 튜닝 하시오.

[mview, group by 튜닝]다음은 부서별 최대급여를 구하는 쿼리문이다. SQL문을 튜닝 하시오.

myemp1 : 2000만건
mydept1 : 5건(0,1,2,3,4)

-- 약1분 소요
SELECT A.ENAME,
A.SAL,
A.DNAME
FROM (SELECT A.ENAME,
B.DNAME,
A.SAL,
RANK() OVER (PARTITION BY A.DEPTNO
ORDER BY A.SAL DESC) AS RANKING
FROM MYEMP1 A,
MYDEPT1 B
WHERE A.DEPTNO = B.DEPTNO ) A
WHERE A.RANKING = 1


| 0 | SELECT STATEMENT | | 20M| 2479M| | 218K (1)
|* 1 | VIEW | | 20M| 2479M| | 218K (1)
|* 2 | WINDOW SORT PUSHED RANK| | 20M| 648M| 844M| 218K (1)
|* 3 | HASH JOIN | | 20M| 648M| | 36575 (2)
| 4 | TABLE ACCESS FULL | MYDEPT1 | 5 | 50 | | 3 (0)
| 5 | TABLE ACCESS FULL | MYEMP1 | 20M| 457M| | 36473 (1)


--------------------------------------------------------------------------------
-- 아래처럼 상관서브쿼리로... 확인해보자.
다음에 나오는 PAREWIAE 방식보다 느리다.

SELECT ENAME, SAL
FROM MYEMP1 A
WHERE SAL = (SELECT MAX(SAL) FROM MYEMP1 B
WHERE B.DEPTNO = A.DEPTNO);

---------------------------------------------------------------------------------
create index idx_myemp1_sal on myemp1(deptno, sal)

SQL> conn scott/tiger
연결되었습니다.
SQL> set timing on
SQL> set autotrace on explain
SQL> select deptno, empno, sal
from myemp1
where (deptno, sal) in (select deptno, max(sal)
from myemp1
group by deptno);

DEPTNO EMPNO SAL
---------- ---------- ----------
0 3999995 3999995
1 3999996 3999996
.......................

25 개의 행이 선택되었습니다.

경 과: 00:00:14.20

Execution Plan
----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%C
PU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 205 | 37588
| 1 | NESTED LOOPS | | | |
| 2 | NESTED LOOPS | | 5 | 205 | 37588
| 3 | VIEW | VW_NSO_1 | 5 | 130 | 37555
| 4 | HASH GROUP BY | | 5 | 45 | 37555
| 5 | TABLE ACCESS FULL | MYEMP1 | 20M| 171M| 36473
(|* 6 | INDEX RANGE SCAN | IDX_MYEMP1_SAL | 5 | | 2
| 7 | TABLE ACCESS BY INDEX ROWID| MYEMP1 | 1 | 15 | 7
--------------------------------------------------------------------------------

인덱스를 이용하고 서브쿼리로 바꾸니 14초 정도 나온다.

이번에는 mview를 만들자

SQL> CREATE MATERIALIZED VIEW m2
2 BUILD IMMEDIATE
3 REFRESH
4 COMPLETE
5 ON DEMAND
6 ENABLE QUERY REWRITE
7 AS
8 select
9 deptno, max(sal) maxsal from myemp1
10 group by deptno;

구체화된 뷰가 생성되었습니다.

작성 후 group by가 있는 쿼리문을 다시 실행하자.
바로 나온다.
(
인덱스를 생성해야 한다. 이미 했으면 SKIP
create index idx_myemp1_sal on myemp1(deptno, sal)
)

SQL> select deptno, empno, sal
2 from myemp1
3 where (deptno, sal) in (select deptno, max(sal)
4 from myemp1
5 group by deptno);

DEPTNO EMPNO SAL
---------- ---------- ----------
0 3999995 3999995
0 7999995 3999995
.......................

25 개의 행이 선택되었습니다.

경 과: 00:00:01.26

Execution Plan
----------------------------------------------------------
Plan hash value: 1180890275

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 205 | 2
| 1 | NESTED LOOPS | | | |
| 2 | NESTED LOOPS | | 5 | 205 | 2
| 3 | SORT UNIQUE | | 5 | 130 |
| 4 | MAT_VIEW REWRITE ACCESS FULL| M2 | 5 | 130 |
|* 5 | INDEX RANGE SCAN | IDX_MYEMP1_SAL | 5 | |
| 6 | TABLE ACCESS BY INDEX ROWID | MYEMP1 | 1 | 15 |

[오라클교육,SQL교육,튜닝교육학원추천_탑크리에듀]group by, where vs having성능비교, 복합인덱스, 구체화뷰를 이용한 SQL튜닝(materialized view, composite index, group by SQL 튜닝)

group by, where vs having성능비교, 복합인덱스, 구체화뷰를 이용한 SQL튜닝(materialized view, composite index, group by SQL 튜닝)

myemp1은 2000만건 deptno는 0,1,2,3,4의 5가지 종류의 값을 가지고 있다.

1. B*Tree 인덱스 이용

-- B*Tree 인덱스
create index idx_myemp1_deptno on myemp1(deptno)

-- 전체 group by를 먼저하고 having으로 조건비교
--13.8초
select deptno, avg(sal) from myemp1
group by deptno
having deptno = 1

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 37555 (4)| 00:07:31 |
|* 1 | FILTER | | | | | |
| 2 | HASH GROUP BY | | 1 | 9 | 37555 (4)| 00:07:31 |
| 3 | TABLE ACCESS FULL| MYEMP1 | 20M| 171M| 36473 (1)| 00:07:18 |
------------------------------------------------------------------------------

-- 1번부서 데이터만 스캔하여 그룹핑
-- 28초
select /*+ index(myemp1 idx_myemp1_deptno) */
deptno, avg(sal) from myemp1
where deptno = 1
group by deptno

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 103
| 1 | SORT GROUP BY NOSORT | | 1 | 9 | 103
| 2 | TABLE ACCESS BY INDEX ROWID| MYEMP1 | 4000K| 34M| 103
| 3 | INDEX RANGE SCAN | IDX_MYEMP1_DEPTNO | 4000K| | 7732

2. deptno, sal 복합인덱스를 이용하자.
drop index idx_myemp1_deptno
create index idx_myemp1_deptno_sal on myemp1(deptno,sal)

-- 1초, deptno컬럼은 varchar2임
select /*+ index(myemp1 idx_myemp1_deptno_sal) */
deptno, avg(sal) from myemp1
where deptno = '1'
group by deptno

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%C
U)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 10930
1)| 00:02:12 |
| 1 | SORT GROUP BY NOSORT| | 1 | 9 | 10930
1)| 00:02:12 |
|* 2 | INDEX RANGE SCAN | IDX_MYEMP1_DEPTNO_SAL | 4000K| 34M| 10930
1)| 00:02:12 |
-------------------------------------------------------------------------------

3. MVIEW를 이용

CREATE MATERIALIZED VIEW m7
BUILD IMMEDIATE
REFRESH
COMPLETE
ON DEMAND -- 요청이 있을때 뷰갱신
ENABLE QUERY REWRITE
AS
select
deptno, avg(sal) avgsal from myemp1
group by deptno

-- 0초
select deptno, avg(sal) from myemp1
group by deptno
having deptno = 1

--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 3 (0)| 00:00
|* 1 | MAT_VIEW REWRITE ACCESS FULL| M7 | 1 | 26 | 3 (0)| 00:00

[오라클교육,SQL교육,튜닝교육학원추천_탑크리에듀][COUNT,SQL튜닝]구체화뷰를 이용한 count(*) 함수 튜닝, oracle mview를 이용한 count튜닝

[COUNT,SQL튜닝]구체화뷰를 이용한 count(*) 함수 튜닝, oracle mview를 이용한 count튜닝

myemp1 테이블은 현재 데이터가 2000만건쯤 있고 empno 컬럼은 primary key이다.
실습데이터는 다음 URL을 참조하여 만드세요

http://www.ojc.asia/bbs/board.php?bo_table=LecHINT&wr_id=117

오라클11g의 경우 아래와 같은 SQL문 실행시 index fast full scan을 하도록 되어 있다.
약 8초쯤 걸렸다.(힌트를 안써도 인덱스 패스트 풀 스캔한다.)

SQL> select /*+ index_ffs(e SYS_C0011122) */ count(empno) from myemp1 e

SQL> select count(empno) from myemp1 e

이번에는 mview를 이용하여 count(*) 튜닝을 해보자. 물론 원본데이터가 변경 되더라도 즉시 mview에 반영이 되어 count가 증가되어야 한다.

1. drop MATERIALIZED VIEW LOG ON myemp1;(이미 있다면)
CREATE MATERIALIZED VIEW LOG ON myemp1 WITH PRIMARY KEY, ROWID
INCLUDING NEW VALUES;

2. CREATE MATERIALIZED VIEW m_count
BUILD IMMEDIATE -- MView 생성과 동시에 데이터들도 생성
REFRESH FAST -- 원본의변경된 데이터만 mview에 갱신
ON COMMIT -- Commit 이 일어날 때 뷰 Refresh
ENABLE QUERY REWRITE
AS
select count(*) cnt from myemp1

count를 해보자 0초 걸린다. mview가 사용됨을 실행계획을 통해 알 수 있다.

SQL> select count(*) from myemp1;

COUNT(*)
----------
20000000

경 과: 00:00:00.00

Execution Plan
----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
me |
--------------------------------------------------------------------------------
--------
| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00
:00:01 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| M_COUNT | 1 | 13 | 3 (0)| 00
:00:01 |

이번에는 myemp table에 데이터를 한건 입력하고 mview에 실시간으로 반영되는지 확인하자.

SQL> insert into myemp1 (empno, ename) values (22222222, '222길동');

1 개의 행이 만들어졌습니다.

SQL> commit;

커밋이 완료되었습니다.

SQL> select count(*) from myemp1;

COUNT(*)
----------
20000001

[오라클교육,SQL교육,튜닝교육학원추천_탑크리에듀][오라클 조인]중첩루프조인(Neted Loop Join)이란?, Driving/Inner Table, Oracle Join

[오라클 조인]중첩루프조인(Neted Loop Join)이란?, Driving/Inner Table, Oracle Join

Nested Loop Join

중첩루프조인(Nested Loop Join)이란?

부분 범위처리에 유리한 조인 방식으로 두개 이상의 테이블 구조에서 하나의 테이블(Driving/Outer Table)을 기준으로 순차적으로 다른 테이블(Driven/Inner Table)의 ROW를 조인하여 원하는 데이터를 추출하는 방식의 조인이다. 추출되는 ROW가 많아 질수록 성능이 현저히 떨어지며 성능을 높이기 위해서는 Drving Table의 크기가 작을수록 유리하며 Inner Table은 조인되는 컬럼으로 인덱스가 구성이 되어 있어야 한다. 즉 드라이빙 테이블의 크기를 어느 정도 줄이느냐, Inner Table에 인덱스가 있느냐에 따라 성능이 결정되는 방식이다.

Inner Table에서 인덱스를 사용한다면 검색알고리즘을 적용할 수도 있으며 한번 읽혀진 블록은 DataBase Buffer Cache에 남겨져 있기에 반복적인 I/O양은 최소화 될 수 있다. 만약 Inner Table의 크기가 작다면 메모리에 올려서 검색하는 것이 좋지만 현실적으로 대용량의 데이터 처리에서는 이러한 처리는 어렵다.

Nested Loop join에서는 Inner Table을 랜덤 액세스에 의해 접근하므로 랜덤액세스 되는 ROW가 많다면 수행속도가 저하된다. 만약 Inner Table Join column에 인덱스가 없다면 Hsah or Merge Join을 이용해야 한다. 또한 Inner Table에서 조인 컬럼은 Unique Column 일수록 성능은 좋다.

[특징]

- Driving Table 인덱스 엑세스의 경우 첫 번째 매칭되는 데이터를 제외하면 나머지 데이터는 스캔 한다.

- 조인작업은 모두 랜덤 엑세스

- 조인방향에 다라 인덱스가 선택적으로 사용된다.

- 주어진 상수 값에 의해 범위가 줄어드는 것은 아니며 값을 받아 처리 범위를 결정한다.

- 조인 후 마지막 체크되는 조건은 수행속도에 영향을 준다.

- 부분범위 처리시에 사용하며 확실히 범위를 줄여 줄 조건들이 있을 때 사용

- Driving Table이 대량이거나 Inner Table에서 조인되는 데이터가 대량인 경우 Sort Merge, Hash Join 검토할 것

[처리방식]

다음과 같은 SQL을 생각해 보자.

- Emp table의 deptno, dept table의 deptno 컬럼은 인덱스 존재

- Dept

Select /*+ ordered use_nl(e) */

e.ename, e.deptno, d.dname

From dept d, emp e //dept 테이블이 드라이빙 테이블

Where e.deptno = d.deptno

and e.job = ‘CLERK’

and d.deptno = 10

and d.loc like ‘서울%’

[처리순서]

1. Dept의 deptno 인덱스를 경유하여 deptno = 10인 데이터를 인덱스 범위(index range)추출

2. 처음 레코드 Access

3. Dept의 deptno인덱스의 ROWID를 이용하여 dept 테이블의 원본데이터 몽땅 추출 후 상수 값으로 두고, 이 값을 통해 d.loc = ‘서울%’인지 확인 후 맞으면 다음수행 아니면 2에에부터 다시 시작

4. e.deptno = d.deptno를 수행한다. 현재 d.deptno는 10이므로 상수처리되어 e.deptno =10인 모양이 되며 emp table의 데이터 추출 job=’CLERK’인지 확인하여 맞으면 select 아니면 버림

5. 2부터 다시 시작

[오라클교육,SQL교육,튜닝교육학원추천_탑크리에듀]Literal SQL & Bind Variable SQL 사용하기(SQL바인드변수,리터럴)

Literal SQL & Bind Variable SQL 사용하기(SQL바인드변수,리터럴)

Literal SQL문을 많이 사용하면 Hard Parsing의 빈도를 높이게 되어 Library Cache내에서 Cache되는 SQL문들이 자주 age out 하게 되므로 주기를 빠르게 하고 Dictionary Cache의 사용율을 높이게 된다. 이러한 이유로 OLTP DB환경에서는 Shared SQL문 중에서 Literal SQL 문들을 찾아서 Bind Variable을 이용한 방법을 사용하도록 해야 한다.

Hard Parse

하드파싱이란 새 SQL 문장이 실행 되는 경우엔 Shared Pool에는 없으므로 완전히 전부 새로 파싱을 한다는 의미이다. 오라클은 Shared Pool에 새로운 SQL문장을 할당 하며 SQL 문장이 문법은 맞는지, 권한이 있는지 등을 검사 하게 됩니다. 이 경우 CPU 사용이 매우 많아 지게 되고 Latch의 사용도 증가 하게 됩니다.

Soft Parse

소프트 파싱이란 스행하고자 하는 SQL 문장이 이미 Shared Pool에 있어 이미 존재하는 SQL에 관련된 정보를 그대로 이용하는 겁니다.

결국 Soft Parse가 되기 위해서는 가능 하면 동일한 SQL 문장을 구사해야 한다.

우선 하드 파싱의 대상에는 어떤 것이 있는지 알아 보자.

- 우선 같은 테이블을 질의 하더라도 사용자 계정이 다른 경우 동일한 SQL 문장으로 간주 되지 않는다.

- SQL문장의 공백이 다른 경우

select * from emp

select * from emp는 다르다.

또한 동일한 질의라도 SQL문장의 대소문자 역시 다르면 이것 역시 하드 파싱의 대상이므로 삼가 해야 한다.

- SQL문장의 라인이 달라도 다르게 인식한다.

아래는 V$SALAREA라는 동적뷰에서 실행된 SQL 조회

select substr(sql_text,1,40) "SQL", count(*),

sum(executions) "총 실행 횟수"

from v$sqlarea

group by substr(sql_text,1,40)

having count(*) > 5

order by 2;

[실습]

SQL> conn / as sysdba

연결되었습니다.

공유 영역을 클리어 합니다.

SQL> alter system flush shared_pool;

시스템이 변경되었습니다.

SQL> conn scott/tiger

연결되었습니다.

SQL> select count(*) from emp;

COUNT(*)

----------

v$SQLAREA 뷰에서 확인

SQL> conn / as sysdba

SQL> select substr(sql_text,1,40) "SQL", count(*),

2 sum(executions) "총 실행 횟수"

3 from v$sqlarea

4 where sql_text like '%emp%'

5 group by substr(sql_text,1,40)

6 having count(*) > 0

7 order by 2;

SQL COUNT(*) 총 실행 횟수

---------- ----------------------------------

select count(*) from emp 1 1

이하 생략

SQL> conn scott/tiger

연결되었습니다.

SQL> select count(*) from emp;

COUNT(*)

----------

SQL> select count(*) from emp;

COUNT(*)

----------

그런 다음 다시 v$SQLAREA에서 확인 하죠^^

SQL> select substr(sql_text,1,40) "SQL", count(*),

2 sum(executions) "총 실행 횟수"

3 from v$sqlarea

4 where sql_text like '%emp%'

5 group by substr(sql_text,1,40)

6 having count(*) > 0

7 order by 2;

SQL

---------------------------------------------------------------------

SQL COUNT(*) 총 실행 횟수

---------- -----------------------------

select count(*) from emp 1 3

이번에는 동일한 SQL 문장인데 공백을 더 넣어서 Hard Parsing이 일어나게 해 보자.

SQL> conn scott/tiger

연결되었습니다.

SQL> select count(*) from EMP;;

COUNT(*)

----------

V$sqlarea를 조회해 보자

SQL> select substr(sql_text,1,40) "SQL", count(*),

2 sum(executions) "총 실행 횟수"

3 from v$sqlarea

4 where sql_text like '%emp%'

5 group by substr(sql_text,1,40)

6 having count(*) > 0

7 order by 2;

SQL COUNT(*) 총 실행 횟수

---------- ---------------------------------------

select count(*) from EMP 1 1

select count(*) from emp 1 3

[손자병법,저자직강강의,PPT강의,프레젠테이션강의추천]손자병법을 활용한 제안서 작성 비밀_탑크리에듀 ppt강좌, 프리젠테이션 강의

손자병법을 활용한 제안서 작성 비밀_탑크리에듀 ppt강좌, 프리젠테이션 강의 from 탑크리에듀(구로디지털단지역3번출구 2분거리)

탑크리에듀의 본과정은 병법에 기초한 제안서 작성 기법을 단기간에 습득하여 제안작업이 가능하도록 이론과 실습을 통해서 학습하는 과정입니다. 기본과정과 고급과정으로 나뉘어져 있으며 제안 전체 과정의 주요 핵심 포인트 이해와 전문적인 제안 요령및방법을 실습과 병행하여 진행 합니다.

[닷넷학원,C#학원,멀티쓰레드학원추천](C#,멀티쓰레드강좌)쓰레드, STA, MTA개요, 간단한 멀티쓰레드 예제

(C#,멀티쓰레드강좌)쓰레드, STA, MTA개요, 간단한 멀티쓰레드 예제_닷넷,C#,WPF,자마린실무강좌 from 탑크리에듀(구로디지털단지역3번출구 2분거리)

탑크리에듀(http://www.topcredu.co.kr),오라클자바커뮤니티(http://ojc.asia)에서 제공하는 초보자를 위한 닷넷, C# 기초강좌 입니다. 이번강좌에서는 쓰레드에 대해 간단히 살펴보고 C#에서 멀티 쓰레드로 프로그래밍 하는 방법을 실습하는 강좌입니다. 동영상으로 보시려면 페이스북(http://www.facebook.com/topcredu.co.kr) 또는 유튜브(https://www.youtube.com/playlist?list=PLy5poRzDk4ypnjylVgzAo8RVWgiBzvz73&disable_polymer=true)에서 확인 기능합니다.