레이블이 Query Processer인 게시물을 표시합니다. 모든 게시물 표시

2010년 4월 27일 화요일

index Rebuild VS. Create

http://www.dbasupport.com/forums/showthread.php?t=43875

Rebuilding Index is used so that queries have continuous access to the index while it is being rebuilt. The mechanisms in a Rebuild or a Recreate after Drop is not too different.

When you rebuild, a new Temp Index Segment is created for the index and afer this new temporary segment is populated, the old index is set to temporary and the populated temp segment is redefined as the permanent segment with the same original index name.

Index Create and Rebuild Locking Improvements in 11g (Ch Ch Ch Changes)

Although the CREATE INDEX … ONLINE and ALTER INDEX … REBUILD ONLINE options have been available for a long while, they can still introduce locking issues in highly active databases.

Oracle requires a table lock on the index base table at the start of the CREATE or REBUILD process (to guarantee DD information) and a lock at the end of the process (to merge index changes made during the rebuild into the final index structure).

These locks have two implications. Firstly, if there’s an active transaction on the base table of the index being created or rebuilt at the time one of these locks is required, the indexing process will hang. This will of course impact the time it takes to complete the indexing process. However the second far more serious issue is that any other active transactions on the base table starting after the indexing process hangs will likewise be locked and be prevented from continuing, until the indexing process obtains and releases its locks. In highly concurrent environments with many transactions, this can cause serious disruptions to the response times of these impacted transactions. Of course, depending on the time the initial locking transactions take to commit or rollback, this backlog of locked transactions can be quite significant.

Oracle11g has made some improvements in the locking implications regarding creating or rebuilding indexes online.

During the creation or rebuilding of an index online, Oracle still requires two associated table locks on the base table at the start and end of indexing process. If there’s an active transaction on the base table at the time one of these locks is required, the indexing process will still hang as its done previously until all these prior active transactions have completed. No change so far.

However, if the indexing process has been locked out and subsequent transactions relating to the base table start afterwards, these transactions will no longer in turn be locked out by the indexing table locks and are able to complete successfully. The indexing process no longer impacts other concurrent transactions on the base table, it will be the only process potentially left hanging while waiting to acquire its associated lock resource.

This means it may not be quite so “risky” to urgently introduce that new index or rebuild that troublesome index during core business hours due to the reduced locking implications introduced in 11g.

http://richardfoote.wordpress.com/2008/02/11/index-create-and-rebuild-locking-improvements-in-11g-ch-ch-ch-changes/

추가 :

[Oracle is Mad] Index Rebuild를 둘러싼 논쟁 - Part2

왜 인덱스 크기가 계속 커지는가 - 삭제된 공간이 재활용됨에도 불구하고

2010년 4월 13일 화요일

Clustering Factor 변경

클러스터링 팩터를 직접 변경한다.

declare

m_numrows number;
m_numlblks    number;
m_numdist number;
m_avglblk number;
m_avgdblk number;
m_clstfct number;
m_indlevel    number;
m_guessq    number;

m_numblks number;
m_avgrlen number;

srec dbms_stats.statrec;
m_distcnt number;
m_density number;
m_nullcnt number;
m_avgclen number;

begin

dbms_stats.get_index_stats(
ownname => NULL,
indname => '&m_source_index.',
numrows => m_numrows,
numlblks    => m_numlblks,
numdist => m_numdist,
avglblk => m_avglblk,
avgdblk => m_avgdblk,
clstfct => m_clstfct,
indlevel    => m_indlevel
--    indlevel    => m_indlevel,
--    quessq    => m_guessq
);

dbms_stats.set_index_stats(
ownname => NULL,
indname => '&m_target_index.',
numrows => m_numrows,
numlblks    => m_numlblks,
numdist => m_numdist,
avglblk => m_avglblk,
avgdblk => m_avgdblk,
   clstfct => '&m_adj_cf',
indlevel    => m_indlevel
);

end;
/

원본 Script

rem

rem Script: hack_stats.sql

rem Author: Jonathan Lewis

rem Dated: Jun 2002

rem Purpose: Demo of modifying existing statistics

rem

rem Last tested

rem 10.0.1.4

rem 9.2.0.6

rem 8.1.7.4

rem

rem Needs some adjustment for 8.1

rem Does not cater for partitioning

rem

rem A quick and dirty way to change some stats on a

rem table, or move some stats from one table to another

rem You can set the source and target values to reference

rem the same thing if you want to

rem

rem Statistics should have been collected on the object,

rem the purpose of these scripts is to change some existing

rem values, not generate a complete new set.

rem

start setenv

define m_source_table='t1'

define m_source_column='x'

define m_source_index='t1_btree'

define m_target_table=''

define m_target_column=''

define m_target_index=''

rem

rem A convenient set to make the target match

rem the source. Comment out when not needed.

rem

define m_target_table='&m_source_table'

define m_target_index='&m_source_index'

define m_target_column='&m_source_column'

declare

m_numrows number;

m_numlblks number;

m_numdist number;

m_avglblk number;

m_avgdblk number;

m_clstfct number;

m_indlevel number;

m_guessq number;

m_numblks number;

m_avgrlen number;

srec dbms_stats.statrec;

m_distcnt number;

m_density number;

m_nullcnt number;

m_avgclen number;

begin

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

