ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 8i下sort*排序大小以及执行计划的问题?

8i下sort*排序大小以及执行计划的问题?

原创 Linux操作系统 作者:lfree 时间:2005-10-20 00:00:00 0 删除 编辑
最近看8i下sort设置对执行计划的影响,发现一些奇特的现象,特此写下来:
最近看8i下sort设置对执行计划的影响,发现一些奇特的现象,特此写下来:

a.sql:
SELECT a.name, b.value
FROM v$statname a, v$mystat b
WHERE a.statistic# = b.statistic#
AND a.name like '%ga %'
/


b.sql:
SELECT "UNDRUG_INFO"."UNDRUG_CODE", "UNDRUG_INFO"."UNDRUG_NAME",
"UNDRUG_INFO"."UNIT_PRICE", "UNDRUG_INFO"."DEPT_CODE",
"UNDRUG_INFO"."SYS_CLASS", "UNDRUG_INFO"."FEE_CODE",
"UNDRUG_INFO"."STOCK_UNIT", "UNDRUG_INFO"."DEPT_NAME",
"UNDRUG_INFO"."SPELL_CODE", "UNDRUG_INFO"."INPUT_CODE",
"HIS_COMPARE"."APPLYFLAG"
FROM "UNDRUG_INFO", "HIS_COMPARE"
WHERE (undrug_info.undrug_code = his_compare.his_code(+))
ORDER BY "UNDRUG_INFO"."SPELL_CODE" ASC

undrug_info 表大小1.13M,记录9186
his_compare 表大小1.88M, 记录10450
/

c.sql
alter session set sort_area_size= &x;
alter session set sort_area_retained_size= &x;
set autotrace traceonly ;
@b ;
set autotrace off ;
@a ;

=======================
select * from v$version;

BANNER
----------------------------------------------------------------
Oracle8i Release 8.1.6.0.0 - Production
PL/SQL Release 8.1.6.0.0 - Production
CORE 8.1.6.0.0 Production
TNS for 32-bit Windows: Version 8.1.6.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production
===================================

将sort_area*参数分别设置65536,524288,6291456(即64K,512k,6m),开机缺省设置sort×=512K。

1。sort*=64k情况下,每次执行完成后退出再进入,以下类同:

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=289 Card=9167 Bytes=724193)
1 0 SORT (ORDER BY) (Cost=289 Card=9167 Bytes=724193)
2 1 MERGE JOIN (OUTER) (Cost=54 Card=9167 Bytes=724193)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'UNDRUG_INFO' (Cost=5 Card=9
Bytes=605
022)

4 3 INDEX (FULL SCAN) OF 'PK_UNDRUG_INFO' (UNIQUE) (Cost=16 Car
167)
5 2 SORT (JOIN) (Cost=44 Card=10436 Bytes=135668)
6 5 TABLE ACCESS (FULL) OF 'HIS_COMPARE' (Cost=5 Card=10436 Byt
135668)


Statistics
----------------------------------------------------------
16 recursive calls
65 db block gets
542 consistent gets
323 physical reads
0 redo size
1152005 bytes sent via SQL*Net to client
68245 bytes received via SQL*Net from client
613 SQL*Net roundtrips to/from client
0 sorts (memory)
2 sorts (disk)
9168 rows processed


NAME VALUE
---------------------------------------------------------------- ----------
session uga memory 84252
session uga memory max 229684
session pga memory 462596
session pga memory max 462596



2。sort*=512K的时候:


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=86 Card=9167 Bytes=724193)
1 0 SORT (ORDER BY) (Cost=86 Card=9167 Bytes=724193)
2 1 MERGE JOIN (OUTER) (Cost=27 Card=9167 Bytes=724193)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'UNDRUG_INFO' (Cost=5 Card=91
Bytes=605
022)

4 3 INDEX (FULL SCAN) OF 'PK_UNDRUG_INFO' (UNIQUE) (Cost=16 Card
167)
5 2 SORT (JOIN) (Cost=18 Card=10436 Bytes=135668)
6 5 TABLE ACCESS (FULL) OF 'HIS_COMPARE' (Cost=5 Card=10436 Byte
135668)




