본문 바로가기

빅데이터과정

#40_140807_OWI_SHARED POOL에서 LATCH 경합 # SHARED POOL에서 LATCH 경합 l shared pool 의 역할 - parsing을 최소화 하기 위한 메모리 영역- SQL과 실행계획, parse tree(실행코드)의 코드를 공유함으로써 파싱을 최소화 할 수 있다 l Shared pool의 구조 - chunck : 메모리 저장 최소단위 - soft parse : 똑 같은 SQL이 존재하면 parsing 생략한다 - hard parse : 똑 같은 문장이 없기 때문에 다시 parse한다 - soft parse를 위해서는 Library cache latch를 잡아야 한다 - hard parse를 위해서는 Shared pool latch가 필요하다 - Library cache latch : 똑 같은 SQL이 있는지 검색할 때 필요 - Share.. 더보기
#39_140806_OWI_UPDATABLE JOIN VIEW # UPDATABLE JOIN VIEW l updatable join view - 아래의 update 문장은 90만건 이상을 가진 테이블을 update 하는문장인데 이런 경우에 메인쿼리를 읽고 서브쿼리를 읽어들이기 때문에 date_id의 rn이 맞는 row를 하나하나 찾아서 90만* 90만 = 1800만 건의 update를 수행한다- 이럴 경우 updatable join view가 필요하다- 이 조인방법은 다른 조인방법에 비해서 속도가 빠르다 update sales200 s set date_id = ( select date_idfrom time2 t where t.rn = s.rn ); l updatable join 예 - 위 쿼리를 보면 emp_loc 컬럼은 비어있는 컬럼이고 dept_loc는 각각의 .. 더보기
#39_140806_OWI_LATCH # LATCH l Lock 1. Latch : 메모리를 보호하기 위한 락- 대기 이벤트가 latchf를 잡을 떄 많은 CPU가 사용되기 때문에 느려진다- Latch는 잡아서 사용하기가 어렵다- Latch는 내가 확보해놓은 공간을 다른 것들이 쓰지 못하게 한다 2. enqueue : 디스크를 보호하기 위한 락 l Latch 테스트 16:35:30 SQL> @execEvent name to simulate: cache_buffers_chains_latchSession count [10]: 30Expired by time(1) or looping count(2) [1]: 1Execution internval(sec or count) [30]: 60Enable_trace (1=TRUE, 0=FALSE) [0]:.. 더보기
#39_140806_OWI_WAIT EVENT # WAIT EVENT l SQL 튜닝을 해도 전반적으로 오라클 DB의 성능이 저하되는 문제가 발생 한다면 OWI 활용한 서버튜닝을 해야한다 1. OWI(Oracle Wait Interface) : 오라클에서 발생하는 성능상의 문제들에 대해서 오라클 개발자들이 원인에 대한 현상을 정의했는데 그 현상의 이름이 대기 이벤트이다- 예) Oracle wait event 목록 확인SQL> select name from v$event_name; 2. 총 대기시간(Response time) = 서비스 시간(Cpu time) + 대기시간(wait time) Wait event 테스트 1. Orange ~> tool ~> session monitor 2. Orange- update 문이 왜 오래 걸리는지 이유를 알려주고.. 더보기
#39_140806_TUNING_SQL 재작성 # SQL 재작성 문제104. 아래의 SQL을 재작성하시오(아래처럼 조인을 안하고도 똑 같은 결과가 나오도록 하시오) # 튜닝 전select decode(no,1,deptno,2, null), sum(sal) from emp e, (select rownum no from dual connect by level <=2 ) d group by decode(no,1,deptno,2, null) order by decode(no,1,deptno,2, null);# 튜닝 후SELECT deptno, SUM(sal)from EMPGROUP BY ROLLUP(deptno); 문제105. 아래의 SQL을 재작성 하시오(조인을 안하고도 똑 같은 결과가 나오도록 하시오) # 튜닝 전select empno, ename,sa.. 더보기
#39_140806_TUNING_TUNING ADVISOR # TUNING ADVISOR l SQL 프로파일 : SQL은 그대로 두고 실행계획만 최적의 실행계획으로 변경하는 기능 문제100. 튜닝전 SQL을 SQL tuning advisor 에게 맡겼을 때 결과 select /*+ optimizer_features_enable('10.2.0.3') merge(v) */ ename, sal, substr( totalsal,1,10) maxsal, substr(totalsal,11,10) minsal, substr(totalsal,21,10) sumsal from (SELECT ename, sal, (SELECT rpad(MAX(sal),10,' ') || rpad(MIN(sal),10,' ') || rpad(SUM(sal),10,' ') FROM EMP ) as .. 더보기
#37_140805_TUNING_VIEW JOIN # VIEW JOIN 문제93. 이름과 월급과 부서위치를 출력하는 view를 생성하고 이 view를 salgrade 테이블을 조인해서 이름과 월급과 부서위치와 급여등급(grade)를 출력하시오 CREATE VIEW emp5000ASSELECT e.ename, e.sal, d.locFROM EMP e, DEPT dWHERE e.deptno=d.deptno; SELECT /*+ no_merge(v) */ e.ename, e.sal, e.loc, s.gradeFROM EMP5000 e, SALGRADE sWHERE sal BETWEEN losal AND hisal;- view를 해체하지 않도록 no_merge 힌트 사용- 실행계획에 view가 나오지 않는다면 view를 해체하지 않는 것이다- 그러나 아래와 같.. 더보기
#37_140805_TUNING_옵티마이저 힌트 총정리 # 옵티마이저 힌트 총정리 l 인덱스 관련 힌트 관련 힌트인덱스 방법indexindex range scanindexindex unique scanindex_fsindex full scanindex_ffsindex fast full scanand_equalindex merge scanindex_combineindex bitmap merge scanindex_descindex descending scanindex_ascindex ascending scanindex_ssindex skip scanno_indexindex를 사용 못하게 l 조인에 관련된 힌트 - 조인 순서 관련 힌트조인 순서leadinglaeding 힌트 안에 테이블 순서orderedfrom 절의 테이블 순서swap_join_inputshash.. 더보기
#37_140805_TUNING_바인드 변수와 실행계획 # 바인드 변수와 실행계획 1. 바인드 변수를 사용할 때 주의할 사항 - SQL> select empno, ename, sal, from emp where empno = 7788;- 위 쿼리 수행 단계1) parsing > SQL > 기계어: 실행계획, SQL, Parse tree 3가지가 shared pool 캐쉬가 된다: 다음번에 똑 같은 문장이 들어오면 parsing 과정을 생략하기 위해서 수행2) execute3) fetch 2. 바인드 변수 사용 SQL> select empno, ename, sal from emp where empno = 7788;SQL> select empno, ename, sal from emp where empno = 7902- 위의 2문장은 다른 문장으로 인식한다SQL>.. 더보기
#37_140805_TUNING_실행계획과 통계정보 # 실행계획과 통계정보 l 10g 버전부터는 매일 밤 10시에 자동으로 DB의 모든 테이블에 대해서 통계정보가 자동으로 수집- SQL> select cline_name, status from dba_autotask_clinet’- auto optimizer stats collection : 테이블 통계정보 확인- auto space advisor : 빈공간을 확보하는 작업- sql tuning advisor : 자동으로 SQL 튜닝 l 현업에서 테이블통계정보를 안하는 이유- 대용량 테이블은 수집시간이 오래걸리기 때문에- 새로 수집한 통계정보로 인해서 실행계획이 달라져서 기존에 잘돌던 SQL이 느려짐을 우려해서 n 통계정보 잠그는 명령어 DBMS_STATS.LOCK_TABLE_STATS('OE','ORDE.. 더보기
#37_140805_TUNING_HISTOGRAM # HISTOGRAM l 히스토그램이란? - 데이터의 분포도의 편차가 심한 emp100 테이블의 ename의 경우에는 히스토그램을 생성해야 한다. 그래야 실행계획이 정확해진다- 통계정보 수집 : sampling 할 data를 담을 상자(버켓)를 1개- 히스토그램을 수집 : sampling 할 data를 담을 상자(버켓)를 여러 개- 중복된 데이터가 많은 경우를 표준편차가 크다고 표현하는데 이런 경우에는 버켓이 여러 개가 필요해서 히스토그램을 수집해야 한다- 반면에 emp 테이블 같은 경우는 표준편차가 작다고 표현하는데 이런 경우는 버켓이 한 개로 충분하여 통계정보를 수집해야 한다 l 히스토그램 테스트 1. exec dbms_stats.delete_table_stats('SCOTT','EMP100'); 2.. 더보기
#37_140805_TUNING_DYNAMIC SAMPLING # DYNAMIC SAMPLING 옵티마이저 동적 표본 추출 작업 • 샘플링은 컴파일 시 수행됩니다. • 동적 표본 추출 기능을 사용하여 질의를 수행하는 경우 다음과같은 결과가 나타납니다.– 데이터 표본 추출을 위해 반복적인 SQL 문이 실행됩니다.– 샘플링된 블록 수는 OPTIMIZER_DYNAMIC_SAMPLING 초기화파라미터에 따라 다릅니다. • 동적 샘플링 동안 선택성을 확인하기 위해 표본에 술어가적용됩니다. • 다음 경우에 동적 표본 추출을 사용합니다.– 표본 추출 시간이 실행 시간의 극히 일부에 불과한 경우– 질의가 여러 번 실행되는 경우– 더 좋은 계획을 찾을 수 있다고 판단되는 경우 SELECT * FROM EMP100 WHERE ename='scott1'; SELECT * FROM EM.. 더보기
#37_140805_TUNING_MODEL 절 # MODEL 절 select empno, ename,year2,salfrom emp700modelpartition by (empno, ename)- 기준이 되는 컬럼dimension by (year2)- 예상 연도에 대한 컬럼measures (sal)- 새로 계산이 되는 컬럼 rules ( sal[1983] = sal[1982] * (sal[1982]/sal[1981]) )order by year2,ename; 점심문제62. SQL model 절을 이용해서(게스글 299번) OECD년간 근로시간의 2014년도 데이터를 예상하시오 SELECT * FROM ( SELECT nation, SUBSTR(year,6) AS year, CNT FROM LAB_TIME unpivot (cnt FOR year IN .. 더보기
#36_140804_TUNING_STAR JOIN # STAR JOIN - 노란색 테이블은 아주 큰 대용량 테이블- 파란색 테이블이 아주 작은 테이블- 파란색 테이블끼리 연결고리가 없어서 연결고리 없이 조인 한다- 파란색이 크기가 작은 테이블들인데 sales의 거대한 테이블과 hash나 nl 각각 조인을 하려면 시간을 많이 소비하게 된다- 그렇기 때문에 크기가 작은 테이블들을 먼저 조인하고 맨 나중에 가장 큰 테이블인 sales 를 조인하면 속도를 높일 수 있다 # dept ---- emp ---- salgradeSELECT e.ename, d.loc, e.sal, s.gradeFROM EMP e, DEPT d, SALGRADE sWHERE e.deptno = d.deptnoAND e.sal BETWEEN s.losal AND s. hisal; SELE.. 더보기
#36_140804_TUNING_UNION TUINING # UNION TUINING 문제76. 직업, 직업별 토탈월급을 출력하는데 맨 아래쪽에 전체 토탈월급도 출력하시오 # 튜닝 전SELECT job, SUM(sal)FROM EMPGROUP BY jobUNION ALLSELECT NULL AS job, SUM(sal)FROM EMP;db block gets 0consistent gets 53physical reads 0# 튜닝 후SELECT job, SUM(sal)FROM EMPGROUP BY ROLLUP(job); db block gets 0consistent gets 46physical reads 0 문제77. 부서번호, 부서번호별 인원수를 출력하는데 맨 아래쪽에 전체 인원수도 출력하시오 # 튜닝 전SELECT deptno, COUNT(*)FROM EMP.. 더보기
#35_140801_TUNING_SUBQUERY TUNING # SUBQUERY TUNING l 서브쿼리를 쓸 수 있는 절 1. select- scalar subquery2. from- in line view3. where- subquery4. having- subquery5. order by- scalar subquery 문제70 이름, 월급, 부서번호, 사원 테이블 전체의 최대월급, 사원 테이블 전체의 최소월급,사원 테이블 전체의 토탈월급을 출력하시오 # 튜닝 전SELECT ename, sal, deptno, (SELECT MAX(sal) FROM emp),(SELECT min(sal) FROM emp), (SELECT sum(sal) FROM emp)FROM EMP; db block gets 0consistent gets 67physical reads 0# .. 더보기
#35_140801_TUNING_JOIN TUNING2 # JOIN TUNING l HASH JOIN RIGHT SEMI- hash join을 하는데 오른쪽에 있는 쿼리부터 조인시키겠다는 의미이다 문제. 아래의 실행계획을 hash join right semi로 만드시오 SELECT /*+ qb_name(main) */ enameFROM EMPWHERE empno IN (SELECT /*+ qb_name(sub) hash_sj swap_join_inputs(emp@sub) */ mgr FROM emp); - hash join은 main과 sub의 순서를 바꾸기 위해서는 swap_join_inputs 문장을 써줘야 한다- 그래서 괄호안에 테이블 이름을 쓰면 되지만 위 같은 경우에는 괄호안에 emp@ 이후에 query block name의 이름을 써주면 된다- 만.. 더보기
#34_140731_TUNING_JOIN TUNING # JOIN TUNING nested loop joinhash joinsort merge join실행계획 위쪽 테이블driving tablehash tabledriving table실행계획 아래쪽 테이블driven tableprobe tabledriven table l join tuning 할때 중요한 점 1. 조인 순서- ordered- leading 2. 조인 방법- use_nl- use_hash- use_merge 3. 주의사항- 대용량 테이블을 조인할 떄는 hash join 이나 sort merge join 이 유리하다- 그래서 hash join 이나 sort merge join 은 주로 index scan 보다는 full table scan이 더 유리하다- 적은양의 테이블을 join 할 떄는 n.. 더보기
#34_140730_TUNING_OPERATOR # OPERATOR INLIST ITERATOR l in 연산자를 사용했을 때의 SQL 성능을 높이는 방법 - inlist를 이용해서 쿼리를 작성할 경우에 index scan이 나눠지지 않고 잘 수행되면 튜닝할 필요가 없다 - 그러나 아래의 그림처럼 index가 나눠지는 경우에는 concat 힌트를 이용해서 하나로 합쳐줄 필요가 있다/*+ QB_NAME(main) use_concat(@main 8) */ 문제40. 부서번호에 인덱스를 생성하고 부서번호가 10번 20번인 사원들의 이름과 월급과 직업과 부서번호를 출력하시오 CREATE INDEX emp_deptno ON EMP(deptno); # 튜닝 전# 현재 방식SELECT ename, sal, jobFROM EMPWHERE deptno IN (10,2.. 더보기
#34_140730_TUNING_SORTING OPERATION # SORTING OPERATION sort를 일으키는 SQL 1. order by- 인덱스를 이용해서 sort를 회피 2. 그룹함수 : max, min, avg, sum- 인덱스를 이용해서 sort를 피함 3. 집합 연산자 : minus, union, intersect- union all 을 사용 또는 SQL 변경를 사용해서 sort를 피함 4. 조인방법 : ort merge join- 다른 조인방법으로 유도하여 sort를 피함 5. 인덱스 생성시 : create index ….- 신속하게 인덱스를 생성되게 하여 sort를 피함 문제31. 사원이름과 월급을 출력하는데 월급이 낮은 사원부터 높은 사원 순으로 출력하시오 # 튜닝 전SELECT ename, salFROM EMPORDER BY sal asc;.. 더보기
#34_140730_TUNING_INVISIBLE INDEX # INVISIBLE INDEX n 수행 예제SQL> demobld.sqlSQL> create index emp_sal on emp(sal);SQL> alter index emp_sal invisible;SQL> select index_name,visibility from user_indexes;INDEX_NAME VISIBILIT------------------------------ ---------EMP_SAL VISIBLESALES500_RN VISIBLESALES500_CHANNEL_PROFIT VISIBLESALES500_CHANNEL_ID VISIBLESALES500_AMOUNT_SOLD VISIBLE………………………………………………………- invisible 유무 확인SQL> select /*.. 더보기
#33_140729_TUNING_INDEX MONITORING # INDEX MONITORING l 인덱스가 많아지면 테이블에 insert 속도가 떨어진다- insert 하는 순간 각각의 index를 다시 구성해야 하기 때문에 느려진다 l 인덱스가 많아지면 공간도 많이 차지한다 l 인덱스 사용 여부 모니터링 하는 방법 SQL> @demobld. sql SQL> create index emp_sal on emp(sal); SQL > alter index emp_sal monitoring usage; SQL> select index_name, used, monitoringfrom v$object_usage; SQL> select /*+ index(emp emp_sal) */ ename, sal, job from emp where sal =3000; SQL> select.. 더보기
#33_140729_TUNING_INDEX SCAN # INDEX SCAN 점심문제14. 이름, 월급, 부서번호, 자기가 속한 부서번호별 평균월급을 출력하시오(아래의SQL을 각각 출력해서 튜닝전후가 비교가 되도록 레포팅) 분석함수 : listagg, max(sal) over () …. 1. 분석함수 이용2. 분석함수 이용하지 않은 것 # 튜닝 전SELECT ename, sal, deptno, AVG(sal) OVER(PARTITION BY deptno)FROM EMP;db block gets 0consistent gets 56physical reads 0# 튜닝 후SELECT e.ename, e.sal, e.deptno, f.averFROM EMP e, (SELECT deptno, AVG(sal) aver FROM EMP GROUP BY deptno) f.. 더보기
#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 sca.. 더보기
#32_140728_TUNING_TUNING BASIC # TUNING BASIC 문제2. 직업, 직업별 토탈월급을 출력하는데 직업이 SALESMAN 인 사원들을 제외하고 출력하고 직업별 토탈월급이 4000 이상인 것만 출력하고 직업별 토탈월급이 높은 것부터 출력하시오 SELECT job, sum(sal) sumsalFROM EMPWHERE job 'SALESMAN'GROUP BY jobHAVING SUM(sal) >= 4000ORDER BY sumsal desc; l having 절은 그룹함수로 조건을 줄 때만 사용하고 job 'SALESMAN'과 같은 일반적인 조건은where 절에 사용한다- 일반적인 조건을 having 절에 쓰면 성능이 느려진다 l Buffer cache와 Shared pool 을 flush 시키는 명령어SQL> alter system .. 더보기
#31_140725_WSHOP2_SCHEDULER # SCHEDULER l 특정 스크립트를 특정시간에 돌아가도록 자동화 하는 방법1. 리눅스에 crontap 기능2. dmbs_job 패키지를 사용(9i 버전)3. dbms_scheduler 패키지를 사용(10g) 버전 l 스케줄을 사용해서 작업을 편하게 할 수 있게 하려면 l 스케줄 사용방법1. 시간 기준2. 이벤트 기준 -- 프로시져 생성SQL> connect /as sysdbaSQL> create or replace procedure gather_me is begin dbms_stats.gather_database_stats; end;/- DB에에 있는 모든 테이블들에 대해서 통계정보를 수집하는 명령어를 수행하는 프로시져-- 프로그램 생성SQL> exec dbms_scheduler.create_pro.. 더보기
#31_140725_WSHOP2_RESOURCE MANAGER # RESOURCE MANAGER l Resource manager란? - 특정 세션이 오라클 서버의 리소스를 과도하게 사용하지 못하도록 제한을 거는 기능 l Resource manager 기능을 사용하기 위해서 설정해야할 내용 - resource consumer group : 리소스를 사용하고 있는 그룹- resource plan : daytimeplan, nighttimeplan을 만듦- daytimeplan - cpu 할당방식을 정한다: oltp 80% cpu 사용: batch 20% cpu 사용 l Resource Manager 로 제한할 수 있는 리소스의 종류 - CPU 사용량- 병렬도 : 병렬작업할 수 있는 수/*+ parallel(emp,20) */- 활성 세션 수 : waiting 하고 있.. 더보기
#31_140725_WSHOP2_RESUMABLE SPACE ALLOCATION # RESUMABLE SPACE ALLOCATION l Resumable space allocation 관리 - resumable session 이란?: DML 작업이 유보되고 있는 세션을 말한다: dba_outstanding_alert : resumable session 존재 여부를 알려줌 l resumable session 기능을 활성화 시키는 명령어 1. SQL> alter session enable resumable;- resumable 기능을 세션레벨로 활성화 2. SQL> exec dbms_resumable.set_timeout(200);- 유보되는 시간을 200초로 하겠다 문제1. ts500 테이블 스패이스를 사이즈 50m 으로 해서 생성하고 이 테이블 스패이스에emp500 테이블을 생성하고.. 더보기
#31_140725_WSHOP2_DB REORG # DB REORG l row chaining 현상- 하나의 row의 길이가 너무 길어서 다른 블럭에 걸쳐서 입력한 현상- 대용량의 row를 채우고 남은 부분을 다른 블록에 채우게 되서 느려지는 원인이 된다 l HWM-HWM은 여유공간을 남기기 위해 설정하는 경계선- HWM을 사용하는 이유는 만약에 HWM을 설정하지 않으면 빈공간보다 큰 사이즈가 들어오면 다른 테이블 스패이스로 옮겨서 저장하는 현상이 발생하는데 이것을 row migration 현상이라고 한다. 이 현상을 방지하기 위해서 사용한다 - delete를 해도 HWM은 변화없음- delete 이후 데이터가 없을 때 count 명령어를 날리면 fulll scan을 HWM까지 하기 떄문에 오랜 시간이 걸린다 DB REORG l DB Reorg 작업 .. 더보기
#31_140725_WSHOP2_DATABASE REPLAY # DATABASE REPLAY l Database replay- 현재 운용중인 DB의 데이터를 가지고 부하 테스트를 진행하는 것 - 예전 장비와 새로운 장비간의 성능차이를 쉽게 알아낼 수 있는 기능 - 사용하고 있는 SQL 들을 캡쳐 디렉토리가 캡쳐해서 이식한 DB에 SQL을 수행한다 더보기

티스토리툴바