OracleDBA性能优化8日游笔记

OracleDBA+性能优化8日游笔记——第一天(一) 2009-10-12 11:31 第一天：

11G的优势：自动化（比10G更自动） Ora-600：未知错误

数据库名：默认ORCL，11Grad control会在冲突时只选一个，最好不要重名。 基本管理： 1. 安装软件 2. 建库 3. 启动 4. 建立用户 5. 给用户授权 6. 为表设置存储空间 7. 创建对象（如表）

8. 构建好数据库之后配置网络连接（C与S之间） 9. 数据保护（备份，恢复策略） 10. 性能优化 二

1. 关系数据库基础 8i ows 9internet oas

11G R2，9.28发布 ias

Bea,ERP(需要做2次开发，开发工具IDS) 网格管理：统一管理，全局监控

Oracle提供完整的解决方案：除了游戏和操作系统都有（收购SUN前） 8i ows

9internet oas

9i之后 ias

现在 Bea,ERP(需要做2次开发，开发工具IDS)

主要产品：7.3，8，8i,9i（35%）,10g（60%）,11gR1(R2在9月28号刚出) 5和6（军队可能会有）

Elearning(典型应用：交易平台)

askTom(Tomcat研发小组)

tahiti.oracle.com（文档，如函数参数使用 ） 2. DBA职责：

高收入，稳定

一档 (入门)：3，4000~6，7000 二档（熟悉）：7，8000~12000，15000 三档（架构）：15000+ 第一守则： 1. 备份

升级前，打补丁前，迁移前 2. 谨慎

例：迁移顺序：导出（备份），清空，导入

3. rm是危险的：删文件

先确认该不该删，在之前做提示， oracle的做法：删表空间时and datafile OMF：文件创建删除由oracle自己处理

ASM：oracle自动存储管理（自己的文件系统，操作系统看不到即删不到） 4. 制定规范 尽量减少人为错误 DBA的工作： 1.定期监控

每日，每周，每月（空间计划） 每日：

确认INSTANCE状态正常 检查文件系统的使用（剩余空间） 检查数据库的跟踪告警文件

检查数据库当日备份的有效性，及时调整备份策略 检查数据文件的状态记录不是 “online”的文件 检查空间的使用情况：PPT给出语句列出空间剩余情况 收集信息 每周：

检查数据增幅非常明显的对象（表空间，数据库空间），优化时优先考虑 系统健康检查

检查无效(一般为被动失效)的对象（视图，过程，包，触发器，索引(表move操作)等，大多因为表）和不起作用（一般为主动禁用）的对象（禁用触发器，约束等）

在批量装载合法数据的时候可以禁用约束，装载数据，再启用约束，触发器类似 每月：

如果使用CBO（10G以后被简化了）

收集统计信息计算代价（例:省时间），10G开始系统自动收集统计信息 检查表空间碎片

寻找数据库性能调整的机会 进行性能调整

提出下一步空间管理计划

10G以后：自监控，自诊断，自调整 预警,相关预警，活动意外事件 其他工作: 备份

灾难测试

应对领导需求变更

根据新需求设计新的数据库架构方案 为其他部门提供业务或脚本支持 考虑存储的使用

OracleDBA+性能优化8日游笔记——第一天(二) 2009-10-12 11:32 三．安装和新特性 OUI安装工具

DBCA建库删库 DBUA升级库 LC监听器 OEM基本管理 SQL*PLUS R-Man备份恢复

Data Dump数据泵（导出，导入） SQL*Loader把文本数据导入到Oracle SQL PLUS

SQL PLUS FRO WINDOWS (SQLPLUSW) ISQL PLUS 11G:

SQL PLUS DEVELOPER （JAVA编写） DV数据库安全审计：控制谁可以使用 AV操作安全审计：控制怎么使用 删除特定的产品或功能 支持新的SYSASM角色

硬盘空间:3G~5G

临时空间至少400M（安装时解压缩用） 核心参数:以下2个一定要设

共享内存段最大尺寸Shmmax = 4294967295（建议至少设置物理内存的50%，SGA50%~70%） 信号量：决定进程数 Semmns 最小不低于100，建议设为proesses*2+10 环境变量以下一定要设

ORACLE_BASE:所有软件的基目录

ORACLE_HOME:特定版本软件的主目录(当前默认的主目录) 主要目录：HOME,BIN,NETWORK,RDBMS

ORACLE_SID:决定当前默认连接的数据库是哪个：set oracle_sid = orcl,show parameter instance_name 以下不一定要设

NLS_LANG：决定语言，地区，字符集

ORA_ NLS33：10和11指向nls\\date目录，指向全球化特性支持文件，用locale bulidee可以看

PATH:系统搜索路径，把ORACLE放前面（其实很重要，只是不属于oracle的参数） LD_LIBRARY_PATH:指向java库的路径 DISPLAY 工作站名（IP）：0.0

数据库设置

比较重要的初始化参数(建库前确定，建完后不能改或改动会有影响)： Db_block_size块大小(数据仓库设小点,块大点I/O就会少点) Db_cache_size Shared_pool_size Log_buffer

Insrance_name Db_name

Processes静态（需要重启数据库），决定并发会话数

其他建库时需要注意的设置：

数据库字符集（大部分不能改，除非从子集变超集） 数据库名（用特殊工具才能改） 安装步骤： 1. 检测环境

2. 配核心参数（LINUX不用重启） 3. 建立用户和组 4. 在用户下设置环境变量 5. 调OUI安装数据库软件

Windows用setup,Linux and UNIX 用runinstaller 6. 配置网络以及其他选项 11G的升级：

提供脚本进行升级前的分析 简化升级过程

升级速度变快了（并行编译） 升级后状态工具

OracleDBA+性能优化8日游笔记——第二天(一) 2009-11-06 15:17

由于出差很忙,每天早9点到晚11点半的工作,而且没有周6,日的休息,所以实在抽不出时间整理笔记和写BOLG,现在开始继续补上出差前的OracleDBA+性能优化笔记

第二天的笔记不太好整理,因为这里包括了前4天的OracleDBA阶段的最重要的部分——Oracle体系架构,那一部分我会贴图说明,所以需要花点时间,必要的话还会再细分几部分,敬请期待.

-----------------------------------------------------------------------------------------------------------------------------------------

第二天

Emctl start dbconsole Sqlplus / as sysdba Show parameter MEMORY 设置SGA+PGA的总值 8i静态SGA

9i动态SGA

设置SGA_MAX_SIZE 一小段连续内存区， 10g SGA自动调整

设置参数SGA_TARGET（0为禁用自动管理，有效值为启用自动管理）

SGA_TARGET可以超过SGA_MAX_SIZE（重启后SGA_MAX_SIZE会自动调整为超过的SGA_TARGET值，所以实际应用中SGA_TARGET最多等于SGA_MAX_SIZE） Alter system set sga_target=600m; 11g 内存自动调整

进程：MMAN

Sga:可以自动调整的内存区，只能动态调整的内存区，只能静态调整的内存区，其他（fixed size,查看命令show sga） 一．PL/SQL开发的新特性

曾经，逻辑上在未受重新定义影响的对象会失效（XX锁），11G以后不会失效 Update（同时有独占的行锁和共享表锁），11G后DDL可以等待DML锁定释放 DDL_LOCK_TIMEOUT初始化参数，初始为0，NOWAIT LOCK TABLE命令新增了WAIT[]子句 一些命令不在用排他锁而使用共享排他锁 不可见索引：no_index

查询结果的高速缓存（类似存储用的二级缓存），用内存空间换时间 经常使用的查询语句并且查询结果不变

临时表空间收缩

以前要减少临时表空间就需要删再建 注意，临时区会干扰自动收缩 临时表的控制

