- --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)