分区默认segment大小变化(64k—>8M) , 11gR2中的CELL_PARTITION_LARGE_EXTENTS 参数
来源:互联网 发布:淘宝衣服怎么上架宝贝 编辑:程序博客网 时间:2024/06/09 21:44
CELL_PARTITION_LARGE_EXTENTS now obsolete
During the Exadata course that I am just delivering in Munich, I noticed that the fairly new parameter CELL_PARTITION_LARGE_EXTENTS is already obsolete now:
SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit ProductionPL/SQL Release 11.2.0.2.0 - ProductionCORE 11.2.0.2.0 ProductionTNS for Linux: Version 11.2.0.2.0 - ProductionNLSRTL Version 11.2.0.2.0 - ProductionSQL> alter system set cell_partition_large_extents=true;alter system set cell_partition_large_extents=true*ERROR at line 1:ORA-25138: CELL_PARTITION_LARGE_EXTENTS initialization parameter has been madeobsolete
This parameter was introduced in 11.2.0.1 especially for Exadata Database Machine because the Allocation Unit Size (AU_SIZE) for Diskgroups built upon Exadata Cells is recommended with 4 MB. Large Segments should therefore use a multiple of 4 MB already for their initial extents. Although the parameter was made obsolete, the effect that was achievable with it is still present:
SQL> create table t (n number) partition by range (n) (partition p1 values less than (2));Table created.SQL> insert into t values (1);1 row created.SQL> select bytes/1024/1024 as mb from user_extents where segment_name='T'; MB---------- 8
I inserted before checking USER_EXTENTS because of the 11g New Feature deferred segment creation:
SQL> drop table t purge;Table dropped.SQL> create table t (n number);Table created.SQL> select bytes/1024/1024 as mb from user_extents where segment_name='T';no rows selectedSQL> insert into t values (1);1 row created.SQL> select bytes/1024/1024 as mb from user_extents where segment_name='T'; MB---------- .0625
Notice that only partitioned tables are affected by the 8 MB initial extent behavior. The new hidden parameter _PARTITION_LARGE_EXTENTS (defaults to true!) is now responsible for that:
SQL> alter session set "_partition_large_extents"=false;Session altered.SQL> drop table t purge;Table dropped.SQL> create table t (n number) partition by range (n) (partition p1 values less than (2));Table created.SQL> insert into t values (1);1 row created.SQL> select bytes/1024/1024 as mb from user_extents where segment_name='T'; MB---------- .0625
Notice that the setting of CELL_PARTITION_LARGE_EXTENTS with alter session is silently overridden by the underscore parameter:
SQL> drop table t purge;Table dropped.SQL> alter session set cell_partition_large_extents=true;Session altered.SQL> create table t (n number) partition by range (n) (partition p1 values less than (2));Table created.SQL> insert into t values (1);1 row created.SQL> select bytes/1024/1024 as mb from user_extents where segment_name='T'; MB---------- .0625
The parameter setting of the underscore parameter was still false.
SQL> drop table t purge;Table dropped.SQL> alter session set "_partition_large_extents"=true;Session altered.SQL> alter session set cell_partition_large_extents=false;Session altered.SQL> create table t (n number) partition by range (n) (partition p1 values less than (2));Table created.SQL> insert into t values (1);1 row created.SQL> select bytes/1024/1024 as mb from user_extents where segment_name='T'; MB---------- 8
Conclusion: With 11.2.0.2, partitioned tables get initial extents of 8 MB in size, which is particular useful in Exadata Environments where the ASM AU_SIZE will be 4 MB. But also ordinary Databases are affected – which is probably a good thing if we assume that partitioned tables will be large in size anyway and will therefore benefit from a large initial extent size as well.
Addendum: During my present Exadata course (03-JUN-2012), I saw a similar parameter for partitioned indexes also: _INDEX_PARTITION_LARGE_EXTENTS defaults to FALSE, though. Brief test:
SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit ProductionPL/SQL Release 11.2.0.2.0 - ProductionCORE 11.2.0.2.0 ProductionTNS for Linux: Version 11.2.0.2.0 - ProductionNLSRTL Version 11.2.0.2.0 - ProductionSQL> create table parti (n number) partition by range (n) (partition p1 values less than (2));Table created.SQL> insert into parti values (1);1 row created.SQL> commit;Commit complete.SQL> create index parti_idx on parti(n) local;Index created.SQL> select bytes from user_extents where segment_name='PARTI_IDX'; BYTES---------- 65536SQL> drop index parti_idx;Index dropped.SQL> alter session set "_index_partition_large_extents"=true;Session altered.SQL> create index parti_idx on parti(n) local;Index created.SQL> select bytes from user_extents where segment_name='PARTI_IDX'; BYTES---------- 8388608
So this parameter gives us also 8M sized extents for partitioned indexes, but not by default.
原文连接:http://uhesse.com/2011/03/29/cell_partition_large_extent-now-obsolete/
- 分区默认segment大小变化(64k—>8M) , 11gR2中的CELL_PARTITION_LARGE_EXTENTS 参数
- 为什么linux kernel默认的页面大小是4K,而不是4M或8M?
- 为什么linux kernel默认的页面大小是4K,而不是4M或8M?
- 为什么linux kernel默认的页面大小是4K,而不是4M或8M?(转载一篇文章)
- 11gr2 deferred segment and exp/imp
- Oracle 11gR2新建空表不分配Segment
- RAC5——11gR2以后GI进程的变化
- Oracle 11gR2 deferred segment creation 与 exp/imp 说明
- Oracle 11gR2 deferred segment creation 与 exp/imp 说明
- B,K,M,G,T大小转换
- Oracle 11gR2 List-Range分区实验
- IIS 上传大小设置默认30M
- oracle 11gR2默认密码修改
- es6 中的默认参数
- 64M nandflash分区示意图
- 64M nandflash分区示意图
- MFC中的控件随窗口大小变化
- MFC对话框中的控件随着对话框大小变化而变化
- 栈、堆、静态存储(转自http://blog.itpub.net/16856446/viewspace-612834)
- 分布式事务介绍
- ubuntu中常用软件安装
- js中(function(){…})()立即执行函数写法理解
- 1
- 分区默认segment大小变化(64k—>8M) , 11gR2中的CELL_PARTITION_LARGE_EXTENTS 参数
- 设计模式 - 代理模式
- 【集合】——大总结
- C语言和Java的区别和相同点
- URI和URL的区别
- YYModel源代码分析(一)整体介绍
- 第一个网站
- 电路与Multisim 直流并联电路(基础)与新建结点
- ios开发 UI高级 更新Ruby