Microsoft Word - 10gWS2.doc

Size: px
Start display at page:

Download "Microsoft Word - 10gWS2.doc"

Transcription

1 Advanced Product Service Oracle Database 10g : Administration Workshop II Creation Date : 2009년 04월 07일 Last Updated : 2009년 04월 11일 Version : 1.0

2 실습환경설정 cd $HOME/mylabs cat env.sh export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1 export ORACLE_SID=orcl export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/jdk/bin:$PATH export PS1=[\$ORACLE_SID:\$PWD\] export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss" export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1 alias orcl='export ORACLE_SID=orcl' alias catdb='export ORACLE_SID=catdb' alias asm='export ORACLE_SID=+ASM' alias sql='sqlplus / as sysdba' cat env.sh >> $HOME/.bash_profile source $HOME/.bash_profile cat login.sql define _editor=vi set linesize 130 set pagesize 50 set serveroutput on set sqlprompt "_USER'@'_CONNECT_IDENTIFIER>" cat login.sql >> $ORACLE_HOME/sqlplus/admin/glogin.sql cat startup.sh lsnrctl start sqlplus / as sysdba <<EOF startup exit EOF emctl start dbconsole isqlplusctl start $HOME/mylabs/startup.sh $HOME/mylabs/unlock.sh

3 Chapter 2. Configuring Recovery Manager # 전체모든과정의실습을초기화하기위해현재상태의 DB 를 Backup 한다. $HOME/labs/lab_02_copy.sh # Archivelog Mode 확인후 Archivelog Mode 로변환 sqlplus / as sysdba > archive log list Database log mode Automatic archival Archive destination No Archive Mode Disabled USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 2 Current log sequence 4 > show parameter spfile NAME TYPE VALUE spfile string /u01/app/oracle/product/10.2.0/db_1/dbs/spfileorcl.ora > show parameter db_recovery_file_dest NAME TYPE VALUE db_recovery_file_dest string /u01/app/oracle/flash_recovery_area db_recovery_file_dest_size big integer 2G > alter system set db_recovery_file_dest_size = 4G ; > shutdown immediate > startup mount > alter database archivelog ; > archive log list Database log mode Automatic archival Archive destination Archive Mode Enabled USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 2 Next log sequence to archive 4 Current log sequence 4 > alter database open ; > exit # Catalog DB 생성후 Repository 생성및등록 export ORACLE_SID=catdb

4 cat $HOME/mylabs/cre_catdb.sh orapwd file=$oracle_home/dbs/orapwcatdb password=oracle sqlplus / as sysdba <<EOF create spfile from pfile='$home/mylabs/initcatdb.ora' ; startup nomount create database catdb user sys identified by oracle user system identified by oracle character set AL32UTF8 undo tablespace undotbs default temporary tablespace conn exit EOF $HOME/mylabs/cre_catdb.sh -- 약 10~15분정도소요된다. sqlplus / as sysdba > create tablespace rc_tbs ; > create user rman identified by rman default tablespace rc_tbs ; > grant resource, connect, recovery_catalog_owner to rman ; > exit cp $HOME/mylabs/listener.ora $ORACLE_HOME/network/admin/ cp $HOME/mylabs/tnsnames.ora $ORACLE_HOME/network/admin/ lsnrctl reload export ORACLE_SID=orcl rman target / catalog=rman/rman@catdb > create catalog ; > register database ;

5 # EM 에 catalog db 등록 DB Home - Maintenance - Backup/Recover Settings 영역의 Recovery Catalog Settings 선택후 Add Recovery Catalog 클릭 각각의 Box 에그림과같이필요한정보입력후 Next 정보확인후 Finish 선택하면다음그림에서 Use Recovery Catalog 선택후 OK

6 # 여기까지진행하였다면교재 B-2 page 의 2 번문제부터진행한다. # 2장과관련된실습이끝나면추가적인실습및결과확인을위해다음의작업진행한다. ( 부분적으로 Chapter 3 의실습도미리진행된다 ) export ORACLE_SID=orcl rman target / catalog=rman/rman@catdb # 실습을위해보존해야하는 Backup 의수를 1로바꾼다. > configure retention policy to redundancy 1 ; # 현재 Backup 대상이되는 file 들과 Backup 이필요한파일을확인 > report schema ; > report need backup ; RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 1 Report of files with less than 1 redundant backups File #bkps Name /u01/app/oracle/oradata/orcl/system01.dbf 2 0 /u01/app/oracle/oradata/orcl/undotbs01.dbf 3 0 /u01/app/oracle/oradata/orcl/sysaux01.dbf 4 0 /u01/app/oracle/oradata/orcl/users01.dbf 5 0 /u01/app/oracle/oradata/orcl/example01.dbf file 6 is excluded from whole database backup # 현재 Backup 된정보확인 > list backup of database ; -- 아무것도안나와야한다. # Incremental Backup 의기준이되는 Backup을압축해서진행 > backup as compressed backupset incremental level 0 database ; # 특정 tablespace 에속한파일들을 Backup > backup as backupset tablespace users, example ;

7 # 수동으로채널할당하여특정 datafile 만 Backup > run { allocate channel c1 device type disk; backup as compressed backupset datafile 1 ; sql "alter system archive log current" ; } # Backup 된정보확인 > list backup of database ; # 보존정책상필요없는 Backup 확인 > report obsolete ; # Catalog DB에수동으로 Sync. > resync catalog ; > exit # 현재 Flash Recovery Area 의사용률확인 sqlplus / as sysdba select * from v$flash_recovery_area_usage ; FILE_TYPE PCT_SPACE_USED PCT_SPACE_RECLAIMABLE NUM_OF_FILES CONTROLFILE ONLINELOG ARCHIVELOG BACKUPPIECE IMAGECOPY FLASHBACKLOG # EM 을통해서도확인가능하다. (All Metrics - Recovery Area)

8 Chapter 3. Using Recovery Manager # 현재실습은교재 B-10 page까지완료된상태이다. B-11 page 의 6번부터실습을이어서한후아래의실습진행 # Block Change Tracking 활성화 sqlplus / as sysdba > alter database enable block change tracking using file '/home/oracle/labs/rman_change_track.f' ; > column filename format a50 > select * from v$block_change_tracking ; > exit # Incremental Backup 을진행하고결과확인 rman target / catalog rman/rman@catdb > backup as compressed backupset incremental level 1 database ; > list backup of database ; # 보존정책상불필요한 Backup 정보확인 > report obsolete ; # 보존정책상불필요한 Backup 삭제 > delete obsolete ; # 필요없는 Backup 들이삭제되었는지확인 > list backup of database ; > exit ;

9 Chapter 5. Database Recovery # USERS Tablespace에 TEST 테이블을생성하고임의의 Data를입력한다. - Logswitch 도진행됨 sqlplus rman target / catalog rman/rman@catdb > backup as compressed backupset incremental level 1 database plus archivelog ; # Backup 된정보확인 > list backup of database ; > list copy ; > exit # rm 명령으로 OS 상에서 users01.dbf 파일삭제 rm /u01/app/oracle/oradata/orcl/users01.dbf # system 유저로 test 테이블검색. 결과나오는가? sqlplus system/oracle > alter system flush buffer_cache ; > select count(*) from test ; ERROR at line 1: ORA-01116: error in opening database file 4 ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl/users01.dbf' ORA-27041: unable to open file Linux Error: 2: No such file or directory Additional information: 3 # RMAN 이용하여 Recovery 수행 > host rman target / catalog rman/rman@catdb > list backup of tablespace users ;

10 > run { sql "alter tablespace users offline immediate" ; restore tablespace users ; recover tablespace users delete archivelog ; sql "alter tablespace users online" ; } # Backup 진행 > backup as compressed backupset database ; > delete obsolete ; > exit # 결과확인 > select count(*) from test ; > drop table test purge ; # 나머지실습은교재의내용을참조한다.

11 Chapter 6. Flashback # 현재의 flashback mode 확인 ( 교재의실습보다먼저진행 ) sqlplus / as sysdba > select flashback_on from v$database ; FLASHBACK_ON NO # Guaranteed Restore Point 생성 ( 가능한가?) > create restore point first_point guarantee flashback database ; ERROR at line 1: ORA-38784: Cannot create restore point 'FIRST_POINT'. ORA-38787: Creating the first guaranteed restore point requires mount mode when flashback database is off. - 첫번째 point 는 Mount 에서가능 ( Flashback logging 비활성화시 ) > shutdown immediate > startup mount > create restore point first_point guarantee flashback database ; > select current_scn from v$database ; - 번호확인할것 > select flashback_on from v$database ; FLASHBACK_ON RESTORE POINT ONLY > alter database open ; > create table test (id number) ; > insert into test values(1) ; > commit ; # Table 삭제전 point 생성 > create restore point before_drop guarantee flashback database ; > drop table test purge ;

12 > select * from test ; ERROR at line 1: ORA-00942: table or view does not exist # Guaranteed Restore Point 이용하여 Flashback database > shutdown immediate > startup mount > select oldest_flashback_scn, oldest_flashback_time from v$flashback_database_log ; FLASHBACK_SCN FLASHBACK_TIME :02:00 > flashback database to restore point before_drop ; > alter database open resetlogs ; > select * from test ; > drop table test purge ; > drop restore point before_drop ; > drop restore point first_point ; > select flashback_on from v$database ; FLASHBACK_ON NO # Recycle Bin 사용 # 실습에필요한 user 및여러 object 등을생성 > create user user1 identified by oracle ; > grant dba to user1 ; > conn user1/oracle > create table emp as select * from scott.emp ; > alter table emp add primary key(empno) ; > create index deptno_idx on emp(deptno) ;

13 > create trigger emp_trig after insert on emp begin null; end; / # 현재 user1 은여러 object 들을가지고있다. 관련정보확인 > column object_name format a30 > column object_type format a30 > select object_name, object_type from user_objects ; OBJECT_NAME OBJECT_TYPE EMP TABLE DEPTNO_IDX INDEX SYS_C INDEX EMP_TRIG TRIGGER > select constraint_name from user_constraints ; CONSTRAINT_NAME SYS_C # User 의실수로 Table 삭제 > drop table emp ; # emp 와연관된모든 Object 는그즉시삭제된것이아니고이름이변경되어예정저장소에저장되어있다. 각각의내용확인. > select object_name, object_type from user_objects ; OBJECT_NAME OBJECT_TYPE BIN$ZzgT6KzAEXPgQKjAZKsc4Q==$0 TRIGGER BIN$ZzgT6KzBEXPgQKjAZKsc4Q==$0 TABLE BIN$ZzgT6Ky/EXPgQKjAZKsc4Q==$0 INDEX BIN$ZzgT6Ky+EXPgQKjAZKsc4Q==$0 INDEX > select constraint_name from user_constraints ; CONSTRAINT_NAME BIN$ZzgT6Ky9EXPgQKjAZKsc4Q==$0

14 > show recyclebin ORIGINA LNAME RECYCLEBIN NAME OBJECT TYPE DROP TIME EMP BIN$ZzgT6KzBEXPgQKjAZKsc4Q==$0 TABLE :18:56:02 # 확인되는 Recyclebin name 을이용하여결과검색 > select * from "BIN$ZzgT6KzBEXPgQKjAZKsc4Q==$0" ; - 결과나오는가? # 추가적으로동일한이름을사용하는 Table 생성 - 가능한가? > create table emp as select * from scott.emp ; # 예전이름으로복원이불가능하므로새로운이름으로 flashback! > flashback table emp to before drop rename to ori_emp ; > select * from ori_emp ; # 인덱스등의다른 Ojbect 는사용가능할까? > set autotrace on explain > select * from ori_emp where empno = 7788 ; Id Operation Name Rows Bytes Cost (%CPU) Time SELECT STATEMENT (0) 00:00:01 1 TABLE ACCESS BY INDEX ROWID ORI_EMP (0) 00:00:01 * 2 INDEX UNIQUE SCAN BIN$ZzgT6Ky/EXPgQKjAZKsc4Q==$0 1 0 (0) 00:00: > set autotrace off # 결과확인하였다면실습정리 > conn /as sysdba > drop user user1 cascade ;

