SQL性能优化应该考虑哪些？



1 、调整数据结构的设计。这一部分在开发信息系统之前完成，程序员需要考虑是否使用 ORACLE 数据库的分区功能，对于经常访问的数据库表是否需要建立索引等。

2 、调整应用程序结构设计。这一部分也是在开发信息系统之前完成，程序员在这一步需要考虑应用程序使用什么样的体系结构，是使用传统的 Client/Server 两层体系结构，还是使用 Browser/Web/Database 的三层体系结构。不同的应用程序体系结构要求的数据库资源是不同的。

3 、调整数据库 SQL 语句。应用程序的执行最终将归结为数据库中的 SQL 语句执行，因此 SQL 语句的执行效率最终决定了 ORACLE 数据库的性能。 ORACLE 公司推荐使用 ORACLE 语句优化器（ Oracle Optimizer ）和行锁管理器（ row-level manager ）来调整优化 SQL 语句。

4 、调整服务器内存分配。内存分配是在信息系统运行过程中优化配置的，数据库管理员可以根据数据库运行状况调整数据库系统全局区（ SGA 区）的数据缓冲区、日志缓冲区和共享池的大小；还可以调整程序全局区（ PGA 区）的大小。需要注意的是， SGA 区不是越大越好， SGA 区过大会占用 操作系统使用的内存而引起虚拟内存的页面交换，这样反而会降低系统。

5 、调整硬盘 I/O ，这一步是在信息系统开发之前完成的。数据库管理员可以将组成同一个表空间的数据文件放在不同的硬盘上，做到硬盘之间 I/O 负载均衡。

6 、调整操作系统参数，例如：运行在 UNIX 操作系统上的 ORACLE 数据库，可以调整 UNIX 数据缓冲池的大小，每个进程所能使用的内存大小等参数。

实际上，上述数据库优化措施之间是相互联系的。 ORACLE 数据库性能恶化表现基本上都是用户响应时间比较长，需要用户长时间的等待。但性能恶化的原因却是多种多样的，有时是多个因素共同造成了性能恶化的结果，这就需要数据库管理员有比较全面的计算机知识，能够敏感地察觉到影响数据库性能的主要原因所在。另外，良好的数据库管理工具对于优化数据库性能也是很重要的。

ORACLE 数据库性能优化工具

常用的数据库性能优化工具有：

1 、 ORACLE 数据库在线数据字典， ORACLE 在线数据字典能够反映出 ORACLE 动态运行情况，对于调整数据库性能是很有帮助的。

2 、操作系统工具，例如 UNIX 操作系统的 vmstat ， iostat 等命令可以查看到系统系统级内存和硬盘 I/O 的使用情况，这些工具对于管理员弄清出系统瓶颈出现在什么地方有时候很有用。

3 、 SQL 语言跟踪工具（ SQL TRACE FACILITY ）， SQL 语言跟踪工具可以记录 SQL 语句的执行情况，管理员可以使用虚拟表来调整实例，使用 SQL 语句跟踪文件调整应用程序性能。 SQL 语言跟踪工具将结果输出成一个操作系统的文件，管理员可以使用 TKPROF 工具查看这些文件。

4 、 ORACLE Enterprise Manager （ OEM ），这是一个图形的用户管理界面，用户可以使用它方便地进行数据库管理而不必记住复杂的 ORACLE 数据库管理的命令。

5 、 EXPLAIN PLAN——SQL 语言优化命令，使用这个命令可以帮助程序员写出高效的 SQL 语言。

ORACLE 数据库的系统性能评估

信息系统的类型不同，需要关注的数据库参数也是不同的。数据库管理员需要根据自己的信息系统的类型着重考虑不同的数据库参数。

1 、在线事务处理信息系统（ OLTP ），这种类型的信息系统一般需要有大量的 Insert 、 Update 操作，典型的系统包括民航机票发售系统、银行储蓄系统等。 OLTP 系统需要保证数据库的并发性、可靠性和最终用户的速度，这类系统使用的 ORACLE 数据库需要主要考虑下述参数：

