ORACLE/TUNING2009. 12. 16. 10:52

cursor_sharing 파라미터에 대한 테스트

반응형

                                                                                    

cursor_sharing 파라미터에 대한 테스트

오라클 데이터베이스의 파라미터 중 cursor_sharing의 value는 exact, similar, force의 값을 가진다. 값의 설정에 따른 영향도와 정확한 동작 방식을 확인하고자 테스트 진행

테스트 시나리오

child cursor이 생기지 않는 형식의 테스트(bind peeking 사용)

1.   테스트 데이터 생성(num의 값이 unique한 10만건 테이블, 인덱스 생성)

2.   cursor_sharing 변경(exact, similar, force)

3.   10개의 JOB 등록(각 JOB은 1만번의 루프를 수행하며 루프 마다 ‘where num = 값’의 조건절 중 값이 리터럴하게 변경되면서 수행)

4.   모니터링

child cursor가 생기는 방식의 테스트(bind peeking 사용)는 위의 방식과 동일한 방식으로 진행되지만 조건절을 ‘where num between A and 값’ 형식으로 값을 변경해 가면서 테스트 진행한다.

 

사용스크립트

       simulation_hard_parsing.sh

#!/bin/bash

 

## $1 => Sample Data Row Count

## 등록되는 job을 10 개로 함

## Hard Parsing Simulation

 

sqlplus /nolog <<EOF

 

CONN / AS SYSDBA

GRANT EXECUTE ON DBMS_SCHEDULER to HR;

DECLARE

BEGIN

   FOR C IN 1 .. 10

   LOOP

      SYS.DBMS_SCHEDULER.DROP_JOB('HR.TEST_SCHEDULE' || '_' || C);

   END LOOP;

END;

/

 

CONN HR/HR

-- Make Test Data

@/home/oracle/script/make_sample_data.sql ALL_OBJECTS TEST $1

 

-- Index 생성하여 index scan 유도

CREATE INDEX TEST_IDX ON TEST(NUM,OBJECT_NAME)

TABLESPACE USERS;

 

-- Creation Procedure for Load

DEFINE P_TOTAL = $1

CREATE OR REPLACE PROCEDURE CURSOR_SHARING_TEST(P_CNT IN NUMBER DEFAULT 0 )

AS

   V_NUM          NUMBER;

   V_OBJECT_NAME  VARCHAR2(128);

   V_OBJECT_ID    NUMBER;

   V_CNT          NUMBER;

   V_STMT         VARCHAR2(4000);

BEGIN

   V_CNT       := P_CNT;

 

   FOR C IN 1 .. &P_TOTAL/10

   LOOP

      V_CNT       := V_CNT + 1;

      V_STMT      := 'SELECT NUM, OBJECT_NAME, OBJECT_ID FROM TEST WHERE NUM = ' || V_CNT;

 

      EXECUTE IMMEDIATE V_STMT INTO   V_NUM, V_OBJECT_NAME, V_OBJECT_ID;

   END LOOP;

END;

/

 

-- Register 10 Test Schedules

CONN / AS SYSDBA

EXECUTE DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => 'HR',TABNAME => 'TEST' , METHOD_OPT => 'for all columns size skewonly' ,DEGREE  => 4   ,CASCADE  => TRUE );

EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

ALTER SYSTEM SET CURSOR_SHARING = EXACT;

ALTER SYSTEM FLUSH SHARED_POOL;

ALTER SYSTEM FLUSH BUFFER_CACHE;

 

DECLARE

BEGIN

   FOR C IN 1 .. 10

   LOOP

      BEGIN

         SYS.DBMS_SCHEDULER.CREATE_JOB(JOB_NAME    => 'HR.TEST_SCHEDULE' || '_' || C

                                      ,JOB_TYPE    => 'STORED_PROCEDURE'

                                      ,JOB_ACTION  => 'HR.CURSOR_SHARING_TEST'

                                      ,START_DATE  => CURRENT_TIMESTAMP

                                      ,JOB_CLASS   => 'DEFAULT_JOB_CLASS'

                                      ,COMMENTS    => 'cursor_sharing value test'

                                      ,AUTO_DROP   => FALSE

                                      ,NUMBER_OF_ARGUMENTS => 1

                                      ,ENABLED     => FALSE

                                      );

         SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(NAME        => 'HR.TEST_SCHEDULE' || '_' || C

                                         ,ATTRIBUTE   => 'logging_level'

                                         ,VALUE       => DBMS_SCHEDULER.LOGGING_FULL

                                         );

         SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(JOB_NAME    => 'HR.TEST_SCHEDULE' || '_' || C

                                                  ,ARGUMENT_POSITION => 1

                                                  ,ARGUMENT_VALUE => &P_TOTAL/10 * C

                                                  );

         SYS.DBMS_SCHEDULER.ENABLE('HR.TEST_SCHEDULE' || '_' || C);

      END;

   END LOOP;

END;

/

 

EXIT

EOF

       ALTER SYSTEM SET CURSOR_SHARING=EXACT 부분을 바꾸어 가면서 테스트 진행

 

simulation_hard_parsing_child.sh

#!/bin/bash

 

## $1 => Sample Data Row Count

## 등록되는 job을 10 개로 함

## Hard Parsing Simulation

 

sqlplus /nolog <<EOF

 

CONN / AS SYSDBA

GRANT EXECUTE ON DBMS_SCHEDULER to HR;

DECLARE

BEGIN

   FOR C IN 1 .. 10

   LOOP

      SYS.DBMS_SCHEDULER.DROP_JOB('HR.TEST_SCHEDULE' || '_' || C);

   END LOOP;

END;

/

 

CONN HR/HR

-- Make Test Data

@/home/oracle/script/make_sample_data.sql ALL_OBJECTS TEST $1

 

-- Index 생성하여 index scan 유도

CREATE INDEX TEST_IDX ON TEST(NUM,OBJECT_NAME)

TABLESPACE USERS;

 

-- Creation Procedure for Load

DEFINE P_TOTAL = $1

CREATE OR REPLACE PROCEDURE CURSOR_SHARING_TEST(P_CNT IN NUMBER DEFAULT 0 )

AS

   V_NUM          NUMBER;

   V_OBJECT_NAME  VARCHAR2(128);

   V_OBJECT_ID    NUMBER;

   V_CNT          NUMBER;

   V_STMT         VARCHAR2(4000);

BEGIN

   V_CNT       := P_CNT;

 

   FOR C IN 1 .. &P_TOTAL/10

   LOOP

      V_CNT       := V_CNT + 1;

      V_STMT      := 'SELECT COUNT(*) FROM TEST WHERE NUM BETWEEN ' || P_CNT || ' AND ' || V_CNT;

 

      EXECUTE IMMEDIATE V_STMT INTO   V_NUM;

   END LOOP;

END;

/

 

-- Register 10 Test Schedules

CONN / AS SYSDBA

EXECUTE DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => 'HR',TABNAME => 'TEST' , METHOD_OPT => 'for all columns size skewonly' ,DEGREE  => 4   ,CASCADE  => TRUE );

EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

ALTER SYSTEM SET CURSOR_SHARING = EXACT;

ALTER SYSTEM FLUSH SHARED_POOL;

ALTER SYSTEM FLUSH BUFFER_CACHE;

 

DECLARE

BEGIN

   FOR C IN 1 .. 10

   LOOP

      BEGIN

         SYS.DBMS_SCHEDULER.CREATE_JOB(JOB_NAME    => 'HR.TEST_SCHEDULE' || '_' || C

                                      ,JOB_TYPE    => 'STORED_PROCEDURE'

                                      ,JOB_ACTION  => 'HR.CURSOR_SHARING_TEST'

                                      ,START_DATE  => CURRENT_TIMESTAMP

                                      ,JOB_CLASS   => 'DEFAULT_JOB_CLASS'

                                      ,COMMENTS    => 'cursor_sharing value test'

                                      ,AUTO_DROP   => FALSE

                                      ,NUMBER_OF_ARGUMENTS => 1

                                      ,ENABLED     => FALSE

                                      );

         SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(NAME        => 'HR.TEST_SCHEDULE' || '_' || C

                                         ,ATTRIBUTE   => 'logging_level'

                                         ,VALUE       => DBMS_SCHEDULER.LOGGING_FULL

                                         );

         SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(JOB_NAME    => 'HR.TEST_SCHEDULE' || '_' || C

                                                  ,ARGUMENT_POSITION => 1

                                                  ,ARGUMENT_VALUE => &P_TOTAL/10 * C

                                                  );

         SYS.DBMS_SCHEDULER.ENABLE('HR.TEST_SCHEDULE' || '_' || C);

      END;

   END LOOP;

END;

/

 

EXIT

EOF

       리터럴 SQL 문장이 between을 사용하는 형태로 바뀌었다.

 

       make_sample_data.sql

-- usage : 1 => Source Table, 2 => Target Table 3 => target Total Rows

 

define cnt=idle

DROP TABLE &2 PURGE;

 

CREATE TABLE &2

AS

   SELECT   1 NUM, ROWNUM SOURCE_NUM, A.*

   FROM     &1 A

   WHERE    1 = 2;

 

COLUMN TBL_CNT NEW_VALUE CNT

 

SELECT   COUNT( * ) TBL_CNT FROM &1;

 

INSERT INTO &2

   SELECT     A.NUM, B.*

   FROM       (SELECT       LEVEL NUM

               FROM         DUAL

               CONNECT BY   LEVEL <= &3) A, (SELECT   ROWNUM - 1 NUM, A.*

                                             FROM     &1 A) B

   WHERE      MOD(A.NUM, &CNT) = B.NUM

   ORDER BY   A.NUM;

 

COMMIT;

 

테스트 중 측정할 데이터 리스트

l  AWR Report

l  ASH Report

l  EM Performance Graph

l  OS 레벨의 vmstat 정보

테스트 결과1 (child cursor이 생기지 않는 시나리오)

먼저 EM의 성능 그래프를 확인해 보자.

 

성능 그래프를 확인해 보면 역시 예상대로 exact 부분에서 wait 현상이 발생하는 것을 확인할 수 있다.

EAXCT

AWR Report를 살펴 보자

 

Load Profile

Load Profile

~~~~~~~~~~~~                            Per Second       Per Transaction

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

                  Redo size:              5,209.31             29,990.00

              Logical reads:              1,398.13              8,049.05

              Block changes:                 16.93                 97.48

             Physical reads:                 21.92                126.21

            Physical writes:                  5.00                 28.76

                 User calls:                  4.18                 24.07

                     Parses:                393.62              2,266.07

                Hard parses:                375.25              2,160.33

                      Sorts:                 15.11                 87.00

                     Logons:                  0.12                  0.67

                   Executes:                424.32              2,442.79

               Transactions:                  0.17

역시 리터럴 SQL들은 다른 SQL문으로 받아 들여 Hard parse 수치가 높게 나온다.

 

Instance Efficiency Percentages

Instance Efficiency Percentages (Target 100%)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

            Buffer Nowait %:  100.00       Redo NoWait %:  100.00

            Buffer  Hit   %:   98.43    In-memory Sort %:  100.00

            Library Hit   %:   61.36        Soft Parse %:    4.67

         Execute to Parse %:    7.23         Latch Hit %:   99.93

Parse CPU to Parse Elapsd %:   14.10     % Non-Parse CPU:   58.98

역시 Library Hit율이 떨어진다.

 

Top 5 Timed Events

Top 5 Timed Events                                         Avg %Total

~~~~~~~~~~~~~~~~~~                                        wait   Call

Event                                 Waits    Time (s)   (ms)   Time Wait Class

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

CPU time                                            206          13.3

db file sequential read               4,788         103     21    6.6   User I/O

os thread startup                       100          85    852    5.5 Concurrenc

latch: library cache                  3,541          58     16    3.7 Concurrenc

cursor: mutex X                           6          51   8447    3.3 Concurrenc

Event 중에 latch: library cache에 대한 event가 많음을 확인할 수 있다.

 

Time Model Statistics

^LTime Model Statistics                        DB/Inst: ORCL/ORCL  Snaps: 69-70

-> Total time in database user-calls (DB Time): 1544.8s

-> Statistics including the word "background" measure background process

   time, and so do not contribute to the DB time statistic

-> Ordered by % or DB time desc, Statistic name

 

Statistic Name                                       Time (s) % of DB Time

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

sql execute elapsed time                              1,555.0        100.7

parse time elapsed                                    1,220.3         79.0

hard parse elapsed time                                 508.4         32.9

DB CPU                                                  206.1         13.3

PL/SQL execution elapsed time                            19.6          1.3

connection management call elapsed time                  15.3          1.0

PL/SQL compilation elapsed time                           4.1           .3

hard parse (sharing criteria) elapsed time                0.8           .1

repeated bind elapsed time                                0.2           .0

hard parse (bind mismatch) elapsed time                   0.1           .0

sequence load elapsed time                                0.0           .0

DB time                                               1,544.8          N/A

background elapsed time                                 221.1          N/A

background cpu time                                       1.9          N/A

Time Model의 통계를 확인해 보면 역시나 Hard Parse에 많은 부분을 사용하는 걸 확인할 수 있다.

 

ASH Report 역시 비슷한 결과에 해당하는 내용이 있다.

SIMILAR

Load Profile

Load Profile

~~~~~~~~~~~~                            Per Second       Per Transaction

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

                  Redo size:              2,690.38             22,573.94

              Logical reads:              1,233.33             10,348.35

              Block changes:                  8.17                 68.58

             Physical reads:                 17.18                144.13

            Physical writes:                  1.41                 11.81

                 User calls:                  4.13                 34.61

                     Parses:                363.36              3,048.84

                Hard parses:                  2.68                 22.45

                      Sorts:                 11.68                 97.97

                     Logons:                  0.11                  0.90

                   Executes:                386.53              3,243.26

               Transactions:                  0.12

같은 테스트지만 Hard parse 부분이 많은 부분 없어진 걸 확인할 수 있다. 파라미터 값을 similar로 변경하여 테스트에 사용한 리터럴 SQL이 동일한 문장은 아니지만 같은 플랜을 사용하게끔 인식이 되는 것이다. 그래서 이미 Library Cache에 존재하는 parsing 정보를 사용하기에 Hard parse를 하지 않게 되었다. 이하의 결과에도 마찬가지의 정보를 보여준다.

 

Instance Efficiency Percentages

Instance Efficiency Percentages (Target 100%)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

            Buffer Nowait %:  100.00       Redo NoWait %:  100.00

            Buffer  Hit   %:   98.61    In-memory Sort %:  100.00

            Library Hit   %:   97.66        Soft Parse %:   99.26

         Execute to Parse %:    5.99         Latch Hit %:   99.76

Parse CPU to Parse Elapsd %:   27.30     % Non-Parse CPU:   91.17

Library Hit율이 97%까지 올라간 결과를 확인할 수 있다.

 

Top 5 Timed Events

Top 5 Timed Events                                         Avg %Total

~~~~~~~~~~~~~~~~~~                                        wait   Call

Event                                 Waits    Time (s)   (ms)   Time Wait Class

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

CPU time                                             53          13.9

db file sequential read               4,218          25      6    6.7   User I/O

library cache load lock                  17           3    195    0.9 Concurrenc

os thread startup                        13           3    253    0.9 Concurrenc

log file parallel write                 123           3     23    0.7 System I/O

latch: library cache에 대한 event가 사라진 것을 확인할 수 있다.

 

Time Model Statistics

^LTime Model Statistics                        DB/Inst: ORCL/ORCL  Snaps: 71-72

-> Total time in database user-calls (DB Time): 378.6s

-> Statistics including the word "background" measure background process

   time, and so do not contribute to the DB time statistic

-> Ordered by % or DB time desc, Statistic name

 

Statistic Name                                       Time (s) % of DB Time

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

sql execute elapsed time                                386.4        102.1

parse time elapsed                                       86.6         22.9

DB CPU                                                   52.6         13.9

PL/SQL execution elapsed time                            38.0         10.0

hard parse elapsed time                                  21.6          5.7

PL/SQL compilation elapsed time                           4.8          1.3

connection management call elapsed time                   0.4           .1

repeated bind elapsed time                                0.2           .1

hard parse (sharing criteria) elapsed time                0.2           .0

failed parse elapsed time                                 0.2           .0

sequence load elapsed time                                0.0           .0

DB time                                                 378.6          N/A

background elapsed time                                  12.0          N/A

background cpu time                                       1.6          N/A

전체적으로 parsing과 관련된 시간이 줄어든 것을 확인할 수 있으며 이를 통해 sql execute elapsed time이 줄어 든 것이 확인 가능하다. 결론적으로 DB time 자체가 줄어드는 성능적인 이득을 취하게 된다. 이것을 다르게 말하면 전체적으로 시스템의 Response Time이 확연히 증가함을 말한다. End 유저는 확실한 성능 차이를 체감하게 될 것이다.

 

ASH Report로 마찬가지의 결과를 보여주고 있다.

FORCE

Load Profile

Load Profile

