oracle数据库查询优化

❶ 优化数据库大幅度提高Oracle的性能

几个简单的步骤大幅提高Oracle性能 我优化数据库的三板斧

数据库优化的讨论可以说是一个永恒的主题 资深的Oracle优化人员通常会要求提出性能问题的人对数据库做一个statspack 贴出数据库配置等等 还有的人认为要抓出执行最慢的语句来进行优化 但实际情况是 提出疑问的人很可能根本不懂执行计划 更不要说statspack了 而我认为 数据库优化 应该首先从大的方面考虑 网络 服务器硬件配置 操作系统配置 Oracle服务器配置 数据结构组织 然后才是具体的调整 实际上网络 硬件等往往无法决定更换 应用程序一般也无法修改 因此应该着重从数据库配置 数据结构上来下手 首先让数据库有一个良好的配置 然后再考虑具体优化某些过慢的语句 我在给我的用户系统进行优化的过程中 总结了一些基本的 简单易行的办法来优化数据库 算是我的三板斧 呵呵 不过请注意 这些不一定普遍使用 甚至有的会有副作用 但是对OLTP系统 基于成本的数据库往往行之有效 不妨试试 （注 附件是Burleson写的用来报告数据库性能等信息的脚本 本文用到）

一．设置合适的SGA

常常有人抱怨服务器硬件很好 但是Oracle就是很慢 很可能是内存分配不合理造成的 ( )假设内存有 M 这通常是小型应用 建议Oracle的SGA大约 M 其中 共享池（SHARED_POOL_SIZE）可以设置 M到 M 根据实际的用户数 查询等来定 数据块缓冲区可以大致分配 M M i下需要设置DB_BLOCK_BUFFERS DB_BLOCK_BUFFER*DB_BLOCK_SIZE等于数据块缓冲区大小 i 下的数据缓冲区可以用db_cache_size来直接分配

( )假设内存有 G Oracle 的SGA可以考虑分配 M 共享池分配 M到 M 数据缓冲区分配 M到 M

( )内存 G SGA可以考虑分配 G 共享池 M到 M 剩下的给数据块缓冲区

( )内存 G以上 共享池 M到 M就足够啦 再多也没有太大帮助 (Biti_rainy有专述)数据缓冲区是尽可能的大 但是一定要注意两个问题 一是要给操作系统和其他应用留够内存 二是对于 位的操作系统 Oracle的SGA有 G的限制 有的 位操作系统上可以突破这个限制 方法还请看Biti的大作吧

二．分析表和索引 更改优化模式

Oracle默认优化模式是CHOOSE 在这种情况下 如果表没有经过分析 经常导致查询使用全表扫描 而不使用索引 这通常导致磁盘I/O太多 而导致查询很慢 如果没有使用执行计划稳定性 则应该把表和索引都分析一下 这样可能直接会使查询速度大幅提升 分析表命令可以用ANALYZE TABLE 分析索引可以用ANALYZE INDEX命令 对于少于 万的表 可以考虑分析整个表 对于很大的表 可以按百分比来分析 但是百分比不能过低 否则生成的统计信息可能不准确 可以通过DBA_TABLES的LAST_ANALYZED列来查看表是否经过分析或分析时间 索引可以通过DBA_INDEXES的LAST_ANALYZED列

下面通过例子来说明分析前后的速度对比 （表CASE_GA_AJZLZ大约有 万数据 有主键）首先在sqlPLUS中打开自动查询执行计划功能 (第一次要执行RDBMSADMINutlxplan sql来创建PLAN_TABLE这个表)

SQL> SET AUTOTRACE ON SQL>SET TIMING ON

通过SET AUTOTRACE ON 来查看语句的执行计划 通过SET TIMING ON 来查看语句运行时间

SQL> select count(*) from CASE_GA_AJZLZ; COUNT(*) 已用时间: : : Execution Plan SELECT STATEMENT Optimizer=CHOOSE SORT (AGGREGATE) TABLE ACCESS (FULL) OF CASE_GA_AJZLZ ……………………

请注意上面分析中的TABLE ACCESS(FULL) 这说明该语句执行了全表扫描 而且查询使用了 秒 这时表还没有经过分析 下面我们来对该表进行分析

