'OraclE' 카테고리의 글 목록 (9 Page)

적용 대상 : 11.1.0.6 to 11.2.0.2 [Release 11.1 to 11.2]

오라클 11g 데이터베이스에는 세 가지 자동화된 유지 관리 작업이 있다 :

자동 최적기 통계 수집(Automatic Optimizer Statistics Collection)

통계가 없거나 오래된 통계를 가지고 있는 데이터베이스 안의 모든 스키마 객체에 대해 최적기 통계를 수집한다.이 작업에 의해 생성되어지는 통계는 SQL 실행 시 성능을 향상시키기 위하여 SQL 쿼리 최적기에 의해서 사용되어 진다.

자동 세그먼트 권고자 (Automatic Segment Advisor)

회수 가능한 공간을 가지는 세그먼트를 구별하고, 해당 세그먼트를 어떻게 조각모음 할 건지에 대한 권장 사항을 만든다. 최신의 권장 사항을 확보하거나 자동 세그먼트 권고자가 회수 가능한 공간을 검토 하지 않은 세그먼트의 권장 사항을 확보하기 위하여 수동으로 세그먼트 권고자를 실행할 수 있다.

자동 SQL 튜닝 권고자(Automatic SQL Tuning Advisor)

높은 부하를 가지는 SQL 문장의 성능을 검사하고, 그 문장을 어떻게 튜닝 할 것인지에 대한 권장 사항을 만든다. SQL 프로파일 권장 사항을 자동으로 구현하기 위하여 이 권고자를 구성할 수 있다.

Oracle10g에서 이 작업들은 별도의 작업으로 생성 되었으며 DBA_SCHEDULER_JOBS.JOB_NAME에서 볼 수 있었다.

SQL> select owner,job_name,schedule_name, schedule_type, enabled from dba_scheduler_jobs;

OWNER           JOB_NAME                       SCHEDULE_NAME                  SCHEDULE_TYP ENABL
--------------- ------------------------------ ------------------------------ ------------ -----
SYS             PURGE_LOG                      DAILY_PURGE_SCHEDULE           NAMED        TRUE
SYS             FGR$AUTOPURGE_JOB                                             CALENDAR     FALSE
SYS             GATHER_STATS_JOB               MAINTENANCE_WINDOW_GROUP       WINDOW_GROUP TRUE
SYS             AUTO_SPACE_ADVISOR_JOB         MAINTENANCE_WINDOW_GROUP       WINDOW_GROUP TRUE

GATHER_STATS_JOB, AUTO_SPACE_ADVISOR_JOB 가 enabled인것을 확인 할수 있다.

오라클 11g에서 변경되었다. 관련된 뷰는 DBA_AUTOTASK_WINDOW_CLIENTS이다.
이제 이 작업들은 시스템에서 실제로 실행 되고 난 후 생성된 이름을 DBA_SCHEDULER_JOBS에서 볼 수 있다.

SQL> desc DBA_AUTOTASK_WINDOW_CLIENTS
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
WINDOW_NAME                               NOT NULL VARCHAR2(30)
WINDOW_NEXT_TIME                                   TIMESTAMP(6) WITH TIME ZONE
WINDOW_ACTIVE                                      VARCHAR2(5)
AUTOTASK_STATUS                                    VARCHAR2(8)
OPTIMIZER_STATS                                    VARCHAR2(8)
SEGMENT_ADVISOR                                    VARCHAR2(8)
SQL_TUNE_ADVISOR                                   VARCHAR2(8)
HEALTH_MONITOR                                     VARCHAR2(8)

OPTIMIZER_STATS가 Status of optimizer statistics gathering 을 나타낸다.

select * from DBA_AUTOTASK_WINDOW_CLIENTS;

WINDOW_NAME          WINDOW_NEXT_TIME                         WINDOW_ACT
-------------------- ---------------------------------------- ----------
AUTOTASK_STATUS OPTIMIZER_STATS SEGMENT_ADVISOR SQL_TUNE_ADVISOR
---------------- ---------------- ---------------- ----------------
HEALTH_MONITOR
----------------
MONDAY_WINDOW        17-FEB-14 10.00.00.000000 PM ASIA/SEOUL FALSE
ENABLED          ENABLED          ENABLED          ENABLED
DISABLED