Statistics
----------------------------------------------------------
8 recursive calls
7 db block gets
539 consistent gets
51 physical reads
0 redo size
1152471 bytes sent via SQL*Net to client
68245 bytes received via SQL*Net from client
613 SQL*Net roundtrips to/from client
3 sorts (memory)
1 sorts (disk)
9168 rows processed


NAME VALUE
---------------------------------------------------------------- ----------
session uga memory 79724
session uga memory max 981836
session pga memory 1214268
session pga memory max 1214268

3。sort*=6M的时候:

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=70 Card=9167 Bytes=724193)
1 0 SORT (ORDER BY) (Cost=70 Card=9167 Bytes=724193)
2 1 HASH JOIN (OUTER) (Cost=13 Card=9167 Bytes=724193)
3 2 TABLE ACCESS (FULL) OF 'UNDRUG_INFO' (Cost=5 Card=9167 Bytes=605
022)
4 2 TABLE ACCESS (FULL) OF 'HIS_COMPARE' (Cost=5 Card=10436 Bytes=13
5668)

Statistics
----------------------------------------------------------
0 recursive calls
8 db block gets
177 consistent gets
0 physical reads
0 redo size
1152387 bytes sent via SQL*Net to client
68245 bytes received via SQL*Net from client
613 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
9168 rows processed


NAME VALUE
---------------------------------------------------------------- ----------
session uga memory 81024
session uga memory max 8408624
session pga memory 8611568
session pga memory max 8611568

从结果可以看出,sort设置越大执行计划发生了改变,趋向hash jion,但是session pga memory
的消耗也增加。另外sort设置越小,磁盘排序也会增加,物理读的数量也增加。


===============================================

将sort_area*参数分别设置524288,6291456(即512k,6m),开机缺省设置sort×=512K。
1。sort*=512k情况下,每次执行完成后不退出再重复执行:

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=86 Card=9167 Bytes=724193)
1 0 SORT (ORDER BY) (Cost=86 Card=9167 Bytes=724193)
2 1 MERGE JOIN (OUTER) (Cost=27 Card=9167 Bytes=724193)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'UNDRUG_INFO' (Cost=5 Card=9
Bytes=605
022)

4 3 INDEX (FULL SCAN) OF 'PK_UNDRUG_INFO' (UNIQUE) (Cost=16 Car
167)
5 2 SORT (JOIN) (Cost=18 Card=10436 Bytes=135668)
6 5 TABLE ACCESS (FULL) OF 'HIS_COMPARE' (Cost=5 Card=10436 Byt
135668)


Statistics
----------------------------------------------------------
8 recursive calls
7 db block gets
539 consistent gets
51 physical reads
0 redo size
1152471 bytes sent via SQL*Net to client
68245 bytes received via SQL*Net from client
613 SQL*Net roundtrips to/from client
3 sorts (memory)
1 sorts (disk)
9168 rows processed


NAME VALUE
---------------------------------------------------------------- ----------
session uga memory 77860
session uga memory max 985016
session pga memory 1225536
session pga memory max 1225536

3。sort*=6M的时候:

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=85 Card=9167 Bytes=724193)
1 0 SORT (ORDER BY) (Cost=85 Card=9167 Bytes=724193)
2 1 MERGE JOIN (OUTER) (Cost=28 Card=9167 Bytes=724193)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'UNDRUG_INFO' (Cost=5 Card=
Bytes=605
022)

4 3 INDEX (FULL SCAN) OF 'PK_UNDRUG_INFO' (UNIQUE) (Cost=16 Ca
167)
5 2 SORT (JOIN) (Cost=18 Card=10436 Bytes=135668)
6 5 TABLE ACCESS (FULL) OF 'HIS_COMPARE' (Cost=5 Card=10436 By
135668)




Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
536 consistent gets
0 physical reads
0 redo size
1152223 bytes sent via SQL*Net to client
68245 bytes received via SQL*Net from client
613 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
9168 rows processed


NAME VALUE
---------------------------------------------------------------- ----------
session uga memory 77860
session uga memory max 1371812
session pga memory 1570652
session pga memory max 1570652

