如何配置不同尺寸的db block size

最新推荐文章于 2023-02-28 14:11:23 发布

clg10051

最新推荐文章于 2023-02-28 14:11:23 发布

阅读量202

点赞数

文章标签：大数据

create tablespace与db block size

http://www.dba-oracle.com/oracle_tips_blocksizes-.htm

-支持2k到16k的data block size,不支持32k;在一些os平台不支持一些特定的db block size
SQL> create tablespace tbs_non_standard datafile 'c:\tbs_non_standard.dbf' size 100m blocksize 32k;

create tablespace tbs_non_standard datafile 'c:\tbs_non_standard.dbf' size 100m blocksize 32k

ORA-00382: 32768 not a valid block size, valid range [2048..16384]

SQL> create tablespace tbs_non_standard datafile 'c:\tbs_non_standard.dbf' size 100m blocksize 16k;

create tablespace tbs_non_standard datafile 'c:\tbs_non_standard.dbf' size 100m blocksize 16k

ORA-29339: tablespace block size 16384 does not match configured block sizes

SQL> alter system set sga_target=3g scope=spfile;

System altered

SQL> select * from v$sga_dynamic_free_memory;

CURRENT_SIZE
------------
           0

SQL> show parameter sga_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_target                           big integer 0

SQL> alter system set db_16k_cache_size=100m;

alter system set db_16k_cache_size=100m

ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00384: Insufficient memory to grow cache

SQL> show parameter cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_size             big integer 0
db_16k_cache_size                    big integer 0
db_2k_cache_size                     big integer 0
db_32k_cache_size                    big integer 0
db_4k_cache_size                     big integer 0
db_8k_cache_size                     big integer 0
db_cache_size                        big integer 1552M
db_flash_cache_size                  big integer 0
db_keep_cache_size                   big integer 0
db_recycle_cache_size                big integer 0

SQL>

SQL> alter system set sga_max_size=3g scope=spfile;

System altered

--重启库

SQL> show parameter cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_size             big integer 0
db_16k_cache_size                    big integer 0
db_2k_cache_size                     big integer 0
db_32k_cache_size                    big integer 0
db_4k_cache_size                     big integer 0
db_8k_cache_size                     big integer 0
db_cache_size                        big integer 1552M
db_flash_cache_size                  big integer 0
db_keep_cache_size                   big integer 0
db_recycle_cache_size                big integer 0

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 3G
sga_target                           big integer 3G

SQL> alter system set db_16k_cache_size=100m;

System altered

SQL> create tablespace tbs_non_standard datafile 'c:\tbs_non_standard.dbf' size 100m blocksize 16k;

Tablespace created

SQL> select tablespace_name,block_size from dba_tablespaces;

TABLESPACE_NAME                BLOCK_SIZE
------------------------------ ----------
SYSTEM                               8192
SYSAUX                               8192
UNDOTBS1                             8192
TEMP                                 8192
USERS                                8192
SELF_LEARN                           8192
T_TBS1                               8192
T_TBS2                               8192
TBS_HANG                             8192
TBS_AUTO                             8192
TBS_NON_STANDARD                    16384

11 rows selected

SQL> create table t_8k(a int) tablespace tbs_auto;

Table created

SQL> begin
2 for i in 1..1000000 loop
3 insert into t_8k values(i);
4 if mod(i,10000)=0 then
5 commit;
6 end if;
7 end loop;
8 end;
9 /

PL/SQL procedure successfully completed

---如下连续运行3次的结果，逻辑读为1577次
SQL> select count(a) from t_8k;

Execution Plan
----------------------------------------------------------
Plan hash value: 576579961

---------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    13 |   293   (5)| 00:00:04 |
|   1 | SORT AGGREGATE    |      |     1 |    13 |            |          |
|   2 |   TABLE ACCESS FULL| T_8K |   870K|    10M|   293   (5)| 00:00:04 |
---------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
       1577 consistent gets
          0 physical reads
          0 redo size
        526 bytes sent via SQL*Net to client
        523 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

SQL>

--创建16 db block size逻辑表大大减少；
SQL> create table t_16k(a int) tablespace tbs_non_standard;

Table created.

22:42:30 SQL> begin
           2 for i in 1..1000000 loop
           3 insert into t_16k values(i);
           4 if mod(i,10000)=0 then
           5   commit;
           6 end if;
           7 end loop;
           8 end;
           9 /


PL/SQL procedure successfully completed

Executed in 120.589 seconds

SQL> select count(a) from t_16k;

Execution Plan
----------------------------------------------------------
Plan hash value: 3599734656

----------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |    13 |   157   (9)| 00:00:02 |
|   1 | SORT AGGREGATE    |       |     1 |    13 |            |          |
|   2 |   TABLE ACCESS FULL| T_16K |   910K|    11M|   157   (9)| 00:00:02 |
----------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
        775 consistent gets
          0 physical reads
          0 redo size
        526 bytes sent via SQL*Net to client
        523 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

--在dml方面二者又有何区别呢