TUESDAY_WINDOW 18-FEB-14 10.00.00.000000 PM ASIA/SEOUL FALSE
ENABLED ENABLED ENABLED ENABLED
DISABLED

WEDNESDAY_WINDOW 12-FEB-14 10.00.00.000000 PM ASIA/SEOUL FALSE
ENABLED ENABLED ENABLED ENABLED
DISABLED

THURSDAY_WINDOW 13-FEB-14 10.00.00.000000 PM ASIA/SEOUL FALSE
ENABLED ENABLED ENABLED ENABLED
DISABLED

FRIDAY_WINDOW 14-FEB-14 10.00.00.000000 PM ASIA/SEOUL FALSE
ENABLED ENABLED ENABLED ENABLED
DISABLED

SATURDAY_WINDOW 15-FEB-14 06.00.00.000000 AM ASIA/SEOUL FALSE
ENABLED ENABLED ENABLED ENABLED
DISABLED

SUNDAY_WINDOW 16-FEB-14 06.00.00.000000 AM ASIA/SEOUL FALSE
ENABLED ENABLED ENABLED ENABLED
DISABLED

7 rows selected.

모든 윈도들의 모든 자동화된 유지 관리 작업을 설정하거나 해제하려면 인수 없이 설정 또는 해제 프로시저를 호출한다.

SQL> execute DBMS_AUTO_TASK_ADMIN.DISABLE;

특정한 유지 관리 작업을 해제하기 위하여 다음과 같이 해제 프로시저를 사용한다 :

SQL> BEGIN
   dbms_auto_task_admin.disable(
   client_name => 'sql tuning advisor',
   operation => NULL,
   window_name => NULL);
     END;
    /

특정한 유지 관리 작업을 다시 설정하기 위하여 다음과 같이 설정 프로시저를 사용한다 :

SQL> BEGIN
       dbms_auto_task_admin.enable(
       client_name => 'sql tuning advisor',
       operation => NULL,
       window_name => NULL);
     END;
     /

client_name 인수에 사용할 작업 이름은 DBA_AUTOTASK_CLIENT 데이터베이스 딕셔너리 뷰 안에 나열되어 있다.

예제：

auto optimizer stats collection
auto space advisor
sql tuning advisor

또 다른 차이점은 사전에 정의된 스케줄러 윈도다 :

Oracle10g : WEEKNIGHT_WINDOW and WEEKEND_WINDOW
Oracle11g : MONDAY_WINDOW .... SUNDAY_WINDOW.

WEEKNIGHT_WINDOW와 WEEKEND_WINDOW는 이전 버전과의 호환성을 위해 아직 존재한다.

--10g SQL> select window_name, repeat_interval, duration
2 from dba_scheduler_windows;

WINDOW_NAME REPEAT_INTERVAL
------------------------------ --------------------------------------------------------------------------------
DURATION
---------------------------------------------------------------------------
WEEKNIGHT_WINDOW freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0
+000 08:00:00

WEEKEND_WINDOW freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0
+002 00:00:00

--11g SQL> select window_name, repeat_interval, duration
2 from dba_scheduler_windows;

WINDOW_NAME REPEAT_INTERVAL
-------------------- --------------------------------------------------------------------------------
DURATION
---------------------------------------------------------------------------
MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0
+000 04:00:00

TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0
+000 04:00:00

WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0
+000 04:00:00

THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0
+000 04:00:00

FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0
+000 04:00:00

SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0
+000 20:00:00

SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0
+000 20:00:00

WEEKNIGHT_WINDOW freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0
+000 08:00:00

WEEKEND_WINDOW freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0
+002 00:00:00

9 rows selected.

