关系型数据库(Oracle)性能优化

最新推荐文章于 2022-11-08 12:53:07 发布

codepeter

最新推荐文章于 2022-11-08 12:53:07 发布

阅读量310

点赞数

分类专栏： Oracle 文章标签：数据库 oracle

本文链接：https://blog.csdn.net/codepeter/article/details/108125385

版权

Oracle 专栏收录该内容

14 篇文章 1 订阅

订阅专栏

数据库性能分析

**数据库具体状态分析 **
1、Top 5 Timed Events：
消耗时间最多的5个等待事件，每种等待都表示一种原因：db file sequential read表示按索引访问出现等待，db file scattered reade表示全表扫描访问出现等待
2、Top N SQL：
根据时间消耗，内存消耗，物理I/O等排序，对相关SQL分析执行计划
3、如果是RAC环境，需要特别关注RAC Statistic中的相关指标
4、SGA PGA分析
5、分析表空间、数据文件I/O

**数据库整体状态分析 **
1、Load Profile：
了解系统整体负载状况，每秒中事务数/语句数，每秒/每事务物理读写次数(Physical Reads/Writes), 逻辑读写次数(Logical Reads/Writes)，SQL语句的解析(Parse)，特别是硬解析次数等
2、Instance Efficiency Percentages：
各指标都应接近100%，除了execute to parse(70%以上)和parse cpu to parse elapsed。如不符合，基本可确定系统存在性能问题；但如果都符合，也不能说明系统完全正常，还要看实际情况

**分析一、解析数据库概要信息 **

Elapsed:574.64 (mins)表明采样时间是574分钟，任何数据都要通过这个时间来衡量，离开了这个采样时间，任何数据都毫无意义
DB Time:4945.26(mins)表明用户操作花费的时候，包括cpu时间和等待时间
DB time=cpu time+wait time db time就是前端用户进程所花费每个CPU耗时:=DB Time/逻辑cpu个数=11.05/8=1.4 CPU利用率:DB Time/(逻辑cpu个数Elapsed)=11.05/(878.19)*100%=2%

提示 
select value/1000000,t.*from v$sys_time_model t where stat_name='DBtime';

select round((select round(e.value/1000000,2) dbtime 
FROM v$SYS_TIME_MODEL e 
WHERE e.STAT_NAME='DB time')*100/
(select ((systimestamp+0)-startup_time)*24*60*60 dbtime_duartion_ from v$instance),2) dbtime_per 
from dual;

** 分析二、实例效率统计 **

1、Buffer Nowait 说明从内存取数据时，没有经历等待的比例，期望值是100%。这里99.5%属正常值。
2、Buffer Hit 说明从内存取数据时，buffer的命中率比例期望值是100%。buffer hit是一个过时的指标，不太准确，但也有一定诊断意义。
3、Library Hit 说明sql在Shared Pool的命中率，期望值是100%
4、Execute to Parse 说明解析sql和执行sql之间的比例，越高越好，一次解析，到处执行，如果parse多，execute少的话，还会出现负数，因为计算公式是100*(1-parse/execute)
5、Parse CPU to Parse Elapsd 说明在解析sql语句过程中，cpu占整个解析时间比例期望值是100%，需要说明的是，即使有硬解析，只要cpu没出现性能问题，可以容忍，比较硬解析有它的好处。
6、Redo NoWait 说明产生日志时，没有产生等待，期望值是100%
7、Soft Parse 说明软解析的比例，期望值是100%，有一点要说明的是，不要单方面的追求软解析的高比例，而去绑定变量，要看性能的瓶颈在哪里
8、Latch Hit 说明latch的命中率，期望值是100%，latch类似锁，是一种内存锁，但只会产生等待，不会产生阻塞，和lock还是有区别的，latch是在并发的情况下产生的
9、Non-Parse CPU 说明非解析cpu的比例，越高越好，用100减去这个比例，可以看出解析sql所花费的cpu，100-99.30=0.7，说明花费在解析sql上的cpu很少

**分析三、前5名的TOP定时事件 **