临时表不存真正存数据（数据存到临时表空间中），没锁，没日志，没回滚信息，

commit后就没，过去数据放在用户所在的临时表空间，11G后可以指定 更易于恢复丢失的SPFILE（参数文件）

其他高级新特性：ACFS,ADVM代替裸设备，自己的时间同步服务，列式压缩，物化视图的改善，物化视图的效率改善等 Sybase用列式存储（用于数据仓库

OracleDBA+性能优化8日游笔记——第二天(二)之Oracle体系架构详解

\体系架构\为前4天的OracleDBA课程中最重要的部分,因此特别传上图片供大家理解,不过文字的部分依然是我当初的原版笔记,所以尽管有图,但能从我笔记中理解多少靠各位自己了.

-----------------------------------------------------------------------------------------------------------------------------------------

二．Oracle体系架构 架构图：

Oracle由Instance（实例）和database（一堆文件）构成。在oracle的所有资料里，database指的是文件。

Instance（实例）+database（文件）= DB SERVER（由Instance（内存，进程），database文件构成）

Database中，黄框里为必要文件

1. 数据文件

用户数据 用户表 DML

数据字典数据（系统数据） 数据字典表 随数据库自动生成，不能对其直接进行修改 修改其中的数据用DDL，任何一条语句都要用到数据字典（语句执行步骤，数据解析，数据返回，取操作，解析获取对象信息和权限信息都需要数据字典） 特点：

大小可变，

表空间就是一个或多个数据文件的逻辑存储表现，

一个数据库最少需要一个表空间（system,所有数据字典表被创建在系统表空间）,建立多个表空间为了把数据分类存放

2. 重做日志文件（redo logfiles）：

按时间顺序存放数据库中所发生的所有改变（只要数据块发生变化就叫改变，表数据块，索引数据块，回滚数据块等） 谁，什么时间，在哪个块的第几行的第几列。 通过日志恢复，提高数据库的可恢复性 Oracle做物理级恢复的原理就是恢复日志 用日志恢复比语句恢复要块 特点：

大小不变，可恢复性，顺序循环，一个数据库至少2组日志，每组至少一个组员文件，如果多个组员文件则为文件级镜像（日志复用）

3. 控制文件（Control files）

很小，几十M算比较大的，但很重要

内容多（但每一个都很小）以下是几个主要内容 数据库的基本信息 名字/ID

数据库的结构信息 数据文件，日志文件的名称，位置，大小，状态 最后一次同步的SCN信息

SCN:系统改变号，一改变就递增，每个SCN对应一个改变，SCN描述数据库的运行

同步：先将当前SCN之前的所有脏数据写入磁盘中，之后把SCN头写入磁盘，突发原因，丢数据丢同步之后的日志，数据库正常关闭同步一定会发生，非正常关闭，同步不发生，从日志中找最后一次同步的SCN之后的日志恢复

日志切换立刻发生同步，该日志对应的脏数据没完成同步之前不允许覆盖

同步判断，实例恢复 当前的日志序列号

日志切换递增

每个序列号代表唯一的日志内容

日志恢复到当前的日志序列号恢复（最后一次同步的SCN信息为起点，此为终点） 介质恢复的终点

介质恢复（介质文件头的SCN为起点，当前日志序列号为终点）

归档信息

RMAN备份信息（放在可变大小，之上放在固定大小），

4. 初始化参数文件（parameter file）(非必要文件)

定制的参数文件，决定实例的属性

无参数文件前提，用RMAN可以启动实例（用默认参数启动实例，用来恢复参数文件（启动实例）和控制文件（启动数据库）），SQLPLUS不行

5. 口令文件

存放：特权身份的口令 作用：特权身份验证

特权身份：超越系统身份之外的身份（建立删除启动关闭数据库） 普通身份：nomal

特权身份：sysoper,sysdba,sysasm(11G) 拥有以上所有身份的用户为sys 口令文件认证，

操作系统认证（在服务器本地做连接，以在那个组中的用户做连接ora_dba组）

想保护数据库，先保护服务器

6. 归档文件

存放：日志历史 作用：为介质恢复使用

个数：如果一开始就是归档模式（数量为当前日志序列号数-1） 大小：小于等于重做日志文件大小

命名：名称中必须包含日志对应的序列号（不用SCN命名因不完全恢复影响），如果是RAC，名称中还要包含日志所对应的线程 离线的，对数据库本身不影响

Instance:

1. SGA

公用内存区：主要有6个

必选：共享池，buffer区，redo Log buffer区

可选：java池（包括JVM则选择），large池（RMAN备份与恢复需要用到，并行操作的时候会用到），流池 共享池（SHARE POOL）：

Library cache:

暂存最近最常使用的SQL语句文本，分析代码，执行计划 作用：让代码共享，减少解析（解析消耗CPU） Data dictionary cache： 最近最常使用的数据字典信息

作用：减I/O（省略去系统表空间找数据字典的I/O）加快解析效率 数据缓存区：

暂存最近最常使用的数据块 作用加快执行效率 重做日志缓存区： 先进先出 暂存日志记录 作用：

很快写入内存，很快改数据（系统先记日志再改数据）

插入数据的修改次数（表+索引）*2（因为回滚段也被插入） 加快操作效率

2. 进程

必要：PMON,SMON,DBWR, LGWR,CKPT 非必要：ARCn等 PMON：进程监视进程 User process:用户进程

Server process:响应客户端请求的服务器进程 PMON监视上面2者的会话（server process） 处理连接故障异常的（突发事件回滚） SMON：系统监视进程 系统异常崩溃（或断电）

检测同步

实例恢复

前滚应用日志，打开数据库，回滚没完成事务

先回滚需要的，按需回滚，之后遇到再回滚

DBWR：数据库写进程 LGWR：日志写进程

CKPT： 做同步 日志切换时候

数据库正常关闭的时候 等

ARCn：归档进程

3. PGA：私有的内存区

被每一个session私有 存会话信息 堆栈空间 游标状态

排序空间（如果排序空间小于排序数据，通过读写临时表空间来排序：直接路径读和直接路径写，频繁的I/O操作），数据仓库要大排序空间

OracleDBA+性能优化8日游笔记——第二天(二)之Oracle体系架构补充与总结 2009-11-10 16:02

以下是对前面的Oracle体系架构详解的补充与总结..总结的部分可以结合前面那个帖子的图去看

--------------------------------------------------------------------------------------------------------------------------------------------------

连接到一个Oracle的实例：如下图 建立一个用户连接 创建一个会话

总结：

连库是连接到instance

RAC,一个instance对应一个database，一个database对应多个instance（多个主机的instance共享一个database，扩展并发会话）,连接负载均衡（会话分到多个主机），高可用（会话连接直接切换），缺点是存储只有一套，锁（RAC加块锁）与I/O不能分享，容易形成竞争和瓶颈，所以对查询有利，对修改不利

三种连接方式：本地连接，网络连接（通过NET），三层连接（通过中间层服务器B/S）

物理结构：数据文件，控制文件，重做日志文件

内存结构：系统全局区（SGA），程序全局区（PGA）

内存分配基本单位是粒度（granule）看大小：select * from v$sgainfo

在自动管理模式下，设置buffer缓存区小于原值，则设置的最小值，大于则改成该值 大池：推荐专用模式（资源利用不充分），如果是共享模式的使用（大量并发，每一个会话里操作时间远远小于挂起时间，如网站,以及想省钱的时候(免除了中间件)）

自动管理依赖于反馈信息（有延时）

SGA，物理内存的50~70%，PGA设置20~40%，总大小不超过90%。

会话建立时产生（用户进程，服务器进程），实例启动时产生（后台进程）

