锐客网

  1. 首页 > 生活百科 > it技术 > >

oracle如何进行优化 优化oracle sql 语句性能的方法

IT技术优化

oracle数据库优化有哪些方法？oracle如何进行优化你最好买一本专门讲ORACLE性能优化的书，好好看看\x0d\x0a1、调整数据库服务器的性能\x0d\x0aOracle数据库服务器是整个系统的核心，它的性能高低直接影响整个系统的性能，为oracle如何进行优化了调整Oracle数据库服务器的性能，主要从以下几个方面考虑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操作，使整个服务器的性能下降 。为oracle如何进行优化了避免过多地使用虚存，应加大计算机的内存 。\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在修改了上述数据库的初始化参数以后，必须先关闭数据库，在重新启动数据库后才能使新的设置起作用 。
九大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设置系统参数进行性能优化 一 SGA
Shared pool tunning
Shared pool的优化应该放在优先考虑 因为一个cache miss在shared pool中发生比在data buffer中发生导致的成本更高 由于dictionary数据一般比library cache中的数据在内存中保存的时间长 所以关键是library cache的优化
Gets （parse）在namespace中查找对象的次数
Pins （execution）在namespace中读取或执行对象的次数
Reloads (reparse)在执行阶段library cache misses的次数 导致sql需要重新解析
） 检查v$librarycache中sql area的gethitratio是否超过 ％ 如果未超过 ％ 应该检查应用代码 提高应用代码的效率
Select gethitratio from v$librarycache where namespace= sql area ;
) v$librarycache中reloads/pins的比率应该小于 ％ 如果大于 ％ 应该增加参数shared_pool_size的值
Select sum(pins) executions sum(reloads) cache misses sum(reloads)/sum(pins) from v$librarycache;
reloads/pins %有两种可能 一种是library cache空间不足 一种是sql中引用的对象不合法
）shared pool reserved size一般是shared pool size的 ％ 不能超过 ％ V$shared_pool_reserved中的request misses＝ 或没有持续增长 或者free_memory大于shared pool reserved size的 % 表明shared pool reserved size过大 可以压缩
）将大的匿名pl/sql代码块转换成小的匿名pl/sql代码块调用存储过程
）从 i开始 可以将execution plan与sql语句一起保存在library cache中 方便进行性能诊断 从v$sql_plan中可以看到execution plans
）保留大的对象在shared pool中 大的对象是造成内存碎片的主要原因 为了腾出空间许多小对象需要移出内存 从而影响了用户的性能 因此需要将一些常用的大的对象保留在shared pool中 下列对象需要保留在shared pool中
a 经常使用的存储过程
b 经常操作的表上的已编译的触发器
c Sequence 因为Sequence移出shared pool后可能产生号码丢失
查找没有保存在library cache中的大对象
Select * from v$db_object_cache where sharable_mem and
type in ( PACKAGE PROCEDURE FUNCTION PACKAGE BODY ) and kept= NO ;
将这些对象保存在library cache中
Execute dbms_shared_pool keep( package_name );
对应脚本 dbmspool sql
)查找是否存在过大的匿名pl/sql代码块 两种解决方案
A．转换成小的匿名块调用存储过程
B．将其保留在shared pool中
查找是否存在过大的匿名pl/sql块
Select sql_text from v$sqlarea where mand_type= and length(sql_text) ;
）Dictionary cache的优化
避免出现Dictionary cache的misses 或者misses的数量保持稳定 只能通过调整shared_pool_size来间接调整dictionary cache的大小
Percent misses应该很低 大部分应该低于 ％ 合计应该低于 ％
Select sum(getmisses)/sum(gets) from v$rowcache;
若超过 ％ 增加shared_pool_size的值
Buffer Cache
）granule大小的设置 db_cache_size以字节为单位定义了default buffer pool的大小
如果SGA M granule= M 否则granule＝ M 即需要调整sga的时候以granule为单位增加大小 并且sga的大小应该是granule的整数倍
) 根据v$db_cache_advice调整buffer cache的大小
SELECT size_for_estimate buffers_for_estimate estd_physical_read_factor estd_physical_reads
FROM v$db_cache_advice WHERE NAME= DEFAULT AND advice_status= ON
AND block_size=(SELECT Value FROM v$parameter WHERE NAME= db_block_size );
estd_physical_read_factor=
) 统计buffer cache的cache hit ratio % 如果低于 ％ 可以用下列方案解决
◆增加buffer cache的值
◆使用多个buffer pool
◆Cache table
◆为 sorting and parallel reads 建独立的buffer cache
SELECT NAME value FROM v$sysstat WHERE NAME IN ( session logical reads
physical reads physical reads direct physical reads direct(lob) );
Cache hit ratio= (physical reads physical reads direct physical reads direct (lob))/session logical reads;Select (phy value dir value lob value)/log value from v$sysstat log v$sysstat phy v$sysstat dir v$sysstat LOB where log name= session logical reads and phy name= physical reads and dir name= physical reads direct and lob name= physical reads direct (lob) ;
影响cache hit ratio的因素 全表扫描 应用设计 大表的随机访问 cache hits的不均衡分布
）表空间使用自动空间管理 消除了自由空间列表的需求 可以减少数据库的竞争
其他SGA对象
）redo log buffer
对应的参数是log_buffer 缺省值与 OS相关 一般是 K 检查v$session_wait中是否存在log buffer wait v$sysstat中是否存在redo buffer allocation retries
A 检查是否存在log buffer wait
Select * from v$session_wait where event= log buffer wait ;
如果出现等待 一是可以增加log buffer的大小 也可以通过将log 文件移到访问速度更快的磁盘来解决
B
Select name value from v$sysstat where name in
( redo buffer allocation retries redo entries )
Redo buffer allocation retries接近 小于redo entries 的 ％ 如果一直在增长 表明进程已经不得不等待redo buffer的空间 如果Redo buffer allocation retries过大 增加log_buffer的值
C 检查日志文件上是否存在磁盘IO竞争现象
Select event total_waits time_waited average_wait from v$system_event
where event like log file switch pletion% ;
如果存在竞争 可以考虑将log文件转移到独立的 更快的存储设备上或增大log文件
D 检查点的设置是否合理
检查alert log文件中 是否存在 checkpoint not plete
Select event total_waits time_waited average_wait from v$system_event
where event like log file switch (check% ;
如果存在等待 调整log_checkpoint_interval log_checkpoint_timeout的设置
E 检查log archiver的工作
Select event total_waits time_waited average_wait from v$system_event
where event like log file switch (arch% ;
如果存在等待 检查保存归档日志的存储设备是否已满 增加日志文件组 调整log_archiver_max_processes
F DB_block_checksum=true 因此增加了性能负担 （为了保证数据的一致性 oracle的写数据的时候加一个checksum在block上 在读数据的时候对checksum进行验证）
）java pool
对于大的应用 java_pool_size应= M 对于一般的java存储过程 缺省的 M已经够用了
）检查是否需要调整DBWn
lishixinzhi/Article/program/Oracle/201311/17744
如何对Oracle中的优化器进行评估优化 Oracle优化器在任何可能oracle如何进行优化的时候都会对表达式进行评估 并且把特定oracle如何进行优化的语法结构转换成等价的结构 这么做的原因是
·要么结果表达式能够比源表达式具有更快的速度
·要么源表达式只是结果表达式的一个等价语义结构
不同的SQL结构有时具有同样的操作（例如 = ANY (subquery) and IN (subquery)） Oracle会把他们映射到一个单一的语义结构
下面将讨论优化器如何评估优化如下的情况和表达式
常量 LIKE 操作符 IN 操作符 ANY和SOME 操作符 ALL 操作符 BEEEN 操作符 NOT 操作符
传递（Transitivity） 确定性（DETERMINISTIC）函数
常量
常量的计算是在语句被优化时一次性完成 而不是在每次执行时 下面是检索月薪大于 的的表达式· sal/ · sal· sal*
如果SQL语句包括第一种情况 优化器会简单地把它转变成第二种
注意 优化器不会简化跨越比较符的表达式 例如第三条语句 鉴于此 应用程序开发者应该尽量写用常量跟字段比较检索的表达式 而不要将字段置于表达式当中
LIKE 操作符
优化器把使用LIKE操作符和一个没有通配符的表达式组成的检索表达式转换为一个“=”操作符表达式
例如 优化器会把表达式ename LIKE SMITH 转换为ename = SMITH 优化器只能转换涉及到可变长数据类型的表达式 前一个例子中 如果ENAME字段的类型是CHAR( ) 那么优化器将不做任何转换
IN 操作符
优化器把使用IN比较符的检索表达式替换为等价的使用“=”和“OR”操作符的检索表达式 例如 优化器会把表达式ename IN ( SMITH KING JONES )替换为:ename = SMITH OR ename = KING OR ename = JONES
ANY和SOME 操作符
优化器将跟随（following）值列表的ANY和SOME检索条件用等价的同等操作符和“OR”组成的表达式替换
例如 优化器将如下所示的第一条语句用第二条语句替换· salANY (:first_sal :second_sal) · sal:first_sal OR sal:second_sal
优化器将跟随子查询的ANY和SOME检索条件转换成由“EXISTS”和一个相应的子查询组成的检索表达式
例如 优化器将如下所示的第一条语句用第二条语句替换· xANY (SELECT sal FROM emp WHERE job = ANALYST ) · EXISTS (SELECT sal FROM emp WHERE job = ANALYST AND xsal)
ALL 操作符
优化器将跟随值列表的ALL操作符用等价的“=”和“AND”组成的表达式替换
例如 salALL (:first_sal :second_sal)表达式会被替换为sal:first_sal AND sal:second_sal
对于跟随子查询的ALL表达式 优化器用ANY和另外一个合适的比较符组成的表达式替换 例如 优化器会把表达式 xALL (SELECT sal FROM emp WHERE deptno = ) 替换为NOT (x = ANY (SELECT sal FROM emp WHERE deptno = ))
接下来优化器会把第二个表达式适用ANY表达式的转换规则转换为下面的表达式NOT EXISTS (SELECT sal FROM emp WHERE deptno = AND x = sal)
BEEEN 操作符
优化器总是用“=”和“=”比较符来等价的代替BEEEN操作符 例如 优化器会把表达式sal BEEEN AND 用sal = AND sal = 来代替
NOT 操作符
优化器总是试图简化检索条件以消除“NOT”逻辑操作符的影响 这将涉及到“NOT”操作符的消除以及代以相应的比较运算符
例如 优化器将下面的第一条语句用第二条语句代替· NOT deptno = (SELECT deptno FROM emp WHERE ename = TAYLOR ) · deptno(SELECT deptno FROM emp WHERE ename = TAYLOR )
通常情况下一个含有NOT操作符的语句有很多不同的写法 优化器的转换原则是使“NOT”操作符后边的子句尽可能的简单 即使可能会使结果表达式包含oracle如何进行优化了更多的“NOT”操作符 例如 优化器将如下所示的第一条语句用第二条语句代替· NOT (salOR m IS NULL) · NOT salAND m IS NOT NULL sal = AND m IS NOT NULL
传递（Transitivity）
如果“WHERE”子句的两个检索条件涉及了一个共同的字段 优化器有时会根据传递原理推断出第三个检索条件 随后可以根据这个推断出的条件对语句 进行优化 推断出的条件可能会激活一个原来的检索条件没有激活的潜在的接口路径（access path） 注意 传递仅仅被用在基于代价（cost based）的优化中
假设有一个这样的包含两个检索条件的“WHERE”子句 WHERE 字段 常量 AND字段 = 字段 在这个例子里 优化器会推断出新的检索条件 字段 常量 在这里 是比较运算符= != ^== 或 =之中的任何一个 常量是指任何一个涉及了操作符 SQL函数 文字 绑定变量（bind variables）或者关联变量（correlation variables）的常量表达式
例如 考虑这样一个包含两个各自使用了字段EMP DEPTNO的检索条件的WHERE子句的查询SELECT * FROM emp dept WHERE emp deptno = AND emp deptno = dept deptno;
使用传递优化 优化器会推断出如下条件 dept deptno = 如果有索引存在于EMP DEPTNO字段上 这个条件会使调用这个索引的接口路径有效 注意 优化器只能对字段关联常量的表达式进行推断 而不是 字段关联字段的表达式 例如 包含这样条件的WHERE子句 字段 字段 AND 字段 = 字段 这种情况不能推断出表达式 字段p_oper 字段
确定性（DETERMINISTIC）函数
在某些情况下 优化器能够使用先前的函数返回结果而不是重新执行用户定义的函数 这仅仅对那些以限制的方式来执行的函数来说是有效的 这些函数必须 对任何的输入都有同样的返回值 函数的结果必须不能因为包（PACKAGE）变量 数据库或会话（SESSION）的参数（例如NLS参数）不同而变化 如果函数在将来重新定义 返回值必须对任何参数来说仍然与以前的返回值相同 函数的创建者可以在以CREATE FUNCTION CREATE PACKAGE或者CREATE TYPE声明函数时根据以上的要求使用DETERMINISTIC关键字向数据库申明该函数为确定性函数 数据库不会对确定性函数的合法性进行校验 即使 一个函数明显的使用了包变量或操作了数据库 仍然可以被定义为确定性函数 这就是说如何安全合法的使用和定义确定性函数是程序员的责任
lishixinzhi/Article/program/Oracle/201311/19040
【oracle如何进行优化 优化oracle sql 语句性能的方法】关于oracle如何进行优化和优化oracle sql 语句性能的方法的介绍到此就结束了，不知道你从中找到你需要的信息了吗 ？如果你还想了解更多这方面的信息，记得收藏关注本站 。

    推荐阅读


    上一篇：如何提升新媒体创新能力，如何提升新媒体创新能力论文

    下一篇：网络游戏挂机2015，网络游戏挂机真的能够赚钱吗?