Oracle设计开发指南

知识库Oracle设计开发指南

版本历史

目录

1.引言 (6)

1.1目的 (6)

1.2范围、约束及假设 (6)

1.3定义、首字母缩写词和缩略语 (6)

1.4阅读对象 (6)

1.5参考资料 (6)

1.6文档概述 (6)

2.数据库设计 (6)

2.1逻辑设计 (6)

2.1.1表设计 (6)

2.1.2范式和冗余 (7)

2.1.3索引设计 (7)

2.1.4聚簇设计 (8)

2.1.5视图设计 (8)

2.2物理设计 (9)

2.2.1内存参数设计 (9)

2.2.2表容量估算 (9)

2.2.3索引容量估算 (9)

2.2.4存储参数设计 (9)

2.2.5表空间设计 (9)

2.2.6数据文件设计 (9)

2.2.7重做日志文件设计 (9)

2.2.8回滚段设计 (9)

2.3命名规范 (9)

2.3.1一般性命名规范 (9)

2.3.2对象命名规范 (9)

3.SQL优化 (11)

3.1SQL执行过程 (11)

3.1.1解析SQL (11)

3.1.2执行SQL (11)

3.1.3显示结果集 (11)

3.2SQL优化器 (12)

3.2.1优化器的优化方式 (12)

3.3SQL优化过程 (12)

3.3.1确定具有高影响力的SQL语句 (12)

3.3.2抽取和解释SQL语句 (13)

3.3.3调整SQL语句 (13)

3.4SQL调整工具 (13)

3.4.1Oracle AutoTrace (13)

3.4.2TKPROF工具 (15)

3.4.3STATSPACK工具 (16)

3.5O RACLE SQL优化方法 (32)

3.5.1选用适合的ORACLE优化器 (32)

3.5.2访问Table的方式 (32)

3.5.3共享SQL语句 (33)

3.5.4选择最有效率的表名顺序 (34)

3.5.5WHERE子句中的连接顺序 (34)

3.5.6SELECT子句中避免使用... * ... . (35)

3.5.7减少访问数据库的次数 (35)

3.5.8使用DECODE函数来减少处理时间 (36)

3.5.9删除重复记录 (37)

3.5.10用TRUNCATE替代DELETE (37)

3.5.11HWM对数据库性能的影响 (37)

3.5.12尽量多使用COMMIT (38)

3.5.13用Where子句替换HAVING子句 (38)

3.5.14减少对表的查询 (39)

3.5.15使用表的别名(Alias) (39)

3.5.16用EXISTS替代IN (39)

3.5.17用NOT EXISTS替代NOT IN (40)

3.5.18用表连接替换EXISTS (40)

3.5.19用EXISTS替换DISTINCT (41)

3.5.20识别?低效执行?的SQL语句 (41)

3.5.21提高insert、update速度 (41)

3.6调整查询 (42)

3.6.1查询调整方法 (42)

3.6.2子查询调整方法 (42)

3.6.3使用Oracle并行查询发挥多CPU的威力 (43)

3.7排序优化 (44)

3.7.1Oracle SQL排序原理 (44)

3.7.2需要进行排序的操作 (45)

3.7.3排序性能诊断 (45)

3.7.4监控临时表空间的使用情况 (45)

3.8调整表联接 (46)

3.8.1表联接类型 (46)

3.8.2Oracle优化器联接原理 (49)

3.8.3联接优化 (53)

3.9调整DML语句 (53)

3.9.1Oracle存储参数和DML性能 (53)

3.9.2空闲列表管理和DML性能 (55)

3.9.3索引和DML性能 (56)

3.10用索引调整SQL (56)

3.10.1Oracle索引概述 (56)

3.10.2索引不被使用的场景 (58)

3.10.3索引使用方法 (58)

3.11分页优化技术 (59)

3.11.1分页查询概述 (59)

3.11.2单表分页查询 (59)

3.11.3联接分页查询 (60)

3.11.4UNION ALL分页查询 (61)

3.11.5使用ROW_NUMBER函数分页 (64)

Oracle设计开发指南错误！未指定书签。错误！未指定书签。

3、引言

a)目的

b)范围、约束及假设

c)定义、首字母缩写词和缩略语

