'ORACLE/TUNING' 카테고리의 글 목록 (3 Page)

'ORACLE/TUNING'에 해당되는 글 52건

2010.05.24 DBMS_XPLAN - 2.포맷 설정하기
2010.05.24 DBMS_XPLAN - 1.실행계획
2010.03.31 HWM(High Water Mark)란?
2010.03.23 AWR - Monitoring & Tuning
2010.03.23 AWR (Automatic Workload Repository)
2010.03.23 Statspack 생성/삭제/Sanpshot생성
2010.02.11 [Oracle 물리설계] 제5부 영역감시
2010.01.26 Oracle Hidden Parameter 란
2010.01.18 FAST_START_MTTR_TARGET
2010.01.12 EXPLAIN PLAN(실행계획) 이란

DBMS_XPLAN - 2.포맷 설정하기

DBMS_XPLAN 패키지의 장점은 포맷을 자유로이 설정한다는것
작년 겨울에 About DBMS_XPLAN - 1.실행계획 이라는 글에서 실행계획의 세부항목을 소개한바 있다.
이 패키지의가장 뛰어난 특징은 사용자가 출력 포맷을 설정하여 원하는 정보만 얻을수 있다는 것이다.
따라서 이번에는 DBMS_XPLAN 패키지 사용시 포맷설정을 자유롭게 하기 위한 Format Controller를 소개하려 한다.

Format Controller는 아래와 같이 3가지 종류가 있다.

1) 기본 Format Controller : 반드시 적용되어야 하는 기본적인 Controller 이다.
적용하지 않더라도 자동으로 기본값으로 적용된다.

2)세부 Format Controller: 기본 포맷정보에 의해서 표시되거나 생략되는 되는 세부적인 포맷을 Control 한다.
이 Control은 + 표시로 추가하거나 - 표시로 생략이 가능하다.

3) 실행통계 Format Controller: 이 Control을 적용하면 실행시의 PGA 통계를 출력한다.

이제 한가지씩 상세히 살펴보자.

1) 기본 Format Controller
1.basic    : 가장 기본적인 포맷으로서 id, Operation, Object Name을 출력한다.
2.typical : basic 옵션에서 한발더 나아가서 옵티마이져가 에상할수 있는 모든것들을 보여준다.
   출력되는 정보로는 예상 row, 예상 bytes, 예상 temporary space 사용량, cost, 예상시간,
   Predicate Information(Operation 별로 access 및 filter 정보) 이다.
3.serial : typical 과 같으나 parallel 쿼리사용시 관련 정보가 나오지 않는다.
4.all : plan 정보는 typical 과 같으나 plan 이외의 정보중에서 Outline Data 정보를 제외하고 전부 출력한다.
5.advanced : all 과 같지만 Peeked Binds, Outline Data, note 등을 더보여준다.

2) 세부 Format Controller
1.alias :Operation 별로 쿼리블럭명과 object alias 를 control 한다.
   plan 의 하단에 위치하며 쿼리변형이 발생하거나 복잡한 쿼리 튜닝의 경우 유용하다.
2.bytes : plan 상의 E-Bytes 정보를 control 한다.
3.cost :plan 상의 Cost (%CPU)를 control 한다.
4.note : 결과중 가장 마지막에 위치하며 여러가지 유용한 정보를 보여준다.
예를 들면 dynamic sampling 이 사용되었는지의 혹은 plan_table 이 old 버젼이므로 새로만들어야
한다는 등의 유용한 정보를 나타낸다.
5.outline : Outline Data를 control 한다. USER 가 작성한 힌트와 옵티마이져가 추가한 내부적인 힌트들이
   포함된다. 쿼리변형이 발생하거나 복잡한 쿼리 튜닝의 경우 유용하다.
6.parallel : PARALLEL 쿼리인경우 TQ, IN-OUT, and PQ Distrib 등의 정보를 control 한다.
7.partition :파티션 ACCESS 가 포함된경우 Pstart(시작 파티션) and Pstop(종료 파티션) 등의 정보를 control 한다.
8.peeked_binds : BIND 변수의 값을 control 한다. 단 _optim_peek_user_binds 파라미터의 값이 TRUE 로
되어있는 경우만 해당되며 파라미터는 세션단위로 수정이 가능하다. EXPLAIN PLAN 을
사용한 경우에는 나타나지 않는다.
9.predicate : Predicate Information을 control 한다. Operation 별로 access 및 filter 정보를 나타낸다.
   일반적인 튜닝시 가장 눈여겨 보아야할 정보이다.
10.projection : projection information을 control 한다. Operation 별로 select 되는 컬럼정보를 나타낸다.
11.remote : DBLINK 를 사용힐때 REMOTE 쿼리의 수행정보를 control 한다.
12.rows : plan 상의 E-Rows수를 control 한다.

3) 실행통계 Format Controller
이정보들은 DBMS_XPLAN.DISPLAY 함수에는 적용되지 않는다. 왜냐하면 explain plan 은 쿼리가 실제 수행되는것이 아니므로 실행통계정보가 없기 때문이다.
또한 DBMS_XPLAN.DISPLAY_CURSOR 나 DBMS_XPLAN.DISPLAY_AWR 등의 함수 수행시에도 GATHER_PLAN_STATISTICS 힌트를 주거나 아니면 파라미터 STATISTICS_LEVEL = ALL 로 되어 있어야 출력이 가능하다.

1.allstats : I/O 통계정보(Buffers, Reads, Writes)와 PGA 통계정보(OMem, 1Mem , Used-Mem, Used-Tmp,
Max-Tmp 등)를 동시에 control 한다.
2.iostats : I/O 통계정보(Buffers, Reads, Writes)를 control 한다.
3.last : 실행통계 출력시 이 control을 명시하면 가장마지막에 수행된 실행통계를 출력한다.
이 control을 명시하지 않으면 실행통계의 누적치를 출력하므로 주의가 필요하다.
4.memstats :PGA 통계정보(OMem, 1Mem , Used-Mem, Used-Tmp, Max-Tmp 등)를 control 한다.
5.runstats_last : iostats control 과 last control 을 합친것과 같다.
이 control은 Oracle 10g Release 1 에서만 사용할수 있다.
6.runstats_tot : iostats control과 동일하다. 이 control은 Oracle 10g Release 1 에서만 사용할수 있다.

주 의사항 : runstats_last 와 runstats_tot 를 제외한 4가지의 control은 Oracle 10g Release 2 에서만 사용할수 있다.

그럼 이제 적용해볼까?
위에서 설명한 Controller 를 이용하여 Format 을적용해보자.

SElECT /*+ gather_plan_statistics */ *
FROM EMP E
WHERE E.DEPTNO = :B1
AND ROWNUM <= 100
ORDER BY EMPNO;

이후로는 위의 SQL 은 동일하므로 생략된다.

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL, 'advanced allstats last' ) );

아래의 plan 은 지면관계상 잘려서 2줄로 나타내었음을 이해해주기 바란다.

----------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp |
----------------------------------------------------------------------------------
| 1 | SORT ORDER BY    |    |    1 |    100 | 2600 | 153M|
|* 2 | COUNT STOPKEY |    |    1 |    | | |
| 3 |    TABLE ACCESS BY INDEX ROWID| EMP    |    1 | 2002K|    49M| |
|* 4 | INDEX RANGE SCAN    | EMP_N1 |    1 | 2003K| | |
----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------
19034 (1)| 00:03:49 |    9 |00:00:00.01 | 4 | 2048 | 2048 | 2048 (0)|
|    |    9 |00:00:00.01 | 4 | | |    |
4126 (1)| 00:00:50 |    9 |00:00:00.01 | 4 | | |    |
   989 (1)| 00:00:12 |    9 |00:00:00.01 | 3 | | |    |
-----------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   3 - SEL$1 / E@SEL$1
   4 - SEL$1 / E@SEL$1

Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
OPT_PARAM('_optim_peek_user_binds' 'false')
OPT_PARAM('_bloom_filter_enabled' 'false')
OPT_PARAM('_optimizer_connect_by_cost_based' 'false')
OPT_PARAM('optimizer_index_cost_adj' 25)
OPT_PARAM('optimizer_index_caching' 90)
FIRST_ROWS(1)
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "E"@"SEL$1" ("EMP"."DEPTNO"))
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ROWNUM<=100)
   4 - access("E"."DEPTNO"=:B1)

Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - (#keys=1) "E"."EMPNO"[NUMBER,22], "E"."DEPTNO"[NUMBER,22], "E"."EMPNO_VARCHAR"[VARCHAR2,40], "E"."JOB"[VARCHAR2,2], "E"."HIREDATE"[DATE,7]
   2 - "E"."EMPNO"[NUMBER,22], "E"."EMPNO_VARCHAR"[VARCHAR2,40], "E"."JOB"[VARCHAR2,2], "E"."HIREDATE"[DATE,7], "E"."DEPTNO"[NUMBER,22]
   3 - "E"."EMPNO"[NUMBER,22], "E"."EMPNO_VARCHAR"[VARCHAR2,40], "E"."JOB"[VARCHAR2,2], "E"."HIREDATE"[DATE,7], "E"."DEPTNO"[NUMBER,22]
   4 - "E".ROWID[ROWID,10], "E"."DEPTNO"[NUMBER,22]

'advanced allstats last' 포맷은 출력되는 정보가 너무많아
'advanced allstats last' 포맷을 적용하였으므로 DBMS_XPLAN.DISPLAY_CURSOR 가 보여줄수 있는 모든
정보를 출력 하였다. 단 지면 관계상 가장 처음에 나오는 SQL TEXT 와 sql_id, child number, plan_hash_value 등은 생략하였다. 많은정보를 생략하였음에도 불구하고 일반적인 튜닝시 필요가 없는 정보가 모두 출력되고 말았다.

이제 위에서 정의된 각 Controller 를 이용하여 여러분만의 Format 을 만들어보자.
필자의 경우 가장 선호하는 포맷은 아래의 두가지 이다.

권장되는 포맷유형 2가지

1. 쿼리변형이 없는 단순 쿼리 튜닝의 경우:

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL, 'allstats last -rows +predicate'));

포맷을 'allstats last -rows +predicate' 로 주었으므로 예측 row 수(E-row) 가 생략되고 실행통계와
Predicate Information 만을 출력한다.
아래의 plan 또한 너무길어 지면관계상 2줄로 나타내었다.

-------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time |
-------------------------------------------------------------------------------
| 1 | SORT ORDER BY    |    |    1 |    9 |00:00:00.01 |
|* 2 | COUNT STOPKEY |    |    1 |    9 |00:00:00.01 |
| 3 |    TABLE ACCESS BY INDEX ROWID| EMP    |    1 |    9 |00:00:00.01 |
|* 4 | INDEX RANGE SCAN    | EMP_N1 |    1 |    9 |00:00:00.01 |
-------------------------------------------------------------------------------

-------------------------------------
   Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------
   4 | 2048 | 2048 | 2048 (0)|
   4 | | |    |
   4 | | |    |
   3 | | |    |
-------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ROWNUM<=100)
   4 - access("E"."DEPTNO"=:B1)

깔 끔하게 꼭필요한 정보만 출력 되었다.

2.쿼리변형이 발생하거나 복잡한 쿼리 튜닝의 경우.

SELECT * FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL, 'allstats last -rows +alias +outline +predicate'));

