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.分离页面交换文件到多个磁盘物理卷

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

三、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).分区对用户透明，最终用户感觉不到分区的存在。