Oracle 优化解决方案.doc

Oracle Performance Tuning Solution for Oracle 9Oracle Performance Tuning SolutionFor Oracle 9iVersionAuthorDate1.0Wang Xian2005-12-41.1Wang Xian2005-12-11目 录一、Oracle 高可靠性方案31.备份恢复机制32.Oracle Data Safe Guard63RAID64Stand-By方式75第三方7二、Oracle 高可用性方案81.Oracle RAC82.分布式数据库8三、Oracle 性能调整101CPU issue102Memory issue10i.Share Pool10ii.DB Block Size11iii.DB Cache Size11iv.Log Buffer113Disk I/O issue124Listener issue135Rollback issue146SQL Statement issue14四、Tips141Oracle的锁机制142获得数据库系统时间143获得当前用户的表和视图154获得当前表的列及索引155获得表/表空间的剩余容量156查看当前数据库字符集157查看错误信息15五、一般安装顺序161安装Oracle files, 选择Customer安装。162运行dbca163建立应用表空间：164建立用户：165进入该用户，运行SQL脚本建立数据库对象：166制订备份方案177随着应用，进行性能调整。17一、 Oracle 高可靠性方案1. 备份恢复机制Oracle提供两种备份方案,分别是热备份和冷备份, 热备份需要数据库是归档模式, 冷备份为非归档模式, 归档模式会占用更多的硬盘空间. 热备份可以使用Oracle自带的Rman命令或者通过cp命令将数据文件备份出来,而冷备份则是要求oracle停止运行后,将数据文件备份出来. 另外, Oracle还提供可逻辑冷备份, 也就是通过exp和imp方式将指定的用户或表备份成dmp文件,通常,对于前期应用,一般用exp命令就可以了, 具体命令和参看exp或imp的帮助:$exp help$exp test/test file=test.dmp$imp help$imp test2/test2 file=test.dmp commit=y full=y其中用commit=y可以减少对rollback的占用.如果用户具有dba权限,则可以备份整个数据库,但是一般备份一个具有connect,resource用户的数据就可以了,也就是说按照用户备份就可以了.以下是java每天backup的例子:BackupDaemon.javapackage backup;public class BackupDaemon public static void main(String args) DaemonThread t = new DaemonThread(); t.run(); DaemonThread.javapackage backup;import java.util.Calendar;import java.util.Properties;import java.io.FileInputStream;public class DaemonThread extends Thread private Calendar curr_cal = null; private String BackupPath=c:; private String UserName=develop; private String Password=sms; private String ConnString=smsdb; public DaemonThread() try Properties MyProperties = new Properties(); java.io.InputStream in=this.getClass().getResourceAsStream(perties); MyProperties.load(in); this.BackupPath=MyProperties.getProperty(backpath); this.UserName=MyProperties.getProperty(username); this.Password=MyProperties.getProperty(password); this.ConnString=MyProperties.getProperty(connection_string); catch (Exception e) e.printStackTrace(); Log.print(启动后台线程); curr_cal = Calendar.getInstance(); public void run() while (!interrupted() try java.util.Calendar cal = Calendar.getInstance(); if (cal.get(Calendar.YEAR) curr_cal.get(Calendar.YEAR) | (cal.get(Calendar.MONTH) curr_cal.get(Calendar.MONTH) | (cal.get(Calendar.DATE) curr_cal.get(Calendar.DATE) curr_cal = cal; /调用处理方法 Log.print(开始进行数据库备份); String strExec = cmd.exe /c start exp +UserName+/+Password+ConnString+ file=+BackupPath+Log.getDate(yyyyMMddHHmmss)+.dmp+ log=+BackupPath+Log.getDate(yyyyMMddHHmmss)+.log+ statistics =none; Process child = Runtime.getRuntime().exec(strExec); sleep(5000); catch (Exception e) e.printStackTrace(); Bpertiesbackpath=c:username=testpassword=testconnection_string=db下表显示了不同的备份的对比:Rman通常应用到归档的热备份上,具体命令可以参考:/archives/2005/10/oracle_howto_use_rman.html但是操作比较复杂.同样, 很多第三方的公司也提供了Oracle Backup Tools,通过这些工具可以非常方便的完成Oracle的备份和恢复功能,例如: VERITAS提供的备份工具.2. Oracle Data Safe GuardOracle企业版提供了dataguard工具可以将主Oracle的数据通过RedoLog备份到backup server中, 且数据库必须是归档模式.具体配置方法请参考:/ha/dataguard-step-by-step.htm/source/oracle/20031213/creatoracledatabase.html3 RAID通过磁盘的RAID可以完成数据的冗余,但是建议用Raid卡和SCSI硬盘能提高Disk I/O的速度.RAID Level 0即数据分割，是最基本的方式。在一个普通硬盘驱动器上，数据被存储在同一张盘的连续扇区上。RAID 0至少使用两个磁盘驱动器，并将数据分成从512字节到数兆字节的若干块，这些数据块被交替写到磁盘中。第1段被写到磁盘1中，第2段被写到磁盘2中，如此等等。当系统到达阵列中的最后一个磁盘时，就写到磁盘1的下一分段RAID Level 1是磁盘镜像写到磁盘1中的一切也写到磁盘2中，从任何一个磁盘都可以读取。这样就提供了即时备份，但需要的磁盘驱动器数量最多，不能提高性能。RAID 1在多用户系统中提供最佳性能和容错能力，是最容易实施的配置，这最适用于财务处理、工资单、金融和高可用数据环境RAID Level 5类似于Level 0，但是它不是将数据分成块，而是将每个字节的位拆分到多个磁盘。这样会增加管理费用，但是，如果一个磁盘出现故障，则它可以更换，数据可以从奇偶和纠错码中重建。RAID 5包括所有的读写运行。它需要三到五个磁盘来组成阵列，最适合于不需要关键特性或几乎不进行写操作的多用户系统。一般可以做成RAID5或者RAID0+1或者RAID1+0.RAID5是读快写慢,有数据冗余.RAID0是读快写一般,无数据冗余.RAID0是读一般写慢,有数据冗余.4 Stand-By方式通过第三方的软件,例如linux cluster软件来达到Oracle Stand-by的方式,他是两个Server共用一个磁盘阵列, 通过linux cluster软件来完成浮动IP的功能,一个Server是Run的时候另外的Server是待机状态,当一个Run Server由于硬件的问题出现错误后,linux Cluster回自动将待机状态的Server启动, 在切换过程中的事务处理将失败.5 第三方例如CJDBC Driver, 可以将数据写到多台数据库中,而在查询的时候仅仅取其中一个数据库的数据, 其URL如下:/二、 Oracle 高可用性方案1. Oracle RACOracle企业版加Oracle RAC产品可完成两个或多个机器同时访问位于一个磁盘阵列的数据,从而完成load balance的功能, 请参见:/source/oracle/20040718/STR_building%20your%20RAC%20in%20Linux%20and%20FireWire(1).htm2. 分布式数据库可以将不同的业务分别放在不同的数据库中,数据库与数据库之间通过Dblink, 同义词( 位于远程服务器上表的一个别名)以及实体化视图完成之间的通信.通过这种方法,可以将不同的业务分布在不同的数据库上,从而分解数据库压力,例如,可以将OLTP的应用放在一台Server或一个Server Cluster中, 而将另外一台Server或Server Cluster用做OLAP应用.建立DB Link的语法如下:CREATE SHAREDPUBLIC DATABASE LINK link_name CONNECT TO CURRENT_USER USING connect_string其中connect_string是本地tnsnames.ora里配置的远程数据库的连接字符串建立好DB Linke就可以建立相应的同义词了, 其语法如下:CREATE OR REPLACE PUBLIC SYNONYM schema.synonym FOR schema.object dblink这样就可以和访问本地的表一样的方式访问远程的表了.具体语法请参见:/ora/index.html需要注意的有如下几点: 查看多个Oracle Server的系统参数(通过select * from v$parameter where name=global_names;)如果值是true,则通过 alter system set global_names=FALSE scope=both修改为False 修改多个DB Server中$ORACLE_HOME/network/admin/tnsnames.ora, 分别增加远程数据库的连接别名,例如:SMSDB = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 50)(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = SMSDB) ) )如果SERVICE_NAME没有,也可以使用向下兼容的方式连接,例如SMSDB = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 50)(PORT = 1521) ) (CONNECT_DATA = (ORACLE_SID = SMSDB) ) )其中IP为50为远程Server的ID,SMSDB为远程DB的SID,当配置完后,则通过sqlplus system/managersmsdb来测试是否能连接到远程服务器.如果不写smsdb,而是直接写sqlplus system/manager, 则不通过listener而直接进行本地连接,也就是说,即使listener没启动,通过sqlplus system/manager一样可以连接数据库. 通过sqlplus XXX/XXX 进入本机用户,创建远程数据库的DB Link:Create DATABASE link server2 connect to system identified by mangager using SMSDB;其中server2为db link名,system/manager是远程数据库的用户名和密码.通过如下命令验证DB Link是否创建成功:Select * from tabserver2 创建同意词,以获得全程数据库一个table的别名Create SYNONYM tab2 for tabserver2这样,访问tab2其实就是访问远程数据库的tab表了,并且所有操作可以在一个事务处理,而不需要Java的JTA支持.三、 Oracle 性能调整1 CPU issue尽量使用多CPU的SMP系统，这样能加快Oracle的运算速度以及提高Process的性能。2 Memory issue内存优化主要是针对Oracle SGA（Oracle System Global Area）的优化, 基本原则是针对一个Oracle DB Server而言，SGA的大小应该接近服务器本身内存的一半， 而Oracle SGA的大小主要是由Share Pool 和DB Block Cache决定。SGA的大小不能超过OS本身的限制,这个限制由OS参数shmmax来决定.通过如下语句可以得到当前系统SGA的大小：进入system用户:SQLselect sum(value)/(1024*1024) from v$sga查看系统各个参数值可以通过如下语句:SQL select * from v$parameteri. Share PoolShare Pool 为Oracle SGA中的一部分存储区域，他是用来存储字典表，解析SQL以及存放存储过程，Trigger的地方，如果应用比较复杂，需要大量不同的SQL，存储过程来解决业务逻辑，则应该调整Share Pool的大小。可以通过如下命令动态调整share pool大小,并将值永久写到spfile中,这样在每次启动oracle都会使用新的shared_pool_size大小了.Alter system set shared_pool_size= 50331648 scope=both当应用产寻运行一段时间后,可以通过如下语句来检查是否应该扩大shared_pool的大小.Select sum(pins) “EXECUTIONS”,sum(RELOADS) “MISSES”,sum(reloads)/sum(pins) “RELOAD RATIO” from V$LIBRARYCACHE;如果Reload Ratio 1 则应该调整shared_pool的大小.ii. DB Block Size定义一个块的大小，他可以是2K,4K,8K,16K,64K,针对OLTP而言,查询的记录相对较少,因为可以为4K或8K,而针对OLAP而言,表的记录通常都比较多,可以使用16K或64K,该值可以在安装数据库的时候指定.iii. DB Cache SizeDB Cache是用来缓存表记录的,该值越大,则缓存的记录就越多,速度也更快.通过如下命令可以调整DB Cache的大小:Alter system set db_cache_size = 25165824 scope=both可以通过如下SQL获得Cache 的hit ratio:Select 1-(phy.value-lob.value-dir.value)/ses.value “CACHE HIT RATIO” from v$sysstat ses,V$sysstat lob,v$sysstat dir,v$sysstat phyWhere =session logical readsAnd =physical reads directAnd =physical reads direct (lob)And =physical reads;如果该值偏小,则可以适当扩大cache的大小iv. Log Buffer该参数指明分配给SGA中的日志缓冲区的字节数，该参数值较大时，可减少日志I/O的次数。对于繁忙的系统不宜采用大于或等于64K的值。缺省值般为数据库块的4倍。3 Disk I/O issue 用快速的磁盘,比如SCSI 用Raid0或5,从而条带化来缓解对一个磁盘的压力 用Raw Device可以提高访问速度,有些OS是支持Raw Device的,这样Oracle就可以绕过File system直接读取硬盘信息 尽量不用自动扩展的表空间,从而方便管理 一个表空间是由多个数据库文件组成的,为方便备份,一个文件最好设置成1G 针对小表的OLTP系统,可以将所有小表都放在一个tablespace中,并设置storage,这样,如果建立表时没有storage子句,则他会默认使用tablespace的子句 针对大表或大表的索引应该分别放在一个tablespace中,从而方便管理 尽量不要使用CLOB或BLOB大对象 尽量不要使用Type结构 尽量不要动态创建数据库对象和使用动态SQL 尽量使用number做为PK 尽量不要使用双PK Storage子句对表和表空间同时有效, Storage的语法如下:STORAGE options Options INITIAL int K | M NEXT int K | M MINEXTENTS int MAXEXTENTS int MAXEXTENTS UNLIMITED PCTINCREASE int FREELISTS int FREELIST GROUPS int OPTIMAL OPTIMAL int K | M OPTIMAL NULL BUFFER POOL KEEP|RECYCLE|DEFAULT对与大表,建议单独指定其的initial值,从而使Oracle分配一个比较大的存储空间给这个表,避免碎片产生,next应该等于initial值,或更大,pctincrease 设置为0.对于基础表,可以将Buffer Pool设置成keep或通过如下语句:Alter table a cache;将表放在缓存中,从而提高性能.例如:create table a (aa varchar2(20) storage( initial 10K); 针对特大的统计原始表,应考虑用分区的设置. (只有企业版里有).表空间可以通过如下方法查询:select * from dba_tablespaces表可以通过查询Select * from user_tables 针对SMP系统,可以增加DBWriter的Porcess数量来提高Disk Write的能力.Alter system set db_writer_processes=2 scope=both 针对频繁update的表,应将PCTFree设置的大些,默认是10,如果是只读的,或者更新的很少,可以将PCTFree设置的少些,来节省空间 针对insert的表,应将PCTUSED的值设置的大写,默认是40.4 Listener issueOracle 支持专用服务器和共享服务器的监听器。默认为专用服务器，专用服务器是将客户端的连接直接与Oracle进行连接，而共享服务器则在客户端和Oracle之间有个中间层的服务负责数据库的连接，专用服务器速度快，稳定性差，适合小数量的连接，且他消耗的系统资源相对较少，而共享服务器更适合频繁的大数量的连接，稳定性好，基于Web Application, 由于有Connection Pool的支持，建议使用专用服务器，并且建议Connection Pool使用应用服务器本身的连接池而不要使用自己写的连接池。以system的身份连接上数据库，可以通过如下命令监视数据库的连接情况：Select * from v$session如果在Web Application运行后，该表的记录持续增多，则要考虑Web Application是否正确释放连接。通过查询select * from V$open_cursor来查看是否有statement没有关闭.通过如下命令，可以强制关闭某个客户端的连接，并且回滚他的所有操作，通常可以强制解决死锁问题：Alter system kill session SID, SERIAL#通过如下语句调整Session的数量和open_cursor的数量Alter system set processes=200 scope=both;Alter system set open_cursors=500 scope=both;Session= processes*1.1;而Process的数量由OS的SEMMNI参数决定.5 Rollback issueRollback segment是用来存储一个事务的脏数据的， 事物本身越大，则需要更大的回滚表空间支持。 Oracle 9i采用自动管理回滚段机制，因而不必考虑配置回滚段。只要根据实际需要加大回滚表空间即可。可以通过如下命令增加回滚表空间大小。Alter tablespace UNDOTBS1 add datafile size 1024M;6 SQL Statement issue 针对查询使用一般的索引 针对列值相对较小的使用Bitmap索引,比如性别,可能的列值为male,和female. 查询的where子句的顺序应与索引的顺序相同 通过select /*+FIRST_ROWS_10*/ from tab方式优化查询优化器 当where 后的字段使用in, not in , is null , is not null, 或则其他函数时则用不上索引 竟可能的少返回数据,以减少网络流量 适当结转过期数据四、 Tips1 Oracle的锁机制Oracle本身是行锁，而SQL Server缺省是表锁。2 获得数据库系统时间Select sysdate from dual;如果需要返回特定格式的系统时间，可以通过如下语句：Select to_char(sysdate,yyyy-mm-dd) from dual;Select to_char(sysdate,yyyy-mm-dd hh24:mi:ss) from dual;3 获得当前用户的表和视图Select * from tab;4 获得当前表的列及索引SQLselect * from user_tab_columns;SQLselect * from user_indexes;5 获得表/表空间的剩余容量select * from dba_data_files;select * from dba_free_space;select * from user_extents6 查看当前数据库字符集如果是本地化应用，则Oracle的Character set应该为GBK如果是国际化应用，则Oracle的Character set应该为UTF8如果Oracle的Character set为UTF8，则一个中文占3个字节，如果是GBK,则占两个字节。通过如下语句可以获得Oracle的字符集:SQL conn sys/sys as sysdba;