Oracle INVISIBLE index는 Oracle 11g new feature 입니다.

말 그대로 보이지 않는 index 입니다. 여기서 보는 주체는 oracle optimizer가 됩니다.
즉 index는 존재하지만 optimizer는 이를 기준으로 plan을 생성하지 않습니다.
그러나 해당 index의 table에 대한 DML 변경 내역은 모두 index에 적용되게 됩니다.

이는 index 생성에 따른 혹은 index 삭제에 따른 전체 성능, 일부 성능 측정에 도움이 될 만한 feature가 아닌가 싶습니다.

Invisible index 생성
SQL> Create index invisible_index on table(column) invisible;

Invitible index 사용 정의
SQL> alter session set optimizer_use_invisible_indexes=true;

Invisible index를 visible로 변경
SQL> alter index invisible_index visible;

visible index를 invisible로 변경
SQL> alter index invisible_index invisible;





Oracle과 다른 DB와의 차이점을 보면 굉장히 손이 많이 갑니다.
oracle 10g, 11g가 나오면서 운영의 자동화와 간소화 기능이 많이 추가 되긴 했지만요..

그런데, 이말을 거꾸로 말해보자면, 사용자의 역량에 따라 tuning 할 수 있는 포인트가 많다는 이야기도 되죠.
또 이런 특성때문에 곳곳에 숨어 있는 고수들도 많이 있습니다.

아래의 SQL은 숨어 있는 이러한 기능들을 control 할 수 있는 parameter를 확인 할 수 있는 SQL 입니다.
물론 show parameter나 v$parameter에서 parameter의 값을 확인 할 수 있지만,
이 두개의 방법으로 찾을 수 있는 parameter는 일반 parameter와 변경된 hidden parameter만을 볼 수 있습니다.

아래의 SQL은 일반 parameter와 hidden parameter 모두 확인 가능한 parameter 입니다.

col value format a20
col description format a70
select a.inst_id "inst_id",  ksppinm "name",  ksppstvl "value",  ksppdesc "description"
from x$ksppi a,x$ksppsv b where a.indx = b.indx and ksppinm like '%&parameter%'
/

SQL만 덩그러니 post하긴 좀 그러니..
각 버전 별로 전체 parameter 갯수와 hidden parameter 개수를 비교해 봤습니다.

10g R2는 전체 1385개의 parameter 중 hidden parameter는 1127 개이고,
11g R1 은 1920개 중 1631개,
11g R2는 2399개 중 2057 개  입니다.
(제 VMware linux 서버 기준입니당)

버전이 높아 질수록 점점 숨겨지는 parameter도 늘어가네요. 
또 전체 parameter 중에서 감춰진 parameter가 대부분이군요..
약 70% 정도 되는 거 같네요.. 

요거 마스터 하면 고수가 될 수 있을 라나..




운영 중 spfile이 유실 되는 경우, 간편하게 recovery 할 수 있는 명령어가 oracle 11g에서 추가되었네요.

뭐 alert log file에 나온 parameter 정보나 show parameter 등으로도 만들 수 있지만 많이 귀찮죠.
(사실 spfile이나 init file 유실되는 건 본적은 없으나...)

create spfile from memory;
create spfile='/u01/oracle/app/oracle/product/11g/dbs/spfile_back.ora' from memory;







전통적인 Oracle 접속 방식인 "Dedicated Server"와 "Shared Server" 방식외에 Oracle 11g에서 DRCP (Database Resident Connection Pooling)라는 접속 방식이 추가 되었습니다.

Database resident connection pooling (DRCP) provides a connection pool in the database server for typical Web application usage scenarios where the application acquires a database connection, works on it for a relatively short duration, and then releases it.

메뉴얼을 보면 각각의 접속 방식에 소모되는 memory를 계산해 놓은 부분이 있는데, 요거 보면 적용할 만 하겠다 싶긴 합니다.

그러나 memory 사용량을 보면 혹할수도 있겠는데, 만약 concurrent가 5000이라고 가정 한다면,
DRCP의 경우는 100개만 접속 해 있고 나머지는 queue에 대기하고 있는 모양이겠죠?

shared server의 경우 100개만 떠있어도 100개의 shared server가 5000개의 세션을 cover 해주고 있으니,
DRCP와 dedicate/ shared server와 비교하는 것 자체가 사실 말이 안될 수 도 있습니다.

memory required for each session is 400 KB and the memory required for each server process is 4 MB. The pool size is 100 and the number of shared servers used is 100. If there are 5000 client connections, the memory used by each configuration is as follows:

 Dedicated Server
 Memory used = 5000 X (400 KB + 4 MB) = 22 GB
 Shared Server
 Memory used = 5000 X 400 KB + 100 X 4 MB = 2.5 GB
 Database Resident Connection Pooling
 Memory used = 100 X (400 KB + 4 MB) + (5000 X 35KB)= 615 MB

설정 방법도 별로 어렵지 않습니다. 서버단에서 process parameter 좀 늘려주고 다음 처럼 수행하고, client 단에서 tnsnaems.ora의 설정 부분에 SERVER를 POOLED라고만 정의하면 되네요. 

DRCP 설정 방법
SQL> exec dbms_connection_pool.configure_pool(maxsize=>10,inactivity_timeout=>60);
SQL> exec sys.dbms_connection_pool.start_pool();

Client 설정
DRCP =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = sample.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = POOLED)
      (SERVICE_NAME = orcl.sample.com)
    )
  )

딱히 테스트 해보고 올린 post가 아니니 잘못된 부분이 있으면 가차없이 알려주세요. ^^





1.
1. Shutdown the database
2. Move the datafile to its new location.
3. Do a "STARTUP MOUNT"
4. ALTER DATABASE RENAME FILE '/old/location' TO '/new/location';
5. ALTER DATABASE OPEN;


2.
--Create new undo tablespace:
SQL> create undo tablespace UNDOTBS02 datafile '/u03/app/oracle/oradata/ovpi/undo02.dbf' size 1000m;

--Set new tablespace as undo_tablespace:
SQL> alter system set undo_tablespace=undotbs02;

--Drop the old tablespace:
SQL> drop tablespace undotbs including contents;

When dropping the old tablespace, you may encounter an ORA-30013 error.
This essentially indicates that you must wait for any existing transactions using this tablespace to either commit or rollback before the tablespace can be dropped.

그럼 undo_retention 까지 기다려야 할까?
아니면 undo_retention은 무시되며 drop 될까?  아마 drop 되지 않을까.. 싶다..

http://www.orafaq.com/forum/t/63723/2/





아직 해보진 않았지만..

RE: How to modify a metric collection interval in Grid Control?

I think I found it.
It's not stored in the db at all it seems.
It uses xml files in the $AGENT_HOME/sysman/admin/default_collection.
I found the "database.xmlp" file which defined the 7 Day interval for
Tablespace Allocation.

Here's the entry in the file:

<!--
======================================================================
== Category: Tablespace Allocation
== DB Versions: pre-8 to 10gR1
== Fetchlet: SQL
======================================================================
-->
<CollectionItem NAME="tbspAllocation">
<ValidIf>
<CategoryProp NAME="VersionCategory"
CHOICES="pre8;8i;8iR2;9i;9iR2;10gR1;10gR2;10gR203;11gR1"/>
</ValidIf>
<Schedule>
<IntervalSchedule INTERVAL="7" TIME_UNIT="Day"/>
</Schedule>
<MetricColl NAME="tbspAllocation" />
</CollectionItem>

ALTHOUGH, I updated this to "1" day, and reloaded the agent, Grid Control is
still showing "7" days under All Metrics for this target.
It appears I'm still missing something.





오라클은 많은 백그라운드프로세스를 갖고 있습니다.
버전이 올라갈 수록 점점 더 많은 백그라운드프로세스들이 등장하고 있습니다.
이 많은 백그라운드 프로세스 중 반드시 떠있어야만 하는 프로세스들은 어떤게 있을까요?
다시 말하자면, 백그라운드 프로세스 중 비정상적으로 죽을 경우 인스턴스까지 죽을 수 있는 프로세스는 어떤게 있을까요?

가장 쉽게 알수 있는 방법은.. 백그라운드 프로세스를 하나씩 죽여 보는 거죠.
제가 찾아본 바로는 메뉴얼에도 그러한 내용은 없으니까요..
(만약 메뉴얼에 그러한 내용이 있으면 .. 뭐.. 영어가 문제죠. ㅠㅠ)
그러나 이럴 경우 mandatory process가 아닐 경우에도 bug등에 의해 죽을 수 있으니 확실한 방법은 아닙니다.

그럼 어떤 방법이 있을까요?
백그라운드를 하나씩 죽여볼까.. 하다 곰곰히 생각을 해봤는데,
백그라운드 프로세스가 죽으면 ORA-XXXX라는 메세지를 반드시 뿌린다는게 생각 나더군요.
(물론 error detecting할 여유조차 없다면 에러도 안나겠지만.. )

그래서 oracle error code가 저장되어 있는 oraus.msg file을 열어봤습니다.
빙고..


CKPT, LGWR, DBWR, PMON, ARCH process 등은 "warm start instance"라고 나오는 군요.
그에 비해 SNP process는 PMON이 금방 띄운다고 하는 군요.

그럼 "warm start instance"는 무얼까요..
다음은 oracle forum의 내용입니다.

warm start instance

What kind of error message did You get? Depending on that we could figure out what exactly should be done in that particular case.
but warm start is actually clean shutdown and startup of oracle database. This activity which is called warm start is perfomed by SMON process upon startup of database - it takes cares about all incomplete transactions and recover them.




AWR을 보다 보니 낯선 enqueue 2개가 자주 보여 요거 좀 정리합니다.

RO-Multiple Object Reuse (fast object reuse)

RO enqueue는 "multi object reuse" enqueue로 알려져 있습니다. 이 Enqueue는 foreground process와 background process 간의 sync 하는데 사용되는 enqueue 입니다. Background는 주로 DBWR나 CKPT를 말합니다.

특별히 object drop이나 table truncate 할 때 많이 사용됩니다.

oracle database내에서 truncate나 drop이 발생하면 다음과 같은 내부 작업들이 수행됩니다.

1. foreground process는 먼저 "RO" enqueue를 "execlusive" mode로 요청합니다.
2. 다음은 instance에 작업을 요청하는 cross instance call이 발생되어, CI enqueue가 할당됩니다.
3. 각 instance의 CKPT는 CI call의 요청에 따라 DBWR에서 dirty buffer를 write하게하고 관련 buffer를
   invalidate 합니다.
