ORACLE数据库部署方案设计 115页

'737091d41a9137da7440670fd738a25d.doc.0ORACLE数据库部署方案设计修改记录编号日期描述版本作者审核发布日期12008-04-15建立文档1.0聂大威开放系统平台管理部22008-04-21至23版本更新1.1聂大威开放系统平台管理部32008-05-12至14版本更新1.3聂大威开放系统平台管理部42008-05-26至27版本更新1.4聂大威开放系统平台管理部52008-06-18版本更新1.5聂大威开放系统平台管理部62009-03-19版本更新:细化了参数设置等1.6贺耀东开放系统平台管理部本文档中所包含的信息属于jimi信息，如无中国建设银行的书面许可，任何人都无权复制或利用。®CopyRight2005byChinaConstructionBank1北京数据中心开放平台管理部第页共115页 737091d41a9137da7440670fd738a25d.doc61前言1.1编写目的为总结我中心开放系统建设的成果，加强开放系统平台建设工作的规范化管理，我们梳理了开放系统平台基础设施设计的相关文档，并进行了深化、细化，力求结合实际的设计、实施工作，对设计、实施起到规范、指导作用。本指南主要从一个设计者的角度进行阐述，相关章节也按此思路编写。作为一个设计者，首先要了解产品可实现的部署模式，如何选择部署模式，其次要考虑设计涉及到的因素，有针对性地做好数据库的设计等；为提高数据库的性能，对程序开发提出了的要求。在界线的划分上，基础产品只涉及本产品的设计，上层应用产品对基础产品的需求放在应用产品中，例如，ORACLE部署对AIX的要求，放在ORACLE设计指导中。在编写过程中，特别关注可操作性，不仅仅是要求，而是提出建议，尽量覆盖设计工作中涉及的工作要点。本指南中参数建议值是对系统设计时的指导，是合理的经验值，但由于应用系统的复杂性，每个系统有自己的特点，建议按建议值进行系统的初始配置，在压力测试和系统上线后根据实际需要做相应的调整。附件中列出了ERP/CLPM/CCBSBS/EBANK四个系统的oracle数据库配置参数以及相应的AIX、HP系统配置参数，作为系统设计的参考。1.2预期读者项目基础设施可行性研究、设计和实施人员，项目组应用系统设计人员，相关运行维护技术人员。北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc61.1数据库部署模式1.2单机模式数据库服务器采用单服务器模式，满足对可用性和性能要求不高的应用，具备以下特点:1、硬件成本低。单节点，硬件投入较低，满足非重要系统的需求。2、安装配置简单。由于是单节点、单实例，所以安装配置比较简单。3、管理维护成本低。单实例，维护成本低。4、对应用设计的要求较低。由于是单实例，不存在RAC系统应用设计时需要注意的事项，所以应用设计的要求较低。5、可用性不高。由于是单服务器、单实例，所以服务器和实例的故障都会导致数据库的不可用。6、扩展性差。无法进行横向扩展，只能进行纵向扩展。当应用对性能有更高的要求时，该模式的数据库服务器无法进行增加节点、实例等横向扩展，只能进行增加硬件配置等纵向扩展，且扩展性有局限。7、根据该模式的特点有如下要求：1)硬件配置方面预留扩展量。由于该模式无法进行横向扩展，所以在选择硬件配置时要为以后的纵向扩展预留扩展量，避免硬件无法满足性能需求的情况。2)充分考虑该模式是否满足应用未来一段时间的需求。需要考虑应用在未来一段时间是否会发生变化，该模式是否满足应用变化的需求。1.3HA热备模式数据库服务器采用HA热备模式，可以满足对可用性有一定要求的应用，具备以下特点：1、需要冗余的服务器设备。该模式需要有冗余的服务器硬件，以满足一备一或者一备多的需求。硬件成本较高。2、需要HA软件的支持。该模式需要配合HA软件才可以实现。3、北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6安装配置相对简单。该模式比单节点、单实例的模式配置复杂一些，需要更多的配置步骤，但相比较RAC、DATAGUARD等模式要简单。1、管理维护成本低。单实例，对维护人员的要求较低，维护成本低。2、对应用设计的要求较低。由于是单实例，不存在RAC系统应用设计时需要注意的事项，所以应用设计的要求较低。3、具备一定的高可用性。由于是多服务器、单实例，所以服务器和实例有故障时会发生实例在不同服务器上的切换，导致数据库的暂时不可用。无法满足对可用性有严格要求的应用类型。4、扩展性差。无法进行横向扩展，只能进行纵向扩展。当应用对性能有更高的要求时，该模式的数据库服务器无法进行增加节点、实例等横向扩展，只能进行增加硬件配置等纵向扩展，且扩展性有局限。根据该模式的特点有如下要求：1)硬件配置方面预留扩展量。由于该模式无法进行横向扩展，所以在选择硬件配置时要为以后的纵向扩展预留扩展量，避免硬件无法满足性能需求的情况。2)充分考虑该模式是否满足应用未来一段时间的需求。需要考虑应用在未来一段时间是否会发生变化，该模式是否满足应用变化的需求。1.1RAC模式数据库服务器采用RAC模式，满足对高可用性要求高的应用类型，具备以下特点:1、需要多个硬件服务器。根据节点的个数，相应的需要多个硬件服务器。硬件成本较高。2、某些数据库版本需要HA软件的支持。该模式下，某些数据库版本需要配合HA软件才可以实现。3、安装配置复杂。该模式比起单实例模式，安装配置相对复杂，安装配置周期长。4、管理维护成本高。该模式的管理维护，对管理维护人员的要求较高，管理维护成本较高。5、北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6对应用设计的要求较高。需要充分考虑业务的逻辑性，以避免在多节点之间的信息交换和全局锁的产生。1、具备较高的高可用性。由于是多服务器、多实例，单服务器和实例有故障不会影响数据库的可用性。可以满足对可用性有严格要求的应用类型。2、扩展性好。既可以进行横向扩展，也可以进行纵向扩展。当应用对性能有更高的要求时，该模式的数据库可以通过增加节点的方式进行横向扩展，也可以通过增加硬件配置等纵向扩展，具备良好的扩展性。根据该模式的特点有如下要求：1、硬件配置方面预留扩展量。预留一定的硬件扩展量，可以更灵活的进行扩展。2、在应用设计时，充分考虑业务逻辑，减少多节点间的信息交换量，更好的发挥RAC的优点。1.1DATAGUARD模式数据库服务器采用DATAGUARD灾备模式，可以满足对可用性有特殊需求的应用，具备以下特点：1、需要冗余的服务器设备。该模式需要有冗余的服务器硬件。硬件成本较高。2、需要冗余的存储设备。主机和备机都需要同样的存储空间，成本较高。3、安装配置比较复杂。该模式比单节点、单实例的模式配置复杂一些，需要更多的配置步骤。4、管理维护成本高。该模式对维护人员的要求较高，维护成本高。5、具备一定的容灾特性。当主机整个数据库系统不可用并短期内无法恢复时，可以把数据库系统切换到备机上，具备容灾的功能。6、备机可以用作只读查询。备机可以切换到只读状态供报表之类的查询操作，减轻主机的压力。根据该模式的特点有如下要求：1、主机与备机在物理上要分开。为了实现容灾的特性，需要在物理上分割主机和备机。北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc61、进行合理的设计，充分实现DATAGUARD的功能。1.1RAC+DATAGUARD模式数据库服务器采用RAC+DATAGUARD模式，可以满足对可用性和容灾都有特定需求的应用，具备以下特点：1、需要冗余的服务器设备。该模式需要有冗余的服务器硬件。硬件成本较高。2、需要冗余的存储设备。主机和备机都需要同样的存储空间，成本较高。3、安装配置比较复杂。该模式既需要配置RAC又需要配置DATAGUARD，配置过程比较复杂，配置周期长。4、管理维护成本高。该模式对维护人员的要求较高，维护成本高。5、具备很高的可用性和容灾性。该模式既满足高可用性也满足容灾的需求。6、备机可以用作只读查询。备机可以切换到只读状态供报表之类的查询操作，减轻主机的压力。根据该模式的特点有如下要求：1、主机与备机在物理上要分开。为了实现容灾的特性，需要在物理上分割主机和备机。2、进行合理的设计，充分实现DATAGUARD的功能。2数据库部署模式选择建议2.1部署模式的选择建议在设计数据库时必须考虑系统的可用性、业务连续性要求，针对系统的可用性需求，采用不同的数据库部署模式：1、对RTO=0、RPO=0的系统，建议数据库采用RAC或RAC+DataGuard模式，数据库单台设备故障时对业务没有影响，并考虑灾备系统的设计。2、北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6对RTO<=4小时，RPO<15分钟的系统，建议数据库采用HA热备或DataGuard的模式，设备故障时通过HA技术切换到备用设备，保证系统的可用性，对重要的系统要考虑灾备的设计。1、对4小时1G时默认为819267584设定打开索引节点的最大数量，此值最小值为14，最大值则限于系统内存大小。(8*NPROC+2048)MAXUPRC2567374设定用户进程数量的最大值，此值必须设置为：3到nproc-5之间((NPROC*9)/10)+1MSGMNI5128192设定系统允许消息队列标识符的最大数，必须设置为：1到1000000之间(NPROC)MSGTQL10248192设定系统允许消息的最大数，此值必须设置为：1到2147483647之间(NPROC)NCSIZE897668608设定索引节点所需的目录名查找高速缓存（DNLC）空间(NINODE+1024)NFLOCKS此值根据系统内存大小初定默认值，当内存<=1G时默认为1200；当内存>1G时默认为40968192设定系统上可用文件锁的最大数量。此值须设置为50-16777216(NPROC)SEMMNI20488192设定整个系统信号量集的最大数量。此值须设置为：2到semmns之间，(NPROC)SEMMNS409616384设定整个系统信号量的数量.此值须设置为：semmni到335534080之间(SEMMNI*2)SEMMNU2568188设定信号量undo结构的数量。此值须设置为：1到nproc-4之间。(NPROC-4)北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6SHMMAX1G可用内存数量设定一个共享内存段的最大允许尺寸。SHMMAX设定值应足够大，以便在一个共享内存段中装下整个SGA。设置过低的结果是创建多个共享内存段，这样会降低性能。此值须设置为：2k到4TB之间，此值的设定请根据系统内存容量以及应用需要综合考虑设置。　SHMMNI400512设定整个系统中共享内存段的最大数量。此值须设置为：3到32768之间　VPS_CEILING16(KB)64设定由系统选择的页面的最大尺寸，以KB为单位。此值须设定为4(KB)到4194304(KB)之间。　以上参数针对HP11.311数据库设计考虑的因素1.1数据库类型特点分析在创建和规划一个Oracle数据库之前，首要任务应确定将来投产的数据库属于何种业务类型。目前的应用业务有以下类型：1、OLTP(OnlineTransactionProcessing)2、OLAP（OnlineAnalytiaclProcessing）3、BATCH4、DSS（DecisionSupportSystem）5、Hybrid1.1.1OLTP（联机事务处理）OLTP数据库支持某种特定的操作，OLTP系统是一个包含繁重及频繁执行的DML应用，其面向事务的活动主要包括更新，同时也包括一些插入和删除。经典的例子是预定系统或在线时时交易系统，例如网上银行和ATM自动取款机系统。OLTP系统可以允许有很高的并发性（在这种情况下，高并发性通常表示许多用户可以同时使用一个数据库系统）。北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc61.1.1OLAP（联机分析处理）OLAP系统可提供分析服务。这意味着数学、统计学、集合以及大量的计算，一个OLAP系统并不永远适合OLTP或DSS模型，有时它是两者之间的交叉。另外，也可以把OLAP看作是在OLTP系统或DSS之上的一个扩展或一个附加的功能层次。通常，地理信息系统或有关空间的数据库和OLAP数据库相集成，提供图表的映射能力。用于社会统计的人口统计数据库就是一个很好的例子。1.1.2BATCH（批处理系统）批作业处理系统是作用于数据库的非交互性的自动应用。它通常含有繁忙DML语句并有较低的并发性（在这种情况下，较低的并发性通常表示少数几个用户能够同时使用一个数据库系统），该业务系统会在某一时段，大批量数据（少则几万，多则几十万，几百万条数据）更新/插入/删除该数据库。事务查询的比率决定了如何物理地设计它，经典的例子是与DW有关的成品数据库和可操作数据库，如：操作型数据存储系统（ODS）。1.1.3DSS（决策支持系统）DSS系统通常是一个大型的、包含历史性内容的只读数据库，通常用于简单的固定查询或特别查询。DSS常常按某种方式变成一个VLDB（VeryLargeDatabase）或DW（DataWarehouse）。VLDB的例子如：企业资源管理财务系统（ERP）数据库，该数据库是一个长期存储数据库的历史数据库；DM的例子如：整个集团的工资和人事数据库。1.1.4Hybrid（混合类型系统）北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6同时数据库系统的应用类型可能是OLTP、OLAP、BATCH等的混合体。也意味着同时拥有上述业务类型特征，这就要求数据库管理员、应用系统分析员、操作系统管理员整体统筹考虑各种业务性能需求及功能需求，对这个系统制定出满足各种业务类型需求的规划，如：企业客户信息整合（ECIF）系统。1.1数据库规模对于数据库的规模，仅从数据量来衡量其规模的大小。因为数据量的规模是反映数据库规模的主要指标。具体如下：1、数据库业务数据量小于100GB属小规模数据库2、数据库业务数据量100GB-600GB属中等规模数据库3、数据库业务数据量600GB-1TB属大规模数据库4、数据库业务数据量大于1TB属超大规模数据库2数据库部署前提建议2.1数据库产品选择建议Oracle数据库产品推出新的主要版本后，要经历一个版本不稳定期。在此期间新版的数据库产品存在较多的bug。在安装和运行过程中，会存在数据库部署安装困难和运行出现不稳定现象。因此在选择版本时，要选择成熟稳定的版本。具体安装要求须参照‘Oracle版本策略最新版’。2.2磁盘阵列布局原则随着硬件技术的发展，目前磁盘阵列的使用变得越来越普遍，由于磁盘阵列和单个磁盘具有较大的不同，故此在数据库的物理划分上也有较大的不同。对于磁盘阵列系统，由于RAID的划分，不存在一个个真实的物理盘，对应的是物理卷(PV)，逻辑卷组(VG)，逻辑卷(LV)。在这种情况下Oracle推荐使用SAME技术，即全部镜像和条带化(StripeAndMirror北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6Everything)。在对磁盘阵列做SAME处理后，所有的逻辑卷都分布在所有的物理磁盘上，每个逻辑卷的读写都能够利用的到所有的物理磁盘的吞吐能力，同时获得较高的可靠性。同时我们在使用磁盘设备的时候不需要考虑各个不同文件的IO情况，因为它们都使用同样的全部磁盘的吞吐能力，这进一步简化了数据库系统的文件管理工作，避免一些意外的操作。对较重要、并且效率要求较高的系统推荐使用RAID0+1的磁盘配置而不使用RAID5，因为RAID5的校验技术会降低应用数据库系统的效率。但使用RAID0+1，比RAID5需要更多物理磁盘。不同的类型对象，尽量分布在不同的卷组上，建议：1、表对应的数据和索引分别放置在不同的物理磁盘上；2、控制文件的多个备份分别放置在不同的物理磁盘上；3、REDO日志组的多个成员放置在不同的物理磁盘上；4、建议将Oracle文件、SYSTEM表空间、TEMPORARY表空间、UNDO表空间放置在不同的物理磁盘上；1数据库物理结构设计1.1软件安装路径及环境变量建立单独的文件系统来安装数据库软件，且文件系统的mount点不要直接建立在根目录下。安装路径：/home/db/oracle各种环境变量设置：ORACLE_BASE=/home/db/oracleCRS_HOME=/home/db/oracle/crs/{数据库release版本}，如/home/db/oracle/crs/10.2.0ORACLE_HOME=/home/db/oracle/product/{数据库release版本}，如/home/db/oracle/product/10.2.0当前(2010.3)推荐版本为10gR2，写为10.2.0,下一个版本（计划从2011.5开始推荐）为11gR2，写为11.2.0,北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc61.1数据库实例的命名规则普通使用模式的Oracle数据库的服务名和实例名（SID）是相同的；RAC模式下的Oracle数据库的服务名与实例名不同。数据库服务名的命名格式为：XXXYYdb{m}数据库的SID的命名格式为：XXXYYdb{m}{n}说明：1、其中XXX表示长度为3个字符的应用项目缩写，具体的见相关设计文档。2、YY：代表数据库用途，pd代表生产库，hi代表历史库，rp代表报表库，cf代表配置库；3、m表示数据库序号，从0-9，根据项目的数据库数量进行编号。4、n表示RAC节点实例序号1,2,3……。用以区分多节点的RAC数据库的不同实例。对于普通模式的数据库，该位不指定。1.2表空间设计1.2.1业务数据量的估算估算所有业务SCHEMA下的所有table的尺寸。数据量估算的前提：l数据库的物理表结构已经确定，并且设计已凝固。l用户方提供较为准确的估算依据，例如业务变动的频率、数据需要保存的周期等。该表是一个示例，可根据业务的不同有所变化。序号表名增长量（/小时/天/周）增长量（/月/半年）年数据量数据库生命周期内的总计1.2.3.北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc61.2.3.4.5.6.7.8.合计新上线或扩容时，对所申请的存储不得全部一次性挂上，应该预留出30％左右的空间用于追加，以防止出现业务发展和预期不一致时剩余空间多寡不均，调整困难。操作系统上应该预先做好几个合适大小的lv备用，包括用于system/sysaux等表空间的小尺寸的lv和用于数据表空间、索引表空间的大尺寸lv，这些lv要求在HA两边主机都可见，不必单纯因为数据库增加数据文件而需要重新同步HA。1.1.1表空间的使用规则目前多数数据库系统采用数据“大集中”原则，对数据库的性能要求较高。这就要求对数据库进行必要的优化配置。表现在表空间的配置上，应遵循以下原则：1、最小化磁盘I/O。2、在不同的物理磁盘设备上，分配数据。3、尽可能使用本地管理表空间。多数系统采用RAID1+0或RAID0+1，该技术很好的解决了最小化磁盘I/O。基本不必考虑在不同的物理磁盘设备上，分配数据的原则。1.1.1.1表空间的类型按照表空间所包含的数据文件类型，Oracle表空间类型有三类：北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc61、数据表空间（permanencetablespace）-用来保存永久数据，包含永久数据文件。强烈建议在永久表空间内创建永久数据文件，不要创建临时数据文件。2、临时表空间（temporarytablespace）-用来保存临时数据，多用于数据的磁盘排序。强烈建议在临时表空间内创建临时数据文件，不要创建永久数据文件。3、回滚表空间（rollback/undotablespace）-仅用来保存回退信息。不能在该表空间创建其他类型的段（如表、索引等）。为了更好的管理表空间，同时提高Oracle数据库系统性能，在上述三类基础上，针对数据的业务功能，进一步对其加以分类。因此Oracle数据库的表空间划分为基本表空间和应用表空间。如下表：（1）基本表空间：是指Oracle数据库系统为其自身运行而使用的表空间。表空间类别表空间名称存储内容说明数据表空间SYSTEM表空间存储oracle数据库系统数据字典对象Oracle数据库系统自身生成的和使用—基本表空间数据表空间SYSAUX存储SYSAUX数据Oracle数据库系统自身生成的和使用—基本表空间回滚表空间UNDO表空间容纳回滚数据如果UNDO表空间是自动管理，则Oracle数据库系统自身生成的。生产数据库不得有如TOOLS、XDB、EXAMPLE等oracle默认安装表空间。（2）应用表空间：是指业务应用数据保存在此类表空间中。它由DBA或相关的数据库规划设计人员创建和规划。表空间类别表空间名称存储内容说明临时表空间TEMP表空间容纳排序数据由DBA设定—应用表空间数据表空间TABLES表空间存储小数据表公用业务数据由DBA设定—应用表空间数据表空间TABLESPARTITION表空间存储巨型表数据由DBA设定—应用表空间北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6数据表空间INDEXS表空间存储小数据表的索引由DBA设定—应用表空间数据表空间INDEXSPARTITION表空间存储巨型数据表的索引由DBA设定—应用表空间数据表空间LOB表空间存储LOB的数据由DBA设定—应用表空间1.1.1.1表空间及其文件的命名规则数据文件都使用裸设备方式，使用固定大小，不得设置为自动扩展。1.1.1.1.1基本表空间及其文件的使用规则（1）基本表空间及其文件命名规范如下表表空间名称裸设备连接文件名普通文件名说明SYSTEMrsystem_nn_sizesystemnn.dbf总空间大小设置为2GSYSAUXrsysaux_nn_sizesysauxnn.dbfOracle10g中必须有的表空间。总空间大小设置为4G，如果空间非常紧张，可设置为2GUNDOTBS1rundotbs_nn_sizeundotbsnn.dbf总空间不小于8GTEMPrtemp_nn_sizetempnn.dbf总空间不小于4G说明：l裸设备连接文件名Ønn为从01开始计数的序号，表示文件的个数。如：01，02，03，04。。。。。。Øsize表示了设备的大小，由数字部分和单位部分组成：XU。其中，X是一个正整数，取值范围从1～1023，U是单位标识位，是1位的字符，取值范围为k、m、g、t，分别表示北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6了KByte、MByte、GByte、TByte，size的值应该根据设备的数据大小指定。l普通文件名（即创建在文件系统上的文件）Ønn为从01开始计数的两位整数序号。如：01，02，03，04。。。。。。Ø各表空间根据需求在建库时确定。（1）数据文件路径：/home/db/oracle/oradata/{DB_NAME}/（2）数据文件的使用方式：裸设备：适用于RAC及共享磁盘双机热备数据库架构。创建数据库前，在指定的目录下创建指向裸设备的软连接文件。命令如下：ln-s/dev/rxxxxx/home/db/oracle/oradata/{DB_NAME}/xxxxx.dbf1.1.1.1.1应用表空间及其文件使用规则（1）应用表空间及其文件命名规范：应用表空间分为如下种类：参见节5.2.2.1-（2）应用表空间表空间种类表空间命名规则裸设备连接文件名普通文件名TABLES公用表空间D_<功能模块名称>_nnr+表空间名称_nn_size表空间名称_nn.dbfTABLESPARTITION分区表空间D_<数据表名>_nnr+表空间名称_nn_size表空间名称_nn.dbfINDEXS公用索引表空间I_<功能模块名称>_nnr+表空间名称_nn_size表空间名称_nn.dbfINDEXSPARTITION大表索引空间I_<数据表名>_nnr+表空间名称_nn_size表空间名称_nn.dbfLOB表空间B_<功能模块名称>_nnr+表空间名称_nn_size表空间名称_nn.dbfTEMP表空间T_<功能模块名称>_nnr+表空间名称_nn_size表空间名称_nn.dbf说明：l表空间的命名规则北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6nn为从01开始计数的两位整数序号，表示表空间的数目。如：01，02，03，04。。。。。。l裸设备连接文件名Ønn为从01开始计数的两位整数序号，表示数据文件的数目。如：01，02，03，04。。。。。。Øsize表示了设备的大小，由数字部分和单位部分组成：XU。其中，X是一个正整数，取值范围从1～1023，U是单位标识位，是1位的字符，取值范围为k、m、g、t，分别表示了KByte、MByte、GByte、TByte，size的值应该根据设备的数据大小指定。l普通文件名（即创建在文件系统上的文件）Ønn为从01开始计数的两位整数，表示数据文件的数目。如：01，02，03，04。。。。。。Ø各表空间根据需求在建库时确定。（1）数据文件路径：/home/db/oracle/oradata/{DB_NAME}/（2）数据文件的使用方式：Ø裸设备：适用于RAC及共享磁盘双机热备数据库架构。创建数据库前，在指定的目录下创建指向裸设备的连接文件。命令如下：ln-s/dev/rxxx/home/db/oracle/oradata/{DB_NAME}/r+表空间名称_nn_size其中：xxx为裸设备的名称。该名规则相关命名规范。1.1.1表空间的物理使用规则1.1.1.1表空间的物理分布对于小规模数据库，I/O不是主要的性能瓶颈，可以不考虑物理分布的问题。对于中规模数据库及大规模数据库，应当考虑：1、北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6尽可能把应用数据表空间、应用的索引表空间以及相应得分区表空间分布在独立的物理卷上。1、其次把UNDO、TEMP、REDOLOG分布在不同的物理卷上。2、对于hp-ux系统，应该为不同用途的数据建立独立的卷组。1.1.1.1表空间的存储参数的设置在规范表空间存储参数之前有必要澄清关于数据块（datablock）、区（extent）、段（segment）的概念及其之间的关系。如下图：数据块（datablock）：Oracle存储数据最细粒度是数据块，它是操作系统文件块的整数倍（有时也称逻辑块，Oracle块，或页）。一个数据块大小有2k、4k、8k、16k等，并以此单位大小保存在物理磁盘中。北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6区（extent）：是由一序列相邻连续的数据块组成的区域叫区。区存储特定类型的数据。它比数据块高一级别。段（segment）：比区（extent）高一逻辑存储级别的称作段（segment）。段是由一系列区组成。用来存储一个特定的数据结构，并且该段只能分配在同一表空间中，不能跨越表空间。如：每个表（table）的数据保存在自己的数据段中；而每个索引保存在自己的索引段中；如果表或索引是分区的，则每个分区拥有自己的段。1.1.1表空间的参数设置原则对于数据库的存储空间管理Oracle有以下的选择：1.1.1.1Extent的管理对Extent的管理有两种方式。一般情况下，我们推荐数据库管理员使用本地管理中的指定大小(UniformSize)的方式创建表空间。1.数据字典管理(DictionaryManagement)在数据字典的管理方式中，数据库使用数据字典来跟踪数据对象的存储分配，这样当出现数据对象的存储变化时，数据库需要更新数据字典以保证系统可以跟踪数据库对象的存储变化，这在某种程度上会造成系统性能的下降。2.本地管理(LocalManagement)在本地管理方式中，数据库使用每一个数据文件的前面8个数据块中的每一位来代表数据块的占用方式。由于这种方式跟踪数据对象的存储分配不需要访问数据字典，这在一定程度上避免了递归调用的出现，提高了系统存储管理的效率。对于本地的Extent管理有两种方式：(1)自动分配(Autoallocate)自动分配的方式指由数据库系统按照数据对象的大小决定该对象的每一个EXNENT的大小。一般情况下，由于数据库系统并不能预先的确定该对象的总的大小，数据库总是倾向于在初始的几个Extent使用较小的值，然后按照8－128－1024－8192个数据块的方式急剧的增大。北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6这通常会造成系统过多的碎片和较低的存储空间的利用效率。(1)指定大小(UniformSize)指定大小的方式指由数据库管理员在创建表空间时间指定该表空间的所有的EXNENT的大小，这样该表空间的所有的Extent具有同样的大小。一般情况下，由于数据库管理员能够预先的估计出该表空间的数据对象的大小，所以数据库管理员通常能够确定合适的UNIFORMSIZE来创建数据表空间。通过指定合适的数据表空间，可以避免系统出现过多的碎片和提高存储空间的利用效率。一般情况下，建议数据库管理员能够使用指定大小的方式来创建表空间，除非明确知道表空间中仅仅存储较小的数据对象，否则不要使用自动的EXTENT管理方式。1.1.1.1Segemnt的管理对Segment的管理可分为两种。我们推荐使用ASSM方式。1.手工管理方式（Manual）手工管理方式是指用户创建表空间时使用手工指定参数Freelist，FreelistGroup来控制表空间的段的空闲块。手工的管理管理可以带来更多的灵活性。2.自动管理方式（ASSM）自动的管理方式指数据库系统使用BITMAP的方式来管理空闲块。在这种情况下如果多个对象需要分配空间，可能会造成对某一块的竞争。1.1.1.1.1数据表空间的存储参数（Oracle9i/10g）数据表空间的区（extent）管理：表空间是以区为单位进行分配空间的。自从9i及以后版本推荐使用本地管理表空间，并且本地管理表空间是默认的。对应的createtablespace语句子句为EXTENTMANAGEMENTLOCAL。Oracle已不推荐使用字典管理的表空间。如下图：北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6如果表空间包含各种不同大小的数据库对象，而这些对象拥有不同尺寸的区，则选择AUTOALLOCATE是最好的选择。即字句EXTENTMANAGEMENTLOCALAUTOALLOCATE。让Oracle来管理EXTENT的分配。如下例：CREATETABLESPACEtestDATAFILE"/u02/oracle/data/test01.dbf"SIZE50MEXTENTMANAGEMENTLOCALAUTOALLOCATE;如果能够预先估算出单个对象或一系列对象的所分配的空间及EXTENTS的尺寸，则选择UNIFORM是个比较好的选择。即字句UNFORMSIZEM。如下例：CREATETABLESPACEtestDATAFILE"/u02/oracle/data/test01.dbf"SIZE50MEXTENTMANAGEMENTLOCALUNIFORMSIZE128K;表空间的段（segment）管理：段管理分为自动段空间管理（缺省参数）和手动段空间管理，对应的子句如下图：自动段管理是一种相对简单而有效的段空间的管理方式。该方式完全摒除了PCTUSED,FREELISTS,FREELISTSGROUPS等物理存储参数的设置。即使这些参数被指定，Oracle仍然会忽略它。自动段管理可根据用户数和实例数自动调整，对于大多数标准负载和应用性能来说，要比手动调整管理段要更好。因此多数情况下推荐使用段管理。如下列：CREATETABLESPACEtestDATAFILE"/u02/oracle/data/test01.dbf"SIZE50M北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6EXTENTMANAGEMENTLOCALSEGMENTSPACEMANAGEMENTAUTO;下面是完整的例子：例1：本地管理表空间+自动段空间管理sql(ORACLE9i/10g)CREATETABLESPACETESTDATAFILE"/ORACLE/PRODUCT/10.2.0/ORADATA/ORCLTEST.DBF"SIZE5MEXTENTMANAGEMENTLOCALSEGMENTSPACEMANAGEMENTAUTO;例2：本地管理表空间+自动统一尺寸段空间管理sql(ORACLE9i/10g)CREATETABLESPACETESTDATAFILE"/ORACLE/PRODUCT/10.2.0/ORADATA/ORCLTEST.DBF"SIZE5MEXTENTMANAGEMENTLOCALUNIFORMSIZE2MSEGMENTSPACEMANAGEMENTAUTO;1.1.1.1.1临时表空间的存储参数（Oracle9i/10g）Oracle9i/10g推荐使用本地表空间管理+统一区尺寸管理1M，分别对应得子句是EXTENTMANAGEMENTLOCAL和UNIFORMSIZE1M。例：SQL（Oracle10.2）CREATETEMPORARYTABLESPACETEMP1TEMPFILE"/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/temp1.dbf"SIZE5MEXTENTMANAGEMENTLOCALUNIFORMSIZE1M;1.1.1.2Autoextend_Clause自动扩展语句会造成数据文件的自动增长，在使用裸设备的情况下可能造成文件越界，在使用文件系统的情况下可能造成文件系统无空闲空间。不应使用自动扩展的功能。北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc61.1.1表的参数设置原则1.Pctfree、Pctused存储参数pctfree和pctused决定了一个数据块在不同的数据库操作下的可用性，它与数据对象的操作性质密切相关。对于主要操作为insert的数据对象，可以考虑设定较小pctfree和较大的pctused，如pctfree=5Pctused=60。对于更新较为频繁的系统，可以设定较大的pctfree和较小的pctused来避免行的迁移，如pctfree=20Pctused=40。对于银行系统，由于数据的保留时间较长，同时数据的删除较少可以考虑设定较小的pctfree和较大的pctused，如：Pctfree=10Pctused=50。2.Initrans、Maxtrans存储参数initrans和maxtrans决定了数据对象的同一个数据块中能够并发进行的事务数。由于目前的数据块由逐步变大的趋势，故此同一个数据块中发生并发事务的几率在上升。对于db_Block_Size=8192的OLTP系统，可以设定initrans＝4，Maxtrans＝101.1.1.1Undo/temp表空间的估算lUndo设置原则oracle9i以后的版本，推荐使用UNDOTABLESPACE，让系统自动管理回滚段。须考虑以下几个问题：Ø系统并发事务数有多少？Ø系统是否存在大查询或者大是事务？频繁与否？Ø能提供给系统的回滚段表空间的磁盘空间是多少？lTemp设置原则北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6可创建缺省临时表空间temp，取数据库的缺省参数。一般情况下，生产数据库系统的临时表空间不是用缺省的。应另外创建临时表空间，以供较大的排序事务使用。可设置每个Transaction类别用户，对应一个临时表空间。1.1.1索引的使用原则1.基本使用原则(1)当查询的行数占整个表总行数的比例<=5%时，建立b*树索引效果比较明显。（普通索引就是b*数索引）(2)在频繁进行排序或分组（即进行groupBy或orderBy操作）的列上建立索引。(3)在频繁使用distinct关键字进行查询的列上面建立索引。(4)进行表连接时，在连接字段上面建立索引。(5)对于键值频繁更新的索引，需要定期的进行重建。2.基本存储参数设置原则(1)物理属性子句（Physical_Attributes_Clause）参见表的物理属性参数设置原则(2)Storage_Clause参见表空间的存储参数设置原则(3)Blevel索引的blevel代表了索引中从根节点到叶节点的深度，对于索引来说，由于索引键值的频繁更新可能造成该索引的节点的过度分裂，使得索引的层次较多。因此系统管理人员应该定期的对索引进行分析，对索引深度较深的的索引进行重建工作。3.复合索引的使用原则一般情况下，对于经常同时使用多个数据项进行查询的对象可以创建复合索引，使用复合索引时特别要考虑的各个数据项在索引中的相对位置。一般情况下，我们把最常用的列放在第一位而不太常用的列放在稍后面的位置。在复合索引创建后，我们要求用户在查询数据的时候也遵循同样的方式来使用索引。虽然目前的Oracle数据库版本能够使用复合索引中的后面的数据项，但是按序使用复合索引可以给我们带来较高的效率。4.函数索引的使用原则(1)在使用函数索引(Function-based北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6INDEX)时，需要设置初始化参数QUERY_REWRITE_ENABLED=TRUE，创建该索引的用户需要有CREATEINDEX和QUERYREWRITE权限。(1)对于经常进行运算比较的一些列，可以考虑建立函数索引，但是也可以通过在表中使用原来的列的函数形式来实现(2)在OLTP系统中，一般情况下我们不建议使用函数索引。1.B树索引的使用原则(1)当查询的行数占整个表总行数的比例<=5%时，建立B*树索引效果比较明显。否则，就要慎重考虑是否需要建立B*索引。(2)索引列包含的不同值很多时，应该建立B*树索引。使用B*树索引时候应该注意的是，它对AND/OR等条件逻辑组合查询的效率很低。2.位图索引的使用原则(1)索引列包含的不同值很少时，应该建立位图索引。位图索引对AND/OR等条件逻辑组合查询的效率很高。(2)修改表的代价很大，适用于只读性，或更新很少的表1.1文件设计如果使用裸设备作为数据库设备，则在该目录下建立到相应的裸设备的链接文件。如果使用文件作为数据库设备，则根据存储空间的需求，建立独立的文件系统，挂接到该目录下。1.1.1RAC配置文件srvconfig_SizeSize表示了文件/设备的大小，由数字部分和单位部分组成：XU，其中，X是一个正整数，取值范围从1～1023，U是单位标识位，是1位的字符，取值范围为k、m、g、t，分别表示了KByte、MByte、GByte、TByte，Size的值应该根据文件/设备的数据大小指定。北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc61.1.1参数文件l对于共享磁盘的双机热备的系统，发生失效接管（failover）时，应使用pfile参数文件设置；在没有发生失效接管情况下，使用spfile参数文件。l对于单机或RAC方式的系统，可使用共享的spfile参数文件设置；1.1.1.1参数文件命名规则Oracle数据库系统在启动时，先读取初始化参数文件，根据该文件的设置，系统才能启动成功。从Oracle9i以后的版本，Oracle系统使用spfile文件和pfile参数文件。数据库系统启动时，首先查找$ORACLE_HOME/dbs/目录的spfile文件，如果无此文件，系统在查找pfile文件。spfile文件是二进制文件，而pfile文件是ASCII文件。lpfile初始化参数文件：该文件是ASCII码文件，可用文本编辑器编辑（注：在编辑前，一定要先备份）。文件命名：init{SID}.ora文件路径：/home/db/{OS_oracle_user}/admin/{DB_NAME}/pfile/以及$ORACLE_HOME/dbs/lspfile初始化参数文件：该文件是二进制文件，不可以直接编辑。只能通过OracleSQL语句进行创建。方法如下（注：在创建前，一定预先备份spfile及pfile）：SQL〉showparameterspfileSQL〉createspfilefrompfile;spfile的两种使用方式：文件系统：spfile{DB_NAME}.ora裸设备：rspfile{DB_NAME}_size保存路径：/home/db/oracle/oradata/{DB_NAME}/；缺省路径：$ORACLE_HOME/dbs/北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6size表示了文件/设备的大小，由数字部分和单位部分组成：XU，其中，X是一个正整数，取值范围从1～1023，U是单位标识位，是1位的字符，取值范围为k、m、g、t，分别表示了KByte、MByte、GByte、TByte，Size的值应该根据文件/设备的数据大小指定。1.1.1控制文件每个数据库实例应至少有两个控制文件，且每个文件存储在独立的物理磁盘上。如果有一个磁盘失效而导致控制文件不可用，与其相关的数据库实例必须关闭。一旦失效的磁盘得到修复，可以把保存在另一磁盘上的控制文件复制到该盘上。这样数据库实例可重新启动。并通过非介质恢复操作使数据库得到恢复。因此，为了使整个系统的高可靠地运行，建议系统设置2-3个控制文件。1.1.1.1控制文件命名规则（1）保存路径：/home/db/oracle/oradata/{DB_NAME}/（2）控制文件的使用方式：l裸设备：创建数据库前，在指定的目录下创建指向裸设备的连接文件。rcontrol_n_size其中：n为从1开始计数的整数，表示控制文件序号。如：1，2……size表示了文件/设备的大小，由数字部分和单位部分组成：XU，其中，X是一个正整数，取值范围从1～1023，U是单位标识位，是1位的字符，取值范围为k、m、g、t，分别表示了KByte、MByte、GByte、TByte，Size的值应该根据文件/设备的数据大小指定。l文件系统：controlnn.ctl其中：nn为从01开始计数的两位整数，表示控制文件序号。如：01，02，03……北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6（1）控制文件数量：为2-3个。如果控制文件所在存储已作镜像，建议2个控制文件。如果没有做镜像，建议3个控制文件。（2）控制文件大小l裸设备：一个物理分区大小，一般为256MB。l文件系统：系统缺省大小。1.1.1重做日志文件重做日志文件的尺寸会对数据库的性能产生重要影响，因为它的尺寸大小决定着数据库的写进程（DBWn）和日志归档进程（ARCn）。一般情况下，较大的日志文件提供较好的数据库性能，较小的重做日志文件会增加核查点（checkpoint）的活动，从而导致性能的降低。当然为了防止I/O争用，还应把各个重做日志文件分布到不同的物理磁盘上。不可能为重做日志文件提供特定大小的建议，重做日志文件在几百兆字节到几GB字节都被认为是合理的。欲确定数据库重做日志文件的大小，应根据该系统产生重做日志的数量，并依据最多每二十分钟发生一次日志切换这个大致原则来决定。在系统运行后，我们从alert文件获取日志的切换时间，并根据切换的间隔来调整重组日志的大小。初始大小建议不低于50M，小于1G。1.1.1.1日志文件命名规则归档日志（archivelog）文件，建议放在独立物理磁盘上。（1）重做日志保存路径：/home/db/oracle/oradata/{DB_NAME}/（2）归档日志保存路径：/home/db/oracle/orarch##表示实例号，1，2，3，……通常要求归档日志备份文件系统大小可以保证容纳2天产生的归档日志。AIX操作系统还要求该文件系统设置rbrw属性，以避免归档日志被放入操作系统内存。（3）日志文件的使用方式：北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6l裸设备创建数据库前，在指定的目录下创建指向裸设备的连接文件。rlog#_n_m_size（单机）rlog#_n_m_size（RAC）l文件系统redon_m.log#表示实例号，1，2，3，……n表示日志组的编号，取值范围为01-06m表示日志组成员的编号，取值范围为01-02size表示了设备的大小，由数字部分和单位部分组成：XU，其中，X是一个正整数，取值范围从1～1023，U是单位标识位，是1位的字符，取值范围为k、m、g、t，分别表示了KByte、MByte、GByte、TByte，size的值应该根据设备的数据大小指定。l日志组数量：为3-6，具体日志组数量根据各项目情况确定，每个日志组包括2个成员。l日志文件大小：为512，1024，2048M,……根据各项目情况选择此区间值。1数据库应用1.1数据库用户设计1.1.1数据库用户的权限1.1.1.1用户权限控制原则业务功能的安全分配是指开发团队定义的用户、角色、特权，它是面向应用程序和开发的。它在数据库部署是几乎是不可修改的。北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6数据库用户安全分配往往取自前台应用设计开发团队的交付生产时的定义。这种安全定义了用户、角色、系统特权、对象特权分配等等。它往往是面向开发的，没有细致考虑用户权限的控制。在数据库系统上线时，才发现有不妥之处。而这种用户安全分配多数情况下不能修改，否则对前台应用造成运行错误。但在交付生产时，投产方和用户方必须对其安全性进行审计。因为这时提供的用户安全往往是面向开发的，而不是面向末端用户的。主要检查一下几个方面：1、每个业务用户不得授予DBA角色。2、取消一些系统特权。但必须之前征询开发者的意见，否则可能对前台应用运行带来不可预测的错误。3、坚持最小化特权原则。1.1.1.1用户及其权限规范根据数据库管理、数据维护、开发、功能等方面分为以下类型用户：序号用户类型描述1.DBA该类用户拥有DBA角色，只有数据库管理员能够使用。其他用户不要授予该角色。2.DATAOWNER该类用户拥有数据库业务schema对象，特别是tables及其他对象。不对末端用户开放。只有通过对象授权和系统授权，Transaction类型用户才可访问DATAOWNER类型用户；表空间的使用，要通过空间授权配额，才可访问；CREATESESSION特权使用时方可临时授权，使用完毕后，取消授权。3.Transaction该类型用户拥有数据库最小权限。只有通过明确的系统和对象授权才可访问DATAOWNER中的对象（如CREATESESSION,ALTERSESSION等）。一般用于末端用户的访问。4.Monitor该类型用户一般用于监控数据库性能，或者是第三方工具使用。作为监控软件，如QUEST监控软件、patrol、statspack、rman等；5.其他北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6作为普通用户使用，使用权限严格限制，并服从DBA管理。如执行一般的查询sql语句等。为非ＤＢＡ用户使用。对象（如:表、索引、触发器、过程等）管理权限归数据中心，应用（属记录级别，创建临时表权限）权限归项目组1.1.1.1.1用户命名规则1、DBA类型命名格式：DBA注：XXX为长度为3个字符的项目英文简称2、DATAOWNER类型的命名格式：DB注：X为长度为2-4个字符的业务功能简称.3、Transaction类型命名格式：T注：X为长度为4-8个字符的业务功能简称4、Monitor类型命名注：监控软件用户应按照第三方的供应商提供的方式命名。5、其他类型命名格式：SQL注：XXX为长度3个字符的功能英文简称。1.1.1.1.2用户权限分配方式对于一个IT数据库项目，在应用系统开发过程中，就开始对数据库用户权限进行严格的控制。即按照该系统未来生产时的方式进行分配，尽管此时数据库还处在开发服务器之中，尽管给开发项目的控制带来更多的工作，但数据库的安全性大大提高了。对数据库用户（user）的授权，应通过数据库角色（role）进行分配。而不要把对象特权和系统特权直接授权给数据库用户。其带来的优势参见小结“5.3.2数据库用户安全的实现”。具体授权特权参见：北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc61.1.1.1各用户类型的角色命名规范1、DATAOWNER类型用户分配的角色命名规则：R__DB注：X为长度为2-4个字符的业务功能简称。2、Transaction类型用户分配的角色命名规则：R__T注：X为长度为4-8个字符的业务功能简称。3、Monitor类型用户分配的角色命名规则：注：应按照第三方厂商提供的方式命名。4、其他类型用户分配的角色命名规则：R__SQL。注：XXX为长度为三个字符的业务功能简称。1.1.2数据库用户安全的实现1.1.2.1数据库特权Oracle数据库是通过“特权”（Privilege）这个概念来实现数据安全的。所谓特权指用一种指定的方式访问xxx数据库数据对象的一个许可，如查询一个数据表的许可等。这个特权能够被授予某个实体，因此这个授予实体特权（privilege）的过程，称之为“授权”（Grant）。涉及Oracle数据库系统安全的实体有两个，分别是系统特权（SystemPrivileges）和对象特权（ObjectPrivileges）。1、系统特权系统特权是指登录到ORACLE数据库系统的用户，执行数据库系统级别的某种操作或者是某一数据库对象的创建、修改、删除。在ORACLE数据库系统中有一系列的系统内置预定义特权，系统用这些特权去控制数据的安全。不得授予普通用户额外的全局权限，如selectany/deleteany/executeany等，应用有特殊需求的除外。2、对象特权北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6对象特权是指登录到ORACLE数据库系统的用户，有权执行数据库对象级别的某种操作。例如表的INSERT，DELETE，UPDATE操作等。同样，在ORACLE数据库系统中有一系列的对象内置预定义特权，系统用这些特权去控制数据的安全。1.1.1.1角色由于ORACLE数据库系统业务处理的复杂性，对ORACLE数据库的系统特权和对象特权的分配也就变得十分复杂。因此，为了方便管理系统特权和对象特权，需要引入角色这个基本概念。所谓角色是指系统特权和对象特权的集合。通过对角色的管理，使得ORACLE数据库的系统特权和对象特权管理变得更加方便和容易。基于角色的安全管理主要有以下几点优势：1、减少授权工作量：可以通过授权给与一组用户相关联的角色，再由该角色授权给该用户组的成员用户。2、动态特权管理：如果授权给某个xxx用户的特权需要改变，只须修改相关角色的授权，那么与这个角色相关的用户的特权会自动改变，不须修改授权给用户特权。3、设置特权的可用性：当某个被授予用户的角色，需要取消，只须对相应的角色设置禁用（DISABLED）。因此，在任何特定的情况下，都可对用户的授权进行必要的控制。4、应用程序级的设置可用性：前台应用程序在试图以某个数据库用户的身份与后台数据库相连接时，可以对角色设置可用性。这种做法可以把非应用程序例如SQL*PLUS或第三方的数据库操作工具等，屏蔽在数据库系统之外，以保证数据库的安全。角色可以根据业务的需求自由定义，系统特权和对象特权可以授权给角色，角色也可授权给另外的角色，角色也可授权给用户。基于上面描述的角色安全管理的优点和特点，ORACLE数据库系统选择角色来实施数据库用户的授权管理，并根据ORACLE的业务需求从不同的角度实现业务的权限分配。北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6根据需求，设置不同级别的角色，某一级别体现对某一项业务的特权。各角色级别之间或是子集关系，或是交集关系；同一级别的角色之间，或是交集，或是互为独立集合的关系。随着对业务需求的增加或变化，不断增加、完善访问控制的粒度，并坚持最小化特权原则。如下图：1、通过存储过程管理特权（storedprocedures）使用存储过程（storedprocedures）来限制数据库的操作，客户端用户只需有权执行存储过程，并通过存储过程来实现对数据库表的访问。因而就屏蔽了用户直接对数据库表的操作。2、通过视图（VIEWS）管理特权通过视图（VIEWS）来控制ORACLE数据库系统的安全。即只分配给用户查询视图的特权，而对基表（定义视图的相关的数据表）则进行屏蔽，禁止对数据表的直接操作。视图可以实现以下两种安全级别：1、使用视图可以限制对数据表中的特定的列的访问。2、使用视图可以限制对数据表中的特定的行的访问。如：对于某一基表，要求只显示部分行，则可通过创建实体的WHERE子句来控制行的显示。北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc61.1.1.1授予权限和角色1.1.1.1.1授予系统权限和角色可以用SQL语句GRANT来授予系统权限和角色给其它角色和用户。有GRANTANYROLE系统权限的任何用户可以授予数据库里的任何角色。下面的语句授予系统权限CREATESESSION和角色ACCTS_PAY给用户JWARD:GRANTCREATESESSION,ACCTS_PAYTOJWARD;注意：对象权限不能跟系统权限和角色在同一句GRANT语句里授予。当一个用户创建一个角色，会把自动这个角色带关键字ADMINOPTION地授予给它的创建者。一个带有关键字ADMINOPTION的被授予者有几项扩展性能：Ø被授予者可以对数据库的其它用户或角色进行授予或撤销系统权限或角色的操作。（用户不可以撤销它本身的角色。）Ø被授予者可以进一步授予有关键字ADMINOPTION系统或角色。Ø拥有一个角色的被授予者可以改变或卸载这个角色。在下面的语句中，安全管理员把NEW_DBA角色授予给MICHAEL：GRANTNEW_DBATOMICHAELWITHADMINOPTION;用户MICHAEL不但可以使用隐含在角色NEW_DBA里的所有权限，当有需要时还可以授予，撤销或卸载NEW_DBA角色。只有在对安全管理员进行相关权限和角色授予时，才允许带有关键字ADMINOPTION。1.1.1.1.2授予对象权限和角色同样可以使用GRANT语句来授予对象权限给角色和用户。要授予对象权限，必须要具备下面任意一个条件：Ø拥有被授予的对象Ø被授予过有关键字GRANTOPTION的对象权限。注意：系统权限和角色不能和对象权限在同一句GRANT语句中授予。下面的语句授予了对应EMP表所有列的SELECT，INSERT和DELETE的对象权限给用户JFEE和TSMITH：北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6GRANTSELECT,INSERT,DELETEONEMPTOJFEE,TSMITH;要授予只对应EMP表的ENAME列和JOB列的INSERT的对象权限给用户JFEE和TSMITH，声明下面的句子：GRANTINSERT(ENAME,JOB)ONEMPTOJFEE,TSMITH;要把对应于SALARY视图的所有对象权限给用户JFEE，要使用ALL关键字，正如下面的例子所示：GRANTALLONSALARYTOJFEE;拥有对象的用户会自动授予所有相关的有关键字GRANTOPTION的对象权限。这几个权限让被授予者有以下几个扩展权限：Ø被授予者可以授予有或没有关键字GRANTOPTION的对象权限给数据库里的任何用户或者任何角色。Ø如果在带有关键字GRANTOPTION情况下授予，被授予者得到一个表的对象权限，并且被授予者有CREATEVIEW或CREATEANYVIEW的系统权限，那么被授予者就可以在这个表上建视图和把这个视图相应的权限授予给数据库的任何用户或角色。Ø当把一个对象权限授予给一个角色时，关键字GRANTOPTION是不起作用的。Oracle防止通过角色来传播对象权限，因此有某角色的被授予者不能通过角色来传播拥有的对象权限。1.1.1.1.1授予列的权限可以授予对应于表里单独一列的INSERT，UPDATE或REFERENCES的权限。警告：在授予对应列的INSERT权限之前，观察这个表是否有许多定义了NOTNULL约束的列，如果是就终止授予。选择性的把插入特性授予给那些没有NOTNULL特性的列，防止了用户插入任何列到表里。要避免这种情形，就要确定每一个NOTNULL的列既可以插入又有非空的默认值。否则，被授予者插入记录就会不成功并出现错误。把ACCOUNTS表的ACCT_NO列的INSERT权限授予给用户SCOTT：GRANTINSERT(ACCT_NO)ONACCOUNTSTOSCOTT;北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc61.1.1.1数据库默认用户除了sys、system、rman用户，其他oracle默认用户都应该置为expire或lock状态，除非有特殊需求;1.1.1.2数据库用户密码数据库所有活动状态用户的密码不得设置为已知的默认密码（如：sys用户密码也不能设置为manager）；数据库用户的密码应该定期修改1.2数据库分区1.2.1数据库分区介绍分区技术是为解决数据库中巨大的表或索引读写速度过慢而提出的解决方案。分区技术是利用物理上和逻辑上对数据进行分割来提高处理速度的。同时，合理的分区也提高了数据库数据的可管理性。Partition表和索引考虑使用分区的条件：1、数据损坏的故障隔离2、支持在线增加、删除3、特定分区上的批处理4、按分区备份5、维护时可访问正常分区6、恢复最关键的数据分区1.2.2逻辑分割根据分区策略，一张表的数据可以逻辑上分布于多个分区、子分区中，对数据的查询如果利用分区策略就可以缩小访问的范围，在一定量级上提高查询速度。北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6同样，对于索引分区也是一样的，从逻辑上分割表，缩小处理中的范围，能够极大地提高Oracle本身的处理速度。1.1.1物理分割对于逻辑分割后的分区，可以通过策略分布到不同的表空间中，从而分布到不同的数据文件中，而数据文件又可以分配到不同的存储介质空间中，这样就可以充分利用操作系统的并行访问，同时也利用存储介质的并行访问，极大地同时提高写入和读取速度。同样，对于索引分区也是一样的机制。1.1.2分区后对数据库管理的好处1、分区允许数据库管理员进行数据管理操作：数据装载，索引的创建和重建，在分区级别的备份与恢复。因此可节省数据库管理员大量的操作时间。2、分区可大幅度减少因维护引起的宕机时间。分区的独立性使数据库管理员对同一表或索引的各个分区进行并发的维护管理操作；也可对分区表并行的执行SELECT和DML操作。3、分区可增加数据库的可用性。减少维护窗口，恢复次数以及系统失效的影响。4、分区不要求修改任何应用程序。如，数据库管理员可把非分区表转换为分区表，而不必修改或重写SELECT语句或DML语句。同时也不必重写前端的应用程序代码。1.1.3分区对数据库规划、创建带来的负面影响当然，任何事物都具两面性，分区带来了数据库创建方面的复杂度。即维护操作原来是面向一个表或索引对象，现在则要面向几十至上百个分区。但后台工作复杂度的加大换来了前台数据操作的效率提高，也是值得的。北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc61.1.1Oracle分区技术Oracle提供以下五种分区技术：1、范围分区范围分区是对某个可度量的字段在可以预见的范围内进行划分的分区方式，例如：日期字段。2、枚举值分区枚举值分区是对某个可列举确定值的字段按照不同值进行划分的分区方式，例如：区县代码字段。3、散列分区散列分区是对某个离散性很大的字段按照根据散列算法计算出的散列值进行分区，例如：证件号码。4、组合范围-散列分区组合范围-枚举值分区是按照范围做主分区，在主分区的基础上再次进行按照枚举值分区的组合分区。例如：日期-区县。5、组合枚举-散列分区组合范围-散列分区是按照范围做主分区，在主分区的基础上再次进行按照散列分区的组合分区。例如：日期-证件号码。1.1.2分区使用选择如果数据按照某个（某些）值分区，那么range分区就最合适，比如按照“销售定额”、“财务年度”、“月份”等等，在这种情况下，range分区可以利用分区消除，这包括应用中使用“＝”、“>”、“<”等作为条件。如果不能找到其他合适的自然条件进行分区，那么HASH分区就比较合适，这是我们建议选择唯一列或者几乎唯一的列作为分区键值。这种情况下，分区数据是均匀的，使用分区键值“＝”或者in(value1,value2…)时，hash分区可以利用分区消除，但是使用其他条件时，hash无法利用分区消除。北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6如果分区后，每个分区的数据量还是很大，建议使用组合分区，例如，首先按照自然条件做range分区，之后，对分区再进行分区。1.1.1分区索引1.1.1.1全局索引(GLOBALindex)1、指向任何一个分区中的记录2、索引可以被分区或不分区3、表可以被分区或不分区4、分区键值可以是有前缀后没有前缀1.1.1.2本地索引(LOCALindex)1、每个本地索引分区只包含本分区的记录2、二种类型的本地索引Ø前缀(prefixed)：唯一或非唯一列，可有效的使用分区消除，适于索引并行查询，适用于OLTPØ非前缀(non-prefixed)：适合于索引并行查询，可有效的使用分区消除，唯一索引受限，适用于DSS3、分区键值可以与索引键值不同Ø本地分区索引：每个表分区都有一个索引分区，而且只索引该表分区的数据。一个给定索引分区中的所有条目都指向一个表分区，表分区中的所有行都表示在一个索引分区中。优点:容易维护;适合并行索引扫描缺点:对少量记录查询相对效率不高Ø全局非分区索引：一个索引可以指向多个分区的数据优点:对单个或少量记录的访问比较有效缺点:管理维护上有额外成本北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6Ø全局分区索引：索引有独立的分区键值，每个索引分区可能指向多个不同的表分区数据优点:可用性和管理性缺点:对新数据的导入不会太有效1.1数据库实例配置对于Oracle各个版本的参数优化，随版本的区别而有所不同。但万变不离其宗，大部分的参数设置原理是相同的，其优化原则也一致。1.1.1数据库字符集数据库字符集的确定非常重要，如果选择不当，会给业务数据的保存带来麻烦。如在现实中，有的汉字保存到数据库时发生乱码，从而使客户的信息不能正常保存和显示。数据库字符集在系统设计开发阶段就应当确定。在数据库系统上线后，再更改数据库字符集，代价会非常大。因为不同的字符集设置之间，存在转换操作，如果不兼容，只能逐条转换。一般选择原则是要适中，满足当前和未来业务数据的保存。既不要选择太大，也不要过小。现有支持汉字的字符集包括：ZHS16CGB231280：此字符集较小，不建议使用ZHS16GBK：此字符包含了大部需要的汉字字符，由于目前已经有了新的国标GB18030－2005，而GB18030不是GBK的严格超集，部分字符编码有改动，如果要升级到GB18030只能是将库导出导入重建，因此不建议使用该字符集。ZHS32GB18030：此字符集是最新国家标准字符集，包含字符较全，如果系统将来不会有其他国家字符被使用，可以使用此字符集。注意10g的DBCA工具建库时，选不到该字符集，要使用该字符集只能手工建库。AL32UTF8：北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6此字符集是oracle推荐使用的字符集，对汉字支持也较好。如果系统可能会有其他国家字符录入，建议选用此字符集。对于其他字符集的选择，请查询应用安装手册。1.1.1数据库版本和补丁集开放平台管理部每季度会推出oracle版本建议，包括使用的oracle版本和补丁集建议，新上线系统安装时应该按最新的版本建议安装。1.2数据库参数设置数据库参数的设置与业务应用密不可分。不同的业务类型，决定着关键参数的值，同时合理参数值也就决定着数据库系统的性能优劣。不仅如此，业务数据的需求也决定着数据库系统的设置。对于实例创建成功而不可更改的或者修改代价比较大的参数，必须慎重考虑，在充分调研各个方面的需求后，方可确定参数值，以满足业务的正常健康的运行。1.2.1必须修改的初始化参数1.2.1.1DB_CACHE_SIZE数据库缓冲高速缓存用来存储最近使用的数据。处理查询时，服务器进程在数据库缓冲高速缓存中查找任何所需的块。如果未在数据库缓冲高速缓存中找到这个块，服务器进程就从数据文件读取这个块并且在数据库缓冲高速缓存中放置一个副本。由于对同一个块的后续请求可能在内存中找到这个块，因此这些后续请求可以不需要再次进行物理读取。Oracle使用最近最少使用算法来释放近期未被访问的缓冲区，以便在缓冲高速缓存中为新块腾出空间。衡量一个系统的的数据缓冲区设定效果的的一个重要指标是数据的缓存命中率。北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6对于OLAP系统，由于大量的数据访问使用全表扫描的方式来运行，回答的是一些统计结果和“如果”“那么”之类的分析和预测，其数据缓存的利用率较低，此时主要应该考虑的是规划系统的IO分布以提高系统IO效率，而不是提高系统缓存的命中率。对于OLTP系统，由于每次处理的数据量较小而且大量的数据是可以重复使用的，所以数据的共享对提高系统的效率非常重要。下面的说明是针对OLTP应用系统的。对于一个设计良好的应用系统，其数据库的缓存命中率应该在85%以上。对于DB_CACHE_SIZE的最终确定，需要依靠对实际系统的运行分析。建议值：该参数一般取物理内存的30%-50%左右。以后根据运行分析，再作调整。1.1.1.1SHARED_POOL_SIZE共享池是用来存放可供全局共享的对象，比如可重用的SQL语句执行计划，PL/SQL包、存储过程、函数和游标等信息。共享池的大小是由SHARED_POOL_SIZE参数决定的，而且可以使用ALTERSYSTEMSET语句来动态改变，但总的SGA区大小不能超过SGA_MAX_SIZE。设置的一般性原则一般情况下，共享池大小的设定与应用的类型和并发用户的数量有关系。其实际设定值与系统的可用内存相关。对于复杂的应用系统――指每个功能拥有较多的SQL语句同时每个SQL语句的执行计划又比较复杂的情况下，可以适当的增加SHARED_POOL_SIZE的值，随着并发用户的增加应当适当的调整SHARED_POOL_SIZE的值。对于较小的应用系统，共享池的大小和数据缓冲区的比例可以为1：2，随着内存的增加，该比例可以进一步的减小，一般地共享池不必超过4GB。对于一个正常的OLTP系统SQL语句的缓存命中率在95%就是一个比较好的系统了，对于OLAP系统80%的缓存命中率也是可以接受的。北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6对于一个具有4GB物理内存300个并发用户的C/S应用体系的OLTP应用系统可以设定SHARED_POOL_SIZE=800M，而对于一个具有8GB物理内存600个并发用户的C/S应用体系的OLTP应用系统则可以设定SHARED_POOL_SIZE=1200M。建议值：建议此参数设置为512M,至少在200M-2G之间。对于SHARED_POOL_SIZE的最终确定，需要依靠对实际系统的运行分析。1.1.1.1LARGE_POOL_SIZE大池用来存储和SQL语句处理无直接关系的大内存结构。如在备份和恢复过程中复制的数据块。大池是SGA区的可选选项，目的为了减轻共享池的压力。大池不使用最近最少使用（LRU）算法来进行管理。大池的大小由参数文件中的LARGE_POOL_SIZE设置。能被ALTERSYSTEM语句动态改变。设置的一般性原则大池一般用来存放并行查询的相关信息和RMAN的磁盘IO缓冲区。对于为RMAN所分配的大池，其设定与RMAN分配的通道数有关，公式为：通道数*(16MB+(4*size_of_tape_buffer))硬盘备份时，size_of_tape_buffer为0，磁带备份时，该值默认为256k建议值：OLTP：100MOLAP：256M1.1.1.2DB_BLOCK_SIZEDB_BLOCK_SIZE参数决定数据库系统的性能和使用。关于数据库的业务应用，该文档已在“4.1数据库类型特点分析”中已讨论。数据库参数DB_BLOCK_SIZE的设置与原则：1、OLTP类型的业务应用数据库系统，则DB_BLOCK_SIZE=4k或8k；2、DSS类型的业务应用数据库系统，则DB_BLOCK_SIZE=16k或32k；3、Hybrid类型的业务应用数据库系统，则DB_BLOCK_SIZE=8k或16k；在北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6oracle9i之后已经可以建立不同blocksize的表空间，根据不同的业务特性将不同的表放置于不同blocksize的表空间中，这个特性对于Hybrid系统会有性能的提升。针对不同业务类型的数据库系统，以上设置方式，不是绝对的，要取决于业务具体情况。1.1.1.1SP_FILE参数设置文件的绝对路径。默认值：ORACLE_HOME/dbs/spfile.ora建议值：根据命名规范命名，文件文件应该放入存储，特别是在HA环境中。1.1.1.2PGA_AGGREGATE_TARGET使用该参数的前提是，必须WORKAREA_SIZE_POLICY=AUTO，并且数据库运行方式是Dedicatesever和连接时Dedicate方式时，这个参数才可生效。此时参数SORT_AREA_SIZE,HASH_AREA_SIZE,BITMAP_AREA_SIZE会被忽略失效。但如果使用MTS和SHARESERVER连接方式时，则PGA_AGGREGATE_TARGET会被忽略失效，此时*_AREA_SIZE就会生效。PGA_AGGREGATE_TARGET是系统自动管理*_AREA_SIZE的大小，因此推荐使用这个参数。其设置原则为：1、OLTP：PGA_AGGREGATE_TARGET=(total_mem*80%)*20%2、DSS：PGA_AGGREGATE_TARGET=(total_mem*80%)*50%其中：total_mem为系统的物理内存。该原则是经验值，对于实际运行系统可视情况适当浮动调整。北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc61.1.1.1PROCESSES根据实际连接数据库的连接数的1.2-1.4倍进行设置，不得低于500。1.1.1.2OPEN_CURSORS该参数设定了一个会话一次可以打开的游标的最大数量，并且限制使用的PL/SQL游标高速缓存的大小，以避免用户再次执行语句时重新进行语法分析。如果初始化参数CURSOR_SPACE_FOR_TIME设为TRUE，则OPEN_CURSORS也需要相应的增大。1)值范围：1～4GB-1。2)默认值：503)推荐值：该值与应用类型相关，设置值不得低于2001.1.1.3MAX_DUMP_FILE_SIZE建议不要使用默认值（UNLIMITED），最大设置100M。以避免数据库多次dump出trc文件时迅速撑满整个文件系统。1.1.1.4RECOVERY_PARALLELISM参与实例恢复（RECOVERY）的进程数量，0或1意味着串行。默认值：操作系统相关，通常为0取值范围：操作系统相关，不超过PARALLEL_MAX_SERVER设置值推荐值：物理CPU个数－1北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc61.1.1.1PARALLEL_EXECUTION_MESSAGE_SIZE并行操作（并行查询，并行DML，并行恢复、复制）时消息的大小，设置较大值对并行查询时有并行进程间大量数据传输时比较有利。默认值：2148取值范围：2148to65535推荐值：8192，超过1T的系统可以更大1.1.1.2INSTANCE_GROUPS（RAC）描述当前实例所属的资源组，RAC中并行操作模式时用到，和下面的PARALLEL_INSTANCE_GROUPS共同使用，可以限制并行操作使用的实例资源。默认值：无取值范围：无推荐值：数据中心通常只使用两节点的RAC，因此只有两个设置值，RAC两个节点各使用一个（和两个实例的instance_name中的顺序保持一致）：node_01,node_bothnode_02,node_both1.1.1.3PARALLEL_INSTANCE_GROUP（RAC）描述当前实例并行操作时可使用的资源组，RAC中并行操作模式时用到，和上面的INSTANCE_GROUPS共同使用，可以限制并行操作使用的实例资源。默认值：无取值范围：其设置名称必须在RAC节点的INSTANCE_GROUPS中曾出现。推荐值：数据中心通常只使用两节点的RAC，需要保证日常并行仅在本节点进行，避免同时耗尽两台实例的所有资源。设置时应根据INSTANCE_GROUPS来设置：INSTANCE_GROUPS设置为node_01,node_both则PARALLELINSTANCE_GROUP设置为node_01;北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6INSTANCE_GROUPS设置为node_02,node_both则PARALLELINSTANCE_GROUP设置为node_02。node_both在通常情况下不会被使用，除非应用有非常大的并行操作需要同时使用两个节点上的资源，此时需要在应用中显式调用altersessionsetPARALLELINSTANCE_GROUPS＝‘node_both’;1.1.1.1与DRM有关的隐藏参数（RAC）10g的RAC中引入的DRM（DynamicResourceMastering）的概念,在RAC的不同节点间根据资源使用情况决定master节点。由于该功能并不十分完善，常常造成节点间master节点不断转换，触发bug，而且数据中心中所有的RAC都是两节点的，使用该功能意义不大，因此应该屏蔽该功能。屏蔽时需要配置如下两个默认参数：_gc_undo_affinity=FALSE_gc_affinity_time=01.1.2系统优化建议修改的初始化参数1.1.2.1SESSION_CACHED_CURSORS设置该参数有助于减少系统parseSQL的时间，提高系统效率。10g中该参数的默认值为20。建议设置session_cached_cursors＝50，对于实际运行系统可视情况适当浮动调整。1.1.2.2BACKUP_TAPE_IO_SLAVES该参数指定了恢复管理器（Recovery北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6Manager）所使用的从属进程的数目，这些从属进程被用来备份，拷贝或恢复数据到磁带上。当此参数为TRUE时，一个I/O从属进程被用来从磁带设备读取或写入数据。如果此参数为FALSE（默认情况下），在备份中不会使用I/O从属进程，而是由备份的阴影进程（shadowprocess）来存取磁带设备。该参数设置为true后，通过模拟AIO的行为，主进程可以不必等待磁带写完成后就能继续处理其他数据块，会启动子进程来等待，从而加快了备份速度。建议值：备份到磁带上的系统，设置该参数为true以加快速度。1.1.1.1JAVA_POOL_SIZE参数JAVA_POOL设定了数据库用来存放JAVA代码的缓冲池的大小。设置的一般性原则对于使用了数据库中的JAVA的应用系统，该参数的大小取决于应用的类型，我们可以通过查询数据库系统中关于SGA的动态视图V$SGASTAT来确定数据库中是否使用了JAVA和合理的JAVA_POOL的值。建议：对于没有使用数据库中的JAVA的应用，不设置，使用系统默认值。对于使用了数据库中的JAVA的应用，JAVA_POOL_SIZE的大小由应用的类型和数量确定。1.1.1.2OPTIMIZER_INDEX_COST_ADJ控制优化器是否会更倾向于使用索引。默认值：100，推荐值：OLTP：50，即优化器评估时将走索引的代价评估为标准状况下（默认值100时）走索引代价的50％。OLAP：不设置，使用默认值北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc61.1.1不得修改的初始化参数1.1.1.1COMPATIBLE该参数设定了Oracle的版本，建库时取默认值。只有在数据库upgrade（升级更高版本）时修改，该参数一旦设置为高版本后，不能回退到旧版本设定数值。1.1.1.2CURSOR_SHARING控制可以共享相同的共享游标的SQL语句类型。(1)值范围：FORCE|SIMILAR|EXACTEXACT：完全相同的SQL语句才能共享一个游标。SIMILAR：让语句类似并且执行计划相同的语句共享一个游标。FORCE：强制表达方式不同但语句意思相同的语句共享一个游标。(2)默认值：EXACT该参数设置为SIMILAR、FORCE对共享SQL执行计划，避免共享池碎片有一定好处，但是这样设置容易触发ORACLE的BUG，从保证系统稳定运行的角度来讲，不建议修改数据库默认设置，应该尽量从应用开发的角度来解决SQL共享问题。1.1.1.3SGA_TARGET在Oracle9i中，必须手工调整和设置各种数据库缓存区的大小。在Oracle10g中，新增加了一个SGA_TARGET参数，该参数的作用是通过设置一个总值，让数据库根据实际需要，动态调整各块缓存区的大小，且每块缓存区的大小不会小于手动设置的参数值。但在实际使用过程中，如果只是设置SGA_TARGET，而不手动设置各种缓存区的参数值，在系统繁忙时由于动态调整不同区域内存占用将将有可能会引发Oracle内部的一些bug。因此，建议SGA北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6区内存管理通过手工设置，不设置该参数，维持默认值0。1.1.1.1SGA_MAX_SIZESGA_MAX_SIZE为SGA的最大允许设置值，系统会自动根据DB_CACHE_SIZE/SHARED_POOL_SIZE/JAVA_POOL_SIZE/LARGE_POOL_SIZE的值扩大，动态修改以上参数设置值时，以上参数设置值的总和不得超过本参数指定值。设置该值大于以上pool的总和或SGA_TARGET的设置值，会造成多余值无法被自动利用而造成浪费。应该尽量通过测试确实合适的pool设置值，避免系统自动扩展或临时扩展。1.1.2建议不修改的初始化参数1.1.2.1UNDO_RETENTION以秒为单位设置UNDO争用值的下限。对自动扩展的UNDO表空间有价值，对固定大小的UNDO表空间（数据中心推荐做法），系统根据UNDO使用情况、UNDO表空间大小自动确定该值，除非启用了retentionguarantee。默认值：900，1.1.2.2SESSIONS指定用户会话和系统会话的总量。默认数量大于PROCESSES，以允许递归会话。1)值范围:任何整数值。2)默认值:派生(1.1*PROCESSES+5)3)建议值：该值无需特意指定，建议在指定PROCESS后使用系统计算值。北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc61.1.1.1TRANSACTIONS指定并行事务处理的最大数量。如果将该值设置得较大，将增加SGA区的大小，并可增加实例启动过程中分配的回滚段的数量。默认值大于SESSIONS，以实现递归事务处理。对于连接到Oracle数据库的应用，根据其连接方式可以分为：1.直接连接到数据库应用。如：客户前台直接通过SQL*NET，JDBC，OCI，IIOP连接到数据库应用，这类应用中由于用户的操作关系，一般的并发事务数较小，远远的小于连接到数据库的会话数；2.用户前台并不直接与数据库相连而是通过中间件与数据库相连，这类应用中连接到数据库的会话数等于中间件的服务器的数量。由于应用服务器的集中作用，这时每个连接到数据库的会话为多个最终客户服务，会话的负荷较重，事务较多，极限情况下每个会话都可能出现由活动事务的情况，考虑到系统的递规调用的情况，最大值可能为1.1倍的会话数。1)值范围：4到4G2)默认值：派生(1.1*SESSIONS)对于第一类应用，TRANSACTIONS最好为1/10到1/5的PROCESS的值；对于第二类应用，TRANSACTIONS最好为1.1倍的PROCESS的值.不建议修改默认值，由ORACLE根据SESSIONS/PROCESS的设的设置自动计算。1.1.1.2DB_KEEP_CACHE_SIZE建议对频繁使用的较小的字典表将其放入到KEEP池中。通常情况下，系统新上线时如果开发部门没有明确要求，不进行设置。运维过程中觉得有必要设置时，可以进行修改。北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc61.1.1.1LOCK_SGA决定设置数据库参数lock_sga将目前数据库SGA索住在物理内存中。同时，对于AIX，上述从操作还需要修改操作系统参数v_pinshm从缺省的0设置为1；对于HPUX，还需修改数据库hpux_sched_noage=178。由于行内系统DB机器通常都是专用的，平时不会被交换至硬盘，可以不设置。1.1.1.2DB_FILES该参数定义了数据库可以打开的数据文件的数目，此参数默认值为200，在创建实例的时指定，在RAC、DATAGUARD等环境要求各实例保持一致，修改时要求重启实例。10g及以上版本中，该值重启实例就能修改，从数据中心的实践来看，通常数据文件都不会超过150个，设置过大的值会浪费一定内存，因此不必过早设置过大数值。1.1.1.3DB_FILE_MULTIBLOCK_READ_COUNT该参数指定了在涉及一个全表连续扫描的一次I/O操作过程中读取的数据块（其大小由数据库初始化参数DB_BLOCK_SIZE决定）的最大数量。只有在系统对数据库的一个表进行全表扫描时（dbfilescatteredread）该参数才起作用。一般的情况下，该参数的设定与系统的应用类型相关，对于做大量数据查询的OLAP系统可以设定较大的值，如32，对于一般的OLTP系统设定为8或16是一个较好的选择。值范围：此参数只适合Oracle9i，Oracle10g对此参数已不要求。默认值：8建议值：9i对OLAP系统推荐使用32，对OLTP系统推荐使用16,10g不必设置北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc61.1.1.1LOG_BUFFER重做日志文件跟踪服务器进程和后台进程对数据库进行的更改。该缓冲区是循环使用的，包含对数据文件所做的各种修改动作的信息，例如：INSERT，UPDATE，DELETE，CREATE，ALTER和DROP等操作。重做日志缓冲区大小由参数文件中的LOG_BUFFER设置。较大的日志缓冲区有助于降低日志文件的读写频率，提高系统的效率。设置的一般性原则由于系统的写日志进程(LGWR)在日志缓冲区中的重做条目(redoentry)达到1MB的时候，会把日志缓冲区的数据写入日志文件，同时当日志缓冲区1/3满的时候也会写日志文件，所以过大的日志缓冲区是没有意义的。10g中该参数的值一般接近SGA设置值决定的粒度值（通常为16M），不必专门设置增大。1.1.1.2FAST_START_MTTR_TARGET控制快速检查点的参数，指定从单个数据库例程崩溃中恢复所需的时间(估计秒数)。该参数设置较低会造成数据库频繁执行检查点(checkpoint)，对系统运行效率有影响。在系统设定以下参数后该参数失效：FAST_START_IO_TARGETLOG_CHECKPOINT_INTERVAL值范围：[0,3600]。默认值：0推荐值：一般系统可以不设置该值，除非AWR报告显示估计MTTR时间（EstdMTTR）超过300s。对不可用时间有严格要求的系统可设置为120或更低，但不得小于30北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc61.1.1与并行操作有关的参数PARALLEL_MIN_SERVERPARALLEL_MAX_SERVERPARALLEL_MIN_PERCENTPARALLEL_ATUOMATIC_TUNINGPARALLEL_EXECUTION_MESSAGE_SIZE一般情况下，除了PARALLEL_EXECUTION_MESSAGE_SIZE，以上参数都使用默认值，系统会根据CPU个数、表设置等自动选择并行。在执行SQL语句操纵的数据对象中指定相应的参数，其优先次序依次为：语句中的提示最优先，其次为对象中的并行定义参数，最后为数据库的初始化参数。有建议在OLTP系统上关闭oracle并行，以免并行任务对日常业务的冲击，考虑到数据中心各实例系统资源一般比较充足、数据量都比较大，在统计信息收集、索引重建维护等过程中充分利用并行还会大大提高效率，因此建议系统通常按默认设置打开并行。日常运维过程中，需要注意的是当我们使用并行时，最好由系统来自行决定并行度，如果没有充足的把握，尽量不要指定过高的并行度，以尽量避免并行操作对日常业务的影响。1.2数据库连接服务1.2.1专用服务器连接在专用服务器模式下，客户连接和服务器进程（或者有可能是线程）之间会有一个一对一的映射。如果一台UNIX主机上有100条专用服务器连接，就会有相应的100个进程在执行。下图显示了专有服务器连接时的体系结构。北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc61.1.1共享服务器连接共享服务器连接强制要求必须使用OracleNet，即使客户和服务器都在同一台机器上也不例外。如果不使用OracleTNS监听器，就无法使用共享服务器。如前所述，客户应用会连接到OracleTNS监听器，并重定向或转交给一个调度器。调度器充当客户应用和共享服务器进程之间的“导管”。下图显示了共享服务器连接时的体系结构。北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc61.1.1连接服务建议1.1.1.1专用服务器连接1、服务器只要有足够的资源（CPU和RAM）就使用专用服务器连接1.1.1.1.1共享服务器连接1、共享服务器只适用于OLTP系统，只在资源（CPU和RAM）不足的情况下使用2、其他高级连接特性需要使用共享服务器连接1.2数据库安全建议1.2.1采用满足需求的最小安装随着Oracle数据库版本的不断升级，Oracle的功能也越来越多，因此安全检查点也越来越多。因此最好根据需求只安装所需内容。如：不需要web功能，就不要安装相应组件。1.2.2安装时的安全Oracle软件的安装目录要和系统盘分开。在Unix下，Oracle数据库系统的帐号和组的权限也要作相应设置。创建数据库管理员组(DBA)并分配root和Oracle软件拥有者的用户ID给这个组。DBA能执行的程序只有“710”权限。在安装过程中SQL*DBA系统权限命令被自动分配给DBA组。1.2.2.1删除或修改默认的用户名和密码Oracle北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6的默认安装会建立很多缺省的用户名和密码，而大部分的数据库管理员都不清楚到底有多少数据库用户，从而留下了很大的安全隐患。因此数据库软件应以定制方式安装。1.1.1.1安装最新的安全补丁Oracle数据库虽然被称为“Unbreakable”，但还是存在不同程度的安全漏洞。保证ORACLE数据库安全性的一个比较好的办法是时刻关注Oracle公司的安全公告，并及时安装安全补丁。安全公告和补丁位置如下：http://otn.Oracle.com/deploy/security/alerts.htm1.2数据库备份和恢复1.2.1RMAN备份该类备份是最为完善的备份恢复解决方案。对于从几百M至TB级的数据库，都可选择其作为备份方案。正因其完善，相关的备份架构相对复杂，维护操作也复杂。对于小型数据库（如几十M）来说，硬件和软件成本以及维护代价过高。其逻辑架构如下图：北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc61.1.1Export/import备份该类备份是用于小型数据库备份作为合适。硬件和软件成本以及维护代价比较低。因其备份方式灵活，可以进行表级、模式级备份，常作为rman备份方案的补充。另外，对于跨操作系统平台迁移数据库，也经常使用其作为首选方案。1.1.2存储级备份—虚拟带库数据块级虚拟磁带库相对其它备份设备具备了显著优势：Ø性能大幅提高——可支持接近磁盘阵列极限的备份/恢复速度。Ø免疫病毒——应用安全性等同物理磁带库。Ø去除磁盘碎片——保障性能持续性及磁盘效率。Ø无转译数据传输——良好的对数据结构损坏的承受力。北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6通用于主机软硬件环境——免除适配、维护和管理风险。1.1.1数据库恢复1.1.1.1实例故障的一致性恢复当实例意外地（如掉电、后台进程故障等）或预料地（发出SHUTDOUMABORT语句）中止时出现实例故障，此时需要实例恢复。实例恢复将数据库恢复到故障之前的事务一致状态。如果在在线后备发现实例故障，则需介质恢复。在其它情况Oracle在下次数据库起动时（对新实例装配和打开），自动地执行实例恢复。如果需要，从装配状态变为打开状态，自动地激发实例恢复，由下列处理：1、为了解恢复数据文件中没有记录的数据，进行向前滚。该数据记录在在线日志，包括对回滚段的内容恢复。2、回滚未提交的事务，按步1重新生成回滚段所指定的操作。3、释放在故障时正在处理事务所持有的资源。4、解决在故障时正经历一阶段提交的任何悬而未决的分布事务。1.1.1.2介质故障或文件错误的不一致恢复介质故障是当一个文件、一个文件的部分或磁盘不能读或不能写时出现的故障。文件错误一般指意外的错误导致文件被删除或意外事故导致文件的不一致。这种状态下的数据库都是不一致的，需要DBA手工来进行数据库的恢复，这种恢复有两种形式，决定于数据库运行的归档方式和备份方式。1、完全介质恢复可恢复全部丢失的修改。一般情况下需要有数据库的备份且数据库运行在归档状态下并且有可用归档日志时才可能。对于不同类型的错误，有不同类型的完全恢复可使用，其决定于毁坏文件和数据库的可用性。2、北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6不完全介质恢复是在完全介质恢复不可能或不要求时进行的介质恢复。重构受损的数据库，使其恢复介质故障前或用户出错之前的一个事务一致性状态。不完全介质恢复有不同类型的使用，决定于需要不完全介质恢复的情况，有下列类型：基于撤消、基于时间和基于修改的不完全恢复。Ø基于撤消(CANCEL)恢复：在某种情况，不完全介质恢复必须被控制，可撤消在指定点的操作。基于撤消的恢复地在一个或多个日志组（在线的或归档的）已被介质故障所破坏，不能用于恢复过程时使用，所以介质恢复必须控制，以致在使用最近的、未损的日志组于数据文件后中止恢复操作。Ø基于时间(TIME)和基于修改(SCN)的恢复：如果希望恢复到过去的某个指定点，是一种理想的不完全介质恢复，一般发生在恢复到某个特定操作之前，恢复到如意外删除某个数据表之前。1.1ORACLENETWORK配置1.1.1监听器的使用配置原则1、创建监听器时，每个实例必须有一个监听器。不能服务于多个实例。2、listenerport端口为安全起见，不使用缺省1521。和网络部协商后，该端口统一使用11521。1.1.2TNSNAMES的使用配置原则1、数据库连接别名，推荐选择专用连接。如果是中间层应用服务器作为连接数据库的客户端，出于性能的考虑强烈推荐使用专用连接。2、对于Oracle9i以上的数据库，推荐使用数据库服务名（servicename），而不是实例名（SID）。3、数据库连接的主机名，推荐使用数据库服务器的IP地址。如果是Oracle10gRAC，推荐使用虚拟IP（VirtualIP）。北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc61.1.1RAC环境下TNSNAMES的配置1.1.1.1各节点启用负载均衡可以通过Oracle自身的负载均衡功能，对应用发起的事务请求进行负载均衡。启用此功能必须对各客户端的TNSNAMES.ORA文件进行相关配置，通过设置TNSNAMES.ORA文件的LOAD_BALANCE参数为ON来启用负载均衡功能，配置如下：ODSPDDB1=(DESCRIPTION=(load_balance=on)(failover=on)(ADDRESS=(PROTOCOL=TCP)(Host=11.156.111.42)(Port=1521))(ADDRESS=(PROTOCOL=TCP)(Host=11.156.111.40)(Port=1521))(CONNECT_DATA=(SERVICE_NAME=odspddb)(FAILOVER_MODE=(BACKUP=odspddb2)(TYPE=SESSION)(METHOD=BASIC))))1.1.1.2各节点不启用负载均衡如果不需要Oracle平均的处理应用发起的请求，而是RAC各节点执行不同客户端发起的请求，必须通过设置TNSNAMES.ORA文件的LOAD_BALANCE参数为OFF来取消负载均衡功能，配置如下：北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6ODSPDDB1=(DESCRIPTION=(load_balance=off)(failover=on)(ADDRESS=(PROTOCOL=TCP)(Host=11.156.111.40)(Port=1521))(ADDRESS=(PROTOCOL=TCP)(Host=11.156.111.42)(Port=1521))(CONNECT_DATA=(SERVICE_NAME=odspddb)(FAILOVER_MODE=(BACKUP=odspddb2)(TYPE=SESSION)(METHOD=BASIC))))1数据库开发建议1.1数据库模型设计规范1.1.1命名规则数据库对象如表、列、序列、过程、函数等在命名时要遵循如下规则：Ø命名要使用富有意义英文词汇，避免使用缩写。Ø数据库、数据库对象的名称可由多个单词组成的。Ø数据库对象名称由如下部分组成：范围、类型、名称实体，各词汇间采用"_"连接。Ø其中各数据库对象的范围和类型的具体含义及取值详见各数据库对象的命名规则。Ø数据库对象的名称不允许是OracleSQL、OraclePL/SQL的保留字和关键字。北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6数据库对象格式样例说明表<模块名称>_T_<表名称>ODS_T_SYSCONFIG同一个模块的表使用相同前缀，长度不超过15个字符　普通索引IND_<表名>_N<序号>IND_ORDER_N1ORDER表第一个普通索引位图索引IND_<表名>_B<序号>IND_ORDER_B1ORDER表第一个位图索引唯一索引IND_<表名>_U<序号>IND_ORDER_U1ORDER表第一个唯一索引　普通视图V_<视图名>V_ORDER　物化视图MV_<视图名>MV_ORDER　　存储过程P_存储过程名P_get_sysdata获取系统时间函数F_函数名F_reception业务受理包PKG_包名PKG_print打印发票　触发器TRG_触发器名称TRG_initial初始化数据触发器　序列SEQ_序列名称SEQ_register_number受理编号序列别名<用户名>_<表名>　　北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc61.1.1表1.1.1.1建表的参数设置Ø不允许将表建立在SYSTEM表空间上。Ø表和索引建立在不同表空间上。Ø建表时必须指明所存储的表空间。Ø生成建表脚本时非空的列放在表的前部，可空的列放置在表的后部。Ø数据缓冲池的类型：查询频繁且数据量较小的参数表采用bufferpoolkeep。ØINITIAL：对于初始化数据量大的表，设置的值要大于初始化数据。ØPARALLEL：对于OLTP系统，不允许使用该参数。1.1.1.2主外键设计Ø必须设置主键，通常不使用存在实际意义的列做主键，具体情况应结合业务特性综合考虑。Ø由Sequence产生的ID列，不作为组合PK中的列。Ø删除约束时使用keepindex参数。Ø唯一性约束用于限定表中记录的唯一性，允许为空，允许对创建唯一性约束的列进行修改操作。Ø检查(check)，一般使用于列的取值受限于一些特定值的情况下，如员工的性别，年龄，贷款的状态等。Ø触发子约束，一般情况下不要使用。1.1.1.3列设计Ø定长字符型列使用CHAR类型，不定长字符型列使用VARCHAR2类型。北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6ØDATE精确到微秒。Ø使用NUMBER型时必须指定长度。1.1.1.1临时表Ø对于只对本事务有效的临时表使用ONCOMMITDELETEROWS关键字创建该表。Ø对于只对本会话有效的临时表使用ONCOMMITPRESERVEROWS关键字创建该表。1.1.2索引Ø选择使用普通B树索引。Ø小表（数据量小于5000条记录为标准）不需要创建索引。Ø对于OLTP应用，分区表使用分区索引。Ø分区索引必须包含分区列，分区列按序放置在分区索引的末尾。Ø建立分区索引必须指明表空间，不允许只写一个LOCAL。Ø单个表上索引的个数不超过5个。Ø将记录差别数最多的列放在索引顺序的最前面。Ø索引数据的重复率不能超过20%。Ø进行orderbycolumndesc排序时，创建columndesc索引。1.1.3视图Ø物化视图的刷新间隔时间最小为3分钟。Ø物化视图的基表必须创建主键。1.1.4存储过程、函数和包Ø存储过程、函数和包中不允许使用DML或DDL语句。北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6Ø存储过程、函数和包必须有相应的出错处理功能。1.1.1触发器Ø触发器的的选择必须与应用程序事务属性保持一致，以避免死锁发生。Ø在有大量数据导入表中的情况下，不使用触发器。1.1.2序列Ø加大序列的cache值，可以减少对于X$SEQ等基表的锁争用，但是会造成序列的不连续性。Ø应用程序不要将任何商业逻辑建立在序列的完全连续性上。Ø不要设置序列为循环使用，防止数据覆盖现象。1.1.3DirectoryØ目录设置要求与Oracle系统用户目录分开。ØDirectory使用权限只赋予需要使用的数据库用户。Ø对应的操作系统目录必须对oracle操作系统用户开放读写权限。Ø定期清理和备份Directory对应的操作系统目录。1.1.4别名Ø对于只读用户，必须创建与表相同名字的别名。Ø别名的访问顺序：public别名->private别名->与表同名的对象。1.1.5DatabaseLinkØ只允许从其它数据库中查询少量数据时使用dblink。北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6不使用dblink更新其它数据库中的数据。1.1PLSQL开发规则1.1.1总体开发原则1.完全按照设计文档进行开发；2.程序模块内聚度要高；外联度要低。3.要有正确、全面的故障对策。4.程序编写结构合理，条理清晰。5.程序名称要按照统一的命名规则进行命名。6.要充分考虑程序的运行效率，包括程序的执行效率和数据库的查询、存储效率。在保证应用的同时要尽量使用效率高的处理方法。7.程序注释要详细、正确、规范。8.除非应用特别需要控制commit和rollback的提交时机，否则必须在存储过程结束时执行显式的commit或者rollback操作。9.程序处理尽量支持7×24小时；对于中断，应用程序提供安全、简单的断点再续处理；10.提供标准、简单的应用输出，为应用维护人员提供明确的进度显示、错误描述和运行结果；为业务人员提供明确、直观的报表、凭证输出。1.1.2程序编写规则1.1.2.1在PL/SQL中使用SQL知识点描述北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6PL/SQL是Oracle公司对SQL的过程化扩展。在标准SQL的基础上面增加了流程控制、游标、异常处理等机制。PL/SQL是一种第四代的高级编程语言，它可以单独使用，也可以嵌入其他宿主语言一起使用。PL/SQL使得用户对Oracle数据库的操作变得非常简单。使用原则1.相关参数(1)CURSOR_SHARING该参数可以设为exact，force和similar，缺省值为exact。(2)使用情况只有在用户的应用已经投入使用的并且SQL语句的共享效果不好，出现librarycachelatch的情况下，我们推荐使用cursor_sharing作为一种补救手段。在进行开发的过程中，设定cursor_sharing＝exact2.如何使用可以提高效率(1)尽量使用变量绑定(2)尽量使用RETURNING子句(3)使用NOCOPY的编译提示。默认情况下，OUT和INOUT参数都是按值传递的。为参数NOCOPY的编译提示表示按照引用传递。举例1.使用变量绑定的例子：在sqlplus中定义变量，使用变量做为查询条件。SQL>DEFINEc=1;SQL>SELECT*FROMt1WHEREc1=&c;2.使用RETURNING子句的例子：更改某员工工资水平的同时返回更改员工的姓名和更改后的薪水。PROCEDUREupdate_salary(emp_idNUMBER)ISnameVARCHAR2(15);new_salNUMBER;BEGINUPDATEempSETsal=sal*1.1WHEREempno=emp_idRETURNINGename,salINTOname,new_sal;END;北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc61.当参数包含大数据结构（如集合，对象类型等），使用按值传递将会消耗过多的内存资源。为了避免这种情况，我们在参数传递时使用NOCOPY的编译提示。DECLARETYPEPlatoonISVARRAY(200)OFSoldier;PROCEDUREreorganize(my_unitINOUTNOCOPYPlatoon)IS……BEGIN……END;1.1.1.1变量声明原则1.使用%TYPE声明变量类型声明时，如果其含义和应用表某字段含义或某变量相同时，使用%TYPE声明。如：creditREAL(7,2);debitcredit%TYPE;my_dnamescott.dept.dname%TYPE;2.使用%ROWTYPE声明记录类型声明时，如果其含义和某应用表行数据或某cursor定义相同时，使用%ROWTYPE声明，如：DECLAREr_empemp%ROWTYPE;CURSORc1ISSELECTdeptno,dname,locFROMdept;r_deptc1%ROWTYPE;3.变量声明格式每行至多包含一条语句，例如：正确写法：v_validBOOLEAN;v_areaVARCHAR2(20);北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6错误写法：v_validBOOLEAN;v_areaVARCHAR2(20);1.1.1.1游标知识点描述为了处理SQL语句，Oracle必须分配一片内存区域，用来存储完成该处理所必需的信息，这就是上下文区域（CONTEXTAREA）。游标（CURSOR）就是一个指向上下文区域的句柄（handle）或指针。必须首先通过游标定位某个位置，然后才能处理该位置的内容。显式游标用来处理返回多于一行的select语句。显式游标可以由用户自定义，并通过open，fetch，close步骤来调用。隐式游标用于处理insert，update，delete和单行select…into语句。隐式游标是系统自定义的，通过PL/SQL引擎打开和关闭，因此，隐式游标不需要用户open，fetch，close。游标变量动态地指向sql语句。它类似于pl/sql变量，在运行时刻可以拥有不同的取值，因此可以实现在运行时刻与不同的语句相关联。游标属性附加在游标名字的后面，但游标属性返回的不是类型，它返回的是在表达式中可以使用的数值。游标属性分以下四种：%FOUND:这是一个布尔属性。如果前一个fetch语句返回一个行，那么它就会返回true，否则，返回false。%NOTFOUND:这是一个布尔属性，如果前一个FETCH语句返回一个行，返回FALSE。仅当前一个FETCH语句没有返回任何行，才返回TRUE。%ISOPEN:这是一个布尔属性，用来确定相关的游标是否被打开。如果已打开，返回TRUE，否则，返回FALSE。%ROWCOUNT:这是个数字属性，它返回到目前位置，由游标返回的行的数目。使用原则北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc61.相关参数(1)cursor_space_for_time(2)open_cursors(3)session_cached_cursors2.如何使用可以提高效率(1)尽量使用变量对于经常使用的SQL语句，推荐在语句中使用变量来代替常量，这样SQL语句可以在SGA中共享，避免了每次执行对语句的分析，提高了内存和CPU的使用效率。(2)空记录测试在应用开发的过程中，经常需要测试表中是否含有符合某种条件的记录，一种常见的错误的做法是使用count(*)的方式判断其值是否为0，这是一个极端低效率的做法，因为它要对全部数据扫描一遍。推荐使用判断游标属性的方法。原来的代码：SELECTcount(*)INTO:v_tmpFROM…WHERE….IFv_tmp>0THEN…………….ENDIF;改进的代码：CURSORc1isSELECTFROM…WHERE…OPENc1;Ifc1%NOTFOUNDTHEN……………ENDIF;3.使用过程之中会经常出现的错误以及解决方案(1)“ora-01555”a.原因：以下处理流程，是Oracle开发的经典错误：北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6step1：先打开cursor（以综合前置为例：select当日所有交易from联机表，十多万条）；step2：从游标中fetch记录，insert入历史表，delete联机表，如此循环，到达若干条记录后commit；step3：再接着如此处理，直到所有记录处理完毕；step4：处理结束后关闭cursor。按照ANSI标准，cursor在提交时无效，必须重新打开。但是Oracle允许编写非标准的SQL，应用程序可以跨commit从cursor中获取行，不过commit后rbs段是可以被重用的，而cursor本身又必须保持读一致性，所以容易出现“rollbacktooold”的错误。a.解决建议：方案1：若干记录commit后立即关闭cursor，再重新打开cursor，继续处理。方案2：只commit一次或尽量减少commit，但需要较大rbs空间。(1)“PLS-00382:expressionisofwrongtype”a.原因：有相同的返回类型，但是变量类型不一样的两个游标变量之间赋值，会产生类型不匹配的错误。例如：DECLARETYPEEmpCurTypISREFCURSORRETURNemp%ROWTYPE;TYPETmpCurTypISREFCURSORRETURNemp%ROWTYPE;emp_cv1EmpCurTyp;emp_cv2TmpCurTyp;BEGINemp_cv2:=emp_cv1;--会产生类型不匹配的错误……END;北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6a.解决建议：将两个游标变量改为相同类型。上面的例子改为如下：DECLARETYPEEmpCurTypISREFCURSORRETURNemp%ROWTYPE;emp_cv1EmpCurTyp;emp_cv2EmpCurTyp;BEGINemp_cv2:=emp_cv1;……END;举例1.打开游标时，通过游标属性判断游标纪录是否为空的方法。DECLARECURSORc1ISSELECTapp_idFROMapp_defWHEREapp_enname=p_app_enname;BEGINOPENc1;EXCEPTIONWHENno_data_foundTHENBEGIN......END;END;2.打开游标前判断游标纪录是否为空的方法。DECLARECURSORc1ISSELECTapp_idFROMapp_defWHEREapp_enname=p_app_enname;北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6v_countNUMBER;BEGINSELECTCOUNT(*)INTOv_countFROMapp_defWHEREapp_enname=p_app_enname;IFv_count=0THENOPENc1;......ENDIF;END;1.1.1.1集合知识点描述集合是一组类型相同的元素。每个元素有一个唯一的下标，下标确定元素在集合中的位置。数组：数组包含固定数目（数组长度）的元素，可以在运行过程中改变数组长度。嵌套表：嵌套表可以包含任意数目的元素，嵌套表使用顺序数字做为下标。索引表：类似于其他编程语言中的散列表，索引表的下标可以是数字或者字符串。使用原则1.使用情况(1)嵌套表和索引表的比较：在许多方面嵌套表和索引表有相似之处，例如：两种表数据类型拥有相同的结构，他们都使用下标记号进行访问。但是两者还是有一些不同之处：a.嵌套表存储可以为数据库的列，但是索引表不行。b.北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6嵌套表适合于重要的数据关系需要持久保存的情况。索引表适合于相对较小的集合，这些集合可以在内存构建。索引表还有一个优点在于下标的灵活性。索引表的下标可以是负数，可以不是顺序的，而且可以是字符串而非数字。(1)嵌套表和数组的比较：a.如果预先知道元素的数目而且数组中的元素经常需要一起顺序访问，数组是一个很好的选择。数组可以是行内存储，也可以是行外存储，可以一次更新或返回数组中所有的元素。b.在嵌套表中，可以删除任意元素，而不仅仅只能去掉最后一个元素。嵌套表存储在行外，每个嵌套表和一个系统产生的数据库表相关联。嵌套表适用于查询和更新只影响集合中某些元素。1.如何使用可以提高效率使用批量绑定提高性能所谓绑定就是指为SQL语句中的PL/SQL变量赋值。PL/SQL引擎执行过程控制语句而将SQL语句传给SQL引擎，SQL引擎执行SQL语句并将执行的结果返回给PL/SQL引擎。在PL/SQL引擎和SQL引擎之间进行频繁的上下文切换会严重影响性能。如下面的PL/SQL块所示，循环执行某个SQL语句，该SQL语句是单独处理集合的一个元素。FOR循环的每一次迭代，DELETE语句都会发往SQL引擎。Oracle提供一下一些途径来减少PL/SQL引擎和SQL引擎之间的上下文切换。(1)使用FORALL语句。FORALL关键字通知PL/SQL引擎在将PL/SQL变量值传入SQL语句前进行批量绑定。虽然FORALL语句在形式上是循环迭代，但在实质上它不是FOR循环语句。FORALL的语法格式如下，其中index只能是集合的下标，sql_statement必须有引用集合元素的INSERT，UPDATE或者DELETE语句：FORALLindexINlower_bound..upper_boundsql_statement;(2)使用BULKCOLLECT子句将查询结果返回到集合中。关键字BULKCOLLECT通知SQL引擎在将结果返回到PL/SQL引擎前进行批量绑定，这里的批量绑定是指将输出结果绑定到集合中。BULKCOLLECT可以用在SELECTINTO，FETCHINTO和RETURNINGINTO语句中。BULKCOLLECT的语法如下所示：北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6...BULKCOLLECTINTOcollection_name[,collection_name]...1.使用过程之中会经常出现的错误以及解决方案(1)ORA-06531:Referencetouninitializedcollectiona.范例一：DECLARETYPENumListISTABLEOFNUMBER;numsNumList;--atomicallynullBEGINnums(1):=1;--raisesCOLLECTION_IS_NULL(1)END;该例会报ora-06531的错误。b.原因：定义集和之后，要求初始化集合。该语句在对集合中的元素操作（语句1）之前，没有对集合进行初始化。c.解决建议：对集合或集合中的元素进行操作前，首先初始化集合，例如，在语句（1）之前加上初始化语句：nums:=NumList(null,null);(2)ORA-06502:PL/SQL:numericorvalueerror:NULLindextablekeyvaluea.范例二：DECLARETYPENumListISTABLEOFNUMBER;numsNumList;--atomicallynullBEGINnums:=NumList(1,3,6);--initializetablenums(null):=3;END;该例会报错：ORA-06502:PL/SQL:numericorvalueerror:NULLindextablekeyvalueb.范例三：北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6DECLARETYPENumListISTABLEOFNUMBER;numsNumList;--atomicallynullaVARCHAR2(2);BEGINnums:=NumList(1,3,6);--initializetablea:="a";(1)nums(a):=3;(2)END;该例会报错：ORA-06502:PL/SQL:numericorvalueerror:charactertonumberconversionerror.a.原因：下标的类型不对，或者下标的转换未成功。例如范例二中下标为”null”，不是number类型，也不能转换成number类型，因此会报以上错误。例如范例三中下标a的值为’a’，该值不能转换成number类型，因此会报以上错误。b.解决建议：将下标的值改为数字，或者number型变量，或者可以转换为number型变量的字符型变量。例如，将范例三中的语句(1)改为a:=’2’，或者直接将语句(2)改为nums(2):=3。(1)ORA-06532:Subscriptoutsideoflimita.范例四：DECLARETYPENumListISTABLEOFNUMBER;numsNumList;--atomicallynullBEGINnums:=NumList(0,0);--initializetable（1）nums(0):=3;--（2）北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6nums(3):=3;--（3）END;语句（2）和（3）都会报这个错误。a.原因：初始化语句初始了n个元素，则下标的范围就是1~n，超出该范围就会报错。范例四中，初始化语句（1）初始化该集合有两个元素，因此，下标的范围为1~2，超出这个范围就会报错。b.解决建议：将下标的值改为正确的范围以内。(1)ORA-01403:nodatafounda.范例五：DECLARETYPENumListISTABLEOFNUMBER;numsNumList;--atomicallynullBEGINnums:=NumList(0,0);--initializetablenums.DELETE(1);--deleteelement1(1)IFnums(1)=1THENdbms_output.put_line("successful!");--(2)ENDIF;END;该语句会报ora-01403的错误。b.原因：语句(1)中已将nums(1)元素删去，nums(1)元素已经没有数据了，因此语句（2）中读num(1)元素的值就会报错。c.解决建议：如果想让某元素中没有数据，可以给它赋空值，以后对该值的读取就不会出错。例如范例五中，语句（1）可以换为：nums(1):=null。举例北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc61.下面的DELETE语句只发往SQL引擎一次，即使它执行了三次DELETE操作。DECLARETYPENumListISVARRAY(20)OFNUMBER;deptsNumList:=NumList(10,30,70);--departmentnumbersBEGINFORALLiINdepts.FIRST..depts.LASTDELETEFROMempWHEREdeptno=depts(i);END;2.在下面的例子的例子中，SQL引擎将EMP表中所有的EMPNO，EMPNAME数据装载到嵌套表中，然后将嵌套表返回给PL/SQL引擎。DECLARETYPENumTabISTABLEOFemp.empno%TYPE;TYPENameTabISTABLEOFemp.ename%TYPE;enumsNumTab;--noneedtoinitializenamesNameTab;BEGINSELECTempno,enameBULKCOLLECTINTOenums,namesFROMemp;...END;1.1.1.1动态PL/SQL知识点描述大多数PL/SQL程序所做的工作都是特定的、预知的工作。例如，一个存储过程可能接受一个员工号码做为输入参数，然后更新该员工的薪水。在这种情况下，UPDATE语句的全文在编译时是确定的。这样的语句是不会在执行阶段发生变化的，这种SQL语句我们称之为静态SQL语句。北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6但是有些时候，程序必须在执行阶段处理一个变化的SQL语句。例如，一个通用的报表程序为了产生不同的报表必须运行不同的SQL语句。这种语句很可能在执行阶段发生改变，这种SQL语句我们称之为动态SQL语句。动态SQL语句存储在字符串中，程序在执行阶段建立这个语句。这些字符串必须包含合法的SQL语句，要绑定的变量前要加冒号，变量的名称是无关的。例如，下面两个语句是没有区别的："DELETEFROMempWHEREsal>:my_salANDcomm<:my_comm""DELETEFROMempWHEREsal>:sANDcomm<:c"拼装SQL语句执行的性质与动态SQL语句相同。如果拼装SQL语句的源如果有些是别人输入的、外部文件读取的，则不能直接拼上去，必须先检查，防止攻击。防止攻击的办法有：1.尽量使用绑定变量的的动态sql；2.对输入条件进行字符检查使用原则1.使用限制由于使用动态sql，在编译阶段是分析不出该语句的。因此每次执行都需要编译，对系统运行产生额外的性能开销。所以，不建议联机系统的交易程序使用动态SQL。2.使用情况由于在通常的PL/SQL中只能使用数据操作语言DML和事务控制语言TCL而不能使用其它一些命令如数据定义语言DDL和系统控制命令，当出现这种需求是一个做法是调用外部任务，另外一个做法就是使用动态的SQL。动态SQL并不在客户端被解释转换，而是由客户端的程序传输到服务器端后在服务器端解释并执行。使用动态SQL的最常见的用法有：使用truncatetable命令代替deletefromtable的操作、在系统进行日结、月结、年终结算时创建新的数据表，创建应用系统使用的临时表等，下面是通过动态SQL来使用truncatetable命令的方法：...CREATEORREPLACEPROCEDUREp_truncate_table北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6(table_namevarchar2(30))ascursor_handleINTEGER;no_query_sql_commandVARCHAR2(200);BEGINno_query_sql_command:="truncatetable’||table_name;cursor_handle:=dbms_sql.open_cursor;dbms_sql.parse(cursor_handle,no_query_sql_command,1);cursor_handle:=dbms_sql.execute(cursor_handle);dbms_sql.close_cursor(cursor_handle);END;/...也可以直接使用EXECUTEIMMEDIATE来执行，如：...BEGINEXECUTEIMMEDIATE"truncatetable’||table_name;;END;...应当说明的是动态SQL的使用在不同的版本间稍有不同，在使用是需要在不同的版本上做测试。1.如何使用可以提高效率(1)尽量使用变量绑定联机交易的事务特点是执行的频度高，执行时间短。此类交易的sql语句都建议使用变量绑定。因为使用变量绑定的方式编写的sql语句，能大大减少sql语句硬分析的性能消耗。(2)使用批量动态SQL（BULKFETCH语句，BULKEXECUTEIMMEDIATE语句，FORALL语句，COLLECTINTO语句，RETURNINGINTO子句）2.使用过程之中会经常出现的错误以及解决方案北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6(1)“ORA-00903:invalidtablename”a.原因：在动态PL/SQL中数据对象名称不能使用变量绑定。例如：下面的存储过程CREATEPROCEDUREdrop_table(table_nameINVARCHAR2)ASBEGINEXECUTEIMMEDIATE"DROPTABLE:tab"USINGtable_name;END;b.解决方法：将变量绑定改为直接字符串拼凑。CREATEPROCEDUREdrop_table(table_nameINVARCHAR2)ASBEGINEXECUTEIMMEDIATE"DROPTABLE"||table_name;END;举例1.在动态SQL中使用变量绑定的例子CREATEPROCEDUREfire_employee(emp_idNUMBER)ASBEGINEXECUTEIMMEDIATE"DELETEFROMempWHEREempno=:num"USINGemp_id;END;1.1.1.1对象知识点描述北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6对象（object）是一个用户定义的复杂的数据类型，其数据结构包含一些变量，以及对这些变量进行处理的函数和过程。组成数据结构的变量，称为“属性”（attributes）。对属性进行操作的过程和函数，称为“方法”(methods)。当创建一个对象时，实际上是创建了一个对真实世界中的对象的抽象模板。例如，对一个雇员创建对象，这个对象包括的属性有：姓名，部门，职位，等等；包括的方法有：工资计算，考勤计算，等等。使用对象时，需要创建实例（instance），通过实例来调用对象中的方法。在真实世界中，不难理解这样做的目的。还是以雇员对象为例，只有一个对象，确有很多个雇员，每个雇员的属性都不相同，所以，要为每个雇员创建实例，他们可以共享“雇员对象”这个数据结构，而分别拥有自己的数据。使用原则1.使用过程之中会经常出现的错误以及解决方案(1)ORA-30625:methoddispatchonNULLSELFargumentisdisalloweda.范例：DECLAREEagleemployee;--创建实例Jackyemployee;--创建实例BEGINDbms_output.put_line(Eagle.salary(eagle.rank,eagle.dept));--通过实例调用对象中的方法END;该语句会报ora-30625的错误。b.原因：实例已创建，但没有初始化。c.解决建议：在调用方法前，初始化实例。以上范例中，加上初始化语句：Eagle:=NEWemployee("EAGLE",24,7,"TACH");举例例一：创建对象CREATEORREPLACETYPEemployeeASOBJECT(--定义属性（attribute）NameVARCHAR2(40),--定义方法(method)北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6MEMBERFUNCTIONsalary(rankNUMBER,deptVARCHAR2)RETURNNUMBER,);CREATEORREPLACETYPEBODYemployeeASMEMBERFUNCTIONsalary(rankNUMBER,deptVARCHAR2)RETURNAGEISBEGIN…………ENDsalary;END;例二：使用对象DECLAREEagleemployee;--创建实例Jackyemployee;--创建实例BEGINEagle:=NEWemployee("EAGLE",24,7,"TACH");--初始化实例Jacky:=NEWemployee("JACKY",36,1,"MANAGE");--初始化实例Dbms_output.put_line(Eagle.salary(eagle.rank,eagle.dept));--通过实例调用对象中的方法END;1.1.1.1大对象类型（LOB）知识点描述一个LOB是一个存储大量数据的数据库字段，例如图形文件或长的文本形式的文档。Oracle提供了四种不同类型的LOB：CLOB、NCLOB、BLOB、BFILE。北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6LOB类型说明CLOB用于存储单字节字符数据BLOB存储没有结构的二进制数据NCLOB用于存储定宽的多字节国家字符集数据BFILE用于对Oracle数据库以外存储的大型二进制文件进行只读形式的访问。LOB数据不是以内联的方式（inline）存储在数据库表中。LOB数据是存储在一个单独的位置上，同时一个“LOB定位符”存储在原始表中。该定位符是一个指向实际数据的指针，实际数据可以存储在和原始数据有不同存储参数的表空间中。使用原则1.使用情况LOB是用于存储非结构化数据的。所谓非结构化数据是指不能被分解为标准组件的数据。例如，一个员工可以分解为姓名（字符串），标识（数字），薪水等等。但是，如果给的是一张图片，会发现数据是由一长串0和1的数字流，在数据存储方面，他们不能分解为良好的结构。非结构化的数据往往是非常大的，如文本、图片、视频片断或者音乐等等。一个典型的员工纪录可以有几百个字节，但是即使是少量的多媒体数据也可能有它几千倍那么大。LOB类型帮助支持Internet应用，随着Internet和内容丰富应用的发展，数据支持这样一种数据类型是非常有需要的：1）可以存储非结构化数据；2）优化处理大量的非结构化数据；3）为存储在数据库内或者数据库外非结构化数据提供统一的访问方式。由于BLOB没有字符集转换，因此当几种LOB都能满足应用要求时，推荐使用BLOB。1.如何使用可以提高效率(1)SQL*Loader性能：加载数据到内部LOB。下表我们可以看到加载数据到内部LOB的各种方法的相对性能的比较，在后面我们将一一列举每种加载方法。加载方法相对性能字段长度预先确定最高北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6字段之间由分隔符分隔低长度-值对字段高每个文件一个LOB高(1)选择内联存储和外联存储的比较。一般情况下，当LOB的容量小于4K时，Oracle建议对LOB进行内联存储。内联存储在数据操作时效率更高。但是当需要进行大量的基本表操作时，我们不希望LOB进行内联存储，如全表扫描、范围扫描或者在表的非LOB列上面有大量的更新、查询操作。(2)提高JDBC驱动加载BLOB和CLOB的性能使用JDBCThin驱动向LOB中加载数据是比较慢的，因为JDBCThin驱动实际上是使用DBMS_LOB包。而使用JDBCOCI和JDBCserver-sideinternal驱动使用本地的LOBAPI，加载数据会比JDBCThin驱动快。因为使用本地LOBAPI没有JDBCThin驱动实现带来的一些额外消耗。Oracle建议使用InputStream和OutputStream来访问和操作LOB数据。因为使用流的方式，JDBC驱动可以合理的控制缓存以减少网络的数据往返。DBMS_LOB.LOADFROMFILE()加载LOB数据的效率比DBMS_LOB.WRITE()高，但使用DBMS_LOB.LOADFROMFILE()加载数据要求数据必须放在服务端。(3)适时的释放临时LOB。在PL/SQL，C(OCI)和Java中，当SQL语句在LOB列上面操作时会返回临时LOB作为结果，临时LOB在PL/SQL块结束时会自动释放。可以在任何时候释放不需要的临时LOB，以释放系统系统资源和临时表空间。如果不合理的释放SQL语句返回的临时LOB，临时表空间将会被过度的消耗，而系统性能将会下降。1.使用过程之中会经常出现的错误以及解决方案(1)更改LOB段的存储参数时出现ORA-904错误。a.问题描述：更改一个LOB对象的MAXEXTENTS存储参数时得到904错误，SQL>CREATETABLEt_lob北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc62(DOCUMENT_NRNUMBER(16,0)NOTNULL,3DOCUMENT_BLOBBLOBNOTNULL4)5STORAGE(INITIAL100k6NEXT100K7PCTINCREASE08MAXEXTENTS1009)10TABLESPACEusers;Tablecreated.SQL>SELECTSEGMENT_NAME,SEGMENT_TYPE,INITIAL_EXTENT,2NEXT_EXTENT,PCT_INCREASE,MAX_EXTENTS3FROMUSER_SEGMENTS;SEGMENT_NAMESEGMENT_TYPEINITIALNEXT_EXTENTPCTMAX_EXTENTS---------------------------------------------------------------------T_LOBTABLE1064961064960100SYS_IL0000030066C00002$$LOBINDEX4096040960502147483645SYS_LOB0000030066C00002$$LOBSEGMENT409604096050505SQL>ALTERTABLET_LOBMODIFYLOB(SYS_LOB0000030066C00002$$)2(STORAGE(MAXEXTENTS200));ALTERTABLET_LOBMODIFYLOB(SYS_LOB0000030066C00002$$)*北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6ERRORatline1:ORA-00904:"SYS_LOB0000030066C00002$$":invalididentifiera.解决办法：更改LOB对象的列名而不要直接使用LOB段的名字SQL>SELECTTABLE_NAME,COLUMN_NAMEFROMUSER_LOBS2WHERESEGMENT_NAME="SYS_LOB0000030066C00002$$";TABLE_NAMECOLUMN_NAME----------------------------------------------T_LOBDOCUMENT_BLOBSQL>ALTERTABLET_LOBMODIFYLOB(DOCUMENT_BLOB)2(STORAGE(MAXEXTENTS200));Tablealtered.Explanation-----------TheALTERTABLEisacommandthatallowsyoutomodifylogicalentities,notphysicalentities.(1)更改LOB时出现ORA-22853错误。a.问题描述在执行下属语句时出现错误MODIFYLOBlobitem(storage_clausepctversioncachelogging)ORA-22853:invalidLOBstorageoptionspecificationCause:ALOBstorageoptionwasnotspecifiedAction:SpecifyoneofCHUNK,PCTVERSION,CACHE,NOCACHE,北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6TABLESPACE,STORAGE,INDEXaspartoftheLOBstorageclause.a.解决办法：把LOB对象的名字放在括号内，正确的命令是：MODIFYLOB(lobitem)(storage_clausepctversioncachelogging)举例1.加载字段长度预先确定的LOB数据在控制文件中，加载入列的LOB的数据长度是确定的，字段长度预先确定可以使数据解析器非常高效的工作。不幸的是，通常我们很难保证所有的LOB的数据长度是相等的。ControlFile：LOADDATAINFILE"sample4.dat"INTOTABLEMultimedia_tabFIELDSTERMINATEDBY","(Clip_IDINTEGEREXTERNAL(5),StoryLOBFILE(CONSTANT"FirstStory1.txt")CHAR(32))DataFile(sample4.dat)：007,008,SecondaryDataFile(FirstStory1.txt)：Onceuponthetime...Theend,Uponanothertime...Theend,2.字段之间由分隔符分隔的数据的加载在LOBFILE文件中，LOB数据由分隔符分隔，这样就允许不同长度的LOB加载入相同的列。当然，高度的灵活性是以牺牲性能为代价的。加载这种格式的数据相对而言是比较慢的，因为加载器必须扫描所有数据并查找分隔符字符串。北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6ControlFileLOADDATAINFILE"sample5.dat"INTOTABLEMultimedia_tabFIELDSTERMINATEDBY","(Clip_IDINTEGEREXTERNAL(5),StoryLOBFILE(CONSTANT"FirstStory2.txt")CHAR(2000)TERMINATEDBY"n")DataFile(sample5.dat)007,008,SecondaryDataFile(FirstStory2.txt)Onceuponatime...Theend.Onceuponanothertime...Theend.1.加载字段是长度-值对的LOB数据每个LOB数据前有一个数字标识该LOB的长度。这种方式的性能会比分隔符分隔数据的方式高效，但是这种方式要求在加载数据之前知道每个LOB的长度。ControlFileLOADDATAINFILE"sample6.dat"INTOTABLEMultimedia_tabFIELDSTERMINATEDBY","(Clip_IDINTEGEREXTERNAL(5),StoryLOBFILE(CONSTANT"FirstStory3.txt")VARCHARC(4,2000))DataFile(sample6.dat)007,008,SecondaryDataFile(FirstStory3.txt)北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc60031Onceuponatime...Theend.00001.每个LOBFILE仅包含一个LOB的加载方式。ControlFileLOADDATAINFILE"sample3.dat"INTOTABLEMultimedia_tabREPLACEFIELDSTERMINATEDBY","(Clip_IDINTEGEREXTERNAL(5),ext_FileNameFILLERCHAR(40),StoryLOBFILE(ext_FileName)TERMINATEDBYEOF)DataFile(sample3.dat)007,FirstStory.txt,008,/tmp/SecondStory.txt,SecondaryDataFile(FirstStory.txt)Onceuponatime...Theend.2.将一个GIF文件john.gif写入BLOB的例子。/**将一个GIF文件john.gif写入BLOB的例子。*/importjava.sql.*;importjava.io.*;importjava.util.*;importOracle.jdbc.driver.*;importOracle.sql.*;publicclassLobExample{北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6publicstaticvoidmain(Stringargs[])throwsException{DriverManager.registerDriver(newOracle.jdbc.driver.OracleDriver());//ConnecttothedatabaseConnectionconn=DriverManager.getConnection("jdbc:Oracle:oci9:@","scott","tiger");conn.setAutoCommit(false);Statementstmt=conn.createStatement();try{stmt.execute("droptablepersons");//CreateatablecontainingaBLOBandaCLOBstmt.execute("createtablepersons(namevarchar2(30),pictureblob)");stmt.execute("insertintopersonsvalues("John",EMPTY_BLOB())");}catch(SQLExceptione){//Anexceptioncouldberaisedhereifthetabledidnotexistalready.}//SelecttheBLOBResultSetrset=stmt.executeQuery("selectpicturefrompersonswherename="John"");if(rset.next()){北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6//GettheBLOBlocatorfromthetableBLOBblob=((OracleResultSet)rset).getBLOB(1);OutputStreamostream=blob.getBinaryOutputStream();//Declareafilehandlerforthejohn.giffileFilebinaryFile=newFile("john.gif");FileInputStreamistream=newFileInputStream(binaryFile);//Createatemporybufferbyte[]buffer=newbyte[1024];intlength=0;//Usetheread()methodtoreadtheGIFfiletothebytearraybuffer,//thenusethewrite()methodtowriteittotheBLOB.while((length=istream.read(buffer))!=-1)ostream.write(buffer,0,length);//Closetheinputstreamandoutputstreamistream.close();ostream.close();}//Closeallresourcesrset.close();stmt.close();conn.close();}}1.1.1.1包（PACKAGE）知识点描述北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6包是一个可以将相关对象存储在一起的PL/SQL结构，包含了两个分离的部件：规范(specification)和主体(body)，每个部件都单独被存储在数据字典中。包中可以包含过程、函数、游标和变量。将这些对象放入一个包中的一个好处就是可以从其他的PL/SQL快中引用它们，这样包也提供了用于PL/SQL的全局变量。使用原则1.使用情况有以下情况发生时，需要使用包：(1)存在私有函数（或过程）或私有变量，只能被某一函数（或过程）调用时，将这个私有的函数（或过程）或私有变量放入调用主函数（或过程）所在的包内。(2)函数（或过程）的参数里有自定义的类型时，应该建立包，将该类型的定义和该函数（或过程）的定义都放入包中。(3)直观的说，当具有一些能够处理相关的任务的程序代码同时希望包装共享该代码时可以使用包。2.如何使用可以提高效率(1)使包尽量大众化，使其可以在未来的应用中被重用。(2)尽量使用Oracle提供的包，注意不要重复创建Oracle已有的包。(3)包头反映出应用的设计。应该在包头只声明用户或其他开发人员必须用到的类型，项和子程序，而将包自己用的对象作为私有变量放在包体中。(4)当代码改变时，为减少重编译，除非必要，尽量少在包头中声明对象。因为对包体做改变时，不要求重编译，而对包头做改变时，要求重编译。(5)减少包中静态的全局变量的使用，避免操作的相互影响。1.1.1故障处理规则知识点描述PL/SQL中，一个警告或错误条件被称为“异常”，这个条件可以是系统本身的，也可以由用户自定义。当满足条件时，程序从当前块中跳出，进入异常处理块，程序的异常处理由用户自己定义。北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6使用异常处理的目的：防止错误的结果被带到外部程序，并且使出错的程序异常处理后退出，而不是中断后直接返回操作系统，从而提高应用程序的健壮性。使用情况在使用的PLSQL块中，如果使用了不能返回的确定结果的SQL语句，应用要在EXCEPTION中对程序可能出现的异常进行处理，避免出现未处理的出错被传递到外层块，导致程序逻辑错误。对于系统已经定义了的异常，应用可以直接使用，对于用户自行定义的异常需要使用pragmaexception_init(exception_name，-SQLCODE)方式来初始化。对于继续处理的EXCEPTION，可以不记录错误堆栈，对于无法继续处理的程序，EXCEPTION中至少要获取程序的返回码，返回错误码，错误描述，其名称分别定为out_retcode，out_errcode，out_errm。为了能够进一步分析程序出现异常的原因，最好能够获得出现异常时的调用错误堆栈。常见错误及解决方案很多情况下，特别是含有DML语句的程序中，系统希望一个存储过程是一个原子操作，要么不执行，要么完全执行。由于出错后程序跳出当前块，进入异常处理块，所以有可能因为程序只执行了一半而造成数据不一致。为防止这种情况发生，可以将commit尽量放在块的末尾，或者可能发生出错的地方之后。并在异常处理中使用rollback。将没做完的事务回滚。在应用设计时应该设置一个错误信息表，出错后，必须向这个表写入错误信息。写入的方式建议使用独立会话，把错误信息插入到错误信息表。具体独立会话的使用请参考下面“批量程序内部代码运行监控”章节。举例EXCEPTIONWHENNO_DATA_FOUNDTHENROLLBACK;out_retcode:="L9003";out_errcode:=SQLCODE;out_errm:=SQLERRM;<把错误代码插入到错误信息表>北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6RETURN;WHENOTHERSTHENROLLBACK;out_retcode:="L9000";out_errcode:=SQLCODE;out_errm:=SQLERRM;<把错误代码插入到错误信息表>RETURN;北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc61.1SQL语句编写规则1.1.1查询语句的使用原则1.1.1.1索引的正确使用合理的使用正确的索引是提高系统执行效率的关键因素，对索引的使用需要注意以下一些问题：1.过度索引一般情况下，使用索引可以缩短查询语句的执行时间，提高系统的执行效率，但是要避免以下两种过度索引的情况出现：(1)对一个表建立了过多的索引，从而造成维护索引所需要的时间超过使用索引所降低的时间，从而造成整个系统效率的下降，这一般发生在对一些进行大量更新的表上面。因此一个联机表上的索引，最多不要超过5个。(2)由于索引数据的区分度不够，造成了使用索引而引起的效率的下降，这一般发生在对数据进行大的统计分析的时候。可以通过指定全表扫描等提示(hint)来避免。2.LIKE运算符在应用程序中为了使用方便，对字符型变量进行比较时经常使用LIKE运算符进行字符模式的匹配。需要注意的是对于LIKE运算，如果通配符%出现在字符串的尾部或中间，LIKE运算符将可以使用索引进行字符串的匹配，否则如果通配符%出现在字符串的开始，则LIKE必须使用全表扫描的方式去匹配字符串，这将产生较大的系统负荷。一般情况下，为了提高系统的效率，我们希望用户能够在通配符的左端提供较多的数据信息以降低查询的数量。3.NULL值北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6NULL值是系统中目前尚无法确定的值，在Oracle数据库系统中NULL是一个比所有的确定值都大的值，然而又不能用大于小于等于运算符来比较，对NULL值的处理只能用是与否来判定，所有的对NULL值的判定都会引起全表扫描，除非同时使用其它的查询条件。1.复合索引复合索引是使用多个数据列的索引，第一个字段的数据区分度非常重要，也是影响一个联合索引效率的关键所在。1.1.1.1.1改写查询语句1.关联子查询与非关联子查询对于一个关联子查询，子查询是每行外部查询的记录都要计算一次，然而对于一个非关联子查询，子查询只会执行一次，而且结果集被保存在内存中。因此，通常在外部查询返回相对较少的记录时，关联子查询比非关联子查询执行得更快；而子查询中只有少量的记录的时候，则非关联子查询将会比关联子查询执行得更快。2.尽量用相同的数据类型的数据进行比较，以避免发生数据转换SQL语言对于数据类型不像JAVA和C++那样进行严格的数据类型检查，不同种数据间可以进行某些运算，但是在做数据操作时需要数据库进行隐含的类型转换，在大数据量的查询中，由于要对每一个数据项做同样的操作，会造成时间和CPU处理能力的浪费。实际应用中通常发生的隐含的数据类型的转换有：(1)字符型到数字型的转换，如：SELECT‘1234’+3FROMDUAL等(2)数字型到字符型的转换，如：UPDATEDEPTSETEMPNO=5678等(3)日期型到字符型的转换，如：UPDATEEMPSETDNAME=SYSDATE等上述的转换都是隐含发生的，在实际使用中要避免使用不同类型的数据操作。1.1.1.1.2减少排序的发生排序是数据库中执行频度比较大的一种操作，根据排序执行的范围不同又可以分为内排序和外排序。我们希望数据库中的排序操作的数量能够被尽量的减少同时每个排序的时间能够缩短。为此我们可以：1.使用UNIONALL来代替UNION2.北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6添加索引。在表连接的时候使用索引可以避免排序的发生，比如添加了合适的索引，可以使连接方式由排序合并连接(SortMergeJoin)转变为索引的嵌套循环连接(IndexedNesttedLoopJoin)。1.在DISTINCT，GROUPBY，ORDERBY子句涉及到的列上创建索引。2.使用较大SORT_AREA_SIZE3.在用户的临时表空间上使用大的extent大小。1.1.1.1.1使用并行查询并行查询适合下列情况：1.全表扫描的查询语句2.返回大数据量的查询所改造的语句3.其它一些数据操作中的查询子句对于较大的数据量的查询，我们可以使用提示(hint)来强制数据库使用并行查询，在Oracle数据库中，并行查询的优先级为语句提示(hint)，表的定义，数据库初始化参数。1.1.1.1.2减少死锁的发生在Oracle数据库中大量的数据库的锁都是行级锁，不同的会话间竞争同一条记录的可能性较小，同时Oracle数据库中提供了自动的死锁检测机制来避免数据库的死锁，保证数据库系统的可用性。因此一般情况下应用系统不需要特殊的设计来解决系统的死锁问题，但是在下列情况下系统可能出现死锁：1.表A上的列n上有一个索引，表B上的列m使用A上的列n作为外键，然后表A的列n上的索引被删除，此时更新表B上列m将造成对表A的表级锁，会导致死锁的发生。2.应用大量的使用SELECT……FORUPDATE语句造成系统不必要的加锁。对于第一种情况要对出现死锁的相关表进行检查，确认是否相关索引被错误的删除。对于第二种情况要修改应用，避免对数据的不必要的加锁。1.1.1.1.3集合运算符的使用Oracle数据库的集合运算包括:UNION，UNIONALL，INTERSECT和MINUS操作。北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6一般情况下当两个集合中的数据都比较多时，集合运算都是比较耗时的操作，使用时需要谨慎小心。如果可能，可以使用UNIONALL操作代替UNION操作。1.1.1.1使用连接方式的原则1.1.1.1.1嵌套循环连接（NESTEDLOOPJOIN）1.知识点描述嵌套循环连接操作关系到两个表，一个内部表和一个外部表。Oracle比较内部数据集的每一条记录和外部数据集的每一条记录，并返回满足条件的记录。嵌套循环连接通常会产生巨大的数据量，所以对嵌套循环连接的使用要有严格的限制。当一个嵌套循环连接的内部表中存在索引的情况，嵌套循环连接变为改进的有索引的嵌套循环连接(INDEXEDNESTEDLOOPJOIN)，通常有索引的嵌套循环连接在产生较小的数据量的情况下可以较快的执行。在使用有索引的嵌套循环连接是必须确保在查询中使用了正确的驱动表和正确的驱动数据集，通常情况下我们使用包含数据量较小的表作为驱动表。一般如果我们使用基于成本的优化器，系统会自动选择驱动表，如果是使用基于规则的优化器，则后表作为驱动表。2.应用原则一般的嵌套循环连接的速度较慢，产生的数据量较大，应该严格控制其使用。在使用有索引的嵌套循环连接时，必须保证其驱动表有合适的索引，最好为主键或唯一键，同时希望在另外一张表在相同的列上有索引。3.举例下面给出了两种连接的例子：对于不存在索引的表EMP和DEPT执行以下操作：SELECTcount(*)FROMdept,empWHEREdept.deptno=emp.deptnoandempno=30为NESTEDLOOP北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6JOIN，因为数据库需要对表DEPT中所有的行进行处理。如果此时JOB表的JOBNO列上有索引则上述查询的方式转变为有索引的嵌套循环连接(IndexedNestedLoopJoin)****************************************************NestedLoopJoin*--tableemphasindexonjobnocolumn*--tablejobhasindexonjobnocolumn*****************************************************SQL>SETAUTOTONEXPSQL>SELECTJOBNAME,SUM(SAL)2FROMEMPE,JOBJ3WHEREE.JOBNO=J.JOBNO4ANDJ.JOBNO<35GROUPBYJ.JOBNAME;JOBNAMESUM(SAL)--------------------------------------------------jobname127725jobname226025ExecutionPlan----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE10SORT(GROUPBY)21TABLEACCESS(BYINDEXROWID)OF"EMP"32NESTEDLOOPS43TABLEACCESS(BYINDEXROWID)OF"JOB"54INDEX(RANGESCAN)OF"IND_JOB_JOBNO"(UNIQUE)63INDEX(RANGESCAN)OF"IND_EMP_JOBNO"(NON-UNIQUE)北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc61.1.1.1.1散列连接(HashJoin)1.知识点描述散列连接将驱动表加载进内存，使用散列技术将驱动表与较大的表进行连接，连接过程中，对大表的访问使用了散列访问。散列连接可以提高等连接的速度。如果可用的散列空间内存足够大，可以加载构建输入，那么散列连接算法能够很好地运行简单的散列连接，但是并不需要将整个输入放入hash_area_size内存。如果散列连接中较小的驱动表无法放入hash_area_size，那么Oracle将拆分该散列连接，并使用temp表空间中的临时段来管理这个溢出。Oracle推荐将驱动表的hash_area_size设置为驱动表字节总数的1.6倍。2.应用原则一般的散列连接发生在一个大表和一个小表做连接的时候，此时小表中的数据全部被读入内存，其处理的速度较快。3.举例*****************************************************HashJoin*--tableemphas450000+rows*--tabledepthasround1000rows*****************************************************SQL>SETAUTOTONEXPSQL>SELECTCOUNT(*)"NUMBEROFDEPT",SUM(SAL)"TOTALSALARY"2FROMEMPE,DEPTD3WHEREE.DEPTNO=D.DEPTNO4ANDDNAMELIKE"DNAMELINE100%"5;numberofdepttotalsalary--------------------------2602609357.21ExecutionPlan北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE(Cost=281Card=1Bytes=32)10SORT(AGGREGATE)21HASHJOIN(Cost=281Card=1062Bytes=33984)32TABLEACCESS(FULL)OF"DEPT"(Cost=4Card=106Bytes=2650)42TABLEACCESS(FULL)OF"EMP"(Cost=265Card=458752Bytes=3211264)1.1.1.1.1排序合并连接(SortMergeJoin)1.知识点描述排序合并连接是指从目标表中读取两个记录数据集，并使用连接字段将两个记录集分别排序的操作。合并过程将来自一个数据集的每一条记录同来自另一个数据集与之匹配的记录相连接，并返回记录数据集的交集。排序合并连接有几种不同的排序方法：外部合并连接，反合并连接和半合并连接。这些不同的排列方法使得合并步骤服务于不同的目的，可以是找到记录数据集的交集，也可以是找到满足SQL语句中WHERE子句条件的那些记录。2.应用原则一般的排序合并连接是在散列连接不能达到应用的要求或Oracle优化器认为排序合并连接效率更高的情况下使用。在下述的条件下排序合并连接被使用：(1)数据表之间的连接不是等值连接而是其它连接(2)数据库使用的优化模式是基于RBO而不是CBO3.举例在下述的查询中******************************************************SortMergeJoin*--tableemphasnoindexondeptnocolumn*--tabledepthasnoindexondeptnocolumn北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6*****************************************************SQL>setautotonexpSQL>selectdname,sum(sal)2fromdeptd,empe3whered.deptno=e.deptno4andd.deptno<55groupbydname;DNAMESUM(SAL)--------------------------------------------------DNAMELINE120475DNAMELINE219475DNAMELINE322400DNAMELINE422675ExecutionPlan----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE10SORT(GROUPBY)21MERGEJOIN32SORT(JOIN)43TABLEACCESS(FULL)OF"EMP"52SORT(JOIN)65TABLEACCESS(FULL)OF"DEPT"1.1.1.1进行复杂查询的原则1.1.1.1.1限制表连接操作所涉及的表的个数北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6对于数据库的连接操作操作，我们可以简单的将其想象为一个循环匹配的过程，每一次匹配相当于一次循环，每一个连接相当于一层循环，则N个表的连接操作就相当于一个N-1层的循环嵌套。一般的情况下在数据库的查询中涉及的数据表越多，则其查询的执行计划就越复杂，其执行的效率就越低，为此我们需要尽可能的限制参与连接的表的数量。1.3-5个表的处理方法对于较少的数据表的连接操作，需要合理的确定连接的驱动表，从某种意义上说，确定合理的驱动表就是确定多层循环嵌套中的最外层的循环，可以最大限度的提高连接操作的效率，可见选择合适的驱动表的重要性。RBO模式下，在SQL语句中FROM子句后面的表就是我们要进行连接操作的数据表，Oracle按照从右到左的顺序处理这些表，让它们轮流作为驱动表去参加连接操作，这样我们可以把包含参与连接的数据量最少的表放在FROM子句的最右端，按照从右到左的顺序依次增加表中参与连接数据的量。CBO模式下，则不需要考虑表放置的位置。2.5个表以上的处理方法对于涉及较多的表(>5+)的数据连接查询，其查询的复杂度迅速增加，其连接的存取路径的变化更大，存取路径的个数与连接的表的个数的阶乘有关：当n=5时存取路径＝1X2X3X4X5=120个，而当连接的表的个数为6时存取路径变为1X2X3X4X5X6=720个，数据库优化器对于数据的存取路径的判断近乎为不可能，此时完全依赖与用户的语句书写方式。对于较多的表的连接，要求开发人员查询返回的结果能够有所预测，同时判断出各个参与连接的表中符合条件的记录的数量，从而控制查询的运行时间。同时为了提高查询的效率，此时可以把部分表的一些连接所形成的中间结果来代替原来的连接表，从而减少连接的表的数目。3.对表连接操作涉及的表数目不应多于8个表如果查询语句拥有过多的表连接，那么它的执行计划过于复杂且可控性降低，容易引起数据库的运行效率低下，即使在开发测试环境中已经经过充分的测试验证，也不能保证在生产系统上由于数据量的变化而引发的相关问题。应该在应用设计阶段就避免这种由于范式过高而导致的情况出现。北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc61.1.1.1.1限制嵌套查询的层数应用中影响数据查询的效率的因素除了参与查询连接的表的个数以外，还有查询的嵌套层数。对于非关联查询，嵌套的子查询相当于使查询语句的复杂度在算术级数的基础上增长，而对于关联查询而言，嵌套的子查询相当于使查询语句的复杂度在几何级数的基础上增长。因此，降低查询的嵌套层数有助于提高查询语句的效率。对嵌套查询层数的限制要求：如果查询语句拥有过多的嵌套层数，那么会使该查询语句的复杂度高速增加，应该在数据库设计阶段就避免这种情况出现，不应多于5层。1.1.1.1.2灵活应用中间表或临时表在对涉及较多表的查询和嵌套层数较多的复杂查询的优化过程中，使用中间表或临时表是优化、简化复杂查询的一个重要的方法。通过使用一些中间表，我们可以把复杂度为M*N的操作转化为复杂度为M+N的操作，当M和N都比较大时M+N<CREATETABLELOC1ASSELECT*FROMLOC2;Tablecreated.Elapsed:00:00:00.19SQL>DELETEFROMLOC12WHEREROWIDIN3(SELECTROW_IDFROM4(SELECTROWIDROW_ID,ROWNUMROW_NUMFROMLOC1)5WHEREROW_NUM>10000)；121088rowsdeleted.Elapsed:00:00:12.37SQL>COMMIT;Commitcomplete.Elapsed:00:00:00.00北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6SQL>DROPTABLELOC1;Tabledropped.Elapsed:00:00:00.32SQL>CREATETABLELOC1ASSELECT*FROMLOC2;Tablecreated.Elapsed:00:00:00.22SQL>CREATEGLOBALTEMPORARYTABLEROWID_ROWNUM2(ROW_IDROWID,ROW_NUMNUMBER);Tablecreated.Elapsed:00:00:00.01SQL>INSERTINTOROWID_ROWNUMSELECTROWID,ROWNUMFROMLOC1;131088rowscreated.Elapsed:00:00:00.25SQL>DELETEFROMLOC1WHEREROWIDIN2(SELECTROW_IDFROMROWID_ROWNUMWHEREROW_NUM>10000);121088rowsdeleted.Elapsed:00:00:10.421.1.1.1.1使用一些改写复杂查询的技巧1.转换连接类型参见上文的改写查询语句部分2.把OR转换为UNIONALL3.区分不同的情况使用IN或EXISTS对于主查询中包含较多条件而子查询条件较少的表使用EXISTS，对于主查询中包含较少条件而子查询条件较多的表使用IN。4.使用合理的连接方式在不同的情况下使用不同的连接方式：散列连接适用于一个较小的表和较大的表的连接，排序合并连接需要对小表进行排序操作，索引的嵌套循环连接对于一般连接是有效的，但是需要在连接的关键字上有索引的存在。北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6应用开发人员应该根据不同的情况选取合适的连接方式。1.使用并行查询如果查询的数据在表中所占的比例较大，可以考虑使用并行查询来提高查询的执行速度。对于UPDATE/INSERT/DELETE操作的查询部分也可以同样做并行查询的处理。2.使用PL/SQL过程和临时表代替复杂查询。对于涉及巨大的表的连接的统计查询，由于可能会造成大量的排序统计工作，使得查询的速度变慢，此时可以考虑使用PLSQL替代原来的查询。1.1.1DML语句的调整原则DML语句包括Insert、Update、Delete和Merge。在使用DML语句的时候，我们也会遇到性能低下的情况，可以参考以下的内容来做出调整。1.1.1.1Oracle存储参数的影响Oracle的DML语句出现性能问题的一些情况：1.Insert操作缓慢并且占用过多的I/O资源。这种情况发生在PCTFREE较高且行记录较大，频繁地寻找新的空闲数据块的时候。在数据对象有(多个)索引的情况下，Insert操作还需要对索引进行维护，这额外的增加了数据插入的成本，所以对于没有过度索引的表的维护是比较花费资源的。2.Update操作缓慢。Update操作需要获得操作对象上的独占锁，如果其它的用户已经占有了该对象的非兼容的锁，那么Update操作就需要等待，通常这是非常短的时间，但是如果该用户在操作时被打断，则该用户持有这个锁的时间就可能变长，造成其它用户的等待，这是一个管理上的问题。如果Update操作扩展了一个Varchar或Blob列导致发生了行迁移的时候，其更新也会变慢。北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc61.Delete操作缓慢。通常发生在记录被删除，而且Oracle必须将数据块重新连接到该表的Freelist的时候。由于删除操作会产生大量的undo和redo信息，所以对系统的性能的影响较大。如果可能可以使用更改状态标志和在另外的表中插入新的记录来代替删除操作。对于删除全表的操作可以用truncatetable等命令来实现，在PL/SQL等不支持truncate命令的环境中可以使用动态SQL来实现truncate的功能。对于碎片比较多的系统，删除操作在某些时候涉及到数据块的回收。另外，当有多个任务想要对一张数据表进行Insert或Update操作的时候，这张数据表的段头可能会产生冲突情况，这种冲突可以表现为出现等待事件：BufferBusyWaits，此时对于数据库表的处理办法是提高Pctfree的值，降低一个数据块中数据的行数，对于冲突的索引可以使用倒排索引来避免同一数据块中的数据的索引存放在同一索引块中。调整原则请参考上文相关部分以适当地对这几个参数进行设置，以在有效空间利用和高性能之间获得一个平衡点。一般情况下对于并发更新比较频繁的对象要降低同一数据块中的数据行数以减少系统对于同一数据块的竞争，以空间换时间以提高性能。对于并发更新竞争不是那么频繁的对象要提高同一数据块中的数据行数，以提高系统空间的利用效率，同时提高缓存的利用率以达到提高系统整体效率的目的。作为队列使用的表的竞争会比较剧烈，这类表中包含的总的数据行数不会太多，所以可以使用空间来换取效率。1.1.1.1大数据类型的影响使用大数据类型（RAW，LONG，BLOB，CLOB等）的时候，主要问题在于它们常常会超出普通的数据块大小，导致数据列分散到相邻的数据块。这样，每行记录被访问时，Oracle都会产生两次以上的I/O操作。北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6调整原则对于使用大数据类型的表，一般情况下其数据的行连接是不可避免的，我们能够做的就是尽量的降低这种事件发生的频率。一般情况下，对于单独存储的LOB对象，我们可以指定其使用较大的db_block_size的表空间以控制其使用的数据块的个数，同时减少对LOB对象的访问次数，其中数据块的大小根据各个不同的LOB的平均大小有所不同。小于4k的LOB对象可以和数据存储在一起，这样对数据访问速度的影响较大，这种情况下我们可以按照LOB对象的实际大小而选择不同表空间来存储数据。1.1.1.1DML执行时约束的开销约束会对DML操作的性能产生影响。1.完整性约束：时间会耗费在验证每一个数据值是否合法上。2.主键约束：主键约束是由唯一索引来强制实施的，而且这个索引在插入和更新操作上的开销使大容量的插入操作和更新操作运行变慢，因为每个值都必须从索引中查询一次。3.外键约束：强制实施了交互表之间的数据关系，必须访问外部的数据表以确认当前值是否合法，才能进行插入。4.其它约束：对于其它检查的，数据也需要做相应的检查5.触发器：触发器对DML的执行效率也有较大的影响，特别当触发器的类型为foreachrow的时候。调整原则1.在执行大容量的插入或更新任务时，可以暂时禁用所有与所影响的数据表有关的约束、触发器，装载数据，最后才重新启用约束2.在启用约束时，需要考虑非法的数据。1.1.1.2DML执行时维护索引所需的开销在记录被插入和修改时，表上所有的参与索引都必须实时地进行更新。这通常会产生由于大量排序而增加的系统开销，严重降低系统的执行性能。北京数据中心开放系统平台管理部Page115of115 737091d41a9137da7440670fd738a25d.doc6调整原则1.在大型的DML批操作中，在更改数据表之前，删除全部索引。在批操作之后，重新建立起索引。2.如果索引因为不平衡而产生拆分等额外操作，那么可以通过重建索引操作，也会减少维护索引所需的时间。3.如果在数据被加载到数据库之前其数据已在外部完成排序，则在创建索引是可以使用NOSORT选项，需要注意的是NOSORT只能使用升序而不能使用降序，并且不能使用在倒排、分区、位图索引中。在批量数据加载后的创建索引的过程中，可以指定用来排序的表空间已提高排序的效率，在指定排序表空间是需要注意该表空间的extent的大小，一般情况下该值越大越好。1附件1.1部分系统数据库参数配置1.2部分系统参数配置1.3Oracle参数说明北京数据中心开放系统平台管理部Page115of115'