ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20131014]System Statistics Gathered.txt

[20131014]System Statistics Gathered.txt

原创 Linux操作系统 作者:lfree 时间:2013-10-14 16:07:12 0 删除 编辑
[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<=1029;

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-774356/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2457
  • 访问量
    6261770