'allstats last -rows +alias +outline +predicate' 포맷을 사용하면 Query Block Name / Object Alias 정보와 Outline Data 정보가 추가로 출력된다.
아래의 plan 도 지면관계상 2줄로 나타내었다.

-------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time |
-------------------------------------------------------------------------------
| 1 | SORT ORDER BY    |    |    1 |    9 |00:00:00.01 |
|* 2 | COUNT STOPKEY |    |    1 |    9 |00:00:00.01 |
| 3 |    TABLE ACCESS BY INDEX ROWID| EMP    |    1 |    9 |00:00:00.01 |
|* 4 | INDEX RANGE SCAN    | EMP_N1 |    1 |    9 |00:00:00.01 |
-------------------------------------------------------------------------------

-------------------------------------
   Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------
   4 | 2048 | 2048 | 2048 (0)|
   4 | | |    |
   4 | | |    |
   3 | | |    |
-------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   3 - SEL$1 / E@SEL$1
   4 - SEL$1 / E@SEL$1

Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
OPT_PARAM('_optim_peek_user_binds' 'false')
OPT_PARAM('_bloom_filter_enabled' 'false')
OPT_PARAM('_optimizer_connect_by_cost_based' 'false')
OPT_PARAM('optimizer_index_cost_adj' 25)
OPT_PARAM('optimizer_index_caching' 90)
FIRST_ROWS(1)
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "E"@"SEL$1" ("EMP"."DEPTNO"))
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ROWNUM<=100)
   4 - access("E"."DEPTNO"=:B1)

쿼리변형이 발생한경우나 뷰(혹은 인라인뷰) 등을 튜닝할경우 아주 적합한 옵션이다. 하지만 특이한경우
Column Projection Information 이 필요한경우도 있다. 이경우는 +projection 포맷을 추가해주면 된다.

자신만의 적절한 포맷이 필요해
출력되는 정보의 양이 너무 많으면 소화 하기가 힘들고 너무 적으면 튜닝하기가 어려워진다. SQL이 아무리 복잡하고 다양한 경우가 있더라도 2~3 가지의 Format 조합으로도 충분하다. 여러분 각자의 입맛에 맞는 Format 을 개발해보길 바란다. 물론 그러기 위해서는 각각의 Controller 들과 친해질 필요가 있다.

[출 처] About DBMS_XPLAN - 2.포맷 설정하기|작성자 치킨플라이

저작자표시 비영리

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

SQL TRACE (0)	2011.10.21
[SQL튜닝] 오라클 CPU 많이 차지하는 쿼리 찾기 (0)	2011.03.28
DBMS_XPLAN - 1.실행계획 (0)	2010.05.24
HWM(High Water Mark)란? (0)	2010.03.31
AWR - Monitoring & Tuning (0)	2010.03.23

Posted by [PineTree]

DBMS_XPLAN - 1.실행계획

최근의 많은수의 사람들이 DBMS_XPLAN 패키지를 사용하여 튜닝을 하고 있다.
필자는 DBMS_XPLAN 패키지에 대한 너무많은 질문공세 때문에 아예 블로그에 올릴 결심을 하였다.
오늘은 DBMS_XPLAN 패키지에 대한 첫번째 이야기로 가장중요한 실행계획에 대하여 조목조목 따져보려고 한다.

DBMS_XPLAN 패키지는 9i 부터 점점 발전하여 지금은 Trace + tkprof 보고서와 자웅을 겨룰 정도로 발전하고
있다.
DBMS_XPLAN 패키지내의 함수는 10g R2 기준으로 6개 이지만 가장 자주 사용하는 함수는 아래의 3가지 이다.

1.DISPLAY --> 예측 실행계획을 보여준다.
2.DISPLAY_CURSOR --> 실제 실행된 실행계획을 보여준다.
3.DISPLAY_AWR --> 실제 실행된 실행계획을 보여준다.

오늘의 주제는 실행계획상의 각항목에 대한 설명이므로 3개의 함수에 대한 자세한 설명은 다음에 계속하여
연재할 계획이다.

실행계획은 패키지 내의 3가지 함수(display, display_cursor, display_awr)를 통해 모두 조회가 가능하다.
아래의 스크립트는 display_awr 의 예제이며 sql_id 만 구하면 언제든지 실행될수 있다.

select * from table(dbms_xplan.display_awr(:v_sql_id,null,null,'advanced allstats last'));

위 PLAN 은 DBMS_XPLAN 패키지의 format 항목을 Advanced 로 했을 경우에 나타나는 Plan 의 모습이다.
아래는 위의 Plan 항목 하나하나에 대한 자세한 설명이다.
물론 위의 예제는 실행계획의 모든 항목이 나온것은 아니다.
예를 들면 파티션테이블을 사용하지 않았으므로 Partiton 관련 항목이 빠진것이다.

DBMS_XPLAN 패키지의 실행계획의 항목은 아래처럼 크게 7개로 나눌수 있다.

주의사항:

파랑색부분( 5), 6), 7)번 에해당됨)은 실행통계가 있을경우만 해당된다.

display_cursor, display_awr 의 경우 statistics_level 파라미터를 all 로 설정하거나 SQL 에 gather_plan_statistics 를 사용한경우 실행통계를 볼수 있다.

1)Basics 항목 (Always Available)

Id :각 Operation 의 ID 임.

* 가 달려있는 경우는 predicate 정 보에 access 및 filter 에 관한정보가 나옴을 표시한것임.

Operation : 각각 실행되는 JOB 을 나타냄

row source operation.의 줄임말임.

Name : Operation 이 엑세스하는 테이블 및 인덱스를 나타냄.

2)Query Optimizer Estimations(옵티마이져의 예상 row 수 및 bytes, cost , temp 사용량)

Rows (E-Rows) : 각 operation 이 끝났을 때 return되 는 건수를 나타냄.

이것은 예측 건수 이므로 실제 건수와는 다름.

Bytes (E-Bytes) : 각 operation 이 return 한 byte 수.
예상치 이므로 실제 받은 byte와는 다름

TempSpc : 각 operation. 이 temporary space 를 사용한 양(예상치임)

Cost (%CPU) : 각 operation 의 Cost. (예상치 임)

괄호안의 내용은 CPU Cost 의 백분율임.

이 값은 Child Operation 의 Cost 를 합친 누적치임.

Time : 예측 수행시간

3)Partitioning (파티션을 엑세스 할경우만 나타남)

Pstart : 파티션을 엑세스 하는경우 시작파티션을 나타냄

상수로 들어올때는 파티션 번호로 나타나며 변수로 들어올때는 KEY 로 나타남

Pstop : 마지막 파티션을 나타냄.

따라서 patart, pstop 를 이용하면 access 한 파티션을 알수 있음.

4)Parallel and Distributed Processing (Parallel Processsing 을 사용하거나 DB-LINK 를 사용하는경우)

Inst : DB-LINK 명(사용하는 경우만 나타남).

TQ : PARALLEL SQL 사용시 table queue 명을 나타냄
TQ 는 PARALLEL SLAVE 간의 통신을 담당함.

IN-OUT : Parallel processing 시에 각각의 Operation 이 Serial 로 실행되는지 parallel 로 진행되는지를
나타냄.

PQ Distrib : Parallel processing 시에 producers 와 consumers 간의 데이터의 분배방식을 나타냄.

* 이부분의 자세한 내용은 아래를 참조하기 바란다.
1.http://scidb.tistory.com /entry/Parallel-Query-의-조인시-Row-Distribution
2.http://scidb.tistory.com /entry/Parallel-Query-의-조인시-또다른-튜닝방법pxjoinfilter

5)Runtime Statistics (실제 수행시간밑 실제수행건수)

Starts : 각 operation 을 try 한 건수(예 를 들어 nested loop join 이라면 인덱스를 여러 번 scan 함)

A-Rows : 각 operation 이 return 한 건수

A-Time : 실제 실행시간

0.1초 까지 나타남 (HH:MM:SS.FF).

이값은 Child Operation 의 Cost 를 합친 누적치임.

6)I/O Statistics (I/O 관련하여 READ / WRITE 한 블록수)

Buffers : 각 Operation 이 메모리에서 읽은 block 수.

Reads :각 Operation 이 disk 에서 읽은 block 수.

Writes : 각 Operation 이 disk 에 write한 block 수.

7)Memory Utilization Statistics(hash 작업이나 sort 작업시 사용한 메모리 통계)

OMem : optimal execution 에 필요한 메모리(예측치임).

1Mem : one-pass execution. 에 필요한 메모리(예측치임)

O/1/M : 각 operation 이 실행한 optimal/one-pass/multipass 횟수가 순서대로 표시됨.

Used-Mem : 마지막 실행시의 사용한 메모리

Used-Tmp : 마지막 실행시 메모리가 부족하여 temporary space 를 대신 사용할 때 나타남.

보이는값에 1024 를 곱해야함.
예를들어 32K 로 나타나면 32MB 를 의미함.

Max-Tmp : 메모리가 부족하여 temporary space 를 사용할 때 최대 temp 사 용량임.

USED-TMP 와 다른점은 마지막 수행시가 아니라 SQL을 여러 번 수행했을경우에
항상 최대값만 보인다는 것이다.
보이는값에 1024 를 곱해야함.
예를들어 32K 로 나타나면 32MB 를 의미함.

결론:
이상으로 PLAN 상에 나오는 각 항목에 대하여 빠짐없이 알아보았다.
특히 Runtime 통계, I/O 통계및 Memory 통계중의 일부항목은 Tkprof 보고서에도 나오지않는 정보들로
튜닝시 요긴하게 사용할수 있다는 점을 기억 해야 한다.
다음시간에는 3가지 함수의 여러가지 옵션에 대하여 알아볼것이다.

Reference :
1.Ttroubleshooting Oracle Performance (Christian Antognini)
2.Oracle 10g Manual : PLSQL Packages and Types Reference

[출처] About DBMS_XPLAN - 1.실행계획|작성자 치킨플라이

저작자표시 비영리

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

[SQL튜닝] 오라클 CPU 많이 차지하는 쿼리 찾기 (0)	2011.03.28
DBMS_XPLAN - 2.포맷 설정하기 (0)	2010.05.24
HWM(High Water Mark)란? (0)	2010.03.31
AWR - Monitoring & Tuning (0)	2010.03.23
AWR (Automatic Workload Repository) (0)	2010.03.23

Posted by [PineTree]

HWM(High Water Mark)란?

HWM(High Water Mark)란 저장공간을 갖는 세그먼트 영역에서 사용한 적이 있는 Block과 사용한 적이 없는 Block 의 경계점을 의미한다.
Block 은 위에서 부터 채워진다.
데이타파일은 HWM을 가지지 않으며, 세그먼트만이 HWM를 가진다.

특성

High Water Mark는 증가하기만 한다.
Truncate 명령을 사용하면 Header Block 위치로 돌아오게 된다.(0으로 set)
Delete 명령은 HWM의 변화를 주지 않는다.
5 block 씩 증가한다. (초기 5 block이 될때까지는 1 block씩 증가한다.)
Table의 Full Scan량과 동일하다.
USER_TABLES.AVG_SPACE의 기준이 된다.

USER_TABLES.BLOCKS	HWM와 같은 값으로 단위는 block 이다. segment에 의해 사용된 적이 있는 block 수
USER_TABLES.EMPTY_BLOCKS	할당된 블록 중에서 HWM 위에 미사용으로 남아있는 공간의 블록 수. HWM 위의 block
USER_TABLES.AVG_SPACE	한 블록당 평균 FREE SPACE SIZE. 단위는 Byte 이다. Header Block을 제외한 HWM 안에 있는 Block들에 대해서 평균을 구하므로 오차가 있을 가능성이 많다.

