SQL优化器相关知识课件

SQL优化器SQL优化器1SQL计划在执行SQL语句前，系统根据优化方式转换为执行方案，称为SQLPLAN。SQL语句和SQLPLAN都存储在SHAREDPOOL，并产生一个HASH值。DBA对不同的优化后，通过SQLPLAN进行比较后获得最优的方案。SQL计划在执行SQL语句前，系统根据优化方式转换为执行方案2EXPLAINPLAN使用PLAN_TABLE表存储SQL计划创建PLAN_TABLE UTLXPLAN.SQL获得SQL的执行计划 EXPLAINPLANFORSELECT…命令查询PLAN_TABLE UTLXPLS.SQLselectplan_table_outputfromtable(dbms_xplan.display('PLAN_TABLE',null,'serial'));

EXPLAINPLAN使用PLAN_TABLE表存储SQL3SQL*PLUS的AUTOTRACE创建PLUSTRACE角色 sqlplus\admin\plustrce.sqlPLUSTRACE授予用户创建PLAN_TABLESETAUTOTRACE命令获得计划和统计数字 SETAUTOT[RACE] {OFF|ON|TRACE[ONLY]}[EXP[LAIN]][STAT[ISTICS]]SETTIMINGON|OFF获得执行时间SQL*PLUS的AUTOTRACE创建PLUSTRACE角4Cache执行计划v$sql、v$sqlarea、v$sqltextv$sql_plan LibraryCache中装载的子游标的执行计划

v$sql_plan_statistics LibraryCache中装载的子游标的每行执行计划的统计数