SQL> *** yze table CASE_GA_AJZLZ pute statistics;

表已分析 已用时间: : : 然后再来查询

SQL> select count(*) from CASE_GA_AJZLZ; COUNT(*) 已用时间: : : Execution Plan SELECT STATEMENT Optimizer=FIRST_ROWS (Cost= Card= ) SORT (AGGREGATE) INDEX (FAST FULL SCAN) OF PK_AJZLZ (UNIQUE) (Cost= Card= ) …………………………

请注意 这次时间仅仅用了 秒！这要归功于INDEX(FAST FULL SCAN) 通过分析表 查询使用了PK_AJZLZ索引 磁盘I/O大幅减少 速度也大幅提升！下面的实用语句可以

用来生成分析某个用户的所有表和索引 假设用户是GAXZUSR

SQL> set pagesize SQL> spool d: *** yze_tables sql; SQL> select *** yze table ||owner|| ||table_name|| pute statistics; from dba_tables where owner= GAXZUSR ; SQL> spool off SQL> spool spool d: *** yze_indexes sql; SQL> select *** yze index ||owner|| ||index_name|| pute statistics; from dba_indexes where owner= GAXZUSR ; SQL> spool off SQL> @d: *** yze_tables sql SQL> @d: *** yze_indexes sql

解释 上面的语句生成了两个sql文件 分别分析全部的GAXZUSR的表和索引 如果需要按照百分比来分析表 可以修改一下脚本 通过上面的步骤 我们就完成了对表和索引的分析 可以测试一下速度的改进啦 建议定期运行上面的语句 尤其是数据经过大量更新

当然 也可以通过dbms_stats来分析表和索引 更方便一些 但是我仍然习惯上面的方法 因为成功与否会直接提示出来

另外 我们可以将优化模式进行修改 optimizer_mode值可以是RULE CHOOSE FIRST_ROWS和ALL_ROWS 对于OLTP系统 可以改成FIRST_ROWS 来要求查询尽快返回结果 这样即使不用分析 在一般情况下也可以提高查询性能 但是表和索引经过分析后有助于找到最合适的执行计划

三．设置cursor_sharing=FORCE 或SIMILAR

这种方法是 i才开始有的 oracle 不支持 通过设置该参数 可以强制共享只有文字不同的语句解释计划 例如下面两条语句可以共享

SQL> SELECT * FROM MYTABLE WHERE NAME= tom SQL> SELECT * FROM MYTABLE WHERE NAME= turner

这个方法可以大幅降低缓冲区利用率低的问题 避免语句重新解释 通过这个功能 可以很大程度上解决硬解析带来的性能下降的问题 个人感觉可根据系统的实际情况 决定是否将该参数改成FORCE 该参数默认是exact 不过一定要注意 修改之前 必须先给ORACLE打补丁 否则改之后oracle会占用 %的CPU 无法使用 对于ORACLE i 可以设置成SIMILAR 这个设置综合了FORCE和EXACT的优点 不过请慎用这个功能 这个参数也可能带来很大的负面影响！

四．将常用的小表 索引钉在数据缓存KEEP池中

内存上数据读取速度远远比硬盘中读取要快 据称 内存中数据读的速度是硬盘的 倍！如果资源比较丰富 把常用的小的 而且经常进行全表扫描的表给钉内存中 当然是在好不过了 可以简单的通过ALTER TABLE tablename CACHE来实现 在ORACLE i之后可以使用ALTER TABLE table STORAGE(BUFFER_POOL KEEP) 一般来说 可以考虑把 数据块之内的表放在keep池中 当然要根据内存大小等因素来定 关于如何查出那些表或索引符合条件 可以使用本文提供的access sql和access_report sql 这两个脚本是着名的Oracle专家 Burleson写的 你也可以在读懂了情况下根据实际情况调整一下脚本 对于索引 可以通过ALTER INDEX indexname STORAGE(BUFFER_POOL KEEP)来钉在KEEP池中

