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

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

      SQL> select TABLESPACE_NAME, BIGFILE from DBA_TABLESPACES;

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

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

      SQL> ALTER TABLESPACE test_big AUTOEXTEND OFF;
      Tablespace altered.

      SQL>  ALTER TABLESPACE test_big AUTOEXTEND ON;
      Tablespace altered.

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

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

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

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

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

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

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

     Tablespace created.

     SQL> select TABLESPACE_NAME, BIGFILE from DBA_TABLESPACES;

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

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

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

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


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

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







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

oracle 접속 잘 안될때 system state dump 뜨기  (2) 2009.10.12
Oracle 11g : Improving Application Performance with Result Cache  (0) 2009.10.09
Oracle EXISTS Versus IN  (1) 2009.10.06
Oracle sqlplus에서 변수 사용하기.  (1) 2009.10.06
oracle 테이블내 조건에 맞는 데이터만 export하는 query option  (2) 2009.09.30

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

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

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

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

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

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

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

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

no rows selected

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

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

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

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



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


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

Oracle 11g : Improving Application Performance with Result Cache  (0) 2009.10.09
Oracle 10g: BIGFILE Type Tablespaces Versus SMALLFILE Type  (2) 2009.10.07
Oracle sqlplus에서 변수 사용하기.  (1) 2009.10.06
oracle 테이블내 조건에 맞는 데이터만 export하는 query option  (2) 2009.09.30
Oracle DESC index에 대한 colunm 이름 찾는 방법  (0) 2009.09.25

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

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

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

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

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

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

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

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

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

SQL> SET VERIFY OFF
SQL> /

Enter value for product_id_var: 4

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

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

SQL> SET VERIFY ON

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

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

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

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

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

SQL> SET DEFINE '&'

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

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

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

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

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

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

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

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


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

SQL> DEFINE product_id_var = 7
SQL> DEFINE product_id_var

DEFINE PRODUCT_ID_VAR          = "7" (CHAR)

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

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

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

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

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

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

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

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

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




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

Oracle 10g: BIGFILE Type Tablespaces Versus SMALLFILE Type  (2) 2009.10.07
Oracle EXISTS Versus IN  (1) 2009.10.06
oracle 테이블내 조건에 맞는 데이터만 export하는 query option  (2) 2009.09.30
Oracle DESC index에 대한 colunm 이름 찾는 방법  (0) 2009.09.25
Oracle AWR 자료 query 할때 편한 함수 LAG  (2) 2009.09.03

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


[export options]

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

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


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

1. QUERY in Parameter file.

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

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

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

%expdp system/manager parfile=expdp_q.par



2. QUERY on Command line.

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

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


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



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

Oracle EXISTS Versus IN  (1) 2009.10.06
Oracle sqlplus에서 변수 사용하기.  (1) 2009.10.06
Oracle DESC index에 대한 colunm 이름 찾는 방법  (0) 2009.09.25
Oracle AWR 자료 query 할때 편한 함수 LAG  (2) 2009.09.03
oracle TimesTen 11g Conference  (0) 2009.09.03

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

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

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


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

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

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

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

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


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


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

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

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


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




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

Oracle sqlplus에서 변수 사용하기.  (1) 2009.10.06
oracle 테이블내 조건에 맞는 데이터만 export하는 query option  (2) 2009.09.30
Oracle AWR 자료 query 할때 편한 함수 LAG  (2) 2009.09.03
oracle TimesTen 11g Conference  (0) 2009.09.03
oracle 운영시 개별 세션이나 프로세스가 이상이 있을 경우 trace/dump 뜨는 방법.  (0) 2009.09.01

Oracle AWR 데이터는 정해진 interval 마다 oracle statistic 값을 retention 기간만큼 보관하게 됩니다.
따라서 해당 시점에 발생된 statistic 값을 보려면 관련 table에 대해 self join을 해야하는데,
이때 편하게 쓸 수 있는 oracle 함수가 LAG 합수 입니다.

LAG is an analytic function. It provides access to more than one row of a table at the same time without a self join. Given a series of rows returned from a query and a position of the cursor, LAG provides access to a row at a given physical offset prior to that position.

If you do not specify offset, then its default is 1. The optional default value is returned if the offset goes beyond the scope of the window. If you do not specify default, then its default is null.

You cannot nest analytic functions by using LAG or any other analytic function for value_expr. However, you can use other built-in function expressions for value_expr.

[참고 ] Oracle 8.1.7 SQL reference [ LAG 함수 ]


간단히 예를 들면..
다음 처럼 DB_TIME 값의 변경 내역을 알고 싶다면 동일한 table에 대한 self join이 필요한데,
이 lag over 함수를 사용하면 join이 필요없어 집니다.

select a.snap_id,a.stat_name,a.value, b.value , a.value - b.value
from
DBA_HIST_SYS_TIME_MODEL a,
DBA_HIST_SYS_TIME_MODEL b
where a.snap_id = b.snap_id + 1
and a.stat_id = 3649082374
and a.stat_id = b.stat_id
/

select snap_id,stat_name,value, lag(value) over (order by snap_id),
       value - nvl(lag(value) over (order by snap_id),0)
from DBA_HIST_SYS_TIME_MODEL
where stat_id = 3649082374
/





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

oracle 테이블내 조건에 맞는 데이터만 export하는 query option  (2) 2009.09.30
Oracle DESC index에 대한 colunm 이름 찾는 방법  (0) 2009.09.25
oracle TimesTen 11g Conference  (0) 2009.09.03
oracle 운영시 개별 세션이나 프로세스가 이상이 있을 경우 trace/dump 뜨는 방법.  (0) 2009.09.01
oracle - recovery manager (RMAN)  (0) 2009.08.23

오라클 운영시 특정 세션이나 프로세스가 resource를 과도하게 소모하거나, 속도 저하 등의 이상이 발생해 다른 세션들 까지 영향을 주는 경우가 종종 발생하곤합니다. 이럴 때 해당 세션이나 프로세스의 문제를 해결하기 위해 시간을 끌다가는 전체 업무에 주는 영향으로 인해 더 큰 문제가 발생할 수 있죠.

다음은 개별 세션이나 프로세스가 이상이 있을때 가징 기본적으로 수집해야 하는 trace/dump를 수행하는 방법입니다. 알아서 관련 정보를 다 수집해 주면 좋겠으나, 일일히 관련 정보를 수집해야 하니 좀 번거로울 수 있겠으나, 이런 작업들을 안해 놓으면 문제가 미궁으로 빠질 수 있으므로, 미리미리 알아 놓는 게 좋겠죠..

오라클은 많은 trace/dump 수행 방법을 제공하고 있는데, 다음의 event 10046, errorstack은 개별 세션의 정보를 수집합니다. 물론 이 두개의 trace/dump를 수행하고 전체적인 systemstate dump나 hanganalyze도 같이 수행을 하면 전체적인 영향까지도 확인 할 수 있겠죠.

10046 event는 일반적인 SQL_TRACE 기능을 수행합니다. 또 특정 level을 지정해주면 해당 세션의 wait event와 bind value까지 tracing 해줍니다.

1  - Enable standard SQL_TRACE functionality (Default)
4  - As Level 1 PLUS trace bind values
8  - As Level 1 PLUS trace waits
12 - As Level 1 PLUS both trace bind values and waits

ERRORSTACK dump은 해당 process의 현재 상태 및 정보를 수집합니다. SQL 구문과 Call stack, errorstack, processstate, context area 정보를 수집합니다.

0    Error stack only (no longer be a valid option)
1    Error stack and function call stack (if implemented)
2    As 1 plus the ProcessState
3    As 2 plus the context area (all cursors and current cursor highlighted)

다음은 oradebug를 이용해 10046 event를 on/off 시키는 방법입니다.
event를 on 시킨 후 충분한 데이터를 수집했다고 판단되면 off 시키면 되며, 결과물은 sql_trace 처럼 tkprof로 정리해서 볼수 있다.

* 10046 tracing on
sqlplus '/as sysdba'
oradebug에 세션이나 프로세스를 지정할 때 oracle pid나 o/s pid, 자기자신의 process를 지정할 수 있다.
따라서 위의 3가지 중 하나를 지정하면 된다.
SQL>oradebug setorapid [Oracle process id]
SQL>oradebug setospid [O/S process id]
SQL>oradebug setmypid
trace 발생양에 대한 제한을 unlimit로 지정한다.
SQL>oradebug unlimit
event 10046을 level 12로 지정한다.
SQL>oradebug Event 10046 trace name context forever, level 12
생성한 trace file이름을 출력한다.
SQL>oradebug tracefile_name
SQL>exit

* 10046 trace off
sqlplus '/as sysdba'
10046 trace 지정시 사용한 oracle pid나 o/s pid, 자기자신의 process를 지정 한다.
SQL>oradebug setorapid [Oracle process id]
SQL>oradebug setospid [O/S process id]
SQL>oradebug setmypid
event 10046 설정을 해지한다.
SQL>oradebug Event 10046 trace name context off
SQL>exit

다음은 oradebug를 이용해 ERRORSTACK dump를 수행하는 방법입니다. ERRORSTACK은 해당 시점의 프로세스 정보를 수집하는 것이므로 on/off 단계는 필요 없이 한번만 수행해 주면 됩니다.

* ERRORSTACK dump
sqlplus '/as sysdba'
oradebug에 세션이나 프로세스를 지정할 때 oracle pid나 o/s pid, 자기자신의 process를 지정할 수 있다.
따라서 위의 3가지 중 하나를 지정하면 된다.
SQL>oradebug setorapid [Oracle process id]
SQL>oradebug setospid [O/S process id]
SQL>oradebug setmypid
trace 발생양에 대한 제한을 unlimit로 지정한다.
SQL>oradebug unlimit
errorstack dump를 level 3으로 수행한다.
SQL>oradebug dump errorstack 3
생성한 trace file이름을 출력한다.
SQL>oradebug tracefile_name
SQL>exit

가끔 sqlplus 자체가 수행이 되지 않아 trace/dump 수행이 어려운 경우가 있는데, 이러한 경우 sqlplus의 -prelim option을 사용하거나 debugger tool인 dbx를 사용해서 errorstack을 수행할 수 있습니다.

dbx debugger를 이용한 process의 call stack 보기.

dbx -a $pid
dbx) where
dbx) detach
 
* 반드시 detach 하지 않을 경우 attach 한 프로세스는 비정상 종료된다.
* -a flag는 해당 프로세스에 attach 하여 dbx 시행하는 동안 해당 프로세스는 실행이 중지되므로 background process에 대한 attach는 피하는 편이 신상에 좋습니다.. --;

dbx debugger를 이용한 core file의 call stack 보기.

dbx program명 core
dbx) where
dbx) quit


위의 내용은 oracle metalink note에서 참고했습니다.
Note.21154.1 EVENT: 10046 "enable SQL statement tracing (including binds/waits)"
Note.37871.1 EVENT: ERRORSTACK - How to get
Note 1058210.6 How to enable 10046 for another session using ORADEBUG  
Note 62294.1 The DBMS_SUPPORT Package                                
Note 35054.1 Interpreting ERRORSTACK output                          
Note 39817.1 Interpreting RAW 10046 output                           




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

Oracle AWR 자료 query 할때 편한 함수 LAG  (2) 2009.09.03
oracle TimesTen 11g Conference  (0) 2009.09.03
oracle - recovery manager (RMAN)  (0) 2009.08.23
Oracle OTN Developer Day  (0) 2009.08.20
Oracle Execution Plan 관련 view 들  (0) 2009.08.18

오라클에서 수행되는 cursor의 수행 정보와 이와 관련된 각종 statistic 정보를 확인할 수 있는 view가 몇개 있습니다.
다음은 해당 view에 대한 비교와 column 정보들, 그리고 해당 view를 이용한 몇개의 sql script 들입니다.


V$SQL_PLAN_STATISTICS:
각 operation에 대한 execution statistics 정보를 제공한다.

1)EXECUTIONS:
Child cursor의 수행된 횟수 이다. 따라서 동일한 SQL이더라도 optimizer 환경이나 bind variable size 등의 변경에 의해 다른 child cursor를 가질 수 있으므로 이에 대한 고려도 필요하다.
두개의 세션에서 동일한 SQL을 2회 수행하는 경우나, 같은 세션에서 동일한 SQL을 2회 수행한 경우 동일하게 2의 값을 갖는다.

2)LAST_OUTPUT_ROWS and OUTPUT_ROWS:
LAST_OUTPUT_ROWS는 해당 operation에 의해 마지막에 처리된 실제 row 수 이며, OUTPUT_ROWS는 누적 합이다.

