02-04 应用优化的其他方法 (60 页).ppt

OracleRDBMS大型应用优化其他方法 麦思博 北京 软件科技有限公司2011 10 2020 2 19 2 内容提要 临时表的使用方法Logging与Nologging适当使用关于热块问题索引统计数据陈旧问题多块读方法索引与相关参数会话级参数使用方法 2020 2 19 3 Oracle临时表 临时表的目的 应用中的有的数据只是临时的存储 使用后不再使用 永久表要存放在表中 给磁盘空间带来碎片 临时比变量更有效 可以用SQL语句 Oracle允许在会话或事务其间创建临时表 以保存会话或事务的数据 临时表就是内存表 它不保存到表空间上 临时表的数据仅在会话其间有效 用CREATEGLOBALTEMPORARY完成临表的创建 创建临时表可指定事务级后会话级有效 不专门指定 则在会话结束后才释放 对临时表做DML操作不产生重做日志 REDOLOG 2020 2 19 4 Oracle临时表 语法 createglobaltemporarytabletable name tabledefinition oncommitdeleterows 在提交后删除行数据 临时表可在提交后删除 语法为 为会话保持数据 临时表可为会话保持数据 语法为 createglobaltemporarytabletable name tabledefinition oncommitpreserverows 2020 2 19 5 Oracle临时表 例子 SQL createglobaltemporarytablemyempasselect fromemp 表已创建 SQL select frommyemp 未选定行SQL insertintomyempvalues 赵元杰 32456 99 10 12 已创建1行 SQL select frommyemp ENAMESALDEPTNOTEL 赵元杰32456 991012SQL connectsys sys已连接 SQL connectzhao zhao已连接 SQL select frommyemp 未选定行 创建临时表 带GLOBALTEMPORARY创建 会话结束自动清除 2020 2 19 6 内容提要 临时表的使用方法Logging与Nologging适当使用关于热块问题索引统计数据陈旧问题多块读方法索引与相关参数会话级参数使用方法 2020 2 19 7 Logging Nologging 什么是redo当块改变时 Oracle记录改变向量 并发将这些存放SGA日志缓冲区由LGWR写到日志文件中简单说 Redo Transactions 2020 2 19 8 Logging Nologging 何时设置为NOLOGGING下面情况可将表设置为NOLOGGINGDIRECTLOAD SQL Loader DIRECTLOADINSERT usingAPPENDhint CREATETABLE ASSELECTCREATEINDEXALTERTABLEMOVEALTERTABLE MOVEPARTITIONALTERTABLE SPLITPARTITIONALTERTABLE ADDPARTITION ifHASHpartition 2020 2 19 9 Logging Nologging 下面情况可将表设置为NOLOGGINGALTERTABLE MERGEPARTITIONALTERTABLE MODIFYPARTITIONADDSUBPARTITONCOALESCESUBPARTITONREBUILDUNUSABLEINDEXESALTERINDEX SPLITPARTITIONALTERINDEX REBUILDALTERINDEX REBUILDPARTITION 2020 2 19 10 Logging Nologging 设置索引为NOLOGGING可加快处理速度ALTERTABLE NOLOGGING INSERT nologging 下面情况可采用NOLOGGING 先设置为NOLOGGINGSQL ALTERINDEXnew indexNOLOGGING SQL ALTERINDEXnew indexREBUILD 当暂时将表或索引设置成NOLOGGING时 别忘了再将它们设置loggingselect altertable table name logging fromuser tableswheretable namelike STATS select alterindex index name logging fromuser indexes 2020 2 19 11 Logging Nologging 创建表可指定NOLOGGING 创建时设置为NOLOGGINGSQL CREATE表指定NOLOGGING后 下面语句不产生日志 SQL INSERTINTOnew table nolog test SQL UPDATEnew table nolog testSET SQL DELETEFROMnew table nolog test SQL INSERTINTOnew table nolog test SQL UPDATEnew table nolog testSET SQL DELETEFROMnew table nolog test INSERT APPEND ALTERTABLEnew table nolog testMOVE ALTERTABLEnew table nolog testMOVEPARTITION 创建时设置为NOLOGGINGSQL CREATETABLEnew table nolog testNOLOGGING 2020 2 19 12 NOLOGGING 特殊的创建表可指定NOLOGGING限制写日志修改不可恢复修改不能传递到备用库谨慎使用NOLOGGING处于主要的生产数据库不要设置NOLOGGING经常被其他数据库读取的数据库不要设置NOLOGGING千万不能dataguard上用 如果已经做了databaseforcelogging就没有事 2020 2 19 13 NOLOGGING 用提示使用NOLOGGINGinsert append intomytablevalues 1 alan Oracle归档模式下当表为logging状态时 append noappend都生成redo当表为nologging状态时 只有append不生成redoOracle非归档模式下表在logging nologing下 append模式都不生成redo 表在logging nologing下 noappend模式下都生成redo 2020 2 19 14 在提示中使用NOLOGGING 可在提示中使用NOLOGGING可提示使用NOLOGGING和并行 insert APPENDNOLOGGINGPARALLEL table name 4 intotable nameselect PARALLEL table name 4 fromtable name dblink 2020 2 19 15 数据库级取消NOLOGGING 从9 2开始 可将数据库或表空间设置为LOGGING 取消NOLOGGING 数据库级取消 强制产生日志 表空间级取消 强制产生日志 将数据库或表空间设置为LOGGING所有的NOLOGGING将无效 ALTERDATABASEFORCELOGGING ALTERDATABASENOFORCELOGGING ALTERTABLESPACEtablespace nameFORCELOGGING ALTERTABLESPACEtablespace nameNOFORCELOGGING 2020 2 19 16 内容提要 临时表的使用方法Logging与Nologging适当使用关于热块问题索引统计数据陈旧问题多块读方法索引与相关参数会话级参数使用方法 2020 2 19 17 Oracle热块 hotblock hotblock指的是同时有若干个session 同时对一个block进行操作 导致此block比较忙 导致block比较忙的原因是 Oracle数据库的数据存放的最小单位就是block 数据每次读取数据的时候只能以block为单位 如果有多条记录存放在一个block上的话 那么导致多个session访问同一个block的可能性就越大 2020 2 19 18 Oracle热块 hotblock hotblock解决方法 1 增大databuffer 2 增加freelist 减小pctused 将一个block上可以使用的空间减少 引起block数据存放的较少 可以提高应用的访问并发率 减少hotblock的产生 3 增加回滚段数目 增大initrans 考虑使用LMT 确认是不是由于热点块造成 如果是可以用反转索引 或者用更小块大小 4 可以建立block较小的表空间 将热点对象移动到此表空间上去 5 优化应用 优化索引 提高索引的命中率 2020 2 19 19 Oracle热块 hotblock Hotblock诊断热块与 bufferbusywaits 等待事件有关1 查询V session wait视图 2 如果上面块在忙等待 则查询这一块的名称和类型 Selectp1 File p2 Block p3 ReasonCode fromv session waitwhereevent bufferbusywaits Selectowner segment name segment typeFromdba extentswherefile id 2020 2 19 20 Oracle热块 hotblock Hotblock诊断 续 热块一旦被确定 则使用v segment statistics性能视图促使块水平统计的实时监控 这一过程使得DBA识别与独立列表与索引有关的问题也可以查询dba data files以确定卷入等待的文件的file name 方法是使用v session wait中的P1查询V SEGMENT STATISTICS视图 SELECTowner object type object name valueFROMV SEGMENT STATISTICSWHERE statistic namelike read ORstatistic namelike write ORDERBYvaluedesc 2020 2 19 21 内容提要 临时表的使用方法Logging与Nologging适当使用关于热块问题索引统计数据陈旧问题多块读方法索引与相关参数会话级参数使用方法 2020 2 19 22 22 90 索引碎片分析当表的数据不断被删除后 与之相关的索引仍然保留那么这些无用的索引数据项是多少呢 Analyzeindex validatestructure检查索引块冲突校验索引的大小结果放入index stats表中 并生成有关索引的详细视图 索引统计数据陈旧问题 2020 2 19 23 ANALYZEINDEX validatestructure命令SELECTDEL LF ROWS LF ROWS Wasted FROMINDEX STATS如果浪费数值 Wasted 0 2或索引节点利用率 60 PCT USED 60 索引有效性分析 SQL ANALYZEINDEX 当删除的比率 ratio 大于15 20 时 需要对索引进行重建 2020 2 19 24 10g 11g基于CBO模式 这种模式需要访问数据字典统计数据 表 索引统计要尽量及时 准确当统计数据不要太旧 误导优化器当统计数据太旧 要及时重新搜集 统计数据过时问题 2020 2 19 25 内容提要 临时表的使用方法Logging与Nologging适当使用关于热块问题索引统计数据陈旧问题多块读方法索引与相关参数会话级参数使用方法 2020 2 19 26 DB FILE MULTIBLOCK READ COUNT参数 表示conference磁盘读取的块数量 通常发生在全表扫描 FullTableScan 和快速全索引扫描 FastFullIndexScan 时尽管这个参数已经由DBA设置 但开发人员可根据目前的具体需要在会话设置 如 DB FILE MULTIBLOCK READ COUNT SQL connectscott tiger orclSQL altersessionsetDB FILE MULTIBLOCK READ COUNT 16 2020 2 19 27 DB FILE MULTIBLOCK READ COUNT参数的大小探测方法 大小要根据当前OS运行的I O能力来设置如何确定这个参数的呢 只有手工办法 1 查询Oracle块大小和会话级设置 DB FILE MULTIBLOCK READ COUNT 目前8i的db file multiblock read count参数为 db file multiblock read count8db block sizeinteger8192 修改db file multiblock read count参数前要进行下面的测试 以确定db file multiblock read count参数的大小SQL settimingonSQL setautotraceon 1 设置会话级参数 altersessionsetdb file multiblock read count 32 2020 2 19 28 DB FILE MULTIBLOCK READ COUNT参数的大小探测方法 2 设置会话跟踪和做一个实际查询操作 DB FILE MULTIBLOCK READ COUNT 2 设置会话跟踪 altersessionsetevents 10046tracenamecontextforever level10 altersessionsetevents 10046tracenamecontextforever level12 3 全表扫描测试 select full a count frombaan TTFGLD416888a 4 取消会话跟踪 altersessionsetevents 10046tracenamecontextoff 2020 2 19 29 DB FILE MULTIBLOCK READ COUNT参数的大小探测方法 3 分析会话跟踪数据 看是否能采用这个参数 DB FILE MULTIBLOCK READ COUNT 5 检查跟踪文件 SQL showparameterUSER DUMP DEST 文件名为 baanivc4 ora 18052 trc oracle admin817 baanIVc4 udump baanivc4 ora 18052 trcOracle8iEnterpriseEditionRelease8 1 7 0 0 64bitProductionWiththePartitioningoptionJServerRelease8 1 7 0 0 64bitProductionORACLE HOME oracle 817Systemname SunOSNodename BCA ERPRelease 5 8Version Generic 108528 16Machine sun4uInstancename baanIVc4Redothreadmountedbythisinstance 1Oracleprocessnumber 11Unixprocesspid 18052 image oracle BCA ERP TNSV1 V3 2020 2 19 30 DB FILE MULTIBLOCK READ COUNT参数的大小探测方法 3 分析会话跟踪数据 看是否能采用这个参数 续 DB FILE MULTIBLOCK READ COUNT 5 检查跟踪文件 2010 01 1009 34 08 389WAIT 0 nam SQL Netmessagefromclient ela 0p1 1413697536p2 1p3 0 PARSINGINCURSOR 1len 56dep 0uid 0oct 3lid 0tim 0hv 1986181661ad bdea11e8 select full a count frombaan TTFGLD416888aENDOFSTMTPARSE 1 c 0 e 0 p 0 cr 0 cu 0 mis 1 r 0 dep 0 og 4 tim 0EXEC 1 c 0 e 0 p 0 cr 0 cu 0 mis 0 r 0 dep 0 og 4 tim 0WAIT 1 nam SQL Netmessagetoclient ela 0p1 1413697536p2 1p3 0 WAIT 1 nam fileopen ela 0p1 0p2 0p3 0WAIT 1 nam dbfilescatteredread ela 0p1 9p2 61562p3 30WAIT 1 nam dbfilescatteredread ela 0p1 13p2 1093577p3 13WAIT 1 nam dbfilescatteredread ela 0p1 13p2 1093591p3 31WAIT 1 nam dbfilescatteredread ela 0p1 16p2 854824p3 32WAIT 1 nam dbfilescatteredread ela 0p1 16p2 854856p3 32WAIT 1 nam dbfilesequentialread ela 0p1 16p2 854888p3 1WAIT 1 nam dbfilescatteredread ela 0p1 17p2 3025147p3 32WAIT 1 nam dbfilescatteredread ela 0p1 17p2 3025179p3 32 表示系统使用过32多块读 2020 2 19 31 db file multiblock read count参数调整 10g版本为自动调整 不设置就表示自动管理 默认为I O可执行的最大值与平台有关 不能超过缓冲区的10 通常相当于1MB对数据仓库 DSS可能不合适 DB FILE MULTIBLOCK READ COUNT 2020 2 19 32 DB FILE MULTIBLOCK READ COUNT Oracle10gR2版本与这个参数相关的功能有 V PROCESS MEMORY辅助监视自动PGA管理与确定PGA AGGREGATE TARGET参数的最佳设置如果没有明确设置DB FILE MULTIBLOCK READ COUNT参数 则表示为自动调整大小与平台有关 但文档经常说明为1MB SYS orcl selectisdefault description2fromv parameter3wherename db file multiblock read count ISDEFAULTDESCRIPTION FALSEdbblocktobereadeachIO 2020 2 19 33 内容提要 临时表的使用方法Logging与Nologging适当使用关于热块问题索引统计数据陈旧问题多块读方法索引与相关参数会话级参数使用方法 2020 2 19 34 Optimizer index cost adj参数 用来调整 基于代价的优化 相对于全表扫描访问代表一个百分比 取值范围在1到10000之间 可理解为Oracle执行多块 MultiBlock I O 比如全表扫描 的代价与执行单块 Single block I O代价的相对比例缺省值100 表示索引扫描成本等价转换与全表扫描成本若减小 则CBO倾向于使用索引 即单块I O 反之则倾向于全表扫描 多块I O OPTIMIZER INDEX COST ADJ 2020 2 19 35 Optimizer index cost adj参数调整 多数OLTP系统 OPTIMIZER INDEX COST ADJ可以设置在10到50之间对于数据仓库和DSS系统 需要反复调整取得一个合理值更为具体的可以根据统计信息 dbfilescatteredreads dbfilesequentialreads来计算 OPTIMIZER INDEX COST ADJ 2020 2 19 36 索引与多块读取数量 select fromt2wheren1 45 表扫描代价15 altersessionsetDB FILE MULTIBLOCK READ COUNT 4 SELECTSTATEMENTOptimizer CHOOSE Cost 16Card 15 TABLEACCESS BYINDEXROWID OF T2 Cost 16Card 15 INDEX RANGESCAN OF T I2 NON UNIQUE Cost 1Card 15 表扫描代价23 altersessionsetDB FILE MULTIBLOCK READ COUNT 16 SELECTSTATEMENTOptimizer CHOOSE Cost 10Card 15 TABLEACCESS FULL OF T2 Cost 10Card 15 2020 2 19 37 索引与单块读取调整 select fromt2wheren1 45 indexaccesscostwas16 altersessionsetOPTIMIZER INDEX COST ADJ 50 SELECTSTATEMENTOptimizer CHOOSE Cost 8Card 15 TABLEACCESS BYINDEXROWID OF T2 Cost 8Card 15 INDEX RANGESCAN OF T I2 NON UNIQUE Cost 1Card 15 altersessionsetOPTIMIZER INDEX COST ADJ 25 SELECTSTATEMENTOptimizer CHOOSE Cost 4Card 15 TABLEACCESS BYINDEXROWID OF T2 Cost 4Card 15 INDEX RANGESCAN OF T I2 NON UNIQUE Cost 1Card 15 2020 2 19 38 OPTIMIZER INDEX CACHING参数 告诉Oracle索引在内存的数据缓冲区中的可能性有多大 即向优化器暗示位于Oracle数据缓冲区的索引的数量参数设置将会影响到 基于代价的优化 做出的对一个表连接 嵌套循环 使用索引还是使用全表扫描选择参数optimizer index caching是一个百分比参数 有效取值范围是0到100 OPTIMIZER INDEX CACHING 2020 2 19 39 2020 2 19 Oracle数据库设计与性能 39 90 OPTIMIZER INDEX CACHING参数调整 这个参数使得我们能够调节 基于代价的SQL优化器 的行为 从而选择最好的方式来访问想要的SQL查询结果 嵌套循环连接哈希连接访问全索引扫描全表扫描访问optimizer index caching设置为较大的百分比 可以使嵌套循环连接在优化器看起来更便宜 从而优化器更有可能选择进行嵌套循环连接而不是哈希连接或者归并排序连接 OPTIMIZER INDEX CACHING 2020 2 19 40 2020 2 19 Oracle数据库设计与性能 40 90 Oracle9i 10g 11g的变化 从Oracle9i开始 运行配置多种块大小 创建表空间的块大小为2K 4K 8K 16K和32K并使用相似大小的表和索引来匹配表空间我们应该理解这些新变化 大数据块的输入输出操作的边缘开销可忽略不计取一个32K大小的数据块的开销仅比取一个2K大小的数据块的开销多1 因为99 的磁盘输入输出操作都是用来把磁盘读写头的移动到相应的磁盘柱面和轨道 OPTIMIZER INDEX CACHING 2020 2 19 41 2020 2 19 Oracle数据库设计与性能 41 90 索引与大块的表空间 当索引被存储在大块表空间中时使用起来更有效 更深层的原因是 B 树可以更好地被平衡并且对顺序索引节点访问而言有更小的磁盘开销著名作家RobinSchumacher的研究表明32K块大小的Oracle索引在执行索引范围扫描时需要更少的逻辑输入输出 并且性能更高 OPTIMIZER INDEX CACHING 2020 2 19 42 2020 2 19 Oracle数据库设计与性能 42 90 创建索引缓冲 可在数据库处于活动状态创建索引缓冲首先 把所有索引都移至某个单独的表空间 定义成一个单独的数据缓冲区然后将参数optimizer index caching设置成正确的值分配一个32K的缓冲区空间altersystemsetdb 32k cache size 100m 创建块大小32K的表空间createtablespaceindex ts 32k blocksize32k OPTIMIZER INDEX CACHING 2020 2 19 43 2020 2 19 Oracle数据库设计与性能 43 90 创建索引缓冲 续 把所有索引移至这个32K的表空间中该命令把所有索引移至这个32K的表空间而无需中断当前存在的索引查询 它把索引重建成临时段 并确保新索引可用后在删除旧索引alterindexcust idxrebuildonlinetablespaceindex ts 32k OPTIMIZER INDEX CACHING 2020 2 19 44 2020 2 19 Oracle数据库设计与性能 44 90 创建索引缓冲 续 既然所有索引已经被隔离到一个单独的表空间和索引缓冲区中 我们就可以执行数据字典脚本来预见相对精确度 即我们期望在内存索引缓冲区中看到的索引的数量 OPTIMIZER INDEX CACHING Selectvalue blocksoptimizer index cachingFromv parameterp dba segmentsswherename db 32k cache size Andtablespace name INDEX TS 32K 2020 2 19 45 OPTIMIZER INDEX CACHING 创建索引缓冲与嵌套连接 只有采用 USE NL 时才使用设置OPTIMIZER INDEX CACHING参数 如 SQL altersessionsetoptimizer index caching 90 Sessionaltered SQL showparameteroptimizer index cachingNAMETYPEVALUE optimizer index cachinginteger90 2020 2 19 46 SKIP UNUSABLE INDEXES skip unusable indexes参数 在会话中设置 表示要跳过无效的索引在EXP中表示要跳过无效的索引 不导出 在IMP中表示要跳过无效的索引 不维护 索引为什么无效 当将表从一个表空间迁移到另外新表空间后 原来的索引自动变为无效可用alterindexidxtunusable 命令使索引失效无效的索引的状态在user indexes status列的值为 UNUSABLE 2020 2 19 47 2020 2 19 47 90 SKIP UNUSABLE INDEXES 索引无效 UNUSABLE 的处理 可设置skip unusable indexes跳过失效的索引这个参数可在系统或会话级上设置 如 SQL select fromawhereid 1 select fromawhereid 1 第1行出现错误 ORA 01502 索引 TEST IDX A ID 或这类索引的分区处于不可用状态SQL altersystemsetskip unusable indexes truescope memory 系统已更改 SQL select fromawhereid 1 2020 2 19 48 SKIP UNUSABLE INDEXES 索引无效处理建议 经常检查关键索引是否失效直接查询dba indexes的status列是否为 UNUSABLE 即可对于处于无效的索引 建议找时间要重建 而不是一味的跳过 跳过只是一种临时的办法 2020 2 19 49 内容提要 临时表的使用方法Logging与Nologging适当使用关于热块问题索引统计数据陈旧问题多块读方法索引与相关参数会话级参数使用方法 2020 2 19 50 参数设置与API 用 ALTERSYSTEM 命令修改实例参数 是参数的完整名称 而是要设置的具体值 如Audit trail TRUEcomment是注释字串deferred表示参数延迟生效MEMORY Default 在当前实例内存有效SPFILE 只写到spfileBOTH memory spfile ALTERSYSTEMSET comment deferred SCOPE memory spfile both sid 2020 2 19 51 参数设置与API 用 ALTERSYSTEM 命令修改实例参数 实例级参数的修改需要具有SYSDBA 系统级半永久化参数 ALTERSYSTEMSETparam valueSCOPE MEMORY 只存储在参数文件的参数 ALTERSYSTEMSETparam valueSCOPE SPFILE 修改后必须重启动才有效的参数 ALTERSYSTEMSETparam valueSCOPE BOTH SQL altersystemsetdb cache size 512Mscope both 系统已更改 2020 2 19 52 参数设置与API DBA对参数的修改与影响范围 DBA用ALTERSYSTEM修改参数影响整个实例DBA也可用ALTERSESSION修改参数只影响该会话Oracle系统参数的会话级修改问题 国内多数开发人员认为修改参数应该是DBA的工作 这是对Oracle参数的误解Oracle系统设计早考虑到参数影响范围这个问题会话级的修改 按照该会话的需要进行设置 不强加将参数实施到别的会话 会话级参数的修改有2 命令方式与API方法 2020 2 19 53 参数设置与API 会话级修改参数方法1 方法1 ALTERSESSIONset 会话级修改参数方法2 DBMS SYSTEM包 DBMS SYSTEM set bool param in sessionDBMS SYSTEM set int param in session 会话级参数设置 altersessionsetworkarea size policy manual EXECUTEDBMS SYSTEM SET INT PARAM IN SESSION 9 27 sort area size 65535000 2020 2 19 54 会话级可修改的参数 可用ALTERSESSION修改的参数 ISSES MODIFIABLE TRUE会话级可修改查询会话级可修改的参数 11gR1colnamefora50colvaluefora20selectname value ISSES MODIFIABLEFROMV PARAMETERWHEREISSES MODIFIABLE TRUE NAMEVALUEISSES timed statisticsTRUETRUEtimed os statistics0TRUEnls languageAMERICANTRUEnls territoryAMERICATRUE asm power limit1TRUEsqltune categoryDEFAULTTRUEtracefile identifierTRUEmax dump file sizeunlimitedTRUE已选择106行 2020 2 19 55 会话级可修改的参数 可用ALTERSESSION修改的参数 ISSES MODIFIABLE TRUE会话级可修改查询会话级可修改的参数 SQL select fromv version BANNER OracleDatabase11gEnterpriseEditionRelease11 2 0 1 0 ProductionPL SQLRelease11 2 0 1 0 ProductionCORE11 2 0 1 0ProductionTNSfor32 bitWindows Version11 2 0 1 0 ProductionNLSRTLVersion11 2 0 1 0 ProductionSQL selectname value ISSES MODIFIABLE2FROMV PARAMETERWHEREISSES MODIFIABLE TRUE NAMEVALUEISSES timed statisticsTRUETRUEtimed os statistics0TRUE max dump file sizeunlimitedTRUE已选择158行 2020 2 19 56 DBMS SYSTEM包 DBMS SYSTEM包有很强的系统管理功能 set bool