윈도가 열릴 때 지속 시간은 11g에서 변경되었다. 월요일 - 금요일은 오후 10시에서 오전 2시까지이며 토요일 - 일요일은 오전 6시에서 오전 2시까지다.

DBMS_SCHEDULER.SET_ATTRIBUTE 프로시저를 사용하여 데이터베이스 환경에 적절한 시간으로 사전에 정의된 유지 관리 윈도를 조절할 수 있다.

예제 : 다음 스크립트는 WEEKNIGHT_WINDOW를 자정부터 모든 평일 오전 8시로 변경하도록 한다. (윈도 기간은 8시간으로 변경되지 않는다) :

EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE(
'WEEKNIGHT_WINDOW',
'repeat_interval',
'freq=daily;byday=MON, TUE, WED, THU, FRI;byhour=8;byminute=0;bysecond=0');

각각의 평일 윈도는 DEFAULT_MAINTENANCE_PLAN이라는 사전에 정의된 리소스 계획을 가지고 있으며 관련 윈도가 열릴 때 활성화 될 것이다. 이것은 10g와 11g 간의 또 다른 차이점이다.

SQL> select window_name, resource_plan from dba_scheduler_windows;

WINDOW_NAME                    RESOURCE_PLAN
------------------------------ ------------------------------
MONDAY_WINDOW                  DEFAULT_MAINTENANCE_PLAN
TUESDAY_WINDOW                 DEFAULT_MAINTENANCE_PLAN
WEDNESDAY_WINDOW               DEFAULT_MAINTENANCE_PLAN
THURSDAY_WINDOW                DEFAULT_MAINTENANCE_PLAN
FRIDAY_WINDOW                  DEFAULT_MAINTENANCE_PLAN
SATURDAY_WINDOW                DEFAULT_MAINTENANCE_PLAN
SUNDAY_WINDOW                  DEFAULT_MAINTENANCE_PLAN
WEEKNIGHT_WINDOW
WEEKEND_WINDOW

9 rows selected.

SQL> select * from dba_rsrc_plans where plan='DEFAULT_MAINTENANCE_PLAN'
PLAN_ID PLAN NUM_PLAN_DIRECTIVES
---------- ------------------------------ -------------------
CPU_METHOD MGMT_METHOD
------------------------------ ------------------------------
ACTIVE_SESS_POOL_MTH PARALLEL_DEGREE_LIMIT_MTH
------------------------------ ------------------------------
QUEUEING_MTH SUB
------------------------------ ---
COMMENTS
--------------------------------------------------------------------------------
STATUS MAN
------------------------------ ---
11187 DEFAULT_MAINTENANCE_PLAN 4
EMPHASIS EMPHASIS
ACTIVE_SESS_POOL_ABSOLUTE PARALLEL_DEGREE_LIMIT_ABSOLUTE
FIFO_TIMEOUT NO
Default plan for maintenance windows that prioritizes SYS_GROUP operations and a
llocates the remaining 5% to diagnostic operations and 25% to automated maintena
nce operations.
YES

SQL> select * from DBA_RSRC_PLAN_DIRECTIVES where plan='DEFAULT_MAINTENANCE_PLAN';

PLAN GROUP_OR_SUBPLAN TYPE
------------------------------ ------------------------------ --------------
CPU_P1 CPU_P2 CPU_P3 CPU_P4 CPU_P5 CPU_P6 CPU_P7
---------- ---------- ---------- ---------- ---------- ---------- ----------
CPU_P8 MGMT_P1 MGMT_P2 MGMT_P3 MGMT_P4 MGMT_P5 MGMT_P6
---------- ---------- ---------- ---------- ---------- ---------- ----------
MGMT_P7 MGMT_P8 ACTIVE_SESS_POOL_P1 QUEUEING_P1 PARALLEL_DEGREE_LIMIT_P1
---------- ---------- ------------------- ----------- ------------------------
SWITCH_GROUP SWITC SWITCH_TIME SWITCH_IO_MEGABYTES
------------------------------ ----- ----------- -------------------
SWITCH_IO_REQS SWITC MAX_EST_EXEC_TIME UNDO_POOL MAX_IDLE_TIME
-------------- ----- ----------------- ---------- -------------
MAX_IDLE_BLOCKER_TIME SWITCH_TIME_IN_CALL
--------------------- -------------------
COMMENTS
--------------------------------------------------------------------------------
STATUS MAN
------------------------------ ---
DEFAULT_MAINTENANCE_PLAN SYS_GROUP CONSUMER_GROUP
100 0 0 0 0 0 0
0 100 0 0 0 0 0
0 0
FALSE
FALSE