22:47:21 SQL> truncate table t_8k;

Table truncated

Executed in 1.046 seconds

22:50:38 SQL> truncate table t_16k;

Table truncated

Executed in 0.047 seconds

--如下测试16k比8k用时少一些；
22:51:52 SQL> begin
           2 for i in 1..1000000 loop
           3 insert into t_8k values(i);
           4 if mod(i,10000)=0 then
           5   commit;
           6 end if;
           7 end loop;
           8 end;
           9 /


PL/SQL procedure successfully completed

Executed in 112.617 seconds

22:53:45 SQL>
22:54:06 SQL>
22:54:06 SQL>
22:54:06 SQL> ed
22:54:24 SQL>
22:54:26 SQL> begin
           2 for i in 1..1000000 loop
           3 insert into t_16k values(i);
           4 if mod(i,10000)=0 then
           5   commit;
           6 end if;
           7 end loop;
           8 end;
           9 /

PL/SQL procedure successfully completed

Executed in 108.936 seconds

--为了公平及更接近真实情况，清空缓冲池及共享池
22:59:08 SQL> alter system flush buffer_cache;

System altered

Executed in 36.317 seconds

22:59:56 SQL> alter system flush shared_pool;

System altered

Executed in 0.094 seconds

23:01:33 SQL> truncate table t_8k;

Table truncated

Executed in 3.104 seconds

23:02:27 SQL> truncate table t_16k;

Table truncated

Executed in 0.125 seconds

--确实大block size比小block size稍显优势；
23:02:55 SQL> begin
           2 for i in 1..1000000 loop
           3 insert into t_8k values(i);
           4 if mod(i,10000)=0 then
           5   commit;
           6 end if;
           7 end loop;
           8 end;
           9 /


PL/SQL procedure successfully completed

Executed in 119.981 seconds

23:04:56 SQL> alter system flush buffer_cache;

System altered

Executed in 34.242 seconds

23:08:19 SQL> alter system flush shared_pool;

System altered

Executed in 0.031 seconds

23:08:31 SQL>
23:08:31 SQL>
23:08:53 SQL> begin
           2 for i in 1..1000000 loop
           3 insert into t_16k values(i);
           4 if mod(i,10000)=0 then
           5   commit;
           6 end if;
           7 end loop;
           8 end;
           9 /


PL/SQL procedure successfully completed

Executed in 103.663 seconds

大数据块在删除数据方面比小数据块尺寸要花费更多的时间；原因？非常重演中学
23:12:03 SQL> alter system flush buffer_cache;

System altered

Executed in 32.76 seconds

23:12:38 SQL> alter system flush shared_pool;

System altered

Executed in 0.016 seconds

23:12:44 SQL>
23:12:44 SQL> delete from t_8k;

1000000 rows deleted

Executed in 31.809 seconds

23:13:28 SQL> commit;

Commit complete

Executed in 0.016 seconds

23:13:40 SQL> alter system flush shared_pool;

System altered

Executed in 0.015 seconds

23:13:48 SQL> alter system flush buffer_cache;

System altered

Executed in 64.944 seconds

23:14:56 SQL> delete from t_16k;

1000000 rows deleted

Executed in 43.618 seconds

23:15:55 SQL>

--重新插入数据分别到2个表中,大块表比小块表用时稍多一点点；
23:20:55 SQL> begin
           2 for i in 1..1000000 loop
           3 insert into t_8k values(i);
           4 if mod(i,10000)=0 then
           5   commit;
           6 end if;
           7 end loop;
           8 end;
           9 /


PL/SQL procedure successfully completed

Executed in 97.002 seconds

23:22:32 SQL> ed
23:22:50 SQL>
23:22:50 SQL>
23:22:50 SQL>
23:22:51 SQL> begin
           2 for i in 1..1000000 loop
           3 insert into t_16k values(i);
           4 if mod(i,10000)=0 then
           5   commit;
           6 end if;
           7 end loop;
           8 end;
           9 /

PL/SQL procedure successfully completed

Executed in 97.937 seconds

23:24:33 SQL> alter system flush buffer_cache;

System altered

Executed in 84.256 seconds

23:26:35 SQL> alter system flush shared_pool;

System altered

Executed in 0.015 seconds

23:26:49 SQL> update t_8k set a=1;

1000000 rows updated

Executed in 43.867 seconds

23:27:46 SQL> commit;

Commit complete

Executed in 0.015 seconds

23:28:17 SQL> alter system flush buffer_cache;

System altered

Executed in 88.156 seconds

23:29:49 SQL> alter system flush shared_pool;

System altered

Executed in 0.016 seconds

23:29:56 SQL> update t_16k set a=1;

1000000 rows updated

Executed in 46.099 seconds

23:30:54 SQL>

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

转载于:http://blog.itpub.net/9240380/viewspace-756446/

clg10051

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
如何配置不同尺寸的db block size

create tablespace与db block sizehttp://www.dba-oracle.com/oracle_tips_blocksizes-.htm-支持2k到16k的data block size,...
复制链接

扫一扫