oracle数据库调优 Oracle数据库系统调优方法

大家好，今天来为大家分享oracle数据库调优的一些知识点，和Oracle数据库系统调优方法的问题解析，大家要是都明白，那么可以忽略，如果不太清楚的话可以看看本篇文章，相信很大概率可以解决您的问题，接下来我们就一起来看看吧！

如何设置使oracle10g性能最优 性能调优 步骤

一、磁盘方面调优

1.规范磁盘阵列

RAID 10比RAID5更适用于OLTP系统，RAID10先镜像磁盘，再对其进行分段，由于对数据的小规模访问会比较频繁，所以对OLTP适用。而RAID5,优势在于能够充分利用磁盘空间，并且减少阵列的总成本。但是由于阵列发出一个写入请求时，必须改变磁盘上已修改的块，需要从磁盘上读取“奇偶校验”块，并且使用已修改的块计算新的奇偶校验块，然后把数据写入磁盘，且会限制吞吐量。对性能有所影响，RAID5适用于OLAP系统。

2.数据文件分布

分离下面的东西,避免磁盘竞争

Ø SYSTEM表空间

Ø TEMPORARY表空间

Ø UNDO表空间

Ø联机重做日志（放在最快的磁盘上）

Ø操作系统磁盘

Ø ORACLE安装目录

Ø经常被访问的数据文件

Ø索引表空间

Ø归档区域（应该总是与将要恢复的数据分离）

例：

²/: System

²/u01: Oracle Software

²/u02: Temporary tablespace, Control file1

²/u03: Undo Segments, Control file2

²/u04: Redo logs, Archive logs, Control file4

²/u05: System, SYSAUX tablespaces

²/u06: Data1,control file3

²/u07: Index tablespace

²/u08: Data2

通过下列语句查询确定IO问题

select name,phyrds,phywrts,readtim,writetim

from v$filestat a,v$datafile b

where a.file#=b.file# order by readtim desc;

3.增大日志文件

u增大日志文件的大小，从而增加处理大型INSERT,DELETE,UPDATE操作的比例

查询日志文件状态

select a.member,b.* from v$logfile a,v$log b where a.GROUP#=b.GROUP#

查询日志切换时间

select b.RECID,to_char(b.FIRST_TIME,'yyyy-mm-dd hh24:mi:ss') start_time,a.RECID,to_char(a.FIRST_TIME,'yyyy-mm-dd hh24:mi:ss') end_time,round(((a.FIRST_TIME-b.FIRST_TIME)*25)*60,2) minutes

from v$log_history a,v$log_history b

where a.RECID=b.RECID+1

order by a.FIRST_TIME desc

增大日志文件大小，以及对每组增加日志文件（一个主文件、一个多路利用文件）

u增大LOG_CHECKPOINT_INTERVAL参数，现已不提倡使用它

如果低于每半小时切换一次日志，就增大联机重做日志大小。如果处理大型批处理任务时频繁进行切换，就增大联机重做日志数目。

alter database add logfile member‘/log.ora’ to group 1;

alter database drop logfile member‘/log.ora’;

4. UNDO表空间

修改三个初始参数：

UNDO_MANAGEMENT=AUTO

UNDO_TABLESPACE=CLOUDSEA_UNDO

UNDO_RETENTION=<#of minutes>

5.不要在系统表空间中执行排序

二、初始化参数调优

32位的寻址最大支持应该是2的32次方，就是4G大小。但实际中32位系统（XP，windows2003等MS32位系统, ubuntu等linux32位系统）要能利用4G内存，都是采用内存重映射技术。需要主板及系统的支持。如果关闭主板BIOS的重映射功能，系统将不能利用4G内存，可能只达3.5G.而在windows下看到的一般为3.25G。所以SGA设置为内存的40%，但不能超过3.25G

1.重要初始化参数

l SGA_MAX_SIZE

l SGA_TARGET

l PGA_AGGREGATE_TARGET

l DB_CACHE_SIZE

l SHARED_POOL_SIZE

2.调整DB_CACHE_SIZE来提高性能

它设定了用来存储和处理内存中数据的SGA区域大小，从内存中取数据比磁盘快10000倍以上

根据以下查询出数据缓存命中率

select sum(decode(name,'physical reads',value,0)) phys,

sum(decode(name,'db block gets',value,0)) gets,

sum(decode(name,'consistent gets',value,0)) con_gets,

(1-(sum(decode(name,'physical reads',value,0))/(sum(decode(name,'db block gets',value,0))+sum(decode(name,'consistent gets',value,0)))))*100 Hitratio

from v$sysstat;

一个事务处理程序应该保证得到95%以上的命中率，命中率从90%提高到98%可能会提高500%的性能，ORACLE正在通过CPU或服务时间与等待时间来分析系统性能，不太重视命中率，不过现在的库缓存和字典缓存仍将命中率作为基本的调整方法。

在调整DB_CACHE_SIZE时使用V$DB_CACHE_ADVICE

select size_for_estimate, estd_physical_read_factor, estd_physical_reads

from v$db_cache_advice

where name='DEFAULT';

如果查询的命中率过低，说明缺少索引或者索引受到限制，通过V$SQLAREA视图查询执行缓慢的SQL

