반응형

테이블 logging 옵션 10초만에 변경!



테이블이 매일 TRUNCATE 후 INSERT 되는데 INSERT 할 때 굳이 Redo LOG 를 쌓아야 하나 의문이 들었다.


Redo 로그를 쌓는다는 것은 I/O 가 발생하게 된다는 건데 DB 는 I/O 가 발생하게 되면 그만큼 속도가 느려지게 된다.


Redo 로그를 그럼 안쌓기 위한 방법은??


바로 테이블에 NOLOGGING 옵션을 부여하면 됩니다.


이미 생성된 테이블은 어떻게 하나?


ALTER TABLE EMP NOLOGGING ;


위와 같은 설정을 하면 Redo LOG 가 남지 않고 INSERT 를 할 수 있게 됩니다.


또 다른 방법으로는 /*+ APPEND */ 힌트를 주어서 INSERT 하면 Redo LOG 를 남기지 않아요! 



INSERT /*+ APPEND */INTO EMP
SELECT * FROM EMP_TEST;


조금이라도 속도를 높여봅시다!

반응형
블로그 이미지

나남나여

일상 제품리뷰와 맛집/여행/사진을 좋아하고 IT 관련 프로그래밍 초급 & 고급 정보를 공유하는 블로그

,
반응형

DBA_HIST 를 활용하여 설정분석


DBA_HIST 테이블을 활용하면 DB에 남은 로그를 분석할 수 있다.


내가 주로 사용하는 것은 DBA_HIST_ACTIVE_SESS_HISTORY 테이블인데 

말그대로 ACTIVE SESSION HISTORY 를 보여준다.


사용용도는 이 테이블에서 SQL_ID 로 SQLTEXT 를 찾을 수 있다.


DBA_HIST_TEXT 테이블을 찾아보면 DB 에서 실행된 SQL 문을 확인 할 수 있다.


그 외 DBA_HIST 관련 테이블은 아래 리스트에서 확인 가능하다.



TABLE_NAME

COMMENTS

DBA_HIST_DATABASE_INSTANCE

Database Instance Information

DBA_HIST_SNAPSHOT

Snapshot Information

DBA_HIST_SNAP_ERROR

Snapshot Error Information

DBA_HIST_BASELINE

Baseline Metadata Information

DBA_HIST_WR_CONTROL

Workload Repository Control Information

DBA_HIST_DATAFILE

Names of Datafiles

DBA_HIST_FILESTATXS

Datafile Historical Statistics Information

DBA_HIST_TEMPFILE

Names of Temporary Datafiles

DBA_HIST_TEMPSTATXS

Temporary Datafile Historical Statistics Information

DBA_HIST_COMP_IOSTAT

I/O stats aggregated on component level

DBA_HIST_SQLSTAT

SQL Historical Statistics Information

DBA_HIST_SQLTEXT

SQL Text

DBA_HIST_SQL_SUMMARY

Summary of SQL Statistics

DBA_HIST_SQL_PLAN

SQL Plan Information

DBA_HIST_SQL_BIND_METADATA

SQL Bind Metadata Information

DBA_HIST_SQLBIND

SQL Bind Information

DBA_HIST_OPTIMIZER_ENV

Optimizer Environment Information

DBA_HIST_EVENT_NAME

Event Names

DBA_HIST_SYSTEM_EVENT

System Event Historical Statistics Information

DBA_HIST_BG_EVENT_SUMMARY

Summary of Background Event Historical Statistics Information

DBA_HIST_WAITSTAT

Wait Historical Statistics Information

DBA_HIST_ENQUEUE_STAT

Enqueue Historical Statistics Information

DBA_HIST_LATCH_NAME

Latch Names

DBA_HIST_LATCH

Latch Historical Statistics Information

DBA_HIST_LATCH_CHILDREN

Latch Children Historical Statistics Information

DBA_HIST_LATCH_PARENT

Latch Parent Historical Historical Statistics Information

DBA_HIST_LATCH_MISSES_SUMMARY

Latch Misses Summary Historical Statistics Information

DBA_HIST_LIBRARYCACHE

Library Cache Historical Statistics Information

DBA_HIST_DB_CACHE_ADVICE

DB Cache Advice History Information

DBA_HIST_BUFFER_POOL_STAT

Buffer Pool Historical Statistics Information

DBA_HIST_ROWCACHE_SUMMARY

Row Cache Historical Statistics Information Summary

DBA_HIST_SGA

SGA Historical Statistics Information

DBA_HIST_SGASTAT

SGA Pool Historical Statistics Information

DBA_HIST_PGASTAT

PGA Historical Statistics Information

DBA_HIST_PROCESS_MEM_SUMMARY

Process Memory Historical Summary Information

DBA_HIST_RESOURCE_LIMIT