테이블 사이즈 계산	테이블 사이즈 = (blocks + empty_blocks + 1) = 사용블록 + 비어있는블록 + segment head block(1)

HWM 측정방법

특정테이블의 HWM를 알기 위해서는 ANALYZE TABLE 명령을 수행하여 통계정보를 수집한다.
```
 ANALYZE TABLE <tablename> ESTIMATE/COMPUTE STATISTICS; 
```

수집한 통계정보를 가지고 HWM를 측정한다.

SELECT blocks, empty_blocks, num_rows

FROM   user_tables

WHERE table_name = <tablename>;

레코드를 삭제하는 것은 HWM의 위치를 아래로 옮기지 않는다.
그러므로, 레코드를 삭제하는 것은 EMPTY_BLOCKS 의 수치를 늘리지 않는다.

TUNING

계산된 테이블사이즈가 세그먼트의 크기(USER_SEGMENTS.BLOCKS)와 다르면 Analyze를 다시 해주어야 한다.
EMPTY_BLOCKS가 BLOCKS에 비해 너무 크면, INITIAL_EXTENT나 NEXT_EXTENT를 줄여야 한다.
입력된 데이터 건수에 비해 HWM가 너무 높다면 segment를 재생성을 고려해야 한다.
이유 는 실제 데이터 건수는 작은데 비해 Full Scan시에 HWM까지 검색해 Disk I/O가 많아져 부하가 증가하게 된다.

USER_TABLES.AVG_SPACE가 너무 크면 테이블을 재생성해야 한다.
PCT_USED 와 PCT_FREE 의 중간 이상

DB_BLOCK_SIZE * (100 - PCT_USED + PCT_FREE) / 200 
[출처] Oracle HWM(High Water Mark) |작성자 리원아빠

저작자표시 비영리

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

DBMS_XPLAN - 2.포맷 설정하기 (0)	2010.05.24
DBMS_XPLAN - 1.실행계획 (0)	2010.05.24
AWR - Monitoring & Tuning (0)	2010.03.23
AWR (Automatic Workload Repository) (0)	2010.03.23
Statspack 생성/삭제/Sanpshot생성 (0)	2010.03.23

Posted by [PineTree]

AWR - Monitoring & Tuning

AWR (Automatic Workload Repository)

- SYSAUX Tablespace (SYS소유) 에 존재하는 Tables
- Snapshot 을 저장
- 기본 7 일간 저장 (DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE을 통해 Baseline 화 하여 삭제안되게 할 수 있다.)
- MMON(기본 1시간단위 수집) 에 의해 생성된 Snapshot 을 DMA 방식으로 메모리의 내용을 저장한다
- 저장된 결과를 ADDM(분석) 또는 DBA 가 분석한다
- Performance(v$) 관련 Data들이 저장
- Infra Structure 역할을 한다.
- 문제확인과 자체튜닝을 가능케해준다

cf.
Baseline : Database 가 정상작동하고 있을때의 통계값 (문제발생시 비교를 위한값)

ADDM (Automatic Database Diagnostic Monitor)
- 분석 후, 기본적인 이상 발생시 알려주는 역할
- 분석에 관하여 핵심적이고 본질적인 역할 수행
- DBA 의 분석작업을 빠르게 제공 (분석, 도움)
- 다른 Advisory 와 달리 자동으로 실행된 다

ADDM 과 MMON
동작 : MMON 이 Snapshot 을 생성한 후 ADDM 에 알리면 ADDM 은 가장 최근의 Snapshot 과 비교하여 이상여부를 분석하여 화면으로 출력하고(EM) AWR 에 저장(ADDM Result) 한다

Alert 의 종류
1. Tool 이 발생시킨 Alert
2. DBA 가 설정한 값에 의해 발생한 Server Alert

1. Metric-Based Alert : DBA_OUTSTANDING_ALERTS 에 기록후 해결되면 지워진다. 모든 이전 기록은 DBA_ALERT_HISTORY 에 남는다
2. Event-Based Alert : 즉시 DBA_ALERT_HISTORY에 기록

Server Alerts
동작 : 문제 확인시 AWR 에 저장(ADDM Result) 하면서 Server Alerts Queue 에 작업을 넣고 화면으로 출력한다(EM)
- DBA 가 설정한 (Metrics 의 설정값) 특정상황 발생시 서버측에서 즉각적으로 경고
- EM 및 DBMS_SERVER_ALERTS Package 사용하여 수동 설정
- Resumable Session Suspended, Snapshot Too Old 같은 상황에 대해 미리 경고를 받을 수 있다.

Advisory Framework

- Performance 분석 및 Failure(11g) 에 대한 도움말 기능
- ADDM 만 자동으로 실행된다
- DBMS_ADVISOR Package 를 사용

Automated Tasks
- 주기적인 (B&R 과 같은) 작업을 자동화 하는 Job으로 생성하여 수행
- DBMS_SCHEDULER Package 사용

Data Warehouse of The Database
- Automatic Collection of Important Statistics
- Direct Memory Access : Oracle Engine 이 아닌 Memory 에 직접적으로 접근

Statistics

Optimizer Stats (일부 dba_)
- 관리자가 프로시저를 사용해 수동으로 통계를 집계해야 한다
- DBMS_STATS package 의 사용으로 통계 수집
참조: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/toc.htm

Performance Stats (v$)
- Server 의 수행 내역에 관한 통계들. 관리하지 않아도 자동으로 변경된다.
- 문제발생시 우선 확인해야하는 부분
- v$ 뷰를 기초로 하기에, Startup 이래로 누적된 값만을 볼 수 있으므로 특정상황에 대한 문제를 발견 할 수 없다
- Shutdown 시 정보가 없어진다
- (1)누적과 (2)휘발의 위험을 위해 Snapshot 을 남긴다

Snapshot 을 만드는 방법
1. utlbstat.sql + utlestat.sql -> report.txt $ORACLE_HOME/rdbms/admin
2. statspack / sp*.sql
3. AWR (+MMON + ADDM)

Metric
- 측정단위. 통계값의 2차 가공한 정보
- 내부 Component 들이 어떤 결과를 내리기 위한 값 (기본적으로 관리자의 관리를 위한 Data 가 아니다)
- Ex] Statistic 가 총 Commit 이 발생한 횟수 라면 Metric 은 초(시간)당 Commit 의 횟수 일 수 있다.

Tuning

SQL Tuning
특정 SQL 이 처리되는 가장 좋은 경로를 알고 있는 상태에서
Optimizer 가 특정 SQL 의 최적의 실행계획을 선택하도록 유도하는 과정

유도 방법
- Optimizer Stats 관리
- Index 의 적절한 조절
- 대안적 저장구조
- Parameter 값 변경 (PGA 크기조정 etc...)
- SQL 변환
- Hint 의 사용
- Etc...

Server Tuning
진단 결과를 해석할 능력이 있는 상태에서
목표에 맞는 Performance 가 발휘되도록 System 의 여러 요소를 조절해가는 과정

Statistic Levels
- BASIC : Snapshot 같은 정보는 수집하지 않는다
- TYPICAL : Default
- ALL : 시스템에 부담. Snapshot + SQL Tuning 정보까지도 포함

저작자표시 비영리

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

DBMS_XPLAN - 1.실행계획 (0)	2010.05.24
HWM(High Water Mark)란? (0)	2010.03.31
AWR (Automatic Workload Repository) (0)	2010.03.23
Statspack 생성/삭제/Sanpshot생성 (0)	2010.03.23
[Oracle 물리설계] 제5부 영역감시 (0)	2010.02.11

Posted by [PineTree]

AWR (Automatic Workload Repository)

출처 : http://www.urbantree.wo.tc/entry/6-Using-Automatic-Workload-Repository

AWR (Automatic Workload Repository)
- SYS 소유
- SYSAUX TS 에 존재
- MMON에 의해 자동으로 60분마다 수집 / 7일간 유지 (Default)
- MMON 에 의해 자동으로 삭제
- Snapshot : Set of performance statistics captured at a certain time
- Baseline : Set of Snapshots

AWR 수록 내용
- Base Statistics
- Metrics
- Active Session History
- Advisor Results
- Snapshot Statistics
- Database Feature Usage

Script
- awrrpt.sql : AWR 관련 Report 생성 Script. (분석은 사용자의 몫)
- awrddrpt.sql (기간비교) : AWR 관련 Report 생성 Script (분석은 사용자의 몫)
- ashrpt.sql : ASH 관련 Report 생성 Script (분석은 사용자의 몫)
- addmrpt.sql : ADDM 관련 Report 생성 Script 분석 및 권고안까지 포함

ADDM Attributes
- STATISTICS_LEVEL = TYPICAL or ALL (Basic으로 설정시 자동 실행 안된다)

ADDM Report
- 하나의 Report 생성

SQL> @?/rdbms/admin/addmrpt

…

Enter value for begin_snap: 8

Enter value for end_snap: 10

…

Enter value for report_name:

Generating the ADDM report for this analysis ...

- 기존의 작업물들을 바탕으로 Report 생성

SELECT dbms_advisor.GET_TASK_REPORT(task_name)

FROM dba_advisor_tasks

WHERE task_id = (

SELECT max(t.task_id)

FROM dba_advisor_tasks t,

dba_advisor_log l

WHERE t.task_id = l.task_id AND

t.advisor_name = 'ADDM' AND

l.status = 'COMPLETED');

ASH (Active Session History)
- V$SESSION 의 내용중 Active Session 의 내용들을 1초 단위로 ASH Buffer로 복사
- ASH Buffer 는 Shared Pool 안에 존재
- ASH Buffer 에는 V$ACTIVE_SESSION_HISTORY View 가 존재
- CPU 당 2mb 의 크기 사용
- ASH 는 Lock 에 의한 보호 메커니즘이 없으므로 읽기 일관성 보장 안된다.
- V$ACTIVE_SESSION_HISTORY 의 내용은 일정크기를 순환해서 쓰는 Rollng Buffer 방식
- V$ACTIVE_SESSION_HISTORY 의 내용을 Direct Path 방식으로 AWR로 내려쓴다
- AWR 로 내려쓰여진 정보들은 DBA_HIST_ACTIVE_SESSION_HISTORY, WRH$_ACTIVE_SESSION_HISTORY 에서 확인가능

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

HWM(High Water Mark)란? (0)	2010.03.31
AWR - Monitoring & Tuning (0)	2010.03.23
Statspack 생성/삭제/Sanpshot생성 (0)	2010.03.23
[Oracle 물리설계] 제5부 영역감시 (0)	2010.02.11
Oracle Hidden Parameter 란 (0)	2010.01.26

Posted by [PineTree]

Statspack 생성/삭제/Sanpshot생성

출처 : http://www.urbantree.wo.tc/entry/5-Using-

Statspack 생성

SQL> shutdown abort
SQL> startup

SQL> !ls $ORACLE_HOME/rdbms/admin/sp*

...
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spauto.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spcpkg.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spcreate.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spctab.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spcusr.sql
...

SQL> @?/rdbms/admin/spcreate.sql

..
Enter value for perfstat_password : perfstat
Enter value for default_tablespace : enter
Enter value for temporary_tablespace : enter
..

