2010년 3월 13일 토요일

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.

Quick Sort

Quick 정렬은 버블정렬과 함께, 가장 쉽게 응용할 수 있는 정렬기법이다. 평균적으로 O(n log n)번의 비교를 수행하며, 최악의 경우에 O(n^2)의 비교를 수행하도록 되어 있다.

정렬할 데이터가 이미 준비되어 있으며, 모든 데이터를 정렬해야 할경우 가장 빠른 수행속도를 보여주는 알고리즘으로 평가되고 있다.

소트효율

가장 비효율적인 '''버블소트'''는 O(N^2)이고, 퀵소트는 평균 O(NlogN)이다.
아무리 뛰어난 정렬 알고리즘(:12)을 개발한다고 하더라도, 데이터의 갯수가 N이면 O(NlogN)보다 더 좋을 수
없다는 것이 증명되어 있다.

즉 정렬알고리즘의 lower bound는 O(NlogN)이다.

단 최대값이 정해져 있는 데이터라면 counting 방식을 쓸수 있고 - counting sort, bucket sort, radix sort - 이 경우 O(n)이 보장될 것이다.

퀵정렬은 다음과 같은 방식으로 진행이 된다.

주어진 데이터 목록에서 임의의 원소를 고른다. 이 원소를 피봇이라 한다.
피봇을 기준으로 피봇의 앞에는 피봇보다 작은 숫자가 오도록 하고, 피봇 뒤에는 피봇보다 큰 원소가 오도록한다. 필요할 경우 피봇은 움지일 수 있다.

분할과정을 거치게 됨을 알 수 있다. 분할이 끝난뒤에 피봇은 더 이상 움직일 필요가 없이 그 자리에 고정된다. 흔히 분할정복알고리즘이라고 한다. 퀵소트는 분할정복방식을 쓰는 정렬알고리즘이라고 달리 부를 수 있을 것이다.

1,2의 과정을 재귀수행한다. 한번의 피봇이 선택되어서 분할이 이루어질 때마다. 반드시 고정되는 값이 생성이 되므로, 이 알고리즘은 반드시 끝난다는 것을 보장할 수 있다.

위의 이미지는 Quick Sort가 이루어지는 과정을 나타내고 있다. 좀더 쉬운 이해를 원한다면 Visual Sort문서를 보기 바란다.

위의 프로세스가 퀵소트 알고리즘의 핵심으로 머리속으로 이해했다면, 구현하는 것도 그리 어렵지 않을 것이다. 구현에도 몇가지 방법이 있는데, 그 중에서 별도의 버퍼를 필요로 하지 않는 내부분할 퀵소트가 널리 사용되고 있다. 이 방법은 정렬을 위한 임시버퍼를 필요로 하지 않으므로 메모리를 할당하고 유지하기 위한 비용을 고려할 필요가 없다는 장점이 있다.

다음은 C로 작성된 퀵정렬 알고리즘이다.

void quickSort(int numbers[], int array_size); 
void q_sort(int numbers[], int left, int right); 
 
void quickSort(int numbers[], int array_size) 
{ 
    q_sort(numbers, 0, array_size -1); 
} 
 
void q_sort(int numbers[], int left, int right) 
{ 
    int pivot, l_hold, r_hold; 
    l_hold = left; 
    r_hold = right; 
    pivot = numbers[left]; // 0번째 원소를 피봇으로 선택 
    while (left < right) 
    { 
        // 값이 선택한 피봇과 같거나 크다면, 이동할 필요가 없다 
        while ((numbers[right] >= pivot) && (left < right)) 
            right --; 
 
        // 그렇지 않고 값이 피봇보다 작다면, 
        // 피봇의 위치에 현재 값을 넣는다. 
        if (left != right) 
        { 
             numbers[left] = numbers[right]; 
        } 
        // 왼쪽부터 현재 위치까지 값을 읽어들이면서 
        // 피봇보다 큰 값이 있다면, 값을 이동한다. 
        while ((numbers[left] <= pivot) && (left < right)) 
            left ++; 
        if (left != right) 
        { 
             numbers[right] = numbers[left]; 
             right --; 
        } 
    } 
    // 모든 스캔이 끝났다면, 피봇값을 현재 위치에 입력한다. 
    // 이제 피봇을 기준으로 왼쪽에는 피봇보다 작거나 같은 값만 남았다. 
    numbers[left] = pivot; 
    pivot = left; 
    left = l_hold; 
    right = r_hold; 
 
    // 재귀호출을 수행한다. 
    if (left < pivot) 
        q_sort(numbers, left, pivot - 1); 
    if (right > pivot) 
        q_sort(numbers, pivot+1, right); 
} 
 
