Oracle性能优化总结

个人理解，数据库性能最关键的因素在于IO，因为操作内存是快速的，但是读写磁盘是速度很慢的，优化数据库最关键的问题在于减少磁盘的IO，就个人理解应该分为物理的和逻辑的优化，物理的是指oracle产品本身的一些优化，逻辑优化是指应用程序级别的优化物理优化：

一、优化内存

V$ROWCACHE视图结构

3.管理员可以通过下述语句来查看数据缓冲区的使用情况

select name,value from v$sysstat where name in ('db block gets', 'consistent gets ', 'physical reads');

数据缓冲区使用命中率（physical reads除以db block gets加consistent gets之和)一定要小于10%，否则需要增加数据缓冲区大小

4.管理员可以通过执行下述语句，查看日志缓冲区的使用情况

select name,value from v$sysstat where name in ('redo entries','redo log space requests')

根据查询出的结果可以计算出日志缓冲区的申请失败率：requests除以entries

申请失败率应该解决与0，否则说明日志缓冲区开设太小，需要增加Oracle数据库的日志缓冲区

二、物理I/0的优化

1.在磁盘上建立数据文件前首先运行磁盘碎片整理程序

为了安全地整理磁盘碎片，需关闭打开数据文件的实例，并且停止服务。如果有足够的连续磁盘空间建立数据文件，那么就容易避免数据文件产生碎片。

2.不要使用磁盘压缩（Oracle文件不支持磁盘压缩）

3.不要使用磁盘加密

加密像磁盘压缩一样加了一个处理层，降低磁盘读写速度。如果担心自己的数据可能泄露，可以使用dbms_obfuscation包和label security选择性地加密数据的敏感部分

4.使用RAID

raid使用应注意：

选择硬件raid超过软件raid；日志文件不要放在raid5卷上，因为raid5读性能高而写性能差；把日志文件和归档日志放在与控制文件和数据文件分离的磁盘控制系统上

5.分离页面交换文件到多个磁盘物理卷

跨越至少两个磁盘建立两个页面文件。可以建立四个页面文件并在性能上受益，确保所有页面文件的大小之和至少是物理内存的两倍。

三、cpu优化调整

1.cpu使用情况

一般unix操作系统，可以使用sar -u命令查看cpu的使用率；NT操作系统的服务器，可以使用NT的性能管理器来查看CPU的使用率

出现CPU资源不足的情况很多：SQL语句的重解析、低效率的SQL语句、锁冲突都会引起cpu资源不足

2.查看sql语句的解析情况

数据库管理员可以执行下述语句来查看SQL语句的解析情况：

select * from v$sysstat where name in ('parse time cpu','parse time elapsed','parse count (hard)');

这里parse_time_cpu是系统服务时间，parse_time_elapsed是响应时间。

waite_time = parse_time_elapsed-parse_time_cpu

由此可以得到用户SQL语句平均解析等待时间:

用户SQL语句平均解析等待时间=waite time/parse count

数据库管理员还可以通过下述语句，查看低效率的SQL语句

select buffer_gets,executlons,sql_text from v$sqlarea;

优化这些低效率的SQL语句也有助于提高CPU的利用率

3.查看Oracle数据库的冲突情况

数据库管理员可以通过v$system_event数据字典中的"latch free"统计项查看Oracle数据库的冲突情况，如果没有冲突的话，latch free查询出来没有结果。如果冲突太大的话，数据库管理员可以降低spin_count参数值，来消除

4.cpu优化

取消屏幕保护、把系统配置为应用服务器、监视系统中消耗中断的硬件、保持最小的安全审计记录、在专门服务器上运行Oracle、禁止非必须的服务

四、网络配置优化

网络配置是性能调整的一项很重要的内容，而且很容易隐藏性能瓶颈

配置网卡使用最快和有效模式、删除不需要的网络协议、优化网络协议绑定顺序，为Oracle 禁止或优化文件共享

五、使用CBO优化器

参见本目录CBD优化器

六、合理配置oracle实例参数

七、索引优化（减少IO）

如何某表的某个字段有主键约束和唯一性约束，则Oracle 则会自动在相应的约束列上建议唯一索引。数据库索引主要进行提高访问速度。

建设原则:

1、索引应该经常建在Where 子句经常用到的列上。如果某个大表经常使用某个字段进行查询，并且检索行数小于总表行数的5%。则应该考虑。

2、对于两表连接的字段，应该建立索引。经常在某表的一个字段进行Order By 则也经过进行索引。

3、不应该在小表上建设索引。

优缺点:

索引主要进行提高数据的查询速度。当进行DML时，会更新索引。因此索引越多，则DML越慢，其需要维护索引。因此在创建索引及DML需要权衡。

创建索引:

单一索引:Create Index On (Column_Name);

复合索引: Create Index i_deptno_job on emp(deptno,job); —>在emp表的deptno、job列建立索引。

select * from emp where deptno=66 and job='sals' ->走索引。

select * from emp where deptno=66 OR job='sals' ->将进行全表扫描。不走索引select * from emp where deptno=66 ->走索引。

select * from emp where job='sals' ->进行全表扫描、不走索引。

如果在where 子句中有OR 操作符或单独引用Job 列(索引列的后面列) 则将不会走索引，将会进行全表扫描。

4.分析表与索引（analyze 不会重建索引）

analyze table tablename compute statistics

