Oracle原厂数据库规范.ppt
《Oracle原厂数据库规范.ppt》由会员分享,可在线阅读,更多相关《Oracle原厂数据库规范.ppt(55页珍藏版)》请在三一办公上搜索。
1、1,Oracle数据库规范,Victor Li,Senior Consultant Oracle Consulting Service,3,Oracle数据库规范 数据库模型设计规范 SQL开发规范数据库管理规范数据库性能优化建议,Oracle Corporation,建表的参数设置 存储参数 PCTFREE-对于经常被UPDATE且被update的列所占字节比较大的表,需要设置较大的PCTFREE,避免造成过多行迁移。-对于可能造成热点块的表,PCTFREE参数需要设置较大,尽量减少热点块发生概率。事务参数-对并发DML事务很大的表,可以在建表初期设置较大的initrans参数。initra
2、ns值过小可能会引起ITL竞争,导致热点块或者enqueue较为严重。,4,Oracle Corporation,建表的参数设置 Initrans使用原则,每个initrans在数据块上占用24Bytes空间Initrans不可以超过10不可以修改maxtrans值修改initrans后只对新的数据块起作用,如需要对全部数据起作,用需要重新组织表 alter index xxx initrans 8;Alter table xxx storage(freelists 8);注意:ASSM表空间上无需设置freelists,5,Oracle Corporation,列设计 定长字符型列使用CHA
3、R类型,不定长字符型列使用VARCHAR2类型。固定长度用char,相对varchar2可以提高查询速度 不定长度用varchar2,如果用char,则会在列末用空格补充,在处理上需要用trim等函数去空格,因此容易造成处理的不便和效率问题。日期字段需定义为DATE类型。如果定义为varchar2或者char时需要进行转换,影响效率.。需要数据精确到微秒的字段需定义为TIMESTAMP类型。列为null时,需定义default值,避免因为null而造成索引不能被用到的情况。使用NUMBER型时必须指定长度。由number得精度和密度来保障数据的一致性,6,Oracle Corporation,
4、列设计 表中字段的命名长度不应该超过20个字节。记录数达到千万级的表,必须进行分区,分区一般遵循以下原则:数据具有明显的范围属性,比如日期,大小等,且经常进行范围条件查询的表,采用范围分区 数据具有明显的列表属性,比如地点,省份等,且经常用列表条件查询的表,采用列表分区 数据不具有明显的范围属性或者列表属性,且数据量很大,则可以采用hash分区,7,Oracle Corporation,临时表 对于只对本事务有效的临时表使用ON COMMITDELETE ROWS关键字创建该表。对于只对本会话有效的临时表使用ON COMMITPRESERVE ROWS关键字创建该表。对于临时表空间要求比较大的
5、业务系统,临时表要存储在独立的表空间中,并且临时表空间的数据文件需要放在单独的磁盘上。,8,Oracle Corporation,9,索引 小表(数据量小于5000条记录为标准)不需要创建索引。对于OLTP应用,分区表使用分区索引。创建或重建大索引时需指定使用NOLOGGING子句,指定并行度,指定较大的排序区,提高执行效率。EXAMPLE:ALTER SESSION SET WORKAREA_SIZE_POLICY=MANUAL;ALTER SESSION SET SORT_AREA_SIZE=2000000000;ALTER SESSION SET SORT_AREA_RETAINED_S
6、IZE=2000000000;CREATE INDEX XXX NOLOGGING PARALLEL X;ALTER INDEX XXX NOPARALLEL;将记录差别数最多的列放在索引顺序的最前面。对于OLTP应用的业务系统索引数据的重复率不能超过20%。进行order by column desc排序时,创建column desc索引。,Oracle Corporation,索引 频繁使用的index需要放入库缓存的keep池中。对于频繁使用的较小的index(可以根据sga buffer来确定大小的标准)可以直接放入buffer的keep池,以提高效率。对于较大或者更新比较频繁的表或者
7、索引不建议放在buffer 的keep池。对于系统经常访问的小表,比如系统配置表等可以直接修改表和索引的属性,以保留在buffer池中。在OLTP系统中,少用位图索引;对于更新频繁的表,少用位图索引。,10,Oracle Corporation,索引 通过索引查询数据,行数一般不会超过全表的20%,否则,采用全表扫描方式更合适些 10,15,20的原则。通过索引查询数据最好是少于表记录数的20%,否则就最好用全表扫描 Tom Kyte建议在20%对索引的使用进行监控,可以发现那些索引有用,那些索引没有用到。没有用到的索引可以直接删除。重建索引的时间最好选择在维护时间窗口,如果一定要在业务处理期
8、间重建索引则使用online选项 如果索引的level不高,且不需要挪动索引的存储位置,可以选择alert index.coalesce 当索引的clustering_factor接近表的行数时,可以把表数据按照索引列排序后进行重建。以提高索引查询的效率,接近表的block数是最理想的情况。反向索引不适合于表上含有较多,=,=范围查询的系统,否则反而会降低系统的整体性能,11,Oracle Corporation,12,分区索引 通过下图定义的规则,可确定分区索引类型索引字段是表分区字段的前缀?,Local PrefixedGlobal PrefixedLocal Non-prefixed,Y
9、esNo该非分区字段是唯一索引?YesNo是否性能在可承受范围,而分区的可管理性、可用性更重要?YesNo是数据仓库/交易系统?,OLTPGlobal Prefixed,DSSLocal Non-prefixed,Oracle Corporation,分区索引 Hash Global分区索引介绍 HASH-Partitioned Global索引是Oracle 10g开始提供的新特性。而在以前的版本中,Oracle只支持 Range-Partitioned Global索引。HASH-Partitioned Global索引的好处如下:比Range-Partitioned Global索引易于
10、实施。HASH-Partitioned Global索引是根据索引字段值,通过Oracle内部的HASH算法自动均匀散列到定义的分区中。而Range-Partitioned Global索引需要根据索引字段值的范围进行分区,因此实施和维护的难度都大。HASH-Partitioned Global索引适合于在并发量、吞吐量很大的交易系统(OLTP)中,对某些字段的访问冲突。尤其是sequence字段值。HASH-Partitioned Global索引适合于大批量的数据查询。HASH-PartitionedGlobal索引不仅可以提供分区之间的并行查询,而且在分区内也可进行并行查询的处理。建立分
11、区索引必须指定表空间,并且指定的表空间要与数据表空间分开,这样便于管理,同时尽可能分开索引和数据的IO访问,提高效率,13,Oracle Corporation,14,索引 索引数量 对于OLTP应用的业务系统,单个表上索引的个数不超过5个。对于OLTP系统来说,索引数量太多会严重影响DML操作 索引类型选择 btree or bitmap B-TREE索引,一般情况下选择普通的b-tree索引 位图索引对于列的distinct value很少,且数据量较大,且DML操作很少,这种情况可以选择使用位图索引。比如性别,distinct value只有男和女且DML操作也会很少,此时选择位图索引,
12、效率可能会更高。,Oracle Corporation,复合索引当需要创建复合索引时,需要慎重考虑列在索引中的顺序,主要影响的是INDEX SKIP SCAN 当查询条件中有第一列时,不需要考虑列的分布情况 当查询条件中没有第一列时,需要考虑列的分布情况。尽量把distinct value不多的列放在复合索引的第一列 如果复合索引所包含的字段超过3 个,那么仔细考虑其必要性,考虑减少复合的字段;不管是单列索引还是复合索引,当列允许为空,且实际有空值时,在查询中可能会不走索引扫描,因为null值不在索引条目中。因此尽可能选择not null的列做为索引列,如果不能避免时,可以采用列为空时去一个默
13、认值的方法解决。,15,Oracle Corporation,索引 函数索引 尽量不采用函数索引,函数索引会在insert以及select时多一次函数计算的消耗。递减索引 进行order by column desc排序时,综合考虑,创建columndesc索引,不再额外进行排序操作。,16,Oracle Corporation,视图 物化视图的刷新间隔时间最小为3分钟。物化视图的基表必须创建主键,17,Oracle Corporation,存储过程、函数和包 存储过程、函数和包中不允许频繁的使用DDL语句。存储过程、函数和包必须有相应的出错处理功能。存储过程、函数和包中变量在引用表字段的时候
14、,需使用rowtype类型。rowtype可以方便,容易的定义字段类型,当表结构或者字段类型发生变化是不再修改程序 rowtype在游标使用中 可以整行的接受数据定义,而不需要对一行数据的每个字段进行分别定义,18,Oracle Corporation,Directory 目录设置要求与Oracle系统用户目录分开。Directory使用权限只赋予需要使用的数据库用户。对应的操作系统目录必须对oracle操作系统用户开放读写权限。定期清理和备份Directory对应的操作系统目录。,19,Oracle Corporation,同义词 对于只读用户,必须创建与表相同名字的别名。别名的访问顺序:p
15、ublic别名-private别名-与表同名的对象。,20,Oracle Corporation,Sequence sequence要求不连续时,需要指定cache和noorder,特别是对于RAC系统而言,sequence 的nocache属性可能会带来较大的性能影响。对于RAC系统,sys.audses$,sys.idgen1$两个sequence的cache值调整为10000或者更高,特别是短连接且logon频率高的系统更需要调整,21,Oracle Corporation,LOB对象Oracle对大对象的操作机制象普通数据一样,因此大对象需要被分解成多个小的数据片段才能完成这种数据操作
16、机制,这种小的数据片段就是CHUNK。使用CLOB/BLOB 不可使用raw/raw long 分开存储,uniform size tbs 使用16K CHUNK SIZE 升级到11g的SecureFiles LOB,22,Oracle Corporation,数据库安全设计数据库用户安全设计原则 数据库用户权限授权按照最小分配原则。数据库用户要分为管理、应用、维护、备份四类用户。不允许使用sys和system用户建立数据库应用对象。禁止grant dba to user。,23,Oracle Corporation,数据库安全设计,24,Oracle Corporation,数据库用户角色
17、及权限设计规范,删除或锁定数据库测试用户scott。修改可用用户的默认密码。对查询用户只能开放查询权限。对新建用户初次登录数据库强制修改密码。开发环境,测试环境,生产环境中相同用户的权限设置必须完全一致。,25,Oracle Corporation,数据库用户角色及权限设计规范,26,Oracle Corporation,27,Oracle数据库规范 数据库模型设计规范 SQL开发规范数据库管理规范数据库性能优化建议,Oracle Corporation,SQL开发规范 Select语句中不可以用*,必须select字段列表,以节省内存,提高效率 避免频繁commit,尤其是把commit 写
18、在循环体中每次循环都进行commit。避免在一个事务中出现2此commit的现象。如果执行一半执行一次commit,执行完另外一半又执行一次commit like 子句尽量前端匹配,如写成 like string%,不要写成%string%。批量insert 大数据量时可以采用append和nologging方式,提高处理速度 exp时可以采用direct=y,index=no的方式提高处理效率,imp时可以指定较大的buffer。如果是oracle 10g以上则可以使用expdp和impdp来提高处理的速度 exp和imp时,如果系统中存在主外键约束,在imp时可以设置constraints
19、=n,避免在imp时因为主外键而报错。,28,Oracle Corporation,SQL开发规范 在使用for update子句时一定注意限制条件,避免锁定全表或者不需要被锁定的行记录。readonly 的情况不能用for update。PLSQL开发中一定注意解析的问题。比如execute immediate select.from table where xx=using 而不要直接写成execute immediate 尽量少用not exist/not in等否则写法。如果一定要用时,尽量选择not exist,not in可能用不到index,not exist效率更高,速度更快。
20、exist和in的选择一般遵循以下原则 当查询的数据较少或者字段值比较时用in比较好,比如select*from t wheret.id in(.);当查询的值比较多或者是子查询时用exist比较好,比如select*from t whereexist(select.)exist中的子查询用常量是速度比select*快,比如select*from t where exist(select 1 from),29,Oracle Corporation,SQL开发规范 尽量不用select嵌套写法,如select from tb1,(selecttb2)尽可能把select的嵌套转化为连接方式,如s
21、elect.From tb1,tb2 尽量少用is null/is not null等null 的处理。对于大表查询中的列项应尽量避免进行诸如to_char()、to_date()、to_numbre()等转换。尽量避免进行全表扫描,限制条件尽可能多,以便更快搜索到要查询的数据 在做全表扫描时,可以先修改session的db_file_multiple_read_count值再进行全表扫描以提高执行效率。alter session set db_file_multiple_read_count=32.,30,Oracle Corporation,SQL开发规范 创建database 对象的时候
22、,对象名不能用“”引上,否则可能会带来找不到对象的问题。hash join时注意表连接顺序。且需要注意选择合适的hash key,选择distinct value很多的字段来作为hash key,如果重复值太多,在进行hash探测的时候效率将会很低。hash join较多的系统,需要设置较大的pga_aggregate_target参数,以便进行最优的hash join 收集统计信息时,尽量在维护时间段进行,否则会造成大面积SQL重新解析,可能导致较为严重的问题。在parallel查询的使用过程中,尽量指定hint来使用而不是在表上加degree参数,这样会导致对该表的所有查询都将偏向于走全表
23、扫描,同时可能会带来内存的大量消耗。对于并行度的问题,不繁忙的系统可以设置并行度为CPU的个数,对于繁忙的OLTP系统,最好不要使用并行查询。在SQL开发的过程中,SQL的字母大小写采用一致规范,避免因为大小写的问题造成SQL的多次解析。,31,Oracle Corporation,SQL开发规范 in 替换or,比如select*from t where id=1 or id=2 or id=3,select*from twhere id in(1,2,3)union 与or,union有时比or更有效,or有时比union更有效,综合union替换or或者or 替换union后是否能用到索
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 数据库 规范
链接地址:https://www.31ppt.com/p-5442102.html