Resource Limit Historical Statistics Information

DBA_HIST_SHARED_POOL_ADVICE

Shared Pool Advice History

DBA_HIST_STREAMS_POOL_ADVICE

Streams Pool Advice History

DBA_HIST_SQL_WORKAREA_HSTGRM

SQL Workarea Histogram History

DBA_HIST_PGA_TARGET_ADVICE

PGA Target Advice History

DBA_HIST_SGA_TARGET_ADVICE

SGA Target Advice History

DBA_HIST_INSTANCE_RECOVERY

Instance Recovery Historical Statistics Information

DBA_HIST_JAVA_POOL_ADVICE

Java Pool Advice History

DBA_HIST_THREAD

Thread Historical Statistics Information

DBA_HIST_STAT_NAME

Statistic Names

DBA_HIST_SYSSTAT

System Historical Statistics Information

DBA_HIST_SYS_TIME_MODEL

System Time Model Historical Statistics Information

DBA_HIST_OSSTAT_NAME

Operating System Statistic Names

DBA_HIST_OSSTAT

Operating System Historical Statistics Information

DBA_HIST_PARAMETER_NAME

Parameter Names

DBA_HIST_PARAMETER

Parameter Historical Statistics Information

DBA_HIST_UNDOSTAT

Undo Historical Statistics Information

DBA_HIST_SEG_STAT

 Historical Statistics Information

DBA_HIST_SEG_STAT_OBJ

Segment Names

DBA_HIST_METRIC_NAME

Segment Names

DBA_HIST_SYSMETRIC_HISTORY

System Metrics History

DBA_HIST_SYSMETRIC_SUMMARY

System Metrics History

DBA_HIST_SESSMETRIC_HISTORY

System Metrics History

DBA_HIST_FILEMETRIC_HISTORY

File Metrics History

DBA_HIST_WAITCLASSMET_HISTORY

Wait Class Metric History

DBA_HIST_DLM_MISC

Distributed Lock Manager Miscellaneous Historical Statistics Information

DBA_HIST_CR_BLOCK_SERVER

Consistent Read Block Server Historical Statistics

DBA_HIST_CURRENT_BLOCK_SERVER

Current Block Server Historical Statistics

DBA_HIST_INST_CACHE_TRANSFER

Instance Cache Transfer Historical Statistics

DBA_HIST_ACTIVE_SESS_HISTORY

Active Session Historical Statistics Information

DBA_HIST_TABLESPACE_STAT

Tablespace Historical Statistics Information

DBA_HIST_LOG

Log Historical Statistics Information

DBA_HIST_MTTR_TARGET_ADVICE

Mean-Time-To-Recover Target Advice History

DBA_HIST_TBSPC_SPACE_USAGE

Tablespace Usage Historical Statistics Information

DBA_HIST_SERVICE_NAME

Service Names

DBA_HIST_SERVICE_STAT

Historical Service Statistics

DBA_HIST_SERVICE_WAIT_CLASS

Historical Service Wait Class Statistics

DBA_HIST_SESS_TIME_STATS

CPU and I/O time for interesting (STREAMS) sessions

DBA_HIST_STREAMS_CAPTURE

STREAMS Capture Historical Statistics Information

DBA_HIST_STREAMS_APPLY_SUM

STREAMS Apply Historical Statistics Information

DBA_HIST_BUFFERED_QUEUES

STREAMS Buffered Queues Historical Statistics Information

DBA_HIST_BUFFERED_SUBSCRIBERS

STREAMS Buffered Queue Subscribers Historical Statistics Information

DBA_HIST_RULE_SET

Rule sets historical statistics information

DBA_HISTOGRAMS

Synonym for DBA_TAB_HISTOGRAMS

[출처] DBA_HIST 시리즈|작성자 시와처


반응형
블로그 이미지

나남나여

일상 제품리뷰와 맛집/여행/사진을 좋아하고 IT 관련 프로그래밍 초급 & 고급 정보를 공유하는 블로그

,
반응형

엑셀 한영자동변환 10초만에 해제!


거두절미하고 의도치 않게 한글 > 영문,  영문 > 한글로 자동변환되는 문제를 해결하고자 합니다!



1. 왼쪽 상단 O 아이콘 클릭 > Excel 옵션 클릭




2. 언어 교정 > 자동 고침 옵션 클릭




3. 한/영 자동 고침 체크 해제!




이제 한글이나 영문을 입력 시 변환되는 것을 두려워하지 말고 독수리타법을 날려주세요!


감사합니다.

반응형
블로그 이미지

나남나여

일상 제품리뷰와 맛집/여행/사진을 좋아하고 IT 관련 프로그래밍 초급 & 고급 정보를 공유하는 블로그

,
반응형