等同于analyze table tablename compute statistics for table for all indexes for all columns

for table 的统计信息存在于视图：user_tables 、all_tables、dba_tables

for all indexes 的统计信息存在于视图: user_indexes 、all_indexes、dba_indexes for all columns 的统计信息存在于视图：user_tab_columns、all_tab_columns、

dba_tab_columns

注：分析表与索引见AnalyzeAllTable存储过程

5、一般来讲可以采用以下三种方式来手工分析索引。

analyze index idx_t validate structure：

analyze index idx_t compute statistics：

analyze index idx_t estimate statistics sample 10 percent

1）analyze index idx_t validate structure：

这段分析语句是用来分析索引的block中是否有坏块儿，那么根据分析我们可以得到索引的结构数据，这些数据会保留到

index_stats中，来判断这个索引是否需要rebuild. 需要注意的是这样的分析是不会收集索引的统计信息的。

2）validate structure有二种模式：online, offline，一般来讲默认的方式是offline。当以offline的模式analyze索引时，会对table加一个表级共享锁，对目前table的一些实时DMl操作会产生一定的影响。

而以online模式分析时候，则不会加任何lock，但在index_stats中是看不到任何信息的。3）analyze index idx_t compute statistics：

用来统计索引的统计信息(全分析)，主要为CBO服务。

4）analyze index idx_t estimate statistics sample 10 percent

主要是用来指定比例进行抽样分析，也是为CBO服务. 例中是抽样10%

6.常用来表连接的字段应用聚簇索引

先建聚簇：这个跟索引列类型一致

create cluster emp_dept_cluster

( deptno number(2) )

size 1024

建立聚簇索引，把索引建在聚簇上

create index emp_dept_cluster_idx on cluster emp_dept_cluster

建表使用聚簇

create table dept(

deptno number(2) primary key,

dname varchar2(14),

loc varchar2(13)

)cluster emp_dept_cluster(deptno)

八、建立表分区，将索引数据和表数据分开在不同的表空间上

对于逻辑结构的优化，还应将表数据和索引数据分开表空间存储，分别使用独立的表空间。因为如果将表数据和索引数据放在一起，表数据的I/O操作和索引的I/O操作将产生影响系统性能的I/O竞争，降低系统的响应效率。将表数据和索引数据存放在不同的表空间中，并在物理层面将这两个表空间的数据文件放在不同的物理磁盘上，就可以避免这种竞争了。

逻辑优化：

一、可以对表进行逻辑分割

如中国移动用户表，可以根据手机尾数分成10个表，这样对性能会有一定的作用

二、Sql语句使用占位符语句，并且必须按照规定编写sql语句

（如全部大写，全部小写等）oracle解析语句后会放置到共享池中

如：select * from Emp where name=? 这个语句只会在共享池中有一条，而如果是字符串的话，那就根据不同名字存在不同的语句，所以占位符效率较好

三、一些耗时的操作，可以通过存储过程等在用户较少的情况下执行

数据库不仅仅是一个存储数据的地方，同样是一个编程的地方，一些耗时的操作，可以通过存储过程等在用户较少的情况下执行，从而错开系统使用的高峰时间，提高数据库性能

四、尽量不要整表查询，尽量用where having语句筛选

如select * from Emp，因为要转化为具体的列名是要查数据字典，比较耗时

五、选择有效的表名

对于多表连接查询，可能oracle的优化器并不会优化到这个程度，oracle 中多表查询是根据FROM字句从右到左的数据进行的，那么最好右边的表（也就是基础表）选择数据较少的表，这样排序更快速，如果有link表（多对多中间表），那么将link表放最右边作为基础表，在默认情况下oracle会自动优化，但是如果配置了优化器的情况下，可能不会自动优化，所以平时最好能按照这个方式编写sql

六. Where字句规则

Oracle 中Where字句时从右往左处理的，表之间的连接写在其他条件之前，能过滤掉非常多的数据的条件，放在where的末尾，另外!=符号比较的列将不使用索引，列经过了计算（如变大写等）不会使用索引（需要建立起函数），is null、is not null等优化器不会使用索引

七、使用Exits Not Exits 替代In Not in

八、合理使用事务，合理设置事务隔离性

数据库的数据操作比较消耗数据库资源的，尽量使用批量处理，以降低事务操作次数

Oracle分区是怎样优化数据库的

Oracle的分区可以分为：列表分区、范围分区、散列分区、复合分区。

1). 增强可用性：如果表的一个分区由于系统故障而不能使用，表的其余好的分区仍可以使用；

2). 减少关闭时间：如果系统故障只影响表的一部份分区，那么只有这部份分区需要修复，可能比整个大表修复花的时间更少；

3). 维护轻松：如果需要得建表，独产管理每个公区比管理单个大表要轻松得多；

4). 均衡I/O：可以把表的不同分区分配到不同的磁盘来平衡I/O改善性能；

5). 改善性能：对大表的查询、增加、修改等操作可以分解到表的不同分区来并行执行，可使运行速度更快

6). 分区对用户透明，最终用户感觉不到分区的存在。

ORACLE优化总结和注意事项

ORACLE优化总结和注意事项 本文档中对优化方法进行详述，并对在优化过程中发现的一些问题进行总结。列出ORACLE的一些注意事项 注意事项： 1.安装的过程中，请务必进行正确安装。 2.当安装过程中出现错误的时候，最好清除原有遗留信息，进行重装，否则在数据库运行 的过程中可能会出现各种诡异的问题。 3.当数据库安装的过程中如果有警告信息，请记录下来，存档，方便排查数据库问题 4.安装的过程中请选择OLTP的数据模板Transaction Processing 5.安装过程中文件的创建

