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

最近看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的内存会回收，为什么会出现这种情况
呢？？？？？？？？？？？

时间： 2024-08-03 19:36:22

8i下sort*排序大小以及执行计划的问题？的相关文章

【显示执行计划】在普通用户下使用set autot 查看执行计划

在普通用户下查看执行计划 时 ,会遇到如下问题: SQL> CONN SCOTT/SCOTT 已连接. SQL> SET AUTOT TRACE搜集统计信息时出错 ORA-942 SP2-0611: 启用 STATISTICS 报告时出错 SQL> SET AUTOT ON 搜集统计信息时出错 ORA-942 SP2-0611: 启用 STATISTICS 报告时出错 SQL> SET AUTOT OFF SQL> SET AUTOT ON 搜集统计信息时出错 ORA-942

SQL SERVER 2014 下IF EXITS 居然引起执行计划变更的案例分享

  这个问题是在SQL SERVER 2005 升级到SQL SERVER 2014的测试过程中一同事发现的.我觉得有点意思,遂稍微修改一下脚本展示出来,本来想构造这样的一个案例来演示,但是畏惧麻烦,遂直接贴上原表,希望 Leader不要叼我(当然个人觉得真没啥,两张表名而已,真泄露不了啥信息).     脚本如下所示,非常简单的一段SQL语句,我将其分为SQL1.SQL2.SQL3.  其实SQL2.SQL3是差不多的,唯一的区别在于多了一个IF EXISTS DECLARE @Operati

[20120323]linux下sort排序.txt

昨天要排序一个文件:Ethernet0/1Ethernet0/10Ethernet0/11Ethernet0/12Ethernet0/13Ethernet0/14Ethernet0/15Ethernet0/16Ethernet0/17Ethernet0/18Ethernet0/19Ethernet0/2Ethernet0/20Ethernet0/21Ethernet0/22Ethernet0/23Ethernet0/24Ethernet0/25Ethernet0/26Ethernet0/27Et

shell下sort排序命令的用法

首先建立一个文件,很乱,没有规律: 正排序: 倒排序: Uniq 删除文件中的重复行:用此命令要先对文件进行排序. 对文件冗余,只要文件所有重复的字符显示一次: 显示1-7,不重复的行: 只显示1-7中重复的行: 文件中的所有字符显示一遍,并计算他们重复的个数: 作者:cnblogs LinuxSuper孟 返回栏目页:http://www.bianceng.cnhttp://www.bianceng.cn/OS/Linux/

[20161216]toad下显示真实的执行计划.txt

[20161216]toad下显示真实的执行计划.txt --大家都应该知道使用explain plan看执行计划,有时候显示的执行计划不是真实的执行计划.现在我虽然使用它看,仅仅作为参考. --昨天看链接: http://www.toadworld.com/platforms/oracle/b/weblog/archive/2016/12/13/toad-explain-plan-tip-returning-actual-sql-execution-explain-plan --才知道toad

Oracle查看执行计划的几种方法

Oracle查看执行计划的几种方法   一般来说,有如下几种获取执行计划的方式: 1.AUTOTRACE方式 AUTOTRACE是Oracle自带的客户端工具SQL*Plus的一个特性.启用AUTOTRACE后,SQL*Plus会自动收集执行过的SQL语句的执行计划.性能统计数据等,并在语句执行结束后显示在SQL*Plus中. DBA用户可以直接使用AUTOTRACE功能,但是如果用户没有DBA权限,那么需要在SYS用户下执行plustrce.sql脚本,自动创建PLUSTRACE角色,再把PL

关键时刻HINT出彩 - PG优化器的参数优化、执行计划固化CASE

背景 有过数据库使用经验的童鞋可曾遇到过SQL执行计划不准确,或者SQL执行计划抖动的问题. PostgreSQL的执行计划与大多数的企业数据库是一样的,都是基于成本优化. 基于成本优化的优化器,在算法靠谱,统计信息准确的前提下,通常得到的执行计划是比较准确的. 那么什么时候执行计划可能不准确呢? 成本估算的算法不好 这个需要内核的不断改进,完善.在没有合理的算法支撑的情况下,内核中往往会带有一些经验值,或者将这些经验值开放给用户设置. 统计信息不准确 PG的统计信息收集调度是几个参数共同决定的

通过dbms_xplan.display_cursor识别低效的执行计划

dbms_xplan.display_cursor定义: function display_cursor(sql_id           varchar2 default  null,                                      cursor_child_no  integer  default  0,                                      format          varchar2 default  'TYPICAL')

【SPM】Oracle如何固定执行计划

[SPM]Oracle如何固定执行计划   1.1  BLOG文档结构图   1.2  前言部分   1.2.1  导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~: ① 固定执行计划的常用方法:outline.SQL Profile.SPM(重点) ② coe_xfr_sql_profile.sql脚本的使用     Tips:        ① 若文章代码格式有错乱,推荐使用QQ.搜狗或360浏览器,也可以下载pdf格式