Directive for system operations
NO

DEFAULT_MAINTENANCE_PLAN OTHER_GROUPS CONSUMER_GROUP
0 70 0 0 0 0 0
0 0 70 0 0 0 0
0 0
FALSE
FALSE

Directive for all other operations
NO

DEFAULT_MAINTENANCE_PLAN ORA$AUTOTASK_SUB_PLAN PLAN
0 25 0 0 0 0 0
0 0 25 0 0 0 0
0 0
FALSE
FALSE

Directive for automated maintenance tasks
NO

DEFAULT_MAINTENANCE_PLAN ORA$DIAGNOSTICS CONSUMER_GROUP
0 5 0 0 0 0 0
0 0 5 0 0 0 0
0 0
FALSE
FALSE

Directive for automated diagnostic tasks
NO

* 11.2.0.3 에서는 SYS_GROUP 의 CPU_P1, MGMT_P1이 75로 변경 됨

10g와 11g의 변경사항 요약 :

제목	10g	11g
작업	DBA_SCHEDULER_JOBS에서 별도의 작업	AUTOTASKS는 접두사 'ORA$AT'라는 이름을 가지며 실행되었을 때 작업처럼 볼 수 있다.
유지 관리 윈도	2개의 윈도, 평일 밤과 주말	매일 자신의 윈도를 가진다.
자원 관리자(Resource manager)	기본으로 설정되지 않음	모든 평일 윈도를 위한 사전 정의된 자원 계획

관련된 뷰：

DBA_AUTOTASK_CLIENT
DBA_AUTOTASK_CLIENT_HISTORY
DBA_AUTOTASK_CLIENT_JOB
DBA_AUTOTASK_JOB_HISTORY
DBA_AUTOTASK_OPERATION
DBA_AUTOTASK_SCHEDULE
DBA_AUTOTASK_TASK
DBA_AUTOTASK_WINDOW_CLIENTS
DBA_AUTOTASK_WINDOW_HISTORY

'OraclE' 카테고리의 다른 글

rman 백업 확인 (0)	2014.03.27
[펌] DBMS_XPLAN.DISPLAY_CURSOR (0)	2014.03.24
Temporary Tablespace Group(10g) (0)	2013.11.20
db link 사용시 lob타입 테이블 문제 (0)	2012.08.03
append 힌트의 효용성 (0)	2012.06.26

Temporary Tablespace Group(10g)

Oracle Database 10g

Temporary Tablespace Group(10g)
===============================

1. 개념

Temporary Tablespace 들을 한 곳으로 모아 놓고 관리한다는 개념이다.

2. 특징

1) 적어도 한 개의 Temporary tablespace를 포함한다. 얼마나 많은 tablespace를 포함할 수 있는가에 대한 제한은 없다.

2) 일반적인 Tablespace와 동일한 성질을 갖는다. 다른 Tablespace 이름과 혼용할 수 없다.

3) Temporary Tablespace Group을 직접 생성할 수 있는 방법은 없다. 단지, 최초의 Temporary tablespace가 그 Group에 할당됨을 표현할 때 자동적으로 생성되고, 마지막 Temporary tablespace가 제거될 때 삭제된다.

3. 장점

1) 사용자가 동시에 다른 세션을 통해서 여러 개의 Temporary tablespace를 사용할 수 있다.

2) Parallel Operation의 slave process들이 다중 Temporary tablespace를 사용할 수 있다.

