PGA详解

1、PGA作用

  PGA是特定于进程的一段内存，是一个操作系统进程或线程专用的内存，不允许系统中的其它进程或线程访问，独立于SGA是server process的私有空间。

2、PGA构成

1）private SQL area

    私有SQL区域:存储server process执行SQL所需要的私有数据和控制结构，

    包括固定区域和运行时区域。

2）Session Memory

    会话空间:存放logon信息等会话相关的控制信息

3）SQL Work Areas

    SQL工作区:排序操作(order by/group by/distinct/union等)，多表hash连接，位图连接，创建位图

    内存排序、一次IO排序，多次IO排序比例

3、Dedicated Server、Shared Server

  select * from v$session;

4、PGA管理方式

  手动：

  自动：

5、重要参数

PGA_AGGREGATE_TARGET

  “理论上”　pga_aggregate_target参数用来控制instance使用PGA内存的总量，

  instance尽力保持在pga_aggregate_target限制以内，但如果实在无法保证，

  它也不会停处理，只是要求不超过这个阈值。实际上这个pga_aggregate_target

  参数是控制工作区的排序操作(order by/group by/distinct/union等)，

  多表hash连接，位图连接，创建位图。pga_aggregate_target参数不能算是一个硬性限制，

  而更应该算是一个请求！！！

alter system set workarea_size_policy=auto scope=both;

alter system set pga_aggregate_target=200m scope=both;

  WORKAREA_SIZE_POLICY

sort_area_size

sort_area_retained_size

hash_area_size

bitmap_merge_area_size

create_bitmap_area_size

_pga_max_size

select ksppinm "Name", ksppstvl/1024/1024 ||'M' "Value", ksppdesc "Desc"

from x$ksppi x, x$ksppcv y

where x.indx = y.indx and ksppinm ='_pga_max_size';

6:各个系统的PGA空间初始分配情况

在OLTP系统中，典型PGA内存设置应该是总内存的较小部分（例如20％），剩下80％分配给SGA。

OLTP：PGA_AGGREGATE_TARGET = (total_mem * 80%) * 20%

OLAP，由于会运行一些很大的查询，典型的PGA内存最多分配70％的内存。

PGA_AGGREGATE_TARGET = (total_mem * 80%) * 50%

7、PGA相关的SQL语句

V$PGASTA

SELECT * FROM V$PGASTAT;

V$PGA_TARGET_ADVICE

Select pga_target_for_estimate/1024/1024 ||'M' "Estimate PGA Target"

      ,estd_pga_cache_hit_percentage "Cache Hit(%)"

      ,estd_extra_bytes_rw/1024/1024 ||'M' "Extra Read/Write"

      ,estd_overalloc_count "Over alloc count"

From v$pga_target_advice

V$SYSSTAT 

select * from V$SYSSTAT

where name like '%sort%';

V$SQL_WORKAREA

V$SQL_WORKAREA_ACTIVE

V$PROCESS

select spid,program,pga_max_mem,pga_alloc_mem,pga_used_mem,pga_freeable_mem

from v$process

--查看使用到临时表空间的SQL

select a.sid,a.username,a.program,c.sql_text from v$session a ,v$tempseg_usage b, v$sql c

where a.saddr=b.session_addr and a.sql_address=c.address

select * from v$tempseg_usage

v$sql_workarea_histogram

--查看PGA排序信息

SELECT LOW_OPTIMAL_SIZE/1024 low_kb,

      (HIGH_OPTIMAL_SIZE+1)/1024 high_kb,

      OPTIMAL_EXECUTIONS, ONEPASS_EXECUTIONS, MULTIPASSES_EXECUTIONS

  FROM V$SQL_WORKAREA_HISTOGRAM

WHERE TOTAL_EXECUTIONS != 0;

--内存排序、一次IO排序，多次IO排序比例

SELECT optimal_count, round(optimal_count*100/total, 2) optimal_perc,

      onepass_count, round(onepass_count*100/total, 2) onepass_perc,

      multipass_count, round(multipass_count*100/total, 2) multipass_perc

FROM

(SELECT decode(sum(total_executions), 0, 1, sum(total_executions)) total,

        sum(OPTIMAL_EXECUTIONS) optimal_count,

        sum(ONEPASS_EXECUTIONS) onepass_count,

        sum(MULTIPASSES_EXECUTIONS) multipass_count

    FROM v$sql_workarea_histogram

  WHERE low_optimal_size >= 64*1024);

--查看正在执行的情况

SELECT to_number(decode(SID, 65535, NULL, SID)) sid,

      operation_type OPERATION,

      trunc(EXPECTED_SIZE/1024) ESIZE,

      trunc(ACTUAL_MEM_USED/1024) MEM,

      trunc(MAX_MEM_USED/1024) "MAX MEM",

      NUMBER_PASSES PASS,

      trunc(TEMPSEG_SIZE/1024) TSIZE

  FROM V$SQL_WORKAREA_ACTIVE

ORDER BY 1,2;

--查看最消耗PGA的10条语句

SELECT *

FROM  (SELECT workarea_address, operation_type, policy, estimated_optimal_size

        FROM V$SQL_WORKAREA

        ORDER BY estimated_optimal_size DESC)

WHERE ROWNUM <= 10;

--查看哪些语句有使用磁盘排序

SELECT sql_text, sum(ONEPASS_EXECUTIONS) onepass_cnt,

      sum(MULTIPASSES_EXECUTIONS) mpass_cnt

FROM V$SQL s, V$SQL_WORKAREA wa

WHERE s.address = wa.address

GROUP BY sql_text

HAVING sum(ONEPASS_EXECUTIONS+MULTIPASSES_EXECUTIONS)>0;

--查询SQL语句使用work area 情况

1）SELECT sql_text, address, hash_value FROM v$sql

WHERE sql_text like 'SELECT * FROM EMP %';

2）SELECT operation, options, object_name name, trunc(bytes/1024/1024) "input(MB)",

      trunc(last_memory_used/1024) last_mem,

      trunc(estimated_optimal_size/1024) optimal_mem,

      trunc(estimated_onepass_size/1024) onepass_mem,

      decode(optimal_executions, null, null,

              optimal_executions||'/'||onepass_executions||'/'||

              multipasses_executions) "O/1/M"

  FROM V$SQL_PLAN p, V$SQL_WORKAREA w

WHERE p.address=w.address(+)

  AND p.hash_value=w.hash_value(+)

  AND p.id=w.operation_id(+)

--  AND p.address='88BB460C'

--  AND p.hash_value=3738161960;

--查询整个数据库内存排序、磁盘排序的情况

SELECT name profile, cnt, decode(total, 0, 0, round(cnt*100/total)) percentage

    FROM (SELECT name, value cnt, (sum(value) over ()) total

    FROM V$SYSSTAT

    WHERE name like 'workarea exec%');

--查看当前会话PGA的情况

select name,value from v$statname a,v$mystat b where a.statistic#=b.statistic# and a.name like '%pga%';

set linesize 200

alter session set statistics_level=all;

select  count(1)

from join_01_objects t1,join_05_objects t2

  where t1.object_id=t2.object_id  order by t1.object_id;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

set autotrace traceonly 

set linesize 200 

select  *

from join_01_objects t1,join_05_objects t2

  where t1.object_id=t2.object_id  order by t1.object_id;