예전 포스트에서 V$ view와 DBA_ view의 차이점에 대해서 언급했었습니다.

v$ view는 memory structure data를 보여주며, dba_ table(?)은 system tablespace 상의 data dictoinary data를 보여줍니다. 10g에서 awr 관련 view들이 만들어져서 dba_hist view 등에서 memory structure statistic data를 system tablespace에 저장해서 보여주기도 하죠.

이전 포스트 : dba_ view와 v$ view (oracle dictionary view & dynamic performance view)

아래의 v$fixed_view_definition view는 오라클의 fixed view의 description을 보여주는 view 입니다.
아래의 내용은 얼마전 bind 변수 값을 찾을 방법이 없을까 .. 해서 한번 뒤져보느라 query 해본 내용입니다.
역시 그런 방법은 없는 것 같더군요.. --;

SQL> select view_definition from v$fixed_view_definition where view_name = 'GV$SQL_BIND_CAPTURE';

VIEW_DEFINITION
-----------------------------------------------------------------------------------------------------------------------
select INST_ID,                 KQLFBC_PADD,                 KQLFBC_HASH,      
          KQLFBC_SQLID,
KQLFBC_CADD,                 KQLFBC_CHNO,                 substr(KQLFBC_NAME,
1, 30),                 KQLFBC_POS,
          to_number(decode(KQLFBC_DUPPOS, 65535, NULL, KQLFBC_DUPPOS)),        
        KQLFBC_OACDTY,             s
ubstr(KQLFBC_DTYSTR, 1, 15),                 KQLFBC_OACCSI,                
KQLFBC_OACPRE,              KQLFBC_OACSCL
,                 KQLFBC_OACMXL,                 decode(KQLFBC_WCAP, 0, 'NO',
'YES'),                 decode(KQLFBC_WCAP
, 0, to_date(NULL), KQLFBC_LCAP),                 KQLFBC_STRVAL,               
  decode(KQLFBC_WCAP, 0, NULL,
                            sys.sys$rawtoany(KQLFBC_BINVAL, KQLFBC_OACDTY,     
                              KQLFBC
_OACCSF, KQLFBC_OACCSI))          from x$kqlfbc;


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


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

Oracle Monitoring Tool : OraScope.Net  (0) 2010.03.08
oracle session 정리 방법 : disconnect session vs kill session  (0) 2010.03.05
sql script를 무한루프로 돌려주는 shell script <rpt>  (0) 2010.02.12
Oracle 11g R2 External Table에서 pre-processor program을 사용할 수 있습니다.  (2) 2010.02.11
다른 User로 Oracle 통계정보 옮기기.  (0) 2010.02.11

뻔한 script 지만, 나름 쓸만한 shell script 입니다.
shell로 저장한 후 뒤에 sql script와 interval을 주만 무한 루프..
active session 모니터링용으로는 뭐 GUI tool이 부럽지 않죠.. ㅋ

<사용방법>
rpt <sql script name> <interval(sec)>