Controlfile、Datafiles、Redo Log Groups如果条件允许，最好分别放于不同的磁盘上。其中Controlfile和Redo Log Groups要尽量保证放在不同的磁盘上 6.其中Redo Log Groups重做日志组最好建5组以上，每个文件大小在1G以上，最大不超 过3G，避免出现进行check_point的时候造成buffer wait 导致数据库宕机 7.检查/etc/hosts文件 配置最后一行信息，将当前的主机名和ip配对起来，避免应用服务连接数据库导致的性能损耗 8.安装完成后，请启动数据库确保数据库基本安装成功 步骤： sqlplus /nolog connect /as sysdba startup//启动数据库实例 exit//退出sqlplus lsnrctl start//启动监听

emctl start dbconsole 上述步骤如果执行完，没有报错，则说明数据库基本安装正确，并可正常运行。如果执行上述操作的时候出现了问题，则说明数据库安装的过程中出现了某些问题，即使数据库实例当前可以启动连接，但是在以后稳定服务的过程中也是有可能会出现一些数据库问题的。 配置OCI连接 因为当前应用服务采用OCI连接的方式，因此在运行应用服务之前要配置OCI的连接条件 1、需求软件： 如果应用服务是跟ORACLE数据库安装在一台机器上，则不需要额外软件，直接进入第2步即可 如果应用服务是跟ORACLE数据库分开部署，则需要在部署应用服务的机器上安装一个客户端（精简客户端即可大小几M）需要从官方网站下载三个文件instantclient-basic-linux-x86-64-10.2.0.3-20070103.zip instantclient-sqlplus-linux-x86-64-10.2.0.3-20070103.zip instantclient-jdbc-linux-x86-64-10.2.0.3-20070103.zip 解压到同一个目录中，同时在该目录下新建一个文件tnsnames.ora文件，文件中添加以下内容 # Generated by Oracle configuration tools. HMS = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.15.61)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = hms) ) )

OracleSQL性能优化方法

OracleSQL性能优化方法 Oracle性能优化方法(SQL篇) (1) 1综述 (2) 2表分区的应用 (2) 3访咨询Table的方式 (3) 4共享SQL语句 (3) 5选择最有效率的表名顺序 (5) 6WHERE子句中的连接顺序． (6) 7SELECT子句中幸免使用’*’ (6) 8减少访咨询数据库的次数 (6) 9使用DECODE函数来减少处理时刻 (7) 10整合简单,无关联的数据库访咨询 (8) 11删除重复记录 (8) 12用TRUNCATE替代DELETE (9) 13尽量多使用COMMIT (9) 14运算记录条数 (9) 15用Where子句替换HA VING子句 (9) 16减少对表的查询 (10) 17通过内部函数提高SQL效率 (11) 18使用表的不名(Alias) (12) 19用EXISTS替代IN (12) 20用NOT EXISTS替代NOT IN (13) 21识不低效执行的SQL语句 (13) 22使用TKPROF 工具来查询SQL性能状态 (14) 23用EXPLAIN PLAN 分析SQL语句 (14) 24实时批量的处理 (16)