~~~~~~~~~~~~                            Per Second       Per Transaction

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

                  Redo size:              2,340.54             21,036.32

              Logical reads:                966.83              8,689.63

              Block changes:                  8.08                 72.63

             Physical reads:                 13.81                124.08

            Physical writes:                  1.17                 10.50

                 User calls:                  2.42                 21.71

                     Parses:                278.51              2,503.18

                Hard parses:                  2.97                 26.66

                      Sorts:                  9.02                 81.08

                     Logons:                  0.08                  0.76

                   Executes:                297.61              2,674.82

               Transactions:                  0.11

similar 의 경우와 비슷한 수치를 보이고 있다.

 

Instance Efficiency Percentages

Instance Efficiency Percentages (Target 100%)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

            Buffer Nowait %:  100.00       Redo NoWait %:  100.00

            Buffer  Hit   %:   98.58    In-memory Sort %:  100.00

            Library Hit   %:   97.35        Soft Parse %:   98.94

         Execute to Parse %:    6.42         Latch Hit %:   99.91

Parse CPU to Parse Elapsd %:   33.57     % Non-Parse CPU:   93.78

similar 의 경우와 비슷한 수치를 보이고 있다.

 

Top 5 Timed Events

Top 5 Timed Events                                         Avg %Total

~~~~~~~~~~~~~~~~~~                                        wait   Call

Event                                 Waits    Time (s)   (ms)   Time Wait Class

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

CPU time                                             77          13.2

db file sequential read               4,384          20      5    3.4   User I/O

job scheduler coordinator slav            1           8   7984    1.4      Other

Streams AQ: qmn coordinator wa            1           5   4884    0.8      Other

os thread startup                        13           4    276    0.6 Concurrenc

latch: library cache에 대한 event가 사라진 것을 확인할 수 있다.

 

Time Model Statistics

^LTime Model Statistics                        DB/Inst: ORCL/ORCL  Snaps: 73-74

-> Total time in database user-calls (DB Time): 585.2s

-> Statistics including the word "background" measure background process

   time, and so do not contribute to the DB time statistic

-> Ordered by % or DB time desc, Statistic name

 

Statistic Name                                       Time (s) % of DB Time

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

sql execute elapsed time                                597.6        102.1

parse time elapsed                                      122.9         21.0

DB CPU                                                   77.4         13.2

PL/SQL execution elapsed time                            44.4          7.6

hard parse elapsed time                                  21.8          3.7

PL/SQL compilation elapsed time                           3.4           .6

hard parse (sharing criteria) elapsed time                0.7           .1

connection management call elapsed time                   0.2           .0

repeated bind elapsed time                                0.2           .0

failed parse elapsed time                                 0.1           .0

sequence load elapsed time                                0.1           .0

hard parse (bind mismatch) elapsed time                   0.0           .0

DB time                                                 585.2          N/A

background elapsed time                                  27.2          N/A

background cpu time                                       2.3          N/A

similar 때와 비슷한 상황으로 분석된다.

테스트 결과2 (child cursor이 생기는 시나리오)

child cursor의 발생 상황에 대해서 먼저 정의가 필요하다. cursor_sharing 파라미터의 값이 similar일 경우 오라클은 실행계획이 같다고 판단이 되는 경우 동일한 형태의 리터럴 SQL 문장에 대해서 같은 문장으로 인식하고 사용하게끔 한다. 하지만 실행계획은 같지만 포함된 통계정보가 다를 경우 같은 문장으로는 인식을 하지만 재사용을 하는 것이 아니라 child cursor을 생성하여 수행하게 된다. 여기에는 여러 변수가 작용할 수 있다. Bind peeking과 히스토그램, 옵티마이저의 방식 등등이 영향을 미치게 된다.

테스트 시스템은 bind peeking을 사용하며 CBO 방식을 사용했으며 child cursor이 발생하는 환경으로 세팅을 완료했다.

다음은 테스트 결과를 보도록 하겠다.

 

 

Similar의 경우 오히려 더 많은 wait event가 발생한 것을 확인할 수 있다.

 

exact, force인 경우 테스트1의 경우와 비슷한 레벨로 보이고 similar에서는 오히려 성능이 더 떨어지는 그래프를 보이고 있다. 결론적으로 similar인 경우에 대해서는 테스트를 완료할 수 없었다. Child cursor이 3만개 이상 발생하자 Job slave가 terminated 되고 오라클 600에러를 내면서 작업이 fail로 끝나게 된다.

결론

cursor_sharing 파라미터의 경우 많은 부분이 해당 value인 exact, similar, force에 대한 용어적 해석을 통한 접근으로 정확하지 않은 표현으로 가이드 되는 경우가 많다.

테스트의 출발은 위 value가 정확히 어떤 기준으로 동작하는지 확인 하는 것이었다.

Similar인 경우 실행계획이 같은 문장에 한해서는 같은 문장으로 인식하고 library cache에 로딩되어 있는 정보를 그대로 사용한다. 하지만 플랜에 포함된 통계정보가 다르다고 판단이 되는 실행계획일 경우 child cursor을 생성하여 사용하게 된다. 즉 similar인 경우 통계정보까지도 동일한 형태의 실행계획일 경우에 child cursor을 생성하지 않고 사용하게 된다. 반면 force인 경우는 실행계획만 같다면 similar와 다르게 같은 문장으로 인식하여 cache된 정보를 재사용하게 한다.

그렇다면 파라미터에 대해선 어떻게 사용해야할까..?

이는 상황에 따라서 다르겠지만 여러가지를 종합적으로 고려를 해야한다. 분명 가이드는 exact 이다. 그리고 bind 변수 사용에 대해 최대한 권장을 해야한다. 임시방편 격으로 similar, force를 사용 가능하다. 하지만 이것도 고려 사항이 있다. Similar인 경우 child cursor가 발생하는 형식으로 된다면 시스템은 버티기가 힘들 것이다. 즉 사이드 이펙트가 없는 상황일 경우에 쓸만하다는 것이다. 그렇다면 force를 쓰는게 더 낫지 않겠냐는 반문이 나올 것이다. 하지만 이도 문제 요소가 있다. 데이터의 분포가 균등하지 못한 경우 사용되는 변수의 값이 어떤 것이냐에 따라서 적합한 실행계획이 다른 바뀔 것이다. Force의 경우 이런 케이스를 커버하지 못한다는 단점이 있는 것이다.

[출처] cursor_sharing 파라미터에 대한 테스트|작성자 명품 관

반응형
저작자표시 비영리

'ORACLE > TUNING' 카테고리의 다른 글

EXPLAIN PLAN(실행계획) 이란  (0) 2010.01.12
Statspack Report 간단 분석 방법  (0) 2009.12.17
Literal SQL 조회하는 방법  (0) 2009.12.05
DML 과 PARALLEL의 관계  (0) 2009.11.06
Oracle dump 뜨는 방법  (0) 2009.05.08
Posted by [PineTree]
ORACLE/INSTALL2009. 12. 15. 16:59

RAC 패치 절차

반응형
01. CRS Patch(10.2.0.x -> 10.2.0.4)
02. DB Engine Patch(10.2.0.x -> 10.2.0.4)
03. CLUSTER_DATABASE Parameter FALSE 수정
04. STARTUP UPGRADE 후 SQL> @?/rdbms/admin/catupgrd.sql
05. SHUTDOWN IMMEDIATE 후 SQL> @?/rdbms/admin/utlrp.sql
06. CLUSTER_DATABASE Parameter TRUE 원복


반응형
저작자표시 비영리

'ORACLE > INSTALL' 카테고리의 다른 글

오라 클 9.2.0.1에서 Patch 셋 올리기  (0) 2010.05.31
oracle enterprise manager 한글 버튼 깨짐 문제  (0) 2010.01.11
Requirements for Installing Oracle 11gR2 RDBMS on RHEL (and OEL) 4 on AMD64/EM64T  (0) 2009.09.07
Oracle과 한글 그리고 UTF-8 <네번째>  (0) 2009.09.04
Oracle과 한글 그리고 UTF-8 <세번째>  (0) 2009.09.04
Posted by [PineTree]
ORACLE/PARAMETER2009. 12. 10. 14:03

parameter SESSION_CACHED_CURSORS

반응형

--------------------------------------------------------------------------------
Syntax  SESSION_CACHED_CURSORS 
설정방법  Parameter File
ALTER SESSION SET SESSION_CACHED_CURSORS = 100;
ALTER SYSTEM SET SESSION_CACHED_CURSORS = 100;
--------------------------------------------------------------------------------
 
SESSION_CACHED_CURSORS 파라미터 값이 0보다 크면 Session Cursor Caching 기능이 사용된다. Session Cursor Caching 기능이란 하나의 Session 내에서 3회 이상 parse call한 커서 SQL Statement를 PGA 영역에 Cache하는 것을 의미한다.

sql문이 실행될 때마다 세션의 파싱 단계에서 library cache를 검색한다. 하지만 자신이 수행하고자 하는 Execute plan이 없으면 'Hard parsing'을 할 것이고, 있으면 'Soft parsing'을 하게 될 것이다.
"Hard parsing 뿐만 아니라 "Soft parsing"도 librrary cache latch 와 cpu 오버 헤드를 발생시킨다.

일반적으로 Soft Parse가 왕성하고 한번에 많은 수의 Cursor를 사용하는 Application에서는 SESSION_CACHED_CURSORS 파라미터의 값을 크게 함으로써 library cache 래치 경합을 줄일 수 있다.

각각의 커서가 고정된 경우는 이 영역에 대해서 할당할 수 있는 더 많은 shared pool 영역을 요구할 수도 있다. 일반적인 shared pool에서의 커서는 2개의 컴퍼넌트로 구성되어 있다.
a)heap 0 - 1 KB 크기
b)SQL AREA - 4KB 배수 크기

Session Cursor Caching에 의해 PGA에 Cache된 Cursor는 다음과 같은 방법으로 Shared Pool에 Pin된다.

   1. Cursor를 구성하는 Heap0(Cursor 기본 정보)은 Pin된다.
   2. Cursor를 구성하는 Heap6(Cursor의 실행 계획 정보)는 Pin되지 않는다. 

Pin된 영역은 Flush되지 않는다. 따라서 Session Curors Caching에 의해 Cache된 Cursor의 기본 정보는 Shared Pool에 계속 상주하게 된다. 이런 이유 때문에 Cache된 Cursor에 대해서는 Shared Pool의 특정 영역으로 직접 Access가 가능하고 그 만큼 library cache 래치를 점유하는 시간이 줄어든다. 반면 이렇게 Cache된 Cursor의 개수가 지나치게 많으면 그만큼 Flush되지 않는 Cursor(Heap0)의 수가 증가한다. 따라서 Shared Pool의 Fragmentation 현상이 발생할 수 있다.

이런 이유 때문에 Hard Parse가 왕성한 시스템에서는 Flush가 원활하게 이루어져야 하기 때문에 SESSION_CACHED_CURSORS 파라미터 값을 낮게 설정하는 것이 좋다. 보통 50 정도의 값에서 시작하는 것이 권장되며, Hard Parse와 Soft Parse의 발생 정도, Shared Pool의 크기에 따라 가감하는 방식을 사용한다.

  이 파라미터의 동작유무를 체크하려면 다음과 같이 수행한다.

SQL> select max(VALUE) from v$sesstat
where STATISTIC# in(select STATISTIC# from v$STATNAME where NAME = 'session cursor cache count');

MAX(VALUE)
-----------
         20

1 rows selected.

  과거에 session_cached_cursors의 최대값을 보여주는데, 이 값이 init.ora에서 value = "session_cached_cursors" 라면 이 값을 증가할지를 고려해보야 한다.

  이 session cache가 얼마나 얼마나 동작하는지를 보고싶다면 다음과 같이 수행한다.

SQL> select cache/tot*100 "Session cursor cache%"
from
(select value tot from v$sysstat where name = 'parse count (total)'),
(select value cache from sys.v$sysstat where name = 'session cursor cache hits');

Session cur
-----------
 8.849e+001

1 rows selected.

결론적으로, OLTP 어플리케이션에서 같은 SQL 세트는 많은 횟수로 실행되기 때문에 반드시 이 파라미터 값을 Default 값이 50 이상으로 세팅할 필요가 있다.

반응형
저작자표시 비영리

'ORACLE > PARAMETER' 카테고리의 다른 글

[SGA튜닝]Cursor Sharing Parameter  (0) 2008.06.10
ORACLE 9i Parameter 설명  (0) 2007.02.10
Posted by [PineTree]
ORACLE/ADMIN2009. 12. 9. 14:32

sqlplus 환경 설정

반응형

vi $ORACLE_HOME/sqlplus/admin/glogin.sql

-- User Environment
set timing on
set time on
set pagesize 100
set linesize 160
반응형
저작자표시 비영리

'ORACLE > ADMIN' 카테고리의 다른 글

ORACLE Datatype Limits  (0) 2010.03.05
데이타펌프 (data pump) 특정 테이블을 익스포트 받지 않을 때  (0) 2010.02.26
oracle 권한확인  (0) 2009.12.04
emca 재구성  (0) 2009.11.20
DBLINK시 LOB CLOB 컬럼오류 해결  (0) 2009.11.20
Posted by [PineTree]
ORACLE/TUNING2009. 12. 5. 07:34

Literal SQL 조회하는 방법

반응형
Oracle에서 OLTP 업무를 위한 SQL 구현시 Literal SQL을 사용하지 말라는 얘기를 한다. 모든 SQL에 일관되게 적용할 수는 없겠지만 몇몇 예외를 제외하고는 Bind Variable을 사용하여 구현하는 것이 좋다.

따라서, DBA들이 개발자에게 Literal SQL을 검색해서 수정 요청을 하는 경우가 많은데 Literal SQL을 조회하는 방법에 대해서 알아보도록 하자.

크게 두가지 방법으로 Literal SQL을 검색한다. 첫번째는 sql_text를 이용한 검색이고, 두번째는 plan_hash_value를 통해서 찾을 수가 있다.

1. sql_text 이용한 검색 방법

사용된 sql의 text를 50자를 짜라서 하나의 데이터로 간주해서 동일한 sql을 찾는 방법이다.
물론 이 방법도 100% 정확하지는 않다. 왜냐하면 50자는 동일하지만 그 다음의 sql_text는 다를 수 있기 때문이다.  다양하게 구현할 수 있겠지만 간략하게 literal sql은 아래와 같다.

SELECT hash_value,
       module,
       first_load_time,
       SUBSTR (sql_text, 1, 50)
FROM   v$sqlarea
WHERE  SUBSTR (sql_text, 1, 50) IN (SELECT SUBSTR (sql_text, 1, 50)
        FROM   v$sqlarea
        HAVING COUNT (*) > 4
        GROUP BY SUBSTR (sql_text, 1, 50))
ORDER BY sql_text;



2. plan_hash_value 이용한 검색 방법

동일한 sql 이지만 literal 을 사용하여 다른 sql 으로 시스템에 사용된 sql은 대부분 동일한 실행계획을 가질 확률이 높다. 따라서 plan_hash_value을 이용하여 Literal SQL을 찾을 수도 있다.
plan_hash_value를 통해서 찾은 sql도 100% 다 Literal SQL은 아니고 Literal SQL candidates할 수 있겠다.


select *    
from   (select PLAN_HASH_VALUE hash,
               count(PLAN_HASH_VALUE) cnt ,
               substr(sql_text, 0, 50)
        from   v$sql
        where  plan_hash_value > 0
        group by PLAN_HASH_VALUE , substr(sql_text, 0, 50)
        order by count(PLAN_HASH_VALUE) )
where  cnt > 5

그 밖에 좋은 방법이 있다면 공유를 해주시기 바랍니다.
반응형
저작자표시 비영리

'ORACLE > TUNING' 카테고리의 다른 글

Statspack Report 간단 분석 방법  (0) 2009.12.17
cursor_sharing 파라미터에 대한 테스트  (0) 2009.12.16
DML 과 PARALLEL의 관계  (0) 2009.11.06
Oracle dump 뜨는 방법  (0) 2009.05.08
SQL Trace와 TKPROF 유틸리티  (0) 2009.03.19
Posted by [PineTree]
ORACLE/ADMIN2009. 12. 4. 09:58

oracle 권한확인

반응형
☞ 부여된 권한 확인
　●부여받거나 부여한 권한을 확인하기 위해 Dictionary뷰를 액세스한다.

Dictionary뷰 설명
ROLE_SYS_PRIVS role에 부여된 시스템 권한
ROLE_TAB_PRIVS role에 부여된 테이블 권한
USER_ROLE_PRIVS 사용자가 액세스할 수 있는 role
USER_TAB_PRIVS_MADE 사용자가 부여한 객체 권한
USER_TAB_PRIVS_RECD 사용자에게 부여된 객체 권한
USER_COL_PRIVS_RECD 특정 Column에 대하여 사용자에게
부여된 객체 권한
♣부여된 권한 확인
예를 들어,DELETE 권한이 없는 테이블의 행을 삭제하는 것과 같이, 허가되지 않은 작업을 수
행 하려 한다면 Oracle7 Server는 작업을 허용하지 않을 것이다. Oracle7 Server의 오류 메
시지인 "테이블이나 뷰가 없습니다"를 받게 되면 다음 중 한 가지를 했다는 뜻이다:
▶존재하지 않는 테이블이나 뷰의 이름 사용
▶적절한 권한이 없는 테이블이나 뷰에 대한 작업 시도
◈ 부여받은 권한은 무엇인가?