15 Chapter 9. Automatic Performance Management # snapshot 을수동으로생성하고결과확인 > host cat $HOME/mylabs/dba_hist_snapshot.sql select snap_id, to_char(startup_time, 'YYYY/MM/DD HH24:MI:SS') start_time, to_char(begin_interval_time,'yyyy/mm/dd HH24:MI:SS') begin_time from dba_hist_snapshot order by snap_id; SNAP_ID START_TIME BEGIN_TIME /04/10 18:44: /04/10 19:00: /04/10 18:44: /04/10 20:00: /04/10 18:44: /04/10 21:00:37 > exec dbms_workload_repository.create_snapshot SNAP_ID START_TIME BEGIN_TIME /04/10 18:44: /04/10 19:00: /04/10 18:44: /04/10 20:00: /04/10 18:44: /04/10 21:00: /04/10 18:44: /04/10 22:00:42 # 시스템에부하를줄수있는문장을수행후결과확인 > host cat $HOME/mylabs/dy_sql.sql declare v_cnt number ; v_start number default dbms_utility.get_time; begin for i in loop execute immediate 'select count(*) from dual where dummy=to_char(' i ')' into v_cnt ; end loop ; dbms_output.put_line(round((dbms_utility.get_time-v_start)/100, 2) 'seconds'); end ; /

16 # snapshot 을다시생성하고결과분석 > exec dbms_workload_repository.create_snapshot # AWR Report 생성 ( 각각의요청에알맞은값입력필요 ) -- 결과는교재와같이 EM 으로확인가능 Specify the Report Type ~~~~~~~~~~~~~~~~~~~~~~~ Would you like an HTML report, or a plain text report? Enter 'html' for an HTML report, or 'text' for plain text Defaults to 'html' Enter value for report_type: text Enter value for num_days: Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 14 Enter value for end_snap: 19 Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is awrrpt_1_14_19.txt. To use this name, press <return> to continue, otherwise enter an alternative. Enter value for report_name: awrrpt.txt > host vi awrrpt.txt # ADDM Report 생성 Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 14 Enter value for end_snap: 19 Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is addmrpt_1_14_19.txt. To use this name, press <return> to continue, otherwise enter an alternative. Enter value for report_name: addmrpt.txt > host vi addmrpt.txt

17 # EM 에서결과확인 HOME - Advisor Central 에서방금진행한결과선택후 View Result 클릭 각각의 Recommendations 확인하고필요한부분은 Implement 선택 # buffer busy waits 관련사항확인여러개의세션이동시에같은테이블에동시에 insert 를수행하는경우, 세그먼트영역이급속히확장되고, 이로인해다양한성능문제가유발된다. buffer lock 경합으로인한 buffer busy waits 대기현상이그중하나이다. /* Segment Space Management : Manual */ > conn /as sysdba > startup force > conn system/oracle > create tablespace tbs_m datafile '$ORACLE_HOME/dbs/tbs_m.dbf' size 50m autoextend on segment space management manual ;

18 > create table test_m(id char(1000)) tablespace tbs_m ; > create or replace procedure do_insert is begin for i in loop insert into test_m values(i); commit ; end loop ; end ; / # buffer busy waits 정보확인 > host cat $HOME/mylabs/system_event.sql select event, total_waits, time_waited from v$system_event where event = 'buffer busy waits' or event like 'enq: HW%' ; no rows selected > host cat $HOME/mylabs/session_event.sql select event, total_waits, time_waited from v$session_event where sid = (select sid from v$mystat where rownum = 1 ) and (event='buffer busy waits' or event like 'enq: HW%') order by 3 desc ; no rows selected

19 # 동시작업생성후실행중인 Job 확인 > host cat $HOME/mylabs/submit_job.sql var job_no number ; begin for idx in loop dbms_job.submit(:job_no,'do_insert;',sysdate+1/86400,'sysdate+1/86400') ; commit ; end loop ; end; / > host cat $HOME/mylabs/dba_jobs.sql select job, log_user, next_date, next_sec, broken, what from dba_jobs where log_user = USER order by 1; 현재실행중인 JOB 이 10개가넘어가는지확인하고안넘어간다면좀더기다렸다가다음진행 (10개의행이보일때까지반복실행 ) > / # 현재 session 에서의 Wait Event 확인을위해 do_insert 실행 > exec do_insert # 작업제거 > host cat $HOME/mylabs/remove_job.sql begin for x in ( select job from dba_jobs where log_user = USER ) loop dbms_job.remove(x.job) ; end loop ; end; /

20 # 결과확인 EVENT TOTAL_WAITS TIME_WAITED buffer busy waits 217, ,853 enq: HW - contention 35,926 56,318 EVENT TOTAL_WAITS TIME_WAITED buffer busy waits 11,079 7,921 enq: HW - contention 1,924 4,205 # 작업초기화후 Segment Space Management Auto 확인 > drop procedure do_insert ; > drop table test_m purge ; > drop tablespace tbs_m including contents and datafiles ; > conn /as sysdba > startup force /* Segment Space Management : Auto */ > conn system/oracle > create tablespace tbs_a datafile '$ORACLE_HOME/dbs/tbs_a.dbf' size 50m autoextend on segment space management auto ; > create table test_a(id char(1000)) tablespace tbs_a ; > create or replace procedure do_insert is begin for i in loop insert into test_a values(i); commit ; end loop ; end ; /

21 # 현재 Event 정보확인 no rows selected no rows selected # 동시작업생성후실행중인 Job 확인 # 현재 session 에서의 Wait Event 확인을위해 do_insert 실행 > exec do_insert # 작업제거 # 결과확인 EVENT TOTAL_WAITS TIME_WAITED buffer busy waits 15,658 13,375 enq: HW - contention ,779 EVENT TOTAL_WAITS TIME_WAITED enq: HW - contention 65 1,780 buffer busy waits # 작업초기화후 Segment Space Management Auto 확인 > drop procedure do_insert ; > drop table test_a purge ; > drop tablespace tbs_a including contents and datafiles ; > conn /as sysdba > startup force

22 /* Segment Space Management : Auto with Partitioned Table */ > conn system/oracle > create tablespace tbs_p datafile '$ORACLE_HOME/dbs/tbs_p.dbf' size 50m autoextend on segment space management auto ; > create table test_p(id char(1000)) tablespace tbs_p partition by hash (id) partitions 8 ; > create or replace procedure do_insert is begin for i in loop insert into test_p values(i); commit ; end loop ; end ; / # 동시작업생성후실행중인 Job 확인 # 현재 session 에서의 Wait Event 확인을위해 do_insert 실행 > exec do_insert # 작업제거 # 결과확인 EVENT TOTAL_WAITS TIME_WAITED buffer busy waits 3,483 7,325 enq: HW - contention 2,334 49,254

23 EVENT TOTAL_WAITS TIME_WAITED enq: HW - contention 212 4,601 buffer busy waits > drop procedure do_insert ; > drop table test_p purge ; > drop tablespace tbs_p including contents and datafiles ; > exit # SQL Access Advisor 사용 sqlplus / as sysdba > grant advisor, query rewrite to sh ; # 시스템에서대량의작업을진행하는문장을찾았고각각의성능과관련된이슈사항들을확인 > conn sh/sh > set autotrace traceonly explain > host cat $HOME/mylabs/mview1.sql select s.prod_id, s.cust_id, sum(s.amount_sold) sum_amount from sales s, customers cs where s.cust_id = cs.cust_id and cs.country_id >= group by s.prod_id, s.cust_id ; Execution Plan Plan hash value: Id Operation Name Rows Bytes TempSpc Cost (%CPU) Time Pstart Pstop SELECT STATEMENT 359K 8423K 5977 (4) 00:01:12 1 HASH GROUP BY 359K 8423K 44M 5977 (4) 00:01:12 * 2 HASH JOIN 918K 21M 1000K 1965 (4) 00:00:24 * 3 TABLE ACCESS FULL CUSTOMERS K 332 (2) 00:00:04 4 PARTITION RANGE ALL 918K 12M 426 (9) 00:00: TABLE ACCESS FULL SALES 918K 12M 426 (9) 00:00:

24 > host cat $HOME/mylabs/mview2.sql select s.prod_id, sum(s.amount_sold) sum_amount from sales s, customers cs where s.cust_id = cs.cust_id and cs.country_id >= group by s.prod_id ; Execution Plan Plan hash value: Id Operation Name Rows Bytes TempSpc Cost (%CPU) Time Pstart Pstop SELECT STATEMENT (10) 00:00:25 1 HASH GROUP BY (10) 00:00:25 * 2 HASH JOIN 918K 21M 1000K 1965 (4) 00:00:24 * 3 TABLE ACCESS FULL CUSTOMERS K 332 (2) 00:00:04 4 PARTITION RANGE ALL 918K 12M 426 (9) 00:00: TABLE ACCESS FULL SALES 918K 12M 426 (9) 00:00: > set autotrace off # SQL Access Advisor 를이용하여 Mview를생성할수있는문장수집 >host cat $HOME/mylabs/create_mview.sql declare name varchar2(20) := 'tune_cust_mv' ; begin dbms_advisor.tune_mview(name, 'create materialized view cust_mv enable query rewrite as select s.prod_id, s.cust_id, sum(s.amount_sold) sum_amount from sales s, customers cs where s.cust_id = cs.cust_id and cs.country_id >= group by s.prod_id, s.cust_id') ; end; /

25 # 결과확인 > set long > select action_id, statement from user_tune_mview where task_name ='tune_cust_mv' order by action_id ; ACTION_ID STATEMENT CREATE MATERIALIZED VIEW LOG ON "SH"."SALES" WITH ROWID, SEQUENCE ("PROD_ID","CUST_ID","AMOUNT_SOLD") INCLUDING NEW VALUES 4 ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."SALES" ADD ROWID, SEQUENCE ("PROD_ID","CUST_ID","AMOUNT_SOLD") INCLUDING NEW VALUES 5 CREATE MATERIALIZED VIEW LOG ON "SH"."CUSTOMERS" WITH ROWID, SEQUENCE ("CUST_ID","COUNTRY_ID") INCLUDING NEW VALUES 6 ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."CUSTOMERS" ADD ROWID, SEQUENCE ("CUST_ID","COUNTRY_ID") INCLUDING NEW VALUES 7 CREATE MATERIALIZED VIEW SH.CUST_MV REFRESH FAST WITH ROWID ENABLE QUERY REWRITE AS SELECT SH.CUSTOMERS.COUNTRY_ID C1, SH.SALES.CUST_ID C2, SH.SALES.PROD_ID C3, SUM("SH"."SALES"."AMOUNT_SOLD") M1, COUNT("SH"."SALES"."AMOUNT_SOLD") M2, COUNT(*) M3 FROM SH.CUSTOMERS, SH.SALES WHERE SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID AND (SH.CUSTOMERS.COUNTRY_ID >= 52772) GROUP BY SH.CUSTOMERS.COUNTRY_ID, SH.SALES.CUST_ID, SH.SALES.PROD_ID 8 DROP MATERIALIZED VIEW SH.CUST_MV # 결과로나온문장들을잘편집하여그대로실행 > CREATE MATERIALIZED VIEW LOG ON "SH"."SALES" WITH ROWID, SEQUENCE ("PROD_ID","CUST_ID","AMOUNT_SOLD") INCLUDING NEW VALUES ; > ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."SALES" ADD ROWID, SEQUENCE ("PROD_ID","CUST_ID","AMOUNT_SOLD") INCLUDING NEW VALUES; > CREATE MATERIALIZED VIEW LOG ON "SH"."CUSTOMERS" WITH ROWID, SEQUENCE ("CUST_ID","COUNTRY_ID") INCLUDING NEW VALUES; > ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."CUSTOMERS" ADD ROWID, SEQUENCE ("CUST_ID","COUNTRY_ID") INCLUDING NEW VALUES;

