병렬쿼리 테스트
DB CAFE
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
/****************************************************************************
* TABLE DATA MOVE --약 3분 14초
- /
TRUNCATE TABLE SCOTT.TB_ABC_CE;
PURGE DBA_RECYCLEBIN;
SELECT * FROM SCOTT.TB_ABC_CE;
/**************************************************************
- 2018.09.04
log_buffer=33554432 db_cache_size=30G
- /
ALTER SESSION ENABLE PARALLEL DML; ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=64;
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;
/****************************************************************************
* INDEX 생성을 위한 스크립트는 동시 각각 4개 세션으로 수행 * IX_IN_ABC_CE_01 INDEX --약 22분
- /
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;
/****************************************************************************
* IX_IN_ABC_CE_02 INDEX
- /
ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=64;
CREATE INDEX GSSMADM.IX_IN_ABC_CE_02 ON SCOTT.TB_ABC_CE
(STKTAKE_NO, STKTAKE_SBJ_YN, SITE_CD)
NOLOGGING TABLESPACE TS_MIG UNUSABLE PARALLEL 4;
ALTER INDEX GSSMADM.IX_IN_ABC_CE_02 REBUILD NOLOGGING PARALLEL 4;
ALTER INDEX GSSMADM.IX_IN_ABC_CE_02 LOGGING NOPARALLEL;
/****************************************************************************
* IX_IN_ABC_CE_03 INDEX
- /
ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=64;
CREATE INDEX GSSMADM.IX_IN_ABC_CE_03 ON SCOTT.TB_ABC_CE
(SITE_CD, STKTAKE_NO, STKTAKE_YN, DPT_CD)
NOLOGGING TABLESPACE TS_MIG UNUSABLE PARALLEL 4;
ALTER INDEX GSSMADM.IX_IN_ABC_CE_03 REBUILD NOLOGGING PARALLEL 4;
ALTER INDEX GSSMADM.IX_IN_ABC_CE_03 LOGGING NOPARALLEL;
/****************************************************************************
* PK_IN_ABC_CE INDEX
- /
ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=64;
CREATE INDEX GSSMADM.PK_IN_ABC_CE ON SCOTT.TB_ABC_CE
(STKTAKE_NO, SITE_CD, ITEM_CD, STK_POSIT_SP_CD, REG_SEQNO)
NOLOGGING TABLESPACE TS_MIG UNUSABLE PARALLEL 8;
ALTER INDEX GSSMADM.PK_IN_ABC_CE REBUILD NOLOGGING PARALLEL 8;
ALTER INDEX GSSMADM.PK_IN_ABC_CE LOGGING NOPARALLEL;