3.设定DB_BLOCK_SIZE来反映数据读取量大小

OLTP一般8K

OLAP一般16K或者32K

4.调整SHARED_POOL_SIZE以优化性能

正确地调整此参数可以同等可能地共享SQL语句，使得在内存中便能找到使用过的SQL语句。为了减少硬解析次数，优化对共享SQL区域的使用，需尽量使用存储过程、使用绑定变量

保证数据字典缓存命中率在95%以上

select((1- sum(getmisses)/(sum(gets)+sum(getmisses)))*100) hitratio

from v$rowcache

where gets+getmisses<>0;

如果命中率小于 99％，就可以考虑增加shared pool以提高library cache的命中率

SELECT SUM(PINS)"EXECUTIONS",SUM(RELOADS)"CACHE MISSES WHILE EXECUTING",1- SUM(RELOADS)/SUM(PINS)

FROM V$LIBRARYCACHE;

通常规则是把它定为DB_CACHE_SIZE大小的50%－150%，在使用了大量存储过程或程序包，但只有有限内存的系统里，最后分配为150%。在没有使用存储过程但大量分配内存给DB_CACHE_SIZE的系统里，这个参数应该为10%－20%

5.调整PGA_AGGREGATE_TARGET以优化对内存的应用

u OLTP：totalmemory*80%*20%

u DSS: totalmemory*80%*50%

6. 25个重要初始化参数

² DB_CACHE_SIZE：分配给数据缓存的初始化内存

² SGA_TARGET：使用了自动内存管理，则设置此参数。设置为0可禁用它

² PGA_AGGREGATE_TARGET：所有用户PGA软内存最大值

² SHARED_POOL_SIZE：分配给数据字典、SQL和PL／SQL的内存

² SGA_MAX_SIZE：SGA可动态增长的最大内存

² OPTIMIZER_MODE：

² CURSOR_SHARING：把字面SQL转换成带绑定变更的SQL，可减少硬解析开销

² OPTIMIZER_INDEX_COST_ADJ：索引扫描成本和全表扫描成本进行调整，设定在1－10间会强制频繁地使用索引，保证索引可用性

² QUERY_REWRITE_ENABLED：用于启用具体化视图和基于函数的索引功能

² DB_FILE_MULTIBLOCK_READ_COUNT：对于全表扫描，为了更有效执行IO，此参数可在一次IO中读取多个块

² LOG_BUFFER：为内存中没有提交的事务分配缓冲区（非动态参数）

² DB_KEEP_CACHE_SIZE：分配给KEEP池或者额外数据缓存的内存

² DB_RECYCLE_CACHE_SIZE：

² DBWR_IO_SLAVES：如果没有异步IO，参数等同于DB_WRITER_PROCESSES模拟异步IO而分配的从SGA到磁盘的写入器数。如果有异步IO，则使用DB_WRITER_PROCESSES设置多个写程序，在DBWR期间更快地写出脏块

² LARGE_POOL_SIZE：分配给大型PLSQL或其他一些很少使用的ORACLE选项LARGET池的总块数

² STATISTICS_LEVEL：启用顾问信息，并可选择提供更多OS统计信息来改进优化器决策。默认：TYPICAL

² JAVA_POOL_SIZE：为JVM使用的JAVA存储过程所分配的内存

² JAVA_MAX_SESSIONSPACE_SIZE：跟踪JAVA类的用户会话状态所用内存上限

² MAX_SHARED_SERVERS：当使用共享服务器时的共享服务器上限

² WORKAREA_SIZE_POLICY：启用PGA大小自动管理

² FAST_START_MTTR_TARGET：完成一次崩溃恢复的大概时间／S

² LOG_CHECKPOINT_INTERVAL：检查点频率

² OPEN_CURSORS：指定了保存用户语句的专用区域大小，如此设置过高会导致ORA-4031

² DB_BLOCK_SIZE：数据库默认块大小

² OPTIMIZER_DYNAMIC_SAMPLING：控制动态抽样查询读取的块数量，对正在使用全局临时表的系统非常有用

三、 SQL调优1.使用提示

1.1改变执行路径

通过OPTIMIZER_MODE参数指定优化器使用方法，默认ALL_ROWS

Ø ALL_ROWS可得最佳吞吐量执行查询所有行

Ø FIRST_ROWS(n)可使优化器最快检索出第一行:

select/*+ FIRST_ROWS(1)*/ store_id,… from tbl_store

1.2使用访问方法提示

允许开发人员改变访问的实际查询方式，经常使用INDEX提示

Ø CLUSTER强制使用集群

Ø FULL

Ø HASH

Ø INDEX语法：/*+ INDEX(TABLE INDEX1,INDEX2….)*/ COLUMN 1,….

当不指定任何INDEX时，优化器会选择最佳的索引

SELECT/*+ INDEX*/ STORE_ID FROM TBL_STORE

Ø INDEX_ASC 8I开始默认是升序，所以与INDEX同效

Ø INDEX_DESC

Ø INDEX_COMBINE用来指定多个位图索引，而不是选择其中最好的索引

Ø INDEX_JOIN只需访问这些索引，节省了重新检索表的时间