int main(int argc, char **argv) 
{ 
    int data[] = {3,7,8,5,2,1,9,5,4}; 
    int i; 
    quickSort(data, 9); 
    for (i =0; i < 9; i++) 
    { 
        printf("%d\n", data[i]); 
    } 
}

Quicksort is a very efficient sorting algorithm invented by C.A.R. Hoare. It has two phases:

the partition phase and
the sort phase.

As we will see, most of the work is done in the partition phase - it works out where to divide the work. The sort phase simply sorts the two smaller problems that are generated in the partition phase.

This makes Quicksort a good example of the divide and conquer strategy for solving problems. (You've already seen an example of this approach in the binary search procedure.) In quicksort, we divide the array of items to be sorted into two partitions and then call the quicksort procedure recursively to sort the two partitions, ie we divide the problem into two smaller ones and conquer by solving the smaller ones. Thus the conquer part of the quicksort routine looks like this:

quicksort( void *a, int low, int high )
  {
  int pivot;
  /* Termination condition! */
  if ( high > low )
    {
    pivot = partition( a, low, high );
    quicksort( a, low, pivot-1 );
    quicksort( a, pivot+1, high );
    }
  }

Initial Step - First Partition

Sort Left Partition in the same way

For the strategy to be effective, the partition phase must ensure that all the items in one part (the lower part) and less than all those in the other (upper) part.

To do this, we choose a pivot element and arrange that all the items in the lower part are less than the pivot and all those in the upper part greater than it. In the most general case, we don't know anything about the items to be sorted, so that any choice of the pivot element will do - the first element is a convenient one.

As an illustration of this idea, you can view this animation, which shows a partition algorithm in which items to be sorted are copied from the original array to a new one: items smaller than the pivot are placed to the left of the new array and items greater than the pivot are placed on the right. In the final step, the pivot is dropped into the remaining slot in the middle.

Quicksort

Quicksort is a fast sorting algorithm, which is used not only for educational purposes, but widely applied in practice. On the average, it has O(n log n) complexity, making quicksort suitable for sorting big data volumes. The idea of the algorithm is quite simple and once you realize it, you can write quicksort as fast as bubble sort.

Algorithm

The divide-and-conquer strategy is used in quicksort. Below the recursion step is described:

Choose a pivot value. We take the value of the middle element as pivot value, but it can be any value, which is in range of sorted values, even if it doesn't present in the array.
Partition. Rearrange elements in such a way, that all elements which are lesser than the pivot go to the left part of the array and all elements greater than the pivot, go to the right part of the array. Values equal to the pivot can stay in any part of the array. Notice, that array may be divided in non-equal parts.
Sort both parts. Apply quicksort algorithm recursively to the left and the right parts.

Partition algorithm in detail

There are two indices i and j and at the very beginning of the partition algorithm i points to the first element in the array and j points to the last one. Then algorithm moves i forward, until an element with value greater or equal to the pivot is found. Index j is moved backward, until an element with value lesser or equal to the pivot is found. If i ≤ j then they are swapped and i steps to the next position (i + 1), j steps to the previous one (j - 1). Algorithm stops, when i becomes greater than j.

After partition, all values before i-th element are less or equal than the pivot and all values after j-thelement are greater or equal to the pivot.

Example. Sort {1, 12, 5, 26, 7, 14, 3, 7, 2} using quicksort.

Notice, that we show here only the first recursion step, in order not to make example too long. But, in fact, {1, 2, 5, 7, 3} and {14, 7, 26, 12} are sorted then recursively.

Why does it work?

On the partition step algorithm divides the array into two parts and every element a from the left part is less or equal than every element b from the right part. Also a and b satisfy a ≤ pivot ≤ binequality. After completion of the recursion calls both of the parts become sorted and, taking into account arguments stated above, the whole array is sorted.

Complexity analysis

On the average quicksort has O(n log n) complexity, but strong proof of this fact is not trivial and not presented here. Still, you can find the proof in [1]. In worst case, quicksort runs O(n²) time, but on the most "practical" data it works just fine and outperforms other O(n log n) sorting algorithms.

Code snippets

Partition algorithm is important per se, therefore it may be carried out as a separate function. The code for C++ contains solid function for quicksort, but Java code contains two separate functions for partition and sort, accordingly.

Java

int partition(int arr[], int left, int right)

{

int i = left, j = right;

int tmp;

int pivot = arr[(left + right) / 2];

while (i <= j) {

while (arr[i] < pivot)

i++;

while (arr[j] > pivot)

j--;

if (i <= j) {

tmp = arr[i];

arr[i] = arr[j];

arr[j] = tmp;

i++;

j--;

}

};

return i;

}

void quickSort(int arr[], int left, int right) {

int index = partition(arr, left, right);

if (left < index - 1)

quickSort(arr, left, index - 1);

if (index < right)

quickSort(arr, index, right);

}

C++

void quickSort(int arr[], int left, int right) {

int i = left, j = right;

int tmp;

int pivot = arr[(left + right) / 2];

/* partition */

while (i <= j) {

while (arr[i] < pivot)

i++;

while (arr[j] > pivot)

j--;

if (i <= j) {

tmp = arr[i];

arr[i] = arr[j];

arr[j] = tmp;

i++;

j--;

}

};

/* recursion */

if (left < j)

quickSort(arr, left, j);

if (i < right)

quickSort(arr, i, right);

}

Reference :

http://www.joinc.co.kr/modules/moniwiki/wiki.php/Site/Database/algorithm/Quick_Sort

http://www.cs.auckland.ac.nz/software/AlgAnim/qsort.html

http://www.algolist.net/Algorithms/Sorting/Quicksort

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월 12일 금요일

Truncate

Oracle Truncate Table

Version 11.1

Note: Deletes perform normal DML. That is, they take locks on rows, they generate redo (lots of it), and they require segments in the UNDO tablespace. Deletes clear records out of blocks carefully. If a mistake is made a rollback can be issued to restore the records prior to a commit. A delete does not relinquish segment space thus a table in which all records have been deleted retains all of its original blocks. Truncates are DDL and, in a sense, cheat. A truncate moves the High Water Mark of the table back to zero. No row-level locks are taken, no redo or rollback is generated. All extents bar the initial are de-allocated from the table (if you have MINEXTENTS set to anything other than 1, then that number of extents is retained rather than just the initial). By re-positioning the high water mark, they prevent reading of any table data, so they have the same effect as a delete, but without all the overhead. Just one slight problem: a truncate is a DDL command, so you can't roll it back if you decide you made a mistake. (It's also true that you can't selectively truncate -no "WHERE" clause is permitted, unlike with deletes, of course). By resetting the High Water Mark, the truncate prevents reading of any table's data, so they it has the same effect as a delete, but without the overhead. There is, however, one aspect of a Truncate that must be kept in mind. Because a Truncate is DDL it issues a COMMIT before it acts and another COMMIT afterward so no rollback of the transaction is possible. Note that by default, TRUNCATE drops storage even if DROP STORAGE is not specified. Oracle Database SQL Reference documentation for versions 11.1, 10.2, 10.1 and 9.2 all state that "DROP STORAGE" is default option for TRUNCATE. That is: "DROP STORAGE: Specify DROP STORAGE to deallocate all space from the deleted rows from the table or cluster except the space allocated by the MINEXTENTS parameter of the table or cluster. This space can subsequently be used by other objects in the tablespace. Oracle also sets the NEXT storage parameter to the size of the last extent removed from the segment in the truncation process. This is the default." (Emphasis added)

Truncate Table
Simple Truncate	TRUNCATE TABLE <table_name> [<PRESERVE \| PURGE>] [MATERIALIZED VIEW LOG] [<DROP \| REUSE> STORAGE]; By default drops storage even if DROP STORAGE is not specified. The PURGE option is for truncating materialized views and purges the log.
	CREATE TABLE test AS SELECT * FROM all_objects WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'WZZZZZ'; SELECT COUNT() FROM test; TRUNCATE TABLE test; or more explicitly TRUNCATE TABLE test DROP STORAGE; SELECT COUNT() FROM test;
Empty Table And Move Highwater Mark	CREATE TABLE test ( testcol VARCHAR2(20)); BEGIN FOR i IN 1..10000 LOOP INSERT INTO test (testcol) VALUES ('ABCDEFGHIJKLMNOPQRST'); END LOOP; COMMIT; END; / exec dbms_stats.gather_table_stats(OWNNAME=>'UWCLASS', TABNAME=>'TEST'); SELECT SUM(blocks) FROM user_extents WHERE segment_name = 'TEST'; TRUNCATE TABLE test; SELECT SUM(blocks) FROM user_extents WHERE segment_name = 'TEST';
Empty Table And Do Not Move Highwater Mark	TRUNCATE TABLE <table_name> REUSE STORAGE;
	BEGIN FOR i IN 1..10000 LOOP INSERT INTO test (testcol) VALUES ('ABCDEFGHIJKLMNOPQRST'); END LOOP; COMMIT; END; / exec dbms_stats.gather_table_stats(OWNNAME=>'UWCLASS', TABNAME=>'TEST'); SELECT SUM(blocks) FROM user_extents WHERE segment_name = 'TEST'; TRUNCATE TABLE test REUSE STORAGE; SELECT SUM(blocks) FROM user_extents WHERE segment_name = 'TEST';

Truncate Partition
Truncate A Single Partition In A Partitioned Table	ALTER TABLE <table_name> TRUNCATE PARTITION <partition_name>;
	CREATE TABLE parttab ( state VARCHAR2(2), sales NUMBER(10,2)) PARTITION BY LIST (state) ( PARTITION northwest VALUES ('OR', 'WA') TABLESPACE uwdata, PARTITION southwest VALUES ('AZ', 'CA') TABLESPACE uwdata); INSERT INTO parttab VALUES ('OR', 100000); INSERT INTO parttab VALUES ('WA', 200000); INSERT INTO parttab VALUES ('AZ', 300000); INSERT INTO parttab VALUES ('CA', 400000); COMMIT; SELECT * FROM parttab PARTITION(northwest); SELECT * FROM parttab PARTITION(southwest); ALTER TABLE parttab TRUNCATE PARTITION southwest; SELECT * FROM parttab PARTITION(northwest); SELECT * FROM parttab PARTITION(southwest);

Truncate In Other Schemas
This procedure was provided to the library by JP Vijaykumar. The second parameter in this function, "p__storage BOOLEAN DEFAULT FALSE" was suggested by Mariusz Mroz, who indicated that it's important for huge tables and full table scans operations.
	/* In applications, users need truncate privileges on third party tables. The owner of the table can truncate his/her own tables. A user with DBA role granted can truncate a third party table. Any user with "drop any table" system privilege can truncate a third party table. It is not recommended in Production environments to grant powerful roles like DBA role or privileges like "drop any table" privilege to accomplish this purpose. This procedure need to be created in each schema. The schema owner need to grant execute permission to the user, and the delete permission on specified tables. Then the user can execute the procedure to truncate tables User A owns the table temp_jp. conn uwclass/uwclass CREATE TABLE t1 AS SELECT * FROM all_tables; CREATE OR REPLACE PROCEDURE trunc_tab(p_tabname IN VARCHAR2, p_storage BOOLEAN DEFAULT FALSE) AUTHID DEFINER AS /******************************** AUTHOR JP Vijaykumar ORACLE DBA *******************************/ v_num NUMBER(10):=0; v_owner VARCHAR2(30); v_user VARCHAR2(30); sql_stmt VARCHAR2(2000); BEGIN SELECT username INTO v_owner FROM user_users; SELECT sys_context('USERENV','SESSION_USER') INTO v_user FROM dual; IF p_drop_storage THEN sql_stmt:='TRUNCATE TABLE ' \|\| v_owner \|\| '.' \|\| p_tabname \|\| ' REUSE STORAGE'; ELSE sql_stmt:='TRUNCATE TABLE ' \|\| v_owner \|\| '.' \|\| p_tabname; END IF; IF (v_owner = v_user) THEN execute immediate sql_stmt; ELSE SELECT COUNT() INTO v_num FROM all_tab_privs WHERE table_name = UPPER(p_tabname) AND table_schema = v_owner AND grantee = v_user AND privilege = 'DELETE'; IF (v_num > 0) THEN execute immediate sql_stmt; ELSE RAISE_APPLICATION_ERROR(-20001,'Insufficient privileges.'); END IF; END IF; EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20001,'Insufficient privileges'); END trunc_tab; / GRANT execute ON t1 TO abc; conn abc/abc exec uwclass.trunc_tab('T1'); conn uwclass/uwclass GRANT delete ON t1 TO abc; conn abc/abc exec uwclass.trunc_tab('T1'); conn uwclass/uwclass SELECT * FROM t1;

