### 서브파티션 테이블에서 DBA_PART_TABLES.DEF_SUBPARTITION_COUNT 가 정상적으로 보이지 않는 경우의 해결 방법
아래 스크립트는 저희가 보통 오렌지 등 툴로 직접 스크립트를 추출하게 되면 아래 처럼 보일겁니다.
이렇게 보이면 정상적으로 파티션에 대한 정보를 모두 보이므로 스크립트 상에 문제가 없는것을 확인 할 수 있고
아마 아래 스크립트로 생성을 하기도 할겁니다.
여기서 문제가 하나 발견 되었습니다.
DBA_PART_TABLES.DEF_SUBPARTITION_COUNT 가 정상적으로 보이지 않는 것을 확인 했습니다.
1. 테스트 스크립트 ( 오렌지등 툴로 추출한 스크립트..)
CREATE TABLE SHWOO.TKT_BKTSEAT_T
(
CO_CD VARCHAR2 (4) NOT NULL,
BKT_SALE_NO VARCHAR2 (16) NOT NULL,
SEQ NUMBER (6) NOT NULL,
REG_K_ID VARCHAR2 (14),
REG_DT DATE,
UPD_K_ID VARCHAR2 (14),
UPD_DT DATE
)
TABLESPACE USERS
PCTFREE 10
INITRANS 10
MAXTRANS 255
STORAGE
(
BUFFER_POOL DEFAULT
)
PARTITION BY RANGE (CO_CD, BKT_SALE_NO)
SUBPARTITION BY HASH (BKT_SALE_NO)
(
PARTITION PRH_PRM_201010 VALUES LESS THAN ('A416', '201011')
TABLESPACE USERS
PCTFREE 10
INITRANS 10
MAXTRANS 255
STORAGE
(
INITIAL 2097152
NEXT 2097152
MINEXTENTS 1
MAXEXTENTS UNLIMITED
)
LOGGING
NOCOMPRESS
(
SUBPARTITION PRH_PRM_201010_S1
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201010_S2
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201010_S3
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201010_S4
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201010_S5
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201010_S6
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201010_S7
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201010_S8
TABLESPACE USERS
)
,
PARTITION PRH_PRM_201011 VALUES LESS THAN ('A416', '201012')
TABLESPACE USERS
PCTFREE 10
INITRANS 10
MAXTRANS 255
STORAGE
(
INITIAL 2097152
NEXT 2097152
MINEXTENTS 1
MAXEXTENTS UNLIMITED
)
LOGGING
NOCOMPRESS
(
SUBPARTITION PRH_PRM_201011_S1
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201011_S2
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201011_S3
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201011_S4
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201011_S5
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201011_S6
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201011_S7
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201011_S8
TABLESPACE USERS
)
,
PARTITION PRH_PRM_201012 VALUES LESS THAN ('A416', '201101')
TABLESPACE USERS
PCTFREE 10
INITRANS 10
MAXTRANS 255
STORAGE
(
INITIAL 2097152
NEXT 2097152
MINEXTENTS 1
MAXEXTENTS UNLIMITED
)
LOGGING
NOCOMPRESS
(
SUBPARTITION PRH_PRM_201012_S1
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201012_S2
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201012_S3
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201012_S4
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201012_S5
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201012_S6
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201012_S7
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201012_S8
TABLESPACE USERS
)
)
ENABLE ROW MOVEMENT ;
-- 생성 완료..
2. DBA_PART_TABLES 딕셔너리 확인 결과 이상 현상 발견..
( DEF_SUBPARTITION_COUNT 값이 1로 표시 )
set line 200
SELECT OWNER, TABLE_NAME, PARTITIONING_TYPE, SUBPARTITIONING_TYPE, PARTITION_COUNT, DEF_SUBPARTITION_COUNT
FROM DBA_PART_TABLES
WHERE TABLE_NAME='TKT_BKTSEAT_T';
OWNER TABLE_NAME PARTITION SUBPARTIT PARTITION_COUNT DEF_SUBPARTITION_COUNT
------------------------------ ------------------------------ --------- --------- --------------- ----------------------
SHWOO TKT_BKTSEAT_T RANGE HASH 3 1
Elapsed: 00:00:00.00
>
########################################################
#### 위의 해결 방법은 아래와 같습니다.
########################################################
1. 테이블 삭제 후 재 생성 수행 ( 주석 반드시 확인)
DROP TABLE SHWOO.TKT_BKTSEAT_T ;
CREATE TABLE SHWOO.TKT_BKTSEAT_T
(
CO_CD VARCHAR2 (4) NOT NULL,
BKT_SALE_NO VARCHAR2 (16) NOT NULL,
SEQ NUMBER (6) NOT NULL,
REG_K_ID VARCHAR2 (14),
REG_DT DATE,
UPD_K_ID VARCHAR2 (14),
UPD_DT DATE
)
TABLESPACE USERS
PCTFREE 10
INITRANS 10
MAXTRANS 255
STORAGE
(
BUFFER_POOL DEFAULT
)
PARTITION BY RANGE (CO_CD, BKT_SALE_NO)
SUBPARTITION BY HASH (BKT_SALE_NO)
SUBPARTITION 8 -- 반드시 서브파티션을 몇개로 나누어 졌는지 여부를 표시 해 준다. (기본은 무조건 1로 표시 합니다.)
(
PARTITION PRH_PRM_201010 VALUES LESS THAN ('A416', '201011')
TABLESPACE USERS
PCTFREE 10
INITRANS 10
MAXTRANS 255
STORAGE
(
INITIAL 2097152
NEXT 2097152
MINEXTENTS 1
MAXEXTENTS UNLIMITED
)
LOGGING
NOCOMPRESS
(
SUBPARTITION PRH_PRM_201010_S1
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201010_S2
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201010_S3
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201010_S4
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201010_S5
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201010_S6
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201010_S7
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201010_S8
TABLESPACE USERS
)
,
PARTITION PRH_PRM_201011 VALUES LESS THAN ('A416', '201012')
TABLESPACE USERS
PCTFREE 10
INITRANS 10
MAXTRANS 255
STORAGE
(
INITIAL 2097152
NEXT 2097152
MINEXTENTS 1
MAXEXTENTS UNLIMITED
)
LOGGING
NOCOMPRESS
(
SUBPARTITION PRH_PRM_201011_S1
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201011_S2
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201011_S3
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201011_S4
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201011_S5
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201011_S6
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201011_S7
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201011_S8
TABLESPACE USERS
)
,
PARTITION PRH_PRM_201012 VALUES LESS THAN ('A416', '201101')
TABLESPACE USERS
PCTFREE 10
INITRANS 10
MAXTRANS 255
STORAGE
(
INITIAL 2097152
NEXT 2097152
MINEXTENTS 1
MAXEXTENTS UNLIMITED
)
LOGGING
NOCOMPRESS
(
SUBPARTITION PRH_PRM_201012_S1
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201012_S2
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201012_S3
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201012_S4
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201012_S5
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201012_S6
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201012_S7
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201012_S8
TABLESPACE USERS
)
)
ENABLE ROW MOVEMENT ;
2. DBA_PART_TABLES 딕셔너리 확인 결과
( DEF_SUBPARTITION_COUNT 값이 정상적으로 8 로 설정 됨)
>
SELECT OWNER, TABLE_NAME, PARTITIONING_TYPE, SUBPARTITIONING_TYPE, PARTITION_COUNT, DEF_SUBPARTITION_COUNT
FROM DBA_PART_TABLES
WHERE TABLE_NAME='TKT_BKTSEAT_T';
OWNER TABLE_NAME PARTITION SUBPARTIT PARTITION_COUNT DEF_SUBPARTITION_COUNT
------------------------------ ------------------------------ --------- --------- --------------- ----------------------
SHWOO TKT_BKTSEAT_T RANGE HASH 3 8
Elapsed: 00:00:00.01
>
>
################################################################################
######## 또는 아래 처럼 반드시 확인 후 처리 요망..
################################################################################
1. PLSQL 패키지를 이용하여 사전 DDL 문 확인 하기.
SELECT DBMS_METADATA.GET_DDL('TABLE','TKT_BKTSEAT_T','SHWOO') FROM DUAL;
2. DDL 문을 확인 후 테이블을 생성 해 주시면 됩니다.
CREATE TABLE "SHWOO"."TKT_BKTSEAT_T"
( "CO_CD" VARCHAR2(4) NOT NULL ENABLE,
"BKT_SALE_NO" VARCHAR2(16) NOT NULL ENABLE,
"SEQ" NUMBER(6,0) NOT NULL ENABLE,
"REG_K_ID" VARCHAR2(14),
"REG_DT" DATE,
"UPD_K_ID" VARCHAR2(14),
"UPD_DT" DATE
) PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
PARTITION BY RANGE ("CO_CD","BKT_SALE_NO")
SUBPARTITION BY HASH ("BKT_SALE_NO")
SUBPARTITIONS 8 -- 이부분 값이 정상 적이지 않으면 수정 후 생성 해 주시면 됩니다.
(PARTITION "PRH_PRM_201010" VALUES LESS THAN ('A416', '201011')
PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255
STORAGE(INITIAL 2097152 NEXT 2097152 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" NOCOMPRESS LOGGING
( SUBPARTITION "PRH_PRM_201010_S1"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201010_S2"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201010_S3"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201010_S4"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201010_S5"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201010_S6"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201010_S7"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201010_S8"
TABLESPACE "USERS" NOCOMPRESS ) ,
PARTITION "PRH_PRM_201011" VALUES LESS THAN ('A416', '201012')
PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255
STORAGE(INITIAL 2097152 NEXT 2097152 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" NOCOMPRESS LOGGING
( SUBPARTITION "PRH_PRM_201011_S1"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201011_S2"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201011_S3"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201011_S4"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201011_S5"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201011_S6"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201011_S7"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201011_S8"
TABLESPACE "USERS" NOCOMPRESS ) ,
PARTITION "PRH_PRM_201012" VALUES LESS THAN ('A416', '201101')
PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255
STORAGE(INITIAL 2097152 NEXT 2097152 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" NOCOMPRESS LOGGING
( SUBPARTITION "PRH_PRM_201012_S1"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201012_S2"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201012_S3"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201012_S4"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201012_S5"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201012_S6"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201012_S7"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201012_S8"
TABLESPACE "USERS" NOCOMPRESS ) ) ENABLE ROW MOVEMENT
;
2010년 12월 29일 수요일
2010년 12월 1일 수요일
[11G]ADRCI PURGE , IPS 기능 활용하기
아래 내용을 보시면 11G 부터는 alert log 및 trace 관리 정책이 바뀌었다는 것을 아실 겁니다.
[11g] ADR - AlertLog와 Tracefile의 새로운 위치
아래 내용은 ADRCI PUGE 기능과 IPS 기능에 대해 테스트 한 것을 기입해 봅니다.
/oracle > adrci
ADRCI: Release 11.2.0.2.0 - Production on Tue Nov 30 10:59:54 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
ADR base = "/oracle/app/oracle"
adrci> show incident
ADR Home = /oracle/app/oracle/diag/clients/user_oracle/host_206825830_76:
*************************************************************************
0 rows fetched
ADR Home = /oracle/app/oracle/diag/clients/user_root/host_206825830_76:
*************************************************************************
0 rows fetched
ADR Home = /oracle/app/oracle/diag/rdbms/test/TEST2:
*************************************************************************
INCIDENT_ID PROBLEM_KEY CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
3968573 ORA 4030 2010-07-28 16:10:37.454000 +09:00
3968533 ORA 4030 2010-07-28 16:10:37.454000 +09:00
3968557 ORA 4030 2010-07-28 16:10:37.454000 +09:00
3968589 ORA 4030 2010-07-28 16:10:37.542000 +09:00
3968541 ORA 4030 2010-07-28 16:10:37.542000 +09:00
3968733 ORA 7445 [kkocsMarkBindFroCB()+40] 2010-07-28 17:26:58.531000 +09:00
3968577 ORA 7445 [kkocsMarkBindFroCB()+40] 2010-07-29 13:16:11.467000 +09:00
3968565 ORA 4030 2010-07-29 17:46:55.237000 +09:00
3968545 ORA 4030 2010-07-29 17:46:55.239000 +09:00
3968537 ORA 4030 2010-07-29 17:46:55.249000 +09:00
3968584 ORA 4030 2010-07-29 17:46:55.257000 +09:00
3968549 ORA 4030 2010-07-29 17:46:55.306000 +09:00
3968773 ORA 7445 [kkocsMarkBindFroCB()+40] 2010-07-29 18:09:57.796000 +09:00
3968593 ORA 7445 [kkocsMarkBindFroCB()+40] 2010-07-29 19:07:05.754000 +09:00
3968639 ORA 7445 [kkocsMarkBindFroCB()+40] 2010-07-29 19:09:57.510000 +09:00
3968669 ORA 7445 [kkocsMarkBindFroCB()+40] 2010-07-29 19:12:40.298000 +09:00
3968594 ORA 7445 [kkocsMarkBindFroCB()+40] 2010-07-29 19:14:02.333000 +09:00
3968816 ORA 7445 [kkocsMarkBindFroCB()+40] 2010-07-29 19:18:16.677000 +09:00
3968357 ORA 7445 [kkocsMarkBindFroCB()+40] 2010-07-30 08:25:10.893000 +09:00
3968645 ORA 7445 [kkocsMarkBindFroCB()+40] 2010-07-30 11:43:34.633000 +09:00
3992524 ORA 4030 2010-08-02 20:59:03.066000 +09:00
3992525 ORA 4030 2010-08-02 20:59:09.414000 +09:00
3992526 ORA 4030 2010-08-02 20:59:16.147000 +09:00
3992527 ORA 4030 2010-08-02 20:59:23.259000 +09:00
3992528 ORA 4030 2010-08-02 20:59:26.024000 +09:00
3992588 ORA 4030 2010-08-03 09:16:49.520000 +09:00
4040570 ORA 7445 [evaopn3()+212] 2010-08-13 19:27:54.942000 +09:00
ADR Home = /oracle/app/oracle/diag/tnslsnr/testdb2/listener:
*************************************************************************
0 rows fetched
ADR Home = /oracle/app/oracle/diag/tnslsnr/testdb2/listener1_testdb1:
*************************************************************************
0 rows fetched
ADR Home = /oracle/app/oracle/diag/tnslsnr/testdb2/listener1_testdb2:
*************************************************************************
0 rows fetched
ADR Home = /oracle/app/oracle/diag/tnslsnr/testdb2/listener2_testdb1:
*************************************************************************
0 rows fetched
ADR Home = /oracle/app/oracle/diag/tnslsnr/testdb2/listener2_testdb2:
*************************************************************************
0 rows fetched
ADR Home = /oracle/app/oracle/diag/tnslsnr/testdb2/listener3_testdb2:
*************************************************************************
0 rows fetched
ADR Home = /oracle/app/oracle/diag/tnslsnr/testdb2/listener_testdb1:
*************************************************************************
0 rows fetched
ADR Home = /oracle/app/oracle/diag/tnslsnr/testdb2/listener_testdb2:
*************************************************************************
0 rows fetched
ADR Home = /oracle/app/oracle/diag/tnslsnr/testdb2/sid_list_listener_testdb1:
*************************************************************************
First 0 rows fetched (*** more available ***)
adrci>
adrci> show homepath
ADR Homes:
diag/clients/user_oracle/host_206825830_76
diag/clients/user_root/host_206825830_76
diag/rdbms/test/TEST2
diag/tnslsnr/testdb2/listener
diag/tnslsnr/testdb2/listener1_testdb1
diag/tnslsnr/testdb2/listener1_testdb2
diag/tnslsnr/testdb2/listener2_testdb1
diag/tnslsnr/testdb2/listener2_testdb2
diag/tnslsnr/testdb2/listener3_testdb2
diag/tnslsnr/testdb2/listener_testdb1
diag/tnslsnr/testdb2/listener_testdb2
diag/tnslsnr/testdb2/sid_list_listener_testdb1
adrci>
adrci> PURGE
adrci> show homepath
ADR Homes:
diag/rdbms/test/TEST2
adrci>
adrci> purge -age 60 -type incident
adrci> purge -age 10 -type incident
adrci> purge -age 10 -type ALERT
adrci> purge -age 10 -type INCIDENT
adrci> purge -age 10 -type TRACE
adrci> purge -age 10 -type CDUMP
adrci> purge -age 10 -type HM
adrci> purge -age 10 -type UTSCDMP
adrci> ips generate package 2 in /oracle/DBAWORK
DIA-48448: This command does not support multiple ADR homes
adrci>
adrci> show homepath
ADR Homes:
diag/clients/user_oracle/host_206825830_76
diag/clients/user_root/host_206825830_76
diag/rdbms/test/TEST2
diag/tnslsnr/testdb2/listener
diag/tnslsnr/testdb2/listener1_testdb1
diag/tnslsnr/testdb2/listener1_testdb2
diag/tnslsnr/testdb2/listener2_testdb1
diag/tnslsnr/testdb2/listener2_testdb2
diag/tnslsnr/testdb2/listener3_testdb2
diag/tnslsnr/testdb2/listener_testdb1
diag/tnslsnr/testdb2/listener_testdb2
diag/tnslsnr/testdb2/sid_list_listener_testdb1
adrci>
adrci> set homepath diag/rdbms/test/TEST2
adrci> IPS GENERATE PACKAGE 1 IN /oracle/DBAWORK
Generated package 1 in file /oracle/DBAWORK/IPSPKG_20101130111546_COM_1.zip, mode complete
adrci>
/oracle/DBAWORK > ls -lart IPSPKG*
-rw-r--r-- 1 oracle dba 73294 Nov 30 11:20 IPSPKG_20101130111546_COM_1.zip
/oracle/DBAWORK >
###################### Note ID 738732.1 ##################################################
ADR Different Methods to Create IPS Package [ID 738732.1]
--------------------------------------------------------------------------------
Modified 26-MAY-2010 Type HOWTO Status PUBLISHED
In this Document Goal
Solution
References
--------------------------------------------------------------------------------
Applies to:
Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.2.0.1.0 - Release: 11.1 to 11.2
Information in this document applies to any platform.
Goal
Oracle has introduced several new Diagnosability features in 11g. One of them is Incident Packaging Service (IPS).
IPS uses rules to correlate all relevant dumps and traces from ADR for a given problem and allows you to package them to ship to Oracle Support.
Refer to the note : 443529.1 for the quick steps to create package.
You can access IPS through EM or ADRCI.
This document provides instructions to create package using different methods.
Solution
Invoke adrci and set ADR_HOME.
You can create a logical package based on an incident number, a problem number, a problem key, or a time interval.
Create a logical package such that it will be most useful to diagnose the error of your concern.
I) Creating package based on incident.
Select correct incident if there are many incidents.
adrci>SHOW INCIDENT
adrci>IPS CREATE PACKAGE INCIDENT incident_number
II) Creating Empty package.
adrci>IPS CREATE PACKAGE
This creates an empty package. You must use the IPS ADD INCIDENT or IPS ADD FILE commands to
add diagnostic data to the package before generating it.
III) Creating package based on problem ID
adrci>IPS CREATE PACKAGE PROBLEM problem_ID
This creates a package and includes diagnostic information for incidents that reference the specified
problem ID. (Problem IDs are integers.) You can obtain the problem ID for an incident from the
report displayed by the SHOW INCIDENT -MODE BRIEF command. Because there can be many incidents with the same problem ID,
ADRCI adds to the package the diagnostic information for the first three incidents ("early incidents") that occurred
and last three incidents ("late incidents") that occurred with this problem ID, excluding any incidents that are older than 90 days.
IV) Creating package based on problem key
adrci>IPS CREATE PACKAGE PROBLEMKEY "problem_key"
The problem key must be enclosed in single quotes (') or double quotes (") if it contains spaces or quotes.
V)Creating package based on time interval.
This creates a package and includes diagnostic information for all incidents that occurred from sec
seconds ago until now. sec must be an integer.
adrci>IPS CREATE PACKAGE SECONDS sec adrci>IPS CREATE PACKAGE TIME 'start_time' TO 'end_time'
This creates a package and includes diagnostic information for all incidents that occurred within the
specified time range. start_time and end_time must be in the format 'YYYY-MM-DD HH24:MI:SS.FF TZR'.
This is a valid string for the NLS_TIMESTAMP_TZ_FORMAT initialization parameter.
The fraction (FF) portion of the time is optional,
and the HH24:MI:SS delimiters can be colons or periods.
Adding Incidents and files to the logical package :
You can add more files or more incidents to the package.
adrci>IPS ADD INCIDENT incident_number PACKAGE package_number
adrci>IPS ADD FILE filespec PACKAGE package_number
filespec is the complete path and name of the trace file to add .
package_number is the package id.
Generate a physical incident package :
Once you have created a logical package using one of the above methods, next step is to generate a physical package.
adrci>IPS GENERATE PACKAGE package_number IN path
This generates a complete physical package (zip file) in the designated path. For example, the
following command creates a complete physical package in the directory /home/steve/diagnostics
from logical package number 2:
adrci>IPS GENERATE PACKAGE 2 IN /home/steve/diagnostics
Upload this package to the SR for diagnosing the problem.
You can also create and generate package with one command : IPS Pack .
adrci>IPS PACK INCIDENT incident_id IN path
All the methods discussed above apply to 'IPS pack' as well.
참고 문서 :
ADRCI IPS 기능
ADRCI PURGE 기능
[11g] ADR - AlertLog와 Tracefile의 새로운 위치
아래 내용은 ADRCI PUGE 기능과 IPS 기능에 대해 테스트 한 것을 기입해 봅니다.
/oracle > adrci
ADRCI: Release 11.2.0.2.0 - Production on Tue Nov 30 10:59:54 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
ADR base = "/oracle/app/oracle"
adrci> show incident
ADR Home = /oracle/app/oracle/diag/clients/user_oracle/host_206825830_76:
*************************************************************************
0 rows fetched
ADR Home = /oracle/app/oracle/diag/clients/user_root/host_206825830_76:
*************************************************************************
0 rows fetched
ADR Home = /oracle/app/oracle/diag/rdbms/test/TEST2:
*************************************************************************
INCIDENT_ID PROBLEM_KEY CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
3968573 ORA 4030 2010-07-28 16:10:37.454000 +09:00
3968533 ORA 4030 2010-07-28 16:10:37.454000 +09:00
3968557 ORA 4030 2010-07-28 16:10:37.454000 +09:00
3968589 ORA 4030 2010-07-28 16:10:37.542000 +09:00
3968541 ORA 4030 2010-07-28 16:10:37.542000 +09:00
3968733 ORA 7445 [kkocsMarkBindFroCB()+40] 2010-07-28 17:26:58.531000 +09:00
3968577 ORA 7445 [kkocsMarkBindFroCB()+40] 2010-07-29 13:16:11.467000 +09:00
3968565 ORA 4030 2010-07-29 17:46:55.237000 +09:00
3968545 ORA 4030 2010-07-29 17:46:55.239000 +09:00
3968537 ORA 4030 2010-07-29 17:46:55.249000 +09:00
3968584 ORA 4030 2010-07-29 17:46:55.257000 +09:00
3968549 ORA 4030 2010-07-29 17:46:55.306000 +09:00
3968773 ORA 7445 [kkocsMarkBindFroCB()+40] 2010-07-29 18:09:57.796000 +09:00
3968593 ORA 7445 [kkocsMarkBindFroCB()+40] 2010-07-29 19:07:05.754000 +09:00
3968639 ORA 7445 [kkocsMarkBindFroCB()+40] 2010-07-29 19:09:57.510000 +09:00
3968669 ORA 7445 [kkocsMarkBindFroCB()+40] 2010-07-29 19:12:40.298000 +09:00
3968594 ORA 7445 [kkocsMarkBindFroCB()+40] 2010-07-29 19:14:02.333000 +09:00
3968816 ORA 7445 [kkocsMarkBindFroCB()+40] 2010-07-29 19:18:16.677000 +09:00
3968357 ORA 7445 [kkocsMarkBindFroCB()+40] 2010-07-30 08:25:10.893000 +09:00
3968645 ORA 7445 [kkocsMarkBindFroCB()+40] 2010-07-30 11:43:34.633000 +09:00
3992524 ORA 4030 2010-08-02 20:59:03.066000 +09:00
3992525 ORA 4030 2010-08-02 20:59:09.414000 +09:00
3992526 ORA 4030 2010-08-02 20:59:16.147000 +09:00
3992527 ORA 4030 2010-08-02 20:59:23.259000 +09:00
3992528 ORA 4030 2010-08-02 20:59:26.024000 +09:00
3992588 ORA 4030 2010-08-03 09:16:49.520000 +09:00
4040570 ORA 7445 [evaopn3()+212] 2010-08-13 19:27:54.942000 +09:00
ADR Home = /oracle/app/oracle/diag/tnslsnr/testdb2/listener:
*************************************************************************
0 rows fetched
ADR Home = /oracle/app/oracle/diag/tnslsnr/testdb2/listener1_testdb1:
*************************************************************************
0 rows fetched
ADR Home = /oracle/app/oracle/diag/tnslsnr/testdb2/listener1_testdb2:
*************************************************************************
0 rows fetched
ADR Home = /oracle/app/oracle/diag/tnslsnr/testdb2/listener2_testdb1:
*************************************************************************
0 rows fetched
ADR Home = /oracle/app/oracle/diag/tnslsnr/testdb2/listener2_testdb2:
*************************************************************************
0 rows fetched
ADR Home = /oracle/app/oracle/diag/tnslsnr/testdb2/listener3_testdb2:
*************************************************************************
0 rows fetched
ADR Home = /oracle/app/oracle/diag/tnslsnr/testdb2/listener_testdb1:
*************************************************************************
0 rows fetched
ADR Home = /oracle/app/oracle/diag/tnslsnr/testdb2/listener_testdb2:
*************************************************************************
0 rows fetched
ADR Home = /oracle/app/oracle/diag/tnslsnr/testdb2/sid_list_listener_testdb1:
*************************************************************************
First 0 rows fetched (*** more available ***)
adrci>
adrci> show homepath
ADR Homes:
diag/clients/user_oracle/host_206825830_76
diag/clients/user_root/host_206825830_76
diag/rdbms/test/TEST2
diag/tnslsnr/testdb2/listener
diag/tnslsnr/testdb2/listener1_testdb1
diag/tnslsnr/testdb2/listener1_testdb2
diag/tnslsnr/testdb2/listener2_testdb1
diag/tnslsnr/testdb2/listener2_testdb2
diag/tnslsnr/testdb2/listener3_testdb2
diag/tnslsnr/testdb2/listener_testdb1
diag/tnslsnr/testdb2/listener_testdb2
diag/tnslsnr/testdb2/sid_list_listener_testdb1
adrci>
adrci> PURGE
adrci> show homepath
ADR Homes:
diag/rdbms/test/TEST2
adrci>
adrci> purge -age 60 -type incident
adrci> purge -age 10 -type incident
adrci> purge -age 10 -type ALERT
adrci> purge -age 10 -type INCIDENT
adrci> purge -age 10 -type TRACE
adrci> purge -age 10 -type CDUMP
adrci> purge -age 10 -type HM
adrci> purge -age 10 -type UTSCDMP
adrci> ips generate package 2 in /oracle/DBAWORK
DIA-48448: This command does not support multiple ADR homes
adrci>
adrci> show homepath
ADR Homes:
diag/clients/user_oracle/host_206825830_76
diag/clients/user_root/host_206825830_76
diag/rdbms/test/TEST2
diag/tnslsnr/testdb2/listener
diag/tnslsnr/testdb2/listener1_testdb1
diag/tnslsnr/testdb2/listener1_testdb2
diag/tnslsnr/testdb2/listener2_testdb1
diag/tnslsnr/testdb2/listener2_testdb2
diag/tnslsnr/testdb2/listener3_testdb2
diag/tnslsnr/testdb2/listener_testdb1
diag/tnslsnr/testdb2/listener_testdb2
diag/tnslsnr/testdb2/sid_list_listener_testdb1
adrci>
adrci> set homepath diag/rdbms/test/TEST2
adrci> IPS GENERATE PACKAGE 1 IN /oracle/DBAWORK
Generated package 1 in file /oracle/DBAWORK/IPSPKG_20101130111546_COM_1.zip, mode complete
adrci>
/oracle/DBAWORK > ls -lart IPSPKG*
-rw-r--r-- 1 oracle dba 73294 Nov 30 11:20 IPSPKG_20101130111546_COM_1.zip
/oracle/DBAWORK >
###################### Note ID 738732.1 ##################################################
ADR Different Methods to Create IPS Package [ID 738732.1]
--------------------------------------------------------------------------------
Modified 26-MAY-2010 Type HOWTO Status PUBLISHED
In this Document Goal
Solution
References
--------------------------------------------------------------------------------
Applies to:
Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.2.0.1.0 - Release: 11.1 to 11.2
Information in this document applies to any platform.
Goal
Oracle has introduced several new Diagnosability features in 11g. One of them is Incident Packaging Service (IPS).
IPS uses rules to correlate all relevant dumps and traces from ADR for a given problem and allows you to package them to ship to Oracle Support.
Refer to the note : 443529.1 for the quick steps to create package.
You can access IPS through EM or ADRCI.
This document provides instructions to create package using different methods.
Solution
Invoke adrci and set ADR_HOME.
You can create a logical package based on an incident number, a problem number, a problem key, or a time interval.
Create a logical package such that it will be most useful to diagnose the error of your concern.
I) Creating package based on incident.
Select correct incident if there are many incidents.
adrci>SHOW INCIDENT
adrci>IPS CREATE PACKAGE INCIDENT incident_number
II) Creating Empty package.
adrci>IPS CREATE PACKAGE
This creates an empty package. You must use the IPS ADD INCIDENT or IPS ADD FILE commands to
add diagnostic data to the package before generating it.
III) Creating package based on problem ID
adrci>IPS CREATE PACKAGE PROBLEM problem_ID
This creates a package and includes diagnostic information for incidents that reference the specified
problem ID. (Problem IDs are integers.) You can obtain the problem ID for an incident from the
report displayed by the SHOW INCIDENT -MODE BRIEF command. Because there can be many incidents with the same problem ID,
ADRCI adds to the package the diagnostic information for the first three incidents ("early incidents") that occurred
and last three incidents ("late incidents") that occurred with this problem ID, excluding any incidents that are older than 90 days.
IV) Creating package based on problem key
adrci>IPS CREATE PACKAGE PROBLEMKEY "problem_key"
The problem key must be enclosed in single quotes (') or double quotes (") if it contains spaces or quotes.
V)Creating package based on time interval.
This creates a package and includes diagnostic information for all incidents that occurred from sec
seconds ago until now. sec must be an integer.
adrci>IPS CREATE PACKAGE SECONDS sec adrci>IPS CREATE PACKAGE TIME 'start_time' TO 'end_time'
This creates a package and includes diagnostic information for all incidents that occurred within the
specified time range. start_time and end_time must be in the format 'YYYY-MM-DD HH24:MI:SS.FF TZR'.
This is a valid string for the NLS_TIMESTAMP_TZ_FORMAT initialization parameter.
The fraction (FF) portion of the time is optional,
and the HH24:MI:SS delimiters can be colons or periods.
Adding Incidents and files to the logical package :
You can add more files or more incidents to the package.
adrci>IPS ADD INCIDENT incident_number PACKAGE package_number
adrci>IPS ADD FILE filespec PACKAGE package_number
filespec is the complete path and name of the trace file to add .
package_number is the package id.
Generate a physical incident package :
Once you have created a logical package using one of the above methods, next step is to generate a physical package.
adrci>IPS GENERATE PACKAGE package_number IN path
This generates a complete physical package (zip file) in the designated path. For example, the
following command creates a complete physical package in the directory /home/steve/diagnostics
from logical package number 2:
adrci>IPS GENERATE PACKAGE 2 IN /home/steve/diagnostics
Upload this package to the SR for diagnosing the problem.
You can also create and generate package with one command : IPS Pack .
adrci>IPS PACK INCIDENT incident_id IN path
All the methods discussed above apply to 'IPS pack' as well.
참고 문서 :
ADRCI IPS 기능
ADRCI PURGE 기능
[11G] INVISIBLE INDEX 기능 활용하기
11g New Feature : Invisible Index
==================================
1. Invisible Index란
Invisible Index기능은 Optimizer가 Execution Plan을 생성 시에 Invisible Index인 Index들을 무시 하게 되는 기능입니다.
Session이나 System별로 OPTIMIZER_USE_INVISIBLE_INDEXES Parameter를 True로 설정하게 되면 Optimizer가 Invisible Index라고 할지라도
무시하지 않고 Execution Plan을 작성하게 됩니다.
Unusable Index와는 다르게 DML 작업을 하면 Invisible Index들은 계속 유지가 됩니다.
Invisible Index의 기능을 이용하면 다음과 같은 장점을 이용할 수 있습니다.
1) Index를 Drop하기 전에 Execution Plan의 변화를 미리 Test해 보실 수 있습니다.
2) 전체 Application의 영향을 주지 않고 특정 Application에서만 Temporary하게 Index를 사용하게 하실 수 있습니다.
2. Syntax
1) Index를 Invisible하게 Create
CREATE INDEX emp_deptno ON emp(deptno)
TABLESPACE users
STORAGE (INITIAL 20K
NEXT 20k
PCTINCREASE 75)
INVISIBLE; <----------- Invisible Option을 사용하여 Create 한다. (DEFAULT 값이므로 옵션 제외 해도 된다.) 2) Index를 Invisible 혹은 Visible하게 만들기 ALTER INDEX index_name INVISIBLE; ALTER INDEX index_name VISIBLE; 3. Test 1) Invisible Index 생성 후에 확인 SQL> CREATE INDEX emp_deptno ON emp(deptno)
TABLESPACE users
STORAGE (INITIAL 20K
NEXT 20k
PCTINCREASE 75)
INVISIBLE;
Index created.
SQL> select index_name, visibility from user_indexes;
INDEX_NAME VISIBILIT
------------ ----------
PK_EMP VISIBLE
EMP_DEPTNO INVISIBLE
PK_DEPT VISIBLE
2) PK_EMP를 Invisible로 바꾼 후의 변화
SQL> ALTER INDEX PK_EMP INVISIBLE;
Index altered.
SQL> set autotrace on
SQL> select * from scott.emp where empno=7369;
EMPNO JOB MGR HIREDATE SAL COMM DEPTNO
----- ------ ---- --------- --- -----------
7369 CLERK 7902 17-DEC-80 800 20
Execution Plan
-----------------------------
Plan hash value: 3956160932
Id Operation Name Rows Bytes Cost(%CPU) Time
------------------------------------------------------------------- -------
0 SELECT STATEMENT 1 33 3 (0) 00:00:01
* 1 TABLE ACCESS FULL EMP 1 33 3 (0) 00:00:01
3) PK_EMP를 다시 Visible로 바꾼 후의 변화
SQL> ALTER INDEX PK_EMP VISIBLE;
Index altered.
SQL> set autot on
SQL> select * from scott.emp where empno=7369;
EMPNO JOB MGR HIREDATE SAL COMM DEPTNO
----- ------- ---- --------- --- -----------
7369 CLERK 7902 17-DEC-80 800 20
Execution Plan
----------------------------
Plan hash value: 2949544139
Id Operation Name Rows Bytes Cost(%CPU) Time
----------------------------------------------------------------------------- ----------
0 SELECT STATEMENT 1 33 1 (0) 00:00:01
1 TABLE ACCESS BY INDEX ROWID EMP 1 33 1 (0) 00:00:01
* 2 INDEX UNIQUE SCAN PK_EMP 1 0 (0) 00:00:01
4) 전체 Application에 영향을 미치지 않고 특정 Query에서만 Invisible Index 사용하기
- Session A
-- * Invisible Index를 사용하도록 OPTIMIZER_USE_INVISIBLE_INDEXES Parameter를 True로 설정합니다.
SQL> alter session set OPTIMIZER_USE_INVISIBLE_INDEXES = true;
Session altered.
-- * Invisible Index가 있는지 확인 합니다.
SQL> select index_name, visibility from user_indexes;
INDEX_NAME VISIBILIT
--------- ----------
PK_EMP VISIBLE
EMP_DEPTNO INVISIBLE
PK_DEPT VISIBLE
-- * Index를 사용하는 부분을 확인 할 수 있습니다.
SQL> set autot on
SQL> select * from scott.emp where deptno=20;
EMPNO JOB MGR HIREDATE SAL COMM DEPTNO
--------------------- --------- ---- ----- ---------
7369 CLERK 7902 17-DEC-80 800 20
7566 MANAGER 7839 02-APR-81 2975 20
7788 ANALYST 7566 19-APR-87 3000 20
7876 CLERK 7788 23-MAY-87 1100 20
7902 ANALYST 7566 03-DEC-81 3000 20
Execution Plan
----------------------------------------------------
Plan hash value: 1182541070
Id Operation Name Rows Bytes Cost (%CPU) Time
----------------------------------------------------------------------------------- ------------
0 SELECT STATEMENT 5 165 2 (0) 00:00:01
1 TABLE ACCESS BY INDEX ROWID EMP 5 165 2 (0) 00:00:01
* 2 INDEX RANGE SCAN EMP_DEPTNO 5 1 (0) 00:00:01
- Session B
SQL> connect / as sysdba
Connected.
-- * OPTIMIZER_USE_INVISIBLE_INDEXES가 Default로 False임을 확인 합니다.
SQL> show parameter OPTIMIZER_USE_INVISIBLE_INDEXES
NAME TYPE VALUE
------------------------------ ------- ----
optimizer_use_invisible_indexes boolean FALSE
SQL> connect scott/tiger
Connected.
SQL> set autot on
-- * OPTIMIZER_USE_INVISIBLE_INDEXES가 False일 때 Invisible Index를 사용하지 않음을 확인 할 수 있습니다.
SQL> select * from scott.emp where deptno=20;
EMPNO JOB MGR HIREDATE SAL COMM DEPTNO
----- ------ ------ --------- ---------------
7369 CLERK 7902 17-DEC-80 800 20
7566 MANAGER 7839 02-APR-81 2975 20
7788 ANALYST 7566 19-APR-87 3000 20
7876 CLERK 7788 23-MAY-87 1100 20
7902 ANALYST 7566 03-DEC-81 3000 20
Execution Plan
-------------------------------------------
Plan hash value: 3956160932
Id Operation Name Rows Bytes Cost (%CPU) Time
---------------------------------------------------------------------- ----------
0 SELECT STATEMENT 5 165 3 (0) 00:00:01
* 1 TABLE ACCESS FULL EMP 5 165 3 (0) 00:00:01
만약 index를 삭제 또는 변경 하는 것이 부담 스러운 경우는
OPTIMIZER_USE_INVISIBLE_INDEXES DEFAULT 값을 유지(FALSE)를 한 후
해당 인덱스만 ALTER 문을 이용하여 INVISIBLE 로 변경 처리 후 PLAN 을 확인 또는 모니터링을 한 후 정의 하시면
될듯 합니다.
개인적으로 유용한 팁이라 생각 듭니다.
Reference
Article-ID: Note 453295.1
Title: 11g New Feature : Invisible Index
본문서는 원본에서 추가 및 수정 함
==================================
1. Invisible Index란
Invisible Index기능은 Optimizer가 Execution Plan을 생성 시에 Invisible Index인 Index들을 무시 하게 되는 기능입니다.
Session이나 System별로 OPTIMIZER_USE_INVISIBLE_INDEXES Parameter를 True로 설정하게 되면 Optimizer가 Invisible Index라고 할지라도
무시하지 않고 Execution Plan을 작성하게 됩니다.
Unusable Index와는 다르게 DML 작업을 하면 Invisible Index들은 계속 유지가 됩니다.
Invisible Index의 기능을 이용하면 다음과 같은 장점을 이용할 수 있습니다.
1) Index를 Drop하기 전에 Execution Plan의 변화를 미리 Test해 보실 수 있습니다.
2) 전체 Application의 영향을 주지 않고 특정 Application에서만 Temporary하게 Index를 사용하게 하실 수 있습니다.
2. Syntax
1) Index를 Invisible하게 Create
CREATE INDEX emp_deptno ON emp(deptno)
TABLESPACE users
STORAGE (INITIAL 20K
NEXT 20k
PCTINCREASE 75)
INVISIBLE; <----------- Invisible Option을 사용하여 Create 한다. (DEFAULT 값이므로 옵션 제외 해도 된다.) 2) Index를 Invisible 혹은 Visible하게 만들기 ALTER INDEX index_name INVISIBLE; ALTER INDEX index_name VISIBLE; 3. Test 1) Invisible Index 생성 후에 확인 SQL> CREATE INDEX emp_deptno ON emp(deptno)
TABLESPACE users
STORAGE (INITIAL 20K
NEXT 20k
PCTINCREASE 75)
INVISIBLE;
Index created.
SQL> select index_name, visibility from user_indexes;
INDEX_NAME VISIBILIT
------------ ----------
PK_EMP VISIBLE
EMP_DEPTNO INVISIBLE
PK_DEPT VISIBLE
2) PK_EMP를 Invisible로 바꾼 후의 변화
SQL> ALTER INDEX PK_EMP INVISIBLE;
Index altered.
SQL> set autotrace on
SQL> select * from scott.emp where empno=7369;
EMPNO JOB MGR HIREDATE SAL COMM DEPTNO
----- ------ ---- --------- --- -----------
7369 CLERK 7902 17-DEC-80 800 20
Execution Plan
-----------------------------
Plan hash value: 3956160932
Id Operation Name Rows Bytes Cost(%CPU) Time
------------------------------------------------------------------- -------
0 SELECT STATEMENT 1 33 3 (0) 00:00:01
* 1 TABLE ACCESS FULL EMP 1 33 3 (0) 00:00:01
3) PK_EMP를 다시 Visible로 바꾼 후의 변화
SQL> ALTER INDEX PK_EMP VISIBLE;
Index altered.
SQL> set autot on
SQL> select * from scott.emp where empno=7369;
EMPNO JOB MGR HIREDATE SAL COMM DEPTNO
----- ------- ---- --------- --- -----------
7369 CLERK 7902 17-DEC-80 800 20
Execution Plan
----------------------------
Plan hash value: 2949544139
Id Operation Name Rows Bytes Cost(%CPU) Time
----------------------------------------------------------------------------- ----------
0 SELECT STATEMENT 1 33 1 (0) 00:00:01
1 TABLE ACCESS BY INDEX ROWID EMP 1 33 1 (0) 00:00:01
* 2 INDEX UNIQUE SCAN PK_EMP 1 0 (0) 00:00:01
4) 전체 Application에 영향을 미치지 않고 특정 Query에서만 Invisible Index 사용하기
- Session A
-- * Invisible Index를 사용하도록 OPTIMIZER_USE_INVISIBLE_INDEXES Parameter를 True로 설정합니다.
SQL> alter session set OPTIMIZER_USE_INVISIBLE_INDEXES = true;
Session altered.
-- * Invisible Index가 있는지 확인 합니다.
SQL> select index_name, visibility from user_indexes;
INDEX_NAME VISIBILIT
--------- ----------
PK_EMP VISIBLE
EMP_DEPTNO INVISIBLE
PK_DEPT VISIBLE
-- * Index를 사용하는 부분을 확인 할 수 있습니다.
SQL> set autot on
SQL> select * from scott.emp where deptno=20;
EMPNO JOB MGR HIREDATE SAL COMM DEPTNO
--------------------- --------- ---- ----- ---------
7369 CLERK 7902 17-DEC-80 800 20
7566 MANAGER 7839 02-APR-81 2975 20
7788 ANALYST 7566 19-APR-87 3000 20
7876 CLERK 7788 23-MAY-87 1100 20
7902 ANALYST 7566 03-DEC-81 3000 20
Execution Plan
----------------------------------------------------
Plan hash value: 1182541070
Id Operation Name Rows Bytes Cost (%CPU) Time
----------------------------------------------------------------------------------- ------------
0 SELECT STATEMENT 5 165 2 (0) 00:00:01
1 TABLE ACCESS BY INDEX ROWID EMP 5 165 2 (0) 00:00:01
* 2 INDEX RANGE SCAN EMP_DEPTNO 5 1 (0) 00:00:01
- Session B
SQL> connect / as sysdba
Connected.
-- * OPTIMIZER_USE_INVISIBLE_INDEXES가 Default로 False임을 확인 합니다.
SQL> show parameter OPTIMIZER_USE_INVISIBLE_INDEXES
NAME TYPE VALUE
------------------------------ ------- ----
optimizer_use_invisible_indexes boolean FALSE
SQL> connect scott/tiger
Connected.
SQL> set autot on
-- * OPTIMIZER_USE_INVISIBLE_INDEXES가 False일 때 Invisible Index를 사용하지 않음을 확인 할 수 있습니다.
SQL> select * from scott.emp where deptno=20;
EMPNO JOB MGR HIREDATE SAL COMM DEPTNO
----- ------ ------ --------- ---------------
7369 CLERK 7902 17-DEC-80 800 20
7566 MANAGER 7839 02-APR-81 2975 20
7788 ANALYST 7566 19-APR-87 3000 20
7876 CLERK 7788 23-MAY-87 1100 20
7902 ANALYST 7566 03-DEC-81 3000 20
Execution Plan
-------------------------------------------
Plan hash value: 3956160932
Id Operation Name Rows Bytes Cost (%CPU) Time
---------------------------------------------------------------------- ----------
0 SELECT STATEMENT 5 165 3 (0) 00:00:01
* 1 TABLE ACCESS FULL EMP 5 165 3 (0) 00:00:01
만약 index를 삭제 또는 변경 하는 것이 부담 스러운 경우는
OPTIMIZER_USE_INVISIBLE_INDEXES DEFAULT 값을 유지(FALSE)를 한 후
해당 인덱스만 ALTER 문을 이용하여 INVISIBLE 로 변경 처리 후 PLAN 을 확인 또는 모니터링을 한 후 정의 하시면
될듯 합니다.
개인적으로 유용한 팁이라 생각 듭니다.
Reference
Article-ID: Note 453295.1
Title: 11g New Feature : Invisible Index
본문서는 원본에서 추가 및 수정 함
[11gR2] Background Processes
11gR2 백그라운드 설명 입니다.
참고 하십시요.
http://download.oracle.com/docs/cd/E14072_01/server.112/e10820/bgprocesses.htm
참고 하십시요.
http://download.oracle.com/docs/cd/E14072_01/server.112/e10820/bgprocesses.htm