1综述 ORACLE数据库的性能调整是个重要，却又有难度的话题，如何有效地进行调整，需要通过反反复复的过程。在数据库建立时，就能依照顾用的需要合理设计分配表空间以及储备参数、内存使用初始化参数，对以后的数据库性能有专门大的益处，建立好后，又需要在应用中不断进行应用程序的优化和调整，这需要在大量的实践工作中不断地积存体会，从而更好地进行数据库的调优。 数据库性能调优的方法 ●调整内存 ●调整I/O ●调整资源的争用咨询题 ●调整操作系统参数 ●调整数据库的设计 ●调整应用程序 本文针对应用程序的调整，来讲明对数据库性能如何进行优化。 2表分区的应用 关于海量数据的表，能够考虑建立分区以提高操作效率。建立分区一样以关键字为分区的标志，也能够以其他字段作为分区的标志，但效率不如关键字高。建立分区的语句在建表时能够进行讲明： create table TABLENAME() partition by range (PutOutNo) (partition PART1 values lessthan (200312319999) partition PART2 values lessthan (200412319999) 。。。。。。 如此，在进行大部分数据查询，数据更新和数据插入时，Oracle自动判定操作应该在哪个分区进行，幸免了整表操作，提高了执行的效率

ORACLE 性能优化

ORACLE 数据库性能优化 参考书目： 《ORACLE 9i Database Performance Tuning Guide and Reference》《ORACLE 9i Database Reference》 《ORACLE 9i SQL Reference》 《ORACLE 9i Database Administrator’s Guide》

一、数据库实例创建过程参数确定 在创建数据库实例过程中，需要确定以下几个参数： 1. 数据块大小（DB_BLOCK_SIZE） 该参数指明了ORACLE所处理的数据存贮于数据文档以及SGA内存中的数据块大小。 该参数的可选择的范围为:4k,8k,16k,32k,64k。对于OLTP系统而言，取值可以为4K或8K，对于DSS系统而言，则可以取较大的数据，如32K或64K 建议统一取8K（即8192） 说明 DB_BLOCK_SIZE的大小将影响创建表时的EXTENT的大小。例如指定db_block_size=16K,某表空间的EXTENT MANAGEMENT 为local autoallocate，则其系统将extent的大小最小指定为1M.所以将可能导致空间的浪费。 2. 字符集(Character set) 该参数确定数据库以何种字符集来存贮CHAR以及V ARCHAR、V ARCHAR2等字符类型的值。对于ORACLE数据字典中的字符（如表及字段的COMMENT 内容）具有同样的作用。因此需要考虑如字符集的使用。对于国际项目，因为数据库中的comment内容（包括表及字符、存贮过程中的中文字符等内容）可能性需要以中文存贮，而用户业务数据使用的字符可能性是使用本地的语言，基于此，该参数需要选择支持UNICODE的字符编码的字符集。目前ORACLE9i支持以下二种UNICODE字符集： ?UTF8 ?AL32UTF8 建议统一取AL32UTF8

( O管理)ORACLESL性能优化(内部培训资料)

（O管理）ORACLESL性能优化(内部培训资料)

ORACLESQL性能优化系列(一) 1.选用适合的ORACLE优化器 ORACLE的优化器共有3种: a.RULE(基于规则) b.COST(基于成本) c.CHOOSE(选择性) 设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS.你当然也在SQL句级或是会话(session)级对其进行覆盖. 为了使用基于成本的优化器(CBO,Cost-BasedOptimizer),你必须经常运行analyze命令,以增加数据库中的对象统计信息(objectstatistics)的准确性. 如果数据库的优化器模式设置为选择性(CHOOSE),那么实际的优化器模式将和是否运行过analyze命令有关.如果table已经被analyze过,优化器模式将自动成为CBO,反之,数据库将采用RULE形式的优化器. 在缺省情况下,ORACLE采用CHOOSE优化器,为了避免那些不必要的全表扫描(fulltablescan),你必须尽量避免使用CHOOSE优化器,而直接采用基于规则或者基于成本的优化器.

2.访问Table的方式 ORACLE采用两种访问表中记录的方式: a.全表扫描 全表扫描就是顺序地访问表中每条记录.ORACLE采用一次读入多个数据块(databaseblock)的方式优化全表扫描. b.通过ROWID访问表 你可以采用基于ROWID的访问方式情况,提高访问表的效率,,ROWID包含了表中记录的物理位置信息..ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系.通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高. 3.共享SQL语句 为了不重复解析相同的SQL语句,在第一次解析之后,ORACLE将SQL语句存放在内存中.这块位于系统全局区域SGA(systemglobalarea)的共享池(sharedbufferpool)中的内存可以被所有的数据库用户共享.因此,当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同,ORACLE就能很快获得已经被解析的语句以及最好的执行路

Oracle SQL性能优化方法研究

Oracle SQL性能优化方法探讨 Oracle性能优化方法(SQL篇) (1) 1综述 (2) 2表分区的应用 (2) 3访问Table的方式 (3) 4共享SQL语句 (3) 5选择最有效率的表名顺序 (5) 6WHERE子句中的连接顺序． (6) 7SELECT子句中幸免使用’*’ (6) 8减少访问数据库的次数 (6) 9使用DECODE函数来减少处理时刻 (7) 10整合简单,无关联的数据库访问 (8) 11删除重复记录 (8) 12用TRUNCATE替代DELETE (9) 13尽量多使用COMMIT (9) 14计算记录条数 (9) 15用Where子句替换HAVING子句 (9) 16减少对表的查询 (10) 17通过内部函数提高SQL效率 (11)

18使用表的不名(Alias) (12) 19用EXISTS替代IN (12) 20用NOT EXISTS替代NOT IN (13) 21识不低效执行的SQL语句 (13) 22使用TKPROF 工具来查询SQL性能状态 (14) 23用EXPLAIN PLAN 分析SQL语句 (14) 24实时批量的处理 (16)

1综述 ORACLE数据库的性能调整是个重要，却又有难度的话题，如何有效地进行调整，需要通过反反复复的过程。在数据库建立时，就能依照顾用的需要合理设计分配表空间以及存储参数、内存使用初始化参数，对以后的数据库性能有专门大的益处，建立好后，又需要在应用中不断进行应用程序的优化和调整，这需要在大量的实践工作中不断地积存经验，从而更好地进行数据库的调优。 数据库性能调优的方法 ●调整内存 ●调整I/O ●调整资源的争用问题 ●调整操作系统参数 ●调整数据库的设计 ●调整应用程序 本文针对应用程序的调整，来讲明对数据库性能如何进行优化。 2表分区的应用 关于海量数据的表，能够考虑建立分区以提高操作效率。建

ORACLE性能优化31条

1.ORACLE的优化器共有3种 A、RULE (基于规则) b、COST (基于成本) c、CHOOSE (选择性) 设置缺省的优化器，可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明，如RULE，COST，CHOOSE，ALL_ROWS，FIRST_ROWS 。你当然也在SQL句级或是会话(session)级对其进行覆盖。 为了使用基于成本的优化器(CBO，Cost-Based Optimizer) ，你必须经常运行analyze 命令，以增加数据库中的对象统计信息(object statistics)的准确性。 如果数据库的优化器模式设置为选择性(CHOOSE)，那么实际的优化器模式将和是否运行过analyze 命令有关。如果table已经被analyze过，优化器模式将自动成为CBO ，反之，数据库将采用RULE 形式的优化器。 在缺省情况下，ORACLE采用CHOOSE优化器，为了避免那些不必要的全表扫描(full table scan) ，你必须尽量避免使用CHOOSE优化器，而直接采用基于规则或者基于成本的优化器。 2.访问Table的方式 ORACLE 采用两种访问表中记录的方式： A、全表扫描 全表扫描就是顺序地访问表中每条记录。ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描。 B、通过ROWID访问表 你可以采用基于ROWID的访问方式情况，提高访问表的效率，ROWID包含了表中记录的物理位置信息。ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系。通常索引提供了快速访问ROWID的方法，因此那些基于索引列的查询就可以得到性能上的提高。 3.共享SQL语句 为了不重复解析相同的SQL语句，在第一次解析之后，ORACLE将SQL语句存放在内存中。这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享。因此，当你执行一个SQL语句(有时被称为一个游标)时，如果它和之前的执行过的语句完全相同，ORACLE就能很快获得已经被解析的语句以及最好的执行路径。ORACLE的这个功能大大地提高了SQL 的执行性能并节省了内存的使用。 可惜的是ORACLE只对简单的表提供高速缓冲(cache buffering)，这个功能并不适用于多表连接查询。 数据库管理员必须在init.ora中为这个区域设置合适的参数，当这个内存区域越大，就可以保留更多的语句，当然被共享的可能性也就越大了。 当你向ORACLE提交一个SQL语句，ORACLE会首先在这块内存中查找相同的语句。这里需要注明的是，ORACLE对两者采取的是一种严格匹配，要达成共享，SQL语句必须完全相同(包括空格，换行等)。 数据库管理员必须在init.ora中为这个区域设置合适的参数，当这个内存区域越大，就可以保留更多的语句，当然被共享的可能性也就越大了。 共享的语句必须满足三个条件： A、字符级的比较：当前被执行的语句和共享池中的语句必须完全相同。 B、两个语句所指的对象必须完全相同： C、两个SQL语句中必须使用相同的名字的绑定变量(bind variables)。 4.选择最有效率的表名顺序(只在基于规则的优化器中有效) ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表(基础表driving table)将被最先处理。在FROM子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。当ORACLE处理多个表时，会运用排序及合并的方式连接它们。首先，扫描第一个表(FROM子句中最后的那个表)并对记录进行派序，然后扫描第二个表(FROM子句中最后第二个表)，最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。 如果有3个以上的表连接查询，那就需要选择交叉表(intersection table)作为基础表，交叉表是指

Oracle性能优化

ORACLE的优化器共有3种 A、RULE (基于规则) b、COST (基于成本) c、CHOOSE (选择性) 设置缺省的优化器，可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明，如RULE，COST，CHOOSE，ALL_ROWS，FIRST_ROWS 。你当然也在SQL句级或是会话(session)级对其进行覆盖。 为了使用基于成本的优化器(CBO， Cost-Based Optimizer) ，你必须经常运行analyze 命令，以增加数据库中的对象统计信息(object statistics)的准确性。 如果数据库的优化器模式设置为选择性(CHOOSE)，那么实际的优化器模式将和是否运行过analyze命令有关。如果table已经被analyze过，优化器模式将自动成为CBO ，反之，数据库将采用RULE形式的优化器。 在缺省情况下，ORACLE采用CHOOSE优化器，为了避免那些不必要的全表扫描(full table scan) ，你必须尽量避免使用CHOOSE优化器，而直接采用基于规则或者基于成本的优化器。 2.访问Table的方式 ORACLE 采用两种访问表中记录的方式： A、全表扫描 全表扫描就是顺序地访问表中每条记录。ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描。 B、通过ROWID访问表 你可以采用基于ROWID的访问方式情况，提高访问表的效率， ROWID 包含了表中记录的物理位置信息。ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系。通常索引提供了快速访问ROWID的方法，因此那些基于索引列的查询就可以得到性能上的提高。 3.共享SQL语句 为了不重复解析相同的SQL语句，在第一次解析之后，ORACLE将SQL语句存放在存中。这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的存可以被所有的数据库用户共享。因此，当你执行一个SQL语句(有时被称为一个游标)时，如果它和之前的执行过的语句完全相同， ORACLE就能很快获得已经被解析的语句以及最好的执行路径。ORACLE的这个功能大提高了SQL的执行性能并节省了存的使用。 可惜的是ORACLE只对简单的表提供高速缓冲(cache buffering)，这个功能并不适用于多表连接查询。

Oracle性能优化

y物理模型CheckList （Oracle，性能） 1. 系统级优化 数据库参数配置 合理分配SGA及其内部参数（经验值如下）： SGA=phy*（60%-80%） Share pool=SAG*45% DB Cache=SGA*45% Log Buffer: 1~3M 注：Oracle9i在Windows下有bug，是由Windows下的SGA最大 值有2G的限制造成的 注意调整process和open cursor参数，这两个参数直接影响 数据库的session量 分离表和索引：将表和索引建立在不同的表空间，决不要将 不属于Oracle内部系统的对象存放到SYSTEM表空间。同 时，确保数据表空间和索引表空间置于不同的硬盘，减少I/O 竞争； 如果是企业版数据库，大表可以考虑采取分区存储措施，提 高系统的性能； 优化Export和Import工作：使用较大的BUFFER(比如10MB , 10,240,000)可以提高EXPORT和IMPORT的速度 定期分析查询计划，提高数据库的性能；

2. 索引相关 要对经常查询的字段建立索引，但是由于索引管理的开销， 在增删改操作频繁的情况下避免建立不必要的索引； 对于只读或者接近只读的场合，如数据仓库，对于势值比较 小的列可以考虑使用bitmap索引； 如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引. 3. SQL相关 Oracle的From子句表的顺序：记录越多的表放在越前面 （左）； Oracle的where子句表达式的顺序：过滤掉最大数目记录的条 件放到where子句的末尾； Select子句中避免使用‘*’，增加了查询表的列的开销； 在执行结果等效的情况下，使用Truncate代替Delete； 为了在查询过程中要尽量使用索引，对于like语句避免使用 右匹配或者中间匹配的模糊查询； 将过滤条件尽可能放到Where子句中，而不是放到Having子 句中； 在SQL语句中，要减少对表的查询，特别是在含有子查询的 SQL子句中； 使用表的别名可以减少解析的时间并避免引起歧义； 使用exists替代in； 用NOT EXISTS替代NOT IN； 通常情况下，采用表连接的方式比exists更有效率； 当提交一个包含一对多表信息(比如部门表和雇员表)的查询

Oracle性能优化总结

个人理解，数据库性能最关键的因素在于IO，因为操作内存是快速的，但是读写磁盘是速度很慢的，优化数据库最关键的问题在于减少磁盘的IO，就个人理解应该分为物理的和逻辑的优化，物理的是指oracle产品本身的一些优化，逻辑优化是指应用程序级别的优化物理优化： 一、优化内存

V$ROWCACHE视图结构

3.管理员可以通过下述语句来查看数据缓冲区的使用情况 select name,value from v$sysstat where name in ('db block gets', 'consistent gets ', 'physical reads'); 数据缓冲区使用命中率（physical reads除以db block gets加consistent gets之和)一定要小于10%，否则需要增加数据缓冲区大小 4.管理员可以通过执行下述语句，查看日志缓冲区的使用情况 select name,value from v$sysstat where name in ('redo entries','redo log space requests') 根据查询出的结果可以计算出日志缓冲区的申请失败率：requests除以entries 申请失败率应该解决与0，否则说明日志缓冲区开设太小，需要增加Oracle数据库的日志缓冲区 二、物理I/0的优化 1.在磁盘上建立数据文件前首先运行磁盘碎片整理程序 为了安全地整理磁盘碎片，需关闭打开数据文件的实例，并且停止服务。如果有足够的连续磁盘空间建立数据文件，那么就容易避免数据文件产生碎片。 2.不要使用磁盘压缩（Oracle文件不支持磁盘压缩） 3.不要使用磁盘加密