26 > CREATE MATERIALIZED VIEW SH.CUST_MV REFRESH FAST WITH ROWID ENABLE QUERY REWRITE AS SELECT SH.CUSTOMERS.COUNTRY_ID C1, SH.SALES.CUST_ID C2, SH.SALES.PROD_ID C3, SUM("SH"."SALES"."AMOUNT_SOLD") M1, COUNT("SH"."SALES"."AMOUNT_SOLD") M2, COUNT(*) M3 FROM SH.CUSTOMERS, SH.SALES WHERE SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID GROUP BY SH.CUSTOMERS.COUNTRY_ID,SH.SALES.CUST_ID, SH.SALES.PROD_ID ; # 위에서실행했던 SQL 을다시실행하면서 Mview 의사용여부를확인 > set autotrace traceonly explain Execution Plan Plan hash value: Id Operation Name Rows Bytes Cost (%CPU) Time SELECT STATEMENT 285K 14M 292 (16) 00:00:04 1 HASH GROUP BY 285K 14M 292 (16) 00:00:04 * 2 MAT_VIEW REWRITE ACCESS FULL CUST_MV 285K 14M 258 (5) 00:00:04 Execution Plan Plan hash value: Id Operation Name Rows Bytes Cost (%CPU) Time SELECT STATEMENT 285K 10M 292 (16) 00:00:04 1 HASH GROUP BY 285K 10M 292 (16) 00:00:04 * 2 MAT_VIEW REWRITE ACCESS FULL CUST_MV 285K 10M 258 (5) 00:00:04 > exit

27 Chapter 10. Managing Schema Objects /* Partitioned Table 사용시 Index type 의중요성 (Global or Local) */ # 실습용테이블생성 (BIG_TABLE) sqlplus system/oracle > host cat $HOME/mylabs/create_big_tab.sql create table big_table nologging as select rownum id, a.* from all_objects a where 1=0 ; # Dummy Data 입력 (100만건) > host cat $HOME/mylabs/load_data_big_tab.sql declare l_cnt number ; l_rows number := ; begin insert /*+ append */ into big_table select rownum, a.* from all_objects a; l_cnt := sql%rowcount ; commit ; while (l_cnt < l_rows) loop insert /*+ append */ into big_table select rownum + l_cnt, owner, object_name, subobject_name, object_id, data_object_id, object_type, created, last_ddl_time, timestamp, status, temporary, generated, secondary from big_table where rownum <= l_rows-l_cnt ; l_cnt := l_cnt + sql%rowcount ; commit ; end loop ; end; > select count(*) from big_table;

28 /* Hash Partitioned Table 생성 */ > select object_id, count(*) from big_table group by object_id ; OBJECT_ID COUNT(*) rows selected. # object_id 를기준으로 Hash Partitioned Table 생성 ( 10개의 partition) > create table big_table_hashed nologging partition by hash(object_id) partitions 10 as select * from big_table ; # object_id 가 20이며 owner 가 'SPECIAL_ROW' 인행을각각하나씩입력 - 각각의테이블에는전체 Data 중에는 SPECIAL_ROW 의이름을갖는 행은하나만존재함 ( 즉, 하나의 Segment 에만존재하는값임 ) > host cat $HOME/mylabs/insert_row.sql insert into big_table(owner, object_name, object_id, last_ddl_time, created) select 'SPECIAL_ROW', object_name, 20, last_ddl_time, created from big_table where rownum = 1 ; insert into big_table_hashed(owner, object_name, object_id, last_ddl_time, created) select 'SPECIAL_ROW', object_name, 20, last_ddl_time, created from big_table_hashed where rownum = 1 ; commit ; # owner 컬럼을기준으로인덱스생성 > create index big_idx1 on big_table(owner) ; # Local index 를 owner 컬럼을기준으로생성 > create index big_hash_idx1 on big_table_hashed(owner) local ;

29 # 통계정보수집 > exec dbms_stats.gather_table_stats('system','big_table',cascade=>true) > exec dbms_stats.gather_table_stats('system','big_table_hashed', - cascade=>true) # 각각의실행계획비교 > explain plan for select * from big_table where owner = :x ; PLAN_TABLE_OUTPUT Plan hash value: Id Operation Name Rows Bytes Cost (%CPU) Time SELECT STATEMENT K 1508 (1) 00:00:19 1 TABLE ACCESS BY INDEX ROWID BIG_TABLE K 1508 (1) 00:00:19 * 2 INDEX RANGE SCAN BIG_IDX (2) 00:00:02 > explain plan for select * from big_table_hashed where owner = :x ; PLAN_TABLE_OUTPUT Plan hash value: Id Operation Name Rows Bytes Cost (%CPU) Time Pstart Pstop SELECT STATEMENT K 1795 (1) 00:00:22 1 PARTITION HASH ALL K 1795 (1) 00:00: TABLE ACCESS BY LOCAL INDEX ROWID BIG_TABLE_HASHED K 1795 (1) * 3 INDEX RANGE SCAN BIG_HASH_IDX (1) 00:00: # 둘의차이점은어떠한가? 전체 data 중에한개의행을찾아갈때 Partitioned index 는각각의 partition 영역을모두스캔하고있음을확인할수있다. 왜그런가? # 각각의테이블에서 SPECIAL_ROW 의이름을갖고있는행을각각 1000번씩검색하면서 SQL Trace를분석해본다. > conn system/oracle > alter session set events '10046 trace name context forever, level 12' ;

30 > host cat $HOME/mylabs/perf_test.sql declare tab_rec big_table%rowtype ; begin for i in loop select * into tab_rec from big_table where owner = 'SPECIAL_ROW' ; end loop ; end ; / declare tab_rec big_table_hashed%rowtype ; begin for i in loop select * into tab_rec from big_table_hashed where owner = 'SPECIAL_ROW' ; end loop ; end ; / > alter session set events '10046 trace name context off' ; # SQL Trace File의위치확인및분석 > host cat $HOME/mylabs/trace_tkprof.sql SELECT 'host tkprof ' d.value '/' lower(c.instance_name) '_ora_' b.spid '.trc' ' $HOME/output.txt sys=no' "User Trace File" FROM v$session a, v$process b, v$instance c, v$parameter d, ( select * from v$mystat where rownum = 1 ) e WHERE a.paddr = b.addr AND a.sid = e.sid AND d.name = 'user_dump_dest' User Trace File host tkprof /u01/app/oracle/admin/orcl/udump/orcl_ora_23008.trc $HOME/output.txt sys=no

31 # 위의실행결과를그대로복사해서실행 > host tkprof /u01/app/oracle/admin/orcl/udump/orcl_ora_23008.trc $HOME/output.txt sys=no > host vi $HOME/output.txt SELECT * FROM BIG_TABLE WHERE OWNER = 'SPECIAL_ROW' call count cpu elapsed disk query current rows Parse Execute Fetch total Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 5 (recursive depth: 1) Rows Row Source Operation TABLE ACCESS BY INDEX ROWID BIG_TABLE (cr=4000 pr=3 pw=0 time= us) 1000 INDEX RANGE SCAN BIG_IDX1 (cr=3000 pr=2 pw=0 time=82324 us)(object id 52632) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited Waited db file sequential read SELECT * FROM BIG_TABLE_HASHED WHERE OWNER = 'SPECIAL_ROW' call count cpu elapsed disk query current rows Parse Execute Fetch total Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 5 (recursive depth: 1) Rows Row Source Operation PARTITION HASH ALL PARTITION: 1 10 (cr=21000 pr=1 pw=0 time= us) 1000 TABLE ACCESS BY LOCAL INDEX ROWID BIG_TABLE_HASHED PARTITION: 1 10 (cr=21000 pr=1 pw=0 time= us) 1000 INDEX RANGE SCAN BIG_HASH_IDX1 PARTITION: 1 10 (cr=20000 pr=0 pw=0 time= us)(object id 52633) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited Waited db file sequential read # Local Index 는 Table 과동일한 Partition Key / Partition type으로분할되어있다. 때문에각각의 B-Tree 구조의인덱스세그먼트가독립적으로존재하는것과마찬가지이다. 위와같은경우 partition key 에대한조건식이함께들어오지않는다면 owner 의조건에만족하는행을찾기위해모든 partition 영역을검색해야한다. 이는 Partition 되어있는테이블에서극히소량의 Data 를찾을때성능상악영향을미칠수있다.

32 # Global Index 생성후확인 > conn system/oracle > drop index big_hash_idx1 ; > create index big_hash_idx1 on big_table_hashed(owner) global partition by range (owner) ( partition p1 values less than ('F'), partition p2 values less than ('M'), partition p3 values less than ('T'), partition p4 values less than (MAXVALUE) ); - 새로만들어지는 Global Index 는 owner 컬럼을기준으로 Partitioning 을하고있다. 이는 SPECIAL_ROW 의이름은갖는행은하나의 partition 영역인 p3 에만들어갈수있음을의미한다. > exec dbms_stats.gather_table_stats('system','big_table_hashed', - cascade=>true) # 실행계획분석후위의결과와차이점분석 > explain plan for select * from big_table_hashed where owner = :x ; PLAN_TABLE_OUTPUT Plan hash value: Id Operation Name Rows Bytes Cost (%CPU) Time Pstart Pstop SELECT STATEMENT K 1904(1) 00:00:23 1 PARTITION RANGE SINGLE K 1904(1) 00:00:23 KEY KEY 2 TABLE ACCESS BY GLOBAL INDEX ROWID BIG_TABLE_HASHED K 1904(1) 00:00:23 ROWID ROWID * 3 INDEX RANGE SCAN BIG_HASH_IDX (2) 00:00:02 KEY KEY # 모든 Partition 영역을검색하는가? Partition key로사용중인 owner 컬럼의값에따라특정 Partition 영역만작업한다. # 실제실행된상태의내용도다시한번확인 > conn system/oracle > alter session set events '10046 trace name context forever, level 12' ; > alter session set events '10046 trace name context off' ;

33 # SQL Trace File의위치확인및분석 User Trace File host tkprof /u01/app/oracle/admin/orcl/udump/orcl_ora_23699.trc $HOME/output.txt sys=no # 위의실행결과를그대로복사해서실행 > host tkprof /u01/app/oracle/admin/orcl/udump/orcl_ora_23699.trc $HOME/output.txt sys=no > host vi $HOME/output.txt SELECT * FROM BIG_TABLE_HASHED WHERE OWNER = 'SPECIAL_ROW' call count cpu elapsed disk query current rows Parse Execute Fetch total Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 5 (recursive depth: 1) Rows Row Source Operation PARTITION RANGE SINGLE PARTITION: 3 3 (cr=4000 pr=3 pw=0 time= us) 1000 TABLE ACCESS BY GLOBAL INDEX ROWID BIG_TABLE_HASHED PARTITION: ROW LOCATION ROW LOCATION (cr=4000 pr=3 pw=0 time= us) 1000 INDEX RANGE SCAN BIG_HASH_IDX1 PARTITION: 3 3 (cr=3000 pr=2 pw=0 time=80809 us) (object id 52649) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited Waited db file sequential read # 결과어떠한가? > drop table big_table purge ; > drop table big_table_hashed purge ;

34 Chapter 11. Managing Storage # Block 공간정보확인 sqlplus / as sysdba > host cat $HOME/mylabs/show_space.sql > select tablespace_name, segment_space_management from dba_tablespaces ; TABLESPACE_NAME SEGMEN SYSTEM MANUAL UNDOTBS1 MANUAL SYSAUX AUTO TEMP MANUAL USERS AUTO EXAMPLE AUTO > create table test tablespace users as select * from scott.emp ; > select segment_name, file_id, extent_id, block_id, blocks from dba_extents where segment_name = 'TEST' and owner = USER ; SEGMENT_NAME FILE_ID EXTENT_ID BLOCK_ID BLOCKS TEST > save 1 > exec show_space('test') Unformatted Blocks... 0 FS1 Blocks (0-25)... 0 FS2 Blocks (25-50)... 0 FS3 Blocks (50-75)... 0 FS4 Blocks (75-100)... 0 Full Blocks... 1 Total Blocks... 8 Total Bytes... 65,536 Total MBytes... 0 Unused Blocks... 4 Unused Bytes... 32,768 Last Used Ext FileId... 4 Last Used Ext BlockId... 1,681 Last Used Block... 4