Index statistics

The references to guessq will have to be deleted

for Oracle 8i. This is the percentage guess for

a secondary index on an IOT, and is not implemented

until 9i. Unless the index is a secondary index,

you will have to delete the references to guessq

even in 9i and 10g.

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

dbms_stats.get_index_stats(

ownname => NULL,

indname => '&m_source_index.',

numrows => m_numrows,

numlblks => m_numlblks,

numdist => m_numdist,

avglblk => m_avglblk,

avgdblk => m_avgdblk,

clstfct => m_clstfct,

indlevel => m_indlevel

-- indlevel => m_indlevel,

-- quessq => m_guessq

);

m_indlevel := 3;

m_numlblks := 1000;

dbms_stats.set_index_stats(

ownname => NULL,

indname => '&m_target_index.',

numrows => m_numrows,

numlblks => m_numlblks,

numdist => m_numdist,

avglblk => m_avglblk,

avgdblk => m_avgdblk,

clstfct => m_clstfct,

indlevel => m_indlevel,

quessq => m_guessq

);

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

Table statistics

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

dbms_stats.get_table_stats(

ownname => NULL,

tabname =>'&m_source_table.',

numrows => m_numrows,

numblks => m_numblks,

avgrlen => m_avgrlen

);

m_avgrlen := m_avgrlen + 25;

dbms_stats.set_table_stats(

ownname => NULL,

tabname =>'&m_target_table.',

numrows => m_numrows,

numblks => m_numblks,

avgrlen => m_avgrlen

);

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

Column statistics

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

dbms_stats.get_column_stats(

ownname => NULL,

tabname => '&m_source_table.',

colname => '&m_source_column.',

distcnt => m_distcnt,

density => m_density,

nullcnt => m_nullcnt,

srec => srec,

avgclen => m_avgclen

);

m_avgclen := m_avgclen + 30;

dbms_stats.set_column_stats(

ownname => NULL,

tabname => '&m_target_table.',

colname => '&m_target_column.',

distcnt => m_distcnt,

density => m_density,

nullcnt => m_nullcnt,

srec => srec,

avgclen => m_avgclen

);

-- Just in case you comment everything out

null;

end;

출처 : Cost-Based Oracle Fundamentals Script

DBMS_STAT refernce

http://psoug.org/reference/dbms_stats.html

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm

2010년 4월 10일 토요일

SYS_OP_COUNTCHG

SYS_OP_COUNTCHG
Counts the number of blocks in a table but the results between these two methods were different when tested with sys.tab$ and very different from looking at BLOCKS in dba_tables and dba_segments.	SYS_OP_COUNTCHG(rowid, integer_between_1_and_255)
	conn uwclass/uwclass SELECT COUNT(DISTINCT dbms_rowid.rowid_block_number(rowid)) FROM airplanes; SELECT sys_op_countchg(SUBSTRB(ROWIDTOCHAR("AIRPLANES".ROWID),1,15),1) FROM airplanes; EXPLAIN PLAN FOR SELECT COUNT(DISTINCT dbms_rowid.rowid_block_number(rowid)) FROM airplanes; SELECT * FROM TABLE(dbms_xplan.display); EXPLAIN PLAN FOR SELECT sys_op_countchg(SUBSTRB(ROWIDTOCHAR("AIRPLANES".ROWID),1,15),1) FROM airplanes; SELECT * FROM TABLE(dbms_xplan.display); -- note the difference between the plans.

클러스터링 팩터

TABLE_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR NUM_ROWS DISTINCT_KEYS
------------------------------ ---------- ----------- ----------------- ---------- --

T2 2 5781 2499738 2500000 2500000

ex)

select /*+ cursor_sharing_exact
dynamic_sampling(0)
no_monitoring
no_expand
index(T2,"T2_IDX")
noparallel_index(T2,"T2_IDX")
*/ sys_op_countchg(SUBSTRB(ROWIDTOCHAR("T2".ROWID),1,15),5)
FROM PROC3.T2
WHERE "A" is not null;

SYS_OP_COUNTCHG(SUBSTRB(ROWIDTOCHAR("T2".ROWID),1,15),5)
--------------------------------------------------------
166998

이전의 방문한 블럭만 참조하는 클러스터링 팩터의 계산을 보정할때 사용한다.

sys_op_countchg 함수를 사용하여 이전의 방문한 블록의 목록의 일부를 저장하여 비교사용한다.

따라서 숫자에(빨간색표시) 값에 따라서 블록 목록의 윈도우가 정해지며,

값이 높을수록 클러스터링 팩터는 줄어들게 된다.

클러스터링 팩터는 메모리 사용량과도 관련이 있으므로 ( 디스크 성능 등 복합적인 튜닝 요구됨 ) , 너무 높은 값으로 하는것은 의미가 없다.

조정 값의 Max 값은 255

http://www.morganslibrary.org/reference/undocumented.html

What is a “Clustering Factor”?

http://oracleact.com/papers/cf.html

Ever since Oracle introduced Cost Based Optimizer, DBAs and developers tried to understand
how Oracle computes the “CLUSTERING FACTOR” metric when ever an index has been
analyzed. Here, I presented a small test case for computing the “CF”.

What is a “Clustering Factor”?

CF is a metric that tells Oracle how the rows in a table are randomly distributed with respect to
index key values.

