Oracle 10G SQL Tuning

Oracle10gSQLTuning李小芹高伟达软件技术有限公司目录1、Oracle数据库结构2、SQL调优的主要方法搜集统计信息执行计划的查看确定运行速度慢的语句使用索引使用hints使用物化视图高伟达软件技术有限公司OracleDatabaseArchitectureTwomaincomponents:Thedatabase:physicalstructuresTheinstance:memorystructuresThesizeandstructureofthesecomponentsimpactperfomance.高伟达软件技术有限公司OracleDatabaseArchitectureThedatabase:--Thecontrolfile--Theredologfiles--Thedatafiles--Theparameterfile--ThepasswordfileTheinstance:--SGA--PGA--backgroundprocesses--serverprocesses高伟达软件技术有限公司OracleDatabaseArchitecture高伟达软件技术有限公司SGADatabasebuffercache:CachesblocksofdataretrievedfromthedatafilesRedologbuffer:Cachesredoinformation(usedforinstancerecovery)untilitcanbewrittentothephysicalredologfilesstoredondiskSharedpool:Cachesvariousconstructsthatcanbesharedamongusers高伟达软件技术有限公司SGALargepool:OptionalareaintheSGAthatprovideslargememoryallocationsforOraclebackupandrestoreoperations,I/Oserverprocesses,andsessionmemoryforthesharedserverJavapool: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、explainplan2、SQLtrace3、Statspack4、AutomaticWorkloadRepository5、v$sql_plan6、SQL*PlusAUTOTRACE高伟达软件技术有限公司EXPLAINPLAN产生执行计划执行计划存储在plan表里并没有真正执行语句执行完explainplan语句之后要用dbms_xplan包的方法获取执行步骤执行：ExplainplanforSQL语句查看：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_snapshotDba_hist_sql_planDba_hist_database_instance查询AWR：selectplan_table_outputfromtable(dbms_xplan.display_awr(sql_id));高伟达软件技术有限公司SQL*PlusAutotraceSetautotraceoffSetautotraceonSetautotracetraceonlySetautotracetraceonlyexplain：不显示statisticsSetautotracetraceonlystatistics:只显示statistics可以在会话级别开启sqltrace功能，产生trace文件Altersessionsetsql_tracetrue;格式化trace文件：TKPROFTkproftracefileoutputfile;高伟达软件技术有限公司索引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:创建视图的时候只创建结构，并不生成数据，直到第一次更新，默认为buildimmediateOnprebuilttable：可以用之前已经存在的表作为数据源，表名要跟视图名一样Enable/disablequeryrewrite:是否支持查询重写，Oracle10G默认为enable高伟达软件技术有限公司物化视图物化视图刷新方式：COMPLETE：全量刷新FAST：增量刷新FORCE：如果可以进行增量刷新则使用增量刷新，不然就用全量刷新NEVER：从不刷新注：可以通过系统视图ALL_MVIEWS的refresh_method查看某个视图的刷新方式高伟达软件技术有限公司物化视图物化视图刷新形式：手动：ondemand(default)自动同步：oncommit自动异步：startwithnext注：可以通过系统视图ALL_MVIEWS的refresh_mode查看某个视图的刷新方式高伟