Search This Blog

Total Pageviews

Tuesday, 25 October 2011

Oracle PGA report

Oracle PGA REPORT  ... 




pga report

PGA Diagnostics

from web http://oracle-cookies.blogspot.com/2007/08/pga-diagnostics.html
-- PGA Report
-- Displays various statistics regarding the PGA usage

set linesize 300 pagesize 400 heading off  feedback off

-- general statistics
select 'SECTION 1: GENERAL STATISTICS FOR THE PGA' from dual;
select '==================================================================' || '=========================' from dual;

set heading on
select name,
       decode(unit, 'bytes', round(value / 1024 / 1024, 2), value) value,
       decode(unit, 'bytes', 'MB', '') unit
  from v$pgastat;

set heading off
select 'Check the following:' from dual;
select '  - "aggregate PGA auto target" should not be too small in comparison '  || 'with the "aggregate PGA target parameter"' from dual;
select '  - "global memory bound" should not be lower than 1M' from dual;
select '  - "over allocation count" should be near 0 or should not increasing in time' from dual;
select '  - the bigger "cache hit percentage", the better' from dual;
select '' from dual;

set heading on
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%');
set heading off

-- PGA used by all DB processes
select 'SECTION 2: PGA USED BY CURRENT DB PROCESSES (IN MB), ORDER BY PGA_ALLOC_MEM' from dual;
select '==================================================================' || '=========================' from dual;
set heading on

break on report;
compute sum label '--> TOTAL' of pga_used_mem on report;
compute sum label '--> TOTAL' of pga_alloc_mem on report;
compute sum label '--> TOTAL' of pga_freeable_mem on report;
compute sum label '--> TOTAL' of pga_max_mem on report;

SELECT PROGRAM,
       round(PGA_USED_MEM / 1024 / 1024, 2)  pga_used_mem,
       round(PGA_ALLOC_MEM / 1024 / 1024, 2)  pga_alloc_mem,
       round(PGA_FREEABLE_MEM / 1024 / 1024, 2) pga_freeable_mem,
       round(PGA_MAX_MEM / 1024 / 1024, 2)  pga_max_mem
  FROM V$PROCESS
 order by pga_alloc_mem desc;


set heading off
select 'The columns have the following meaning:' from dual;
select '  - PGA_USED_MEM = PGA memory currently used by the process' from dual;
select '  - PGA_ALLOC_MEM = PGA memory currently allocated by the process (including free '   || 'PGA memory not yet released to the operating system by the server process)' from dual;
select '  - PGA_FREEABLE_MEM = Allocated PGA memory which can be freed' from dual;
select '  - PGA_MAX_MEM = Maximum PGA memory ever allocated by the process' from dual;
select '' from dual;

set feedback off;
select 'SECTION 3: USED PGA MEMORY BY CATHEGORIES (VALUES IN MB ORDER DESC BY ALLOCATED_MB).' from dual;
select '=================================================================='  || '=========================' from dual;
set heading on
select category,
       round(sum(allocated) / 1024 / 1024, 2) allocated_mb,
       round(sum(used) / 1024 / 1024, 2) used_mb,
       round(sum(max_allocated) / 1024 / 1024, 2) max_allocated_mb
  from v$process_memory
 group by category
 order by 2 desc;

set heading off
select '' from dual;

-- workareas histogram
select 'SECTION 4: SQL WORKAREAS HISTOGRAM' from dual;
select '==================================================================' || '=========================' from dual;
set heading on
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;

set heading off
select '' from dual;

-- active workareas
select 'SECTION 5: CURRENTLY ACTIVE WORKAREAS' from dual;
select '==================================================================' || '=========================' from dual;
set heading on  feedback on
col OPERATION for a20
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/1024) TSIZE_MB
  FROM V$SQL_WORKAREA_ACTIVE
 ORDER BY 1,2;

set heading off feedback off
select 'The meaning of the above columns is:' from dual;
select '  - SID = the active session identifier' from dual;
select '  - OPERATION = the type of the operation' from dual;
select '  - ESIZE = the expected size for the sql workarea' from dual;
select '  - MEM = Amount of PGA memory (in KB) currently allocated on behalf of this work area.' from dual;
select '  - MAX MEM = Maximum memory amount (in KB) used by this work area' from dual;
select '  - PASS = Number of passes corresponding to this work area (0 if running in OPTIMAL mode)' from dual;
select '  - TSIZE_MB = Size (in megabytes) of the temporary segment used on behalf of this work area. '  || 'This column is NULL if this work area has not (yet) spilled to disk.' from dual;
select '' from dual;

-- top 10 sql with gurmand sql areas
select 'SECTION 6: OP 10 WORK AREAS REQUIRING MOST CACHE MEMORY' from dual;
select '==================================================================' || '=========================' from dual;
set heading on linesize 200
col sql_text        for a50
col operation_type  for a20
SELECT *
  FROM (SELECT distinct s.sql_id,
                        operation_type,
                        estimated_optimal_size,
                        max_tempseg_size,
                        substr(s.SQL_TEXT, 1, 50) sql_text
          FROM V$SQL_WORKAREA a, V$SQLSTATS s
         WHERE a.SQL_ID = s.SQL_ID
         ORDER BY estimated_optimal_size)
 WHERE ROWNUM <= 10;