3) 데이터베이스 수준에서 다중 Default temporary tablespace를 지정할 수 있다.
그리하여, DBA는 하나 이상의 Default temporary tablespace를 지정할 수 있고, 하나의 SQL operation은 sorting 작업을 할 때 하나 이상의 temporary tablespace를 사용할 수 있다. 이러한 기능은 특정 operation이 temporary tablespace의 공간을 모두 소진하는 상황을 미연에 방지할 수 있다.

4. 사용 예

1) 생성 예

SQL> SELECT group_name, tablespace_name
2 FROM DBA_TABLESPACE_GROUPS;
no rows selected

SQL> select file_name, bytes from dba_temp_files;
FILE_NAME         BYTES
------------------------------------------------   ---------
C:ORACLEPRODUCT10.2.0ORADATASTBYTEMP01.DBF   20971520

SQL> CREATE TEMPORARY TABLESPACE temp1
2 TEMPFILE 'C:ORACLEPRODUCT10.2.0ORADATASTBYTEMP101.DBF' SIZE 10M
3 TABLESPACE GROUP group1;
Tablespace created.

SQL> select file_name, bytes from dba_temp_files;
FILE_NAME         BYTES
------------------------------------------------   ---------
C:ORACLEPRODUCT10.2.0ORADATASTBYTEMP01.DBF   20971520
C:ORACLEPRODUCT10.2.0ORADATASTBYTEMP101.DBF 10485760

SQL> SELECT group_name, tablespace_name
2 FROM DBA_TABLESPACE_GROUPS;
GROUP_NAME                     TABLESPACE_NAME
------------------------------ ---------------
GROUP1                         TEMP1

SQL> CREATE TEMPORARY TABLESPACE temp2
2 TEMPFILE 'C:ORACLEPRODUCT10.2.0ORADATASTBYTEMP201.DBF' SIZE 10M
3 TABLESPACE GROUP group2;
Tablespace created.

SQL> select file_name, bytes from dba_temp_files;
FILE_NAME         BYTES
------------------------------------------------   ---------
C:ORACLEPRODUCT10.2.0ORADATASTBYTEMP01.DBF   20971520
C:ORACLEPRODUCT10.2.0ORADATASTBYTEMP101.DBF 10485760
C:ORACLEPRODUCT10.2.0ORADATASTBYTEMP201.DBF 10485760

SQL> SELECT group_name, tablespace_name
2 FROM DBA_TABLESPACE_GROUPS;
GROUP_NAME                     TABLESPACE_NAME
------------------------------ ---------------
GROUP1                         TEMP1
GROUP2                         TEMP2

SQL> CREATE TEMPORARY TABLESPACE temp3
2 TEMPFILE 'C:ORACLEPRODUCT10.2.0ORADATASTBYTEMP301.DBF' SIZE 10M
3 TABLESPACE GROUP '';
Tablespace created.

SQL> SELECT group_name, tablespace_name
2 FROM DBA_TABLESPACE_GROUPS;
GROUP_NAME                     TABLESPACE_NAME
------------------------------ ---------------
GROUP1                         TEMP1
GROUP2                         TEMP2
주) Group 이름을 ''로 지정하는 것은 group에 포함시키지 않겠다는 의미이다.
즉, CREATE TEMPORARY TABLESPACE temp3 TEMPFILE 'C:ORACLEPRODUCT10.2.0ORADATASTBYTEMP301.DBF' SIZE 10M ;
명령과 동일하다.

2) Group을 변경, 사용자에게 할당하는 예

SQL> ALTER TABLESPACE temp3 TABLESPACE GROUP group2;
Tablespace altered.

SQL> SELECT group_name, tablespace_name
2 FROM DBA_TABLESPACE_GROUPS;
GROUP_NAME                     TABLESPACE_NAME
------------------------------ ---------------
GROUP1                         TEMP1
GROUP2                         TEMP2
GROUP2                         TEMP3

SQL> ALTER TABLESPACE temp2 TABLESPACE GROUP '';
Tablespace altered.

