Manual PGA Memory Management

Scripts:

create table t as select * from all_objects;

exec dbms_stats.gather_table_stats(user,'T');


run_query . sql

点击(此处)折叠或打开

1. connect /
2. set serveroutput off
   
3. set echo on
   
4. column sid new_val SID
   
5. select sid from v$mystat where rownum=1;
   
6. alter session set workarea_size_policy=manual ;
   
7. alter session set sort_area_size= &1;
   
8. prompt run @reset_stat &SID and @watch_stat in another session
   
9. pause
   
10. set termout off
    
11. select * from t order by 1,2,3,4;
    
12. set termout on
    
13. prompt run @watch_stat in another session
    
14. pause

reset_stat.sql

点击(此处)折叠或打开

1. drop table sess_stats;
   
2. create table sess_stats
   
3.    (
   
4.     name varchar2(64),
   
5.     value number,
   
6.     diff number
   
7.     );
   
8. 
   
9. variable sid number
   
10. exec :sid := &1

watch_stat.sql

点击(此处)折叠或打开

1. merge into sess_stats
   
2. using
   
3. (
   
4.  select a.name ,b.value
   
5.    from v$statname a, v$sesstat b
   
6.    where a.statistic#=b.statistic#
   
7.    and b.sid= :sid
   
8.    and (a.name like '%ga%'
   
9.         or a.name like '%direct temp%')
   
10.  ) curr_stats
    
11. on (sess_stats.name=curr_stats.name)
    
12. when matched then
    
13.   update set diff=curr_stats.value-sess_stats.value,value=curr_stats.value
    
14. when not matched then
    
15.   insert (name,value,diff)
    
16.   values(curr_stats.name,curr_stats.value,null)
    
17. /
    
18. select name,case when name like '%ga%'
    
19.        then round(value/1024,0)
    
20.        else value
    
21.        end kbytes_writes,
    
22.   case when name like '%ga%'
    
23.        then round(diff/1024,0)
    
24.        else value
    
25.        end diff_kbytes_writes
    
26.   from sess_stats
    
27. order by name;

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

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