|
Sort
|
When Automatic PGA Memory Management is activated, a single process can use the workarea
up to pga_aggregate_target*5%.
In this issue, I will analyze how the amount of PGA memory changes if twenty processes
perform a sort at the same time.
(Test environment)
OS:Red Hat Linux release 7.1
Oracle:9.2.0.1
pga_aggregate_target=50MB
SQL> select value/1024/1024 from v$parameter
where name = 'pga_aggregate_target';
VALUE/1024/1024
---------------
50
|
(Test 1)
I make twenty processes perform sort at a time.
I use our opensource language SQeeL to do so.
SQL> select count(*) from v$session where program='sqeel.exe';
COUNT(*)
----------
20
|
I view v$sql_workarea_active to check the total amount of memory used for sort in
SUM (ACTUAL_MEM_USED) column.
SQL> select count(*),sum(ACTUAL_MEM_USED)/1024/1024
from v$sql_workarea_active;
COUNT(*) SUM(ACTUAL_MEM_USED)/1024/1024
---------- ------------------------------
20 18.78125
|
I also view v$process to check the total amount of PGA memory
in SUM (PGA_USED_MEM) column.
SQL> select count(*),sum(pga_used_mem)/1024/1024 from v$process;
COUNT(*) SUM(PGA_USED_MEM)/1024/1024
---------- ---------------------------
30 47.2087221
|
Take a look at the pga_used_mem column.
The amount of PGA memory is less than 50MB that is the amount
set to the value of pga_aggregate_target.
There are two major reasons why the memory amount in pga_used_mem is
larger than that of actual_mem_used.
1. In addition to sort area, PGA has session information, cursor state,
and stack space.
2. Oracle background process is active.
View the result of SQL statement.
SQL> select * from code where rownum<300000 order by 1
299999 rows selected
Elapsed: 00:01:37.05
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 * from code where rownum<300000 order by 1';
SQL_TEXT OPERATION_TYPE
-------------------------------------------------- ----------------
select * from code where rownum<300000 order by 1 GROUP BY (SORT)
POLICY LAST_MEMORY_USED/1024/1024 LAST_EXECUTION LAST_TEMPSEG_SIZE
-------- ---------------------------- --------------- -------------------
AUTO 2.296875 1 PASS 7340032
|
LAST_MEMORY_USED column indicates that 2.3MB of sort area is used,
which is about 4.6% of the pga_aggregate_target.
(Test 2)
I make forty processes perform sort at a time.
SQL> select count(*),sum(ACTUAL_MEM_USED)/1024/1024 from v$sql_workarea_active;
COUNT(*) SUM(ACTUAL_MEM_USED)/1024/1024
---------- ------------------------------
40 8.51660156
SQL> select count(*),sum(pga_used_mem)/1024/1024 from v$process;
COUNT(*) SUM(PGA_USED_MEM)/1024/1024
---------- ---------------------------
50 37.687932
SQL> select * from code where rownum<300000 order by 1;
299999 rows selected
Elapsed: 00:03:44.83
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 * from code where rownum<300000 order by 1';
SQL_TEXT OPERATION_TYPE
-------------------------------------------------- ----------------
select * from code where rownum<300000 order by 1 GROUP BY (SORT)
POLICY LAST_MEMORY_USED/1024/1024 LAST_EXECUTION LAST_TEMPSEG_SIZE
-------- ---------------------------- --------------- -------------------
AUTO .53125 17 PASSES 8388608
|
LAST_MEMORY_USED column indicates that 544KB of sort area is used, which is
only 1% of the pga_aggregate_target. LAST_EXECUTION column indicates
that there are multiple passes, and the time required for executing the SQL statement
is 2.3 times longer than that of Test1.
This proves that the sort area is allocated evenly to each process.
Even though Automatic PGA Memory Management is active, we still need to handle
database tuning by determining the amount of memory required for sort process,
the number of concurrent processes, and the number of concurrent sort operations.
That's it for today.
Takuya Kishimoto
|
|