SQL> SELECT group_name, tablespace_name
2 FROM DBA_TABLESPACE_GROUPS;
GROUP_NAME                     TABLESPACE_NAME
------------------------------ ---------------
GROUP1                         TEMP1
GROUP2                         TEMP3

SQL> ALTER TABLESPACE temp1 TABLESPACE GROUP group2;
Tablespace altered.

SQL> SELECT group_name, tablespace_name
2 FROM DBA_TABLESPACE_GROUPS;
GROUP_NAME                     TABLESPACE_NAME
------------------------------ ---------------------
GROUP2                         TEMP1
GROUP2                         TEMP3

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE group2;
Database altered.

SQL> CREATE USER sun IDENTIFIED BY sun
2 DEFAULT TABLESPACE users
3 TEMPORARY TABLESPACE group2;
User created.

SQL> select username, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE
2 from dba_users
3 where username = 'SUN';
USERNAME     DEFAULT_TABLESPACE      TEMPORARY_TABLESPACE
----------- -------------------     ---------------------
SUN          USERS                   GROUP2

주) 일반적인 Temporary tablespace가 아니라, Temporary tablespace Group이 지정된 것을 확인할 수 있다.

<DBA Session>
SQL> grant connect, resource, dba to SUN;
Grant succeeded.

SQL> select username, session_num, tablespace from v$sort_usage;
no rows selected

<SUN Session 1>
SQL> select a.table_name, b.table_name from dba_tables a, dba_tables b;

<SUN Session 2> 동시에
SQL> select a.table_name, b.table_name from dba_tables a, dba_tables b;

<DBA Session>
SQL> select username, session_num, tablespace from v$sort_usage;
USERNAME                       SESSION_NUM TABLESPACE
------------------------------ ----------- ----------
SUN                                     19 TEMP1
SUN                                     78 TEMP3
주) SUN 유저가 두개의 서로 다른 temporary tablespaces에서 Sort를 진행중이다.

[출처] Temporary Tablespace Group(10g)|작성자 예민

추가 테스트 결과 (11gR2)

	hole temp tablespace	temp tablespace group
temp 파일 개수 / TBS 개수	10GB * 12개 / 1개	10GB * 1개 / 12개
71GB 인덱스 생성 시간( parallel 12)	00:19:51.66	00:18:11.42

사이즈가 많이 크지는 않아서 갭은 별로 없지만 성능 향상이 있습니다.

구글 검색 결과 temp tablespace group 사용시에

Reads 66%, Writes 29%의 성능 향상을 보인다는 테스트 결과도 있습니다.

'OraclE' 카테고리의 다른 글

[펌] DBMS_XPLAN.DISPLAY_CURSOR (0)	2014.03.24
11g 스케줄러 자동작업 dbms_auto_task_admin (0)	2014.02.12
db link 사용시 lob타입 테이블 문제 (0)	2012.08.03
append 힌트의 효용성 (0)	2012.06.26
datapump (0)	2012.06.20

Ora-22992 has a workaround in 10gR2 [ID 436707.1]

수정 날짜:2012. 1. 30유형:PROBLEM상태:PUBLISHED우선순위:3

In this Document
  Symptoms
  Changes
  Cause
  Solution
  References

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 and later [Release: 10.2 and later ]
Information in this document applies to any platform.

Symptoms

When trying to select from a remote table, getting 'ORA-22992: cannot use LOB locators selected from remote tables.' as following:

1. From the remote database:
create user cris identified by cris;
grant connect, resource to cris;
conn cris/cris
create table test(id number, obj clob);
insert into test values(1,'sdfsdfsfd');
insert into test values(2, 'sdfsdfsfdvfgdfvgdfvdf');
commit;

2 From the local database:

create user cris identified by cris;
grant connect, resource, create database link to cris;
conn cris/cris
create database link torem using 'identification of the remote database in the tnsnames.ora';

set serveroutput on

declare
my_ad clob;
BEGIN
SELECT obj INTO my_ad FROM test@torem where id=1;
dbms_output.put_line(my_ad);
END;
/

