Can the performance of Istroke O be improved by adding db_block_size
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
