Oracle9i关系数据库实用教程ppt课件.ppt

Oracle的性能优化 第9章 1 影响Oracle数据库性能的因素主要有哪些 2 为什么说Oracle数据库的性能与硬件环境有关 3 是否所有的表都需要建立索引以提高查询速度 为什么 4 Oracle中锁有几种类型 用途是什么 5 什么类型的约束能保证表中的每行彼此不同 6 什么类型的约束能保证一个表中的每行在另一个表中有匹配的一行 7 利用所学的知识 试举出三个例子 说明SQL多关联查询中 采用不同SQL语句的写法对提高查询效率的影响 8 编写一个序号生成程序 用于连锁超市系统中进货单号的生成 单号的组成格式为 日期 YYYYMMDD 序号 本章练习 9 3Oracle运行环境的优化 9 4并发事件处理 9 5数据完整性 9 7常见问题处理 本章内容 9 2SQL语句的优化 9 1数据库性能优化概述 9 8案例分析 9 6数据库性能优化的工具 一 数据库性能优化的内容 1 调整数据结构的设计 2 调整应用程序结构设计 3 调整数据库SQL语句 4 调整服务器内存分配 5 调整硬盘I O 这一步是在信息系统开发之前完成的 6 调整操作系统参数 9 1数据库性能优化概述 二 不同类型系统的优化1 在线事务处理信息系统 OLTP 这种类型的信息系统一般需要有大量的Insert Update操作 OLTP系统需要保证数据库的并发性 可靠性和最终用户的速度 这类系统使用的Oracle数据库需要主要考虑下述因素或参数 1 数据库回滚段是否足够 2 是否需要建立Oracle数据库索引 聚集 散列 3 系统全局区 SGA 大小是否足够 4 SQL语句是否高效 2 数据仓库系统 DataWarehousing 这种信息系统的主要任务是从Oracle的海量数据中进行查询 得到数据之间的某些规律 数据库管理员需要为这种类型的Oracle数据库着重考虑下述因素或参数 1 是否采用B 索引或者bitmap索引 2 是否采用并行SQL查询以提高查询效率 3 是否采用PL SQL函数编写存储过程 4 是否有必要建立并行数据库 来提高数据库的查询效率 一 SQL语句的优化规则 1 去掉不必要的大表 全表扫描 不必要的大表 全表扫描会造成不必要的输入输出 而且还会拖垮整个数据库 2 检查优化索引的使用这对于提高查询速度来说非常重要 9 2SQL语句的优化 3 检查子查询 考虑SQL子查询是否可以用简单连接的方式进行重新书写 4 调整PCTFREE和PCTUSED等存储参数优化插入 更新或者删除等操作 5 考虑数据库的优化器 6 考虑数据表的全表扫描和在多个CPU的情况下考虑并行查询 二 SQL语句优化的具体方法1 索引的使用 1 尽量使用索引是全表扫描还是索引范围扫描主要考虑SQL的查询速度问题 试比较下面两条SQL语句 语句A SELECTdname deptnoFROMdeptWHEREdeptnoNOTIN SELECTdeptnoFROMemp 语句B SELECTdname deptnoFROMdeptWHERENOTEXISTS SELECTdeptnoFROMempWHEREdept deptno emp deptno 2 索引不起作用的情况 存在数据类型隐形转换 列上有数学运算 使用不等于 运算 使用substr字符串函数 通配符在第一个字符 字符串连接 3 函数的索引例如 日期类型是经常用到的 而且在SQL语句中会使用to char函数以查询具体的的范围日期 如 select fromstaff memberwhereTO CHAR birth day YYYY 2003 可以建立基于函数的索引如 CREATEINDEXInd emp birthONstaff member to char birth day YYYY 2 SQL语句排序优化排序发生的情况如下 SQL中包含groupby子句SQL中包含orderby子句SQL中包含distinct子句SQL中包含minus或union操作 3 选择联合查询的联合次序联合查询中如涉及到多个表的字段关联及查询 其SQL查询语句联合次序的不同写法 会导致语句对各表具体操作的步骤有不同的次序 所以虽然执行结果相同 但执行效率却不同 SQL子查询的调整 1 关联子查询和非关联子查询非关联查询的开销 非关联查询时子查询只会执行一次 而且结果是排序好的 并保存在一个Oracle的临时段中 其中的每一个记录在返回时都会被父查询所引用 在子查询返回大量的记录的情况下 将这些结果集排序 以及将临时数据段进行排序会增加大量的系统开销 关联查询的开销 对返回到父查询的记录来说 子查询会每行执行一次 因此 必须保证任何可能的时候子查询用到索引 2 在子查询中慎重使用IN或者NOTIN语句在子查询中慎重使用IN或者NOTIN语句 使用where NOT exists的效果要好的多 带IN的关联子查询是多余的 因为IN子句和子查询中相关的操作的功能是一样的 为非关联子查询指定EXISTS子句是不适当的 因为这样会产生笛卡尔乘积 尽量不要使用NOTIN子句 3 慎重使用视图的联合查询慎重使用视图的联合查询 尤其是比较复杂的视图之间的联合查询 一般对视图的查询最好都分解为对数据表的直接查询效果要好一些 可以在参数文件中设置SHARED POOL RESERVED SIZE参数 这个参数在SGA共享池中保留一个连续的内存空间 连续的内存空间有益于存放大的SQL程序包 一 内存结构的调整内存参数的调整主要是指Oracle数据库的系统全局区 SGA 的调整 SGA主要由三部分构成 共享池 数据缓冲区 日志缓冲区 9 3Oracle运行环境的优化 1 共享池共享池由两部分构成 共享SQL区和数据字典缓冲区 共享SQL区是存放用户SQL命令的区域 数据字典缓冲区存放数据库运行的动态信息 1 数据库管理员通过执行下述语句 来查看共享SQL区的使用率 select sum pins reloads sum pins LibCache fromv librarycache 共享SQL区的使用率应该在90 以上 否则需要增加共享池的大小 2 数据库管理员可以执行下述语句 查看数据字典缓冲区的使用率 select sum getmisses usage fixed sum gets RowCache fromv rowcache 数据字典缓冲区的使用率也应该在90 以上 否则需要增加共享池的大小 2 数据缓冲区数据库管理员可以通过下述语句 来查看数据库数据缓冲区的使用情况 SELECTname FROMv sysstatWHEREnameIN dbblockgets consistentgets physicalreads 根据查询出来的结果可以计算出数据缓冲区的使用命中率 数据缓冲区的使用命中率 1 physicalreads dbblockgets consistentgets 这个命中率应该在90 以上 否则需要增加数据缓冲区的大小 3 日志缓冲区数据库管理员可以通过执行下述语句 查看日志缓冲区的使用情况 selectname valuefromv sysstatwherenamein redoentries redologspacerequests 根据查询出的结果可以计算出日志缓冲区的申请失败率 申请失败率 requests entries申请失败率应该接近于0 否则说明日志缓冲区开设太小 需要增加Oracle数据库的日志缓冲区 二 物理I O的调整 1 在磁盘上建立数据文件前首先运行磁盘碎片整理程序为了安全地整理磁盘碎片 需关闭打开数据文件的实例 并且停止服务 如果有足够的连续磁盘空间建立数据文件 那么就很容易避免数据文件产生碎片 2 不要使用磁盘压缩Oracle数据文件不支持磁盘压缩 3 不要使用磁盘加密加密象磁盘压缩一样增加了一个处理层 降低磁盘读写速度 如果担心自己的数据可能泄密 可以使用dbms obfuscation包和labelsecurity选择性地加密数据的敏感部分 4 不要使用超过70 的磁盘空间 剩余的磁盘空间存放系统临时数据和作为磁盘碎片整理程序存放中间数据 所以一般情况下 不要使用超过70 的磁盘空间 5 使用RAIDRAID的使用应注意 选择硬件RAID超过软件RAID 日志文件不要放在RAID5卷上 因为RAID5读性能高而写性能差 把日志文件和归档日志放在与控制文件和数据文件分离的磁盘控制系统上 6 分离页面交换文件到多个磁盘物理卷跨越至少两个磁盘建立两个页面文件 可以建立四个页面文件并在性能上受益 确保所有页面文件的大小之和至少是物理内存的两倍 三 CPU的优化调整1 查看CPU的使用情况使用操作命令可以看到CPU的使用情况 一般UNIX操作系统的服务器 可以使用sar u命令查看CPU的使用率 NT操作系统的服务器 可以使用NT的性能管理器来查看CPU的使用率 出现CPU资源不足的情况是很多的 SQL语句的重解析 低效率的SQL语句 锁冲突都会引起CPU资源不足 2 查看SQL语句的解析情况 1 数据库管理员可以执行下述语句来查看SQL语句的解析情况 SELECT FROMV SYSSTATWHERENAMEIN parse time cpu parse time elapsed parse count hard 这里 parse time cpu 是系统服务时间 parse time elapsed 是响应时间 而用户等待时间为 waite time parse time elapsed parse time cpu由此可以得到用户SQL语句平均解析等待时间 用户SQL语句平均解析等待时间 waite time parse count 2 数据库管理员还可以通过下述语句 查看低效率的SQL语句 SELECTBUFFER GETS EXECUTIONS SQL TEXTFROMV SQLAREA 优化这些低效率的SQL语句也有助于提高CPU的利用率 3 查看Oracle数据库的冲突情况数据库管理员可以通过v system event数据字典中的 latchfree 统计项查看Oracle数据库的冲突情况 如果没有冲突的话 latchfree查询出来没有结果 如果冲突太大的话 数据库管理员可以降低spin count参数值 来消除高的CPU使用率 4 CPU的优化调整方法一些优化CPU使用和配置的具体方法有 1 取消屏幕保护 2 把系统配置为应用服务器 3 监视系统中消耗中断的硬件 4 保持最小的安全审计记录 5 在专用服务器上运行Oracle 6 禁止非必须的服务 四 网络配置的优化网络配置是性能调整的一项很重要的内容 而且很容易隐藏性能瓶颈 1 配置网卡使用最快速度和有效模式 2 删除不需要的网络协议 3 优化网络协议绑定顺序 4 为Oracle禁止或优化文件共享 五 Oracle碎片整理1 碎片是如何产生的2 碎片对系统的影响 1 导致系统性能减弱 2 浪费大量的表空间 3 自由范围的碎片计算用fsfi freespacefragmentationindex 自由空间碎片索引 值来直观体现 fsfi 100 sqrt max extent sum extents 1 sqrt sqrt count extents 4 自由范围的碎片整理可以将表空间的缺省存储参数pctincrease改为非0 一般将其设为1 如 altertablespacetempdefaultstorage pctincrease1 这样smon便会将自由范围自动合并 达到碎片整理的目的 也可以采用如下语句 通过手工合并自由范围来达到碎片整理的目的 altertablespacetempcoalesce 5 段的碎片整理段由范围组成 在有些情况下 有必要对段的碎片进行整理 要查看段的有关信息 可查看数据字典DBA segments 范围的信息可查看数据字典DBA extents 如果段的碎片过多 将其数据压缩到一个范围的最简单方法便是用正确的存储参数将这个段重建 然后将旧表中的数据插入到新表 同时删除旧表 这个过程可以用import export 输入 输出 工具来完成 export 命令有一个 压缩 标志 这个标志在读表时会引发export确定该表所分配的物理空间量 它会向输出转储文件写入一个新的初始化存储参数 等于全部所分配空间 若这个表关闭 则使用import 工具重新生成 这样 它的数据会放入一个新的 较大的初始段中 例如 expuser passwordfile exp dmpcompress ygrants yindexes ytables table1 table2 若输出成功 则从库中删除已输出的表 然后从输出转储文件中输入表 impuser passwordfile exp dmpcommit ybuffer 64000full y 这种方法可用于整个数据库 另外 应该定期shutdowndatabase 从而清理momery碎片 六 Oracle系统参数的调整1 SharedPoolandLibraryCachePerformanceTuning 共享池和LibraryCache 共享池调整的技巧主要有 1 刷共享池刷 Flush 共享池可以使小块的内存合并为大块的内存 当共享池的碎片过多时 能够暂时恢复性能 刷共享池可以使用语句 altersystemflushshared pool 2 绑定变量 2 BufferCachePerformanceTuning 数据库缓存调整 从缓存调整的角度看 应力求避免以下的问题 1 缓存的最近最少使用 LRN 链 cachebuffersLRUchain 的加锁竞争 2 平均写队列 AverageWriteQueue 长度过大 3 过多时间花在等待 写完毕等待上 writecompletewaits 4 过多时间花在等待 缓冲释放等待 上 freebufferwaits 3 LatchContention 加锁或插销竞争 插销加锁是SGA中保护共享数据结构的低层的串行化机制 插销latch是一类可以非常快的获得和释放的锁 插销锁的实现是依赖于操作系统的 尤其在关于一个进程是否会等待一个锁 和等多久方面 有如下的锁 插销 需要调整 1 RedoCopy AllocationLatch 重写日志的复制 分配插销 2 SharedPoolLatch 共享池的插销 3 LibraryCacheLatch LibraryCache插销 4 RedoLogBufferPerformanceTuning 重写日志缓冲的调整 LGWR将重写日志缓冲中的重写项写到重写日志文件中 一旦LGWR将这些项复制到重写日志文件中 用户进程就可以重写这些项 统计项目 redologspacerequests 反映了用户进程等待重写日志缓冲中空间的时间的数字 1 设置重写日志大小的提示 redologspacerequests 的值应该接近0 2 设定合适的重写日志的大小 建议每15 30分钟进行一次重写日志的切换 5 QueryPerformanceTuning 查询效率的调整 如果查询运行得很慢 请考虑以下这些方面 1 希望这个查询运行的有多快以及有理由这样要求吗 2 优化模式OPTIMIZER MODE设为何值 3 查询涉及的索引都是有效的吗 4 在数据库中有没有其他的长时间运行的查询 大查询 5 表和索引上有统计信息吗 6 统计信息是被计算出来的还是被估计出来的 对于查询的性能调整有两个主要的调试工具 TKPROF和AUTOTRACE 6 TemporaryTablespacePerformanceTuning 临时表空间的调整 临时表空间的调整的技巧如下 如果即使在稳定的状态下也存在很多的排序扩展锁 SortExtentPoollatch 的竞争 应该通过修改临时表空间的DEFAULTSTORAGE子句的NEXT值来增大扩展块的大小 如果存在很多的排序扩展锁 SortExtentPoollatch 的竞争并且这种等待是由于过多的并发的排序造成的 应该增大SORT AREA SIZE参数的大小 以使更多的排序能保存在内存中 建议让扩展块的大小和SORT AREA SIZE参数相同 一 并发事件的产生数据库是一个共享资源 可为多个应用程序所共享 这些程序可串行运行 但在许多情况下 可能多个程序或一个程序的多个进程并行地运行 这就是数据库的并行操作 在多用户数据库环境中 多个用户程序可并行地存取数据库 如果不对并发操作进行控制 会存取不正确的数据 或破坏数据库数据的一致性 9 4并发事件处理 1 当一个用户正在修改表中数据的同时 另一个用户正试图删除该表 2 用户A正试图读取用户B的某个事务中的一些数据 在用户A的事务开始后 该事务由用户B修改和提交 用户A读取用户B提交的数据 这意味着在同一个事务中读取的数据在某个时刻不一致 3 某个用户对数据进行修改 另一个用户在第一个用户提交事务以前对同一行进行修改 因此 第一个用户所做的改变丢失了 4 一个用户从另一个用户尚未提交的数据中读取数据 也就是说 在用户B的改变提交以前 用户A读取用户B正在修改的行 二 锁1 锁的功能 1 数据一致性 2 数据并行性 3 数据完整性 Oracle在两个不同级上提供读一致性 语句级读一致性和事务级一致性 1 语句级读取一致性 2 事务级读取一致性 2 锁的类型Oracle锁具体分为以下几类 1 自动锁与显示锁按用户与系统划分 可以分为自动锁与显示锁 自动锁 当进行一项数据库操作时 缺省情况下 系统自动为此数据库操作获得所有有必要的锁 显示锁 某些情况下 需要用户显示的锁定数据库操作要用到的数据 才能使数据库操作执行得更好 显示锁是用户为数据库对象设定的 2 共享锁与排它锁按锁级别划分 可分为共享锁与排它锁 共享锁 共享锁允许相关资源可以共享 几个用户可同时读同一数据 几个事务可在同一资源上获取共享封锁 共享锁比排它锁具有更高的数据并行性 但拙劣的事务设计 共享锁容易造成死锁或数据更新丢失 排它锁 事务设置排它锁后 该事务单独获得此资源 另一事务不能在此事务提交之前获得相同对象的共享锁或排它锁 排它锁禁止相关资源的共享 如果一事务以排它方式封锁一资源 仅仅该事务可更改该资源 直至释放排它封锁 3 DML锁和DDL锁按操作划分 可分为DML锁 DDL锁 DML锁又可以分为 行锁 表锁 死锁 行锁 当事务执行数据库插入 更新 删除操作时 该事务自动获得操作表中操作行的排它锁 表锁 当事务获得行锁后 此事务也将自动获得该行的表锁 共享锁 以防止其它事务进行DDL语句影响记录行的更新 事务也可以在进行过程中获得共享锁或排它锁 只有当事务显示使用LOCKTABLE语句显示的定义一个排它锁时 事务才会获得表上的排它锁 也可使用LOCKTABLE显示的定义 死锁 当两个事务需要一组有冲突的锁 而不能将事务继续下去的话 就出现死锁 DDL锁又可以分为 排它DDL锁 共享DDL锁 分析锁 排它DDL锁 创建 修改 删除一个数据库对象的DDL语句获得操作对象的排它锁 如使用altertable语句时 为了维护数据的完成性 一致性 合法性 该事务获得一排它DDL锁 共享DDL锁 需在数据库对象之间建立相互依赖关系的DDL语句通常需共享获得DDL锁 如创建一个包 该包中的过程与函数引用了不同的数据库表 当编译此包时 该事务就获得了引用表的共享DDL锁 分析锁 Oracle使用共享池存储分析与优化过的SQL语句及PL SQL程序 使运行相同语句的应用速度更快 一个在共享池中缓存的对象获得它所引用数据库对象的分析锁 分析锁是一种独特的DDL锁类型 Oracle使用它追踪共享池对象及它所引用数据库对象之间的依赖关系 当一个事务修改或删除了共享池持有分析锁的数据库对象时 Oracle使共享池中的对象作废 下次在引用这条SQL PLSQL语句时 Oracle重新分析编译此语句 4 内部闩锁内部闩锁是Oracle中的一种特殊锁 用于顺序访问内部系统结构 当事务需向缓冲区写入信息时 为了使用此块内存区域 Oracle首先必须取得这块内存区域的闩锁 才能向此块内存写入信息 3 锁的模式 1 模式0 none 2 模式1 null空 1级锁有 Select 有时会在v locked object出现 3 模式2 Row S行共享 RS 共享表锁 subshare 2级锁有 Selectforupdate LockForUpdate LockRowShare selectforupdate当对话使用forupdate子串打开一个游标时 所有返回集中的数据行都将处于行级 Row X 独占式锁定 其他对象只能查询这些数据行 不能进行update delete或selectforupdate操作 4 模式3 Row X行独占 RX 用于行的修改 subexclusive 3级锁有 Insert Update Delete LockRowExclusive 没有commit之前插入同样的一条记录会没有反应 因为后一个3的锁会一直等待上一个3的锁 必须释放掉上一个才能继续工作 5 模式4 Share共享锁 S 阻止其他DML操作 share 4级锁有 CreateIndex LockShare locked mode为2 3 4不影响DML insert delete update select等 操作 但DDL alter drop等 操作会提示错误 6 模式5 S Row X共享行独占 SRX 阻止其他事务操作 share subexclusive 5级锁有 LockShareRowExclusive 具体来讲有主外键约束时update delete 可能会产生4 5的锁 7 模式6 exclusive独占 X 独立访问使用 exclusive 6级锁有 Altertable Droptable DropIndex Truncatetable LockExlusive 数字越大锁级别越高 影响的操作越多 4 查看相关信息Oracle在动态状态表V lock中存储与数据库中的锁有关的所有信息 当资源被锁定时 可以检查V lock表 查看数据库中的各种情况 9 5数据完整性 数据完整性是指数据的正确性和相容性 数据的完整性是为了防止数据库存在不符合原义的数据 防止错误信息输入和输出 即数据要遵守由DBA或应用开发者所决定的一组预定义的规则 1 数据完整性的类型Oracle应用于关系数据库的表的数据完整性有下列类型 1 在插入或修改表的行时允许不允许包含有空值的列 称为空与非空规则 2 唯一列值规则 允许插入或修改的表行在该列上的值唯一 3 引用完整性规则 同关系模型定义相符 4 用户对定义的规则 是复杂的完整性检查 2 完整性约束及其优点完整性约束 是对表的列定义规则的说明性方法 Oracle利用完整性约束机制防止无效的数据进入数据库的基表 如果任何DML执行结果破坏完整性约束 该语句被回滚并返回上一个错误 利用完整性约束实施数据完整性规则有下列优点 1 定义或更改表时 不需要程序设计 便很容易地编写程序并可消除程序性错误 其功能是由Oracle控制 所以说明性完整性约束优于应用代码和数据库触发器 2 对表所定义的完整性约束是存储在数据字典中 所以由任何应用进入的数据都必须遵守与表相关联的完整性约束 3 具有最大的开发能力 当由完整性约束所实施的事务规则改变时 管理员只需改变完整性约束的定义 所有应用自动地遵守所修改的约束 3 完整性约束的类型Oracle的DBA和应用开始者对列的值输入可使用的完整性约束有下列类型 1 NOTNULL约束 如果在表的一列的值不允许为空 则需在该列指定NOTNULL约束 2 UNIQUE键约束 在表指定的列或组列上不允许两行具有重复值时 则需要该列或组列上指定UNIQUE键完整性约束 在UNIQUE键约束定义中的列或组列称为唯一键 所有唯一完整性约束是用索引方法实施 3 PRIMARYKEY约束 在数据库中每一个表可有一个PRIMARYKEY约束 包含在PRIMARYKEY完整性约束的列或组列称为主键 每个表可有一个主键 Oracle使用索引实施PRIMARYKEY约束 4 FOREIGNKEY约束 在关系数据库中表可通过公共列相关联 该规则控制必须维护的列之间的关系 包含在引用完整性约束定义的列或组列称为外来键 由外来键所引用的表中的唯一键 称为引用键 包含有外来键的表称为子表或从属表 由子表的外来键所引用的表称为双亲表或引用表 如果对表的每一行 其外来键的值必须与主键中一值相匹配 则需指定引用完整性约束 5 CHECK约束 表的每行对一指定的条件必须是TRUE或未知 则需在一列或列组上指定CHECK完整性约束 如果在发出一个DML语句时 CHECK约束的条件计算得FALSE时 该语句被回滚 4 数据库触发器数据库触