Dictionary뷰를 액세스하면 부여받은 권한을 볼 수 있다.
데이타 사전 테이블 설명
ROLE_SYS_PRIVS role에 부여된 시스템 권한
ROLE_TAB_PRIVS role에 부여된 테이블 권한
USER_ROLE_PRIVS 사용자가 액세스할 수 있는 role
USER_TAB_PRIVS_MADE 사용자가 부여한 객권한
USER_TAB_PRIVS_RECD 사용자에게 부여된 객체 권한
USER_COL_PRIVS_RECD 특정 Column에 대하여 사용자에게
부여된 객체 권한
반응형
저작자표시 비영리

'ORACLE > ADMIN' 카테고리의 다른 글

데이타펌프 (data pump) 특정 테이블을 익스포트 받지 않을 때  (0) 2010.02.26
sqlplus 환경 설정  (0) 2009.12.09
emca 재구성  (0) 2009.11.20
DBLINK시 LOB CLOB 컬럼오류 해결  (0) 2009.11.20
logminer + 불완전 복구  (0) 2009.11.19
Posted by [PineTree]
ORACLE/OWI2009. 12. 2. 17:40

Oracle Wait Event 모니터링

반응형
 
Oracle Wait Event 모니터링
글: 안진철 (jcahn@warevalley.com)
現 ㈜웨어밸리 책임 컨설턴트
前 LG-EDS 기술 연구부문 DB 팀
- 솔루션 컨설팅 경력 6년

연재 순서
[1] Oracle Wait Event 모니터링 (2003년 1월 28일)
[2] Enqueue와 Latch (2003년 2월 12일)
[3] Shared Pool 관련 Wait Event (2003년 2월 26일)
[4] buffer cache 관련 Wait Event (2003년 3월 12일)
[5] redo log 관련 Wait Event (2003년 3월 26일)
[6] Top SQL 튜닝 (2003년 4월 9일)
 
[1] Oracle Wait Event 모니터링
흔히 DBA를 3D업종이라고 부르는 이유 가운데 하나는 몸은 고달픈데 반해 그 성과가 별로 티가 나지 않는다는 사실 때문일 것이다. 실제로, DBA가 수행해야 하는 일상적인 관리 업무들은 몸은 다소 피곤하게 만들지 몰라도 어느 정도 경험이 쌓이면 그리 부담을 주는 일은 아니다. 우리가 한단계 업그레이드된 전문가로서 인정 받는 DBA가 되기 위해서는 장애상황 혹은 유사 장애 상황에서 DB 모니터링 작업을 수행하고 분석할 수 있어야 한다. 시스템이 갑자기 느려지고 업무가 마비되는 상황에 맞닥뜨렸을 때 문제의 원인이 무엇인지를 집어낼 수 있는 능력이 있어야 하며 최소한 오라클의 문제인지 아닌지를 판단할 수는 있어야 몸으로 야간작업이나 때우는 DBA가 아니라 조직에 없어서는 안될 전문가로서의 나의 존재가치를 인정 받을 수 있을 것이다.
이 글에서는 오라클 Wait Event에 대하여 간단히 알아보고 일시적인 성능저하 상황에서 Wait Event를 모니터링하고 그 원인을 찾아가는 방법에 대하여 다루어 보고자 한다. 짧은 지면 위에 다룰 수 있는 내용도 제한되어 있고 글쓴이의 지식 또한 일천하지만 오라클 전문가가 되기 위해 같은 길을 가고 있는 동료로서 가진 지식 몇 가지 공유한다는 취지로 이 글을 쓴다.
오라클의 Wait Event 정보는 V$SYSTEM_EVENT, V$SESSION_EVENT, V$SESSION_WAIT 등이 있는데, 이 가운데 V$SESSION_WAIT는 각 세션이 현재 Waiting 하고 있는 Event나 마지막으로 Wait한 Event 정보를 보관하고 있으며, V$SYSTEM_EVENT와 V$SESSION_EVENT는 시스템이 Startup된 이후 각각 시스템 전체, 혹은 세션별로 발생한 Wait Event 정보를 누적하여 기록하고 있다.
오라클의 Wait Event는 성격에 따라 Network교신이나 IO를 위해 대기하는 일상적인 Wait와 특정 자원에 대해 여러 프로세스가 동시에 액세스하고자 할 때 발생하는 Wait, 별달리 할 일이 없어 대기하고 있는 Idle Wait 등 세가지 유형으로 구분할 수 있는데 그 유형에 따라 해석방법도 달라진다. 일단, Idle Wait는 일반적인 관심의 대상에서 제외되며 IO나 Network 관련 Wait는 작업량이 증가하면 같이 증가하는 Wait이므로 전체 서비스 시간(CPU time)과 비교하여 상대적으로 평가해야 하며 총 Wait time보다는 평균 Wait Time에 관심을 두고 분석을 해야 할 것이다. 시스템 자원에 대한 Wait는 데이터베이스 서버 튜닝시 가장 주된 관심 대상이 되며 이들 Wait에 대해서는 평균 Wait Time뿐만 아니라 총 Wait Time에도 관심을 가지고 분석해야 할 것이다. 유형별로 대표적인 Wait Event를 살펴본다면 아래와 같다.

[주요 Wait Event] 구분 이벤트명 설 명
일상적인 Wait Event 
   db file scattered read - Full Scan시 OS에 I/O를 요청해놓고 대기
   db file sequential read -  Index Scan시 OS에 I/O를 요청해놓고 대기
(IO, Network)
   log file sync - 변경 log buffer를 log file에 반영하는 동안 대기
   DFS lock handle - OPS 환경에서 노드간 분산 Lock 교환에 따른 대기
   global cache cr request - OPS 환경에서 노드간 Buffer Block 교환에 의한 대기
자원 경합에 따른 Wait Event
    enqueue - Type에 따라 세분화 (24개의 enqueue type (9i))
    latch free - Name에 따라 세분화 (239개의 latch가 존재 (9i))
    buffer busy waits - 동일블록에 대한 동시 액세스에 따른 경합
    free buffer waits - free buffer를 할당위해 DBWR의 Write를 대기
    Log buffer space - Log buffer를 할당 받기 위해 LGWR의 write를 대기
    library cache lock - SGA내의 library cache를 참조하기 위한 대기(검색)
    row cache lock - SGA내의 dictionary cache를 참조하기 위한 대기
Idle Event
    SQL*Net message from client -   Client로부터의 작업요청을 대기
    Pmon timer - PMON이 할일 없을 때 대기하는 Event
   
업무시간대에 시스템이 갑자기 느려졌다면서 오라클 서버에 문제가 없는지 문의가 들어오면 글쓴이는 우선 아래의 SQL을 수행시켜본다.
select /*+ ordered / distinct /* 속도를 위해 v$sql을 조인할 경우 중복되는 레코드 제거 */
           s.sid SID, s.username, s.program, p.spid "OS-Pid",w.seconds_in_wait as "W_time(Sec)",
          decode(w.wait_time,0,'Wai-ting', 'Waited') Status, w.ename event,
--              p1text || ':' || decode(event,'latch free',p1raw, to_char(p1)) ||','||
--              p2text || ':' || to_char(p2) ||','|| p3text || ':' || to_char(p3) "Additional Info",
           q.sql_text
from ( select a.*, decode(a.event,'latch free', 'latch free (' ||b.name||')',
                                         'row cache lock', 'row cache lock (' || c.parameter || ')',
                                         'enqueue', 'enqueue ('||chr(bitand(p1, -16777216)/16777215)||
                                                                             chr(bitand(p1,16711680)/65535)||':'||
                                                decode(bitand(p1,65535), 1, 'N', 2, 'SS',3,'SX',4,'S',5,'SSX',6,'X') ||')',
                              a.event ) ename
           from v$session_wait a, v$latchname b, v$rowcache c
         where a.p2 = b.latch#(+) and a.p1 = c.cache#(+) and c.type(+) = 'PARENT'
           and a.event not in ('rdbms ipc message','smon timer','pmon timer','slave wait','pipe get','null event',
                                     'SQL*Net message from client', 'SQL*Net message to client','PX Idle Wait',
                                      'PX Deq: Execution Msg', 'KXFQ: kxfqdeq - normal deqeue',
                                         'ges remote message', 'wakeup time manager', /* idle event 적절히 수정 */
                                         'lock manager wait for remote message', 'single-task message')
        ) w, v$session s, v$process p, v$sql q
where w.sid = s.sid and s.paddr = p.addr
and s.sql_hash_value = q.hash_value(+) and s.sql_address = q.address(+)
order by w.ename;
 
SQL의 구체적인 내용이야 필요한 정보와 개인적 취향에 따라 달라지겠지만, 중요한 것은 일단 V$SESSION_WAIT 뷰로부터 실시간 Wait Event 정보를 얻어낸다는 것이다. 위 SQL을 수행했을 때 나타나는 결과가 없다면 일단 오라클 측면에서 업무성능을 심각하게 마비시키는 Waiting이 발생하고 있지 않다고 봐도 큰 무리가 없을 것이다.
일반적인 상태에서는 주로 'db file sequential read'나 'db file scattered read' 가 나타날 텐데, 이러한 Wait Event는 보통 짧은 시간 동안 지속되며 대상 자원(블록)을 바꿔가며 Wait가 반복되는 형태로 나타날 것이다. 이는 작업 처리량이 많을 때 일상적으로 발생하는 IO관련 Wait Event이므로 해당 세션에서 IO를 제법 많이 유발하고 있다는 정도로 이해하고 넘어가면 될 것이다. 물론, Wait의 지속시간이 길거나 지나치게 빈번히 나타나는 SQL에 대해서는 비효율적인 실행계획을 수립하고 있지 않은지 검토해서 튜닝해 주어야 한다.
성능저하의 원인이 오라클 쪽에 있는 경우에는 특정 자원에 대한 Waiting이 상당히 오랫동안 지속되어 현재까지 Waiting이 진행 중인 세션들(STATUS가 'Wai-ting' (wait_time=0)이며 'W_time(sec)' (seconds_in_wait) 값이 상당히 큰 세션)이 존재할 가능성이 높다. 오라클의 내부적인 작업들은 매우 짧은 기간에 처리되어야 하므로, Idle event(where절에서 not in으로 처리한 부분, 버전에 따라 달라질 수 있다.) 이외의 특정 Wait Event가 눈에 띌 정도로 검출된다는 것은 오라클 내부적으로는 훨씬 더 많은 Waiting이 발생하고 있다고 생각해야 한다. 바로 이런 세션들이 문제의 범인들이며 이제부터 DBA는 이들 Wait Event에 대한 원인을 파악하여 조치하는 작업을 해주어야 한다. 각각의 Wait Event에 따라 원인을 추적하고 조치하는 방법은 달라질 것이다.
다음 호에서는, 자주 경험하는 몇가지 대표적인 Wait Event들에 대하여 SGA 영역별로 구분하여 좀 더 자세히 살펴보고, 그에 앞서 Lock 또는 Latch Event의 이해를 위해 필요한 Enqueue와 Latch의 개념을 간단히 알아보도록 하겠다.

[다음]
[2] Enqueue와 Latch 개념 이해하기
DBMS의 가장 주된 기능 중에 하나는 동일 자원에 대한 동시 액세스를 관리하는 것이며, 이를 위해 오라클이 사용하는 대표적인 제어 구조가 Enqueue와 Latch이다.
Enqueue 와 Latch는 모두 특정 자원에 대한 접근을 serialize하는 것이 목적이라는 점에서는 같은 Lock의 일종이지만 관리방식이나 용도에서 차이가 있다. Enqueue는 이름에서 보듯 Queue를 통해 관리된다. 대상 자원에 대한 Owner, Waiter, Converter Queue를 관리하면서 먼저 요청한 순서대로 Lock을 획득하도록 하는 구조이며, Exclusive 모드 뿐 아니라 다양한 수준의 공유를 허용한다. 대표적인 것이 테이블 데이터를 Update할 때 사용되는 TM, TX enqueue이다.
반면에, Latch는 Enqueue에 비해 훨씬 단순한 구조로서 매우 짧은 시간 내에 획득되고 해제된다. Queue를 통해 관리되지 않으므로 먼저 Request한 프로세스가 먼저 latch를 획득한다는 보장이 없으며, 대부분의 경우 Exclusive모드로만 획득된다. Latch는 주로 SGA의 특정 메모리 구조체에 대한 액세스(library cache latch, cache buffers chains latch) 혹은 메모리 할당 시 (shared pool latch) 사용되거나 오라클의 중요한 코드가 동시에 수행되지 않도록 하기 위한 용도로(redo writing latch) 사용된다. Latch는 Enqueue보다는 하위 level에서 Locking 자체의 부하를 최소화하며 작동하는 제어 메커니즘이라고 할 수 있으며, 실제로 Enqueue 역시 내부적으로는 Latch (enqueues, enqueue hash chains latch )에 의해 운영된다는 점을 생각하면 둘 사이의 차이를 쉽게 이해할 수 있을 것이다.
■ Enqueue
Enqueue 정보는 내부적으로 Enqueue Resource 배열과 Enqueue Lock 배열에 저장된다. 특정 자원에 대한 Lock이 요청되면 대상을 하나의 Resource로 정의하여 할당하고 그 Resource에 대해 관련 Lock 정보를 Owner, Waiter, Converter가운데 하나로서 Link시키는 방식으로 운영되며, 이러한 정보는 V$RESOURCE와 V$LOCK 뷰를 통해 조회해 볼 수 있다. V$RESOURCE와 V$LOCK은 1:M 관계로 하나의 Resource에 대하여 여러 건의 Lock 레코드가 Owner (LMODE>0, REQUEST=0), Waiter (LMODE=0 ,REQUEST>0), Converter (LMODE>0, REQUEST>0) 중 하나로서 대응된다.
Enqueue Wait이 발생하는 것은 다른 세션이 이미 나보다 먼저 해당 자원에 대한 Lock을 잡고 있으므로 인해 내가 원하는 모드로 Lock을 할당 받을 수 없기 때문이다. 자신이 필요로 하는 Lock의 획득에 실패한 세션은 Owner가 작업을 완료하고 자신을 깨워줄 때까지(세마포어를 포스트해줄 때까지) Waiter 혹은 Converter Queue에서 대기하게 되며, 기다려도 소식이 없으면 3초 간격으로 timeout에 의해 일어나 혹시 Deadlock 상황이 아닌지 점검해 본 후 다시 Sleep에 빠져들기를 반복하게 된다. 튜닝관련 자료를 보다 보면 가끔 Enqueue에 대한 Wait이 많은 경우에 Enqueue_resource나 Enqueue_lock 파라미터를 증가시켜 주어야 한다는 가이드를 보게 되는 경우가 있는데 이 파라미터들은 Enqueue resource와 lock 배열의 크기를 늘려줄 뿐 특정 Enqueue 자원에 대한 동시 경합을 해소시키는 것과는 상관이 없다. Enqueue Wait를 해소하기 위한 구체적인 방법은 Enqueue type에 따라 달라지지만 결국은 Enqueue를 불필요하게 요청하는 경우가 없는지를 살펴 Enqueue에 대한 요청을 최소화하고 Enqueue를 점유하는 시간을 최대한 단축시키는 것이다. TX Enqueue에 대한 Wait은 대상 자원에 대한 Lock을 소유하고 있는 세션과 그 세션이 수행 중인 SQL을 찾아 트랜잭션이 장시간 지속되고 있는 이유가 무엇인지 애플리케이션 측면에서 조사해야 하며, SQ enqueue는 Sequence 값 할당 시 발생하는 경합이므로 cache값을 늘려줌으로써 완화시킨다거나 ST Enqueue의 경합이 존재할 경우에는 Locally managed tablespace를 사용하거나 Initial, Next 등의 extent 크기를 적당한 값으로 조정하여 실시간 공간할당을 감소시켜주는 등의 방법들이 Enqueue Wait에 대처하는 대표적인 사례이다. 지난 호에서 소개한 Session Waiter 스크립트는 Enqueue Wait 이벤트에 대해서 Enqueue type과 모드를 함께 표시하여 주도록 하고 있으며, 참고로 Enqueue type별 누적 Wait현황을 확인하고자 하면 아래 SQL을 수행하면 된다.

select q.ksqsttyp type,
           q.ksqstget gets,
           q.ksqstwat waits,
            round(q.ksqstwat/q.ksqstget,3) waitratio
       from sys.x$ksqst q
where q.inst_id = userenv('Instance')
      and q.ksqstget > 0
order by waits desc
/

■ Latch
오라클 운영 시에 하위레벨에서 내부적으로 처리되는 다양한 조작들이 latch의 관할 하에 수행되는데 V$LATCHNAME을 조회해보면 (9i 기준으로) 239 종류나 되는 Latch가 존재하는 것을 확인할 수 있다. 이 가운데 우리가 자주 접하게 되는 latch는 다음과 같은 정도이며 각 Latch의 기능은 관련 SGA별 Wait를 다룰 때 간단하게나마 소개하도록 하겠다.
Shared pool -  library cache latch, shared pool latch, row cache objects
Buffer Cache - cache buffers chains latch, cache buffers lru latch, cache buffer handle
Redo log - redo allocation latch, redo copy latch, redo writing latch
OPS - dlm resource hash list
 