4. DBWR가 write 작업을 끝내면 foreground process는 "RO" enqueue를 release 합니다.

사실상 이 enqueue 작업은 truncate/drop operation을 순차적으로 수행되기 때문에 자주 drop/truncate가 발생하면 "RO" enqueue contention이 발생할 수 있겠죠..

KO-Multiple Object Checkpoint (fast object checkpoint)

얼마전에 PDML 관련해 posting 한 내용 중 다음과 같은 글을 쓴적 있습니다.

"direct-path read가 발생하면 변경되었거나 disk에 반영되지 않은 데이터를 buffer cache에서 disk로 강제로 flush 한다. 그후 data를 direct path I/O로 읽는다."
Parallel Query Execution

oracle 10g R2이전엔 direct-path read가 발생하면 관련 segment가 저장된 tablespace를 대상으로 flush를 하였습니다. 그러나 oracle 10g R2이후엔 관련 object만 disk로 flush하게 바뀌었습니다.
tablespace 단위로 flush 할 경우 해당 tablespace에 많은 데이터가 저장되어 있거나 한 tablespace에 데이터를 몰아 넣었다면 엄청 불필요한 dirty buffer write가 발생할 수 있겠죠.

KO enqueue는 10g R2 부터 바뀐 이러한 동작에 발생하는 enqueue lock type 입니다. 즉 object에 대한 checkpoint 시 대기할 때 발생하는 enqueue lock입니다.


다음은 일반적인 Enqueue 성능을 확인하는 script 들 입니다.
Enqueue는 v$system_event  v$session_wait  v$enqueue_stat을 통해 각종 statistic 정보를 확인할 수 있습니다.

전체 database system 내의 wait event에 대한 wait 관련 정보

select EVENT,TOTAL_WAITS,TOTAL_TIMEOUTS,TIME_WAITED,AVERAGE_WAIT,WAIT_CLASS
from v$system_event
where wait_class not in ('Idle')
order by 4 desc
/

전체 enqueue lock에 대한 사용 정보
select * from v$enqueue_stat order by 7 desc
/


이 포스트는 다음의 문서를 참고했습니다.

http://ww.orafaq.com/usenet/comp.databases.oracle.server/2006/09/23/1586.htm
Note 286363.1 Truncate Takes A Long Time -- Waits on RO enqueue





Oracle Data Type에 대한 Technical iSeminar 입니다.


참고 : 첨부된 자료는 Oracle의 technical iSeminar 자료 입니다. 


1. Overview
 - Data Type 개요
 - Data Type 종류
 - Data Type 이해의 중요성

2. Scalar Data Type
 - CHAR/NCHAR
 - VARCHAR2/NVARCHAR2
 - CLOB/NCLOB
 - LONG
 - NUMBER
 - DATE
 - TIMESTAMP
 - INTERVAL
 - BLOB/BFILE
 - RAW/ LONG RAW
 - ROWID/UROWID

3. User Defined Data Type
 - OBJECT TYPE
 - VARRAY
 - NESTED TABLE
 - REF

4. 데이터 타입 선정 지침
 - 문자열 관련 일반지침
 - 숫자/날짜 관련 일반 지침
 - LONG/ LONG RAW 제약사항
 - BLOB/ CLOB 제약사항
 
5. Reference


* CRS process 확인
1. ps -ef | grep d.bin

* Resource Status 확인
crs_stat -t
crs_stat -p ==> CRS PROFILE을 확인

* Node_name 확인
as oracle> $ORA_CRS_HOME/crs/bin/olsnodes -n

* CRS 실행
1. as root> $ORA_CRS_HOME/crs/bin/crsctl start crs

* ASM 사용시 Start/Stop 방법
as oracle> srvctl start/stop asm -n node_name

* Listener Start/Stop 방법
as oracle> srvctl start/stop listener -n node_name [-l listenername]

* Instance Startup/Stop

srvctl start instance -d oraDB -i oraDB1
srvctl stop instance -d oraDB -i oraDB1
srvctl status database -d oraDB
Instance oraDB1 is not running on node ds04e
Instance oraDB2 is not running on node ds05e

* Database Down 방법

1. sqlplus "/as sysdba"
   SQL> shutdown immediate
   또는
   srvctl stop database -d db_name

2. srvctl stop nodeapps -n <node#1_name>
3. srvctl stop nodeapps -n <node#2_name>
4. as root> /fsoracle/app/oracle/crs/bin/crsctl stop crs
5. OS Cluster Down                     -- 꼭 내릴 필요는 없음

* Database startup 방법

1. OS Cluster startup
2. as root> /fsoracle/app/oracle/crs/bin/crsctl start crs
3. srvctl start nodeapps -n <node#1_name>        -- Resource Start (Optional)
4. srvctl start nodeapps -n <node#2_name>        -- Resource Start (Optional)
5. sqlplus "/as sysdba"
   SQL> startup
   또는 srvctl start database -d db_name

* CRS setup 방법

1. Database Auto Restart Disable 방법
   crs_stat -p ora.DSCTMA.db > /tmp/ora.DSCTMA.db.cap
   crs_profile -update ora.DSCTMA.db -dir /tmp -o as=2,ra=0
   crs_register ora.DSCTMA.db -dir /tmp -u
   crs_stat -p  ora.DSCTMA.db | grep -E "REQUIRED_RESOURCES|RESTART_ATTEMPTS|AUTO_START"


2.Instance Auto Restart  Disable 방법
  crs_stat -p ora.DSCTMA.DSCTMA01.inst > /tmp/ora.DSCTMA.DSCTMA01.inst.cap
  crs_profile -update ora.DSCTMA.DSCTMA01.inst -dir /tmp -o as=2,ra=0        -- Resource Option 수정
  crs_register ora.DSCTMA.DSCTMA01.inst -dir /tmp -u
  crs_stat -p  ora.DSCTMA.DSCTMA01.inst | grep -E "REQUIRED_RESOURCES|RESTART_ATTEMPTS|AUTO_START"

  crs_stat -p ora.DSCTMA.DSCTMA02.inst > /tmp/ora.DSCTMA.DSCTMA02.inst.cap
  crs_profile -update ora.DSCTMA.DSCTMA02.inst -dir /tmp -o as=2,ra=0        -- Resource Option 수정
  crs_register ora.DSCTMA.DSCTMA02.inst -dir /tmp -u
  crs_stat -p  ora.DSCTMA.DSCTMA02.inst | grep -E "REQUIRED_RESOURCES|RESTART_ATTEMPTS|AUTO_START"

3.crsctl disable crs ( Automatic Startup Disable )

4. Network Down 시 Database shutdown 방지 방법
crs_stat -p ora.DSCTMA.DSCTMA01.lsnr > /tmp/ora.DSCTMA.DSCTMA01.lsnr.cap
crs_profile -update ora.DSCTMA.DSCTMA01.lsnr -dir /tmp -o as=0,ra=0
crs_register ora.DSCTMA.DSCTMA01.lsnr -dir /tmp -u
crs_stat -p  ora.DSCTMA.DSCTMA01.lsnr | grep -E "REQUIRED_RESOURCES|RESTART_ATTEMPTS|AUTO_START"


5. VIP auto restart 기능 OFF
crs_stat -p ora.<node#1_name>.vip > /tmp/ora.<node#1_name>.vip.cap
crs_profile -update ora.<node#1_name>.vip -dir /tmp -o as=0  # auto_start=0
crs_register ora.<node#1_name>.vip -dir /tmp -u
crs_stat -p  ora.<node#1_name>.vip | grep -E "REQUIRED_RESOURCES|RESTART_ATTEMPTS|AUTO_START"





대부분의 오라클 운영자는 '/as sysdba' 나 system 계정으로 작업을 합니다.
모니터링 중 특정 user에서 대해 수행되는 sql의 이상 등을 발견하는 경우 가끔 해당 sql을 직접 수행해 보는 경우가 있는데, v$sqltext 등에서 잡아온 sql을 그대로 수행하면 에러가 당연히 발생합니다.

해당 sql의 table list가 몇개 안될 경우 table 명 앞에 username을 지정하면, 수행은 가능하지만 귀찮기도 하고 어찌되었던 sql이 변경되 버리고 말죠.

또 다른 sql에서 권한이 있는 table을 수행할 경우에도 table의 각각의 username을 설정해야 합니다.
그게 아니라면 synonym을 따로 만들어 줘야 합니다.

이러한 경우 간단히 CURRENT_SCHEMA의 설정으로 이 모든 귀찮은 것을 해결할 수 있습니다.
CURRENT_SCHEMA는 현재 수행되는 SCHEMA환경을 지정한 SCHEMA로 변경해 줍니다.


SQL> CONNECT SCOTT/TIGER;
SQL> GRANT SELECT ON EMP TO JAMES;
SQL> CONNECT JAMES/TIGER;
SQL> SELECT COUNT(*) FROM SCOTT.EMP;
SQL> SELECT COUNT(*) FROM EMP;

SELECT COUNT(*) FROM EMP
                                       *
ERROR at line 1:
ORA-00942: table or view does not exist


위의 경우 당연히 에러가 발생하게 됩니다.
그러면 synonym이나 username을 추가 지정없이 수행하려면 다음의 CURRENT_SCHEMA를 SCOTT으로 지정해 줍니다.

SQL> CONNECT JAMES;
SQL> ALTER SESSION SET CURRENT_SCHEMA=SCOTT;
Session altered.

SQL> SELECT COUNT(*) FROM EMP;
COUNT(*)
10
...




'Oracle Database' 카테고리의 다른 글

Oracle Data Type  (0) 2009.12.18
Oracle 10g crs관련 명령들  (0) 2009.12.17
Oracle crs 설치 정보 확인  (0) 2009.12.11
Oracle Korea Magazin 2009년 겨울호 발간  (0) 2009.12.10
실례를 통해 알아본 Oracle Cluster의 활용  (0) 2009.11.25

1. crs 버전

# ./crsctl query crs softwareversion
CRS software version on node [mars] is [10.2.0.2.0]

# ./crsctl query crs activeversion
CRS active version on the cluster is [10.2.0.2.0]

2. crs 설치 정보

각 노드에서 olsnodes -n,   oicfg getif 를 수행한다.

예:
# olsnodes -n

