《《Oracle9i关系数据库实用教程（第二版）》-蔡立军-电子教案及教学大纲 09》由会员分享，可在线阅读，更多相关《《Oracle9i关系数据库实用教程（第二版）》-蔡立军-电子教案及教学大纲 09（81页珍藏版）》请在金锄头文库上搜索。
1、Oracle的性能优化,第9章,Oracle9i关系数据库,1影响Oracle数据库性能的因素主要有哪些？ 2为什么说Oracle数据库的性能与硬件环境有关？ 3是否所有的表都需要建立索引以提高查询速度，为什么？ 4Oracle中锁有几种类型，用途是什么？ 5什么类型的约束能保证表中的每行彼此不同？ 6什么类型的约束能保证一个表中的每行在另一个表中有匹配的一行？ 7利用所学的知识，试举出三个例子，说明SQL多关联查询中，采用不同SQL语句的写法对提高查询效率的影响。 8编写一个序号生成程序，用于连锁超市系统中进货单号的生成。单号的组成格式为：日期（YYYYMMDD）+序号。,本章练习,9.3
2、Oracle运行环境的优化,9.4 并发事件处理,9.5 数据完整性,9.7 常见问题处理,本章内容,9.2 SQL语句的优化,9.1 数据库性能优化概述,9.8 案例分析,9.6 数据库性能优化的工具,一、数据库性能优化的内容 （1）调整数据结构的设计。 （2）调整应用程序结构设计。 （3）调整数据库SQL语句。 （4）调整服务器内存分配。 （5）调整硬盘I/O，这一步是在信息系统开发之前完成的。 （6）调整操作系统参数。,9.1 数据库性能优化概述,二、不同类型系统的优化 1在线事务处理信息系统（OLTP） 这种类型的信息系统一般需要有大量的Insert、Update操作。OLTP系统需要
3、保证数据库的并发性、可靠性和最终用户的速度，这类系统使用的Oracle数据库需要主要考虑下述因素或参数： （1）数据库回滚段是否足够？ （2）是否需要建立Oracle数据库索引、聚集、散列？ （3）系统全局区（SGA）大小是否足够？ （4）SQL语句是否高效？,2数据仓库系统（Data Warehousing） 这种信息系统的主要任务是从Oracle的海量数据中进行查询，得到数据之间的某些规律。数据库管理员需要为这种类型的Oracle数据库着重考虑下述因素或参数： （1）是否采用B*-索引或者bitmap索引？ （2）是否采用并行SQL查询以提高查询效率？ （3）是否采用PL/SQL函数编写存
4、储过程？ （4）是否有必要建立并行数据库，来提高数据库的查询效率。,一、SQL语句的优化规则 （1）去掉不必要的大表、全表扫描。不必要的大表、全表扫描会造成不必要的输入输出，而且还会拖垮整个数据库； （2）检查优化索引的使用 这对于提高查询速度来说非常重要；,9.2 SQL语句的优化,（3）检查子查询，考虑SQL子查询是否可以用简单连接的方式进行重新书写； （4）调整PCTFREE和PCTUSED等存储参数优化插入、更新或者删除等操作； （5）考虑数据库的优化器； （6）考虑数据表的全表扫描和在多个CPU的情况下考虑并行查询。,二、SQL语句优化的具体方法 1、索引的使用 （1）尽量使用索引
5、是全表扫描还是索引范围扫描主要考虑SQL的查询速度问题。试比较下面两条SQL语句： 语句A： SELECT dname,deptno FROM dept WHERE deptno NOT IN（SELECT deptno FROM emp）;,语句B： SELECT dname,deptno FROM dept WHERE NOT EXISTS（SELECT deptno FROM emp WHERE dept.deptno = emp.deptno）;,（2）索引不起作用的情况 存在数据类型隐形转换 列上有数学运算 使用不等于（）运算 使用substr字符串函数 %通配符在第一个字符 字符串
6、连接（|）,（3）函数的索引 例如，日期类型是经常用到的，而且在SQL语句中会使用to_char函数以查询具体的的范围日期。如： select * from staff_member where TO_CHAR（birth_day,YYYY）=2003; 可以建立基于函数的索引如： CREATE INDEX Ind_emp_birth ON staff_member （to_char（birth_day,YYYY）;,2.SQL语句排序优化 排序发生的情况如下： SQL中包含group by 子句 SQL 中包含order by 子句 SQL 中包含 distinct 子句 SQL 中包含 m
7、inus 或 union操作,3选择联合查询的联合次序 联合查询中如涉及到多个表的字段关联及查询，其SQL查询语句联合次序的不同写法，会导致语句对各表具体操作的步骤有不同的次序，所以虽然执行结果相同，但执行效率却不同。,SQL子查询的调整 （1）关联子查询和非关联子查询 非关联查询的开销非关联查询时子查询只会执行一次，而且结果是排序好的，并保存在一个Oracle的临时段中，其中的每一个记录在返回时都会被父查询所引用。在子查询返回大量的记录的情况下，将这些结果集排序，以及将临时数据段进行排序会增加大量的系统开销。 关联查询的开销对返回到父查询的记录来说，子查询会每行执行一次。因此，必须保证任何可
8、能的时候子查询用到索引。,（2）在子查询中慎重使用IN或者NOT IN语句 在子查询中慎重使用IN或者NOT IN语句，使用where （NOT）exists的效果要好的多。 带IN的关联子查询是多余的，因为IN子句和子查询中相关的操作的功能是一样的。 为非关联子查询指定EXISTS子句是不适当的，因为这样会产生笛卡尔乘积。 尽量不要使用NOT IN子句。,（3）慎重使用视图的联合查询 慎重使用视图的联合查询，尤其是比较复杂的视图之间的联合查询。一般对视图的查询最好都分解为对数据表的直接查询效果要好一些。 可以在参数文件中设置SHARED_POOL_RESERVED_SIZE参数，这个参数在S
9、GA共享池中保留一个连续的内存空间，连续的内存空间有益于存放大的SQL程序包。,一、内存结构的调整 内存参数的调整主要是指Oracle数据库的系统全局区（SGA）的调整。SGA主要由三部分构成：共享池、数据缓冲区、日志缓冲区。,9.3 Oracle运行环境的优化,1共享池 共享池由两部分构成：共享SQL区和数据字典缓冲区。共享SQL区是存放用户SQL命令的区域，数据字典缓冲区存放数据库运行的动态信息。,（1）数据库管理员通过执行下述语句，来查看共享SQL区的使用率。 select （sum（pins-reloads）/sum（pins） “Lib Cache“ from v$libraryca
10、che; 共享SQL区的使用率应该在90以上，否则需要增加共享池的大小。,（2）数据库管理员可以执行下述语句，查看数据字典缓冲区的使用率。 select （sum（-getmisses-usage-fixed）/sum（gets） “Row Cache“ from v$rowcache; 数据字典缓冲区的使用率也应该在90以上，否则需要增加共享池的大小。,2数据缓冲区 数据库管理员可以通过下述语句，来查看数据库数据缓冲区的使用情况。 SELECT name， FROM v$sysstat WHERE name IN （db block gets,consistent gets,physical
11、 reads）;,根据查询出来的结果可以计算出数据缓冲区的使用命中率： 数据缓冲区的使用命中率1 ( physical reads/(db block gets + consistent gets) 这个命中率应该在90以上，否则需要增加数据缓冲区的大小。,3日志缓冲区 数据库管理员可以通过执行下述语句，查看日志缓冲区的使用情况。 select name,value from v$sysstat where name in (redo entries,redo log space requests);,根据查询出的结果可以计算出日志缓冲区的申请失败率： 申请失败率requests/entrie
12、s 申请失败率应该接近于0，否则说明日志缓冲区开设太小，需要增加Oracle数据库的日志缓冲区。,二、物理I/O的调整 （1）在磁盘上建立数据文件前首先运行磁盘碎片整理程序 为了安全地整理磁盘碎片，需关闭打开数据文件的实例，并且停止服务。如果有足够的连续磁盘空间建立数据文件，那么就很容易避免数据文件产生碎片。 （2）不要使用磁盘压缩 Oracle数据文件不支持磁盘压缩。,（3）不要使用磁盘加密 加密象磁盘压缩一样增加了一个处理层，降低磁盘读写速度。如果担心自己的数据可能泄密，可以使用dbms_obfuscation包和label security选择性地加密数据的敏感部分。 （4）不要使用超过
13、70%的磁盘空间。 剩余的磁盘空间存放系统临时数据和作为磁盘碎片整理程序存放中间数据。所以一般情况下，不要使用超过70%的磁盘空间。,（5）使用RAID RAID的使用应注意： 选择硬件RAID超过软件RAID； 日志文件不要放在RAID 5卷上，因为RAID 5读性能高而写性能差； 把日志文件和归档日志放在与控制文件和数据文件分离的磁盘控制系统上。 （6）分离页面交换文件到多个磁盘物理卷 跨越至少两个磁盘建立两个页面文件。可以建立四个页面文件并在性能上受益，确保所有页面文件的大小之和至少是物理内存的两倍。,三、CPU的优化调整 1查看CPU的使用情况 使用操作命令可以看到CPU的使用情况，一
14、般UNIX操作系统的服务器，可以使用sar u命令查看CPU的使用率；NT操作系统的服务器，可以使用NT的性能管理器来查看CPU的使用率。 出现CPU资源不足的情况是很多的：SQL语句的重解析、低效率的SQL语句、锁冲突都会引起CPU资源不足。,2查看SQL语句的解析情况 （1）数据库管理员可以执行下述语句来查看SQL语句的解析情况： SELECT * FROM V$SYSSTAT WHERE NAME IN (parse_time_cpu,parse_time_elapsed,parse_count_ hard); 这里：parse_time_cpu：是系统服务时间。 parse_time_
15、elapsed：是响应时间。 而用户等待时间为： waite_time = parse_time_elapsed parse_time_cpu 由此可以得到用户SQL语句平均解析等待时间： 用户SQL语句平均解析等待时间waite_time/parse_count,（2）数据库管理员还可以通过下述语句，查看低效率的SQL语句： SELECT BUFFER_GETS,EXECUTIONS,SQL_TEXT FROM V$SQLAREA; 优化这些低效率的SQL语句也有助于提高CPU的利用率。,3查看Oracle数据库的冲突情况 数据库管理员可以通过v$system_event数据字典中的“lat
16、ch free”统计项查看Oracle数据库的冲突情况，如果没有冲突的话，latch free查询出来没有结果。如果冲突太大的话，数据库管理员可以降低spin_count参数值，来消除高的CPU使用率。,4CPU的优化调整方法 一些优化CPU使用和配置的具体方法有： （1）取消屏幕保护。 （2）把系统配置为应用服务器。 （3）监视系统中消耗中断的硬件。 （4）保持最小的安全审计记录。 （5）在专用服务器上运行Oracle。 （6）禁止非必须的服务。,四、网络配置的优化 网络配置是性能调整的一项很重要的内容，而且很容易隐藏性能瓶颈。 （1）配置网卡使用最快速度和有效模式 （2）删除不需要的网络协议 （3）优化网络协议绑定顺序 （4）为Oracle禁止或优化文件共享,五、Oracle碎片整理 1碎片是如何产生的 2碎片对系统的影响 （1）导致系统性能减弱 （2）浪费大量的表空间,3自由范围的碎