▷ Willing to wait 모드와 No-wait 모드
Latch 획득 방식은 No-wait과 Willing to wait 의 두 가지 모드로 구분할 수 있다. Willing to wait 모드는 Latch의 획득에 실패하면 좀더 시간을 끌면서 해당 Latch를 잡을 때까지 재시도를 해보는 방식을 말한다. 일차적으로는 CPU를 놓지 않고 정해진 횟수만큼 Spinning을 한 후 재시도를 해보다가 그래도 실패하면 CPU를 놓고 Sleep하다가 timeout되어 재시도하는 작업을 반복하면서 Latch의 획득을 노력하게 된다. Latch가 sleep에 들어가게 되면 'latch free' wait event 대기가 시작된다. sleep의 지속시간은 sleep 횟수가 늘어갈수록 점점 길어지게 되는데, 따라서 V$LATCH의 Gets와 Sleeps의 비율과 함께 Sleep1~sleep4 항목에서 몇차 Sleep까지 발생했는지 여부도 각 Latch Wait의 심각성을 판단하는 요소 가운데 하나가 된다.
No-wait 모드는 Willing to wait과는 달리 더 이상 미련을 두지 않고 해당 Latch에 대한 획득을 포기하는 것이다. No-wait 모드가 사용되는 경우는 두 가지가 있는데, 하나는 동일한 기능을 하는 Latch가 여러 개 존재하여 그 중에 하나만 획득하면 충분하여서 특정 Latch에 미련을 가질 필요가 없는 경우이다. 물론, 이 때에도 같은 기능의 모든 Latch에 대한 시도가 실패로 끝날 경우에는 Willing to wait 모드로 요청을 할 것이다. No-wait 모드가 사용되는 다른 한가지 경우는 dead lock을 피하기 위해서 이다. 오라클은 기본적으로 latch dead lock 상황을 피하기 위하여 모든 Latch에 level을 부여하여 정해진 순서를 따라서만 Latch를 획득하도록 하고 있는데, 필요에 의해 이 규칙을 어기고 Latch를 획득하고자 할 경우 일단 No-wait 모드로 시도를 해보는 것이다. 다행히 Latch를 잡으면 좋은 것이고 비록 latch를 잡을 수 없더라도 무한정 기다림으로써 dead lock 상태에 빠지는 일은 피할 수 있는 것이다. No-wait 모드의 Latch작업에서는 당연히 Latch 관련 wait이 발생하지 않으며, redo copy latch를 제외하고는 Willing to wait 모드로 Latch를 획득하는 경우가 훨씬 많다.
▷ Parent latch와 Child latch
Latch 가운데에는 동일 기능을 하는 Child latch들의 set으로 운영되는 Latch도 있으며 하나의 Latch로만 운영되는 Latch도 있다. 전자의 대표적인 예로는 cache buffers chains (버퍼캐쉬 블록 들을 같은 이름의 다수의 Latch가 나누어 담당)가 있으며, 후자의 예로는 shared pool latch (shared pool내에서 메모리 할당을 위해 획득해야 하는 Latch로 시스템에 하나만 존재)가 있다. 이와 같은 Latch 관련 통계 정보는 Parent latch와 Child latch의 개념으로 관리가 되는데 Latch set에서 개별 Child latch에 대한 통계정보는 V$LATCH_CHILDREN 뷰를 통해 조회할 수 있으며, 단일 Latch 혹은 Latch set의 마스터 Latch (parent)에 대한 통계정보는 V$LATCH_PARENT 뷰를 통해 조회할 수 있다.
지금까지 한 회 분량을 할애하여 Enqueue와 Latch에 대해 요약해본 이유는, 많은 Waiting이 SGA내의 공유자원 (Block, Cursor 등)에 대한 경합으로 인해 발생하며 이러한 경합은 다시 해당 자원에 대한 동시 액세스를 제어하는 Enqueue와 Latch에 대한 경합으로 흔히 드러나게 되므로 오라클의 Wait Event를 모니터링하기 위해서는 Enqueue와 Latch의 구조와 작동원리에 대해 이해하는 것이 필수적이기 때문이다.

[3] Shared Pool 관련 Wait
■ Share pool과 성능문제
오라클이 공유 메모리(SGA)를 사용하는 가장 큰 이유는 기본적으로 메모리 사용을 최소화하면서 처리성능은 최대화하기 위한 것이다. 한번 액세스된 블록을 Database buffer cache에 캐쉬 함으로써 비용이 큰 Disk I/O를 최소화하는 것처럼, 한번 처리된 SQL의 실행 정보를 Shared Pool에 공유함으로써 파싱 작업을 위한 CPU, 메모리 자원의 사용을 최소화하고 SQL 수행속도를 증가시킬 수 있다. Shared Pool에는 SQL이나 PL/SQL을 수행하기 위한 각종 정보 - SQL구문 및 실행계획, PL/SQL 소스, 테이블, 뷰 등의 각종 오브젝트와 오브젝트 상호간의 의존관계, 권한관계 등 - 가 저장되어 있다. 지면 관계상 이 글에서 Shared Pool의 관리 메커니즘을 상세히 기술할 수는 없지만 몇 가지 내재적인 특징으로 인해 Shared Pool은 오라클의 메모리 영역 가운데에서도 가장 성능문제의 요소가 많은 곳이면서도 효과적인 튜닝이 수월치 않은 영역이기도 하다.
무엇보다, Shared Pool에서 가장 문제가 되는 것은 메모리의 조각화(Fragmentation)이다. Shared Pool에서 라이브러리 캐쉬 오브젝트를 위해 할당되는 메모리 단위를 chunk라고 부르는데 chunk의 크기는 수 바이트에서 수 K바이트에 이르기까지 필요에 의해 다양하게 할당된다. 새로운 chunk의 할당이 필요하게 되면, 프로세스는 이미 존재하는 chunk로부터 필요한 만큼의 크기만을 떼어내어 사용하므로 시간이 흐를수록 점차 메모리가 조각화 되는 것을 피할 수 없다. ( 이는, Pctincrease가 0가 아닌 테이블스페이스에서 익스텐트의 할당과 해제가 반복됨에 따라 공간의 조각화가 심해지는 것을 떠올리면 이해가 쉬울 것이다. ). 어느 정도 정형화된 패턴의 애플리케이션이 수행되는 환경이 아니라, 공유가 불가능한 다양한 형태의 SQL(대표적으로 Literal SQL)이 빈번히 요청되는 환경이라면 Shared Pool 메모리 조각화에 따른 문제는 더욱 심각해진다.
또한, Shared Pool은 일반적인 메모리 캐쉬와는 달리 메모리에 저장되었던 정보를 잠시 기록해둘 대응되는 디스크 공간이 없으므로 한번 flush된 라이브러리 캐쉬 오브젝트를 reload하기 위해서는 해당 정보를 재생성 해야만 한다. 이 과정에서 관련 오브젝트 정보의 검색 및 참조, locking, 메모리 할당 등의 작업을 위해 많은 비용이 들기 때문에 결국 Shared Pool 관련 튜닝의 최대 과제는 SQL 공유를 최대화하여 새로운 파싱 요청과 메모리 요청을 최소화하는 것이라고 할 수 있다. 헌데, 이는 애플리케이션의 설계와 연계되는 영역으로서 이미 개발이 완료된 운영서버에서는 변경작업이 여의치 않은 것이 현실이다. 앞서, Shared Pool이 DBA로서 튜닝이 수월치 않은 영역이라고 표현한 이유 가운데 하나가 여기에 있다.
■ Shared Pool 관련 오해 바로잡기
Shared Pool과 관련하여 판단이 쉽지 않은 부분 가운데 하나가 과연 shared_pool_size를 얼마나 할당할 것인가 하는 것이다. 오라클은 Shared Pool 메모리를 최대한 효율적으로 활용하기 위하여 다양한 기법을 동원하고 있는데, 이러한 메모리 관리 메커니즘에 대해 정확히 알지 못하여 Shared Pool 크기를 지나치게 크게 할당함으로써 오히려 문제를 악화시키는 경우도 드물지 않다. 이러한 오해를 바로잡기 위해 Shared Pool의 메모리 할당과정을 간단하게나마 살펴보도록 하겠다.
새로운 메모리 Chunk가 할당되는 과정을 살펴보면, 우선 프로세스는 Free List를 검색하여 자신이 필요로 하는 크기의 Free Chunk를 찾고, 그러한 Free Chunk가 없으면 원하는 크기보다 한단계 큰 Free Chunk를 찾아서 필요한 크기만큼 분할하여 사용하게 된다. 만약 Free List에서 충분한 크기의 Free Chunk를 찾을 수 없다면, 이미 사용되었으나 현재는 사용되고 있지 않는(unpinned) Chunk들의 LRU List를 검색하여 오래된 것부터 8개씩 flush시켜 Free Chunk로 만든 후 자신이 필요한 크기를 할당하여 사용하게 된다. 만약 이 과정에서 현재 사용중인(pinned) Chunk가 대부분이거나, 너무 메모리 조각화가 많이 일어나서 기존 Chunk를 Flush시킨 후 인접한 Free Chunk들을 병합해보아도 원하는 크기의 Free Chunk를 얻어낼 수 없다면 오라클은 ORA-4031 에러를 발생시키는데, 그 이전에 한가지 최후의 비밀무기가 더 숨어 있다. 바로 Spare Free 메모리라는 것인데 오라클은 인스턴스 기동 후 처음에는 전체 Shared Pool의 50% 가량은 Free List에 올려놓지 않고 아예 숨겨두었다가 앞서와 같이 도저히 피할 수 없는 순간이 되면 조금씩 해제 시켜 사용하도록 한다. 그야말로 메모리의 조각화를 최소화하기 위한 오라클의 눈물 나는 노력이라고 할 수 있을 것이다. 물론 이 영역까지 다 소모한 후에 flush를 통해서도 필요한 Chunk를 확보할 수 없는 상황이 되면 결국 ORA-4031 에러가 발생할 것이다.
많은 이들이 Shared Pool의 남아있는 Free memory의 크기가 작으면 shared_pool_size를 증가시켜주어야 한다고 믿고 있는데 이는 잘못된 것이다. Shared Pool은 정보의 재사용을 위해 운영하는 것이므로 SQL 실행이 끝났다고 해서 해당 Chunk를 Free List로 반납하지 않는다. 즉, Free Memory가 남아있는 한 계속 소모 시키는 방식으로 사용되므로 오랜 시간동안 운영되어온 시스템에서 Shared Pool의 Free Memory가 매우 적게 남아 있는 것은 그 자체로는 문제가 되지 않으며, 오히려 피크타임이 지난 후에도 많은 양의 Free Memory가 남아있다면 이는 Spare Free 메모리도 다 소모하지 않은 상태로서 불필요하게 많은 메모리가 할당되어 낭비되고 있음을 의미한다. 더구나, Shared Pool 크기가 지나치게 크면 Free Memory를 다 사용할 때까지의 기간이 연장되는 효과는 얻을 수 있겠지만, 시간이 지날수록 Memory의 조각화가 더욱 심해지고 Free List의 길이가 길어져 Free Chunk의 검색과 할당에 걸리는 시간이 지연되므로 오히려 성능이 악화되는 결과를 초래할 것이다.
또한, 메모리 조각화에 따른 영향을 줄이기 위해 오라클은 5000 bytes가 넘는 큰 사이즈의 Chunk만을 위해 전체 Shared Pool의 5% 정도를 따로 관리하는 방법을 사용하고 있는데, 경험적으로 보면 이 공간은 거의 사용되지 않고 버려지고 있는 경우가 많다. 이는 V$SHARED_POOL_RESERVED 뷰의 USED_SPACE 값을 확인해 보면 알 수 있으며, 5000 bytes 이상의 large chunk가 거의 요구되지 않는 환경에서는 오히려 이 크기를 줄여주는 것이 나을 것이다.

