[20131014]System Statistics Gathered.txt

http://www.antognini.ch/2013/10/system-statistics-gathered-in-exadata-mode-when-are-they-relevant/

这篇对于system statistics的收集很重要，特别是cost的算法。系统统计收集我估计很少dba做，即使知道这个
功能，一般都很少在生产系统使用，我个人也不用。自己对这种方式计算cost不是很了解，做一个测试看看。

SCOTT@test> Select * from sys.aux_stats$;

SNAME                          PNAME                                   PVAL1 PVAL2

------------------------------ ------------------------------ -------------- ------------------------------

SYSSTATS_INFO                  STATUS                                        COMPLETED

SYSSTATS_INFO                  DSTART                                        08-20-2013 11:28

SYSSTATS_INFO                  DSTOP                                         08-20-2013 11:58

SYSSTATS_INFO                  FLAGS                                       1

SYSSTATS_MAIN                  CPUSPEEDNW                               2657

SYSSTATS_MAIN                  IOSEEKTIM                                  10

SYSSTATS_MAIN                  IOTFRSPEED                               4096

SYSSTATS_MAIN                  SREADTIM                                    5

SYSSTATS_MAIN                  MREADTIM                                   10

SYSSTATS_MAIN                  CPUSPEED                                 1000

SYSSTATS_MAIN                  MBRC                                       16

SYSSTATS_MAIN                  MAXTHR

SYSSTATS_MAIN                  SLAVETHR

13 rows selected.

正常收集方式如下：
exec dbms_stats.gather_system_stats('start');
---等一段时间，一般选择高峰期执行。
exec dbms_stats.gather_system_stats('stop');
或者
execute dbms_stats.gather_system_stats('interval',1800);

1.如果有系统统计信息，正常的算法：

io_cost = ceil ( blocks / mbrc * mreadtim / sreadtim ) + 1


自己按照以上链接做一些测试：

@ver

SCOTT@test> @ver

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

create table t pctfree 99 as select rownum id , 'test' name from dual connect by level

exec dbms_stats.gather_table_stats(user,'T');

SCOTT@test> select num_rows,blocks from dba_tables where wner=user and table_name='T';

  NUM_ROWS     BLOCKS

---------- ----------

      1029       1055

--占用1055块。

BEGIN
  dbms_stats.delete_system_stats();
  dbms_stats.set_system_stats(pname => 'CPUSPEED', pvalue => 2832);
  dbms_stats.set_system_stats(pname => 'SREADTIM', pvalue => 1.3);
  dbms_stats.set_system_stats(pname => 'MREADTIM', pvalue => 7.8);
  dbms_stats.set_system_stats(pname => 'MBRC',     pvalue => 42);
END;
/

SCOTT@test> Select * from sys.aux_stats$;

SNAME                          PNAME                                PVAL1 PVAL2

------------------------------ ------------------------------ ----------- ------------------------------

SYSSTATS_INFO                  STATUS                                     COMPLETED

SYSSTATS_INFO                  DSTART                                     10-14-2013 09:46

SYSSTATS_INFO                  DSTOP                                      10-14-2013 09:46

SYSSTATS_INFO                  FLAGS                               1.0000

SYSSTATS_MAIN                  CPUSPEEDNW                       1531.0000

SYSSTATS_MAIN                  IOSEEKTIM                          10.0000

SYSSTATS_MAIN                  IOTFRSPEED                       4096.0000

SYSSTATS_MAIN                  SREADTIM                            1.3000

SYSSTATS_MAIN                  MREADTIM                            7.8000

SYSSTATS_MAIN                  CPUSPEED                         2832.0000

SYSSTATS_MAIN                  MBRC                               42.0000

SYSSTATS_MAIN                  MAXTHR

SYSSTATS_MAIN                  SLAVETHR

13 rows selected.

SCOTT@test> explain plan for select * from t ;

Explained.

SCOTT@test> @dp

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |  1029 |  1009K|   154   (2)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| T    |  1029 |  1009K|   154   (2)| 00:00:01 |

--------------------------------------------------------------------------

8 rows selected.

SCOTT@test> SELECT (blocks / 42 * 7.8 / 1.3) + 1 AS io_cost_formula FROM user_tables WHERE table_name = 'T';

IO_COST_FORMULA

---------------

            152

--相差2，不知道为什么?没有包括cpu的cost吗？

2.如果没有系统统计信息，算法如下：
sreadtim = ioseektim + db_block_size / iotfrspeed
mreadtim = ioseektim + db_file_multiblock_read_count * db_block_size / iotfrspeed

BEGIN
  dbms_stats.delete_system_stats();
  dbms_stats.set_system_stats(pname => 'CPUSPEEDNW', pvalue => 1618);
  dbms_stats.set_system_stats(pname => 'IOSEEKTIM',  pvalue => 9);
  dbms_stats.set_system_stats(pname => 'IOTFRSPEED', pvalue => 88419);
END;
/

SCOTT@test> Select * from sys.aux_stats$;

SNAME                          PNAME                                PVAL1 PVAL2

------------------------------ ------------------------------ ----------- ------------------------------

SYSSTATS_INFO                  STATUS                                     COMPLETED

SYSSTATS_INFO                  DSTART                                     10-14-2013 12:01

SYSSTATS_INFO                  DSTOP                                      10-14-2013 12:01

SYSSTATS_INFO                  FLAGS                               1.0000

SYSSTATS_MAIN                  CPUSPEEDNW                       1618.0000

SYSSTATS_MAIN                  IOSEEKTIM                           9.0000