必选：DBWn 可选：ARCn，Cjq0(作业调度进程)，Jnnn(作业进程),RECO(分布式恢复进程),MMAN（内存管理进程，自调整）,MMON（内存监控进程，自收集），Snnn（共享模式下的共享服务进进程），Dnnn（共享模式下的调度启用进程），Pnnn

进程启用时机：

DBWn（数据库写进程）：发生检查点，脏缓存达到限制（时间（人为控制）*速录（系统知道）=限制），没有自由的缓存（没找到），超时发生（时间到了没找到），RAC ping请求，表空间离线，表空间只读，表被删除或截断，开始备份表空间 LGWR(日志写进程)：提交的时候（写日志不写数据，都能保证数据不丢失，日志写的比数据写的快，丢数据可能性越低），日志缓冲区达到三分之一满（像漏斗，先

进先出，进快出慢，多留缓冲区防止被写满被造成的等待），日志大小到1M（减少数据损失），每隔三秒（减少损失），在SBWn写之前（先写脏数据相关的日志）

SMON（系统检测进程）：

作用：实例恢复，释放临时表空间

PMON（进程监测进程）：

作用：清除失败的进程，如果是共享模式则负责重启死掉的调度器，动态注册

监听器。

CKPT（检查点进程）：

时机：日志切换，数据库正常关闭，一些参数，表空间操作（如离线）等 DML操作：

改数据：锁定数据，产生回滚的日志，产生回滚的信息，修改数据，索引维护等

OracleDBA+性能优化8日游笔记——第二天(三) 2009-11-23 17:43 三．管理oracle实例 初始化参数文件

启动的时候使用，SPFILE（稳固参数文件）和PFILE（静态参数文件） 两种类型的参数：显式，隐式 设置参数：直接设置，借助默认值 根据特性：静态参数，动态参数 系统级参数，会话级参数

可以存在多个参数文件（只有一个有效） PFILE：

文本文件（静态）

Db_1\\database目录下，11G应该只有SPFILE没有PFILE（用下面命令可以手动导入） Create pfile from spfile(反之也可以，只有特权用户可以使用该命令) SPFILE: 二进制文件 由Oracle服务器维护 一般存在于服务器上

能用RMAN备份 区别：

PFILE静态改文件(当时没用重启有效)，动态修改用alter system set（当时有效重启失效） SPFILE 动态静态都用alter system set。。。。。用scope设置状态（both,memory（动态）,spfile(静态)），SPFILE能用RMAN备份，用*符号使所有节点上的实例能使用统一的SPFILE SPFILE优先级比PFILE高，若使用PFILE需要修改SPFILE名。用show parameter spfile查看启用的是SPFILE（有值）还是PFILE（无值）

---------------------------------------------------------------------------------------------------------------------- 启动数据库到OPEN

三个阶段：启动实例（NOMOUNT），打开控制文件（MOUNT），打开控制文件中所包含的所有文件（OPEN）

一阶段从SHUTDOW到NOMOUNT：

读参数文件，分内存起进程

我们能在NOMOUNT做的事情：改参数，建库，重建控制文件（在SPFILE中指定） 二阶段到MOUNT:

读取控制文件,装载数据库的控制信息

我们能在MOUNT做的事情：数据库的备份和恢复，数据文件和日志文件的重命名和重定位（改控制文件的结构信息），归档和非归档的改变

三阶段到OPEN

数据库OPEN之前只有特权身份可以连（普通用户口令需要访问数据字典，数据字典未启动） 关闭数据库 关闭模式:

A=abort;(相当于断电，不安全)I=IMMEDIATE;（常用）T = TRANSACTION；N= NORMAL（慢）

用abort关闭，在下次启动会首先进行实例恢复，有小几率恢复失败，所以慎用

OracleDBA+性能优化8日游笔记——第三天(一) 2009-11-26 14:03 一. 跟踪告警文件

可能会由于大量错误造成该文件很大 管理：

可以直接删除（再有错误会自动建立新文件）， 9和10 在 bdump下，

11，adump是放审计文件，告警放在diag\\rdbms\\orcl11g\\orcl11g\\trace下 告警文件中有重大操作（建库等），报错信息，数据库的启动关闭 通过告警文件可以看到经过abort关闭后，再启动时的实例恢复过程 后台进程跟踪文件： 存后台运行的报错信息 也在trace下

用户进程跟踪文件（ora开头）： 存用户操作的错误信息

在trace下

用户的会话跟踪：

调优用，DBA执行，搜集会话的相关信息

9i，静态开启跟踪，10G可以在实例中动态开启，但一般都在系统级开启 11g，自动诊断 捕捉错误

给予DBA提醒

填一个信息调查表（查询相关bug信息） 如果是BUG，则可以通过OEM直接下载补丁 如果是未知BUG，则打包上传给oracle

自动诊断资料库

ADR：自动诊断仓库基目录（可以设，也可不设）

诊断目录（diag）：

Rdbms\\db名\\SID

Trace目录：存告警文件

Alart目录：XML格式的告警文件 Cdump

Incident:意外事件目录

问题：一个比较严重的错误，如：ora-600

意外事件：一个问题

意外事件状态：正在收集，就绪，正在跟踪，数据已清除，关闭

意外事件处理方式：自己看，提交给oracle处理（通过OEM，目前没人这样做，没人知道响应时间是否及时等） Hm：健康检查信息目录 健康检查器：

脱机（mount状态下可以做）： DB结构完整性检查 数据块完整性 重做完整性

联机（OPEN）：

事务处理完整性：检查如断电情况发生的故障 还原段完整性：检查回滚段是否有故障 字典完整性

通过OEM： 指导中心

OEM可以检查故障和查看报告 ADR只能查看报告，不能做检查

告警文件的查看：

OEM可以查看：

ADR命令行工具也可以查看： Cmd下：adric Show incident

查找告警日志： Show homepath

Set homepath 告警文件路径 Show alart

SQL修复指导： 语句失败：语句报错 执行时语句崩溃，

在ADR中生成意外事件， DBA收到预警，

ORACLE自动产生补丁，或DBA发现进行修补

OEM主目录下有活动意外事件，进入后有SQL修复指导，

指导中心的SQL指导也有SQL修复指导

数据恢复指导：

OEM的指导中心有数据恢复指导：系统通过检查器检查错误，然后按步骤恢复

OracleDBA+性能优化8日游笔记——第三天(二) 2009-11-26 14:06

二. Dbca(创建数据库)

建库的目的： 新需求，新业务

更换版本，导出导入新数据库需要花费时间（导入10个G的DUMP需要花费半天甚至一天），所以，原地升级比较合适， 建库满足：内存，交换，资源，存储空间 建库注意设置SID 建库：命令dbca DBCA工具：

建库，删库，

10以后可以创建ASM实例

连接ASM实例到mount阶段创建一个或多个磁盘组（database部分放在这里） 建库：

选择模板：最大区别以下2个参数不一样

db_file_m参数（单个I/O可以读取的最多数据块数,用在全表扫描上，如数据仓库）

Show parameter db_file_m

Pga参数（OLTP设小，数据仓库设大） Show parameter pga 设数据库名和SID

管理选项，把EM选上勾，要装某软件才能点grid control,自动备份选项只能备份到快速恢复区中 身份证明：

11g提供更严格的口令设置功能（可选，在后面的步骤11安全设置中选择） 字符集一般默认数据库字符集，国家字符集虽然大，但相对的对某个字符的查询速度会变慢，特殊情况才会选择

13步数据库存储：里面可以设置数据文件，控制文件，重做日志文件

生成脚本文件，调用这些脚本才能生成完整数据库windows从.bat文件开始看,这些脚本完整的记录了整个的建库过程

路径：admin\\建好的数据库名\\scripts