db file scattered read 等待事件是当SESSION等待multi-block I/O时发生的，通过是由于full table scans或 index fast full scans。发生过多读操作的Segments可以在“Segments by Physical Reads”和 “SQL ordered by Reads”节中识别（在其它版本的报告中，可能是别的名称）。如果在OLTP应用中，不应该有过多的全扫描操作，而应使用选择性好的索引操作。
DB file sequential read 等待意味着发生顺序I/O读等待（通常是单块读取到连续的内存区域中），如果这个等待非常严重，应该使用上一段的方法确定执行读操作的热点SEGMENT，然后通过对大表进行分区以减少I/O量，或者优化执行计划（通过使用存储大纲或执行数据分析）以避免单块读操作引起的sequential read等待。通过在批量应用中，DB file sequential read是很影响性能的事件，总是应当设法避免。
Log File Parallel Write事件是在等待LGWR进程将REDO记录从LOG 缓冲区写到联机日志文件时发生的。虽然写操作可能是并发的，但LGWR需要等待最后的I/O写到磁盘上才能认为并行写的完成，因此等待时间依赖于OS完成所有请求的时间。如果这个等待比较严重，可以通过将LOG文件移到更快的磁盘上或者条带化磁盘（减少争用）而降低这个等待。
Buffer Busy Waits事件是在一个SESSION需要访问BUFFER CACHE中的一个数据库块而又不能访问时发生的。缓冲区“busy”的两个原因是：1）另一个SESSION正在将数据块读进BUFFER。2）另一个SESSION正在以排它模式占用着这块被请求的BUFFER。可以在“Segments by Buffer Busy Waits”一节中找出发生这种等待的SEGMENT，然后通过使用reverse-key indexes并对热表进行分区而减少这种等待事件。
Log File Sync事件当用户SESSION执行事务操作（COMMIT或ROLLBACK等）后，会通知 LGWR进程将所需要的所有REDO信息从LOG BUFFER写到LOG文件，在用户SESSION等待LGWR返回安全写入磁盘的通知时发生此等待。减少此等待的方法写Log File Parallel Write事件的处理。

查询优化方法

**查询优化尽量避免全表扫描 **

首先应考虑在 where 及 order by 涉及的列上建立索引
应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引。尽可能的使用 NOT NULL填充数据库
应尽量避免在 where 子句中使用 != 或 <> 操作符，否则引擎将放弃使用索引
应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有，将导致引擎放弃使用索引
应尽量避免在 where 子句中对字段进行表达式操作：select id from t where num/2=100，应改为select id from t where num=100*2
应尽量避免在 where 子句中对字段进行函数操作：select id from t where substring(name,1,3)=’abc’，应改为select id from t where name like ‘abc%’
应尽量避免使用耗费资源的操作：带有UNION, MINUS, INTERSECT的SQL语句都可以用其他方式重写
应尽量少嵌套子查询，会消耗大量CPU资源；对比较多or运算的查询，建议分成多个查询union all联结
用EXISTS替换DISTINCT：SELECT a,b FROM t WHERE EXISTS(SELECT ‘X’ FROM e WHERE e.a=t.a)优于SELECT DISTINCT a,b FROM t,e WHERE t.a=e.a
用UNION-ALL替换UNION：SELECT a,b FROM t WHERE a=’a’ UNION ALL SELECT a,b FROM e WHERE a=’a’优于SELECT a,b FROM t WHERE a=’a’ UNION SELECT a,b FROM e WHERE a=’a’
用DECODE提高效率：SELECT COUNT(DECODE(a,2,‘X’,’’)),SUM(DECODE(a,2,b,0)) FROM t WHERE a=2 and n LIKE 'S%'优于SELECT COUNT(a),SUM(b) FROM t WHERE a=2 and n LIKE ‘S%’
WHERE子句中的连接顺序：排在最后的表应当是返回行数可能最少的表
WHERE子句替换HAVING：HAVING检索出所有记录才进行过滤SELECT a,AVG(b) FROM t WHERE a!=‘SP’ GROUP BY a优于SELECT a,AVG(b) FROM t GROUP BY a HAVING a!=‘SP’
WHERE子句中使用参数，会导致全表扫描：select id from t where num=@num，应改为select id from t with(index(索引名)) where num=@num
查询：select id from t where name like ‘%abc%’导致全表扫描，若要提高效率，可以考虑全文检索
查询慎用in 和 not in，否则会导致全表扫描：SELECT a FROM t WHERE a>0 优于 SELECT a FROM t WHERE a not in (0)
查询不带任何条件的count会引起全表扫描，并且没有任何业务意义，一定要杜绝
多表查询的语句，选择最有效率的表名顺序。Oracle解析器对表解析从右到左，所以记录少的表放在右边
减少表查询：SELECT a FROM t WHERE (a,b)=(SELECT a,b) FROM e WHERE v=6)优于SELECT a FROM t WHERE a=(SELECT a FROM e WHERE v=6) AND b=(SELECT b FROM e WHERE v=6)
外部联接"+"的用法：select t.c from t,t1 where t.c=t1.c(+) and t1.c is null and t1.b='SALE’优于select t.c from t where t.c not in(select c from t1 where b=‘SALE’)