<내용>
if [ $# -eq 1 ]
then
 arg2=2
elif [ $# -eq 2 ]
then
 arg2=$2
else
     echo "Usage : rpt sql term";
     exit;
fi

arg1=$1
while true
do
sqlplus -s '/ as sysdba' <<EOF
set linesize 200
set pause off
set pagesize 120
@$arg1
EOF
sleep $arg2
done



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

oracle session 정리 방법 : disconnect session vs kill session  (0) 2010.03.05
oracle fixed view의 definition 찾아보기.  (0) 2010.02.12
Oracle 11g R2 External Table에서 pre-processor program을 사용할 수 있습니다.  (2) 2010.02.11
다른 User로 Oracle 통계정보 옮기기.  (0) 2010.02.11
Oracle INVISIBLE index  (0) 2010.02.08

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

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

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

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

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

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

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




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

Oracle 11g R2 External Table에서 pre-processor program을 사용할 수 있습니다.  (2) 2010.02.11
다른 User로 Oracle 통계정보 옮기기.  (0) 2010.02.11
Oracle의 감춰진 parameter 찾아보기  (0) 2010.01.29
oracle 11g new feature : 아주 약간 편해진 spfile recovery  (0) 2010.01.27
Oracle 11g New feature : Database resident connection pooling  (0) 2010.01.26

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

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

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

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

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

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

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

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

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



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

다른 User로 Oracle 통계정보 옮기기.  (0) 2010.02.11
Oracle INVISIBLE index  (0) 2010.02.08
oracle 11g new feature : 아주 약간 편해진 spfile recovery  (0) 2010.01.27
Oracle 11g New feature : Database resident connection pooling  (0) 2010.01.26
오렌지 무료교육  (2) 2010.01.13

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

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

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

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

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

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

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

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

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

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

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




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

Oracle의 감춰진 parameter 찾아보기  (0) 2010.01.29
oracle 11g new feature : 아주 약간 편해진 spfile recovery  (0) 2010.01.27
오렌지 무료교육  (2) 2010.01.13
Oracle UNDO tablespace의 datafile 옮기는 방법 2개.  (0) 2010.01.07
Oracle Grid control - metric collection interval 변경하기.  (0) 2010.01.06

아직 해보진 않았지만..

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

I think I found it.

It's not stored in the db at all it seems.

It uses xml files in the $AGENT_HOME/sysman/admin/default_collection.

I found the "database.xmlp" file which defined the 7 Day interval for 

Tablespace Allocation.



Here's the entry in the file:



<!--

  ======================================================================

  == Category: Tablespace Allocation

  == DB Versions: pre-8 to 10gR1

  == Fetchlet: SQL

  ======================================================================

  -->

  <CollectionItem NAME="tbspAllocation">

    <ValidIf>

        <CategoryProp NAME="VersionCategory" 

CHOICES="pre8;8i;8iR2;9i;9iR2;10gR1;10gR2;10gR203;11gR1"/>

    </ValidIf>

    <Schedule>

      <IntervalSchedule INTERVAL="7" TIME_UNIT="Day"/>

    </Schedule>

    <MetricColl NAME="tbspAllocation" />

  </CollectionItem>



ALTHOUGH, I updated this to "1" day, and reloaded the agent, Grid Control is 

still showing "7" days under All Metrics for this target.

It appears I'm still missing something.




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

오렌지 무료교육  (2) 2010.01.13
Oracle UNDO tablespace의 datafile 옮기는 방법 2개.  (0) 2010.01.07
오라클 필수 백그라운드 프로세스들 (Oracle Mandatory Background Process)  (0) 2010.01.05
AWR에서 보이는 낯선 enqueue lock : RO enqueue와 KO enqueue  (2) 2009.12.28
Oracle Data Type  (0) 2009.12.18

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

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

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

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


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

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

warm start instance

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



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

Oracle UNDO tablespace의 datafile 옮기는 방법 2개.  (0) 2010.01.07
Oracle Grid control - metric collection interval 변경하기.  (0) 2010.01.06
AWR에서 보이는 낯선 enqueue lock : RO enqueue와 KO enqueue  (2) 2009.12.28
Oracle Data Type  (0) 2009.12.18
Oracle 10g crs관련 명령들  (0) 2009.12.17

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

RO-Multiple Object Reuse (fast object reuse)

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

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

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

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

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

KO-Multiple Object Checkpoint (fast object checkpoint)

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

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

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

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


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

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

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

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


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

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




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

Oracle Grid control - metric collection interval 변경하기.  (0) 2010.01.06
오라클 필수 백그라운드 프로세스들 (Oracle Mandatory Background Process)  (0) 2010.01.05
Oracle Data Type  (0) 2009.12.18
Oracle 10g crs관련 명령들  (0) 2009.12.17
oracle 다른 계정에서 synonym이나 username 지정 없이 table query 하기.  (0) 2009.12.16

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

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

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

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


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

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


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

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

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




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

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


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

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

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

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

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

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



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

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

구현:

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

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

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


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

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

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

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

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

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


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

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




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

행사 : 데이터베이스 그랜드 컨퍼런스 2009  (7) 2009.10.28
오라클의 모든 소프트웨어는 무료로 다운로드 가능합니다  (2) 2009.10.27
DB link를 통한 remote DB를 query하는데 적용되는 10가지 법칙  (2) 2009.10.23
oracle pre complier의 그 유명한 hold_cursor/release_cursor option  (0) 2009.10.22
oracle partition name 변경하기  (0) 2009.10.22


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

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

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

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

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

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

2009년 10월 13일 update :

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


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

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

2009년 10월 13일 update :

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

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





저작자표시

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

oracle partition name 변경하기  (0) 2009.10.22
oracle partition table 중간에 partition 추가하기 (split partition)  (0) 2009.10.22
Oracle SQLPLUS에서 CASE문 사용하기  (4) 2009.10.12
oracle 접속 잘 안될때 system state dump 뜨기  (2) 2009.10.12
Oracle 11g : Improving Application Performance with Result Cache  (0) 2009.10.09
 
oracle 운영 중 DB hang 이나 slowdown(거의 hang) 현상이 발생하면, 사실 거의 할 수 있는 일이 없다. monitoring이나 trace 등의 데이터는 resource 부족으로 수행이 잘 안되며, 운영 부서에서 빨리 문제 해결해 달라고 엄청 push 하기 마련이다. 대부분 resource 문제는 database restart하면 풀린다. 물론 recovery 등의 작업은 다시 올리면 또 돌긴하지만 이런 작업은 심각한 hang이나 slowdown 현상을 대개 보이지는 않는다.

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

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

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

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

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

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

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

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

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

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

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

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

참고한 자료들 입니다.

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

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

oradeblog : SQL*Plus 'prelim' connection


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




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

oracle의 Instance Load Balancing (client-side vs server-side)  (8) 2009.10.13
Oracle SQLPLUS에서 CASE문 사용하기  (4) 2009.10.12
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 EXISTS Versus IN  (1) 2009.10.06


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

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

사용자 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


가끔 database를 운영하다 보면 멀정히 잘 돌던 SQL이 plan이 바뀌어서 응답 시간이 터무니 없이 느려지곤 해 이로 인해 운영 장애가 발생하기도 하죠.

"같은 SQL을 같은 환경에서 사용하는데, SQL이 왜 느려지냐 ? "

뭐 이런 얘기를 DB 운영하는 사람이라면 가끔 들었을 법한 애기 입니다.
대개 이런 얘기는 높은 분들이 하기때문에 설명하기도 귀찮고 해서
그냥 "글쎄요.." 하고 넘어가기도 하는데,

사실 따지고 보면 데이터도 바뀌고, 변수값도 바뀌기 때문에
엄밀히 말하자면 같은 환경은 아닙니다.

bind peeking 기능으로 인해 hard parse 단계의 변수값에 영향을 받기도 하고,
automatic optimizer statistic collection 기능으로 인해 statistic 정보가 바뀔수도 있고,
dynamic sampling에 따라서 block 정보에 의해서도 바뀔수도 있죠.


이런 현상을 막으려면 부지런히 analyze 정보를 update 해주면 이러한 현상을 좀 줄어들 수도 있겠지만, 요거는 plan을 고정하기 보다는 plan을 최적화 한는 방법이죠.
뭐 잘되면 더 좋은 성능을 내겠지만,

그러나 다들 공감하시겠지만 성능 좋아진건 별로 눈에 띄지 않죠.
높은 분들도 잘 모르시고... ㅋㅋ

만약 특정 sql의 plan을 변경되지 않게 하기 위해서는 여러 방법이 있겠지만,
크게 outline을 사용하거나 hint를 sql에 적용하는 방법을 많이 사용합니다.

그러나 가끔 hint를 사용했는데, plan이 바뀌는 경우가 있는데, 이러한 경우는 대부분 hint를 꼼꼼히 부여하지 않아 optimizer가 hint를 무시하게 됩니다.

hint를 지정할때는 1) join method 2) join order 3) access method 를 전부 다 기술해 줘야 합니다.
요넘의 optimizer에게 일을 시키려면 절대 빠져나갈 구멍을 주면 안됩니다.. ㅋㅋ




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

