manul PGA下sort_area*需設定兩次問題

作者: 絃樂之花 | 可以轉載, 但必須以超連結形式標明文章原始出處和作者資訊及版權宣告

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

[@more@]

這個問題首先由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.

不知道這個問題是否有官方說明？