'Knowledge/Oracle' 카테고리의 글 목록

Local Partition INDEX Test

Knowledge/Oracle 2018. 4. 24. 00:33

■ 개요

유니크 인덱스를 위주로 관리 편의를 위해 LOCAL 인덱스로 변경하거나 처음부터 LOCAL 인덱스로 설계할 때 고려해야할 점을 간단히 테스트해보고자 한다.

최근 고객사에서 이걸 고려하는 건이 있어 정리겸.

■ 사전 스크립트

CREATE TABLE sales
  ( prod_id       NUMBER
  , cust_id       NUMBER
  , time_id       DATE
  )
 PARTITION BY RANGE (time_id)
 ( PARTITION sales_2017_12 VALUES LESS THAN (TO_DATE('01-JAN-2018','dd-MON-yyyy'))
    TABLESPACE users
 , PARTITION sales_2018_01 VALUES LESS THAN (TO_DATE('01-FEB-2018','dd-MON-yyyy'))
    TABLESPACE users
 , PARTITION sales_2018_02 VALUES LESS THAN (TO_DATE('01-MAR-2018','dd-MON-yyyy'))
    TABLESPACE users
 , PARTITION sales_2018_03 VALUES LESS THAN (TO_DATE('01-APR-2018','dd-MON-yyyy'))
    TABLESPACE users
 , PARTITION sales_2018_04 VALUES LESS THAN (TO_DATE('01-MAY-2018','dd-MON-yyyy'))
    TABLESPACE users
 );
 
 create sequence sales_seq;
 
 insert into sales values(sales_seq.nextval,1121,to_date('20180101','yyyymmdd'));
 insert into sales values(sales_seq.nextval,1122,to_date('20180101','yyyymmdd'));
 insert into sales values(sales_seq.nextval,1120,to_date('20180101','yyyymmdd'));
 insert into sales values(sales_seq.nextval,1111,to_date('20180101','yyyymmdd'));
 insert into sales values(sales_seq.nextval,1123,to_date('20180201','yyyymmdd'));
 insert into sales values(sales_seq.nextval,1123,to_date('20180201','yyyymmdd'));
 insert into sales values(sales_seq.nextval,1234,to_date('20180201','yyyymmdd'));
 insert into sales values(sales_seq.nextval,1643,to_date('20180201','yyyymmdd'));
 insert into sales values(sales_seq.nextval,1111,to_date('20180301','yyyymmdd'));
 insert into sales values(sales_seq.nextval,2222,to_date('20180301','yyyymmdd'));
 insert into sales values(sales_seq.nextval,1321,to_date('20180301','yyyymmdd'));
 insert into sales values(sales_seq.nextval,1955,to_date('20180301','yyyymmdd'));
 insert into sales values(sales_seq.nextval,1754,to_date('20180401','yyyymmdd'));
 insert into sales values(sales_seq.nextval,1027,to_date('20180401','yyyymmdd'));
 insert into sales values(sales_seq.nextval,1013,to_date('20180401','yyyymmdd'));
 insert into sales values(sales_seq.nextval,1100,to_date('20180401','yyyymmdd'));
 commit;
create unique index sales_pk on sales(prod_id) local;
--유니크 인덱스는 파티션 키를 포함해야 local로 만들어진다.
                                *
--ERROR at line 1:
--ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index 
create unique index sales_pk on sales(prod_id,time_id) local;
create index sales_custid on sales(cust_id) local;
exec dbms_stats.gather_table_stats(user,'sales');
alter session set statistics_level=all;
alter session set nls_date_format='yyyymmdd hh24:mi:ss';
select /*+gather_plan_statistics*/ * from sales;
   PROD_ID    CUST_ID TIME_ID
---------- ---------- -----------------
        21       1121 20180101 00:00:00
        22       1122 20180101 00:00:00
        23       1120 20180101 00:00:00
        24       1111 20180101 00:00:00
        25       1123 20180201 00:00:00
        26       1123 20180201 00:00:00
        27       1234 20180201 00:00:00
        28       1643 20180201 00:00:00
        29       1111 20180301 00:00:00
        30       2222 20180301 00:00:00
        31       1321 20180301 00:00:00
        32       1955 20180301 00:00:00
        33       1754 20180401 00:00:00
        34       1027 20180401 00:00:00
        35       1013 20180401 00:00:00
        36       1100 20180401 00:00:00

■ 엑세스 테스트

이제 where prod_id=NN, where cust_id=NN 할 때 어떻게될까?
1. 파티션 하나의 인덱스만 액세스한다
2. 파티션 전부의 인덱스를 액세스한다?