Ø INDEX_FFS执行一次索引的快速全局扫描，只处理索引，不访问具体表

Ø INDEX_SS

Ø INDEX_SSX_ASC

Ø INDEX_SS_DESC

Ø NO_INDEX

Ø NO_INDEX_FFS

Ø NO_INDEX_SS

1.3使用查询转换提示

对于数据仓库非常有帮助

Ø FACT

Ø MERGE

Ø NO_EXPAND语法：/*+ NO_EXPAND*/ column1,…

保证OR组合起的IN列表不会陷入困境，/*+ FIRST_ROWS NO_EXPAND*/

Ø NO_FACT

Ø NO_MERGE

Ø NO_QUERY_TRANSFORMATION

Ø NO_REWRITE

Ø NO_STAR_TRANSFORMATION

Ø NO_UNSET

Ø REWRITE

Ø STAR_TRANSFORMATION

Ø UNSET

Ø USE_CONCAT

1.4使用连接操作提示

显示如何将连接表中的数据合并在一起，可用两提示直接影响连接顺序。LEADING指定连接顺序首先使用的表，ORDERED告诉优化器基于FROM子句中的表顺序连接这些表，并使用第一个表作为驱动表（最行访问的表）

ORDERED语法：/*+ ORDERED*/ column 1,….

访问表顺序根据FROM后的表顺序来

LEADING语法：/*+ LEADING(TABLE1)*/ column 1,….

类似于ORDER，指定驱动表

Ø NO_USE_HASH

Ø NO_USE_MERGE

Ø NO_USE_NL

Ø USE_HASH前提足够的HASH_AREA_SIZE或PGA_AGGREGATE_TARGET

通常可以为较大的结果集提供最佳的响应时间

Ø USE_MERGE

Ø USE_NL通常可以以最快速度返回一个行

Ø USE_NL_WITH_INDEX

1.5使用并行执行

Ø NO_PARALLEL

Ø NO_PARALLEL_INDEX

Ø PARALLEL

Ø PARALLEL_INDEX

Ø PQ_DISTRIBUTE

1.6其他提示

Ø APPEND不会检查当前所用块中是否有剩余空间，而直接插入到表中，会直接将数据添加到新的块中。

Ø CACHE会将全表扫描全部缓存到内存中，这样可直接在内存中找到数据，不用在磁盘上查询

Ø CURSOR_SHARING_EXACT

Ø DRIVING_SITE

Ø DYNAMIC_SAMPLING

Ø MODEL_MIN_ANALYSIS

Ø NOAPPEND

Ø NOCACHE

Ø NO_PUSH_PRED

Ø NO_PUSH_SUBQ

Ø NO_PX_JOIN_FILTER

Ø PUSH_PRED

Ø PUSH_SUBQ强制先执行子查询，当子查询很快返回少量行时，这些行可以用于限制外部查询返回行数，可极大地提高性能

例：select/*+PUSH_SUBQ*/ emp.empno,emp.ename

From emp,orders

where emp.deptno=(select deptno from dept where loc=’1’)

Ø PX_JOIN_FILTER

Ø QB_NAME

2.调整查询

2.1在V$SQLAREA中选出最占用资源的查询

HASH_VALUE：SQL语句的Hash值。

ADDRESS：SQL语句在SGA中的地址。

PARSING_USER_ID：为语句解析第一条CURSOR的用户

VERSION_COUNT：语句cursor的数量

KEPT_VERSIONS：

SHARABLE_MEMORY：cursor使用的共享内存总数

PERSISTENT_MEMORY：cursor使用的常驻内存总数

RUNTIME_MEMORY：cursor使用的运行时内存总数。

SQL_TEXT：SQL语句的文本（最大只能保存该语句的前1000个字符）。

MODULE,ACTION：用了DBMS_APPLICATION_INFO时session解析第一条cursor时信息

SORTS:语句的排序数

CPU_TIME:语句被解析和执行的CPU时间

ELAPSED_TIME:语句被解析和执行的共用时间

PARSE_CALLS:语句的解析调用(软、硬)次数

EXECUTIONS:语句的执行次数

INVALIDATIONS:语句的cursor失效次数

LOADS:语句载入(载出)数量

ROWS_PROCESSED:语句返回的列总数

select b.username,a.DISK_READS,a.EXECUTIONS,a.DISK_READS/decode(a.EXECUTIONS,0,1,a.EXECUTIONS) rds_exec_ratio,a.SQL_TEXT

from v$sqlarea a,dba_users b

where a.PARSING_USER_ID=b.user_id and a.DISK_READS>100 order by a.DISK_READS desc;

2.2在V$SQL中选出最占用资源的查询

与V$SQLAREA类似

select* from

(select sql_text,rank() over(order by buffer_gets desc) as rank_buffers,to_char(100*ratio_to_report(buffer_gets) over(),'999.99') pct_bufgets from v$sql)

where rank_buffers<11

2.3确定何时使用索引

²当查询条件只需要返回很少的行（受限列）时，则需要建立索引，不同的版本中这个返回要求不同

V5:20% V7:7% V8i,V9i:4% V10g: 5%

查看表上的索引

select a.table_name,a.index_name,a.column_name,a.column_position,a.table_owner

from dba_ind_columns a

