ITPub博客

首页 > 应用开发 > IT综合 > SHARED POOL (4)

SHARED POOL (4)

原创 IT综合 作者:zhyuh 时间:2006-02-08 14:10:31 0 删除 编辑
在shared pool中,确定每个用户的Good SQL和Bad SQL所占的内存比例。[@more@]

1. 先建立区分Good和Garbage SQL的视图

运行一次以上的sql定义为Good
只运行一次的定义为Garbage
根据业务特点,可以调整Good SQL和Bad SQL的区分标准,即调整标准的运行次数a.executions

REM
REM View to sort SQL into GOOD and GARBAGE
REM
CREATE OR REPLACE VIEW sql_garbage AS
SELECT
  b.username users,
  SUM(a.sharable_mem+a.persistent_mem) Garbage,
  TO_NUMBER(null) good
FROM
  sys.v_$sqlarea a, dba_users b
WHERE
  (a.parsing_user_id = b.user_id and a.executions<=1)
GROUP BY b.username
UNION
SELECT DISTINCT
  b.username users,
  TO_NUMBER(null) garbage,
  SUM(c.sharable_mem+c.persistent_mem) Good
FROM
  dba_users b, sys.v_$sqlarea c
WHERE
  (b.user_id=c.parsing_user_id and c.executions>1)
GROUP BY b.username;

2. 察看每个用户的good sql和garbage sql的比例

REM
REM Report on SQL Area Reuse by user
REM
column garbage format 9,999,999,999 heading 'Non-Shared SQL'
column good format 9,999,999,999 heading 'Shared SQL'
column good_percent format 999.99 heading 'Percent Shared'
set feedback off
break on report
compute sum of garbage on report
compute sum of good on report
compute avg of good_percent on report
@title80 'Shared Pool Utilization'
--spool rep_out&dbsql_garbage
select
  a.users,
  a.garbage,
  b.good,
  (b.good/(b.good+a.garbage))*100 good_percent
from
  sql_garbage a, sql_garbage b
where a.users=b.users
  and a.garbage is not null
  and b.good is not null
/
--spool off
set feedback off
clear columns
clear breaks
clear computes

执行结果如下:

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

上一篇: SHARED POOL (3)
下一篇: SHARED POOL (5)
请登录后发表评论 登录
全部评论
  • 博文量
    233
  • 访问量
    2012707