Statspack 생성 확인

SQL> show user

USER is "PERFSTAT"

SQL> col object_name for a40
SQL> select object_name, object_type from user_objects order by 2;

....
OBJECT_NAME                              OBJECT_TYPE
---------------------------------------- -------------------
V_$DATAGUARD_STATUS                      VIEW
V_$THREAD                                VIEW
V_$PROCESS                               VIEW
V_$BGPROCESS                             VIEW
V_$SESSION                               VIEW
V_$LICENSE                               VIEW
V_$TRANSACTION                           VIEW
V_$BSP                                   VIEW
V_$FAST_START_SERVERS                    VIEW
WEEKNIGHT_WINDOW                         WINDOW
WEEKEND_WINDOW                           WINDOW
MAINTENANCE_WINDOW_GROUP                 WINDOW GROUP
...

수동 Snapshot 생성

SQL> select * from stats$sga;
SQL> exec statspack.snap
SQL> col name for a40
SQL> select * from stats$sga;

   SNAP_ID       DBID INSTANCE_NUMBER NAME                                          VALUE STARTUP_T PAR VERSION
---------- ---------- --------------- ---------------------------------------- ---------- --------- --- -----------------
         1 1235361507               1 Fixed Size                                  1218992
         1 1235361507               1 Variable Size                             100664912
         1 1235361507               1 Database Buffers                          180355072
         1 1235361507               1 Redo Buffers                                2973696

4 rows selected.

SQL> variable no number
SQL> exec :no :=statspack.snap
SQL> print no

자동 Snapshot 생성

SQL> !ls $ORACLE_HOME/rdbms/admin/sp*

/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spauto.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spcpkg.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spcreate.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spctab.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spcusr.sql
....

SQL> !more $ORACLE_HOME/rdbms/admin/spauto.sql

...
variable jobno number;
variable instno number;
begin
select instance_number into :instno from v$instance;
dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')'
, TRUE, :instno);
commit;
end;
/
...

Report 생성

SQL> !ls $ORACLE_HOME/rdbms/admin/sp*

...
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/sprepcon.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/sprepins.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spreport.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/sprepsql.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/sprsqins.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/sptrunc.sql
...

SQL> @?/rdbms/admin/spreport.sql

...

Listing all Completed Snapshots

                                                       Snap
Instance     DB Name        Snap Id   Snap Started    Level Comment
------------ ------------ --------- ----------------- ----- --------------------
orcl         ORCL                 1 15 Jan 2010 14:16     5
                                 11 15 Jan 2010 14:29     5

※ snap level 이 커질수록 내용이 더욱 자세하다

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1

Enter value for end_snap: 11
End   Snapshot Id specified: 11

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_11. To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name: enter

...

End of Report ( sp_1_11.lst )

SQL> !ls sp*
SQL> !more sp_1_11.lst

Statspack 삭제

SQL> !ls $ORACLE_HOME/rdbms/admin/sp*

...
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spdoc.txt
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spdrop.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spdtab.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spdusr.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/sppurge.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/sprepcon.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/sprepins.sql
...

SQL> @?/rdbms/admin/spdrop.sql

저작자표시 비영리

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

AWR - Monitoring & Tuning (0)	2010.03.23
AWR (Automatic Workload Repository) (0)	2010.03.23
[Oracle 물리설계] 제5부 영역감시 (0)	2010.02.11
Oracle Hidden Parameter 란 (0)	2010.01.26
FAST_START_MTTR_TARGET (0)	2010.01.18

Posted by [PineTree]

[Oracle 물리설계] 제5부 영역감시

서두

한마디로 스페이스 감시라고 해더라도, 그 목적 및 실현 수단은 여러가지입니다. 본장에서는 그 중에서도 이른바 「단편화」라고 하는 키워드에 주목해 단편화의 발생을 감시한다고 하는 관점에서 각종 스페이스 감시의 대상·방법에 대해 해설하겠습니다. 감시를 실시하고 문제를 밝혀낸 후, 그 문제를 해결하는 방법에 대해서는 다음 장인 제 6부에서 해설하겠습니다.이번은 SQL가 많이 소개됩니다만, 특별한 기술이 없는 한 SYSTEM 유저로 실행하고 있습니다.다른 유저로 실행하는 경우, 검색 대상의 딕쇼내리나 권한등을 적당 조정하면 된다. 덧붙여 본장으로 소개한 SQL는 실행 예의 형식이 되어 있으므로, SQL문을 재이용할 수 있도록 Code Tips에도 게재해 있습니다.
Code Tips

단편화란

Oracle에 있어서의 단편화란, Oracle의 물리 영역이 어떠한 형태로 불연속이 되어 있는 상태를 말합니다. 단편화가 발생하면 발생의 정도로 따라 주로 이하와 같은 영향이 발생한다.

영역이 효율적으로 이용되지 않고, 실데이터량에 비해 보다큰 디스크 용량을 소비해 버린다.
I/O에 시간이 걸려, 퍼포먼스가 떨어진다.

한마디로 단편화 라고 해도 Oracle의 경우 DB의 물리 구조가 계층 구조가 되어 있어, 계층에 따라 나타나는 단편화의 현상에 차이가 있습니다.표 1으로 DB의 물리 구조에 대응한 단편화 현상을 정리했습니다. 앞으로 각각의 단편화 현상에 대하고 설명합니다. 단편화 현상의 해소 방법에 대해서는 제6회에서 정리해 해설하겠습니다.

표1：Oracle의 물루구조와 단편화
계층	단편화 대상
데이터파일 （테이블스페이스）	・파일 레벨의 단편화 ・데이터 파일 레벨의 미사용 영역의 발생
세그먼트	・위치(값)이 높은 하이 워터 마크 ・세그먼트 레벨의 미사용 영역발생 ・계층이깊은 인덱스
익스텐트	・불연속 extent
블럭	・행 이행 ・행 연쇄 블록내의 미사용 영역의 발생

◆ 제2장 데이터파일(데이블스페이스)레벨의 단편화

파일 레벨의 단편화

아무리 블록 레벨이나 세그먼트(segment) 레벨 등의 세세한 레벨로 단편화를 해소해도, 데이터 파일의 레벨로 단편화가 발생하고 있으면 별로 의미가 없습니다.특히 하나의 디스크에 데이타베이스가 깔려있고 게다가 초기의 물리 설계를 대충한 시스템에 대해선 격렬하게 단편화가 발생해 시스템의 퍼포먼스를 떨어뜨리고 있을 가능성이 있습니다.또, 테이블 스페이스의 설정으로 자동 확장을 하고 있었을 경우, 파일 레벨의 단편화가 일어나기 쉬워집니다. 파일 레벨의 단편화의 상황은 OS레벨의 이야기가 된다. 예를 들면 Windows라면 defrag 툴이라고 하는 OS의 기능이용한다.

High Water mark란

데이터 파일 레벨의 미사용 영역의 발생에 대해 설명하기전에 사전 지식으로서 하이 워터 마크(이하 HWM)에 대해 해설합니다. Oracle에 있어서의 HWM와는 과거에 데이터가 격납된 것이 있는 제일 높은(마지막) 위치를 나타내는 지표가 됩니다.하이 워터 마크의 개념은 데이터 파일과 세그먼트(segment)에 존재합니다.

그림1:high water mark의 개념

데이터 파일 레벨의 미사용 영역의 발생　HWM 이후의 미사용 영역

데이터 파일내의 미사용 영역은, HWM 이후의 미사용 영역 및 HWM 이전의 세그먼트(segment)간의 미사용 영역으로부터 구성됩니다.여기에서는 전자에 대해 언급하겠습니다.HWM 이후의 미사용 영역은 데이터의 검색이나 갱신에는 영향을 주지 않습니다만, 격납하고 있는 데이터 용량에 비교해 데이터 파일의 사이즈가 크기 때문에 실데이터량에 비교해 백업 및restore에 시간을 필요로 하게 됩니다.반면 앞으로의 데이터 파일의 확장을 막기 위해서 사전 확보하고 있는 경우도 있으므로 통틀어 HWM 이후의 미사용 영역이 큰 것이 나쁜 일이라고는 말할 수 없습니다.차후의 데이터의 증가 예상과의 균형으로 사이즈의 조정을 실시 한다.어느 테이블스페이스에 존재하는 데이터 파일의 HWM 이후의 미사용 영역의 합계에 관한 정보는 이하의 SQL로 파악하는 것이 가능합니다.

＜테이블 스페이스의 데이터 파일마다의 HWM 이후의 미사용 영역의 사이즈를 구하기＞

SQL>

select sumdf.file_name "Datafile 명",
  to_char(sumdf.total_bytes, 'FM999,999,999,990') "tablespace size",
  to_char(sumfs.free_bytes, 'FM999,999,999,990')
    "HWM이후 미사용 영역사이즈"
from (select df.file_id, df.file_name, sum(df.bytes) total_bytes
      from dba_data_files df
      where df.tablespace_name = upper('&&tsname')
      group by df.file_id, df.file_name) sumdf
left outer join (select fs.file_id, fs.bytes free_bytes
                 from (select fs2.file_id, fs2.bytes, fs2.block_id,
                         max(fs2.block_id) over (partition by
                         fs2.file_id) max_block
                       from dba_free_space fs2
                       where fs2.tablespace_name = upper('&&tsname')
                       ) fs
                 where fs.block_id = fs.max_block) sumfs
on (sumdf.file_id = sumfs.file_id);

tsname에 값을 입력하시오: USERS3

구6: where df.tablespace_name = upper('&&tsname')

신6: where df.tablespace_name = upper('USERS3')

구12: where fs2.tablespace_name = upper('&&tsname')) fs

신12: where fs2.tablespace_name = upper('USERS3')) fs

Datafile명
--------------------------------------------------------------------------------
tablespace size                              HWM이후 미사용 영역사이즈
-------------------------------- --------------------------------
D:\ORACLE\ORADATA\TEST1\USERS03.DBF
786,432,000                                 34,504,704

D:\ORACLE\ORADATA\TEST1\USERS03_2.ORA
134,217,728                                 133,464,064

상기 SQL의 실행에 있어서 tsname에는 테이블 스페이스명을 입력해 주세요.DBA_FREE_SAPCE 딕쇼내리의 각 데이터 파일마다로 블록 ID가 제일 큰 빈영역을 추출하고 있습니다.

하는 김에 한 테이블 스페이스 전체의 미사용 영역을 요구하는 SQL도 게재해 보았습니다.

＜어느 테이블 스페이스의 미사용 영역의 총사이즈를 구하기＞

SQL>

tselect to_char(sumdf.total_bytes, 'FM999,999,999,990')
  "tablespace size",
  to_char(sumdf.total_bytes - sumfs.free_bytes, 'FM999,999,999,990')
  " 사용완료 영역 size",
  to_char(sumfs.free_bytes, 'FM999,999,999,990') "미사용영 역사이즈",
  to_char((nvl(sumfs.free_bytes, 0) / sumdf.total_bytes) * 100,
  'FM990.99') || '%' " 사용율"
from (select df.tablespace_name, sum(df.bytes) total_bytes
      from dba_data_files df
      group by df.tablespace_name) sumdf
left outer join (select fs.tablespace_name, sum(fs.bytes) free_bytes
                 from dba_free_space fs
                 group by fs.tablespace_name) sumfs
on (sumdf.tablespace_name = sumfs.tablespace_name)
where sumdf.tablespace_name = upper('&tsname');