35 > begin for i in loop insert into test select * from test ; end loop ; commit ; end; / > delete test where deptno = 10 ; > commit ; > alter table test allocate extent ; SEGMENT_NAME FILE_ID EXTENT_ID BLOCK_ID BLOCKS TEST TEST TEST TEST TEST TEST TEST TEST TEST TEST TEST TEST TEST > exec show_space('test') Unformatted Blocks... 0 FS1 Blocks (0-25)... 0 FS2 Blocks (25-50) FS3 Blocks (50-75)... 0 FS4 Blocks (75-100)... 3 Full Blocks... 1 Total Blocks Total Bytes ,968 Total MBytes... 0 Unused Blocks... 8 Unused Bytes... 65,536 Last Used Ext FileId... 4 Last Used Ext BlockId... 1,769 Last Used Block... 8 > drop table test purge ;

36 # Resumable Space Allocation > sqlplus /as sysdba > host cat $HOME/mylabs/print_table.sql > create tablespace userdata datafile '/u01/app/oracle/oradata/orcl/userdata.dbf' size 1m autoextend off ; > create user test identified by test default tablespace userdata ; > grant connect, resource to test ; # Resumable 권한부여 > grant resumable to test ; # test 유저로접속 > conn test/test > create table t1 (id char(1000)) ; # 대량의 Data Loading 중에공간이부족하면 error 발생 > begin for i in loop insert into t1 values ( i ) ; end loop ; end; / ERROR at line 1: ORA-01653: unable to extend table TEST.T1 by 8 in tablespace USERDATA ORA-06512: at line 3 > save load replace # Resumable Space Allocation 활성화 > alter session enable resumable ; # 대량의 Data Loading 중에공간이부족해도 error 발생안함 ( 대기중 )

37 # 새로운터미널을열고실습진행 tail -60 $ORACLE_BASE/admin/orcl/bdump/alert_orcl.log Sat Apr 11 04:32: statement in resumable session 'User TEST(62), Session 152, Instance 1' was suspended due to ORA-01653: unable to extend table TEST.T1 by 8 in tablespace USERDATA # Dictionary View를통해서확인 sqlplus / as sysdba > exec print_table('select user_id, session_id, status,start_time, - suspend_time, sql_text, error_number, error_msg - from dba_resumable' ) ; USER_ID : 62 SESSION_ID : 152 STATUS : SUSPENDED START_TIME : 04/11/09 04:32:47 SUSPEND_TIME : 04/11/09 04:32:50 SQL_TEXT : INSERT INTO T1 VALUES (:B1) ERROR_NUMBER : 1653 ERROR_MS : ORA-01653: unable to extend table TEST.T1 by 8 in tablespace USERDATA # Tablespace 의공간부족해결 > alter database datafile '/u01/app/oracle/oradata/orcl/userdata.dbf' autoextend on ; # 대기중이던 test 유저의터미널을보면작업이재개되어있다. # 실습정리 > drop user test cascade ; > drop tablespace userdata including contents and datafiles ; > exit

38 # Transportable Tablespace sqlplus / as sysdba # 플랫폼의엔디언형식확인 > select platform_name from v$database ; > select * from v$transportable_platform ; # 실습준비 > create tablespace oltp datafile '/u01/app/oracle/oradata/orcl/oltp.dbf' size 10m ; > create user oltp identified by oltp default tablespace oltp ; > grant dba to oltp ; > conn oltp/oltp > create table dept as select * from scott.dept ; > alter table dept add primary key(deptno) using index tablespace users ; > create table emp as select * from scott.emp ; > alter table emp add foreign key(deptno) references dept(deptno) ; # Transport 하기전에검사 > conn /as sysdba > exec dbms_tts.transport_set_check('oltp',true) > select * from transport_set_violations ; VIOLATIONS Index OLTP.SYS_C in tablespace USERS enforces primary constriants of table OLTP.DEPT in tablespace OLTP # 참조무결성을위한인덱스가있다면 oltp tablespace 로이동 > alter index oltp.sys_c rebuild tablespace oltp ; > alter tablespace oltp read only ; > exit

39 # Datapump 를이용하여필요한 Metadata 수집 expdp system/oracle transport_tablespaces =OLTP dumpfile=tts.dmp Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is: /u01/app/oracle/admin/orcl/dpdump/tts.dmp Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 05:08:31 # 필요한파일들을원하는경로로이동 cp /u01/app/oracle/oradata/orcl/oltp.dbf /u01/app/oracle/oradata/catdb/oltp.dbf cp /u01/app/oracle/admin/orcl/dpdump/tts.dmp $ORACLE_HOME/rdbms/log/ # 필요한유저생성 sqlplus sys/oracle@catdb as sysdba > create user oltp identified by oltp ; > grant connect, resource to oltp; > exit # Datapump 를이용하여 Target DB에정보입력 export ORACLE_SID=catdb impdp system/oracle dumpfile=tts.dmp \ transport_datafiles=/u01/app/oracle/oradata/catdb/oltp.dbf # 결과확인 sqlplus sys/oracle@catdb as sysdba > alter tablespace oltp read write ; > select * from oltp.emp ; > conn sys/oracle@orcl as sysdba > alter tablespace oltp read write ; > drop tablespace oltp including contents and datafiles ; > drop user oltp cascade ; > exit

40 Chapter 15. Database Security # Virtual Private Database 구성 # context 생성권한부여 sqlplus / as sysdba # context 에필요한응용프로그램의속성등을저장하는 package 생성 > host cat $HOME/mylabs/create_pkg.sql CREATE OR REPLACE PACKAGE system.app_pkg IS PROCEDURE show_app_context ; PROCEDURE set_app_context ; FUNCTION the_predicate (schema_in VARCHAR2, name_in VARCHAR2) RETURN VARCHAR2 ; END app_pkg ; / CREATE OR REPLACE PACKAGE BODY system.app_pkg IS c_context VARCHAR2(30) := 'HR_INFO' ; c_dept_attrib VARCHAR2(30) := 'DEPT_ATTRIB' ; c_title_attrib VARCHAR2(30) := 'TITLE_ATTRIB' ; c_country_attrib VARCHAR2(30) := 'COUNTRY_ATTRIB' ; c_dept_val VARCHAR2(30) := 'Finance' ; c_title_val VARCHAR2(30) := 'FI_MGR' ; c_country_val VARCHAR2(4) := 'US' ; PROCEDURE show_app_context IS BEGIN DBMS_OUTPUT.PUT_LINE('Type: ' c_dept_attrib ' - ' SYS_CONTEXT(c_context, c_dept_attrib)); DBMS_OUTPUT.PUT_LINE('Type: ' c_title_attrib ' - ' SYS_CONTEXT(c_context, c_title_attrib)); DBMS_OUTPUT.PUT_LINE('Type: ' c_country_attrib ' - ' SYS_CONTEXT(c_context, c_country_attrib)); END show_app_context ; PROCEDURE set_app_context IS BEGIN DBMS_SESSION.SET_CONTEXT(c_context, c_dept_attrib, c_dept_val) ; DBMS_SESSION.SET_CONTEXT(c_context, c_title_attrib, c_title_val) ; DBMS_SESSION.SET_CONTEXT(c_context, c_country_attrib, c_country_val) ; END set_app_context ; FUNCTION the_predicate (schema_in VARCHAR2, name_in VARCHAR2) RETURN VARCHAR2 IS l_context VARCHAR2(100) := SYS_CONTEXT(c_context, c_dept_attrib) ; v_return_val VARCHAR2(2000); BEGIN