可以发现一个奇特的现象,在同一个回话下,sort*变大,执行计划并没有发生改变,
现在修改b.sql文件加入一些注释,再执行b.sql看看执行计划,发现计划也没有变化,
说明oracle仅仅认第一次修改的sort×参数来生成执行计划。以后在回话中修改这些参数,
执行计划并没有发生变化。可以反向来验证这个结果,先设置sort×=6M,在设置为64K,
结果如下(在执行前必须退出回话):

sort×=6M:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=70 Card=9167 Bytes=724193)
1 0 SORT (ORDER BY) (Cost=70 Card=9167 Bytes=724193)
2 1 HASH JOIN (OUTER) (Cost=13 Card=9167 Bytes=724193)
3 2 TABLE ACCESS (FULL) OF 'UNDRUG_INFO' (Cost=5 Card=9167 Bytes
022)
4 2 TABLE ACCESS (FULL) OF 'HIS_COMPARE' (Cost=5 Card=10436 Byte
5668)




Statistics
----------------------------------------------------------
0 recursive calls
8 db block gets
177 consistent gets
0 physical reads
0 redo size
1152387 bytes sent via SQL*Net to client
68245 bytes received via SQL*Net from client
613 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
9168 rows processed


NAME VALUE
---------------------------------------------------------------- ----------
session uga memory 82188
session uga memory max 8412088
session pga memory 8621988
session pga memory max 8621988



sort×=64k:

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=248 Card=9167 Bytes=724193)
1 0 SORT (ORDER BY) (Cost=248 Card=9167 Bytes=724193)
2 1 HASH JOIN (OUTER) (Cost=13 Card=9167 Bytes=724193)
3 2 TABLE ACCESS (FULL) OF 'UNDRUG_INFO' (Cost=5 Card=9167 Bytes=6
022)
4 2 TABLE ACCESS (FULL) OF 'HIS_COMPARE' (Cost=5 Card=10436 Bytes=
5668)




Statistics
----------------------------------------------------------
8 recursive calls
52 db block gets
180 consistent gets
264 physical reads
0 redo size
1151885 bytes sent via SQL*Net to client
68245 bytes received via SQL*Net from client
613 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
9168 rows processed


NAME VALUE
---------------------------------------------------------------- ----------
session uga memory 82908
session uga memory max 8412088
session pga memory 7807952
session pga memory max 8621988

===================================================


这回修改系统的sort参数,看看情况(系统缺省512K):
ALTER SYSTEM SET sort_area_retained_size = 6291456 deferred ;
ALTER SYSTEM SET sort_area_size = 6291456 deferred ;
exit

退出后在进入,
show parameter sort_

NAME TYPE VALUE
------------------------------------ ------- ------------------------------
sort_area_retained_size integer 6291456
sort_area_size integer 6291456
sort_multiblock_read_count integer 2

参数已经发生了变化,执行b.sql,看结果:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=85 Card=9167 Bytes=724193)
1 0 SORT (ORDER BY) (Cost=85 Card=9167 Bytes=724193)
2 1 MERGE JOIN (OUTER) (Cost=28 Card=9167 Bytes=724193)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'UNDRUG_INFO' (Cost=5 Card=916
Bytes=605
022)

4 3 INDEX (FULL SCAN) OF 'PK_UNDRUG_INFO' (UNIQUE) (Cost=16 Card=
167)
5 2 SORT (JOIN) (Cost=18 Card=10436 Bytes=135668)
6 5 TABLE ACCESS (FULL) OF 'HIS_COMPARE' (Cost=5 Card=10436 Bytes
135668)


Statistics
----------------------------------------------------------
36 recursive calls
4 db block gets
546 consistent gets
0 physical reads
0 redo size
1152223 bytes sent via SQL*Net to client
68245 bytes received via SQL*Net from client
613 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
9168 rows processed


NAME VALUE
---------------------------------------------------------------- ----------
session uga memory 82904
session uga memory max 1372612
session pga memory 289972
session pga memory max 1595156

可以发现执行计划并没有因为sort×=6m,而没有选择hash join,而是选择merge join.
而仔细发现由于执行计划使用merge join,完成后pga的内存会回收,为什么会出现这种情况
呢???????????

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

上一篇: 另类SQL优化
请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2485
  • 访问量
    6292139