将表定在KEEP池中需要做一些准备工作 对于ORACLE i 需要设置DB_KEEP_CACHE_SIZE 对于 i 需要设置buffer_pool_keep 在 i中 还要修改db_block_lru_latches 该参数默认是 无法使用buffer_pool_keep 该参数应该比 * *CPU数量少 但是要大于 才能设置DB_KEEP_CACHE_BUFFER buffer_pool_keep从db_block_buffers中分配 因此也要小于db_block_buffers 设置好这些参数后 就可以把常用对象永久钉在内存里

五．设置optimizer_max_permutations

对于多表连接查询 如果采用基于成本优化(CBO) ORACLE会计算出很多种运行方案

从中选择出最优方案 这个参数就是设置oracle究竟从多少种方案来选择最优 如果设置太大 那么计算最优方案过程也是时间比较长的 Oracle 和 i默认是 建议改成 对于 i 已经默认是 了

六．调整排序参数

( ) SORT_AREA_SIZE:默认的用来排序的SORT_AREA_SIZE大小是 K 通常显得有点小 一般可以考虑设置成 M（ ） 这个参数不能设置过大 因为每个连接都要分配同样的排序内存

lishixin/Article/program/Oracle/201311/18879

❷ Oracle数据库系统性能优化策略

一个数据库系统的生命周期可以分成设计 开发和成品三个阶段 在设计阶段进行数据库性能优化的成本最低 收益最大 在成品阶段进行数据库性能优化的成本最高 收益最小 数据库的优化可以通过对网络 硬件 操作系统 数据库参数和应用程序的优化来进行 最常见的优化手段就是对硬件的升级 据统计 对网络辩敬 硬件 操作系统 数据库参数进行优化所获得的性能提升 全部加起来只占数据库系统性能提升的 %左右 其余的 %系统性能提升来自对应用程序的优化 许多优化专家认为 对应用程序的优化可以得到 %的系统性能的提升

一 数据库性能的优化

数据库设计是应用程序设计的基础 其性能直接影响应用程序的性能 数据库性能包括存储空间需求量的大小和查询响应时间的长短两个方面 为了优化数据库性能 需要对数据库中的表进行规范化 规范化的范式可分为第一范式 第二范式 第三范式 BCNF范式 第四范式和第五范式 一般来说 逻辑数据库设计会满足规范化的前 级标准 但由于满足第三范式的表结构容易维护且基本满足实际应用的要求 因此 实际应用中一般都按照第三范式的标准进行规范化 但是 规范化也有缺点 由于将一个表拆分成为多个表 在查询时需要多表连接 降低了查询速度

由于规范化有可能导致查询速度慢的缺点 考虑到一些应用需要较快的响应速度 在设计表时应同时考虑对某些表进行反规范化 反规范化可以采用以下几种方法

分割表

分割表包括水平分割和垂直分割

水平分割是按照行将一个表分割为多个表 这可以提高每个表的查询速度 但查询 更新时要选择不同的表 统计时要汇总多个表 因此应用程序会更复杂

垂直分割是对于一个列很多的表 若某些列的访问频率远远高于其它列 就可以将主键和这些列作为一个表 将主键和其它列作为另外一个表 通过减少列的宽度 增加了绝山每个数据页的行数 一次I/O就可以扫描更多的行 从而提高了访问每一个表的速度 但是由于造成了多表连接 所以应该在同时查询或更新不同分割表中的列的情况比较少的情况下使用

保留冗余列

当两个或多个表在查询中经常需要连接时 可以在其中一个表上增加若干冗余的列 以避免表之间的连接过于频繁 由于对冗余列的更新操作必须对多个表同步进行 所以一般在冗余列的数据不经常变动的情况下使用

增加派生列

派生列是由表中的其它多个列计算所得 增加派生列可以减少统计运算 在数据汇总时可以大大缩短运算时间

二 应用程序性能的优化

应用程序的优化通常可分为两个方面 源代码和SQL语句 由于涉及到对程序逻辑的改变 源代码的优化在时间成本和风险上代价很高 而对数据库系统性能的提升收效有限 因此应用程序的优化应着重在SQL语句的优化 对于海量数据 劣质SQL语句和优质SQL语句之间的速度差别可以达到上百倍 可见对于一个系统不是简单地能实现其功能就行 而是要写出高质量的SQL语句 提高系统的可用性