where a.table_owner='CLOUDSEA'

²修正差的索引，可使用提示来限制很差的索引，如INDEX，FULL提示

²在SELECT和WHERE中的列使用索引

如: select name from tbl where no=?

建立索引：create index test on tbl(name,no) tablespace cloudsea_index storage(….)

对于系统中很关键的查询，可以考虑建立此类连接索引

²在一个表中有多个索引时可能出现麻烦，使用提示INDEX指定使用索引

²使用索引合并，使用提示INDEX_JOIN

²基于函数索引，由于使用了函数造成查询很慢.必须基于成本的优化模式,参数：

QUERY_REWRITE_ENALED=TRUE

QUERY_REWRITE_INTEGRITY=TRUSTED(OR ENFORCED)

create index test on sum(test);

2.4在内存中缓存表

将常用的相对小的表缓存到内存中，但注意会影响到嵌套循环连接上的驱动表

alter table tablename cache;

2.5使用EXISTS与嵌套子查询代替IN

SELECT…FROM EMP WHERE DEPT_NO NOT IN(SELECT DEPT_NO FROM DEPT WHERE DEPT_CAT=’A’);

(方法一:高效)

SELECT….FROM EMP A,DEPT B WHERE A.DEPT_NO= B.DEPT(+) AND B.DEPT_NO IS NULL AND B.DEPT_CAT(+)=‘A’

(方法二:最高效)

SELECT….FROM EMP E WHERE NOT EXISTS(SELECT‘X’ FROM DEPT D WHERE D.DEPT_NO= E.DEPT_NO AND DEPT_CAT=‘A’);

四、使用STATSPACK和AWR报表调整等待和闩锁

1. 10GR2里的脚本

在$ORACLE_HOME/RDBMS/ADMIN下

Spcreate.sql通过调用spcusr.sql spctab.sql和spcpkg.sql创建STATSPACK环境，使用SYSDBA运行它

Spdrop.sql调用sptab.sql和spdusr.sql删除整个STATSPACK环境，使用SYSDBA运行它

Spreport.sql这是生成报表的主要脚本，由PERFSTAT用户运行

Sprepins.sql为指定的数据库和实例生成实例报表

Sprepsql.sql为指定的SQL散列值生成SQL报表

Sprsqins.sql为指定的数据库和实例生成SQL报表

Spauto.sql使用DBMS_JOB自动进行统计数据收集（照相）

Sprepcon.sql配置SQLPLUS变量来设置像阈值这样的内容的配置文件

Spurge.sql删除给定数据库实例一定范围内的快照ID，不删除基线快照

Sptrunc.sql截短STATSPACK表里所有性能数据

五、执行快速系统检查1.缓冲区命中率

查询缓冲区命中率

select(1-(sum(decode(name,'physical reads',value,0))/

(sum(decode(name,'db block gets',value,0))+

sum(decode(name,'consistent gets',value,0)))))* 100"Hit Ratio"

from v$sysstat;

Oracle数据库系统调优方法

Oracle数据库广泛应用在社会的各个领域，特别是在Client/Server模式的应用，但是应用开发者往往碰到整个系统的性能随着数据量的增大显着下降的问题，为了解决这个问题，从以下几个方面：数据库服务器、网络I/O、应用程序等对整个系统加以调整，充分发挥Oracle的效能，提高整个系统的性能。

1调整数据库服务器的性能

Oracle数据库服务器是整个系统的核心，它的性能高低直接影响整个系统的性能，为了调整Oracle数据库服务器的性能，主要从以下几个方面考虑：

1.1调整

操作系统以适合Oracle数据库服务器运行

Oracle数据库服务器很大程度上依赖于运行服务器的操作系统，如果操作系统不能提供最好性能，那么无论如何调整，Oracle数据库服务器也无法发挥其应有的性能。

1.1.1为Oracle数据库服务器规划系统资源

据已有计算机可用资源，规划分配给Oracle服务器资源原则是：尽可能使Oracle服务器使用资源最大化，特别在Client/Server中尽量让服务器上所有资源都来运行Oracle服务。

1.1.2调整计算机系统中的内存配置

多数操作系统都用虚存来模拟计算机上更大的内存，它实际上是硬盘上的一定的磁盘空间。当实际的内存空间不能满足应用软件的要求时，操作系统就将用这部分的磁盘空间对内存中的信息进行页面替换，这将引起大量的磁盘I/O操作，使整个服务器的性能下降。为了避免过多地使用虚存，应加大计算机的内存。

1.1.3为Oracle数据库服务器设置操作系统进程优先级

不要在操作系统中调整Oracle进程的优先级，因为在Oracle数据库系统中，所有的后台和前台数据库服务器进程执行的是同等重要的工作，需要同等的优先级。所以在安装时，让所有的数据库服务器进程都使用缺省的优先级运行。

1.2调整内存分配

Oracle数据库服务器保留3个基本的内存高速缓存，分别对应3种不同类型的数据：库高速缓存，字典高速缓存和缓冲区高速缓存。库高速缓存和字典高速缓存一起构成共享池，共享池再加上缓冲区高速缓存便构成了系统全程区（SGA）。SGA是对数据库数据进行快速访问的一个系统全程区，若SGA本身需要频繁地进行释放、分配，则不能达到快速访问数据的目的，因此应把SGA放在主存中，不要放在虚拟内存中。内存的调整主要是指调整组成SGA的内存结构的大小来提高系统性能，由于Oracle数据库服务器的内存结构需求与应用密切相关，所以内存结构的调整应在磁盘I/O调整之前进行。