3)LAST_DISK_READS and DISK_READS:
LAST_DISK_READS는 해당 operation이 disk를 읽은 횟수를 의미하며, DISK_READS는 누적 합이다.

V$SQL_PLAN_STATISTICS_ALL:

다음의 관련 view의 데이터에 대한 전체적인 정보를 갖는다.
V$SQL_PLAN                   (all operations, objects, cost, cardinality etc)
V$SQL_PLAN_STATISTICS (execution statistics as above)
V$SQL_WORKAREA           (memory usage)

1) estimated_optimal_size
해당 operation을 메모리상에서 처리하기 위해 측정된 memory size (KB)

2) last_memory_used
cursor의 마지막 execution 동안 사용된 memory (KB)

3) active_time
Average active time (in centi-seconds)

다음의 SQL script는 특정 SQL cursor에 대한 plan과 관련 statistic 정보를 보여줍니다.

qeps.sql과 qep.sql을 생성해 수행하면 SQL의 성능을 평가하는 8개의 항목에 대해 지정된 갯수의 SQL과 그에 따른 statistic 정보를 보여줍니다. init parameter인 statistics_level를 ALL로 설정해야 정상적인 결과를 보여줍니다.

참고 :
Note 186548.1 9i Release 2 Cached cursors information in the row source level
Note 260942.1 Display Execution plans from Statement's in V$SQL_PLAN
Note 550578.1 How to Obtain the most Resource Intensive SQL Execution Plans using the Libr...




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

oracle - recovery manager (RMAN)  (0) 2009.08.23
Oracle OTN Developer Day  (0) 2009.08.20
"A Tour of the AWR Tables" [NOCOUG Summer Conference]  (0) 2009.08.18
Oracle AWR view MAP - Wait event 관련  (0) 2009.08.12
Oracle AWR table MAP - SQL 관련  (0) 2009.08.12

AWR table map을 찾던 중 "A Tour of the AWR Tables"라는 NOCOUG Summer Conference에서 소개된 자료를 확인할 수 있었습니다. 제목을 보니 AWR관련 table에 대한 여러 힌트를 얻을 수 있을 듯해서 자료를 보니 "load spike"를 찾기 위한 좀 다른 방법을 설명하고 있네요.

이전 " oracle awr 설정 시 고려해야 할 점" post에서 강호동, 이건희 회장까지 등장시키며 AWR 자료 수집 주기를 15 ~20분으로 줄여야한다고 글을 썼는데, 이 글의 저자는 "In my experience, the hourly interval is appropriate." 라고 하더군요. 저자는 DBA_HIST_SYSSTAT의 DB time, elapsed time 등의 load profile 자료를 사용하지 않고 1초마다 수집되는 ASH의 자료를 이용해 "load spike"를 찾는 방법을 제시하고 있습니다.

오옷 .. 그런 방법이.. 근데, 그 외의 성능관련 정보는 어떻게... --;
추가적인 AWR 관련 table을 이용한 문제가 될만한 SQL 찾는 방법 등도 같이 소개합니다.

Average Active Session이 튀는 시간 찾기.
column sample_hour format a16

select
   to_char(round(sub1.sample_time, 'HH24'), 'YYYY-MM-DD HH24:MI') as sample_hour,
   round(avg(sub1.on_cpu),1) as cpu_avg,
   round(avg(sub1.waiting),1) as wait_avg,
   round(avg(sub1.active_sessions),1) as act_avg,
   round( (variance(sub1.active_sessions)/avg(sub1.active_sessions)),1) as act_var_mean
from
   ( -- sub1: one row per second, the resolution of SAMPLE_TIME
     select
        sample_id,
        sample_time,
        sum(decode(session_state, 'ON CPU', 1, 0))  as on_cpu,
        sum(decode(session_state, 'WAITING', 1, 0)) as waiting,
        count(*) as active_sessions
     from
        dba_hist_active_sess_history
     where
        sample_time > sysdate - (&hours/24)
     group by
        sample_id,
        sample_time
   ) sub1
group by
   round(sub1.sample_time, 'HH24')
order by
   round(sub1.sample_time, 'HH24')
;

SAMPLE_HOUR          CPU_AVG   WAIT_AVG    ACT_AVG ACT_VAR_MEAN
----------------- ---------- ---------- ---------- ------------
2008-04-16 07:00         1.4         .4        1.8           .6
2008-04-16 08:00         1.8         .5        2.3            1
2008-04-16 09:00         2.3         .5        2.8          1.3
2008-04-16 10:00         2.6         .6        3.2          2.3
2008-04-16 11:00         3.5         .6        4.1          2.3
2008-04-16 12:00         2.4         .6          3          1.1
2008-04-16 13:00         2.3         .6        2.9            1
2008-04-16 14:00         3.7        2.7        6.4         95.4   <== spike in variance
2008-04-16 15:00         3.1         .7        3.8          1.9
2008-04-16 16:00         2.9         .7        3.6          1.6
2008-04-16 17:00         2.3         .4        2.7           .9
2008-04-16 18:00         2.1         .6        2.7          2.6

Average Active Session이 튀는 시간대의 특정 분단위 찾기.
column sample_minute format a16

select
   to_char(round(sub1.sample_time, 'MI'), 'YYYY-MM-DD HH24:MI') as sample_minute,
   round(avg(sub1.on_cpu),1) as cpu_avg,
   round(avg(sub1.waiting),1) as wait_avg,
   round(avg(sub1.active_sessions),1) as act_avg,
   round( (variance(sub1.active_sessions)/avg(sub1.active_sessions)),1) as act_var_mean
from
   ( -- sub1: one row per second, the resolution of SAMPLE_TIME
     select
        sample_id,
        sample_time,
        sum(decode(session_state, 'ON CPU', 1, 0))  as on_cpu,
        sum(decode(session_state, 'WAITING', 1, 0)) as waiting,
        count(*) as active_sessions
     from
        v$active_session_history
     where
        sample_time > sysdate - (&minutes/1440)
     group by
        sample_id,
        sample_time
   ) sub1
group by
   round(sub1.sample_time, 'MI')
order by
   round(sub1.sample_time, 'MI')
;

SAMPLE_MINUTE           CPU_AVG   WAIT_AVG    ACT_AVG ACT_VAR_MEAN
-------------------- ---------- ---------- ---------- ------------
2008-04-16 13:54:00           3          1          4            0
2008-04-16 13:55:00         3.2         .3        3.5           .7
2008-04-16 13:56:00         4.2         .3        4.5          3.4
2008-04-16 13:57:00         3.8         .8        4.7           .7
2008-04-16 13:58:00         6.3          1        7.3          1.6
2008-04-16 13:59:00         3.4         .4        3.8           .2
2008-04-16 14:00:00         8.3         .5        8.8          1.8
2008-04-16 14:01:00        10.7        2.2       12.8           .5
2008-04-16 14:02:00         3.5         .7        4.2           .5
2008-04-16 14:03:00         2.6        1.2        3.8          1.5
2008-04-16 14:04:00         3.3        1.2        4.5          1.3
2008-04-16 14:05:00         8.2         .7        8.8          2.1
2008-04-16 14:06:00         6.7        1.3          8          1.1
2008-04-16 14:07:00         4.7        3.2        7.8          3.7
2008-04-16 14:08:00        20.5      109.8      130.3          170  <== spike in AAS
2008-04-16 14:09:00           6        1.3        7.3         10.3
2008-04-16 14:10:00         2.6         .4          3           .8
2008-04-16 14:11:00           4         .3        4.3          1.1
2008-04-16 14:12:00         5.7         .8        6.5          1.6
2008-04-16 14:13:00           3         .3        3.3           .7
2008-04-16 14:14:00         1.8         .7        2.5           .6
2008-04-16 14:15:00         3.3          2        5.3          2.2


 
참고:  A Tour of the AWR Tables (NoCOUG Summer Conference 2008, August 21 2008)

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

Oracle OTN Developer Day  (0) 2009.08.20
Oracle Execution Plan 관련 view 들  (0) 2009.08.18
Oracle AWR view MAP - Wait event 관련  (0) 2009.08.12
Oracle AWR table MAP - SQL 관련  (0) 2009.08.12
oracle awr 설정 시 고려해야 할 점  (0) 2009.08.11

다음의 그림은 AWR 내의 wait event관련 view의 primary/reference key 들입니다.
필요한 view를 각각의색에 맞게 join하여 사용할 수 있습니다.


다음은 Oracle 10g R2 Reference 메뉴얼 링크입니다.
해당 view가 어떤 정보들을 가지고 있는지 확인할 수 있습니다.

DBA_HIST_SERVICE_WAIT_CLASS
DBA_HIST_SESS_TIME_STATS
DBA_HIST_SYSTEM_EVENT
DBA_HIST_ACTIVE_SESS_HISTORY
DBA_HIST_BASELINE
DBA_HIST_BG_EVENT_SUMMARY
DBA_HIST_WAITCLASSMET_HISTORY
DBA_HIST_WAITSTAT
DBA_HIST_EVENT_NAME
DBA_HIST_SNAPSHOT






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

Oracle Execution Plan 관련 view 들  (0) 2009.08.18
"A Tour of the AWR Tables" [NOCOUG Summer Conference]  (0) 2009.08.18
Oracle AWR table MAP - SQL 관련  (0) 2009.08.12
oracle awr 설정 시 고려해야 할 점  (0) 2009.08.11
Oracle Enqueue 개요  (0) 2009.08.11

Oracle AWR 관련 table 중 SQL에 관련된 table 들과 reference key를 그린 그림입니다.
다음의 table join으로 과거의 SQL 수행 정보 등을 찾을 수 있습니다. 


아래의 SQL은 Oracle Note에 나온 AWR 수집 데이터에서 SQL 관련 정보를 추출하는 SQL 입니다.

SQL ordered by CPU time:

column sqltext format a65
set pages 60
set lines 132
set trims on

select x.*, dbms_lob.substr(d.sql_text) sqltext
from (
select distinct sql.dbid, sql.sql_id, sum(sql.cpu_time_total)
cpu_total
from dba_hist_sqlstat sql
where sql.snap_id between &begin_id and &end_id
group by
sql.dbid, sql.sql_id
order by
sum(sql.cpu_time_total) desc
) x,
dba_hist_sqltext d
where d.sql_id = x.sql_id
and d.dbid = x.dbid
and rownum <= 10;

SQL ordered by Executions:

column sqltext format a30
select distinct sql.sql_id, dbms_lob.substr(d.sql_text) sqltext, sum(sql.executions_total) from
dba_hist_sqlstat sql,
dba_hist_snapshot s,
dba_hist_sqltext d
where
s.snap_id between &begin_id and &end_id
and rownum <= 10
group by sql.sql_id, dbms_lob.substr(d.sql_text)

SQL ordered by Disk Reads:

column sqltext format a30
select distinct sql.sql_id, dbms_lob.substr(d.sql_text) sqltext, sum(sql.
disk_reads_total) from
dba_hist_sqlstat sql,
dba_hist_snapshot s,
dba_hist_sqltext d
where
s.snap_id between &begin_id and &end_id
and rownum <= 10
group by sql.sql_id, dbms_lob.substr(d.sql_text)


참고 : Note 833323.1 Customized queries from AWR

 

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

"A Tour of the AWR Tables" [NOCOUG Summer Conference]  (0) 2009.08.18
Oracle AWR view MAP - Wait event 관련  (0) 2009.08.12
oracle awr 설정 시 고려해야 할 점  (0) 2009.08.11
Oracle Enqueue 개요  (0) 2009.08.11
삽입을 위한 인트라 파티션 병렬 기능(intra-partition parallelism)  (0) 2009.07.17
100명의 사람들이 있습니다.
이 사람들의 어떤 평균 수치값이 주어져 있습니다.

이 그룹에 주어진 평균 수치가 만약 몸무게나 키, 허리 둘레 등에 대한 평균이라고 한다면,
이 그룹 중 강호동처럼 (몸무게가 어떤지는 잘은 모릅니다^^;) 몸무게가 다른 사람들 보다 많이 나가는 사람을 찾을 수 있을가요? 

물론 평균 수치가 재산, 보유주택 가격 등에 대한 평균이고 이 중 이건희 회장 같은 사람이 있다면 당연히 그 평균값은 거의 한 사람의 값과 같겠죠. 따라서 일반적이지 않은 사람이 섞여있다고 판단할 수 있습니다.