下面就某些SQL语句的where子句编写中需要注意的问题作详细介绍 在这些where子句中 即使某些列存在索引 但是由于编写了劣质的SQL 系统在运行该SQL语句时也不能使用该索引 而同样使用全表扫描 这就造成了响应速度的极大降低

IS NULL 与 IS NOT NULL

携宏慎不能用null作索引 任何包含null值的列都将不会被包含在索引中 即使索引有多列的情况下 只要这些列中有一列含有null 该列就会从索引中排除 也就是说如果某列存在空值 即使对该列建索引也不会提高性能

任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的

联接列

对于有联接的列 即使最后的联接值为一个静态值 优化器不会使用索引的 例如 假定有一个职工表（employee） 对于一个职工的姓和名分成两列存放（FIRST_NAME和LAST_NAME） 现在要查询一个叫乔治？布什（Gee Bush）的职工 下面是一个采用联接查询的SQL语句

select * from employee where first_name|| ||last_name = Gee Bush

上面这条语句完全可以查询出是否有Gee Bush这个员工 但是这里需要注意 系统优化器对基于last_name创建的索引没有使用

当采用下面这种SQL语句的编写 Oracle系统就可以采用基于last_name创建的索引

Select * From employee where first_name = Gee and last_name = Bush

遇到下面这种情况又如何处理呢？如果一个变量（name）中存放着Gee Bush这个员工的姓名 对于这种情况我们又如何避免全程遍历使用索引呢？可以使用一个函数 将变量name中的姓和名分开就可以了 但是有一点需要注意 这个函数是不能作用在索引列上 下面是SQL查询脚本

select * from employee where first_name = SUBSTR（ &&name INSTR（ &&name ） ）

and last_name = SUBSTR（ &&name INSTR（ &&name ）+ ）

带通配符（%）的like语句

同样以上面的例子来看这种情况 目前的需求是这样的 要求在职工表中查询名字中包含Bush的人 可以采用如下的查询SQL语句

select * from employee where last_name like %Bush%

这里由于通配符（%）在搜寻词首出现 所以Oracle系统不使用last_name的索引 在很多情况下可能无法避免这种情况 但是一定要心中有底 通配符如此使用会降低查询速度 然而当通配符出现在字符串其他位置时 优化器就能利用索引 例如 在下面的查询中索引得到了使用

select * from employee where last_name like c%

Order by语句

Order by语句决定了Oracle如何将返回的查询结果排序 Order by语句对要排序的列没有什么特别的限制 也可以将函数加入列中（象联接或者附加等） 任何在Order by语句的非索引项或者有计算表达式都将降低查询速度

仔细检查order by语句以找出非索引项或者表达式 它们会降低性能 解决这个问题的办法就是重写order by语句以使用索引 也可以为所使用的列建立另外一个索引 同时应绝对避免在order by子句中使用表达式

NOT

我们在查询时经常在where子句使用一些逻辑表达式 如大于 小于 等于以及不等于等等 也可以使用and（与） or（或）以及not（非） NOT可用来对任何逻辑运算符号取反 下面是一个NOT子句的例子

…… where not （status = VALID ）

如果要使用NOT 则应在取反的短语前面加上括号 并在短语前面加上NOT运算符 NOT运算符包含在另外一个逻辑运算符中 这就是不等于（<>）运算符 换句话说 即使不在查询where子句中显式地加入NOT词 NOT仍在运算符中 见下例

…… where status <> INVALID

再看下面这个例子

select * from employee wheresalary<>

对这个查询 可以改写为不使用NOT的语句

select * from employee wheresalary< or salary>

虽然这两种查询的结果一样 但是第二种查询方案会比第一种查询方案更快些 第二种查询允许Oracle对salary列使用索引 而第一种查询则不能使用索引

IN和EXISTS

有时候会将一列和一系列值相比较 最简单的办法就是在where子句中使用子查询 在where子句中可以使用两种格式的子查询

第一种格式是使用IN操作符 …… where column in（select * from …… where ……）

第二种格式是使用EXIST操作符 …… where exists （select X from ……where ……）

绝大多数人会使用第一种格式 因为它比较容易编写 而实际上第二种格式要远比第一种格式的效率高 在Oracle中可以将几乎所有的IN操作符子查询改写为使用EXISTS的子查询