1.2.1库缓冲区的调整

库缓冲区中包含私用和共享SQL和PL/SQL区，通过比较库缓冲区的命中率决定它的大小。要调整库缓冲区，必须首先了解该库缓冲区的活动情况，库缓冲区的活动统计信息保留在动态性能表v$librarycache数据字典中，可通过查询该表来了解其活动情况，以决定如何调整。

1.2.2数据字典缓冲区的调整

数据字典缓冲区包含了有关数据库的结构、用户、实体信息。数据字典的命中率，对系统性能影响极大。数据字典缓冲区的使用情况记录在动态性能表v$librarycache中，可通过查询该表来了解其活动情况，以决定如何调整。

1.2.3缓冲区高速缓存的调整

用户进程所存取的所有数据都是经过缓冲区高速缓存来存取，所以该部分的命中率，对性能至关重要。缓冲区高速缓存的使用情况记录在动态性能表v$sysstat中，可通过查询该表来了解其活动情况，以决定如何调整。

2调整 Client/Server模式下的网络 I/O

Client/Server环境中的应用处理是分布在客户应用程序和数据库服务程序之间的。在 Client/Server环境中Client与Server之间的网络I/O是整个系统性能提高的瓶颈，一个客户应用程序引起的网络I/O越少，应用及整个系统的性能越好。减少网络I/O的最重要的一条原则：将应用逻辑集中在数据库服务器中。

2.1使用Oracle数据库的完整约束性

当为应用建表时，应当为一些有特殊要求的数据加上适当的完整性约束，这样就能实现由数据库本身而不是应用程序来约束数据符合一定的条件。数据库服务器端的完整约束的执行操作是在比SQL语句级别更低的系统机制上优化，它与客户端无关，只在服务器中运行，不需在Client端和Server端之间传递SQL语句，有效地减轻网络I/O负担。

2.2使用数据库触发器

完整约束性只能实现一些较简单的数据约束条件，对一些较复杂的事物处理规则就无能为力，这时最好不要在应用程序中实施复杂的程序控制，而是应当采用数据库触发器来实施复杂的事物规则。数据库触发器能实现由数据库本身，而不是应用程序，来约束数据符合复杂的事物处理规则，并且容易创建，便于管理，避免大量的网络I/O。

2.3使用存储过程、存储函数和包

Oracle的存储过程和存储函数是命名的能完成一定功能并且存储在Server端的PL/SQL的集合。包是一种把有关的过程和函数组织封装成一个数据库程序单元的方法。它们相对于应用程序的过程、函数而言，把SQL命令存储在Server端。使用存储过程和存储函数，应用程序不必再包含多个网络操作的SQL语句去执行数据库服务器操作，而是简单调用存储过程和存储函数，在网络上传输的只是调用过程的名字和输出结果，这样就可减少大量的网络I/O。

3应用程序的调整

3.1 SQL语句的优化

SQL语句的执行速度，可以受很多因素的影响而变化。但主要的影响因素是：驱动表、执行操作的先后顺序和索引的运用。可以由很多不同的方法间接地改变这些因素，以达到最优的执行速度。这里主要探讨当对多个表进行连接查询时应遵循的优化原则：

3.2建立和使用视图、索引

利用视图可以将基表中的列或行进行裁减、隐藏一部分数据，并且能够将涉及到多个表的复杂查询以视图的方式给出，使应用程序开发简洁快速。利用索引可以提高查询性能，减少磁盘 I/O，优化对数据表的查询，加速SQL语句的执行。但任何时候建立索引都能提高性能，何时建立索引应当遵循以下原则：该表常用来在索引列上查询，该表不常更新、插入、删除等操作，查询出来的结果记录数应控制在原表的2%～4%。

3.3使用 Oracle的数组接口

当一个客户应用程序插入一行或用一个查询来向服务器请求某行时，不是发送具有单个行的网络包，而是采用数组处理，即把要插入的多个行或检索出的多个行缓冲在数组中，然后通过很少的几个包就可在网上传送这些数组。例如，一个给定的Select语句返回2000行数据，每行平均大小为40个字节，数据包的大小为4kB，而数组大小参数（arraysize）设置为20，则需从服务器发送100个数据包到客户机。如果简单地把（arraysize）设置为2000，那么同样的操作只需要传送 20个数据包。这样就减少了网络的传输量，提高了所有应用的性能。

4总结

我们在开发应用程序时，遵循上述的方法和原则，对系统进行调整，收到了令人满意的效果。但是应当指出，由于客户机、网络、服务器这3个相互依存的组成部分都必须调整和同步才能产生最佳的性能，因此还应根据系统的具体情况，具体分析和调整。

Oracle调优(入门及提高篇)

在过去的十年中， Oracle已经成为世界上最专业的数据库之一。对于 IT专家来说，就是要确保利用 Oracle的强大特性来提高他们公司的生产力。最有效的方法之一是通过 Oracle调优。它有大量的调整参数和技术来改进你的 Oracle数据库的性能。

