Oracle性能优化总结

1、个人理解，数据库性能最关键的因素在于IO ，因为操作内存是快速的，但是读写磁盘是速度很慢的，优化数据库最关键的问题在于减少磁盘的IO ，就个人理解应该分为物理的和逻辑的优化，物理的是指oracle产品本身的一些优化，逻辑优化是指应用程序级别的优化物理优化：一 、优化内存内存是指Oracle SGA（系统全局区），它由共享池、数据缓冲区、日志缓冲区组成；共享池又分为共享SQL 区和数据字典缓存区。1.数据库管理员通过执行下述语句，来查看共享sql 区的使用率select (sum(pins-reloads)/sum(pins) "libCache" from v$librar

2、ycache（ DBA 权限）共享 SQL 区的使用率应该在90% 以上， 否则增加共享池大小v$rowcache视图结构NAMESVARCHARPACE2(15)GETSNUMBERGETHINUMBERTSGETHITRATI NUMBER OPINSNUMBERPINHINUMBERTS名称空间 , 例如 :sql_area,index请求的语句句柄数获得的句柄数前两者之比根据句柄查找对像并执行的次数对象在内存中的次数PINHITRATI NUMBER ORELOANUMBERDS精品文库前两者之比由于是第一次执行, 或者语句体被调出内存时需要重新reload次数oracle执行一条语句

3、会首先去获得该语句句柄(gets),然后根据句柄查找对应的语句 , 对像 (pins) 执行 , 如果该语句体因为某些因为没有在内存中则需要重载语句体 (reloads)所以 reloads最好不要超过1%,sum(pinhits)/sum(pins)要达到 95% 以上 .sum(gethits)/sum(gets)命中率也应在 95% 以上 .2.数据库管理员可以执行下述语句，查看数据字典缓冲区的使用率select (sum (gets-getmisses-usage-fixed)/sum(gets) "Row Cache" from v$rowcache （DBA 权

4、限 ）数据字典缓冲区也应该在90% 以上， 否则增加共享池大小。 本视图显示数据字典缓存(也叫 rowcache) 的各项统计。每一条记录包含不同类型的数据字典缓存数据统计，注意数据字典缓存有层次差别，因此同样的缓存名称可能不止一次出现V$ROWCACHE视图结构PARAMETERVARCHAR2(15)缓存名COUNTNUMBER缓存项总数USAGENUMBER包含有效数据的缓存项数GETSNUMBER请求总数GETMISSESNUMBER请求失败数SCANSNUMBER扫描请求数欢迎下载2SCANMISSESMODIFICATIONSNUMBERNUMBER精品文库扫描请求失败次数添加、修

5、改、删除操作数DLM_REQUESTSNUMBERDLM请求数DLM_CONFLICTSNUMBERDLM冲突数DLM_RELEASESNUMBERDLM释放数3.管理员可以通过下述语句来查看数据缓冲区的使用情况select name,valuefrom v$sysstat where name in ('db block gets', 'consistent gets', 'physical reads');数据缓冲区使用命中率 （ physical reads除以 db block gets加 consistent gets之和 )一定要小于

6、 10% ，否则需要增加数据缓冲区大小4.管理员可以通过执行下述语句，查看日志缓冲区的使用情况select name,value from v$sysstat where name in ('redo entries','redo log spacerequests')根据查询出的结果可以计算出日志缓冲区的申请失败率：requests除以 entries申请失败率应该解决与0 ，否则说明日志缓冲区开设太小，需要增加Oracle数据库的日志缓冲区二、物理 I/0 的优化1.在磁盘上建立数据文件前首先运行磁盘碎片整理 程序为了安全地整理磁盘碎片，需关闭打开数据文件的

7、实例，并且停止服务。 如果有足够的连续磁盘空间建立数据文件，那么就容易避免数据文件产生碎片。2.不要使用磁盘压缩（Oracle文件不支持磁盘压缩）3.不要使用磁盘加密欢迎下载3精品文库加密像磁盘压缩一样加了一个处理层，降低磁盘读写速度。如果担心自己的数据可能泄露，可以使用dbms_obfuscation包和 label security选择性地加密数据的敏感部分4.使用 RAIDraid 使用应注意：选择硬件 raid 超过软件 raid ；日志文件不要放在raid5卷上，因为raid5读性能高而写性能差；把日志文件和归档日志放在与控制文件和数据文件分离的磁盘控制系统上5.分离页面交换文件到多

8、个磁盘物理卷跨越至少两个磁盘建立两个页面文件。可以建立四个页面文件并在性能上受益，确保所有页面文件的大小之和至少是物理内存的两倍。三、 cpu 优化调整1.cpu 使用情况一般 unix 操作系统，可以使用sar -u命令查看cpu 的使用率； NT 操作系统的服务器，可以使用 NT 的性能管理器来查看CPU 的使用率出现 CPU 资源不足的情况很多：SQL 语句的重解析、低效率的SQL 语句、锁冲突都会引起 cpu 资源不足2.查看 sql 语句的解析情况数据库管理员可以执行下述语句来查看SQL 语句的解析情况：select * from v$sysstat where name in (&

9、#39;parse time cpu','parse time elapsed','parsecount (hard)');这里 parse_time_cpu是系统服务时间，parse_time_elapsed是响应时间。waite_time = parse_time_elapsed-parse_time_cpu由此可以得到用户SQL 语句平均解析等待时间:欢迎下载4精品文库用户 SQL 语句平均解析等待时间=waite time/parse count数据库管理员还可以通过下述语句，查看低效率的SQL 语句select buffer_gets,exec

10、utlons,sql_text from v$sqlarea;优化这些低效率的SQL 语句也有助于提高CPU 的利用率3.查看 Oracle数据库的冲突情况数据库管理员可以通过v$system_event数据字典中的 "latch free"统计项查看Oracle 数据库的冲突情况，如果没有冲突的话，latch free查询出来没有结果。如果冲突太大的话，数据库管理员可以降低spin_count参数值，来消除4.cpu 优化取消屏幕保护、 把系统配置为应用服务器、监视系统中消耗中断的硬件、保持最小的安全审计记录、在专门服务器上运行Oracle 、禁止非必须的服务四、网络配置

11、优化网络配置是性能调整的一项很重要的内容，而且很容易隐藏性能瓶颈配置网卡使用最快和有效模式、删除不需要的网络协议、优化网络协议绑定顺序，为 Oracle禁止或优化文件共享五、使用 CBO 优化器参见本目录CBD 优化器六、合理配置 oracle 实例参数序号参数名称建议值说明log_chcckpoint_intcr11val2log_checkpoint_0欢迎下载5精品文库timeoutparallcl_automa3tic_tuning4OPEN_CURSORSTRUE10245Dml_locks10000根据用户数量可适当调整，6MTS_servers10一般为用户数量的 10%7cur

12、sor_sharingSIMILAR七、索引优化（减少IO ）如何某表的某个字段有主键约束和唯一性约束，则 Oracle则会自动在相应的约束列上建议唯一索引。数据库索引主要进行提高访问速度。建设原则 :1 、索引应该经常建在Where子句经常用到的列上。如果某个大表经常使用某个字段进行查询，并且 检索行数小于总表行数的5% 。则应该考虑 。2 、对于两表连接的字段，应该建立索引。经常在某表的一个字段进行Order By则也经过进行索引 。3 、不应该在小表上建设索引。优缺点 :索引主要进行提高数据的查询速度。当进行 DML 时，会更新索引。因此索引越多，则DML 越慢，其需要维护索引。因此在创

13、建索引及DML 需要权衡。欢迎下载6精品文库创建索引 :单一索引 :Create Index <Index-Name> On <Table_Name>(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='sal

14、s' ->将进行全表扫描。不走索引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

15、table for all indexes for allcolumnsfor table的统计信息存在于视图：user_tables、all_tables、dba_tablesfor all indexes的统计信息存在于视图: user_indexes、 all_indexes、 dba_indexesfor all columns的统计信息存在于视图：user_tab_columns、 all_tab_columns、dba_tab_columns注：分析表与索引见AnalyzeAllTable存储过程5 、一般来讲可以采用以下三种方式来手工分析索引。analyze index idx_

16、t validate structure：欢迎下载7精品文库analyze index idx_t compute statistics：analyze index idx_t estimate statistics sample 10 percent1 ） analyze index idx_t validate structure：这段分析语句是用来分析索引的block中是否有坏块儿，那么根据分析我们可以得到索引的结构数据，这些数据会保留到index_stats中，来判断这个索引是否需要rebuild.需要注意的是这样的分析是不会收集索引的统计信息的。2 ） validate struct

17、ure有二种模式： 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

18、 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(欢迎下载8精品文库deptno number(2) primary key,dname varchar2(14),loc var

19、char2(13)cluster emp_dept_cluster(deptno)八、 建立表分区，将索引数据和表数据分开在不同的表空间上对于逻辑结构的优化，还应将表数据和索引数据分开表空间存储，分别使用独立的表空间。因为如果将表数据和索引数据放在一起，表数据的I/O 操作和索引的I/O 操作将产生影响系统性能的I/O 竞争，降低系统的响应效率。将表数据和索引数据存放在不同的表空间中，并在物理层面将这两个表空间的数据文件放在不同的物理磁盘上，就可以避免这种竞争了。逻辑优化：一、可以对表进行逻辑分割如中国移动用户表，可以根据手机尾数分成10 个表，这样对性能会有一定的作用二、 Sql 语句使用占

20、位符语句，并且必须按照规定编写sql 语句（如全部大写，全部小写等）oracle解析语句后会放置到共享池中如：select * from Emp where name=?这个语句只会在共享池中有一条，而如果是字符串的话，那就根据不同名字存在不同的语句，所以占位符效率较好三、一些耗时的操作，可以通过存储过程等在用户较少的情况下执行欢迎下载9精品文库数据库不仅仅是一个存储数据的地方，同样是一个编程的地方，一些耗时的操作，可以通过存储过程等在用户较少的情况下执行，从而错开系统使用的高峰时间，提高数据库性能四、 尽量不要整表查询，尽量用where having语句筛选如 select * from Emp，因为要转化为具体的列名是要查数据字典，比较耗时五、选择有效的表名对于多表连接查询，可能oracle的优化器并不会优化到这个程度，oracle中多表查询是根据 FROM 字句从右到左的数据进行的，那么最好右边的表（也就是