锐客网

  1. 首页 > 睿知 > it技术 > >

oracle语句如何优化 优化oracle数据库

IT技术语句

oracle数据库优化有哪些方法？你最好买一本专门讲ORACLE性能优化的书，好好看看\x0d\x0a1、调整数据库服务器的性能\x0d\x0aOracle数据库服务器是整个系统的核心，它的性能高低直接影响整个系统的性能，为了调整Oracle数据库服务器的性能 ， 主要从以下几个方面考虑： \x0d\x0a1.1、调整操作系统以适合Oracle数据库服务器运行\x0d\x0aOracle数据库服务器很大程度上依赖于运行服务器的操作系统，如果操作系统不能提供最好性能，那么无论如何调整 ， Oracle数据库服务器也无法发挥其应有的性能 。\x0d\x0a1.1.1、为Oracle数据库服务器规划系统资源 \x0d\x0a据已有计算机可用资源, 规划分配给Oracle服务器资源原则是：尽可能使Oracle服务器使用资源最大化,特别在Client/Server中尽量让服务器上所有资源都来运行Oracle服务 。\x0d\x0a1.1.2、调整计算机系统中的内存配置 \x0d\x0a多数操作系统都用虚存来模拟计算机上更大的内存，它实际上是硬盘上的一定的磁盘空间 。当实际的内存空间不能满足应用软件的要求时，操作系统就将用这部分的磁盘空间对内存中的信息进行页面替换，这将引起大量的磁盘I/O操作，使整个服务器的性能下降 。为了避免过多地使用虚存，应加大计算机的内存 。\x0d\x0a1.1.3、为Oracle数据库服务器设置操作系统进程优先级 \x0d\x0a不要在操作系统中调整Oracle进程的优先级，因为在Oracle数据库系统中，所有的后台和前台数据库服务器进程执行的是同等重要的工作，需要同等的优先级 。所以在安装时，让所有的数据库服务器进程都使用缺省的优先级运行 。\x0d\x0a1.2、调整内存分配\x0d\x0aOracle数据库服务器保留3个基本的内存高速缓存 ， 分别对应3种不同类型的数据：库高速缓存 ， 字典高速缓存和缓冲区高速缓存 。库高速缓存和字典高速缓存一起构成共享池 ， 共享池再加上缓冲区高速缓存便构成了系统全程区(SGA) 。SGA是对数据库数据进行快速访问的一个系统全程区 ， 若SGA本身需要频繁地进行释放、分配 ， 则不能达到快速访问数据的目的，因此应把SGA放在主存中，不要放在虚拟内存中 。内存的调整主要是指调整组成SGA的内存结构的大小来提高系统性能，由于Oracle数据库服务器的内存结构需求与应用密切相关，所以内存结构的调整应在磁盘I/O调整之前进行 。\x0d\x0a1.2.1、库缓冲区的调整 \x0d\x0a库缓冲区中包含私用和共享SQL和PL/SQL区，通过比较库缓冲区的命中率决定它的大小 。要调整库缓冲区，必须首先了解该库缓冲区的活动情况，库缓冲区的活动统计信息保留在动态性能表v$librarycache数据字典中，可通过查询该表来了解其活动情况，以决定如何调整 。\x0d\x0a \x0d\x0aSelect sum(pins),sum(reloads) from v$librarycache;\x0d\x0a \x0d\x0aPins列给出SQL语句，PL/SQL块及被访问对象定义的总次数；Reloads列给出SQL 和PL/SQL块的隐式分析或对象定义重装载时在库程序缓冲区中发生的错误 。如果sum(pins)/sum(reloads) ≈0，则库缓冲区的命中率合适；若sum(pins)/sum(reloads)1, 则需调整初始化参数 shared_pool_size来重新调整分配给共享池的内存量 。\x0d\x0a1.2.2、数据字典缓冲区的调整 \x0d\x0a数据字典缓冲区包含了有关数据库的结构、用户、实体信息 。数据字典的命中率，对系统性能影响极大 。数据字典缓冲区的使用情况记录在动态性能表v$librarycache中，可通过查询该表来了解其活动情况，以决定如何调整 。\x0d\x0a \x0d\x0aSelect sum(gets),sum(getmisses) from v$rowcache;\x0d\x0a \x0d\x0aGets列是对相应项请求次数的统计；Getmisses 列是引起缓冲区出错的数据的请求次数 。对于频繁访问的数据字典缓冲区，sum(getmisses)/sum(gets)10%～15% 。若大于此百分数，则应考虑增加数据字典缓冲区的容量，即需调整初始化参数shared_pool_size来重新调整分配给共享池的内存量 。\x0d\x0a1.2.3、缓冲区高速缓存的调整 \x0d\x0a用户进程所存取的所有数据都是经过缓冲区高速缓存来存取，所以该部分的命中率，对性能至关重要 。缓冲区高速缓存的使用情况记录在动态性能表v$sysstat中，可通过查询该表来了解其活动情况，以决定如何调整 。\x0d\x0a \x0d\x0aSelect name,value from v$sysstat where name in ('dbblock gets','consistent gets','physical reads');\x0d\x0a \x0d\x0adbblock gets和consistent gets的值是请求数据缓冲区中读的总次数 。physical reads的值是请求数据时引起从盘中读文件的次数 。从缓冲区高速缓存中读的可能性的高低称为缓冲区的命中率，计算公式： \x0d\x0a \x0d\x0aHit Ratio=1-(physical reds/(dbblock gets consistent gets))\x0d\x0a \x0d\x0a如果Hit Ratio60%～70%，则应增大db_block_buffers的参数值 。db_block_buffers可以调整分配给缓冲区高速缓存的内存量，即db_block_buffers可设置分配缓冲区高速缓存的数据块的个数 。缓冲区高速缓存的总字节数=db_block_buffers的值*db_block_size的值 。db_block_size 的值表示数据块大小的字节数，可查询 v$parameter 表： \x0d\x0a \x0d\x0aselect name,value from v$parameter where name='db_block_size';\x0d\x0a \x0d\x0a在修改了上述数据库的初始化参数以后，必须先关闭数据库，在重新启动数据库后才能使新的设置起作用 。
数据库性能优化有哪些措施？1、调整数据结构oracle语句如何优化的设计
这一部分在开发信息系统之前完成oracle语句如何优化，程序员需要考虑是否使用ORACLE数据库的分区功能，对于经常访问的数据库表是否需要建立索引等 。
2、调整应用程序结构设计
这一部分也是在开发信息系统之前完成，程序员在这一步需要考虑应用程序使用什么样的体系结构，是使用传统的Client/Server两层体系结构，还是使用Browser/Web/Database的三层体系结构 。不同的应用程序体系结构要求的数据库资源是不同的 。
3、调整数据库SQL语句
应用程序的执行最终将归结为数据库中的SQL语句执行，因此SQL语句的执行效率最终决定了ORACLE数据库的性能 。ORACLE公司推荐使用ORACLE语句优化器(OracleOptimizer)和行锁管理器(row-levelmanager)来调整优化SQL语句 。
4、调整服务器内存分配
内存分配是在信息系统运行过程中优化配置的 ， 数据库管理员可以根据数据库运行状况调整数据库系统全局区(SGA区)的数据缓冲区、日志缓冲区和共享池的大小;还可以调整程序全局区(PGA区)的大小 。需要注意的是 ， SGA区不是越大越好，SGA区过大会占用操作系统使用的内存而引起虚拟内存的页面交换，这样反而会降低系统 。
5、调整硬盘I/O
这一步是在信息系统开发之前完成的 。数据库管理员可以将组成同一个表空间的数据文件放在不同的硬盘上，做到硬盘之间I/O负载均衡 。
6、调整操作系统参数
例如oracle语句如何优化：运行在UNIX操作系统上的ORACLE数据库，可以调整UNIX数据缓冲池的大小 ， 每个进程所能使用的内存大小等参数 。
实际上，上述数据库优化措施之间是相互联系的 。ORACLE数据库性能恶化表现基本上都是用户响应时间比较长，需要用户长时间的等待 。但性能恶化的原因却是多种多样的 ， 有时是多个因素共同造成了性能恶化的结果，这就需要数据库管理员有比较全面的计算机知识，能够敏感地察觉到影响数据库性能的主要原因所在 。另外，良好的数据库管理工具对于优化数据库性能也是很重要的 。
一、ORACLE数据库性能优化工具
【oracle语句如何优化 优化oracle数据库】常用的数据库性能优化工具有：
ORACLE数据库在线数据字典，ORACLE在线数据字典能够反映出ORACLE动态运行情况，对于调整数据库性能是很有帮助的 。
操作系统工具 ， 例如UNIX操作系统的vmstat，iostat等命令可以查看到系统系统级内存和硬盘I/O的使用情况，这些工具对于管理员弄清出系统瓶颈出现在什么地方有时候很有用 。
SQL语言跟踪工具(SQLTRACEFACILITY) ， SQL语言跟踪工具可以记录SQL语句的执行情况，管理员可以使用虚拟表来调整实例，使用SQL语句跟踪文件调整应用程序性能 。SQL语言跟踪工具将结果输出成一个操作系统的文件，管理员可以使用TKPROF工具查看这些文件 。
ORACLEEnterpriseManager(OEM)，这是一个图形的用户管理界面，用户可以使用它方便地进行数据库管理而不必记住复杂的ORACLE数据库管理的命令 。
EXPLAINPLAN——SQL语言优化命令，使用这个命令可以帮助程序员写出高效的SQL语言 。
二、ORACLE数据库的系统性能评估
信息系统的类型不同，需要关注的数据库参数也是不同的 。数据库管理员需要根据自己的信息系统的类型着重考虑不同的数据库参数 。
1、在线事务处理信息系统(OLTP) ， 这种类型的信息系统一般需要有大量的Insert、Update操作，典型的系统包括民航机票发售系统、银行储蓄系统等 。OLTP系统需要保证数据库的并发性、可靠性和最终用户的速度，这类系统使用的ORACLE数据库需要主要考虑下述参数：
数据库回滚段是否足够?
是否需要建立ORACLE数据库索引、聚集、散列?
系统全局区(SGA)大小是否足够?
SQL语句是否高效?
2、数据仓库系统(DataWarehousing) ， 这种信息系统的主要任务是从ORACLE的海量数据中进行查询，得到数据之间的某些规律 。数据库管理员需要为这种类型的ORACLE数据库着重考虑下述参数：
是否采用B*-索引或者bitmap索引?
是否采用并行SQL查询以提高查询效率?
是否采用PL/SQL函数编写存储过程?
有必要的话，需要建立并行数据库提高数据库的查询效率
三、SQL语句的调整原则
SQL语言是一种灵活的语言，相同的功能可以使用不同的语句来实现，但是语句的执行效率是很不相同的 。程序员可以使用EXPLAINPLAN语句来比较各种实现方案 ， 并选出最优的实现方案 。总得来讲，程序员写SQL语句需要满足考虑如下规则：
1、尽量使用索引 。试比较下面两条SQL语句：
语句A：SELECTdname,deptnoFROMdeptWHEREdeptnoNOTIN
(SELECTdeptnoFROMemp);
语句B：SELECTdname,deptnoFROMdeptWHERENOTEXISTS
(SELECTdeptnoFROMempWHEREdept.deptno=emp.deptno);
这两条查询语句实现的结果是相同的，但是执行语句A的时候，ORACLE会对整个emp表进行扫描，没有使用建立在emp表上的deptno索引，执行语句B的时候 ， 由于在子查询中使用了联合查询，ORACLE只是对emp表进行的部分数据扫描，并利用了deptno列的索引，所以语句B的效率要比语句A的效率高一些 。
2、选择联合查询的联合次序 。考虑下面的例子：
SELECTstuffFROMtabaa,tabbb,tabcc
WHEREa.acolbetween:alowand:ahigh
ANDb.bcolbetween:blowand:bhigh
ANDc.ccolbetween:clowand:chigh
ANDa.key1=b.key1
AMDa.key2=c.key2;
这个SQL例子中，程序员首先需要选择要查询的主表，因为主表要进行整个表数据的扫描，所以主表应该数据量最小，所以例子中表A的acol列的范围应该比表B和表C相应列的范围小 。
3、在子查询中慎重使用IN或者NOTIN语句 ， 使用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数据字典中“CPUusedbythissession”统计项得知ORACLE数据库使用的CPU时间，查看“OSUserlevelCPUtime”统计项得知操作系统用户态下的CPU时间，查看“OSSystemcallCPUtime”统计项得知操作系统系统态下的CPU时间，操作系统总的CPU时间就是用户态和系统态时间之和，如果ORACLE数据库使用的CPU时间占操作系统总的CPU时间90%以上，说明服务器CPU基本上被ORACLE数据库使用着，这是合理 ， 反之，说明服务器CPU被其它程序占用过多，ORACLE数据库无法得到更多的CPU时间 。
数据库管理员还可以通过查看v$sesstat数据字典来获得当前连接ORACLE数据库各个会话占用的CPU时间，从而得知什么会话耗用服务器CPU比较多 。
出现CPU资源不足的情况是很多的：SQL语句的重解析、低效率的SQL语句、锁冲突都会引起CPU资源不足 。
1、数据库管理员可以执行下述语句来查看SQL语句的解析情况：
SELECT*FROMV$SYSSTATWHERENAMEIN
('parsetimecpu','parsetimeelapsed','parsecount(hard)');
这里parsetimecpu是系统服务时间 ， parsetimeelapsed是响应时间，用户等待时间，waitetime=parsetimeelapsed_parsetimecpu
由此可以得到用户SQL语句平均解析等待时间=waitetime/parsecount 。这个平均等待时间应该接近于0，如果平均解析等待时间过长，数据库管理员可以通过下述语句
SELECTSQL_TEXT,PARSE_CALLS,EXECUTIONSFROMV$SQLAREA
ORDERBYPARSE_CALLS;
来发现是什么SQL语句解析效率比较低 。程序员可以优化这些语句，或者增加ORACLE参数SESSION_CACHED_CURSORS的值 。
2、数据库管理员还可以通过下述语句：
SELECTBUFFER_GETS,EXECUTIONS,SQL_TEXTFROMV$SQLAREA;
查看低效率的SQL语句 ， 优化这些语句也有助于提高CPU的利用率 。
3、数据库管理员可以通过v$system_event数据字典中的“latchfree”统计项查看ORACLE数据库的冲突情况，如果没有冲突的话，latchfree查询出来没有结果 。如果冲突太大的话，数据库管理员可以降低spin_count参数值，来消除高的CPU使用率 。
五、内存参数的调整
内存参数的调整主要是指ORACLE数据库的系统全局区(SGA)的调整 。SGA主要由三部分构成：共享池、数据缓冲区、日志缓冲区 。
1、共享池由两部分构成：共享SQL区和数据字典缓冲区，共享SQL区是存放用户SQL命令的区域，数据字典缓冲区存放数据库运行的动态信息 。数据库管理员通过执行下述语句：
select(sum(pins-reloads))/sum(pins)"LibCache"fromv$librarycache;
来查看共享SQL区的使用率 。这个使用率应该在90%以上，否则需要增加共享池的大小 。数据库管理员还可以执行下述语句：
select(sum(gets-getmisses-usage-fixed))/sum(gets)"RowCache"fromv$rowcache;
查看数据字典缓冲区的使用率，这个使用率也应该在90%以上，否则需要增加共享池的大小 。
2、数据缓冲区 。数据库管理员可以通过下述语句：
SELECTname,valueFROMv$sysstatWHEREnameIN('dbblockgets','consistentgets','physicalreads');
来查看数据库数据缓冲区的使用情况 。查询出来的结果可以计算出来数据缓冲区的使用命中率=1-(physicalreads/(dbblockgets consistentgets)) 。
这个命中率应该在90%以上，否则需要增加数据缓冲区的大小 。
3、日志缓冲区 。数据库管理员可以通过执行下述语句：
selectname,valuefromv$sysstatwherenamein('redoentries','redologspacerequests');
查看日志缓冲区的使用情况 。查询出的结果可以计算出日志缓冲区的申请失败率：
申请失败率=requests/entries，申请失败率应该接近于0，否则说明日志缓冲区开设太小，需要增加ORACLE数据库的日志缓冲区 。
昆明北大青鸟java培训班转载自网络如有侵权请联系我们感谢您的关注谢谢支持
Oracle游标sql语句代码块的优化游标操作的优化：
-- 有一个表格oracle语句如何优化，存储的是用户的名字oracle语句如何优化，将emp表中所有的用户名转换成小写，再写入这个表格
create table ename_emp(
ename varchar2(50)
);
-- 下面这个游标的操作 ， 需要 43s时间才能完成
declare
begin
for i in (select ename from emp_liebiao) loop
insert into ename_emp values(lower(i.ename));
commit;
end loop;
end;
-- 因为游标是以行为单位进行数据的操作的，所有游标的效率是比较慢的，而且游标需要消耗的内存也是比较多的，我们需要将游标以行进行操作的方式 ， 修改成一次性操作所有数据的批量的方式 。
批量操作在游标里面 叫做bulk collect
第一步，创建一个表类型
type表类型的名字istable of表名.列名%type;
变量名 表类型的名字;
第三步，创建一个游标，读取某个查询的结果
cursor游标名字isselect查询语句;
第四步，打开游标
open游标名字;
第五步，捕获游标的数据，将内容给到表类型的变量进行保存
fetch游标名字bulk collect into变量名字;
第六步，使用 forall 语句，对数据进行批量的操作
forall i in变量.first ..变量.last DML语句操作;
第七步，关闭游标
close游标名字;
declare
-- 创建表类型
type biao is table of emp_liebiao.ename%type;
b biao;
-- 创建游标
cursor m is select ename from emp_liebiao;
begin
-- 打开游标
open m;
-- 将游标的内容批量的给到变量
fetch m bulk collect into b;
-- 使用forall批量修改数据
forall i in b.first .. b.last insert into ename_emp values(lower(b(i)));
commit;
-- 关闭游标
close m;
end;
练习：批量修改用户的编号，让编号 工资等级 部门 ， 形成一个新的编号，存入下面的表格中，使用bulk collect来实现 。
2000以下是 C ， 2000-3000是 B ， 3000以上是 A，
例如 SMITH，新编号应该是7369_C_20
create table empno_emp(
empno varchar2(50)
);
declare
type biao is table of emp_liebiao%rowtype;
b biao;
cursor m is select * from emp_liebiao;
begin
open m;
fetch m bulk collect into b;
forall i in b.first..b.last
insert into empno_emp values(
b(i).empno||'_'||decode(sign(b(i).sal-2000) sign(b(i).sal-3000),-2,'C',2,'A','B')||'_'||b(i).deptno
);
commit;
close m;
end;
怎么使用 基于oracle的sql优化（1）选择最有效率的表名顺序(只在基于规则的优化器中有效)：
ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表 driving table)将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表 。如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.
（2）WHERE子句中的连接顺序．：
ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.
（3）SELECT子句中避免使用 ‘ * ‘：
ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间
（4）减少访问数据库的次数：
ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等；
（5）在SQL*Plus , SQL*Forms和Pro*C中重新设置ARRAYSIZE参数, 可以增加每次数据库访问的检索数据量 ,建议值为200
（6）使用DECODE函数来减少处理时间：
使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.
（7）整合简单,无关联的数据库访问：
如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系)
（8）删除重复记录：
最高效的删除重复记录方法 ( 因为使用了ROWID)例子：
DELETEFROMEMP EWHEREE.ROWID(SELECT MIN(X.ROWID)
FROMEMP XWHEREX.EMP_NO = E.EMP_NO);
（9）用TRUNCATE替代DELETE：
当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短. (译者按: TRUNCATE只在删除全表适用,TRUNCATE是DDL不是DML)
（10） 尽量多使用COMMIT：
只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:
COMMIT所释放的资源:
a. 回滚段上用于恢复数据的信息.
b. 被程序语句获得的锁
c. redo log buffer 中的空间
d. ORACLE为管理上述3种资源中的内部花费
（11） 用Where子句替换HAVING子句：
避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销. (非oracle中)on、where、having这三个都可以加条件的子句中，on是最先执行 ， where次之，having最后，因为on是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的 ， where也应该比having快点的，因为它过滤数据后才进行sum，在两个表联接时才用on的 ， 所以在一个表的时候 ， 就剩下where跟having比较了 。在这单表查询统计的情况下，如果要过滤的条件没有涉及到要计算字段，那它们的结果是一样的，只是where可以使用rushmore技术，而having就不能 ， 在速度上后者要慢如果要涉及到计算的字段，就表示在没计算之前，这个字段的值是不确定的，根据上篇写的工作流程 ， where的作用时间是在计算之前就完成的，而having就是在计算后才起作用的，所以在这种情况下，两者的结果会不同 。在多表联接查询时 ， on比where更早起作用 。系统首先根据各个表之间的联接条件，把多个表合成一个临时表后，再由where进行过滤，然后再计算，计算完后再由having进行过滤 。由此可见，要想过滤条件起到正确的作用，首先要明白这个条件应该在什么时候起作用 ， 然后再决定放在那里
（12） 减少对表的查询：
在含有子查询的SQL语句中,要特别注意减少对表的查询.例子：
SELECTTAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT
TAB_NAME,DB_VER FROMTAB_COLUMNSWHEREVERSION = 604)
（13） 通过内部函数提高SQL效率.：
复杂的SQL往往牺牲了执行效率. 能够掌握上面的运用函数解决问题的方法在实际工作中是非常有意义的
（14） 使用表的别名(Alias)：
当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.
（15） 用EXISTS替代IN、用NOT EXISTS替代NOT IN：
在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率. 在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.
例子：
（高效）SELECT * FROMEMP (基础表)WHEREEMPNO0ANDEXISTS (SELECT ‘X'FROM DEPTWHEREDEPT.DEPTNO = EMP.DEPTNOANDLOC = ‘MELB')
(低效)SELECT* FROMEMP (基础表)WHEREEMPNO0ANDDEPTNO IN(SELECT DEPTNOFROMDEPTWHERELOC = ‘MELB')
（16） 识别'低效执行'的SQL语句：
虽然目前各种关于SQL优化的图形化工具层出不穷,但是写出自己的SQL工具来解决问题始终是一个最好的方法：
SELECTEXECUTIONS , DISK_READS, BUFFER_GETS,
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,
ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
SQL_TEXT
FROMV$SQLAREA
WHEREEXECUTIONS0
ANDBUFFER_GETS0
AND(BUFFER_GETS-DISK_READS)/BUFFER_GETS0.8
ORDER BY4 DESC;
（17） 用索引提高效率：
索引是表的一个概念部分,用来提高检索数据的效率，ORACLE使用了一个复杂的自平衡B-tree结构. 通常,通过索引查询数据比全表扫描要快. 当ORACLE找出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引. 同样在联结多个表时使用索引也可以提高效率. 另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证. 。那些LONG或LONG RAW数据类型, 你可以索引几乎所有的列. 通常, 在大型表中使用索引特别有效. 当然,你也会发现, 在扫描小表时,使用索引同样能提高效率. 虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢. 。定期的重构索引是有必要的.：
ALTERINDEX INDEXNAME REBUILD TABLESPACENAME
（18） 用EXISTS替换DISTINCT：
当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXIST替换, EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果. 例子：
(低效):
SELECTDISTINCTDEPT_NO,DEPT_NAMEFROMDEPT D , EMP E
WHERED.DEPT_NO = E.DEPT_NO
(高效):
SELECTDEPT_NO,DEPT_NAMEFROMDEPT DWHEREEXISTS ( SELECT ‘X'
FROMEMP EWHERE E.DEPT_NO = D.DEPT_NO);
（19） sql语句用大写的；因为oracle总是先解析sql语句 ， 把小写的字母转换成大写的再执行
（20） 在java代码中尽量少用连接符“＋”连接字符串！
（21） 避免在索引列上使用NOT 通常，
我们要避免在索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的影响. 当ORACLE”遇到”NOT,他就会停止使用索引转而执行全表扫描.
（22） 避免在索引列上使用计算．
WHERE子句中 ， 如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描．
举例:
低效：
SELECT … FROMDEPTWHERE SAL * 1225000;
高效:
SELECT … FROM DEPT WHERE SAL25000/12;
（23） 用=替代
高效:
SELECT * FROMEMPWHEREDEPTNO =4
低效:
SELECT * FROM EMP WHERE DEPTNO 3
两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录.
（24） 用UNION替换OR (适用于索引列)
通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低. 在下面的例子中, LOC_ID 和REGION上都建有索引.
高效:
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE LOC_ID = 10
UNION
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE REGION = “MELBOURNE”
低效:
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE LOC_ID = 10 OR REGION = “MELBOURNE”
如果你坚持要用OR, 那就需要返回记录最少的索引列写在最前面.
（25） 用IN来替换OR
这是一条简单易记的规则 ， 但是实际的执行效果还须检验，在ORACLE8i下，两者的执行路径似乎是相同的．
低效:
SELECT…. FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30
高效
SELECT… FROM LOCATION WHERE LOC_ININ (10,20,30);
（26） 避免在索引列上使用IS NULL和IS NOT NULL
避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引．对于单列索引，如果列包含空值，索引中将不存在此记录. 对于复合索引，如果每个列都为空，索引中同样不存在此记录.　如果至少有一个列不为空，则记录存在于索引中．举例: 如果唯一性索引建立在表的A列和B列上, 并且表中存在一条记录的A,B值为(123,null) , ORACLE将不接受下一条具有相同A,B值（123,null）的记录(插入). 然而如果所有的索引列都为空，ORACLE将认为整个键值为空而空不等于空. 因此你可以插入1000 条具有相同键值的记录,当然它们都是空! 因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引.
低效: (索引失效)
SELECT … FROMDEPARTMENTWHEREDEPT_CODE IS NOT NULL;
高效: (索引有效)
SELECT … FROMDEPARTMENTWHEREDEPT_CODE =0;
（27） 总是使用索引的第一个列：
如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引. 这也是一条简单而重要的规则 ， 当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引
（28） 用UNION-ALL 替换UNION ( 如果有可能的话)：
当SQL语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并, 然后在输出最终结果前进行排序. 如果用UNION ALL替代UNION, 这样排序就不是必要了. 效率就会因此得到提高. 需要注意的是，UNION ALL 将重复输出两个结果集合中相同记录. 因此各位还是要从业务需求分析使用UNION ALL的可行性. UNION 将对结果集合排序,这个操作会使用到SORT_AREA_SIZE这块内存. 对于这块内存的优化也是相当重要的. 下面的SQL可以用来查询排序的消耗量
低效：
SELECTACCT_NUM, BALANCE_AMT
FROMDEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'
UNION
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'
高效:
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'
UNION ALL
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'
（29） 用WHERE替代ORDER BY：
ORDER BY 子句只在两种严格的条件下使用索引.
ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序.
ORDER BY中所有的列必须定义为非空.
WHERE子句使用的索引和ORDER BY子句中所使用的索引不能并列.
例如:
表DEPT包含以下列:
DEPT_CODE PK NOT NULL
DEPT_DESC NOT NULL
DEPT_TYPE NULL
低效: (索引不被使用)
SELECT DEPT_CODE FROMDEPTORDER BYDEPT_TYPE
高效: (使用索引)
SELECT DEPT_CODEFROMDEPTWHEREDEPT_TYPE0
（30） 避免改变索引列的类型.:
当比较不同数据类型的数据时, ORACLE自动对列进行简单的类型转换.
假设 EMPNO是一个数值类型的索引列.
SELECT …FROM EMPWHEREEMPNO = ‘123'
实际上,经过ORACLE类型转换, 语句转化为:
SELECT …FROM EMPWHEREEMPNO = TO_NUMBER(‘123')
幸运的是,类型转换没有发生在索引列上,索引的用途没有被改变.
现在,假设EMP_TYPE是一个字符类型的索引列.
SELECT …FROM EMPWHERE EMP_TYPE = 123
这个语句被ORACLE转换为:
SELECT …FROM EMPWHERETO_NUMBER(EMP_TYPE)=123
因为内部发生的类型转换, 这个索引将不会被用到! 为了避免ORACLE对你的SQL进行隐式的类型转换, 最好把类型转换用显式表现出来. 注意当字符和数值比较时, ORACLE会优先转换数值类型到字符类型
（31） 需要当心的WHERE子句:
某些SELECT 语句中的WHERE子句不使用索引. 这里有一些例子.
在下面的例子里, (1)‘!=' 将不使用索引. 记住, 索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中. (2) ‘||'是字符连接函数. 就象其他函数那样, 停用了索引. (3) ‘ '是数学函数. 就象其他数学函数那样, 停用了索引. (4)相同的索引列不能互相比较,这将会启用全表扫描.
（32） a. 如果检索数据量超过30%的表中记录数.使用索引将没有显著的效率提高.
b. 在特定情况下, 使用索引也许会比全表扫描慢, 但这是同一个数量级上的区别. 而通常情况下,使用索引比全表扫描要块几倍乃至几千倍!
（33） 避免使用耗费资源的操作:
带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎
执行耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序. 通常, 带有UNION, MINUS , INTERSECT的SQL语句都可以用其他方式重写. 如果你的数据库的SORT_AREA_SIZE调配得好, 使用UNION , MINUS, INTERSECT也是可以考虑的, 毕竟它们的可读性很强
（34） 优化GROUP BY:
提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉.下面两个查询返回相同结果但第二个明显就快了许多.
低效:
SELECT JOB , AVG(SAL)
FROM EMP
GROUP JOB
HAVING JOB = ‘PRESIDENT'
OR JOB = ‘MANAGER'
高效:
SELECT JOB , AVG(SAL)
FROM EMP
WHERE JOB = ‘PRESIDENT'
OR JOB = ‘MANAGER'
GROUP JOB
九大Oracle性能优化基本方法详解 Oracle性能优化基本方法包括一下几个步骤 包括
）设立合理的Oracle性能优化目标
）测量并记录当前的Oracle性能
）确定当前Oracle性能瓶颈（Oracle等待什么 哪些SQL语句是该等待事件的成分）
）把等待事件记入跟踪文件
）确定当前的OS瓶颈
）优化所需的成分（应用程序 数据库 I/O 争用 OS等）
）跟踪并实施更改控制过程
）测量并记录当前性能
）重复步骤 到 直到满足优化目标
下面来一一详述
设立合理的Oracle性能优化目标
重点 关于设立目标的最重要的一点是它们必须是可量化和可达到的
方法 目标必须是当前性能和所需性能的的陈述形式的语句
测量并记录当前Oracle性能重点
）需要在峰值活动时间获得当前系统性能快照
）关键是要在出现性能问题的时间段内采集信息
）必须在合理的时间段上采集 一般在峰值期间照几个为期 分钟的快照
确定当前Oracle性能瓶颈重点 从Oracle 等待接口v$system_event v$session_event和v$session_wait中获得等待事件 进而找出影响性能的对象和sql语句 方法如下
)首先 利用v$system_event视图执行下面的查询查看数据库中某些常见的等待事件
select * from v$system_event
where event in ( buffer busy waits
db file sequential read
db file scattered read
enqueue
free buffer waits
latch free
log file parallel write
log file sync );
)接着 利用下面对v$session_event和v$session视图进行的查询 研究具有对上面显示的内容有贡献的等待事件的会话
select se sid s username se event se total_waits se time_waited se average_wait
from v$session s v$session_event se
where s sid = se sid
and se event not like SQL*Net%
and s status = ACTIVE
and s username is not null;
)使用下面查询找到与所连接的会话有关的当前等待事件 这些信息是动态的 为了查看一个会话的等待最多的事件是什么 需要多次执行此查询
select sw sid s username sw event sw wait_time sw state sw seconds_in_wait SEC_IN_WAIT
from v$session s v$session_wait sw
where s sid = sw sid
and sw event not like SQL*Net%
and s username is not null
order by sw wait_time desc;
)查询会话等待事件的详细信息
select sid event p text p p text p p text p
from v$session_wait
where sid beeenand
and event not like %SQL%
and event not like %rdbms% ;
)利用P P 的信息 找出等待事件的相关的段
select owner segment_name segment_type tablespace_name
from dba_extents
where file_id = fileid_in
and blockid_in beeen block_id and block_idblocks ;
)获得操作该段的sql语句
select sid getsqltxt(sql_hash_value sql_address)
from v$session
where sid = sid_in;
)getsqltxt函数
)至此已经找到影响性能的对象和sql语句 可以有针对性地优化
把等待事件记入跟踪文件
重点 如果在跟踪系统上的等待事件时 由于某种原因遇到了麻烦 则可以将这些等待事件记入一个跟踪文件 方法如下
)对于当前会话
alter session set timed_statistics=true;
alter session set max_dump_file_size=unlimited;
alter session set events trace name context forever level ;
)执行应用程序 然后在USER_DUMP_DEST指出的目录中找到跟踪文件
)查看文件中以词WAIT开始的所有行
)对于其它的会话
)确定会话的进程ID（SPID） 下面的查询识别出名称以A开始的所有用户的会话进程ID
select S Username P Spid from V$SESSION S V$PROCESS P
where S PADDR = P ADDR and S Username like A% ;
)以sysdba进入sqlplus执行
alter session set timed_statistics=true;
alter session set max_dump_file_size=unlimited;
oradebug setospid
oradebug unlimit
oradebug event trace name context forever level X /* Where X = ( ) */
)跟踪某个时间间隔得会话应用程序
)在USER_DUMP_DEST 的值指出的目录中利用SPID查看跟踪文件
)查看文件中以词WAIT开始的所有行
确定当前OS瓶颈 ）Windows NT上的监控
使用控制面板 〉管理工具 〉性能即可
）UNIX上的监控
使用通用性的工具 包括sar iostat cpustat mpstat netstat top osview等
．Oracle性能优化所需的成分（应用程序 数据库 I/O 争用 OS等）
．跟踪并实施更改控制过程
．测量并记录当前Oracle性能
lishixinzhi/Article/program/Oracle/201311/18709
oracle怎么优化1.去掉不必要的大型表的全表扫描
2.缓存小型表的全表扫描
3.检验优化索引的使用
4.检验优化的连接技术
5.尽可能减少执行计划的Cost
在含有子查询的SQL语句中,要特别注意减少对表的查询.例子oracle语句如何优化：
SELECTEMP_NO FROM EMP WHERE (GROUP,NAME) = ( SELECT
COLUMN1,COLUMN2 FROMTEST WHERETEST_ID = 604)
最高效的删除重复记录方法 ( 因为使用了ROWID)例子：
DELETEFROMEMP EWHEREE.ROWID(SELECT MIN(X.ROWID)
FROMEMP XWHEREX.EMP_NO = E.EMP_NO);
sql语句用大写的；因为oracle总是先解析sql语句oracle语句如何优化，把小写的字母转换成大写的再执行
在java代码中用到preparedStatement的时候尽量少用连接符“＋”连接字符串oracle语句如何优化！
关于oracle语句如何优化和优化oracle数据库的介绍到此就结束了，不知道你从中找到你需要的信息了吗 ？如果你还想了解更多这方面的信息，记得收藏关注本站 。

    推荐阅读


    上一篇：如何推广自家的产品销售，如何推进产品销售网站的推广

    下一篇：如何将代码推送到git服务器？ 怎么推送到git服务器