Oracle调优是一个复杂的主题。关于调优可以写整整一本书，不过，为了改善 Oracle数据库的性能，有一些基本的概念是每个 Oracle DBA都应该遵从的。

在这篇简介中，我们将简要地介绍以下的 Oracle主题：

--外部调整：我们应该记住 Oracle并不是单独运行的。因此我们将查看一下通过调整 Oracle服务器以得到高的性能。

--Row re-sequencing以减少磁盘 I/O：我们应该懂得 Oracle调优最重要的目标是减少 I/O。

--Oracle SQL调整。 Oracle SQL调整是 Oracle调整中最重要的领域之一，只要通过一些简单的 SQL调优规则就可以大幅度地提升 SQL语句的性能，这是一点都不奇怪的。

--调整 Oracle排序：排序对于 Oracle性能也是有很大影响的。

--调整 Oracle的竞争：表和索引的参数设置对于 UPDATE和 INSERT的性能有很大的影响。

我们首先从调整 Oracle外部的环境开始。如果内存和 CPU的资源不足的话，任何的 Oracle调整都是没有帮助的。

外部的性能问题

Oracle并不是单独运行的。 Oracle数据库的性能和外部的环境有很大的关系。这些外部的条件包括有：

． CPU--CPU资源的不足令查询变慢。当查询超过了 Oracle服务器的 CPU性能时，你的数据库性能就受到 CPU的限制。

．内存--可用于 Oralce的内存数量也会影响 SQL的性能，特别是在数据缓冲和内存排序方面。

．网络--大量的 Net8通信令 SQL的性能变慢。

许多新手都错误的认为应该首先调整 Oracle数据库，而不是先确认外部资源是否足够。实际上，如果外部环境出现瓶颈，再多的 Oracle调整都是没有帮助的。

在检查 Oracle的外部环境时，有两个方面是需要注意的：

1、当运行队列的数目超过服务器的 CPU数量时，服务器的性能就会受到 CPU的限制。补救的方法是为服务器增加额外的 CPU或者关闭需要很多处理资源的组件，例如 Oracle Parallel Query。

2、内存分页。当内存分页时，内存容量已经不足，而内存页是与磁盘上的交换区进行交互的。补救的方法是增加更多的内存，减少 Oracle SGA的大小，或者关闭 Oracle的多线程服务器。

可以使用各种标准的服务器工具来得到服务器的统计数据，例如 vmstat,glance,top和 sar。 DBA的目标是确保数据库服务器拥有足够的 CPU和内存资源来处理 Oracle的请求。

以下让我们来看一下 Oracle的 row-resequencing是如何能够极大地减少磁盘 I/O的。

Row-resequencing（行的重新排序）

就象我们上面提到的，有经验的 Oracle DBA都知道 I/O是响应时间的最大组成部分。其中磁盘 I/O特别厉害，因为当 Oracle由磁盘上的一个数据文件得到一个数据块时，读的进程就必须等待物理 I/O操作完成。磁盘操作要比数据缓冲慢 10,000倍。因此，如果可以令 I/O最小化，或者减少由于磁盘上的文件竞争而带来的瓶颈，就可以大大地改善 Oracle数据库的性能。

如果系统响应很慢，通过减少磁盘 I/O就可以有一个很快的改善。如果在一个事务中通过按一定的范围搜索 primary-key索引来访问表，那么重新以 CTAS的方法组织表将是你减少 I/O的首要策略。通过在物理上将行排序为和 primary-key索引一样的顺序，就可以加快获得数据的速度。

就象磁盘的负载平衡一样，行的重新排序也是很简单的，而且也很快。通过与其它的 DBA管理技巧一起使用，就可以在高 I/O的系统中大大地减少响应的时间。

在高容量的在线事务处理环境中（ online transaction processing， OLTP），数据是由一个 primary索引得到的，重新排序表格的行就可以令连续块的顺序和它们的 primary索引一样，这样就可以在索引驱动的表格查询中，减少物理 I/O并且改善响应时间。这个技巧仅在应用选择多行的时候有用，或者在使用索引范围搜索和应用发出多个查询来得到连续的 key时有效。对于随机的唯一 primary-key（主键）的访问将不会由行重新排序中得到好处。

让我们看一下它是如何工作的。考虑以下的一个 SQL的查询，它使用一个索引来得到 100行：

selectsalaryfromemployeewherelast_name like'B%';

这个查询将会使用 last_name_index，搜索其中的每一行来得到目标行。这个查询将会至少使用 100次物理磁盘的读取，因为 employee的行存放在不同的数据块中。

不过，如果表中的行已经重新排序为和 last_name_index的一样，同样的查询又会怎样处理呢？我们可以看到这个查询只需要三次的磁盘 I/O就读完全部 100个员工的资料（一次用作索引的读取，两次用作数据块的读取），减少了 97次的块读取。

重新排序带来的性能改善的程度在于在你开始的时候行的乱序性如何，以及你需要由序列中访问多少行。至于一个表中的行与索引的排序键的匹配程度，可以查看数据字典中的 dba_indexes和 dba_tables视图得到。

