六、操作命令& z) u {) E- T
系统级更改:+ v% p* ]* Y3 R/ [: k0 f
ALTER SYSTEM SET WORKAREA_SIZE_POLICY = {AUTO | MANAUL};
0 S6 m# |- w+ w( I# i ALTER SYSTEM SET PGA_AGGREGATE_TARGET=100000000;
4 r: R: n7 r1 R( A ALTER SYSTEM SET SORT_AREA_SIZE = 65536 SCOPE = SPFILE;
: q- P; D5 ?0 s. a. S* A, F# u ALTER SYSTEM SET HASH_AREA_SIZE = 65536 SCOPE = SPFILE;/ i* _/ e, ]8 M$ U }# ]. ?
会话级更改
9 j/ }( ?. ]/ v. W$ w& ^ ALTER SESSION SET WORKAREA_SIZE_POLICY = {AUTO | MANAUL};& u- }# K' _9 M& P C4 n, r
ALTER SESSION SET SORT_AREA_SIZE = 65536;/ d7 v% C7 U; ]
ALTER SESSION SET HASH_AREA_SIZE = 65536;
; H. G9 X2 G9 E/ b( Z/ a; G 七、学以致用8 Z5 R Q# S2 j4 a7 V) F# I
1,排序区:
* ], p. z+ |- _) Q4 ^- r/ C- R9 c pga_aggregate_target为100MB,单个查询能用到5%也就是5MB时排序所需时间 9 z, x- t' @: l8 P/ j! w
SQL> create table sorttable as select * from all_objects;
- [( x* W, u& U# M$ k5 P 表已创建。
) l; c7 w/ L) E, |8 R+ H SQL> insert into sorttable (select * from sorttable);
/ Y1 P3 B8 ^/ p) t$ x2 w% x/ I& ~2 C 已创建49735行。
9 D# s2 i, A0 g+ ~6 x- J# V SQL> insert into sorttable (select * from sorttable);
* K3 |$ ]. r7 ?: R 已创建99470行。
* k% `. P. K& f: v; b SQL> set timing on;
' @( W6 o9 c* L* c SQL> set autotrace traceonly;' p: y0 M) M* f2 I
SQL> select * from sorttable order by object_id;
, `- Y$ D% ?0 t, ?4 r 已选择198940行。. A7 L3 V v4 {& d
已用时间: 00: 00: 50.492 l5 m P& C& p3 }" q
Session级修改排序区为30mb所需时间
% Y; c" p7 J( V0 F3 M) {! ]5 i/ n: L SQL> ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL;
, j5 b, B: u' B# g' ~ 会话已更改。7 k# B" v9 \/ m g4 x
已用时间: 00: 00: 00.022 n g0 c. }5 ?
SQL> ALTER SESSION SET SORT_AREA_SIZE = 30000000;
4 e1 ]- I' w+ r, [3 P6 X 会话已更改。
, C3 s8 }* F4 r. B% f4 c: k, z( ]& U 已用时间: 00: 00: 00.01
8 s* t: M0 m6 p1 ] A SQL> select * from sorttable order by object_id;
: h0 m7 B0 i: v3 S 已选择198940行。2 ~+ z# ?: D2 a! m
已用时间: 00: 00: 10.764 I2 @) S: r/ n* \" p* v3 @
可以看到所需时间从50.49秒减少到10.31秒,速度提升很明显。) c9 _4 h5 t% y+ v5 _+ [8 o/ x6 t* x
2,散列区:
9 r* `' r/ H/ ? T1 U pga_aggregate_target为100MB,单个查询能用到5%也就是5MB时表连接所需时间0 y1 v/ \/ D3 `. g5 Q& \) R
SQL> select /*+ use_hash(tb1 tb2)*/ * from sorttable tb1,sorttable tb2 where tb1.object_id=tb2.object_id;
$ _/ g4 {0 G! ?9 f; T5 @! [ 已选择49735行。; T* r) m( s, ]1 O$ m$ w: h# ^
已用时间: 00: 00: 40.50
r7 _+ \9 \& s9 M Session级修改散列区为30mb所需时间 o% k# g1 S* @- _ a
SQL> ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL;
; w. [- ]7 q; E) N9 r 会话已更改。$ q: o1 Z. A$ Y$ N* d
已用时间: 00: 00: 00.01
; I1 j% F6 A$ j: I/ M SQL> ALTER SESSION SET HASH_AREA_SIZE = 30000000;
+ B/ h* ^$ U |) W* Q" J* [+ c$ Z 会话已更改。& ^2 T e" {3 @( j: X. K0 Y
已用时间: 00: 00: 00.01
+ n' J, W1 |( q! ]0 O SQL> select /*+ use_hash(tb1 tb2)*/ * from sorttable tb1,sorttable tb2 where tb1.object_id=tb2.object_id;
7 ~9 Q' x7 c% a% y% e) v 已选择49735行。7 P. X G- {. H- g( I
已用时间: 00: 00: 04.477 Y8 j9 X4 _* y* }9 p
所需时间由40.50秒提升到4.47秒,效果同样很明显。
/ k" N3 L- K+ K; s, |; p+ C 备注:以上实验皆执行全表扫描保证相关表读入缓冲区中,避免因数据没读入缓存造成误差。' W& W3 o+ u ~
结论:在9iR2版以后,PGA不再像以前那样困扰DBA了,Oracle会帮我们做好PGA的分配。但这并不意味着DBA不需要深入了解PGA了,掌握PGA并根据适当应用会让工作如虎添翼。 |