d)阅读对象

e)参考资料

f)文档概述

4、数据库设计

a)逻辑设计

i.表设计

1、选择合适的数据类型与长度

选择最合适的数据类型，避免数据类型转换；确定好字段的长度，避免浪费存储空间；若在创建字段的时候，不能够确认字段长度的话，则最好采用变长的数据类型。禁止使用LONG和LONG RAW类

型,应使用BLOB或CLOB类型。

2、确定表需要采用的完整性约束与默认值。

在建立表之前，需要确定列的主键约束、外键约束、非空约束、唯一性约束和CHECK约束；需要确认列的默认值。

3、在设计大表时，把允许NULL的列放在表的后面。

在数据库存储的时候，若字段为NULL，则这个值根本没有存储。把允许NULL的字段放在表的末尾，当数据库比较大或者空字段比较多的话，则可以大大的减少数据库的存储空间。

Oracle表列建立好之后，除非重新删除再建立，否则无法调整列的顺序。因此在创建表之前，要对列、以及是否为空有一个明确的定义。只有如此，才能够合理安排列的顺序。

4、确定表需要采用的类型。

对于数据量比较大的表（Oracle建议：单个表大小超过2G），根据表数据的属性进行分区，以得到较好的性能。如果表按某些字段进行增长，则采用按字段值范围进行范围分区。如果表按某个字段的几个关键值进行分布，则采用列表分区。对于静态表，则采用Hash分区或列表分区。在范围分区中，如果数据按某关键字段均衡分布，则采用子分区的复合分区方法。

表中数据多数情况下按主键列进行查询且更新不频繁应使用索引表或聚簇表，其他情况下使用普通的堆表。

5、每个表在创建时候，必须指定所在的表空间，不要采用默认表空间以防止表建立在系统表空间上导致性能问题。对于事务比较繁忙的数据表，必须存放在该表的专用表空间中。表的数据和索引应存放在不同的表空间中。大二进制数据列存储于独立表空间中。

6、每个表均创建类型为Sequence的主键字段。为每个表增加如下字段：LAST_UPDATE_DATE、LAST_UPDATE_USER_SEQ、CREATE_DATE、CREATE_USER_SEQ。为每个业务数据表增加以下字段：COMPANY_SEQ （租户序列号）。为每个数据表增加删除标记字段。为业务数据表增加5-8个保留字段。

ii.范式和冗余

如果没有性能上的原因，应该使用关系数据库理论，达到较高的范式，避免数据冗余。如果在数据量上与性能上有一定要求，并且表的更新率较低，查询率较高，考虑到实现的方便性可以有适当的数据冗余。

iii.索引设计

iv.聚簇设计

Oracle支持两种类型的聚簇：索引聚簇和哈希聚簇

1.使用索引聚簇

2.使用哈希聚簇

v.视图设计

b)物理设计

i.内存参数设计

ii.表容量估算

iii.索引容量估算

iv.存储参数设计

v.表空间设计

vi.数据文件设计

vii.重做日志文件设计

viii.回滚段设计

c)命名规范

i.一般性命名规范

ii.对象命名规范

a)SQL执行过程

i.解析SQL

ii.执行SQL

iii.显示结果集

这一步骤将对字段数据执行所有必要的排序、转换和重新格式化。

Oracle在执行一个SQL之前,首先要分析一下语句的执行计划,然后再按执行计划去执行。分析

语句的执行计划的工作是由优化器(Optimizer)来完成的。不同的情况,一条SQL可能有多种执行计划,但在某一时点,一定只有一种执行计划是最优的,花费时间是最少的。

i.优化器的优化方式

Oracle的优化器共有两种的优化方式,即基于规则的优化方式(Rule-Based Optimization,简称

为RBO)和基于成本的优化方式(Cost-Based Optimization,简称为CBO)。

1、RBO方式：优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。比如我们常

见的,当一个where子句中的一列有索引时,优化器会选择按索引扫描。

2、CBO方式：依词义可知,它是看SQL语句的代价(Cost),这里的代价主要指CPU和内存。优化