I emphasis “with respect to index key values” words one more time because that’s how Oracle
computes the “CF” – lowest index key value to the highest index key value (when not using
parallel degree) which you will see later in this article.

Good CF Vs Bad CF OR Good Index Vs Bad Index

Over the years, we have been told if the CF value is close to number of blocks used by table
rows, then it is a “Good” index meaning the table rows are sorted with respect to the index keys.
On the other hand if the rows are distributed randomly, then it is a bad index, and you will
normally see a very high CF value that is very close the number of rows in the table. When the
optimizer computes execution plan for a SQL statement, it uses the CF value very much. On
many occasions the optimizer would not choose index access path simply because the CF value
is too high. This is mainly due to bug in calculating CF value, rounding errors,

lack of understanding in CF by Oracle kernel developers, usage of FREELISTS, FREELIST
GROUPS,
table partition etc. In this paper I stick with only “CF” calculation.

I will not talk about performance issues.

First I created a test table in which I stored 50 rows randomly selected from dba_objects.
SQL> create table clf_test
pctfree 95 pctused 5
tablespace tamil_large_data
as select object_id, object_name, status from dba_objects
where rownum < 51
order by dbms_random.value ;

Table created.

I used PCTFREE 95 so that atleast the rows are populated more than 1 block.
The next SQL verifies that rows are selected randomly.
Look at the OBJECT_ID column values.
SQL> select * from clf_test order by rowid ;

OBJECT_ID OBJECT_NAME STATUS
--------- --------------------------------- -------
48598 /1240abae_JdbcOperations VALID
49148 /1240f5cf_LessExpression VALID
53072 /10744837_ObjectStreamClass2 VALID
47880 /1225525_PlainTextInputStream VALID
60864 /1261d15c_CompilerOptions VALID
48658 /12579bb9_ResolutionDriverReso VALID
58824 /10e48aa3_StringExpressionCons VALID
59234 /1005bd30_LnkdConstant VALID
56842 /11144507_CharConv12ByteBuilde VALID
47666 /1236a7cd_SignatureFileBlock VALID
50556 /10845320_TypeMapImpl VALID

------ many lines are omitted --------------

61991 /10c906a0_ProfilePrinterErrors VALID
53168 /112785cc_FVDCodeBaseImpl VALID
51294 /1146b53d_BasicSliderUIScrollL VALID
61373 /122e6c15_OracleSqljXADataSour VALID
57102 /11799933_SchemaProtectionDoma VALID
62683 /11ba27f1_CustomizerHarnessBea VALID
49052 /10804ae7_Constants VALID

50 rows selected.

SQL> analyze table clf_test compute statistics ;
Table analyzed.

SQL> select table_name, tablespace_name , blocks, num_rows
from user_tables where table_name = 'CLF_TEST' ;

TABLE_NAME TABLESPACE_NAME BLOCKS NUM_ROWS
------------ -------------------- ---------- ----------
CLF_TEST TAMIL_LARGE_DATA 7 50

SQL> create index clf_test_idx on clf_test(object_id)
tablespace tamil_large_index compute statistics ;

Index created.

SQL> select index_name, tablespace_name, blevel, leaf_blocks,
distinct_keys, clustering_factor
from user_indexes where index_name = 'CLF_TEST_IDX' ;

INDEX_NAME TABLESPACE_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
------------ --------------- ------ ----------- ------------- -----------------
CLF_TEST_IDX TAMIL_LARGE_INDEX 0 1 50 45

SQL> select object_id, dbms_rowid.rowid_block_number(rowid) blk_num
from clf_test order by object_id;

Note CLF_MANUAL column is computed by me manually.

OBJECT_ID BLK_NUM CLF_MANUAL

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

47666 25611 1
47736 25613 2
47880 25610 3
48402 25613 4
48598 25610 5
48658 25610 5 - Clustering Factor remains same when Previous BLOCK # is same
48866 25613 6
49034 25613 6 - Clustering Factor remains same when Previous BLOCK # is same
49052 25616 7
49148 25610 8
49258 25612 9
49542 25612 9 - Clustering Factor remains same when Previous BLOCK # is same.
49714 25614 10
50132 25612 11
50556 25611 12
51156 25612 13
51294 25615 14
51480 25613 15
52160 25612 16
52414 25612 16 - Clustering Factor remains same when Previous BLOCK # is same.
52462 25614 17
53072 25610 18
53168 25615 19
54186 25613 20
55536 25612 21
56194 25611 22
56208 25615 23
56346 25614 24
56842 25611 25
57102 25615 26
57104 25611 27
57240 25614 28
58824 25610 29
58834 25614 30
59234 25610 31
59860 25613 32
60044 25615 33
60298 25612 34
60314 25614 35
60696 25611 36
60698 25614 37
60864 25610 38
60981 25613 39
61373 25615 40
61991 25615 40 - Clustering Factor remains same when Previous BLOCK # is same
62005 25611 41
62109 25614 42
62683 25616 43
63606 25611 44
64234 25615 45

My manual computation for “CLUSTERING FACTOR” value 45 is same as Oracle computed
value.

How Oracle gathers index statistics

First I used the “COMPUTE STATISTICS” along with CREATE INDEX command and enabled the
SQL trace. I didn’t see any useful information in the trace file.