Oracle recovery manager II : RMAN 사용전 고려해야 할 사항들  (0) 2009.02.04
Oracle recovery manager I : RMAN 시작전에 알아야 할 ..  (0) 2009.02.04
[Oracle magazine jan.2009] On Dynamic Sampling By Tom Kyte  (0) 2009.02.03
[oracle magazine jan.2009] Oracle Advanced Security - Encrypting Tablespaces  (0) 2009.02.03
Oracle Database 11g: The Top New Features for DBAs and Developers [펌]  (0) 2009.02.03

가끔 오라클이 대량 작업 중에 죽거나 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 Database' 카테고리의 다른 글

oracle 11g documents library  (0) 2009.01.18
대용량의 transaction rollback, 얼마나 걸릴까요?  (0) 2009.01.18
Oracle 9i online documents  (0) 2009.01.18
oracle 10g documents library  (0) 2009.01.18
Oracle AWR 이란?  (0) 2009.01.07

오라클에서는 oracle의 성능을 측정하기 위한 몇몇 script를 제공해 왔습니다.

기본적으로 제공되던 script 들은 oracle version에 따라 utlbstat/utlestat, statspack, awr report 등의 형태로 제공되고 있습니다.

utlbstat/utlestat은 시작시점과 끝시점에 수행하여 구간의 데이터를 OS상의 text file로 출력하고,
oracle 8i 부터 제공되었던 statspack은 job이나 cron에 등록하여 구간별 데이터를 얻을 수 있게 되었습니다. awr report는 oracle 10g부터 제공되었으며, sql 수행정보등 level에 따라 좀더 다양한 성능관련 정보들을 보여 줍니다.

