|
Sort
|
In this issue, I will analyze if the workarea can automatically be set within the value of
pga_aggregate_target when a hundred processes perform a sort process at the same time.
(Test 3)
I make a hundred processes perform a sort at the same time.
(Test environment)
OS:Red Hat Linux release 7.1
Oracle:9.2.0.1
Set pga_aggregate_target to 50MB
SQL> select value/1024/1024 from v$parameter
where name = 'pga_aggregate_target';
VALUE/1024/1024
---------------
50
|
I use our opensource language SQeeL to make a hundred processes perform sort.
SQL> select count(*) from v$session where program='sqeel.exe';
COUNT(*)
----------
100
|
I view actual_mem_used column in v$sql_workarea_active to check the total amount
of memory required for sorting.
SQL> select count(*),sum(ACTUAL_MEM_USED)/1024/1024
from v$sql_workarea_active;
COUNT(*) SUM(ACTUAL_MEM_USED)/1024/1024
---------- ------------------------------
100 24.9804688
|
I also view pga_used_mem column in v$process to check the total PGA memory.
SQL> select count(*),sum(pga_used_mem)/1024/1024 from v$process;
COUNT(*) SUM(PGA_USED_MEM)/1024/1024
---------- ---------------------------
110 73.0665865
|
Take a look at the value of pga_used_mem. The value is larger than 50MB
that is the amount set to the value of the pga_aggregate_target.
Next, I check the total amount of PGA memory used.
The following SQL statement indicates that the PGA memory used for sorting is
larger than the value of the pga_aggregate_target.
SQL> select * from v$pgastat;
NAME VALUE UNIT
---------------------------------------- -------------------- ------------
aggregate PGA target parameter 52428800 bytes
aggregate PGA auto target 4194304 bytes
global memory bound 131072 bytes
total PGA inuse 89151488 bytes
total PGA allocated 164722688 bytes
maximum PGA allocated 168338432 bytes
total freeable PGA memory 327680 bytes
PGA memory freed back to OS 27360231424 bytes
total PGA used for auto workareas 30837760 bytes
maximum PGA used for auto workareas 79933440 bytes
total PGA used for manual workareas 0 bytes
maximum PGA used for manual workareas 0 bytes
over allocation count 16461
bytes processed 963551232 bytes
extra bytes read/written 316532736 bytes
cache hit percentage 75 percent
|
The total PGA inuse row indicates that about 85MB of PGA memory is used.
The over allocation count is cumulative. Over allocation of the PGA memory
occurs if the value of the pga_aggregate_target is too small.
Now, I check the result of the sort processes.
SQL> select * from code where rownum<300000 order by 1;
299999 rows selected
Elapsed: 00:24:24.38
SQL> select sql_text,operation_type,policy,last_memory_used/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 * from code where rownum<300000 order by 1';
SQL_TEXT OPERATION_TYPE
-------------------------------------------------- ----------------
select * from code where rownum<300000 order by 1 SORT
POLICY LAST_MEMORY_USED/1024 LAST_EXECUTION LAST_TEMPSEG_SIZE
-------- ----------------------- ---------------- -------------------
AUTO 561 108 PASSES 11534336
|
LAST_MEMORY_USED column indicates that 561KB of memory is used, which is about 1% of
the value of the pga_aggregate_target. When I analyzed the concurrent sort by
twenty processes last time, the execution time was 1 minute 37 seconds.
This time, it takes about 24 minutes that is fifteen times longer than that of twenty processes.
Even though Automatic PGA Memory Management is active, it does not necessarily mean
memory swap and paging do not occur at all.
If too many processes attempt to perform a sort at the same time, the value of the pga_aggregate_target
is unable to handle all processes, and as a result, over allocation of PGA memory occurs.
That's it for today.
Takuya Kishimoto
|
|