41 IF user = 'HR' and l_context = 'Finance' THEN v_return_val := 'department_id IN ( SELECT department_id FROM hr.departments WHERE department_name = SYS_CONTEXT(''' c_context ''',''' c_dept_attrib '''))'; RETURN v_return_val ; ELSE RETURN NULL; END IF; END the_predicate ; END app_pkg; / # system 계정에생성되는 Package 이며 3가지의 Subprogram 이있다. show_app_context : 현재 context 에등록된정보확인 set_app_context : context 에속성정의 the_predicate : VPD 정책을구현하는함수 > grant execute on system.app_pkg to hr; # FGAC 정책구현 > create or replace context hr_info using system.app_pkg ; > host cat $HOME/mylabs/add_policy.sql BEGIN DBMS_RLS.ADD_POLICY ( 'HR', 'EMPLOYEES', 'HR_POLICY', 'SYSTEM', 'APP_PKG.THE_PREDICATE', 'SELECT, UPDATE, DELETE', FALSE, TRUE); END; /

42 # 모든로그인작업후정보를 context에저장할수있도록트리거구현 > create or replace trigger set_id_on_logon after logon on database begin system.app_pkg.set_app_context ; end ; / # HR 유저로로그인후결과확인 > conn hr/hr > set serveroutput on > exec system.app_pkg.show_app_context Type: DEPT_ATTRIB - Finance Type: TITLE_ATTRIB - FI_MGR Type: COUNTRY_ATTRIB - US > select employee_id, first_name, last_name, salary, department_id from hr.employees ; EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY DEPARTMENT_ID Nancy Greenberg Daniel Faviet John Chen Ismael Sciarra Jose Manuel Urman Luis Popp # system 유저로로그인하여확인. 위와동일한결과가나오는가? > conn system/oracle > set serveroutput on > exec system.app_pkg.show_app_context Type: DEPT_ATTRIB - Finance Type: TITLE_ATTRIB - FI_MGR Type: COUNTRY_ATTRIB - US

43 > select employee_id, first_name, last_name, salary, department_id from hr.employees ; 191 Randall Perkins Sarah Bell Britney Everett Samuel McCain Vance Jones Alana Walsh Kevin Feeney rows selected. # 실습정리 > conn /as sysdba > begin dbms_rls.drop_policy( 'HR', 'EMPLOYEES', 'HR_POLICY') ; end; / > drop trigger set_id_on_logon ; > drop package system.app_pkg ; > drop context hr_info ;

I T C o t e n s P r o v i d e r h t t p : / / w w w. h a n b i t b o o k. c o. k r

I T C o t e n s P r o v i d e r h t t p : / / w w w. h a n b i t b o o k. c o. k r I T C o t e n s P r o v i d e r h t t p : / / w w w. h a n b i t b o o k. c o. k r -------------------------------------------------------------------- -- 1. : ts_cre_bonsa.sql -- 2. :

More information

歯sql_tuning2

歯sql_tuning2 SQL Tuning (2) SQL SQL SQL Tuning ROW(1) ROW(2) ROW(n) update ROW(2) at time 1 & Uncommitted update ROW(2) at time 2 SQLDBA> @ UTLLOCKT WAITING_SESSION TYPE MODE_REQUESTED MODE_HELD LOCK_ID1

More information

ORANGE FOR ORACLE V4.0 INSTALLATION GUIDE (Online Upgrade) ORANGE CONFIGURATION ADMIN O

ORANGE FOR ORACLE V4.0 INSTALLATION GUIDE (Online Upgrade) ORANGE CONFIGURATION ADMIN O Orange for ORACLE V4.0 Installation Guide ORANGE FOR ORACLE V4.0 INSTALLATION GUIDE...1 1....2 1.1...2 1.2...2 1.2.1...2 1.2.2 (Online Upgrade)...11 1.3 ORANGE CONFIGURATION ADMIN...12 1.3.1 Orange Configuration

More information

목 차

목      차 Oracle 9i Admim 1. Oracle RDBMS 1.1 (System Global Area:SGA) 1.1.1 (Shared Pool) 1.1.2 (Database Buffer Cache) 1.1.3 (Redo Log Buffer) 1.1.4 Java Pool Large Pool 1.2 Program Global Area (PGA) 1.3 Oracle

More information

Oracle Database 10g: Self-Managing Database DB TSC

Oracle Database 10g: Self-Managing Database DB TSC Oracle Database 10g: Self-Managing Database DB TSC Agenda Overview System Resource Application & SQL Storage Space Backup & Recovery ½ Cost ? 6% 12 % 6% 6% 55% : IOUG 2001 DBA Survey ? 6% & 12 % 6% 6%

More information

Jerry Held

Jerry Held DB / TSC Oracle Database 10g (Self-Managing Database) (Common Infrastructure) (Automatic Workload Repository) (Server-generated Alerts) (Automated Maintenance Tasks) (Advisory Framework) (ADDM) (Self-Managing

More information

목차 BUG offline replicator 에서유효하지않은로그를읽을경우비정상종료할수있다... 3 BUG 각 partition 이서로다른 tablespace 를가지고, column type 이 CLOB 이며, 해당 table 을 truncate

목차 BUG offline replicator 에서유효하지않은로그를읽을경우비정상종료할수있다... 3 BUG 각 partition 이서로다른 tablespace 를가지고, column type 이 CLOB 이며, 해당 table 을 truncate ALTIBASE HDB 6.1.1.5.6 Patch Notes 목차 BUG-39240 offline replicator 에서유효하지않은로그를읽을경우비정상종료할수있다... 3 BUG-41443 각 partition 이서로다른 tablespace 를가지고, column type 이 CLOB 이며, 해당 table 을 truncate 한뒤, hash partition

More information

13주-14주proc.PDF

13주-14주proc.PDF 12 : Pro*C/C++ 1 2 Embeded SQL 3 PRO *C 31 C/C++ PRO *C NOT! NOT AND && AND OR OR EQUAL == = SQL,,, Embeded SQL SQL 32 Pro*C C SQL Pro*C C, C Pro*C, C C 321, C char : char[n] : n int, short, long : float

More information

The Self-Managing Database : Automatic Health Monitoring and Alerting

The Self-Managing Database : Automatic Health Monitoring and Alerting The Self-Managing Database : Automatic Health Monitoring and Alerting Agenda Oracle 10g Enterpirse Manager Oracle 10g 3 rd Party PL/SQL API Summary (Self-Managing Database) ? 6% 6% 12% 55% 6% Source: IOUG

More information

MS-SQL SERVER 대비 기능

MS-SQL SERVER 대비 기능 Business! ORACLE MS - SQL ORACLE MS - SQL Clustering A-Z A-F G-L M-R S-Z T-Z Microsoft EE : Works for benchmarks only CREATE VIEW Customers AS SELECT * FROM Server1.TableOwner.Customers_33 UNION ALL SELECT

More information

세미나(장애와복구-수강생용).ppt

세미나(장애와복구-수강생용).ppt DB PLAN Consultant jina6678@yahoo.co.kr 011-864-1858 - - 1. 2. DB 3. - 4. - 5. 6. 1 INSTANCE MMAN RECO RFS MRP ORBn RBAL MMON Dnnn Snnn Data Buffer Cache SGA Stream Pool Shared pool Large Pool PGA Log

More information

Tablespace On-Offline 테이블스페이스 온라인/오프라인

Tablespace On-Offline 테이블스페이스 온라인/오프라인 2018/11/10 12:06 1/2 Tablespace On-Offline 테이블스페이스온라인 / 오프라인 목차 Tablespace On-Offline 테이블스페이스온라인 / 오프라인... 1 일반테이블스페이스 (TABLESPACE)... 1 일반테이블스페이스생성하기... 1 테이블스페이스조회하기... 1 테이블스페이스에데이터파일 (DATA FILE) 추가

More information

FlashBackt.ppt

FlashBackt.ppt 1. Flashback 목적 Flashback 이란? 사용자실수에의한손상된데이터를 Database 의크기와상관없이복구를할수있는기능이다. 이 Flashback 기능은일반적인복구에서우려되는데이터베이스의크기를걱정하지않아도된다. 보통의사용자실수는커다란시스템장애가수반되며, 이를복구하기위해서는많은자원과시간이필요하다. 하지만 9i 에서지원되느 flashback query

More information

슬라이드 1

슬라이드 1 사례를통해본 RMAN (RMAN Case Study) 2013. 02. Seungtaek Lee( 放浪 A) RMAN 백업 Server-Managed Backup(RMAN) Components Target : 백업대상 Catalog : 백업정보가저장되는 RMAN Repository Auxiliary : 백업또는 Target 를 Restore하여여러가지용도로사용하는

More information

62

62 2 instance database physical storage 2 1 62 63 tablespace datafiles 2 2 64 1 2 logical view control files datafiles redo log files 65 2 3 9i OMF Oracle Managed Files, OMF 9i 9i / / OMF 9i 66 8 1MB 8 10MB

More information

ePapyrus PDF Document

ePapyrus PDF Document Goodus 기술노트 [38 회 ] Author 윤병길, 이은정 Creation Date 2009-02-27 Last Updated Version 1.0 Copyright(C) 2004 Goodus Inc. All Rights Reserved Version 변경일자변경자 ( 작성자 ) 주요내용 1 2009-02-27 윤병길, 이은정문서최초작성 Contents

More information

오라클 데이터베이스 10g 핵심 요약 노트

오라클 데이터베이스 10g 핵심 요약 노트 1 10g 10g SYSAUX 10g 22 Oracle Database 10g, 10g. 10g. (Grid), 10g.. 10g SYSAUX (ASM, Automatic Storage Management) 10g 10g. g. (DBA).,., 1).,..? 10g,.. (Larry Ellison).. (Leverage Components), (ASM) (

More information

목차 BUG 문법에맞지않는질의문수행시, 에러메시지에질의문의일부만보여주는문제를수정합니다... 3 BUG ROUND, TRUNC 함수에서 DATE 포맷 IW 를추가지원합니다... 5 BUG ROLLUP/CUBE 절을포함하는질의는 SUBQUE

목차 BUG 문법에맞지않는질의문수행시, 에러메시지에질의문의일부만보여주는문제를수정합니다... 3 BUG ROUND, TRUNC 함수에서 DATE 포맷 IW 를추가지원합니다... 5 BUG ROLLUP/CUBE 절을포함하는질의는 SUBQUE ALTIBASE HDB 6.3.1.10.1 Patch Notes 목차 BUG-45710 문법에맞지않는질의문수행시, 에러메시지에질의문의일부만보여주는문제를수정합니다... 3 BUG-45730 ROUND, TRUNC 함수에서 DATE 포맷 IW 를추가지원합니다... 5 BUG-45760 ROLLUP/CUBE 절을포함하는질의는 SUBQUERY REMOVAL 변환을수행하지않도록수정합니다....

More information

Tina Admin

Tina Admin 유니원아이앤씨 DB 기술지원팀 2015 년 09 월 09 일 문서정보 프로젝트명 TTS (Transportable Tablespace) 서브시스템명 버전 1.0 문서명 TTS (Transportable Tablespace) 작성일 2015-08-16 작성자 김성한 최종수정일 2015-09-09 문서번호 UNIONE-201509091051-KSH 재개정이력 일자내용수정인버전

More information

PRO1_09E [읽기 전용]

PRO1_09E [읽기 전용] Siemens AG 1999 All rights reserved File: PRO1_09E1 Information and - ( ) 2 3 4 5 Monitor/Modify Variables" 6 7 8 9 10 11 CPU 12 Stop 13 (Forcing) 14 (1) 15 (2) 16 : 17 : Stop 18 : 19 : (Forcing) 20 :

More information

Simplify your Job Automatic Storage Management DB TSC

Simplify your Job Automatic Storage Management DB TSC Simplify your Job Automatic Storage Management DB TSC 1. DBA Challenges 2. ASM Disk group 3. Mirroring/Striping/Rebalancing 4. Traditional vs. ASM 5. ASM administration 6. ASM Summary Capacity in Terabytes

More information

PowerPoint Presentation

PowerPoint Presentation Server I/O utilization System I/O utilization V$FILESTAT V$DATAFILE Data files Statspack Performance tools TABLESPACE FILE_NAME PHYRDS PHYBLKRD READTIM PHYWRTS PHYBLKWRT WRITETIM ------------- -----------------------

More information

PowerPoint 프레젠테이션

PowerPoint 프레젠테이션 Reasons for Poor Performance Programs 60% Design 20% System 2.5% Database 17.5% Source: ORACLE Performance Tuning 1 SMS TOOL DBA Monitoring TOOL Administration TOOL Performance Insight Backup SQL TUNING

More information

<4D F736F F D205BB4EBBBF3C1A4BAB8B1E2BCFA5DB1E2BCFAB9AEBCAD2D524D414EBBE7BFEBBFB9C1A65F39695F313067>

<4D F736F F D205BB4EBBBF3C1A4BAB8B1E2BCFA5DB1E2BCFAB9AEBCAD2D524D414EBBE7BFEBBFB9C1A65F39695F313067> 9i 에서의 RMAN 사용법예제 Author : 여현승 Creation Date : 2009-04-15 Last Updated : Latest Version : 1.0 Updated by Updated date Version < YYYY-MM-DD>

More information

Microsoft Word - RMAN 스터디자료_공개용_ doc

Microsoft Word - RMAN 스터디자료_공개용_ doc RMAN 의특징및기능에대한내부기술자료 발표일 : 2004 년 9 월 3 일 작성자 : LG 카드중형서버운영파트민연홍 작성일 : 2004 년 9 월 3 일 업데이트 : 2006 년 2 월 23 일 목 차 1. rman 의특징... 2 2. rman catalog... 2 3. 컨트롤파일을사용한 rman repository... 2 4. CHANNEL 할당...

More information

Jerry Held

Jerry Held ,, - - - : DELETE : ROW (ROWID) row ROWID : I/O Full Table Scan I/O Index Scan ROWID I/O Fast Full Index Scan scan scan scan I/O scan scan Unique, nonunique. (Concatenated Index) B* Tree Bitmap Reverse

More information

Microsoft Word - 10g RAC on Win2k.doc

Microsoft Word - 10g RAC on Win2k.doc 10g RAC on Win2K Document Control Date Author Change References 2006-03-30 신종근 초기작성함 1-1 ** Agenda 1. 작업목적 Down-Time 최소화!! 2. Pre-Install 환경 3. CRS Install 4. DBMS S/W Install 5. 9i 10g Upgrade 6. 문제점및주의사항

More information

K7VT2_QIG_v3

K7VT2_QIG_v3 1......... 2 3..\ 4 5 [R] : Enter Raid setup utility 6 Press[A]keytocreateRAID RAID Type: JBOD RAID 0 RAID 1: 2 7 " RAID 0 Auto Create Manual Create: 2 RAID 0 Block Size: 16K 32K

More information

SQL Tuning Business Development DB

SQL Tuning Business Development DB SQL Tuning Business Development DB Oracle Optimizer 4.1 Optimizer SQL SQL.. SQL Optimizer :.. Rule-Based Optimization (RBO), Cost-Based Optimization (CBO) SQL Optimizer SQL Query Parser Dictionary Rule-Based

More information

Contents Data Mart 1. 개요 실습방향 테스트위한사전설정 본격실습시작 ) 데이터파일 dd 명령어로 백업수행및유실시키기 ) 장애복구수행 결론...7 페이지 2 / 7

Contents Data Mart 1. 개요 실습방향 테스트위한사전설정 본격실습시작 ) 데이터파일 dd 명령어로 백업수행및유실시키기 ) 장애복구수행 결론...7 페이지 2 / 7 ( 참 ) 본상단부머리말에있는 Data Mart 는본문서작성자의블로그이름입니다 dd 명령어를 이용한백업수행 최소개념이해 본문서의 pdf 문서는다음 URL 참조 http://mindata.tistory.com/55 Version 변경일자 ( 작성일자 ) 변경자 ( 작성자 ) 주요내용 1 2013.4.3 김민기 최초작성 2 3 페이지 1 / 7 Contents

More information

Microsoft Word - [Unioneinc] 특정컬럼의 통계정보 갱신_ _ldh.doc

Microsoft Word - [Unioneinc] 특정컬럼의 통계정보 갱신_ _ldh.doc 특정 Column 통계정보갱신가이드 유니원아이앤씨 DB 사업부이대혁 2015 년 03 월 02 일 문서정보프로젝트명서브시스템명 버전 1.0 문서명 특정 Column 통계정보갱신가이드 작성일 2015-03-02 작성자 DB사업부이대혁사원 최종수정일 2015-03-02 문서번호 UNIONE-201503021500-LDH 재개정이력 일자내용수정인버전 문서배포이력

More information

,, - - - : DELETE : ROW (ROWID) row ROWID : I/O Full Table Scan scan I/O scan Index Scan ROWID scan I/O Fast Full Index Scan scan scan I/O Unique, nonunique. (Concatenated Index) B* Tree Bitmap Reverse

More information

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

Microsoft Word - SQL튜닝_실습교재_.doc * 실습환경 * 1. 오라클데이터베이스의튜닝실습을하기위해서는기본적인테이블과데이터가필요합니다. 다음과같은절차에의해환경설정을하십시오. 1) 강사가제공하는 Export 된파일 (scott.dmp) 을자신의 ORACLE 경로에저장하십시오. [C: ] cd C: ORACLE ORA92 BIN [C: ] dir scott.dmp scott.dmp 2) SYSTEM 사용자로접속하여

More information

Result Cache 동작원리및활용방안 엑셈컨설팅본부 /DB 컨설팅팀김철환 개요 ORACLE DBMS 를사용하는시스템에서 QUERY 성능은무엇보다중요한요소중하나이며그 성능과직접적인관련이있는것이 I/O 이다. 많은건수를 ACCESS 해야만원하는결과값을얻을수있는 QUER

Result Cache 동작원리및활용방안 엑셈컨설팅본부 /DB 컨설팅팀김철환 개요 ORACLE DBMS 를사용하는시스템에서 QUERY 성능은무엇보다중요한요소중하나이며그 성능과직접적인관련이있는것이 I/O 이다. 많은건수를 ACCESS 해야만원하는결과값을얻을수있는 QUER Result Cache 동작원리및활용방안 엑셈컨설팅본부 /DB 컨설팅팀김철환 개요 ORACLE DBMS 를사용하는시스템에서 QUERY 성능은무엇보다중요한요소중하나이며그 성능과직접적인관련이있는것이 I/O 이다. 많은건수를 ACCESS 해야만원하는결과값을얻을수있는 QUERY 을실행하게된다면 BLOCK I/O 가많이발생하게된다. 이런이유로 QUERY 의성능은좋지못할것이다.

More information

ETL_project_best_practice1.ppt

ETL_project_best_practice1.ppt ETL ETL Data,., Data Warehouse DataData Warehouse ETL tool/system: ETL, ETL Process Data Warehouse Platform Database, Access Method Data Source Data Operational Data Near Real-Time Data Modeling Refresh/Replication

More information

Microsoft Word - 기술노트[19회] Flashback.doc

Microsoft Word - 기술노트[19회] Flashback.doc Goodus 기술노트 [19 회 ] Flashback Author 권웅원, 나지혜 Creation Date 2007-04-25 Last Updated 2007-04-25 Version 1.0 Copyright(C) 2004 Goodus Inc. All Rights Reserved Version 변경일자 변경자 ( 작성자 ) 주요내용 1 2007-04-25 권웅원,

More information

ALTIBASE HDB Patch Notes

ALTIBASE HDB Patch Notes ALTIBASE HDB 6.5.1.5.6 Patch Notes 목차 BUG-45643 암호화컬럼의경우, 이중화환경에서 DDL 수행시 Replication HandShake 가실패하는문제가있어수정하였습니다... 4 BUG-45652 이중화에서 Active Server 와 Standby Server 의 List Partition 테이블의범위조건이다른경우에 Handshake

More information

Remote UI Guide

Remote UI Guide Remote UI KOR Remote UI Remote UI PDF Adobe Reader/Adobe Acrobat Reader. Adobe Reader/Adobe Acrobat Reader Adobe Systems Incorporated.. Canon. Remote UI GIF Adobe Systems Incorporated Photoshop. ..........................................................

More information

Bind Peeking 한계에따른 Adaptive Cursor Sharing 등장 엑셈컨설팅본부 /DB 컨설팅팀김철환 Bind Peeking 의한계 SQL 이최초실행되면 3 단계의과정을거치게되는데 Parsing 단계를거쳐 Execute 하고 Fetch 의과정을통해데이터

Bind Peeking 한계에따른 Adaptive Cursor Sharing 등장 엑셈컨설팅본부 /DB 컨설팅팀김철환 Bind Peeking 의한계 SQL 이최초실행되면 3 단계의과정을거치게되는데 Parsing 단계를거쳐 Execute 하고 Fetch 의과정을통해데이터 Bind Peeking 한계에따른 Adaptive Cursor Sharing 등장 엑셈컨설팅본부 /DB 컨설팅팀김철환 Bind Peeking 의한계 SQL 이최초실행되면 3 단계의과정을거치게되는데 Parsing 단계를거쳐 Execute 하고 Fetch 의과정을통해데이터를사용자에게전송하게되며 Parsing 단계에서실행계획이생성된다. Bind 변수를사용하는 SQL

More information

最即時的Sybase ASE Server資料庫診斷工具

最即時的Sybase ASE Server資料庫診斷工具 TOAD 9.5 Toad Oracle 料 SQL 料 行 理 SQLprofile Quest Software 了 Oracle -Toad Tools of Oracle Application Developers Toad 了 DBA DBA 理 易 度 Toad 料 SQL PL/SQL Toad Oracle PL/SQL Toad Schema Browser Schema Browser

More information

Microsoft PowerPoint - The overview of MView.ppt

Microsoft PowerPoint - The overview of MView.ppt The Overview of Materialized View Getting the most out of MetaLink 최창권 한국오라클제품지원실 안녕하십니까? 한국오라클에서주최하는 Technical iseminar Mview Overview 에참석해주신여러분께감사드립니다. 저는이번세미나를진행하게될한국오라클제품지원실에근무하는최창권입니다. 본 seminar 는

More information

8 장데이터베이스 8.1 기본개념 - 데이터베이스 : 데이터를조직적으로구조화한집합 (cf. 엑셀파일 ) - 테이블 : 데이터의기록형식 (cf. 엑셀시트의첫줄 ) - 필드 : 같은종류의데이터 (cf. 엑셀시트의각칸 ) - 레코드 : 데이터내용 (cf. 엑셀시트의한줄 )

8 장데이터베이스 8.1 기본개념 - 데이터베이스 : 데이터를조직적으로구조화한집합 (cf. 엑셀파일 ) - 테이블 : 데이터의기록형식 (cf. 엑셀시트의첫줄 ) - 필드 : 같은종류의데이터 (cf. 엑셀시트의각칸 ) - 레코드 : 데이터내용 (cf. 엑셀시트의한줄 ) 8 장데이터베이스 8.1 기본개념 - 데이터베이스 : 데이터를조직적으로구조화한집합 (cf. 엑셀파일 ) - 테이블 : 데이터의기록형식 (cf. 엑셀시트의첫줄 ) - 필드 : 같은종류의데이터 (cf. 엑셀시트의각칸 ) - 레코드 : 데이터내용 (cf. 엑셀시트의한줄 ) - DDL(Data Definition Language) : show, create, drop

More information

歯PLSQL10.PDF

歯PLSQL10.PDF 10 - SQL*Pl u s Pl / SQL - SQL*P lus 10-1 1 0.1 PL/ SQL SQL*Pl u s. SQL*P lus 10-2 1 0.2 S QL* Pl u s PL/ S QL SQL*Pl u s, Pl / SQL. - PL/ SQL (i npu t ), (s t or e ), (r un). - PL/ SQL s cr i pt,,. -

More information

untitled

untitled (shared) (integrated) (stored) (operational) (data) : (DBMS) :, (database) :DBMS File & Database - : - : ( : ) - : - : - :, - DB - - -DBMScatalog meta-data -DBMS -DBMS - -DBMS concurrency control E-R,

More information

Document Server Information Items Description Test Date 2011 / 05 / 31 CPU Intel(R) Xeon(R) CPU 2.40GHz X 8 Main Memory 1GB O/S version OEL 5.

Document Server Information Items Description Test Date 2011 / 05 / 31 CPU Intel(R) Xeon(R) CPU 2.40GHz X 8 Main Memory 1GB O/S version OEL 5. 11g 에서향상된 ASMCMD-CP 기능 (Oracle 11g R1 11.1.0.7) Author: Hyun-Ho, Jung Job: Oracle DBA Site: http://www.commit.co.kr Email: admin@commit.co.kr cleanto@naver.com Creation Date: 2011-05-31 Document Server

More information

10.ppt

10.ppt : SQL. SQL Plus. JDBC. SQL >> SQL create table : CREATE TABLE ( ( ), ( ),.. ) SQL >> SQL create table : id username dept birth email id username dept birth email CREATE TABLE member ( id NUMBER NOT NULL

More information

Advanced Product Service

Advanced Product Service Advanced Oracle Document CONVERT to ASM and Non-ASM Author: Hyun-Ho, Jung Site: http://www.commit.co.kr Email: admin@commit.co.kr cleanto@naver.com Creation Date: 2011-12 - 13 CONVERT to ASM and Non-ASM

More information

소개 TeraStation 을 구입해 주셔서 감사합니다! 이 사용 설명서는 TeraStation 구성 정보를 제공합니다. 제품은 계속 업데이트되므로, 이 설명서의 이미지 및 텍스트는 사용자가 보유 중인 TeraStation 에 표시 된 이미지 및 텍스트와 약간 다를 수

소개 TeraStation 을 구입해 주셔서 감사합니다! 이 사용 설명서는 TeraStation 구성 정보를 제공합니다. 제품은 계속 업데이트되므로, 이 설명서의 이미지 및 텍스트는 사용자가 보유 중인 TeraStation 에 표시 된 이미지 및 텍스트와 약간 다를 수 사용 설명서 TeraStation Pro II TS-HTGL/R5 패키지 내용물: 본체 (TeraStation) 이더넷 케이블 전원 케이블 TeraNavigator 설치 CD 사용 설명서 (이 설명서) 제품 보증서 www.buffalotech.com 소개 TeraStation 을 구입해 주셔서 감사합니다! 이 사용 설명서는 TeraStation 구성 정보를

More information

휠세미나3 ver0.4

휠세미나3 ver0.4 andromeda@sparcs:/$ ls -al dev/sda* brw-rw---- 1 root disk 8, 0 2014-06-09 18:43 dev/sda brw-rw---- 1 root disk 8, 1 2014-06-09 18:43 dev/sda1 brw-rw---- 1 root disk 8, 2 2014-06-09 18:43 dev/sda2 andromeda@sparcs:/$

More information

CD-RW_Advanced.PDF

CD-RW_Advanced.PDF HP CD-Writer Program User Guide - - Ver. 2.0 HP CD-RW Adaptec Easy CD Creator Copier, Direct CD. HP CD-RW,. Easy CD Creator 3.5C, Direct CD 3.0., HP. HP CD-RW TEAM ( 02-3270-0803 ) < > 1. CD...3 CD...5

More information

RDB개요.ppt

RDB개요.ppt 1 2 3 < > 1 SQL SQL 2 SQL 3 column DEPT DEPT# DNAME BUDGET D1 D2 D3 Marketing Development Research 10M 12M 5M tuple EMP EMP# ENAME DEPT# SALARY D1 40 D1 45 E1 E2 E3 Lopez Cheng Finzi D2 30 E4 Satio D2

More information

NoSQL

NoSQL MongoDB Daum Communications NoSQL Using Java Java VM, GC Low Scalability Using C Write speed Auto Sharding High Scalability Using Erlang Read/Update MapReduce R/U MR Cassandra Good Very Good MongoDB Good

More information

Commit_Wait / Commit_Logging 두파라미터를통해 Log File Sync 대기시간을감소시킬수있다는것은놀라움과의아함을동시에느낄수있다. 단지파라미터의수정을통해당연히대기해야하는시간을감축한다는것은분명성능을개선해야하는입장에서는놀라운일이될것이다. 반면, 그에따

Commit_Wait / Commit_Logging 두파라미터를통해 Log File Sync 대기시간을감소시킬수있다는것은놀라움과의아함을동시에느낄수있다. 단지파라미터의수정을통해당연히대기해야하는시간을감축한다는것은분명성능을개선해야하는입장에서는놀라운일이될것이다. 반면, 그에따 Commit Wait Class 대기시간감소방안 엑셈컨설팅본부 /DB 컨설팅팀박준연 개요 Wait Class 중 Commit 카테고리에해당하는 Wait Event 에의한대기현상으로 DB 시스템의성능저하현상이발생하는것은종종경험할수있다. 그중대표적인 Wait Event 는 Log File Sync 이다. 실제로대부분의 DB 시스템의 Top 5 Wait Event

More information

SQL Tuning Business Development DB SQL - -SQL -SQL

SQL Tuning Business Development DB SQL - -SQL -SQL 0:00-0:50 SQL :00-2:00 2:00-3:30 3:30-4:20 SQL 4:30-5:20 5:30-7:20 SQL Tuning Business Development DB SQL - -SQL -SQL SQL () H/W( ) CPU, Memory, Network ( ) SQL I/O ( ) SQL (2) ( ) ( ) SQL SQL SQL SQL

More information

PowerPoint Presentation

PowerPoint Presentation FORENSICINSIGHT SEMINAR SQLite Recovery zurum herosdfrc@google.co.kr Contents 1. SQLite! 2. SQLite 구조 3. 레코드의삭제 4. 삭제된영역추적 5. 레코드복원기법 forensicinsight.org Page 2 / 22 SQLite! - What is.. - and why? forensicinsight.org

More information

윈백및업그레이드 Tibero Flashback 가이드

윈백및업그레이드 Tibero Flashback 가이드 Tibero Flashback 가이드 2014. 05. 09. 목차 1. FLASHBACK 소개... 3 1.1. Flashback 개요... 3 1.2. Flashback 기능... 3 2. FLASHBACK 기능... 3 2.1. FLASHBACK QUERY... 3 2.1.1. FLASHBACK QUERY 개요... 3 2.1.2. FLASHBACK QUERY

More information

Microsoft PowerPoint - Linux_10gRAC_3Node

Microsoft PowerPoint - Linux_10gRAC_3Node Linux 환경에서 3 Node 10g RAC 구성및 Data 이관하기 일시 : 2006. 08. 10 Contents I. RAC 구성환경및전체순서 II. Volume 및 Server 환경 Setup III. CRS 및 DB S/W 설치 IV. CRS 및 DB S/W Patchset V. File-System DBF raw-device 이관 VI. Sinlge

More information

MySQL-Ch10

MySQL-Ch10 10 Chapter.,,.,, MySQL. MySQL mysqld MySQL.,. MySQL. MySQL....,.,..,,.,. UNIX, MySQL. mysqladm mysqlgrp. MySQL 608 MySQL(2/e) Chapter 10 MySQL. 10.1 (,, ). UNIX MySQL, /usr/local/mysql/var, /usr/local/mysql/data,

More information

목차 BUG DEQUEUE 의 WAIT TIME 이 1 초미만인경우, 설정한시간만큼대기하지않는문제가있습니다... 3 BUG [qp-select-pvo] group by 표현식에있는컬럼을참조하는집합연산이존재하지않으면결괏값오류가발생할수있습니다... 4

목차 BUG DEQUEUE 의 WAIT TIME 이 1 초미만인경우, 설정한시간만큼대기하지않는문제가있습니다... 3 BUG [qp-select-pvo] group by 표현식에있는컬럼을참조하는집합연산이존재하지않으면결괏값오류가발생할수있습니다... 4 ALTIBASE HDB 6.5.1.5.10 Patch Notes 목차 BUG-46183 DEQUEUE 의 WAIT TIME 이 1 초미만인경우, 설정한시간만큼대기하지않는문제가있습니다... 3 BUG-46249 [qp-select-pvo] group by 표현식에있는컬럼을참조하는집합연산이존재하지않으면결괏값오류가발생할수있습니다... 4 BUG-46266 [sm]

More information

Orcad Capture 9.x

Orcad Capture 9.x OrCAD Capture Workbook (Ver 10.xx) 0 Capture 1 2 3 Capture for window 4.opj ( OrCAD Project file) Design file Programe link file..dsn (OrCAD Design file) Design file..olb (OrCAD Library file) file..upd

More information

Contents 1. Oracle Recovery Manager(RMAN) RMAN이란? RMAN의특징 RMAN의 Channel과 Media Management RMAN CONFIGURE COMMAND.

Contents 1. Oracle Recovery Manager(RMAN) RMAN이란? RMAN의특징 RMAN의 Channel과 Media Management RMAN CONFIGURE COMMAND. Goodus 기술노트 [40 회 ] Recovery Manager(RMAN) Author Author Job Title 김상국 차장 Creation Date 2009-04-30 Last Updated 2009-04-30 Version 1.0 Copyright(C) 2004 Goodus Inc. All Rights Reserved Contents 1. Oracle

More information

PRO1_02E [읽기 전용]

PRO1_02E [읽기 전용] Siemens AG 1999 All rights reserved File: PRO1_02E1 Information and 2 STEP 7 3 4 5 6 STEP 7 7 / 8 9 10 S7 11 IS7 12 STEP 7 13 STEP 7 14 15 : 16 : S7 17 : S7 18 : CPU 19 1 OB1 FB21 I10 I11 Q40 Siemens AG

More information

6주차.key

6주차.key 6, Process concept A program in execution Program code PCB (process control block) Program counter, registers, etc. Stack Heap Data section => global variable Process in memory Process state New Running

More information

PowerPoint 프레젠테이션

PowerPoint 프레젠테이션 Spider For MySQL 실전사용기 피망플러스유닛최윤묵 Spider For MySQL Data Sharding By Spider Storage Engine http://spiderformysql.com/ 성능 8 만 / 분 X 4 대 32 만 / 분 많은 DB 중에왜 spider 를? Source: 클라우드컴퓨팅구 선택의기로 Consistency RDBMS

More information

ORACLE EXADATA HCC 압축방식이해하기 엑셈컨설팅본부 /DB 컨설팅팀김철환 개요 시간이지나면서데이터는급속하게증가하고있다. 데이터가증가함에따라 DBMS 에서관리되어지는정보도급속하게증가하고있다. 이로인해저장공간의부족으로하드웨어비용의증가와데이터처리성능에많은문제점들

ORACLE EXADATA HCC 압축방식이해하기 엑셈컨설팅본부 /DB 컨설팅팀김철환 개요 시간이지나면서데이터는급속하게증가하고있다. 데이터가증가함에따라 DBMS 에서관리되어지는정보도급속하게증가하고있다. 이로인해저장공간의부족으로하드웨어비용의증가와데이터처리성능에많은문제점들 ORACLE EXADATA HCC 압축방식이해하기 엑셈컨설팅본부 /DB 컨설팅팀김철환 개요 시간이지나면서데이터는급속하게증가하고있다. 데이터가증가함에따라 DBMS 에서관리되어지는정보도급속하게증가하고있다. 이로인해저장공간의부족으로하드웨어비용의증가와데이터처리성능에많은문제점들이나타나고있다. 이러한문제점들을해결하고자 ORACLE 에서는 EXADATA 라는시스템을통해스토리지공간부족현상과데이터처리성능을향상시키고자하였다.

More information

<4D F736F F D203033C6C4C6BCBCC72DB8AEBFC0B1D7B9E6B9FD2E646F63>

<4D F736F F D203033C6C4C6BCBCC72DB8AEBFC0B1D7B9E6B9FD2E646F63> Reviewed by Oracle Certified Master Korea Community ( http:www.ocmkorea.com http:cafe.daum.netoraclemanager ) 1.1.1 파티션테이블에서사용할수있는리오그방법파티션 level 의 importexport 방법을이용해파티션테이블중특정파티션 ( 혹은서브파티션 ) 만을선택적으로리오그할수있다.

More information

SRC PLUS 제어기 MANUAL

SRC PLUS 제어기 MANUAL ,,,, DE FIN E I N T R E A L L O C E N D SU B E N D S U B M O TIO

More information

PowerPoint Presentation

PowerPoint Presentation FORENSIC INSIGHT; DIGITAL FORENSICS COMMUNITY IN KOREA SQL Server Forensic AhnLab A-FIRST Rea10ne unused6@gmail.com Choi Jinwon Contents 1. SQL Server Forensic 2. SQL Server Artifacts 3. Database Files

More information

28 THE ASIAN JOURNAL OF TEX [2] ko.tex [5]

28 THE ASIAN JOURNAL OF TEX [2] ko.tex [5] The Asian Journal of TEX, Volume 3, No. 1, June 2009 Article revision 2009/5/7 KTS THE KOREAN TEX SOCIETY SINCE 2007 2008 ko.tex Installing TEX Live 2008 and ko.tex under Ubuntu Linux Kihwang Lee * kihwang.lee@ktug.or.kr

More information

단계

단계 본문서에서는 Tibero RDBMS 운영중에발생할수있는장애상황을가정하여각유형별장애상황에대해 Tibero 에서지원하고있는백업및복구방법을알아본다. Contents 1. BACKUP & RECOVERY 개요... 4 1.1. BACKUP( 백업 )... 4 1.2. RECOVERY( 복구 )... 4 2. BACKUP... 5 2.1. 백업형태... 5 2.1.1.

More information

다양한 예제로 쉽게 배우는 오라클 SQL 과 PL/SQL

다양한 예제로 쉽게 배우는 오라클 SQL 과 PL/SQL 다양한예제로쉽게배우는 오라클 SQL 과 PL/SQL 서진수저 9 장인덱스를배웁니다 1 1. 인덱스란무엇인가? 2 - ROWID ( 주소 ) 조회하기 SCOTT>SELECT ROWID, empno, ename 2 FROM emp 3 WHERE empno=7902 ; ROWID EMPNO ENAME --------------------------------- ----------

More information

Microsoft PowerPoint - Tech-iSeminar_Managing_Tablespace.ppt

Microsoft PowerPoint - Tech-iSeminar_Managing_Tablespace.ppt Tablespace 의관리 Getting the most out of MetaLink 최창권, 김주연 제품지원실한국오라클 ( 주 ) 한국오라클에서주최하는 Technical iseminar DATABASE의 tablespace 이해 에참석해주신여러분께감사드립니다. 저는한국오라클제품지원실에근무하는최창권입니다. 오늘세미나에서는 ORACLE database의논리적인저장소역할을하는

More information

Microsoft Word - dataguard_세미나_v1.8.doc

Microsoft Word - dataguard_세미나_v1.8.doc Oracle9i Dataguard 기술서 작성일 : 2005년 3월 24일업데이트 : 2006년 1월 22일 v1.8 Final 작성자 : LG카드중형서버운영파트 DBA 민연홍 Phone : 016-744-0220 E-Mail : ses0124@hanmail.net 목 차 1. dataguard 개요및아키텍처...2 (1) dataguard 란무엇인가?...2

More information

untitled

untitled PowerBuilder 連 Microsoft SQL Server database PB10.0 PB9.0 若 Microsoft SQL Server 料 database Profile MSS 料 (Microsoft SQL Server database interface) 行了 PB10.0 了 Sybase 不 Microsoft 料 了 SQL Server 料 PB10.0

More information

PowerChute Personal Edition v3.1.0 에이전트 사용 설명서

PowerChute Personal Edition v3.1.0 에이전트 사용 설명서 PowerChute Personal Edition v3.1.0 990-3772D-019 4/2019 Schneider Electric IT Corporation Schneider Electric IT Corporation.. Schneider Electric IT Corporation,,,.,. Schneider Electric IT Corporation..

More information

DocsPin_Korean.pages

DocsPin_Korean.pages Unity Localize Script Service, Page 1 Unity Localize Script Service Introduction Application Game. Unity. Google Drive Unity.. Application Game. -? ( ) -? -?.. 준비사항 Google Drive. Google Drive.,.. - Google

More information

슬라이드 1

슬라이드 1 / 유닉스시스템개요 / 파일 / 프로세스 01 File Descriptor file file descriptor file type unix 에서의파일은단지바이트들의나열임 operating system 은파일에어떤포맷도부과하지않음 파일의내용은바이트단위로주소를줄수있음 file descriptor 는 0 이나양수임 file 은 open 이나 creat 로 file

More information

T100MD+

T100MD+ User s Manual 100% ) ( x b a a + 1 RX+ TX+ DTR GND TX+ RX+ DTR GND RX+ TX+ DTR GND DSR RX+ TX+ DTR GND DSR [ DCE TYPE ] [ DCE TYPE ] RS232 Format Baud 1 T100MD+

More information

Microsoft PowerPoint - Tech-iSeminar_Flashback.ppt

Microsoft PowerPoint - Tech-iSeminar_Flashback.ppt Getting the most out of MetaLink 이은지 한국오라클 ( 주 ) 제품지원실 목차 1. Flashback Overview 2. Flashback drop 3. Flashback Versions Query 4. Flashback Transaction Query 5. Flashback Table 6. Flashback database 7.

More information

본교재는수업용으로제작된게시물입니다. 영리목적으로사용할경우저작권법제 30 조항에의거법적처벌을받을수있습니다. [ 실습 ] 스위치장비초기화 1. NVRAM 에저장되어있는 'startup-config' 파일이있다면, 삭제를실시한다. SWx>enable SWx#erase sta

본교재는수업용으로제작된게시물입니다. 영리목적으로사용할경우저작권법제 30 조항에의거법적처벌을받을수있습니다. [ 실습 ] 스위치장비초기화 1. NVRAM 에저장되어있는 'startup-config' 파일이있다면, 삭제를실시한다. SWx>enable SWx#erase sta [ 실습 ] 스위치장비초기화 1. NVRAM 에저장되어있는 'startup-config' 파일이있다면, 삭제를실시한다. SWx>enable SWx#erase startup-config Erasing the nvram filesystem will remove all configuration files Continue? [confirm] ( 엔터 ) [OK] Erase

More information

배치프로그램에서튜닝대상 SQL 추출하기 엑셈컨설팅본부 /DB 컨설팅팀박성호 배치프로그램의성능문제를진단하기위해트레이스를사용할수없고, 개별 SQL 에대한성 능점검은비효율적인경우에어떻게배치프로그램의성능문제를제대로파악하고개선안을도 출할것인가? 복잡한로직을가지고있는프로그램 (

배치프로그램에서튜닝대상 SQL 추출하기 엑셈컨설팅본부 /DB 컨설팅팀박성호 배치프로그램의성능문제를진단하기위해트레이스를사용할수없고, 개별 SQL 에대한성 능점검은비효율적인경우에어떻게배치프로그램의성능문제를제대로파악하고개선안을도 출할것인가? 복잡한로직을가지고있는프로그램 ( 배치프로그램에서튜닝대상 SQL 추출하기 엑셈컨설팅본부 /DB 컨설팅팀박성호 배치프로그램의성능문제를진단하기위해트레이스를사용할수없고, 개별 SQL 에대한성 능점검은비효율적인경우에어떻게배치프로그램의성능문제를제대로파악하고개선안을도 출할것인가? 복잡한로직을가지고있는프로그램 ( 이후배치프로그램 ) 에대한성능문제를파악하기위해수행되는모든 SQL 에대한개별수행내역을정확히판단할수있어야한다.

More information

Intra_DW_Ch4.PDF

Intra_DW_Ch4.PDF The Intranet Data Warehouse Richard Tanler Ch4 : Online Analytic Processing: From Data To Information 2000. 4. 14 All rights reserved OLAP OLAP OLAP OLAP OLAP OLAP is a label, rather than a technology

More information

목차 1. 제품 소개... 4 1.1 특징... 4 1.2 개요... 4 1.3 Function table... 5 2. 기능 소개... 6 2.1 Copy... 6 2.2 Compare... 6 2.3 Copy & Compare... 6 2.4 Erase... 6 2

목차 1. 제품 소개... 4 1.1 특징... 4 1.2 개요... 4 1.3 Function table... 5 2. 기능 소개... 6 2.1 Copy... 6 2.2 Compare... 6 2.3 Copy & Compare... 6 2.4 Erase... 6 2 유영테크닉스( 주) 사용자 설명서 HDD014/034 IDE & SATA Hard Drive Duplicator 유 영 테 크 닉 스 ( 주) (032)670-7880 www.yooyoung-tech.com 목차 1. 제품 소개... 4 1.1 특징... 4 1.2 개요... 4 1.3 Function table... 5 2. 기능 소개... 6 2.1 Copy...

More information

3 S Q L A n t i p a t t e r n s Trees/intro/parent.sql CREATE TABLE Comments ( comment_id SERIAL PRIMARY KEY, parent_id BIGINT UNSIGNED, comment TEXT

3 S Q L A n t i p a t t e r n s Trees/intro/parent.sql CREATE TABLE Comments ( comment_id SERIAL PRIMARY KEY, parent_id BIGINT UNSIGNED, comment TEXT 3 S Q L A n t i p a t t e r n s Trees/intro/parent.sql CREATE TABLE Comments ( comment_id SERIAL PRIMARY KEY, parent_id BIGINT UNSIGNED, comment TEXT NOT NULL, FOREIGN KEY (parent_id) REFERENCES Comments(comment_id)

More information

@OneToOne(cascade = = "addr_id") private Addr addr; public Emp(String ename, Addr addr) { this.ename = ename; this.a

@OneToOne(cascade = = addr_id) private Addr addr; public Emp(String ename, Addr addr) { this.ename = ename; this.a 1 대 1 단방향, 주테이블에외래키실습 http://ojcedu.com, http://ojc.asia STS -> Spring Stater Project name : onetoone-1 SQL : JPA, MySQL 선택 http://ojc.asia/bbs/board.php?bo_table=lecspring&wr_id=524 ( 마리아 DB 설치는위 URL

More information

ALTIBASE HDB Patch Notes

ALTIBASE HDB Patch Notes ALTIBASE HDB 5.3.3.93 Patch Notes Table of Contents BUG-27950 ALL PRIVILEGES 권한을가진계정이다른계정의테이블에 Foreign Key 를 생성하지못한다. 3 BUG-38105 PASSWORD_LIFE_TIME 경과후유예기간 (PASSWORD_GRACE_TIME) 내에 접속을시도할경우알림메시지를발생해야한다.

More information

untitled

untitled Push... 2 Push... 4 Push... 5 Push... 13 Push... 15 1 FORCS Co., LTD A Leader of Enterprise e-business Solution Push (Daemon ), Push Push Observer. Push., Observer. Session. Thread Thread. Observer ID.

More information

s SINUMERIK 840C Service and User Manual DATA SAVING & LOADING & & /

s SINUMERIK 840C Service and User Manual DATA SAVING & LOADING & & / SINUMERIK 840C Service and Uer Manual DATA SAVING & LOADING & & / / NC, RS232C /. NC NC / Computer link () Device ( )/PC / / Print erial Data input RS232C () Data output Data management FLOPPY DRIVE, FLOPPY

More information

Microsoft PowerPoint - Oracle Data Access Pattern.ppt

Microsoft PowerPoint - Oracle Data Access Pattern.ppt Special Key Note Oracle Data Access Pattern ( 주 ) 오픈메이드컨설팅 오동규수석컨설턴트 1 What is Data Access Pattern? > 데이터를 I/O 하는방식 Index Scan Full Table Scan Rowid 2 Why is The Pattern Important? >SQL 의성능을좌지우지함. >SQL

More information

결과보고서

결과보고서 오픈 소스 데이터베이스 시스템을 이용한 플래시 메모리 SSD 기반의 질의 최적화 기법 연구 A Study on Flash-based Query Optimizing in PostgreSQL 황다솜 1) ㆍ안미진 1) ㆍ이혜지 1) ㆍ김지민 2) ㆍ정세희 2) ㆍ이임경 3) ㆍ차시언 3) 성균관대학교 정보통신대학 1) ㆍ시흥매화고등학교 2) ㆍ용화여자고등학교 3)

More information

<C0CCBCBCBFB52DC1A4B4EBBFF82DBCAEBBE7B3EDB9AE2D313939392D382E687770>

<C0CCBCBCBFB52DC1A4B4EBBFF82DBCAEBBE7B3EDB9AE2D313939392D382E687770> i ii iii iv v vi 1 2 3 4 가상대학 시스템의 국내외 현황 조사 가상대학 플랫폼 개발 이상적인 가상대학시스템의 미래상 제안 5 웹-기반 가상대학 시스템 전통적인 교수 방법 시간/공간 제약을 극복한 학습동기 부여 교수의 일방적인 내용전달 교수와 학생간의 상호작용 동료 학생들 간의 상호작용 가상대학 운영 공지사항,강의록 자료실, 메모 질의응답,

More information

PowerPoint Presentation

PowerPoint Presentation Data Protection Rapid Recovery x86 DR Agent based Backup - Physical Machine - Virtual Machine - Cluster Agentless Backup - VMware ESXi Deploy Agents - Windows - AD, ESXi Restore Machine - Live Recovery

More information

기술노트 49 회 SQL PLAN MANAGEMENT Author 윤병길과장 Creation Date Last Updated Version 1.0 Copyright(C) 2009 Goodus Inc. All Rights Reserved

기술노트 49 회 SQL PLAN MANAGEMENT Author 윤병길과장 Creation Date Last Updated Version 1.0 Copyright(C) 2009 Goodus Inc. All Rights Reserved 기술노트 49 회 SQL PLAN MANAGEMENT Author 윤병길과장 Creation Date 2010-06-01 Last Updated Version 1.0 Copyright(C) 2009 Goodus Inc. All Rights Reserved Contents 1. SQL PLAN MANAGEMENT OVERVIEW... 3 1.1. INTRODUCTION...

More information

Data Sync Manager(DSM) Example Guide Data Sync Manager (DSM) Example Guide DSM Copyright 2003 Ari System, Inc. All Rights reserved. Data Sync Manager

Data Sync Manager(DSM) Example Guide Data Sync Manager (DSM) Example Guide DSM Copyright 2003 Ari System, Inc. All Rights reserved. Data Sync Manager Data Sync Manager (DSM) Example Guide DSM Copyright 2003 Ari System, Inc. All Rights reserved. Data Sync Manager are trademarks or registered trademarks of Ari System, Inc. 1 Table of Contents Chapter1

More information

Microsoft Word - Goodus_기술노트[19회]_Flashback

Microsoft Word - Goodus_기술노트[19회]_Flashback Goodus 기술노트 [19 회 ] Flashback Author 권웅원, 나지혜 Creation Date 2007-04-25 Last Updated 2007-04-25 Version 1.0 Copyright(C) 2004 Goodus Inc. All Rights Reserved Version 변경일자 변경자 ( 작성자 ) 주요내용 1 2007-04-25 권웅원,

More information

Connection 8 22 UniSQLConnection / / 9 3 UniSQL OID SET

Connection 8 22 UniSQLConnection / / 9 3 UniSQL OID SET 135-080 679-4 13 02-3430-1200 1 2 11 2 12 2 2 8 21 Connection 8 22 UniSQLConnection 8 23 8 24 / / 9 3 UniSQL 11 31 OID 11 311 11 312 14 313 16 314 17 32 SET 19 321 20 322 23 323 24 33 GLO 26 331 GLO 26

More information

thesis

thesis ( Design and Implementation of a Generalized Management Information Repository Service for Network and System Management ) ssp@nile nile.postech.ac..ac.kr DPE Lab. 1997 12 16 GMIRS GMIRS GMIRS prototype

More information

단답형 (26 회기출문제 ) 1. 아래와같은테이블이있을때아래의 SQL 결과에대해서 Oracle, SQL Server 순서로적으시오 TAB1 COL1 CHAR(10) COL2 CHAR(10) INSERT INTO TAB1 VALUES ('1',''); INSERT INT

단답형 (26 회기출문제 ) 1. 아래와같은테이블이있을때아래의 SQL 결과에대해서 Oracle, SQL Server 순서로적으시오 TAB1 COL1 CHAR(10) COL2 CHAR(10) INSERT INTO TAB1 VALUES ('1',''); INSERT INT Study Room Doc.03 : SQLD 예상문제 ( 단답형 ) 네이버 Cafe : 데이터베이스전문가포럼 Study Room http://cafe.naver.com/sqlpd SQLD 26,25,24,21 회기출문제를바탕으로작성 작성자 : 월야루 도움 : 빙수민외카페댓글 2017-11-30 단답형 (26 회기출문제 ) 1. 아래와같은테이블이있을때아래의 SQL

More information

Slide 1

Slide 1 1 EM Performance & Resource Management 최야벳 (yabet.choi@oracle.com) Sales Consultant Oracle Direct Agenda Intro 관리자의고민 기존시스템관리의문제점 About EM Case Demo Lock 경합 성능튜닝권고 ( 파라미터 ) Instance

More information

강의10

강의10 Computer Programming gdb and awk 12 th Lecture 김현철컴퓨터공학부서울대학교 순서 C Compiler and Linker 보충 Static vs Shared Libraries ( 계속 ) gdb awk Q&A Shared vs Static Libraries ( 계속 ) Advantage of Using Libraries Reduced

More information