Oracle数据库性能优化最佳实践

InsertPictureHereOracle数据库性能优化最佳实践大纲•数据库性能基础及调优方法论•使用自动工作量资料档案库•SQL语句调优•Oracle性能优化解决方案数据库性能基础及调优方法论数据库性能基础Schema设计和SQL语句•好的Schema和数据模型设计是好的数据库性能的基础.•好的Schema和数据模型设计应该使应用操作更加简单并减少错误发生•选择正确的数据类型(e.g.CharvsVARCAHR2)•使用不同的索引设计高效率的访问路径•选择正确的存储选项•使用分区•收集并维护准确的统计信息•编写简单高效的SQL•尽量避免序列化瓶颈数据库性能基础会话和游标•应用服务器/中间件管理数据库会话连接，保证数据库的性能和稳定•在一台机器上超过5000进程会使机器变的不稳定•在实验室很容易模拟大量的数据库连接，但是在生产系统上要使用则非常困难•logon/logoff对性能的影响进程的建立和撤销开销非常大•连接和去连接到共享内存的开销也非常大•共享SQL,在OLTP应用中使用绑定变量•应用应该尽量避免对数据库对象进行create、drop、truncate等DDL操作会话管理•不要连接所有浏览器用户•不要连接、执行操作、断开连接•使用连接池—中间层的最大优点数据库性能基础会话和游标(Thisslideisover10Yearsold!)050100150200250300350400LogonLogoffHardParsingEXACTSoftParsingOptimalUsersDBA优化哪些内容•性能优化范围：•应用程序：•SQL语句性能•更改管理•实例优化：•内存•数据库结构•实例配置•操作系统交互：•I/O•交换•参数与开发人员共享与系统管理员共享优化方法•优化步骤：•自上而下优化以下内容：•在优化应用程序代码之前先优化设计•在优化实例之前先优化代码•对可以带来最大潜在好处的方面进行优化，并确定：•最长的等待•最大的服务时间•达到目标时停止优化。性能优化工具•可用工具：•基本工具：•时间模型•顶级等待事件•动态性能视图和表•预警日志•跟踪文件•OracleEnterpriseManager页•AWR或Statspack•选件：•诊断包•优化包顶级等待事件CPU时间和等待时间优化维可扩展应用程序可能需要SQL优化需要实例/RAC优化CPU时间等待时间添加CPU/节点没有任何作用数据库时间=数据库CPU时间+数据库等待时间时间模型示例公共等待事件等待事件区域缓冲区忙等待缓冲区高速缓存、DBWR可用缓冲区等待缓冲区高速缓存、DBWR、I/ODB文件分散读取，DB文件序列读取I/O、SQL优化入队等待(enq:)锁库高速缓存等待闩锁日志缓冲区空间日志缓冲区I/O日志文件同步过度提交、I/O从等待事件发现瓶颈从AWRReport&ASHReport&StatspackReport获取WaitEventsStatisticsTopEvents从动态性能视图中获取：事件DBFileScatteredRead读取大量的数据块到Cache中调整思路：调整大表上的索引收集更新统计信息事件DBFileSequentialRead一般指读取索引的数据调整思路：调整索引的设计Rebuild索引，提高索引效率事件BufferBusyWait访问的块正在读取中其他进程正在将数据读到Cache中访问的块正在修改中其他进程正在修改Cache中的数据调整思路：找到hotblock，改变pctused和pctfree增加freelistgroup和freelist增加一定数量的回滚段事件FreeBufferWait寻找可用Cache块，如大量的数据被修改，或没有可用的空闲块•调整思路增加db_cache_size的值检查DBWR效率事件LogBufferSpace生成日志的速度大于将日志写到磁盘的速度调整思路增加log_buffer的值将log文件放到快速空闲的设备上事件CheckpointNotCompleted等待Checkpoint操作结束调整思路：减小logbuffer的大小增加Checkpoint的频率事件DirectPathRead磁盘上进行的排序，并行的全表扫描调整思路：在排序操作(orderby/groupby/union/distinct/rollup/合并连接)时，由于PGA中的SORT_AREA_SIZE空间不足，造成需要使用临时表空间来保存中间结果，当从临时表空间读入排序结果时，产生directpathread等待事件增加SORT_AREA_SIZE或PGA_AGGREGATE_TARGET事件EnqueueEnqueue是Oracle内部的一种锁，用来进行串行操作•调整思路：使用本地管理表空间外键建索引及时commit事件LogFileSwitchArchivingneeded等待归档完成调整思路：增加log_archive_processes的数量Checkpointnotcompleted等待切换到下一个日志调整思路：调整大小Completion等待日志切换完成调整思路：将log放到更快的设备上事件LogFileSync在用户commit/rollback时，等待将Logbuffer写入日志文件的过程原因：LGWR效率低下，系统提交过于频繁调整思路将日志放到更快的设备上一次提交更多纪录适当使用NOLOGGING等选项取决于缓冲区类型。PK索引和序列出现问题时的V$SESSION_WAIT（块）公共等待事件V$SYSSTAT视图中的提交数和回退数，磁盘数I/O速度慢，未批量提交过度提交、I/OLogfilesyncV$SYSSTAT重做缓冲区分配重试次数，磁盘缓冲区小，I/O速度慢日志缓冲区I/OlogbufferspaceV$SQLAREA分析调用，子游标SQL分析/共享闩锁LibrarycachewaitsV$ENQUEUE_STAT取决于入队类型锁Enqueuewaits(enq:)V$SQLAREA磁盘读取数。V$FILESTAT读取时间SQL优化不当，I/O系统速度慢I/O、SQL优化dbfilescatteredread、dbfilesequentialread使用OS统计信息时的入时间缓冲区高速缓存统计信息DBWR速度慢缓冲区高速缓存、DBWRI/Ofreebufferwaits缓冲区高速缓存、DBWRbufferbusywaits检查可能的原因区域等待事件使用软件包的功能•SQL跟踪•Statspack•系统统计信息•等待模型•时间模型•OS统计信息•度量•服务统计信息•直方图•优化程序统计信息•SQL统计信息使用软件包进行监视和优化不使用软件包进行监视和优化数据库配置管理包•数据库和主机配置•部署•补丁程序数据库和视图补丁程序高速缓存•补丁程序存放•克隆数据库•克隆Oracle主页•搜索配置•比较配置•策略数据库诊断包•自动工作量资料档案库•自动数据库诊断监视程序(ADDM)•活动会话历史记录(ASH)•性能监视（数据库和主机）•事件通知：通知方法、规则和计划•事件历史记录和度量历史记录（数据库和主机）•封锁•动态度量基线•监视模板数据库优化包•SQL访问指导•SQL优化指导•SQL自动优化•SQL优化集•SQL计划管理的自动计划发展•SQL监视•重组对象访问数据库主页https://主机名:1158/emOracleEnterpriseManager性能页查看预警日志•“DatabaseHomepageRelatedLinksregionAlertLogContents（数据库主页相关链接预警日志内容）”使用预警日志信息帮助进行优化•预警日志文件包含的下列信息，可用于帮助优化数据库：•执行归档的时间•实例恢复的开始时间和完成时间•死锁错误和超时错误•不完整的检查点•检查点的开始时间和结束时间用户跟踪文件•可以在会话级别或实例级别启用或禁用服务器进程跟踪。•用户跟踪文件包含该会话中跟踪的SQL语句的统计信息。•用户跟踪文件是按每个服务器进程创建的。•用户跟踪文件还可通过下列方式创建：•执行一个BACKUPCONTROLFILETOTRACE命令•进程错误使用自动工作量资料档案库自动工作量资料档案库：概览SGAV$DBA_*ADDM自优化组件自优化组件…内部客户机外部客户机EMSQL*Plus…高效的内存中统计信息收集AWR快照MMON自动工作量资料档案库数据指导结果AWRSQL统计信息基础统计信息度量ASH工作量资料档案库SYSAUXSGA内存中统计信息6:00a.m.7:00a.m.8:00a.m.快照1快照2快照3快照49:00a.m.9:30a.m.ADDM查找首要问题。MMONDatabaseControl和AWR编辑快照参数运行AWR报表管理快照管理基线手动AWR快照•创建快照DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(};DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(LOW_SNAP_ID=102,HIGH_SNAP_ID=105);删除一或多个快照使用EM创建和删除快照使用PL/SQL管理快照DBMS_WORKLOAD_REPOSITORY程序包过程名称说明CREATE_SNAPSHOT立即创建手动快照DROP_SNAPSHOT_RANGE删除一组快照MODIFY_SNAPSHOT_SETTINGS修改快照设置在EM中生成AWR报表阅读AWR报表•第一部分提供•概览•最重要的诊断•其它页•特定区域的详细统计信息信息快照和期间比较比较时段：优点DBA≠DBA≠比较时段：结果比较时段：报表工作量资料档案库比较时段报表比较时段：负载概要文件比较时段：顶级事件ADDM性能监视快照ADDM内存中统计信息AWRSGA60分钟ADDM结果MMON检测到的首要性能问题登录/注销过多内存大小不足热块和对象w/SQLRAC服务问题锁和ITL争用检查点原因PL/SQL、Java时间OracleStreams、AQ和RMAN顶层SQLI/O问题分析配置问题应用程序使用情况Statspack未检测到ADDM确定顶级问题DatabaseControl和ADDM查找结果ADDM分析结果123ADDM建议DatabaseControl和ADDM任务活动会话历史记录：技术SGA统计信息V$SESSIONASH最近历史记录滚动缓冲区工作量资料档案库1秒1秒1秒每60分钟MMONMMNL到达66%V$ACTIVE_SESSION_HISTORYWRH$_ACTIVE_SESSION_HISTORY（已分区）未使用SQL直接路径插入DBA_HIST_ACTIVE_SESSION_HISTORY十分之一查看器已解锁ASH采样率：示例会话1等待I/O等待锁等待块等待I/O等待I/O等待锁等待I/O等待块……会话1等待I/O会话1等待I/O会话1等待块会话2活动会话3活动会话n活动…活动非活动的会话1秒1秒ASH…V$ACTIVE_SESSION_HISTORY时间访问ASH数据•转储到跟踪文件•V$ACTIVE_SESSION_HISTORY•DBA_HIST_ACTIVE_SESS_HISTORY•ASH报表•EM诊断程序包性能页将ASH转储到文件•生成的文件包含指定的最后几分钟数内的ASH样本。SQLoradebugsetmypidSQLoradebugdumpashdump10ACTIVESESSIONHISTORY-PROCESSTRACEDUMPHEADERBEGINDBID,INSTANCE_NUMBER,SAMPLE_ID,SAMPLE_TIME,SESSION_ID,SESSION_SERIAL#,USER_ID,SQL_ID,SQL_CHILD_NUMBER,SQL_PLAN_HASH_VALUE,SERVICE_HASH,SESSION_TYPE