select /*+gather_plan_statistics*/ * from sales where prod_id=28;
set long 5000 line 200 pages 1000
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST -ROWS +PARTITION'));
 
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name     | Starts | Pstart| Pstop | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |          |      1 |       |       |      1 |00:00:00.01 |       5 |
|   1 |  PARTITION RANGE ALL               |          |      1 |     1 |     5 |      1 |00:00:00.01 |       5 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| SALES    |      5 |     1 |     5 |      1 |00:00:00.01 |       5 |
|*  3 |    INDEX RANGE SCAN                | SALES_PK |      4 |     1 |     5 |      1 |00:00:00.01 |       4 |
----------------------------------------------------------------------------------------------------------------
 
 
select /*+gather_plan_statistics*/ * from sales where cust_id=1643;
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST -ROWS +PARTITION'));
 
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name         | Starts | Pstart| Pstop | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |              |      1 |       |       |      1 |00:00:00.01 |       5 |
|   1 |  PARTITION RANGE ALL               |              |      1 |     1 |     5 |      1 |00:00:00.01 |       5 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| SALES        |      5 |     1 |     5 |      1 |00:00:00.01 |       5 |
|*  3 |    INDEX RANGE SCAN                | SALES_CUSTID |      4 |     1 |     5 |      1 |00:00:00.01 |       4 |
--------------------------------------------------------------------------------------------------------------------

파티션을 하나 추가한다면 버퍼가 하나 더 늘것이다. 데이터가 없어 인덱스 블럭이 하나이기 때문이다

alter session set deferred_segment_creation=false;
alter table sales add partition sales_2018_05 VALUES LESS THAN (TO_DATE('01-JUN-2018','dd-MON-yyyy'))
    TABLESPACE users;
exec dbms_stats.GATHER_TABLE_STATS(null,'sales',no_invalidate=>false);
 
select /*+gather_plan_statistics*/ * from sales where prod_id=28;
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST -ROWS +PARTITION'));
 
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name     | Starts | Pstart| Pstop | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |          |      1 |       |       |      1 |00:00:00.01 |       6 |
|   1 |  PARTITION RANGE ALL               |          |      1 |     1 |     6 |      1 |00:00:00.01 |       6 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| SALES    |      6 |     1 |     6 |      1 |00:00:00.01 |       6 |
|*  3 |    INDEX RANGE SCAN                | SALES_PK |      5 |     1 |     6 |      1 |00:00:00.01 |       5 |
----------------------------------------------------------------------------------------------------------------
 
select /*+gather_plan_statistics*/ * from sales where cust_id=1643;
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST -ROWS +PARTITION'));
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name         | Starts | Pstart| Pstop | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |              |      1 |       |       |      1 |00:00:00.01 |       6 |
|   1 |  PARTITION RANGE ALL               |              |      1 |     1 |     6 |      1 |00:00:00.01 |       6 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| SALES        |      6 |     1 |     6 |      1 |00:00:00.01 |       6 |
|*  3 |    INDEX RANGE SCAN                | SALES_CUSTID |      5 |     1 |     6 |      1 |00:00:00.01 |       5 |
--------------------------------------------------------------------------------------------------------------------

예상대로 하나가 늘었다. 추가된 sales_2018_05 Local Index 블럭이 하나 늘었기 때문이다.

당연히 partition 조건도 같이 들어온다면 single partition만 스캔할것이다.

select /*+gather_plan_statistics*/ * from sales where prod_id=28 and time_id=to_date('20180201 00:00:00','yyyymmdd hh24:mi:ss');
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST -ROWS +PARTITION'));
 
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name     | Starts | Pstart| Pstop | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |          |      1 |       |       |      1 |00:00:00.01 |       2 |
|   1 |  PARTITION RANGE SINGLE            |          |      1 |     3 |     3 |      1 |00:00:00.01 |       2 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| SALES    |      1 |     3 |     3 |      1 |00:00:00.01 |       2 |
|*  3 |    INDEX UNIQUE SCAN               | SALES_PK |      1 |     3 |     3 |      1 |00:00:00.01 |       1 |
----------------------------------------------------------------------------------------------------------------

마지막으로 prod_id는 시퀀스에 의해 right grow Index라서 Heavy하게 Insert될 경우 Index Split 현상으로 경합이 발생하기에
hash partition 인덱스를 만들어서 테스트해본다.

drop index sales_pk;
 
create index sales_pk on sales(prod_id) global
partition by hash(prod_id)
partitions 10
store in (users);
 
select /*+gather_plan_statistics*/ * from sales where prod_id=23;
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST -ROWS +PARTITION'));
 
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name     | Starts | Pstart| Pstop | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |          |      1 |       |       |      1 |00:00:00.01 |       3 |
|   1 |  PARTITION HASH SINGLE              |          |      1 |     7 |     7 |      1 |00:00:00.01 |       3 |
|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| SALES    |      1 | ROWID | ROWID |      1 |00:00:00.01 |       3 |
|*  3 |    INDEX RANGE SCAN                 | SALES_PK |      1 |     7 |     7 |      1 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------------------------
 
