oracle个模块设置参数,ORACLE 块参数设置

// db_block_size数据块大小

// 默认8kb

SQL> show parameter db_block_size;

NAME                                 TYPE        VALUE

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

db_block_size                        integer     8192

// db_file_multiblock_read_count 表示一次从物理存储中读取数据块的数量。

// 默认每次做多读取16块

SQL> show parameter db_file_multiblock_read_count;

NAME                                 TYPE        VALUE

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

db_file_multiblock_read_count        integer     16

// 修改读取的块数

SQL> alter session set db_file_multiblock_read_count=16;

// 查询TEST表有多少块

SQL> SELECT segment_name, segment_type, blocks FROM DBA_SEGMENTS WHERE segment_name='TEST';

SEGMENT_NAME                                                                      SEGMENT_TYPE           BLOCKS

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

TEST                                                                              TABLE                      24

// 所占的存储空间大小24*8192(8*1024)=196608

SQL> SELECT segment_name AS TABLENAME,BYTES FROM user_segments WHERE segment_name='TEST';

TABLENAME                                                                              BYTES

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

TEST                                                                                  196608

SQL>  ANALYZE TABLE TEST ESTIMATE STATISTICS;

表已分析。

// 行数，已用块数，空闲块数

SQL> SELECT table_name,num_rows,blocks,empty_blocks FROM user_tables WHERE TABLE_NAME='TEST';

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS

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

TEST                                10000         20            3

// 从行数的角度考虑表的大小。

SQL> select num_rows * avg_row_len from user_tables where table_name='TEST';

SQL> select num_rows,avg_row_len from user_tables where table_name='TEST';

NUM_ROWS*AVG_ROW_LEN

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

70000

//

// arraysize是指读取数据时一次读取得到的行数。这个值默认为15

SQL> SHOW arraysize;

arraysize 15