Automatic PGA Memory Management

1.sql

点击(此处)折叠或打开

1. create table sess_stats
   
2. as
   
3. select name,value, 0 active from
   
4. (
   
5.  select a.name,b.value
   
6.    from v$statname a ,v$sesstat b
   
7.    where a.statistic#=b.statistic#
   
8.      and b.sid=(select sid from v$mystat
   
9.                   where rownum=1)
   
10.      and (a.name like '%ga% '
    
11.           or a.name like '%direct temp%')
    
12.  union all
    
13.   select 'total: '||a.name,sum(b.value)
    
14.       from v$statname a,v$sesstat b,v$session c
    
15.       where a.statistic#=b.statistic#
    
16.         and (a.name like '%ga%'
    
17.              or a.name like '%direct temp%')
    
18.         and b.sid=c.sid
    
19.         and c.username is not null
    
20.       group by 'total: '||a.name
21.  );

2.SQL
 

点击(此处)折叠或打开

1. set echo on
   
2. declare
   
3.   l_first_time boolean default true;
   
4. begin
   
5.   for x in (select * from t order by 1,2,3,4)
   
6.   loop
   
7.     if(l_first_time)
   
8.     then
   
9.       insert into sess_stats(name,value,active)
   
10.       select name,value,
    
11.         (select count(*) from v$session
    
12.            where status='ACTIVE'
    
13.              and username is not null)
    
14.       from
    
15.       (
    
16.         select a.name,b.value
    
17.          from v$statname a,v$sesstat b
    
18.          where a.statistic#=b.statistic#
    
19.           and b.sid=
    
20.              (select sid from v$mystat
    
21.                 where rownum=1)
    
22.           and (a.name like '%ga%'
    
23.               or a.name like '%direct temp%')
    
24.         union all
    
25.         select 'total: '||a.name,sum(b.value)
    
26.          from v$statname a,v$sesstat b ,
    
27.                     v$session c
    
28.          where a.statistic#=b.statistic#
    
29.          and (a.name like '%ga%'
    
30.             or a.name like '%direct temp%')
    
31.          and b.sid=c.sid
    
32.          and c.username is not null
    
33.          group by 'total: '||a.name
    
34.        );
    
35.        l_first_time:=false;
    
36.      end if;
    
37.     end loop;
    
38.   end;
    
39. /
    
40. commit;

3.sql
 

点击(此处)折叠或打开

1. declare
   
2.   l_msg long;
   
3.   l_status number;
   
4. begin
   
5.   dbms_alert.register('WAITING');
   
6.   for i in 1..999999 loop
   
7.     dbms_application_info.set_client_info(i);
   
8.     dbms_alert.waitone('WAITING',l_msg,l_status,0);
   
9.   exit when l_status=0;
   
10.   for x in(select * from t order by 1,2,3,4)
    
11.   loop
    
12.     null;
    
13.   end loop;
    
14.  end loop;
    
15. end;

4.sql
 

点击(此处)折叠或打开

1. begin
   
2.   dbms_alert.signal('WAITING','');
   
3.   commit;
   
4. end;

 5.sql
 

点击(此处)折叠或打开

1. create or replace package demo_pkg
   
2. as
   
3.   type array is table of char(2000)
   
4.      index by binary_integer;
   
5.   g_data array;
   
6. end;

6.sql
 

点击(此处)折叠或打开

1. select a.name,to_char(b.value,'999,999,999')
   
2.     bytes,to_char(round(b.value/1024/1024,1),'99,999.9') mbytes from v$statname a,v$mystat b
   
3. where a.statistic#=b.statistic#
   
4. and a.name like '%ga memory%';

7.sql

点击(此处)折叠或打开

1. begin
   
2.   for i in 1..200000
   
3.     loop
   
4.       demo_pkg.g_data(i):='x';
   
5.     end loop;
   
6. end;

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

转载于:http://blog.itpub.net/31349667/viewspace-2124032/