第二种格式中 子查询以 select X 开始 运用EXISTS子句不管子查询从表中抽取什么数据它只查看where子句 这样优化器就不必遍历整个表而仅根据索引就可完成工作（这里假定在where语句中使用的列存在索引） 相对于IN子句来说 EXISTS使用相连子查询 构造起来要比IN子查询困难一些

通过使用EXISTS Oracle系统会首先检查主查询 然后运行子查询直到找到第一个匹配项 这就节省了时间 Oracle系统在执行IN子查询时 首先执行子查询 并将获得的结果列表存放在一个加了索引的临时表中 在执行子查询之前 系统先将主查询挂起 待子查询执行完毕 存放在临时表中以后再执行主查询 这也就是使用EXISTS比使用IN通常查询速度快的原因

同时应尽可能使用NOT EXISTS来代替NOT IN 尽管二者都使用了NOT（不能使用索引而降低速度） 但NOT EXISTS要比NOT IN查询效率更高

lishixin/Article/program/Oracle/201311/17060

❸ 【基于ORACLE数据库的SQL语句优化分析】 数据库查询语句的优化

【摘要】随着数据库应用范围及规模的不断扩大，数据库的性能问题逐渐显现，优化数据库有助于维持系统的稳定性以及运行的高效性。本文主要依据笔者在实际工作中的精坦敏拍英，对SQL语句优化的目的、SQL语句优化技术及原则进行全面分析和阐述。
【关键词】ORACLE数据库；SQL语句；优化
1前言
随着现代化信息技术的迅猛发展，互联网应用的日益普及，数据库技术的影响力越来越大。作为信息系统管理的核心，数据库的主要操作就是查询，数据库的应用效率在很大程度上是由查询速度决定的，特别是对于规模较大的数据库而言，查询速度十分关键。查询速度在SQL语句中占有很大比重，所以，通过对查询语句进行优化有助于促进应用系统性能及效率的进一步提升。
2SQL语句优化分析
2.1SQL语句优化的目的
对于一个数据库而言，在确保设计无误的前提下，要想避免出现性能问题必须确保其拥有合理的SQL语句拿唤结构。最简单的数据库寻找数据路径是对SQL语句进行调整，ORACLE数据库性能提升的主要途径就是对SQL语句进行适当的调整。从本质上讲，SQL语句优化就是确保所使用的语句可以被优化器识别，对索引进行有效利用以便控制表扫描的I/O次数，有效防止出现表搜索。用高性能的SQL语句替代低性能的SQL语句，确定最佳的数据查找路径，尽可能使CPU时间与I/O时间保持平衡是进行优化的主要目的。在对SQL语句进行优化的过程中，以系统需求为依据确定最有可能实现性能提升的语句并进行优化。
2.2SQL语句优化技术及原则
当数据量积累到一定程度之后，对于数据库全表SQL语句进行一次扫描，若查询策略较好，一般只用几秒钟，但如果SQL语句性能较低，就需要用几分钟甚至更多时间。从这点不难看出，SQL语句性能对于查询速度具有极大的影响，所以，对于应用系统而言，不仅能满足功能的实现，还要保证SQL语句的质量。
（1）采取适宜的索引。为达到优化查询的目的，一项重要工作就是确定相适应的索引，并严格依照原则加以使用，与此同时，为有效控制I/O竞争，不可以在同一个磁盘中同时建立索引和用户表空间。
语句1：SELECT CUS_NO, CUS_NAME FROM CUSTOMER WHERE CUS_NO NOT IN
(SELECT CUS_NO FROM SERVICE);
语句2： SELECT CUS_NO, CUS_NAME FROM CUSTOMER WHERE NOT EXISTS
(SELECT * FROM SERVICE WHERE SERVICE.CUS_NO=CUSTOMER.CUS_NO);
上述两个语句可以达到一致的查询结果，对二者进行对比，当执行语句1时，由于ORACLE未利用CUSTOMER 表上CUS_NO索引，所以就会扫描整表，在执行语句2的过让羡程中，ORACLE所扫描的只是CUSTOMER 表子查询中的联合查询，并且使用了CUS_NO索引，因此，在执行效率方面明显优于前者。
（2）避免在SELECT子句中出现“*”。ORACLE在进行解析时，需要按照一定顺序对“*”进行转换，该项转换工作的进行需要对数据库的数据字典进行查询，势必需要花费较多的时间，这样就会导致较低的效率，所以，要避免在SELECT子句中出现“*”。
（3）如果必要可以利用COMMIT提交事务。ORACLE能够自动提交DDL语句，而诸如DML等类型的语句的提交则是通过手动方式或者回滚事务实现的。在编写应用程序的过程中，在操作诸如insert、delete以及update 等较为复杂的语境的时候，利用COMMIT提交事务可以讲会话中持有的锁加以释放，将存在于缓存中的未经修改的数据块进行清除，进而将系统资源予以释放，促进系统性能的进一步提升，因此，如果有必要，可以利用COMMIT对相关事务进行提交。
（4）联合查询连接顺序的确定。如果查询操作涉及到多个表，基础表应当是交叉表，所谓交叉表具体是指被其他表引用的表。连接执行效果在很大程度上受到FROM语句中表的顺序的影响，对于FROM中所包含的表，ORACLE解析器进行处理的顺序是由右至左，SQL语句中所选择的基础表会因优化器的不同而有所区别，在使用CBO的情况下，优化器会对SQL语句中各个表的物理大小以及索引状态进行检查，在此基础上确定一个花费最小的执行路径；在使用RBO的情况下，如果全部的连接条件均有索引与之相对应，那么，FROM子句中位置最后面的表就是基础表。
（5）IN用EXISTS取代。在对数个基础表查询过程中，一般需要进行表的连接。因为利用IN的子查询过程中，ORACLE的扫描对象是全表，因此，出于提高查询效率目的的考虑，应当将IN用EXISTS取代。
（6）在索引列中不使用计算。当通过对函数进行引用在WHERE子句中进行计算的时候，假如索引列只是函数的一部分，优化器就会针对全表进行扫描，而不会使用索引，所以，在索引列中不能使用函数。
3结语
综上所述，随着现代化信息技术的迅猛发展，互联网应用的日益普及，数据库技术的影响力越来越大。在信息量迅速激增的形势下，数据库优化调整成为当前所面临的一大关键性问题，特别是对规模较大的数据库而言，及时进行优化的意义更加倍重大。对于数据库的运行性能而言，最主要的影响因素主要体现在以下几点：数据库系统架构的设计是否合理，资源配置是否科学以及SQL语句编写效率等。笔者从事的是电信企业的运营分析工作，每天都要从数据库取各种数据，可以说是离不开数据库，所以在实践中，我觉得严格遵守SQL语句优化原则及方法，并在实践中及时总结经验教训，可以实现对系统响应时间的有效控制，促进运行效率的提升。
参考文献
[1] 许开宇,胡文骅. 如何提高ORACLE数据库应用程序的性能[J]. 计算机应用与软件. 2002(10)
[2] 郑耀,吴建岚. 基于Oracle数据库的语句优化策略[J]. 信息与电脑(理论版). 2011(07)
[3] 高攀,施蔚然. 基于Oracle数据库的SQL语句优化[J]. 电脑编程技巧与维护. 2010(22)
[4] 钟小权,叶猛. Oracle数据库的SQL语句优化[J]. 计算机与现代化. 2011(03)
作者简介：
王勇军，男，（1981.1-），吉林通化人，就职于中国联合网络通信有限公司长春市分公司，通信工程师，本科，研究方向：SQL使用
（作者单位：中国联合网络通信有限公司长春市分公司）

❹ oracle 数据库很慢，应该如何优化

1、是这样的。
2、这个说不好，我没这么做过。你手边应该有oralce的全套电子文档吧。关键是你要找对系统表或者视图。我记得索引的系统视图不是这个。
3、这些与你要做的有关系吗？别像没头苍蝇一样瞎撞了。
4、不用删表，如果你连基本的语句命令都不懂，那只能看书了。
5、慢的原因有好多，逐步排除吧，等找到真正原因再说。急没用的。
6、默认情况下，是会建到用户的默认表空间的。
7、这个看你的维护需要。最起码先弄明白你的库是怎么回事再说吧。就从这些问题看，你根本就是门外汉，连库是怎么回事都没弄明白。