Oracle实例性能优化

ORACLE 实实例性能例性能优优化化 调整内存分配调整内存分配 Memory 调整调整 I O 调整竞争调整竞争 Contention 其它调整其它调整 调整内存分配的步骤调整内存分配的步骤 调整操作系统 提供足够的内存 调整操作系统 提供足够的内存 调整私有调整私有 SQL 和和 PL SQL 区 减少语法分析调用 区 减少语法分析调用 调整共享池调整共享池 调整缓冲区高速缓存 调整缓冲区高速缓存 Cache 调整操作系统调整操作系统 减少分页和对换 减少分页和对换 Paging and Swapping 调整系统全局区 调整系统全局区 SGA V SGA 调整用户内存分配调整用户内存分配 调整私有调整私有 SQL 和和 PL SQL 区区 识别不必要的语法分析调用识别不必要的语法分析调用 减少不必要的语法分析调用 减少不必要的语法分析调用 Open Cursors 用用 ORACLE 预编译器减少语法分析调用预编译器减少语法分析调用 用用 ORACLE 的的 OCI 减少语法分析调用减少语法分析调用 用用 ORACLE Forms 减少语法分析调用减少语法分析调用 调整共享池调整共享池 检查库高速缓存的活动 检查库高速缓存的活动 V LibraryCache select SUM pins Executions SUM reloads Cache Misses While Executing From V LibraryCache pins 执行时 SQL 语句 PL SQL 块和对象定义被存取的总次数 reloads 执行时 库高速缓存未命中的总次数 期望 reloads 接近零 调整 reloads pins 1 时 应减少库高速缓存的不命中率 减少库高速缓存的不命中率减少库高速缓存的不命中率 分配更多的内存给库高速缓存 Init Shared Pool Size Open Cursors 写等价的 SQL 语句 SQL 语句等价原则 在执行中加速对共享在执行中加速对共享 SQL 区的存取区的存取 Init Cursors Space For Time True False 高速缓存会话的游标高速缓存会话的游标 Init Session Cached Cursors 正整数 方法 Alter Session Set Session Cached Cursors 动态起作用 依据 V SesStat 的 Session Cursor Cache Hits 原则 若 Hits 的百分比较低 则增大参数 Session Cached Cursors 调整数据字典高速缓存调整数据字典高速缓存 检查数据字典高速缓存的活动 V ROWCACHE 权限 select sum gets Data Dictionary Gets sum getmisses Data Ditionary Cache Get Misses from VROWCACHE gets Total number of requests for information on the data object getmisses Number of data requests resulting in cache misses 原则 Getmisses gets 10 or 15 调整 增大 Shared Pool Size 调整缓冲区高速缓存调整缓冲区高速缓存 检查缓冲区高速缓存的活动 检查缓冲区高速缓存的活动 V SYSSTAT 权限 权限 select name value from V SYSSTAT where name in db block gets consist gets physical reads db block gets 数据请求总数 consist gets 存取就满足要求的请求 physical reads 造成磁盘数据文件存取的数据请求总数 命中率 1 physical reads db block gets consistent gets 减少缓冲区高速缓存的不命中率减少缓冲区高速缓存的不命中率 原则 命中率 60 or 70 时 方法 增大 DB BLOCK BUFFERS 值 调整调整 I O 的步骤的步骤 减少磁盘竞争减少磁盘竞争 在数据块中分配空间在数据块中分配空间 避免动态空间管理避免动态空间管理 减少磁盘竞争减少磁盘竞争 监控监控 Oracle 的磁盘活动 的磁盘活动 V FileStat 权限 权限 select name phyrds phywrts from V DataFile df V FileStat fs where df file fs file PHYRDS Number of physical reads done PHYWRTS Number of times DBWR is required to write 监控监控 OS 的磁盘活动 操作系统工具实现 的磁盘活动 操作系统工具实现 对 UNIX 和 VMS 多数磁盘每秒 40 次以上 I O 是过度的 分布分布 I O 数据文件和重做日志文件分放在不同磁盘上 大表的数据分区或条状化并分放在不同的磁盘上的不同数据文件中 创建表空间时 在 datafile 子句中指定数据文件 每个文件应在不同的磁盘上 create tablespace trip datafile file on disk1 size 500k file on disk2 size 500k file on disk3 size 500k 创建表时 在 tablespace 子句中指定表空间 在 storage 子句中指定表范围大小 create table striptab tablespace strip storage initial 495k next 495k minextent 5 pctincrease 0 表和索引分放在不同的磁盘上 创建表空间 指定数据文件 创建库表 指定表空间 创建新的表空间 指定另一磁盘上的数据文件 创建索引 指定新的表空间 减少与 Oracle 无关的磁盘 I O 在数据块中分配空间在数据块中分配空间 迁移 migrating 整行移植到新的数据块 链接 chaining 行分成多片 片存于不同的数据块中 动态空间管理 迁移和链接 使性能下降 减少在已有表中的迁移和链接的步骤 1 用 analyze 命令收集信息 analyze table list chained rows 2 查询 analyze 的输出表 select from chained rows where table name 3 消除迁移行 创建一个与已有表相同的中间表来保存迁移行和链接行 create table int as select from where rowed in select head rowid from chained rows where table name 从已有表中删除迁移行和链接行 delete from where rowid in select head rowed from chained rows where 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 链接行不可避免 避免动态空间管理避免动态空间管理 段扩充会降低性能 对初始段动态扩充 段扩充会降低性能 对初始段动态扩充 检测动态扩充检测动态扩充 select name value from V SYSTAT where name recursive calls 避免回退段中的动态空间管理避免回退段中的动态空间管理 指定事务使用的回退段 set transaction use rollback segment 调整竞争调整竞争 减少回退段的竞争减少回退段的竞争 识别回退段的竞争 识别回退段的竞争 V WAITSTAT 权限 权限 select class count from V waitstat where class in system undo header system undo block undo header undo block select sum value from V sysstat where name in db block gets consistent gets CLASS Class of block COUNT Number of waits by this OPERATION for this CLASS of block system undo header 对含 system 回退段头部块的缓冲区的等待次数 system undo block 对含 system 回退段非头部块的缓冲区的等待次数 undo header 对含非 system 回退段头部块的缓冲区的等待次数 undo block 对含非 system 回退段非头部块的缓冲区的等待次数 如果任何等待数大于总请求数的如果任何等待数大于总请求数的 1 则应创建更多的回退段来减少竞争 则应创建更多的回退段来减少竞争 当前事务数建议回退段数 N 164 16 N 328 32 NN 4 但小于 50 减少多线程服务器进程的竞争减少多线程服务器进程的竞争 识别调度进程的竞争 识别调度进程的竞争 V Dispatcher 权限 权限 select network protocol sum busy sum busy sum idle total busy rate from V Dispatcher group by network DCNET 的调度进程接近 0 5 时间紧张 TCP 的调度进程接近 3 时间紧张 检查调度进程响应队列的等待时间 检查调度进程响应队列的等待时间 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 d where q paddr d paddr group by network PADDR Address of the process that owns the queue WAIT Total time that all items in this queue have waited Divide by TOTALQ for average wait per item TOTALQ Total number of items that have ever been in the queue 增加调度进程增加调度进程 init MTS MAX DISPATCHERS run Alter system mts dispatchers 减少共享服务器进程的竞争减少共享服务器进程的竞争 检查共享服务器进程的竞争 检查共享服务器进程的竞争 V QUEUE 权限 权限 select decode totalq 0 No Request wait totalq hundredths of seconds average wait time per requests from V queue where type COMMON select count shared server processes from V shared servers where status QUIT 增加共享服务器进程增加共享服务器进程 init MTS SERVERS MTS MAX SERVERS run Alter system MTS SEVERS 减少并行查询服务器的竞争减少并行查询服务器的竞争 检查并行查询服务器的竞争 检查并行查询服务器的竞争 V PQ SYSSTAT 权限 权限 select from V PQ SYSSTAT where statistic server busy Servers Busy Number of currently busy servers on this instance Servers Idle Number of currently idle servers on this instance Servers Started Total number of servers started on this instance Servers Shutdown Total number of servers shutdown on this instance 增加并行查询服务器增加并行查询服务器 init parallel min servers 减少重做日志缓冲区锁竞争减少重做日志缓冲区锁竞争 检查重做日志缓冲区的空间 检查重做日志缓冲区的空间 V SYSSTAT 权限 权限 select name value from V SYSSTAT where name redo log space requests 应接近零 否则应增大该缓冲区的大小 增大重做日志缓冲区的大小增大重做日志缓冲区的大小 init log buffer byte 增量 5 其它调整考虑其它调整考虑 调整排序调整排序 识别大的排序 识别大的排序 V SYSSTAT 权限 权限 select name value from V SYSSTAT where name in sorts memory sorts disk sorts memory 不需要磁盘 I O sorts disk 需要磁盘 I O 增加排序区大小增加排序区大小 init SORT AREA SIZE 减少自由空间竞争减少自由空间竞争 识别自由竞争 识别自由竞争 V WAITSTAT 权限 权限 select class count from V WAITSTAT where class free list select sum value from V SYSSTAT where name in db block gets consistent gets 若自由块等待次数大于总请求次数的 1 则应减少自由竞争 增大自由空间列表增大自由空间列表 For each data and index segment Oracle maintains one or more free lists lists of data blocks that have been allocated for that segment s extents and have free space greater than PCTFREE these blocks are available for inserts 调整检查点调整检查点 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 checkpo