set heading off pagesize 300
select 'SECTION 7: SQLs WITH WORK AREAS THAT HAVE BEEN EXECUTED IN ONE OR EVEN MULTIPLE PASSES' from dual;
select '==================================================================' || '=========================' from dual;
set heading on  feedback on
col SQL_TEXT for a100
SELECT s.sql_id,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 s.sql_id,sql_text
HAVING sum(ONEPASS_EXECUTIONS+MULTIPASSES_EXECUTIONS)>0
order by 3;

set feedback off heading off

select 'SECTION 8: PGA TARGET ADVCE' from dual;
select '==================================================================' || '=========================' from dual;
show parameter pga_aggregate_target
set heading on
SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
       ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
       ESTD_OVERALLOC_COUNT
  FROM V$PGA_TARGET_ADVICE;

prompt PGA_TARGET_ADVICE
set linesize 300 pagesize 300
col PGA_TARGET_FOR_ESTIMATE   for 999,999,999,990  heading 'PGA_TARGET'
col PGA_TARGET_FACTOR    for 99.0   heading 'Factor'
col BYTES_PROCESSED    for 999,999,999,999,990 heading 'Bytes Processed'
col ESTD_EXTRA_BYTES_RW   for 999,999,999,999,990 heading 'Est. Bytes|r/w'
col ESTD_PGA_CACHE_HIT_PERCENTAGE  for 9,990   heading 'Est. PGA|hit rate'
col ESTD_OVERALLOC_COUNT   for 999,990   heading 'Est.|overalloc'
select PGA_TARGET_FOR_ESTIMATE
, PGA_TARGET_FACTOR
, BYTES_PROCESSED
, ESTD_EXTRA_BYTES_RW
, ESTD_PGA_CACHE_HIT_PERCENTAGE 
, ESTD_OVERALLOC_COUNT
from v$pga_target_advice
order by PGA_TARGET_FACTOR
/



set linesize 300 pagesize 300
col "O/1/M" format a10
col name format a20
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.sql_id='&sql_id'
/



COL mem_component HEAD COMPONENT FOR A35

SELECT
    component mem_component
  , ROUND(current_size/1048576) cur_mb
  , ROUND(min_size/1048576)     min_mb
  , ROUND(max_size/1048576)     max_mb
  , ROUND(user_specified_size/1048576)    spec_mb
  , oper_count
  , last_oper_type last_optype
  , last_oper_mode last_opmode
  , last_oper_time last_optime
  , granule_size/1048576        gran_mb
FROM
    v$sga_dynamic_components
/





set lines 200 pages 100
col parameter          format a50
col description        format a80 word_wrapped
col "session value"    format a10
col "instance value"   format a10
select a.ksppinm       "parameter",
a.ksppdesc             "description",
b.ksppstvl             "session value",
c.ksppstvl             "instance value"
from x$ksppi a,x$ksppcv b,x$ksppsv c
where a.indx = b.indx
and a.indx = c.indx
and a.ksppinm like '/_pga%' escape '/'
order by a.ksppinm;


parameter                                          description                                                                      session va instance v
-------------------------------------------------- -------------------------------------------------------------------------------- ---------- ----------
_pga_aggregate_xmem_limit                          limit of aggregate PGA XMEM memory consumed by the instance                      0          0
_pga_auto_snapshot_percentage                      percent growth of PGA memory for additional snapshots                            20         20
_pga_auto_snapshot_threshold                       bytes of PGA memory in one process to trigger detail snapshot                    524288000  524288000
_pga_in_sga_param1                                 pga in sga param1
_pga_in_sga_param2                                 pga in sga param2
_pga_in_sga_param3                                 pga in sga param4
_pga_in_sga_param4                                 pga in sga param4
_pga_in_sga_param5                                 pga in sga param4
_pga_large_extent_size                             PGA large extent size                                                            1048576    1048576
_pga_limit_check_wait_time                         microseconds to wait for over limit confirmation                                 1000000    1000000
_pga_limit_dump_summary                            dump PGA summary when signalling ORA-4036                                        TRUE       TRUE
_pga_limit_interrupt_smaller                       whether to interrupt smaller eligible processes                                  FALSE      FALSE
_pga_limit_min_req_size                            bytes of PGA usage below which process will not get ORA-4036                     4194304    4194304
_pga_limit_per_process_minimum                     pga_aggregate_limit per-process minimum                                          3145728    3145728
_pga_limit_physmem_perc                            default percent of physical memory for pga_aggregate_limit and SGA               90         90
_pga_limit_simulated_physmem_size                  bytes of physical memory to determine pga_aggregate_limit with                   0          0
_pga_limit_target_perc                             default percent of pga_aggregate_target for pga_aggregate_limit                  200        200
_pga_limit_time_to_interrupt                       seconds to wait until direct interrupt                                           2          2
_pga_limit_time_until_idle                         seconds to wait before treating process as idle                                  15         15
_pga_limit_time_until_killed                       seconds to wait before killing session over limit                                30         30
_pga_limit_tracing                                 trace pga_aggregate_limit activity                                               0          0
_pga_limit_use_immediate_kill                      use immediate kill for sessions over limit                                       TRUE       TRUE
_pga_limit_watch_perc                              percentage of limit to have processes watch                                      50         50
_pga_limit_watch_size                              bytes of PGA usage at which process will begin watching limit                    104857600  104857600
_pga_max_size                                      Maximum size of the PGA memory for one process                                   209715200  209715200



No comments:

Oracle DBA

anuj blog Archive