ITPub博客

首页 > Linux操作系统 > Linux操作系统 > pga相关视图和脚本

pga相关视图和脚本

原创 Linux操作系统 作者:juve_pavel 时间:2013-07-25 08:58:12 0 删除 编辑
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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: LMT ASSM
下一篇: 批量insert测试
请登录后发表评论 登录
全部评论

注册时间:2013-06-06

  • 博文量
    7
  • 访问量
    12964