(이 내용은 [블랙스완]이란 책에서 경제학자들의 일반화 오류에 대한 예입니다. 물론 거긴 강호동이나 이건희 회장은 나오지 않습니다만...ㅋㅋ)

database system 내에서 수행되는 많은 AP들이 있습니다.
AWR은 현황에 대한 분석 뿐 아니라 많은 경우 비 정상적으로 문제를 발생하는 AP 등을 찾는데도 자주 사용됩니다.
대부분의 비정상적인 AP는 일반 AP에 비해 2~3배 정도의 resource를 더 많이 사용하죠.

위의 예처럼 대부분의 수치를 차지하는 이건희 회장 같은 AP가 있다면, 이건 계발에 대한 검증이나 운영장비로의 적용 단계에 문제가 있다고 판단해야 합니다 !! 또 AWR을 보지 않아도 찾아내기 쉽죠.
(개인적으로 이건희 회장을 좋아하진 않지만, 뭐 다른 의도가 있는건 아닙니다. 얼마전 뉴스에서 제일 돈이 많다고해서 부럽기도 하고.. 뭐)

서론이 너무 길었지만,
특정 시간대에 문제의 소지가 있는 AP가 있는지에 대한 판단은 그 표본 범위가 작을 수록 더 쉬워지기 마련입니다.
즉 강호동이 10000명 중 하나일 경우와 10명 중 하나일 경우 전체 평균에 기여하는 부분이 달라지죠.

oracle 10g 부터 AWR이 default로 수행됩니다. 1주일의 보관 주기와 1시간의 수집 주기를 가지고 있죠.
하지만 대부분의 제가 본 AWR 주기는 (얼마 보진 않았지만.. --;) default로 설정되어 사용하고 있습니다.

만약 특정 시간동안 hang이 발생했거나 resource contention이 발생했다면 이에 대한 증거들인 database statistic 값들이 1시간이란 시간 안에서 "나 여기 있어요" 하고 말할 수 있을까요?

AWR의 데이터를 한달에 한번씩 수집해서 "이번달엔 이 정도 일했습니다."라고 말할 용도가 아니라면
적어도 15분 ~ 30분 정도의 수집 주기와 AP 수행 주기의 2배 만큼은 보관해야 합니다.
 
또 한가지 AWR에는 base-line을 기록할 수 있습니다.
base-line은 말 그대로 기준선이 됩니다. 이 base-line을 사용하면 "막연히 이번달엔 지난달 보다 좀 느려진것 같아요" 대신 "이번달은 지난달 보다 load가 1.5배 정도 더 발생해 전체적인 속도가 늦어졌어요" 라고 말할 수 있겠죠...

다 쓰고 보니 별 내용이 없군요..
하지만 위의 예제는 꼭 써먹구 싶었습니다... ㅋㅋㅋ

* 현재 AWR 설정 값 확인

select snap_interval, retention from dba_hist_wr_control;

SNAP_INTERVAL       RETENTION
------------------- -------------------
+00000 01:00:00.0   +00007 00:00:00.0

* AWR 설정값 변경

begin
   dbms_workload_repository.modify_snapshot_settings (
      interval => 20,
      retention => 2*24*60
   );
end;

* Base-line 설정

exec dbms_workload_repository.create_baseline (56,59,'apply_interest_1'):

* Base-line 확인

select * from dba_hist_baseline;

      DBID BASELINE_ID BASELINE_NAME        START_SNAP_ID END_SNAP_ID
---------- ----------- -------------------- ------------- -----------
4133493568           1 apply_interest_1                56          59




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

Oracle AWR view MAP - Wait event 관련  (0) 2009.08.12
Oracle AWR table MAP - SQL 관련  (0) 2009.08.12
Oracle Enqueue 개요  (0) 2009.08.11
삽입을 위한 인트라 파티션 병렬 기능(intra-partition parallelism)  (0) 2009.07.17
INTRODUCTION TO PARALLEL DML  (0) 2009.07.17

Enqueue는 오라클에서 사용되는 locking 메커니즘이다.
Enqueue는 동시에 여러 프로세스가 기존의 자원에 대해서 다른 정도(degree)로 공유할 수 있는 방법을 제공한다. 

* enqueue 종류

column enqueue format a150
select EQ_TYPE,EQ_NAME||'('||REQ_REASON||') : '||REQ_DESCRIPTION enqueue  from  V$ENQUEUE_STATISTICS;

EQ ENQUEUE
--------------------------------------------------------------------------------------------------------------------------------------------------------
TA Instance Undo(contention) :Serializes operations on undo segments and undo tablespaces
TX Transaction(contention) :Lock held by a transaction to allow other transactions to wait for it
TX Transaction(row lock contention) :Lock held on a particular row by a transaction to prevent other transactions from modifying it
TX Transaction(allocate ITL entry) :Allocating an ITL entry in order to begin a transaction
TX Transaction(index contention) :Lock held on an index during a split to prevent other operations on it
TW Cross-Instance Transaction(contention) :Lock held by one instance to wait for transactions on all instances to finish
US Undo Segment(contention) :Lock held to perform DDL on the undo segment
SU SaveUndo Segment(contention) :Serializes access to SaveUndo Segment
TT Tablespace(contention) :Serializes DDL operations on tablespaces
IM Kti blr lock(contention for blr) :Serializes block recovery for IMU txn
TD KTF map table enqueue(KTF dump entries) :KTF dumping time/scn mappings in SMON_SCN_TIME table
                                                               :
위의 내용처럼 TX lock의 경우 발생할 수 있는 경우가 4가지가 있다.
transaction contention, row lock contention, ITL allocation, index contention.
한개의 enqueue가 한개의 원인일 꺼란 생각은 금물 !!

Enqueue의 가장 대표적인 예가 테이블에 대한 Lock(TM)이라 할 수 있겠다.
즉 하나의 테이블에 대해서 두개의 프로세스가 share 모드나 share update 모드로 Lock을 잡을 수 있다.

Enqueue는 O/S의 locking 메커니즘을 이용하여 사용자가 요구한 lock의 모드에 관한 정보를 갖고  있고 O/S lock 관리자는 Lock에 걸린 자원를 계속해서 추적한다.　만약 어떤 프로세스가  요구한 Lock 모드가 현재 허용될 수 없다면 O/S는 Lock을 요구하는 프로세스를 wait queue에 넣게 된다.

* Lock monitoring

SQL> select * from v$lock where type not in ('MR');

ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
0000040B410D1DA0 0000040B410D1DC0        879 TS          3          1          3          0      98069          0
0000040B410D1B20 0000040B410D1B40        880 RS         25          1          2          0      98071          0
0000040B410D19E0 0000040B410D1A00        880 CF          0          0          2          0      98074          0
0000040B410D1940 0000040B410D1960        880 XR          4          0          1          0      98074          0
0000040B410D1C60 0000040B410D1C80        881 RT          1          0          6          0      98071          0



Latches 와 Enqueues의 차이는 latch는 wait하기위한 queue의  순서가 없는 반면 enqueue에는 queue의 순서가 있다는 것이다. Latch waiter는 wake up 하기위한 timer를 이용하거나, retry 또는 spin (multiprocessors 환경)을 이용한다. 모든 waiter가 동시에 retry(scheduller dependent)하기 때문에 누구든지 latch를 얻을 수 있고, 어느 경우에는 처음 시도한 프로세스가 가장 나중에 latch를 획득(get) 할 수도 있다.

ENQUEUE_RESOURCES 는 lock manager 에 의해 lock 되는 자원(resource)의 개수를 의미한다. 

이의 초기값은 SESSIONS 파라미터에 의하며, 적절히 부여하기 위해 DML_LOCKS+20 보다 크게 주어야 한다.
예를 들어 3-4 개의 세션(session) 인 경우 default 값은 20 이다.
또 4-10 세션(session) 인 경우 default 값은 ((SESSIONS - 3) * 5) + 20,  
10개 이상의 세션(session)에서는 ((SESSIONS - 10) * 2) + 55 이다.
만일 ENQUEUE_RESOURCES 를 DML_LOCKS + 20 보다 크게 한 경우 그 값이 이용된다.

만일 테이블이 많은 경우 이 값은 증가한다.
이는 한 lock 당 부여되는 것이 아니고, 자원을 사용하는 세션(session)의 개수나 cursor 개수에 관계없이 각 자원(resource) 마다 부여되기 때문이다.

* resource 사용현황

SQL> select * from v$resource_limit;

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION   LIMIT_VALUE
------------------------------ ------------------- --------------- -------------------- --------------------
processes                                      180             477        800                  800
sessions                                       173             470        885                  885
enqueue_locks                                   60              81      10750                10750
enqueue_resources                               60              90       4112            UNLIMITED
ges_procs                                        0               0          0                    0




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

Oracle AWR table MAP - SQL 관련  (0) 2009.08.12
oracle awr 설정 시 고려해야 할 점  (0) 2009.08.11
삽입을 위한 인트라 파티션 병렬 기능(intra-partition parallelism)  (0) 2009.07.17
INTRODUCTION TO PARALLEL DML  (0) 2009.07.17
Oracle Parallel Query Execution  (1) 2009.07.16

인트라 파티션 병렬 기능(intra-partition parallelism)

병렬 실행의 동적 로드 밸런싱 기능의 이점을 활용
내부에서 모두 자동으로 처리
많은 데이타 스큐가 있을 경우 그 진가가 최대한 발휘

Oracle8i의 경우 하나의 테이블 파티션은 병렬 삽입(PDML) 중 오직 하나의 슬래이브 만을 따른다. 이에 따라 파티 션 간의 데이타 스큐가 발생할 경우, 로드 밸런싱은 불가능하게 된다. 예를 들어 여러 애플리케이션에서 테이블은 날 짜 열 범위에 의해 파티셔닝되며 행은 주로 마지막 파티션에 삽입되다고 할 경우를 마지막 파티션에서 운영되는 슬래 이브는 다른 슬래이브에 비해 훨씬 많은 작업을 해야 한다.

Oracle9i는 인트라 파티션 병렬 기능(intra-partition parallelism)을 채용했다.

여러 슬래이브가 하나의 파티션에서 작업하도록 지원할 경우 성능 병목을 완화하는데 도움이 된다.
이는 병렬 실행의 동적 로드 밸런싱 기능의 이점을 활용하는 것이다. 이는 PDML이 지원될 경우 내부에 서 모두 자동으로 처리된다.

인서트 인트라 파티션 병렬 기능(intra-partition parallelism of Inserts)의 이점은 많은 데이타 스큐가 있을 경우 그 진가가 최대한 발휘된다. 삽입된 데이타가 균일할 경우 일반 병렬 삽입에 비해 이점이 거의 없거나 전혀 없다.

샘플 문제:
Operational Data Store 테이블 SALES_ODS의 행이 월별로 분할되는 fact 테이블 SALES_1999 에 삽입되어야 한다. 데이타 배포는 스큐가 되며 아래와 같은 그래프로 나타난다.


구현:
Oracle8i와 Oracle9i, 모두에서 이는 다음과 같이 구현된다.

ALTER SESSION ENABLE PARALLEL DML;

INSERT INTO SALES_1999 /*+ parallel(SALES_1999, 10) */ SELECT * from SALES_ODS;

다음 표는 증가하는 데이타 양에 따른 병렬 삽입의 경과 시간을 비교한 것이다.




데이타 스큐를 증가시켜 1999년 11월에 거의 스파이크에 도달하게 된다면 이보다 더 큰 성능 이점을 기대할 수 있다. Oracle8i의 병렬 삽입의 성능은 스큐의 증가에 따라 저하되지만 Oracle9i를 이용할 경우 그 성능은 일정하 다. 다음 결과는 보다 많은 스큐가 적용된 데이타 분배에 대한 것이다(아래의 배포 그래프 참조).




Oracle9i에서 인서트 인트라 파티션 병렬 기능(intra-partition parallelism of Inserts)은 다음과 같은 이점을 통해 Oracle8i Parallel Insert(PDML) 보다 나은 성능을 발휘할 수 있다.

1. CPU 활용도 확대 - Oracle9i의 경우 이 작업은 Oracle8i와는 달리 거의 전적으로 CPU에 의존한다
2. 보다 효과적인 IO 대역폭 활용 - Oracle9i의 경우 Oracle8i 보다 높은 tps(transaction per second)를 제공한다
3. 다 높은 IO 처리 성능 - Oracle9i의 경우 Oracle8i보다 더 높은 IO 전송 처리 성능을 제공한다


이 POST는 "오라클 기술백서 : Oracle 9i와 DSS 환경에서의 성능과 확장성"의 일부 내용을 발췌한 것입니다.



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