If you use DBMS_STATS.gather_index_stats procedure to analyze index and also enable the
SQL trace, you will see the SQL statements for gathering statistics on index.

An example is given below for the index I created in my test case.
The tkprof output shows:
=====================
PARSING IN CURSOR #14 len=347 dep=1 uid=31 oct=3 lid=31 tim=1111951510293891 hv=2407722251 ad='8755ccf0'

select /*+ cursor_sharing_exact
dynamic_sampling(0)
no_monitoring
no_expand
index(t,"CLF_TEST_IDX")
noparallel_index(t,"CLF_TEST_IDX") */
count(*) as nrw,
count(distinct sys_op_lbid(67975,'L',t.rowid)) as nlb,
count(distinct "OBJECT_ID") as ndk,
sys_op_countchg(substrb(t.rowid,1,15),1) as clf
from "TAMIL"."CLF_TEST" t
where "OBJECT_ID" is not null

END OF STMT
PARSE#14:c=10000,e=1256,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1111951510293885
EXEC#14:c=0,e=54,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1111951510294037
FETCH#14:c=0,e=375,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,tim=1111951510294433
STAT #14 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT GROUP BY '
STAT #14 id=2 cnt=50 pid=1 pos=1 obj=67975 op='INDEX FULL SCAN CLF_TEST_IDX '

Oracle uses a function/procedure sys_op_countchg to calculate the “CLUSTERING FACTOR”.

The substr(rowid,1,15) is for object_id (6 bytes), file_id (3 bytes) and block number (6 bytes) and
they form a unique value. When gathering statistics on index without using parallel degree
option, Oracle reads all the index key values starting from the lowest leaf block to the last leaf
block and at the same time the sys_op_countchg function increments a counter whenever the
block number changes. It stores the previous block number.

(See previous page how I did it manually).

Till date Oracle has not published the code for sys_op_countchg.

2010년 4월 2일 금요일

Buffer Pinning / Latch

Buffer Pinning :

http://121.254.172.39:8080/pls/apex/f?p=101:11:0::::P11_QUESTION_ID:986700346022221

Latch :

http://wiki.ex-em.com/index.php/Latch:_cache_buffers_chains

2010년 3월 15일 월요일

no_parallel, no_parallel_index

1. no_parallel_index

Index를 사용할때 병렬 처리를 하지 않도록한다.

기본적으로 Index 는 single access 이다. 하지만 INDEX FAST FULL SCAN 의 경우 Index 의 물리적인 Block을 순차적으로 읽기 때문에 병렬 ACCESS 가 가능하다. 따라서 해당 힌트를 사용하면 실행계획 생성시 INDEX FAST FULL SCAN 을 사용하지 않는 방법을 택한다. ( INDEX FULL SCAN ..)

2. no_parallel

Table Scan 시에 병렬 처리를 하지 않도록한다.

일반적으로 CREATE AS SELECT 질의시 옵티마이져가 일반적으로 병렬처리를 하게된다.

해당 힌트로 사용하여, 옵티마이져가 병렬처리하는것을 배제하도록한다.

해당 테이블을 어떠한 특정 정렬로 복사 하고자 할때 no_parallel 힌트를 사용하지 않으면,

병렬처리로 인하여 원본 테이블과는 다른 Block 개수를 가지게 될수 있다.

아마 FreeList 의 할당 방법 때문인것으로 보이는데 병렬처리시에 FreeList Contention 을 줄이기 위해 각기 다른 Data Block을 할당하게 된다.

따라서 동시에 여러 Data Block 에 대해서 Insert 작업이 일어나기 때문에, 각각의 데이터 사용률 또한 원본 테이블의 분포도와는 차이를 보이게 되며,

사용하는 Data Block 또한 일반적으로 원본 테이블이 사용한 Block 수 보다 조금더 사용하게 된다.

또한 Index 생성시 Clustering Factor 또한 변화가 있을것이다.

사용예제

insert into v52.DSS_REGION nologging select
/*+ no_parallel(DSS_REGION) no_parallel_index(DSS_REGION) */ * from v40.DSS_REGION;

2010년 3월 13일 토요일

SORT_AREA_RETAINED_SIZE

Property	Description
Parameter type	Integer
Default value	Derived from `SORT_AREA_SIZE`
Modifiable	`ALTER SESSION`, `ALTER SYSTEM ... DEFERRED`
Range of values	From the value equivalent of two database blocks to the value of `SORT_AREA_SIZE`

Note:

Oracle does not recommend using the SORT_AREA_RETAINED_SIZE parameter unless the instance is configured with the shared server option. Oracle recommends that you enable automatic sizing of SQL working areas by setting PGA_AGGREGATE_TARGET instead. SORT_AREA_RETAINED_SIZE is retained for backward compatibility.

SORT_AREA_RETAINED_SIZE specifies (in bytes) the maximum amount of the user global area (UGA) memory retained after a sort run completes. The retained size controls the size of the read buffer, which Oracle uses to maintain a portion of the sort in memory. This memory is released back to the UGA, not to the operating system, after the last row is fetched from the sort space.

Oracle may allocate multiple sort spaces of this size for each query. Usually, only one or two sorts occur at one time, even for complex queries. In some cases, however, additional concurrent sorts are required, and each sort keeps its own memory area. If the shared server is used, allocation is to the SGA until the value in SORT_AREA_RETAINED_SIZE is reached. The difference between SORT_AREA_RETAINED_SIZE and SORT_AREA_SIZE is allocated to the PGA.

