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的記憶體會回收，為什麼會出現這種情況
呢？？？？？？？？？？？
最近看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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Array.sort排序問題排序
- SQL執行計劃異常引起的效能問題SQL
- SQL執行計劃異常 引起的效能問題SQL
- 009 Linux 檔案大小統計與排序( du於df和sort)Linux排序
- [20210114]toad檢視真實執行計劃問題.txt
- 計數排序 - Counting Sort排序
- [20210926]並行執行計劃疑問.txt並行
- 執行計劃-1：獲取執行計劃
- 分析執行計劃優化SQLORACLE的執行計劃(轉)優化SQLOracle
- SQL優化案例-從執行計劃定位SQL問題（三）SQL優化
- Oracle優化案例-從執行計劃定位SQL問題（三）Oracle優化SQL
- 【執行計劃】Oracle獲取執行計劃的幾種方法Oracle
- 執行緒中斷以及執行緒中斷引發的那些問題執行緒
- java多執行緒程式設計問題以及解決辦法Java執行緒程式設計
- [20240313]toad gather_plan_statistics執行計劃相關問題.txt
- [20210205]toad檢視真實執行計劃問題3.txt
- SQL最佳化案例-從執行計劃定位SQL問題（三）SQL
- sort排序排序
- 服裝製造企業存在的問題以及未來行業發展計劃行業
- SYBASE執行計劃
- MySQL 執行計劃MySql
- MySQL執行計劃MySql
- 在命令列用 sort 進行排序命令列排序
- JAR衝突問題的解決以及執行狀態下如何檢視載入的類JAR
- SqlServer的執行計劃如何分析？SQLServer
- sqm執行計劃的繫結
- Sort排序專題（5）快速排序（QuickSort）(C++實現)排序UIC++
- mysql explain 執行計劃MySqlAI
- explain執行計劃分析AI
- mysql執行計劃explainMySqlAI
- oracle 固定執行計劃Oracle
- Oracle sql執行計劃OracleSQL
- MySQL執行計劃解析MySql
- 執行計劃執行步驟原則
- 【redis】-使用Lua指令碼解決多執行緒下的超賣問題以及為什麼？Redis指令碼執行緒
- 【PG執行計劃】Postgresql資料庫執行計劃統計資訊簡述SQL資料庫
- 演算法題-測試用例執行計劃演算法
- Sort排序專題（7）歸併排序（MergeSort）(C++實現)排序C++
- 快速排序 (Quick Sort)排序UI