실무에서 그럴일은 없겠지만 Hash 파티션된 대상에 부등호 조건으로 조회한다면 어찌될까
select /*+gather_plan_statistics*/ * from sales where prod_id<=22;
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST -ROWS +PARTITION'));
 
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name     | Starts | Pstart| Pstop | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |          |      1 |       |       |      2 |00:00:00.01 |      13 |
|   1 |  PARTITION HASH ALL                 |          |      1 |     1 |    10 |      2 |00:00:00.01 |      13 |
|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| SALES    |     10 | ROWID | ROWID |      2 |00:00:00.01 |      13 |
|*  3 |    INDEX RANGE SCAN                 | SALES_PK |     10 |     1 |    10 |      2 |00:00:00.01 |      11 |
-----------------------------------------------------------------------------------------------------------------

비파티션은 테스트는 생략한다. 위 결과와 의미는 같다.

■결론

테이블을 날짜나 월 등으로 나누어 파티션하고 과거 파티션을 주기적으로 DROP하려고 한다면 LOCAL 인덱스가 관리에 유리하다.
단, 파티션 키가 조건에 같이 사용되지 않는다면 성능은 나쁠 것이다.
미래의 파티션을 만들어둘 경우 파티션의 갯수만큼 블럭 접근이 더 많아질 것이다만 한 파티션당 1 블럭 정도야 문제 없을 것이다.
문제는 과거의 파티션이다. 과거의 파티션은 RANSE SCAN을 위해 파티션 인덱스마다 root-branch를 거쳐 leaf 블럭을
하나는 접근하게 될 것이다.
데이터가 많으면 blevel이 높을 것이다. 추출 행이 적다면 포기할 성능 수준은 감안할 수 있지만추출행이
추출행이 많고 NL같은 조인이 일어난다면 그 차이는 심각해진다.
비파티션은 Leaf 블럭까지 접근은 한번만 일어날 것이다.

한가지 더 짚고 넘어가야 할 것은 중복 무결성 문제다.
UNIQUE 조건은 LOCAL 인덱스를 만들기 위해 파티션된 컬럼을 포함해줘야 한다.
만약 prod_id가 시퀀스를 통해 입력되지 않았다면? prod_id를 UPDATE하는 건이 있다면?

   PROD_ID    CUST_ID TIME_ID
---------- ---------- -----------------
        21       1121 20180101 00:00:00
        22       1122 20180101 00:00:00
        23       1120 20180101 00:00:00
        24       1111 20180101 00:00:00
        25       1123 20180201 00:00:00
        26       1123 20180201 00:00:00
        27       1234 20180201 00:00:00
        28       1643 20180201 00:00:00
        29       1111 20180301 00:00:00
        30       2222 20180301 00:00:00
        31       1321 20180301 00:00:00
        32       1955 20180301 00:00:00
        33       1754 20180401 00:00:00
        34       1027 20180401 00:00:00
        35       1013 20180401 00:00:00
        36       1100 20180401 00:00:00
 
Insert into sales values(36, 1231 , to_date('20180401 00:00:01','yyyymmdd hh24:mi:ss'));

만약 global이나 non-partition으로 unique 인덱스를 만들었다면 에러 나겠지만,
local로 prod_id+time_id를 만들었다면 정상적으로 입력되어 버린다.

drop index sales_pk;
create unique index sales_pk on sales(prod_id,time_id) local;
 
SQL> Insert into sales values(36, 1231 , to_date('20180401 00:00:01','yyyymmdd hh24:mi:ss'));
1 row created.
SQL> commit;
Commit complete.
 
select /*+gather_plan_statistics*/ * from sales where prod_id=36;
 
   PROD_ID    CUST_ID TIME_ID
---------- ---------- -----------------
        36       1100 20180401 00:00:00
        36       1231 20180401 00:00:01

회원정보 테이블의 중복을 허용하지 말아야 하는 유저닉네임 이라면 어찌될까? 잘 생각하자
이런 점들 때문에 OLTP는 일반적으로 GLOBAL이나 비파티션을 권장하게 된다.

그런데 주로 history성 테이블에 파티션을 range로 주로 적용하기에 문제가 안될 경우도 많다.

■정리 script

drop table sales purge;
drop sequence sales_seq;

저작자표시 비영리 변경금지

'Knowledge > Oracle' 카테고리의 다른 글

Oracle DBA와 개발자의 필독서 (2)	2016.09.23
Data guard와 Active data guard license (0)	2016.08.12
sqlplus 접속 hang시에 접속 방법 (0)	2016.05.27
impdp, imp 주의사항 (0)	2016.03.20
Oracle Create Table 내부 절차 (0)	2015.10.21

