v$sql_workarea_histogram
from OTN v$sql_workarea_histogram
위의 파라미터는 인스턴스 시작이래 정렬을 위해 사용된 work area의 사용 횟수를
누적한다.
work area의 size는 총 33개로 0k ~ 1k , 1k ~ 2k, … , 2TB ~ 4TB 까지 2배수로 증가한다.
VIEW의 구성 컬럼으로 아래와 같다.
|
COLUMN |
DATATYPE |
|
LOW_OPTIMAL_SIZE |
NUMBER |
|
HIGH_OPTIMAL_SIZE |
NUMBER |
|
OPTIMAL_EXECUTIONS |
NUMBER |
|
ONEPASS_EXECUTIONS |
NUMBER |
|
MULTIPASSES_EXECUTIONS |
NUMBER |
|
TOTAL_EXECUTIONS |
NUMBER |
LOW_OPTIMAL_SIZE 와 HIGH_OPTIMAL_SIZE 는 WORK AREA의 범주를 의미한다.
나머지 *_EXECUTIONS 는 INSTANCE가 시작된 이래로의 해당 연산의 실행 횟수를
의미한다.
필자는 PGA_AGGREGATE_TARGET을 사용하고 있기에 SORT AREA SIZE를 줄이기 위해
앞의 파라미터 사이즈를 조절하였다.
SQL> alter system set pga_aggregate_target=1m scope=both;
alter system set pga_aggregate_target=1m scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00093: pga_aggregate_target must be between 10M and 4096G-1
PGA_AGGREGATE_TARGET 의 SIZE는 10M 이상이다.
SQL> alter system set pga_aggregate_target=10m scope=both;
System altered.
최소 크기로 수정하였다.
SQL> get e_swhist;
1 rem 'examine the usage of pga whether one-pass or n-pass'
2 select low_optimal_size/1024 "L(K)",
3 (high_optimal_size+1)/1024 "H(K)",
4 optimal_executions "OptExec",
5 onepass_executions "OneExec",
6 multipasses_executions "MulExec"
7 from v$sql_workarea_histogram
8* where total_executions <> 0
9
high_optimal_size에 1을 더해준 이유는 가독성을 위해서이다.
1을 더하지 않고 1024로 나누게 되면 딱 떨어지지 않는다.
1* select low_optimal_size,high_optimal_size from v$sql_workarea_histogram
SQL> /
LOW_OPTIMAL_SIZE HIGH_OPTIMAL_SIZE
---------------- -----------------
0 1023
1024 2047
SQL> @e_swhist
L(K) H(K) OptExec OneExec MulExec
---------- ---------- ---------- ---------- ----------
4 8 198 0 0
지금까지 사용된 work area 는 4 ~ 8 K뿐이었고 one-pass도 multi-pass도 없었다는 것을
의미한다.
아래 코드로 과도한 sort를 유발해 보겠다.
SQL> select count(*) from
2 ( select cust_id,prod_id,channel_id
3 from sh.sales
4 order by 1 desc, 2 asc, 3 desc);
COUNT(*)
----------
1016271
SQL> save sort
Created file sort.sql
SQL> @e_swhist
L(K) H(K) OptExec OneExec MulExec
---------- ---------- ---------- ---------- ----------
4 8 222 0 0
8 16 12 0 0
16384 32768 0 0 2
SQL> select 16384/1024 from dual;
16384/1024
----------
16
16 ~ 32 메가의 multipass가 2번 발생하였다.
SQL> alter system set pga_aggregate_target=32m scope=both;
System altered.
sort area 로 할당 될 수 있는 크기를 늘려주었다.
SQL> @sort
COUNT(*)
----------
1016271
SQL> @e_swhist
L(K) H(K) OptExec OneExec MulExec
---------- ---------- ---------- ---------- ----------
4 8 222 0 0
8 16 12 0 0
16384 32768 0 0 4
멀티패스로 2회 실행 카운트가 올라간 것을 볼 수 있다.
SQL> alter system set pga_aggregate_target=64m scope=both;
System altered.
공간을 더 크게 늘려주었다.
SQL> @sort
COUNT(*)
----------
1016271
SQL> @e_swhist
L(K) H(K) OptExec OneExec MulExec
---------- ---------- ---------- ---------- ----------
4 8 222 0 0
8 16 12 0 0
16384 32768 0 2 4
one-pass 실행으로 2회 추가된 모습을 확인하였다.
pga_aggregate_target 을 늘려준 것은 근본적으로 정렬공간으로 사용할 수 있는
공간을 늘려주자는 것이었다.
다만 위 파라미터는 자동적으로 pga를 통합적으로 관리하는 파라미터이기 때문에
sort_area_size 를 사용한 경우에 비해 유추가 힘들지 않았나 싶다.
또 pga_aggregate_target을 늘려줄때도 주의해야 할 점은 해당 work area 가 사용되는
빈도를 따져봐야 한다는 것이다.
마지막으로 위 파라미터의 적절한 size를 정하는데 있어 어려움은 multi pass 의
의미에 있지 않나 싶다.
위 one-pass 때는 그나마 낫지만 multi pass의 경우 얼마나 크기를 조절해야 개선되는지
알 수가 없다.
/*+ 위의 view와 별개로 추가적인 정보를 수집해야 할 것이다. */