oracle性能调优-管理oracle日志之Oracle日志运行机制

理解Oracle的日志机制 ? Oracle的日志是用来记录用户对数据库的改变，这样，当出现服务器硬件故障或者用户错误而丢失数据时，可以通过重做这些日志来恢复已提交的事务，Oracle日志机制包含以下组件： ?日志缓存SGA的一部分，用于缓存服务器进程产生的日志，包括DML和DDL； ? LGWR进程这个后台进程负责将日志缓存的数据写到联机日志文件，每个实例只有一个； ?数据库检查点检查点用于同步数据文件和日志文件，一个检查点事件的完成，代表在这个事件开始之前发生的所有对数据文件的改变都已实际记录到了数据文件，数据库在这个时间点是一致的，在实例恢复的时候，只有在最后一个检查点之后的日志才需要重做； ?联机日志文件用于存放从日志缓存中写出的日志数据，每个数据库最少需要两个日志文件，当前日志文件填满以后，发生日志切换，然后才可以继续写下一个日志文件； ?日志归档LGWR写满所有组的联机日志文件以后，会回头再写第一个组的日志文件，在非归档模式下，被重用的日志文件中的日志会被丢弃，在归档模式下，日志文件被重用前会被ARC0进程复制到归档日志文件； ? 一些可选的日志机制，如归档和Standby，因为附加的I/O会降低系统的性能，同时提供了可靠的灾难恢复能力，不建议因这些性能的下降而关闭生产系统的归档功能。 调整日志缓存 ? 日志缓存的管理机制可以类似理解成一个漏斗，日志数据不断地从漏斗上方加入，然后偶尔打开漏斗下方的开关将加入的数据清空，这个开关就是LGWR进程，为了日志缓存有空间容纳不断加进来的日志数据，LGWR在下面列出的任何一个条件下都会执行写出日志缓存的操作： ?应用程序发出Commit命令时； ?三秒间隔已到时； ?日志缓存三分之一满时； ?日志缓存达到1M时； ?数据库检查点发生时； ? 测量日志缓存的性能通过服务器进程放置日志条到日志缓存时发生等待的次数和时间来测量； Select Name, Value From V$sysstat Where Name In ('redo entries', 'redo buffer allocation retries','redo log space requests'); redo entries 服务器进程放进日志缓存的日志条的总数量； redo buffer allocation retries 服务器放置日志条时必须等待然后再重试的次数； redo log space requests LGWR进程写出日志缓存时等待日志切换的次数； 这个查询用于计算日志缓存重试率，这个比率应该小于百分之一； Select Retries.Value / Entries.Value "Redo log Buffer Retry Ratio" From V$sysstat Entries, V$sysstat Retries Where https://www.wendangku.net/doc/692154071.html, = 'redo entries' And https://www.wendangku.net/doc/692154071.html, = 'redo buffer allocation retries'; 这个查询用来显示哪些会话的LGWR正在进行写等待；

