Pre 9i
* 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
SELECT
a.name as "NAME",
b.value as "VALUE"
FROM
v$statname a, v$sesstat b
WHERE
a.statistic#=b.statistic#
AND
(
a.name LIKE 'session%ga memory%%'
OR a.name LIKE '%direct temp%'
)
AND sid=
(
SELECT DISTINCT
sid
FROM
v$mystat
);
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
execute DBMS_STATS.GATHER_TABLE_STATS('SYS','testpo');
-- 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;
PGA_AGGREGATE_TARGET
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.
V$PGASTAT
Show the current PGA stat.
select * from V$PGASTAT
V$PROCESS
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
SELECT
b.pid,
b.spid ,
a.sid,
a.osuser,
a.username,
a.machine,
a.process,
a.logon_time,
a.program,
b.PGA_USED_MEM,
b.PGA_ALLOC_MEM
FROM
V$SESSION a,
V$PROCESS B
WHERE
a.PADDR = B.ADDR
AND B.PGA_USED_MEM/1024/1024 > 1
ORDER BY
b.PGA_USED_MEM
V$PGA_TARGET_ADVICE
statistics_level needs to be TYPICAL or ALL in order to use V$PGA_TARGET_ADVICE.
select * from v$pga_target_advice
V$STATISTICS_LEVEL
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;
V$SYSSTAT
Below query show the sorrt area status
select * from V$sysstat where name like '%sort%'
Reference
docs.oracle.com/cd/E25054_01/server.1111/e25789/memory.htm#autoId4
ReplyDeleteMuch obliged much for your post, it impacts us to have a consistently expanding number of circles for the duration of our life, and so kind for you, I in like manner believe you will make progressively incredible post and permits progressively talk, much obliged, dear.
Look at this web-site : Custom essay writing service
I like reading your blog
ReplyDeletewez-pozyczke
Great Article. Thank you for sharing! Really an awesome post for every one.
ReplyDeleteProject Centers in Chennai
JavaScript Training in Chennai
Final Year Project Domains for IT
JavaScript Training in Chennai
Rolex offers a wide replica watches range of models ranging from professional to classic watches to suit any wrist. replica rolex watches Explore the Rolex collection by selecting your favourite models, replica rolex air king watches materials, bezels, dials and bracelets to find the watch that was made for you.
ReplyDelete