|
Sort
|
In this issue, I will continue the basic analysis of sort.
PGA_AGGREGATE_TARGET specifies the total amount of PGA memory available within the instance.
I wonder if a single process can consume the specified size to the maximum or if there is
any upper limit.
I will change the value of PGA_AGGREGATE_TARGET dynamically to examine if there is any upper limit
of memory size that a single process can use for sort process.
(Test environment)
OS:Red Hat Linux release7.1
Oracle:9.2.0.1
Set PGA Memory Management to AUTO.
SQL> alter system set pga_aggregate_target=10M;
System altered.
SQL> alter system set workarea_size_policy=auto;
System altered.
|
Perform sort process (pga_aggregate_target=10M)
SQL> set timing on
SQL> select distinct * from code where rownum<1000000;
Elapsed: 00:01:45.43
*View SQL workarea
SQL> select
sql_text,operation_type,policy,last_memory_used/1024/1024,last_execution,
last_tempseg_size from v$sql l,v$sql_workarea a
where l.hash_value=a.hash_value
and sql_text='select distinct * from code where rownum<1000000';
SQL_TEXT OPERATION_TYPE
------------------------------------------------ ----------------
select distinct * from code where rownum<1000000 GROUP BY (SORT)
POLICY LAST_MEMORY_USED/1024/1024 LAST_EXECUTION LAST_TEMPSEG_SIZE
------ ---------------------------- -------------- -----------------
AUTO .5 61 PASSES 26214400
|
Take a look at LAST_MEMORY_USED column.
PGA_AGGREGATE_TARGET is set to 10M, but the process does not consume
10M. Now, I will increase the size of the PGA_AGGREGATE_TARGET.
Set PGA_AGGREGATE_TARGET to 20M.
SQL> alter system set pga_aggregate_target=20M;
System altered
SQL> select distinct * from code where rownum<1000000;
Elapsed: 00:01:04.61
SQL> select
sql_text,operation_type,policy,last_memory_used/1024/1024,last_execution,
last_tempseg_size from v$sql l,v$sql_workarea a
where l.hash_value=a.hash_value
and sql_text='select distinct * from code where rownum<1000000';
SQL_TEXT OPERATION_TYPE
------------------------------------------------ ----------------
select distinct * from code where rownum<1000000 GROUP BY (SORT)
POLICY LAST_MEMORY_USED/1024/1024 LAST_EXECUTION LAST_TEMPSEG_SIZE
------ ---------------------------- -------------- -----------------
AUTO 1.09570313 4 PASSES 25165824
|
Set PGA_AGGREGATE_TARGET to 30M.
SQL> alter system set pga_aggregate_target=30M;
System altered.
SQL> select distinct * from code where rownum<1000000;
Elapsed: 00:00:58.25
SQL> select
sql_text,operation_type,policy,last_memory_used/1024/1024,last_execution,las
t_tempseg_size from v$sql l,v$sql_workarea a
where l.hash_value=a.hash_value
and sql_text='select distinct * from code where rownum<1000000';
SQL_TEXT OPERATION_TYPE
------------------------------------------------ ----------------
select distinct * from code where rownum<1000000 GROUP BY (SORT)
POLICY LAST_MEMORY_USED/1024/1024 LAST_EXECUTION LAST_TEMPSEG_SIZE
------ ---------------------------- -------------- -----------------
AUTO 1.5 2 PASSES 25165824
|
Set PGA_AGGREGATE_TARGET to 40M.
SQL> alter system set pga_aggregate_target=40M;
System altered.
SQL> select distinct * from code where rownum<1000000;
Elapsed: 00:00:56.92
SQL> select
sql_text,operation_type,policy,last_memory_used/1024/1024,last_execution,
last_tempseg_size from v$sql l,v$sql_workarea a
where l.hash_value=a.hash_value
and sql_text='select distinct * from code where rownum<1000000';
SQL_TEXT OPERATION_TYPE
------------------------------------------------ ----------------
select distinct * from code where rownum<1000000 GROUP BY (SORT)
POLICY LAST_MEMORY_USED/1024/1024 LAST_EXECUTION LAST_TEMPSEG_SIZE
------ ---------------------------- -------------- -----------------
AUTO 2 1 PASSES 25165824
|
Set PGA_AGGREGATE_TARGET to 4095G.
SQL> alter system set pga_aggregate_target=4095G;
System altered.
SQL> select distinct * from code where rownum<1000000;
Elapsed: 00:00:51.81
SQL> select
sql_text,operation_type,policy,last_memory_used/1024/1024,last_execution,
last_tempseg_size from v$sql l,v$sql_workarea a
where l.hash_value=a.hash_value
and sql_text='select distinct * from code where rownum<1000000';
SQL_TEXT OPERATION_TYPE
------------------------------------------------ ----------------
select distinct * from code where rownum<1000000 GROUP BY (SORT)
POLICY LAST_MEMORY_USED/1024/1024 LAST_EXECUTION LAST_TEMPSEG_SIZE
------ ---------------------------- -------------- -----------------
AUTO 35.75 OPTIMAL
|
The process does not consume the memory up to the specified size, but is able to
consume the PGA_AGGREGATE_TARGET*5% to the maximum.
The following SQL statement also proves the result.
SQL> select name,value/1024/1024 from v$pgastat
where name in ('aggregate PGA target parameter','global memory bound');
NAME VALUE/1024/1024
------------------------------ ------------------
aggregate PGA target parameter 10
global memory bound .5
|
According to the manual, Global Memory Bound is the maximum size of a work
area executed in automatic mode. Take a look at the result above.
aggregate PGA target parameter:10(M)*5% = 0.5(M)
Why a single process does not consume the specified size to the maximum?
It is of course a specification. Also, if a single process consumes the entire
memory size, other processes are unable to use the memory and the entire throughput
will slow down.
That's it for today.
Have a happy holiday season.
Takuya Kishimoto
|
|
