아래 blog 내용 처럼 v$lock을 조회할때 그리 시간이 많이 걸리는 경험은 없었던것 같지만..
v$lock을 조회하면 내부 fixed table join시 'MERGE JOIN CARTESIAN'이 사용되며 여기에 비용이 많이 든다는 군요.
혹시 v$lock query 결과가 늦으시면 ordered hint를 써보심이...
select s.inst_id,l.laddr,l.kaddr,s.ksusenum,r.ksqrsidt,r.ksqrsid1, r.ksqrsid2,l.lmode,l.request,l.ctime,decode(l.lmode,0,0,l.block) from v$_lock l, x$ksuse s, x$ksqrs r where l.saddr=s.addr and concat(USERENV('Instance'),l.raddr)=concat(r.inst_id,r.addr) and s.inst_id = USERENV('Instance');
보통, Test 시스템에서 가동 시스템으로 데이타를 이관하는 경우, 데이타 뿐만 아니라 통계정보도 이관하여, Test계와 가동계를 동일하게 유지하고자 하는 경우가 있읍니다. 이러한 경우,데이타는 쉽게 옮겨도 통계치는 딱히 옮기는 방법이 없습니다. 이때 아래의 방법을 사용하시면 쉽게 올길수 있읍니다.
dbms_stats.export_table_stats(
ownname VARCHAR2, -- schema name
tabname VARCHAR2, -- table name
partname VARCHAR2 DEFAULT NULL, -- partition name
stattab VARCHAR2, -- stat table name
statid VARCHAR2 DEFAULT NULL, -- optional identifier
cascade BOOLEAN DEFAULT TRUE, -- TRUE = indexes too
statown VARCHAR2 DEFAULT NULL); -- stat table schema
1. 통계정보를 임시로 저장할 Table생성.
SQL> exec dbms_stats.create_stat_table('SCOTT','STATS');
2. 원하는 table의 통계정보를 임시 table로 이관.
SQL> exec dbms_stats.export_table_stats('SCOTT','EMP',NULL,'STATS','emp stats',TRUE);
* 참고 (updated: 2010/08/03) "emp stats"로 중간에 space가 들어가니 에러가 나는 군요.. 이래서 직접 테스트를해봐야 한다니까...
SQL> exec dbms_stats.export_table_stats('SCOTT','EMP',NULL,'STATS','EMP_STATS',TRUE,'SCOTT'); BEGIN dbms_stats.export_table_stats('SCOTT','EMP',NULL,'STATS','EMP STATS',TRUE,'SCOTT'); END; * ERROR at line 1: ORA-20001: EMP STATS is an invalid identifier ORA-06512: at "SYS.DBMS_STATS", line 8240 ORA-06512: at "SYS.DBMS_STATS", line 9188 ORA-06512: at line 1
* 주의 (updated: 2010/08/03)
STATS table의 데이터를 qery 해보면 EMP table의 원래 소유자인 SCOTT이 C5 column에 명시되어 있습니다. 만약 다른 계정으로, 즉 SCOTT2 계정으로 통계정보를 옮기려면 STATS table의 SCOTT값을 SCOTT2로 update 해주어야 합니다.
두개의 DB의 계정이 동일하다면 변경해 줄 필요는 없겠죠~
3. 임시 table을 export, 그리고 target db로 import.
$exp scott/tiger tables=STATS file=expstat.dmp
$imp scott/tiger file=expstat.dmp full=y log=implog.txt
(만약 동일 DB라면 이 과정은 생략 가능합니다.)
4. 임시 Table로 부터 통계치를 원하는 table에 넣는다.
SQL> exec dbms_stats.import_table_stats('SCOTT2','EMP2',NULL,'STATS');
옵티마이저는 실행 계획의 비용을 계산하기 위한 비용 모델(Cost Model)을 갖고 있고, 이 비용 모델은 Oracle Data Dictionary에서 관리하는 다양한 통계정보를 기반으로 크게 다음과 같은 세 가지 값(measure)의 예상치를 계산한다.
Q1:
select ename, sal
from emp e, dept d
where e.deptno = d.deptno and d.loc = ‘SEOUL’
선택도
우선 선택도(selectivity)의 개념을 예로 들자. 앞에서 예로 든 질의 Q1에서 d.loc = ‘SEOUL’이라는 조건의 선택도는 dept 테이블 전체 중에서 loc의 값이 ‘SEOUL’인 레코드의 비율을 일컫는다. 옵티마이저는 선택도 계산을 통해서 해당 조건을 만족하는 레코드가 몇 건 정도가 되는지를 예측하게 된다. 옵티마이저는 만일 DBA_TABLES에 dept 테이블의 loc 칼럼의 distinct column values가 10이라면 옵티마이저는 선택도가 0.1이라고 판단하게 된다. 이때 선택도를 이와 같이 정하는 이유는 dept 테이블이 loc 칼럼들에 골고루 분포되어 있다고 가정할 때 성립한다. 그러나, 실제로 loc 칼럼의 값들이 skew되어서 분포할 수도 있다.
예를 들어, 전체 레코드의 50%가 loc 값으로‘SEOUL’을 갖는다면 잘못된 선택도 값을 얻게 된다. 이와 같이 데이타 분포가 skew되어 있는 경우, 해당 칼럼에 대한 히스토그램 정보를 DBA_HISTOGRAM 테이블에 만들어 주어야 정확한 선택도 값을 계산할 수 있다(이 경우는 0.5). 오라클 옵티마이저는 다양한 조건식의 종류에 대해 선택도를 통계정보에 기반해서 계산하는 수식을 내부적으로 갖고 있다. 그렇지만, 만일 dept 테이블이 아직 분석되지 않아서 통계정보가 없는 경우, 옵티마이저는 내부적으로 갖고 있는 디폴트 값을 선택도로 지정한다(예를 들어, 0.01).
카디널러티
앞의 dept 테이블의 전체 레코드 건수가 1000일 때, 앞에서 설명한 loc = ‘SEOUL’의 선택도가 0.1로 계산되었을 때, 조건을 만족하는 레코드 건수는 1000 x 0.1, 즉 100개로 예상할 수 있다. 이와 같이 어떤 연산을 수행한 결과로 나오는 레코드 건수를‘카디널러티(cardinality)’라 하는데, 정확한 카디널러티를 계산하는 것은 좋은 실행 계획을 만드는 데 굉장히 중요하다.
예를 들어, (T1§_T2)§_T3 순서로 테이블을 조인할 때 (T1§_T2)의 결과와 T3를 조인할 때 어떤 조인 방법을 선택하는 것이 좋을지를 결정하기 위해서는 (T1§_T2)의 크기를 정확하게 알아야 한다. 이를 위해서는 (T1§_T2) 조인의 결과 레코드가 몇 개인지를 예상할 수 있어야 한다. 이를 위해 오라클 옵티마이저는 다양한 연산의 결과 레코드의 카디널러티를 통계정보와 수식에 의해서 계산한다. T1과 T2의 조인 조건이 T1.c1 = T2.c2(이를‘P’라
표기)라 했을 때, 앞에서 설명한 선택도 계산 공식에 의해 이 조건식의 선택도 Sel(P)를 먼저 계산한 후, 이 조인의 결과 카디널러티는 Card(T1) x Card(T2) x Sel(P)가 된다.
예를 들어, T1, T2의 튜플 수가 각각 1000, 5000이고 Sel(P)가 0.01이면, 조인의 결과로 생기는 튜플 수는 1000 x 5000 x 0.01 = 5000이 된다. 그런데, Sel(P)가 조금이라도 틀리면 이후의 전체적인 비용 산정이 잘못되게 된다. 오라클 옵티마이저는 다양한 종류의 연산에 대해 내부 공식을 사용해 카디널러티를 계산한다.
비용
비용(cost)은 테이블 액세스, 조인 등을 수행하는 데 걸리는 시간을 의미하는데, 시간은 주로 디스크 I/O 수와 CPU 사용시간을 고려한다. 비용은 앞에서 계산한 통계 정보와 내부 계산식에 의해 계산된다.
예를 들어, T1§_T2를 Nested Loop 방식으로 조인할 경우 조인비용은 (T1의 데이타 블록수) + ((T1의 레코드 건수)*(T2의 액세스 비용))이 된다. 이처럼 오라클 옵티마이저는 모든 연산에 대해 소요되는 비용을 계산하는 수식을 갖고 있다. 오라클 옵티마이저는 이 세 가지 예상 값(measure)을 기반으로, 현재의 실행 계획의 예상 비용을 구한다.
위 내용은 아래 첨부된 "Oracle Technical Note : 오라클 옵티마이저의 기본 원리"의 일부를 정리한 내용입니다.
이런 놈들을 다 알필요는 없지만 아는 많큼 손발이 고생할 여지가 조금씩 줄어 듭니다. 그러니 기회가 있을 때마다 조금씩 알아 놓는게 좋겠죠 ^^;
얼마전 작업하다기 DBMS_SHARED_POOL.PURGE procedure를 유용하게 사용할 수 있는 기회가 있었어 간략하게 정리합니다.
주말의 몇몇 table의 re-org 작업이 있었는데,
시간 계산을 잘못해서 마지막 즈음에 기다리던 작업자를 위해 사용자 세션을 풀어 주었습니다. 주말이라 얼마나 들어오겠나 싶었죠..
많은 세션들이 들어오진 않았지만, 몇몇 세션들이 주로 수행되는 SQL을 이것 저것 수행하고 나갔습니다.
근데 작업 전보다 성능이 않좋다고 하더군요.
지금 re-org 작업이 있으니 조금 느릴 수도 있겠다 싶었죠. 근데, 작업 후에도 느리다고 하더군요.
Plan이 바뀌었습니다. 통계정보도 같이 넣어 주었는데 말이죠..
import로 data를 loading 하면 마지막에 해당 segment의 통계정보를 같이 적제하게 됩니다. 근데 통계정보가 다 들어가기 전에 수행된 SQL은 통계정보가 없는 상태에서 cost를 계산해서 sql plan을 만들어 버리죠. 또 이렇게 만들어진 plan 정보는 해당 cursor가 purge 되기 전까지는 계속 사용하게 됩니다.
이러한 비슷한 경우는 bind peeking 기능에 의해 일반적이지 않은 bind 값이 들어가도 발생할 수가 있습니다.
이러한 경우 shared pool flush를 시켜 해당 cursor를 purge 시킬 수 도 있겠지만 11g 이후 부터는 단일 cursor 별로 purge가 가능합니다. 10.2.0.4 version에서도 사용가능한데, 이 버전의 경우 event 설정이 필요합니다.
서점에서 몇번 오라클 관련 서적을 샀었는데, 많이 실망한 적이 많이 있었습니다.
그때는 오라클에 대해 잘 모를 때였던 거 같은데,
나중에 보니 tablespace를 작업공간이라고 해석해 놓았던 책도 있더군요.. --;
다음의 리스트는 오라클 전문가가 되기 위해 읽을만한 추천책입니다.
물론 이제 막 십년 밖에 안된 야매인 제가 추천해드리는 책은 아니고, http://oracledoug.com 블로그를 운영하는 "Douglas Ian Burns"라는 사람이 추천한 책입니다.
이분은 자그마치 18년의 경력을 갖고 있다시는 군요..
저도 18년 정도 하면 이분 정도의 공력을 갖을 수 있을까..
걱정입니다.. --;
1. Chris Date's Database In Depth from O'Reilly
2. Oracle Concepts manual
3. Expert One-on One: Oracle Written by Tom Kyte
4. Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions
5. Practical Oracle 8i
6. Cost Based Oracle: Fundamentals
7. Optimizing Oracle Performance
8. Oracle Wait Interface: A Practical Guide to Performance Diagnostics and Tuning
기존의 통계정보를 다른 Database의 동일 계정으로 옮기는 작업에 대한 내용들은 많이 기술 되어 있는데,
이름이 다른 계정은 어떻게 옮길까요?
다른 계정으로 통계정보를 옮기는 작업은 다른 데이터베이스내의 동일 계정으로 통계정보를 옮기는 작업처럼 procedure로만은 불가능합니다.
물론 작업 자체가 불가능하다는 이야기는 아닙니다.
You may not export stats from one schema name and import into a different schema name (Bug 1077535).
The schema names much match exactly.
If the target database schema name (import database) is different from the source
database schema name (export database), then you may update the table you exported the statistics
into and set the C5 column to the target schema name.
통계정보를 export 받기 전에 생성하는 statistic 정보를 저장하는 stat table에는 통계정보가 만들어진 계정의 이름이 지정되어 있어,
이를 다른 계정으로 import하려고 하면 import는 성공했다고 나오나 통계정보는 입력 되지 않습니다.
따라서 수집된 통계정보가 있는 table의 owner column의 owner를 바꿔줘야 합니다.
i.e.
"update table sd_stat set c5 = '<target schemaname>'
where c5 = '<Source Schema name>'
and statid = '<Stat Id used while exporting these stats>;"
update sd_stat set c5 = 'JBARLOW';
where c5 = 'SCOTT'
and statid = 'a';
commit;
정리해 보자면 SCOTT 계정의 SD table의 통계정보를 JBAARLOW 계정으로 옮긴다면 아래와 같이 수행해야 합니다.
* statistic 정보 수집 table 생성
SQL> exec dbms_stats.create_stat_table('SCOTT','SD_STAT');
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 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.
오라클에서 수행되는 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)
정리:
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 처리 시간에 영향을 미치게 된다.
oracle 10g에서 소개된 Automatic Optimizer Statistics Collection 기능에 대해 간단히 얘기해보겠습니다. 대부분 이 기능을 끄고 안쓰는 걸로 알고 있는데 요놈 때문에 optimizer가 plan을 바꿔 곤란한 경우가 가끔 생기기도 합니다. 하지만 어찌 보면 굉장히 매력적인 기능이 아닐 수 없습니다. 대부분 데이터 베이스가 엄청 커져서 analyze 하는 시간도 오래 걸릴 뿐 아니라 더이상 오라클에서는 RULE base는 지원안한다고 하니..
그래서 지금은 안쓰지만 언젠가 쓰게될 요 기능에 대해 좀 알아보죠..
그러면 언제 automatic optimizer statistics collection이 시작 되며, 얼마나 수행될까요?
dba_scheduler_jobs를 보면 gather_stats_job이라는 job으로 등록되어 있습니다. 요 넘은 기본으로 저녁 10시 부터 8시간 동안, 또 토요일 00시 부터 2일 간 수행되게 되어 있죠.
만약 평일 8시간, 주말 2일간 수행을 다 못하면 어찌 될까요?
만약 수행 중 다 끝나지 않은 table의 statistic 정보는 원복 하게 됩니다. 그럼 다음날 에 다시 하겠죠? 아마 ~
그럼 얘는 뭘 보고 대상을 선정할 까요?
10g는 기본적으로 monitoring 기능이 enable 되어 있습니다. dba_tables 등의 view를 보면 monitoring이라는 column에 YES로 기본으로 박혀있죠.. dba_tab_modifications에 해당 table의 변경 내역을 저장하고 변경 내역이 10% 이상이 되면 STEAL 상태가 되어 Automatic Optimizer Statistics Collection의 대상이 됩니다.. 그리고 job이 수행되면 dba_tab_modifications의 데이터는 cleanup 됩니다.
가끔 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에게 일을 시키려면 절대 빠져나갈 구멍을 주면 안됩니다.. ㅋㅋ
기본적으로 제공되던 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;
/
오라클 모니터링 할 때 모니터링 툴을 많이 사용하시죠.
oracle의 Sql Developer, 웨어밸리의 Orange, 퀘스트의 TOAD, 엑셈의 MaxGuage, 데이타헤븐의 Intuvision,..
GUI tool을 사용하면 한눈에 시스템 전체를 파악하기 용이하고, 마우스 클릭 몇번으로 금방 정보를 확인할 수 있습니다. 그러나 oracle의 data dictionary view나 dynamic performance view의 모든 내용을 포함하기는 어렵고, 또 다 포함하고 있다고 해도 그에 대한 사용 방법에 대해 사용자가 인지하고 있긴 어렵죠.
일반적으로 DBA 들이 주로 사용하는 dictionary view는 열손가락 꼽을 정도 밖에 안되지 않을까 합니다. dba_users, dba_tablespaces, v$tablespace, dba_data_files, v$filestat, v$sysstat, v$session_wait ... (음, 10개는 넘겠군요.. )
그런데 dictionary view를 보면 dba_로 시작하는 view와 v$로 시작하는 view 들이 있습니다.
(물론, all_ 이나 user_, gv$로 시작하는 view 들도 있긴 하죠)
dba_ view와 v$ view의 차이점을 무얼까요?
이 차이에 대해 평소에 별로 생각해 본적이 없어 google에서 찾아 봤더니 oracle faq's blog에 잘 설명 되어 있네요.
V$% views return data from memory structures. Data is lost when the instance is restarted.
DBA_% tables return data from the database's data dictionary (SYSTEM tablespace). Data persists across instance restarts.
v$ view는 memory structure data를 보여주며, dba_ table(?)은 system tablespace 상의 data dictoinary data를 보여줍니다. 10g에서 awr 관련 view들이 만들어져서 dba_hist view 등에서 memory structure statistic data를 system tablespace에 저장해서 보여주기도 하죠.
Static Data Dictionary Views and Dynamic Performance Views are the core of database administration. They are of crucial importance. Whoever wants to learn Oracle database administration has to get familiar with these views.
The Data Dictionary tables contain meta data on objects, privileges, roles, users etc.
Whenever you issue DML statements Oracle consults its data dictionary to make sure a table is there, it has the right columns and you have privileges on it. And whenever you issue DDL statements Oracle updates its data dictionary.
All information needed by an instance on its database is kept in the data dictionary. Except some storage related information which is in the datafile headers and some information in controlfile and spfile which is needed at instance startup.
At runtime the oracle instance stores information about itself, about its CPU, memory or I/O performance, wait statistics and everything else which is going on at the instance in dynamic performance tables. Dynamic performance tables are non persistent. You can’t see past instance startup. After shutdown or instance crash all information contained in dynamic performance tables is lost. *1)
Through static dictionary views users can get at information kept in data dictionary tables.
And through dynamic performance views users can look at non persistent runtime information contained in dynamic performance tables.
But why are those views that important, you might ask? All information necessary for database administration can be found in those static dictionary views. And basically all information necessary for instance diagnostic and tuning can be obtained through those dynamic performance views at runtime.
Of course, GUI tools like Enterprise Manager or SQL Developer offer the same information
in a more user friendly manner. But all those GUI tools rely on static dictionary views and dynamic performance views.
As a DBA you will likely get into situations where there is no GUI tools available. At times you will be alone with your SQL*Plus skills. Your mastery of data dictionary and performance views will make all the difference!
동감입니다 !
tool을 사용하는 것이 더 효율적이라 생각하실지 모르지만
마지막엔 DBA가 직접 sqlplus에서 dictionary view들을 query해야 하는 경우가 허다하죠..
현재 수행한 SQL의 plan을 확읺는 방법 2가지입니다..
select * from table(dbms_xplan.display);
SQL> desc dbms_xplan
FUNCTION DISPLAY RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TABLE_NAME VARCHAR2 IN DEFAULT
STATEMENT_ID VARCHAR2 IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
explain plan
for
sql ..
utlxpls.sql 수행
select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));
SQL> desc dbms_xplan
FUNCTION DISPLAY RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TABLE_NAME VARCHAR2 IN DEFAULT
STATEMENT_ID VARCHAR2 IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
FUNCTION DISPLAY_AWR RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_ID VARCHAR2 IN
PLAN_HASH_VALUE NUMBER(38) IN DEFAULT
DB_ID NUMBER(38) IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_ID VARCHAR2 IN DEFAULT
CURSOR_CHILD_NO NUMBER(38) IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
FUNCTION PREPARE_RECORDS RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
PAR1 VARCHAR2 IN
PAR2 VARCHAR2 IN
02winter_optimizer_1.pdf
