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	     1074

SYS@PROD1> select ksppstdvl from x$ksppcv where indx=1074;

KSPPSTDVL
----------------------------------------------------------------------------------------------------
8

--实验表
SYS@PROD1> select count(*) from tt;

  COUNT(*)
----------
    480000
	
SYS@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)