器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小、有多少行、每行的长度等信息。这些统计信息起初在库内是没有的,是你在做analyze后才出现的,很多的时侯过期统计信息会令优化器做出一个错误的执行计划,因些我们应及时更新这些信息。在Oracle8及以后的版本,Oracle列推荐用CBO的方式。

CBO的成本主要由物理I/O组成，计算公式为：

IO+CPU/1000+Net I/O*1.5

IO表示物理I/O请求，CPU表示逻辑I/O请求，Net I/O表示通过数据库链接访问远程数据库的逻辑I/O请求。CBO会尝试计算所有可能执行的物理I/O，选择只需要最小物理I/O的计划。

ii.优化器的优化模式

优化模式包括Rule,Choose,First rows,All rows这四种方式。

1、Rule:基于规则的方式。

2、Choose:默认的情况下Oracle用的便是这种方式。指的是当一个表或或索引有统计信息,则走CBO的方式,如果表或索引没有统计信息,表又不是特别的小,而且相应的列有索引时,那么就走索引,

走RBO的方式。

3、First Rows:它与Choose方式是类似的,所不同的是当一个表有统计信息时,它将以最快的方式返回查询的最先的几行,从总体上减少了响应时间。

4、All Rows:也就是我们所说的Cost的方式,当一个表有统计信息时,它将以最快的方式返回表的所有的行,从总体上提高查询的吞吐量。没有统计信息则走基于规则的方式。

使用下列SQL语句可以修改优化器模式:

c)SQL优化过程

i.确定具有高影响力的SQL语句

在V$SQLAREA视图中的SQL语句将根据下列参数进行级别排序,这些参数是按照重要性由高到低进行排序的:

1、Executions(执行)：经常执行的SQL语句就应当越早进行调整,因为它们会对总体性能产生巨大的影响。

2、Disk_reads(磁盘读取)：高的磁盘读取量有可能表明查询导致了过多的输入输出量。

3、Rows_processed(记录处理)：处理大量记录的查询会导致较高的输入输出量，同时在排序时有可能对TEMP表空间产生影响。

4、Buffer_gets(缓冲区读取)：高的缓存读取量有可能表明了一个高资源使用量的查询。

5、Sorts(排序)：排序会造成速度的明显降低，尤其是那些在磁盘TEMP表空间中进行的排序。

V$SQLAREA视图提供了一种识别有潜在问题或者需要优化的SQL语句的方法，可以使用下列SQL 语句找出执行开销高的语句。

ii.抽取和解释SQL语句

在指定SQL语句后，需要对它进行“解释”以丰富现存的执行计划，并且对它进行调整来确定这个执行计划是否可以改进。

在TOAD和PLSQL Developer中，可以使用Explain plan查看SQL语句的执行计划，在

SQL*Plus中可以使用Oracle AutoTrace查看SQL语句的执行计划，还可以使用Oracle提供的TKPROF工具、STATSPACK工具诊断和分析SQL语句可能存在的问题。

iii.调整SQL语句

我们将使用下列方法之一对拥有非优化执行计划的SQL语句进行调整：

d)SQL调整工具

i.Oracle AutoTrace

AUTOTRACE是一项SQL*Plus功能，自动跟踪为SQL语句生成一个执行计划并且提供与该语句的处理有关的统计。

1、在开始菜单->运行中,输入cmd,进入命令窗口,输入sqlplus命令,输入用户名:test,输入密

码:test,连接成功。

2、输入set autotrace on，表示启用autotrace查看sql执行计划。输入SQL语句，在执行完成后，先显示SQL执行结果，然后显示SQL执行计划，最后显示SQL统计信息。

附：AUTOTRACE Statistics常用列解释

判断SQL 效率高低除了通过时间来衡量，还应该通过执行SQL 执行状态里面的逻辑读的数量来衡量：

逻辑读=（db block gets+ consistent gets ）

ii. TKPROF 工具

TKPROF 是一个用于分析Oracle 跟踪文件并且产生一个更加清晰合理的输出结果的可执行工具。如果一个系统的执行效率比较低，一个比较好的方法是跟踪用户的会话并使用TKPROF 工具的排序功能格式化输出，从而找出有问题的SQL 语句。

TKPROF 的使用遵循以下几个步骤：

在数据库级别上设置

TIMED_STATISTICS 为