l     l     数据库回滚段是否足够？

l     l     是否需要建立 ORACLE 数据库索引、聚集、散列？

l     l     系统全局区（ SGA ）大小是否足够？

l     l     SQL 语句是否高效？

2 、数据仓库系统（ Data Warehousing ），这种信息系统的主要任务是从 ORACLE 的海量数据中进行查询，得到数据之间的某些规律。数据库管理员需要为这种类型的 ORACLE 数据库着重考虑下述参数：

l     l     是否采用 B*- 索引或者 bitmap 索引？

l     l     是否采用并行 SQL 查询以提高查询效率？

l     l     是否采用 PL/SQL 函数编写存储过程？

l     l     有必要的话，需要建立并行数据库提高数据库的查询效率

SQL 语句的调整原则

SQL 语言是一种灵活的语言，相同的功能可以使用不同的语句来实现，但是语句的执行效率是很不相同的。程序员可以使用 EXPLAIN PLAN 语句来比较各种实现方案，并选出最优的实现方案。总得来讲，程序员写 SQL 语句需要满足考虑如下规则：

1 、尽量使用索引。试比较下面两条 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);

这两条查询语句实现的结果是相同的，但是执行语句 A 的时候， ORACLE 会对整个 emp 表进行扫描，没有使用建立在 emp 表上的 deptno 索引，执行语句 B 的时候，由于在子查询中使用了联合查询， ORACLE 只是对 emp 表进行的部分数据扫描，并利用了 deptno 列的索引，所以语句 B 的效率要比语句 A 的效率高一些。

2 、选择联合查询的联合次序。考虑下面的例子：

SELECT stuff FROM taba a, tabb b, tabc c

WHERE a.acol between :alow and :ahigh

AND b.bcol between :blow and :bhigh

AND c.ccol between :clow and :chigh

AND a.key1 = b.key1

AMD a.key2 = c.key2;

这个 SQL 例子中，程序员首先需要选择要查询的主表，因为主表要进行整个表数据的扫描，所以主表应该数据量最小，所以例子中表 A 的 acol 列的范围应该比表 B 和表 C 相应列的范围小。

3 、在子查询中慎重使用 IN 或者 NOT IN 语句，使用 where (NOT) exists 的效果要好的多。

4 、慎重使用视图的联合查询，尤其是比较复杂的视图之间的联合查询。一般对视图的查询最好都分解为对数据表的直接查询效果要好一些。

5 、可以在参数文件中设置 SHARED_POOL_RESERVED_SIZE 参数，这个参数在 SGA 共享池中保留一个连续的内存空间，连续的内存空间有益于存放大的 SQL 程序包。

6 、 ORACLE 公司提供的 DBMS_SHARED_POOL 程序可以帮助程序员将某些经常使用的存储过程 “ 钉 ” 在 SQL 区中而不被换出内存，程序员对于经常使用并且占用内存很多的存储过程 “ 钉 ” 到内存中有利于提高最终用户的响应时间。

CPU 参数的调整

CPU 是服务器的一项重要资源，服务器良好的工作状态是在工作高峰时 CPU 的使用率在 90 ％以上。如果空闲时间 CPU 使用率就在 90 ％以上，说明服务器缺乏 CPU 资源，如果工作高峰时 CPU 使用率仍然很低，说明服务器 CPU 资源还比较富余。

使用操作相同命令可以看到 CPU 的使用情况，一般 UNIX 操作系统的服务器，可以使用 sar –u 命令查看 CPU 的使用率， NT 操作系统的服务器，可以使用 NT 的性能管理器来查看 CPU 的使用率。

数据库管理员可以通过查看 v$sysstat 数据字典中 “CPU used by this session” 统计项得知 ORACLE 数据库使用的 CPU 时间，查看 “OS User level CPU time” 统计项得知操作系统用户态下的 CPU 时间，查看 “OS System call CPU time” 统计项得知操作系统系统态下的 CPU 时间，操作系统总的 CPU 时间就是用户态和系统态时间之和，如果 ORACLE 数据库使用的 CPU 时间占操作系统总的 CPU 时间 90 ％以上，说明服务器 CPU 基本上被 ORACLE 数据库使用着，这是合理，反之，说明服务器 CPU 被其它程序占用过多， ORACLE 数据库无法得到更多的 CPU 时间。

