* Image from here
- SORT_AREA_SIZE: Use by sort operation
- HASH_AREA_SIZE: Use by hash join operation
- CREATE_BITMAP_AREA_SIZE: use by building bitmap index. Larger value would help speed up create the bitmap index.
- MERGE_BITMAP_AREA_SIZE: use by merge comparison operation. Larger value would help speed up the merge operation.
* Image from here
- Session Memory: User information, such as username, login time.
- Persistent Area: store bind variables.
- Runtime area: store Query execution state
Show parameter pga_aggregate_target
show parameter area_size
Show the current session pga/uga usage
a.name as "NAME",
b.value as "VALUE"
v$statname a, v$sesstat b
a.name LIKE 'session%ga memory%%'
OR a.name LIKE '%direct temp%'
Below example show when the sort area size is 64K (65536 byte), the disk sort count is 1.
-- Create test table
create table testpo as select * from dba_objects;
-- Collect statistic on the table
-- Enable the trace
set autotrace traceonly statistic;
select * from testpo order by 1;
After increase the sort area size to 128K, the disk count is still 1.
alter session set sort_area_size = 131072;
Once I increase the sort_area_size to 12800K, the disk sort become 0. The sort now is all happen in the memory. This also means if we have a lot of process need the sort operation, we have to allocate a lot of memory to each session for the sort area.
alter session set sort_area_size = 13107200;
After 9i, Oracle add the PGA_AGGREGATE_TARGET to automatically manage the PGA memory instead it is fixed for the entire PGA. Every operation can use 5% of PGA_AGGREGATE_TARGET. In parallel operation, can use up to 30% memory. Once the process complete, the memory get release back to the pool for next process. So the same session start the next operation, it will need to request the memory again.
alter system set PGA_AGGREGATE_TARGET=150M;
PGA_AGGREGATE_TARGET is dynamic parameter. we don’t have to bounce the instance. Once the PGA_AGGREGATE_TARGET set greater than 0. he WORKAREA_SIZE_POLICY change to auto automatically .
However, if you want to turn off the PGA_AGGREGATE_TARGET, we have to bounce the instance and set to spfile only. Otherwise, we would get the ORA-00093 error.
After we turn on PGA_AGGREGATE_TARGET, the same query we execute before has 0 disk sort . The SORT_AREA_SIZE,SORT_AREA_SIZE,CREATE_BITMAP_AREA_SIZE and MERGE_BITMAP_AREA_SIZE are not effective because of PGA_AGGREGATE_TARGET is enable.. The PGA_AGGREGATE_TARGET would automatically adjust the memory.
Show the current PGA stat.
select * from V$PGASTAT
V$PROCESS contain the current active process.
Below query show the current active process and PGA memory usage
select pid,spid,program,PGA_USED_MEM,PGA_ALLOC_MEM,PGA_MAX_MEM,addr from v$process
Below query list all the user session that use PGA memory more than 1MB
statistics_level needs to be TYPICAL or ALL in order to use V$PGA_TARGET_ADVICE.
select * from v$pga_target_advice
This view shows what advice would be turn on by statistics_level .
select sl.statistics_name,sl.session_status,sl.system_status,sl.activation_level,sl.session_settable from v$statistics_level sl;
Below query show the sorrt area status
select * from V$sysstat where name like '%sort%'