결과
pvl31  1
pvl41  2

# oifcfg getif

결과
en0  192.1.10.0 global public
en1  192.1.11.0 global cluster_interconnect

3. crs 상태 확인

as oracle user

# crs_stat -t

ora.V10SN.V10SN1.inst                         ONLINE     ONLINE on opcbsol1
ora.V10SN.V10SN2.inst                         ONLINE     ONLINE on opcbsol2
ora.V10SN.db                                  ONLINE     ONLINE on opcbsol2
ora.opcbsol1.ASM1.asm                         ONLINE     ONLINE on opcbsol1
ora.opcbsol1.LISTENER_OPCBSOL1.lsnr           ONLINE     ONLINE on opcbsol1
ora.opcbsol1.gsd                              ONLINE     ONLINE on opcbsol1
ora.opcbsol1.ons                              ONLINE     ONLINE on opcbsol1
ora.opcbsol1.vip                              ONLINE     ONLINE on opcbsol1
ora.opcbsol2.ASM2.asm                         ONLINE     ONLINE on opcbsol2
ora.opcbsol2.LISTENER_OPCBSOL2.lsnr           ONLINE     ONLINE on opcbsol2
ora.opcbsol2.gsd                              ONLINE     ONLINE on opcbsol2
ora.opcbsol2.ons                              ONLINE     ONLINE on opcbsol2
ora.opcbsol2.vip                              ONLINE     ONLINE on opcbsol2

프로세스(process) 확인
ps -ef | grep oprocd     | grep -v grep
ps -ef | grep evmd     | grep -v grep
ps -ef | grep ocssd     | grep -v grep
ps -ef | grep crsd     | grep -v grep

4. network 구성

기본 구성
node        : pvl31, pvl41
interconnect: pvl31_int,pvl41_int
vip         : pvl31_vip(192.1.10.131),pvl41_vip(192.1.10.141)

en0 : public  (192.1.10.31/41)
en1 : private (192.1.11.31/41)

# netstat -l

Name  Mtu    Network   Address
En0   1500   link#2    0.2.55……..
En0   1500   192.1.10  pvl31
En0   1500   192.1.10  pvl31_vip
En1   1500   link#3    0.2.55…….
En1   1500   192.1.11  pvl31_int

5. voting , ocr 위치

[voting disk]
# crsctl query css votedisk

[ocr]
/var/opt/oracle/srvConfig.loc 에 지정되어 있거나 환경 변수인 SRV_CONFIG에 지정 되어 이다.

# ocrcheck

리눅스     : /var/oracle
기타 유닉스: /var/opt/oracle

내용:
ocrconfig_loc=<shared device>
local_only=FALSE

6. callout 디렉토리

$ORA_CRS_HOME/racg/usrco
$ORA_CRS_HOME/racg/usrco






사례 1) Database를 매뉴얼하게 생성 후 netca, srvctl add 명령으로 서비스 등록 후, srvctl start 명령 시 node 2번에서만 listener, instance가 시작되지 않고 crs_satat-t로 보았을 때 target =online, status UNKNOWN으로 나타난다.

사례 2) srvctl로 instnace startup시 다음과 같은 에러가 발생한다. Sqlplus로 메뉴얼하게 startup하면 정상적으로 startup된다.
srvctl start database -d BDB
PRKP-1001 : Error starting instance BDB1 on node bdb1
CRS-0215: Could not start resource 'ora.BDB.BDB1.inst'.
PRKP-1001 : Error starting instance BDB2 on node bdb2
CRS-0215: Could not start resource 'ora.BDB.BDB2.inst'.

사례 3) Veritas clusterware에 10gR2 CRS 설치 후 ons와 gsd가 구동되지 않는다.







Table partitioning은 Oracle의 대표적인 DW를 위한 기능이다.

이 기능은 테이블을 특정 partition set으로 나누어 관리 및 성능 상의 이점을 얻는다.
다음의 내용은 이러한 partition table의 종류와 간단한 정의이다.

Range Partitioning Method

column 값의 범위에 따라 partition을 나누는 가장 일반적인 방법이다. 이 방식은 대개 날짜와 같은 범위로 많이 나뉘게 되는데, AP의 성격에 따라 데이터가 특정 partition에 몰리는 경우 hash partition 방식이나, list partition 방식 등 다른 partition 방식을 통해 sub-partition을 나누기도 한다.


Create table test164874 (
ord_day         NUMBER(2),
ord_month       NUMBER(2),
ord_year        NUMBER(4),
ord_id  NUMBER(10)
)
storage (initial 12k next 12k pctincrease 0 minextents 1)
PARTITION BY RANGE (ord_year,ord_month,ord_day)
(
PARTITION P1 VALUES LESS THAN (2001,3,31) TABLESPACE PART1,
PARTITION P2 VALUES LESS THAN (2001,6,30) TABLESPACE part2,
PARTITION P3 VALUES LESS THAN (2001,9,30) TABLESPACE part3,
PARTITION P4 VALUES LESS THAN (2001,12,32) TABLESPACE part4
)
/


Hash Partitioning Method

partition key의 hash 값에 의해 partition으로 나눠진다. 일반적으로 균등한 분포를 가지며 성능상의 이유로 hash partition을 사용하기도 한다.


CREATE TABLE tabpart1(
ord_id   NUMBER(5),
ord_date DATE
)
PARTITION BY HASH(ord_id) PARTITIONS 16
STORE IN (tbs1,tbs2,tbs3,tbs4)
/


Composite Partitioning Method

Oracle 10g 이전엔 Range-List, Range-Hash composite partition 만 지원했으나, Oracle 11에서는 List-List, List-Hash, List-Range and Range-Range composite partition이 지원된다. 또 interval partition 방식에 대해서 Interval-Range, Interval-List and Interval-Hash가 가능하다.


   

CREATE TABLE TAB1(
     ord_id  NUMBER(10),
     ord_day    NUMBER(2),
     ord_month  NUMBER(2),
     ord_year   NUMBER(4)
     )
PARTITION BY RANGE (ord_year,ord_month,ord_day)
  SUBPARTITION BY HASH(ord_id) SUBPARTITIONS 8
  STORE IN (TBS1,TBS2,TBS3,TBS4,TBS5,TBS6,TBS7,TBS8)
     (  PARTITION P1 VALUES LESS THAN (2001,3,31),
        PARTITION P2 VALUES LESS THAN (2001,6,30),
        PARTITION P3 VALUES LESS THAN (2001,9,30),
        PARTITION P4 VALUES LESS THAN (2001,12,31)
 )
/


List Partitioning Method

Oracle 9i에 소개된 partition 방식으로 partition key의 값 자체에 의해 분리된 partition으로 데이터가 저장된다. 값에 의해 데이터가 partition 되므로 전혀 관계없는 데이터 값을 하나의 partition에 저장할 수 있다.

CREATE TABLE sales_list
(salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_state VARCHAR2(20),
sales_amount NUMBER(10),
sales_date DATE)
PARTITION BY LIST(sales_state)
(
PARTITION sales_west VALUES('California', 'Hawaii'),
PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'),
PARTITION sales_central VALUES('Texas', 'Illinois')
PARTITION sales_other VALUES(DEFAULT)
);

Interval Partitioning Method

Interval partition은 range partition 기능의 확장으로 개별 범위를 명시적으로 정의하지 않아도 해당 interval에 속하는 데이터가 입력 될 때 partition이 자동으로 생성된다.

