怎么手动PGA没效啊

显示全部楼层 · 2008-9-27 09:35:45

NAME
VALUE DIFF
---------------------------------------------------------------- ---------- ----------
physical reads direct temporary tablespace
188
physical writes direct temporary tablespace
188
session pga memory
7313996
session pga memory max
11115084
session uga memory
1396000
session uga memory max
2705280
SQL> alter session set workarea_size_policy=manual;

SQL> alter session set sort_area_size = 65536;

Session altered
SQL> set termout off
SQL> select * from t order by 1, 2, 3, 4;
SQL> set termout on
SQL> @ D:\watch_stat.sql
NAME
VALUE DIFF
---------------------------------------------------------------- ---------- ----------
physical reads direct temporary tablespace
2947 2759
physical writes direct temporary tablespace
2947 2759
session pga memory
7117388-196608
session pga memory max
111150840
session uga memory
13960000
session uga memory max
27052800

SQL> alter session set sort_area_size=1073741820;---1G
SQL> set termout off
SQL> select * from t order by 1, 2, 3, 4;
SQL> set termout on
SQL> @ D:\watch_stat.sql
NAME
VALUE DIFF
---------------------------------------------------------------- ---------- ----------
physical reads direct temporary tablespace
3606
0
physical writes direct temporary tablespace
3606
0
session pga memory
77503052 69992448
session pga memory max
78551628 67436544
session uga memory
1396000
0
session uga memory max
80078645302584

千问 · 2008-9-27 09:35:45

有效果啊、

千问 · 2008-9-27 09:35:45

我把排序区设大了
怎么IO反而增加了

千问 · 2008-9-27 09:35:45

sort_area_retained_size

千问 · 2008-9-27 09:35:45

值有变化
但好像还是不对啊
SQL> alter session set sort_area_retained_size = 65536;

Session altered
SQL> set termout off
SQL> select * from t order by 1, 2, 3, 4;
SQL> set termout on
SQL> @ D:\watch_stat.sql
NAME
VALUE DIFF
---------------------------------------------------------------- ---------- ----------
physical reads direct temporary tablespace
2752 2752
physical writes direct temporary tablespace
2752 2752
session pga memory
891468 393216
session pga memory max
89146865536
session uga memory
86720
0
session uga memory max
21764865464

SQL> alter session set sort_area_retained_size=1073741820;

Session altered
SQL> set termout off
SQL> select * from t order by 1, 2, 3, 4;
SQL> set termout on
SQL> @ D:\watch_stat.sql
NAME
VALUE DIFF
---------------------------------------------------------------- ---------- ----------
physical reads direct temporary tablespace
8261 2752
physical writes direct temporary tablespace
8261 2752
session pga memory
891468
0
session pga memory max
891468
0
session uga memory
86720
0
session uga memory max
217648
0

千问 · 2008-9-27 09:35:45

show parameters sort_area 结果是什么

千问 · 2008-9-27 09:35:45

NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
sort_area_retained_size
integer 0
sort_area_size
integer 65536
怎么好像改不了

千问 · 2008-9-27 09:35:45

会话里改pga到manul以后可以改sort_area_的，show parameters 确认是否成功

千问 · 2008-9-27 09:35:45

SQL> alter session set workarea_size_policy=manual;

Session altered

SQL> alter session set sort_area_retained_size=1073741820;

Session altered

SQL> show parameters sort_area

NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
sort_area_retained_size
integer 0
sort_area_size
integer 65536

不行啊，不知哪里出问题了

千问 · 2008-9-27 09:35:45

SQL> alter session set workarea_size_policy=manual;

Session altered

SQL> show parameters workarea

NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy
stringAUTO

发现问题了，怎么不能改成手动呢