■ Shared Pool 관련 wait
Shared Pool과 관련하여 흔히 발생하는 Wait은 라이브러리 캐쉬 오브젝트에 대한 동시 액세스와 메모리 할당에 따른 관련 Lock 또는 Latch에 대한 경합이 대부분이며, 구체적인 이름은 다음과 같다. (Latch free 이벤트시 괄호 안의 관련 latch 이름은 v$session_wait의 p2값과 v$latchname의 latch#를 조인하여 얻어낼 수 있다. 1회 SQL 참조)
Latch                                   Lock
latch free ( library cache )           library cache lock, library cache pin
latch free ( library cache load lock)  library cache load lock 
latch free ( row cache objects )       row cache lock
latch free ( shared pool )             -      
Library cache lock, library cache pin, library load lock은 각각 특정 라이브러리 캐쉬 오브젝트에 대한 검색이나 변경 및 실행 또는 로드 시에 대상 오브젝트에 대해 할당되며, 이러한 Locking 작업은 library cache latch와 library cache load lock latch의 관할 하에 처리된다. Shared pool latch는 Free List나 LRU List를 검색하거나 메모리를 할당하는 작업에 사용되며, row cache lock과 row cache objects latch는 Data dictionary cache 오브젝트에 대한 동시 액세스를 제어하는데 사용된다.
Latch의 개수는 시스템 전체적으로 하나 또는 제한된 개수가 존재하는 것이고 Lock은 대상 오브젝트 각각 대해 할당되는 것이므로, 엄밀하게 말해서 Lock에 대한 경합은 직접적으로는 특정 라이브러리 캐쉬 오브젝트에 대한 동시 액세스로 인해 유발되는 것인 반면에, Latch에 대한 경합은 시스템 전체적으로 관련 오퍼레이션(즉, SQL 파싱) 자체가 지나치게 많이 발생하거나, 짧은 시간 내에 처리되지 못함으로 인해 유발되는 것이라고 구분해볼 수 있다. 그러나, 결국 이 모든 경합은 근본적으로 Shared Pool의 조각화(Fragmentation)에 따른 문제가 주된 원인이며 다시 이러한 조각화는 요청되는 SQL들이 공유되지 못하고 지속적으로 새롭게 파싱되고 메모리가 할당됨으로 인해 발생하는 것이다. 따라서, 이러한 문제를 해결하는 가장 효과적인 방법은 Literal SQL을 바인드 변수를 사용하도록 수정하거나, SQL작성 표준을 마련하고, HOLD_CURSOR/ RELEASE_CURSOR, SESSION_CACHED_CURSORS, CURSOR_SPACE_FOR_TIME, CURSOR_SHARING 등의 파라미터를 활용하는 등의 방법을 통해 SQL의 공유도를 높여주는 것이며, 또한 자주 사용되는 PL/SQL에 대해서는 DBMS_SHARED_POOL 패키지를 사용하여 메모리에서 Flush되지 않도록 보존하는 등의 조치를 취해주면 도움이 될 것이다. SQL의 수정이 어려운 환경이거나 시스템에 요청되는 SQL의 절대량이 확보된 메모리 공간에 비해 많은 상황이라면 주기적으로 피크타임을 피해 Shared Pool을 직접 Flush(alter system flush shared_pool 명령을 사용한다.) 시켜주는 것도 권장할 만한 관리 방법이다. 많은 이들이 우려하는 바와는 달리 Shared Pool을 직접 flush 시키는 것이 심각한 성능상 문제를 야기하지는 않으며 특히 중요한 패키지나 SQL cursor, Sequence 등이 keep되어 있는 경우라면 더욱 그러하다.
가끔 버그를 포함한 특수한 상황에서 특정 라이브러리 캐쉬 오브젝트에 대한 lock이 장시간 해제되지 못하고 있는 경우도 있는데 이때는 X$KGLLK 뷰를 조회하면 library cache lock에 대한 holder/waiter를 확인하여 조치할 수 있다. 또한, Row cache lock에 대한 경합은 Locally managed tablespace를 도입하거나, DML이 빈번한 테이블에 대한 인덱스의 개수를 줄여주는 등의 조치를 통해 완화될 수 있을 것이다.
부연하자면, Shared Pool과 관련된 Wait는 특정 오브젝트 자원에 대한 경합에 의해 발생하기 보다는 애플리케이션의 설계, 보다 단순화시켜 표현하면 Literal SQL에 의한 메모리 조각화에 의해 발생하는 경우가 많다. 따라서, Shared Pool관련 Wait가 많이 발생하여 오라클이 그로 인한 성능상의 문제를 드러낼 때 눈에 띄는 하나의 주범을 찾아내려는 노력은 별 효과를 거두지 못하는 경우가 많으며, 그러한 시점에 DBA가 즉각적으로 취할 수 있는 조치로는 직접 Shared Pool을 Flush 시키는 정도가 있을 것이다. 결국, 평소에 꾸준한 모니터링을 통해 Shared Pool의 적절한 크기와 관련 파라미터 값을 찾아가는 것, 그리고 무엇보다 애플리케이션 측면에서 튜닝 및 수정 작업을 진행함으로써 성능문제를 사전에 예방하는 것이 최선이다.
[3] Buffer Cache 관련 Wait
■ Buffer Cache 구조
Buffer Cache의 기본적인 기능은 여러 프로세스에 의해 공통으로 자주 액세스 되는 데이터베이스 블록을 메모리에 캐쉬하여 물리적인 디스크 IO를 최소화함으로써 더 빠른 액세스 속도를 제공하기 위한 것이다. 복잡한 설명은 생략하고, Buffer Cache 의 기본구조를 이해하기 위한 몇 가지 핵심 용어들을 간단히 정리해 보도록 하겠다.
▷ Buffer header
모든 버퍼 블록들은 각자의 buffer header를 통해 액세스되고 관리된다. 즉, 메모리에 캐쉬된 특정 데이터 블록에 대한 액세스는 먼저 해쉬 알고리즘을 통해 cache chain 상의 buffer header를 찾고 해당 buffer header에 기록된 데이터 블록의 메모리상 주소를 찾아가 원하는 정보를 읽는 방식으로 이루어진다. Buffer header에 기록되는 주요정보는 다음과 같으며 Buffer header의 내용은 V$bh 뷰를 통하여 조회해볼 수 있다.
     - 메모리상에서의 해당 버퍼블록의 주소
     - 해당 버퍼 블록(실제로는 버퍼헤더)가 포함되어 있는 hash chain
     - LRU, LRUW, CKPTQ와 같은 리스트상에서의 해당 버퍼블록의 위치
     - 해당 버퍼블록에 대한 User, Waiter와 상태를 나타내는 각종 Flag
▷ Hash Buckets/ Hash Chains
Buffer Cache의 모든 블록은 해쉬 알고리즘을 통해 관리된다. 곧, 데이터 블록의 DBA, Class 값으로 Hash Function을 적용하여 해당 블록이 속하는 hash buckets을 할당하며, 동일한 hash buckets에 할당되는 데이터 블록의 버퍼헤더들은 linked list형태로 hash chain을 이루게 된다. Hash buckets/hash chains는 특정 데이터 블록을 찾아가기 위한 수단을 제공한다. 각각의 hash buckets에는 자신에 속한 hash chain을 보호하기 위한 latch(cache buffers chains)가 할당된다.
▷ LRU
LRU는 두개의 리스트, 즉 LRUW와 LRU 리스트의 쌍으로 구성된다. LRUW(LRU Write list)는 dirty list와 같은 말이며, 수정되어 디스크에 반영되어야 할 블록들의 리스트이다. LRU(Least recently used list)는 LRUW에 올라가지 않은 나머지 버퍼 블록들이 등록되어 있다. Buffer cache 상의 버퍼블록은 반드시 LRU나 LRUW 둘 중의 하나에 등록되며, 두 리스트에 동시에 포함되는 경우는 없다. LRU는 Free Buffer를 찾기 위한 수단을 제공한다. 경합을 피하기 위해 버퍼캐쉬 블록들을 여러 개의 LRU쌍으로 나누어 관리할 수 있으며, 각 LRU리스트를 보호하기 위해 Latch(Cache buffers lru chain)가 하나씩 할당된다.
■ Buffer Cache 운영규칙
▷ 메모리상의 특정 버퍼블록을 찾아가거나, 특정 블록이 메모리에 캐쉬 되어 있는지를 확인하기 위해서 오라클은 hash bucket/hash chain 구조를 사용한다.
▷새로운 데이터블록을 디스크로부터 메모리로 읽어 들이기 위한 free buffer를 확보하기 위해 오라클은 LRU 리스트를 사용한다.
▷ 버퍼블록은 LRU나 LRUW 둘 가운데 하나에 등록된다.
▷ 하나의 블록에 대해 시간대가 다른 여러 개의 복사본이 존재할 수 있으며, 그 가운데 오직 CURRENT 버퍼만이 변경될 수 있다.
▷하나의 버퍼블록은 한번에 오직 하나의 프로세스에 의해서만 변경될 수 있다.
■ Buffer Cache 관련 Waits
버퍼캐쉬와 관련되어 흔히 발생하는 대표적인 Wait 이벤트는 다음과 같다.
▷ buffer busy waits
여러 세션이 동시에 같은 블록을 읽으려고 하거나 여러 세션이 같은 블록에 대한 변경작업이 완료되기를 기다리고 있는 경우에 발생하며, 특정 블록에 대한 경합을 해소하기 위한 조치는 블록의 유형에 따라 달라진다. Data block에 대한 경합이 많은 경우는 Pct free나 Pct used 값을 사용하여 블록 당 로우수를 줄이거나, 특정 블록에 로우 입력이 몰리는 구조의 인덱스(right-hand-index)일 경우는 reverse key index의 사용을 검토하는 등의 방법이 있으며, segment header의 경합이 많은 경우는 freelist 수를 늘리거나 Extent의 크기를 증가시키는 등의 방법이 있고, undo header나 undo block에 대한 경합은 롤백세그먼트의 개수나 크기를 증가시키는 것이 전형적인 조치 방법이다. v$waitstat과 x$kcbfwait을 이용하며 Class 또는 file별로 wait 발생상황을 판단할 수 있다.
 
▷ free buffer waits/write complete waits
DBWR가 dirty buffer를 write하는 동안 서버 프로세스가 대기하고 있는 경우 발생한다. 곧, 너무나 많은 dirty buffer가 생겨나거나 DBWR의 쓰기 속도가 충분히 튜닝 되지 못한 경우에 발생한다. 점검 포인트는 물리적 디스크의 속성(stripe size, layour, cache size) 최적화, Raw device의 활용, Async IO나 multi-DBWR(db_writer_processes) 활용여부 등이다.
위와 같은 버퍼 블록에 대한 경합 역시 비효율적인 실행계획을 통해 수행되는 애플리케이션에 의하여 불필요하게 많은 블록이 메모리로 올라오는 것이 원인일 경우가 많으므로 경합이 빈번한 블록이 속하는 테이블/인덱스 명을 찾아낼 수 있다면 관련 SQL을 찾아내어 보다 효과적인 튜닝작업이 이루어질 수 있을 것이다. v$session_wait의 p1,p2 컬럼에 각각 file#, block#값을 표시하여 주므로 이 값을 이용하여 아래의 SQL문으로 현재 어떤 오브젝트에 대하여 해당 wait가 발생하고 있는지를 추적할 수 있다. ( 1회에 소개한 SQL문에서는 Additional Info 값을 참조. )
     select segment_name, segment_type
     from dba_extents
     where file_id = :file#
     and :block# between block_id and block_id + blocks -1
 

▷ cache buffers chains latch
SGA내에 캐쉬된 데이터블록을 검색할 때 사용된다. 버퍼캐쉬는 블록들의 chain을 이루고 있으므로 각각의 chain은 이 Latch의 child들에 의해 보호된다. 이 Latch에 대한 경합은 특정 블록에 대한 대량의 동시 액세스가 발생할 때 유발된다. 애플리케이션을 검토해 보아야 한다.
Ø cache buffers lru chain latch
버퍼캐쉬의 버퍼를 LRU 정책에 따라 이동시켜야 할 필요가 있는 경우 프로세스는 이 Latch 획득하게 된다. 이 Latch에 대한 경합은 Multiple buffer pool을 사용하거나 DB_BLOCK_LRU_LATCHES 를 증가시켜 LRU Latch의 개수를 늘려서 해소할 수 있다. SQL문을 튜닝하면 해당 프로세스에 의해 액세스 될 블록의 수가 줄어들 것이므로 당연히 효과를 거둘 수 있다.
위와 같이 버퍼캐쉬를 관리하는 Latch에 대한 경합은 경합이 집중되는 특정 Child Latch에 의해 관리되는 버퍼블록을 찾아 해당 블록이 속한 세그먼트 정보를 알아낸다면 보다 효과적인 조치가 가능할 것인데, latch free wait일 경우 v$session_wait의 p1raw 값이 해당 Latch address를 의미한다. 이 값을 x$bh의 hladdr 값과 조인하면 관련 오브젝트 이름을 추적해볼 수 있다.
     select file#, dbarfil, dbablk, obj, o.name
     from x$bh bh, obj$ o
     where bh.hladdr = :latch_address
     and bh.obj = o.obj#;

5] Redo buffer 관련 Wait
■ Redo buffer 구조
오라클 리두 구조의 핵심은 모든 트랜잭션 정보를 OS 파일에 기록해 둠으로써 시스템 장애가 발생해도 트랜잭션 단위의 일관성을 잃지 않고 데이터베이스를 복구할 수 있도록 하겠다는 것이다. 리두버퍼(redo buffer)는 이처럼 데이터베이스에 가해진 모든 변경내역을 파일에 기록 하기 위해 잠시 사용되는 메모리 영역이며 리두버퍼에 기록된 리두 정보는 다시 리두로그 파일에 기록되어짐으로써 향후 시스템 복구 작업이 필요할 때에 사용하게 된다. 오라클의 리두 구조를 이해하기 위한 핵심적인 개념을 간단히 정리해보면 다음과 같다.
데이터베이스에 대한 변경내역은 블록단위로 저장된다. 물론 변경되는 모든 블록의 복사본을 통째로 저장하는 것은 아니고 블록별로 어떠한 오퍼레이션을 수행하는가, 그리고 그러한 블록별 오퍼레이션을 어떠한 순서로 수행하는가를 기록한다. 이러한 블록별 단위액션을 change vector라고 부르며 change vector가 순차적으로 모여 하나의 의미 있는 redo record가 된다. 리두로그는 시스템내의 모든 프로세스들에 의해 생성되는 redo record를 SCN 순서대로 저장해놓은 것이다. 이때 리두로그에 기록되는 내용에는 테이블이나 인덱스 등의 데이터 블록 뿐만 아니라 UNDO 블록 또는 UNDO 세그먼트 헤더블록에 대한 변경내용을 포함하는 모든 버퍼캐쉬 블록에 대한 변경내역이 대상이 된다.
리두 정보는 항상 실제 변경작업보다 먼저 보관되어야 어떤 상황에서도 복구가 가능해진다. 따라서 트랜잭션을 수행하는(데이터베이스 블록에 변경을 가하는) 프로세스는 우선 자신의 메모리 영역 내에서 수행하고자 하는 작업에 대한 리두 레코드를 만들며, 이를 먼저 로그버퍼에 기록하고 난 후에 실제 버퍼블록에도 리두 레코드에 담긴 내용을 따라 적용하게 된다. 또한 같은 이유로 오라클은 변경된 버퍼캐쉬 블록을 디스크에 기록하기 전에 먼저 관련된 로그버퍼를 로그파일에 기록하는 작업을 처리하게 된다. 따라서, 리두 버퍼 또는 리두 파일 (아카이브 파일을 포함해서)에 대한 쓰기 작업에 병목이 생기면 시스템에 대한 모든 작업 수행이 대기 상태로 빠지게 될 것이다.
트랜잭션 커밋을 요청한 프로세스는 우선 해당 트랜잭션에 대한 로그버퍼가 리두로그 파일에 기록되는 작업이 완료된 후에야 커밋 완료 메세지를 받을 수 있다. 그렇게 함으로써 버퍼캐쉬 변경내역을 모두 디스크에 반영하지 않고도 시스템의 비정상 종료시 리두파일에 저장된 리두 레코드로부터 커밋 트랜잭션을 보존할 수 있게 된다.
■ 리두 버퍼관련 Wait 이벤트
일반적으로는 로그버퍼 관련해서 심각한 Waiting이 발생하는 경우는 드물지만, 가끔 볼 수 있는 리두 관련 Wait 이벤트로는 다음과 같은 것들이 있다.
▷ Log file parallel write
LGWR가 OS에 리두 버퍼를 로그파일에 기록하도록 요청해 둔 상태에서 대기하고 있는 이벤트이다. 이 경우에는 DML 작업시 nologging 옵션 등을 사용하여 시스템에서 발생하는 리두 레코드의 절대량을 줄이거나 하드웨어적으로 DISK IO를 개선시켜주는 것이 방안이다.
▷Log buffer space
프로세스가 로그버퍼를 할당하기 위해 대기하는 이벤트인데 LGWR가 로그버퍼를 비우는 것보다 더 빠른 속도로 프로세스들이 리두 레코드를 생성하고 있다는 것을 의미한다. 로그버퍼의 크기를 늘려주거나, DISK IO의 속도를 개선시켜 주어야 할 것이다. 로그버퍼는 로그파일에 대응되는 블록이 맵핑이 된 후에 사용될 수 있으므로 로그 스위치 발생시에도 log buffer space 이벤트에 대한 대기가 발생할 수 있다. 로그 스위치가 너무 잦다면 리두 로그 파일의 크기를 증가시켜주는 것이 좋다.
▷ Log file sync
프로세스가 커밋이나 롤백을 수행할 경우 우선 LGWR에게 해당 트랜잭션까지의 로그버퍼를 Write하도록 요청하게 되는데 이때 사용자 프로세스는 LGWR가 쓰기 작업을 완료할 때까지 log file sync 이벤트를 대기하게 된다. 버전 8i 이전에서는 DBWR가 쓰기 작업을 수행하다가 아직 관련 로그버퍼가 파일에 쓰여지지 않을 경우에도 LGWR에 쓰기를 요청하고 log file sync 이벤트에 대기하였으나 8i 이상에서는 log file sync에 대기하는 대신 deferred write queue에 등록한다. 따라서 버전 8i 이상에서 log file sync 이벤트는 사용자 프로세스에 의해 요청되는 커밋, 롤백 처리 시에 발생하며 결국, 시스템 전체적으로 커밋, 롤백이 지나치게 자주 수행되거나 상대적으로 LGWR의 쓰기 속도가 느린 것이 원인일 것이다. 또는, 로그 버퍼가 너무 커서 LGWR가 백그라운드로 flush 시켜주기 전( 보통 3초 간격 및 1/3 이상의 로그버퍼가 찬 경우)에 커밋에 의한 쓰기 요청이 이루어지므로 커밋 시점에 써야 할 양이 많아 대기시간이 길어지는 경우도 있는데 이 경우엔 리두 버퍼의 크기를 오히려 줄여주어야 할 것이다. 또는, LGWR wait for redo copy 이벤트가 많이 나타난다면 redo copy latch가 너무 많아 LGWR이 사용자 프로세스가 버퍼 쓰기 작업을 마칠 때까지 기다리는 일이 잦은 경우를 뜻하며 이 경우엔 _LOG_SIMULTANEOUS_COPIES 파라미터를 사용하여 copy latch의 수를 줄여주는 조치가 필요할 것이다.
 