tsname에 값을 입력하시오: USERS3

旧 13: where sumdf.tablespace_name = upper('&tsname')

新13: where sumdf.tablespace_name = upper('USERS3')

tablespace size                            사용완료 영역 size
-------------------------------- --------------------------------
미사용영역사이즈                         사용율
-------------------------------- ----------------
920,649,728                              487,653,376
432,996,352                               47.03%

상기 SQL의 실행에 있어서 tsname에는 테이블 스페이스명을 입력해 주세요. 이쪽은 비어있는 영역 모두를 집계하고 있습니다.

데이터 파일 레벨의 미사용 영역의 발생　HWM 이전의 세그먼트(segment)간의 미사용 영역

데이터 파일내의 미사용 영역은, HWM 이후의 미사용 영역 및 HWM 이전의 세그먼트(segment)간의 미사용 영역으로부터 구성됩니다.여기에서는 후자에 대해 언급하겠습니다.
세그먼트(segment)간의 미사용 영역은 물리 단위에서는 extent가 됩니다.로컬 관리표 영역에서, 특히 UNIFORM 사이즈 지정의 경우는 이러한 영역도 효율적으로 이용됩니다만, AUTOALLOCATE 지정의 경우는 미사용인 채 남을 가능성이 있습니다.HWM 이전의 세그먼트(segment)간에 어느 정도의 미사용 extent가 존재하는지에 대해서는 이하의 SQL로 조사할 수 있습니다.

＜HWM 이전의 세그먼트(segment)간의 미사용 영역을 구하기＞

SQL>

select sumdf.file_name " 데이터 파일명",
  to_char(sumfs.free_bytes, 'FM999,999,999,990') "미사용영역사이즈"
from (select df.file_id, df.file_name from dba_data_files df
      where df.tablespace_name = upper('&&tsname')) sumdf
left outer join (select fs.file_id, fs.bytes free_bytes
                 from (select fs2.file_id, fs2.bytes, fs2.block_id,
                         max(fs2.block_id) over (partition by
                         fs2.file_id) max_block
                       from dba_free_space fs2
                       where fs2.tablespace_name = upper('&&tsname')
                      ) fs
                 where fs.block_id <> fs.max_block) sumfs
on (sumdf.file_id = sumfs.file_id);

tsname에 값을 입력하시오: USERS3

구4: where df.tablespace_name = upper('&&tsname')) sumdf

신4: where df.tablespace_name = upper('USERS3')) sumdf

구9: where fs2.tablespace_name = upper('&&tsname')) fs

신9: where fs2.tablespace_name = upper('USERS3')) fs

데이터 파일명 미사용영역사이즈

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

D:\ORACLE\ORADATA\TEST1\USERS03.DBF 32,768

D:\ORACLE\ORADATA\TEST1\USERS03.DBF 16,384

D:\ORACLE\ORADATA\TEST1\USERS03.DBF 278,528

D:\ORACLE\ORADATA\TEST1\USERS03.DBF 32,768

D:\ORACLE\ORADATA\TEST1\USERS03.DBF 81,920

.........(이하생략)

상기 SQL의 실행에 있어서 tsname에는 테이블 스페이스명 입력한다.이 결과 많은 행이 표시되었다고 해도, 세그먼트(segment)간의 미사용 영역의 수가 많은 것에 의해 퍼포먼스에 관한 영향은, 로컬 관리표 영역의 경우는 경미합니다.또, 백업이나 restore의 처리 시간에는 HWM 이후의 미사용 영역과 같이 영향을 줍니다.또 빈 영역의 수는 아니고 합계 사이즈가 많은 경우는, 자세한 것은 다음 번에 해설합니다만 데이터 파일의 축소의 효과가 작아져 버립니다.

◆ 제3장 세그먼트 레벨의 단편화

수위가 높은 하이 워터 마크

HWM가 어떠한 것일까에 대해서는 제2장으로 해설했습니다.단지, 제2장으로 해설하고 있지 않는 중요한 포인트가 한 개 있습니다.그것은, HWM는 자동에서는 결코 내려가지 않는다는 점입니다.구체적인 낮추는 방법은 다음 번에 설명합니다만 예를 들면 전건을 DELETE문으로 삭제했다고 해도, HWM의 위치는 그대로입니다.

그림2:DELETE로 움직이지 않는 하이 워터 마크

세그먼트 (segment) 레벨의 HWM는 주로

전건검색
다이렉트·로드/다이렉트·로드·인서트

에 영향을 줍니다.이하 구체적으로 해설합니다.

（1）HWM의 전건검색에 대한 영향

테이블이나 인덱스의 풀 스캔을 실행하는 경우, 실제의 스캔 범위는 테이블이나 인덱스 전체가 아니고, HWM의 위치까지를 스캔 합니다.이것에 의해 그림 3의 상부와 같이 실제로 용량을 확보하고 있는 사이즈에 비교해 데이터량이 적은 경우의 처리 시간을 단축하고 있습니다.그러나, HWM가 자동으로 내려갈리 없습니다. 그 때문에 일단 많이 데이터가 들어가 있는 상태로부터 대량 삭제가 있으면, 그림 3의 하부와 같이, 실제로는 데이터가 들어가 있지 않음에도 불구하고 HWM의 위치까지 스캔 해 버려, 실데이터량에 비교해 검색에 시간이 걸려 버립니다.

그림3:하이 워터 마크의 풀 스캔시의 영향

（2）HWM의 다이렉트 처리에 대한 영향

다이렉트·로드나 다이렉트·로드·인서트는 INSERT문에 의해 데이터를 삽입하는 것이 아니라, 먼저 블록에 저장된 포맷 이미지를 작성해, 그 블록 이미지를 직접 씁니다.그렇기 때문에 조금이라도 데이터가 들어가 있는 블록에는 쓸 수 없습니다. HWM 이후의 블록은 비어 있는 것이 보증되고 있기 때문에, 다이렉트 처리는 그림 4의 상부와 같이 HWM 이후의 블록에 데이터를 씁니다.이러한 처리에 의해 다이렉트 처리는 퍼포먼스를 확보하고 있습니다.덧붙여서 패러렐·다이렉트·로드 때는 HWM 이후의 extent로부터 씁니다. 만약 HWM 이전의 영역에 빈 곳이 많은 경우, 그림 4의 하부와 같이 세그먼트(segment)내에 큰 빈 영역이 생기게 됩니다.만약 이 테이블에 다이렉트 처리에 의한 데이터 삽입 밖에 없는 경우, 이 빈영역은 사용되지 않는채 남아 버립니다.

그림4:하이 워터 마크의 다이렉트 처리에의 영향

（3）HWM의 위치를 찾는 방법

HWM의 위치를 알려면 DBMS_SPACE 패키지에 있는 UNUSED_SPACE 프로시저를 이용합니다.이 프로시저를 이용하기 위해서는, ANALYZE 혹은 ANALYZE ANY 시스템 권한이 필요합니다. 다만 실제로 ANALYZE를 실시하는 것은 아니기 때문에, 룰 베이스로 운용하고 있는 시스템에서도 이용 가능합니다. 또 빈영역 관리를 프리 리스트가 아닌 자동 세그먼트(segment) 관리(ASSM)를 이용하고 있는 경우는, UNUSED_SPACE 프로시저말고 SPACE_USA GE프로시저를 이용하지 않으면 잘못된 결과가 되어 버립니다.다음의 UNUSED_SPACE 프로시저의 이용 예를 이용해 HWM의 위치가 어떻게 표현되는지를 설명합니다.

＜HWM 위치 구하기＞

SQL>

SQL> set serveroutput on
SQL> declare
       v_total_blocks              number;
       v_total_bytes               number;
       v_unused_blocks             number;
       v_unused_bytes              number;
       v_last_used_extent_file_id number;
       v_last_used_extent_block_id number;
       v_last_used_block           number;
   begin
      dbms_space.unused_space(upper('&uname'), upper('&sename'), '&stype',
      v_total_blocks, v_total_bytes, v_unused_blocks, v_unused_bytes,
      v_last_used_extent_file_id, v_last_used_extent_block_id, v_last_used_block);
      dbms_output.put_line('HWM가 있는 데이터 파일의 ID          ：'
        || to_char(v_last_used_extent_file_id, '9,999,990'));
        dbms_output.put_line('HWM가 있는 extent의 개시 블록 ID   ：'
          || to_char(v_last_used_extent_block_id, '9,999,990'));
       dbms_output.put_line('HWM가 있는 블록의 위치                  ：'
          || to_char(v_last_used_block, '9,999,990'));
   end;
   /

uname에 값을 입력해 주세요: SCOTT
sename에 값을 입력해 주세요: CUSTOMERS
stype에 값을 입력해 주세요: TABLE

