본문 바로가기

빅데이터과정/WORKSHOP 2

#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# .. 더보기
#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을 수행한다 더보기
#31_140725_WSHOP2_DATABASE REPLAY # DATABASE REPLAY l Database replay- 현재 운용중인 DB의 데이터를 가지고 부하 테스트를 진행하는 것 - 예전 장비와 새로운 장비간의 성능차이를 쉽게 알아낼 수 있는 기능 - 사용하고 있는 SQL 들을 캡쳐 디렉토리가 캡쳐해서 이식한 DB에 SQL을 수행한다 더보기
#30_140724_WSHOP2_실행계획 # 실행계획 l 테이블 통계정보란?- 옵티마이저가 수행하려는 SQL에 대한 실행계획을 생성할 때 참고하는 테이블에 대한 분석정보- 이 정보로 정확한 실행계획이 생성된다 l emp100 테이블에 통계정보가 생성됐는지 여부 확인 SQL> select table_name, num_rows, last_analyzedfrom user_tableswhere table_name = 'EMP100';TABLE_NAME NUM_ROWS LAST_ANALYZED------------------------------ ---------- -------------------EMP100- last_analyzed 에 값이 없기 때문에 생성되지 않은 것이다 l emp100 테이블에 통계정보를 수집하는 명령어 SQL> exec db.. 더보기
#30_140724_WSHOP2_오렌지 실행계획 오류 오렌지 실행계획 오류 l 오렌지 실행계획(ctrl + E)이 안보이면 아래를 scott 에서 수행1. scott에서 수행SQL> @?/rdbms/admin/utlxplan.sqlSQL> desc plan_table 2. 오렌지에서 아래 스크립트 수행 create table PLAN_TABLE ( statement_id varchar2(30), plan_id number, timestamp date, remarks varchar2(4000), operation varchar2(30), options varchar2(255), object_node varchar2(128), object_owner varchar2(30), object_name varchar2(30), object_alias varchar2(.. 더보기
#30_140724_WSHOP2_REPORT # REPORT l 성능저하의 원인을 분석하기 위해서 오라클에서 제공하는 3가지 레포트 1. AWR report : 성능저하의 원인을 전반적으로 알려주는 레포트2. ADDM report : 성능저하의 원인에 해결방법을 제공해주는 레포트: @?/rdbms/admin/addmrpt.sql 3. ASH report : 최근에 발생한 악성 SQL과 대기이벤트를 보여주는 레포트 l 레포트 확인SQL> @?/rdbms/admin/addmrpt.sqlSQL> @?/rdbms/admin/addmrpt.sqlInstance DB Name Snap Id Snap Started Level------------ ------------ --------- ------------------ -----dbdb DBDB 101 24 .. 더보기
#30_140724_WSHOP2_LARGE POOL # LARGE POOL l Large pool 의 역할 - 병렬 작업을 하기위한 메모리 영역- Rman으로 백업과 복구를 수행했을 떄 사용하는 메모리 영역- 예 : SQL> select /*+ parallel(emp,4) */ ename, salfrom empwhere job='SALESMAN';- 낮에 중요한 오라클 메모리 영역 : database buffer cache 영역(db_cache_size), 그림의 온라인 영역- 밤에 중요한 오라클 메모리 영역 : larget pool(large_pool_size), 그림의 일괄 처리 작업 l 위의 메모리 사이즈 관리 작업을 자동화 하는 기능이 오라클에 무엇인가?- ASMM(automatic Shared Memory Management) 기능 l ASMM .. 더보기
#30_140724_WSHOP2_SHARED POOL # SHARED POOL shared pool 의 라이브러리 캐쉬 영역을 효율적으로 사용할 수 있는 방법 l Library cache의 역할- parsing 된 정보를 메모리에 올려놓고 똑 같은 SQL이 들어오면 parsing을 생략(soft parse)을 위한 역할- 만약 다시 parsing(hard parse) 한다면 똑 같은 SQL이 없기 때문이다 l 똑 같은 SQL 이란(parsing 생략을 위한) 1. 대소문자 구문 2. 공백이나 들여쓰기 구분 3. 테이블의 소유자가 틀릴떄도 구분 4. Literal SQL 구분 5. 예를 들면SQL> select ename, sal from emp where empno = 7788;SQL> select ename, sal from emp where empno .. 더보기
#30_140724_WSHOP2_ORACLE MEMORY # ORACLE MEMORY l db buffer cache를 효율적으로 사용할 수 있는 방법 1. 다중 버퍼풀을 구성해서 사용해라: 테이블의 데이터를 메모리에 올릴 때 엑세스 되는 빈도에 따라서 다중 버퍼풀의 지정된 영역으로 데이터를 올리도록 하는 기능- 자주 엑세스 하는 테이블(keep 풀) : 메모리에 오래 둠- 자주 엑세스 하지 않는 테이블(recycle 풀) : 메모리에서 빨리 빠져나감- 보통으로 엑세스 하는 테이블(default 풀) : 기본으로 관리되는 LRU 알고리즘 - buffer cache를 3개의 영역으로 나눈다 l 다중 버퍼풀 구성 방법 1. SQL> show parameter buffer 2. SQL> alter system set db_keep_cache_size=20m; 3. .. 더보기
#29_140723_WSHOP2_AUTOMATIC DIAGNOSTIC REPOSITORY # AUTOMATIC DIAGNOSTIC REPOSITORY l Automatic Diagnostic Repository - 11g 버전부터 오라클에 문제가 생겼을 때 진단하기 위해서 필요한 파일들을 하나의 디렉토리 밑에 두고 간단한 명령어(adrci)로 진단을 빨리 할 수 있도록 하는 기능 l 오라클 문제가 생겼을떄 진단하기 위한 로그 정보들은 아래의 디렉토리에 저장된다(11g이상부터 적용) SQL> show parameter diagNAME TYPE VALUE------------------------------------ ----------- ------------------------------diagnostic_dest string /u01/app/oracle l adrci 명령어로 오라클의 문.. 더보기
#29_140723_WSHOP2_FLASHBACK ARCHIVE # FLASHBACK ARCHIVE Flashback Archive(Total Recall) - 특정 테이블에 대한 변경이력정보를 1년 또는 2년 동안 즉 특정 기간동안 계속 저장하고 싶을 때 사용- undo tablespace 말고 별도의 테이블 스패이스를 따로 생성해서 이력정보를 저장하는 방식 l 구현 순서 1. 테이블 스패이스 생성 2. flashback archive 를 관리할 유저를 생성 3. 위에서 생성한 유저로 flashback archive 생성 4. flashback archive를 사용할 테이블을 지정 l flashback archive 테스트 1. SQL> alter system set undo_retention=1; 2. SQL> create tablespace flash_tbs1d.. 더보기
#29_140723_WSHOP2_FLASHBACK QUERY # FLASHBACK QUERY flashback drop- 테이블을 drop 하게 되면 10g 버전부터는 휴지통에 해당 테이블이 들어가게 된다. 휴지통속에서 복원하는 명령어 flashback drop이다. l EMP 테이블을 drop 하는데 휴지통 속에 들어가도록 dop 하시오SQL> drop table emp;SQL> drop table emp purge;- purge 옵션은 휴지통을 거치지않고 삭제하는 기능 l 휴지통속에 있는 object를 확인하시오SQL> show recyclebin l 휴지통속에 있는 emp테이블 복원SQL> flashback table emp to before drop;SQL> select * from emp; l 인덱스의 이름을 변경 SQL> alter index "BIN$.. 더보기
#28_140722_WSHOP2_복구와 FLASHBACK # 복구와 FLASHBACK l 복구와 flashback의 차이는? - 복구(recover)는 백업 받았던 파일을 복원해서 최신파일로 만들어주는 과정- flashback은 현 시점에서 과거로 되돌리기 위한 작업을 수행하는 것 과거로 되돌리기 위해서 그 동안 했던 DML 작업들을 반대로 수행- flashback은 복구보다 빠르다- flashback의 기능 총 정리 flashback database – database 전체를 과거로 되돌리는 기능 - 이 기능을 쓰려면 2가지가 설정 되야 한다 1) 아카이브 모드 2) flashback database 기능이 활성화 되야 한다 SQL> select flashback_on from v$database;FLASHBACK_ON------------------NOSQ.. 더보기
#28_140722_WSHOP2_EXPORT/IMPORT # EXPORT/IMPORT 1. export/import 하는 과정에서 전달해야할 2가지 파일- 해당 테이블 스패이스의 data file- 해당 테이블 스패이스의 메타정보가 들어있는 덤프 파일 2. tablespace level로 export/import를 왜 해야하는가?- 구장비에서 신장비로 데이터 이행을 할 때 가장 빠르게 이행할 수 있는 방법 l tablespace export 테스트 순서 1. SQL> create tablespace ts200DATAFILE '/home/oracle/ts200.dbf' SIZE 5m; 2. SQL> alter tablespace ts200 read only; 3. $ exp transport_tablespace=ts200 tablespaces=ts200 file.. 더보기
#28_140722_WSHOP2_RESTORE POINT # RESTORE POINT l restore point - restore point : 복원할 지점을 지정- 서버의 부하 테스트 하기전에 restore point를 찍고 나중에 원상복구 시키기 위해서 l restore point 테스트 1. SQL> create restore point before_load;2. SQL> drop user scott cascade;3. SQL> shutdown immediate4. SQL> startup mount5. RMAN> restore database until restore point before_load;6. RMAN> recover database until restore point before_load;7. RMAN> alter database open .. 더보기
#28_140722_WSHOP2_DATAFILE OPEN 복구 # DATAFILE OPEN 복구 만약에 장애가 나서 복구를 해야 한다면 open 시켜놓고 복구를 하는게 바람직하다 문제1. ts405 테이블 스패이스를 생성하고 ts405 테이블 스패이스에 emp 테이블과 똑같은 구조와 테이터로 emp405를 생성하고 나서 ts405테이블 스패이스를 백업받으시오 SQL> create tablespace ts405 datafile '/u01/app/oracle/oradata/dbdb/ts405.dbf' size 20m;SQL> connect scott/tigerSQL> create table emp405 tablespace ts405 as select * from emp where 1=2;SQL> exit[dbdb:dbdb]$ rman target /RMAN> backu.. 더보기
#28_140722_WSHOP2_DATAFILE, CONTROLFILE, REDO FILE 복구 # DATAFILE, CONTROLFILE REDO, FILE 복구 문제4. 모든 datafile들과 모든 controlfile 들을 삭제하고 복구하시오 rm *.dbf – dbf 파일 전부다 삭제rm *.ctl – ctl 파일 전부다 삭제 l 복구순서1. SQL>startup2. RMAN> restore controlfile from autobackup;3. RMAN> alter database mount;4. RMAN> restore database;5. RMAN> recover database;6. RMAN> alter database open resetlogs; 1. alert log file을 보고 어떤 파일이 삭제 되서 문제가 생겼는지 확인해야 한다 2. SQL> select group#, s.. 더보기
#28_140722_WSHOP2_NOLOGGING # NOLOGGING l nologging : redo 정보가 생성안하도록 하여 속도를 빠르게 함 l 사원 테이블을 nologging 으로 변경SQL> alter table emp nologging; SQL> alter table emp logging; 더보기
#27_140721_WSHOP2_완전/불완전 복구 # 완전/불완전 복구 l 복구의종류 2가지- 완전 복구 : 마지막 commit 한 시점으로 복구- 불완전 복구 : 과거의 특정 시점으로 복구 l 불완전 복구의 종류- cancel base : current redo log file 또는 archive log file이 깨졌을 때 복구방법으로 사용- time base : 과거의 특정 시점으로 복구# 잘못된 data를 입력했다든지# 아주 중요한 테이블이 drop 되었을때# 유저가 drop 되었을 때 l time base 불완전 복구 순서- 테스트 하기전에 로그 스위치를 여러 번 일으키고 수행한다- 아래를 .bash_profile 에 추가해준다NLS_LANG=american_america.we8iso8859p15NLS_DATE_FORMAT=RRRR/MM/DD:.. 더보기
#27_140721_WSHOP2_CURRENT REDO LOG FILE 복구 # CURRENT REDO LOG FILE 복구 l current redo log file 삭제 후 복구 작업 수행l redo log group 상태 4가지1. unused2. current3. active4. inactive l inactive와 unused redo group 그룹의 경우SQL> alter database drop logfile group 번호; l current redo log group이 깨지면 불완전 복구를 해야한다l currnet redo log file이 깨졌을 때 복구 방법 1. full backup 수행 2. SQL> select group#, status, sequence# from v$log;- 위에서 sequence# 은 로그 시퀀스 번호인데 로그 시퀀스 번호는 LG.. 더보기
#27_140721_WSHOP2_NON CRITICAL FILE 복구 # NON CRITICAL FILE 복구 l 파일의 종류 2가지1. cretical file- data file 들(system tablespace, undo tablespace)- controlfile- redo log file 중에서 상태가 current, active 인것 2. non crtical file- data file 들 중에서 non system file들- temp file- redo log file 중에서 상태가 inactive, unused ● SYSTEM 테이블 스페이스 - Data Dictionary 정보, Stored Procedure의 정의 Package, Trigger 포함 - SYSTEM ROLLBACK SEGMENT 포함 - 사용자 데이터 포함 가능 ● NON-SYSTE.. 더보기
#27_140721_WSHOP2_압축 백업 # 압축 백업 l full backup 을 수행하는 압축으로 백업하시오 RMAN> backup as compressed backupset database;RMAN> list backupset; # 압축 설정 여부 확인 ……………………………………………………………………BS Key Type LV Size Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ ---------------48 Full 9.67M DISK 00:00:03 21-JUL-14 BP Key: 48 Status: AVAILABLE Compressed: NO Tag: TAG20140721T113644 Piece Name: /home.. 더보기
#27_140721_WSHOP2_아카이브 백업 관리 # 아카이브 백업 관리 - 백업 보존 정책과는 별개로 특정 백업본을 지워도 되는 파일리스트에서(report obsolte) 안나오게 하고 싶으면?- RMAN> CHANGE BACKUP TAG 'consistent_db_bkup' KEEP FOREVER;RMAN> backup tag 'month_full_backup' database;RMAN> list backup tag 'month_full_backup';# shtudown abort를 하고 OS에서 datafile 4번을 깨트렸다면 아래와 같이 수행한다SQL> startupRMAN> restore datafile 4 from tag='month_full_backup';RMAN> recover datafile 4 from tag='month_full_b.. 더보기
#27_140721_WSHOP2_INCREMENTAL BACKUP # INCREMENTAL BACKUP l CTWR : 리두로그 버퍼에서 변경사항을 읽고 변경사항에 대한 지도를 만든다.- incremental 백업을 위해서는 CRWR을 활성화 시켜야 한다ALTER DATABASE {ENABLE|DISABLE} BLOCK CHANGE TRACKING [USING FILE '...']- 블록변경사항 추적 모니터SQL> SELECT filename, status, bytes FROM v$block_change_tracking; l 이미지 카피본과는 다르게 백업셋으로 백업했을 때의 장점?1. incremental level 백업은 백업 이후에 변경된 것만 백업 할 수 있다- RMAN> backup incremental level 0 database;# dtabase 전체 fu.. 더보기
#27_140721_WSHOP2_IMAGE COPY # IMAGE COPY RMAN> backup as copy datafile 3;Starting backup at 21-JUL-14using channel ORA_DISK_1using channel ORA_DISK_2using channel ORA_DISK_3……………………………………………………………………RMAN> list copy of datafile 3;List of Datafile Copies=======================Key File S Completion Time Ckp SCN Ckp Time ------- ---- - --------------- ---------- ---------------2 3 A 21-JUL-14 2705190 21-JUL-14 Name: /home/orac.. 더보기
#27_140721_WSHOP2_RMAN 설정 관리 # RMAN 설정 관리 지속 설정 관리 l controlfile 자동백업되는 설정 RMAN> configure controlfile autobackup clear;- default 명령어old RMAN configuration parameters:CONFIGURE CONTROLFILE AUTOBACKUP ON;RMAN configuration parameters are successfully reset to default value RMAN> show controlfile autobackup format;- format 확인RMAN configuration parameters for database with db_unique_name DBDB are:CONFIGURE CONTROLFILE AUTOBACK.. 더보기

티스토리툴바