作者: 弦乐之花 | 可以转载, 但必须以超链接形式标明文章原始出处和作者信息及版权声明

网址http://shiri512003.itpub.net/post/37713/496824

这个问题首先由eagle提出，相关链接为http://www.dbafan.com/blog/?p=295#comment-38424

这里只是自己在10204linux平台上测试一下
相关脚本
-- 脚本取自tom 9i&10g编程艺术P172-173
-- reset_stat.sql
drop table sess_stats;
create table sess_stats
( name varchar2(64), value number, diff number );
variable sid number
exec :sid := &1
-- watch_stat.sql
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 *
from sess_stats
order by name;
测试表t1
SQL>!ora size t1
Session altered.

OWNER SEGMENT_NAME SEGMENT_TYPE SIZE_MB
---------- ------------ ------------------ ----------
mytest t1 TABLE 222
场景1：sort_area_size只设置一次情况
sess1：
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
SQL>select sid from v$mystat where rownum=1;
SID
----------
1083
sess2:
SQL>@reset_stat
Table dropped.

Table created.
Enter value for 1: 1083
PL/SQL procedure successfully completed.
SQL>@watch_stat
6 rows merged.

NAME VALUE DIFF
---------------------------------------------------------------- ---------- ----------
physical reads direct temporary tablespace 0
physical writes direct temporary tablespace 0
session pga memory 560728
session pga memory max 560728
session uga memory 224176
session uga memory max 224176
6 rows selected.
sess1:
SQL>show parameter sort_area
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sort_area_retained_size integer 0
sort_area_size integer 1048576
SQL>alter session set workarea_size_policy=manual;
Session altered.
SQL>alter session set sort_area_size = 300000000;
Session altered.
SQL>set autot trace stat
SQL>select * from t1 order by 1;
819960 rows selected.

Statistics
----------------------------------------------------------
207 recursive calls
98 db block gets
28031 consistent gets
32815 physical reads
0 redo size
108280270 bytes sent via SQL*Net to client
90681 bytes received via SQL*Net from client
8201 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk) -- 注意到这里是disk sort
819960 rows processed

sess2:
SQL>@watch_stat
6 rows merged.

NAME VALUE DIFF
---------------------------------------------------------------- ---------- ----------
physical reads direct temporary tablespace 32813 32813
physical writes direct temporary tablespace 32813 32813
session pga memory 757336 196608
session pga memory max 2264664 1703936 -- 注意到这里diff只有1M+
session uga memory 289584 65408
session uga memory max 1728560 1504384
6 rows selected.
场景2：sort_area_size设置两次情况
sess1:
SQL>select sid from v$mystat where rownum=1;
SID
----------
1083
SQL>show parameter sort_area
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sort_area_retained_size integer 0
sort_area_size integer 1048576
sess2:
SQL>@reset_stat
Table dropped.

Table created.
Enter value for 1: 1083
PL/SQL procedure successfully completed.
SQL>@watch_stat
6 rows merged.

NAME VALUE DIFF
---------------------------------------------------------------- ---------- ----------
physical reads direct temporary tablespace 0
physical writes direct temporary tablespace 0
session pga memory 691800
session pga memory max 1019480
session uga memory 231664
session uga memory max 674912
6 rows selected.
sess1:
SQL>alter session set workarea_size_policy=manual;
Session altered.
SQL>alter session set sort_area_size = 300000000;
Session altered.
SQL>alter session set sort_area_size = 300000000;
Session altered.
SQL>set autot trace stat
SQL>select * from t1 order by 1;
819960 rows selected.

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
28031 consistent gets
0 physical reads
0 redo size
108392579 bytes sent via SQL*Net to client
90681 bytes received via SQL*Net from client
8201 SQL*Net roundtrips to/from client
1 sorts (memory) -- 注意到这里已经是memory sort了
0 sorts (disk)
819960 rows processed
sess2:
SQL>@watch_stat
6 rows merged.

NAME VALUE DIFF
---------------------------------------------------------------- ---------- ----------
physical reads direct temporary tablespace 0 0
physical writes direct temporary tablespace 0 0
session pga memory 757336 65536
session pga memory max 223841880 222822400 --注意到这里diff值已经是220M左右了，接近于表大小
session uga memory 289584 57920
session uga memory max 222938416 222263504
6 rows selected.

不知道这个问题是否有官方说明？