구 10:   dbms_space.unused_space(upper('&uname'), upper('&sename'), '&stype',
신 10:   dbms_space.unused_space(upper('SCOTT'), upper('CUSTOMERS'), 'TABLE',
HWM가 있는 데이터 파일의 ID          ：        11
HWM 가 있는 extent의 개시 블록 ID   ：     1,033
HWM가 있는 블럭의 위치                 ：         6

PL/SQL프로시져가 정상적으로 종료했습니다.

상기 SQL 스크립트의 실행에 있어서, uname에는 세그먼트(segment) 소유자명을, sename에는 세그먼트(segment)명을, stype에는 세그먼트(segment)의 타입(TABLE/TABLE PARTITION/TABLE SUBPARTITION/INDEX/INDEX PARTITION/INDEX SUBPARTITION/CLUSTER/LOB의 어느쪽이든)을 입력한다.

상기 SQL 스크립트의 행 결과를 설명하면「HWM가 있는 데이타 파일의 ID」는 프로시져의 V_LAST_USED_EXTENT_FILE_ID파라미터의 값이 됩니다. DBA_DATA_FILES 딕쇼내리의 FILE_ID열이 V_LAST_USED_EXTENT_FILE_ID파라미터와 일치하는 데이터 파일중에 HWM가 존재하는 것을 나타내고 있습니다

「HWM가 있는 extent의 개시 블록 ID」는, 프로시저의 V_LAST_USED_EXTENT_BLOCK_ID파라미터의 값이 됩니다.???_EXTENTS(이하???(은)는 DBA/ALL/USER의 어느것이든) 딕쇼내리의 FILE_ID열이 V_LAST_USED_EXTENT_FILE_ID와 같고, 한편 BLOCK_ID열이 V_LAST_USED_EXTENT_BLOCK_ID와 일치하는 익스텐트중에 HWM가 존재하는 것을 나타내고 있습니다.「HWM가 있는 블록의 위치」는, 프로시저의 V_LAST_USED_BLOCK 파라미터의 값이 됩니다.해당 extent의 V_LAST_USED_BLOCK 파라미터의 값번째의 블록에 HWM가 존재하는 것을 나타내고 있습니다.

그림5:하이 워터 마크의 위치

세 그먼트(segment) 레벨의 미사용 영역의 발생　HWM 이후의 미사용 영역

제2장의 데이터 파일의 HWM 이후의 미사용 영역과 같은 영향이 있습니다. HWM 이후의 미사용 영역은 이하와 같은 방법으로 조사할수 있다.

（1）DBMS_SPACE.UNUSED_SPACE프로시져의 사용

앞서 기술한 DBMS_SPACE.UNUSED_SPACE 프로시저를 이용하는 것으로 세그먼트(segment)의 HWM 이후의 미사용 영역의 크기를 계산할 수 있습니다. 아래의 샘플 스크립트를 참조해 주세요.

＜HWM이후의 미사용영역 구하기(1)＞

SQL> set serveroutput on
SQL> declare
        v_total_blocks              number;
        v_total_bytes               number;
        v_unused_blocks             number;
        v_unused_bytes              number;
        v_last_used_extent_file_id number;
      v_last_used_extent_block_id number;
        v_last_used_block           number;
    begin
       dbms_space.unused_space(upper('&uname'), upper('&sename'), '&stype',
       v_total_blocks, v_total_bytes, v_unused_blocks, v_unused_bytes,
       v_last_used_extent_file_id, v_last_used_extent_block_id, v_last_used_block);
    dbms_output.put_line('현세그먼트용량：'
      || to_char(v_total_bytes, '999,999,999,990') || ' バイト');
    dbms_output.put_line('소비용량　　：'
      || to_char(v_total_bytes - v_unused_bytes, '999,999,999,990') || ' バイト');
    dbms_output.put_line('나머지용량 　：'
      || to_char(v_unused_bytes, '999,999,999,990') || ' バイト');
    dbms_output.put_line('소비율　　　：         '
       || to_char((v_total_bytes - v_unused_bytes) / v_total_bytes * 100, '990.99') || ' %');
end;
/

uname에 값을 입력해 주세요: SCOTT
sename에 값을 입력해 주세요: CUSTOMERS
stype에 값을 입력해 주세요: TABLE
구10:   dbms_space.unused_space(upper('&uname'), upper('&sename'), '&stype',
신 10:   dbms_space.unused_space(upper('SCOTT'), upper('CUSTOMERS'), 'TABLE',
현세그먼트용량 ：       9,437,184 바이트
소비용량　　　：       8,593,408 바이트
나머지용량　　：         843,776 바이트
소비율　　　    ：           91.06 %

PL/SQL프로시져가 정상 종료하였습니다.

상 기 SQL의 실행에 있어서, uname에는 세그먼트(segment) 소유자명을, sname에는 세그먼트(segment)명을, stype에는 세그먼트(segment)의 타입(TABLE/TABLE PARTITION/TABLE SUBPARTITION/INDEX/INDEX PARTITION/INDEX SUBPARTITION/CLUSTER/LOB)을 입력한다.

（2）ANALYZE 실행 후 딕쇼내리를 참조한다

테이블의 경우는 ANALYZE를 실행한 후의???_TABLES 딕쇼내리의 BLOCKS와 EMPTY_BLOCKS의 값을 검색하는 것으로도 HWM 이후의 미사용 영역의 크기를 구할 수 있습니다.BLOCKS는 세그먼트(segment)내의 사용이 끝난 블록수, EMPTY_BLOCKS는 세그먼트(segment)내의 미사용 블록수(HWM 이후)를 나타냅니다.

＜HWM 이후의 미사용 영역 구하기(2)＞

SQL> analyze table scott.customers compute statistics;

테이블이 분석되었습니다.

SQL>

         select to_char((blocks + empty_blocks) * 8192, 'FM999,999,999,990') as "테이블 용량",
    to_char(empty_blocks * 8192, 'FM999,999,999,990') as "남은용량"
   from dba_tables where owner = '&uname' and table_name = '&tname';

uname에 값을 입력해 주세요: SCOTT
tname에 값을 입력해 주세요: CUSTOMERS
구 3: from dba_tables where owner = '&uname' and table_name = '&tname'
신 3: from dba_tables where owner = 'SCOTT' and table_name = 'CUSTOMERS'

테이블 용량 남은용량
----------------------- --------------------------------
9,428,992 843,776

상기의 테이블의 사용량에 관해서 SQL의 실행에 있어서, uname에는 세그먼트(segment) 소유자명을, tname에는 테이블명을 입력한다.
이 방법에 의한 세그먼트(segment) 용량과 (1)의 DBMS_SPACE.UNUSED_SPACE 프로시저를 이용하는 방법에 따르는 세그먼트(segment) 용량의 1 블록분 (본시험대에서는, 블록 사이즈를 8,192바이트로 하고 있습니다)의 차이는, 세그먼트(segment) 헤더를 포함하는지 포함하지 않는지의 차이입니다.전자가 세그먼트(segment) 헤더를 포함한 크기가 되어 있습니다.

세그먼트(segment) 레벨의 미사용 영역의 발생　HWM 이전의 미사용 영역

HWM 이전의 미사용 영역의 영향은, 본장의 최초로 설명한 HWM의 해설을 참조. 계산방법은, 테이블만의 대응입니다만, 이하와 같은 SQL로 산출할 수 있습니다.

＜HWM이전의 미사용영역 구하기＞

SQL> analyze table scott.customers compute statistics;

테이블이 분석되었습니다

SQL>

select to_char(avg_space * blocks, 'FM999,999,999,999') "빈영역"
from dba_tables where owner = '&uname' and table_name = '&tname';
uname 에 값을 입력해 주세요: SCOTT
tname에 값을 입력해 주세요: CUSTOMERS
구 2: from dba_tables where owner = '&uname' and table_name = '&tname'
신 2: from dba_tables where owner = 'SCOTT' and table_name = 'CUSTOMERS'

빈 영역
--------------------------------
595,264

상기의 테이블의 사용량에 관해서 SQL의 실행에 있어서 uname에는 세그먼트(segment) 소유자명을, tname에는 테이블명을 입력해 주세요.

계층이 깊은 인덱스

B*Tree 인덱스는 계층 구조로 되어 있습니다. 이 계층이 깊으면 검색에 시간이 걸리게 됩니다. 계층의 깊이는, 인덱스를 ANALYZE 한 뒤 INDEX_STATS 딕쇼내리의 HEIGHT열 내지는 ???_INDEXES 딕쇼내리의 BLEVEL열로 조사할 수 있습니다. 이러한 열의 값이 4이상의 경우는, 인덱스를 이용한 검색의 퍼포먼스에 영향이 있으므로 때문에 재편성을 검토 한다. 또한 INDEX_STATS 딕쇼내리를 검색하는 경우는, VALIDATE STRUCTURE 옵션 첨부로 ANALYZE 커멘드를 실행할 필요가 있습니다.

◆ 제4장 익스텐트 레벨의 단편화

불연속 익스텐트

어떤 세그먼트(segment)를 구성하는 extent가 연속해서 확보되어 있지 않아도 퍼포먼스에 거의 영향을 주지 않습니다. 단지 불연속의 extent간의 타세그먼트(segment)의 extent가 미사용 extent가 되었을 경우, 특히 딕쇼내리 관리표 영역에서 세그먼트(segment)마다 개별의 INITIAL/NEXT/PCTINCREASE를 지정해 있는 경우는 불필요 영역의 발생하기 쉬워집니다. 로컬 관리의 경우는 이러한 일이 일어나지 않도록 하는 것으로 특별히 감시할 필요는 없을 것입니다. 감시하고 싶은 경우는 이하와 같은 스크립트로 감시를 할 수 있습니다.

＜extent가 연속하고 있는지 어떤지를 조사하기＞

SQL> select ext2.extent_id "extent ID", ext2.file_id "file ID",
       ext2.block_id "개시블럭ID", ext2.blocks "블럭수",
       case when ext2.extent_id = 0 then '선두 EXTENT'
         when ext2.old_fid <> ext2.file_id then '데이터파일틀림'
5      when ext2.old_blk_id <> ext2.block_id then '불연속EXTENT'
6      else '연속EXTENT'
7    end "단편화 상태"
8 from (select ext1.extent_id, ext1.file_id, ext1.block_id, ext1.blocks,
9    lag(ext1.file_id, 1) over (order by ext1.extent_id) old_fid,
10    lag(ext1.block_id, 1) over (order by ext1.extent_id) +
11      lag(ext1.blocks, 1) over (order by ext1.extent_id) old_blk_id
12    from dba_extents ext1
13    where ext1.owner = '&uname' and ext1.segment_name = '&sname') ext2;

uname에 값을 입력해 주세요: SCOTT
sname에 값을 입력해 주세요: C3
구 13:   where ext1.owner = '&uname' and ext1.segment_name = '&sname') ext2
신 13:   where ext1.owner = 'SCOTT' and ext1.segment_name = 'C3') ext2

extent ID   file ID    개시블럭ID 블럭수 단편화 상태
--------- ------- ---------- ------ --------------------------
             0         12            113          2 선두 EXTENT
             1         13             93          2 데이터파일틀림
             2         12            115          2 데이터파일틀림
             3         13             95          2 데이터파일틀림
             4         13             97          2 연속EXTENT
             5         13             99          2 연속EXTENT
             6         13            101          2 연속EXTENT
             7         12            119          2 데이터파일틀림
             8         12            123          2 불연속EXTENT
             9         12            125          2 연속EXTENT

10행이 선택되었습니다.

상기의 테이블의 사용량에 관해서 SQL의 실행에 있어서, sname에는 세그먼트(segment) 소유자명을, tname에는 테이블명을 입력해 주세요.

◆ 제5장 블록 레벨의 단편화

행 이행

어느 레코드에 대해서 갱신을 했을 때에, PCTFREE로 확보한 영역을 가지고 있더라고 원래 존재하고 있던 블록에 들어가지 않는 길이가 되어 버렸다고 해 봅시다. 이 경우 Oracle는 해당 레코드를 다른 블록에 격납합니다. 이 때, 원래의 블록에 새로운 격납처에의 포인터를 남깁니다.이러한 상태를 행 이행이라고 부릅니다. 행 이행이 발생하면 해당 레코드는 본래 1 블록에 들어가는 길이 임에도 불구하고 2 블록에 건너 격납됩니다. 즉 이 레코드에 액세스 하기 위해서는 2 블록 읽어들일 필요가 있는 것입니다.행 이행이 발생하고 있는 레코드에의 액세스가 많으면 읽기 I/O의 증가나 캐쉬 히트율의 저하라고 하는 영향이 발생합니다.

그림6:행 이행

행 연쇄

행 연쇄는 행 이행과 같이 레코드가 복수 블록에 걸치는 현상입니다만, 행 연쇄는 단지 블록에 격납 가능한 사이즈 이상의 레코드가 복수 블록에 건너 격납되는 현상입니다. 물리적으로 큰 레코드를 넣으려 하고 있는 이상 어쩔 수 없는 현상입니다만, 다발하면 행 이행과 같은 영향이 있습니다.

그림7:행 연쇄

행 이행·행 연쇄를 조사한다

행 이행과 행 연쇄는 같은 방법으로 조사합니다. 테이블을 ANALYZE 한 후,???_TABLES 딕쇼내리의 CHAIN_CNT열에 행 이행내지 행 연쇄하고 있는 레코드수가 격납됩니다. 즉, 행 이행과 행 연쇄는 따로 따로 파악할 수 없습니다.

블록 레벨의 미사용 영역의 발생

본쳅터 이전에 문제가 발생하고 있지 않는데 세그먼트(segment) 용량에 비교해 격납 가능한 데이터량이 적은 경우는 블록의 내용이 효율적으로 이용되어 있지 않은 경우를 생각할 수 있습니다. 예를 들면 데이터 격납 가능 영역이 3000바이트에 대해서 레코드 사이즈가 평균 2000바이트의 경우, 단순 계산이라면 1 블록에 대해 1000바이트의 쓸데없는 공간이 발생하게 됩니다. 테이블이면 DELETE의 기회에 비교해 PCTUSED의 값이 낮은 경우에 블록 레벨의 미사용 영역이 발생하기 쉬워집니다. 인덱스의 경우는 DELETE가 많은 경우, 인덱스 대상열의 값이 승순은 아니고 랜덤이나 내림차순에 INSERT 되는 경우, 인덱스 대상열에 대해서 갱신이 발생하는 것이 많은 경우에 블록 레벨의 미사용 영역이 발생하기 쉬워집니다. 용량 견적 서비스등을 이용하고, 이론적인 견적치에 대해서 실제의 용량(HWM까지의 용량)이 너무 큰 경우는 재편성을 검토한다. 특히 풀 스캔의 경우는 견적치와 실제치의 비에 가까운 레벨로 처리 시간이 걸립니다.

출처 : http://blog.naver.com/hirokorea?Redirect=Log&logNo=20023344842

[출처] [Oracle 물리설계] 제5부 영역감시 |작성자 샤크

저작자표시 비영리

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

AWR (Automatic Workload Repository) (0)	2010.03.23
Statspack 생성/삭제/Sanpshot생성 (0)	2010.03.23
Oracle Hidden Parameter 란 (0)	2010.01.26
FAST_START_MTTR_TARGET (0)	2010.01.18
EXPLAIN PLAN(실행계획) 이란 (0)	2010.01.12

Posted by [PineTree]

Oracle Hidden Parameter 란

출처 : http://www.dbguide.net 에서 오라클 hidden parameter 보기 ( 김형일님의 글 )

Oracle parameter중.. hidden parameter 라는 것이 있읍니다.

이러한 parameter는 여러가지 용도로 사용됩니다.

1. 향후 지원될 기능을 미리 넣어두고 test하기 위해.(오라클내에서)

2. 어떤 문제가 발생한 경우 자세한 trace를 뜨기 위해.

3. 임의의 기능에 대한 변경 및 제약을 가하기 위해.

예를 들면 _trace_files_public 은 sql trace file이 oracle udump 에 생기는데,

Unix의 경우, oracle owner의 권한으로만 read, write를 할수 있게 되죠.

그래서 일반 다른 os user(dba가 아닌)가 sql trace를 떠서 확인해 보려면

권한이 없어서 볼수 가 없읍니다.

이때, 위의 _trace_files_public=true로 지정하게 되면 other user(일반 user)도

읽을수 있는 권한으로 trace file이 생깁니다.

이러한 hidden parameter를 조회 할 수 있는 sql은 아래와 같습니다.

sys user로 접속해서...

select ksppinm
from x$ksppi
where substr(ksppinm,1,1) = '_';

저작자표시 비영리

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

Statspack 생성/삭제/Sanpshot생성 (0)	2010.03.23
[Oracle 물리설계] 제5부 영역감시 (0)	2010.02.11
FAST_START_MTTR_TARGET (0)	2010.01.18
EXPLAIN PLAN(실행계획) 이란 (0)	2010.01.12
Statspack Report 간단 분석 방법 (0)	2009.12.17

Posted by [PineTree]

FAST_START_MTTR_TARGET

No. 17860

(V9I) ORACLE 9I: FAST_START_MTTR_TARGET

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

PURPOSE
-------

Oracle 9i(9.0.1)에서 New Feature인 Fast-Start Time-Based Recovery
에 대해 알아보도록 한다.

SCOPE
-----
9i~10g Standard Edition에서는 지원하지 않는다.

Explanation

-----------

8I에서는(Note.12240) new feature로 Bounded recovery 를 위해
FAST_START_IO_TARGET PARAMETER 를 제공하고
Instance Recovery 시간을 최소화 하는데 이용되었다.

참조 Note.12240) FAST_START_IO_TARGET

   Oracle 8i부터 제공되는 FAST_START_IO_TARGET이라는 파라미터는
   instance recovery 시에 읽어야 할 datafile block의 maximum 갯수를 제한
   함으로써 instance recovery를 수행하는 데 드는 시간을 제한할 수 있다.
   이 parameter는 dynamic하고, redo log file 내의 target RBA(Redo Block
   Address)를 indicate하는 데 영향을 주게 되는데, 몇 가지 다른 parameter
   와 함께 recovery 시에 읽어야 할 target RBA를 결정짓는 후보가 된다.

그러나 8I 에서 제공하는 FAST_START_IO_TARGET 사용은
Instance Recovery 를 위해 읽어야할 최소한의 redo block 수를 결정하는데
다음과 같은 고려사항이 있었다.
즉, checkpoint target position을 위한 고려사항으로
FAST_START_IO_TARGET와 LOG_CHECKPOINT_INTERVAL 설정하는데
어느정도가 적합한지에 대한 결정의 어려움이었다.

여기서 Oracle 8i에서 bounded time recovery를 가능하게 해주는 몇 가지 요인들
에 대해 알아보도록하자.

(1) Target based on FAST_START_IO_TARGET

initSID.ora 파라미터 화일에 다음과 같이 지정하면

FAST_START_IO_TARGET = 10000

   This allows a DBA to specify the maximum number of datafile blocks
   that should be processed during instance recovery.
   This is used in conjunction with other parameters to determine
   target for checkpointing.

   instance recovery하는 동안에 읽어들어야 할 block의 수를 제한해 주며,
   따라서, cache 내에 존재하는 dirty buffer의 갯수를 제한할 수 있다.
   Crash난 시점에 dirty buffer의 갯수가 instance recovery를 수행하는 데
   소요되는 시간에 영향을 주므로, dirty buffer의 갯수가 중요하다는 것
   이다.

   Oracle 8.0부터 소개된 DB_BLOCK_MAX_DIRTY_TARGET 이라는 파라미터도
   cache 내에 존재하는 dirty buffer의 갯수를 제한하는 역할을 하는데,
   Oracle 8i에서는 DB_BLOCK_MAX_DIRTY_TARGET 에 지정한 값보다
   FAST_START_IO_TARGET에 지정한 값이 우선이다.

(2) 90% of size of smallest redo log

The incremental checkpoint should not lag the tail of the log more
than 90% of the size of the smallest log file.

   가장 작은 redo log file size의 90% 이상을 incremental checkpoint가
   lag해서는 안 된다는 의미이다. 이것은 다시 말해서, instance recovery
   시 process할 redo block의 수가 1개의 redo log file 이상이면 안 된다
   는 것을 의미하기도 한다.

(3) LOG_CHECKPOINT_INTERVAL blocks from the end

The incremental checkpoint should not lag the tail of the log by more
than N blocks of redo.

   LOG_CHECKPOINT_INTERVAL에 지정한 block의 갯수보다 더 많은 redo block
   을 instance recovery 시에 읽지 않도록 해야 한다.
   즉, 이 파라미터에 지정한 숫자보다 더 많은 갯수의 dirty block이
   memory 내에 있으면 안 된다는 것을 의미한다.

(4) End of the log LOG_CHECKPOINT_TIMEOUT seconds ago

The incremental checkpoint should not lag the tail of the log in
time by more than LOG_CHECKPOINT_TIMEOUT seconds.

   LOG_CHECKPOINT_TIMEOUT 에 지정한 시간(초) 동안 만큼 발생한 redo
   block 갯수 이상을 instance recovery 시에 읽어서는 안 된다는 것을 의미
   한다.

9I에서는 이러한 문제점을 고려하여 설정해야하는 파라미터의 문제점을
자동화 함으로써 Fast-Start Time-Based RECOVERY 가 가능하게 되었다.

FAST_START_MTTR_TARGET 파라미터는 단순히 원하는 Instance recovery 시간을
지정함으로써 기존에 설정해야하는 다음 Parameter 를 지정하지 않아도
Oracle 에서 자동으로 해당 값을 지정하여 Instance recovery 시간을 최소화 한다.

LOG_CHECKPOINT_INTERVAL
FAST_START_IO_TARGET

FAST_START_MTTR_TARGET 파라미터는 dynamic Parameter 로
다음과 같이 설정 가능하며 단위는 초단위 이다.

ALTER SYSTEM SET PAST_START_MTTR_TARGET = 60;

즉, 위와 같이 60초로 설정하게 되면 오라클 내부적으로 LOG_CHECKPOINT_INTERVAL 와
FAST_START_IO_TARGET 파라미터의 적정치가 해당 시간내에 이루어지기 위해 결정되고
Recovery 시 읽어야할 Redo log block의 갯수가 결정된다.

Recovery할 block 갯수에 대한 정보는 V$INSTANCE_RECOVERY 중
새롭게 추가된 Column인 TARGET_MTTR/ESTIMATED_MTTR/CKPT_BLOCK_WRITES
에서 알 수 있다.

Example
-------
none

Reference Document
------------------
Note. 151062.1
Note. 159891.1

저작자표시 비영리

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

[Oracle 물리설계] 제5부 영역감시 (0)	2010.02.11
Oracle Hidden Parameter 란 (0)	2010.01.26
EXPLAIN PLAN(실행계획) 이란 (0)	2010.01.12
Statspack Report 간단 분석 방법 (0)	2009.12.17
cursor_sharing 파라미터에 대한 테스트 (0)	2009.12.16

Posted by [PineTree]

EXPLAIN PLAN(실행계획) 이란

☞ EXPLAIN PLAN(실행계획) 이란?

◈ SQL문의 엑세스 경로를 확인하고 튜닝할 수 있도록 SQL문을 분석하고 해석하여

실행계획을 수립한 후 실행계획을 PLAN_TABLE에 저장하도록 해주는 명령 입니다.

◈ SQL Trace없이 사용 할 수 있습니다.

◈ ORACLE_HOME/rdbms/admin/utlxplan.sql실행

◈ statement_id컬럼에 인덱스를 생성해주는것이 수행속도를 향상시켜주고

id값이 중복되는 것을 막을 수 있습니다.

[Syntax]

- statement_id = 'identifiedr' : 1-30자로 부여할 수 있는 해당 실행문의 제목

- INTO tablename : 출력문을 저장하는 테이블명 PLAN_TABLE을 사용하지 않을경우 사용

- FOR statement : 실행계획을 수립하고자 하는 SQL문(SELECT, INSERT, DELETE, UPDATE)

1. Plan_table 생성

◈ Explain plan을 sql에 포함해서 수행하면 옵티마이저가 실행 계획까지만 수립하여

plan_table에 저장해 둡니다.

◈ 이 table을 생성하기 위한 script는 ORACLE_HOME/rdbms/admin/utlxplan.sql 입니다.

SQL>@C:\oracle\ora81\rdbms\admin\utlxplan.sql

테이블이 생성되었습니다.

2. Index 생성

◈ 테이블 생성 후 수행 속도 향상과 동일한 statement_id가 생성되는 것을 방지하기 위해

index를 생성 합니다.

SQL> CREATE UNIQUE INDEX plan_index ON PLAN_TABLE(statement_id,id);

인덱스가 생성되었습니다.

3. SQL 문 사용

◈ FOR 뒷 부분에 확인하고자 하는 sql을 입력 합니다.

SQL>EXPLAIN PLAN SET STATEMENT_ID='a1' FOR

SELECT /*+ index(emp emp_pk) */ * FROM emp WHERE empno > 0;

해석되었습니다.

4. PLAN_TABLE 을 SELECT 하는 SQL 문을 실행

◈ plan.sql이라고 저장해서 사용하면 편리합니다.

SQL>SELECT LPAD(operation,LENGTH(operation)+ 2*(LEVEL-1)) ||DECODE(id,0,'cost estimate:' ||

DECODE(position,'0','N/A',position),null) || ' ' ||options || DECODE(object_name,null,null,':') ||

RPAD(object_owner, LENGTH(object_name)+1,',') || object_name ||

DECODE (object_type,'UNIQUE' ,'(U) ','NON_UNIQUE','(NU)',null) ||

DECODE(object_instance,null,null,'('||object_instance||')') "Explain Plan"

FROM PLAN_TABLE

START WITH ID= 0 and STATEMENT_ID = '&&id'

CONNECT by prior ID=PARENT_ID and STATEMENT_ID='&&id'

a1을 입력하면 아래와 같은 실행계획을 볼 수 있습니다.

Explain Plan

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

SELECT STATEMENTcost estimate:1

TABLE ACCESS BY INDEX ROWID:TESTEMP(1)

INDEX RANGE SCAN:TEST,,,EMP_PK

☞ PLAN_TABLE 컬럼 설명

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

컬 럼 명 설 명

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

STATEMENT_ID EXPLAIN PLAN문에서 사용자가 지정한 제목

TIMESTAMP 실행계획이 수립된 날짜와 시간

REMARKS 사용자가 부여한 주석(COMMENT)

OPERATION 아래 표에 자세히 설명 되어 있습니다.

OPTIONS 아래 표에 자세히 설명 되어 있습니다.

OBJECT_NODE 사용한 데이터베이스 링크

OBJECT_OWNER 해당 라인의 오브젝트를 생성한 사용자 그룹

OBJECT_NAME 테이블이나 인덱스, 클러스터등의 이름

OBJECT_INSTANCE SQL의 FROM절에 기술된 오브젝트를 좌에서 우로 부여한 번호

OBJECT_TYPE 오브젝트의 종류(예 non-unique index)

ID 수립된 각 실행단계에 붙여진 일련번호

PARENT_ID 해당 ID의 부모가 가진 ID

POSITION 같은 부모 ID를 가지고 있는 자식 ID간의 처리 순서

OTHER 다른 필요한 텍스트를 지정하기 위한 필트

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

☞ OPERATION의 종류와 OPTIONS에 대한 설명

OPERATION(기능) OPTIONS(옵션) 설 명

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

AGGREGATE GROUP BY 그룹함수를 사용하여 하나의 로우가 추출되도록 하는 처리(버전 7에서만 표시됨)

AND-EQUAL 인덱스 머지를 이용하는 경우

CONNECT BY CONNECT BY를 사용하여 트리 구조로 전개

CONCATENATION 단위 액세스에서 추출한 로우들의 합집합을 생성

COUNTING 테이블의 로우스를 센다

FILTER 선택된 로우에 대해서 다른 집합에 대응되는 로우가 있다면 제거하는 작업

FIRST ROW 조회 로우 중에 첫번째 로우만 추출한다.

FOR UPDATE 선택된 로우에 LOCK을 지정한다.

INDEX UINQUE UNIQUE인덱스를 사용한다. (단 한개의 로우 추출)

RANGE SCAN NON-UNIQUE한 인덱스를 사용한다.(한 개 이상의 로우)

RANGE SCAN RANGE SCAN하고 동일하지만 역순으로 로우를 추출한다.

DESCENDING

INTERSECTION 교집합의 로우를 추출한다.

MERGE JOIN OUTER 먼저 자신이ㅡ 조건만으로 액세스한 후 각각을 SORT하여

MERGE해 가는 조인

위와 동일한 방법이지만 outer join을 사용한다.

MINUS MINUS 함수를 사용한다.

NESTED LOOPS OUTER 먼저 어떤 드라이빙 테이블의 로우를 액세스한 후 그 결과를

이용해 다른 테이블을 연결하는 조인

위와 동일하지만 outer join을 사용한다.

PROJECTION 내부적인 처리의 일종

REMOTE 다른 분산 데이터베이스에 있는 오브젝트를 추출하기 위해

DATABASE LINK를 사용하는 경우

SEQUENCE 시퀀스를 액세스 한다.

SORT UNIQUE 같은 로우를 제거하기 위한 SORT

GROUP BY 액세스 결과를 GROUP BY 하기 위한 SORT

JOIN MERGE JOIN을 하기 위한 SORT

ORDER BY ORDER BY를 위한 SORT

TABLE ACCESS FULL 전체 테이블을 스캔한다.

CLUSTER CLUSTER를 액세스 한다.

HASH 키값에 대한 해쉬 알고리즘을 사용(버전 7에서만)

BY ROWID ROWID를 이용하여 테이블을 추출한다.

UNION 두 집합의 합집합을 구한다.(중복없음)

항상 전체 범위 처리를 한다.

UNION ALL 두 집합의 합집합을 구한다.(중복가능)

UNION과는 다르게 부분범위 처리를 한다.

VIEW 어떤 처리에 의해 생성되는 가상의 집합에서 추출한다.(주로 서브쿼리에 의해 수행된 결과)

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

출처 : http://jaehan.tistory.com/92?srchid=BR1http%3A%2F%2Fjaehan.tistory.com%2F92

EXPLAIN PLAN 이란?

사용자들이 SQL 문의 액세스 경로를 확인하고
튜닝을 할 수 있도록 SQL 문을 분석하고 해석하여 실행 계획을 수립한 후
실행 계획을 테이블(plan_table)에 저장하도록 해주는 명령 입니다.

1. PLAN TABLE의 생성

Explain plan을 sql 에 포함해서 수행하면 옵티마이저가 실행 계획까지만
수립하여 plan_table에 저장해 둡니다.

PLAN을 사용하고자 하는 USER로 SQLPLUS LOGIN한 후
ORACLE_HOME/RDBMS/ADMIN/utlxplan.sql을 수행하여 plan_table을 생성 합니다.

C:\>SQLPLUS scott/tiger

SQL*Plus: Release 10.2.0.1.0 - Production on 화 10월 10 16:41:26 2006

다음에 접속됨:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> @C:\oracle\product\10.2.0\db_2\RDBMS\ADMIN\utlxplan.sql

테이블이 생성되었습니다.

SQL>

2. PLUSTRACE ROLE의 생성
sqlplus "/ as sysdba"로 접속하여 PLUSTRACE ROLE을 생성 합니다.
ORACLE_HOME/sqlplus/admin/plustrce.sql을 수행하여 plustrace role을 생성 합니다.

C:\>sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on 화 10월 10 17:01:26 2006

다음에 접속됨:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> @C:\oracle\product\10.2.0\db_2\sqlplus\admin\plustrce.sql
SQL>
SQL> drop role plustrace;
drop role plustrace
*
1행에 오류:
ORA-01919: 롤 'PLUSTRACE'(이)가 존재하지 않습니다

SQL> create role plustrace;

롤이 생성되었습니다.

SQL>
SQL> grant select on v_$sesstat to plustrace;

권한이 부여되었습니다.

SQL> grant select on v_$statname to plustrace;

권한이 부여되었습니다.

SQL> grant select on v_$mystat to plustrace;

권한이 부여되었습니다.

SQL> grant plustrace to dba with admin option;

권한이 부여되었습니다.

SQL>
SQL> set echo off
SQL>

3. PLUSTRACE Role의 부여

PLUSTRACE ROLE을 plan을 사용하고자 하는 유저에게 부여 합니다.

SQL> GRANT plustrace TO scott;

권한이 부여되었습니다.

권한을 부여 한다음.
다시 plan을 사용하는 유저로 접속을 합니다.

SQL> conn scott/tiger
연결되었습니다.

autotrace 상태를 on으로 바꿉니다.

SQL> SET AUTOTRACE ON ;
SQL>

SQL문을 실행 합니다.

SQL> SELECT a.ename, a.sal, b.dname
2 FROM emp a, dept b
3 WHERE a.deptno = b.deptno;

ENAME             SAL DNAME
---------- ---------- --------------
SMITH             800 RESEARCH
ALLEN            1600 SALES
WARD             1250 SALES
JONES            2975 RESEARCH
MARTIN           1250 SALES
BLAKE            2850 SALES
CLARK            2450 ACCOUNTING
SCOTT            3000 RESEARCH
KING             5000 ACCOUNTING
TURNER           1500 SALES
ADAMS            1100 RESEARCH

ENAME             SAL DNAME
---------- ---------- --------------
JAMES             950 SALES
FORD             3000 RESEARCH
MILLER           1300 ACCOUNTING

14 개의 행이 선택되었습니다.

Execution Plan
----------------------------------------------------------
Plan hash value: 351108634

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

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

| 0 | SELECT STATEMENT | | 14 | 364 | 4 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | 14 | 364 | 4 (0)| 00:00:01 |

| 2 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0)| 00:00:01 |

| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |

|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("A"."DEPTNO"="B"."DEPTNO")

Statistics
----------------------------------------------------------
        626 recursive calls
          0 db block gets
        134 consistent gets
         10 physical reads
          0 redo size
        856 bytes sent via SQL*Net to client
        385 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
         10 sorts (memory)
          0 sorts (disk)
         14 rows processed

SQL>

⊙ 참고

☞ 8.17
@C:\oracle\ora81\RDBMS\ADMIN\utlxplan.sql;
@C:\oracle\ora81\sqlplus\admin\plustrce.sql;

☞ 10g
@C:\oracle\product\10.2.0\db_2\RDBMS\ADMIN\utlxplan.sql
@C:\oracle\product\10.2.0\db_2\sqlplus\admin\plustrce.sql

⊙ Golden(골든)의 결과

- Ctrl + P 키를 누루면 결과 나온다.

⊙ TOAD(토드)에서 설정방법

- Ctrl + E 키를 누루면 결과 나온다.

PLAN 테이블(TOAD_PLAN_TABLE)이 존재한다면

TOAD 상단 메뉴에서
View 메뉴 > Options 메뉴 내용중 "Oracle > General" 에 Explain Plan Table Name 이 있습니다. 이곳에 "TOAD_PLAN_TABLE" 이라 명시하시고 사용하면 됩니다

저작자표시 비영리

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

Oracle Hidden Parameter 란 (0)	2010.01.26
FAST_START_MTTR_TARGET (0)	2010.01.18
Statspack Report 간단 분석 방법 (0)	2009.12.17
cursor_sharing 파라미터에 대한 테스트 (0)	2009.12.16
Literal SQL 조회하는 방법 (0)	2009.12.05

Posted by [PineTree]

About DATABASE

'ORACLE/TUNING'에 해당되는 글 52건

DBMS_XPLAN - 2.포맷 설정하기

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

DBMS_XPLAN - 1.실행계획

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

HWM(High Water Mark)란?

HWM(High Water Mark)란?

특성

관련 Data Dictionary

HWM 측정방법

TUNING

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

AWR - Monitoring & Tuning

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

AWR (Automatic Workload Repository)

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

Statspack 생성/삭제/Sanpshot생성

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

[Oracle 물리설계] 제5부 영역감시

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

Oracle Hidden Parameter 란

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

FAST_START_MTTR_TARGET

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

EXPLAIN PLAN(실행계획) 이란

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

카테고리

공지사항

태그목록

최근에 올라온 글

최근에 달린 댓글

최근에 받은 트랙백

글 보관함

달력

링크

티스토리툴바