10和11数据库创建完成立刻修改口令

OracleDBA+性能优化8日游笔记——第三天(三) 2009-11-26 14:07

三. 数据字典和动态性能视图的使用

存储空间管理，内部对象管理，方案权限管理

数据字典：了解数据库的必要工具，对用户来说是只读的，静态信息，便于了解基本情况，存，数据来自数据文件，数据库没打开之前不能访问数据字典

每个oracle数据库的中枢 描述数据库和它的对象 包含只读的表和视图

存储在SYSTEM表空间中 拥有者是SYS用户 。。。

插曲小BUG：数据库OPEN之前就尝试打开数据字典视图，就会在系统中标记说该视图无法打开，之后再OPEN后受其影响依然无法打开该视图，而基表是可以看的，只要重启后就可以正常打开了,因为没有神经病会在open之前就想打开数据字典所以该BUG从7开始一直留到11g

动态性能视图：也是了解数据库的工具，动态信息，便于了解运行情况，不存，信息来自于内存和控制文件，数据库没打开之前（从nomount状态开始）可以访问 虚表

记录了当前数据库的行为

当数据库操作时，动态性能视图被不断更新 包含了来自内存和控制文件的信息 DBA使用动态性能视图监视调优数据库

动态性能视图被SYS用户拥有

使用V$开头的同义词（动态性能视图用V_$开头）

X$是动态性能表， GV$是全局动态性能视图，

在V$FIXED_TABLE中可以查到 内置数据库对象: 数据字典

基表：看不懂的 DESC USER$ --基表

视图：基表的简化总结和格式化处理，看得懂的 DESC DBA_USERS --视图

通过PUBLIC同义词访问（select_catalog_role权限，详见建库脚本catalog.sql）

数据字典提供下列信息： 逻辑和物理数据库结构 对象定义和空间分配

一致性限制

用户 角色

权限 审计 。。。

数据字典种类：

三类视图：对应的查询语句不一样（见cdsqldll.sql脚本）

DBA：包含所有对象（最高）

ALL：包含当前用户能够访问的对象 USER：当前用户拥有的对象（最低）

DICTIONARY表:字典信息（dict为同义词，还有一个类似的表叫

DICT_COLUMNS）

DESC DICTIONARY；

SELECT count(*) from DICTIONARY 查数据字典，名字都要用大写

动态性能视图 PL/SQL包

数据库事件触发器

OracleDBA+性能优化8日游笔记——第三天(四) 2009-11-26 14:10

四．控制文件与日志文件管理

控制文件管理:

做好复用，保证数据库的健壮性 做好备份，以防万一 控制文件的重要性： 很小但经常要用，

在MOUNT状态读取，操作数据库使用

一个控制文件只跟一个库相关，丢失了一定要恢复 包含数据库信息，结构信息，归档信息等 多重镜像控制文件（复用）：

坏掉一个就会down，找到好的，把坏的删除，改参数（每个文件加单引号，别在引号中敲回车），关库，创建另外的控制文件，启动数据库 用RMAN备份控制文件

获得控制文件信息：V$CONTROLFILE 日志文件管理：V$log 建议：

第一， 需要足够多的日志组

万一检查点或归档没完成不能被覆盖（漏斗概念） 不够就增加

第二， 需要有足够大的组员文件

组员文件越小，切换越频繁（应该10分钟以上），I/O越频繁 删除组员文件，建新的组员文件

第三， 日志文件最好做复用，并且复用组员文件到不同的存储位置上

便于I/O分散，最大化保护日志

日志的管理都跟控制文件相关

日志的大小不能小于一定的值（和块大小有关）

增加组员文件：由于刚增加的时候为空文件，所以不能用（一个组的组员文件必须一样）

删除组（或组员文件）：当前组不能删，当前组的组员文件不能删，如果有唯一用来做实例恢复的组员文件不能删（取决于留下的组员文件有没有完整日志）

注意：只删掉了控制文件中的信息，系统中的文件没有删（RM是危险的，删的时候谨慎对待）

OracleDBA+性能优化8日游笔记——第三天(五) 2009-11-26 14:16

五．空间和数据文件管理

数据存储: 操作系统块：

操作系统I/O的最小单位

数据块：

数据库I/O的最小单位（由于操作系统块往往太小所以用数据块）

数据块由一个或多个连续的操作系统块构成 段：

存储对象（表，索引，回滚段，临时段等）

区：

段的空间空间扩展单位（块太小） 区由连续的数据块组成

组成段的区不一定是连续的

数据存储（图）：

线的两个含义：一对多，只属于

注意：段不能跨表空间（即如表不能跨表空间），区不能跨数据文件 组成段的所有区，必须位于段所在表空间的组成文件上

表空间：

创建表空间，维护表空间，删除表空间 系统表空间：SYSTEM 在数据库创建的时候创建 放了数据字典表 放了系统回滚段

系统表空间最好永远只放数据字典表和系统回滚段（专用） 非系统表空间：其他 分隔不同的段

对用户对象限制使用空间 大文件表空间（新特性）：

最大可包含4G（2^32）个BLOCK 检查点性能提升 简化了管理 一般用不上

缺省的oracle使用小文件表空间

表空间管理:

本地管理表空间（使用bitmap,基本都用这个）和数据字典管理表空间(DMT技术已经被放弃)

系统表空间是本地的，就只能用本地管理，系统表空间是字典的，才可以选择用字典管理表空间 区分配方式：

统一方式性能好，但复杂，大表跟小表放在不同的区上 自动方式简单

建表前确定，之后不能改

表空间类型： 永久的

放永久对象

11G提供在EM中可以加密（一般不用）

可以设置默认永久表空间（避免用户使用系统表空间）

临时的

放临时数据

写在TEMPFILE上

不参与同步操作和同步校验

可以设置默认临时表空间（避免用户使用系统表空间）

说明：

初始创建临时表空间不会真正分配空间 在随后使用中开始分配 由于空间问题可能存在故障隐患

UNDO的

9i以后出现，用来放自动管理的回滚段 有几个instance就需要几个UNDO

数据库中，整个路径作为一个文件的名字，后缀是骗人的

表空间状态改变：只读，读写，offline,online,可以用命令也可以用OEM做 只读表空间：

Create table user.tab(a char(10)) tablespace t1; Alter tablespace t1 read only; SELECT * FROM user.tab --成功 INSERT INTO --失败 添加字段b --成功

由于操作的是系统表空间SYSTEM，所以不妨碍t1 删除字段b --失败

第一删字典信息

第二在块中释放空间，所以要操作t1表空间 修改字段b类型 --成功 修改a为varchar2(10) --成功 可变长

修改a回char （10） --失败 不可变长，修改了表空间

删表 --成功

删表只是删除数据字典，数据只在建表在分配空间的时候把以前的覆盖 建表 --失败

OFFLINE表空间:

Alter tablespace table offline

与onlyread区别在于，offline不能读，其他上述操作效果一样 改变表空间大小：表空间由一个或多个文件组成 自动扩展

方便但性能不太好 文件满了扩展 增加文件

10以前删除表空间文件很难

10以后可以允许单独的删除文件，但必须为空文件且不是唯一的文件 扩展文件大小

变大可以不受限制

变小不一定变到已存在的文件大小，处在数据中间的空闲空间不能被减少 手动扩展为主，辅助自动扩展

文件不易过多，也不易过大，平衡处理 存储位置的改变： mount以后可以改 表空间必须离线 目标文件必须存在

OPEN下：

ALTER TABLESPACE 表空间名 RENAME DATAFILE ‘源文件’ TO ‘目标文件’

--文件要在，而且要通过验证，先将源文件复制到目标路径再执行命令

MOUNT下：

将源文件复制到目标路径再执行命令（注意命令不同）：