시스템에 따라서 언급한 외의 다양한 이벤트 대기와 원인이 존재할 수 있고, 더구나 버전에 따라 redo copy latch와 redo allocation latch를 포함한 리두 운영 방식상 상이한 부분이 많이 존재하여 그에 따른 추가적인 튜닝요소가 있으나 이 글에서는 지면 관계상 8i를 기준으로 간략히 정리해 보았다.
[6] Top SQL 튜닝하기 (맺음)
■ Top SQL 튜닝의 필요성
지난 회까지 실시간 Wait Event 모니터링과 이벤트별 원인분석에 대해서 간단히 살펴보았다. 일시적 성능장애 시 재빨리 원인을 찾아내는 것도 중요하지만 보다 바람직한 것은 이러한 성능문제를 사전에 최대한 예방하는 것임은 두말할 필요도 없다. 오라클 성능문제를 다루는데 있어 강조하고 싶은 한가지는 시스템 자원의 배분을 변경하거나 증가를 고려하기 전에, 불필요한 작업을 최소화함으로써 자원요구 횟수와 자원점유 시간을 줄여주는 노력이 선행되어야 한다는 점이다. Wait Event에 대한 모니터링과 분석이 DBMS의 자원에 대한 경합과 관련된 성능문제를 파악하는데 유용한 방법임에 틀림없지만, 이 같은 정보는 데이터베이스 혹은 그 하위 레벨의 구조적 비효율성을 드러내어줄 뿐 애플리케이션 레벨의 문제를 직접적으로 알려주지는 않는다. 간단히 말하자면, DBMS 튜닝을 위해 정성을 쏟기 이전에 애플리케이션 튜닝에 더 많은 투자를 하라는 것이다. 이런 관점에서 DBA가 비교적 손쉽게 수행할 수 있는 것이 오라클 메모리로부터 악성 SQL을 추출하여 튜닝하는 Top SQL 튜닝이다.
■ Top SQL 추출기준
사용자로부터 요청되어 오라클 내에서 처리되는 모든 SQL은 오라클의 공유 메모리 영역 가운데 shared pool내에 캐쉬 되어 지며 이렇게 캐쉬 되어 있는 SQL과 관련 통계정보는 V$SQL 또는 V$SQLAREA 뷰를 통해서 조회할 수 있다. 이때, Top SQL을 추출하는데 중요하게 사용되는 항목은 buffer_gets, disk_reads, executions, rows_processed 등이며 일반적으로 아래와 같은 기준으로 Top SQL을 추출한다.
▷ Buffer gets 수치가 높은 SQL
Buffer gets은 해당 SQL이 처리되는 과정에서 액세스한 메모리 블록의 수(Logical IO)를 의미한다. 물론 이 값이 높다고 해서 무조건 악성 SQL임을 의미하는 것은 아니다. 즉, 이러한 SQL들 중에는 실제로 요구되는 작업량이 많아서 액세스 블록수가 많은 SQL도 있을 것이며 불필요한 처리를 수행하느라 액세스 블록수가 많은 SQL도 있을 것이다. 어느 경우이든 이 SQL들이 현재 오라클 서버에 부하를 많이 유발하고 있는 SQL들이라는 것만은 분명하며 사소한 비효율적 요소에 의해서도 서버에 큰 영향을 미칠 잠재적인 가능성이 있는 SQL들이므로 일차적으로 점검해 볼 필요가 있다.
악성 SQL여부를 판단하기 위한 Buffer gets의 수치에 절대적인 기준은 없으며 시스템의 데이터 규모와 트랜잭션량에 따라 다르다. buffer gets값을 기준으로 역순으로 정렬한 후 패턴을 살펴 적절한 추출기준을 선택하는 것이 좋을 것이다. 만일, 상위 몇 개의 SQL들과 나머지 SQL들 간의 buffer gets의 편차가 매우 크게 나타난다면 상위 몇 개의 SQL에 대해서만 튜닝을 수행해 주어도 큰 효과를 볼 수 있을 것이다. 일반적으로 시스템에서 수행되는 SQL 가운데 심각한 부하를 야기하는 SQL은 소수에 불과한 경우가 많으며 뒤에 기술될 다른 조건들과 조합하여 최대 Top 50건 정도를 추출하여 효율성을 검증하고 튜닝을 통해 개선하는 작업을 수행하여 주면 충분하다.
▷Buffer gets/Execution 수치가 높은 SQL
SQL의 단위 수행당 buffer gets 수치를 의미한다. 단위 수행당 buffer gets 값이 높다는 것은 해당 SQL의 처리가 비효율적일 가능성이 높음을 의미한다. 액세스 블록수가 비정상적으로 많다는 것은 rows_processed 값과 비교하여 상대적으로 평가되어야 할 부분이다. 실제로 반환하는 로우수가 매우 많은 배치성 SQL이거나 혹은 반환되는 로우수가 1건이라도 Group Function이 사용된 Summary성 SQL이라면 처리과정에서 많은 수의 블록을 액세스하는 것은 불가피하며 이 자체가 문제가 될 수는 없기 때문이다.
▷Execution 수치가 높은 SQL
Executions는 해당 SQL이 수행된 횟수를 의미한다. 수행횟수가 잦은 SQL은 buffer gets가 높을 경우가 많다. 일반적으로 십만 ~ 백만 회 이상 빈번하게 수행되는 SQL이라면 buffer gets/executions 값이 2자리 수 이내의 값을 나타내어야 정상이며 단위 수행당 속도는 0.1초 이내로 매우 빨라야 한다. 따라서, 이러한 SQL의 경우 SQL단위로 보면 튜닝의 효과를 체감하기도 어렵고 필요성을 느끼지 못할 수도 있으나 튜닝을 통해 아주 적은 차이라도 개선을 가져올 수 있다면 시스템 전체적인 관점에서는 매우 큰 효과를 가져다 줄 수 있다는 점이 중요하다. 하루에 백만번 수행되는 SQL에 대하여 0.01초를 개선한다면 시스템 시간으로 하루에 일만초를 절약한 셈이 될 것이다. 이러한 SQL에 대해서는 현재 빠르게 수행되고 있다고 해도 더 빠르게 처리할 여지가 없는지 점검하고 가능한 모든 방안을 동원하여 개선시키도록 노력해야 한다.
▷disk_reads 수치가 높은 SQL
disk_reads는 SQL이 처리되는 과정에서 물리적인 IO를 통해 액세스한 블록의 수를 의미한다. 물리적 IO의 발생여부는 원하는 블록이 메모리에 캐쉬되어 있는지 여부에 따라 달라지므로 수행되는 횟수와 수행되는 시간대의 데이터베이스 캐쉬 상황에 따라 유동적이라고 할 수 있다. 그러나, buffer gets의 값과 비교하여 disk_reads의 비율이 높은 SQL은 Full Scan을 수행하는 SQL일 가능성이 큰데 그 이유는 Full Scan을 통해 액세스되는 블록들은 기본적으로 DB buffer Cache의 LRU 알고리즘에 의해 관리되지 않으므로 작업 후에 곧바로 메모리로부터 밀려나 버릴 가능성이 높기 때문이다. 반면에 인덱스를 통하여 액세스하는 경우, 일상적으로 액세스되는 테이블에 대해서는 인덱스의 root block과 branch block은 항상 메모리에 캐쉬 되어 있을 확률이 높으므로 물리적 IO를 유발하는 비율이 낮을 수 밖에 없다.
■ Top SQL 추출기준
글을 맺기 전에 마지막으로 언급하고 싶은 것 하나는, 문제가 발생했을 때 문제의 원인이 bug로 인한 것일 가능성을 항상 염두에 두어야 헛된 고생을 덜한다는 것이다. 오라클도 사람이 만든 프로그램이므로 버그가 없을 수 없으나 다행히 오라클의 버그 및 패치 관리는 매우 훌륭한 편이다. 오라클 메타링크를 활용하여 유사한 문제가 보고된 적은 없는지 관련 버그에 대한 정보는 없는지 살펴보아야 하며, 평소에 정기적으로 패치 및 버전 관리를 해주는 것이 바람직하다.
오라클 시스템을 운영하다 보면 현실에서는 다양한 문제가 복잡하게 얽혀 나타나므로 명백한 원인을 파악하기가 쉽지 않을 때가 많지만, 운영 시 자신의 시스템에서 자주 발생하는 Wait 패턴 또한 분명히 존재하므로 굵은 가지들부터 하나씩 이해하고 해결해 나가다 보면 오라클이 우리가 보인 애정에 보답해줄 날이 올 것이라 믿는다. 지면 관계상 OPS(RAC) 관련 Wait을 비롯한 기타 Wait 이벤트에 대해 다루지 못한 점, 그리고 각 Wait 이벤트별로 좀더 친절한 설명과 사례를 제시하지 못한 점이 아쉬움으로 남지만, 누구든 아주 작은 것 하나라도 이 글을 통해 새로이 얻을 수 있었다면 그 이상 바랄 것은 없다.

[출처] Oracle Wait Event 모니터링 |작성자 타락천사

반응형
저작자표시 비영리

'ORACLE > OWI' 카테고리의 다른 글

shared pool의 heap구조 dump자료  (0) 2013.06.23
OWI를 활용한 shared pool 진단 및 튜닝  (0) 2009.06.30
Latch의 발생과 경합의 원인  (0) 2009.03.09
Oracle에서 Latch 가 성능 저하의 원인이 아닙니다  (0) 2009.03.01
Enqueue 와 Latch  (0) 2009.03.01
Posted by [PineTree]
ORACLE/ORACLE TOOL2009. 11. 23. 10:19

Orange-국내 기술로 개발된 오라클 개발 지원 및 튜닝 도구

반응형

시 스템 개발·운영시에 다양한 소프트웨어나 솔루션을 도입해 구축하게 된다. 이때 어떠한 솔루션을 도입하느냐에 따라 시스템의 성패가 좌우된다. 또한 다양한 툴의 구매도 그 목적에 따라 해당 업무의 효율성을 높일 수도 있고 효과를 주지 못하는 경우도 있다. 이 글에서는 데이터베이스를 이용하는 개발 프로젝트에서 필수 불가결한 툴인 데이터베이스 클라이언트 툴 가운데 웨어밸리의 Orange for Oracle(이하 오렌지) 제품의 기술적인 기능에 대해 알아보고자 한다. 오렌지 도입시 판단의 기준에 도움이 되길 바란다.


1990년대부터 데이터베이스 시장이 급성장하면서 오라클 개발지원 도구 시장에 먼저 선보인 외산 제품들은 국내 고객에게 쉽게 파고들었다. 하지만 국내 제품이 아닌 탓에 사용자는 사용상의 불편함이나 비용 측면의 불리한 점이 있더라도 눈감고 외산 제품을 사용할 수밖에 없었다. 웨어밸리 오렌지는 국내 기술로 개발된 오라클 개발 지원 및 튜닝 툴로, 사용자의 요구사항에 맞춰 기능을 추가할 수 있다는 점이 가장 돋보이는 매력이다.

뒷방 신세로 전락되는 툴

많은 기업에서 오라클 데이터베이스 지원 툴을 구매하고도 6개월 아니 1년이 지나면 단지 구매 리스트에만 등재돼 있을 뿐 현업에서는 사용하지 않아 사장되는 경우를 종종 볼 수 있다. 이것은 툴의 기능에 대한 잘못된 판단으로 적절하게 구매하지 못한 이유도 있겠지만, 구매 후 잘 활용할 수 있도록 교육이 되지 않는 이유가 크다. 이러한 측면에서 볼 때 오렌지는 지금도 매월 1회씩의 무료 교육을 실시해 사용자들이 오렌지를 업무에 잘 활용하도록 유도한다.


필자의 경우 튜닝 작업이나 시스템 아키텍처를 수립하기 위해 고객과 함께 작업을 할 때, 오렌지의 기본 기능만을 사용하거나 아예 사용하지 않는 고객도 많이 봤다. 이러한 경우 고객에게 몇 가지 유용한 기능을 알려주면 대부분의 고객 반응은 ‘아니, 이런 기능도 있었군요’란 놀란 표현이 나온다. 결국 수 십 가지 기능 중에 사용자가 알고 사용하는 기능은 극히 일부이며, 편리하게 사용할 수 있는 기능이 있는데도 불구하고 적절하게 사용하지 못하고 있어 구매 후 교육 여부도 구매시 꼭 생각해 볼 점이다.

툴 구매시 가장 고려할 점

그럼 툴을 구매하고자 할 때 가장 고려해야 할 점은 무엇일까? 앞서 언급한 사후 교육, 사용자 요구사항 적용 등도 있겠지만 가장 먼저 생각해 볼 수 있는 것은 ‘툴의 기능성이다. 기본적으로 적용하고자 하는 업무에 툴이 갖고 있는 기능이 얼마나 적합한가에 따라 잘 활용되는 도구로써 사용될 가능성이 높아지기 때문이다.

오렌지의 4가지 버전과 특징

오렌지는 2001년 12월 12일 1.0버전 출시 이후 현재 3.1버전까지 오라클 데이터베이스 기반의 개발 및 DBA 툴로 선보이고 있다. 최초 오렌지의 개발자는 DBMS 튜닝 및 디자인을 하는 사람으로, 현업에서 작업 중에 비효율적인 매뉴얼 작업들을 자동화하기 위해 오렌지를 만들었다. 이렇게 만들어진 오렌지는 많은 튜너 및 컨설턴트의 피드백을 받아 사용자 중심의 툴로 설계된 것이 가장 큰 특징이다. 지금의 오렌지 3.1버전은 개발 지원 기능 및 튜닝 지원 툴, 모니터링 툴도 제공된다.


오렌지는 오라클 데이터베이스를 기반으로 하는 시스템을 구축할 경우, 개발자가 SQL을 작성하고 데이터를 조회, 등록, 수정 및 PL/SQL 개발 등을 하는데 많은 기능을 제공한다. 또한 DBA의 주요 작업인 데이터베이스 모니터링을 쉽게 할 수 있는 모니터링 기능도 제공한다(DBA 에디션).


오렌지는 사용 용도별로 크게 4가지로 나눠 구분할 수 있다. 스탠더드 에디션(Standard Edition), DBA 에디션, Reorg 에디션, Trusted 에디션으로 나눠지는데, 2005년 3월에 Reorg 에디션을 추가로 구성했다. 사실 Reorg 에디션이라고 해서 특별히 Table, Index, Tablespace 등의 reorg 작업을 오렌지가 직접 해주는 것이 아니라 오라클의 DBMS_REDEFINITION 패키지를 이용해 온라인 reorg 절차를 간편하게 작업할 수 있도록 지원해 주는 것이다. 또한 Trusted 에디션은 사전 등록된 내부 결제 프로세스를 거친 SQL만을 수행할 수 있도록 하는 기능을 갖춘 사전 보안 감시용이라 할 수 있다.


Trusted 에디션의 경우 고객의 업무 프로세스와 연계해 구성되어 사용하는 것이므로 제품 구입시 고려할 사항이 스탠더드 에디션, DBA 에디션과 현격하게 다르기에, 이글에서는 고객이 주로 사용하는 스탠더드 에디션, DBA 에디션만을 설명하도록 하겠다.

개발자 위한 오렌지 스탠더드 에디션

다음의 <표 1>과 같이 스탠더드 에디션은 오라클 데이터베이스를 기반으로 개발할 때 필요한 많은 기능들을 제공한다. 그리고 DBA 에디션은 데이터베이스 시스템의 전반적인 작업 및 모니터링 작업을 수행할 때 필요한 기능들을 갖추고 있다.

<표 1> 오렌지의 개발자 지원 기능
구분 개발자 지원 기능
SQL 툴 다양한 방식의 데이터 조회 및 SQL의 수행 지원, 멀티 탭으로 여러 SQL 동시 작업 가능, 미리 생성된 SQL을 호출해 수행할 수 있는 Named SQL 기능, 다양한 출력, SQL 포맷팅 등을 지원함
Schema Browser 16가지 타입의 데이터베이스 오브젝트에 대한 조회 및 상세 참조 지원, 타입 별로 실행할 수 있는 메뉴 지원
Query Builder GUI 화면으로 Drag & Drop 방식의 SQL의 작성을 지원함
PL/SQL 툴 Package, Procedure, Function의 작성 지원, 다양한 템플릿을 지원하며 PL/SQL 디버깅 기능 포함
Description 툴 데이터베이스 내 오브젝트의 상세 정보를 참조할 수 있는 기능, 또한 오브젝트를 재 생성할 수 있는 스크립트 생성 기능도 지원
Table Editor 테이블의 데이터를 쉽게 조회해 Form 형식으로 변경/삭제할 수 있는 기능
Plan 툴 SQL의 튜닝 작업시 실행계획 및 Trace, trace out 분석 및 테이블, 인덱스의 통계 정보를 볼 수 있는 기능 지원. 특히 오라클의 tkprof를 사용하지 않고 이보다 더 많은 정보를 제공하는 trace 분석 엔진 지원
Trace 툴 Session trace의 output인 trace file을 가독성 있는 포맷으로 보여주는 방식
Database Information 데이터베이스의 system parameter, hidden parameter, system 통계치, sga정보, log file, control file 등의 일반 정보를 확인하는 기능
Network Conf 툴 클라이언트의 SQLNET Service(tnsnames.ora)를 설정, 변경, 테스트하는 기능
Export/Import 툴 오라클 데이터베이스로부터 데이터의 export/import 기능 지원
File Editor 개발자 PC의 텍스트 파일 편집을 지원
Loader Data Loading/Unloading 기능 지원, 엑셀 혹은 텍스트 자료를 손쉽게 Loading/Unoloading. 오렌지 자체 Loader 엔진 이용
Lock/Latch Monitor Database Lock 및 블럭킹된 세션 및 오브젝트 정보 제공, OPS/RAC 지원
Transaction Monitor 수행 중인 트랜잭션의 롤백세그먼트 사용량, DML의 양에 대한 정보 제공
SQL Monitor SQL Area 내의 정보 분석 제공, Buffer gets, buffer gets/exec, execution, disk reads, elapsed time별로 조회 가능. 또한 실제 SQL text를 이용해 검색 가능
Session Monitor 데 이터베이스에 접속되어 있는 모든 세션들의 정보 제공, 세션의 100여 가지 통계정보 중 5개를 선택해 표시하는 옵션, 상세 조사가 필요한 세션에 대핸 trace 생성 기능, Kill session 기능, 현재 사용하는 SQL display 기능
Data Dictionary 툴 오라클 내부의 딕셔너리 테이블을 카테고리 별로 구분해 제공, 카테고리 별로 참고하고자 하는 딕셔너리 테이블을 쉽게 검색하여 참고할 수 있는 기능
ERP Monitor
 대표적으로 고객의 요청에 의해 추가된 기능으로 Oracle Applications(ERP)를 사용하는 경우 ERP의 기본적인 부분에 대한 모니터링 기능


Step-In 기능으로 직관적 정보 분석이 가능

오렌지는 개발부터 가장 가볍고 빠르게 수행될 수 있어야 한다는 전제 하에 제작됐으며, 성능이 느린 화면은 개발시부터 자체 튜닝 대상이 되어 집중적으로 관리됐다. 오렌지의 개발 언어는 Visual C++를 이용했고, 오라클 OCI 인터페이스를 사용한다. 또한 디스크 공간도 매뉴얼을 포함해 약 20MB 정도만 사용할 뿐이다. 다만 오렌지의 빠른 성능을 보장하기 위해서 오렌지에서 사용하는 뷰(view)를 데이터베이스에 생성해야 하는데, 이 작업을 하는 것은 ‘Orange Configuration Admin’을 통해서 쉽게 생성할 수 있다. 하지만 이 작업은 오라클 데이터베이스의 내부 사용 User인 SYS user로 접속해서 수행해야 하기 때문에 데이터베이스 관리자의 허가 하에 작업할 수 있다. 처음 오렌지를 사용할 때는 이것이 다소 제약사항이지만, 스탠더드 에디션인 경우에는 ‘Orange Configuration Admin’을 돌리지 않아도 대부분의 기능은 사용이 가능하다.