CREATE TABLE interval_sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2) )
PARTITION BY RANGE (time_id)
INTERVAL(numtodsinterval(7,'day'))
( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2007', 'DD-MM-YYYY')) );

Reference Partitioning Method

Oracle 11g에서 소개된 partition 방식으로 “모-자” 관계의 table 간에 모 table의 partition key column의 복제 없이 모 table의 partition 구조를 따라가게 됩니다.

SQL> CREATE TABLE ref_part_parent
2 (pcol1 NUMBER PRIMARY KEY,
3 pcol2 VARCHAR2(10))
4 PARTITION BY RANGE (pcol1)
5 (PARTITION p1 VALUES LESS THAN (100),
6 PARTITION p2 VALUES LESS THAN (200),
7 PARTITION p3 VALUES LESS THAN (300),
8* PARTITION p4 VALUES LESS THAN (MAXVALUE))

Table created.

SQL> CREATE TABLE ref_part_child
2 (ccol1 NUMBER NOT NULL,
3 CONSTRAINT ccol1_fk FOREIGN KEY(ccol1) REFERENCES ref_part_parent(pcol1))
4 PARTITION BY REFERENCE(ccol1_fk);

Table created.

System Partitioning Method

oracle 11g에서 소개된 partition 방식으로 다른 partition 방식과의 가장 큰 차이는 partition key를 정의하지 않는데 있다. 즉, system이 알아서 partition을 해준다.

CREATE TABLE systab (c1 integer, c2 integer)
PARTITION BY SYSTEM
(
PARTITION p1 TABLESPACE tbs_1,
PARTITION p2 TABLESPACE tbs_2,
PARTITION p3 TABLESPACE tbs_3,
PARTITION p4 TABLESPACE tbs_4
); 

Virtual column-based Partitioning Method

virtual column partition 방식은 partition key의 값이 물리적으로 존재하지 않은 경우에도 virtual column 값에 의해 partition을 할 수 있게 한다.

 CREATE TABLE employees
  (employee_id  number(6) not null, first_name varchar2(30), 
   last_name varchar2(40) not null, email varchar2(25),
   phone_number varchar2(20), hire_date  date not null,
   job_id  varchar2(10) not null, salary number(8,2),
   commission_pct number(2,2), manager_id  number(6),
   department_id number(4),
   total_compensation as (salary *( 1+commission_pct))
   )
    PARTITION BY RANGE (total_compensation)
     (
       PARTITION p1 VALUES LESS THAN (50000),
       PARTITION p2 VALUES LESS THAN (100000),
       PARTITION p3 VALUES LESS THAN (150000),
       PARTITION p4 VALUES LESS THAN (MAXVALUE)
     );






oracle은 데이터베이스 사용자들이 수행한 관련 정보를 SGA라는 메모리에 저장하여 공유합니다.
SGA에는 buffer cache, shared pool, log buffer 등의 많은 구성요소들이 있습니다.



library cache는 shared pool내에서 sql 수행 정보등을 저장하는 가장 중요한 구성요소 중 하나라고 할 수 있습니다.
library cache의 구조는 다음과 같습니다.


SQL이 수행되면 해당 구조에 어떻게 정보가 저장될까요?
sql이 수행되었을 때 library cache내에서 어떻게 저장되는지, LIBRARY_CAHCE dump와 구조를 비교해 보았습니다.
(할일이 없어 그런건 아닙니다 --;)




2009.10.30 update:

library cache 관련해서 좋은 문서가 있어 Link 겁니다.

OWI를 활용한 shared pool 진단 및 튜닝







oracle homepage에서 oracle의 모든 소프트웨어는 무료로 다운로드가 가능합니다. 예전엔 오라클을 구입하면 CD나 그 이전엔 Tape 등으로 제품을 공급했지만, 이제는 Oracle homepage를 통해 제품을 공급합니다.

oracle korea homepage에도 download는 가능했지만, 현재는 link가 좀 깨져 있는 것 같네요.
Oracle 11G R2는 아예 항목이 없구.. --;


위와 같이 oracle homepage에 들어가면 오른쪽 부분에 download 링크가 있습니다. 이곳으로 들어가 보면 아래와 같이 download 받을 수 있는 product들의 list가 나옵니다.



Oracle homepage를 통해 다운로드 받을 수 있는 제품은 다음과 같습니다. 거의 모든 Oracle product는 다 있는 것 같습니다.

데이터베이스
미들웨어
개발자 도구
애플리케이션
엔터프라이즈 리눅스
엔터프라이즈 관리 도구
드라이버
유틸리티/플러그인
마이그레이션 툴
과거 제품




모든 소프트웨어는 무료로 다운로드 가능합니다. 다운로드한 소프트웨어에는 개발 라이센스가 적용되며, 애플리케이션의 개발, 프로토타이핑 작업 시에만 제품의 풀 버전을 사용할 수 있습니다. 운영 환경에서 제품을 사용하고자 하는 경우 오라클 영업 담당자 또는 온라인 스토어에서 제품을 구매하실 수 있습니다.

용량이 큰 제품을 다운로드하는 경우에는 GetRight 등의 다운로드 관리자 소프트웨어를 사용할 것을 권장 드립니다.

오라클은 모든 제품을 다운로드 받을 수 있습니다.
그러나 이 제품들은 개발 라이센스가 적용되어 운영 등의 용도로의 사용은 불가능합니다.






다음은 oracle korea에서 만든(?) oracle 9i의 new feature인 data block prefetching에 대한 내용을 발췌했습니다..

데이타 블럭 프리페칭(Data Block prefetching)은 Oracle9i에서 새롭게 선보이는 내부 최적화 기능으로서 특정한 경우에 질의 응답 시간을 크게 개선할 수 있다. 데이타 블럭 프리페칭은 테이블 조회(lookup)에 의해 사용된다.

색인 액세스 경로가 선택되고 질의가 색인 만으로는 충족될 수 없을 경우, rowid가 지시하는 데이타 행 역시 페치되어야 한다. 이 데이타 행 액세스(테이블 조회)에 대한 rowid는 올바른 rowid의 어레이가 지시히는 블럭 어레이를 읽는 것을 수반하는 데이타 블럭 프리페칭을 사용해 개선됐다.

블럭 프리페칭은 I/O 처리 성능에 대한 보다 효과적인 활용과 가능할 경우 항상 병렬 읽기를 발행해 응답시간을 단축시킬 수 있도록 지원한다.

데이타 블럭 프리페칭은 대개 색인이 빈약하게 클러스터링되고 테이블 액세스가 전반적으로 낮은 버퍼 캐시 적중률 의 랜덤 디스크 액세스를 수반하는 경우 유용하다.

이 경우 질의는 시스템 상에 가용 I/O 대역폭이 있더라도 이들 단일 블록 들이 캐시로 동시에 읽혀지기를 기다림으로써 손쉽게 I/O 최대값에 도달할 수 있다. 데이타 블럭 프리페칭 주요 색인에 만족스러운 rowid의 수가 축적될 때까지 테이블 블럭의 읽기를 지연시킨다. I/O 완료 시 훨씬 짧게 기다림으로써 데이타베이스 인스턴스가 CPU 자원을 보다 효과적으로  활용할 수 있도록 지 원하게 된다.



샘플 문제:
TPC-H 스펙의 질의 17은 데이타 블럭 프리페치가 사용될 수 있는 대표적인 예제이다.
해당 브랜드 및 컨테이너 유형의 경우, 평균 보다 20% 적은 수량의 주문이 접수된다면, 연평균 매출액 중 총손실액은 어느 정도인가?

데이타베이스는 7년 간의 데이타를 포함하고 있다.

구현:

다음 질의는(DOP 16) Oracle8i and Oracle9i 모두에서 위의 문제를 구현하는데 사용된다

select  sum(l_extendedprice) / 7.0 as avg_yearly parts,
from   parts,  lineitem l1
where  p_partkey = l_partkey
and p_brand = 'Brand#23'
and p_container = 'MED BOX'
and l_quantity <(
                           select 0.2 * avg(l_quantity)
                           from lineitem l2
                           where l_partkey = p_partkey);

Oracle8i에서 질의 수행시간은 264초였다
Oracle9i에서 질의 수행시간은 180초였으며 이는 31.82%의 향상을 나타낸다


다음은 위의 white-paper의 원문입니다.
영어는 잼병이지만, 아래의 원문을 보기전까지는 위의 말이 뭔소린지 이해가 안됬다능..
(한글 공부를 다시해야 하나 .. 고민)

Data block prefetching is an example of an 'under the covers' performance feature provided in Oracle9i. This feature is entirely transparent to both the end-users and database administrator, yet it can improve performance by 30% or more for individual queries.

Data block prefetching can significantly improve the performance of queries which retrieve large numbers of rows via a b-tree index.Data block prefetching provides the largest performance gains for queries which use an index that is 'poorly clustered'. (An index is said to be 'clustered' if the data in the underlying table is in the same order as the index keys. A poorly clustered index is one in which the rows in the underlying table are in an entirely different order than the index keys).

Poorly clustered indexes are quite common, since most tables can only have at most one well-clustered index. Previously, accessing a large number of rows using a poorly clustered b-tree index could be expensive. Each row accessed by the index would likely be in a separate data block and thus would require a separate I/O operation.

The buffer cache is ineffective for these queries because the I/Os are spread throughout the table, and the buffer cache hit ratio is low.

In such cases, a query can easily become I/O bound, waiting for single data blocks to be read into the cache one at a time, even though there may be available I/O bandwidth on the system. With data block prefetching, Oracle delays data blocks reads until multiple rows specified by the underlying index are ready to be accessed and then retrieves multiple data blocks at once, rather than reading a single data block at a time.
Block prefetching allows better utilization of the I/O capacity, and provides a further reduction in response time by issuing I/O operations in parallel whenever possible.


요약하자면 index scan 사용할때 내부적으로 수행되는 기능으로 요구되는 rowid에 의한 access를 일정 block이 될때까지 기다려 한꺼번에 수행하는 기능 입니다.

With data block prefetching, Oracle delays data blocks reads until multiple rows specified by the underlying index are ready to be accessed and then retrieves multiple data blocks at once, rather than reading a single data block at a time.





오라클은 DB link와 synonym을 통해서 remote oracle database에 있는 table을 local database 서버에 있는 것처럼 query가 가능합니다. 하지만 내부적으로는 remote database로 관련 query를 수행해 return 되는 데이터를 이용해 결과 값을 출력해줍니다. (당연하죠..ㅎㅎ) 그러나 remote database의 table에 대한 정보가 없기 때문에 일반적인 role을 이용해 query를 생성, 수행 요청하게 됩니다.

National Hurricane Center
(아무 상관없는 그림이지만 내용이 작아 붙여 봅니다. ^^;)


아래 box 안의 role은 remote database로 query를 수행하는 일반적인 규칙입니다. 참조한 문서가 오래전꺼긴 한데, 큰 차이는 없으리라.. 생각되는 군요.

간단히 보자면 ..

MAX() 같은 group 함수는 전달 되지 않으니, 테이블의 범위에 따라서는 엄청난 데이터가 local로 전송된 후 MAX() 값 등을 구할 수도 있겠군요.

또 상수 조건이 있는 table은 remote로 해당 조건이 전달 되겠지만, 그렇지 않은 경우는 FULL TABLE SCAN을 할 수 도 있겠고요..

ORDER BY도 local에서 수행된다니, 대상이 많으면 이것도 성능에 영향을 줄 수 있겠네요.

  1. Aggregate functions like MAX(), COUNT() are NOT passed across the net but rather are performed on the local node.
  2. Expressions in the SELECT list are decomposed into columns and evaluated when the data is retrieved.
  3. Only a relevant subset of columns are fetched across the net.
  4. An expression in a WHERE clause is passed across to the remote database if it compares the remote column to a literal (eg ename = 'KING').
  5. Expressions in a WHERE clause are passed to the remote database if all columns are in the expression are located in the same remote table the remote database (eg emp.sal * 0.20 > emp.bonus)
  6. Datatype conversion functions like TO_CHAR() in a WHERE clause are subject to the conditions in #4 and #5.
  7. The optimizer only has knowledge of local indexes when making decisions about how to execute a query.
  8. Remote indexes can be still be used to resolve the decomposed query sent to the remote database.
  9. GROUP BY is not sent to the remote database.
  10. ORDER BY is not sent to the remote database.

이 내용은 oracle metalink "Note 1004553.6 DISTRIBUTED QUERY ANALYSIS"를 참조했습니다.

써놓고 보니 DB link를 이용하지 말라는 이야기가 된 것 같아 몇줄 더 씁니다.

물론 group 함수, order by 등의 처리가 local database에서 처리가 되지만 이러한 것들을 피해가는 방법들은 다 있기 마련이죠. view를 만들어 remote에서 group 함수 처리를 한다든지, join의 경우 아예 한 곳에서 처리하게 한다든지, temporary table을 만들어 처리하거나.. 뭐 이런

요는 network을 통한 전달되는 데이터의 양을 얼마나 많이 줄이느냐 겠죠.

위의 metalink note 보시면 몇몇 예제가 있으니 참조하세요.






이 두개의 OPTION은 Precompile된 모든 program의 implicit, exeplicit cursor 에 모두 영향을 준다. (pro*ada의 경우는 약간의 예외가 있다.)

CURSOR에는 program cursor와 oracle cursor 두 가지가 있다.

program cursor는 SQL문으로 인해 생기는 data 구조이다. program cursor는 procompiler에 의해 발견된 각 SQL문 마다 선언된다. 다음의 문장이 program 안에 있다고 하자.

EXEC SQL DECLARE SEL_EMP_CURS CURSOR FOR...
EXEC SQL INSERT...

그렇다면 c1과 c2의 두개의 program cursor가 선언될 것이다.

Oracle cursor ( context area 라고도 한다.)는 실행 중에 생성이 된다. 이 공간은 parse된 문장과, host 변수의 주소값, 그 외에 SQL문을 실행하기 위해 필요한 정보를 가지고 있다.

이 두 개의 cursor는 cursor cache를 통해서 서로 연결되어 있다. 이 cursor cache의 초기 크기는 MAXOPENCURSORS option에 의해 결정이 된다. 아래의 그림은 이러한 연관 관계를 설명한다.

?ui=2&view=att&th=124760a212c92f44&attid=0.1&disp=attd&realattid=ii_124760a212c92f44&zw


이러한 관점에서 HOLD_CURSOR와 RELEASE_CURSOR는 연관 관계는 다음과 같다.

HOLD_CURSOR option는 program cursor와 해당 cache와의 관계를 다룬다. 반면, RELEASE_CURSOR는 Oracle cursor와 cache와의 관계를 다룬다. 만약 자주 재사용되는 SQL문이 보다 빠른 속도를 갖게 하기 위해서는 이 SQL문과 예상되는 Oracle cursor가 붙어 있도록 하는 것이 좋다.

SQL문이 Oracle cursor와 붙어있다는 말은 SQL문과 해당 Oracle cursor 사이의 연결이 지속적으로 유지되고 있는것을 의미한다. 위에서 언급한 바와 같이 Oracle cursor(context area)에서는 parse된 문장이나 host변수의 주소값 등과 같이 중요한 정보가 들어 있게 되므로 SQL문장과 Oracle cursor(context area) 를 붙여두는 것이 유용하다.


이와 같이 문장과 cache를 붙여두기 위해서 HOLD_CURSOR와 RELEASE_CURSOR가 사용된다.

HOLD_CURSOR=YES option이 사용되면 cache들은 재사용이 가능하도록 flag을 표시 할 수 없도록 한다.
이것은 매우 중요한 의미를 가지는데, 만약 cache들이 사용이 되어지고 새로운 SQL문마다 각기 새로운 cache들이 할당이 된다면, MAXOPENCURSORS에 의해 결정된 수만큼의 cache가 할당된 후에 추가의 cache는 재사용이 가능하도 flag가 표시 된 cache를 사용하기 때문이다.

위의 그림을 참조로 예를 들면 다음과 같다.
C(1) cache가 재사용이 사능하도록 표시가 되어있고 EXEC SQL SELECT 문이 실행이 된다고 가정을 할 경우, program cursor P(MAXOPENCURSORS+1)가 생성되고 이것은 cache와 Oracle cursor가 필요하다. 그러나 이때 MAXOPENCURSORS에 의해 결정된 값만큼의 숫자의 cache가 이미 사용되어졌다면, 이 문장은 C(1)의 cache와 거기에 해당하는 Oracle cursor를 할당받는다. 그리고 이 cache과 Oracle cursor는 비워지고 새로운 SQL문으로 다시 parse가 되어진다.


위의 option과 함께 사용되는 것이 RELEASE_CURSOR=NO이다. 이것은 cache들과 Oracle cursor사이의 관계를 규정한다. 이 option은 parse된 문장이 실행하고 난후의 상태를 관리한다. 이때 할당된 memory는 사용가능한 상태로 유지 되어진다. 이 memory를 풀어주기 위해서는 RELEASE_CURSOR=YES를 사용한다. 이 option을 사용 하면 이 cache에 연결되어 있는 다음 문장들은 추가적으로 parse를 다시 해야하는 부담이 있다.



HOLD_CURSOR와 MAXOPENCURSORS는 밀접한 관계를 가진다.

만약 모든 cursor cache들이 현재 "재사용 불가"로 표시되어있다면 ( 이런 경우는 explicit하게 열린 cursor가 close되지 않은 상태로 fetch를 진행하는 경우와 같이 cursor cache를 사용하는 모든 문장이 실행중인 경우와 HOLD_CURSOR option을 사용한 경우등이 있다.),  새로운 cursor를 위해서는 실행중에 cursor cache를 확장해야 한다. (즉,  MAXOPENCURSORS가 10이라면, 11번째의 cursor cache를 생성한다.) 이때 11 번째 생성된 cache는 cursor가 close되어도 제거되지 않는다.

MAXOPENCURSOR를 작게 잡는 것은 memory를 절약 할수있으나 cache가 추가될때는 비용이 많이든다. 반면, 높게 잡을 경우는 추가 비용에 대한 부담이 줄어드는 많큼 필요이상의 memory를 사용하게 된다. 그리고 무조건 가장 오래된 cache를 재사용하도록 허용하는 것이 옳바른 것은 아니다.

만약 10개의 explicit cursor를 선언해서 open한 user가 11번째의 cursor를 사용하기 위하여 가장 오래된 program cursor를 재사용하게 된다면 user는 첫번째 cursor에 대한 위치를 잃어버리게 되어 이 곳에서 fetch를 수행할 수 없게 된다.

만약 program 안에서 문장의 재사용이 일어나지 않을 경우는 HOLD_CURSOR=NO, RELEASE_CURSOR=YES를 사용한다. HOLD_CURSOR=NO 는 cache들이 필요에 따라 자동적으로 "재사용"으로 표시되게 한고, RELEASE_CURSOR=YES는 Oracle cursor 가 자동적으로 해제되고 parse된 문장을 잃어 버리게 한다. site의 memory에 대한 문제로 인해 Oracle cursor들의 숫자가 제한을 받을 경우는 이 option을 사용 해야 한다.

이 때 만약 RELEASE_CURSOR=YES를 사용하게되면 자동적으로 HOLD_CURSOR=YES는 사용할수 없게 된다. RELEASE_CURSOR=YES가 Oracle cursor와 cache사이의 연결 을 끊어 버리고 Oracle cursor를 해제 시켜 버린다. 그러므로 심지어 program cursor가 cache와 HOLD_CURSOR=YES에 의해 연결되어 있어도 memory를 다시 할당하고 다시 parse를 해야 한다.그러므로 RELEASE_CURSOR=YES를 주면 HOLD_CURSOR=YES를 준 이점이 하나도 없다.

이 내용은 oracle discussion forum의 "hold_cursor & release_cursor"와 아래 첨부한 oracle internet seminar 자료, 그리고 oracle metalink의  Note 2055810.6 "Precompiler HOLD_CURSOR and RELEASE_CURSOR Options"를 참고했습니다.
.






SQL> alter table p_emp rename partition emp_p1 to emp_p01;
SQL> alter table p_emp rename partition emp_p2 to emp_p02;
SQL> alter table p_emp rename partition emp_p3 to emp_p03;

SQL> select table_name,partition_name,high_value from dba_tab_partitions where table_name = 'P_EMP';

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ ----------
P_EMP                          EMP_MAX                        MAXVALUE
P_EMP                          EMP_P01                        2000
P_EMP                          EMP_P02                        3000
P_EMP                          EMP_P03                        4000

partition table의 partition 이름 변경시 index의 partition 이름은 같이 변경되지 않는다. 따라서 index partition이름도 같이 변경해 주어야 한다.

SQL>  select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name = 'P_EMP_I';

INDEX_NAME           PARTITION_NAME                 HIGH_VALUE STATUS
-------------------- ------------------------------ ---------- --------
P_EMP_I              EMP_MAX                        MAXVALUE   USABLE
P_EMP_I              EMP_P1                         2000       USABLE
P_EMP_I              EMP_P2                         3000       USABLE
P_EMP_I              EMP_P3                         4000       USABLE

SQL> ALTER INDEX p_emp_i RENAME PARTITION emp_p1 TO emp_p01;
SQL> ALTER INDEX p_emp_i RENAME PARTITION emp_p2 TO emp_p02;
SQL> ALTER INDEX p_emp_i RENAME PARTITION emp_p3 TO emp_p03;

SQL>  select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name = 'P_EMP_I';

INDEX_NAME           PARTITION_NAME                 HIGH_VALUE STATUS
-------------------- ------------------------------ ---------- --------
P_EMP_I              EMP_MAX                        MAXVALUE   USABLE
P_EMP_I              EMP_P01                        2000       USABLE
P_EMP_I              EMP_P02                        3000       USABLE
P_EMP_I              EMP_P03                        4000       USABLE










1. partition table & local index 생성.

CREATE TABLE p_emp (sal NUMBER(7,2))
 PARTITION BY RANGE(sal)
 (partition emp_p1 VALUES LESS THAN (2000),
  partition emp_p2 VALUES LESS THAN (4000),
  partition emp_max VALUES LESS THAN (MAXVALUE));

 create index p_emp_i on p_emp (sal) local;

insert into p_emp values (1000);
insert into p_emp values (2000);
insert into p_emp values (3000);
commit;

SQL> select table_name,partition_name,high_value from dba_tab_partitions where table_name = 'P_EMP';

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ ----------
P_EMP                          EMP_MAX                        MAXVALUE
P_EMP                          EMP_P1                         2000
P_EMP                          EMP_P2                         4000

SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions  where index_name = 'P_EMP_I';

INDEX_NAME           PARTITION_NAME                 HIGH_VALUE STATUS
-------------------- ------------------------------ ---------- --------
P_EMP_I              EMP_MAX                        MAXVALUE   USABLE
P_EMP_I              EMP_P1                         2000       USABLE
P_EMP_I              EMP_P2                         4000       USABLE

2. split partition table

전체 partition range의 중간에 partition을 삽입히기 위해서는 split partition 명령을 사용해야 한다. MAXVALUE가 없는 상태에서 제일 끝에 추가할 경우는 add partition 명령을 사용한다.

SQL> alter table p_emp split partition emp_p2 at (3000) into (partition emp_p2, partition emp_p3);

SQL> select table_name,partition_name,high_value from dba_tab_partitions where table_name = 'P_EMP';

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ ----------
P_EMP                          EMP_MAX                        MAXVALUE
P_EMP                          EMP_P1                         2000
P_EMP                          EMP_P2                         3000
P_EMP                          EMP_P3                         4000


SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions  where index_name = 'P_EMP_I';

INDEX_NAME           PARTITION_NAME                 HIGH_VALUE STATUS
-------------------- ------------------------------ ---------- --------
P_EMP_I              EMP_MAX                        MAXVALUE   USABLE
P_EMP_I              EMP_P1                         2000       USABLE
P_EMP_I              EMP_P2                         3000       UNUSABLE
P_EMP_I              EMP_P3                         4000       UNUSABLE

partition table의 local partition index도 같이 split 되나 데이터가 있는 상태라면 "UNUSABLE" 상태로 되므로 이에 대한 rebuild 작업이 반드시 필요하다.

If the partition you are splitting contains data, the ALTER TABLE...SPLIT PARTITION statement marks UNUSABLE the new partitions (there are two) in each local index, all global index partitions, and any
global nonpartitioned index. You must rebuild such affected indexes or index partitions.

(Metalink Note 165599.1 Top Partitioned Tables Issues 중.)


SQL> alter index p_emp_i rebuild partition emp_p2;
SQL> alter index p_emp_i rebuild partition emp_p3;

SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name = 'P_EMP_I';

INDEX_NAME           PARTITION_NAME                 HIGH_VALUE STATUS
-------------------- ------------------------------ ---------- --------
P_EMP_I              EMP_MAX                        MAXVALUE   USABLE
P_EMP_I              EMP_P1                         2000       USABLE
P_EMP_I              EMP_P2                         3000       USABLE
P_EMP_I              EMP_P3                         4000       USABLE







oracle 9i rac 이전 버전인 ops에서는 양 노드간의 data sync를 맞추기 위해 file ping 방식을 사용했다. 즉, 한 노드에서 변경된 내용을 다른 노드에서 읽고자 했을 때 변경된 데이터를 file에 write하고 이를 다시 다른 노드에서 읽는 방식을 사용했다.

이 방식은 노드 간에 동일한 데이터를 읽게 되는 경우 file writing이 발생되므로 성능에 심각한 영향을 끼칠 수 있다.
따라서 OPS에서는 양 노드의 업무 분할이 필요했고, 공통으로 사용되는 code성 데이터 사용을 가능한한 최소화 해야 되었다.

oracle 9i RAC에서는 memory간의 통신으로 데이터를 전달하는 cache fusion 방식이 사용되면서 노드간의 데이터 sync 방식의 성능은 크게 향상되었다. 이제 노드간의 데이터 sync는 이전 버전에서 처럼 더이상 심각한 문제가 문제를 유발하지 않으며, 노드간의 업무 분할은 더이상 필요가 없어 졌다.

cache fusion으로 인한 pinging 현상의 성능이 좋아져서 업무 분할이 필요없어짐으로 해서 비로서 load balance 기능 구현이 가능해 졌으며, 이 을 이용해 각 노드의 resource를 보다 효율적으로 사용할 수 있다.

load balance는 client-side load balance와 server-side load balance로 나눌 수있다.

client-side load balance는 tnsnames.ora내의 tns alias 설정시 load balnace 설정에 의해 가능하며 기술된 listener list에 대해 random 하게 connection을 이루게 된다. 따라서 connection의 개수는서버의 resource 사용량이나 기존의 connection 갯수와는 상관없이 random 하게 이루어 진다.

2009년 10월 13일 update :

net_service_name=
 (DESCRIPTION=
  (LOAD_BALANCE=on)
  (ADDRESS=(PROTOCOL=tcp)(HOST=sales1-svr)(PORT=1521))
  (ADDRESS=(PROTOCOL=tcp)(HOST=sales2-svr)(PORT=1521)))
  (CONNECT_DATA=(SERVICE_NAME=sales.us.acme.com))


server-side load balnace는 listener로 들어오는 client connection을 oracle background process인 pmon의 dynamic service registration에 의해 수집된 profile statistic 정보를 이용해 덜 바쁜 노드, 덜 바쁜 instance, 덜 바쁜 dispatcher 순으로 connection을 전이 시켜 준다.

server-side load balance는 init file내의 service_name, local_listener와 remote_listener 등록이 필요하다.

2009년 10월 13일 update :

다음의 그림은 server-side load balancing 구성에 대한 그림이다. instance A는 listener A를 local listener로 갖고 있으며, listener B를 remote listener로 갖는다. 마찬가지로 instance B는 listener B를 local listener로 listener A를 remote listener로 갖는다.

각각의 instance의 PMON process는 주기적으로 해당 node에 대한 profile statistic을 listener에게 알려주어 listener로 들어오는 client connection request는 load가 적은 노드로 connection request를 redirect 하기도 한다.






CASE 문은 PL/SQL 없이 SQL문 안에서 if-then-else logic을 구현하는 데 사용합니다.

CASE 문은 "Simple case expressions" 와 "Searched case expressions"로 나눌 수 있는데, "Simple case expressions"은 expression을, "Searched case expressions"은 condition을 사용해 return 값이 결정된다.

다음은 simple CASE 구문의 사용방법이다.

CASE search_expression
  WHEN expression1 THEN result1
  WHEN expression2 THEN result2
  ...
  WHEN expressionN THEN resultN
  ELSE default_result
END

위의 예제는 search_expression이 expression1,2,3.. 일 경우 result1,2,3.. 이 출력된다.

SELECT product_id, product_type_id,
  CASE product_type_id
  WHEN 1 THEN 'Book'
  WHEN 2 THEN 'Video'
  WHEN 3 THEN 'DVD'
  WHEN 4 THEN 'CD'
  ELSE 'Magazine'
  END
FROM products;

PRODUCT_ID PRODUCT_TYPE_ID CASEPROD
---------- --------------- --------
         1               1 Book
         2               1 Book
         3               2 Video
         4               2 Video
         5               2 Video
         6               2 Video
         7               3 DVD
         8               3 DVD
         9               4 CD
        10               4 CD
        11               4 CD
        12                 Magazine

다음은 searched CASE 구문의 사용방법이다.

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ...
  WHEN conditionN THEN resultN
  ELSE default_result
END

위의 예는 condition1,2 가 true 일 경우 result1,2,.. 출력한다.

SELECT product_id, product_type_id,
  CASE
  WHEN product_type_id = 1 THEN 'Book'
  WHEN product_type_id = 2 THEN 'Video'
  WHEN product_type_id = 3 THEN 'DVD'
  WHEN product_type_id = 4 THEN 'CD'
  ELSE 'Magazine'
  END
FROM products;

PRODUCT_ID PRODUCT_TYPE_ID CASEPROD
---------- --------------- --------
         1               1 Book
         2               1 Book
         3               2 Video
         4               2 Video
         5               2 Video
         6               2 Video
         7               3 DVD
         8               3 DVD
         9               4 CD
        10               4 CD
        11               4 CD
        12                 Magazine

SELECT product_id, price,
  CASE
  WHEN price > 15 THEN 'Expensive'
  ELSE 'Cheap'
  END
FROM products;

PRODUCT_ID      PRICE CASEWHENP
---------- ---------- ---------
         1      19.95 Expensive
         2         30 Expensive
         3      25.99 Expensive
         4      13.95 Cheap
         5      49.99 Expensive
         6      14.95 Cheap
         7      13.49 Cheap
         8      12.99 Cheap
         9      10.99 Cheap
        10      15.99 Expensive
        11      14.99 Cheap
        12      13.49 Cheap




 
oracle 운영 중 DB hang 이나 slowdown(거의 hang) 현상이 발생하면, 사실 거의 할 수 있는 일이 없다. monitoring이나 trace 등의 데이터는 resource 부족으로 수행이 잘 안되며, 운영 부서에서 빨리 문제 해결해 달라고 엄청 push 하기 마련이다. 대부분 resource 문제는 database restart하면 풀린다. 물론 recovery 등의 작업은 다시 올리면 또 돌긴하지만 이런 작업은 심각한 hang이나 slowdown 현상을 대개 보이지는 않는다.

암튼 조치 후에 문제 분석을 위해서는 반드시 system state dump를 수행해야 한다. 요게 없으면 문제에 대한 기본적인 분석이 어려워 어떤때는 현상 파악 자체도 잘 안되는 경우가 있다. (10g에서는 active session history나 AWR 등의 데이터로 인해 좀 나아졌다)

system state dump는 몇분 주기로 여러번 수행해야 특정 resource의 변동사항을 유추해 낼 수 있다.
일반적으로 system state dump는 두가지의 방법으로 수행된다.

1.  alter session set events 'immediate trace name SYSTEMSTATE level 10';

2.  $ sqlplus (svrmgrl)
      connect internal
      oradebug setospid <process ID>
      oradebug unlimit
      oradebug dump systemstate 10

드물지만 어떤 경우엔 database로의 접근 자체가 안되는 경우가 발생한다(max process 등..). 이럴 경우 당연히 alter session이나 oradebug를 이용해 system dump 수행 자체가 안된다.

이러한 상황에서 system dump state dump를 수행할 수 있는 방법이 있는데, OS의 debug tool을 이용해 직접 dump를 수행하는 function call을 수행하는 방법과 10g에서 소개된 sqlplus의 -prelim option을 사용하는 방법이 그것이다.

OS debuger를 이용해 function call을 하는 경우 조심할 점은 attach 한 process가 terminated 될 수 있으므로 가급적 oracle background process에 대한 수행은 안하는 게 좋다.

dbx -a PID (where PID = any oracle shadow process)
dbx() print ksudss(10)
...return value printed here
dbx() detach

(saki) % dbx -a 28348
Waiting to attach to process 28348 ...
Successfully attached to oracle.
warning: Directory containing oracle could not be determined.
Apply 'use' command to initialize source path.

Type 'help' for help.
reading symbolic information ...
stopped in read at 0xd016fdf0
0xd016fdf0 (read+0x114) 80410014        lwz   r2,0x14(r1)
(dbx) print ksudss(10)
2
(dbx) detach

oracle 10g를 사용하는 경우 사용할 수 있는 option이 하나 있는데, sqlplus의 prelim option이다.
이 방법은 기존의 oradebug를 사용하는 방법과 동일하며 단지 sqlplus 수행시 option으로 지정하면 된다.
 
prelim option에 관한 내용은 10g, 11g sqlplus manual에도 나와 있지 않으므로.. 생략 !!

sqlplus -prelim / as sysdba

export ORACLE_SID=PROD                                 ## Replace PROD with the SID you want to trace
sqlplus -prelim / as sysdba
oradebug setmypid
oradebug unlimit;
oradebug dump systemstate 10

참고한 자료들 입니다.

Note 121779.1 - Taking Systemstate Dumps when You cannot Connect to Oracle
http://oraclue.com/2008/09/25/sqlplus-preliminary-connection/
http://yaping123.wordpress.com/2008/08/30/sqlplus-using-prelim-connection/

2009년 8월 26일 추가.
sqlplus의 relim option 관련해서 참고할 만한 블로그 입니다.

oradeblog : SQL*Plus 'prelim' connection


2009년 10월 09일 추가
-relim option은 정상적인 세션이 아니기 때문에 systemstate dump 명령이 수행이 되더라도 dump가 끝까지 떨어졌는지 확인이 필요합니다. trace file을 여셔서 맨 끝에 "end of system state dump(?)"라고 되어 있는지 확인해 보세요.





oracle 11g에서 제공하는 result cache 기능에 대한 설명입니다.
shared pool내에 결과가 저장되는 영역이 새로 할당되는 군요.
세션 레벨에서 hint를 통해 지정할 수 있답니다..

Implementing SQL Query Result Cache

The new SQL Query Result Cache enables explicit caching of queries and query fragments in an area of the shared pool called Result Cache Memory.
When a query is executed the result cache is built up and the result is returned.
The database can then use the cached results for subsequent query executions, resulting in faster response times.
Cached query results become invalid when data in the database object(s) being accessed by the query is (are) modified.

You can enable Query Result Cache at the database level using the RESULT_CACHE_MODE initialization parameter in the database initialization parameter file.
The same parameter can also be used at the session level using the ALTER SESSION command.

RESULT_CACHE_MODE can be set to:

    * MANUAL : (default) you have to add the RESULT_CACHE hint to your queries in order for results to be cached or to be served out of the cache.
               The RESULT_CACHE hint can also be added in sub queries and in-line views.
    * FORCE  : results are always stored in the Result Cache Memory if possible.


자세한 내용과 example은 OTN에서..
Improving Application Performance Using Result Cache






BFT (BigFileTablespace)는 oracle 10g에서 사용가능한 tablespace type 이다. Bigfile Tablespace는 하나의 매우 큰 datafile을 갖는 tablespace를 말한다. Bigfile tablespace의 개념이 만들어 지면서 기존의 일반적인 tablespace는 자동으로 smallfile tablespace로 불린다.

SMALLFILE tablespace와 BIGFILE tablespace는 database내에 공존할 수 있다.

      SQL> select TABLESPACE_NAME, BIGFILE from DBA_TABLESPACES;

      TABLESPACE_NAME                BIG
      ------------------------------ ---
      SYSTEM                         NO
      UNDOTBS1                       NO
      SYSAUX                         NO
      TEMP                           NO
      USERS                          NO
      TEST_BIG                       YES

BIGFILE tablespace는 하나의 datafile만을 사용하게 된다. 이는 datafile에 대한 관리가 간편해 질 수 있다는 것을 의미하며, 일반적인 SMALLFILE tablespace에서 사용하던 다음과 같은 option도 역시 사용가능하다.

      SQL> ALTER TABLESPACE test_big AUTOEXTEND OFF;
      Tablespace altered.

      SQL>  ALTER TABLESPACE test_big AUTOEXTEND ON;
      Tablespace altered.

      SQL> ALTER TABLESPACE test_big RESIZE 2M;
      Tablespace altered.

BIGFILE tablespace는 다음과 같이 사용될 수 있다.

      --> ASM (Automatic Storage Management)
      --> a logical volume manager supporting striping/RAID
      --> dynamically extensible logical volumes
      --> Oracle Managed Files (OMF)

만약 default로 BIGFILE tablespace를 생성하고 싶다면 다음과 같이 tablespace 생성 option을 바꿀 수 있다.

    SQL> select * from database_properties
          where property_name='DEFAULT_TBS_TYPE';

     PROPERTY_NAME        PROPERTY_VALUE  DESCRIPTION
     -------------------- --------------- ------------------------
     DEFAULT_TBS_TYPE     SMALLFILE       Default tablespace type
   
     SQL> ALTER DATABASE SET DEFAULT bigfile TABLESPACE;
     Database altered.

     SQL> CREATE TABLESPACE big_test2
          DATAFILE '/ORACLE10/ORCL/testbig2.dbf' size 1M;

     Tablespace created.

     SQL> select TABLESPACE_NAME, BIGFILE from DBA_TABLESPACES;

     TABLESPACE_NAME                BIG
     ------------------------------ ---------
     ...
     USERS                          NO
     TEST_BIG                       YES
     TEST_BIG2                      YES

앞서 말한 것 처럼 BIGFILE tablespace에는 datafile 추가는 불가능하다.

      SQL> ALTER TABLESPACE test_big
           ADD DATAFILE '/ORACLE10/ORCL/testbig2.dbf' size 500G;

      ALTER TABLESPACE test_big
      *
      ERROR at line 1:
      ORA-32771: cannot add file to bigfile tablespace


여러 datafile을 사용해 큰 공간을 확보하는 방법에 비해 하나의 큰 파일로 tablespace를 구성하는 이 방법은 관리상의 이점이 있으나, backup이나 recovery 단계에서는 큰 file에 대한 backup과 file 손상이 발생할 경우 전체를 restore해야 하기 때문에 오히려 부담이 될 수 도 있다. 따라서 각각의 tablespace type에 대한 이점과 단점을 구분하고 적용해야할 것이다.

참고 문서 :
Note 262472.1  10g: BIGFILE Type Tablespaces Versus SMALLFILE Type
Note 243245.1  10G New Storage Features and Enhancements
Note 1057891.6 HOW TO USE DBMS_ROWID PROCEDURE AND FUNCTIONS








일반적으로 IN operation은 특정 table(view) data의 row 값에 따른 다른 table의 데이터를 추출해내고자 할 때 자주 사용되는데, 가끔 IN operation을 row가 있는지 check하는 용도로 사용하기도 한다. 그러나 row가 존재하는지에 대해서는 EXISTS라는 근사한 operation을 따로 제공하고 있다. 

주의해야 할 점은 EXISTS와 IN은 다른 점이 존재하므로 이에 대해 유의해야 한다.  EXISTS는 단지 해당 row가 존재하는지만 check하고 더이상 수행되지 않으나 IN은 실제 존재하는 데이터들의 모든 값까지 확인한다. 따라서 일반적으로 EXISTS operation이 더 좋은 성능을 보이므로 가능하면 EXISTS를 사용하는 것이 바람직해 보인다.

또한가지 EXISTS와 IN 사용시 주의해야 할 점은 join 되는 column에 NULL을 갖는 row가 존재한다면, NOT EXISTS는 true값을, NOT IN은 false 가 return 된다. 즉, NOT IN을 사용하면 조건에 맞는 데이터가 있다고 하더라도 NULL이 존재하면 "no rows selected"라고 나오게 된다. 따라서 NVL을 이용한 NULL 처리가 꼭 필요하다.

다음은 NOT EXISTS operation을 이용한 방법이다.
예제의 products table의 product_type_id column 데이터 중 일부가 NULL로 입력되어 있다.

SELECT product_type_id, name
FROM product_types outer
WHERE NOT EXISTS
  (SELECT 1
   FROM products inner
   WHERE inner.product_type_id = outer.product_type_id);

PRODUCT_TYPE_ID NAME
--------------- ----------
              5 Magazine

다음은 동일한 데이터에 대해 NOT IN을 사용했을 경우다. NULL data에 의해 조건 자체가 false가 되어 "no rows selected"라는 결과가 발생한다.

SELECT product_type_id, name
FROM product_types
WHERE product_type_id NOT IN
  (SELECT product_type_id
   FROM products);

no rows selected

다음은 NVL()을 이용해 NULL값을 처리한 후의 결과이다.

SELECT product_type_id, name
FROM product_types
WHERE product_type_id NOT IN
  (SELECT NVL(product_type_id, 0)
   FROM products);

PRODUCT_TYPE_ID NAME
--------------- ----------
              5 Magazine

NOT IN operation의 경우 위와 같은 사실을 미리 인지하고 있지 않다면 나중에 이러한 경우를 찾기는 매우 어려울 수 있다. 따라서 NULL에 대한 operation이나 table의 default column 값등의 지정 등의 세심한 주의가 필요하다.



* 여기저기서 좋다는 책 몇권 추천합니다. 고수가 되는 그날까지.. 파이팅!!



오라클의 SQL 언어를 수행할 수 있는 tool인 sqlplus에서는 변수를 지원한다.

변수는 다들 잘 알고 있듯이 특정 변경되는 값을 변수값으로 입력 받아 이를 해당 변수와 치환해주게 된다. SQLPLUS에서 지원하는 변수는 2가지로 변수값이 지속되지 않는 변수 temporary variable과 그 변수 값이 지속되어 해당 값을 삭제, 재정의 또는 sqlplus를 빠져나갈때 까지 유지하는 defined variable이 있다.

Temporary variable의 경우 SQL구문의 수행 시 해당 변수값을 입력 받아 사용되며 동일한 SQL이 재수행 되더라도 해당 변수값을 다시 묻게 된다.

SQL> SELECT product_id, name, price
  2  FROM products
  3  WHERE product_id = &product_id_var;

Enter value for product_id_var: 2
old   3: WHERE product_id = &product_id_var
new   3: WHERE product_id = 2

PRODUCT_ID NAME                                PRICE
---------- ------------------------------ ----------
         2 Chemistry                              30

SQL> /
Enter value for product_id_var: 3
old   3: WHERE product_id = &product_id_var
new   3: WHERE product_id = 3

PRODUCT_ID NAME                                PRICE
---------- ------------------------------ ----------
         3 Supernova                           25.99

위의 예를 보면 product_id_var 변수의 값이 3으로 치환되고 이에 대한 결과를 보여준다. 이러한 old/ new 값에 대한 output은 VERIFY option을 이용해 출력을 제어할 수 있다.

SQL> SET VERIFY OFF
SQL> /

Enter value for product_id_var: 4

PRODUCT_ID NAME                                PRICE
---------- ------------------------------ ----------
         4 Tank War                            13.95

다시 변수에 대한 old/ new value를 출력하려면 VERIFY option을 on으로 설정한다.

SQL> SET VERIFY ON

또 "SET DEFINE" 명령을 이용해 변수 앞의 ampersand (&)도 바꿔 줄 수 있다. UNIX 상에서 해당 character가 다른 의미로 사용되고 있는 경우를 제외하면 별로 사용할 일은 없을 듯 하지만..

SQL> SET DEFINE '#'
SQL> SELECT product_id, name, price
  2  FROM products
  3  WHERE product_id = #product_id_var;

Enter value for product_id_var: 5
old   3: WHERE product_id = #product_id_var
new   3: WHERE product_id = 5

PRODUCT_ID NAME                                PRICE
---------- ------------------------------ ----------
         5 Z Files                             49.99

다시 원래의 ampersand (&)로 바꾸려면 다음과 같이 수행해 준다.

SQL> SET DEFINE '&'

가끔 동일한 변수를 같은 SQL문에서 사용하게 되는데 이때 동일한 변수라도 사용한 개수만큼 다시 값을 묻게 된다. 이러한 현상을 막기 위해서는 ampersand를 두개로 지정해 준다.  (&&)

SQL> SELECT name, &col_var
  2  FROM &table_var
  3  WHERE &col_var = &col_val;

Enter value for col_var: product_type_id
old   1: SELECT name, &col_var
new   1: SELECT name, product_type_id

Enter value for table_var: products
old   2: FROM &table_var
new   2: FROM products

Enter value for col_var: product_type_id
Enter value for col_val: 1
old   3: WHERE &col_var = &col_val
new   3: WHERE product_type_id = 1

NAME                           PRODUCT_TYPE_ID
------------------------------ ---------------
Modern Science                               1
Chemistry                                    1

You can avoid having to repeatedly enter a variable by using &&. For example:

SELECT name, &&col_var
FROM &table_name
WHERE &&col_var = &col_val;


Defined Variable은 선언된 변수를 여러번 사용이 가능하다. 이 값은 재정의 되거나, 변수값이 삭제되거나, SQLPLUS를 빠져나가기 전까지 사용가능하다. Defined variable은 "DEFINE" 명령을 통해 변수값이 설정, 조회하며 "UNDEFINE"명령에 의해 변수값이 삭제된다. 또 "ACCEPT" 명령을 통해 해당 변수값을 유저로 부터 받아들일 수 있다.

SQL> DEFINE product_id_var = 7
SQL> DEFINE product_id_var

DEFINE PRODUCT_ID_VAR          = "7" (CHAR)

SQL> SELECT product_id, name, price
      2  FROM products
      3  WHERE product_id = &product_id_var;

old   3: WHERE product_id = &product_id_var
new   3: WHERE product_id = 7

ACCEPT 명령은 해당 변수의 type과 입력 받을 format, prompt 형태 등의 다양한 지정이 가능하다. HIDE option의 경우 9i 이전엔 asterisk characters (*)로 보여주지만 10g 부터는 아무런 문자를 출력하지 않는다.

ACCEPT variable_name [type] [FORMAT format] [PROMPT prompt] [HIDE]

SQL> ACCEPT customer_id_var NUMBER FORMAT 99 PROMPT 'Customer id: '
Customer id: 5

SQL> ACCEPT date_var DATE FORMAT 'DD-MON-YYYY' PROMPT 'Date: '
Date: 12-DEC-2006

SQL> ACCEPT password_var CHAR PROMPT 'Password: ' HIDE
Password: *******

현재 설정되어 있는 변수값을 clear 하기 위해서는 UNDEFINE 명령이 사용된다. 물론 재정의나 SQLPLUS를 logout 하면 해당 값은 clear 되지만 다른 script를 계속 수행한다면 예상치 못한 상황을 만나지 않기 위해 깨끗이 정리할 필요가 있다.

SQL> UNDEFINE customer_id_var
SQL> UNDEFINE date_var
SQL> UNDEFINE password_var





oracle을 사용하면서 데이터베이스 내의 데이터를 다른 데이터베이스로 옮기거나, backup을 위해서 export tool을 사용하는데 일반적으로 table의 전체 데이터를 backup 받거나 user의 데이터, 또는 전체 데이터를 backup 받곤 합니다.
export에는 많은 option들이 있는데, 이 중 특정 table의 일부데이터만을 backup 받는 option이 query option 입니다.


[export options]

Keyword    Description (Default)      Keyword      Description (Default)
--------------------------------------------------------------------------
USERID     username/password          FULL         export entire file (N)
BUFFER     size of data buffer        OWNER        list of owner usernames
FILE       output files (EXPDAT.DMP)  TABLES       list of table names
COMPRESS   import into one extent (Y) RECORDLENGTH length of IO record
GRANTS     export grants (Y)          INCTYPE      incremental export type
INDEXES    export indexes (Y)         RECORD       track incr. export (Y)
DIRECT     direct path (N)            TRIGGERS     export triggers (Y)
LOG        log file of screen output  STATISTICS   analyze objects (ESTIMATE)
ROWS       export data rows (Y)       PARFILE      parameter filename
CONSISTENT cross-table consistency(N) CONSTRAINTS  export constraints (Y)

OBJECT_CONSISTENT    transaction set to read only during object export (N)
FEEDBACK             display progress every x rows (0)
FILESIZE             maximum size of each dump file
FLASHBACK_SCN        SCN used to set session snapshot back to
FLASHBACK_TIME       time used to get the SCN closest to the specified time
QUERY                select clause used to export a subset of a table
RESUMABLE            suspend when a space related error is encountered(N)
RESUMABLE_NAME       text string used to identify resumable statement
RESUMABLE_TIMEOUT    wait time for RESUMABLE
TTS_FULL_CHECK       perform full or partial dependency check for TTS
VOLSIZE              number of bytes to write to each tape volume
TABLESPACES          list of tablespaces to export
TRANSPORT_TABLESPACE export transportable tablespace metadata (N)
TEMPLATE             template name which invokes iAS mode export


query option은 export 수행시 query에서 사용되는 where 절을 지정함으로써 원하는 데이터만을 export 하게 됩니다. 다른 option 들과 마찬가지로 query option을 지정하는 방법은 parameter file을 이용하는 방법과 command line에서 지정하는 방법이 있습니다.

1. QUERY in Parameter file.

parameter file을 이용하는 방법은 당연히 parameter file을 만들어야 합니다. 아래의 예는 scott.emp와 hr.departments table에서 필요한 정보만을 export data pump로 backup하는 방법입니다.

File: expdp_q.par
-----------------
DIRECTORY = my_dir
DUMPFILE  = exp_query.dmp
LOGFILE   = exp_query.log
SCHEMAS   = hr, scott
INCLUDE   = TABLE:"IN ('EMP', 'DEPARTMENTS')"
QUERY     = scott.emp:"WHERE job = 'ANALYST' OR sal >= 3000"
# place following 3 lines on one single line:
QUERY     = hr.departments:"WHERE department_id IN (SELECT DISTINCT
department_id FROM hr.employees e, hr.jobs j WHERE e.job_id=j.job_id
AND UPPER(j.job_title) = 'ANALYST' OR e.salary >= 3000)"