true ，在SQL*Plus 中使用下列语句： TIMED_STATISTICS 为true 表示允许sql trace 和其他一些动态性能视图收集与时间有关的参数。

在session 级别设置trace ，在SQL*Plus 中使用下列语句： 3、找到生成的trace 文件，路径：Oracle 安装目录\admin\实例名\udump ，文件格式为：实例名_ora_编号.trc

4、对trace 文件使用TKPROF 工具进行分析

Tkprof tracefile outfile[explain=user/password][options …]

一般来说，使用TKPROF 得到的输出文件中包含3个部分：SQL 语句本身；相关的诊断信息，包括CPU 时间、总共消耗时间、读取磁盘数量、逻辑读数量、以及查询中返回的记录数目等。

例：在SQL*Plus 中输入下列语句：

Tkprof 输出文件中主要性能指标的说明：

i. STATSPACK 工具

Oracle Statspack 从Oracle8.1.6开始被引入Oracle,并马上成为DBA 和Oracle 专家用来诊断数据库性能的强有力的工具。通过Statspack 我们可以很容易的确定Oracle 数据库的瓶颈所在，记录数据库性能状态，也可以使远程技术支持人员迅速了解你的数据库运行状况。Statspack 的脚本位于$ORACLE_HOME\RDBMS\ADMIN\spcreate.sql

1. 安装STATSPACK

1、为了能够顺利安装和运行Statspack 你可能需要设置以下系统参数：

job_queue_processes

为了能够建立自动任务，执行数据收集，该参数需要大于0。你可以在初试化参数文件中修改该参数(使该参数在重启后以然有效)。该参数可以在系统级动态修改(重启后失效)。在SQL*Plus 中输入下列语句：

timed_statistics

收集操作系统的计时信息，这些信息可被用来显示时间等统计信息、优化数据库和 SQL 语句。

要防止因从操作系统请求时间而引起的开销，请将该值设置为False。使用statspack收集统计信息时建议将该值设置为 TRUE，否则收集的统计信息大约只能起到10%的作用，将timed_statistics设置为True所带来的性能影响与好处相比是微不足道的。该参数使收集的时间信息存储在V$SESSTATS 和V$SYSSTATS等动态性能视图中。Timed_statistics参数可以在实例级进行更改，在SQL*Plus中输入下列语句：

2

、创建Statspack需要使用的表空间和用户

3、安装Statspack

安装Statspack需要拥有SYSDBA(connect / as sysdba)权限的用户登陆。在Oracle8.1.7以后版本中运行spcreate.sql。首先登陆到数据库，最好转到$ORACLE_HOME/RDBMS/ADMIN目录，这样我们执行脚本就可以方便些。

执行完成，提示如下信息表示创建成功。

1. 生成分析报告

1、用statspack 用户帐号登录SQL*Plus

2、抓取快照，至少抓取2次以上，在SQL*Plus 中输入下列语句：

生成分析报告，，在SQL*Plus 中输入下列语句： 按照提示，依次输入begin_snap 、end_snap 和report_name 值。

生成的分析报告位于$ORACLE_HOME/RDBMS/ADMIN 目录下。

1. STATSPACK 收集选项

Statspack 有两种类型的收集选项： 级别（level ）：控制收集数据的类型 门限（threshold ）：设置收集的数据的阈值。 1、级别（level ）

Statspack 共有三种快照级别，默认值是level 5

可以通过statspack 包修改缺省的级别设置

如果你只是想本次改变收集级别，可以忽略i_modify_parameter 参数。 2、快照门限

快照门限只应用于stats$sql_summary 表中获取的SQL 语句。因为每一个快照都会收集很多数据，每一行都代表获取快照时数据库中的一个SQL 语句，所以stats$sql_summary 很快就会成为Statspack 中最大的表。

门限存储在stats$statspack_parameter 表中。让我们了结一下各种门限：

任何一个门限值超过以上参数就会产生一条记录。通过调用statspack.modify_statspack_parameter 函数我们可以改变门限的默认值。

例如：

2.STATSPACK分析报告详解

statspack 输出结果中必须查看的十项内容：

1、报表头信息

数据库实例相关信息，包括数据库名称、ID、版本号及主机等信息。