화면 간의 작업 및 동작은 멀티 쓰레드 방식으로 구현돼 여러 개의 창에서 동시에 작업할 수 있으며, 오래 수행되는 쿼리를 기다리지 않고 다른 작업을 할 수 있다는 것이 장점이다. 또한 데이터베이스로의 접속도 여러 개의 세션을 맺을 수 있어서 많은 작업을 한 번에 할 수 있게 디자인됐다. 게다가 많은 컨설턴트들의 오랜 노하우가 담겨 있어 개발자, DBA는 작업시 편리한 작업환경에서 오렌지를 사용할 수 있다. 이러한 예는 오렌지 곳곳에서 찾아 볼 수 있다.


SQL monitor에서 특정 SQL을 찾은 후, 현재 이 SQL을 사용하는 세션을 찾는 Step-In 기능은 현업에서 시스템 분석시 많이 사용하는 방법이다. 또한 DBA 에디션의 Health Check 기능은 간단하면서도 기본적으로 점검해야 하는 부분을 많은 컨설턴트가 아이디어 및 개인의 노하우를 집약시킨 것이다. 물론 Report & Graph 기능에 이미 정의되어 있는 스크립트 구성을 보면 노하우를 쉽게 느낄 수 있을 것이다.


그리고 <표 1>에서 나열한 많은 툴 간에 Step-In 작업이 가능해 직관적인 정보 분석이 가능하다. 관심 있는 정보에서 더블 클릭이나 오른쪽 버튼을 누르면 상세 정보 창으로 전이되어 빠르게 자세한 정보를 확인할 수 있다. <화면 1>은 오렌지가 제공하는 툴 간의 Step-In을 보여주고 있다. 이러한 Step-In 기능은 실제 업무에서 작업하는 경우 참고 정보를 확인하기 위한 최적의 툴로 연동하는 기능이다.

<화면 1> 오렌지 툴 간의 Step-In 기능
 
 
 


고객 맞춤형 툴로 변신 가능한 ‘오렌지’

마지막으로 오렌지의 일반적인 측면 중 가장 돋보이는 것은 국내에서 개발한 툴이므로 사용자의 요구사항을 적극적으로 수용할 수 있다는 점이다. 여타 다른 툴들은 외산이므로 제공되는 기능만을 사용할 뿐 추가적인 요구 사항을 요청할 방법이 없다. 다만 오렌지의 경우에는 사용자가 필요한 기능을 개발자와 협의해 점차 고객에게 편리한 툴로 만들 수 있다는 것이다. 사실 오렌지를 개발한 웨어밸리 홈페이지(www.warevalley.com)에는 고객과 개발자의 통신 채널이 있으며 활발하게 Q&A를 진행하고 있다.


그 중 한 예가 ERP 모니터 기능으로 오라클 애플리케이션(ERP)을 사용하는 고객의 요청사항에 의해 만들어진 기능이다. 이는 ERP 관리 작업을 할 때 기본적으로 모니터링을 해야 하는 항목에 대한 화면을 항상 보게 되는데, 오렌지를 이용한 모니터링과 함께 하게 되므로 불편할 사항이 있었다. 이에 대해 고객이 적극적인 요청을 해서 오렌지에 상기 기본 모니터링을 할 수 있는 ERP 모니터 기능을 추가하게 된 것이다. 그러므로 고객이 꼭 필요하다고 느끼고 생각하는 기능에 대해서는 향후에도 추가적인 Add-on이 될 수 있다.

오렌지 탄생 실화

오렌지 개발의 핵심 목표는 가볍고, 빠르고, 쉽게 사용할 수 있는 툴을 만든다는 목표 아래 만들어 졌다. 상상을 해보라. 오렌지의 instance monitor(9개의 그래프)는 단 하나의 SQL로 구현됐다.
오렌지의 탄생은 처음 툴을 만들려고 만들어진 것은 아니다. 데이터베이스 컨설팅을 하던 초기 개발자가 자신이 작업하는 것을 자동화하기 위해 2000년부터 2001년 사이 약 1년간 ‘오렌지’라는 이름으로 만들어 졌다. 그 이후 2001년 12월 정식 ‘오렌지’라는 제품으로 탄생했다. 오렌지라는 이름은 그가 오렌지를 그냥 좋아했기 때문이란다.
그 후 웨어밸리의 한 개발자가 오라클 연동한 클라이언트 툴을 혼자 만들어 보기 위해 ‘토마토’라는 것을 만들었다. 결국 토마토는 오렌지의 기능을 강화하기 위해 오렌지에 흡수됐고, 그 이름을 Report & Graph 툴이라고 했다. 가볍고, 빠르고, 쉽게 사용할 수 있는 툴로 개발될 수밖에 없는 이유는 국내 개발 환경을 잘 아는 개발자의 땀이 묻어 있기 때문이다.

개발자 측면의 유용한 기능들

이제까지는 오렌지의 일반적인 구조에 대해 알아봤다. 그럼 개발자 측면에서의 오렌지 기능들에 대해 살펴보자. 개발자가 주로 하는 작업은 SQL의 작성, 데이터 로딩, 데이터 확인, 데이터 수정, PL/SQL 프로그램 작성 등으로, 오렌지는 이러한 작업 활동을 수행할 수 있는 기능들을 제공한다.


SQL 툴에서 지원되는 기능으로 SQL의 조회 결과를 엑셀로 저장하는 기능은 기본적으로 지원되며 그리드 출력, 텍스트 출력을 지원한다. 그리고 멀티 탭을 지원해 여러 SQL 작업을 동시에 할 수 있다. 이 SQL 툴에서는 필자가 자주 사용하는 기능으로는 SQL 툴의 Action 메뉴에 Recall Named Script 기능인데, 자주 사용되는 SQL 스크립트를 선택해서 수행할 수 있다. 핫 키로 Ctrl+R을 누르면 쉽게 저장된 SQL을 수행할 수 있다(필자의 경우에는 Named Script를 종류별로 잘 정리해 약 200여개의 스크립트를 업무에 필요시 바로바로 사용한다). 이 SQL 툴에서 특별히 지원되는 기능으로 SQL 포맷 기능이 있는데, 이는 정돈되지 않는 SQL을 가지런하게 정돈해주는 기능이다. 특히 이 기능은 튜닝 작업을 할 때 SQL Area에서부터 악성 SQL을 추출해 작업하는 경우에 더욱 편리하다. SQL Area에서 추출한 SQL들은 주로 작성된 SQL의 형태가 아닌 무작위로 보여주는데, 이 SQL 포맷 기능은 버튼 한 번의 클릭으로 보기 어렵게 되어 있는 SQL을 잘 정돈해준다. SQL*Navigator에도 이러한 기능이 있는데 비교해 본다면 오렌지가 그다지 훌륭하게 포맷되지는 않지만 무료로 제공된다는 데에서 매력이 있다.


SQL 툴에서 SQL을 작성하다 보면 여러 번의 수정 및 테스트를 하게 된다. 이때 여러 번 수정된 것을 취소하고 싶을 때는 Undo 기능을 이용해 변경 전의 값으로 돌리는데, 오렌지의 경우는 무한 undo가 지원된다. 이는 SQL을 처음 작성한 시점까지 undo할 수 있다는 것으로. undo가 제한적으로 지원되는 경우(예, MS-Word)에는 undo를 하다가 더 이상 안 될 때 난처할 경우가 발생하는 단점을 없애준다.


개발자들이 많이 사용하는 또 다른 기능 중에 하나는 로더(Loader)이다. 프로젝트의 개발 단계에서 레거시 데이터를 오라클 데이터베이스로 로딩할 때 사용하게 된다. 또한 현재 오라클 데이터베이스에서 데이터를 Un-loading하는 기능도 지원된다. 로더를 이용해 데이터를 로딩할 수 있는 포맷으로는 .csv, .xls, .txt file 등을 지원하므로, 다양한 소스의 데이터 로딩이 가능하다. 반대의 경우인 Unload 역시 상기 지원되는 포맷으로 Un-loading이 가능하다.


개발자들이 SQL을 작성하거나 PL/SQL을 작성할 때 모든 구문을 알고 작업하는 것이 아니므로, 자주 오라클 매뉴얼을 참조해봐야 하는데, 이러한 불편을 없애기 위해 오렌지에서는 DML, DDL, PL/SQL, PL/SQL Control structure, Pseudo column, SQL Function 등의 템플릿을 지원해 매뉴얼 참조 없이 손쉽게 SQL이나 PL/SQL의 작성을 도와준다.



PL/SQL 툴의 독특한 기능 ‘Source Control’

개발자 관련 오렌지 툴 중의 PL/SQL 툴은 독특한 기능이 있다. 이는 오라클 내의 PL/SQL을 작성할 때 편리하게 작업할 수 있는 환경을 지원한다. 이 PL/SQL 툴의 독특한 기능으로 Source Control이 있는데, 기존의 PL/SQL을 수정할 때 다른 개발자와의 동시 수정을 예방하고 어떤 사용자가 현재 어떤 PL/SQL을 수정하고 있는지 알 수 있는 checkout list를 지원한다. Checkout list는 다른 개발자가 동일 PL/SQL을 Check-In한 정보를 보여 준다.


<화면 2>는 checkout list 버튼을 눌렀을 때, 현재 PL/SQL을 수정하고 있는 사용자의 IP-address와 컴퓨터 이름이 나타난다. 만약 자신이 수정하고자 하는 PL/SQL이 이미 다른 개발자로부터 checkout이 됐다면, PL/SQL 툴에서는 이 PL/SQL을 수정할 수 없다. 만약 일반적인 툴이라면 이러한 제약사항 없이 사용하게 되어 두 명의 개발자가 동시에 소스를 수정해 혼란이 발생하게 될 것이다. 만약 다른 개발자가 동시에 작업하고 있다면 IP-address 및 컴퓨터 이름을 통해 어떤 개발자인지 알 수 있고, 전화 등의 통신 채널을 통해 작업 정지나 잔업 협의를 통해 계속 진행할 수 있을 것이다.

<화면 2> Checkout list

PL/SQL 툴의 다른 큰 특징으로는 디버거(Debugger) 기능이다. 일반적인 컴파일러와 유사한 형태로 PL/SQL(procedure, function) 등을 수행 스텝 별로 디버깅할 수 있다. 이 디버거 기능도 다른 외산 제품들은 고가로 팔고 있으나 오렌지는 기본으로 제공하고 있으니 이 또한 큰 장점이라 할 수 있다. 물론 이 PL/SQL 디버거 기능이 C++ 등의 디버거처럼 화려한 기능을 제공하지는 않지만, 기본적은 기능(변수 참조, Step into, Step over, Breakpoirt 등)은 지원한다. PL/SQL 툴에서 procedure, function을 컴파일할 때 컴파일 에러가 발생하게 되면 status 창에 오라클 에러 번호와 에러 메시지를 보여주는데, 이 에러 번호를 더블 클릭하면 그 에러가 발생한 PL/SQL의 부분으로 커서(cursor)를 이동시킨다. 이 기능은 개발자가 빠르게 에러의 위치를 파악하고 수정할 수 있도록 한다.


개발자의 데이터베이스 관련 툴을 사용하는 목적 중의 하나는 데이터베이스 내의 오브젝트를 참조하는 경우이다. 어떤 테이블이 있는지 해당 테이블에 어떤 컬럼이 있는지, 또한 제약사항, 인덱스 등을 확인하는 경우가 있다. 이렇듯 개발자가 SQL 작성시 참조하는 DB 오브젝트의 정보를 손쉽게 확인할 수 있는 스키마 브라우저 기능은 약 16개의 오브젝트를 윈도우의 탐색기와 같이 참조할 수 있도록 지원해 동시에 창을 열고 쉽게 다른 오브젝트를 참고할 수 있다. 물론 스키마 브라우저에서 16개 오브젝트를 검색하며 오른쪽 버튼을 누르면 그 오브젝트에 대한 작업을 할 수 있는 메뉴가 나타난다. 만약 constraint tab에서 오른쪽 버튼을 누르게 되면 ‘Describe, Add constraint, Enable Constraint, Disable Constraint, Drop object’ 등의 연관된 메뉴를 보여준다. 이는 각각의 오브젝트 타입에 따라 필요한 작업을 바로 할 수 있게 하는 기능이다.



DBA 측면의 유용한 기능들

DBA를 위한 기능은 스탠더드 에디션보다 DBA 에디션에 많다. DBA 에디션은 <표 1>과 같이 스탠더드 에디션의 모든 기능에 Space Manager, Security Manager, Instance Monitor, Graph & Report, Health Check, Analyze Manager, Log Miner가 있다.


스탠더드 에디션과 DBA 에디션의 차이는 DBA 기능은 주로 데이터베이스 전체 범위의 모니터링이나 점검, 전체 레벨의 작업을 지원하는 툴들이 대부분이라는 것이다. 가장 대표적인 DBA 지원 툴은 ‘Instance Monitor’이다. 이 툴은 데이터베이스 전반적인 상태를 표시해 주는데, 크게 9가지 부분으로 나눠져 있다. Buffer Cache Hit Ratio, Library/Dictionary Cache Hit Ratio, Connection 수, Active Transactions, Shared Pool Size usage, I/O block per Second, Redo size, Parse/Execution, Wait event 등을 한 눈에 볼 수 있게 주기적으로 그래프를 그려준다. 이 9가지 그래프들은 서로 시간적으로 연결돼 있어 한 그래프의 위치를 선택하면 나머지 8개의 그래프에 시간을 함께 보여줌으로 같은 시간의 정보를 비교해 점검해 볼 수 있다. 이전의 오렌지 2.0 버전에서는 로깅 기능이 없어서 현재 상태의 정보만을 볼 수 있었고 오렌지를 정지했다가 다시 사용할 때는 이전에 모니터링 결과는 무시되고 새로이 모니터링을 해야 했는데, 3.0 버전부터는 로깅 기능도 생겨서 정상적일 때와 부하가 있을 때의 상태를 비교해 볼 수 있다.


이 인스턴스 모니터는 데이터베이스의 전체적인 뷰를 확인하고자 할 때 사용한다. 특별히 이 인스턴스 모니터로부터 다른 Action은 취할 수 없고 현상만 보게 되지만 간단히 시스템의 상태를 보기에는 무리가 없다. 특히 wait event는 옵션 쪽에서 사용자가 특별히 관심 있는 오라클 wait event를 지정할 수 있어 사용자의 편의성을 갖추고 있다.

<화면 3> Instance Monitor의 사용화면

두 번째로 소개할 DBA의 툴 중에 Space Manager가 있다. 이것은 데이터베이스의 테이블 스페이스, 데이터 파일들의 사용량, redo log의 현황, 롤백 세그먼트의 현황을 확인하는 기능을 제공한다. <화면 4>는 각각의 오브젝트가 물리적으로 데이터 파일의 어떤 위치에 몇 개의 extent로 구성되어 있는지 맵 형식으로 한 눈에 보여준다. 이 화면을 통해서 데이터 파일(테이블 스페이스)이 얼마나 조각화가 됐는지 손쉽게 파악할 수 있으며 오브젝트의 실제 저장 위치를 확인 할 수 있다. 다만 블럭의 맵을 그릴 때 약간의 시간이 걸린다는 아쉬움이 남는다(여러 번 튜닝을 했다고는 하는데, 작업량 자체가 많은 관계로 쉽게 빨라지지 않는 것 같다).

<화면 4> Space Manager 데이터 파일 맵

상세한 정보를 제공하는 세그먼트 인포 탭

그리고 <화면 5>와 같이 세그먼트 인포 탭을 보게 되면 해당 오브젝트가 할당 받은 공간 중에 얼마만큼의 블럭을 사용하고 있고, 얼마만큼의 여유 블럭이 있는지 확인할 수 있다. 세그먼트 Usage 바 차트를 보면 진한 파란색, 파란색, 연한 파란색, 하얀색 등으로 구분되어 있는데, 블럭의 사용 정도에 따라 색깔을 달리해 정확한 정보를 시각적으로 보여준다. 이 정보를 이용해 이미 할당 받은 공간의 내부 사용량을 점검함으로써 추가적인 공간 할당이 언제쯤 필요하게 될지 판단하는데 도움이 된다. 보통은 이미 할당한 공간은 모두 사용하는 것으로 간주하고 테이블 스페이스의 공간 할당 계획을 수립하게 되는데, 좀 더 자세한 정보를 이용해 상세한 공간 할당 계획을 수립할 수 있다. 이러한 기능은 여타 다른 툴에서는 찾아보기 어려운 상세한 정보를 제공하는 기능이다.
<화면 5> Space Manager 세그먼트 인포

