Day 2 -Oracle数据库规范

©2010OracleCorporation1InsertPictureHereOracle数据库规范VictorLiSeniorConsultant–OracleConsultingService©2010OracleCorporationOracle数据库规范3•数据库模型设计规范•SQL开发规范•数据库管理规范•数据库性能优化建议©2010OracleCorporation建表的参数设置•存储参数PCTFREE-对于经常被UPDATE且被update的列所占字节比较大的表，需要设置较大的PCTFREE，避免造成过多行迁移。-对于可能造成热点块的表，PCTFREE参数需要设置较大，尽量减少热点块发生概率。•事务参数-对并发DML事务很大的表，可以在建表初期设置较大的initrans参数。initrans值过小可能会引起ITL竞争，导致热点块或者enqueue较为严重。4©2010OracleCorporation建表的参数设置•Initrans使用原则•每个initrans在数据块上占用24Bytes空间•Initrans不可以超过10•不可以修改maxtrans值•修改initrans后只对新的数据块起作用，如需要对全部数据起作用需要重新组织表•alterindexxxxinitrans8;•Altertablexxxstorage(freelists8);注意:ASSM表空间上无需设置freelists5©2010OracleCorporation列设计•定长字符型列使用CHAR类型，不定长字符型列使用VARCHAR2类型。•固定长度用char，相对varchar2可以提高查询速度•不定长度用varchar2,如果用char，则会在列末用空格补充，在处理上需要用trim等函数去空格，因此容易造成处理的不便和效率问题。•日期字段需定义为DATE类型。如果定义为varchar2或者char时需要进行转换，影响效率.。需要数据精确到微秒的字段需定义为TIMESTAMP类型。•列为null时，需定义default值，避免因为null而造成索引不能被用到的情况。•使用NUMBER型时必须指定长度。由number得精度和密度来保障数据的一致性6©2010OracleCorporation列设计•表中字段的命名长度不应该超过20个字节。•记录数达到千万级的表，必须进行分区，分区一般遵循以下原则：•数据具有明显的范围属性，比如日期，大小等，且经常进行范围条件查询的表，采用范围分区•数据具有明显的列表属性，比如地点，省份等，且经常用列表条件查询的表，采用列表分区•数据不具有明显的范围属性或者列表属性，且数据量很大，则可以采用hash分区7©2010OracleCorporation临时表•对于只对本事务有效的临时表使用ONCOMMITDELETEROWS关键字创建该表。•对于只对本会话有效的临时表使用ONCOMMITPRESERVEROWS关键字创建该表。•对于临时表空间要求比较大的业务系统，临时表要存储在独立的表空间中，并且临时表空间的数据文件需要放在单独的磁盘上。8©2010OracleCorporation索引•小表（数据量小于5000条记录为标准）不需要创建索引。•对于OLTP应用，分区表使用分区索引。•创建或重建大索引时需指定使用NOLOGGING子句，指定并行度，指定较大的排序区，提高执行效率。•将记录差别数最多的列放在索引顺序的最前面。•对于OLTP应用的业务系统索引数据的重复率不能超过20%。•进行orderbycolumndesc排序时，创建columndesc索引。9EXAMPLE:ALTERSESSIONSETWORKAREA_SIZE_POLICY=MANUAL;ALTERSESSIONSETSORT_AREA_SIZE=2000000000;ALTERSESSIONSETSORT_AREA_RETAINED_SIZE=2000000000;CREATEINDEXXXXNOLOGGINGPARALLELX;ALTERINDEXXXXNOPARALLEL;©2010OracleCorporation索引•频繁使用的index需要放入库缓存的keep池中。对于频繁使用的较小的index(可以根据sgabuffer来确定大小的标准)可以直接放入buffer的keep池，以提高效率。对于较大或者更新比较频繁的表或者索引不建议放在buffer的keep池。对于系统经常访问的小表，比如系统配置表等可以直接修改表和索引的属性，以保留在buffer池中。•在OLTP系统中，少用位图索引；对于更新频繁的表，少用位图索引。10©2010OracleCorporation索引•通过索引查询数据，行数一般不会超过全表的20%，否则，采用全表扫描方式更合适些•10,15,20的原则。通过索引查询数据最好是少于表记录数的20%，否则就最好用全表扫描TomKyte建议在20%•对索引的使用进行监控，可以发现那些索引有用，那些索引没有用到。没有用到的索引可以直接删除。•重建索引的时间最好选择在维护时间窗口，如果一定要在业务处理期间重建索引则使用online选项•如果索引的level不高，且不需要挪动索引的存储位置，可以选择alertindex….coalesce•当索引的clustering_factor接近表的行数时，可以把表数据按照索引列排序后进行重建。以提高索引查询的效率，接近表的block数是最理想的情况。•反向索引不适合于表上含有较多,=,,=范围查询的系统，否则反而会降低系统的整体性能11©2010OracleCorporation分区索引12•通过下图定义的规则，可确定分区索引类型索引字段是表分区字段的前缀？该非分区字段是唯一索引？LocalPrefixed是否性能在可承受范围，而分区的可管理性、可用性更重要？GlobalPrefixedLocalNon-prefixed是数据仓库/交易系统？LocalNon-prefixedGlobalPrefixedYesYesYesNoNoNoOLTPDSS©2010OracleCorporation分区索引•HashGlobal分区索引介绍•HASH-PartitionedGlobal索引是Oracle10g开始提供的新特性。而在以前的版本中，Oracle只支持•Range-PartitionedGlobal索引。HASH-PartitionedGlobal索引的好处如下：•比Range-PartitionedGlobal索引易于实施。HASH-PartitionedGlobal索引是根据索引字段值，通过Oracle内部的HASH算法自动均匀散列到定义的分区中。而Range-PartitionedGlobal索引需要根据索引字段值的范围进行分区，因此实施和维护的难度都大。•HASH-PartitionedGlobal索引适合于在并发量、吞吐量很大的交易系统（OLTP）中，对某些字段的访问冲突。尤其是sequence字段值。•HASH-PartitionedGlobal索引适合于大批量的数据查询。HASH-PartitionedGlobal索引不仅可以提供分区之间的并行查询，而且在分区内也可进行并行查询的处理。•建立分区索引必须指定表空间，并且指定的表空间要与数据表空间分开，这样便于管理，同时尽可能分开索引和数据的IO访问，提高效率13©2010OracleCorporation索引14•索引数量•对于OLTP应用的业务系统，单个表上索引的个数不超过5个。•对于OLTP系统来说，索引数量太多会严重影响DML操作•索引类型选择btreeorbitmap•B-TREE索引，一般情况下选择普通的b-tree索引•位图索引对于列的distinctvalue很少，且数据量较大，且DML操作很少，这种情况可以选择使用位图索引。比如性别，distinctvalue只有男和女且DML操作也会很少，此时选择位图索引，效率可能会更高。©2010OracleCorporation复合索引当需要创建复合索引时，需要慎重考虑列在索引中的顺序，主要影响的是INDEXSKIPSCAN•当查询条件中有第一列时，不需要考虑列的分布情况•当查询条件中没有第一列时，需要考虑列的分布情况。尽量把distinctvalue不多的列放在复合索引的第一列•如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；•不管是单列索引还是复合索引，当列允许为空，且实际有空值时，在查询中可能会不走索引扫描，因为null值不在索引条目中。因此尽可能选择notnull的列做为索引列，如果不能避免时，可以采用列为空时去一个默认值的方法解决。15©2010OracleCorporation索引•函数索引•尽量不采用函数索引，函数索引会在insert以及select时多一次函数计算的消耗。•递减索引•进行orderbycolumndesc排序时，综合考虑，创建columndesc索引，不再额外进行排序操作。16©2010OracleCorporation视图•物化视图的刷新间隔时间最小为3分钟。•物化视图的基表必须创建主键17©2010OracleCorporation存储过程、函数和包•存储过程、函数和包中不允许频繁的使用DDL语句。•存储过程、函数和包必须有相应的出错处理功能。•存储过程、函数和包中变量在引用表字段的时候，需使用％rowtype类型。•rowtype可以方便，容易的定义字段类型，当表结构或者字段类型发生变化是不再修改程序•rowtype在游标使用中可以整行的接受数据定义，而不需要对一行数据的每个字段进行分别定义18©2010OracleCorporationDirectory•目录设置要求与Oracle系统用户目录分开。•Directory使用权限只赋予需要使用的数据库用户。•对应的操作系统目录必须对oracle操作系统用户开放读写权限。•定期清理和备份Directory对应的操作系统目录。19©2010OracleCorporation同义词•对于只读用户，必须创建与表相同名字的别名。•别名的访问顺序：public别名-private别名-与表同名的对象。20©2010OracleCorporationSequence•sequence要求不连续时，需要指定cache和noorder，特别是对于RAC系统而言，sequence的nocache属性可能会带来较大的性能影响。•对于RAC系统，sys.audses$,sys.idgen1$两个sequence的cache值调整为10000或者更高，特别是短连接且logon频率高的系统更需要调整21©2010OracleCorporationLOB对象Oracle对大对象的操作机制象普通数据一样，因此大对象需要被分解成多个小的数据片段才能完成这种数据操作机制，这种小的数据片段就是CHUNK。•使用CLOB/BLOB•不可使用raw/rawlong•分开存储,uniformsizetbs•使用16KCHUNKSIZE•升级到11g的SecureFilesLOB22©2010OracleCorporation数据库安全设计数据库用户安全设计原则•数据库用户权限授权按照最小分配原则。•数据库用户要分为管理、应用、维护、备份四类用户。•不允许使用sys和system用户建立数据库应用对象。•禁止grantdbatouser。23©2010OracleCorporation数据库安全设计序号用户类型描述1DATAOWNER该类用户拥有数据库业务schema对象，特别是tables及package和Procedure以及其他对象。不对应用程序开放。只有通过对象授权和系统授权，Transaction类型用户才可访问DATAOWNER类型用户；表空间的使用，要通过空间授权配额，才可访问。2Transaction该类型用户拥有数据库最小权限。只有通过角色R