REF CURSOR

http://www.atmarkit.co.jp/fdb/rensai/odpdotnet01/odpdotnet04.html

REF CURSOR

　REF CURSORは、OracleDataReader、DataSetまたはOracleRefCursorとして取得できます。OracleRefCursorオブジェクトとして取得されたREF CURSORは、OracleDataReaderの作成またはREF CURSORからDataSetへの移入に使用できます。REF CURSORにアクセスする際は、必ずOracleDbType.RefCursorとしてバインドします。以下にストアドプロシージャからREF CURSORを取得する方法、および DataGridオブジェクトに情報を表示する方法を説明します。

REF CURSORを使用したパッケージの作成

CREATE OR REPLACE PACKAGE SCOTT.pkg_ref AS
CURSOR c1 IS SELECT * FROM emp;
CURSOR c2 IS SELECT * FROM dept;
TYPE empCur IS REF CURSOR RETURN c1%ROWTYPE;
TYPE deptCur IS REF CURSOR RETURN c2%ROWTYPE;
PROCEDURE GetEmpDeptData(
EmpCursor in out empCur,
DeptCursor in out deptCur
);
END;
リスト18　REF CURSORを使用したパッケージ

CREATE OR REPLACE PACKAGE BODY SCOTT.pkg_ref AS
PROCEDURE GetEmpDeptData(
EmpCursor in out empCur,
DeptCursor in out deptCur) IS
BEGIN
OPEN EmpCursor FOR SELECT * FROM emp;
OPEN DeptCursor FOR SELECT * FROM dept;
END GetEmpDeptData;
END pkg_ref;
リスト19　REF CURSORを使用したパッケージ本体


ストアドプロシージャからREF CURSORを取得しDataGridオブジェクトに結果を表示するコードは以下のようになります。

Dim cmd As New OracleCommand("pkg_ref.GetEmpDeptData", cnn)
cmd.CommandType = CommandType.StoredProcedure

'REF CURSORパラメータのバインド
cmd.Parameters.Add("EmpCursor", _
OracleDbType.RefCursor, ParameterDirection.Output)
cmd.Parameters.Add("DeptCursor", _
OracleDbType.RefCursor, ParameterDirection.Output)

'SQL文の実行とRef Cursorの使用
Dim dsData As New DataSet
Dim da As New OracleDataAdapter(cmd)
da.Fill(dsData, "data")

'DataGridへ表示
DataGridEmp.SetDataBinding(dsData, "data")
DataGridDept.SetDataBinding(dsData, "data1")
リスト20　REF CURSORを取得しDataGridオブジェクトに結果を表示（VB.NET）

OracleCommand cmd =
new OracleCommand("pkg_ref.GetEmpDeptData", cnn);
cmd.CommandType = CommandType.StoredProcedure;

//REF CURSORパラメータのバインド
cmd.Parameters.Add("EmpCursor",
OracleDbType.RefCursor, ParameterDirection.Output);
cmd.Parameters.Add("DeptCursor",
OracleDbType.RefCursor, ParameterDirection.Output);

//SQL文の実行とRef Cursorの使用
DataSet dsData = new DataSet();
OracleDataAdapter da = new OracleDataAdapter(cmd);
da.Fill(dsData, "data");

//DataGridへ表示
dataGridEmp.SetDataBinding(dsData, "data");
dataGridDept.SetDataBinding(dsData, "data1");
リスト21　REF CURSORを取得しDataGridオブジェクトに結果を表示（C#）

　上記のサンプルコードでは、REF CURSORからDataSetへデータを格納していますが、OracleRefCursorオブジェクトからOracleDataReaderオブジェクトへ格納することも可能です。

Dim cmd As New OracleCommand("pkg_ref.GetEmpDeptData", cnn)
cmd.CommandType = CommandType.StoredProcedure

'REF CURSORパラメータのバインド
cmd.Parameters.Add("EmpCursor", OracleDbType.RefCursor, _
ParameterDirection.Output)
cmd.Parameters.Add("DeptCursor", OracleDbType.RefCursor, _
ParameterDirection.Output)
cmd.ExecuteNonQuery()

'SQL文の実行とREF CURSORの使用
Dim dr1 As OracleDataReader = _
CType(cmd.Parameters(0).Value, OracleRefCursor).GetDataReader
Dim dr2 As OracleDataReader = _
CType(cmd.Parameters(1).Value, OracleRefCursor).GetDataReader
リスト22　REF CURSORを取得しOracleDataReaderへ結果を格納（VB.NET）

OracleCommand cmd =
new OracleCommand("pkg_ref.GetEmpDeptData", cnn);
cmd.CommandType = CommandType.StoredProcedure;

//REF CURSORパラメータのバインド
cmd.Parameters.Add("EmpCursor", OracleDbType.RefCursor,
ParameterDirection.Output);
cmd.Parameters.Add("DeptCursor", OracleDbType.RefCursor,
ParameterDirection.Output);
cmd.ExecuteNonQuery();

//SQL文の実行とREF CURSORの使用
OracleRefCursor cur1 =
(OracleRefCursor)cmd.Parameters[0].Value;
OracleRefCursor cur2 =
(OracleRefCursor)cmd.Parameters[1].Value;
OracleDataReader dr1 = cur1.GetDataReader();
OracleDataReader dr2 = cur2.GetDataReader();
リスト23　REF CURSORを取得しOracleDataReaderへ結果を格納（C#）

UniqueIndex VS PK

오라클's 낙공불락
cafe.naver.com/ocpgroup
님의 자료를 퍼왔습니다.


아주 설명이 잘되있는 걸 찾아서 부연설명해서 올립니다.

참고로 퍼온곳은 www.dbguide.net 이라는 곳인데, 질문답변란에 올리면

엔코아나, 기타 좀 이름있는 DB컨설팅 회사 사람들이 답변을 해줍니다.

메일링 가입하시면 좋은 정보 많이 얻으실겁니다.







유니크인덱스와 PK의 차이점? 조회: 413 2004-03-26
김윤선(covey02)

PK와 유니크 인덱스의 차이점은 뭔가요?

테이블에서 FK를 사용하지 않는다면 유닉스 인덱스 만으로도 가능한데..
굳이 PK를 잡는 이유는 뭔지 알고 싶습니다.

테이블을 대표하는 것을 나타내기위해 PK를 쓴다는 말이 있는데...
이건 유닉스 인덱스로 대치 할 수 없는건가요?

PK와 유니크 인덱스 중 유니크인덱스로만 구성했을때 퍼포먼스가 더 빠르다고 하던데 맞는 말인지 알고 싶습니다. 맞다면 어떻게 해서 더 빠른지두 알고 싶습니다.





유니크인덱스와 PK의 차이점? 2004-03-26
유기연(inyou2)

PK와 유니크 인덱스는 비교 하기엔 좀 무리가 있다고 보는데요
Table 생성시 PK에 자동적으로 유니크 인덱스가 생성 됩니다.
일반적인 유저가 생성하는 인덱스는 유닉스 인덱스로 만들지 않는것이
좋습니다.
만일 PK없이 유저가 유니크 인덱스만 만들었다고 해도 PK를 대신할수는 없습니다.
옵티마이져가 실행계획시 인덱스 안타고 FTS 하면 인덱스는 아무 소용이 없는거죠.
인덱스와 키는 개념 자체가 틀린겁니다.
테이블에서 FK를 사용 하지 않아도 PK는 필요합니다.
이유를 말하자면 모델링 측면에서는 Relational 개념에 부합하는거겠고
Database측면으로 보자면 옵티마이져가
빠른 실행계획을 만드는데 도움이 되겠죠...
굳이 따지자면 PK와 유니크키의 차이점을 물어 보시는듯 하는데요..
큰 차이점은 유니크키는 널 값을 허용 하는 겁니다.
키가 될수 있는 후보 키가 유니크키고 그중 가장 값을 유일하게 구별해 주는 넘을
PK라고 이해하시면 될듯 합니다.
PK없이 유니크인덱스로만 구성했을때 퍼포먼스가 더 빠르냐는 문제점은
당시 테이블의 데이타의 양 및 어떤 분포도를 가지냐에 따라
사례에 따라 천차 만별 달라집니다.
튜닝및 퍼포먼스 문제는 언제나 딱 잘라 절대적으로 말할수 없는 문제죠...









