ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [笔记]Oracle9i Monitoring Automated SQL Execution Memory Management

[笔记]Oracle9i Monitoring Automated SQL Execution Memory Management

原创 Linux操作系统 作者:husthxd 时间:2004-11-08 00:00:00 0 删除 编辑

监控9i自动管理PGA的一些脚本。


0.The following query returns a percentage of work areas used with optimal memory size.

select

trunc (

       (sum(case when name like 'workarea executions - optimal' 

                                       then value else 0 end) *100) /

       (

                  sum(case when name like 'workarea executions - optimal'   

then value else 0 end) +

                  sum(case when name like 'workarea executions - one pass'  

                                             then value else 0 end)         +

                  sum(case when name like 'workarea executions - multipass' 

                                             then value else 0 end)

        )

        ) optimal_percent

from v$sysstat

where name like 'workarea executions - %'

/

1. Finding top ten work areas requiring the most cache memory:

select * 

from

(select workarea_address, operation_type, policy, estimated_optimal_size

  from v$sql_workarea

order by estimated_optimal_size DESC)

where ROWNUM <=10;

2. Finding the percentage of work areas using maximum memory:

select operation_type, total_executions * 100  / optimal_executions "%cache"

From v$sql_workarea

Where policy='AUTO'

And optimal_executions > 0

Order By operation_type;

3. Finding the top ten biggest work areas currently allocated in the system:

select c.sql_text, w.operation_type, top_ten.wasize

From (Select *

      From (Select workarea_address, actual_mem_used wasize

            from v$sql_workarea_active

            Order by actual_mem_used)

      Where ROWNUM <=10) top_ten,

      v$sql_workarea w,

      v$sql c

Where    w.workarea_address=top_ten.workarea_address

        And c.address=w.address

        And c.child_number = w.child_number

        And c.hash_value=w.hash_value;          

4. Finding the percentage of memory that is over and under allocated:

select  total_used,

        under*100/(total_used+1) percent_under_use,

        over*100/(total_used+1)   percent_over_used

From

        ( Select

                sum(case when expected_size > actual_mem_used 

                                       then actual_mem_used else 0 end) under,

                sum(case when expected_size<> actual_mem_used 

                                       then actual_mem_used else 0 end) over,

                sum(actual_mem_used) total_used

        From v$sql_workarea_active

        Where policy='AUTO') usage; 

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

上一篇: 系统上线
请登录后发表评论 登录
全部评论
ITPUB数据库版块资深版主,对Oracle、PostgreSQL有深入研究。现就职于广州云图数据技术有限公司,系统架构师。

注册时间:2007-12-28

  • 博文量
    1393
  • 访问量
    3839839