Note:

The default value as reflected in the V$PARAMETER dynamic performance view is 0. However, if you do not explicitly set this parameter, Oracle actually uses the value of the SORT_AREA_SIZE parameter.

PGA_AGGREGATE_TARGET

PGA_AGGREGATE_TARGET(PAT)

정의 : Oracle 9i부터 제공된 파라미터. 하나의 인스턴스에서 구동되는 모든 서버 프로세스(Server Process)가 사용하는 PGA 메모리의 합계 크기에 대한 목표치(Target)를 설정하는 파라미터
특징
1. 사용자가 Sort Area Size와 같은 Workarea Size를 개별 지정하지 않아도 오라클이 목표치를 기준으로 각 프로세스마다 적합한 크기의 PGA를 할당
2. 이 값이 설정되면 WORKAREA_SIZE_POLICY 파라미터 값은 AUTO로 세팅된 것으로 간주
  (SORT_AREA_SIZE, HASH_AREA_SIZE와 같은 Workarea Size를 결정하는 파라미터 값들은 무시)
3. 최대 설정 가능 사이즈가 아니고 한 세션에서 가질 수 있는 값을 할당하기 위한 지표로 사용 됨
pga_aggregate_target와 다른 파라미터들 간의 관계
```
예제】 
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=1G;
pga_aggregate_target = 1073741824 = 1G
_smm_max_size = 104850 = 100M
_smm_px_max_size = 524288 = 500M
_pga_max_size = 214732800 = 200M
```
1. _SMM_MAX_SIZE : 하나의 서버 프로세스가 사용 가능한 최대 Wokrarea 크기.
  위의 예를 보면, PAT 값을 1G로 지정하더라도 실제 하나의 서버 프로세스가 사용 가능한 최대 Workarea는 100M로 제한
2. _SMM_PX_MAX_SIZE : 하나의 병렬 실행에 속한 병렬 슬레이브들이 사용 가능한 최대 Workarea 크기
  위의 예를 보면, 모든 병렬 슬레이들이 사용 가능한 Workarea 영역의 최대 크기를 500M로 제한
3. _PGA_MAX_SIZE : 하나의 서버 프로세스가 사용 가능한 최대 PGA 크기
  PGA는 Wokarea외에 Session 정보, Cursor 정보 등의 정보를 포함
Hash/Sort 작업과의 관계
1. PGA_AGGREGATE_TARGET 파라미터에 의해 지정된 Workarea의 크기는 Hash 작업과 Sort 작업의 성능을 결정하는 중요한 값
2. 사용 가능한 Workarea의 크기에 따라 Hash/Sort 작업이 Optimal, Onepass 또는 Multipass로 실행
  실행 방법에 따라 Hash/Sort 작업의 성능이 크게 달라짐
3. Hash/Sort 작업 : PGA_AGGREGATE_TARGET 값을 이용해 Workarea의 크기를 증가 시키고 가능한 Optimal이나 Onepass 방법으로 실행
매우 큰 Sort Area가 필요할 경우 : 이 경우 큰 크기의 Worarea가 필요함
1. PGA_AGGREGATE_TARGET을 사용하는 경우 각 서버 프로세스가 사용 가능한 최대 Workarea의 크기가 오라클에 의해 일정한 크기로 제한
2. 오라클에 의해 지정된 크기 이상의 Sort Area가 필요한 경우 : 해당 세션에 대해서 WORKAREA_SIZE_POLICY 값을 Manual로 전환 후 SORT_AREA_SIZE 조정
```
예제 】
ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL;
ALTER SESSION SET SORT_AREA_SIZE = 2000000;
```

PGA 개념 및 특징

정의 : PGA(작업공간. Work Area) : 정렬, 해시 조인(Hash Join), 비트맵(Bitmap) 연산 등의 수행을 위한 메모리 영역

특징

8i까지 : SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE,
CREATE_BITMAP_AREA_SIZE와 같은 파라미터를 이용해서 관리자가 직접 개별 작업공간의 크기를 조정
9i 이후 : PGA_AGGREGATE_TARGET 파라미터를 이용하면 이러한 작업 공간의 크기 동적 관리 가능. 세션 별로 PGA_AGGREGATE_TARGET의 값을 골고루 분배

Multi pass sort가 발생하는 경우에는 정렬작업 중간에 정렬결과를 정렬 세그먼트(Sort Segment)영역에 읽고 쓰는 작업이 발생하게 되고, 이로 인해 direct path read temp, direct path write temp 대기가 발생

Warning

1. 해당 대기가 광범위하게 발생 시 : PGA_AGGREGATE_TARGET의 값을 적절히 늘려줌으로써 문제 해결
2. 주의점 : 서버 프로세스가 실제 사용 가능한 메모리의 크기에 제한 있음

Optimal pass sort : 모든 작업이 메모리상에서 이루어지는 경우
One pass sort : 프로세스에 할당된 작업공간의 크기 내에서 디스크 상에서 한번에 정렬이 이루어지는 것
Multi pass sort : One pass sort 의 반대 경우.

PGA_AGGREGATE_TARGET에 지정된 값의 일부분만을 개별 세션이 사용 가능

