|
Sort
|
In this issue, I will continue to talk about pga_aggregate_target.
Last time, I set pga_aggregate_target as follows and found out that
5% of the pga_aggragate_target was actually allocated to a single process
as memory area.
(Test environment)
OS:Red Hat Linux release7.1
Oracle:9.2.0.1
SQL> select name,value/1024/1024/1024 from v$parameter
where name='pga_aggregate_target';
NAME VALUE/1024/1024/1024
----------------------------------- --------------------
pga_aggregate_target 4095
SQL> select distinct * from code;
5422055 rows selected
Elapsed: 00:08:49.21
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'
SQL_TEXT OPERATION_TYPE POLICY
---------------------------- ----------------- --------
select distinct * from code GROUP BY (SORT) AUTO
LAST_MEMORY_USED/1024/1024 LAST_EXECUTION LAST_TEMPSEG_SIZE
---------------------------- ---------------- -------------------
87.265625 1 PASS 132120576
|
The result must be wrong or I may have told you a lie. I just told you that
a single process consumed the memory for an amount of the pga_aggregate_target*5%.
Here, the result is supposed to be pga_aggregate_target=4095G*5%=204.75G, but
LAST_MEMORY_USED column indicates that only 87MB of memory is allocated.
Also, LAST_EXECUTION column indicates that the disk sort has occurred.
There must be some hidden threshold in Oracle.
SQL> select i.ksppinm name , v.ksppstvl value
from x$ksppi i, x$ksppcv v
where i.indx = v.indx
and i.ksppinm like '_pga%';
NAME VALUE
-------------------- --------------------
_pga_max_size 209715200
|
_pga_max_size is a key to solve the problem.
I set the _pga_max_size to the value ten times larger than the original.
SQL> alter system set "_pga_max_size"=2G;
System altered
|
I execute the same SQL statement again.
SQL> select distinct * from code;
5422055 rows selected
Elapsed: 00:07:22.42
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';
SQL_TEXT OPERATION_TYPE POLICY
---------------------------- ----------------- --------
select distinct * from code GROUP BY (SORT) AUTO
LAST_MEMORY_USED/1024/1024 LAST_EXECUTION LAST_TEMPSEG_SIZE
---------------------------- ---------------- -------------------
189.33593 OPTIMAL
|
This time, larger size of memory is allocated when compared to _pga_max_size=209715200
(LAST_MEMORY_USED=189MB), and the sort process is handled within memory area (LAST_EXECUTION=OPTIMAL).
Next, I set _pga_max_size to a smaller value (100MB).
SQL> alter system set "_pga_max_size"=100M;
System altered
SQL> select distinct * from code;
5422055 rows selected
Elapsed: 00:08:07.72
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';
SQL_TEXT OPERATION_TYPE POLICY
---------------------------- ----------------- --------
select distinct * from code GROUP BY (SORT) AUTO
LAST_MEMORY_USED/1024/1024 LAST_EXECUTION LAST_TEMPSEG_SIZE
---------------------------- ---------------- -------------------
49.2421875 1 PASS 132120576
|
LAST_MEMORY_USED (about 49MB) is a half of _pga_max_size (100MB).
In Automatic PGA Memory Management, a single process can use the pga_aggregate_target*5%
or more than half of _pga_max_size of memory sort area.
That's it for today.
Takuya Kishimoto
|
|