oracle性能优化简介

ORACLE SQL性能优化 我要讲的题目是Oracle SQL性能优化，只是Oracle性能优化中的一项。Oracle的性能优化包含很多方面，比如调整物理存取，调整逻辑存取，调整内存使用，减少网络流量等。这里选择SQL性能优化是因为这部分内容我们测试人员最容易接触到，另外开发人员写SQL脚本时有时很随意，不知不觉就会造成程序性能上的下降。 1．选择最有效率的表名顺序(只在基于规则的优化器中有效) ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基 础表.当ORACLE处理多个表时, 会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描 第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出 的记录与第一个表中合适记录进行合并. 例如: 表 TAB1 16,384 条记录 表 TAB2 1 条记录 选择TAB2作为基础表 (最好的方法) select count(*) from tab1,tab2 执行时间0.96秒 选择TAB2作为基础表 (不佳的方法)

select count(*) from tab2,tab1 执行时间26.09秒 如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表. 例如: EMP表描述了LOCATION表和CATEGORY表的交集. SELECT * FROM LOCATION L , CATEGORY C, EMP E WHERE E.EMP_NO BETWEEN 1000 AND 2000 AND E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN 将比下列SQL更有效率 SELECT * FROM EMP E , LOCATION L , CATEGORY C WHERE E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN AND E.EMP_NO BETWEEN 1000 AND 2000 2．WHERE子句中的连接顺序． ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.