*
ERROR at line 1:
ORA-22992: cannot use LOB locators selected from remote tables
ORA-06512: at line 4

Changes

Cause

Trying to select LOB column from a table at a remote site using dblink.

Solution

The following link to documentation states that you can successfully select form lob objects through dblink All what you have to do is to receive the LOB objects into variables defined as CHAR or RAW.

Oracle� Database Application Developer's Guide - Large Objects 10g Release 2 (10.2)
Part Number B14249-01

Restrictions Removed in Oracle Database 10g Release 2

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14249/adlob_what.htm#CHDGGBCG

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14249/adlob_data_interface.htm#CACIFCJF

Here are the steps of how to do that:

1. Selecting a CLOB object through the dblink:

1.1 From the remote database:

create user cris identified by cris;
grant connect, resource to cris;
conn cris/cris
create table test(id number, obj clob);
insert into test values(1,'sdfsdfsfd');
insert into test values(2, 'sdfsdfsfdvfgdfvgdfvdf');
commit;

1.2 From the local database:

create user cris identified by cris;
grant connect, resource, create database link to cris;
conn cris/cris
create database link torem using 'identification of the remote database in the tnsnames.ora';

set serveroutput on

declare
my_ad varchar(6000);
BEGIN
SELECT obj INTO my_ad FROM test@torem where id=1;
dbms_output.put_line(my_ad);
END;
/

2. Selecting a BLOB object through the dblink:

2.1 From the remote database:

create user cris identified by cris;
grant connect, resource to cris;
conn cris/cris
create table test2(id number, obj blob);
insert into test2 values(1,empty_blob());
insert into test2 values(2,empty_blob());
commit;

2.2 From the local database:

create user cris identified by cris;
grant connect, resource, create database link to cris;
conn cris/cris
create database link torem using 'identification of the remote database in the tnsnames.ora';

declare
my_ad raw(50);
BEGIN
SELECT obj INTO my_ad FROM test2@torem where id=1;
END;
/

References

NOTE:158924.1 - Referencing DBMS_LOB.COPY() Against Remote Table Fails With ORA-22992

'OraclE' 카테고리의 다른 글

11g 스케줄러 자동작업 dbms_auto_task_admin (0)	2014.02.12
Temporary Tablespace Group(10g) (0)	2013.11.20
append 힌트의 효용성 (0)	2012.06.26
datapump (0)	2012.06.20
DBMS_STATS (0)	2012.06.13

append 힌트의 효용성에 대한 논란이 잠시 있어 막간을 이용, 확인차 테스트해봤습니다..

아래 링크를 차례대로 읽어보세요...

i) NOLOGGING이 아닌 테이블에는 INSERT /*+ APPEND */ 효과가 없다?
: "APPEND 힌트의 진실과 거짓" --> http://blog.naver.com/addibuddi/22476554

ii) http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5280714813869

위 사이트에서 Kyte는 다음과 같이 정리함..

Table Mode    Insert Mode     ArchiveLog mode      result
-----------   -------------   -----------------    ----------
LOGGING       APPEND          ARCHIVE LOG          redo generated
NOLOGGING     APPEND          ARCHIVE LOG          no redo
LOGGING       no append       ""                   redo generated
NOLOGGING     no append       ""                   redo generated
LOGGING       APPEND          noarchive log mode   no redo
NOLOGGING     APPEND          noarchive log mode   no redo
LOGGING       no append       noarchive log mode   redo generated
NOLOGGING     no append       noarchive log mode   redo generated

이제 테스트를 시작합니다..

############################################
### 1. logging 테이블에 conventional INSERT
############################################

SQL> conn scott
Enter password:
Connected.

SQL> create table tab3 logging
as select * from all_objects
where 1=2;

Table created.

SQL> insert into tab3
select * from all_objects
;

36013 rows created.

SQL> commit;

Commit complete.

SQL> select * from v$sesstat
where sid = (select sid from v$mystat where rownum = 1)
and STATISTIC# = (
select STATISTIC# from v$statname
where name = 'redo entries') ;