유니크인덱스와 PK의 차이점? 2004-03-26
김인호(yesino)

안녕하세요.. 이노입니다.


궁금해하시는 내용에 대한 답변은 예전 좀 다른 문제로 제가 답변을 올렸던 경우와
유사한 듯 하여 그때 내용을 간략하게 다시 올리겠습니다.

먼저 PK Index와 Unique Index의 차이는 간단하게 Nullable의 차이라고 하겠습니다.
쉽게 말해서 PK Index라는 것은 Unique Index + Not Null을 의미합니다. -> 뒷부분에서 근본적으로 차이가 있다라고 설명합니다.
반대로 Unique Index는 인덱스가 걸리는 필드에 대해서도 Null을 허용하죠.
즉, 특정 테이블의 컬럼에 대해 Unique 인덱스가 있다고 해서, 해당 필드에 Null 값을
Insert 못하지는 않습니다. 하지만, PK Index가 설정되어 있다고 한다면 자동으로
해당 필드에 대한 Not null 제약조건이 생기기 때문에 Null을 insert할 수 없죠.

이러한 이유로 인해, SQL의 경우에 따라서는 Unique Index임에도 불구하고 Table
Access를 하는 예도 있습니다. 해당 값이 Not Null임을 보장하지 못하기 때문이죠.
따라서 SELECT문의 경우 때로는 오히려 Unique보다 PK가 아주 근소한 차이지만 더
빠른 경우도 있습니다. 물론, INSERT의 경우 제약조건에 대한 체크로 인해 조금 더
느릴(?)수도 있겠지요..

실제 테스트를 해봐도, 특정 field의 값에서 max값을 추출하고자 하는데 null이 포함되어 있으면 실제로 index search에서 찾은 값이 null인지 값을 가지고 있는지는 테이블을 뒤져봐야 알 수 있는 정보가 됩니다. -> 그래서 질문할 때 말했던, unique index가 더 빠르다는 것은 경우에 따라서 반대로 primary key 일때 더 빠를 수 있다. 겠네요
그래서 PK Index인 경우에는 (null을 가진 필드가 없음을 보장해주기 때문에..)index 영역안에서 결과 값을 리턴해줄 수 있고, Unique의 경우에는 데이터 영역까지 가서 실제 값을 확인해봐야 하는 결과가 나옵니다.
(이러한 경우도 Analyze나 테이블에 대한 정확한 통계가 있다면 그렇지 않을수도..)


이상 짧은 제 소견이였습니다.
원하시는 답변이 어느 정도 되셨는지 모르겠습니다.


행복한 하루 되시기를~~


유니크인덱스와 PK의 차이점? 2004-03-27
조시형(oraking)

안녕하십니까? 엔코아 정보컨설팅에 근무하는 컨설턴트 조시형입니다.

우선 Primary Key와 Unique Index의 차이점을 설명하는 것은 부적절하다는 말씀을 드리고 싶습니다. 둘간의 상관관계를 설명하는 것이 맞는 개념입니다. 많은 개발자들이 PK는 왠지 부하를 준다는 잘못된 선입견을 가지고 있고 따라서 PK 대신 Unique Index를 사용하는 것으로 알고 있는데 매우 그릇된 관행(?)이라고 생각합니다. 따라서 앞에서 다른 분들이 좋은 설명 많이 해 주셨지만 부연해서 설명을 드리도록 하겠습니다.

Primary Key라고 하는 것은 논리적인 개념입니다. Primary Key는 해당 컬럼이 그 테이블의 식별자임을 나타내는 것으로서, 자신과 다른 레코드가 서로 다른 인스턴스임을 확인할 수 있게 해 주는 역할을 합니다. 즉, 해당 그 레코드의 존재자체인 것이지요.
원래 사람이름이라는 것이 '나'와 다른 사람을 식별하기 위해 사용하는 것인데, 다른 사람과 중복될 수 있으므로 나를 식별할 수 있는 속성으로서 주민등록번호라는 것을 대신 사용합니다. 따라서 주민등록번호는 나와 별개가 아닌 나의 존재 그 자체입니다. 철학적으로는 맞지 않는 설명이겠지만 적어도 ‘데이터의 세계’에서는 그렇습니다.

