Automatic PGA Memory Management
1.sql
2.SQL
3.sql
4.sql
5.sql
6.sql
7.sql
点击(此处)折叠或打开
-
create table sess_stats
-
as
-
select name,value, 0 active from
-
(
-
select a.name,b.value
-
from v$statname a ,v$sesstat b
-
where a.statistic#=b.statistic#
-
and b.sid=(select sid from v$mystat
-
where rownum=1)
-
and (a.name like '%ga% '
-
or a.name like '%direct temp%')
-
union all
-
select 'total: '||a.name,sum(b.value)
-
from v$statname a,v$sesstat b,v$session c
-
where a.statistic#=b.statistic#
-
and (a.name like '%ga%'
-
or a.name like '%direct temp%')
-
and b.sid=c.sid
-
and c.username is not null
- group by 'total: '||a.name
- );
点击(此处)折叠或打开
-
set echo on
-
declare
-
l_first_time boolean default true;
-
begin
-
for x in (select * from t order by 1,2,3,4)
-
loop
-
if(l_first_time)
-
then
-
insert into sess_stats(name,value,active)
-
select name,value,
-
(select count(*) from v$session
-
where status='ACTIVE'
-
and username is not null)
-
from
-
(
-
select a.name,b.value
-
from v$statname a,v$sesstat b
-
where a.statistic#=b.statistic#
-
and b.sid=
-
(select sid from v$mystat
-
where rownum=1)
-
and (a.name like '%ga%'
-
or a.name like '%direct temp%')
-
union all
-
select 'total: '||a.name,sum(b.value)
-
from v$statname a,v$sesstat b ,
-
v$session c
-
where a.statistic#=b.statistic#
-
and (a.name like '%ga%'
-
or a.name like '%direct temp%')
-
and b.sid=c.sid
-
and c.username is not null
-
group by 'total: '||a.name
-
);
-
l_first_time:=false;
-
end if;
-
end loop;
-
end;
-
/
- commit;
点击(此处)折叠或打开
-
declare
-
l_msg long;
-
l_status number;
-
begin
-
dbms_alert.register('WAITING');
-
for i in 1..999999 loop
-
dbms_application_info.set_client_info(i);
-
dbms_alert.waitone('WAITING',l_msg,l_status,0);
-
exit when l_status=0;
-
for x in(select * from t order by 1,2,3,4)
-
loop
-
null;
-
end loop;
-
end loop;
- end;
点击(此处)折叠或打开
-
begin
-
dbms_alert.signal('WAITING','');
-
commit;
- end;
点击(此处)折叠或打开
-
create or replace package demo_pkg
-
as
-
type array is table of char(2000)
-
index by binary_integer;
-
g_data array;
- end;
点击(此处)折叠或打开
-
select a.name,to_char(b.value,'999,999,999')
-
bytes,to_char(round(b.value/1024/1024,1),'99,999.9') mbytes from v$statname a,v$mystat b
-
where a.statistic#=b.statistic#
- and a.name like '%ga memory%';
点击(此处)折叠或打开
-
begin
-
for i in 1..200000
-
loop
-
demo_pkg.g_data(i):='x';
-
end loop;
- end;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31349667/viewspace-2124032/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
