Oracle 11g通过提高IO吞吐量（修改_db_file_optimizer_read_count）来优化全表扫描

--Oracle 11g通过提高IO吞吐量（修改_db_file_optimizer_read_count）来优化全表扫描SYS@PROD1> select ksppinm, indx from x$ksppi where ksppinm like '%db_file_optimi%';KSPPINM      INDX------------------------------ ----------_db_file_optimizer_read_count     1074SYS@PROD1> select ksppstdvl from x$ksppcv where indx=1074;KSPPSTDVL----------------------------------------------------------------------------------------------------8--实验表SYS@PROD1> select count(*) from tt;  COUNT(*)----------    480000SYS@PROD1> select count(*) from tt;  --默认值为8时的开销Execution Plan----------------------------------------------------------Plan hash value: 3133740314-------------------------------------------------------------------| Id  | Operation   | Name | Rows  | Cost (%CPU)| Time  |-------------------------------------------------------------------|   0 | SELECT STATEMENT   |  |1 |  1546   (1)| 00:00:01 ||   1 |  SORT AGGREGATE    |  |1 |       |  ||   2 |   TABLE ACCESS FULL| TT   |   587K|  1546   (1)| 00:00:01 |-------------------------------------------------------------------Note-----   - dynamic sampling used for this statement (level=2)SYS@PROD1> alter session set "_db_file_optimizer_read_count"=16; Session altered.SYS@PROD1> select count(*) from tt;  --修改为16时，开销降低19%Execution Plan----------------------------------------------------------Plan hash value: 3133740314-------------------------------------------------------------------| Id  | Operation   | Name | Rows  | Cost (%CPU)| Time  |-------------------------------------------------------------------|   0 | SELECT STATEMENT   |  |1 |  1250   (1)| 00:00:01 ||   1 |  SORT AGGREGATE    |  |1 |       |  ||   2 |   TABLE ACCESS FULL| TT   |   587K|  1250   (1)| 00:00:01 |-------------------------------------------------------------------Note-----   - dynamic sampling used for this statement (level=2)SYS@PROD1> alter session set "_db_file_optimizer_read_count"=32;Session altered.SYS@PROD1> select count(*) from tt;  --修改为32时，开销降低11%Execution Plan----------------------------------------------------------Plan hash value: 3133740314-------------------------------------------------------------------| Id  | Operation   | Name | Rows  | Cost (%CPU)| Time  |-------------------------------------------------------------------|   0 | SELECT STATEMENT   |  |1 |  1103   (1)| 00:00:01 ||   1 |  SORT AGGREGATE    |  |1 |       |  ||   2 |   TABLE ACCESS FULL| TT   |   587K|  1103   (1)| 00:00:01 |-------------------------------------------------------------------Note-----   - dynamic sampling used for this statement (level=2)SYS@PROD1> alter session set "_db_file_optimizer_read_count"=64;Session altered.SYS@PROD1> select count(*) from tt;  --修改为64时，开销降低7%Execution Plan----------------------------------------------------------Plan hash value: 3133740314-------------------------------------------------------------------| Id  | Operation   | Name | Rows  | Cost (%CPU)| Time  |-------------------------------------------------------------------|   0 | SELECT STATEMENT   |  |1 |  1029   (1)| 00:00:01 ||   1 |  SORT AGGREGATE    |  |1 |       |  ||   2 |   TABLE ACCESS FULL| TT   |   587K|  1029   (1)| 00:00:01 |-------------------------------------------------------------------Note-----   - dynamic sampling used for this statement (level=2)SYS@PROD1> alter session set "_db_file_optimizer_read_count"=128;Session altered.SYS@PROD1> select count(*) from tt;  --修改为128时，开销降低3.5%Execution Plan----------------------------------------------------------Plan hash value: 3133740314-------------------------------------------------------------------| Id  | Operation   | Name | Rows  | Cost (%CPU)| Time  |-------------------------------------------------------------------|   0 | SELECT STATEMENT   |  |1 |   992   (1)| 00:00:01 ||   1 |  SORT AGGREGATE    |  |1 |       |  ||   2 |   TABLE ACCESS FULL| TT   |   587K|   992   (1)| 00:00:01 |-------------------------------------------------------------------Note-----   - dynamic sampling used for this statement (level=2)SYS@PROD1> alter session set "_db_file_optimizer_read_count"=256;Session altered.SYS@PROD1> select count(*) from tt;  --修改为256时，开销不再降低Execution Plan----------------------------------------------------------Plan hash value: 3133740314-------------------------------------------------------------------| Id  | Operation   | Name | Rows  | Cost (%CPU)| Time  |-------------------------------------------------------------------|   0 | SELECT STATEMENT   |  |1 |   992   (1)| 00:00:01 ||   1 |  SORT AGGREGATE    |  |1 |       |  ||   2 |   TABLE ACCESS FULL| TT   |   587K|   992   (1)| 00:00:01 |-------------------------------------------------------------------Note-----   - dynamic sampling used for this statement (level=2)