SID STATISTIC# VALUE
---------- ---------- ----------
282 133 3420 <====

SQL> explain plan for insert into tab3
2 select * from tab3 ;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1925526863

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | INSERT STATEMENT |      | 37070 | 4633K|   129 (14)| 00:00:02 |    <====
|   1 | TABLE ACCESS FULL| TAB3 | 37070 | 4633K|   129 (14)| 00:00:02 |
--------------------------------------------------------------------------

#######################################
### 2. logging 테이블에 Direct Insert
#######################################

SQL> conn scott
Enter password:
Connected.

SQL> create table tab1 logging
as select * from all_objects
where 1=2;

Table created.

SQL> insert /*+ append */ into tab1
select * from all_objects
;

36011 rows created.

SQL> commit;

Commit complete.

SQL> select * from v$sesstat
where sid = (select sid from v$mystat where rownum = 1)
and STATISTIC# = (
select STATISTIC# from v$statname
where name = 'redo entries') ;

SID STATISTIC# VALUE
---------- ---------- ----------
240 133 866 <====

SQL> explain plan for insert /*+ append */ into tab1
2 select * from tab1;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1481117511

---------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | INSERT STATEMENT   |      | 32454 | 4056K|   126 (12)| 00:00:02 |
|   1 | LOAD AS SELECT    | TAB1 |       |       |            |          |    <====
|   2 |   TABLE ACCESS FULL| TAB1 | 32454 | 4056K|   126 (12)| 00:00:02 |
---------------------------------------------------------------------------

#######################################
### 3. nologging 테이블에 Direct Insert
#######################################

SQL> conn scott
Enter password:
Connected.

SQL> create table tab2 nologging
as select * from all_objects
where 1=2;

Table created.

SQL> insert /*+ append */ into tab2
select * from all_objects
;

36012 rows created.

SQL> commit;

Commit complete.

SQL> select * from v$sesstat
where sid = (select sid from v$mystat where rownum = 1)
and STATISTIC# = (
select STATISTIC# from v$statname
where name = 'redo entries') ;

SID STATISTIC# VALUE
---------- ---------- ----------
118 133 363 <====

SQL> explain plan for insert /*+ append */ into tab2
2 select * from tab2;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4264119061

---------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | INSERT STATEMENT   |      | 36080 | 4510K|   128 (14)| 00:00:02 |
|   1 | LOAD AS SELECT    | TAB2 |       |       |            |          |    <====
|   2 |   TABLE ACCESS FULL| TAB2 | 36080 | 4510K|   128 (14)| 00:00:02 |
---------------------------------------------------------------------------

** 테스트 결과, logging 속성의 테이블이더라도 APPEND 힌트에 의한 REDO감소 효과는 큰것으로 나타남.

3420 > 866 > 363

이정도의 차이라면 Production환경에서는 recovery를 포기하고 테이블까지 nologging으로 해야 한다는 우를 범하지 않고서라도 충분히 append 힌트의 효용성을 누릴수 있을 것이다.

[출처] http://blog.naver.com/dbmedic?Redirect=Log&logNo=50040322413

'OraclE' 카테고리의 다른 글

Temporary Tablespace Group(10g) (0)	2013.11.20
db link 사용시 lob타입 테이블 문제 (0)	2012.08.03
datapump (0)	2012.06.20
DBMS_STATS (0)	2012.06.13
DBMS_STATS 패키지 (0)	2012.06.13

JJON's StorY

OraclE

11g 스케줄러 자동작업 dbms_auto_task_admin

10g와 11g의 변경사항 요약 :

관련된 뷰：

'OraclE' 카테고리의 다른 글

Temporary Tablespace Group(10g)

'OraclE' 카테고리의 다른 글

db link 사용시 lob타입 테이블 문제

Applies to:

Symptoms

Changes

Cause

Solution

References

'OraclE' 카테고리의 다른 글

append 힌트의 효용성

'OraclE' 카테고리의 다른 글

+ Recent posts

티스토리툴바