ORACLE实例性能优化.doc

ORACLE实例性能优化l 调整内存分配（Memory）l 调整I/Ol 调整竞争（Contention）l 其它调整调整内存分配的步骤l 调整操作系统（提供足够的内存）l 调整私有SQL和PL/SQL区（减少语法分析调用）l 调整共享池l 调整缓冲区高速缓存（Cache）调整操作系统l 减少分页和对换（Paging and Swapping）l 调整系统全局区（SGA：V$SGA）l 调整用户内存分配调整私有SQL和PL/SQL区l 识别不必要的语法分析调用l 减少不必要的语法分析调用（Open_Cursors）l 用ORACLE预编译器减少语法分析调用l 用ORACLE的OCI减少语法分析调用l 用ORACLE Forms减少语法分析调用调整共享池l 检查库高速缓存的活动（V$LibraryCache）select SUM(pins) “Executions”,SUM(reloads) “Cache Misses While Executing”From V$LibraryCachepins: 执行时，SQL语句、PL/SQL块和对象定义被存取的总次数reloads: 执行时，库高速缓存未命中的总次数期望：reloads接近零调整：reloads/pins1%时，应减少库高速缓存的不命中率l 减少库高速缓存的不命中率分配更多的内存给库高速缓存（Init：Shared_Pool_Size、Open_Cursors）写等价的SQL语句（SQL语句等价原则）l 在执行中加速对共享SQL区的存取Init: Cursors_Space_For_Time=True/Falsel 高速缓存会话的游标Init: Session_Cached_Cursors(正整数)方法：Alter Session Set Session_Cached_Cursors=*（动态起作用）依据：V$SesStat的“Session Cursor Cache Hits”原则：若Hits的百分比较低，则增大参数Session_Cached_Cursorsl 调整数据字典高速缓存检查数据字典高速缓存的活动（V$ROWCACHE：权限）select sum(gets) “Data Dictionary Gets”,sum(getmisses) “Data Ditionary Cache Get Misses”from VROWCACHEgets: Total number of requests for information on the data objectgetmisses: Number of data requests resulting in cache misses原则：Getmisses/gets10% or 15%调整：增大Shared_Pool_Size调整缓冲区高速缓存l 检查缓冲区高速缓存的活动（V$SYSSTAT：权限）select name, value from V$SYSSTATwhere name in (db block gets, consist gets, physical reads)db block gets: 数据请求总数consist gets: 存取就满足要求的请求physical reads: 造成磁盘数据文件存取的数据请求总数命中率=1-(physical reads/(db block gets + consistent gets)l 减少缓冲区高速缓存的不命中率原则：命中率60% or 70%时方法：增大DB_BLOCK_BUFFERS值调整I/O的步骤l 减少磁盘竞争l 在数据块中分配空间l 避免动态空间管理减少磁盘竞争l 监控Oracle的磁盘活动（V$FileStat： 权限）select name, phyrds, phywrtsfrom V$DataFile df, V$FileStat fswhere df.file# = fs.file#PHYRDS: Number of physical reads donePHYWRTS: Number of times DBWR is required to writel 监控OS的磁盘活动（操作系统工具实现）对UNIX和VMS，多数磁盘每秒40次以上I/O是过度的l 分布I/O数据文件和重做日志文件分放在不同磁盘上大表的数据分区或条状化并分放在不同的磁盘上的不同数据文件中创建表空间时，在datafile子句中指定数据文件，每个文件应在不同的磁盘上create tablespace tripdatafile file_on_disk1 size 500k,file_on_disk2 size 500k,file_on_disk3 size 500k,创建表时，在tablespace子句中指定表空间，在storage子句中指定表范围大小create table striptab()tablespace stripstorage (initial 495k next 495k minextent 5 pctincrease 0)表和索引分放在不同的磁盘上创建表空间，指定数据文件创建库表，指定表空间创建新的表空间，指定另一磁盘上的数据文件创建索引，指定新的表空间减少与Oracle无关的磁盘I/O在数据块中分配空间迁移（migrating）：整行移植到新的数据块链接（chaining）：行分成多片，片存于不同的数据块中动态空间管理（迁移和链接）使性能下降减少在已有表中的迁移和链接的步骤：1. 用analyze命令收集信息analyze table * list chained rows2. 查询analyze的输出表select * from chained_rows where table_name=*3. 消除迁移行创建一个与已有表相同的中间表来保存迁移行和链接行create table int_* as select * from *where rowed in (select head_rowid from chained_rowswhere table_name=*)从已有表中删除迁移行和链接行delete from *where rowid in (select head_rowed from chained_rowswhere table_name=*)把中间表中的行插入已有表中insert into *select * from int_*删除中间表delete table int_*4. 从analyze的输出表中删除相应表的信息delete from chained_rows where table_name=*5. 再用analyze命令收集信息，并查询输出表6. 在输出表中出现的行是链接行，可增大数据块长度注意：long, char, varchar2链接行不可避免！避免动态空间管理段扩充会降低性能（对初始段动态扩充）l 检测动态扩充select name, value from V$SYSTATwhere name = recursive callsl 避免回退段中的动态空间管理指定事务使用的回退段set transaction use rollback segment *调整竞争减少回退段的竞争l 识别回退段的竞争（V$WAITSTAT：权限）select class, count from V$waitstatwhere class in (system undo header, system undo block,undo header, undo block)select sum(value) from V$sysstatwhere name in (db block gets, consistent gets)CLASS： Class of blockCOUNT：Number of waits by this OPERATION for this CLASS of blocksystem undo header：对含system回退段头部块的缓冲区的等待次数system undo block：对含system回退段非头部块的缓冲区的等待次数undo header：对含非system回退段头部块的缓冲区的等待次数undo block：对含非system回退段非头部块的缓冲区的等待次数l 如果任何等待数大于总请求数的1%，则应创建更多的回退段来减少竞争当前事务数建议回退段数N16416=N32832NN/4, 但小于50减少多线程服务器进程的竞争l 识别调度进程的竞争（V$Dispatcher：权限）select network “protocol”, sum(busy)/(sum(busy)+sum(idle) “total busy rate”from V$Dispatchergroup by networkDCNET的调度进程接近0.5%，时间紧张TCP的调度进程接近3%，时间紧张l 检查调度进程响应队列的等待时间（V$QUEUE：权限）select network “protocol”,decode(sum(totalq), 0, no responses, sum(wait)/sum(totalq)|hundredths of seconds) “average wait time per response”from V$queue q, V$Dispatcher dwhere q.paddr = d.paddrgroup by networkPADDR：Address of the process that owns the queueWAIT：Total time that all items in this queue have waited. Divide by TOTALQ for average wait per itemTOTALQ：Total number of items that have ever been in the queuel 增加调度进程init：MTS_MAX_DISPATCHERSrun：Alter system mts_dispatchers减少共享服务器进程的竞争l 检查共享服务器进程的竞争（V$QUEUE：权限）select decode(totalq, 0, No Request, wait/totalq|hundredths of seconds) “average wait time per requests”from V$queuewhere type = COMMONselect count(*) “shared server processes”from V$shared_serverswhere status != QUITl 增加共享服务器进程init：MTS_SERVERS, MTS_MAX_SERVERSrun：Alter system MTS_SEVERS减少并行查询服务器的竞争l 检查并行查询服务器的竞争（V$PQ_SYSSTAT：权限）select * from V$PQ_SYSSTATwhere statistic = server busyServers Busy:：Number of currently busy servers on this instanceServers Idle：Number of currently idle servers on this instanceServers Started：Total number of servers started on this instanceServers Shutdown: Total number of servers shutdown on this instancel 增加并行查询服务器init：parallel_min_servers减少重做日志缓冲区锁竞争l 检查重做日志缓冲区的空间（V$SYSSTAT：权限）select name, value from V$SYSSTATwhere name = redo log space requests应接近零，否则应增大该缓冲区的大小l 增大重做日志缓冲区的大小init：log_buffer （byte：增量5%）其它调整考虑调整排序l 识别大的排序（V$SYSSTAT：权限）select name, value from V$SYSSTATwhere name in (sorts(memory), sorts(disk)sorts(memory)：不需要磁盘I/Osorts(disk)：需要磁盘I/Ol 增加排序区大小init：SORT_AREA_SIZE减少自由空间竞争l 识别自由竞争（V$WAITSTAT：权限）select class, count from V$WAITSTATwhere class = free listselect sum(value) from V$SYSSTATwhere name in (db block gets, consistent gets)若自由块等待次数大于总请求次数的1%，则应减少自由竞争l 增大自由空间列表For each data and index segment, Oracle maintains one or more free lists-lists of data blocks that have been allocated for that segments extents and have free space greater than PCTFREE; these blocks are available for inserts.调整检查点l LOG_CHECKPOINT_INTERVAL：设置为大于重做日志文件数LOG_CHECKPOINT_INTERVAL specifies the frequency of checkpoints in terms of the number of redo log file blocks that can exist between an incremental checkpoint and the last block written to the redo log. This number refers to physical operating system blocks, not database blocksl LOG_CHECKPOINT_TIMEOUT：设置为零LOG_CHECKPOINT_TIMEOUT specifies that the incremental checkpoint is at the position where the last write to the redo log (sometimes called the tail of the log) was integer seconds ago. This parameter also signifies that no buffer will remain dirty (in the cache) for more than integer seconds. The value is specified in seconds.Decode（值替换函数）DECODE(value, if1, then1, if2, then2, if3, then3, , else)对于Value域中的每个值，DECODE从一系列if/then测试中去匹配并替换select Feature, Section, DECODE(Page, 1, Front Page, Turn to| Page) from NEWSPAPER如果页号为1，则替换为“Front Page”，如果页号不是1，则替换为“Turn to”加页号Else可以是一个函数、一个常量或一个其它的列实例结构oracle实例=内存结构+进程结构oracle实例启动的过程，其实就是oracle内存参数设置的值加载到内存中，并启动相应的后台进程进行相关的服务过程。进程结构oracle进程=服务器进程+用户进程几个重要的后台进程：DBWR：数据写入进程.LGWR:日志写入进程.ARCH:归档进程.CKPT:检查点进程(日志切换；上一个检查点之后，又超过了指定的时间；预定义的日志块写入磁盘；例程关闭，DBA强制产生,表空间offline)LCKn（0-9）：封锁进程.Dnnn:调度进程.内存结构(我们重点讲解的)内存结构=SGA（系统全局区）+PGA（程序全局区）SGA：是用于存储数据库信息的内存区，该信息为数据库进程所共享。它包含Oracle 服务器的数据和控制信息,它是在Oracle服务器所驻留的计算机的实际内存中得以分配，如果实际内存不够再往虚拟内存中写我们重点就是设置SGA，理论上SGA可占OS系统物理内存的1/21/3原则：SGA+PGA+OS使用内存总物理RAMSGA=(db_block_buffers*blocksize)+(shared_pool_size+large_pool_size+java_pool_size+log_buffers)+1MB1、SGA系统全局区.(包括以下五个区)A、数据缓冲区:（db_block_buffers）存储由磁盘数据文件读入的数据。大小: db_block_buffers*db_block_sizeOracle9i设置数据缓冲