**如何创建和使用索引 **

索引并不是越多越好，提高 select 的效率的同时，降低了 insert 及 update 的效率，索引数最好不要超过6个，若太多考虑不常使用的列上建的索引是否必要
使用索引字段作为条件，如果是复合索引，必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，并且应尽可能字段顺序与索引顺序一致
使用带通配符（%）的like语句，通配符（%）在搜寻词首，系统不使用l的索引：select * from t where l like '%c%‘应为select * from t where l like ‘c%’
应尽可能避免更新 clustered 索引数据列，该列顺序就是表记录物理存储顺序，若频繁更新 clustered 索引数据列，那么需考虑是否将该索引建为 clustered 索引
应尽可能避免使用表变量代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）
应尽可能避免出现索引列自动转换：如a是一个字符类型索引列，SELECT a FROM t WHERE a=109 转换为：SELECT a FROM t WHERE TO_NUMBER(a)=109，这个索引将不会被用到
应尽可能避免在order by子句中使用表达式：任何在Order by语句的非索引项或者有计算表达式都将降低查询速度
应尽可能避免联接列，即使最后联接值为静态值，优化器不会使用索引：select * from t where f=‘Beill’ and l=‘Cliton’优于select * from t where f||’’||l=‘Beill Cliton’
索引列上避免使用计算：SELECT a FROM t WHERE b>25000/12优于SELECT a FROM t WHERE b*12>25000
索引列上用>= 替代 >：SELECT * FROM t WHERE n>=4优于SELECT * FROM t WHERE n>3
索引列上通过使用>=、<=等，避免使用NOT：select * from t where a<>3000优于select * from t where a<3000 or a>3000

**如何设计表结构 **

不写没有意义的查询，如生成空表结构：select col1,col2 into #t from t where 1=0不会返回结果集，消耗系统资源，应改成：create table #t(…)
Update 语句，如果只更改1、2个字段，不要Update全部字段，频繁调用会引起明显性能消耗，同时带来大量日志
只含数值信息的字段尽量使用数字型，不要设计为字符型，降低性能，增加存储开销。引擎处理查询会逐个比较字符串中每一个字符，对于数字型只需要比较一次就够了
尽可能使用 varchar/nvarchar 代替 char/nchar，首先变长字段存储空间小，节省存储空间，其次对于查询，一个相对较小的字段内搜索效率显然要高些
避免频繁创建和删除临时表，减少资源消耗。例如，当需要重复引用大型表或常用表中的某个数据集时使用临时表。但对于一次性事件，最好使用导出表
新建临时表，一次性插入数据量很大，使用 select into 代替 create table，可以避免造成大量日志，以提高速度；如果数据量不大，为缓和系统表资源，先create table，再insert

**如何优化业务设计 **

对于多张大数据量（这里几百条就算大了）的表JOIN，要先分页再JOIN，否则逻辑读会很高，性能很差
尽量避免使用 select * from t ，用具体字段列表代替“*”，不要返回用不到的任何字段
尽量避免大事务操作，提高系统并发能力
尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理
避免使用游标，因为游标效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写
使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效
游标并不是不可使用。小型数据集使用 FAST_FORWARD 游标通常优于其他逐行处理方法，尤其是在必须引用几个表才获得所需数据时。结果集中包括“合计”的例程比使用游标执行的速度快
基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好
如果使用到了临时表，在存储过程最后务必将所有临时表显式删除，先 truncate table，然后 drop table，可避免系统表的较长时间锁定
所有存储过程和触发器的开始处设置 SET NOCOUNT ON，结束时设置 SET NOCOUNT OFF。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息

数据库性能优化工具

SQL共享原理

