ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【调优】CBO基础(二)

【调优】CBO基础(二)

原创 Linux操作系统 作者:yellowlee 时间:2011-12-27 19:10:32 0 删除 编辑

表扫描

关于扫描我们关注很多因素:

 

Block size

db_file_multiblock_read_count

表空间管理方式和extent扩展方式和大小

空闲空间管理方式

Optimizer_mode

系统统计信息

 

多块读参数

db_file_multiblock_read_count

 

在其他条件不变的情况测试这个参数对cost的影响(oracle 11.1.0.6

SQL> show parameter db_file_multiblock_read_count

 

NAME                                 TYPE        VALUE

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

db_file_multiblock_read_count        integer     128

SQL>

 

create table t_test_tb1 as select * from dba_objects a ;

 

 

统计信息如下:

db_file_multiblock_read_count

Cost

Consistent gets

physical reads

1

1392

1432

1361

4

524

1432

1361

8

379

1365

1361

16

307

1432

1361

32

271

1365

1361

64

252

1432

1361

128

243

1432

1361

256

239

1432

1361

 

例如:

SQL> alter system flush buffer_cache;

 

系统已更改。

 

SQL> alter session set db_file_multiblock_read_count=64;

 

会话已更改。

 

SQL> select max(object_id) from t_test_tb1 a ;

 

MAX(OBJECT_ID)

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

        113421

 

 

执行计划

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

Plan hash value: 3620065802

 

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

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

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

|   0 | SELECT STATEMENT   |            |     1 |    13 |   252   (1)| 00:00:04 |

|   1 |  SORT AGGREGATE    |            |     1 |    13 |            |         |

|   2 |   TABLE ACCESS FULL| T_TEST_TB1 | 89843 |  1140K|   252   (1)| 00:00:04 |

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

 

Note

-----

   - dynamic sampling used for this statement

 

 

统计信息

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

          5  recursive calls

          0  db block gets

       1432  consistent gets

       1361  physical reads

          0  redo size

        427  bytes sent via SQL*Net to client

        416  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

SQL>

 

 

table的详细信息:

SQL> SELECT A.BLOCKS, A.EXTENTS, A.HEADER_BLOCK, A.BYTES

  2    FROM DBA_SEGMENTS A

  3   WHERE A.SEGMENT_NAME = UPPER('t_test_tb1');

 

    BLOCKS    EXTENTS HEADER_BLOCK      BYTES

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

      1408         26        76011   11534336

 

SQL> select count(*) from t_test_tb1 a ;

 

  COUNT(*)

----------

     91502

 

对于128的参数值来说,每个cost可以读取的block数:

SQL> Select ceil(1408/243) from dual;

 

TRUNC(1408/243,1)

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

              6

 

验证一下这个公式:

 

SQL> select max(object_id) from t_test_tb2 a ;

 

MAX(OBJECT_ID)

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

         70620

 

 

执行计划

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

Plan hash value: 3156419431

 

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

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

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

|   0 | SELECT STATEMENT   |            |     1 |    13 |    80   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE    |            |     1 |    13 |            |          |

|   2 |   TABLE ACCESS FULL| T_TEST_TB2 | 28071 |   356K|    80   (0)| 00:00:01 |

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

 

Note

-----

   - dynamic sampling used for this statement

 

 

统计信息

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

         48  recursive calls

          0  db block gets

        506  consistent gets

        434  physical reads

          0  redo size

        427  bytes sent via SQL*Net to client

        416  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

SQL>

SQL> SELECT A.BLOCKS, A.EXTENTS, A.HEADER_BLOCK, A.BYTES

  2    FROM DBA_SEGMENTS A

  3   WHERE A.SEGMENT_NAME = UPPER('t_test_tb2');

 

    BLOCKS    EXTENTS HEADER_BLOCK      BYTES

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

       512         19        77675    4194304

 

计算值:

512/6= 86

 

实际值:

80

 

再看一个例子:

create table t_test_tb3 as select * from dba_objects union all select * from dba_objects ;

SELECT A.BLOCKS, A.EXTENTS, A.HEADER_BLOCK, A.BYTES

  FROM DBA_SEGMENTS A

 WHERE A.SEGMENT_NAME = UPPER('t_test_tb3');--2816

select ceil(2816/6) from dual;--470

SQL> select max(object_id) from t_test_tb3 a ;

 

MAX(OBJECT_ID)

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

        113424

 

 

执行计划

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

Plan hash value: 3109043199

 

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

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

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

|   0 | SELECT STATEMENT   |            |     1 |    13 |   485   (1)| 00:00:06 |

|   1 |  SORT AGGREGATE    |            |     1 |    13 |            |          |

|   2 |   TABLE ACCESS FULL| T_TEST_TB3 |   202K|  2571K|   485   (1)| 00:00:06 |

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

 

Note

-----

   - dynamic sampling used for this statement

 

 

统计信息

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

         48  recursive calls

          0  db block gets

       2808  consistent gets

       2724  physical reads

          0  redo size

        427  bytes sent via SQL*Net to client

        416  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

SQL>

 

计算值:

select ceil(2816/6) from dual;--470

 

实际值:

485

 

那么,这个系统的表扫描的cost计算则可以大致量化出来。很多时候这种可以量化的值需要收集起来,无论是系统架构师还是dba或者开发人员,对自己的系统的能力有了详细了解,那么在考虑性能问题的时候也有一些依据。

 

 

前面说道block_size也会影响表扫描,具体的,也可以做一些测试,看看不同的block_sizecost值的影响,可以测试2k,4k,8k,16k等不同值,其也是有一定规律的,一般来说随着block_size的增大,cost值呈降低趋势。

 

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

请登录后发表评论 登录
全部评论

注册时间:2008-12-27

  • 博文量
    316
  • 访问量
    655016