oracle awr 설정 시 고려해야 할 점  (0) 2009.08.11
Oracle Enqueue 개요  (0) 2009.08.11
INTRODUCTION TO PARALLEL DML  (0) 2009.07.17
Oracle Parallel Query Execution  (1) 2009.07.16
Oracle 10G Transaction Rollback Monitoring  (0) 2009.07.14

Parallel DML은 대량의 table/index에 대해 "speed up"과 "scale up"을 위해 insert, update, delete ,merge operation에 parallel execution mechanism을 적용시킨 operation을 말합니다. 즉 일반적으로 말하는 parallel query나 parallel direct-path read 등은 포함되지 않는 개념입니다.

'ParallelExecution'에 해당되는 글 4건

  1. 2009/07/17 삽입을 위한 인트라 파티션 병렬 기능(intra-partition parallelism)
  2. 2009/07/16 Parallel Query Execution
  3. 2009/07/07 10.2 oracle concept - 19 Direct-Path INSERT

Parallel DML은 기본적으로 session에 "enable" 되어 있지 않습니다. PDML과 serial DML의 locking, transaction, disk space requirement 등의 차이에 의해 PDML mode의 "enable"이 요구됩니다.

ALTER SESSION ENABLE PARALLEL DML;

따라서 Parallel DML이 "disable"되있을 경우 parallel hint나 table/index에 degree가 설정되어 있어도 이는 무시되게 됩니다. 물론 PDML mode가 "enable"되어 있어도 parallel hint나 table/index에 대한 degree가 설정되어 있어야 PDML로 수행 가능하다.


한 Transaction은 서로 다른 table에 대해 여러 PDML이 수행될 수 있습니다. 그러나 PDML로 변경된 table에 대해 해당 transaction 내에서 serial/parallel 명령(DML or Query)으로 access를 할 수 없습니다. 즉 commit/rollback 등으로 transaction을 완료 후에야 동일 table에 대한 operation이 가능합니다.
(참조 Note 201978.1 PDML Restrictions on Parallel DML)


Oracle 9i 이후에 intra-partition parallelism 개념이 소개되었습니다.
이 개념은 partition당 한개씩만 수행되는 parallel execution server의 제한을 완화(?) 시키는 개념입니다.
(참고 Note 241376.1 What is Intra-partition parallelism )

특정 세션의 PDML의 "enable" 여부는 v$session의 PDML_STATUS, PDDL_STATUS, PQ_STATUS column으로 확인 할 수 있습니다.

SQL> SELECT SID,PDML_STATUS, PDDL_STATUS, PQ_STATUS FROM V$SESSION;

SID PDML_STATUS PDDL_STATUS PQ_STATUS
---------- ------------ ------------ ------------
141 DISABLED DISABLED DISABLED
143 DISABLED ENABLED ENABLED
145 DISABLED ENABLED ENABLED
148 DISABLED ENABLED ENABLED
150 DISABLED DISABLED DISABLED


이 POST는 metalink note를 참고하여 작성되었습니다.
Note 201457.1- Introduction to PDML




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

Oracle Enqueue 개요  (0) 2009.08.11
삽입을 위한 인트라 파티션 병렬 기능(intra-partition parallelism)  (0) 2009.07.17
Oracle Parallel Query Execution  (1) 2009.07.16
Oracle 10G Transaction Rollback Monitoring  (0) 2009.07.14
Oracle index block split 현상  (7) 2009.07.10

정리:
1. slave 가 disk로 부터 데이터를 읽을 때 buffer cache를 거치지 않는 direct I/O path read를 수행하는데, 작업전 slave는 이미 변경되었으나 disk에 반영되지 않은 데이터를 buffer cache에서 disk로 강제로 flush 한다. 그후 data를 direct path I/O로 읽는다.

2. slave는 consumer slave와 producer slave로 나눌 수 있는데, consumer slave는 추가적인 작업이 필요할 때 수행되며, 이로 인해 예상한 slave 개수보다 더 많은 slave가 작업을 수행할 수 있다.

3. 기본적으로 optimizer가 query를 parallel로 수행하도록 SQL 수행 계획을 생성되더라도 수행시 요구되는 parallel slave를 띄우기 위한 충분한 resource가 없을 경우, 사용자에게 특별한 메세지 없이 serial하게 query를 수행한다. 이러한 경우가 발생하면 query 수행시간이 예상보다 더 늦어지는 일이 발생할 수 있다.

4. serial query가 parallel query보다 더 유리한 경우는 index에 의해 query 조건의 선처리로 대상 데이터량을 줄이는데서 발생한다.


1. Introduction to Parallel Execution

대용량 table에 대한 Full table scan이나 큰 size의 index 생성 등 많은 데이터를 handle할 때 작업들은 여러 process에게 나누어 수행하도록 할 수 있습니다.

이러한 작업 방식을 parallel execution 또는 parallel processing라고 합니다. 

parallel execution은 여러가지 유형의 작업에서 쓸만합니다. 

  • large table scan이나 join 또는 partitioned index scan등의 query 
  • large table이나 index 생성 
  • Bulk insert나 update, delete
  • Aggregations 


2. How Parallel Execution works


(1) 처음 query가 oracle server로 들어오면 query를 분석하는 parse 단계를 거치게 됩니다. 이때 여러개의 access path 중 가장 성능이 좋다고 판단되는 access path가 결정되게 됩니다. parallel execution이 선택되게 되면, 

(2) 수행 단계에서 query를 수행한 user shadow process는 query coordinator(QC)가 되고 parallel slave 들이 요청한 session에 할당됩니다.

(3) query coordinator는 할당된 slave process에 ROWID나 partition 단위로 데이터를 나눠줍니다.

(4) "producer slave"는 데이터를 읽어 "table queue"로 데이터를 보내는데, "consumer slave"나 query coordinator가 데이터 처리를 위해 이 table queue의 데이터를 대기하게 됩니다.

 (5) 만약 sort가 필요한 parallel execution이라면 이들 table queue의 데이터는 "consumer slave process"에 의해 읽혀져 sort 되며,sort 된 데이터는  새로운 "table queue"에 보내지게 됩니다. 이들 sort 된 데이터는 다시 Query coordinator에 의해 읽혀집니다.

만약 sort가 필요 없는 parallel execution이라면 producer slave가 보낸 table queue데이터를 query coordinator가 직접 읽어 처리합니다.

[그림] parallel executions with/without SORT



용어 설명 :

* Query Coordinator(QC)

parallel execution을 수행한 foreground process, 즉 query를 수행한 session으로 query slave로 부터 데이터를 받게 된다. 

* Slaves

slave는 disk로 부터 바로 데이터를 읽거나 다른 slave에 의해 만들어진 table queue 구조로 부터 읽어 그것을 자신의 queue에 write한다.  slave 가 disk로 부터 데이터를 읽을 때 buffer cache를 거치지 않는 direct I/O path read를 수행한다. slave는 이미 변경되었으나 disk에 반영되지 않은 데이터를 buffer cache에서 disk로 강제로 flush 한다. 그후 data를 direct path I/O로 읽는다. 

 slave는 producers slave와 consumer slave의 두개의 종류가 있다. 

 Producers slave는 QC로 부터의 주어진 ROWID range나 partition을 통해 data block을 읽어 관련 데이터를 읽어 온다. 이 데이터는 table queue에 보내지며 이를 다시 QC나 consumer slave가 처리하게 된다.

Consumer slave는 producer slave에 의해 보내진 table queue의 데이터를 처리하여 QC로 dequeue 하게 된다.

consumer slave와 producer slave로 나눠져있기 때문에 sort가 필요한 parallel execution에는 두배의 parallel query slave가 필요하게 된다.

 

* Table Queues

(TQ)는 process가 다른 process에레 row를 보내기 위한 queue이다. Table queue는 producer slave가 consumer slave에게, consumer slave가 query coordinator에게 데이터를 보내기 위해 사용된다.



3. Database Parameter Setup for Parallel Execution


PARALLEL_MAX_SERVERS 
인스턴스 당 최대 사용가능한 slave 개수. 0 설정시 parallel query 사용 불가.

PARALLEL_MIN_SERVERS 
instance startup 시 미리 띄워 놓을 slave 개수.

PARALLEL_MIN_PERCENT 

기본적으로 optimizer가 query를 parallel로 수행하도록 SQL 수행 계획을 생성되더라도 수행시 요구되는 parallel slave를 띄우기 위한 충분한 resource가 없을 경우, 사용자에게 특별한 메세지 없이 serial하게 query를 수행한다. 이러한 경우가 발생하면 query 수행시간이 예상보다 더 늦어지는 일이 발생할 수 있다.

PARALLEL_MIN_PERCENT는 수행시 충분치 못한 resource로 인한 parallel execution이 무시되는 경우를 방지하고 에러를 출력한다. PARALLEL_MIN_PERCENT는 가능한 parallel execution slave의 percent로 설정한다.

만약 설정한 percentage 만큼의 query slave를 띄울 수 없다면 serial로 수행하지 않고 ORA-12827 에러를 발생한다.

예) 만약 resource 부족으로 slave를 띄우지 못했을 경우:

0 에러 없이 serial execution을 수행한다.
50 best parallel execution time의 2배 정도까지의 execution time은 accept하고 에러 없이 수행
100 주어진 parallel query를 수행할 수 있는 resource가 없는 경우 ORA-12827 에러 발생.


OPTIMIZER_PERCENT_PARALLEL 

PARALLEL_ADAPTIVE_MULTI_USER

PARALLEL_ADAPTIVE_MULTI_USER init parameter가 TRUE로 설정되어 있을 경우 parallel execution 사용할 때 multi-user 환경에서의 성능 향상을 위한 algorithm을 사용하게 된다. 이 algorithm은 query가 수행되는 시점에 system load에 따라 자동으로 요청하는 parallel의 degree를 줄여 query를 수행한다. 

예를 들어 17 CPU 시스템에서 default parallel degree가 32로 설정되어 있다면 첫번째 사용자는 32개의 parallel slave process를 사용해 query가 수행된다. 그러나 두번째 사용자가 query를 수행할 경우 16개의 parallel slave process가 사용되며, 세번째 사용자는 8개, ..

결국 32번째 user는 1개의 parallel slave process를 사용하게 된다.

 

PARALLEL_AUTOMATIC_TUNING  new with 8i

다음의 내용은 PARALLEL_AUTOMATIC_TUNING에 의해 영향을 받는 Parallel execution parameter와 변경 방식이다.

Parameter

Default Values 
When parallel_automatic_tuning= 
 FALSE 

Default Values
When parallel_automatic_tuning=
TRUE
parallel_execution_message_size  2KByte 4KByte
parallel_adaptive_multi_user FALSE TRUE
large_pool_size

no effect 

 is incerased based on
a complicated computation using 
various other parameters
processes

 no effect

 if processes < parallel_max_servers
The processes parameter is increased
parallel_max_servers

5

 if parallel_adaptive_multi_user==true
(cpus * parallel_threads_per_cpu * 
_parallel_adaptive_max_users * 5)
else 
(cpus * parallel_threads_per_cpu *
_parallel_adaptive_max_users * 8)


4. Parallel Execution Performance

Parallel query의 수행은 performance 상의 이점을 얻을 수 있으나 parallel queyr를 수행하기 앞서 몇가지 고려할 만한 사항이 있다.

multi-slave process는 당연한 얘기지만 single process 보다 많은 CPU resource와 slave process 각각의 private memory를 사용하게 된다. 만약 CPU 자원이 부족하게 되면 oracle은 parallel operation을 serial operation으로 변경해 작업을 수행한다. 따라서 parallel execution은 현재 system의 resource 상태를 고려해 parallel degree를 고려해야 한다.

또 I/O stress가 많은 시스템이라면 slave process에 의한 추가적인 I/O요구가 부담이 될 수 있습니다.
특히 I/O가 특정 disk에 집중된다면 disk I/O의 bottleneck이 발생할 수 있으므로 I/O의 분산 등도 고려되어야 한다.

parallel query는 Full Table Scan으로 데이터를 처리한다. 따라서 index의 사용이 유리한 경우에는 오히려 parallel execution의 성능이 더 나쁠 수 있다. 