Posted by neo-orcl

,

Oracle DBA와 개발자의 필독서

Knowledge/Oracle 2016. 9. 23. 12:04

이펙티브 오라클이라는 2004년에 출시한 서적에 나오는 이야기로 아마 9i나 10g가 출시된 시기를 기준으로 써있습니다.

책에 나온 내용은 반말로, 제가 추가한 내용은 존댓말로 적겠습니다.

개발자와 DBA의 공용 필독서 => 새로운 릴리즈가 나올 때마다 다음 문서를 읽어야 한다.

■ Concepts

■ New feature Guide

개발자의 필독서

■ Application Development Guide

■ PL/SQL User Guide , Reference => 최근엔 User Guide가 따로 없고 위에서 말한 Development Guide에 PL/SQL 내용이 있습니다

■ Perfomance Tuning Guide => 전반부를 반드시 읽고 나머지 부분은 훑어보면 된다

DBA의 필독서 => concepts와 New feature Guide를 읽은 후 다음 문서를 읽어야 한다.

■ Backup and Recovery Concepts => 현재 기준으로 Backup and Recovery User's Guide를 이야기하는 것 같습니다.

10g에서도 Backup 관련은 Backup and Recovery Basics, Backup and Recovery Advanced Users's Guide 그리고 Reference만 있습니다. 아마도 Backup and Recovery Basics를 말하는 것 같은데 11g 문서부터는 Backup and Recovery User's Guide와 Reference만 나오고 있습니다.

■ Backup and Recovery Reference

■ Administration Guide

■ Perfomance Tuning Guide => 후반부는 각별히 주의해 읽어야 한다.

Concepts 문서를 조금이라도 읽어본 사람들은 해당 문서가 굉장히 좋은 문서임을 압니다.

허나 무료인데도 국내에선 Concept를 읽어본 사람은 드뭅니다(제 주변을 보아선?). 아무래도 영문이라서 그렇겠지요.

그리고 개발자 중에서 과연 Concepts을 읽는 사람이 있을까요? 아마도 없을 것 같습니다.

저작자표시 비영리 변경금지

'Knowledge > Oracle' 카테고리의 다른 글

Local Partition INDEX Test (0)	2018.04.24
Data guard와 Active data guard license (0)	2016.08.12
sqlplus 접속 hang시에 접속 방법 (0)	2016.05.27
impdp, imp 주의사항 (0)	2016.03.20
Oracle Create Table 내부 절차 (0)	2015.10.21

Posted by neo-orcl

,

Data guard와 Active data guard license

Knowledge/Oracle 2016. 8. 12. 13:29

Oracle Enterprise Edition에서 아래 DG 옵션이 무료이다.(11gR2 license 문서에서 나옴)

Oracle Data Guard—Redo Apply
Oracle Data Guard—SQL Apply
Oracle Data Guard—Snapshot Standby

그런데 Active Data guard는 Enterprise Edition 옵션에서 추가 비용을 지불하는 옵션이다.

하여 active data guard가 정확히 무엇을 해야 active data guard를 사용한다고 인식되는지 조금 뒤져보았다.

11g문서는 없었고(있을지도 모르지만 못찾았다)