i) PGA_AGGREGATE_TARGET에 지정된 값에 따라 개별 프로세스에 할당 가능한 최대 메모리영역을 계산하는데, 그 값은 히든 파라미터인 _SMM_MAX_SIZE에 저장되며 단위는 Kbyte.
ii) _SMM_PX_MAX_SIZE : Parallel Query 수행 시 전체 슬레이브 세션들이 사용 가능한 최대 메모리 크기
Cf) V$SESSTAT : "session pga memory max" 값을 조회하면 세션이 실제로 사용한 최대 메모리 영역 조회 가능

PGA_AGGREGATE_TARGET을 그대로 사용하면서 특정 세션에 대해서만 작업 공간의 크기를 크게 해 줄 경우 : 해당 세션의 PGA 관리정책만 변경
- "alter session set workarea_size_policy = manual" 로 변경 → "alter session set sort_area_size = 필요한 크기" (오라클 튜닝 가이드 권고 사항)

크기 설정 : OS 레벨에서 20% 정도의 메모리를 사용한다고 가정

i) OLTP : PGA_AGGREGATE_TARGET = (total_mem * 80%) * 20%
☞ OLTP 시스템의 경우에는 여유메모리의 20% 정도
ii) DSS: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 50%
☞ DSS 시스템은 정렬작업이 많으므로 50% 정도를 사용
iii) 메모리용량 산정이 기본 정책으로 활용 가능하나, 절대적이지 않고, 불필요하게 많은 메모리를 할당하는 것은 바람직하지 않음

물리적인 메모리 크기를 초과하게끔 PGA 메모리영역의 크기를 할당 시 발생되는 문제점 : 페이지 아웃/페이지 인이 발생할 확률 높아짐(페이징 현상이 자주 발생하면 시스템 성능이 크게 저하되는 현상 발생)
PGA_AGGREGATE_TARGET 값 적절하게 설정 : direct path I/O가 사라지고 이로 인해 direct path read temp, direct path write temp 대기현상이 완전히 사라지고, 성능도 크게 개선됨

출처

# 이 문서는 오라클클럽에서 작성하였습니다.
# 출처 : http://wiki.oracleclub.com/display/DBSTUDY/PGA_AGGREGATE_TARGET?

PGA_AGGREGATE_TARGET

Property	Description
Parameter type	Big integer
Syntax	`PGA_AGGREGATE_TARGET =` `integer` `[K \| M \| G]`
Default value	10 MB or 20% of the size of the SGA, whichever is greater
Modifiable	`ALTER SYSTEM`
Range of values	Minimum: 10 MB Maximum: 4096 GB - 1
Basic	Yes

PGA_AGGREGATE_TARGET specifies the target aggregate PGA memory available to all server processes attached to the instance.

Setting PGA_AGGREGATE_TARGET to a nonzero value has the effect of automatically setting the WORKAREA_SIZE_POLICY parameter to AUTO. This means that SQL working areas used by memory-intensive SQL operators (such as sort, group-by, hash-join, bitmap merge, and bitmap create) will be automatically sized. A nonzero value for this parameter is the default since, unless you specify otherwise, Oracle sets it to 20% of the SGA or 10 MB, whichever is greater.

Setting PGA_AGGREGATE_TARGET to 0 automatically sets the WORKAREA_SIZE_POLICY parameter to MANUAL. This means that SQL workareas are sized using the *_AREA_SIZEparameters.

Oracle attempts to keep the amount of private memory below the target specified by this parameter by adapting the size of the work areas to private memory. When increasing the value of this parameter, you indirectly increase the memory allotted to work areas. Consequently, more memory-intensive operations are able to run fully in memory and less will work their way over to disk.

When setting this parameter, you should examine the total memory on your system that is available to the Oracle instance and subtract the SGA. You can assign the remaining memory to PGA_AGGREGATE_TARGET.

http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/initparams177.htm#REFRN10165

Assume that an Oracle instance is configured to run on a system with 4 GB of physical memory. Part of that memory should be left for the operating system and other non-Oracle applications running on the same hardware system. You might decide to dedicate only 80% (3.2 GB) of the available memory to the Oracle instance.

You must then divide the resulting memory between the SGA and the PGA.

For OLTP systems, the PGA memory typically accounts for a small fraction of the total memory available (for example, 20%), leaving 80% for the SGA.
For DSS systems running large, memory-intensive queries, PGA memory can typically use up to 70% of that total (up to 2.2 GB in this example).

Good initial values for the parameter PGA_AGGREGATE_TARGET might be:

For OLTP: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 20%
For DSS: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 50%

where total_mem is the total amount of physical memory available on the system.

In this example, with a value of total_mem equal to 4 GB, you can initially set PGA_AGGREGATE_TARGET to 1600 MB for a DSS system and to 655 MB for an OLTP system.

Heap Sort And Oracle Sorting

1. Heap Sort

Simple Code