Related Topics

Delete

Partitions

Tables

출처 : http://www.psoug.org/reference/truncate.html
Oracle Doc : http://download-west.oracle.com/docs/cd/B12037_01/server.101/b10759/statements_10006.htm

Thread: DELETE, DROP, TRUNCATE의 비교
http://forums.oracle.com/forums/thread.jspa?threadID=468676

Index에 걸린 컬럼의 상태 확인 [ALL_IND_COLUMNS]

ALL_IND_COLUMNS

ALL_IND_COLUMNS describes the columns of indexes on all tables accessible to the current user.

Note:

For join indexes, the TABLE_NAME and TABLE_OWNER columns in this view may not match the TABLE_NAME and TABLE_OWNER columns you find in the *_INDEXES (and other similar) data dictionary views.

Related Views

DBA_IND_COLUMNS describes the columns of indexes on all tables in the database.
USER_IND_COLUMNS describes the columns of indexes owned by the current user and columns of indexes on tables owned by the current user. This view does not display the INDEX_OWNER or TABLE_OWNER columns.

Column	Datatype	NULL	Description
`INDEX_OWNER`	`VARCHAR2(30)`	`NOT NULL`	Owner of the index
`INDEX_NAME`	`VARCHAR2(30)`	`NOT NULL`	Name of the index
`TABLE_OWNER`	`VARCHAR2(30)`	`NOT NULL`	Owner of the table or cluster
`TABLE_NAME`	`VARCHAR2(30)`	`NOT NULL`	Name of the table or cluster
`COLUMN_NAME`	`VARCHAR2(4000)`		Column name or attribute of object type column
			Note: If you create an index on a user-defined `REF` column, the system creates the index on the attributes that make up the `REF` column. Therefore, the column names displayed in this view are the attribute names, with the `REF` column name as a prefix, in the following form: `"REF_name"."attribute"`
`COLUMN_POSITION`	`NUMBER`	`NOT NULL`	Position of column or attribute within the index
`COLUMN_LENGTH`	`NUMBER`	`NOT NULL`	Indexed length of the column
`CHAR_LENGTH`	`NUMBER`		Maximum codepoint length of the column
`DESCEND`	`VARCHAR2(4)`		Whether the column is sorted in descending order (`Y`/`N`)

ex)

select INDEX_NAME , COLUMN_POSITION , COLUMN_NAME from dba_ind_columns where table_owner = 'V40';

해당 View 를 이용하여, 해당 Index 에 걸려 있는 컬럼의 상태 를 파악할수 있다.

2010년 3월 13일 토요일