oracle性能优化(简单版)

--数据库巡检或性能优化方法各异，但首要的是要发现数据库性能瓶颈，系统自带的statspack,或awr太耗时， --以下是本人常用的方法，共享之 --1、查询数据库等待事件top10,关注前前几个等待事件，关注前三个等待事件是否有因果或关联关系 --oracle 9i select t2.event,round(100*t2.time_waited/(t1.w1+t3.cpu),2) event_wait_percent from ( SELECT SUM(time_waited) w1 FROM v$system_event WHERE event NOT IN ('smon timer','pmon timer','rdbms ipc message','Null event','parallel query dequeue','pipe get', 'client message','SQL*Net message to client','SQL*Net message from client','SQL*Net more data from client', 'dispatcher timer','virtual circuit status','lock manager wait for remote message','PX Idle Wait', 'PX Deq: Execution Msg','PX Deq: Table Q Normal','wakeup time manager','slave wait','i/o slave wait', 'jobq slave wait','null event','gcs remote message','gcs for action','ges remote message','queue messages') ) t1, (select * from ( select t.event,t.total_waits,t.total_timeouts,t.time_waited,t.average_wait,rownum num from (select event,total_waits,total_timeouts,time_waited,average_wait from v$system_event where event not in ('smon timer','pmon timer','rdbms ipc message','Null event','parallel query dequeue','pipe get', 'client message','SQL*Net message to client','SQL*Net message from client','SQL*Net more data from client', 'dispatcher timer','virtual circuit status','lock manager wait for remote message','PX Idle Wait', 'PX Deq: Execution Msg','PX Deq: Table Q Normal','wakeup time manager','slave wait','i/o slave wait', 'jobq slave wait','null event','gcs remote message','gcs for action','ges remote message','queue messages') order by time_waited desc ) t) where num<11) t2, (SELECT VALUE CPU FROM v$sysstat WHERE NAME LIKE 'CPU used by this session' ) t3 --oracle10g select t2.event,round(100*t2.time_waited/(t1.w1+t3.cpu),2) event_wait_percent from ( SELECT SUM(time_waited) w1 FROM v$system_event WHERE event NOT IN ('smon timer','pmon timer','rdbms ipc message','Null event','parallel query dequeue','pipe get','client message','SQL*Net message to client','SQL*Net message from client','SQL*Net more data from client','dispatcher timer','virtual circuit status','lock manager wait for remote message','PX Idle Wait','PX Deq: Execution Msg','PX Deq: Table Q Normal','wakeup time manager','slave wait', 'i/o slave wait','jobq slave wait','null event','gcs remote message','gcs for action','ges remote

Oracle性能优化总结

个人理解，数据库性能最关键的因素在于IO，因为操作存是快速的，但是读写磁盘是速度很慢的，优化数据库最关键的问题在于减少磁盘的IO，就个人理解应该分为物理的和逻辑的优化，物理的是指oracle产品本身的一些优化，逻辑优化是指应用程序级别的优化 物理优化： 一、优化存

3.管理员可以通过下述语句来查看数据缓冲区的使用情况 select name,value from v$sysstat where name in('db block gets','consistent gets','physica l reads'); 数据缓冲区使用命中率（physical reads除以db block gets加consistent gets之和)一定要小于10%，否则需要增加数据缓冲区大小 4.管理员可以通过执行下述语句，查看日志缓冲区的使用情况 select name,value from v$sysstat where name in ('redo entries','redo log space requests') 根据查询出的结果可以计算出日志缓冲区的申请失败率：requests除以entries 申请失败率应该解决与0，否则说明日志缓冲区开设太小，需要增加Oracle数据库的日志缓冲区 二、物理I/0的优化 1.在磁盘上建立数据文件前首先运行磁盘碎片整理程序 为了安全地整理磁盘碎片，需关闭打开数据文件的实例，并且停止服务。如果有足够的连续磁盘空间建立数据文件，那么就容易避免数据文件产生碎片。 2.不要使用磁盘压缩（Oracle文件不支持磁盘压缩） 3.不要使用磁盘加密 加密像磁盘压缩一样加了一个处理层，降低磁盘读写速度。如果担心自己的数据可能泄露，可以使用dbms_obfuscation包和label security选择性地加密数据的敏感部分 4.使用RAID raid使用应注意： 选择硬件raid超过软件raid；日志文件不要放在raid5卷上，因为raid5读性能高而写性能差；把日志文件和归档日志放在与控制文件和数据文件分离的磁盘控制系统上 5.分离页面交换文件到多个磁盘物理卷 跨越至少两个磁盘建立两个页面文件。可以建立四个页面文件并在性能上受益，确保所有页面文件的大小之和至少是物理存的两倍。

Oracle性能优化学习心得byLYH

Oracle性能优化学习心得 一，优化总的原则 1，查看系统的使用情况 2，查看SGA分配情况，结合系统具体情况进行分析。 3，表的设计分析 4，SQL语句分析 实施要则 1，查看系统的使用情况，CPU占用，内存，I/O读取等 Oracle10G提供的Oracle Enterprise Manager图形化工具中的ADDM 和SQL Tuning Advisor等可以方便的查看系统状况 2，OPS上负载均衡，不同查询用不同Instance 3，提供脚本查看SGA使用情况 4，分析SQL执行情况（trace及其他工具） 实施细节 1，外部调整：我们应该记住Oracle并不是单独运行的。因此我们将查看一下通过调整Oracle服务器以得到高的性能。 2，Row re-sequencing以减少磁盘I/O：我们应该懂得Oracle调优最重要的目标是减少I/O。 3，Oracle SQL调整。Oracle SQL调整是Oracle调整中最重要的领域之一，只要通过一些简单的SQL调优规则就可以大幅度地提升SQL语句的性能，这是一点都不奇怪的。 4，调整Oracle排序：排序对于Oracle性能也是有很大影响的。 5，调整Oracle的竞争：表和索引的参数设置对于UPDATE和INSERT的性能有很大的影响。 二，调优分类： 对Oracle数据库进行性能调整时，应当按照一定的顺序进行，因为系统在前面步骤中进行的调整可以避免后面的一些不必要调整或者代价很大的调整。一般来说可以从两个阶段入手： 1、设计阶段：对其逻辑结构和物理结构进行优化设计，使之在满足需求条件的情况下,系统性能达到最佳，系统开销达到最小； 2、数据库运行阶段：采取操作系统级、数据库级的一些优化措施来使系统性能最佳； ㈠设计阶段： A，数据库设计优化 较多修改较少查询的数据和较多查询较少修改的数据分别对待。 a，结构优化 1，根据应用程序进行数据库设计。 即应用程序采用的是传统的C/S两层体系结构，还是B/W/D三层体系结构。不同的应用程序体系结构要求的数据库资源是不同的。 2，遵循3大范式规范化数据结构，减少不必要的冗余。 3，反规范设计，增加必要冗余，提高查询速度。 4，针对变化较少的数据，合理创建临时表和视图，需注意对临时表和视图的及时同步更新

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)例子： DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID)FROM EMP X WHERE X.EMP_NO = E.EMP_NO); （9）用TRUNCATE替代DELETE： 当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短. 译者按: TRUNCATE只在删除全表适 用,TRUNCATE是DDL不是DML) （10）尽量多使用COMMIT： 只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求