void heapsort(int arr[], unsigned int N)
{
int t; /* the temporary value */
unsigned int n = N, parent = N/2, index, child; /* heap indexes */
/* loop until array is sorted */
for (;;) {
if (parent > 0) {
/* first stage - Sorting the heap */
t = arr[--parent]; /* save old value to t */
} else {
/* second stage - Extracting elements in-place */
n--; /* make the heap smaller */
if (n == 0) return; /* When the heap is empty, we are done */
t = arr[n]; /* save lost heap entry to temporary */
arr[n] = arr[0]; /* save root entry beyond heap */
}
/* insert operation - pushing t down the heap to replace the parent */
index = parent; /* start at the parent index */
child = index * 2 + 1; /* get its left child index */
while (child < n) {
/* choose the largest child */
if (child + 1 < n && arr[child + 1] > arr[child]) {
child++; /* right child exists and is bigger */
}
/* is the largest child larger than the entry? */
if (arr[child] > t) {
arr[index] = arr[child]; /* overwrite entry with child */
index = child; /* move index to the child */
child = index * 2 + 1; /* get the left child and go around again */
} else {
break; /* t's place is found */
}
}
/* store the temporary value at its new location */
arr[index] = t;
}
}

Heap Sort 는 Heap 자료구조를 이용한 Sorting 기법이다.

오라클은 Sort Join 시에 Heap Sort 를 사용한다. (CBO 책에 근거), 따라서 각 Sub List 의 크기, 즉 한번에 정렬해야될 Row 의 수에 따라서, 성능에 차이가 존재한다.

Heap 자료구조는 이진트리 형태를 가진다. 만약 부모의 노드의 키 값이 항상 자식 노드의 키값보다 크거나 같다면 max-heap 이라고 하고, 반대로 부모 노드의 키값이 자식노드의 키값보다 항상 작거나 같다면 min-heap 이라고 한다.

heap 구조는 이진트리의 형태로 구성되며, 배열로 구현한다. 따라서 K[n] 의 child node 는 K[2n], K[2n+1] 이되며, K[n] 의 parent node는 K⌊n/2⌋ 가 된다.

Heap 구조로 Bulid 하는것이 O(n) 의 시간이 걸린다.

n-1 번 실행하는것이 각각 O(log n) 의 시간이 걸리므로 총 수행시간은 O(nlogn) 이다.

2. ORACLE SORT

오라클은 Sort 시에 PGA(또는 UGA) 영역에서 사용하는 정렬 메커니즘은 Heap Sort 를 사용한다. ( 또는 유사한? ) 따라서 정렬메모리 공간에 이진트리를 위한 공간이 따로 마련되어야 한다.

이진트리의 높이는 예를 들어 8개의 로우가 정렬되기 위해서는 log2(n) = 3 인 이진트리가 만들어진다.

1. Merge 시의 비교 횟수는 로우개수 * log2(sort run 개수) 로 추정된다.

2. 메모리 정렬을 위한 비교 횟수는 로우개수 * log2(로우 개수) 로 추정된다.

In-Memory Sort 의 경우 정렬해야 할 로우의 개수가 많다면, 트리의 높이가 log2(로우 개수) 만큼 높아지기 때문에, 탐색하는 시간이 오래 걸리게 되고, 결국 더 많은 CPU 를 소비하게 된다.

sort_area_size 가 높아짐에 따라서 initial Runs 의 개수가 적어지게 된다. 따라서 Disk I/O 도 줄어들게 되며, Merge 시의 비교 횟수 또한 일정 값까지 줄어들게 된다.

하지만 각각의 Run 의 크기가 커짐에 따라서 트리의 높이가 높아져 그만큼 메모리내에서의 비교 횟수 또한 증가하게 된다. ( Trade off 가 존재함 )

성능 저하의 원인이 Disk 가 아니라면 One-Pass Sort 로 수행되도록 하여, 트리의 높이를 줄여 성능을 향상 시킬수도 있을것이다.

위의 내용은 CBO 책에 기반한 것으로써 9i ~ 10g r1 버전을 기반으로 하고 있다.

하지만 10g r2 버전부터 Sort 알고리즘이 수정된것으로 보인다. 실제로 수행되는 것을 보면 10g r2 의 sort 수행 성능이 향상됨을 볼수 있었다.

Faster sorting
Starting in 10gr2 we see an improved sort algorithm, Oracle10gRw introduced a new sort algorithm which is using less memory and CPU resources. A hidden parameter _newsort_enabled = {TRUEFALSE} governs whether the new sort algorithm will be used

Heap Sort 를 사용하면 CPU 오버헤드가 많이 발생한다. 따라서 많은 데이터 처리가 가능한 요즘 컴퓨팅 환경으로 접어듬에 따라, DISK I/O 에 집중되었던 COST 방식이 CPU 기반 COST 로 변화하였고,

따라서 메모리 내에서 CPU COST 많이 발생하는 알고리즘에 대해서도 변화가 필요하지 않을까 생각한다.

그런 맥락에서 오라클도 CPU 자원을 아끼기위해 다른 알고리즘으로 변경한것으로 추측한다.

Reference :

1. http://en.wikibooks.org/wiki/Algorithm_implementation/Sorting/Heapsort

2. Cost Based Oracle Fundamentals Book

3. Introduction to ALGORITHMS

4. 쉽게 배우는 알고리즘 - 한빛 미디어

2010년 3월 11일 목요일

Estimation the Size of a Join

1. 용어 정리

T(R) - R relation 의 총 튜플 수

T(S) - S relation 의 총 튜플 수

V(relation,attribute) - relation 에서 해당 attribute 의 distinct value 값의 개수 ex) V(R,a)

