一个Distinct的优化。
ISN有3000万笔,SCARTON有600万笔
原SQL:
16:07:02 SQL> set autotrace on;
16:07:19 SQL> SELECT COUNT(DISTINCT(SCARTON.SCARNO)) MAX_SCARTON
16:07:59 2 FROM SCARTON,ISN WHERE SCARTON.SO='NB1P813740' AND SCARTON.SOLINE=1 AND SCARTON.SCARNO=ISN.SCARNO;
192
Elapsed: 00:00:00.93
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=67 Card=1 Bytes=34)
1 0 SORT (GROUP BY)
2 1 NESTED LOOPS (Cost=67 Card=6 Bytes=204)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'SCARTON' (Cost=7 Car
d=6 Bytes=174)
4 3 INDEX (RANGE SCAN) OF 'SCARTON2' (NON-UNIQUE) (Cost=
3 Card=6)
5 2 PARTITION RANGE (ALL)
6 5 PARTITION HASH (ALL)
7 6 INDEX (RANGE SCAN) OF 'ISN7' (NON-UNIQUE) (Cost=10
Card=1 Bytes=5)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
21593 consistent gets
99 physical reads
0 redo size
522 bytes sent via SQL*Net to client
656 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
优化后的SQL:
16:08:02 SQL> SELECT COUNT(SCARTON.SCARNO)
16:09:22 2 FROM SCARTON WHERE exists (select 1 from ISN,SCARTON where SCARTON.SCARNO=ISN.SCARNO and SCARTON.SO='NB1P813740' AND SCARTON.SOLINE=1)
16:09:22 3 and SCARTON.SO='NB1P813740' AND SCARTON.SOLINE=1;
192
Elapsed: 00:00:00.08
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=12)
1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 INDEX (RANGE SCAN) OF 'SCARTON2' (NON-UNIQUE) (Cost=3
Card=6 Bytes=72)
4 2 NESTED LOOPS (Cost=27469280 Card=5505130 Bytes=1211128
60)
5 4 PARTITION RANGE (ALL)
6 5 PARTITION HASH (ALL)
7 6 INDEX (FULL SCAN) OF 'ISN7' (NON-UNIQUE) (Cost=2
4330 Card=27444950 Bytes=137224750)
8 4 INDEX (UNIQUE SCAN) OF 'SCARTON1' (UNIQUE) (Cost=1 C
ard=1 Bytes=17)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
20 consistent gets
2 physical reads
0 redo size
532 bytes sent via SQL*Net to client
656 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
仅仅这个SQL的改动减少了Production DB约20%的总buffer gets.
而且发现CBO在估计partition table的cost颇有失水准…
改动通过多次不同值static sql和Bind Variable下的稳定性测试。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10856805/viewspace-1000690/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10856805/viewspace-1000690/