12c기준으로 링크에서 아래처럼 나와있다.(http://www.oracle.com/technetwork/database/options/active-data-guard/overview/index.html)

Data Guard Architecture Overview

Data Guard provides the management, monitoring, and automation software to create and maintain one or more synchronized copies of a production database to protect Oracle data from failures, disasters, human error, and data corruptions while providing high availability for mission critical applications. Data Guard is included with Oracle Database Enterprise Edition.

Active Data Guard Functionality

Active Data Guard is an option license for Oracle Database Enterprise Edition. Active Data Guard enables advanced capabilities that that extend basic Data Guard functionality. These include:

· Real-Time Query - offload read-only workloads to an up-to-date standby database => 즉 DB read-only 모드로 오픈

· Automatic Block Repair - automatic repair of physical corruption transparent to the user => 예전부터 있던 기능

· Far Sync - zero data loss protection across any distance => 12c new feature이다

· Standby Block Change Tracking - enable incremental backups on an active standby => 예전부터 있던 기능

· Active Data Guard Rolling Upgrade - make it simple to reduce planned downtime => 12c new feature. 단, 10.1.0.3부터 DG sql apply는 db rolling upgrade가 가능하다

· Global Database Services - load balancing and service management across replicated databases. See Global Data Services => 12c new feature이다

· Application Continuity - make outages transparent to users. See Application Continuity => (rac one node도 가능) => Requires the Active Data Guard option or the Real Application Clusters option

저작자표시 비영리 변경금지

'Knowledge > Oracle' 카테고리의 다른 글

Local Partition INDEX Test (0)	2018.04.24
Oracle DBA와 개발자의 필독서 (2)	2016.09.23
sqlplus 접속 hang시에 접속 방법 (0)	2016.05.27
impdp, imp 주의사항 (0)	2016.03.20
Oracle Create Table 내부 절차 (0)	2015.10.21

Posted by neo-orcl

,

sqlplus 접속 hang시에 접속 방법

Knowledge/Oracle 2016. 5. 27. 13:33

■ 개요

접속이 안된다는 이상이 있다고 연락을 받고 확인을 하려 할 때 maximum processes 값을 초과하여 접속을 할 수 없을 경우가 있을 수 있다.

아니면 shared pool의 부족 등의 이유도 있을 수 있다.

이때 선택하는 방법은 pmon을 kill하고 startup 하는 비상 조치를 취할 수 있는데 문제는 이 경우 추후 원인을 찾지 못할 수도 있고 이 조치를 취하면 안되는 즉, DB를 내리면 안되는 경우도 있을 수 있다.

그래서 -prelim 옵션이 존재한다. preliminary connect 이다.

(오라클 매뉴얼에서 검색으로도 찾을 수 없다)

일반적으로 정상 상태에서 sysdba 접속시 새 프로세스를 시작하고, 새 프로세스가 SGA에 접속하고, SGA내에 프로세스와 세션 상태 정보를 넣기 위한 메모리를 할당받아 남긴다.

하지만 prelim 옵션은 마지막 과정이 없다.

그래서 Lock이나 latch나 mutext를 사용하는 작업을 할 수는 없다.

(prelim 접속시 shutdown 명령도 abort만 가능하다.)

■ 테스트

$ sqlplus -prelim / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri May 27 13:17:51 2016

SQL> select 1 from dual;
select 1 from dual
*
ERROR at line 1:
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0

SQL> oradebug setmypid
Statement processed.

SQL> oradebug unlimit

Statement processed.

SQL> oradebug hanganalyze 12
Statement processed.

RAC일 경우에는 옵션이 다르게 있다.

SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug -g all hanganalyze 3
SQL> oradebug -g all dump systemstate 266

※해당 기능을 사용한 케이스의 링크

http://arup.blogspot.kr/2008/08/diagnosing-library-cache-latch.html

저작자표시 비영리 변경금지

'Knowledge > Oracle' 카테고리의 다른 글

Oracle DBA와 개발자의 필독서 (2)	2016.09.23
Data guard와 Active data guard license (0)	2016.08.12
impdp, imp 주의사항 (0)	2016.03.20
Oracle Create Table 내부 절차 (0)	2015.10.21
SQL Loader Direct Load (0)	2015.10.05

Posted by neo-orcl

,

impdp, imp 주의사항

Knowledge/Oracle 2016. 3. 20. 01:04

■ synonym 관련

오라클에서 import시 해당 테이블에 대한 시노님이 걸려 있으면 ?

말 그대로, 오라클에서 IMPORT하려는 테이블명과 동일한 SYNONYM이 존재하는 경우,
테이블을 생성하지 않고 해당 SYNONYM에 DATA를 IMPORT하려고 시도한다

■ 스키마 단위 주의사항

import나 impdp나 스키마단위로 import하면 full과는 다르게
role, public synonym, profile, public database link, system 권한 등이 import되지 않으니 주의해야 한다.

■ 캐릭터셋 주의사항

캐릭터 셋이 다른 경우 자동 변환되지만
소스 DB의 캐릭터셋이 타겟 DB의 하위 셋이어야 한다.
예를 들어 소스 DB가 US7ASCII(7bit)이고 타겟 DB가 WE8ISO8859P1(8bit)일 경우 문제 없지만
상이한 8bit 캐릭터셋 사이나 8bit에서 7bit로 import할 경우 특수 문자가 손실될 수 있다.

■ network_link를 통한 datapump는 conventional path이다.

network_link를 사용하지 않는 impdp의 경우 direct path를 사용하지만
network_link를 사용하는 impdp는 conventional path이다.

저작자표시 비영리 변경금지

'Knowledge > Oracle' 카테고리의 다른 글

Data guard와 Active data guard license (0)	2016.08.12
sqlplus 접속 hang시에 접속 방법 (0)	2016.05.27
Oracle Create Table 내부 절차 (0)	2015.10.21
SQL Loader Direct Load (0)	2015.10.05
undo 관련 좋은 링크 (0)	2015.09.15

Posted by neo-orcl

,

Oracle Create Table 내부 절차

Knowledge/Oracle 2015. 10. 21. 13:30

오라클은 DDL을 DML과는 다르게 처리한다.

create table을 진행하면 DB는 create table문을 최적화하지 않는다.

DDL에 대해 일반적으로 오라클은 parse 후 여러개의 recursive SQL 문을 처리해야 한다.

CRATE TABLE test_table (column1 number);

위 문장을 처리할 때 다음과 recursive SQL 처리 과정을 거친다.

1. create table 문 전에 commit 한다.

2. 유저권한과 create table 권한을 확인한다.

3. 테이블이 어떤 테이블스페이스에 저장될지 정한다.

4. 테이블스페이스의 quota를 넘지 않도록 보장한다.

5. 같은 스키마에 같은 object가(이름이) 없는지 보장한다.

6. Dictionary에 테이블 정의를 한 row들을 insert한다.

7. 성공한다면 commit 실패한다면 rollbckup한다.

저작자표시 비영리

'Knowledge > Oracle' 카테고리의 다른 글

sqlplus 접속 hang시에 접속 방법 (0)	2016.05.27
impdp, imp 주의사항 (0)	2016.03.20
SQL Loader Direct Load (0)	2015.10.05
undo 관련 좋은 링크 (0)	2015.09.15
null 관련 주의사항 (0)	2015.07.10

Posted by neo-orcl

,

SQL Loader Direct Load

Knowledge/Oracle 2015. 10. 5. 16:24

언제 Conventional Data Load를 사용해야 하나?

만약에 데이터 적재 속도가 가장 중요하다면 direct path를 사용해야 한다. conventional path load보다 더 빠르기 때문이다. 그러나 direct path에 특정 제약조건이 있기 때문에 conventional path load를 사용해야 한다. 앞으로는 CPL을 conventional path load라고 하고 DPL을 direct path load라고 하겠다.

구분	conventional path load	direct path load
속도	느림	빠름
가용성	로드 작업중 다른 유저가 테이블 수정 가능(가용성up)	로드 작업 중 다른 유저가 테이블 변경 불가(가용성down)
클러스터 테이블	클러스터 테이블에 load 가능	클러스터 테이블에 load 불가
인덱스 관련	인덱스가 걸린 큰 테이블에 소량의 행을 load할 경우(append) 유리	DPL에서는 인덱스를 로드 종료 후에 병합하는데 이때 기존 인덱스를 복사하고 병합한다. 만약 기존 인덱스가 크고 새로운 인덱스 키들이 작을 경우 DPL의 빠른 속도는 기존 인덱스를 복사하는 시간에 의해 상쇄 될 것이다.
제약조건	비교적 적은 수의 행을 큰 테이블에 로드하는데 참조 제약조건과 check 제약조건이 걸려있을 경우 CPL을 사용해야 한다.	DPL에서는 PRIMARY KEY, UNIQUE, NOT NULL만 검증한다.
INSERT 트리거	실행됨	실행되지 않음

Direct Path Load 장점

DPL은 CPL보다 빠른데 그 이유는 다음과 같다.

n 부분적인 블럭들은 사용되지 않는다, 그래서 읽을 블락을 찾을 필요가 없고 적은 쓰기만 수행된다.

n INSERT가 사용되지 않는다.

n 로드 시작전에 테이블과 INDEX를 LOCK하고 로드가 끝난뒤에 LOCK을 푼다.

n 데이터파일에 대해 MULTIBLOCK ASYNCHRONOUS I/O를 사용한다.

n DPL 도중에 프로세스들은 오라클의 버퍼캐시를 사용하는 대신 각자의 쓰기 I/O를 수행한다. 이는 다른 오라클 유저와의 경합을 줄인다.

n 적재되는 테이블이 비어있으면 사전정렬 옵션이 인덱스 생성의 정렬과 병합 단계를 제거한다. 인덱스는 데이터가 도착하는대로 채워진다.

n 인스턴스 장애에 대한 보호가 요구되지 않기에 리두 파일의 IO가 줄어든다. 다음 상황에서 로그파일에 쓰기 과정이 필요없다.

n DB가 NOARCHIVELOG 모드일 때

n SQL*Loader의 UNRECOVERABLE 문이 활성화되었을 때

n SQL에 NOLOGGING이 적용되었을 경우

Direct Path Load의 제약사항

n 클러스터테이블에는 적재될 수 없다.

n Virtual Private Database(VPD) 정책이 Insert에 활성화된 테이블에는 적재될 수 없다

n 적재되어야할 세그먼트에 트랜잭션이 추가될 수 없다.

n 부모 테이블과 자식 테이블을 함께 적재할 수 없다.

n BFILE 컬럼을 로딩할 수 없다.

n 적재 중에 CREATE SEQUENCE를 사용할 수 없다. DPL은 insert 문을 생성하지 않고 로드를 진행하기 때문에 next value를 불러올 수 없다.

Direct Path Load의 Single Partition 제약사항

n 글로벌 인덱스를 가지고 있는 파티션에는 적재할 수 없다,

n 트리거 활성화 불가하다

n 참조와 체크조건이 걸려있는 테이블의 파티션에 대한 적재를 할 수 없다.

언제 Direct Path Load를 사용해야 하나?

만약 위의 제약사항에 해당되지 않는다면 아래 상황에서 direct path를 사용하는 것이 좋다.

n 많은 데이터를 빠르게 적재하고 싶을 때,

n 데이터 로드시 성능을 최대로 써서 병렬로 진행하고 싶을 때

Direct Path Load 주의사항

n Check와 참조무결성 제약조건은 DPL시에 자동으로 적재시작 전에 비활성화되며, 적재 완료 후에 반드시 수동으로 활성화시켜야 한다. 이를 자동으로 활성화하게 하려면 REENABLE DISABLED_CONSTRAINTS 절을 컨트롤 파일에 기록해줘야 한다.

n Check 제약조건은 EVALUDATE CHECK_CONSTRAINTS를 컨트롤 파일에 기록하면 비활성화하지 않게 할 수 있다.

n INSERT 트리거는 적재 시작전에 비활성화된다. 그리고 적재 완료 후에 자동으로 활성화된다. 즉 그 동안 INSERT된 행들에 대한 트리거는 동작하지 않는다.

n 테이블의 DEFAULT는 DPL에선 사용할 수 없다. 이를 DEFAULT 적용하려면 DEAFULTIF 를 사용해야 한다. DEFAULTIF를 쓰지 않으면 NULL이 입력된다.

이 외에도 많은 내용이 더 있다.

http://docs.oracle.com/cd/E11882_01/server.112/e22490/ldr_modes.htm#SUTIL009

저작자표시 비영리

'Knowledge > Oracle' 카테고리의 다른 글

impdp, imp 주의사항 (0)	2016.03.20
Oracle Create Table 내부 절차 (0)	2015.10.21
undo 관련 좋은 링크 (0)	2015.09.15
null 관련 주의사항 (0)	2015.07.10
logon trail trigger on oracle (0)	2015.01.22

Posted by neo-orcl

,

undo 관련 좋은 링크

Knowledge/Oracle 2015. 9. 15. 20:10

개요

http://blog.oracle48.nl/oracle-database-undo-space-explained/

만약 active undo extents만 존재하여 더이상 해당 트랜잭션에 대한 undo 공간을 확보할 수 없을 경우 ORA-30036 unable to extend segment in Undo tablespace 에러를 받게 되고 자동으로 트랜잭션은 rollback되게 된다.

이 현상이 얼마나 일어났는지 확인은 아래 sql을 통해 가능하다.

SQL> select sum(NOSPACEERRCNT) from v$undostat;

만약 active상태의 extents이 필요한데 expired extents이 없다면 unexpired(undo_retention 기간이 아직 지나지 않은 상태) extents를 빼앗게(steal) 되는데 해당 count를 확인하는 법은 아래와 같다.

SQL> select sum(unxpstealcnt) from v$undostat;

※ steal 못하게 하려면 undo 테이블스페이스에 대해 retention guarantee 옵션을 주면 된다.

SQL> alter tablespace undotbs1 retention guarantee;

단.. 이러면 이제 unexpired extent를 뺏지 않기 때문에 조금 긴 dml의 경우 실패할 경우가 자주 생길 것이다. 이는 즉 ORA-30036과 ORA-01555 중 어느것을 선택하느냐이다.

만약 이렇게 steal된 unexpired extents에 대해 read consisteny read 수행하면 ORA-01555 snapshot too old 발생하고 이 역시 v$undostat에서 확인 가능하다.

SQL> select sum(ssolderrcnt) from v$undostat;

※ undo extent 크기와 퍼센트를 확인하는 쿼리. 단 free는 포함되지 않음

select status,
round(sum_bytes / (1024*1024), 0) as MB,
round((sum_bytes / undo_size) * 100, 0) as PERC
from
(
select status, sum(bytes) sum_bytes
from dba_undo_extents
group by status
),
(
select sum(a.bytes) undo_size
from dba_tablespaces c
    join v$tablespace b on b.name = c.tablespace_name
    join v$datafile a on a.ts# = b.ts#
where c.contents = 'UNDO'
    and c.status = 'ONLINE'
);

■ undo size tune

undo tablespace size가 고정되어있다면 오라클이 retention time을 undo 생성량에 따라 최적값을 찾게 된다. 하지만 undo tablespace 크기가 크다면 이를 크게 잡게 될 것이다.

이는 아래 쿼리로 확인 가능하다.

SQL> select tuned_undoretention from v$undostat;

이 경우 undo_retention parameter는 "최소 undo retention"이 된다.

undo retention autotune은 '_undo_autotune' hidden parameter를 설정해 끌 수도 있다.

■ undo_retention 값을 가장 긴 쿼리에 맞춰 설정하기

ORA-01555 에러를 피하기 위해 가장 긴 쿼리에 맞춰 undo retention을 설정하는 방법을 생각할 수 있다.

최근 7일간 가장 긴 쿼리의 길이(초)를 확인하는 방법

SQL> select max(maxquerylen) from v$undostat

저작자표시 비영리

'Knowledge > Oracle' 카테고리의 다른 글

Oracle Create Table 내부 절차 (0)	2015.10.21
SQL Loader Direct Load (0)	2015.10.05
null 관련 주의사항 (0)	2015.07.10
logon trail trigger on oracle (0)	2015.01.22
oracle 접속 ip 차단하기 (0)	2015.01.22

Posted by neo-orcl

,

null 관련 주의사항

Knowledge/Oracle 2015. 7. 10. 14:07

- number형 자료는 null 연산시 결과가 항상 null이다.

- 항상 null이 포함될 우려가 있다면 nvl을 사용한다.

-숫자형 컬럼은 0이나 1등 적절한 숫자로 치환한 후 연산한다.

-문자형 컬럼은 ' ' 이나 특정 문자로 치환하여 조건절에 이용한다.

-null 비교연산시 where A is null 혹은 where A is not null을 사용한다.

-절대로 A=null 혹은 A != null을 사용하지 말자. 에러가 안나고 엉뚱한 결과가 나온다.

저작자표시 비영리

'Knowledge > Oracle' 카테고리의 다른 글

SQL Loader Direct Load (0)	2015.10.05
undo 관련 좋은 링크 (0)	2015.09.15
logon trail trigger on oracle (0)	2015.01.22
oracle 접속 ip 차단하기 (0)	2015.01.22
dg4obbc 제약사항 (0)	2014.12.31

Posted by neo-orcl

,

logon trail trigger on oracle

Knowledge/Oracle 2015. 1. 22. 10:56

비밀번호를 변경하고 싶은데 평소에 이 유저에 접속하는 다른 머신을 확인하고 싶을 수가 있다.

sys나 system으로 진행

1. 테이블 생성

create table log_audit

(username varchar2(30),

osuser varchar2(30),

machine varchar2(64),

logon_time date,
session_id_1 number,

session_id_2 number)

tablespace users;

2. 트리거 생성

CREATE or replace TRIGGER logonauditing
AFTER LOGON ON 유저명.SCHEMA
DECLARE
username       VARCHAR2(30);
machinename         VARCHAR2(64);
osuserid            VARCHAR2(30);
session_id_part1    NUMBER;
session_id_part2    NUMBER;
CURSOR c1 IS
    SELECT username, osuser, machine, sid, serial#
      FROM v$session WHERE audsid = userenv( 'sessionid' );
BEGIN
OPEN c1;
FETCH c1 INTO username, osuserid, machinename, session_id_part1, session_id_part2;
INSERT INTO r3log_audit(username, osuser, machine, logon_time, session_id_1, session_id_2)
          VALUES ( username, osuserid, machinename, sysdate,session_id_part1, session_id_part2);
CLOSE c1;
COMMIT;
END;
/

저작자표시 비영리

'Knowledge > Oracle' 카테고리의 다른 글

undo 관련 좋은 링크 (0)	2015.09.15
null 관련 주의사항 (0)	2015.07.10
oracle 접속 ip 차단하기 (0)	2015.01.22
dg4obbc 제약사항 (0)	2014.12.31
EZCONNECT 사용방법 (0)	2014.05.30

Posted by neo-orcl

,

Neo-Oracle Home

'Knowledge/Oracle'에 해당되는 글 30건

Local Partition INDEX Test

'Knowledge > Oracle' 카테고리의 다른 글

Oracle DBA와 개발자의 필독서

'Knowledge > Oracle' 카테고리의 다른 글

Data guard와 Active data guard license

'Knowledge > Oracle' 카테고리의 다른 글

sqlplus 접속 hang시에 접속 방법

'Knowledge > Oracle' 카테고리의 다른 글

impdp, imp 주의사항

'Knowledge > Oracle' 카테고리의 다른 글

Oracle Create Table 내부 절차

'Knowledge > Oracle' 카테고리의 다른 글

SQL Loader Direct Load

'Knowledge > Oracle' 카테고리의 다른 글

undo 관련 좋은 링크

'Knowledge > Oracle' 카테고리의 다른 글

null 관련 주의사항

'Knowledge > Oracle' 카테고리의 다른 글

logon trail trigger on oracle

'Knowledge > Oracle' 카테고리의 다른 글

카테고리

태그목록

최근에 올라온 글

최근에 달린 댓글

최근에 받은 트랙백

글 보관함

달력

링크

티스토리툴바