일반적으로 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 값등의 지정 등의 세심한 주의가 필요하다.
변수는 다들 잘 알고 있듯이 특정 변경되는 값을 변수값으로 입력 받아 이를 해당 변수와 치환해주게 된다. 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" 명령을 통해 해당 변수값을 유저로 부터 받아들일 수 있다.
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 받는 방법입니다.
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> /
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
/
오라클 운영시 특정 세션이나 프로세스가 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
recovery manager (RMAN)은 오라클에서 제공하는 backup/recovery solution이다. RMAN을 이용하여 많은 backup vender에서는 solution을 제공하고 있다. oracle도 EM을 통해 RMAN backup을 지원하고 있다.
rman은 oracle datafile, control file, archive file 을 backup할 수 있다. 그러나 online redo log에 대해서는 backup을 하지 못하므로 archive 화하여 backup을 한다. 또 init file이나 password file도 backup을 하지 못한다. 10g에서는 spfile backup은 되는 듯 하다.
archive mode는 물론 no-archive mode에 대해 backup도 가능하다. 하지만 no-archive mode에서의 backup시 target database는 open 상태이면 당연히 안된다.
rman은 catalog를 이용하는 운영방법과 target database의 controlfile을 이용하는 방법이 있으며, 오라클에서는 catalog를 이용하는 방법을 강력히 권고한다. control file을 이용하는 경우 복구가 어려울 수 있으며, resync 등의 작업들이 필요하게 된다.
rman은 Incremental Backup을 지원하므로 backup 정책을 유연하게 수립할 수 있다.
만약 다음과 같이 backup 정책을 수립했다고 하면..
일요일에 level 0로 full backup,
월요일에는 level 2로 일요일 backup시점 이후 변동 사항만을 incremental backup,
화요일에는 level 2로 월요일 backup 시점 이후 변동사항만을 incremental backup,
수요일에는 level 1으로 일요일 backup 시점 이후 변동 사항만을 incremental backup,
목요일에는 level 2로 수요일 backup 시점 이후 변동 사항만을 incremental backup.
금요일에는 level 2로 목요일 backup 시점 이후 변동사항만을 incremental backup
만약 토요일에 장애가 발생하면, 일요일, 수요일, 목요일,금요일의 backup을 이용하여
recovery하면 된다. 만약 level 1 incremental backup을 이틀에 한번 정도 할 수 있다면 recovery 시간은 더 단축 할 수 있다.
다가오는 8월 26일(음력 7월 7일), 견우와 직녀가 만난다는 칠석(七夕)날,
오라클이 OTN Developer Day에서 개발자 여러분들과의 특별한 만남을 준비하였습니다.
저희 오라클은 최고의 개발자를 꿈꾸는 오라클 개발자 분들을 지원하기 위해 전세계
최고의 개발자 커뮤니티인 OTN을 지원하고, 최신의 기술과 동향에 대해 전문가에게 직접 듣고 궁금증을 해결할 수 있도록 OTN Developer Day를 정기적으로 개최하고 있습니다.
이번 OTN Developer Day에서는 SOA의 구성요소와 거버넌스, REST에 대한 개념과 구현 방법 및 설계 사례, 그리고 Rich Enterprise Application을 위한 웹센타의 기능에 대해
살펴봅니다. 또한 표준 기반 ADF Faces 11g
특징과 개발 방법, Oracle Application Testing Suite을 통한 개발 생산성 향상 및 애플리케이션의 품질
개선 방안을 제시하고, Application Grid 의 개념과 이를 뒷받침하는 Coherence의 기능에 대해서 살펴볼
것입니다.
오라클과 개발자 여러분들의 특별한 만남! OTN Developer Day!
변화하는 기술에 준비된 개발자가 될 수 있는 더할 나위 없는 좋은 기회가 될 것입니다.
지난 몇 년간 차세대를 포함하여 많은 프로젝트가 서비스 기반 아키텍처로 설계되고 구현되어 왔습니다. 이미 기술트랜드의 핵심으로 자리 잡은
서비스 기반 아키텍처에 대해 알아보고, 서비스 기반 아키텍처를 위해
필요한 기술요소와 효과적인 서비스 기반 아키텍처의 구현을 위해
오라클이 바라보는 서비스 기반 아키텍처 모습과 이를 구성하는
핵심요소와 이러한 기술요소간의 효율적인 통합과 운영에 필요한
거버넌스와 같은 관리 요소에 대해 살펴봅니다.
10:50 ~ 11:40
Rest Architecture - 조병욱 컨설턴트 / 한국오라클
REST는 기업의 SOA와 더불어 WEB2.0사상이 대두됨에 따라 구글이나
아마존과 같은 서비스 업체를 중심으로 급속하게 퍼지고 있는 웹기반의
아키텍쳐입니다. 본 발표에서는 REST에 대한 개념과, 구현 방법 그리고 ESB를 이용한 REST 기반의 아키텍쳐 설계 방식을 실제 프로젝트 사례를 기반으로 설명 합니다.
11:40 ~ 12:30
Rich Enterprise Application 위한 웹센타 - 황주필 컨설턴트 /
한국오라클
업계 표준 기술 기반의 Dynamic UI 기술의 RIA개 발을 손쉽게 해주고,
조합형 어플리케이션, 소셜 커뮤니티, 웹 어플리케이션의 포탈이나 차세대 어플리케이션을 개발하기 위한 open 프레임워크이며, 개발 시간을
단축하며, 일반 웹 어플리케이션에 포탈과 같은 기능을 추가하는 법에
대해서 알아봅니다.
12:30 ~ 13:30
Lunch
13:30 ~ 14:20
JSF 2.0, A whole new world!! - 양수열 소장 / 인피언컨설팅
IDE
friendly하고 컴포넌트 기반의 웹 개발을 가능하게 해주는 JavaEE 표준 컴포넌트인 JSF는 많은 장점에도 불구하고
기존에 1.2 spec에서는 외면을 받았습니다. 하지만, 2.0으로 진화하면서, JavaEE6에 새로이 포함이 될
매우 강력한 프레임웍으로 부상하고 있습니다. 이번 세션에서는 기존의 JSF 1.2의 리뷰와 2.0에서 새로워진 부분을 살펴보고, 이러한 변화가 개발자들에게 어떠한 혁신을 가져다 줄지 살펴볼 예정입니다.
ADF Faces는 표준 JSF(JavaServer Faces)의 오라클 구현체입니다.
오라클은 JavaServer Faces를 표준 기반으로 확장하여 RIA(Rich Internet Application) 술루션으로 활용하고 있습니다. 또한 표준 기반 RIA
프레임웍의 개발 생산성을 높이는 범용적 자바 개발툴인 JDeveloper를
함께 지원함으로써 Java 애플리케이션의 생산성을 높이고 있습니다. 이번 세션은 표준 기반 ADF Faces 11g 특징을 살펴보고 JDeveloper를 이용한 애플리케이션 개발 방법에 대하여 다룰 것입니다.
15:10 ~ 15:30
Coffee Break
15:30 ~ 16:20
개발자의 효율적인 웹 애플리케이션 테스트를 위한
Oracle Application Testing Suite - 박지영 컨설턴트 / 한국오라클
오라클이 제공하는 Oracle Application Testing Suite(Oracle ATS)은 웹
애플리케이션의 품질 보증을 위한 기능/회귀 테스트, 부하 테스트, 테스트 관리를 제공합니다. 애플리케이션의 전반적인 라이프사이클에 걸쳐 이를 통합적으로 신속하게 테스트하여 개발 생산성을 향상시키며
애플리케이션의 품질을 개선시켜 사용자의 폭발적인 증가에도 성능을
일정하게 유지할 수 있는 방안을 제시합니다.
16:20 ~ 17:10
Application Grid 의 핵심, Coherence - 윤호성 컨설턴트 / 한국오라클
Application Grid란 기업 내 한정된 자원을 효율적으로 활용해 보자는
취지의 미들웨어 레벨의 접근법입니다. Application Grid의 구현을
위해서는 유동적인 자원할당이 무엇보다도 중요한데, Coherence는
노
드의 추가/삭제가 쉽고 선형확장성을 보장함으로써 이러한 핵심 역할을 훌륭히 수행하고 있습니다. 뿐만 아니라, Coherence의
in-memory data grid 는 하드웨어 메모리의 효율적인 사용과 탁월한 성능을 보장해 줍니다. 이번 세션에서는
Application Grid 의 개념과 이를 뒷받침하는,
Coherence의 기능에 대해서 살펴봅니다.
오라클에서 수행되는 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 정보를 보여줍니다.
set pagesize 600
set tab off
set linesize 140
set echo off
set long 4000
col TQID format A4
col "SLAVE SQL" format A95 WORD_WRAP
col address format A12
col sql_hash format A15
col exec format 9999
col sql_text format A75 WORD_WRAP
repfooter off;
set timing off veri off space 1 flush on pause off termout on
numwidth 10;
alter session set "_complex_view_merging"=false;
select hash_value||decode(child_number, 0, '', '/'||child_number)
sql_hash,
sql_text
from v$sql
where child_number= 0 and hash_value= &hashvalue;
select '| Operation | Name | Starts | E-Rows | A-Rows | Buffers |
Reads | Writes | E-Time |' as "Plan Table" from dual
union all /* QWEKLOIPYRTJHH7 */
select
'------------------------------------------------------------------------------------------------------------------------'
from dual
union all
select rpad('| '||substr(lpad(' ',1*(depth))||operation||
decode(options, null,'',' '||options), 1, 33), 34, ' ')||'|'||
rpad(substr(object_name||' ',1, 19), 20, ' ')||'|'||
lpad(decode(starts,null,' ',
decode(sign(starts-1000), -1, starts||' ',
decode(sign(starts-1000000), -1, round(starts/1000)||'K',
decode(sign(starts-1000000000), -1, round(starts/1000000)||'M',
round(starts/1000000000)||'G')))), 8, ' ') || '|' ||
lpad(decode(cardinality,null,' ',
decode(sign(cardinality-1000), -1, cardinality||' ',
decode(sign(cardinality-1000000), -1, round(cardinality/1000)||'K',
decode(sign(cardinality-1000000000), -1, round(cardinality/1000000)||'M',
round(cardinality/1000000000)||'G')))), 8, ' ') || '|' ||
lpad(decode(outrows,null,' ',
decode(sign(outrows-1000), -1, outrows||' ',
decode(sign(outrows-1000000), -1, round(outrows/1000)||'K',
decode(sign(outrows-1000000000), -1, round(outrows/1000000)||'M',
round(outrows/1000000000)||'G')))), 8, ' ') || '|' ||
lpad(decode(crgets,null,' ',
decode(sign(crgets-10000000), -1, crgets||' ',
decode(sign(crgets-1000000000), -1, round(crgets/1000000)||'M',
round(crgets/1000000000)||'G'))), 9, ' ') || '|' ||
lpad(decode(reads,null,' ',
decode(sign(reads-10000000), -1, reads||' ',
decode(sign(reads-1000000000), -1, round(reads/1000000)||'M',
round(reads/1000000000)||'G'))), 8, ' ') || '|' ||
lpad(decode(writes,null,' ',
decode(sign(writes-10000000), -1, writes||' ',
decode(sign(writes-1000000000), -1, round(writes/1000000)||'M',
round(writes/1000000000)||'G'))), 8, ' ') || '|' ||
lpad(decode(etime,null,' ',
decode(sign(etime-10000000), -1, etime||' ',
decode(sign(etime-1000000000), -1, round(etime/1000000)||'M',
round(etime/1000000000)||'G'))), 8, ' ') || '|' as "Explain
plan"
from
(select /*+ no_merge */
p.HASH_VALUE, p.ID, p.DEPTH, p.POSITION, p.OPERATION,
p.OPTIONS, p.COST COST, p.CARDINALITY CARDINALITY,
p.BYTES BYTES, p.OBJECT_NODE, p.OBJECT_OWNER,
p.OBJECT_NAME, p.OTHER_TAG, p.PARTITION_START,
p.PARTITION_STOP, p.DISTRIBUTION, pa.starts,
pa.OUTPUT_ROWS outrows, pa.CR_BUFFER_GETS crgets,
pa.DISK_READS reads, pa.DISK_WRITES writes,
pa.ELAPSED_TIME etime
from
v$sql_plan_statistics_all pa,
V$sql_plan p
where p.hash_value =
&hashvalue
and
p.CHILD_NUMBER= 0
and
p.hash_value = pa.hash_value(+)
and
pa.child_number(+) = 0 )
union all
select
'------------------------------------------------------------------------------------------------------------------------'
from dual;
REM
REM Print slave sql
REM
select /* QWEKLOIPYRTJHH7 */
decode(object_node,null,'', substr(object_node,length(object_node)-3,1)
|| ',' ||
substr(object_node,length(object_node)-1,2)) TQID,
other
"SLAVE SQL"
from v$sql_plan vp
where other is not NULL
and hash_value = &hash_value
and CHILD_NUMBER= 0;
qeps.sql과 qep.sql을 생성해 수행하면 SQL의 성능을 평가하는 8개의 항목에 대해 지정된 갯수의 SQL과 그에 따른 statistic 정보를 보여줍니다. init parameter인 statistics_level를 ALL로 설정해야 정상적인 결과를 보여줍니다.
--
-- Script: qeps.sql
--
-- Script to report the explain plan for the most expensive N SQL statements
-- based on user specified criteria:
--
-- buffer_gets - 1
-- CPU time - 2
-- disk_reads - 3
-- rows_processed - 4
-- executions - 5
-- parse calls - 6
-- Buffers/Exec - 7
-- Cost per row - 8
--
-- Usage: start qeps.sql
--
-- This scripts requires qep.sql in order to function.
-- See Oracle Metalink Note: 550578.1 for more detail.
--
SET ECHO OFF
PROMPT
PROMPT Starting QEPS.SQL
PROMPT
PROMPT NOTES:
PROMPT
PROMPT The database parameter statistics_level should be set to ALL
PROMPT to obtain full statistics in the plan output.
PROMPT
PROMPT Script only works with Oracle 9.2 and above
PROMPT
PROMPT Script must be run from a database account with access to:
PROMPT
PROMPT . gv$sql_plan
PROMPT . gv$sqltext_with_newlines
PROMPT . gv$sql_plan_statistics_all
PROMPT
PROMPT Requires the partner script qep.sql
PROMPT
SET HEAD OFF
SET SERVEROUT OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET TIMING OFF
SET PAUSE OFF
SET PAGESIZE 0
prompt Available Expense Criteria:
prompt
prompt buffer_gets - 1
prompt CPU time - 2
prompt disk_reads - 3
prompt rows_processed - 4
prompt executions - 5
prompt parse calls - 6
prompt Buffers/Exec - 7
prompt Cost per row - 8
prompt
accept sec_opt prompt "Please select required expense criteria [7]: "
accept top_n prompt 'Please enter the number of SQL Statements to report [5]: '
SET TERMOUT OFF
SPOOL gen_plans.sql
SELECT /* QWEKLOIPYRTJHH7 */
'define instid=' ||'''' || inst_id ||'''' || CHR(10) ||
'define hash_value=' ||'''' || hash_value ||'''' || CHR(10) ||
'define address=' ||'''' || address ||'''' || CHR(10) ||
'define child_number='||'''' || child_number ||'''' || CHR(10) ||
'@qep'
FROM
(SELECT inst_id, hash_value, child_number, address, buffer_gets
FROM gv$sql
WHERE sql_text NOT LIKE '%QWEKLOIPYRTJHH7%'
AND (UPPER(sql_text) like 'SELECT%'
OR UPPER(sql_text) like 'UPDATE%'
OR UPPER(sql_text) like 'DELETE%'
OR UPPER(sql_text) like 'INSERT%')
ORDER BY
DECODE('&sec_opt',
NULL,
buffer_gets / decode(greatest(rows_processed,executions),0,1,
greatest(rows_processed,executions)),
1, buffer_gets,
2, cpu_time,
3, disk_reads,
4, rows_processed,
5, executions,
6, parse_calls,
7, buffer_gets / decode(executions,0,1, executions),
8, buffer_gets / decode(greatest(rows_processed,executions),0,1,
greatest(rows_processed,executions))) DESC )
WHERE rownum < DECODE(TO_NUMBER('&&top_n'),NULL, 6, &&top_n + 1)
/
SPOOL OFF
SET HEAD ON PAGESIZE 66
--
-- Setup Sort Opt descr for main report
--
BREAK ON SORTOPT
COLUMN SORTOPT NEW_VALUE SORTOPT_VAR
SELECT /* QWEKLOIPYRTJHH7 */
decode(NVL('&sec_opt',7)
,1,'Buffer Gets',
2,'CPU time',
3,'Disk Reads',
4,'Rows Processed',
5,'Executions',
6,'Parse Calls',
7,'Buffers/Exec',
8,'Cost per Row') SORTOPT
FROM DUAL;
CLEAR BREAKS
COLUMN instance_name NEW_VALUE instance
SELECT instance_name
FROM v$instance;
SPOOL qeps_&instance._&file_date_var
TTITLE
left today skip 2 center 'Top &&top_n Performing SQL Statements
for Database Instance &&instance by &&sortopt_var '
skip 2
SET TERMOUT ON
@gen_plans
spool
spool off
--
-- Script:
qep.sql
--
-- This script is called via the qeps.sql script. Please refer to Oracle
-- Metalink Note: 550578.1 for more detail.
--
set pagesize 600
set tab off
set linesize 133
set echo off
set long 4000
col Inst_ID format 999 heading "Inst ID"
col TQID format A4
col "SLAVE SQL" format A95 WORD_WRAP
col address format A12 heading "SQL Address"
col sql_hash format A15 heading "SQL Hash"
col buffer_gets heading "Buffer|Gets "
col exec format 9999
col disk_reads heading "Disk |Reads"
col sorts heading "Sorts"
col rows_processed heading "Rows |Processed"
col parse_calls heading "Parse|Calls"
col cpu_time format 9999999.99 heading "CPU Time|(Secs)"
col executions format 999,999,999 heading "Execs"
col sql_text format A80 heading "SQL Text"
repfooter off;
SET TIMING OFF VERI OFF SPACE 1 FLUSH ON PAUSE OFF TERMOUT ON NUMWIDTH 10;
ALTER SESSION SET "_complex_view_merging"=FALSE;
SELECT /* QWEKLOIPYRTJHH7 */ inst_id, address,
hash_value||DECODE(child_number, 0,
'', '/'||child_number) sql_hash,
executions,
parse_calls,
buffer_gets,
disk_reads,
rows_processed,
cpu_time/1000000 as cpu_time,
sorts
FROM gv$sql
WHERE inst_id = '&instid'
AND hash_value = '&hash_value'
AND child_number = '&child_number';
set head off
TTITLE OFF
SELECT /* QWEKLOIPYRTJHH7 */ REPLACE(sql_text,CHR(10),' ') as sql_text
FROM gv$sqltext_with_newlines
WHERE inst_id = '&instid'
AND address = '&address'
ORDER BY piece;
set head on
PROMPT
PROMPT C-Rows => Cardinality O-Rows => Output Rows E-Time => Elapsed Time (Secs)
SELECT RPAD('| Operation',45) || RPAD('| Name',25) || ' |C-Rows|O-Rows|IN-OUT| Buffers |'
|| RPAD(' Reads',9) || RPAD('| Writes',10) || RPAD('| E-Time',10) || '|'
AS "Plan Table"
FROM dual
UNION ALL /* QWEKLOIPYRTJHH7 */
SELECT
'-------------------------------------------------------------------------------------------------------------------------------------'
FROM dual
UNION ALL
SELECT RPAD('| '||substr(LPAD(' ',1*(depth))||operation|| DECODE(options, null,'',' '||options), 1, 44), 45, ' ')||'|'||
RPAD(substr(object_name||' ',1, 25), 25, ' ')||'|'||
LPAD(DECODE(cardinality,null,' ',
DECODE(sign(cardinality-1000), -1, cardinality||' ',
DECODE(sign(cardinality-1000000), -1, round(cardinality/1000)||'K',
DECODE(sign(cardinality-1000000000), -1, round(cardinality/1000000)||'M',
round(cardinality/1000000000)||'G')))), 6, ' ') || '|' ||
LPAD(DECODE(outrows,null,' ',
DECODE(sign(outrows-1000), -1, outrows||' ',
DECODE(sign(outrows-1000000), -1, round(outrows/1000)||'K',
DECODE(sign(outrows-1000000000), -1, round(outrows/1000000)||'M',
round(outrows/1000000000)||'G')))), 6, ' ') || '|' ||
RPAD(DECODE(other_tag,
'PARALLEL_TO_SERIAL', ' P->S',
'PARALLEL_FROM_SERIAL', ' P<-S',
'PARALLEL_FROM_REMOTE', ' P<-R',
'PARALLEL_TO_PARALLEL', ' P->P',
'PARALLEL_COMBINED_WITH_PARENT',' PCWP',' '),6,' ') || '|' ||
LPAD(DECODE(crgets,null,' ',
DECODE(sign(crgets-10000000), -1, crgets||' ',
DECODE(sign(crgets-1000000000), -1, round(crgets/1000000)||'M',
round(crgets/1000000000)||'G'))), 9, ' ') || '|' ||
LPAD(DECODE(reads,null,' ',
DECODE(sign(reads-10000000), -1, reads||' ',
DECODE(sign(reads-1000000000), -1, round(reads/1000000)||'M',
round(reads/1000000000)||'G'))), 9, ' ') || '|' ||
LPAD(DECODE(writes,null,' ',
DECODE(sign(writes-10000000), -1, writes||' ',
DECODE(sign(writes-1000000000), -1, round(writes/1000000)||'M',
round(writes/1000000000)||'G'))), 9, ' ') || '|' ||
REPLACE(TO_CHAR(NVL(etime/1000000,0), '99990.99'),' 0.00',' ') ||
'|' AS "Explain plan"
FROM
(SELECT /*+ no_merge */ /* QWEKLOIPYRTJHH7 */
p.HASH_VALUE, p.ID, p.DEPTH, p.POSITION, p.OPERATION,
p.OPTIONS, p.COST COST, p.CARDINALITY CARDINALITY,
p.BYTES BYTES, p.OBJECT_NODE, p.OBJECT_OWNER,
p.OBJECT_NAME, p.OTHER_TAG, p.PARTITION_START,
p.PARTITION_STOP, p.DISTRIBUTION, pa.starts,
pa.OUTPUT_ROWS outrows, pa.CR_BUFFER_GETS crgets,
pa.DISK_READS reads, pa.DISK_WRITES writes,
pa.ELAPSED_TIME etime
FROM gv$sql_plan_statistics_all pa,
gv$sql_plan p
WHERE p.inst_id = '&instid'
AND pa.inst_id = '&instid'
AND p.hash_value = '&hash_value'
AND p.child_number = '&child_number'
AND pa.child_number(+) = '&child_number'
AND p.id = pa.id(+)
AND p.address = pa.address(+)
ORDER BY p.id)
UNION ALL
SELECT
'-------------------------------------------------------------------------------------------------------------------------------------'
FROM dual;
REM
REM Print slave sql
REM
SELECT /* QWEKLOIPYRTJHH7 */
DECODE(object_node,null,'', substr(object_node,length(object_node)-3,1) || ',' ||
substr(object_node,length(object_node)-1,2)) TQID,
other "Slave SQL"
FROM gv$sql_plan vp
WHERE vp.inst_id = '&instid'
AND vp.other IS NOT NULL
AND vp.hash_value = '&hash_value'
AND vp.child_number = '&child_number';
PROMPT
************************************************************************************************************************************
PROMPT
01/25/2008
Top
Performing SQL Statements for Database Instance V102 by
Buffers/Exec
Parse
Buffer Disk Rows CPU Time
Inst
ID SQL Address SQL Hash Execs Calls Gets
Reads Processed (Secs) Sorts
-------
------------ --------------- ------------ ---------- ----------
---------- ---------- ----------- ----------
1
31ADC51C 2327026800 1 1 3835
778 0 .58 0
select
o.owner#,o.obj#,decode(o.linkname,null,
decode(u.name,nul
l,'SYS',u.name),o.remoteowner),
o.name,o.linkname,o.namespace,o.
subname
from user$ u, obj$ o where u.user#(+)=o.owner# and
o.typ
e#=:1
and not exists (select p_obj# from dependency$ where
p_obj
#
= o.obj#) order by o.obj# for
update
Parse
Buffer Disk Rows CPU Time
Inst
ID SQL Address SQL Hash Execs Calls Gets
Reads Processed (Secs) Sorts
-------
------------ --------------- ------------ ---------- ----------
---------- ---------- ----------- ----------
1
319B23DC 1187151836 1 1 920
32 1 .09 0
select
ee.ectx#, o.owner#, u.name, o.name, ee.num_rules,
ee.num_
boxes,
ee.ee_flags from rule_set_ee$ ee, obj$ o, user$ u
where
ee.rs_obj#
= :1 and ee.ectx# = o.obj# and o.owner# =
u.user#
Parse
Buffer Disk Rows CPU Time
Inst
ID SQL Address SQL Hash Execs Calls Gets
Reads Processed (Secs) Sorts
-------
------------ --------------- ------------ ---------- ----------
---------- ---------- ----------- ----------
1
31A63F14 2372567631 1 1 867
803 0 .21 0
select
o.obj#, u.name, o.name, t.spare1,
DECODE(bitand(t
.flags,
268435456), 268435456, t.initrans, t.pctfree$) from
s
ys.obj$
o, sys.user$ u, sys.tab$ t where
(bitand(t.trigflag,
1048576)
= 1048576) and o.obj#=t.obj# and o.owner#
=
u.user#
Parse
Buffer Disk Rows CPU Time
Inst
ID SQL Address SQL Hash Execs Calls Gets
Reads Processed (Secs) Sorts
-------
------------ --------------- ------------ ---------- ----------
---------- ---------- ----------- ----------
1
31A9A254 162926978 1 1 764
113 0 .12 0
select
table_objno, primary_instance, secondary_instance,
owner_
instance
from sys.aq$_queue_table_affinities a where
a.owner_i
nstance
<> :1 and
dbms_aqadm_syscalls.get_owner_instance(a.prima
ry_instance,
a.secondary_instance,
a.owner_instance)
=
:2 order by table_objno for update of a.owner_instance
skip
locked
Plan
Table
-------------------------------------------------------------------------------------------------------------------------------------
|
Operation | Name
|C-Rows|O-Rows|IN-OUT| Buffers | Reads | Writes | E-Time |
-------------------------------------------------------------------------------------------------------------------------------------
|
FOR UPDATE |
| | 0 | | 371 | 19 | 0 | 0.06|
|
TABLE ACCESS BY INDEX ROWID
|AQ$_QUEUE_TABLE_AFFINITIE| | 0 | | 371 | 19
| 0 | 0.06|
| INDEX FULL SCAN
|AQ$_QTABLE_AFFINITIES_PK | | 14 | | 1 | 1
| 0 | |
-------------------------------------------------------------------------------------------------------------------------------------
************************************************************************************************************************************
currently spooling to qeps_V102_20080125_213802.txt
참고 :
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...
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')
;
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')
;
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)
이 그룹에 주어진 평균 수치가 만약 몸무게나 키, 허리 둘레 등에 대한 평균이라고 한다면,
이 그룹 중 강호동처럼 (몸무게가 어떤지는 잘은 모릅니다^^;) 몸무게가 다른 사람들 보다 많이 나가는 사람을 찾을 수 있을가요?
물론 평균 수치가 재산, 보유주택 가격 등에 대한 평균이고 이 중 이건희 회장 같은 사람이 있다면 당연히 그 평균값은 거의 한 사람의 값과 같겠죠. 따라서 일반적이지 않은 사람이 섞여있다고 판단할 수 있습니다.
(이 내용은 [블랙스완]이란 책에서 경제학자들의 일반화 오류에 대한 예입니다. 물론 거긴 강호동이나 이건희 회장은 나오지 않습니다만...ㅋㅋ)
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;
병렬 실행의 동적 로드 밸런싱 기능의 이점을 활용
내부에서 모두 자동으로 처리
많은 데이타 스큐가 있을 경우 그 진가가 최대한 발휘
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 환경에서의 성능과 확장성"의 일부 내용을 발췌한 것입니다.
Parallel DML은 대량의 table/index에 대해 "speed up"과 "scale up"을 위해 insert,
update, delete ,merge operation에 parallel execution mechanism을 적용시킨
operation을 말합니다. 즉 일반적으로 말하는 parallel query나 parallel direct-path read 등은 포함되지 않는 개념입니다.
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
정리:
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) 처음 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에게 데이터를 보내기 위해 사용된다.
기본적으로 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 에러 발생.
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 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 처리 시간에 영향을 미치게 된다.
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;
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을 막을 수 있습니다.
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 를 다음과 같이
바꾸어야 합니다.
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는 예상되는 동시 접속 수를 설정해 주는 것이 좋습니다.
이 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'를 설정해야 합니다.
다음의 내용은 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에 다음과 같이 기술하는 방법입니다.
이 방법은 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 가용성을 키우는 좋은 방법입니다.
데이터베이스 개발 툴(Database Development Tools) 팀은 최근 새로운 제품 오퍼링인 Oracle SQL Developer Data Modeler를
발표했습니다. Oracle SQL Developer Data Modeler는 ERD(Entity Relationship
Diagrams), 관계형 (데이터베이스 설계), 데이터 유형 및 다차원 모델링, 완벽한 포워드/리버스 엔지니어링 및 DDL
코드 생성 등을 비롯한 포괄적인 데이터 및 데이터베이스 모
델링 툴을 포함한 독립
제품입니다. 이 데이터 모델러는 다양한 소스 및 타깃 환경에서 가져오기/내보내기를 실행할 수 있으며 다양한 포맷팅 옵션을 제공하고 사전 정의된 설계 규칙을 통해 모델을 검증합니다.
Oracle SQL Developer Data Modeler는 현재
여기에서 이용할 수 있습니다. 이번 호에서는 본 제품에 대해 이해하는 데 유용한 일련의 링크를 제공합니다.
또한 Oracle Fusion Middleware 11g 프로덕션 릴리스(데이터베이스 애플리케이션 개발자 및 DBA도 반드시 알아야 하는 내용)와 OTN의 새로운 공동 샘플 코드 리파지토리인 samplecode.oracle.com의 출범 등 매우 흥미 진진한 내용들이 게재되어 있습니다. 보다 자세한 내용을 원하시면 아래 링크를 확인해 보십
시오.
다운로드: Oracle VM 2.1.5 무료로 제공되는 차세대
서버 가상화 및 관리 소프트웨어를 다운로드하십시오. Oracle VM Manager Web Services API를 통해 보다 뛰어난 호환성을 지원됩니다.
새로운 샘플 코드 리파지토리: samplecode.oracle.com 신
속하게 프로젝트에 착수하는 데 필요한 샘플 코드를 찾고 있습니까? 아니면 커뮤니티와 함께 자체 샘플 코드를 공유하거나, 아예
공동으로 개발하기를 원하십니까? OTN의 이 새로운 서비스는 여러분이 필요로 하는 플랫폼을 제공합니다. 이 리파지토리에 참여
또는 기여하거나 이를 활용하는 데 필요한 것은 오직 OTN 멤버쉽 뿐입니다. 바로 지금 samplecode.oracle.com을 살펴 보십시오! (SQL 및 PL/SQL 샘플 코드는 아직 제공되지 않음, 현재 이를 제공하기 위해 작업 진행 중)
라이브 웹캐스트: 온라인 비즈니스의 가속화 및 보호(8월 14일. 10AM PT)
기업들이 비즈니스 프로세스를 효율화하고 사용자 경험을 향상시키기 위한 방안으로 온라인 채널을 검토함에 따라 기밀 데이터를 빼내려는 보안 위험이 더욱 널리 확산되고 있습니다.
오라클의 엔터프라이즈 보안 아키텍트(Enterprise Security Architect)인 Chris Fox가 보안 위험을 완화하고 고객 경험을 향상시키며 기업 수지를 개선할 수 있는 방안을 설명합니다.
등록하기.
교육: “Bundled” SQL and PL/SQL Training from Oracle University
2개의 새로운 SQL 개발자 코스에는 고급 수준의 주제를 포함한 프로그래밍 기초 과정이 포함되어 있습니다. 각 번들 타이틀은 운영자 및 개발자를 위한 속성 5일 코스로 구성되어 있습니다.
Oracle OpenWorld 2009: 조기 등록 프로그램 오픈
Oracle OpenWorld (10월 11일~15일, Oracle Develop 포함)를 위한 등록 사이트가
오픈되었습니다. 곧 보다 많은 정보가 제공될 예정입니다. 조기 등록자들은 8월 14일까지 등록해 주십시오!
기술 기사: 전체 .NET 및 Oracle Database 개발 주기를 위한 Microsoft Visual Studio 사용
Visual Studio로 Oracle Database 개발 주기 전반에 대한 단계별 투어를 살펴 보십시오. Oracle의 많은 새로운 디자인 타임 기능에 대해 다루고 있습니다.
기술 기사: Oracle RAC 및 PHP를 이용한 고성능 및 HA(High Availability) 이 기사 에서는 Oracle Real Application Clusters를 기반으로 업무에 중요한 PHP 애플리케이션을 구축하는 실제 경험에 대해 검토하고 있습니다.
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
가끔 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.
dbms_stat로 table/index에 대한 통계 정보가 새로 생성하면
이후 새로 생성되는 cursor의 경우 새로운 통계정보에 의한 sql plan이 만들어 지지만,
기존의 library cache에 저장된 sql은 새로운 sql plan을 얻기 위해 invalidation 되어
해당 sql은 다시 hard pare 단계를 거쳐 새로운 통계정보에 의한 sql plan을 획득하게 됩니다.
이때 shared pool내의 많은 sql 들이 hard parse 되면서
latch contention과 CPU 자원을 많이 소모하게 되는데,
이러한 현상은 "hard-parse spike"라고 합니다.
이러한 현상은 운영자들에게는 많은 부담이 될텐데요..
이러한 현상을 막기 위해 NO_INVALIDATE option을 이용해 기존의 sql에 대한 invalidation을 제어합니다. 그러나 이는 새로운 통계정보에 의한 sql plan을 당장 사용하지 못하니 이 또한 그리 좋은 방법은 아니겠죠..
10g 부터는 auto invalidate 기능이 추가 되어 "hard parse spike"를 많이 감소시켰다고 하네요..
다음은 metalink의 rolling cursor invalidation에 대한 설명입니다...
문서 557661.1 Rolling Cursor Invalidations with DBMS_STATS in Oracle10g
Purpose
Starting with Oracle10g, the DBMS_STATS package offers the
AUTO_INVALIDATE option for the NO_INVALIDATE parameter of its
GATHER_xxx_STATS and DELETE_xxx_STATS procedures. This parameter allows
the user to specify when to invalidate dependent cursors i.e. cursors
cached in the library cache area of the shared pool which reference a
table, index, column or fixed object whose statistics are modified by
the procedure call.
According to the documentation the values NO_INVALIDATE can take are:
TRUE: does not invalidate the dependent cursors
FALSE: invalidates the dependent cursors immediately
AUTO_INVALIDATE (default): have Oracle decide when to invalidate dependent cursors
This article describes details of how AUTO_INVALIDATE works.
Scope and Application
This article may be of interest to DBAs interested in the behaviour of DBMS_STATS with respect to cursor invalidations.
Rolling Cursor Invalidations with DBMS_STATS in Oracle10g
When statistics are modified by DBMS_STATS, new cursors not yet cached
in the shared pool will use them to get execution plans. Existing
cursors cached in the shared pool cannot update their execution plans
however. Instead, such cursors are invalidated and new versions
(children cursors) are created which get execution plans based on the
updated statistics. This involves a hard-parse operation which is more
expensive in resource consumption than a soft-parse which simply reuses
a cached cursor.
An important question to consider is: when does this invalidation occur in time after the statistics have been modified ?
Cursor Invalidations on Gathering Statistics prior to Oracle10g
In
releases prior to Oracle10g gathering statistics using DBMS_STATS
resulted in immediate invalidations of dependent cached cursors, unless
NO_INVALIDATE was set to TRUE. An invalidation of a cached cursor means
it has to be hard-parsed the next time it is executed. If large numbers
of such cursors had to be invalidated and immediately re-executed e.g.
due to DBMS_STATS being used on a popular object during a time period
of heavy user workload, this would result in a hard-parse spike which
could have serious effects on performance including high CPU usage,
heavy library cache and shared pool latch contention with subsequent
slowdown in application user response times.
Setting
NO_INVALIDATE to TRUE would prevent such spikes but this meant that
cursors would not notice the updated object statistics and would
continue using existing execution plans until hard-parsed. Such a hard
parse could happen on a cursor reload (i.e. on the next execution
following the cursor being automatically flushed out of the shared pool
due to inactivity and heavy usage of other cursors) or after a manual
flushing of the shared pool (which could itself also result in
hard-parse spikes as most of the flushed-out cursors would need to do a
hard parse on their next execution.)
Cursor Invalidations with Oracle10g and AUTO_INVALIDATE
With the AUTO_INVALIDATE option the
goal is to spread out the cursor invalidations over a time period long enough for hard-parses not to cause noticeable spikes.
In
this way a cached cursor depending on an object whose statistics have
been modified by DBMS_STATS will be invalidated as follows:
when DBMS_STATS modifies statistics for an object, all current
cached cursors depending on this object are marked for rolling
invalidation. Let's call this time T0.
the next time a session parses a cursor marked for rolling
invalidation, it sets a timestamp. This timestamp can take a random
value up to _optimizer_invalidation_period sec from the time of this
parse. The default for this parameter is 18000 sec i.e. 5 hours. Let's
call the time of this parse T1 and the timestamp value Tmax. On this
(first) parse we reuse the existing cursor i.e. we do not hard-parse
and do not use the modified statistics to generate a new plan (it is a
soft parse.)
on every subsequent parse of this cursor (which is now marked for
rolling invalidation and timestamped) we check whether the current time
T2 exceeds the timestamp Tmax. If not, we reuse the existing cursor
again, as happened on the first (soft) parse at time T1. If Tmax has
been exceeded, we invalidate the cached cursor and create a new version
of it (a new child cursor) which uses the new statistics of the object
to generate its execution plan. The new child is marked
ROLL_INVALID_MISMATCH in V$SQL_SHARED_CURSOR to explain why we could
not share the previous child.
From the above descriptions, it follows that:
a cursor which is never parsed again after being marked for rolling
invalidation will not be invalidated and may eventually be flushed out
of the shared pool if memory becomes scarce
a cursor which is only parsed once after being marked for rolling
invalidation will not be invalidated (it will only be timestamped) and
again may be eventually flushed out if memory in the shared pool
becomes scarce
cursors which are regularly reused will become invalidated on the next parse that happens after the timestamp Tmax has been
exceeded
It should be clear that the above method is efficient in that it incurs the overhead of invalidations only for
frequently reused cursors.
Exception:
parallel SQL are immediately invalidated in order to ensure consistency
between execution plans of slaves and Query Coordinator across multiple
RAC instances. This is not a problem as parallel SQL are usually heavy
and therefore hard-parse resources are insignificant to their total
resource usage.
이전 dictionary managed tablespace 방식에서는 엄청난 extent가 발생했을 때
이에 대한 정리작업 (drop, delete 등)을 수행할 경우
UET$와 FET$ dictionary table 갱신에 많은 시간이 소모 되었었죠..
다음의 내용은 OTN에 있는 내용을 가져 왔습니다.
제품 : ORACLE SERVER
작성날짜 :
DICTIONARY MANAGED TABLESPACE를 사용 대비, LOCALLY MANAGED TABLESPACE를 사용하는 것의 장점
===========================================================================================
PURPOSE
이 문서는, Locally Managed Tablespace에 대한 설명과 함께,
Dictionary Managed Tablespace 대비, 장점을 기술하는 데 목적이 있다.
Explanation
1. LOCALLY MANAGED TABLESPACE
Locally Managed Tablespace는, 자체 extent에 대한 관리를 각각의 데이터 파일에비트맵 형식으로 저장하여 관리하는 테이블스페이스로, 데이터 파일을 구성하는 블럭이 비어 있는지, 사용 중인지에 대한 정보를 관리한다. 비트맵의 각각의 비트는, 하나의 블럭 또는 블럭의 그룹에 해당하는 정보를 나타낸다.
익스텐트가 할당되거나, 비워지거나, 재사용될 때, 오라클에서는 블럭의 새로운 상태를 나타내기 위해 비트맵의 값을 변경한다. 이와 같은 변경사항은 기본 방식인 Dictionary Managed Tablespace와는 달리, rollback 정보를 생성하지 않는데, 이것은 데이터 딕셔너리의 테이블을 갱신하지 않기 때문이다 (테이블스페이스 별 quota 정보는 제외).
1) 공간 정보 관리를 위한 내부 작업을 줄임.
2) 데이터 딕셔너리 테이블에 대한 경합 감소됨.
3) 익스텐트 관리와 관련된 관련 rollback 생성이 되지 않음.
4) Coalescing 이 불필요함.
2. 테이블스페이스의 공간 관리
1) 사용되지 않는 익스텐트 정보가 비트맵에 의해 관리됨.
(따라서, 테이블스페이스의 일부분이 비트맵 정보를 저장하는 데 사용됨)
2) 각 비트는, 블럭이나, 블럭의 그룹의 정보를 나타냄.
3) 비트 정보는, 사용 중인지, 그렇지 않은지를 나타냄.
4) DBA_EXTENTS 나 DBA_FREE_SPACE 등의 뷰는 동일하게 사용됨.
3. 구문 규칙
EXTENT MANAGEMENT 절의 LOCAL 옵션을 사용하면, 테이블스페이스가
Locally Managed 방식으로 생성된다.
DICTIONARY : 테이블스페이스에 대해 Dictionary Table를 사용하여 공간 정보를 관리. (default)
LOCAL : 테이블스페이스가에 대해 비트맵을 사용하여 Locally Managed 방식으로 공간 정보 관리
AUTOALLOCATE : 테이블스페이스에 대한 익스텐트 관리를 시스템에서 관장 (사용자는 익스텐트의 크기를 수동으로 지정할 수 없음)
UNIFORM : 테이블스페이스가 동일한 크기의 익스텐트로 구성되도록 지정함. 크기는 기본적으로 바이트 단위로 지정 ( 익스텐트 크기를 KB 또는 MB 단위로 지정하기 위해서는 K 또는 M 을 사용하여 지정) 이 옵션을 사용하게 되면, DEFAULT Storage 절, MINIMUM EXTENT 또는 TEMPORARY 옵션을 사용할 수 없다.
Oracle 9.2 이전 버젼에서는, EXTENT MANAGEMENT 절을 SYSTEM 테이블스페이스를 제외한 permanent tablespace나, temporary tablespace 생성 시 지정할 수 있었다. Oracle 9.2부터는 SYSTEM 테이블스페이스를 포함한 모든 테이블스페이스를 Locally managed 방식으로 생성할 수 있다.
CREATE DATABASE에서 EXTENT MANAGEMENT LOCAL 절을 사용하게 되면, 오라클에서는 SYSTEM 테이블스페이스를 Locally Managed Tablespace로 생성하며, 익스텐트의 크기는 오라클에서 결정하게 된다. 이 기능을 사용하기 위해서는, COMPATIBLE 옵션이 9.2 또는 그 이상으로 지정되어 있어야 한다.
Locally Managed SYSTEM tablespace는 기본적으로 AUTOALLOCATE 방식을 사용하게 되며, Locally Managed 방식으로 SYSTEM 테이블스페이스를 생성할 때, UNIFORM extent 크기를 지정할 수 없다.
다음 storage parameter(NEXT, PCTINCREASE, MINEXTENTS, MAXEXTENTS)와 DEFAULT STORAGE는 Locally Managed Tablespace에서는 사용할 수 없다.
테이블스페이스 생성 후에는 공간관리 방법을 변경할 수 없다.
Oracle 8.1.6부터, Dictionary Managed Tablespace를 Locally Managed Tablespace로 마이그레이션을 할 수 있으나, 8.1.5에서는 그와 같은 작업을 수행할 수 없다.
기존에 사용해 왔던 테이블스페이스를 Locally Managed 방식으로 전환 시키기 위해서는 DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL 프로시져를 사용하면 된다.
그리고, DBMS_SPACE_ADMIN 패키지는 Locally Managed Tablespace를 관리하는 데 필요한 각종 프로시저를 제공한다.
위 문장을 수행시키면, Locally Managed Tablespace를 생성하며, 모든 extent의 크기를 128K로 할 때, 비트맵의 각 비트는 64개 블럭에 대한 정보를 나타낸다. 기본적으로, 데이터베이스 블럭의 크기의 기본값을 2K로 가정하였을 때, 각 비트는 하나의 extent(128K)에 대한 정보를 나타내므로,
각 비트맵은 64개의 오라클 블럭을 필요로 하게 된다.
4. Dictionary Managed Tablespace를 사용하는 것 대비, Locally Managed Tablespace를 사용하였을 경우의 장점
1) Locally Managed Tablespace는 가용한 공간에 대한 정보를 데이터 딕셔너리에 저장하지 않으므로, 데이터 딕셔너리에 대한 경합을 줄이게 된다.
2) Extent에 대한 local management를 통해, 인접한 가용 공간의 정보를 자동으로 관리하게 되므로, 가용 extent에 대한 coalesce 작업을 수행하지 않아도 된다.
3) 공간 관리를 위한 데이터베이스 내부 처리 작업을 피할 수 있다. 반면 이와 같은 내부 처리 작업은 Dictionary Managed Tablespace에서는 필요한데, extent를 사용하거나, 반납을 하는 등의 작업이 발생할 때마다 rollback segment나 데이터 딕셔너리 테이블의 공간을 사용하거나 반납하는 등의 작업이 필요하게 된다.
4) Locally Managed Tablespace에서의 extent의 크기는, 시스템에 의해 자동적으로 관리된다. 반면, 모든 extent는 Locally Managed Tablespace 에서는 동일한 크기를 사용하게 할 수도 있다.
5) Extent에 대한 정보를 나타내는 비트맵의 변경 사항은 rollback 정보를 생성하지 않는다. 이것은, 데이터 딕셔너리의 정보를 변경하지 않기 때문이다. ( 테이블스페이스에 대한 quota 정보와 같은 일부 사항은 예외 )
6) Fragmentation을 줄일 수 있다.
Example
Reference Documents
<Note:93771.1> Locally Managed Tablespace in Oracle 8i
<Note:103020.1> Migration from Dictionary Managed to Locally Managed
Tablespaces
<Note:105120.1> Advantages of Using Locally Managed vs Dictionary Managed Tables
paces
문제가 발생했을때 자동으로 인지하고 필요한 dump를 수행하도록 해주는 LTOM 입니다.
처음 설정후 사용자는 ltom 기동만 해주면 되니 잘만 쓰면 편리한 툴이죠..
다음의 내용은 OTN에 있는 내용입니다.
제품 : Database
작성날짜 : 2007-12-21
PURPOSE
실시간 성능 자료 취합과 진단하는 LTOM 툴의 사용 방법을 소개한다.
CONTENTS
1. Automatci Hang Detection
2. System Profiler
3. Automatic Session Tracing
4. LTOM 설치하기
5. LTOM 수행하기
6. 부록 - 디렉토리구조
EXPLANATIONS
LiTe Onboard Monitor (LTOM) 은 자바로 개발된 실시간 성능진단툴로 DB 자료뿐만 아니라 UNIX OS 성능자료까지 취합하고, 미리 지정한 룰에따라 Hang 과 Slow Performance 시점을 인지하여 덤프와 트레이스파일 자동 생성해주므로 성능이슈 해결에 소요되는 시간과 리소스를 절감할 수 있다.
DB 또는 OS 성능이슈 진단시 가장 어려운 점은 "문제 발생 시점에" 필요한 "정확한 데이터"를
취합하는 것 자체가 힘들다는 것이다. 특히 언제 발생할지도 모르고, 몇초 단위의 짧은 시간동안만
지속되는 성능이슈라면 재현되기를 기다려서 분석자료를 얻는데만도 많은 리소스와 시간이
소진될뿐더라 거의 불가능한 경우가 많아 여러번의 시행착오를 겪어야한다.
LTOM 은 이런 유형의 성능이슈 해결을 위해 주요 3가지 기능을 제공한다.
. Automatic Hang Detection
. System Profiler
. Automatic Sesssion Tracing
1. Automatci Hang Detection
- 주의! 이 기능은 Oracle Support 나 경험 많은 DBA 의 안내에따라 사용해야한다.
덤프 설정 레벨별로 생성되는 트레이스 양을 인지하고, 시스템에 부하를 테스트 후 적용해야한다.
(1) 용도
v$session 또는 v$session_wait 의 wait event 를 기준으로 hang detect rule 을 설정한다.
예를들어, 새벽 2시 아무도 없을때 갑자기 'latch free' wait event 가 누적되면서 몇초간
DB hang 이 발생하는 경우에는 hang detect rule 을 'latch free' 가 15초 이상 지속되면
systemstate 덤프나 hanganalyze 덤프들을 자동 생성하도록 설정할 수 있다.
(2) 장점
. systemstate 덤프와 hanganalyze 덤프를 문제발생 시점에 사용자 개입없이 자동생성한다
. hang 덤프를 24*7 동안 취합가능하다.
. Automatic Hang Detection 이 발생하면 email notification 을 보낸다.
($TOM_HOME/src/ltommail.sh 에 email 설정)
. 과도한 트레이스 방지위해 동일 hang 이 반복적으로 발생해도 Automatic Hang Detect 설정
후 처음 hang 인지시점에만 덤프를 수행한다.
(3) 설정파일 : $TOM_HOME/init/hangDetect.properties
. 모니터링할 wait event, hang detect time 기준, 생성할 덤프들을 지정한다.
. HangDetect.properties 의 기본적인 덤프생성은 아래 순서이나, 사용자가 순서 및 레벨을
변경할 수 있다.
자세한 설정방법과 설정단위는 LTOM 에 포함된 hangDetect.properties 파일참고.
(4) 결과물
. $TOM_HOME/hanglog/hang*.log : systemstate 덤프 와 hanganalyze 덤프 내용 정리결과
. $TOM_HOME/hanglog/hang*.report : hang detect 히스토리와 생성된 덤프 리스트
. 실제 systemstate 덤프와 hanganalyze 덤프들은 udump 에 생성된다.
(6) 사용예제
TM enqueue wait 이 발생한 경우 Automatic Hang Detect 로 진단하는 절차이다.
. $TOM_HOME/init/hangDetect.properties 파일에 rule 지정 방법
a. 문제가 되는 wait event 와 waiting time 임계치 지정
EVENT= enq: TM - contention,VALUE=15
event name 은 v$event_name 의 event name 과 정확히 일치해야한다.
VALUE=15 는 초단위로 해당 wait event 가 15초이상 지속되면 지정한 덤프를 수행한다.
b. ACTION PLAN 추가
SYSTEMSTATE=266
DELAY=30
HANGANALYZE=4
BLOCKERS=Y
. $./startltom.sh 수행
===> Enter 1 to Start Auto Hang Detection
===> polling 간격 5초 지정
. TM enqueue wait 시나리오 만들기
세션1 연결
$ sqlplus scott/tiger
SQL> lock table junk in exclusive mode;
세션2 연결
$ sqlplus scott/tiger
SQL> lock table junk in exclusive mode;
. 몇분 기다리면 LTOM 화면에도 hang detect 되는 시점에 메시지가 나오고,
$TOM_HOME/hanglog/hang*.report 에도 hang detect 정보가 있다.
. $TOM_HOME/hanglog/hang*.log 에서 systemstate 덤프 정리내용과 hanganalyze 내용을 확인한다.
2. System Profiler
(1) 용도
AWR 과 Statspack 의 단점은 DB 성능이슈 진단시 non-Oracle 프로세스정보와 OS 리소스
(MEMORY, CPU, IO) 에대한 자료가 포함되지 않아 "전체적인 관점(a holistic point of view)"으로
성능이슈를 접근하기 힘들다는 것이다. 더구나 snapshot 들이 "분" 단위이기 때문에, "초" 단위의
일시적인 성능이슈에는 해당시점의 정보만 분리해서 분석할 수 없어서 정확한 원인규명을위해
추가자료를 취합해야한다.
(2) 장점
. DB 성능정보 뿐만아니라 OS 성능정보와 non-Oracle 프로세스 정보까지 포함한다.
. 초단위로 성능정보가 자동 취합된다.
. 결과를 OS 텍스트 파일로 저장하므로 정보취합시 DB 부하 없다
. LTOMg 라는 그래픽 툴로 결과를 확인하고 html 결과 리포트도 생성할 수 있다.
. System Profiler 시작
$ ./startltom.sh
===> Enter 3 to Start System Profiling
===> Polling frequency = 5
===> Profile Which Oracle Sessions =A
===> Profile Unix Top Processes = Y
===> Profile Unix Vmstat Info = Y
===> Profile Unix Iostat Info = Y
===> Profile CPU Statistics = Y
===> Profile Current SQL Executing = R
===> Profile Which Level = 2
. 10분 후에 System Profiler 종료
===> Enter 4 to Stop System Profiling
. 결과확인
a. $TOM_HOME/recordings/profile 에서 텍스트파일을 직접 열어서 볼수 있다.
b. LTOMg 로 진단 결과를 그래픽하게 확인할 수 있다.
$ cd $TOM_HOME/ltomg
$ java -jar ltomg.jar -i $TOM_HOME/recordings/profile/pro*.log (최종 profile 로그명)
개별그래프를 온라인으로 확인할 수도 있고, "P" 옵션을 선택하면 그래프 및 설명 포함한
HTML 형태 리포트파일이 생성된다. HTML 파일은 $TOM_HOME/ltomg/profile 의 서브디렉토리로
생성되므로 해당 디렉토리에서 HTML 을 열어보거나, 서브디렉토리를 압축해서 다른 시스템으로
이동하여 웹브라우저로 확인하다.
3. Automatic Session Tracing
- 주의! 이 기능은 Oracle Support 나 경험 많은 DBA 의 안내에따라 사용되야 한다.
덤프 설정 레벨별로 생성되는 트레이스 양을 인지하고, 시스템에 부하를 테스트 후 적용해야한다.
(1) 용도
SQL trace 는 DB 성능이슈 진단시 가장 중요한 정보 중 하나이나, 언제 이슈가 발생할지
모르고 발생 전까지는 트레이싱할 세션들을 결정할 수 없을때 기존에는 전체 DB 에
트레이스를 설정하는 위험을 감수했다. Automatic Session Tracing 은 Wait Event ,
CPU 사용량, DB User 기준으로 룰을 미리 설정하여 문제시점을 자동으로 인지하여
해당되는 세션들만 event 10046 level 12 트레이스를 자동 생성한다.
(2) 장점
. 성능이슈 발생 시점에만 10046 트레이싱 한다.
. 특정 Oracle wait event 나 CPU 사용량에따라 트레이스 결정한다.
. 룰에 해당하는 세션중 트레이싱할 세션수를 제한할 수도 있다.
. 성능이슈 원인이 되는 SQL 과 USER 를 쉽게 찾아낼 수 있다.
. 메모리 버퍼 트레이싱하므로 대량 트레이스 파일 생성을 피할 수 있고 문제시점 전후
내용만 트레이스 파일로 생성하므로 디스크 부하가 적다.
. 메모리 버퍼 크기를 지정할 수 있고, 수동으로 메모리 버퍼를 파일로 내릴 수도 있다.
. Automatic Session Tracing exit 할때 트레이싱 중이던 세션들이 자동 tracing off 되나
임의로 세션 트레이스 종료하려면 $TOM_HOME/recordings/session/stopsessions.sql 을
수행하면 된다.
EVENT=A,VALUE=B,C ====> EVENT 로 룰 설정시
where A = wait event you want to trigger on. The event must match exactly
the event name from v$event_name.
where B = the average wait time to turn on session tracing in microseconds
where C = the average wait time to dump memory tracing to a file in
microseconds. This value only necessary if using in memory
tracing. This value is ignored if you will be tracing to a file.
CPU=,VALUE=B,C ====> CPU 점유로 룰 설정시
where B = the average cpu time to turn on session tracing in 10s of
milliseconds
where C = the average cpu time to dump memory tracing to a file in 10's
of milliseconds. This value only necessary if using in memory
tracing. This value is ignored if you will be tracing to a file.
USER=A ====> DB USER 설정시
where A = db user you want to turn trace on. T
(4) 결과물
. $TOM_HOME/recordings/session - 트레이싱 히스토리
. 실제 트레이스 파일들은 udump, bdump 에 생성된다
(5) 사용예제
<문제상황>
고객사 업무기준에 모든 트랜잭션은 1초안에 끝나야하나 종종 1초이상 지속되는 트랜잭션이 있다.
LTOM 의 System Profile 으로 초단위 모니터링 결과 'global cache cr request' wait event 가
문제시점에 체크되었다. 하지만 1000 여개의 세션 중 어떤 SQL이 문제인지 알기위해 모든 세션에
10046 trace 를 설정하게되면 트레이싱 부하로 정상적인 트랜잭션까지 1초이상 걸리게된다.
<분석방법>
Automatic Session Recorder 가 'global cache cr request' wait event 가 1초이상 지속되는 시점에 해당 세션들을 트레이싱하게 설정한다. 이 방법은 전체 세션에 계속 10046 트레이스를 설정하는 것에 비하면 부하가 현저히 줄어든다.
<설정절차>
. 룰 지정
a. $TOM_HOME/init/sessionRecorder.properties 에 추가
event name 은 v$event_name 에있는 event name 과 space 까지 정확히 일치해야함.
VALUE 단위는 microsecond(1/1000000초)이고 100000(0.1초)는 메모리 트레이싱 시작 기준,
1000000(1초)은 메모리를 파일로 내리는 기준이다.
b. $ ./startltom.sh 수행
===> Enter 7 to Start Session Tracing
===> Enter a polling frequency in seconds : default 5초
===> Trace sessions to memory or file : default M to memory
===> Enter amount of memory for each trace buffer in bytes :
세션당 사용할 트레이싱 버퍼 지정 default 50000 bytes
===> Enter max processes to trace : 5-10 세션정도로 지정
c. 트레이싱 세션정보 LTOM 화면에서 모니터링
Enter 71 to Display Sessions Traced
Enter 72 to Dump All Trace Buffers
Enter 73 to Dump Specific Trace Buffer
Select option 74 to stop a specific session from being traced
d. 트레이싱 종료
===> Enter 8 to Stop Session Tracing
e. 각 10046 트레이스는 bdump, udump 에서 확인
4. LTOM 설치하기
(1) Supported Platforms: Solaris, Linux, HP-UX, AIX, Tru64
(2) 다운로드 : Metalink 에서 Note 352363.1 에 링크있음.
(3) 압축풀기
uncompress ltom.tar.Z
tar xvfp ltom.tar
(4) 초기화 : ~/ltom/tom_base/install/autoinstall.sh 수행
5. LTOM 수행하기
$ cd ~/tom_base/tom ($TOM_HOME)
$ ./startltom.sh
menu example
Enter 1 to Start Auto Hang Detection
Enter 2 to Stop Auto Hang Detection
Enter 3 to Start System Profiling
Enter 4 to Stop System Profiling
Enter 7 to Start Session Tracing
Enter 71 to Display Sessions Traced
Enter 72 to Dump All Trace Buffers
Enter 73 to Dump Specific Trace Buffer
Enter 8 to Stop Session Tracing
Enter S to Update status
Enter Q to End Program
CURRENT STATUS: HangDetection=OFF ManRec=OFF SessionRec=OFF
Please Select an Option:
6. 부록 - 디렉토리구조
tom_base /install - 설치관련 파일들
/tom /ltomg /gif - ltomg 결과 gif 파일들
/src - ltomg source 파일들
/profile - html 결과 파일들
/init - 환경설정 파일들
/hanglog - Automatic Hang Detection 시 생성 로그
/recordings /event - 사용안함
/profile - System Profiler 결과
/smart - 사용안함
/session - Automatic Session Tracing 로그
/src - ltom 용 SQL과 shell scripts
/tmp - ltom 용 임시파일
References
Note 352363.1 : LTOM - The On-Board Monitor User Guide
Note.461052.1 : LTOM System Profiler - Sample Output
Note 461050.1 : The LTOM Graph (LTOMg) User Guide
Note.461228.1 : The LTOM Graph FAQ
smon(pmon)이 recovery를 하고 있다는 것은 이미 user process가 해당 recovery를 cancel (kill -9 <process id>) 시키거나, database를 강제로 shutdown 시킨 이후의 상황이죠. 이 상황에서 recovery를 빠르게 하려면 두가지 방법이 있습니다.
1. 여러개의 프로세서가 동시에 recovery를 하는 방법
2. 한번 recovery를 할때 많은 양의 recovery를 하게 하는 방법
여러개의 프로세서로 동시에 transaction recovery 하는 방법은 oracle 8i에서 소개된 fast start parallel recovery 기능이 있습니다. 이 기능은 fast_start_parallel_recovery parameter의 설정으로 가능합니다.
이 parameter는 (LOW/HIGH/FALSE)의 값을 지정할 수 있으며 LOW 지정시에는 CPU * 2개의 parallel slave process까지 기동할 수 있으며, HIGH의 경우 CPU * 4개의 parallel slave process가 기동 됩니다. parallel recovery는 v$fast_start_servers와 v$fast_start_transactions view로 확인이 가능합니다. 가끔 parallel recovery가 smon과의 통신으로 인해 성능 저하가 발생할 경우가 있다는데, 이럴 경우 FALSE로 설정하여 serial하게 recovery를 진행할 수 있습니다.
두번째 한번 수행시 많은 양의 recovery를 하게끔 유도하는 방법은 cleanup_rollback_entries parameter로 조정가능합니다. 기본적으로 smon은 한번에 20개의 undo entriy를 정리하고 잠깐 sleep 하게 됩니다. 따라서 많은 양의 transaction recovery가 필요한 경우 이 값을 많이 늘려 한번에 수행되는 양을 많게 설정합니다. 유의할 점은 smon에게 한번에 많은 양의 recovery를 지정하였으므로 CPU를 과도하게 소모할 수 있으므로 recovery시 수행되는 다른 작업에 영향을 끼칠 수 도 있습니다.
가끔 이전 데이터 backup을 이용해서 과거의 데이터를 추출해 내야 될 경우들이 있다.
또 어느 누군가 데이터를 삭제하거나 큰 사고로 특정 시점으로 돌아가야 하는 불행한 사태가 아주 가끔 벌어지곤 한다.
다행이 backup 받은게 남아 있다면 time-recovery를 수행하면 해당 시점까지의 복구가 가능하다. 그런데, 가끔 time-base recovery를 진행하다 보면 가끔 요런 메세지가 뜨기도 한다.
ORA-1547 warning: RECOVER succeeded but OPEN RESETLOGS would get error be
간단히 말하자면 "지금 reset log로 올리면 안올라 갈수 도 있으니 좀 더 recovery 하셔야 되요" 라는 내용이다. 이러한 메세지는 datafile header에 fuzzy bit가 설정되어 발생하는 메세지 이다.
fuzzy bit는 해당 datafile에 적용되어야 할 변경사항이 아직 남아 있다는 의미이다. 더 엄밀히 말하자면 fuzzy bit를 해제할 fuzzy marker가 필요하다는 의미이다.
fuzzy bit는 hot-backup fuzzy, online fuzzy, media recovery fuzzy,absolute fuzzy의 4 가지가 있으며, 일반적으로 hot-backup fuzzy bit가 recovery시 종종 문제가 되곤 한다.
hot-backup buzzy bit는 tablespace에 begin backup이 수행되면 해당 datafile의 header에 fuzzy bit가 설정된다. 이후 end backup이 수행되면 해당 시점의 redo log내에 fuzzy marker가 등록되어 이 redo log를 적용하면 fuzzy bit가 해제 되게 된다.
따라서 hot backup으로 backup을 받은 datafile은 반드시 end backup 시점의, fuzzy marker를 갖고 있는 redo log file이 필요하게 된다.
v$datafile_header의 fuzzy column으로 fuzzy bit 상태를 알 수 있다.
이 방법은 수행중인 transaction을 무시하고 oracle을 내려버려서 smon이 transaction을 가지고 있는 rollback segment 별로 얼마나 rollback을 해야하는지 시간을 대충 산정할 수 있는 방법이죠.
현재 수행중인 transaction이 얼마나 작업을 했을까, 또 요거 끊으면 얼마나 rollback하는데 시간이 소모도리까? 이걸 알 수 있는 방법은 없을까요?
뭐 대략적인 방법이긴 하지만, v$transaction의 used_ublk, used_urec column으로 대충 산정할 수 있습니다.
다음의 SQL은 transaction을 모니터링 할 수 있는 SQL 입니다. 뭐 v$session과 v$transaction을 join 한 단순한 script 입니다.
select sid,event,p1,p2,p3, used_ublk, used_urec
from v$session s,v$transaction t
where s.taddr=t.addr
/
그럼, 우선 테스트를 할 table을 만들어 보고요..
create table test_rollback
as
select * from dba_extents
where 1=2;
이제 transaction을 만들어 보겠습니다. 시간이 좀 걸려야 하므로 1000만건 정도 만들겠습니다.
insert into test_rollback
select a.*
from dba_extents a, dba_extents b
where rownum < 10000001;
(음.. 제 PC내의 vmware에 설치한 oracle로 테스트를 하니 난리군요..
log buffer switch, log file switch completion, .. disk IO가 안따라 줍니다..--;)
암튼 이 transaction이 수행되는 과정에서 used_urec는 10초당 12000 ~ 13000 씩, 빠를 때는 15000 개씩 쌓이는 군요..
총 insert 되는 데 걸린 시간은 약 3분 55초 09 이며, USED_UREC는 150593, USED_UBLK는 3772 개 입니다. (used_urec와 실제 처리도니 row 수의 차이가 많군요.. 요건 나중에 함 알아보죠 ^^;)
rollback;
rollback시 used_urec는 10초당 대충 3000 ~ 5000개 정도 씩 정리하네요..
이 수치만 보면 rollback 작업이 실제 transaction 보다 약 3배 정도 이상 느리게 나오는 걸로 보이네요.. (실제 used_urec를 모니터링 해보면 특정 시간당 정리하는 갯수의 편차가 좀 있습니다. )
실제 rollback에 걸린 시간은 11분 09초 94 입니다. transaction 보다 약 3배 이상 걸렸군요..
그리고 transaction 생성시엔 used_urec가 10초당 15000 정도인데 반해서 rollback시는 10초당 5000 정도.. (음 생각보단 performance가 많이 안좋게 나왔네요..)
위의 테스트는 제 PC에서 수행된 거라 운영시스템에서의 환경과는 많이 다를 수 있겠지만,
일반적으로 rollback이 transaction보다 시간은 많이 소모됩니다. 이러한 테스트를 관리하는 장비에서 미리 한번씩 해본다면 실제 이러한 문제 발생시 좀더 확실하게 대처 할 수 있겠죠..
옆 부서 직원이 와서
"지금 10시간째 잘못 돌고 있는 transaction이 있는데, 이거 어쩌죠? "
"이거 죽이면 얼마나 걸릴까요"
"이거 그냥 두는 게 나을까요? 아니면 끊는게 나을까요"
라고 말씀하시면 여러분은 뭐라고 하시겠나요?
미리미리 준비하세요... ^^
물론 위에서 한 테스트는 rollback 의 시간에 영향을 많이 끼치는 cleanup_rollback_entries 의 기본 설정환경에서 진행 되었죠.
다음 번에는 cleanup_rollback_entries parameter의 값 조정과
user process에서의 recovery와 smon에서의 recovery의 시간 비교를 한번 해보겠습니다...
언제? 글쎄요.. ^^;
가끔 오라클이 대량 작업 중에 죽거나 abort로 내렸을 경우
alert log에 보면 다음과 같이 SMON이 transaction rollback을 하는 메세지를 볼 수 있다.
SMON: about to recover undo segment 4
SMON: mark undo segment 4 as available
:
"음 .. 뭔가 큰 transaction이 있었군.."
"어? 왜 아직도 안끝나지? 언제 끝나는 거야? "
다음의 SQL은 rollback 대상 extent size를 확인할 수 있는 sql 문입니다.
이외에 undo segment header dump로 확인하는 방법도 있긴하지만,
요게 시간 산정하기는 더 편하겠지요..
select KTUXEUSN,KTUXESLT,KTUXESQN,KTUXERDBF,KTUXERDBB,KTUXESTA,KTUXESIZ
from x$ktuxe
where KTUXEUSN in (select segment_id from dba_rollback_segs where segment_name = '_SYSSMU156$')
and KTUXESTA = 'ACTIVE';
다운로드: Oracle SQL Developer Data Modeler 