DBA 에디션에서 빈번히 사용되는 기능 중 하나로 Health Check 기능을 꼽을 수 있는데, 이는 데이터베이스의 전반적인 점검 부분들을 한 번에 점검해 HTML 형식의 리포트를 생성해 준다. 크게 General, SGA, Wait Event, I/O, Space, Access Type, MTS, OPS(RAC)의 8가지로 나뉘며, 각각의 부문별로 점검해야 하는 통계량 및 데이터베이스 운영 내역을 검토해 점검 리포트를 생성한다. 리포트에는 각각의 점검 내역에 대한 자세한 가이드도 제공되어, 문제점이 발견됐을 경우 어떠한 조치를 취해야 하는지 DBA가 쉽게 알 수 있다. 이 Health Check 기능에서 점검하는 점검 리스트들은 데이터베이스 튜닝 및 컨설팅 경험이 있는 여러 명의 노하우가 집약되어 만든 것이다.


그러므로 Instance Monitor와 Health Check 기능을 통해서 데이터베이스 관리자는 관리하고 있는 데이터베이스 상태와 일일 점검 작업을 손쉽게 수행할 수 있다. 다만 오렌지에서 제공되는 기능은 고객의 환경 및 상황에 만족되지 않을 수 있다. 즉, 오렌지에서 이미 정의된 내용이 아닌 고객의 환경에 관련한 점검이 필요한 경우에는 Instance Monitor와 Health Check 기능으로도 점검이 되지 않는다. 이러한 고객의 특화된 부분을 위해 Graph & Report 툴을 제공한다. 이 Graph & Report는 개발 초기에 ‘토마토’라고 불리었고, 오렌지와는 별도로 개발되다가 오렌지의 한 개 툴로서 통합됐다.


Graph & Report에서는 Health Check에서 수행되지 않는 다양한 점검 스크립트가 미리 정의돼 있으며, 사용자가 직접 새로운 스크립트를 정의할 수 있는 User Define Group을 통해 원하는 스크립트를 생성해 모니터링하고 이를 차트로 표현될 수 있도록 제공한다.


DBA 에디션에서 제공되는 Log miner 툴은 오라클의 log miner 기능을 이용해 이미 수행된 작업들에 대한 상세 내역을 편리하게 확인할 수 있다. 이 log miner 사용자에게 오라클 log miner package의 사용법을 숙지하지 않더라도 손쉽게 데이터베이스 작업 이력을 확인해 볼 수 있다.

<표 2> 오렌지의 DBA 지원 기능
구분 DBA 지원 기능
Space Manager Tablespace, Datafile의 물리적인 저장 공간 할당 정보 제공. 테이블스페이스 추가, 변경, 데이터 파일 추가 변경 지원, 특히 세크먼트 인포에서는 할당된 익스텐트 내부의 블럭들의 사용 정도를 상세하게 보여줌
Security Manager User, Role, Privilege, Profile 등의 권한 관리 기능 제공
Instance Monitor 데이터베이스의 기본적인 SGA 모니터링 기능 제공, 9개의 기본적인 차트를 지원하며 특히 wait event는 사용자가 선정해 구성 가능하다. 히스토리 관리를 위해 로깅기능 제공
Graph & Report 특정 SQL의 반복 수행을 이용한 그래프 작성 제공, User Define SQL의 수행 및 그래프 작성 제공
Health Check 10여명의 컨설턴트에 의해 선별된 데이터베이스의 기본적인 Health Check 및 Report 제공, OPS, RAC, MTS 관련 지원
Analyze Manager Table, Index에 대한 통계치 생성 및 관리 기능 제공, 특히 대량의 통계치 생성 작업시 테이블의 크기나 건수에 따라 완전분석(compute)이나 예측분석(estimate)이 가능
Log Miner Redo log/ Archive log에 대한 Log miner 분석을 쉽게 할 수 있는 기능 제공


튜너 측면의 유용한 기능들
오렌지의 태생이 튜너의 작업 지원을 위해서 개발됐다는 것은 이미 앞에서 언급했다. 즉, 튜너의 작업시 필요한 많은 기능이 오렌지에는 포함되어 있다.

SQL 튜닝에 강력한 기능을 갖고 있는 '오렌지'

오라클 데이터베이스 관련 튜닝이라고 하면 크게 두 가지로 나눌 수 있는데, 하나는 데이터베이스 튜닝이고 나머지는 SQL 튜닝이라 할 수 있다. 오렌지는 후자의 SQL 튜닝에 강력한 기능들을 많이 갖고 있으며, 전자인 데이터베이스 튜닝 기능도 보유하고 있다. 앞서 설명한 개발자 기능, DBA 기능에도 데이터베이스 튜닝을 위한 점검 기능이 포함되어 있으며, 특히 SQL Monitor, Instance Monitor, Session Monitor, Transaction Monitor, Lock Monitor는 데이터베이스 튜닝시에 자주 사용되는 기능이다.


SQL Monitor에서는 데이터베이스에 다양한 옵션을 이용해 부하를 많이 주는 SQL을 찾을 수 있으며, Instance Monitor에서는 주된 병목 현상이 어떤 부분에서 발생하는지 쉽게 확인할 수 있다. Session Monitor에서는 데이터베이스에 접속한 세션들의 작업 내역을 데이터베이스 통계치 기준으로 자원을 많이 사용하는 세션들을 편리하게 점검할 수 있다. 여기에서는 SQL을 튜닝하는 것을 중점적으로 설명하겠다.

대표적 튜닝 지원 기능 ‘Plan 툴’

대표적인 튜닝 지원 기능으로는 ‘Plan 툴’이라고 할 수 있다. Plan 툴은 개별 SQL의 수행 계획(Execution Plan)을 보여주며, 수행 계획의 각 단계별로 위치하는 오브젝트의 정보를 손쉽게 확인해 볼 수 있다. 단지 더블 클릭만으로 테이블의 컬럼 정보, 인덱스 정보, 그리고 테이블의 통계치 등을 확인해 볼 수 있다. 또한 하나의 SQL에 포함되어 있는 모든 테이블과 인덱스 정보를 수집할 수 있다. 물론 이러한 static 정보만을 갖고 정확한 튜닝 결과를 도출 할 수 없다. 이에 Plan 툴에서는 실시간 SQL Trace 기능을 지원한다. 오라클에서 SQL trace를 생성해 이 trace file을 실시간으로 읽기 편한 포맷으로 분석해 사용자에게 제공한다. 10046 이벤트를 이용한 여러 레벨의 trace를 지원하며, SQL 내부에 있는 bind 변수 값, wait event 및 실제 수행되는 시간을 정확하게 분석할 수 있다.



오라클의 tkprof 없이 자체 엔진으로 trace file을 분석한다?

오렌지를 사용하지 않는 경우에는 SQL의 trace를 걸어서 trace file을 확인하고, 오라클에서 제공하는 tkprof를 이용해 포맷팅을 해서 trace 결과를 확인하는 5∼6단계의 작업이 필요하나, plan 툴의 실시간 trace 기능을 사용하면 버튼 하나로 모든 작업이 완료돼 빠르게 튜닝 작업을 할 수 있다. 여기서 오렌지의 특이한 점은 trace file을 분석하기 위해서 오라클의 tkprof를 사용하지 않고 자체적으로 엔진을 만들어 trace file을 분석하는 것이다. 이렇게 자체적으로 엔진을 만들어서 사용하는 까닭은 오라클 서버의 tkprof를 이용해 분석하는 경우, 서버에 tkprof를 수행할 수 있도록 셸 프로그램 등을 구성해야 하는 구조적인 문제(오렌지는 서버에 에이전트가 없는 가벼운 구조이다)와 클라이언트 반응 시간, Wait Event 정보와 같은 더 많은 분석 정보를 주기 위해서다.


SQL 튜닝을 하는 과정에 하나의 SQL을 여러 방법으로 수정해 보고 작업하게 되는데, Plan 툴에서는 멀티 탭 기능을 이용해 여러 개의 SQL 버전을 만들어서 튜닝 작업을 할 수 있다. 물론 이런 변형된 SQL들을 각각 trace를 할 수 있으며, 이러한 모든 결과는 Workspace 개념으로 저장 및 로드할 수 있다(PTD 파일로 저장이 된다). 이 Workspace 개념은 개발자와 튜너와의 통신 수단으로 사용되곤 한다. 개발자가 개발한 SQL의 성능이 나오지 않을 경우에는 1차적인 Trace 및 통계정보를 workspace로 저장해 튜너에게 튜닝 요청을 하게 되면, 튜너는 이 workspace를 로드해 바로 튜닝 작업을 진행할 수 있으며 튜닝이 완료되면 수정된 SQL과 이에 따른 trace 정보를 다시 개발자에게 workspace로 저장해 전달할 수 있다.

<화면 6> Plan 툴

이 Plan 툴에서는 Run time execution plan을 보여준다는 것도 특징이다. SQL이 수행되는 과정은 크게 『파싱 | 바인딩 |수행』으로 나뉘는데, 지금까지는 실행 계획을 파싱 단계에서 수립해 수행했다. 그런데 오라클 데이터베이스 9i 버전부터는 bind peeking이라는 기능이 있어서 바인딩 단계에서 변수 값을 이용해 새로이 실행 계획을 생성하는 것이다.


그러므로 수행 계획만을 체크했을 때와는 달리 실제 수행 계획이 달라지는 경우가 종종 있다.


이러한 차이점은 개발자나 튜너를 황당하게 만드는데, 이렇게 차이가 나는 실행 계획은 확인시켜 줄 수 있다. Plan 툴의 내부 기능인 Trace를 독립적인 Trace 툴로 제공하고 있는데, 이는 Trace를 오라클 전체적인 방법으로 수행하거나, 이미 trace가 생성되어 있는 경우 이 파일을 분석하는 형태로 사용하기 때문에 추가된 것이다. 또한 포맷팅된 결과로 볼 수도 있지만 종종 로우 파일(Raw file) 형태의 참조가 필요한 경우를 대비해 로우 파일의 참조도 지원한다.



미래의 오렌지에 대한 기대

지금까지 오렌지가 개발자, DBA, 튜너에게 어떠한 기능을 제공하는지에 대해 알아봤다. 이 글에서 언급한 내용이 오렌지의 모든 기능은 아니지만, 자칫 매뉴얼처럼 될 것을 우려해 오렌지가 갖고 있는 기능 중에 사용자들이 가장 많이 사용하고 많은 도움을 주는 기능 위주로 설명해 오렌지를 선택하고자 하는 고객에게 객관적인 정보가 됐으면 하는 바람이다.


오렌지 역시 버전이 업그레이드되며 사용자 중심의 기능들이 많이 추가됐다. 하지만 앞으로 좀 더 해야 할 일이 많은 것 역시 현재의 숙제이다. 오라클 9i, 오라클 10g, 그리고 2005년 8월 말에 출시될 오라클 10g R2의 새로운 기능들을 사용자들이 손쉽게 접근하고 활용할 수 있는 그러한 오렌지로 변신해야 할 것이다. 아직까지는 오라클 8, 오라클 8i, 그리고 오라클 9i를 지원하는데 머물러 있기 때문에, 향후 1∼2년 이내에 오라클 10g의 장점을 표현해 주지 못한다면 시장에서의 입지는 좁아질 것이다.


오라클 10g에서 제공되는 기본 기능에는 AWR(Automatic Workload Repository), ADDM(Automatic Database Diagnostic Manager) 등이 있다. 이 기능들은 데이터베이스의 운영 상태, 문제점, 병목 현상의 원인 등을 모니터링 툴이 없어도 될 정도의 데이터를 제공한다. 이렇게 풍부한 데이터를 어떻게 효율적으로 사용자에게 제공하느냐가 다음 버전 오렌지의 발전에 계기가 될 것이다.


출처 :
오라클 데이터베이스 환경에서 쉽고, 빠르게 개발의 효율성과 생산성을 높이고, 성능의 최적화를 구현하는 어플리케이션 개발 및 성능 튜닝 툴


제품목적
SQL과 PL/SQL개발 및 튜닝, 모니터링을 빠르고 쉽게 할 수 있는 인터페이스를 제공함으로써 사용자의 생산성을 향상 시킨다.

사용대상
ORACLE DB를 액세스 하기 위한 SQL(Structured Query Language), PL/SQL을 작성하는 개발자와 SQL 튜닝과 모니터링을 하는 DBA

운영환경
  • CPU : Pentium 133 이상
  • 메모리: 64 MB 이상
  • Software Platform: Windows 9x/NT/Me/2000/xp
  • SQL*Net 최소 : 8.0.5, 권장 : 8.1.7 이상 (http://otn.oracle.co.kr/software/)
  • Oracle RDBMS: Oracle 7.3 이상
  • Disk Space : 10MB

Orange 특장점
  • 현업 개발자들이 요구하는 핵심기능에 충실
  • 뛰어난 인터페이스
  • 오라클 경력이 많은 컨설턴트들의 실전을 통해 검증된 방법 및 interface 제공
  • 모든 기능이 통합형으로 제공
  • 순수 국내기술
    • 사용자들의 추가 요구사항에 신속한 대응
    • 한글 매뉴얼 및 HELP 제공
    • 기술축적에 따라 저렴한 가격으로 다양한 DB TOOL software 제공

Architecture



제품종류
  • Standard Edition
    • Schema Browser
    • Query Builder
    • SQL Tool
    • PL/SQL Tool
    • Description Tool
    • Table Editor
    • Plan Tool
    • Trace Tool
    • Database Information
    • Network Configuration Tool
    • Expert Tool
    • Import Tool
    • File Editor
    • Loader
    • Latch Monitor
    • Transaction Monitor
    • ERP Monitor
    • SQL Monitor
    • Session Monitor
    • Lock Monitor
    • Data Dictionary Tool
  • DBA Edition
    • Space Manager
    • Security Manager
    • Instance Monitor
    • Graph & Report
    • Health Check
    • Analyze Manager
    • Log Miner
  • Reorg Edition
    • Reorg Manager
      dbms_redefinition 패키지를 사용하여 테이블 온라인 REORG 및 REORG 대상 추출
  • Trusted Edition
    DB 사용자가 의도적으로 외부유출을 목적으로 데이터를 액세스하거나 또는 비의도적인 실수에 의하여 문제가 발생하는 것을 사전에 차단하기 위하여 DB 액세스 시 사전 등록된 내부결재 프로세스를 거친 SQL만 실행하도록 하는 기능을 갖춘 사전 보안감시용 툴
반응형
저작자표시 비영리

'ORACLE > ORACLE TOOL' 카테고리의 다른 글

오렌지나 TOAD에서 Predicate Information을 참조하는 방법  (0) 2010.01.22
SQL * Plus 명령어 & 환경 시스템 변수  (0) 2009.09.04
Toad 에서 null 값 노란색으로 표시하기  (0) 2009.08.21
Oracle DB로 보는 데이터베이스 인터페이스의 발전  (0) 2009.07.10
toad 에서 explain plan 보기  (1) 2009.03.27
Posted by [PineTree]
ORACLE/ADMIN2009. 11. 20. 17:22

emca 재구성

반응형


리스너 포트 1525 em포트 1082

 emca -config dbcontrol db -repos recreate -SID ora11gr2 -PORT 1525 -ORACLE_HOME /u0/app/ora11gr2/product/11gr2/db_1 -DBCONTROL_HTTP_PORT 1082

반응형
저작자표시 비영리

'ORACLE > ADMIN' 카테고리의 다른 글

sqlplus 환경 설정  (0) 2009.12.09
oracle 권한확인  (0) 2009.12.04
DBLINK시 LOB CLOB 컬럼오류 해결  (0) 2009.11.20
logminer + 불완전 복구  (0) 2009.11.19
Index Coalesce VS. Shrink  (0) 2009.11.06
Posted by [PineTree]
ORACLE/ADMIN2009. 11. 20. 13:50

DBLINK시 LOB CLOB 컬럼오류 해결

반응형

-- DB링크를 만든다.

CREATE DATABASE LINK DB링크명
CONNECT TO scott IDENTIFIED BY tiger USING 'DB링크명';

 

-- 원격테이블의 컬럼에 clob 혹은 long과 같은 데이터 타입이 존재한다.
select * from 원격테이블@DB링크명
where rownum = 1
-- 에러가 발생한다.  ORA-22992:원격테이블로부터 선택된 LOB위치를 사용할 수 없습니다.


-- 따라서 로컬에 원격지의 테이블을 만들고 그대로 만들어 준다.
create table temp_원격테이블
as
select * from 원격테이블@DB링크명
where 1=2;

-- 그리고나서 원격지 테이블을 그대로 insert해온다.
insert into temp_원격테이블
select * from 원격테이블@DB링크명


-- 이제 로컬테이블로 되어 있다.
select * from temp_원격테이블
where rownum = 1;

[출처] DBLINK시 LOB CLOB 컬럼오류 해결|작성자 조은신


반응형
저작자표시 비영리

'ORACLE > ADMIN' 카테고리의 다른 글

oracle 권한확인  (0) 2009.12.04
emca 재구성  (0) 2009.11.20
logminer + 불완전 복구  (0) 2009.11.19
Index Coalesce VS. Shrink  (0) 2009.11.06
REDO log buffer 캐쉬의 튜닝 및 REDO Latch 경합의 해결 방법  (0) 2009.11.06
Posted by [PineTree]

티스토리툴바