在 dba_indexes的视图中，查看 clustering_factor列。如果 clustering_factor的值和表中的块数目大致一样，那么你的表和索引的顺序是一样的。不过，如果 clustering_factor的值接近表中的行数目，那就表明表格中的行和索引的顺序是不一样的。

行重新排序的作用是不可以小看的。在需要进行大范围的索引搜索的大表中，行重新排序可以令查询的性能提高三倍。

一旦你已经决定重新排序表中的行，你可以使用以下的工具之一来重新组织表格。

.使用 Oracle的 Create Table As Select(CTAS)语法来拷贝表格

. Oracle9i自带的表格重新组织工具

以下，我们来看以下 SQL语句的调优。

SQL调优

Oracle的 SQL调优是一个复杂的主题，甚至是需要整本书来介绍 Oracle SQL调优的细微差别。不过有一些基本的规则是每个 Oracle DBA都需要跟从的，这些规则可以改善他们系统的性能。 SQL调优的目标是简单的：

.消除不必要的大表全表搜索：不必要的全表搜索导致大量不必要的 I/O，从而拖慢整个数据库的性能。调优专家首先会根据查询返回的行数目来评价 SQL。在一个有序的表中，如果查询返回少于 40%的行，或者在一个无序的表中，返回少于 7%的行，那么这个查询都可以调整为使用一个索引来代替全表搜索。对于不必要的全表搜索来说，最常见的调优方法是增加索引。可以在表中加入标准的 B树索引，也可以加入 bitmap和基于函数的索引。要决定是否消除一个全表搜索，你可以仔细检查索引搜索的 I/O开销和全表搜索的开销，它们的开销和数据块的读取和可能的并行执行有关，并将两者作对比。在一些情况下，一些不必要的全表搜索的消除可以通过强制使用一个 index来达到，只需要在 SQL语句中加入一个索引的提示就可以了。

.在全表搜索是一个最快的访问方法时，将小表的全表搜索放到缓存中，调优专家应该确保有一个专门的数据缓冲用作行缓冲。在 Oracle7中，你可以使用 alter table xxx cache语句，在 Oracle8或以上，小表可以被强制为放到 KEEP池中缓冲。

.确保最优的索引使用：对于改善查询的速度，这是特别重要的。有时 Oracle可以选择多个索引来进行查询，调优专家必须检查每个索引并且确保 Oracle使用正确的索引。它还包括 bitmap和基于函数的索引的使用。

.确保最优的 JOIN操作：有些查询使用 NESTED LOOP join快一些，有些则是 HASH join快一些，另外一些则是 sort-merge join更快。

这些规则看来简单，不过它们占 SQL调优任务的 90%，并且它们也无需完全懂得 Oracle SQL的内部运作。以下我们来简单概览以下 Oracle SQL的优化。

我们首先简要查看 Oracle的排序，并且看一看排序操作是如何影响性能的。

调整 Oracle的排序操作

排序是 SQL语法中一个小的方面，但很重要，在 Oracle的调整中，它常常被忽略。当使用 create index、 ORDER BY或者 GROUP BY的语句时， Oracle数据库将会自动执行排序的操作。通常，在以下的情况下 Oracle会进行排序的操作：

使用 Order by的 SQL语句

使用 Group by的 SQL语句

在创建索引的时候

进行 table join时，由于现有索引的不足而导致 SQL优化器调用 MERGE SORT

当与 Oracle建立起一个 session时，在内存中就会为该 session分配一个私有的排序区域。如果该连接是一个专用的连接(dedicated connection)，那么就会根据 init.ora中 sort_area_size参数的大小在内存中分配一个 Program Global Area(PGA)。如果连接是通过多线程服务器建立的，那么排序的空间就在 large_pool中分配。不幸的是，对于所有的 session，用做排序的内存量都必须是一样的，我们不能为需要更大排序的操作分配额外的排序区域。因此，设计者必须作出一个平衡，在分配足够的排序区域以避免发生大的排序任务时出现磁盘排序（ disk sorts）的同时，对于那些并不需要进行很大排序的任务，就会出现一些浪费。当然，当排序的空间需求超出了 sort_area_size的大小时，这时将会在 TEMP表空间中分页进行磁盘排序。磁盘排序要比内存排序大概慢 14,000倍。

上面我们已经提到，私有排序区域的大小是有 init.ora中的 sort_area_size参数决定的。每个排序所占用的大小由 init.ora中的 sort_area_retained_size参数决定。当排序不能在分配的空间中完成时，就会使用磁盘排序的方式，即在 Oracle实例中的临时表空间中进行。

磁盘排序的开销是很大的，有几个方面的原因。首先，和内存排序相比较，它们特别慢；而且磁盘排序会消耗临时表空间中的资源。 Oracle还必须分配缓冲池块来保持临时表空间中的块。无论什么时候，内存排序都比磁盘排序好，磁盘排序将会令任务变慢，并且会影响 Oracle实例的当前任务的执行。还有，过多的磁盘排序将会令 free buffer waits的值变高，从而令其它任务的数据块由缓冲中移走。

接着，让我们看一下 Oracle的竞争，并且看一下表的存储参数的设置是如何影响 SQL UPDATE和 INSERT语句的性能的。

