"병렬쿼리 테스트"의 두 판 사이의 차이
DB CAFE
(새 문서: /**************************************************************************** * TABLE DATA MOVE --약 3분 14초 *********************************************************************...) |
|||
| 1번째 줄: | 1번째 줄: | ||
| − | + | ## 초기화 | |
| − | + | <source lang=sql> | |
| − | |||
TRUNCATE TABLE SCOTT.TB_ABC_CE; | TRUNCATE TABLE SCOTT.TB_ABC_CE; | ||
| − | |||
PURGE DBA_RECYCLEBIN; | PURGE DBA_RECYCLEBIN; | ||
| − | |||
SELECT * FROM SCOTT.TB_ABC_CE; | SELECT * FROM SCOTT.TB_ABC_CE; | ||
| − | + | </source> | |
| − | / | + | ## 병렬처리 설정 |
| − | # | + | <source lang=sql> |
| − | |||
| − | |||
| − | |||
| − | |||
ALTER SESSION ENABLE PARALLEL DML; | ALTER SESSION ENABLE PARALLEL DML; | ||
ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=64; | ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=64; | ||
| + | </source> | ||
| + | ## 테이블 속성 변경 NOLOGGING , PARALLEL | ||
| + | <source lang=sql> | ||
ALTER TABLE SCOTT.TB_ABC_CE NOLOGGING PARALLEL 32; | ALTER TABLE SCOTT.TB_ABC_CE NOLOGGING PARALLEL 32; | ||
| − | + | </source> | |
| + | <source lang=sql> | ||
INSERT INTO /*+ APPEND_VALUES */ SCOTT.TB_ABC_CE B | INSERT INTO /*+ APPEND_VALUES */ SCOTT.TB_ABC_CE B | ||
SELECT /*+FULL(A) PARALLEL(A,32) */ * | SELECT /*+FULL(A) PARALLEL(A,32) */ * | ||
FROM SCOTT.TB_ABC A; | FROM SCOTT.TB_ABC A; | ||
| − | |||
COMMIT; | COMMIT; | ||
| − | + | </source> | |
| + | ## 속성 복구 | ||
| + | <source lang=sql> | ||
ALTER TABLE SCOTT.TB_ABC_CE LOGGING NOPARALLEL; | ALTER TABLE SCOTT.TB_ABC_CE LOGGING NOPARALLEL; | ||
| − | + | </source> | |
| − | + | ## 인덱스 생성 | |
| − | + | <source lang=sql> | |
| − | |||
| − | |||
ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=64; | ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=64; | ||
| 42번째 줄: | 38번째 줄: | ||
ALTER INDEX GSSMADM.IX_IN_ABC_CE_01 REBUILD NOLOGGING PARALLEL 4; | ALTER INDEX GSSMADM.IX_IN_ABC_CE_01 REBUILD NOLOGGING PARALLEL 4; | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
ALTER INDEX GSSMADM.PK_IN_ABC_CE REBUILD NOLOGGING PARALLEL 8; | ALTER INDEX GSSMADM.PK_IN_ABC_CE REBUILD NOLOGGING PARALLEL 8; | ||
ALTER INDEX GSSMADM.PK_IN_ABC_CE LOGGING NOPARALLEL; | ALTER INDEX GSSMADM.PK_IN_ABC_CE LOGGING NOPARALLEL; | ||
| + | |||
| + | </source> | ||
2018년 11월 7일 (수) 21:50 판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
- 초기화
TRUNCATE TABLE SCOTT.TB_ABC_CE;
PURGE DBA_RECYCLEBIN;
SELECT * FROM SCOTT.TB_ABC_CE;- 병렬처리 설정
ALTER SESSION ENABLE PARALLEL DML;
ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=64;- 테이블 속성 변경 NOLOGGING , PARALLEL
ALTER TABLE SCOTT.TB_ABC_CE NOLOGGING PARALLEL 32;INSERT INTO /*+ APPEND_VALUES */ SCOTT.TB_ABC_CE B
SELECT /*+FULL(A) PARALLEL(A,32) */ *
FROM SCOTT.TB_ABC A;
COMMIT;- 속성 복구
ALTER TABLE SCOTT.TB_ABC_CE LOGGING NOPARALLEL;- 인덱스 생성
ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=64;
CREATE INDEX GSSMADM.IX_IN_ABC_CE_01 ON SCOTT.TB_ABC_CE
(SITE_CD, STKTAKE_NO, STKTAKE_SBJ_YN)
NOLOGGING
TABLESPACE TS_MIG
UNUSABLE
PARALLEL 4;
ALTER INDEX GSSMADM.IX_IN_ABC_CE_01 REBUILD NOLOGGING PARALLEL 4;
ALTER INDEX GSSMADM.PK_IN_ABC_CE REBUILD NOLOGGING PARALLEL 8;
ALTER INDEX GSSMADM.PK_IN_ABC_CE LOGGING NOPARALLEL;