ALTER DATABASE 表空间名 RENAME FILE ‘源文件’ TO ‘目标文件’

--用DATABASE而不是TABLESPACE是因为后者是对字典的操

作，字典只在OPEN以后才能用

修改表空间位置命令执行会很快：因为只修改一个值 删除表空间：

系统表空间不能删

有激活段的表空间不能删

主表还被字表参照的时候，要删主表需要加一个命令属性

OracleDBA+性能优化8日游笔记——第四天(一) 2009-12-04 14:29

一． 段区块的管理

段：

段的分类（常用）： 表（TABLE）：

单表单段：段不能跨表空间，所以表也不能跨表空间 表分区（TABLE PARTITION）： 单表多段：跨空间的表 容量大

数据访问性能好

WHERE中经常出现中的字段分区 分区方法：

Range:范围分区（可以按时间，地点等） Hash:散列分区

按散列算法把散列值写入对应位置，所以谓之不明 等值运算比较快

List:列表分区（特殊的范围分区） oracle允许分区上构建子分区 存放数据量比较大的信息 聚簇（Cluster）： 多表单段

有公用值字段的放一起

把a,b表相关的数据交织的存在一起 存储空间少，I/O少

加快多表查询速度（聚簇连接） 单表访问使I/O增多（慢） 数据操作开销大 索引（INDEX）：

通过树形结构能明显降低查找需要的I/O 通过关键字和rowid查找

索引组织表（Index-organized table）： 与索引的区别是存的关键字和数据

基于主关键字查询速度快，对其他字段的查询速度慢 LOB段（LOB segment）:

大字段，推荐使用LOB代替LONG LOB容量大（9i到4G，10G到128TB）

一个表只能有一个LONG，但一个表可以有多个LOB Long行内存储（造成单行数据很大） LOB单独拿出来形成LOB段，在行中留一个指针

回滚段（Undo segment）：

暂存事务中的原始数据 读一致性

闪回（跨事务的读一致性，需要延长保留时间由某参数决定） 临时段(Tempporary segment)

区(Extent)：

区是在表空间中被段使用的大块空间 段在创建，扩展，修改的时候区被分配 段在删除，修改，截断的时候区被释放

DELETE：只删数据不释放空间，不会重置高水平线 Truncate：删数据释放空间，重置高水平线 块：

块状态：可用（可插入），不可用（不能插入，但是可以修改删除） 最小的I/O单位

由一个或多个操作系统块构成 在数据库创建时设定

一个空由块头（上），空闲区（中），数据区（下），块头由上向下递增，数据区由下向上递增

INITRANS参数:初始事务槽设置太高浪费空间，太少不够用 PCTFREE参数：为update操作预留的空间 设小会发生行迁移，设大了浪费空间 PCTUSED参数：

Freelist放所有可用块的地方

当剩余空间小于PCTFREE，块不可用，之后当使用空间小于PCTUSED，块再次可用

自动段空间管理（ASSM）：解决freelist中的块竞争 使用BITMAP管理

创建表空间之前确定，创建之后不能改

新的数据库创建表空间的时候一般会设成ASSM

OracleDBA+性能优化8日游笔记——第四天(二) 2009-12-04 14:34

二． 用户权限的管理

用户管理 创建用户

确定用户存放对象的表空间

确定每个表空间的配额（现在用的很少，用权限管理而不是用配额） 指定默认和临时表空间 创建用户

为用户分配权限和角色 修改口令:

特权用户可以修改，普通用户可以修改自己的口令 口令安全性在11G提高了：

过去不分大小写，11以后区分大小写（取决某参数） 11以后的口令支持更多的字符数（如中文） 11以后能设置登陆失败（N次）的锁定天数 口令复杂性校验脚本：utlpwdmg.sql

比如：口令与密码：正着不能一样，反着也不能一样（11g新加）

得到用户信息：DBA_USERS表

权限管理

系统权限： 有100多个 ANY对所有用户 GRANT授予权限 REVOKE移除权限

级联授予权限，非级联移除 对象权限：

用户对自己的对象具有完全的对象权限

有些（如UPDATE）权限可以详细到字段级（grant update(字段名)）

级联授予权限，级联移除

创建表和索引需要空间使用权限 角色管理 创建角色

把权限授予角色 角色授予用户 预定义角色

CONNECT角色10以后只有CREATE SESSION权限 RESOURCE角色

UNLIMITED TABLESPACE（表空间使用权）权限不能被直接赋予，

表空间使用权限只要被grant resource就通过触发器自动赋予 DBA角色等

级联跟系统级联一样

设置默认用户的默认角色 启用/禁用角色

通过SET ROLE启用或禁用角色 默认角色是自动启用的 从用户身上移除角色 删除角色

OracleDBA+性能优化8日游笔记——第四天(三)对象管理 2009-12-04 14:53

三． 对象管理

1. 表管理（作为段的管理） 命名规则：

字母开头 30字符以内

a - z,A-Z,0-9,#,_,$,汉字等 不能使用保留关键字 不能重名对象

特殊字符或保留关键字可以用“”括起来（非标准命名） 为适应其他数据库的保留关键字的折中方法 用“”括起来的名字在数据字典中区分大小写

字段类型：

内建类型

字符串，数字，时间，

BFILE类型数据在数据库外面存（数据库有一个指向的指针） 用户定义类型

删除违反约束的行：

脚本在Utlexcpt.sql

在脚本最后加入alter table enable constraints xxx exceptions into exception; ROWID格式：

限制型ROWID：过去使用

48进制，16个字符，分成8，4，4 三部分（块编号，行编号，文件编号）

扩展型ROWID：现在使用（文件多了）

64进制，18个字符，6363 四部分（数据对象编号，相对文件号，块编号，编号）

Desc dbms_rowid 创建表：

CREATE TABLE tab( ?. TABLESPACE ts_name) 手动分配区：

ALTER TABLE t ALLOCATE EXTENT(SIZE 500K DATAFILE ‘路径/名’)

知道要一次装载一大批数据的时候使用 表的重组:

ALTER TABLE tab_name MOVE TABLESPACE data1； 把表放到需要的表空间上去 可以让松散的表重新变的结构紧凑

整理表空间

做move占2倍的表空间大小

做move会让所有访问这张表的程序都失效（重编译即可） 做move会让索引失效（需要重建） 截取表（清空表）：

TRUNCATE TABLE 。。。；

如果使用DELETE会给表和索引带来大量的日志，维护等 删除表：

注意对应的视图，触发器等

2. 索引管理

索引对查询可能有帮助，但对DML操作一定有负面影响 希望在第一次查询的时候过滤掉尽量多的字段 在经常在where中出现和唯一值较多的字段建索引

逻辑上:

单列索引或组合索引：

看where中经常一起出现的用组合（全包括用组合的，否则用单列的）

唯一或非唯一索引：

唯一索引在DML时，会在索引维护的时候再做一次唯一校验

函数索引 物理上：

分区索引和非分区：

大表分区，小表非分区 B-TREE索引：

叶子块有序存储索引记录

三部分（索引头，关键字，rowid）

索引层次跟记录数，块大小以及索引记录大小有关 分裂：

根块满后放每一个叶子块的“中间值”，

叶子块满了先做同层分裂，直到根块满后（中间值放满）才做第

3层分裂

小块8K（每行数据对应的索引条设40k），第一层放200，第二层放40000，第三层放800W依次类推 取数据量少走索引，取数据量大走全表扫描 对于包含OR操作符是低效的

多用于OLTP

位图索引

关键字+rowid范围+位图

Bit值和关键字值是一一对应 适合唯一值少的字段

对于包含OR操作符是高效的（2个BITMAP做位操作） 最适合多个位图索引之间的‘与’操作

