Oracle 性能优化小结

最新推荐文章于 2024-10-03 20:57:12 发布

ct075538

最新推荐文章于 2024-10-03 20:57:12 发布

阅读量80

点赞数

文章标签：数据库

1 SQL优化方面

1.1 尽量减少硬解析

当一个用户与数据库建立连接后，会向数据库发出操作请求，即向数据库送过去SQL语句。服务器进程会将SQL语句转变为asc码，并通过一个hash函数将asc码生成出一个hash值，服务器进程会到共享池中library cache查询此hash是否存在，如果存在，服务器进程会从library cache中读取已经解析好的语句来执行（软解析）。如果不存在，则需要做以下步骤：生成执行计划和生成执行编码。解析完成后，Oracle会将SQL语句本身代码、hash值、编译代码、执行计划和所有与该语句相关的统计数据存放到library cache中。（硬解析）。

在优化时要注意：
1.SQL书写要规范，大小写不同，空格数量的不同会生成不同的asc码在解析时会被认为是不同的SQL语句。

2.对大量重复的SQL使用绑定变量。

1.2 选择优化器

优化器是SQL优化的关键。SQL在硬解析时会用到优化器，优化器分为RBO（Rule Based Optimizer）和CBO(Cost Based Optimizer)两种。RBO按固定的规则生成执行计划，CBO更智能一些会生成消耗cup和O/I最少的执行计划。推荐使用CBO优化器。

1.3 SQL语句优化

Oracle数据库80%的性能问题是由SQL引起的，尽量减少SQL语句消耗的逻辑读，物理读，CUP时间，热点争用。

在优化时要注意：

1.合理创建索引。

2.避免使用前置通配符如：like ‘%ABC’。

3.用exists,和not exists 代替in 和not in。

4.避免使用*,因为在解析时会查询数据字典将“*”转换成所有列名。

2 内存优化方面

2.1 合理设置share pool
    shared pool 中library cache和data dictionary cache的大小，严重影响着解析过程；library cache越大，存放的执行计划越多，从而减少硬解析。如果library cache过小，SQL语句会经常进行硬解析，导致SQL语句执行时间增加。增大share_pool_size可以保留更多的缓存在内存中的sql语句执行计划，也意味着共享SQL的可能性的增大。
    Oracle引入了data dictionary cache 组件以用来在内存中直接缓存数据字典中的信息，避免了系统从磁盘直接读取信息，提高了系统性能。如果data dictionary cache 组件内存很小，或者缓存的信息不是本次所需要的数据，就需要从磁盘读取信息，会直接影响系统性能。shared pool中library cache和data dictionary cache两个缓冲区可动态的扩展或收缩。但是内存的扩展与收缩操作，也会产生cpu等开销。为了避免两者其一过大，导致另一个缓冲区过小的状况，可以设置缓冲区的最小值阈值。

2.2 合理设置DB buffer cache
    尽量少读数据文件，提高命中率。在硬解析时，service process会试图从DB buffer cache中读取是否存在相关的缓存数据。如果不存在相关数据，则服务器进程会把相应的块从datafiles里读入到DB buffer cache中，此时如果DB buffer cache空闲空间不足，则会触发写操作—DBWn。就会触发DBWr进程，将DB buffer cache中脏数据写入数据文件。腾出来的空间写入新数据。如果DB buffer cache太小命中率会降低，会增加物理I/O。

2.3 合理设置 redo log buffer 和redo日志组
    当redo log files里的一组日志被写满时，在开启归档的情况下会触发ARCn进程对日志进行归档，并切换到下一组日志进行循环写。当日志再次切换回该组日志但还没有完成归档时，数据库就会等待其完成归档再写入新的日志，这将导致大量的性能下降。所以应该适当增加日志文件组的个数和每组日志的大小。但是，当日志组个数很多时并且每组很小时会导致日志组频繁的进行切换，会触发CKPT检查点进程，检查点进程会把scn写进数据文件头和控制文件中，当需要实例恢复时，会读取控制文件中的最后一个检查点位置作为起点，运用redo进行前滚。检查点进程会触发DBWn进程将DB buffer cache中的脏块频繁写到datafiles中，导取数据时先要将datafiles中的数据块读到DB buffer cache中，增长了SQL语句的执行时间。所以，正确的设置好日志组的个数以及每组的大小很重要。

2.4 尽量减少排序
如果面临order by 排序时，将要进行PGA sort内存排，如果数据量很大，PGA不能满足内存排所需空间时，就需要与磁盘上的临时表空间进行存储分配交换，这个磁盘分配交换将要对临时文件频繁进行写和读，因为对磁盘频繁操作，导致系统性能大大降低。此时，需要将PGA优化，如果不得不使用临时文件时，就需要创建多个临时文件，并发降低读写压力，以达到优化效果。

DBA_建瑾
2013.11.20

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29324876/viewspace-777153/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29324876/viewspace-777153/