执行过的SQL语句存放在内存的共享池(shared buffer pool)中，可以被所有的数据库用户共享。
一个SQL语句（有时称为游标），和之前执行过的语句完全相同，ORACLE很快获得已经被解析的语句和最好的执行路径。大大提高了SQL执行性能并节省了内存使用
可惜的是，Oracle只对简单的表提供高速缓冲(cache bufferiIlg)，这个功能并不适用于多表连接查询。
数据库管理员必须在启动参数文件中为这个区域设置合适的参数，这个内存区域越大，就可以保留更多的语句，当然被共享的可能性就越大。
如果避免重编译：select * from t where col=320 可以用 select * from t where col=@num 绑定变量只需要硬解析一次；如果是常量每执行一次，就硬解析一次

**ORACLE优化器模式 **

RULE （基于规则）、COST（基于成本）、CHOOSE（基于选择）
启动参数文件中对OPTIMIZER_MODE参数各种声明选择，如RULE、COST、CHOOSE、ALL_ROWS、FIRST_ROWS。也可以在SQL语句级别或是会话级别对其进行覆盖

**优化器与执行计划 **

执行一个SQL之前，首先要分析一下语句的执行计划，然后再按执行计划去执行。分析语句的执行计划的工作是由优化器(Optimizer)来完成的。
优化器共有两种的优化方式，即基于规则的优化方式（Rule-Based Optimization，简称为RBO）和基于代价的优化方式（Cost-Based Optimization，简称为CBO）。在Oracle10g中，取消了RBO的支持。
返回

A、RBO方式：优化器在分析SQL语句时，所遵循的是Oracle内部预定的一些规则。比如我们常见的,当一个where子句中的一列有索引时去走索引。
B、CBO方式：看语句代价(Cost)，这里的代价主要指Cpu和内存。优化器在判断是否用这种方式时，主要是参照表及索引的统计信息，很多的时侯过期统计信息会令优化器做出一个错误的执行计划。Oracle8及以后版本，Oracle推荐用CBO的方式。

**用EXPLAIN PLAN分析SQL语句 **

EXPLAIN PLAN是一个很好的分析SQL语句的工具，它甚至可以在不执行SQL的情况下分析语句。通过分析，我们就可以知道ORACLE是怎么样连接表，使用什么方式扫描表（索引扫描或全表扫描）以及使用到的索引名称。

提示
SQL> select ename,dname from emp, dept
where emp.deptno=dept.deptno
and dept.dname in ('ACCOUNTING','RESEARCH','SALES','OPERATIONS');
Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE
1    0   NESTED LOOPS
2    1     TABLE ACCESS (FULL) OF 'EMP'
3    1     TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
4    3       INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
最起码要解决全表扫描问题
改变where条件的次序一般没有用

最起码要解决全表扫描问题
改变where条件的次序一般没有用

**使用TKPROF工具 **

SQL trace工具收集正在执行的SQL的性能状态数据并记录到一个跟踪文件中。这个跟踪文件提供了许多有用的信息，例如解析次数，执行次数，CPU使用时间等，这些数据将可以用来优化系统。
设置SQL TRACE在会话级别: 有效
ALTER SESSION SET SQL_TRACE TRUE
设置SQL TRACE在整个数据库有效, 你必须将SQL_TRACE参数在init.ora中设为TRUE, USER_DUMP_DEST参数说明了生成跟踪文件的目录。
再使用TKPROF对TRACE文件进行分析，结果更加准确、清楚。

**在SQLPLUS 配置AUTOTRACE **
首先创建PLUSTRACE角色并且赋给public
赋权限给用户
以SYSTEM用户创建PLAN_TABLE表
在每个用户下设置AUTOTRACE可显示其执行计划
Sql> @ $ORACLE_HOME/sqlplus/admin/plustrce.sql Sql> grant plustrace to public;（预赋权的用户名） Sql> @$ ORACLE_HOME/rdbms/admin/utlxplan.sql
Sql> create public synonym plan_table for plan_table;
Sql> grant all on plan_table to public;

codepeter

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
关系型数据库(Oracle)性能优化

数据库性能分析**数据库具体状态分析 **1、Top 5 Timed Events：消耗时间最多的5个等待事件，每种等待都表示一种原因：db file sequential read表示按索引访问出现等待，db file scattered reade表示全表扫描访问出现等待2、Top N SQL：根据时间消耗，内存消耗，物理I/O等排序，对相关SQL分析执行计划3、如果是RAC环境，需要特别关注RAC Statistic中的相关指标4、SGA PGA分析5、分析表空间、数据文件I/O**
复制链接

扫一扫