数据库管理员还可以通过查看 v$sesstat 数据字典来获得当前连接 ORACLE 数据库各个会话占用的 CPU 时间，从而得知什么会话耗用服务器 CPU 比较多。

出现 CPU 资源不足的情况是很多的： SQL 语句的重解析、低效率的 SQL 语句、锁冲突都会引起 CPU 资源不足。

1 、数据库管理员可以执行下述语句来查看 SQL 语句的解析情况：

SELECT * FROM V$SYSSTAT

WHERE NAME IN

('parse time cpu', 'parse time elapsed', 'parse count (hard)');

这里 parse time cpu 是系统服务时间， parse time elapsed 是响应时间，用户等待时间

waite time = parse time elapsed – parse time cpu

由此可以得到用户 SQL 语句平均解析等待时间＝ waite time / parse count 。这个平均等待时间应该接近于 0 ，如果平均解析等待时间过长，数据库管理员可以通过下述语句

SELECT SQL_TEXT, PARSE_CALLS, EXECUTIONS FROM V$SQLAREA

ORDER BY PARSE_CALLS;

来发现是什么 SQL 语句解析效率比较低。程序员可以优化这些语句，或者增加 ORACLE 参数 SESSION_CACHED_CURSORS 的值。

2 、数据库管理员还可以通过下述语句：

SELECT BUFFER_GETS, EXECUTIONS, SQL_TEXT FROM V$SQLAREA;

查看低效率的 SQL 语句，优化这些语句也有助于提高 CPU 的利用率。

3 、数据库管理员可以通过 v$system_event 数据字典中的 “latch free” 统计项查看 ORACLE 数据库的冲突情况，如果没有冲突的话， latch free 查询出来没有结果。如果冲突太大的话，数据库管理员可以降低 spin_count 参数值，来消除高的 CPU 使用率。

内存参数的调整

内存参数的调整主要是指 ORACLE 数据库的系统全局区（ SGA ）的调整。 SGA 主要由三部分构成：共享池、数据缓冲区、日志缓冲区。

1 、    共享池由两部分构成：共享 SQL 区和数据字典缓冲区，共享 SQL 区是存放用户 SQL 命令的区域，数据字典缓冲区存放数据库运行的动态信息。数据库管理员通过执行下述语句：

select (sum(pins - reloads)) / sum(pins) "Lib Cache"  from v$librarycache;

来查看共享 SQL 区的使用率。这个使用率应该在 90 ％以上，否则需要增加共享池的大小。数据库管理员还可以执行下述语句：

select (sum(gets - getmisses - usage - fixed)) / sum(gets) "RowCache" from v$rowcache;

查看数据字典缓冲区的使用率，这个使用率也应该在 90 ％以上，否则需要增加共享池的大小。

2 、    数据缓冲区。数据库管理员可以通过下述语句：

SELECT name, value  FROM v$sysstat  WHERE name IN ('db block gets', 'consistentgets','physical reads');

来查看数据库数据缓冲区的使用情况。查询出来的结果可以计算出来数据缓冲区的使用命中率＝ 1 - ( physical reads / (dbblock gets + consistent gets) ) 。

这个命中率应该在 90 ％以上，否则需要增加数据缓冲区的大小。

3 、    日志缓冲区。数据库管理员可以通过执行下述语句：

select name,value from v$sysstat where name in ('redo entries','redo log spacerequests'); 查看日志缓冲区的使用情况。查询出的结果可以计算出日志缓冲区的申请失败率：

申请失败率＝ requests/entries ，申请失败率应该接近于 0 ，否则说明日志缓冲区开设太小，需要增加 ORACLE 数据库的日志缓冲区。