Oracle性能优化技巧

1.选用适合的ORACLE优化器 ORACLE的优化器共有3种: 1.RULE(基于规则) 2.COST(基于成本) 3.CHOOSE(选择性) 设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如 RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS . 你当然也在SQL 句级或是会话(session)级对其进行覆盖. 为了使用基于成本的优化器(CBO, Cost-Based Optimizer) , 你必须经常运行analyze 命令,以增加数据库中的对象统计信息(object statistics)的准确性. 如果数据库的优化器模式设置为选择性(CHOOSE),那么实际的优化器模式将和是否运行过analyze命令有关. 如果table已经被analyze过, 优化器模式将自动成为CBO , 反之,数据库将采用RULE形式的优化器. 在缺省情况下,ORACLE采用CHOOSE优化器, 为了避免那些不必要的全表扫描(full table scan) , 你必须尽量避免使用CHOOSE优化器,而直接采用基于规则或者基于成本的优化器. 2.访问Table的方式 ORACLE 采用两种访问表中记录的方式: 1.全表扫描

全表扫描就是顺序地访问表中每条记录. ORACLE 采用一次读入多个数据块(database block)的方式优化全表扫描. 2.通过ROWID访问表 你可以采用基于ROWID的访问方式情况,提高访问表的效率, , ROWID包含了表中记录的物理位置信息..ORACLE 采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系. 通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高. 3.共享SQL语句 为了不重复解析相同的SQL语句,在第一次解析之后, ORACLE将SQL语句存放在内存中.这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享. 因此,当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同, ORACLE就能很快获得已经被解析的语句以及最好的执行路径. ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用. 可惜的是ORACLE只对简单的表提供高速缓冲(cache buffering) ,这个功能并不适用于多表连接查询.