awr report는 기본적으로 60분 간격으로 7일간의 데이터를 수집, 보관합니다. 이 데이터는 SYSAUX tablespace에 보관되며, 다음의 performance view에 데이터를 저장하게 됩니다.

V$ACTIVE_SESSION_HISTORY - Displays the active session history (ASH) sampled every second.
V$METRIC - Displays metric information.
V$METRICNAME - Displays the metrics associated with each metric group.
V$METRIC_HISTORY - Displays historical metrics.
V$METRICGROUP - Displays all metrics groups.

DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history.
DBA_HIST_BASELINE - Displays baseline information.
DBA_HIST_DATABASE_INSTANCE - Displays database environment information.
DBA_HIST_SNAPSHOT - Displays snapshot information.
DBA_HIST_SQL_PLAN - Displays SQL execution plans.
DBA_HIST_WR_CONTROL - Displays AWR settings.

AWR report를 생성하기 위해서는 $ORACLE_HOME/rdbms/admin 밑에 있는 awr로 시작하는 몇몇 script를 수행하여 성능 보고서를 얻게 됩니다. 주로 awrrpt.sql이나 awrsqrpt.sql로 특정 구간의 성능데이터나 특정 구간에서의 SQL 수행정보를 얻곤 하죠.



다음은 awr관련 script 들입니다.

1)The awrrpt.sql SQL script generates an HTML or text report that displays
statistics for a range of snapshot Ids.

2)The awrrpti.sql SQL script generates an HTML or text report that displays
statistics for a range of snapshot Ids on a specified database and instance.

3) The awrsqrpt.sql SQL script generates an HTML or text report that displays
statistics of a particular SQL statement for a range of snapshot Ids. Run this report
to inspect or debug the performance of a SQL statement.

4) The awrsqrpi.sql SQL script generates an HTML or text report that displays
statistics of a particular SQL statement for a range of snapshot Ids on a specified SQL.

5) The awrddrpt.sql SQL script generates an HTML or text report that compares
detailed performance attributes and configuration settings between two selected
time periods.

6) The awrddrpi.sql SQL script generates an HTML or text report that compares
detailed performance attributes and configuration settings between two selected
time periods on a specific database and instance.

awr을 control 하기 위한 몇몇 procedure가 제공되는데,
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_setting 은 interval이나 보관기간에 대한 설정을 위해 사용되며, DBMS_WORKLOAD_REPOSITORY.create_baseline는 성능 판단의 기준이 되는 baseline을 만들때 사용됩니다.

자세한 procedure 사용법은 다음과 같습니다.

How to Modify the AWR SNAP SHOT SETTINGS:
=====================================

BEGIN
  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
    retention => 43200,        -- Minutes (= 30 Days). Current value retained if NULL.
    interval  => 30);          -- Minutes. Current value retained if NULL.
END;
/

Creating the Baseline:
======================

BEGIN
  DBMS_WORKLOAD_REPOSITORY.create_baseline (
    start_snap_id => 10,
    end_snap_id   => 100,
    baseline_name => 'AWR First baseline');
END;
/

Dropping the AWR baseline:
==========================

BEGIN
  DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range(
(row_snap_id=>40,
High_snap_id=>80);
END;
/

Creating SNAPSHOT Manually:
===========================

BEGIN
  DBMS_WORKLOAD_REPOSITORY.create_snapshot();
END;
/

awr report는 수행 주기와 보관주기를 잘 선택해 놓으면, 정작 중요할 때 긴히 써먹을 수 있으니, SYSAUX tablespace 공간 아까워하지 말고, 좀 넉넉히 셋팅하는 것이 좋습니다..

위의 내용은 metalink.oracle.com의 아래 문서를 참조했습니다.

note 748642.1 What is AWR( Automatic workload repository ) and How to generate the AWR report?




저작자표시

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

Oracle 9i online documents  (0) 2009.01.18
oracle 10g documents library  (0) 2009.01.18
oracle media recovery시 챙겨봐야 할 oracle dictionary view들  (0) 2009.01.05
dba_ view와 v$ view (oracle dictionary view & dynamic performance view)  (0) 2009.01.03
oracle 10g RAC & CRS  (1) 2008.12.31

+ Recent posts

티스토리툴바