《oracle_SQL语句优化.ppt》由会员分享，可在线阅读，更多相关《oracle_SQL语句优化.ppt（82页珍藏版）》请在金锄头文库上搜索。
1、SQL语句优化 李俊逸 SAKYA Mobile 13910113943Mail lijunyi MSN ljy java QQ 40259908 2 SQL语句优化 SQL语句的优化器调优SQL语句的相关工具SQL语句的数据访问方法收集CBO所需的统计信息RBO模式下的规则和建议影响SQL语句的执行计划 3 什么是Optimizer 优化器 Optimizer 是一组Oracle的内部例行程序 该例行程序用以决定SQL语句的最有效的访问路径 Oracle数据库会自动进行查询优化 这些操作对于用户来说是透明的优化器模式通过初始化参数设置OPTIMIZER MODE 4 优化模式 在Oracle
2、9i 两种优化模式可以被选择 RBO 基于规则的Rule based使用一个分级系统语法 Syntax 驱动和字典 Dictionary 驱动的Oracle从早期版本提供RBO优化器 至Oracle10g该优化器不再被支持CBO 基于代价的Cost based选择最低代价的路径统计 Statistics 驱动从Oracle7 1992 Oracle10g主要支持的优化器 5 设置优化模式 在实例级 optimizer mode choose rule first rows first rows n all rows 在会话级 altersessionsetoptimizer mode choo
3、se rule first rows first rows n all rows 在语句级 使用提示 hints FIRST ROWS x 6 What sNewInOracle10g RBO不再被支持缺省的优化器模式为ALL ROWSOptimizer modeCHOOSE RULE参数不再支持ALL ROWS FIRST ROWS FIRST ROWS N是Oralce10g支持的优化目标 Connectedto OracleDatabase10gEnterpriseEditionRelease10 1 0 2 0 64bitProductionWiththePartitioning OL
4、APandDataMiningoptionsSQL showparameteroptimizer modeNAMETYPEVALUE optimizer modestringALL ROWS 7 WhyRBOisDead SQL selectt1 object name t2 object name2frombig tablet1 big tablet23wheret1 object id t2 object id4andt1 owner WMSYS 5 ExecutionPlan 0SELECTSTATEMENTOptimizer ALL ROWS Cost 619Card 10989Byt
5、es 670329 10HASHJOIN Cost 619Card 10989Bytes 670329 21TABLEACCESS FULL OF BIG TABLE TABLE Cost 309Card 1000Bytes 39000 31TABLEACCESS FULL OF BIG TABLE TABLE Cost 304Card 99989Bytes 2199758 SQL select rule t1 object name t2 object name2frombig tablet1 big tablet23wheret1 object id t2 object id4andt1
6、owner WMSYS 5 ExecutionPlan 0SELECTSTATEMENTOptimizer HINT RULE10TABLEACCESS BYINDEXROWID OF BIG TABLE TABLE 21NESTEDLOOPS32TABLEACCESS FULL OF BIG TABLE TABLE 42INDEX RANGESCAN OF OBJECT ID IDX INDEX 8 Continue CBO callcountcpuelapseddiskquerycurrentrows Parse10 010 000000Execute10 000 000000Fetch1
7、6520 940 9426364304024763 total16540 950 9526364304024763 RBO callcountcpuelapseddiskquerycurrentrows Parse10 010 000000Execute10 000 000000Fetch165257 6956 2615441202607024763 total165457 7056 2715441202607024763 9 诊断工具概述 STATSPACK AWR ADDMEXPLAINPLANSQLtrace和TKPROFSQL Plusautotrace特性OracleSQLAnaly
8、zeSQLTuningadvisorSQLAccessadvisor 10 在STATSPACK中关于SQL的报告 STATSPACK收集了下列关于SQL的信息 SQLorderedbygetsSQLorderedbyreadsSQLorderedbyexecutionsSQLorderedbyparsecalls 11 EXPLAINPLAN 不需要打开跟踪便可以使用需要PLAN TABLE表 创建解释表 查询plan table表显示执行计划 直接查询PLAN TABLE使用脚本utlxpls sql 隐藏了并行查询的信息 使用脚本utlxplp sql 显示并行查询信息 SQL ORAC
9、LE HOME rdbms admin utlxplan SQL Explainplanfor2selectlast namefromhr employees 12 TheEXPLAINPLANCommand SETSTATEMENT ID text INTOyourplantable EXPLAINPLAN FORstatement 13 显示执行计划 SQL selectid lpad 2 level operation decode id 0 Cost position options object nameas QueryPlan fromplan tablewherestatemen
10、t id demo01 connectbypriorid parent idstartwithid 0 IDQueryPlan 0SELECTSTATEMENTCost 1TABLEACCESSBYINDEXROWIDPRODUCTS2AND EQUAL3INDEXRANGESCANPRODUCTS PROD CAT IX4INDEXRANGESCANPRODUCTS PROD SUBCAT IX 14 查询V SQL PLAN SELECTid lpad depth operationoperation options object name optimizer costFROMV SQL
11、PLANWHEREhash value 912244748ANDaddress 67D419DC STARTWITHid 0CONNECTBY priorid parent idANDpriorhash value hash valueANDpriorchild number child number ORDERSIBLINGSBYid position IDOPERATIONOPTIONSOBJECT NAMEOPTCOST 0SELECTSTATEMENTCHO1TABLEACCESSBYINDEXROWIDPRODUCTS2AND EQUAL3INDEXRANGESCANPRODUCTS
12、 PROD CAT IX4INDEXRANGESCANPRODUCTS PROD SUBCAT I 15 SQL PlusAUTOTRACE 创建PLAN TABLE表创建和授予Plustrace角色Autotrace语法 SQL ORACLE HOME sqlplus admin plustrce sqlSQL grantplustracetoscott setautotrace off on traceonly explain statistics 16 使用SQLTrace和TKPROF 为了使用SQLtrace和TKPROF 设置初始化参数 AltersessionsetSQL Tra
13、ce true 运行应用 AltersessionsetSQL Trace false 使用TKPROF格式化显示跟踪文件的内容 解释输出的结果 17 启用或禁用SQLTrace 在实例级 SQL TRACE TRUE FALSE 在会话级 SQL altersessionsetSQL TRACE true false SQL executeDBMS SESSION SET SQL TRACE true false SQL executeDBMS SYSTEM SET SQL TRACE IN SESSION session id serial id true false 其它方法 数据库的S
14、ETEVENTsaltersessionsetevents 10046tracenamecontextforever level8 oradebugoradebugsetospidoradebugevent10046tracenamecontextforever level8 18 格式化跟踪文件 TKPROFcommandexamples OS tkprofOS tkprofora 901 trcrun1 txtOS tkprofora 901 trcrun2 txtsys nosort execpuprint 3 OS tkproftracefileoutputfile options 1
15、9 TKPROF提供的统计信息 Count 执行调用的次数CPU CPU使用的秒数Elapsed 总的持续时间Disk 物理读数Query 一致性读 consistentread 的逻辑读数Current 当前模式 currentmode 的逻辑读数Rows 处理的行数 20 SQLTuningAdvisor概述 添加遗漏的索引运行访问顾问 重建SQL 计划调整 SQL配置文件 自动调整优化器 SQL分析优化模式 访问分析优化模式 计划调整优化模式 统计信息检查优化模式 检测陈旧或遗漏的统计信息 广泛的SQL调整 SQLTuningAdvisor 21 SQL优化指导 22 顶级SQL 23
16、运行SQL优化指导 24 数据访问方法 为了提高数据访问性能 可以选用下列数据访问方法 聚簇表 Clusters索引组织表 Index organizedtables索引 IndexesB树B tree 普通或者反转 位图Bitmap基于函数的Functionbased物化视图 Materializedviews 25 索引访问方法 IndexrowidscanFastIndexscanSkipindexscan 26 ROWIDs ROWIDsindicatetheaddressofarow ExtendedandrestrictedROWIDsareavailable EachtablehasaROWIDpseudocolumn SQL selectrowid c fromchannelsc ROWIDCCHANNEL DESCCHANNEL CLASS AAABQDAABAAAJ4yAAASDirectSalesDirectAAABQDAABAAAJ4yAABTTeleSalesDirectAAABQDAABAAAJ4yAACCCatalogIndirectAAABQDAABAA