이러한 성능의 차이는 index에 의해 query 조건의 선처리로 대상 데이터량을 줄이는데서 발생한다.

  •  Nested Loops vs. Hash/Sort Merge Joins
    Nested loop join의 경우 query 조건에 의한 "row elimination"으로 driviing table의 대상 row를 줄이기 때문에 FTS 보다는 index scan에 적합하다. 반면 Hash join과 sort merge의 경우 일반적으로 대량의 데이터를 처리하는데 더 효과적이다. 이는  Hash join과 Sort Merge join은 driving row source에 대해서 조건에 의한 데이터의 "row eliminate"를 하지 않기 때문이다. 
  •  slave process를 생성하고, data를 분할하고, 여러 slave process로 데이터를 전송하고 결과를 취합하는 등의 비용이 data를 serial하게 처리하는 것보다 많을 수 있다.
  •  Data skew
    parallel query는 데이터를 ROWID range를 기본으로 slave process 간에 할당한다. 각각의 slave에게 같은 개수의 block을 할당한다는 것은 같은 수의 row를 할당한다는 말과는 다른 의미이다. 예를 들어 대량의 데이터가 수집되고 삭제되는 업무의 경우 특정 블록들에는 데이터가 전혀 들어 있지 않을 수 있다. 이러한 균등하지 않은 데이터 분할로 인해 특정 slave query의 성능이 늦어질 수 있으며 이는 전체 PQ 처리 시간에 영향을 미치게 된다.


이 내용은 metalink.oracle.com의 note를 참조했습니다.

Note 203238.1 - Using Parallel Execution



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


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

삽입을 위한 인트라 파티션 병렬 기능(intra-partition parallelism)  (0) 2009.07.17
INTRODUCTION TO PARALLEL DML  (0) 2009.07.17
Oracle 10G Transaction Rollback Monitoring  (0) 2009.07.14
Oracle index block split 현상  (7) 2009.07.10
SQL*NET Trace 설정하기  (0) 2009.07.10

10G 이전에는 v$fast_start_servers, v$fast_start_transactions 두개의 view를 통해 parallel transaction recovery를 모니터링 할 수 있었습니다. 그러나 일반적인 transaction rollback이나 SMON에 의한 transaction recovery는 모니터링 할 수 없었죠.

그러나 10G에서는 transaction rollback monitoring 기능의 향상으로 일반적인 transaction rollback 작업과 SMON에 의한 transaction recovery에 대한 모니터링이 가능해 졌습니다. v$fast_start_servers, v$fast_start_transactions 두개의 view는 transaction recovery와 transaction rollback작업에 관한 historical 정보까지 갖고 있어 transaction recovery 관련되어 평균 rollback duration을 확인할 수 있습니다.

추가적으로 일반적인 transaction recovery 시간의 산정이 가능해졌으며, system performance에 맞춘 FAST_START_PARALLEL_ROLLBACK init parameter 설정도 가능합니다.

v$fast_start_transaction 의 STATE column은 RECOVERED, RECOVERING 등의 상태를 instance가 shutdown 될때까지 갖고 있어 현재 transaction 상태를 갖고 있으며, 작은 transaction은 보이지 않을 수 있습니다.

Example:
SELECT state,undoblocksdone,undoblockstotal,cputime FROM v$fast_start_transactions;

STATE         UNDOBLOCKSDONE        UNDOBLOCKSTOTAL CPUTIME
---------- -------------- --------------- ---------
RECOVERING     574                    1945             16
…
SQL> /
STATE         UNDOBLOCKSDONE         UNDOBLOCKSTOTAL CPUTIME
---------- -------------- --------------- ---------
RECOVERING     1300                     1945             34
…
SQL> /
STATE        UNDOBLOCKSDONE         UNDOBLOCKSTOTAL CPUTIME
---------- -------------- --------------- ---------
RECOVERED     1945                     1945              65

oracle metalink Note 265198.1를 참조했습니다.

Note 265198.1 - Oracle 10G Transaction Rollback Monitoring





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

INTRODUCTION TO PARALLEL DML  (0) 2009.07.17
Oracle Parallel Query Execution  (1) 2009.07.16
Oracle index block split 현상  (7) 2009.07.10
SQL*NET Trace 설정하기  (0) 2009.07.10
Bulletin no : 12060 SQL*NET V2 최적화하기 [펌]  (2) 2009.07.08

Block split은 새로운 index key가 들어왔을때 기존에 할당된 block내에 저장할 영역이 없어 새로운 block을 할당 받는 index segment관련 operation 입니다.

Index Block Split은 새로 들어오는 index key 데이터에 따라 2개의 다른 방식으로 이루어 집니다.


1. index key 값이 기존의 index key 값에 비해 제일 큰 값이 아닐 경우 50/50  
   block split이 발생한다. 50/50 split은 기존에 존재하던 old block과 새로
   만들어진 new block에 50%의 데이터씩 채워져 split이 발생하게 된다.

2. index key 값이 기존의 index key 값에 비해 제일 큰값이 들어올 경우 99/1
   block split 이 발생한다. 99/1 split은 기존에 존재하던 old block에 99%의
   데이터가 있고 new block엔 새로운 데이터가 저장되게 된다.

이러한 index key의 저장 영역의 확인은 "analyze .. validate structure" 수행 후 index_stats view의 조회를 통해 확인 할 수 있습니다.

다음의 예는 간단한 테스트 입니다.

1. 계속 증가되는 데이터에 의해 99/1 split이 발생하는 예제 입니다.

SQL> drop table t1;
SQL> create table t1 (name varchar2(10),nr number) pctfree 0;
SQL> create index i1 on t1(nr);
SQL> declare
     i number;
     begin
     for i in 1..50000
     loop
     insert into t1 values('XX',i);
     end loop;
     end;
/

SQL> analyze index i1 validate structure;
SQL> select blocks, lf_blks, pct_used from index_stats;

Results:

BLOCKS: 104 LF_BLKS: 99 PCT_USED: 99


2. 데이터의 입력 순서를 바꿔 50/50 split이 발생하는 예제입니다.

SQL> drop table t2;
SQL> create table t2 (name varchar2(10),nr number) pctfree 0;
SQL> create index i2 on t2(nr);
SQL> declare
     i number;
     begin
     for i in 25000..50000
     loop
      insert into t2 values('XX',i);
     end loop;
     for i in 1..25000
     loop
      insert into t2 values('XX',i);
     end loop;
     end;
/

SQL> analyze index i2 validate structure;
SQL> select blocks, lf_blks, pct_used from index_stats;

Results:

BLOCKS: 256 LF_BLKS: 146 PCT_USED: 68

 마지막의 blocks의 결과를 보시면 데이터의 입력 순서에 따라 데이터의 저장영역이 2배 이상 차이가 나는 것을 확인 할 수 있습니다.

이거 보시고 99/1로 index split을 하면 저장역역을 아낄 수 있겠지만,
예제1의 t1이 OLTP에서 여러 세션에 의해 insert 되는 table의 index라면
t1에 대한 insert 세션들이 index split을 기다리는 "enq: TX - index contention"라는
block contention event를 오랫동안 만나실 수도 있습니다. ㅎㅎ

이 내용은 metalink note 183612.1을 참고했습니다.

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

Oracle Parallel Query Execution  (1) 2009.07.16
Oracle 10G Transaction Rollback Monitoring  (0) 2009.07.14
SQL*NET Trace 설정하기  (0) 2009.07.10
Bulletin no : 12060 SQL*NET V2 최적화하기 [펌]  (2) 2009.07.08
Listener 접속 단계 및 성능 측정  (0) 2009.07.07
요 몇일은 listener 관련해서 알아볼일이 많네요.
마지막으로 trace 설정하는 방법입니다.

Bulletin no : 12060 SQL*NET V2 최적화하기 [펌]

Listener 접속 단계 및 성능 측정




client쪽에 trace 설정 방법입니다.

TRACE_LEVEL_CLIENT = 0
TRACE_DIRECTORY_CLIENT = $ORACLE_HOME/network/trace (UNIX)
TRACE_FILE_CLIENT = client

server쪽에 trace 설정 방법입니다.

TRACE_LEVEL_SERVER = 0
TRACE_DIRECTORY_SERVER = $ORACLE_HOME/network/trace (UNIX)
TRACE_FILE_SERVER = SERVER

listener re-start 나 reload가 필요합니다...

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

Oracle 10G Transaction Rollback Monitoring  (0) 2009.07.14
Oracle index block split 현상  (7) 2009.07.10
Bulletin no : 12060 SQL*NET V2 최적화하기 [펌]  (2) 2009.07.08
Listener 접속 단계 및 성능 측정  (0) 2009.07.07
Oracle's Dev2DBA newsletter - 2009/07  (0) 2009.07.07
OTN의 sql*net 최적화 하는 방법에 대한 bulletin 입니다.
나온지 꽤 지난 문서 같지만 엇그제 포스팅한 Listener 접속 단계 및 성능 측정 와 연관된 내용이라
같이 올립니다.


Bulletin no : 12060  SQL*NET V2 최적화하기


1) PING
TCP/IP 네트워크상에서 ping을 사용해서 client와 server간에 걸리는 시간을 check할 수 있다.
만일 이 시간이 오래 걸리면 SQL*Net 보다 이 문제를 먼저 해결해야 한다.

사용 방법 :
ping 호스트이름
(NT의 경우에는 dos command상태에서)

2) TNSPING
이 tool은 기본적으로 설치가 되어 있으며 이 tool을 가지고 user가 client에 setting한 TNS alias(tnsnames.ora 파일안에 설정)
가 정상적으로 동작하는지를 테스트해 볼 수 있습니다.
TNSPING은 접속하고자하는 database가 있는 machine의 listener에 접속을하고 걸리는 시간을 miliseconds로 표시해 줍니다.
(실제 db와 connection을 맺는 것은 아닙니다.)

사용 방법 :
tnsping TNSalias이름
(NT의 경우에는 dos command상태에서)

3) 모든 logging 과 tracing 막기
Tracing 은 client와 server에 모두 가능하게 할 수 있습니다. 다음 parameter를 SQLNET.ORA파일과 LISTENER.ORA파일 ( $ORACLE_HOME/network(또는 net80)/admin에 위치합니다 )
에 setting하고 listener를 restart (lsnrctl stop, lsnrctl start) 하면 SQL*Net의 모든 tracing을 막을 수 있습니다.

   SQLNET.ORA:
   -----------
   TRACE_LEVEL_CLIENT =OFF
   TRACE_LEVEL_SERVER =OFF
   TNSPING.TRACE_LEVEL=OFF

   'OFF'대신에 '0'을 사용해도 됩니다.

   LISTENER.ORA:
   -------------
   TRACE_LEVEL_LISTENER=OFF
   LOGGING_LISTENER=OFF

4) Listener log 파일들 지우기
만 일 listener의 logging이 설정되어 있는 상태라면 LISTENER.LOG 파일이 이 생깁니다. listener는 connection이 맺어질대 마다 이 파일에 lock을 걸고 write하기 때문에 size가 계속 증가하게 되어 문제가 생길 수
있습니다. 만일 LISTENER.LOG 파일의 size가 너무 크게 되면 rename을 하시기 바랍니다. 그리고 listener를 restart하면 새로운 log file이 만들어 집니다.

5) SQLNET.ORA에 AUTOMATIC_IPC를 OFF로 설정
   AUTOMATIC_IPC = { ON | OFF }
위 parameter는 "SQLNET.ORA"파일에 설정할 수 있으며 ON으로 되어 있는경우 SQL*Net이 같은 alias정보를 가진 local database가 있는지 check하게 됩니다.
만일 local database가 있다면 connection은 network layer를 건너뛰고 local 'Inter Process Communication'(IPC)  connection을 맺게 됩니다.
따라서 이 setting은 database server쪽에 사용할 수 있는 것이지 client machine SQL*Net에는 아무 쓸모 없습니다.
database server쪽에 사용하더라도 local database에 SQL*Net connection이 반드시 필요한 경우가 아니라면 사용하시 않는 것(OFF로 설정)이 좋습니다.

6) SQLNET.ORA에 NAMES.DIRECTORY_PATH 설정
   NAMES.DIRECTORY_PATH = (ONAMES,TNSNAMES)
이 parameter는 TNS aliases를 찾는 경로를 지정할때 사용합니다. Oracle*Names가 설정이 안되어 있는 경우 ONAMES을 지우시는 것이 좋습니다.


7) SDU와 TDU
SDU('Session Data Unit')는 네트워크를 통해 보내는 packet의 size입니다. 이 size는 MTU(Maximum Transmission Unit)를 넘어서는 안됩니다. MTU는 네트워크상에 고정된 값입니다.
TDU('Transport Data Unit')는 SQL*Net이 data를 함께 묶는 기본 packet size 이며 SDU의 배수여야 합니다.

다음에서 예를 들어 보겠습니다.
   * SDU=1024, TDU=1536:

SQL*Net은 buffer에 1536 byptes까지 저장했다가 네트워크로 보냅니다. 낮은 network layer에서 이것을 다시 두개의 physical packets(1024,512 bytes)로 나누어 보냅니다.

   * SDU=1514, TDU=1000:
SQL*Net은 buffer에 1000 byptes까지 저장했다가 네트워크로 보냅니다. SDU는 request당 514 bytes를 더 담을 수 있는데도 불구하고 보내지기 때문에 network resource의 낭비를 초래합니다.

표준 Ethernet network에서 MTU의 default size는 1514 bytes입니다.
표준 token ring network에서 MTU의 default size는 4202 bytes입니다.

SDU와 TDU를 설정하려면 TNSNAMES.ORA 과 LISTENER.ORA 를 다음과 같이
바꾸어야 합니다.

   TNSNAMES.ORA:
   -------------
   ORCL.WORLD =
      (DESCRIPTION =
         (SDU=1514)
         (TDU=1514)
         (ADDRESS =
            (PROTOCOL = TCP)
            (HOST = fu.bar)
            (PORT = 1521)
         )
         (CONNECT_DATA = (SID = ORCL))
      )

   LISTENER.ORA:
   -------------
   SID_LIST_LISTENER =
      (SID_LIST =
         (SID_DESC =
            (SDU = 1514)
            (TDU = 1514)
            (SID_NAME = ORCL)
            (GLOBAL_DBNAME = ORCL.WORLD)
         )
      )

SDU와 TDU는 modem을 사용하는 환경에서는 줄여주는 것이 좋고 fiber나 T3 line을 사용하는 환경에서는 늘려주는 것이 좋습니다.
SDU와 TDU의 default값은 2048이고 maximum값은 32768입니다.


8) PROTOCOL.ORA의 tcp.no_delay 설정
기본적으로 SQL*Net은 SDU buffer가 찰때가지 request를 전송하지 않고 기다립니다. 다시 말해 request가 도착지점으로 바로 전송되지 않는 다는것을 의미 합니다.
그 런데 'no_delay'를 설정함으로써 data buffering을 하지 않게 할 수 있습니다. 따라서 'no_delay'를 설정하게 되면 작은 size의 patckets의 전송이 늘게되어 network traffic이 증가하게 됩니다.
따라서 이 parameter는 TCP timeout이 발생했을 경우에만 사용하셔야 합니다.

9) LISTENER.ORA의 QUEUESIZE 설정
QUEUESIZE는 listener가 저장할 수 있는 request의 수를 의미 합니다. 만일 들어오는 reqeusts의 수가 buffer의 size를 넘게 되면, 접속을 시도한 client는 접속을 실패하게 됩니다.
이 buffer의 size는 예상되는 동시 접속 수를 설정해 주는 것이 좋습니다.

   LISTENER =
     (ADDRESS_LIST =
           (ADDRESS =
             (PROTOCOL = TCP)
             (HOST = fu.bar)
             (PORT = 1521)
             (QUEUESIZE = 32)
           )
     )

이 parameter는  TCP/IP나 DECNET protocol이 사용될때만 사용됩니다.

10) SQLNET.ORA의 BREAK_POLL_SKIP 설정
이 parameter는 user break을 check하는 사이의 packet수를 지정합니다. 다시 말해 만일 이 parameter의 값이 높으면 CTRL-C checking은 덜 자주 일어나게되며 CPU overhead는 줄게 됩니다.
만일 이 parameter의 값이 낮으면 CTRL-C checking이 자주 발생되어 CPU overhead가 늘게 됩니다. 기본값은 4이며 client SQL*NET에만 사용됩니다.

11) SQLNET.ORA의 DISABLE_OOB 설정
Out of band break check를 enable시키거나 disable시킬때 사용하는 parameter입니다.
기본값은 off입니다.

여기서 잠간만 !
Out of Band Breaks란 무엇인가 ?
네트워크 통신상에서 받아들여지는 interrupt signals은 일반적으로 다른 data(예를 들어 select 문장)과 같이 도착하게 됩니다.
이것을 In-band Breaks라고 합니다. 그런데 이 interrupt signals을 connection과는 다른 channel을 통해 전달할 수 있습니다 이것을 Out of Band Breaks라고 하며 이 방식은
interrupt signal을 훨씬 빠르게 그리고 효과적으로 전달 할 수 있습니다. (예를 들어 deadlock을 break하기위해 control-C를 사용하는 것)

12) PROCESS.DAT와 REGID.DAT
7.3.2 버전에서 Oracle Server Tracing은 기본적으로 enabled되어 있습니다. 따라서 모든 connection과 request가 PROCESS.DAT와 REGID.DAT에
기록이됩니다. database의 사용기간이 길어지면 이 파일들은 접속속도를 현저히 떨어뜨리게 됩니다.
이러한 trace 파일들을 사용하지 않기위해 listener.ora파일에 'EPC_DISBLED=TRUE'를 설정해야 합니다.



Oracle Korea Customer Support Technical Bulletins



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

Oracle index block split 현상  (7) 2009.07.10
SQL*NET Trace 설정하기  (0) 2009.07.10
Listener 접속 단계 및 성능 측정  (0) 2009.07.07
Oracle's Dev2DBA newsletter - 2009/07  (0) 2009.07.07
10.2 oracle concept - 19 Direct-Path INSERT  (0) 2009.07.07

다음의 내용은 listener 접속시 지연현상에 대해 분석할 만한 방법에 대한 내용입니다.
요건 oracle metalink Note 214022.1를 참조한 내용입니다.

우선 SQLNET 관련 performance를 측정하기 위해서는 우선 sqlnet trace를 설정하고 그에 따라 소요 시간을 측정해야 합니다.

client에서 server side의 listener로 접속이전에 client 내의 connect descriptor에 대한 해석이 먼저 이루어 져야 합니다. oracle net trace file내에서 이 시점을 측정하기 위해서는 trace file이 생성된 시점과 "niotns: setting up interrupt handler"가 나오는 부분까지로 보시면 됩니다.

일반적으로 connect descriptor는 tnsnames.ora에 기술하여 사용합니다만.. 가장 빠른 방법은 command line에 다음과 같이 기술하는 방법입니다.

sqlplus scott/tiger@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS= / (PROTOCOL=TCP)(HOST=kareardo-pc)(PORT=1521))) / (CONNECT_DATA= SERVICE_NAME=iasdb.kareardo_pc.us.oracle.com)))

이 방법은 AP에 hard coding이 필요하므로 권장되는 방법은 아니지만, 성능 측정에서는 사용할만 합니다.

다음 방법은 잘 알려진 tnsnames.ora에 connect descriptor를 기술하는 방법입니다. 이때 NAMES.DEFAULT_DOMAIN 확장없이 속도를 측정해 볼 수 있습니다.

sqlplus scott/tiger@iasdb

 다음은 NAMES.DEFAULT_DOMAIN 확장을 사용한 방법입니다.

sqlplus scott/tiger@iasdb.us.oracle.com

 여기까지로 connect descriptor의 해석 과정을 측정해 볼수 있습니다.


또한가지 client에서 connect descriptor를 network protocol location이나 database service name으로 변경하는 단계에 영향을 줄 수 있는 것이 하나 더 있습니다. 그게 NAMES.DIRECTORY_PATH 입니다.

oracle net은 sqlnet.ora 내에 정의된 NAMES.DIRECTORY_PATH에 기술된 여러 방법을 참고해 connect descriptor를 가져오게 됩니다. 물론 default는 tnsnames.ora를 확인하는 방법이죠.

만약 oracle internet directory 등의 external naming method를 사용할 경우 external naming source에 대한 network round-trip이 필요합니다. 이러한 external naming source에 대한 속도는 network bandwidth나 system resource 등의 많은 부분에 영향을 받을 수 있습니다.

이러한 변수를 줄이기 위해서 oracle internet directory server가 위치한 동일 시스템에서 다음의 테스트를 진행해 볼수 있습니다.

Oracle Net이 connect descriptor를 network protocol-specific 정보로 변환하였다면 이제 드디어 oracle listener로 접속을 하게 됩니다.

일반적인 Network protocol은 응답이나 처리에서 오류가 발생할 경우를 대비한 응답 mechanism을 가지고 있습니다. 또 packet 실패의 경우 해당 packet을 재전송하는데, 이러한 network lose나 drop 등 network protocol 요소에 대한 tuning이 우선되어야 합니다.
(network protocol은 아는게 전혀 없으므로 통과 ~)
network protocol이 oracle listener로 접속된 이후에 listener는 여러가지 작업을 수행하는데, 그 중 부하가 높은 부분 중 하나가 server process를 생성하는 작업입니다.

일반적으로 process의 생성은 많은 메모리와 CPU, 시간을 소모하죠. 따라서 server process가 미리 생성되어 있다면 속도향상을 꾀할 수 있습니다. 이러한 환경이 pmon에 의해 미리 띄워지는 shared server 환경이죠..
그러나 요즘 shared server 환경은 많이 사용하지 않으므로 생략 합니다.
(왠지 날로 먹는 듯한... --;)

여기까지는 환경 설정 단계에서 잘설정하고 network에 큰 문제가 없으면 굳이 문제될 부분은 아닙니다만 주로 문제는 listener에 대한 동시 접속이 몰리는 경우에 발생합니다. 이런 경우 listener에 동시에 접속할 수 있는 queue 설정과 multi-listener를 설정합니다.

queue size의 경우 listener가 동시에 처리할수 있는 request 수를 지정해 줍니다. 이 값은 각 protocol의 설정값에 따라, system에 따라 달라지므로 적정값을 확인해 봐야 겠죠. multi-listener의 설정은 multi-cpu 환경에서 listener 가용성을 키우는 좋은 방법입니다.

   LISTENER =
     (ADDRESS_LIST =
           (ADDRESS =
             (PROTOCOL = TCP)
             (HOST = marvin)
             (PORT = 1521)
             (QUEUESIZE = 32)
           )
     )



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


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

SQL*NET Trace 설정하기  (0) 2009.07.10
Bulletin no : 12060 SQL*NET V2 최적화하기 [펌]  (2) 2009.07.08
Oracle's Dev2DBA newsletter - 2009/07  (0) 2009.07.07
10.2 oracle concept - 19 Direct-Path INSERT  (0) 2009.07.07
sequence cache & pining  (0) 2009.06.30

19 Direct-Path INSERT


Introduction to Direct-Path INSERT

오라클은 table에 데이터를 입력하는데 두가지 방식을 사용합니다.


  • 전통적인 insert operation은 table내의 free space를 재사용합니다. 기존의 데이터와 새로 insert 되는 데이터가 같이 저장되며, 참조 constraint도 자동으로 관리합니다.
  • direct-path insert operation은 새로 입력되는 데이터를 기존의 table내에 존재하는 데이터 이후에 저장합니다. buffer cache를 거치지 않고 바로 datafile에 저장되며, table내의 free space를 사용하지 않습니다. 또한 참조 constraint도 무시됩니다. 이러한 일련의 procedure로 인해 performance가 향상됩니다.

이러한 direct-path insert operation은 direct-path insert 구문이나 sql loader의 direct-path option을 이용해 사용될 수 있습니다. 


Advantages of Direct-Path INSERT

Direct-path insert는 다음의 performance 잇점을 갖습니다.


  • direct-path insert 시 redo와 undo entry들의 logging을 disable 할 수 있습니다. 반면 전통적인 insert operation은 free space 사용과 참조무결성을 위해 반드시 이러한 entry들에 대해서 logging을 해야 합니다.
  • 새 로운 데이터가 저장된 테이블을 생성하기 위해서 create table과 insert into를 사용하거나 create table .. as select 구문을 사용할 수 있습니다. create table 과 direct-path insert를 이용해 데이터를 insert 한 경우 기존 테이블에 존재하는 인덱스도 자동으로 변경됩니다. 반면 create table as select 구문을 사용한 경우 insert는 추후 재생성해야 합니다.
  • Direct-path insert는 serial/parallel mode 둘다 transaction이 하나의 단위로 처리된다 (transaction atomicity). 그러나 sql loader의 parallel-patch load의 경우 transaction atomicity를 보장하지 않는다.
  • parallel direct-path load의 경우 에러가 발생하면 index는 load 후 UNUSABLE로 빠질 수 있다. 반면 parallel direct-path INSERT 구문의 경우 index update시 에러가 발생하면 rollback 된다.

Serial and Parallel Direct-Path INSERT