在update的代价很高 多用于数据仓库

绝对不会是唯一性索引

创建索引:提示

平衡查询和DML的需求

将索引放在单独的表空间

对于大索引考虑使用NOLOGGING

索引的INITRANS（事务槽）应该比表的INITRANS（事务槽）设高些 维护索引：

手动分配区：

ALTER INDEX i ALLOCATE EXTENT(SIZE 500K DATAFILE ‘路径/名’)

重建索引：

ALTER INDEX tab_name REBUILD TABLESPACE data1； 把索引放到需要的表空间上去

可以让松散的索引重新变的结构紧凑 整理索引

让失效的索引生效 删除索引：

准备装载一批数据之前

删除很少使用的索引，需要的时候再重建 删除并重建失效的索引

可以监视索引判断是否失效，只能普通用户做

OracleDBA+性能优化8日游笔记——第四天(四) 2009-12-04 14:58

从第五天开始将会正式进入性能优化的部分

----------------------------------------------------------------------------------------------------------- 四． 网络管理

服务器端管理： 监听器的管理

Conn hr/hr@ABC

ABC = 解析（协议 主机 端口 SID）

单个监听器可以监听多个数据库的链接请求 多个也可以监听一个数据库的链接请求 监听器支持多种网络协议 监听器的名字必须唯一

无论有多少监听器，所有监听器配置信息都放在一个listener.ora 监听器的注册： 静态服务注册 配置lintener.ora 动态服务注册

PMON进程进行注册

OEM对动态注册支持不好

建议即使有动态注册，也最好配静态注册 管理监听器:命令行工具LISRCTL 客户端：配置

Easy connect(解决一次性连接的简单链接，为不常做链接的用户准备)

连接类型：默认（一般选这个），专用模式，共享模式，池中服务器（很少见）

高级：

地址配置：

选择同时连2个地址 选择随即尝试连接

数据库性能优化前瞻--开发人员应注意的几点问题 2009-12-08 17:34

此文章属于我的《OracleDBA+性能优化8日游》系列之一，该系列分为2部分，即：前4天的\部分\与后4天的\性能优化部分\。之所以要在即将开始的\性能优化\之前放出这样一篇文章，是因为\笔记\终究是\笔记\，其中有一些值得介绍的东西都被一带而过，尤其是有搞开发朋友正在面临SQL优化的任务，因此我特别针对一些对开发人员有帮助的优化要点在这里提前做一个略微详细的介绍。。下面进入正文

-----------------------------------------------------------------------------------------------------------------------------------------------

一.对于数据库要优化些什么？

为提高速度而优化的对象也有很多，比如网络，硬件，数据库等等。由于这是一篇面向开发人员的数据库优化的文章，所以这里只关注数据库。

简单的说，数据库的优化实际上就是3部分：对资源使用的优化（内存与磁盘空间），I/O的优化以及对竞争的优化。

其中I/O优化是开发人员重点关注的，因为一条SQL语句的执行速度，主要取决于其执行过程中的I/O次数，不管你怎样理解，对SQL的优化实际上就是对I/O的优化。

其次，对有限内存的使用也是开发者经常考虑的问题，原因很简单，在内存中执行的速度一定比在磁盘中快，而当内存分配不够的时候，系统便会使用磁盘来代替，实际上对磁盘的操作就会消耗多余的I/O，所以这又说明了I/O优化的重要性

最后是竞争的优化，也就是锁的概念，一般来说普通开发者没有处理锁的权限，但他们在程序的设计上可能会考虑到这个问题。 二.优化到什么程度结束

很多人一但开始优化，不在速度上达到极致就不会罢休，实际上，优化是一个找平衡的过程，把一点做到极致就会在其他方面出现缺陷，你可以去看一下百度和谷歌首页的源代码，你会发现所有的代码被写成了一行(我最近一次看的时候其实是4行，但我怀疑是编辑器显示的问题)，并且所有的命名都是很简单的\等，简单的说这就是为了达到最极致的速度而付出的代价，当然还包括省钱，在每天成百上千万的流量面前，多出1个回车或一个字节的字符需要付出的代价都是以\千万倍\为单位计算的。

所以，当一方面的优势最大程度体现出来的同时其缺点没有明显暴露的时候，就是你可以结束优化的时候，另外，出于各方面的考虑，一般达到了客户要求的指标之后就可以停止优化了。 三.慎用触发器

前几天和一位学弟聊天，谈到了他们的数据库老师教给他们的一些知识，其中提到这样一点，\存储过程与触发器都是很常用的数据库技术\。

的确，触发器在理论上也算是很常用的数据库对象，但是，它同时也是一个要尽量少用的对象，原因如下：

1.触发器是\隐式执行\的，它非常不利于维护。什么是隐式执行？简单的说就是它由系统控制而非由你的程序控制。当你执行程序时发现结果与预期不一样，然后去检查你的程序，却在代码中看不到一点问题，检查了3天3夜才发现

原来在系统里还有一个触发器导致了问题出现，这绝对是一件让人非常郁闷的事情。

2.触发器对于性能的影响是相当可观的。触发器针对每一条DML语句都会触发一个事件，这就意味了，你更新1万条数据，触发器就会被执行1万遍，万一触发器中还包含复杂的逻辑判断，万一触发器中还有涉及到对表的查询，万一这个查询还特复杂，万一.......

结论，尽量使用存储过程代替触发器 四.索引的适用范围纠正

网上很多文章对于普通索引的使用范围中，都有一条：查询出来的数据量占整张表数据量的5%以内适合采用索引，最多不能超过10%。

上面那句话其实是有问题的，真正的数字应该是2%以内适合使用索引，最多不要超过5%，具体的计算过程会在以后的性能优化8日游笔记中给出来。 五.\的使用要三思

很多人会在做用于模糊查询的SQL的时候用上\语法，但是这里必须要小心的是，该语法当首字母不确定的时候就不会走索引，所以在写SQL的时候要小心。

六.函数导致的普通索引的失效

这点特别要注意，我在上周刚被这个问题困扰过，比如一个有索引的字段叫a，那么where a = XXX,这种条件可能会走索引，而把该字段包上一个函数则一定会让索引失效，比如where to_char(a) = XXX，解决方法是，如果允许的话可以增加一个函数索引，否则得话，尽量改变你的写法，让to_char(a) = XXX 变成a = XXX。在这里我贴出前几天遇到的问题以及解决方法拿出来做一个例子： 为了节省字数，下面这个SQL省略了SELECT和FROM的部分，只展示WHERE中必要的条件，该条件是对比2个表中的一个叫\的时间字段，比较2个时间的年和月是否相同，这是一个informix数据库的语法： SELECT ... FROM ...

WHERE extend(tpw_spw_c2_sum.first_result,year to month)=extend(te_time.first_result,year to month)

由于被套上了函数，所以原本在first_result字段上的索引失效了，

tpw_spw_c2_sum表的数据量有240万行，此SQL的运行时间长达50秒。 于是为了让它走索引，我将WHERE改成了下面这个样子：

WHERE tpw_spw_c2_sum.first_result between te_time.first_result and extend(to_date((extend(te_time.first_result,year to month)+interval(1) month to month)||'-1 00:00:00'),year to day)-interval(1) day to day 虽然代码比较复杂，但重要的是，我让这个语句走了索引，SQL的执行速度达到了4到8秒的速度

上面是一个修改写法让索引生效的例子，如果增加函数索引和修改写法都无法做到的话(或者水平有限做不到),则可以考虑多增加一个走索引的条件，先通过索引过滤掉一大批的数据，这样也会让速度快一些。 七.注意代码书写规范