SYSSTATS_MAIN                  IOTFRSPEED                      88419.0000

SYSSTATS_MAIN                  SREADTIM

SYSSTATS_MAIN                  MREADTIM

SYSSTATS_MAIN                  CPUSPEED

SYSSTATS_MAIN                  MBRC

SYSSTATS_MAIN                  MAXTHR

SYSSTATS_MAIN                  SLAVETHR

13 rows selected.

SCOTT@test> ALTER SESSION SET db_file_multiblock_read_count = 42;

Session altered.

SCOTT@test> explain plan for select * from t ;

Explained.

SCOTT@test> @dp

PLAN_TABLE_OUTPUT

---------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |  1029 |  1009K|    38   (3)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| T    |  1029 |  1009K|    38   (3)| 00:00:01 |

--------------------------------------------------------------------------

8 rows selected.

SELECT ceil(blocks / 42 * ( 9 + 42 * 8192 / 88419 ) / ( 9 + 8192 / 88419 ) ) + 1 AS io_cost_formula

FROM user_tables

WHERE table_name = 'T';

IO_COST_FORMULA

---------------

             37

--相差1，非常接近。

3.如果没有设置db_file_multiblock_read_count参数呢？
如果没有设置db_file_multiblock_read_count参数在spfile文件中，db_file_multiblock_read_count自动设置128.

自己也做一个测试：
$ strings spfiletest.ora | grep -i  multi
*.db_file_multiblock_read_count=8

SCOTT@test> alter system reset db_file_multiblock_read_count sid='*';
System altered.

$ strings spfiletest.ora | grep -i  multi

重新启动看看。

SCOTT@test> show parameter db_file_multiblock_read_count

NAME                                 TYPE                                     VALUE

------------------------------------ ---------------------------------------- --------

db_file_multiblock_read_count        integer                                  128

--确实128.

SCOTT@test> explain plan for select * from t ;

Explained.

SCOTT@test> @dp

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |  1029 |  1009K|   144   (1)| 00:00:02 |

|   1 |  TABLE ACCESS FULL| T    |  1029 |  1009K|   144   (1)| 00:00:02 |

--------------------------------------------------------------------------

8 rows selected.

--这样计算的结果144.

--以db_file_multiblock_read_count=128计算.

SELECT ceil(blocks / 128 * ( 9 + 128 * 8192 / 88419 ) / ( 9 + 8192 / 88419 ) ) + 1 AS io_cost_formula

FROM user_tables

WHERE table_name = 'T'

IO_COST_FORMULA

---------------

             20

--以db_file_multiblock_read_count=8计算.

SELECT ceil(blocks / 8 * ( 9 + 8 * 8192 / 88419 ) / ( 9 + 8192 / 88419 ) ) + 1 AS io_cost_formula

FROM user_tables

WHERE table_name = 'T'

IO_COST_FORMULA

---------------

            143

--说明按照db_file_multiblock_read_count＝8来计算，在没有设置db_file_multiblock_read_count的情况下。

4.如果在系统收集设置MBRC呢？

BEGIN
  dbms_stats.delete_system_stats();
  dbms_stats.set_system_stats(pname => 'CPUSPEEDNW', pvalue => 1618);
  dbms_stats.set_system_stats(pname => 'IOSEEKTIM',  pvalue => 9);
  dbms_stats.set_system_stats(pname => 'IOTFRSPEED', pvalue => 88419);
  dbms_stats.set_system_stats(pname => 'MBRC',       pvalue => 128);
END;
/

MBRC=128.

SCOTT@test> explain plan for select * from t ;

Explained.

SCOTT@test> @dp '' ''

PLAN_TABLE_OUTPUT

-----------------------------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |  1029 |  1009K|    21   (5)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| T    |  1029 |  1009K|    21   (5)| 00:00:01 |

--------------------------------------------------------------------------

8 rows selected.

SELECT ceil(blocks / 128 * ( 9 + 128 * 8192 / 88419 ) / ( 9 + 8192 / 88419 ) ) + 1 AS io_cost_formula

FROM user_tables

WHERE table_name = 'T'

IO_COST_FORMULA

---------------

             20

BEGIN
  dbms_stats.delete_system_stats();
  dbms_stats.set_system_stats(pname => 'CPUSPEEDNW', pvalue => 1618);
  dbms_stats.set_system_stats(pname => 'IOSEEKTIM',  pvalue => 9);
  dbms_stats.set_system_stats(pname => 'IOTFRSPEED', pvalue => 88419);
  dbms_stats.set_system_stats(pname => 'MBRC',       pvalue => 42);
END;
/

SCOTT@test> explain plan for select * from t ;

Explained.

SCOTT@test> @dp '' ''

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |  1029 |  1009K|    38   (3)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| T    |  1029 |  1009K|    38   (3)| 00:00:01 |

--------------------------------------------------------------------------

8 rows selected.

SELECT ceil(blocks / 42 * ( 9 + 42 * 8192 / 88419 ) / ( 9 + 8192 / 88419 ) ) + 1 AS io_cost_formula

FROM user_tables

WHERE table_name = 'T'

IO_COST_FORMULA

---------------

             37

--有点误差，基本一致。

5.收尾还原。

BEGIN
  dbms_stats.delete_system_stats();
END;
/

SCOTT@test> alter system set db_file_multiblock_read_count=8 scope=both ;
System altered.

SCOTT@test> show parameter db_file_multiblock_read_count

NAME                                 TYPE                                     VALUE

------------------------------------ ---------------------------------------- ------

db_file_multiblock_read_count        integer                                  8