parallel DML mode에서 insert 할 경우 direct-path INSERT가 default 이다. parallel DML mode로 사용하기 위해서 다음의 요구사항이 필요하다.


  • Oracle Enterprise Edition을 사용해야 한다.
  • ALTER SESSION { ENABLE | FORCE } PARALLEL DML; 명령으로 parallel DML 기능을 enable 해야한다. 
  • table 생성시 또는 그 이후 table에 대한 parallel attribute를 지정하거나 insert 구문내에 parallel hint를 지정한다.


direct-path INSERT를 disable 하기 위해 NOAPPEND hint를 insert 구문에 지정해 줘야 한다.

serial mode로 insert 할때 INSERT 구문 뒤에 APPEND hint를 사용하거나 INSERT 구문 내의 subquery의 SELECT 구문 바로 뒤에 APPEND hint를 지정해 줘야 한다.

Direct-Path INSERT Into Partitioned and Nonpartitioned Tables

Direct-path INSERT는 partition/non-partition table 모두 사용가능하다.

Serial Direct-Path INSERT into Partitioned and Nonpartitioned Tables

Single process insert의 경우 table segment나 각 partition segment의 HWM 뒤에 데이터가 insert 된다.

commit이 수행되면 HWM는 새로운 값으로 update 되며 user에게 데이터가 보이게 된다.

Parallel Direct-Path INSERT into Partitioned Tables

Partition table에 대한 parallel direct-path insert는 serial direct-path insert와 유사하다.

각각의 parallel execution server는 하나 또는 여러 partition에 할당된다. 하나의 partition에 대해 한개 이상의 parallel execution server process가 할당되진 않는다.

(Each parallel execution server is assigned one or more partitions, with no more than one process working on a single partition.)

각 parallel execution server는 할당된 partition segment의 HWM 이후에 데이터를 insert 한다.

commit이 수행되면 HWM는 update 되며 새로운 데이터는 user에게 보이게 된다.

Parallel Direct-Path INSERT into Nonpartitioned Tables

Non-partition table에 대한 parallel direct-path insert는 각각의 parallel execution server가 temporary segment를 만들며 이 temporary segment에 데이터를 insert 한다.

commit이 수행되면 parallel execution coordinator가 temporary segment를 table segment로 merge 한다.

Direct-Path INSERT and Logging Mode

Direct-path INSERT는 redo entry와 undo entry를 logging 할지를 선택 할 수 있다.


  • table, partition, index, LOB storage에 대해 생성시나 추후 logging mode를 설정할 수 있다. 
  • 만약 LOGGING이나 NOLOGGING을 설정하지 않았다면..
    • partition 의 경우 해당 table의 logging attribute를 따른다.
    • table이나 index의 경우 tablespace의 logging attribute를 따른다.
    • LOB storage의 경우 만약 CACHE option으로 정의 된 경우 LOGGING이 default attribute로 설정된다. 만약 CACHE option이 지정되지 않은 경우 해당 tablespace의 logging attribute를 따른다.
  • CREATE TABLESPACE 나 ALTER TABLESPACE 명령으로 logging attribute를 지정할수 있다.


Direct-Path INSERT with Logging

logging mode에서 direct-path insert가 수행될 경우 oracle은 full redo logging을 수행한다.


Direct-Path INSERT without Logging

NOLOGGING 으로 Direct-path insert 할 경우 oracle은 redo entry와 undo retry를 logging 하지 않습니다.

그러나 새로운 extent를 invalid로 marking하기 위한 최소한은 log와 data dictionary 변경에 대한 내용은 logging 합니다.

NOLOGGING은 performance를 증가시키지만 media recovery가 필요한 경우 redo 정보가 없기때문에 변경된 데이터에 대해 logical corruption을 발생하게 됩니다. 

따라서 NOLOGGING mode에서 direct-insert 한 경우 반드시 backup이 필요합니다.


Additional Considerations for Direct-Path INSERT

Index Maintenance with Direct-Path INSERT

오라클은 direct-patch INSERT 이후 table의 index에 대한 변경 사항을 적용합니다.

parallel direct-path INSERT의 경우 parallel execution server에 의해, serial direct-path INSERT의 경우 single process에 의해 수행됩니다.

Index 관리에 따른 performance 저하를 피하기 위해 insert 수행 전 index를 drop하고 rebuild 할 수 있습니다.

Space Considerations with Direct-Path INSERT

direct-patch INSERT의 경우 table의 free-list내의 공간을 사용하지 않기때문에 기존의 INSERT 방식에 비해 많은 공간을 요구하게 된다. 

serial direct-path INSERT와 partition table에 대한 parallel direct-path INSERT의 경우 해당 segment의 HWM 이후에 데이터를 저장하게 됩니다. 

이로 인해 추가적인 공간이 더 필요하게 된다. 


non-partition table에 대한 parallel direct-path INSERT의 경우 각각의 parallel degree 만큼의 temporary segment가 생성되어 추가적인 공간이 더 필요하다.

만약 non-partition table이 locally managed tablespace내에 automatic mode로 생성되지 않았다면 NEXT, PCTINCREASE, MININUM EXTENT 등의 storage parameter를

적정한 수치로 지정해 주어야 한다.


  • 각각의 extent size가 너무 작을 경우 object의 extent 갯수에 영향을 미친다.
  • parallel insert 결과가 segment내의 공간을 낭비하지 않도록 적당한 extent size를 갖어야 한다.


direct-path insert 이후 이들 storage parameter를 serial operation에 맞도록 다시 설정한다.

Locking Considerations with Direct-Path INSERT

direct-path INESRT 시 해당 table에 대한 exclusive lock이 요구된다. 따라서 다른 user들은 해당 table에 대해 insert, update, delete, index create/build operation이 제한된다. 

데이터에 대한 query는 지원되며 direct-insert 이전의 데이터만 보여진다.




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

Listener 접속 단계 및 성능 측정  (0) 2009.07.07
Oracle's Dev2DBA newsletter - 2009/07  (0) 2009.07.07
sequence cache & pining  (0) 2009.06.30
오라클의 에러  (0) 2009.06.25
Cluster I/O Fencing – exclusion strategy  (0) 2009.06.23 

oracle sequence는 순차적으로 특정 interval로 번호를 부여해 주는 object 입니다. 

sequence 생성시 cache option을 줄수 있는데, 이는 지정한 갯수의 sequence를 미리 
일정 부분 생성해 library cache영역에 저장해 두는 기능을 합니다. 

cache option을 사용하지 않을 경우 매번 next value를 참조할 때 disk I/O를 발생하게 
됩니다. 또 sequence관련 row cache lock이 발생하는 경우도 있죠. 

이러한 현상을 완화 시키기 위해 자주 사용되는 sequence에 대해서는 cache 기능을 부여하게됩니다. 

alter sequence seq cache 100;

그러나 sequence 생성시 cache 기능을 사용할 경우  "cache aging out"과 "db re-start" 
event로 인해 sequence number가 skip 될 수 있습니다. 더구나 sequence cache는 oracle 7.2 이전엔
row cache에 저장되었으나 7.3 이후 library cache에 저장되어 더 자주 aging-out 현상이 발생하게 
됩니다. 

이러한 현상을 최소화하기 위해 sequence를 pining 할 수 있습니다. 
sequence pin은 (물론 다른 object도 마찬가지지만) dbms_shared_pool package의 keep procedure를 
이용할 수 있습니다. 이 package는 default로 설치되지 않으며, dbmspool.sql 수행으로 설치 할 수 있습니다. 

dbms_shared_pool.keep('seq', 'Q'). 

참조 : oracle metalink Note 62002.1 Caching Oracle Sequences

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

Oracle's Dev2DBA newsletter - 2009/07  (0) 2009.07.07
10.2 oracle concept - 19 Direct-Path INSERT  (0) 2009.07.07
오라클의 에러  (0) 2009.06.25
Cluster I/O Fencing – exclusion strategy  (0) 2009.06.23
Sample code - sqlplus : demobld.sql  (0) 2009.06.09

오라클의 에러는 "ORA-XXX"의 일정한 형식을 갖고 있습니다.

물론 listener관련에러는 "TNS-XXX"라고 나오긴 하지만 일단 database에서 발생하는 에러만 이야기 하겠습니다.

가끔 DB 운영자에게 end-user나 개발자들이 ORA-xxx라는 에러가 나는데, 이게 뭐에요? 하고 물어 오는 경우가 있죠. 경험이 많은 운영자나 해당 에러를 많이 본 운영자라면 "그건 이러이러해서 나오는 에러야" 라고 말할 수 있습니다.

그러나 아무리 뛰어난 운영자나 심지어 오라클 엔지니어도 모르는 에러가 있죠.


그게 ORA-600에러와 ORA-7445에러 입니다.

ORA-600과 ORA-7445에러는 비정상적인 exception 발생을 통칭하는 에러입니다. 따라서 ORA-600, ORA-7445라는 에러 번호만으로는 아무것도 알 수 없죠.

그러면 이 두개의 exception 에러 분석은 어떻게 할까요?
이 두개의 에러는 뒤에 관련 argument들을 보여 줍니다. 각각의 argument는 에러가 발생한 위치와 에러의 원인 등 여러가지 정보를 보여 줍니다.

그러면 ORA-600과 ORA-7445는 어떤 차이가 있을까요?

ORA-600은 오라클 내부에 정의된 exception error를 나타냅니다.
ORA-7445는 O/S로 부터의 fatal signal에 의해 발생됩니다.

따라서 ORA-600의 경우가 오라클 입장에서는 명확할 수 있습니다. ORA-7445의 경우는 개발자의 실수나 O/S의 resource 부족 등 많은 원인을 갖고 있기 때문에 trace file내의 stack dump나 추가로 발생한 core에 대한 분석 등이 필요할 수 있습니다.

다음은 ORA-7445 관련해 볼만한 문서들입니다.

Note 211909.1 Customer Introduction to ORA-7445 Errors
Note 153788.1 Troubleshoot an ORA-600 or ORA-7445 Error Using the Error Lookup Tool .
Note 1038055.6 LIST OF UNIX SIGNALS AND EXPLANATIONS




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

10.2 oracle concept - 19 Direct-Path INSERT  (0) 2009.07.07
sequence cache & pining  (0) 2009.06.30
Cluster I/O Fencing – exclusion strategy  (0) 2009.06.23
Sample code - sqlplus : demobld.sql  (0) 2009.06.09
Oracle Korea Magazine - Summer 2009  (0) 2009.06.09

가끔 RAC나 CRS 관련해 문서를 보다보면 I/O fencing이라는 말이 나오는데, 이게 무슨 말일까요?

다음은 Cluster I/O fencing에 대한 설명입니다.

There will be some situations where the leftover write operations from failed database instances reach the storage system after the recovery process starts, such as when the cluster function failed on the nodes, but the nodes are still running at OS level.

Since these write operations are no longer in the proper serial order, they can damage the consistency of the stored data. Therefore, when a cluster node fails, the failed node needs to be fenced off from all the shared disk devices or disk groups.

This methodology is called I/O Fencing, sometimes called Disk Fencing or failure fencing.

정리해 보자면, cluster 구성에서 떨어져 나간 노드의 I/O를 방지하는 방법?




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

sequence cache & pining  (0) 2009.06.30
오라클의 에러  (0) 2009.06.25
Sample code - sqlplus : demobld.sql  (0) 2009.06.09
Oracle Korea Magazine - Summer 2009  (0) 2009.06.09
Oracle Magazine - July/August 2009  (0) 2009.06.09



--
-- Copyright (c) Oracle Corporation 1999. All Rights Reserved.
--
-- NAME
-- demobld.sql
--
-- DESCRIPTION
-- This script creates the SQL*Plus demonstration tables in the
-- current schema. It should be STARTed by each user wishing to
-- access the tables. To remove the tables use the demodrop.sql
-- script.
--
-- USAGE
-- SQL> START demobld.sql
--
--

SET TERMOUT ON
PROMPT Building demonstration tables. Please wait.
SET TERMOUT OFF

DROP TABLE EMP;
DROP TABLE DEPT;
DROP TABLE BONUS;
DROP TABLE SALGRADE;
DROP TABLE DUMMY;

CREATE TABLE EMP
(EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2));