Oracle对SQL的执行顺序是：\自内而外，自右而左，自下而上\，先执行最里面的子查询，这点不用说了，当然数据库优化器可能会改变你写的SQL的结构，但这不是这里要讨论的东西。自右而左和自下而上其实是一个东西，比如你将WHERE写成一行，Oracle会先按照最右边的条件过滤，如果用回车将不同条件分成不同行，那么就会优先执行最下面的过滤条件。所以，将能过滤最多数据量的条件放在最下（右）面（最大限度避免重复处理数据），将数据量最多的表放在最上（左）面（FROM a,b 在内部进行表连接的时候b表全表扫描，a表走索引），是一个很不错的习惯。

另外，注意代码规范的理由除了\可读性\外，还有一点，就是对数据库的库高缓存（属于共享池）会有很重要的影响，这里稍微解释下，简单的说就是，数据库会把某些常用SQL语句连同其查出来的数据一起放到内存中，这样可以让下次执行同样的操作的时候大幅度提高速度，但是这样的方便的功能必须要满足一些很苛刻的条件，那就是SQL语句的格式和查询结果必须一模一样，哪怕一个回车、空格或不同的大小写都会导致该SQL不共享,这是规范SQL书写的很重要的一点。

八.不要在代码中设置太长的事务

很多人习惯在处理完所有的事情最后在commit，这个习惯会存在隐患，涉及原因包括回滚段，突发事件等诸多因素，如果事务太长，考虑一下将它分解成小事务，当然了，确实需要很长的事务也不要勉强拆分。 九.尽量将业务处理放在前台业务逻辑层，将数据处理放在后台

这点在itpub论坛上和人家争论了很长时间，有些例子不是举不出来，实在是不想举，这纯粹是个人原因。重点在讨论的中心，有人提倡将所有的事情都仍在数据库以\架空前台\，但实际上我本人不提倡这种做法，尽管现在数据库的功

能相当强大，但原则上应该让数据库做尽量简单的事情，由于这点有争议，所以我只希望各位在遇到实际问题的时候将这个说法作为一个参考，而不是否决其他说法。

但是，无论争论的有多激烈，有一点是当时争论双方的共识，如果你的程序涉及到的大批量的更新操作，要将它放在数据库中去做，比如说前几天一个同事就遇到了这个问题，它用C#编写一个程序，其中有一个用循环去Insert的部分，但是这样循环插入2000条数据就已经让它对速度无法忍耐了，所以来找我写一个存储过程解决问题，这一点要记住，如果涉及到上面相同的情况，一定要把这一部分仍给数据库去做。

十.清空全表使用TRUNCATE代替DELETE

这里涉及到一个高水位线的问题，各位可以自己去试试，往一个表里插20W的数据，然后SELECT *一下，假设用了10秒，那么DELETE FROM table，把所有数据删除掉，再SELECT *一下，会发现明明没数据却还要花10秒钟，原因就是不管有没有数据，全表扫描都会扫描到最高水位线的位置，DELETE是不会重置这个水位线的，具体原因以及什么叫最高水位线这里不做解释。全表清空的语句用\表名\代替\表名\的写法 十一.减少物理读

不太恰当但比较容易的理解是，逻辑读是内存读取数据，物理读从磁盘读取数据，前面说了，内存快磁盘慢，所以要尽量减少物理读，很多人喜欢使用oracle的dual表，但是要记住，所有对dual表的操作都是物理读 十二.慎用DBLINK

尽量避免远端连接，对远端表的访问基本都是全表扫描

OracleDBA+性能优化8日游笔记——第五天(一)性能优化部分 2010-01-06 15:54

已经拖了很久了，从这次开始是性能优化部分

----------------------------------------------------------------------------------------------------------- 第五天

性能优化：

Set autotrace on[off]; --看执行计划 优化思路：知道从哪方面下手 分析：（核心）

收集信息（用工具收集）

找瓶颈（性能指标的判断） 分析原因

选择解决方案（调优）

对知识面和知识体系的理解要广和深 数据库优化： 内存调优 I/O调优 竞争调优 应用调优：

应用语句调优 SQL调优

SQL书写调优 SQL算法调优

所有通过dual表做的查询，每次都是硬解析 应用存储对象调优 索引等 一． 优化思路

优化怎么做:调资源

把会带来竞争的操作从时间和地点上分散 竞争意味着得到的快，得不到的就慢 例如：

从时间上：备份放晚上做（消耗I/O，减少竞争） 从地点上：在前台消耗客户端的CPU，在后台消耗服务器的CPU

越早越好

充分合理的利用资源

让每个动作只去使用自己应用的CPU

从数据库到算法，各个层面的设计达到最好 调什么：

基于数据库的整个应用系统 架构

前台应用 数据库

应用数据库的网络 数据库的操作系统 磁盘

谁调优：参与项目的各阶段的所有人 需求分析人员 设计人员 开发人员 存储管理员 网络管理员

数据库管理员（关键）

大部分性能问题集中在与数据库的交互上

系统管理员

需要各级人员之间的交互沟通 调优的类型：

设计期的调优 正式运行之前

预定义调优（假想式调优） 直到压力测试通过结束 周期性调优（主动式调优）

数据量和并发访问量达到一定程度的时候开始 周期性

根据周期性的收集数据，判断指标是否衰落来调优 被动式调优：

运行过程中出现了问题（如客户提出语句执行慢了） 什么样的系统需要调优：

有数据有人用的系统（生产系统） 很少有人用的一般不需要调优 调优到什么地步结束：

调优是寻找平衡的过程（可能会影响其他动作） 把长处发挥出来同时不让短处变明显 达到性能指标就结束 调优的阶段：

应用设计和开发 功能模块的设计 应用算法

功能模块的划分

做大量逻辑判断的放前台

处理大量数据的部分放后台做 E-R模型的设计

拆分（第三范式）

适当的冗余对性能有好处（性别，政治面貌等） 冗余大考虑拆分

经常在一起访问的不拆，不经常一起访问的可以拆 冗余大又经常一起访问的，根据冗余的唯一值个数拆分（少则拆）

拆多了消耗CPU（外键校验），拆少了冗余多 表设计：

如CHAR与VARCHAR2： 看字段的长度差别有多大 应用开发： 代码规范

书写规则（大小写，回车换行）

有助于写出相同的语句（数据库只做一次解析） 辅助开发人员写好语句 帮开发人员制定数据规范

LIKE查询的首字母确定会走索引，不确定不走索引 数据的大小写，排放顺序 从开发人员了解情况:

查询多还是数据操作多 表的重要性 并发数据多少 。。。

数据库配置

配置参数（instence） 表空间划分(database) 部署应用

创建对象（表，索引等）

选择对象类型和相关参数 测试：

功能测试，性能测试，压力测试 周期性调整 低效时调整

应用调优>设计调优>数据库调优>系统调优>其他 应用与数据库：需供关系

数据库与操作系统：需供关系 调优的常见问题:

糟糕的会话管理（通常跟中间件有关）

糟糕的游标管理（通常是开发人员的错误导致） OPEN忘CLOSE

糟糕的关系设计（通常是没有遵守好规范/或过度遵循规范） 糟糕的应用算法设计（通常与功能模块设计）

糟糕的数据库架构设计（通常与DBA部署数据库有关） 典型安装不调参数 糟糕的语句算法设计

OracleDBA+性能优化8日游笔记——第五天(二) 2010-01-06 15:57

调优阶段2： 调优设计

设计人员和开发人员 调优应用

DBA和开发人员 调优内存 调优I/O 调优竞争

调优操作系统 数据库调优：

Instance：内存，进程（DBWR，LGWR等） Database

最基本原则：

如果某部分不是瓶颈，就不要尝试优化

优化可能会过头，注意协调整个系统的性能

优化时为系统提供足够的资源并且充分合理的使用资源，而不是无节制的扩充资源

