Manual PGA Memory Management
Scripts:
create table t as select * from all_objects;
exec dbms_stats.gather_table_stats(user,'T');
run_query.sql
reset_stat.sql
watch_stat.sql
create table t as select * from all_objects;
run_query.sql
点击(此处)折叠或打开
- connect /
-
set serveroutput off
-
set echo on
-
column sid new_val SID
-
select sid from v$mystat where rownum=1;
-
alter session set workarea_size_policy=manual ;
-
alter session set sort_area_size= &1;
-
prompt run @reset_stat &SID and @watch_stat in another session
-
pause
-
set termout off
-
select * from t order by 1,2,3,4;
-
set termout on
-
prompt run @watch_stat in another session
- pause
点击(此处)折叠或打开
-
drop table sess_stats;
-
create table sess_stats
-
(
-
name varchar2(64),
-
value number,
-
diff number
-
);
-
-
variable sid number
- exec :sid := &1
点击(此处)折叠或打开
-
merge into sess_stats
-
using
-
(
-
select a.name ,b.value
-
from v$statname a, v$sesstat b
-
where a.statistic#=b.statistic#
-
and b.sid= :sid
-
and (a.name like '%ga%'
-
or a.name like '%direct temp%')
-
) curr_stats
-
on (sess_stats.name=curr_stats.name)
-
when matched then
-
update set diff=curr_stats.value-sess_stats.value,value=curr_stats.value
-
when not matched then
-
insert (name,value,diff)
-
values(curr_stats.name,curr_stats.value,null)
-
/
-
select name,case when name like '%ga%'
-
then round(value/1024,0)
-
else value
-
end kbytes_writes,
-
case when name like '%ga%'
-
then round(diff/1024,0)
-
else value
-
end diff_kbytes_writes
-
from sess_stats
- order by name;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31349667/viewspace-2123818/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