반면 PK constraint는 물리적인 개념입니다. "이 컬럼(들)은 다른 레코드와 구분짓는 식별자 역할을 하는 중요한 컬럼이므로 데이터는 중복을 허용해서는 안 되고(unique), null값을 허용해서도 안돼(not null)"라고 DB에게 정보를 주는 것입니다. primary key constraint를 설정하면 unique index와 not null constraint가 자동적으로 생성되는 이유도 여기에 있지요. -> 스터디할때 primary key는 논리적인 개념이고 index는 실제로 눈에 보이는 물리적인 것이라고 했는데, 설명을 좀 틀린것 같습니다. Primary Key가 논리적인 개념이며, PK Constraint 와 not null 등의 제약조건과 unique index 들이 물리적인 개념이라고 하는게 맞겠군요.
특히 인덱스는 PK 컬럼의 Unique성을 보장하기 위해 매우 필수적인 도구인데, 만약 인덱스 없이 해당 컬럼값이 중복되지 않도록 할 수 있는 방법을 생각해 보시기 바랍니다. 잘 떠오르지 않을 것입니다. 결론적으로 말씀드리면, 인덱스와 PK의 상관관계에 있어서 Unique이든 Non-Unique이든 Index라는 놈은 PK 컬럼의 Unique성을 보장하기 위해 사용하는 하나의 도구(Tool)에 지나지 않습니다.
어떤 의미에서만 본다면, 어느 한 컬럼에 Not Null Constraint를 주고, 그 컬럼에 Unique Index를 생성하였다면 primary key와 다를 것이 하나도 없어 보입니다.
하지만 primary key는 데이터베이스와 사용자 입장에서 매우 중요한 정보 역할을 하기 때문에 중요하다고 말씀드리고 싶습니다.
앞서 말씀드렸듯이, 원래 primary key는 “이 컬럼(들)이 테이블의 식별자(identifier)이므로 중복을 허용해서는 안 되고, null값을 허용해서도 안 된다”라는 의미론적(semantic) 인 의미에서 정의하는 것이며, DBMS는 이를 효과적으로 처리하기 위해서 index를 자동생성해서 사용하고 not null constraint를 정의하는 것입니다.
참고로 primary key를 위해서 반드시 unique index가 필요한 것은 아닙니다. non-unique index만 있더라도 새로운 값이 들어올 때 중복값이 있는지 체크하는 데에는 전혀 문제가 없으므로 기존에 이미 non-unique 인덱스가 정의되어 있는 상황이었다면 그 인덱스를 그대로 사용합니다. DW 시스템에서 대량의 데이터 로딩시 속도를 빠르게 하기 위해 PK Constraint를 일시적으로 Disable 시키는 경우가 있는데 이렇게 되면 Unique Index도 동시에 제거되므로 인덱스를 다시 생성해야 하는 부담이 생깁니다. 이러한 부담을 덜기 위해 의도적으로 non-unique index를 생성하는 경우가 있는데 이에 대해서는 더 깊이 언급하지 않겠습니다. -> 스터디할때 책에 나왔던 부분입니다. primary constraint key 를 생성하면 자동적으로 unique index가 생기는데, 주의할점은 primary constraint key를 해제할 때 unique index 도 자동적으로 삭제된다, 따라서 연기가능한 제약조건(맞나?) 등을 이용할 수 있다. 라는 부분이었습니다. 그때는 사람이 실수나, 다른 이유로 제약조건을 삭제할 때 인덱스까지 없어지므로 갑자기 느려지는 상황이 올 수 있다. 라고 말을 했었는데요, 그런 경우와 함께 DW 시스템 (OLAP) 에서 데이터 로딩 속도를 빠르게 하기 위해 PK Constraint 를 일시적으로 Disable 하는 경우가 있어서, 이것땜에 인덱스를 다시 생성하는 경우가 있다. 그래서 의도적으로 non-unique index 를 생성하는 경우가 있다.. 라고 하네요. 설명을 끝까지 하지 않아서 아쉽지만 어쨌든 공부한게 나왔네요
하여튼 primary key, foreign key, not null 등과 같은 integrity constraint 정보들은 plan을 생성하고, query rewrite(주로 DW에서 많이 사용되는 기능임)를 수행할 때, 그리고 기타 여러가지 용도로 데이터베이스에 의해 사용되어집니다.
그리고 OLAP Tool과 같이 데이터베이스에 접근하는 여러 tool들이 동적으로 ad-hoc query를 생성할 때 이 정보들을 활용합니다.
optimizer와 tool 입장에서 뿐만 아니라 데이터베이스를 사용하는 사용자 입장에서도 실제 document 역할을 하게 되므로 의미가 있습니다. ER Diagram을 보지 않고 data dictionary에 있는 정보만을 보고도 그 테이블의 식별자(Identifier)가 어떤 컬럼으로 구성되어 있는지 쉽게 확인할 수 있잖아요.
이런 좋은 기능과 역할을 하는데도 불구하고 unique index와 not null 만을 정의해서 primary key 기능을 대신하도록 할 이유는 없지 않을까요?
성능과 관련해서 말씀드리면, Not Null Constraint와 Unique 인덱스를 PK Constraint 대신 사용하는 것이 더 속도가 빠르다는 것은 전혀 근거없는 낭설에 불과합니다. 오히려 SQL 옵티마이저에게 더 많은 정보를 제공함으로써 더 좋은 실행계획을 만드는데 일조하게 되고 따라서 더 빨라지는 경우가 많겠지요...

- TABLE rename tips

- TABLE rename tips



you can copy a table using CREATE TABLE .. AS SELECT statement.