优化有时候也意味着合理的分配或者划分任务 性能优化的基本步骤：

1.设立合理的性能优化目标（确立目标） 减少或消除等待

访问最少块数（减少数据访问量） 写好语句 选好算法

把需要的数据放入最少的块中 将数据保留在内存中（I/O减少） 相应时间 吞吐量

单位时间内的数据处理能力 负载量

并发访问量 恢复时间

2.测量记录当前性能（收集信息） 数据库的信息

Statspack报告 统计信息 等待信息 操作系统的信息 IO,内存，CPU 应用级信息

3.确定数据库的瓶颈（等待什么） 平衡性能与数据库的健壮性的需求 影响性能的因素： Raid方式

Raid-5对写操作影响比较大

Raid-1浪费空间单对写操作影响小 复用控制文件

复用重做日志文件 频繁的执行检查点

看系统忙不忙决定 备份数据文件

从频度上减少影响 执行归档

如数据不重要，写操作多，不用归档 异地容灾等 4.确定OS的瓶颈

5.分析瓶颈原因 6.优化所需部分

7.跟踪并实施更改过程 记录实施之前的状态

逐个实施调优方法而不是同时实施大量方法 实施完了，运行已段时间后重新收集统计信息 8.测量并记录当前性能

9.重复3-7，直到满足优化目标 信息收集工具： 操作系统：

CPU：Top（当前时刻）/sar（历史值）等 RAM：vmstat IO:iostat

数据库：(从上到下最新顺序) 动态性能视图 累计值 顺时值

Utlbstal.Sql/ Utlestal.Sql 一段时间内的值 Statspack（9i）

需要安装，不需要了卸载而不是删除

Statspack本质是一个用户和其下的一堆对象 一段时间内的多点信息 图形化

按“最“方式排序

10以后，可以生成STS（造成高负载的语句的结果集） AWR（自动负载仓库）/ADDM（自动数据库诊断监控） 自动收集

收集主机信息

AWR自动调ADDM产生分析报告， AWR 用MMON进程进行收集，

默认每小时收集一次（快照），保留7到8天 存在sysaux表空间，被sysman用户拥有 ADDM发现问题

用OEM在自动工作量资料档案库中修改 收集级别：

BASIC（收集最少） TYPICAL（常用） ALL（收集最多）

BEGIN和END报告中间不能跨越重启 在指导中心能查看ADDM和其他指导 自动的东西可以参照，但不要完全相信 等待事件

如果不用参数就能描述竞争位置就不要参数，否则要参数

V$EVENT_NAME

BUFFER_BUSY_WAITS参数

段头块竞争（freelist不够）， 段中块竞争（初始事务槽不够）， 回滚段竞争 空闲等待：

正在等待某动作发生（网络等待） 必须等待 非空闲等待：

数据库发生了竞争

高速缓存忙等待等 统计事件的视图：

V$SYSTEM_EVENT（系统级） 主动优化

数据库建立到现在

一个事件的所有会话统计 V$SESSION_EVENT（会话级）

比SYSTEM_EVENT多了个会话ID 被动优化

从会话建立到现在 V$SESSION_WAIT（瞬时）

正在发生或刚刚结束的等待事件 重点关注等待事件时间长，次数多的 块等待v$waitstat

OEM的tuning包和diagnostics包 自动性能优化指导（10G） SQL自动调整（11g） 应用级：

Autotrace SQL trace 。。。 辅助相关文件

告警日志文件（关系很小） 用户进程跟踪文件

执行计划和统计信息（做SQL调优） Alter session（收集自己的） ORADEBUG 第三方工具

故障诊断和调优相关视图： 系统级

竞争：锁，内存锁，回滚段 会话级

性能监控的解决方案 查动态性能视图

Statspack AWR ADDM

查看等待，看是什么等待，分析

OracleDBA+性能优化8日游笔记——第五天(三) 2010-01-06 16:18

二．数据库优化 内存优化

增加内存：

性能提高越来越不明显， 理论增加到平滑点为止

需要内存远远大于物理内存：增加到拐点以上 两种方法判断平滑点和拐点： 尝试不断增大

使用9i内存大小建议 调整内存管理方法

数据高速缓冲区

暂存最近最常使用的数据块 块状态：

空的

Free：用过清空的 脏的

正在访问的 块维护：

LRU list Dirty list

SGA

数据缓冲区

影响执行效率

由多个独立的子缓存区和多种块大小(非标准块)的缓存区组成

DB_BLOCK_SIZE决定了标准块的大小 非标准块：

先设置非标准块内存区，再设置块大小 调优目的：

服务器能够在缓存区中招到数据

OLTP中要求90%的命中率（最好95%） 查命中率在运行一段时间后查 单池命中率：通过V$sysstat计算 多池命中率

一个性能糟糕的数据库命中率同

样能达到99%

当算法正确的情况下命中率高性能越好

命中率低了一般都不好 使用多池：

常用池 不常用池 其他池 定义多池 使用多池：

设置存储对象的存储特性 Keep池：

设置大小：所有常用块数的总大小 命中：99% RECYCLE池：

放不常用的块 命中：50% DEFAULT池

命中：90% 用CATCH表

用CATCH子句创建表 用CATCH子句修改表

用hint:SELECT /*+CATCH*/ * FROM 表

共享池

影响解析效率

减少大量的硬解析就减少了CPU消耗

对于大量重复执行的SQL语句进行解析优化

库高缓存

暂存最近最常使用的SQL语句文本，分析代码，执行计划

让我们能不解析就不解析

在解析前先算hash value，通hash value找相同的文本信息等

V$librarycatch（算命中率判断代码共享量）

命中率和重载率决定影响

没共享原因： 所有动态SQL都是硬解析

对dual表的访问肯定是硬解析

共享池太小

调大

语句本身不共享

调代码

语句看上去一样，但值不一样（输入条件不一样）

建立代码规范

绑定变量代替

ORACLE先解析，后绑定，再执行

语句一样，但之前对表结构做了DDL操作

最好不要在业务繁忙期做对象维护

11G新特性，当语句跟表相关，对表结构的改变对语句没有影响的时候不失效

V$SGASTAT查看大小

V$SQL，V$SQLAREA等视图查看SQL代码共享情况

看上去一样的SQL语句，哪怕多个空过或换行或某个大小写不一样都不能共享

完全相同的语句，值也相同，哪怕不是同一个会话也可以共享

11g让以上操作造成的失效率变低了

其他调优方法

防止碎片： 为大的内存需求保留空间

碎片会造成内存不够用，使大程序把频繁访问的小程序挤出缓存区，造成重载

SHARED_POOL_RESERVED_SIZE

默认共享池大小的10%

最大不能超过共享池大小的50%

将被频繁访问的大对象保留（pin）在内存中

EXECUTE dbms_shared_pool_.keep(‘package_name’)

EXECUTE dbms_shared_pool_.onkeep(‘package_name’)

影响库高缓存的其他参数：

OPEN_CURSORS 一个用户最多可打开的游标个数

执行的语句不一样且很多，就要调高

CURSORS_SPACE_FRO_TIME

空间换时间

空间打开不释放

有很多语句在频繁调用的时候用

字典高速缓存区

在不得不解析的情况下加速解析

内容：对象的定义 V$ROWCATCH SUM（GETMISSES）/ SUM（GET）应该小于15%

在共享模式下配置足够大的大池存放UGA

UGA会在共享模式下从SGA跑到PGA

重做日志缓冲区

跟日志产生的操作有关

日志的I/O操作可能比数据操作造成的I./O总量要大的多

以写为主的系统，日志可能会是一个性能平均

等待事件

本文来源：https://www.bwwdw.com/article/rtm3.html