parameter file을 다 만들었으면 export 수행시 해당 parameter file을 지정해 수행합니다.

%expdp system/manager parfile=expdp_q.par



2. QUERY on Command line.

말 그대로 command line에서 모든 명령문을 써 주는 방법입니다. 주의 하실 것은 unix shell 상태에서 특수 문자를 사용해야 하므로 특수문자 앞에 escape 문자인 \를 지정해야 합니다.
다음의 예는 scott.dept 전체 데이터와 scott.emp의 일부 데이터를 backup 받는 방법입니다.

% expdp scott/tiger DIRECTORY=my_dir \
DUMPFILE=expdp_q.dmp LOGFILE=expdp_q.log TABLES=emp,dept \
QUERY=emp:\"WHERE ename LIKE \'A\%\'\"


참조 : Note 277010.1 Export/Import DataPump Parameter QUERY - How to Specify a Query




index는 oracle내의 table data를 access 하기 위한 access patch를 제공하는 segment 입니다.
즉, table의 data access를 index를 참조해서 scan하게 됩니다.

따라서 index 생성시 자주 scan 되는 방식으로 index를 구성하면 굳이 query 시 테이블 데이터를 순차적으로 ordering 할 필요가 없게 됩니다.  다음의 예제 처럼 SCOTT의 EMP table이 SAL의 역순으로 자주 query가 수행된다면 다음처럼 역순으로  index를 생성할 수 있습니다.

   create index IDX_EMP on SCOTT.EMP(DEPTNO, SAL DESC);