INSERT INTO EMP VALUES
(7369, 'SMITH', 'CLERK', 7902,
TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO EMP VALUES
(7499, 'ALLEN', 'SALESMAN', 7698,
TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
INSERT INTO EMP VALUES
(7521, 'WARD', 'SALESMAN', 7698,
TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
INSERT INTO EMP VALUES
(7566, 'JONES', 'MANAGER', 7839,
TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO EMP VALUES
(7654, 'MARTIN', 'SALESMAN', 7698,
TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES
(7698, 'BLAKE', 'MANAGER', 7839,
TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
INSERT INTO EMP VALUES
(7782, 'CLARK', 'MANAGER', 7839,
TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
INSERT INTO EMP VALUES
(7788, 'SCOTT', 'ANALYST', 7566,
TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
(7839, 'KING', 'PRESIDENT', NULL,
TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES
(7844, 'TURNER', 'SALESMAN', 7698,
TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
INSERT INTO EMP VALUES
(7876, 'ADAMS', 'CLERK', 7788,
TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES
(7900, 'JAMES', 'CLERK', 7698,
TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);
INSERT INTO EMP VALUES
(7902, 'FORD', 'ANALYST', 7566,
TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
(7934, 'MILLER', 'CLERK', 7782,
TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

CREATE TABLE DEPT
(DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13) );

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

CREATE TABLE BONUS
(ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER,
COMM NUMBER);

CREATE TABLE SALGRADE
(GRADE NUMBER,
LOSAL NUMBER,
HISAL NUMBER);

INSERT INTO SALGRADE VALUES (1, 700, 1200);
INSERT INTO SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SALGRADE VALUES (5, 3001, 9999);

CREATE TABLE DUMMY
(DUMMY NUMBER);

INSERT INTO DUMMY VALUES (0);

COMMIT;

SET TERMOUT ON
PROMPT Demonstration table build is complete.

EXIT




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

오라클의 에러  (0) 2009.06.25
Cluster I/O Fencing – exclusion strategy  (0) 2009.06.23
Oracle Korea Magazine - Summer 2009  (0) 2009.06.09
Oracle Magazine - July/August 2009  (0) 2009.06.09
The Official Oracle Wiki  (0) 2009.05.08


create table test_partition (a number(2))
            partition by range(a)
            (partition  p01 values less than (10) ,
             partition  p02 values less than (20) ,
             partition  p03 values less than (99) );

ALTER TABLE test_partition
 ADD PARTITION p04 VALUES LESS THAN (MAXVALUE) ;

ALTER TABLE test_partition
  MERGE PARTITIONS p03, p04 INTO PARTITION p04 ;

alter table test_partition rename partition p04 to p03;

참고 : Oracle Technical iSeminar - Partition Table과 Index 관리




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

The Official Oracle Wiki  (0) 2009.05.08
Oracle 11g: DBA와 개발자가 알고 있어야 하는 새로운 기능 - 파티셔닝  (0) 2009.04.09
Buffer Cache Latch & Buffer Cache LRU Latch  (0) 2009.04.07
Latch  (0) 2009.04.07
Database Writer (DBWR)  (0) 2009.04.07

사용자 process가 SGA내의 database buffer cache나 dirty buffer를 포함하는 LRU chain을 scan하기 위한 latch이다.

+ cache buffer handles - buffer cache내의 buffer header에는 다음의 두 가지 list를 포함하고 있다.
 1. user list: doubly linked list로 연결되어 있는 "handle"을 포함한다. 여기에서 handle은 해당 buffer를 사용하고 있는 oracle process를 가리키는 정보를 담고 있다.
 2. waiter list: 이 list도 doubly linked 되어 있는 "handle"의 list를 포함하고 있는데, 여기에서의 handle은 해당 buffer를 사용하기 위해 기다리고 있는 oracle process에 대한 정보를 담고 있다.
cache buffer handle latch를 확보한 뒤, process에 buffer handle을 할당하고 나서 latch를 푼다.

+ cache buffers chains -  foreground process가 buffer를 변경하기 전에 잡아야 하는latch로 복수 사용자에 의해 동시에 변경되는 것을 막아준다. 하나의 latch에 대해서 여러 개의buffer가 DBA를 이용하여 hash되어진다.
    latch contention이 심한 경우, 특정한 particular hash list가 크게 증가하였거나, 하나의 block에 대해서 CR copy가 여러 개 존재하는 경우이다. 다음과 같은 query를 이용하여 그러한 경우인지를 확인한다.
    select dbarfil "File #", dbablk "Block #",count(*)
    from x$bh
    group by dbarfil, dbablk
    having count(*) > 1 ;

+ cache buffers lru chain - LRU list를 보호하기 위한 latch이다. buffer를 이 list에 옮기려면 일단 이 latch를 잡아야 한다. LRU latch이 개수는 {_}db_block_lru_latches로 결정된다. 이 값은 기본적으로 Oracle8.0이전은 CPU_COUNT/2, Oracle8i의 경우 CPU_COUNT, Oracle9i의 경우 CPU_COUNT*4 이다. ( Buffer Cache의 크기에 따라 변경될 수 있다)



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

Oracle 11g: DBA와 개발자가 알고 있어야 하는 새로운 기능 - 파티셔닝  (0) 2009.04.09
[tip] 마지막 partition key value 값을 maxvalue로 바꾸기  (0) 2009.04.08
Latch  (0) 2009.04.07
Database Writer (DBWR)  (0) 2009.04.07
Log Writer (LGWR)  (0) 2009.04.07

Latch는 SGA의 공유 데이터구조를 보호하기 위해서 사용되는 기법으로 빨리 획득되고 풀리는 lock의 일종이다. 일반적으로, Latch는 한 순간에 하나 이상의 프로세스가 동시에 같은 실행코드(code)를 수행하는 것을 방지하는데 사용된다. 그렇게 함으로서 SGA의 공유 데이터 구조를 보호하게 된다.
Latch에는 Wait latch (“willing-to-wait”)와 Nowait latch (immediate) 2종류가 있다. Latch의 모든 항목은 99%이상의 Hit Ratio를 유지해야 한다. 그렇지 않은 경우 Latch Contention이 발생되고 있다고 판단 할 수 있다.

+ Wait latches (“willing-to-wait”)
 - Gets -  Latch획득에 성공한 횟수.
 - Misses -  최초 Latch의 획득을 실패한 횟수.
 - Sleeps  - 최초 Latch의 획득을 실패한 이후로 Latch획득을 시도한 횟수.
 - Latch get hit ratio = (gets - misses) / gets, 이 수치는 1에 가까워야 한다.

+ Nowait latches (immediate)
 - immediate gets - immediate call에 의한 Latch획득에 성공한 횟수.
 - Immediate misses - immediate call에 의한 최초 Latch의 획득을 실패한 횟수.
 - Nowait latches와 관련된 Sleeps은 없다.
 - Nowait hit ratio = (nowait_gets - nowait_misses) / nowait_gets, 이 수치는 또한 1에 가까워야 한다.



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

[tip] 마지막 partition key value 값을 maxvalue로 바꾸기  (0) 2009.04.08
Buffer Cache Latch & Buffer Cache LRU Latch  (0) 2009.04.07
Database Writer (DBWR)  (0) 2009.04.07
Log Writer (LGWR)  (0) 2009.04.07
EXADATA  (0) 2009.03.20

Oracle은 Buffer Cache 를 관리하기 위해서는 세 가지의 내부적인 structure를 사용하는데, 그 각각은 cache buffer chain, LRUW(dirty list), LRU(Least Recently Used list)이다.  buffer cache management의 기본을 이루는 이 세가지 list를 관리하면서 사용자에게 필요한 buffer를 사용가능 하도록 제공하여 주는 역할을 하는 것은 DBWR이다.  DBWR은 데이터화일에 대한 대부분의 Write 작업을 수행하는 프로세스이기도하다.(일부 CKPT(Checkpoint Process)에 의해 데이터화일의 Header는 주기적으로 Write된다). 또한, DBWR은 startup시 각 online datafile에 대해서 Media Recovery (MR) lock을 획득하는 등 데이타베이스 file의 관리자로도 간주된다.

Cache Buffer Chain
cache buffer chain list는 hashed chain list라고도 하며, doubly-linked hash list로 연결된 hash table (또는 hash bucket)로 구성되어진다. 이 has bucket은 instance startup시에 할당되며, 실제 buffer block으로 구성된 것은 아니고 buffer header만을 포함하게 된다.
hash bucket의 개수는 기본적으로 Oracle8.0 이하에서는 db_block_buffers/4, Oracle8i 에서는 db_block_buffers*2, Oracle9i에서는 _db_block_buffers*2 보다 큰 최소의 소수(prime number)가 되며, init$ORACLE_SID.ora에 명시적으로 _db_block_hash_buckets parameter에 의해 지정할 수 있다.
이 cache buffer chain에 존재하는 buffer들은 LRU list나 LRUW list (dirty list) 중의 하나에 위치하게 되며, 두 list 모두에 포함되지는 않는다. buffer들은 data block address(DBA)에 의해서 hash되어 hash table에 할당된다.
이상의 Cache Buffer Chain 을 관리하는 Latch는 cache buffers chains latch이고, _db_block_hash_latchesd에 의해 제어되며, default로 _db_block_buffers /128 값보다 크거나 같은 2의 승수로 결정된다. (_db_block_buffers 또는 db_block_buffers) < 4096 이면, _db_block_hash_latches는 1024로 결정된다. 대부분의 경우 기본값으로 충분한 성능을 발휘한다.
Oracle8i까지는 이러한 Cache 관련 List 등이 Shared Pool 에 존재했으나, Oracle9i에서부터는 Buffer Cache 영역에 존재한다.

LRU list
least recently used list 혹은 replacement list라 불리는 것으로, 이 LRU list의 head부분에는 가장 최근에 사용된 MRU buffer들을 포함한다. 특별한 경우를 제외하고는, 모든 새로운 block들은 모두 MRU end에 위치하며, LRU의 끝부분은 최근에 사용되지 않은, 곧 재사용될 buffer들이 위치한다. 그러므로 foreground process는 빈 buffer를 얻기 위해 LRU의 끝부분부터 찾기 시작한다. 이 LRU의 buffer들은 free, pinned, dirty 세가지 중 하나를 가지며 각각은 다음과 같은 특성을 가진다.

- pinned buffer: 현재 user에 의해 사용 중이어서, 재사용될 수 없는 상태의 buffer이며, pinned clean 혹은 pinned dirty로 다시 나뉘어질 수 있다.
- free buffer: 사용되지 않은 buffer, 즉 disk block이 읽혀져서 할당되어 사용될 buffer이다.
- dirty buffer: dirty buffer는 pinned dirty buffer와 마찬가지로 user가 사용하여 내용이 변경된 buffer이다. 그러나 pinned buffer가 현재 사용중이서 재사용될 수 없는 반면에, dirty buffer는 현재 사용중인 user나 waiter는 없기 때문에 LRUW list로 옮겨질 수 있고, 결국은 disk로 write될 buffer이다.

위에서 언급한 새로 읽어 들인 block중 MRU end부분에 위치하지 않는 특별한 경우란, 바로  Full Table Scan이다. Full Table Scan으로 읽은 table은 LRU list의 끝부분에 위치하게 된다. 이렇게 LRU end에 위치시키는 이유는 Full Table Scan으로 읽은 block은 다시 access할 확률이 적어서 곧 다시 재사용될 수 있도록 한 것이다.
그러나 Full Table Scan의 경우에도 매우 중요하고 자주 사용되어 MRU end에 전체 table의 내용을 모두 cache시키고 싶은 경우도 있을 수 있다. 이러한 경우에는 다음과 같이 CACHE을 사용하면 된다.
SQL> alter table dept cache;
SQL> select /*+ cache(a) */ * from emp a;
이러한 CACHE절은 작은 table에만 사용하는 것이 바람직하며, 큰 table에 사용하게 되면 buffer의 MRU end쪽의 대부분의 buffer를 이 하나의 table이 차지하게 되는 현상이 발생 가능하다. 그리고 이 CACHE절로 MRU end에 위치한 table도 이후 다른 TABLE이 계속 사용되어짐에 따라 LRU end쪽으로 점차 이동하다 disk로 write되고 Buffer Cache에서 사라질 수 있다.

LRUW list
dirty list라고도 불리며, DBWR는 이 list의 buffer의 내용을 disk에 write하여 빈 buffer로 만든다. buffer를 LRUW list로 옮기고 결국은 disk에 write하는 것이 DBWR의 기본 기능이라 할 수 있다.




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

Buffer Cache Latch & Buffer Cache LRU Latch  (0) 2009.04.07
Latch  (0) 2009.04.07
Log Writer (LGWR)  (0) 2009.04.07
EXADATA  (0) 2009.03.20
Automatic Optimizer Statistics Collection  (0) 2009.03.20

+ Recent posts

티스토리툴바