• 1. Oracle数据库性能监控与开发规范
  • 2. Overview 性能监控与优化 性能监控 问题定位 优化方法
  • 3. 性能监控Database Buffer CacheRedo Log BufferShared PoolSGAServer Process 1客户端Server Process 2客户端客户端Server Process 3会话
  • 4. 性能监控活动会话 会话:SESSION,应用访问数据库的通道 会话状态 活动: ACTIVE, 正在执行SQL 不活动:当前,没有执行SQL,等待客户端的下一个指令
  • 5. 性能监控监控工具 Grid Control / DB control
  • 6. 问题定位基于“时间” 的性能模型 响应时间 = 运算时间 + 等待时间 响应时间: 从客户端提交请求,到从数据库接收完随后一条记录。 运算时间:扫描内存,计算查询条件,比较连接字段,排序 等待时间:I/O, 锁,网络。
  • 7. 问题定位AWR –Automatic Workload Repository Advisor resultsAWRSQL statisticsBase statisticsMetricsASH
  • 8. 性能优化ADDM –Automatic Database Diagnostic Monitor exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT 至少2个快照 SQL> @ ?/rdbms/admin/addmrpt.sql ADDM报告: 发现的问题 优化建议
  • 9. 手工分析AWR报告- Oracle 10g以上版本 DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT 产生报告 SQL> @ ?/rdbms/admin/awrrpt.sql Statspack报告- Oracle 9i版本 SQL> @ ?/rdbms/admin/spreport.sql
  • 10. 常见性能故障系统关注的问题 CPU idle 较低 (<20%) 内存Paging / Swaping I/O 100% Busy Network buffer overflow,丢包
  • 11. 常见性能故障数据库关注的问题 压力信息 命中率 异常等待事件 I/O指标 存储结构异常的INDEX和TABLE
  • 12. AWR/STATSPACK性能分析原则:系统表现为CPU + I/O资源 CPU time db file sequential read db file scattered read
  • 13. AWR/STATSPACK性能分析db file sequential read 较高的原因 没有使用正确适合的索引。 I/O瓶颈。 数据库中表中有chain row. 如果排除上述原因,系统业务繁忙,此指标也会较高。
  • 14. AWR/STATSPACK性能分析db file scattered read 的原因是: SQL语句没有使用索引。可以通过STATSPACK报告中TOP SQL 的部分来发现有性能问题的SQL 语句。 SQL语句没有使用适合的索引。可以通过STATPACK报告中TOP SQL 的部分来发现有性能问题的SQL 语句。从后面的数据库统计数据中还可以发现全表扫描中小表占很大比例,在系统内存充足的前提下,可以使这些小表常驻内存,以减小系统I/O。
  • 15. AWR/STATSPACK性能分析SQL语句检查: SQL ORDER BY GETS列出消耗CPU较高的SQL语句。其中第一条语句在数据库操作中消耗的时间最多,这条语句可以用于检查和优化以得到更好的性能。 对于统计时段中消耗CPU较高的SQL语句,可以对上述语句进行执行计划的分析,看有无优化的可能。如有无用到高效的索引,是否使用了外部变量。 较高的buffer get并不会导致高I/O,因为资料都被存在数据库缓冲区中;但是较高的buffer get会导致高CPU和系统资源被使用以得到资料。因此,一条优化好的SQL语句应该取出较少的buffer,从而减少系统资源的使用
  • 16. 数据库极端情况处理 hanganalyzeDump hanganalyze by ORADEBUG SQL>select * from dual; /* 在某些情况下,任何SQL语句都无法正常完成,此时,终止语句,重新登录,并跳过此步骤 */ SQL> oradebug setmypid SQL> oradebug unlimit SQL> oradebug hanganalyze (only for RAC) SQL> oradebug setinst all SQL> oradebug -g def hanganalyze
  • 17. 数据库极端情况处理 hanganalyze(续)LEVEL 10 Dump all processes (IGN state) 5 Level 4 + Dump all processes involved in wait chains (NLEAF state) 4 Level 3 + Dump leaf nodes (blockers) in wait chains (LEAF,LEAF_NW,IGN_DMP state) 3 Level 2 + Dump only processes thought to be in a hang (IN_HANG state) 1-2 Only HANGANALYZE output, no process dump at all
  • 18. 数据库极端情况处理 systemstateDump systemstate by ORADEBUG SQL>select * from dual; /* 在某些情况下,任何SQL语句都无法正常完成,此时,终止语句,重新登录,并跳过此步骤 */ SQL> oradebug setmypid SQL> oradebug unlimit SQL> oradebug dump systemstate 10 Level 1 - Very basic process information only Level 10 - Most common level - includes state object trees Level + 256 - Adding 256 to the level will try to dump short stack info for each process
  • 19. 数据库极端情况处理 紧急状况下的信息收集HANGANALYZE应进行多次,间隔90s,重新登录(/as sysdba),推荐level 3 SYSTEMSTATE DUMP应进行多次,间隔90s,重新登录(/as sysdba),推荐level 266(9.2.0.6以上) FOR RAC 'immediate trace name locks level 10 'immediate trace name row_cache level 10'
  • 20. Overview 设计建议 设计原则 模型设计建议 命名规则 对象设计规范 安全设计
  • 21. 设计原则 —— 整体架构 对于OLTP尽量满足第三范式——可降低数据字段冗余 结合业务需要,提高数据库高可用性和可扩展性 参考同行业成熟数据库架构规划方案 使用集群技术,需要在设计时,充分考虑节点间业务和数据分离 数据库安全与审计要求——安全基线 根据业务数量,对物理硬件进行选择 根据业务变化,妥善处理历史数据转储和清理 系统和数据库灾备策略、备份恢复策略 考虑Web,App,DB和OS各层面软件版本及应用接口兼容性 考虑为后期维护提供接口,如:SQL调整、添加Hint等 对于OLTP系统长短连接的选择,是否使用绑定变量等细节性要求
  • 22. 模型设计建议 命名规则 数据库对象如表、列、序列、过程、函数等在命名时建议遵循如下规则: 采用有含义的英文单词或缩写 对象类型、模块、名字等以“_”分隔 对象名称不允许使用Oracle关键字 对象名称、结构和含义需要有应用数据字典记录
  • 23. 模型设计 表 不应使用非应用表空间创建应用对象表 避免在交易时间段使用DDL语句修改对象结构,如:增减表分区、索引等 业务表、LOB字段、历史表和索引存放不同表空间 建表语句需要指定表、主键、LOB字段使用的不同表空间 在表结构中,非空字段位于表前部,可以为空和备用字段位于表后部 访问频繁的表存放KEEP缓冲区 根据应用需要选择合适数据类型
  • 24. 模型设计 字段设计 数据精确到微秒的时间字段字段需定义为TIMESTAMP类型 索引字段要保证非空,并给默认值 使用NUMBER型时必须指定长度 用LOB类型代替LONG类型,并对LOB指定独立表空间
  • 25. 模型设计 分区表设计 千万以上或4G以上表可以考虑使用分区表,选择适合的分区关键字,并且要求应用设计使用 持续增长且需要定期清理的表可以考虑时间上进行范围分区 建表时要有“ENABLE ROW MOVEMENT”参数 如果使用子分区,则需要根据业务需要详细讨论
  • 26. 模型设计 分区表设计 1)range分区 范围分区是对某个可度量的字段在可以预见的范围内进行划分 的分区方式,在进行范围查找时具有比较高的效率。 例如:日期字段。 2)list分区 枚举值分区是对某个可列举确定值的字段按照不同值进行划分 的分区方式,该分区方式仅限于单个列; 例如:分行号。 3)hash分区 散列分区是对某个离散性很大的字段按照根据散列算法计算出 的散列值进行分区;分区个数必须是2的幂; 例如:身份证号码进行hash分区,按照帐号进行hash分区。
  • 27. 模型设计表存储策略 表设计时,需要考虑到表中数据的存储期限、清理、备份策略,避免因表中历史数据量巨大而影响日常访问的性能。 OLTP类应用的数据和OLAP类应用的数据存储到不同的表中。 不同的应用模块的表,建议通过Schema或表名加以区分。
  • 28. 模型设计 主键设计 数据约束优先考虑Oracle提供的约束机制 主键建议选择无意义字段 有外键的表,外键字段需要创建索引 Sequence产生的字段不能作为复合主键的字段 主键使用索引的名称需要在语句中指定 再删除约束时,使用keep index保留索引
  • 29. 模型设计 索引设计 不允许出现冗余索引 如果一个索引的所有列刚好是另外一个索引的前导列,则该索引是冗余索引。冗余索引是不仅占用了空间,而且还会占用ORACLE产生执行计划的时间。 索引创建原则 其它类型索引
  • 30. 模型设计 索引设计 不允许出现冗余索引 索引创建原则 索引数量太多会严重影响DML操作 在建立索引时,一定要结合应用的需要,创建索引个数需要结合表的交易类型和字段分布 复合索引需要将选择性强的字段放在前面 访问频繁的索引可以位于KEEP缓冲区 小表(一般<5000)一般不需要创建索引 检索数据量超过10%,则使用全表扫描可能效率更高 对于分区表建议使用LOCAL索引
  • 31. 模型设计 索引设计 其它类型索引 复合索引 合理选择字段之间顺序 函数索引 增加insert语句开销 位图索引 影响高并发DML操作 递减索引 适用于order by中带有排序的索引扫描 分区索引 需要合理选择LOCAL和GLOBAL类型 唯一索引需要包含分区关键字
  • 32. 模型设计 物化视图 物化视图的刷新间隔时间最小为3分钟,最长时间不能超过循环周期 物化视图的基表必须创建主键 使用视图时数据库参数query_rewrite_enabled必须为true OLTP系统尽量不使用实时刷新的物化视图
  • 33. 模型设计 存储过程、函数和包 存储过程、函数和包中不允许频繁的使用DDL语句。 存储过程、函数和包必须有相应的异常捕获功能 日志或错误信息记录表来保存这些错误信息。
  • 34. 模型设计 触发器 保证准确的异常捕获,才能保证触发器的正常使用 与应用程序事务属性保持一致,以避免死锁发生 带有DML语句的触发器对DML的影响是很大 当大数据量DML发生,且并发很高的情况下,触发器将带来很大的影响,可能会导致大量enqueue发生。
  • 35. 模型设计 同义词 对于只读用户,建议创建与对象名字的同义词。 DB Link 容易导致分布式事物的多种故障,不建议使用。 Sequence 需要针对变化较大的sequence设置cache 对于RAC系统,sequence 的nocache可能会带来较大的性能影响 根据业务特点设置sequence的最大值或循环使用 对于RAC系统,sys.audses$(审计会话用, sessionid from this sequence),sys.idgen1$(在存在大量的LOB对象时,需要将该值调大)两个sequence的cache值调整为10000或者更高,特别是短连接且logon频率高的系统更需要调整
  • 36. 安全设计 用户安全设计 管理、监控、应用、备份四类用户 不需要的用户需要锁定 用户权限、角色限制 根据应用需求分配权限和角色 根据用户类型管理权限和角色 数据库审计 从性能角度考虑,不建议开启审计 密码安全设计 更改所有用户默认密码 不要在脚本或明文中保留用户密码 安全设计细节,参考安全处《数据库安全基线》
  • 37. Overview 开发建议 SQL开发建议 PL/SQL开发建议
  • 38. 开发建议—— SQL Select 列和where条件要求 尽量避免出现对索引列函数及其它运算表达式 常量和变量的使用原则 排序操作的使用
  • 39. 开发建议—— SQL 多表连接 使用表的别名来引用列 SQL语句中关联查询的大表尽量不要超过4个
  • 40. Where条件中避免数据发生隐式转换 隐式转换时可能导致索引不能被使用 隐式转换的一般规则: 比较时,一般是字符型转换为数值型,字符型转换为日期型 算术运算时,一般把字符型转换为数值型,字符型转换为日期型 连接时,一般是把数值型转换为字符型,日期型转换为字符型 赋值、调用函数时,以定义的变量类型为准。 开发建议—— SQL
  • 41. 开发建议—— SQL 不能使用SELECT * 选择表中的所有列,而是要把所选择的列写出来 好处如下: 减少了ORACLE在解析SQL语句时查找表定义的时间 可以节省内存,提高效率 方便于以后系统的扩展和维护
  • 42. 开发建议—— SQL 在INSERT SQL语句中指定表的列 提高效率 便于以后对表的字段扩展
  • 43. 对表定义别名,使用表的别名来引用列 使用别名可以提高ORACLE解析SQL语句的效率。 开发建议—— SQL
  • 44. 开发建议—— SQL DELETE语句中必须包含WHERE子句 不带WHERE子句的DELETE语句会删除表中的所有记录 如果确实需要删除表中所有数据,请使用truncate语句
  • 45. 开发建议—— SQL对于大批量的DML操作分段提交 防止大事务失败
  • 46. 开发建议—— PL/SQL批量绑定使用建议 使用批量绑定提高性能。 使用FORALL语句。 使用BULK COLLECT子句将查询结果返回到集合中。
  • 47. 开发建议—— PL/SQL动态SQL使用建议 不建议联机系统的交易程序使用动态SQL 实现数据定义语言DDL和系统控制命令的功能 使用批量动态SQL可以提高效率
  • 48. 开发建议—— PL/SQL注释建议 数据库表、过程、函数必须有开始注释。 数据库表和数据库表中包含的列必须添加注释 过程、函数的输入/输出参数必须添加内容注释 过程、函数、包等数据库对象中的变量定义需要添加注释 过程、函数、包体等数据库对象中的重要语句或过程、函数的调用需要注释说明 不要在SQL语句中对所选择的列名与 ‘,’之间添加注释 业务流程必须添加简要注释说明
  • 49. 开发建议—— PL/SQL事务建议 在PL/SQL程序中: LOOP循环中不能进行单笔commit,当数据量大时,进行分批提交。 在一个事务中,不要将一条记录或某个字段的中间结果多次更新到表中。 在批量处理程序中,不能进行频繁的单笔commit; 当应用程序(Java、Pro*C/C++、PL/SQL)中需要将运行情况的日志信息记录到数据库中时,必须将日志信息记录的SQL封装到PL/SQL程序中,且对该PL/SQL程序使用自动匿名事务方式,这样日志记录的事务不会影响到调用者的事务。
  • 50. Q&A