初步学习Oracle性能优化
Oracle 性能优化就是通过合理安排资源、调整系统参数使Oracle 运行更快、更节省资源。Oracle性能优化包括查询速度优化、更新速度优化、Oracle服务器优化等。
1.优化简介
优化 Oracle数据库是数据库管理员和数据库开发人员的必备技能。Oracle 优化，一方面是出系统的瓶颈，提高 Oracle 数据库整体的性能；另一方面需要合理的结构设计和参数调整，以提高用户操作响应的速度；同时还要尽可能节省系统资源，以便系统可以提供更大负荷的服务。
Oracle数据库优化是多方面的，原则是减少系统的瓶颈，减少资源的占用，提高系统的反应速度。例如，通过优化文件系统，提高磁盘I/O的读写速度；通过优化操作系统调度策略，提高Oracle在高负荷情况下的负载能力；优化表结构、索引、查询语句等使查询响应更快。
从内存中直接读取数据的速度远远大于从磁盘中读取数据。影响内存读取速度的因素有两个，包括内存的大小和内存的分配、使用和管理方法。Oracle提供了自动内存管理机制，所
以用户只需要手动分配内存即可。
Oracle中的内存主要包括两部分：系统全局区和进程全局区。它们既可以在数据库启动时进行加载，也可以在数据库使用中进行设置。
1.1修改系统全局区
系统全局区，简称为SGA，是System Global Area的缩写。SGA是共享的内存机构，主要存储的是数据库的公用信息，SGA也被称为共享全局区。SGA主要包括共享池、缓冲区、大型池、Java池和日志缓冲区。
查看当前数据库的SGA状态，命令如下：
SQL> show parameter sga;
需要注意的结果有两个：sga_max_size和sga_target。sga_max_size是为SGA分配的最大内存，sga target指定的是数据库可管理的最大内存。如果sga target值为0,表示关闭共享内存区。
在Oracle中，管理员还可以通过视图 v$sgastat来查看SGA的具体分配情况。命令如下：
SQL> SELECT * FROM v$sgastat;
客户端接收到查询语句后，首先进行语法分析，然后是语义分析，最后才是执行步骤。在执行步骤以前的操作就是SQL语句的预处理，这些预处理都是在共享池中进行缓存，缓存的标示是根据SQL语句所形成的Hash 值。服务器收到SQL语句是根据Hash 值在共享池中查是否已经有预处理的SQL语句，如果存在，则直接进行数据库操作，否则将进行语法分析。对于共享池来说，存在命中率的概念，也就是直接从共享池中获取执行计划的成功率。成功率越高，代表数据库的性能越高。因此，共享池命中率是影响SQL语句的重要指标。
修改SGA内存大小，命令如下：
SQL> alter system set sga_max_size=2000M scope=spfile;
scope=spfile表示设置作用到数据库启动文件中，一旦数据库重启，则该参数将立即重启。
修改参数sga_target为2000MB，代码如下：
SQL> alter system set sga_target =2000M scope=spfile;
数据库重启后，SGA的大小已经被成功修改了。
1.2修改进程全局区
进程全局区简称为PGA。每个客户端连接到Oracle服务器都由服务器分配一定内存来保持连接，并将在该内存中实现用户私有操作。所有用户连接的内存集合就是Oracle数据库的PGA。
查看PGA的状态，命令如下：
show parameter pga;
参数pga_aggregate_target可以指定PGA内存的最大值。当pga_aggregate_target值大于0时，Oracle将自动管理PGA内存。
修改PGA的大小，命令如下：
SQL>alter system set pga_aggregate_target=500M scope=both;
scope=both表示同时修改当前环境与启动文件spfile。
2.优化查询
查询是数据库中最频繁的操作，提高查询速度可以有效地提高 Oracle 数据库的性能。
2.1分析查询语句的执行计划
如果想要分析SQL语句的性能，可以查看该语句的执行计划，从而分析每一步执行是否存在问题。
查看执行计划的方法有以下两种：
通过设置 AUTOTRACE查看执行计划
设置AUTOTRACE的具体含义如下：
（1）SET AUTOTRACE OFF：此为默认值，即关闭AUTOTRACE。
（2）SET AUTOTRACE ON EXPLAIN：只显示执行计划。
（3）SET AUTOTRACE ON STATISTICS：只显示执行的统计信息。
（4）SET AUTOTRACE ON：包含（2）、（3）两项的内容。
（5）SET AUTOTRACE TRACEONLY：与（4）相似，但不显示语句的执行结果。
通过设置AUTOTRACE查看执行计划，命令如下：
SQL> set autotrace on;
在查询结果中，Id表示一个序号，但不是执行的先后顺序，执行的先后根据缩进来判断；O
数据库优化sql语句peration表示当前操作的内容；Rows表示当前操作的 Cardinality,Oracle 估计当前操作的返回结果集；Cost（%CPU）表示Oracle计算出来的一个数值，用于说明SQL执行的代价；Time表示 Oracle 估计当前操作的时间。
1、使用EXPLAIN PLAN FOR语句查看执行计划
使用EXPLAIN PLAN FOR语句可以查看执行计划。具体语法格式如下：
EXPLAIN PLAN FOR SQL语句；
通过设置EXPLAINPLAN FOR语句查看执行计划，命令如下：
EXPLAIN PLAN FOR SELECT * FROM dual;
2.2索引对查询速度的影响
Oracle中提高性能的一个最有效的方式就是对数据表设计合理的索引。索引提供了高效访问数据的方法，并且加快查询的速度，因此，索引对查询的速度有着至关重要的影响。使用索引可以快速地定位表中的某条记录，从而提高数据库查询的速度，提高数据库的性能。
如果查询时没有使用索引，查询语句将扫描表中的所有记录。在数据量大的情况下，这样查询的速度会很慢。如果使用索引进行查询，查询语句可以根据索引快速定位到待查询记录，从而减少查询的记录数，达到提高查询速度的目的。
合理的索引可以提高查询的速度，但不是索引越多越好。在执行插入语句的时候，Oracle要为新插入的记录建立索引。所以，过多的索引会导致插入操作变慢。原则上是只有查询用的字段才建立索引。
2.3使用索引查询
索引可以提高查询的速度。但并不是使用带有索引的字段查询时，索引都会起作用。
使用索引有几种特殊情况，在这些情况下，有可能使用带有索引的字段查询时，索引并没有起作用，下面重点介绍这几种特殊情况。
使用LIKE关键字的查询语句
在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不
会起作用。只有“%”不在第一个位置，索引才会起作用。
使用多列索引的查询语句
Oracle可以为多个字段创建索引。一个索引可以包括16个字段。对于多列索引，只有查询条件中使用了这些字段中第1个字段时，索引才会被使用。
使用 OR关键字的查询语句
查询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，查询中才使用索引。否则，查询将不使用索引。
2.4优化子查询
Oracle 支持子查询，使用子查询可以进行 SELECT 语句的嵌套查询，即一个 SELECT 查询的结果作为另一个SELECT 语句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。子查询虽然可以使查询语句很灵活，但执行效率不高。执行子查询时，Oracle需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时
表中查询记录。查询完毕后，再撤销这些临时表。因此，子查询的速度会受到一定的影响。如果查询的数据量比较大，这种影响就会随之增大。
在Oracle中，可以使用连接（JOIN）查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引的话，性能会更好。连接之所以更有效率，是因为Oracle不需要在内存中创建临时表来完成查询工作。
3.优化数据库结构
一个好的数据库设计方案对于数据库的性能常常会起到事半功倍的效果。合理的数据库结构不仅可以使数据库占用更小的磁盘空间，而且能够使查询速度更快。数据库结构的设计，需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。
3.1将字段很多的表分解成多个表
对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而使查询速度变慢。
3.2增加中间表
对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。
3.3增加冗余字段
设计数据库表时应尽量遵循范式理论的规约，尽可能减少冗余字段，让数据库设计看起来精致、优雅。但是，合理地加入冗余字段可以提高查询速度。
表的规范化程度越高，表与表之间的关系就越多，需要连接查询的情况也就越多。
冗余字段会导致一些问题。比如，冗余字段的值在一个表中被修改了，就要想办法在其他表中更新该字段，否则就会使原本一致的数据变得不一致。分解表、增加中间表和增加冗余字段都浪费了一定的磁盘空间。从数据库性能来看，为了提高查询速度而增加少量的冗余大部分时候是可以接受的。是否通过增加冗余来提高数据库性能，这要根据实际需求综合分析。
3.4优化插入记录的速度
插入记录时，影响插入速度的主要是索引、唯一性校验、一次插入记录条数等。根据这些情况，可以分别进行优化。
对于MyISAM引擎的表，常见的优化方法如下：
禁用索引
对于非空表，插入记录时，Oracle会根据表的索引对插入的记录建立索引。如果插入大量数据，建立索引会降低插入记录的速度。为了解决这种情况，可以在插入记录之前禁用索引，数据插入完毕后再开启索引。禁用索引的语句如下：
ALTER index index_name unusable;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。