ITPub博客

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

SHARED POOL (3)

原创 IT综合 作者:zhyuh 时间:2000-01-01 00:00:16 0 删除 编辑
察看某个用户的各sql对shared pool的使用情况[@more@]

rem
rem FUNCTION: Generate a report of SQL Area Memory Usage
rem showing SQL Text and memory catagories
rem
rem sqlmem.sql
rem
column sql_text format a30 heading Text word_wrapped
column sharable_mem heading Shared|Bytes
column persistent_mem heading Persistent|Bytes
column runtime_mem heading Runtime|Bytes
column loads heading Loads
column users format a15 heading 'User'
column executions heading 'Executions'
column users_executing heading 'Used By'
start title132 'Users SQL Area Memory Use'
--spool rep_out&dbsqlmem
set long 2000 pages 59 lines 132
break on users
compute sum of sharable_mem on users
compute sum of persistent_mem on users
compute sum of runtime_mem on users
select
username users, sql_text, Executions, loads, users_executing,
sharable_mem, persistent_mem
from
sys.v_$sqlarea a, dba_users b
where
a.parsing_user_id = b.user_id
and b.username like upper('%&user_name%')
order by 3 desc,1;
--spool off
pause Press enter to continue
clear columns
clear computes
clear breaks
set pages 22 lines 80


以UNIVERSE40用户为例,执行结果为:

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

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