pga相关视图和脚本

V$PGASTAT

V$PGASTAT displays PGA memory usage statistics as well as statistics about the automatic PGA memory manager when it is enabled (that is, when PGA_AGGREGATE_TARGET is set). Cumulative values in V$PGASTAT are accumulated since instance startup.

这样查询显示比较方便，并且因为有的value值非常大，如果直接查询显示不全，要处理后显示才能显示正常结果

select name,  

        to_char(decode(unit, 'bytes', value / 1024 / 1024, value),  

                                                            '999,999,999.9') value,  

        decode(unit, 'bytes', 'mbytes', unit) unit  

  from V$PGASTAT;  

  

  

V$PGA_TARGET_ADVICE

V$PGA_TARGET_ADVICE predicts how the cache hit percentage and over allocation count statistics displayed by the V$PGASTAT performance view would be impacted if the value of the PGA_AGGREGATE_TARGET parameter is changed. The prediction is performed for various values of the PGA_AGGREGATE_TARGET parameter, selected around its current value. The advice statistic is generated by simulating the past workload run by the instance.

The content of the view is empty if PGA_AGGREGATE_TARGET is not set. In addition, the content of this view is not updated if the STATISTICS_LEVEL parameter is set to BASIC. Base statistics for this view are reset at instance startup and when the value of the PGA_AGGREGATE_TARGET initialization parameter is dynamically modified.

V$SQL_WORKAREA

V$SQL_WORKAREA displays information about work areas used by SQL cursors. Each SQL statement stored in the shared pool has one or more child cursors that are listed in the V$SQL view. V$SQL_WORKAREA lists all work areas needed by these child cursors; V$SQL_WORKAREA can be joined with V$SQLAREA on (ADDRESS, HASH_VALUE) and with V$SQL on (ADDRESS, HASH_VALUE, CHILD_NUMBER).

You can use this view to find out answers to the following questions:

What are the top 10 work areas that require the most cache area?

For work areas allocated in AUTO mode, what percentage of work areas are running using maximum memory?

SELECT *    

  FROM (SELECT address,workarea_address,   

                    operation_type,   

                    policy,   

                    estimated_optimal_size/1024/1024   

            FROM V$SQL_WORKAREA   

           ORDER BY estimated_optimal_size desc)   

 WHERE ROWNUM <= 10; 

 

 和v$sql利用address字段相结合就能查到相应的sql语句

 

 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;  

V$SQL_WORKAREA_HISTOGRAM

V$SQL_WORKAREA_HISTOGRAM displays the cumulative work area execution statistics (cumulated since instance startup) for different work area groups. The work areas are split into 33 groups based on their optimal memory requirements with the requirements increasing in powers of two. That is, work areas whose optimal requirement varies from 0 KB to 1 KB, 1 KB to 2 KB, 2 KB to 4 KB, ... and 2 TB to 4 TB.

For each work area group, the V$SQL_WORKAREA_HISTOGRAM view shows how many work areas in that group were able to run in optimal mode, how many were able to run in one-pass mode, and finally how many ran in multi-pass mode. The DBA can take a snapshot at the beginning and the end of a desired time interval to derive the same statistics for that interval.

V$SQL_WORKAREA_ACTIVE

V$SQL_WORKAREA_ACTIVE contains an instantaneous view of the work areas currently allocated by the system. You can join this view against V$SQL_WORKAREA on WORKAREA_ADDRESS to access the definition of that work area. If a work area spills to disk, then this view contains information for the temporary segment created on behalf of this work area.

The last three columns are included to enable joining V$SQL_WORKAREA_ACTIVE with V$TEMPSEG_USAGE to retrieve more information on this temporary segment.

You can use this view to answer the following:

What are the top 10 largest work areas currently allocated in the system?

What percentage of memory is over-allocated (EXPECTED_SIZE < ACTUAL_MEM_USED) and under-allocated (EXPECTED_SIZE > ACTUAL_MEM_USED)?

What are the active work areas using more memory than what is expected by the memory manager?

What are the active work areas that have spilled to disk?

v$sysstat

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%'); 

          

          select * from V$SYSSTAT where name like '%sort%'; 

          

          总的uga大小

          SELECT ROUND(SUM(value)/1024/1024) || ' MB' "Total session memory"   

  FROM v$sesstat, v$statname   

WHERE name = 'session uga memory'   

   AND v$sesstat.statistic# = v$statname.statistic#;

   

   uga曾经最大使用到多大

   SELECT ROUND(SUM(value)/1024/1024) || ' MB' "Total max memory"   

  FROM v$sesstat, v$statname   

WHERE name = 'session uga memory max'   

   AND v$sesstat.statistic# = v$statname.statistic#;

   

   结合 V$SESSION，查询每个会话的UGA使用了多少空间：

   SELECT a.sid,   

        c.username,   

        ROUND((a.value / 1024 / 1024), 2) || ' MB' "Session Memory"   

  FROM v$sesstat a, v$statname b, v$session c   

 WHERE b.name = 'session uga memory'   

   AND a.sid = c.sid   

   AND a.statistic# = b.statistic#   

   AND c.username not like 'SYS%'   

 ORDER BY "Session Memory";   

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28952551/viewspace-767054/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28952551/viewspace-767054/