Oracle 10G SQL Tuning

Oracle10gSQLTuning 李小芹 目录 1 Oracle数据库结构2 SQL调优的主要方法搜集统计信息执行计划的查看确定运行速度慢的语句使用索引使用hints使用物化视图 OracleDatabaseArchitecture Twomaincomponents Thedatabase physicalstructuresTheinstance memorystructuresThesizeandstructureofthesecomponentsimpactperfomance OracleDatabaseArchitecture Thedatabase Thecontrolfile Theredologfiles Thedatafiles Theparameterfile ThepasswordfileTheinstance SGA PGA backgroundprocesses serverprocesses OracleDatabaseArchitecture SGA Databasebuffercache CachesblocksofdataretrievedfromthedatafilesRedologbuffer Cachesredoinformation usedforinstancerecovery untilitcanbewrittentothephysicalredologfilesstoredondiskSharedpool Cachesvariousconstructsthatcanbesharedamongusers SGA Largepool OptionalareaintheSGAthatprovideslargememoryallocationsforOraclebackupandrestoreoperations I Oserverprocesses andsessionmemoryforthesharedserverJavapool Usedforallsession specificJavacodeanddatawithintheJavaVirtualMachine JVM Streamspool UsedbyOracleStreams SGA Oracle10g中可以选择手动或者自动的方式来管理SGA及各部分的大小 默认为自动管理 ASSM OracleDatabase10gusesanSGAsizeparameter SGA TARGET thatincludesallthememoryintheSGA includingalltheautomaticallysizedcomponents manuallysizedcomponents andanyinternalallocationsduringstartup ASMMsimplifiestheconfigurationoftheSGAbyenablingyoutospecifyatotalmemoryamounttobeusedforallSGAcomponents TheOracleDatabasethenperiodicallyredistributesmemorybetweenthesecomponentsaccordingtoworkloadrequirements PGA TheProgramGlobalArea PGA isamemoryregion whichcontainsdataandcontrolinformationforeachserverprocess Aserverprocessisaprocessthatservicesaclient srequests EachserverprocesshasitsownprivatePGAthatiscreatedwhentheserverprocessisstarted OnlyaserverprocesscanaccessitsownPGA 搜集统计信息 Oracle优化器会根据系统表中搜集到的统计信息生产执行计划 所以需要对数据库对象定期进行搜集统计信息统计信息包括 对象的统计信息 表索引列系统的统计信息 I O性能CPU性能 搜集统计信息 Oracle10G有个自动搜集统计信息的作业 gather stats job每天自动运行对数据库中的每个对象都进行搜集可以通过系统视图查看是否启用 SELECT FROMDBA SCHEDULER JOBSWHEREJOB NAME GATHER STATS JOB 可以修改成启用或者不启用 BEGINDBMS SCHEDULER DISABLE GATHER STATS JOB END BEGINDBMS SCHEDULER ENABLE GATHER STATS JOB END 可以设置搜集的频率可以设置搜集的方式 比如设置成抽样搜集或者DML monitoring等 搜集统计信息 手工搜集统计信息 使用dbms stats包搜集表的统计信息 BEGINDBMS STATS GATHER TABLE STATS OWNER TABNAME END 锁定表的统计信息 BEGINDBMS STATS LOCK TABLE STATS OWNER TABNAME END 锁定schema的统计信息 BEGINDBMS STATS LOCK SCHEMA STATS OWNER TABNAME END 查看表的搜集统计信息状态 SELECT FROMDBA TAB STATISTICS 执行计划 执行计划是执行SQL语句的一些步骤查看执行计划 1 explainplan2 SQLtrace3 Statspack4 AutomaticWorkloadRepository5 v sql plan6 SQL PlusAUTOTRACE EXPLAINPLAN 产生执行计划执行计划存储在plan表里并没有真正执行语句执行完explainplan语句之后要用dbms xplan包的方法获取执行步骤执行 ExplainplanforSQL语句查看 selectplan table outputfromtable dbms xplan display V SQL PLAN V sql plan视图用来查看执行过的SQL语句的执行计划 可以查看不同用户下执行的相同语句的执行计划 V SQL PLAN视图包括adress hash value等跟explainplan相同的值 要查看完整的执行计划数也需要调用dbms xplan包 selectplan table outputfromtable dbms xplan display cursor V sql plan statistics 存储生成执行计划时的统计信息 AWR AWR报告包括对象的统计信息 系统和会话的统计信息等通过快照来记录可以调用dbms workload repository包来产生快照和管理快照可以通过系统视图查看快照的情况 Dba hist snapshotDba hist sql planDba hist database instance查询AWR selectplan table outputfromtable dbms xplan display awr sql id SQL PlusAutotrace SetautotraceoffSetautotraceonSetautotracetraceonlySetautotracetraceonlyexplain 不显示statisticsSetautotracetraceonlystatistics 只显示statistics可以在会话级别开启sqltrace功能 产生trace文件Altersessionsetsql tracetrue 格式化trace文件 TKPROFTkproftracefileoutputfile 索引 1 索引类型 unique和nounique索引复合索引按存储分类 B treeBitmap 不能用merge DomainindexesKeycompression 索引 一般何时需要建立索引 1 经常在检索和查询语句中出现 where子句 2 连接表的关键字3 外键4 高选择度的关键字 此字段相同的值多 5 经常被update的关键字不适合建立索引注 索引里面不记录空值 即如果查询isnull时不会走索引 DML对索引的影响 1 insert 可能引起分列 如果insert的值超过block的最大值 则要用另一个block存储 用第一个block指向新的block2 delete 索引相应删除3 先delete后insert 对索引的影响最大 索引监控 索引扫描只能走索引查询 根据rowid查询数据 而全表扫面可以多块读 即可以并行 监控索引是否使用 alterindexindex namemonitoringusage alterindexindex namenomonitoringusage 查看系统视图 select fromv object usagewhereindex name Hints 1 Hints用来影响优化器的决定 改变执行计划 2 Hints的种类 单个表 use index 多个表 useNL table1table2 整个语句 ALL ROWS 3 语法 merge select update delete insert 或者 merge select update delete insert 注 1 不会提示错误 如果书写错误 则不执行2 如果对象有别名 一定要使用别名3 视图一般不要使用hints 如果要加可以加上视图名称 物化视图 物化视图是一组查询结果集 有自己的存储数据段 可以建立索引 一般用来建议复杂的查询和聚合数据 使用物化视图的好处 1 节省存储2 支持查询重写 效率高3 可以自动刷新 物化视图 物化视图 Buildimmediate 创建视图的时候生成数据 builddeferred 创建视图的时候只创建结构 并不生成数据 直到第一次更新 默认为buildimmediateOnprebuilttable 可以用之前已经存在的表作为数据源 表名要跟视图名一样Enable disablequeryrewrite 是否支持查询重写 Oracle10G默认为enable 物化视图 物化视图刷新方式 COMPLETE 全量刷新FAST 增量刷新FORCE 如果可以进行增量刷新则使用增量刷新 不然就用全量刷新NEVER 从不刷新注 可以通过系统视图ALL MVIEWS的refresh method查看某个视图的刷新方式 物化视图 物化视图刷新形式 手动 ondemand default 自动同步 oncommit自动异步 startwithnext注 可以通过系统视图ALL MVIEWS的refresh mode查看某个