v$sql_plan_statistics_all LibraryCache中装载的子游标的每行执行计划的统计数，包括内存的使用（v$sql_workarea)Cache执行计划v$sql、v$sqlarea、v$sql5Cache执行计划SELECTsql_text,address,hash_value,child_number FROMv$sqlWHEREsql_textlike‘…’-selectlpad('',2*(level-1))||operation||''||options||''||object_name||''||decode(object_node,'','','['||object_node||']')||decode(optimizer,'','','['||optimizer||']')||decode(id,0,'Cost='||position)queryfrom(select*fromv$sql_planwhereaddress='…'andhash_value=‘….'andchild_number=…)startwithid=0connectbypriorid=parent_idCache执行计划SELECTsql_text,addre6两种优化器基于规则（RBO）:根据数据字典按系统预设的优化路径给出优化的SQL方案RBO适合于动态数据，如OLTP基于成本（CBO）:根据分析数据结果给出最优化路径。使用CBO，DBA需要及时对象进行分析，如果使用过时的分析数据可能产生不可估计的结构。CBO适合于相对静态数据，如OLAP两种优化器基于规则（RBO）:7通过ROWID的单行访问通过簇连接的单行访问通过散列键或主关键字的单行访问通过主关键字的单行访问簇连接散列簇键索引簇键复合键单列索引在索引列上的有边界搜索在索引列上的无边界搜索排序合并连接索引列的最大到最小通过索引列排序全表扫描优化路径通过ROWID的单行访问优化路径8分析数据由DBA执行ANALYZE命令获得。分析数据包括表、索引和列的数据 ANALYZETABLE ANALYZEINDEX分析查看数据 <DBA|ALL|USER>_TABLES <DBA|ALL|USER>_INDEXES <DBA|ALL|USER>_TAB_COLUMNS

分析数据分析数据由DBA执行ANALYZE命令获得。分析数据9语法：ANALYZETABLE<表>COMPUTSTATISTICSANALYZETABLE<表>ESTIMATESTATISTICS SAMPLE<值>PERCENT|ROWSANALYZETABLE<表>DELETESTATISTICS注：不要分析SYS表；超过50%或超过总行数一半时进行全表统计；分析表的同时，系统分析列和索引。表的分析语法：表的分析10表的分析ANALYZETABLEempCOMPUTSTATISTICS;ANALYZETABLEempESTIMATESTATISTICS SAMPLE10PERCENT;ANALYZETABLEempESTIMATESTATISTICS SAMPLE5ROWS;ANALYZETABLEempDELETESTATISTICS;表的分析ANALYZETABLEempCOMPUTS11表的分析数据 <DBA|ALL|USER>_TABLES相关列中。 NUM_ROWS 表中行数 BLOCKS 使用块数 EMPTY_BLOCKS 空闲块数 AVG_SPACE 块中自由空间字节数 CHAIN_CNT 链接行数 AVG_ROW_LEN 平均行长 LAST_ANALYZED 最后一次分析表的分析数据 <DBA|ALL|USER>_TABLES12ANALYZETABLE<表>COMPUTESTATISTICS FORCOLUMNS<列>[SIZE<n>],…ANALYZETABLE<表>COMPUTESTATISTICS FORALLINDEXEDCOLUMNS[SIZE<n>]ANALYZETABLE<表>ESTIMATESTATISTICS SAMPLE<值>PERCENT|ROWS FORCOLUMNS<列>[SIZE<n>]注：SIZE子句是直方图的“bucket”数量（数值分断），列分析时缺省为75，表分析时为1。列分析用于分布不均，查询条件使用固定值的列列分析ANALYZETABLE<表>COMPUTESTAT13例ANALYZETABLEempCOMPUTESTATISTICS FORCOLUMNSjobSIZE5例ANALYZETABLEempCOMPUTE14列分析数据

<DBA|ALL|USER>_TAB_COLUMNS

<DBA|ALL|USER>_TAB_COL_STATISTICSNUM_DISTINCT列中唯一值数量LOW_VALUE 列中最小值（二进制数的前32个字节）HIGH_VALUE 列中最大值（二进制数的前32个字节）DENSITY 列的密度NUM_NULLS 列的空值数NUM_BUCKET分配给列的存储桶数列分析数据 <DBA|ALL|USER>_TAB_COL15直方图数据<DBA|ALL|USER>_TAB_HISTOGRAMS

TABLE_NAMECOLUMN_NAMEENDPOINT_NUMBER ENDPOINT_VALUEENDPOINT_ACTUAL_VALUE注： ENDPOINT_NUMBER=0为最小值直方图数据<DBA|ALL|USER>_TAB_HISTO16索引分析ANALYZEINDEX<index>COMPUTSTATISTICSANALYZEINDEX<index>ESTIMATESTATISTICS SAMPLE<n>PERCENT|ROWSANALYZEINDEX<index>DELETESTATISTICS索引分析ANALYZEINDEX<index>COMP17索引分析数据<DBA|ALL|USER>_INDEXES

BLEVEL 索引的层次LEAF_BLOCKS 叶的块数DISTINCT_KEYS 唯一索引的值AVG_LEAF_BLOCKS_PER_KEY 包括每个键的平均叶数AVG_DATA_BLOCK_PER_KEY 每个键对应的数据块数索引分析数据<DBA|ALL|USER>_INDEXES18分析包

DBMS_UTILITY.ANALYZE_SCHEMA(）DBMS_STATS分析包

DBMS_UTILITY.ANALYZE_SCHEM19ANALYZE_SCHEMASchema 用户模式Method ESTIMATE,COMPUTE,DELETEEstimate_row 行数Estimate_percent 百分比Method_opt FORTABLE FORALLINDEXEDCOLUMNS例： DBMS_UTILITY.ANALYZE_SCHEMA('scott','compute');ANALYZE_SCHEMASchema 用户模式20DBMS_STATS包全分析SCOTT用户对象 dbms_stats.gather_schema_stats(ownname=>'SCOTT',cascade=>TRUE)抽样分析SCOTT用户对象 dbms_stats.gather_schema_stats(ownname=>'SCOTT',estimate_percent=>10,cascade=>TRUE);

删除SCOTT用户对象分析 dbms_stats.delete_schema_stats(ownname=>'SCOTT');DBMS_STATS包全分析SCOTT用户对象21DBMS_STATS包导出SCOTT用户对象分析数据 dbms_stats.create_stat_table(ownname,stattab,tablespace) dbms_stats.export_schema_stats(ownname,stattab)导入SCOTT用户对象分析数据 dbms_stats.import_schema_stats(ownname,stattab)DBMS_STATS包导出SCOTT用户对象分析数据22使用OEM分析使用OEM分析23设置表的监视系统自动收集表的UPATE、DELETE、INSERT和TRUCATE的行数，用于DBMS_STATS自动分析STALE表（10%改变行），需要在GATHERSTATS过程中设置“GATHERSTALE”选项。

语法： CREATE|ALTERTABLE…MONITORING dbms_stats.alter_database_tab_monitoring() dbms_stats.alter_schema_tab_monitoring() dbms_stats.flush_database_monitoring_info() dbms_stats.flush_schema_monitoring_info()

查询: <DBA_ALL|USER_TAB_MODIFICATIONS设置表的监视系统自动收集表的UPATE、DELETE、INS24设置优化模式实例级 optimizer_mode= RULE|CHOOSE|FIRST_ROWS|ALL_ROWS对话级 ALTERSESSIONSEToptimizer_goal= RULE|CHOOSE|FIRST_ROWS|ALL_ROWS语句级 使用SQL提示设置优化模式实例级25模式选项CHOOSE：在对象分析数据存在时CBO，否则使用RBO。CHOOSE是缺省值。RULE：基于规则方式。FIRST_ROWS: 在对象分析数据存在时使用CBO，且选择响应时间最短执行计划。用于OLTP和小型的DSS。9i提供FIRST_ROWS_<n>。ALL_ROWS:在对象分析数据存在时使用CBO，且选择最大吞吐量的执行计划。用于大规模的DSS。模式选项CHOOSE：在对象分析数据存在时CBO，否则使用R26模式选项 优化模式缺省是为CHOOSE。SQL语句涉及多个对象时，如果其中一个对象有分析数据（num_rows非空）则使用CBO，如果其它对象没有分析数据，则系统会对其它对象进行抽样临时分析，会降低执行效率。模式选项 优化模式缺省是为CHOOSE。SQL语句涉及多个对27SQL提示在SQL动词后面加入暗示语句。 格式：/*+提示*/或--+提示/ 优化方式： CHOOSE RULE FIRST_ROWS FIRST_ROWS_<n> ALL_ROWS例： SELECT/*+RULE*/*FROMEMP WHEREEMPNO=7934SQL提示在SQL动词后面加入暗示语句。28SQL优化器SQL优化器29SQL计划在执行SQL语句前，系统根据优化方式转换为执行方案，称为SQLPLAN。SQL语句和SQLPLAN都存储在SHAREDPOOL，并产生一个HASH值。DBA对不同的优化后，通过SQLPLAN进行比较后获得最优的方案。SQL计划在执行SQL语句前，系统根据优化方式转换为执行方案30EXPLAINPLAN使用PLAN_TABLE表存储SQL计划创建PLAN_TABLE UTLXPLAN.SQL获得SQL的执行计划 EXPLAINPLANFORSELECT…命令查询PLAN_TABLE UTLXPLS.SQLselectplan_table_outputfromtable(dbms_xplan.display('PLAN_TABLE',null,'serial'));

EXPLAINPLAN使用PLAN_TABLE表存储SQL31SQL*PLUS的AUTOTRACE创建PLUSTRACE角色 sqlplus\admin\plustrce.sqlPLUSTRACE授予用户创建PLAN_TABLESETAUTOTRACE命令获得计划和统计数字 SETAUTOT[RACE] {OFF|ON|TRACE[ONLY]}[EXP[LAIN]][STAT[ISTICS]]SETTIMINGON|OFF获得执行时间SQL*PLUS的AUTOTRACE创建PLUSTRACE角32Cache执行计划v$sql、v$sqlarea、v$sqltextv$sql_plan LibraryCache中装载的子游标的执行计划

v$sql_plan_statistics LibraryCache中装载的子游标的每行执行计划的统计数

v$sql_plan_statistics_all LibraryCache中装载的子游标的每行执行计划的统计数，包括内存的使用（v$sql_workarea)Cache执行计划v$sql、v$sqlarea、v$sql33Cache执行计划SELECTsql_text,address,hash_value,child_number FROMv$sqlWHEREsql_textlike‘…’-selectlpad('',2*(level-1))||operation||''||options||''||object_name||''||decode(object_node,'','','['||object_node||']')||decode(optimizer,'','','['||optimizer||']')||decode(id,0,'Cost='||position)queryfrom(select*fromv$sql_planwhereaddress='…'andhash_value=‘….'andchild_number=…)startwithid=0connectbypriorid=parent_idCache执行计划SELECTsql_text,addre34两种优化器基于规则（RBO）:根据数据字典按系统预设的优化路径给出优化的SQL方案RBO适合于动态数据，如OLTP基于成本（CBO）:根据分析数据结果给出最优化路径。使用CBO，DBA需要及时对象进行分析，如果使用过时的分析数据可能产生不可估计的结构。CBO适合于相对静态数据，如OLAP两种优化器基于规则（RBO）:35通过ROWID的单行访问通过簇连接的单行访问通过散列键或主关键字的单行访问通过主关键字的单行访问簇连接散列簇键索引簇键复合键单列索引在索引列上的有边界搜索在索引列上的无边界搜索排序合并连接索引列的最大到最小通过索引列排序全表扫描优化路径通过ROWID的单行访问优化路径36分析数据由DBA执行ANALYZE命令获得。分析数据包括表、索引和列的数据 ANALYZETABLE ANALYZEINDEX分析查看数据 <DBA|ALL|USER>_TABLES <DBA|ALL|USER>_INDEXES <DBA|ALL|USER>_TAB_COLUMNS

分析数据分析数据由DBA执行ANALYZE命令获得。分析数据37语法：ANALYZETABLE<表>COMPUTSTATISTICSANALYZETABLE<表>ESTIMATESTATISTICS SAMPLE<值>PERCENT|ROWSANALYZETABLE<表>DELETESTATISTICS注：不要分析SYS表；超过50%或超过总行数一半时进行全表统计；分析表的同时，系统分析列和索引。表的分析语法：表的分析38表的分析ANALYZETABLEempCOMPUTSTATISTICS;ANALYZETABLEempESTIMATESTATISTICS SAMPLE10PERCENT;ANALYZETABLEempESTIMATESTATISTICS SAMPLE5ROWS;ANALYZETABLEempDELETESTATISTICS;表的分析ANALYZETABLEempCOMPUTS39表的分析数据 <DBA|ALL|USER>_TABLES相关列中。 NUM_ROWS 表中行数 BLOCKS 使用块数 EMPTY_BLOCKS 空闲块数 AVG_SPACE 块中自由空间字节数 CHAIN_CNT 链接行数 AVG_ROW_LEN 平均行长 LAST_ANALYZED 最后一次分析表的分析数据 <DBA|ALL|USER>_TABLES40ANALYZETABLE<表>COMPUTESTATISTICS FORCOLUMNS<列>[SIZE<n>],…ANALYZETABLE<表>COMPUTESTATISTICS FORALLINDEXEDCOLUMNS[SIZE<n>]ANALYZETABLE<表>ESTIMATESTATISTICS SAMPLE<值>PERCENT|ROWS FORCOLUMNS<列>[SIZE<n>]注：SIZE子句是直方图的“bucket”数量（数值分断），列分析时缺省为75，表分析时为1。列分析用于分布不均，查询条件使用固定值的列列分析ANALYZETABLE<表>COMPUTESTAT41例ANALYZETABLEempCOMPUTESTATISTICS FORCOLUMNSjobSIZE5例ANALYZETABLEempCOMPUTE42列分析数据

<DBA|ALL|USER>_TAB_COLUMNS

<DBA|ALL|USER>_TAB_COL_STATISTICSNUM_DISTINCT列中唯一值数量LOW_VALUE 列中最小值（二进制数的前32个字节）HIGH_VALUE 列中最大值（二进制数的前32个字节）DENSITY 列的密度NUM_NULLS 列的空值数NUM_BUCKET分配给列的存储桶数列分析数据 <DBA|ALL|USER>_TAB_COL43直方图数据<DBA|ALL|USER>_TAB_HISTOGRAMS

TABLE_NAMECOLUMN_NAMEENDPOINT_NUMBER ENDPOINT_VALUEENDPOINT_ACTUAL_VALUE注： ENDPOINT_NUMBER=0为最小值直方图数据<DBA|ALL|USER>_TAB_HISTO44索引分析ANALYZEINDEX<index>COMPUTSTATISTICSANALYZEINDEX<index>ESTIMATESTATISTICS SAMPLE<n>PERCENT|ROWSANALYZEINDEX<index>DELETESTATISTICS索引分析ANALYZEINDEX<index>COMP45索引分析数据<DBA|ALL|USER>_INDEXES

BLEVEL 索引的层次LEAF_BLOCKS 叶的块数DISTINCT_KEYS 唯一索引的值AVG_LEAF_BLOCKS_PER_KEY 包括每个键的平均叶数AVG_DATA_BLOCK_PER_KEY 每个键对应的数据块数索引分析数据<DBA|ALL|USER>_INDEXES46分析包

DBMS_UTILITY.ANALYZE_SCHEMA(）DBMS_STATS分析包

DBMS_UTILITY.ANALYZE_SCHEM47ANALYZE_SCHEMASchema 用户模式Method ESTIMATE,COMPUTE,DELETEEstimate_row 行数Estimate_percent 百分比Method_opt FORTABLE FORALLINDEXEDCOLUMNS例： DBMS_UTILITY.ANALYZE_SCHEMA('scott','compute');ANALYZE_SCHEMASchema 用户模式48DBMS_STATS包全分析SCOTT用户对象 dbms_stats.gather_schema_stats(ownname=>'SCOTT',cascade=>TRUE)抽样分析SCOTT用户对象 dbms_stats.gather_schema_stats(ownname=>'SCOTT',estimate_percent=>10,cascade=>TRUE);

删除SCOTT用户对象分析 dbms_stats.delete_schema_stats(ownname=>'SCOTT');DBMS_STATS包全分析SCOTT用户对象49DBMS_STATS包导出SCOTT用户对象分析数据 dbms_stats.create_stat_table(ownname,stattab,tablespace) dbms_stats.export_schema_stats(ownname,stattab)导入SCOTT用户对象分析数据 dbms_stats.import_schema_stats(ownname,st