위의 구문으로 index를 생성한다면 query 시 해당 index를 이용하게끔 만들어 준다면 order by 등의 구문과 그에 따른 sorting 작업은 필요없게 됩니다.

그런데, "SAL DESC"는 어떤 index column 이름을 갖게 될까요?

SAL_DESC 등의 이름을 갖는다면 dba_ind_column에서 대충 해당 column이 table의 어떤 column으로 부터 만들어 졌는지 확인할수 있겠지만, oracle은 이와 같은 index column이름을 SYS_NCnnnnn$라는 형식의 이름으로 생성하게 됩니다. 이와 같이 oracle이 자체적으로 이름을 생성하는 것은 대개 constraint 생성할 때 이름을 지정하지 않는 경우에 많이 볼수 있습니다.

SQL> select index_name, column_name, descend from dba_ind_columns  where index_name='I_EMP'
SQL> /

INDEX_NAME                     COLUMN_NAME          DESC
------------------------------ -------------------- ----
I_EMP                          DEPTNO                     ASC
I_EMP                          SYS_NC00009$         DESC


그러면 위의 "SYS_NC00009$" column은 EMP table의 어떤 column에 의해 만들어진걸까요?
이 정보는 DBA_IND_EXPRESSIONS에서 확인할 수 있습니다.


SQL> select index_name, column_expression, column_position from DBA_IND_EXPRESSIONS where index_name
SQL> /

INDEX_NAME                     COLUMN_EXPRESSION    COLUMN_POSITION
------------------------------ -------------------- ---------------
I_EMP                          "SAL"                              2  

즉, "I_EMP index의 두번째 column은 EMP table의 SAL의 가공으로 만들어진 column 이다."


이상의 내용은 다음의 metalink 문서를 참조했습니다.
Note 272357.1 Column name of a descending index is system generated: SYS_NCnnnnn$




+ Recent posts