R ⋈ S - R 과 S 를 자연조인

Optimizer 가 Join Cost 계산을 위해서는 조인시에 생기는 결과 집합의 크기를 산출해야 한다.

해당 정보는 Join Order 계산에 사용하게 된다.

R, S 가 Join R(X,Y) ⋈ S(Y,Z) 한다고 가정하자.

Y 값으로 R, S 가 Join 된다고 가정하였을때, Y 값이 R 과 S 둘다 관련이 있다고 해당 정보만을 가지고는 확인할수가 없다.

몇가지 가정을 해보자..

1. 두 릴레이션이 Y 값에 대한 disjoing set 이라면, T(R ⋈ S) = 0 이다.

2. S 릴레이션의 키가 Y 이고 R 의 외래키와 대응된다면,

정확히 S 의 각 튜플 하나는 R 의 각 튜플들과 Join 하게 된다. 따라서 T(R ⋈ S) = T(R) 이다.

3. 거의 모든 R 과 S 의 Y 값들이 같은 값을 가진다면, T(R ⋈ S) = T(R)T(S) 이다.

위의 경우 외에도 여러가지 상황이 존재하지만, 일반적인 경우를 가정하여, 간단히 가정을 해보자.

1. Containment of Value Sets

만일 R 과 S 가 attribute Y 를 가지고 있고, V(R,Y) ≦ V(S,Y) 라면, 모든 R 의 Y 값들은 S 의 Y 값들에 포함된다.

2. Preservation of Value Sets

만일 R 의 attirbure A 가 S 에 포함되지 않을때, V(R ⋈ S, A) = V(R,A) 이다.

사실 1번 가정은 아마도 모순일수 있다. 하지만, Y 가 S 의 Key 이고, R 의 외래키에 대응된다면, 조건을 만족한다. 또한 거의 많은 다른 케이스에서도 거의 1번의 조건을 만족한다.

왜냐하면, 직관적으로 S 가 많은 Y의 값들을 가지고 있고, R 도 Y 값을 가지고 있다면, R 의 Y 값은 S 에도 존재할 가능성이 충분이 있다고, 기대 가능하기 때문이다.

2번가정 또한 아마도 모순일수 있다. 하지만, R ⋈ S 의 join attribute 들이 S 의 key 값이고, R 의 외래키와 대응된다면 2번의 조건을 만족한다.

사실상, R 이 "dangling tuples ( join 조건에 불필요한 튜플들 ) " 를 가지고 있다면, R 의 튜플들은 S 의 튜플과 join 되지 않게 된다. 하지만 dangling tuples 를 가지고 있더라도 2번의 가정은 여전히 유효하다.

위 2개의 가정을 기반으로하여 R(X,Y) ⋈ S(Y,Z) 의 join 크기를 추정할수 있다.

r 을 R 에 속하는 튜플이라고하고, s 는 S 에 속하는 튜플이라고 하자.

V(R,Y) ≥ V(S,Y) 라고 가정하면, 1번의 가정에 의해서 s 의 Y 값은 정확이 한개의 값이 R 에 나타나게 된다. 따라서, r 은 s의 1/V(R,Y) 의 같은 Y 값들을 가질수 있게 된다.

이와 유사하게, V(R,Y) < V(S,Y) 라면, r 의 Y 값들은 S 에 나타나게 되고, 가능성은 1/V(S,Y) 이다.

일반적으로 Y가 일치할 확률은, 1/max(V(R,Y),V(S,Y)) 이며, Join Cardinarity 는

T(R ⋈ S) = T(R)T(S) / max(V(R,Y),V(S,Y))

위의 공식은 T(R ⋈ S) 에서 두 릴레이션간 일치하는 tuples 들의 수를 계산하게 된다.

위에서와 같이 기본적으로 Join 의 크기를 정확히 측정하는것은 한계가 있다.

위의 가정이 일반적으로 맞는 가정이라고 해서, 경험상 이렇게 추정하는것이 어느정도 맞으리라고 기대하게 되는 경험적인 산출방법이기 때문에, 넓은 Case에 있어서 정확한 조인 크기를 산출하지 못한다.

이를 보완하기 위해서 상용 DBMS 의 Join 크기 산출은 위의 공식과 거의 일치하지만,

히스토그램 같은 통계치를 사용하여 Join 크기 산출의 오차를 수정하는 노력이 이루어지고 있다.

* 위의 글은 DATABASE SYSTEM The Complete Book Second Edition ( Jeffrey D. Ullman )

의 내용을 을 참고하였습니다. *

2010년 4월 27일 화요일

2010년 4월 13일 화요일

2010년 4월 10일 토요일

2010년 4월 2일 금요일

2010년 3월 15일 월요일

2010년 3월 13일 토요일

SORT_AREA_RETAINED_SIZE

PGA_AGGREGATE_TARGET(PAT)

# 이 문서는 오라클클럽에서 작성하였습니다. # 출처 : http://wiki.oracleclub.com/display/DBSTUDY/PGA_AGGREGATE_TARGET?

PGA_AGGREGATE_TARGET

2010년 3월 11일 목요일

# 이 문서는 오라클클럽에서 작성하였습니다.
# 출처 : http://wiki.oracleclub.com/display/DBSTUDY/PGA_AGGREGATE_TARGET?