Shulou(Shulou.com)06/01 Report--

What I want to share with you in this article is about whether adding db_block_size can improve the performance of Ican. the editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article. Let's take a look at it.

In a performance tuning exchange, I heard an expert say that in the default db_block_size=8K environment, adding additional db block size=32K table space, and then moving the table (FTS) or index (FIS) that requires a full table scan to that table space can improve the Imao performance of full table / index scan.

It sounds reasonable. Db block size has increased, and the data contained in each block has increased. When the db_file_multiblock_read_count is unchanged, the db_block_size * db_file_multiblock_ count value increases, and the data read by the db_file_multiblock_read_count O increases, so the performance of the block O is improved. In fact, this is a misconception.

Tablescan is clearly introduced in the second chapter of jonathan lewis "CBO Foundation". The following is a test based on oracle 10.2.0.1 under the AIX5.3 platform:

1. Basic introduction

1. FTS Cost = 1 + HWM/dbf_mbrc = > dbf_mbrc=HWM/ (cost-1)

2. Default db_file_multiblock_read_count=16, db_block_size=8

3. Create the tablespace of test_32K. The segment management of tablespace is manual.

SQL > alter system set db_cache_size = 3034M; (decrease)

SQL > alter system set dba_32k_cache_size=512M

SQL > create tablespace test_32K datafile

'/ home/XXXX/oracle/oradataXXXX/test_32K' size 200m

Blocksize 32K segment space management manual

2. Experiment

1. Create table T1 at normal block size = 8k

SQL > create table T1

Pctfree 99

Pctused 1

As

With generator as (

Select-+ materialize

Rownum id

From all_objects

Where rownum null

Method_opt= > 'for all columns size 1'

);

End

/

3. Calculate the dbf_ MBRC values corresponding to different db_file_multiblock_read_count of the platform.

Alter session set events' 10053 trace name context forever, level 2'

Alter session set db_file_multiblock_read_count=2

Select / * + nocpu_costing * / count (*) from T1

-- Cost_io: 3836

-- # Blks: 10143

-- adjusted dbf_mbrc=HWM/ (cost-1) = 10143 / (3836-1) = 2.645

Alter session set db_file_multiblock_read_count=4

Select / * + nocpu_costing * / count (*) from T1

-- Cost_io: 2431

-- # Blks: 10143

-- adjusted dbf_mbrc= 10143 / (2431-1) = 4.174

Alter session set db_file_multiblock_read_count=8

Select / * + nocpu_costing * / count (*) from T1

-- Cost_io: 1541

-- # Blks: 10143

-- adjusted dbf_mbrc= 10143 / (1541-1) = 6.586

Alter session set db_file_multiblock_read_count=16

Select / * + nocpu_costing * / count (*) from T1

Cost_io: 977

-- # Blks: 10143

-adjusted dbf_mbrc= 10143 / (977-1) = 10.392

Alter session set db_file_multiblock_read_count=32

Select / * + nocpu_costing * / count (*) from T1

Cost_io: 620

-- # Blks: 10143

-adjusted dbf_mbrc= 10143 / (620-1) = 16.386

Alter session set events' 10053 trace name context off'

Db_file_multiblock_read_count Adjusted dbf_mbrc

2 2.645

4 4.174

8 6.586

16 10.392

32 16.386

The size of a single read by Imax O = 8K*16=128K

4. Create a test table in the tablespace of db_block_size=32K

SQL > create table t1room32k

Pctfree 99

Pctused 1

Tablespace test_32K

As

With generator as (

Select-+ materialize

Rownum id

From all_objects

Where rownum

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.