【調優】CBO基礎（二）

yellowlee發表於2011-12-27

表掃描

關於掃描我們關注很多因素：

 

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_size對cost值的影響，可以測試2k,4k,8k,16k等不同值，其也是有一定規律的，一般來說隨著block_size的增大，cost值呈降低趨勢。

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16179598/viewspace-714024/，如需轉載，請註明出處，否則將追究法律責任。

相關文章