(but it DOESN'T COPY table key, index, column default)

After that, use DROP TABLE.



CREATE TABLE new_table AS SELECT * FROM old_table;

DROP TABLE old_table;



http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_73a.htm#2062898



Especially Oracle 9i supports RENAME TO clause.



ALTER TABLE old_table RENAME TO new_table;



http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_32a.htm#2086662

프로세스 ID로 실행중인 SQL알아 보기

http://blog.naver.com/tkpolee/80010740220에서 퍼온 내용입니다.

먼저 시스템 자원현황을 살펴보기 위해서 unix에서 top을 실행한다.

# top

load averages: 1.54, 1.47, 2.07 12:24:08
1461 processes:1457 sleeping, 2 stopped, 2 on cpu
CPU states: % idle, % user, % kernel, % iowait, % swap
Memory: 9216M real, 211M free, 9434M swap in use, 7976M swap free

PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND
17334 oracle 1 51 0 2510M 2488M sleep 36:46 2.24% oracle
29538 root 5 55 0 4808K 3632K sleep 3:50 1.48% save
29536 root 5 53 0 8048K 6864K sleep 3:34 1.47% save
29537 root 5 60 0 4768K 3648K sleep 0:22 1.35% save
24582 root 1 0 0 414M 1288K sleep 150.0H 0.86% rtf_daemon
9781 oracle 11 58 0 2510M 2481M sleep 933:20 0.74% oracle
6993 oracle 1 20 0 2509M 2485M cpu9 83.3H 0.57% oracle
2208 oracle 1 50 0 2515M 2492M sleep 0:01 0.52% oracle
2211 oracle 1 0 0 2592K 1712K cpu8 0:00 0.36% top
476 tuxkigum 11 50 0 2524M 2491M sleep 45:13 0.32% oracle
470 tuxkigum 12 2 0 2522M 2491M sleep 45:24 0.12% oracle
474 tuxkigum 12 58 0 2524M 2490M sleep 41:19 0.10% oracle
25911 kamzone 11 14 2 2510M 2486M sleep 2:00 0.10% oracle
8824 xwnts 39 23 12 322M 51M sleep 82:17 0.10% java
17692 oracle 1 25 0 2515M 2491M sleep 111:29 0.09% oracle



이중에서 cpu의 사용량이 많은 프로세스(17334)에 대해서 어떤 SQL이 사용되고 있는지

살펴보자. 아래의 SQL을 cpu_overhead.sql로 저장하고 실행한다.

---------------------------------------------------------------------------------------

-- programed by Lee Chang Kie --

ttitle 'Cpu Overhead SQL Check'
clear screen
set verify off
set pagesize 200
set linesize 110
set embedded off
set feedback off

col col0 format a25 heading "Sid-Serial"
col col1 format a10 heading "UserName"
col col2 format a10 heading "Schema"
col col3 format a10 heading "OsUser"
col col4 format a10 heading "Process"
col col5 format a10 heading "Machine"
col col6 format a10 heading "Terminal"
col col7 format a20 heading "Program"
col col8 format 9 heading "Piece"
col col9 format a8 heading "Status"
col col10 format a64 heading "SQL"

!rm -f ./cpu_overhead.lst

spool cpu_overhead.lst

Select A.sid||','||A.serial# col0,
A.username col1,
A.schemaname col2,

A.osuser col3,
A.process col4,
A.machine col5,
A.Terminal col6,
upper(A.program) col7,
C.piece col8,
A.status col9,
C.sql_text col10
From v$session A, v$process B, v$sqltext C
Where B.spid = '&1'
and A.paddr = B.addr
and C.address = A.sql_address
order by C.piece;

spool off

----------------------------------------------------------------------------------------

[KAMCO:/oracle/app/oracle/product/806/work]# sqlplus internal

SQL*Plus: Release 8.1.7.0.0 - Production on Fri Mar 4 13:01:40 2005

(c) Copyright 2000 Oracle Corporation. All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production

SQL> @cpu_overhead



그러면 다음과 같이 프로세스 번호를 입력하라고 뜰 것이다.

Enter value for 1:



top명령을 실행했을 때 가장 상위에 나타는 프로세스ID(17334)를 입력한다.

그러면 아래와 같이 부하를 가중시키는 SQL이 검출될 것이다.

필요시 힌트, 인덱스정책, 실행계획등이나 트레이스를 떠서 필요한 튜닝을

수행해야 할 것이다.



Cpu Overhead SQL Check

Sid-Serial UserName Schema OsUser Process Machine Terminal

------------------------- ---------- ---------- ---------- ---------- ---------- ----------

Program Piece Status SQL

-------------------- ----- -------- ----------------------------------------------------------------

664,9791 KAMCO KAMCO tuxkigum 23914 KAMCO

SVZIPSND@KAMCO (TNS 0 ACTIVE SELECT A.LOAN_NO LOAN_NO,A.LOAN_TYPE LOAN_TYPE,NVL(A.SANGYE_DATE

V1-V3)



664,9791 KAMCO KAMCO tuxkigum 23914 KAMCO

SVZIPSND@KAMCO (TNS 1 ACTIVE ,' ') SANGYE_DATE,NVL(A.RUPT_DATE,' ') RUPT_DATE,NVL(A.SANSIL_DAV1-V3)

.

Oracle 10g DBPUMP

datapump(10g to 10g)

OS dir 作成
C:>sqlplus /nolog

SQL*Plus: Release 10.1.0.2.0 - Production on 日 3月 13 14:03:03 2005

Copyright (c) 1982, 2004, Oracle. All rights reserved.

SQL> connect sys/sys as sysdba
接続されました。
SQL> create or replace directory exportdir as 'C:datapump'
ディレクトリが作成されました。

SQL> create user sam identified by sam default tablespace users quota unlimited on users;
ユーザーが作成されました。

SQL> grant create any directory to sam;

SQL> grant read,write on directory exportdir to sam
権限付与が成功しました。

SQL>alter user sam default tablespace users quota unlimited on users;

expdp 実行
C:Documents and Settingsoracle>C:Documents and Settingsoracle>expdp sam/sam tables=dbpump dumpfile=exportdir:aaa.dump logfile=exportdir:aaa.log

Export: Release 10.1.0.2.0 - Production on 日曜日, 13 3月, 2005 14:40

Copyright (c) 2003, Oracle. All rights reserved.

接続先: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
"SAM"."SYS_EXPORT_TABLE_01"を起動しています: sam/******** tables=dbpump dumpfile
=exportdir:aaa.dump logfile=exportdir:aaa.log
BLOCKSメソッドを使用して見積り中です...
オブジェクト型TABLE_EXPORT/TABLE/TBL_TABLE_DATA/TABLE/TABLE_DATAの処理中です
BLOCKSメソッドを使用した見積合計: 0 KB
オブジェクト型TABLE_EXPORT/TABLE/TABLEの処理中です
. . "SAM"."DBPUMP" 0 KB 0行がエクスポート
されました
マスター表"SAM"."SYS_EXPORT_TABLE_01"は正常にロード/アンロードされました
******************************************************************************
SAM.SYS_EXPORT_TABLE_01に設定されたダンプ・ファイルは次のとおりです:
C:DATAPUMPAAA.DUMP
ジョブ"SAM"."SYS_EXPORT_TABLE_01"が14:42で正常に完了しました。


impdp実行
C:Documents and Settingsoracle>impdp sam/sam tables=dbpump dumpfile=exportdir:aaa.dump logfile=exportdir:aaa.log
Import: Release 10.1.0.2.0 - Production on 日曜日, 13 3月, 2005 14:45

Copyright (c) 2003, Oracle. All rights reserved.

接続先: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
マスター表"SAM"."SYS_IMPORT_TABLE_01"は正常にロード/アンロードされました
"SAM"."SYS_IMPORT_TABLE_01"を起動しています: sam/******** tables=dbpump dumpfile
=exportdir:aaa.dump logfile=exportdir:aaa.log
オブジェクト型TABLE_EXPORT/TABLE/TABLEの処理中です
オブジェクト型TABLE_EXPORT/TABLE/TBL_TABLE_DATA/TABLE/TABLE_DATAの処理中です
. . "SAM"."DBPUMP" 0 KB 0行がインポートさ
れました
ジョブ"SAM"."SYS_IMPORT_TABLE_01"が14:45で正常に完了しました


dbconsole実行
C:>emctl status dbconsole
Environment variable ORACLE_SID not defined. Please define it.

C:>set ORACLE_SID=jeon

C:>emctl status dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.1.0.2.0
Copyright (c) 1996, 2004 Oracle Corporation. All rights reserved.
http://EVS-D106:5500/em/console/aboutApplication
Oracle Enterprise Manager 10g is running.
------------------------------------------------------------------
Logs are generated in directory C:oracleproduct10.1.0db_1/EVS-D106_jeon/sysm
an/log

오라클 DBA를 위한 유용한 5가지 유닉스 명령어

좀 오래된 듯한 감이 있기는 한 글이지만...
Unix for Oracle DBAs Pocket Reference

오라클 DBA를 위한 유용한 5가지 유닉스 명령어
2001년 03월 21일

Oracle DBAs Pocket Reference는 데이터베이스 관리자(DBA)가 알아야 할 모든
유닉스 명령어를 20년 이상 공부하고 하나로 모아 놓은 결과물이다. 컨설턴트
이기 때문에 유닉스 다이어렉트에 대한 데이터베이스 조절 방법을 강구하고 모
든 명령어를 암기해야 했는데, 정말이지 힘든 과정이었다. 여기에 Oracle
DBAs Pocket Reference에 수록되어 있는 스크립트 중에서 내가 좋아하는 5개
를 뽑아 보았다.

유닉스용 "Change All" 명령어

이 스크립트는 디렉토리에 있는 모든 파일에서 한 문자열과 다른 문자열을 바
꿔서 검색과 교환을 실행시킨다. 유닉스 디렉토리에 수 백 개의 파일이 들어
있고 각 파일에서 ORACLE_SID를 바꾸고 싶을 때, 이 스크립트를 이용하면 몇
초안에 모든 것을 해결할 수 있다. 게다가 변환된 파일의 원 파일에 대한 백
업 디렉토리도 만들어 준다. 나는 이 스크립트를 이용해 수 백 시간에 걸쳐 똑
같이 수정해야 하는 작업을 하지 않을 수 있었다.
#!/bin/ksh

tmpdir=tmp.$$

mkdir $tmpdir.new

for f in $*
do
sed -e 's/oldstring/newstring/g'
< $f > $tmpdir.new/$f
done

# Make a backup first!
mkdir $tmpdir.old
mv $* $tmpdir.old/


cd $tmpdir.new
mv $* ../

cd ..
rmdir $tmpdir.new



위에 있는 for 루프로 인해 sed 명령어가 현재 디렉토리 내 모든 파일에서 실
행된다. sed 명령어는 작업을 실제 검색, 교환하며 동시에 임시 디렉토리에 관
계 파일의 새로운 버전을 생성한다.

이 스크립트를 사용하려면 여기에 나타난 코드의 파일 이름을 chg_all.sh 로
바꿔야 한다. 전체를 바꾸고자 한다면, 스크립트 파일에서 이전 문자열과 새로
운 문자열을 수정하는 것부터 해야 한다. 그러면 스크립트를 실행할 때, 파일
마스크에서는 인자로서 지나쳐도 된다. 예를 들어 SQL 파일을 바꿀 때는 다음
과 같은 명령을 실행하기만 하면 되는 것이다.

root> chg_all.sh *.sql

스크립트가 완성되면, 원하던 문자열이 대체되어 있을 것이고, tmp.old 라는
디렉토리 이름이 붙은 파일이 남게 될 것이다. 이 파일은 수정된 파일의 원 버
전이다.

수백 개의 데이터베이스에서 오라클 값을 검사하는 스크립트

모든 데이터베이스에서, 심지어는 서버가 다른 데이터베이스에서 바로
SQL*Plus 명령어를 실행할 수 있는 방법이 유닉스에 꼭 필요하다고 생각해 왔
다. 내가 아는 한 매니저는 가게에 있는 모든 데이터베이스에 대한 디폴트 최
적화 모드를 알고 싶어했다. 그 가게에는 30개의 데이터베이스 서버에 150개
의 데이터베이스가 있었다. 그는 이틀 안에 이 일을 끝내라고 했는데, 내가 10
분 안에 정확한 답을 말하자 크게 놀랐다. 그때 이용한 것이 바로 이 스크립트
이다.
# Loop through each host name . . .
for host in `cat ~oracle/.rhosts|
cut -d"." -f1|awk '{print $1}'|sort -u`
do
echo " "
echo "************************"
echo "$host"
echo "************************"
# loop from database to database
for db in `cat /etc/oratab|egrep ':N|:Y'|
grep -v *|grep ${db}|cut -f1 -d':'"`
do
home=`rsh $host "cat /etc/oratab|egrep ':N|:Y'|
grep -v *|grep ${db}|cut -f2 -d':'"`
echo "************************"
echo "database is $db"
echo "************************"
rsh $host "
ORACLE_SID=${db}; export ORACLE_SID;
ORACLE_HOME=${home}; export ORACLE_HOME;
${home}/bin/sqlplus -s /<
set pages 9999;
set heading off;
select value from v"""$"parameter
where name='optimizer_mode';
exit
!"
done
done



이 스크립트를 사용할 때에는 유닉스 원격 쉘(rsh)이 필요하다. 이 유닉스
rsh 를 이용하면 서버들 사이에서 빨리 옮겨 다닐 수 있다. 자신의 .rhosts 파
일에 엔트리를 만들기만 하면 되는 것이다. 스크립트가 시스템에서 .rhosts 파
일에 정의된 서버 이름을 통해서 반복되고, 각 서버의 /etc/oratab 파일에 리
스트된 데이터베이스를 통해서도 반복될 것이다.

데이터베이스 값을 확인할 때, 그리고 SQL*Plus 스크립트를 운영할 때 이 스크
립트를 이용하면 된다. 자신의 엔터프라이즈에 있는 모든 데이터베이스에 대
한 사용자 리포트, 수행 통계, 정보량 등을 빨리 받아 볼 수 있다. 이 스크립
트를 변형시키면 오라클 디렉토리에서 쓸모 없는 파일을 지울 수도 있고, 재실
행 로그 파일시스템 아카이브의 빈 공간을 확인할 수도 있다. 이 스크립트를
이용해서 여러 데이터베이스에 같은 명령을 실행해서 반복적인 일을 수행하는
데 드는 시간을 많이 줄일 수 있었다.

오라클 환경을 변화시키는 빠른 방법

큰 가게에서 일할 때 발생할 수 있는 곤란한 문제 한가지는 오라클 환경을 빨
리 변환시켜야 할 때이다. 모든 사람이 각자의 방식으로 이 문제를 해결하고,
서버들 간에 어떤 차이가 있는지 기억하기도 힘들어 보인다. 서버가 다른 오라
클 버전을 운영하고 있다면 문제는 더욱 해결하기 힘들어 진다.

이럴 때 나는 모든 서버에 표준 .profile 스크립트를 설치한다. 내가 서버에
신호를 보내면 .profile이 실행하고 모든 데이터베이스에 대한 얼라이어스를
자동으로 만들어 낸다. 이 데이터베이스는 Oracle SID의 이름과 같다. 유닉스
프롬프트에서 Oracle SID를 입력하면, 전체 유닉스 환경이 새로운 데이터베이
스용으로 바뀐다. 다음에 나오는 코드는 내 .profile 파일에 만들어 놓은 것이
다.
for DB in `cat /etc/oratab|grep -v #|grep -v *|cut -d":" -f1`
do
alias $DB='export ORAENV_ASK=NO;
export ORACLE_SID='$DB';
. $TEMPHOME/bin/oraenv;
export ORACLE_HOME;
export ORACLE_BASE=
`echo $ORACLE_HOME | sed -e 's:/product/.*::g'`;
export DBA=$ORACLE_BASE/admin;
export SCRIPT_HOME=$DBA/scripts;
export PATH=$PATH:$SCRIPT_HOME;
export LIB_PATH=$ORACLE_HOME/lib64:$ORACLE_HOME/lib '
done



이제부터는 PROD 데이터베이스로 환경을 바꾸고 싶을 때, 단지 유닉스 명령 프
롬프트에서 PROD라는 명령만 입력하면 된다.

솔라리스에선 /etc에서 /var/opt/oracle까지 oratab 디렉토리 이름을 변환시
켜 주어야 한다.

유용한 유닉스 얼라이어스 패키지

새벽 3시에 제품에 이상이 있다는 호출을 받는다면, 모든 오라클 경고 로그 파
일과 쓸모 없는 파일 디렉토리가 어디에 있는지 기억할 수 없을 것이다. 이럴
때 일을 단순하고 일괄적으로 처리하기 위해서 나는 항상 내 유닉스 .profile
파일에 표준 얼라이어스 목록을 만들어 놓는다. 예를 들면:
# Aliases
#
alias alert='tail -100
$DBA/$ORACLE_SID/bdump/alert_$ORACLE_SID.log|more'
alias arch='cd $DBA/$ORACLE_SID/arch'
alias bdump='cd $DBA/$ORACLE_SID/bdump'
alias cdump='cd $DBA/$ORACLE_SID/cdump'
alias pfile='cd $DBA/$ORACLE_SID/pfile'
alias rm='rm -i'
alias sid='env|grep ORACLE_SID'
alias admin='cd $DBA/admin'



이 얼라이어스를 이용하면 긴 명령어를 쉽게 기억할 수 있다. 일례로 경고 얼
라이어스는 다음에 나오는 긴 명령을 의미한다.

tail -100 $DBA/$ORACLE_SID/bdump/alert_$ORACLE_SID.log|more

이 얼라이어스를 이용해서 유닉스 프롬프트에서 경고를 입력하기만 하면 오라
클 경고 로그에 있는 가장 최근 엔트리를 볼 수 있다. 그리고 아카이브를 입력
하면 오라클 아카이브 재실행 로그 디렉토리의 위치로 갈 수 있다.

서버 통계를 오라클 테이블에 저장할 때 사용하는 스크립트

오라클 데이터베이스를 튜닝할 때 수행 문제가 발생하면 데이터베이스 서버에
서 어떤 일이 일어나는가를 알아야 한다. 수행 문제가 발생하면 유닉스
vmstat 명령으로부터 출력 데이터를 알아내어 mon_vmstats라는 오라클 테이블
에 서버 메트릭스를 저장하는 스크립트를 만든다. 바로 이것이다:
#!/bin/ksh

# First, we must set the environment . . . .
ORACLE_SID=BURLESON
export ORACLE_SID
ORACLE_HOME=`cat /etc/oratab|
grep ^$ORACLE_SID:|cut -f2 -d':'`
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
MON=`echo ~oracle/mon`
export MON

SERVER_NAME=`uname -a|awk '{print $2}'`
typeset -u SERVER_NAME
export SERVER_NAME

# sample every five minutes (300 seconds) . . . .
SAMPLE_TIME=300

while true
do
vmstat ${SAMPLE_TIME} 2 > /tmp/msg$$

# This script is intended to run starting at
# 7:00 AM EST Until midnight EST
cat /tmp/msg$$|sed 1,4d | awk '{
printf("%s %s %s %s %s %s %s
", $1, $6, $7,
$14, $15, $16, $17) }' | while read RUNQUE
PAGE_IN PAGE_OUT USER_CPU SYSTEM_CPU
IDLE_CPU WAIT_CPU
do

$ORACLE_HOME/bin/sqlplus -s / <
insert into mon_vmstats values (
sysdate,
$SAMPLE_TIME,
'$SERVER_NAME',
$RUNQUE,
$PAGE_IN,
$PAGE_OUT,
$USER_CPU,
$SYSTEM_CPU,
$IDLE_CPU,
$WAIT_CPU
);
EXIT
EOF
done
done

rm /tmp/msg$$


이 스크립트는 5분간의 경과 시간동안 vmstat 유틸리티를 작동시켜
mon_vmstat 테이블에 있는 정보를 저장한다. 테이블에 있는 정보에서 서버 수
행 통계를 뽑아 내어, 훌륭한 서버 수행 그래프를 만들 수 있다. 예를 들어 마
이크로소프트 엑셀에 데이터를 복사해서 붙이기를 하면 다음에 있는 페이지 활
성화 그래프를 만들 수 있는 것이다. 이 그래프를 보면 몇 달에 걸쳐 세 번의
다른 시간대 간격의 페이지 활성화 그래프를 알 수 있다.

위의 스크립트는 내가 쓴 Unix for Oracle DBAs Pocket Reference에서 시간을
절약해 주는 몇 가지만을 나열한 것이다. 유닉스의 위력은 실로 대단하며, 이
유닉스의 위력으로 자신의 일을 더 쉽게 만들 수 있다.

테이블 스페이스의 데이터 파일과 테이블 스페이스의 크기 확인

오라클 클럽에서

테이블스페이스 정보보기

테이블 스페이스의 데이터 파일과 테이블 스페이스의 크기 확인

DBA_DATA_FILES 데이터 사전을 이용 하면 됩니다.

SQL>
COL FILE_NAME FORMAT A40
COL TABLESPACE_NAME FORMAT A15

SELECT file_name, tablespace_name, bytes, status FROM DBA_DATA_FILES;

FILE_NAME T ABLESPACE_NAME BYTES STATUS
------------------------------------- --------------- ------------ ------------
C:ORACLEORADATAORACLESYSTEM01.DBF SYSTEM 248250368 AVAILABLE
C:ORACLEORADATAORACLERBS01.DBF RBS 545259520 AVAILABLE
C:ORACLEORADATAORACLEUSERS01.DBF USERS 113246208 AVAILABLE
C:ORACLEORADATAORACLETEMP01.DBF TEMP 75497472 AVAILABLE
C:ORACLEORADATAORACLETOOLS01.DBF TOOLS 12582912 AVAILABLE
C:ORACLEORADATAORACLEINDX01.DBF INDX 60817408 AVAILABLE
C:ORACLEORADATAORACLEDR01.DBF DRSYS 92274688 AVAILABLE

◎ FILE_NAME : DATAFILE의 물리적인 위치와 파일명을 알 수 있습니다.
◎ TABLESPACE_NAME : 테이블 스페이스의 이름을 알 수 있습니다.
◎ BYTES : 테이블 스페이스의 크기를 알수 있습니다.
◎ STATUS : 테이블 스페이스의 이용 가능 여부를 알 수 있습니다.



테이블 스페이스별 사용 가능한 공간의 확인

DBA_FREE_SPACE 데이터 사전


SQL> SELECT tablespace_name, SUM(bytes), MAX(bytes)
FROM DBA_FREE_SPACE
GROUP BY tablespace_name


TABLESPACE_NAME SUM(BYTES) MAX(BYTES)
--------------- ---------- ----------
DRSYS 88268800 88268800
INDX 60809216 60809216
RBS 524279808 498589696
SYSTEM 65536 65536
TEMP 75489280 74244096
TOOLS 12574720 12574720
USERS 113238016 113238016


◎ SUM을 사용한 이유는하나의 테이블 스페이스에 분산되어 있는 여유공간을 합한 것이며,
◎ MAX를 사용한 이유는 여유 공간중 가장 큰 공간의 SIZE를 의미 합니다.




데이타 화일에 대한 총 크기와 남아있는 공간, 사용한 용량, 남은 %율

DBA_FREE_SPACE, DBA_DATA_FILES 데이터 사전

SQL>
COL FILE_NAME FORMAT A40
COL TABLESPACE_NAME FORMAT A30
SET LINESIZE 150
SELECT b.file_name "FILE_NAME", -- DataFile Name
b.tablespace_name "TABLESPACE_NAME", -- TableSpace Name
b.bytes / 1024 "TOTAL SIZE(KB)", -- 총 Bytes
((b.bytes - sum(nvl(a.bytes,0)))) / 1024 "USED(KB)", -- 사용한 용량
(sum(nvl(a.bytes,0))) / 1024 "FREE SIZE(KB)", -- 남은 용량
(sum(nvl(a.bytes,0)) / (b.bytes)) * 100 "FREE %" -- 남은 %
FROM DBA_FREE_SPACE a, DBA_DATA_FILES b
WHERE a.file_id(+) = b.file_id
GROUP BY b.tablespace_name, b.file_name, b.bytes
ORDER BY b.tablespace_name


FILE_NAME TABLESPACE_NAME TOTAL SIZE(KB) USED(KB) FREE SIZE(KB) FREE %
------------------------------------- --------------- -------------- ------------- ------------- ----------
C:ORACLEORADATAORACLEDR01.DBF DRSYS 90112 3912 86200 95.6587358
C:ORACLEORADATAORACLEINDX01.DBF INDX 59392 8 59384 99.9865302
C:ORACLEORADATAORACLERBS01.DBF RBS 532480 20488 511992 96.1523438
C:ORACLEORADATAORACLETEMP01.DBF TEMP 73728 8 73720 99.9891493
C:ORACLEORADATAORACLETOOLS01.DBF TOOLS 12288 8 12280 99.9348958
C:ORACLEORADATAORACLEUSERS01.DBF USERS 110592 8 110584 99.9927662

oracle export import option

//-------------------
//table 설정 export
//-------------------
set ORACLE_SID=SP

exp system/manager@sp file=c:expXXXS010TB.dmp log=c:explogXXXS010TB.log tables=XXX_XXX.XXXS010TB direct=y

//-------------------
//user설정 export
//-------------------
set ORACLE_SID=SP

exp system/manager@sp file=c:expXXX_XXX.dmp log=c:explogXXXX001TB.log owner=XXX_XXX direct=y

//-------------------
// import
//-------------------
set ORACLE_SID=SP

imp system/manager@sp file=c:expXXXS010TB.dmp log=c:explogXXXS010TB.log ignore=y fromuser=XXX_XXX touser=XXX_XXX rows=y indexes=y

init.ora parameter

OracleClub.com 에서 가져온 내용입니다.

시스템 성능에 큰 영향을 미치는 상위 8개 INIT.ORA 파라미터
=========================================================

Technical Bulletins No. 17104 (http://211.106.111.2:8880/bulletin/list.jsp)

PURPOSE -------
이 문서는 init.ora의 어떠한 parameter들이 database성능에 많은 영향을 미치는지에 대해 기술한다.

Explanation -----------
다음에 열거된 파리미터는 각각 데이터베이스 튜닝에 영향을 미치는 것들이다.


DB_BLOCK_BUFFERS
SHARED_POOL_SIZE
SORT_AREA_SIZE
DBWR_IO_SLAVES
ROLLBACK_SEGMENTS
SORT_AREA_RETAINED_SIZE
DB_BLOCK_LRU_EXTENDED_STATISTICS
SHARED_POOL_RESERVE_SIZE



1. DB_BLOCK_BUFFERS

이 파라미터는 모든 버젼의 오라클에서 사용되며, Oracle block 크기를 단위로 지정하게 된다.
이 값은 사용자가 요청하는 데이터를, 메모리 영역에 저장해 둘 수 있는
공간의 크기를 지정하므로 튜닝시 매우 중요한 역할을 한다.

db_block_buffers 값은 SGA 캐쉬 영역에 존재하는 버퍼의 갯수를 지정 하는데 사용되며,
적절한 캐쉬 크기는 실제 디스크 I/O를 줄이는데 도움이 된다.

캐쉬 영역이 적절하게 지정되어 있는지 여부는 buffer cache hit ratio로 측정 가능하며,
일반적으로 90% 이상의 값을 유지하도록 하는 것이 바람직하다.
buffer cache hit ratio는 다음 SQL을 사용하여 조회 가능하다.

SELECT ROUND(((1-(SUM(DECODE(name, 'physical reads', value,0))/
(SUM(DECODE(name, 'db block gets', value,0))+
(SUM(DECODE(name, 'consistent gets', value, 0))))))*100),2) || '%' "Buffer Cache Hit Ratio"
FROM V$SYSSTAT;

실행 결과는 다음과 같은 형식으로 나타나게 된다.

Buffer Cache Hit Ratio:
97.63%


만약 hit ratio가 90% 미만이라면,
hit ratio 가 90% 이상을 유지할 정도로 buffer cache의 크기를 늘려주는 것이 바람직하다.
이 값이 작을 경우 사용된 데이터가,
다른 데이터를 처리할 메모리 영역을 확보시키기 위해 메모리에서 삭제된 후,
다시해당 데이터가 요청될 경우 충분한 cache를 확보하였을 때
피할 수 있는 물리 I/O 가 발생하게 된다.

그러나 만약 이 값을 가용한 메모리 크기에 비해 너무 크게 지정할 경우에는
OS 에서 swapping이 발생하게 되어 시스템이 hang 상태까지 갈 수 있다.




2. SHARED_POOL_SIZE


SHARED_POOL_SIZE는 모든 버젼의 오라클에서 사용되는 파라미터로, 단위는 byte 단위이다.

이 영역은 data dictionary나, stored procedure, 그리고 각종 SQL statement가 저장된다.

SGA 영역가운데 많은 비중을 차지하는 shared_pool_size는 다시 dictionary cache
및 library cache 영역으로 나뉘어 지며,
db_block_buffers와 마찬가지로 너무 크거나, 작게 잡지 않도록 하여야 한다.

SHARED_POOL_SIZE 값이 적절한지 여부는 data dictionary cache 및
library cache 의 hitratio로 측정할 수 있다.

SQL 처리에는 data dictionary가 여러차례 참조되므로,
data dictionary 조회시 디스크 I/O가 적게 발생하도록 하면, 성능 향상에 도움이 된다.

Data dictionary cache hit ratio는 다음 SQL에 의해 측정 가능하다.

SELECT (1-(SUM(getmisses)/SUM(gets))) * 100 "Hit Ratio"
FROM V$ROWCACHE;

결과는 다음과 같이 생성된다.

Hit Ratio
95.40%



Data dictionary cache hit ratio는 90% 이상을 유지하는 것이 바람직 하지만,
인스턴스 구동 직후에는 캐쉬영역에 데이터가 저장되지 않으므로
대략 85% 가량을 유지 하도록 하는 것이 바람직하다.


Library cahce 영역은 공유 SQL 영역 및 PL/SQL 영역으로 나뉘어 진다.
SQL이 실행될 경우,
문장은 먼저 parsing 되어야 하는데, library cache는 SQL 및 PL/SQL을 미리 저장해 두어,
실제 parsing이 발생하는 빈도를 줄이는 역할을 한다.
OLTP 업무의 경우, 동일한 SQL이 여러차례 수행되므로
적절한 cache 영역을 확보함으로써 성능 향상을 기대할 수 있다.
- 물론 bind variable을 사용하여야만 공유가능한 SQL이 생성된다.

SHARED_POOL_SIZE 값이 적을 경우는 물론이거니와,
너무 이 값을 크게 지정해도 문제가 된다. SHARED_POOL_SIZE가 너무 클 경우,
새로운 SQL 수행시 가용한 메모리 영역을 찾아 내기 위한 latch contention 의 가능성이 높아지게 된다.


V$SGASTAT을 조회하여 free memory를 조사할 수 있으며,
메모리가 낭비되고 있는지 여부도 확인 가능하다.

SELECT name, bytes/1024/1024 "Size in MB"
FROM V$SGASTAT
WHERE name='free memory';

실행 결과는 다음과 같다.

NAME Size in MB
Free memory 39.6002884

이 결과는 shared pool에 39M 공간이 사용되지 않고 있으며,
만약 shared pool의 크기를 70M 로 지정하였다면,
절반 이상의 메모리 공간이 사용되지 않고 낭비되고 있음을 의미한다.



3. SORT_AREA_SIZE


SORT_AREA_SIZE에 대해서는 흔히 잘못된 이해를 하게된다.
대부분의 사용자들은 이 값이 모든 사용자들이
sort 작업에 사용하게 되는 공용 메모리 영역의 크기로 이해를 하는데,
실제로는 사용자 프로세스 별로 사용하게 되는 sort 영역의 크기를 나타낸다.
앞에서 살펴본 두개의 파라미터와 달리, SORT_AREA_SIZE는 SGA영역에 속하지 않는다.

만약 sort_area_size 값이 너무 작다면,
sort 작업 대부분이 사용자의 temporary tablespace에서 디스크를 사용하여 이루어 지게 된다.

SQL 처리시 order by 나, group by 등을 사용할 경우에는 sort 작업이 발생하나.
index 생성등에도 sort가 발생한다.


메모리 sort는 디스크 sort에 비해 훨씬 좋은 성능을 보이므로,
지속적으로 SORT_AREA_SIZE 값을 모니터하여 튜닝을 하는것이 바람직하다.
하지만, 이 값을 너무 크게 지정할 경우,
swapping이 발생하면서 시스템 성능이 급격하게 저하될 수 있다.


* SORT_AREA_SIZE는 세션별로도 지정가능하며, 지정하기 위해서는
ALTER SESSION 권한이 있어야 한다. 특정 세션에서 시스템상의
모든 메모리를 사용하도록 할 경우 시스템 성능이 급격히 저하
될 수도 있다.



4. DBWR_IO_SLAVES

DBWR_IO_SLAVES는 SORT_AREA_SIZE와 마찬가지로 사용자들이 흔히 잘못 이해하는 파라미터로,
Oracle 8 이후 버젼에서 사용된다.

이 파라미터는 Oracle 8 이전에 사용되던 DB_WRITERS 파라미터를 대체한다.
Oracle 8에서는 DB_WRITER_PROCESSES 라는 파라미터가 DB_WRITERS를 대체하지만,
DBWR_IO_SLAVES 파라미터와 함께 사용할 경우 아직까지도 문제점들이 발생한다.


DBWR_IO_SLAVES는 slave writer process가 - OS에서 지원할 경우 -
asynchronous I/O를 수행하도록 허용한다.

DB_WRITERS 및 DBWR_IO_SLAVES 관련 자료는 METALINK에 많이 올라와 있으며,
DB_WRITERS 와 DBWR_IO_SLAVES 는 동시에 사용하 지 못한다는 것을 이해하는 것이 중요하다.

* 참조



5. ROLLBACK_SEGMENTS

이 파라미터는 모든 버젼의 오라클에서 사용되며,
인스턴스 기동중에 온라인 상태로 사용할 rollback segment를 지정한다.
만약 파라미터에서 지정한 rollback segment가 존재하지 않는 것이라면 ora-1534 에러가 발생하며,
데이터베이스는 mount까지만 되고 open 되 지는 않는다.

Rollback segment는 트랜잭션에서 발생하는 변경사항을 기록하여,
트랜잭션이 rollback 되어야 할 경우 이전 상태로 돌리기 위한 각종
정보를 저장하는 영역이다. - Windows 의 undo 기능과 유사함.

Rollback segment는 여러 extent들로 구성되는데,
extent는 round-robin 방식으로 순환되며 사용된다.
즉, 현재 사용되는 extent가 full이 나는 경우 다음 extent를 사용하는 식으로 사용된다.

Rollback segment는 read consistency를 제공해 주고, 트랜잭션을 undo 시킬수 있고,
recovery에 사용되는 등, 데이터베이스에서 매우 중요한 역할을 수행한다.

Read consistency는 업무적으로도 매우 중요한데, 한 사용자 (1번 사용자) 가 데이터를 읽는동안,
다른 사용자가 (2번 사용자) 그 데이터에 변경을 가한다면,
2번 사용자가 데이터 변경을 일관성 있게 종료하기 전가지 1번 사용자는 이전 상태의 데이터,
즉 이전에 commit 된 상태의 데이터를 사용하여야만 데이터 일관성및 정합성이 보장된다.


RBS의 적정 크기는 다른 문제와 마찬가지로 데이터베이스 내에서
사용되는 일반적인 트랜잭션 레벨에 따라 다르다.
RBS extent의 크기와 관련해서는 오라클에서는
extent size와 관련된 (initial,next 값)권고 사항이 존재한다.

Rollback segment의 갯수와 관련해서는,
rollback segment간의 contention 이 발생하지 않도록 조정해 주는 것이 중요하다.
모든 트랜잭션은 RBS의 헤더에 존재하는 트랜잭션 테이블에 정보가 저장된다.
모든 트랜잭션이 이 테이블의 내용을 변경하여야 하므로, contention이 발생할 수 있다.
한 시점에 한개의 트랜잭션이 한개의 rollback segment를 사용하도록 하는
것이 일반적인 원칙이다. 오라클에서는 4개의 트랜잭션당 한개의
rollback segment를 사용하는 것을 권고하지만,
절대적인 기준이 아니라 상대적인 기준으로 보는 것이 바람직하다.

rollback segment간 contention을 조사하기 위해서는 v$waitstat을 조회하면 된다.
다음 query로 rollback segment간 contention을 조회해 볼 수 있다.


SELECT a. name, b.extents, b.rssize, b.xacts, b.waits,
b. gets, optsize, status
FROM V$ROLLNAME A, V$ROLLSTAT B
WHERE a.usn = b.usn;

실행결과는 대략 다음과 같은 형식으로 나타난다.

NAME EXTENTS RSSIZE XACTS WAITS GETS OPTSIZE STATUS
SYSTEM 4 540672 1 0 51 ONLINE
RB1 2 10240000 0 0 427 10240000 ONLINE
RB2 2 10240000 1 0 425 10240000 ONLINE
RB3 2 10240000 1 0 422 10240000 ONLINE
RB4 2 10240000 0 0 421 10240000 ONLINE

위의 질의를 처리한 결과로 "xacts" ( 트랜잭션의 줄임말 ) 가 계속해서 1 이상이 경우,
rollback segment의 갯수를 늘려주는 것이 contention이 발생할 가능성을 줄여준다.
만약 wait 갯수가 0보다 크고, 특별한 사항에서만 나타나는 것이 아니라 항상 비슷한 상황이라면,
이 경우에도 rollback segment의 갯수를 늘려주는 편이 낫다.

* Rollback segment의 적정 갯수 도출관련 자료는 , 참조
* Rollback segment의 생성, 최적화 관련 자료는 , 참조



6. SORT_AREA_RETAINED_SIZE

init.ora 파일에서 지정하는 sort 작업 관련된 파라미터로 SORT_AREA_RETAINED_SIZE 도 있다.
이 값은 sort 가 끝난 후에도 유지하고자 하는 SORT_AREA_SIZE를 나타낸다.
이 파라미터는 SORT_AREA_SIZE 값과 같거나 적게 지정되어야 한다.

SORT_AREA_RETAINED_SIZE는 SORT_AREA_SIZE와 마찬가지로 적절한 값이 지정되어야 하는데,
소트작업을 수행하기 위해 할당된 메모리 영역이 소트 작업이 끝난 후가 아니라 세션이 종료될 때
까지 유지될 수 있기 때문이다.

SORT_AREA_SIZE 값은 다른 파라미터와 마찬가지로 시스템에 가용한 실제 메모리 크기 이내에서 조정되어야 한다.
일반적으로 권고되는 SORT_AREA_SIZE 값은 65k 에서 1M 사이 에서 결정된다.



7. DB_BLOCK_LRU_EXTENDED_STATISTICS

Oracle 8i 부터는 사용되지 않는 파라미터로, SGA의 buffer cache 값을
증가시키거나 감소시킬 경우 미치는 영향을 예측하기 위한 각종 통계 정보를
수집하는 작업을 활성화 시키거나 비 활성화 시킬 수 있다.

사용자는 DB_BLOCK_BUFFERS 값을 바꾸어 시스템을 재 기동 시키지 않고도,
alter system 명령으로 buffer cache 크기를 조정할 수 있게 해 주시만,
내부적으로는 DB_BLOCK_BUFFERS 값은 데이터 베이스 재 기동시에만 바뀔 수 있다.

통계정보는 X$KCBRBH 테이블에 저장된다.

이 값을 0 이상으로 지정하면 DB_BLOCK_BUFFERS 값을 추가하거나
혹은 추가한 것처럼 simulate 시킬 수 있다.

기능상으로는 튜닝에 많은 도움을 줄 것 처럼 보이나,
많은 문제점을 안고 있는 것으로 알려져 있으므로 오라클에서는
production 환경에서는 사용하지 않도록 권고하고 있다.



8. SHARED_POOL_RESERVE_SIZE

sahred pool의 일정 부분을 larget object을 위해 할당하도록 지정하는 파라미터로,
기본적으로는 shared_pool_size의 5% 정도가 사용된다.
파라미터 값은 byte 단위로 지정한다.

이 파라미터를 지정할 때 유의해야 할 점은 shared pool의 대부분의 영역이
large object에 의해 사용되지 않도록 하고,
large object는 별도의 영역에서 처리되도록 지정하는
것이 관건이다.



Reference Documents -------------------













- 김정식 [2004-11-11]
-- Current Hit Ratio
SELECT SUM(DECODE(Name, 'consistent gets',Value,0)) Consiste
nt,
SUM(DECODE(Name, 'db block gets',Value,0)) Dbblockget
s,
SUM(DECODE(Name, 'physical reads',Value,0)) Physrds,
ROUND(((SUM(DECODE(Name, 'consistent gets', Value, 0)
)+
SUM(DECODE(Name, 'db block gets', Value, 0)) -
SUM(DECODE(Name, 'physical reads', Value, 0)) )/
(SUM(DECODE(Name, 'consistent gets',Value,0))+
SUM(DECODE(Name, 'db block gets', Value, 0)))) *100,2
) Hitratio
FROM V$SYSSTAT;

유저 권한

SQL> select * from user_sys_privs;

USERNAME PRIVILEGE ADM
----------------------------------------------- ---
xxx ALTER TABLESPACE NO
xxx SELECT ANY TABLE NO
xxx UNLIMITED TABLESPACE NO