调整 Oracle的竞争

Oracle的其中一个优点时它可以管理每个表空间中的自由空间。 Oracle负责处理表和索引的空间管理，这样就可以让我们无需懂得 Oracle的表和索引的内部运作。不过，对于有经验的 Oracle调优专家来说，他需要懂得 Oracle是如何管理表的 extent和空闲的数据块。对于调整拥有高的 insert或者 update的系统来说，这是非常重要的。

要精通对象的调整，你需要懂得 freelists和 freelist组的行为，它们和 pctfree及 pctused参数的值有关。这些知识对于企业资源计划（ ERP）的应用是特别重要的，因为在这些应用中，不正确的表设置通常是 DML语句执行慢的原因。

对于初学者来说，最常见的错误是认为默认的 Oracle参数对于所有的对象都是最佳的。除非磁盘的消耗不是一个问题，否则在设置表的 pctfree和 pctused参数时，就必须考虑平均的行长和数据库的块大小，这样空的块才会被有效地放到 freelists中。当这些设置不正确时，那些得到的 freelists也是"dead"块，因为它们没有足够的空间来存储一行，这样将会导致明显的处理延迟。

Freelists对于有效地重新使用 Oracle表空间中的空间是很重要的，它和 pctfree及 pctused这两个存储参数的设置直接相关。通过将 pctused设置为一个高的值，这时数据库就会尽快地重新使用块。不过，高性能和有效地重新使用表的块是对立的。在调整 Oracle的表格和索引时，需要认真考虑究竟需要高性能还是有效的空间重用，并且据此来设置表的参数。以下我们来看一下这些 freelists是如何影响 Oracle的性能的。

当有一个请求需要插入一行到表格中时， Oracle就会到 freelist中寻找一个有足够的空间来容纳一行的块。你也许知道， freelist串是放在表格或者索引的第一个块中，这个块也被称为段头（ segment header）。 pctfree和 pctused参数的唯一目的就是为了控制块如何在 freelists中进出。虽然 freelist link和 unlink是简单的 Oracle功能，不过设置 freelist link(pctused)和 unlink(pctfree)对 Oracle的性能确实有影响。

由 DBA的基本知识知道， pctfree参数是控制 freelist un-links的（即将块由 freelists中移除）。设置 pctfree=10意味着每个块都保留 10%的空间用作行扩展。 pctused参数是控制 freelist re-links的。设置 pctused=40意味着只有在块的使用低于 40%时才会回到表格的 freelists中。

许多新手对于一个块重新回到 freelists后的处理都有些误解。其实，一旦由于一个删除的操作而令块被重新加入到 freelist中，它将会一直保留在 freelist中即使空间的使用超过了 60%，只有在到达 pctfree时才会将数据块由 freelist中移走。

表格和索引存储参数设置的要求总结

以下的一些规则是用来设置 freelists, freelist groups, pctfree和 pctused存储参数的。你也知道， pctused和 pctfree的值是可以很容易地通过 alter table命令修改的，一个好的 DBA应该知道如何设置这些参数的最佳值。

有效地使用空间和高性能之间是有矛盾的，而表格的存储参数就是控制这个方面的矛盾：

.对于需要有效地重新使用空间，可以设置一个高的 pctused值，不过副作用是需要额外的 I/O。一个高的 pctused值意味着相对满的块都会放到 freelist中。因此，这些块在再次满之前只可以接受几行记录，从而导致更多的 I/O。

.追求高性能的话，可以将 pctused设置为一个低的值，这意味着 Oracle不会将数据块放到 freelists中直到它几乎是空的。那么块将可以在满之前接收更多的行，因此可以减少插入操作的 I/O。要记住 Oracle扩展新块的性能要比重新使用现有的块高。对于 Oracle来说，扩展一个表比管理 freelists消耗更少的资源。

让我们来回顾一下设置对象存储参数的一些常见规则：

．经常将 pctused设置为可以接收一条新行。对于不能接受一行的 free blocks对于我们来说是没有用的。如果这样做，将会令 Oracle的性能变慢，因为 Oracle将在扩展表来得到一个空的块之前，企图读取 5个"dead"的 free block。

．表格中 chained rows的出现意味着 pctfree太低或者是 db_block_size太少。在很多情况下， RAW和 LONG RAW列都很巨大，以至超过了 Oracle的最大块的大小，这时 chained rows是不可以避免的。

．如果一个表有同时插入的 SQL语句，那么它需要有同时删除的语句。运行单一个一个清除的工作将会把全部的空闲块放到一个 freelist中，而没有其它包含有任何空闲块的 freelists出现。

． freelist参数应该设置为表格同时更新的最大值。例如，如果在任何时候，某个表最多有 20个用户执行插入的操作，那么该表的参数应该设置为 freelists=20。

应记住的是 freelist groups参数的值只是对于 Oracle Parallel Server和 Real Application Clusters才是有用的。对于这类 Oracle， freelist groups应该设置为访问该表格的 Oracle Parallel Server实例的数目。

好了，文章到这里就结束啦，如果本次分享的oracle数据库调优和Oracle数据库系统调优方法问题对您有所帮助，还望关注下本站哦！