搜索
SQL优化器SQL计划–在执行SQL语句前，系统根据优化方式转换为执行方案，称为SQLPLAN。–SQL语句和SQLPLAN都存储在SHAREDPOOL，并产生一个HASH值。–DBA对不同的优化后，通过SQLPLAN进行比较后获得最优的方案。EXPLAINPLAN–使用PLAN_TABLE表存储SQL计划–创建PLAN_TABLEUTLXPLAN.SQL–获得SQL的执行计划EXPLAINPLANFORSELECT…命令–查询PLAN_TABLEUTLXPLS.SQLselectplan_table_outputfromtable(dbms_xplan.display('PLAN_TABLE',null,'serial'));SQL*PLUS的AUTOTRACE–创建PLUSTRACE角色sqlplus\admin\plustrce.sql–PLUSTRACE授予用户–创建PLAN_TABLE–SETAUTOTRACE命令获得计划和统计数字SETAUTOT[RACE]{OFF|ON|TRACE[ONLY]}[EXP[LAIN]][STAT[ISTICS]]–SETTIMINGON|OFF获得执行时间Cache执行计划–v$sql、v$sqlarea、v$sqltext–v$sql_planLibraryCache中装载的子游标的执行计划–v$sql_plan_statisticsLibraryCache中装载的子游标的每行执行计划的统计数–v$sql_plan_statistics_allLibraryCache中装载的子游标的每行执行计划的统计数，包括内存的使用（v$sql_workarea)Cache执行计划–SELECTsql_text,address,hash_value,child_numberFROMv$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_id两种优化器–基于规则（RBO）:•根据数据字典按系统预设的优化路径给出优化的SQL方案•RBO适合于动态数据，如OLTP–基于成本（CBO）:•根据分析数据结果给出最优化路径。使用CBO，DBA需要及时对象进行分析，如果使用过时的分析数据可能产生不可估计的结构。•CBO适合于相对静态数据，如OLAP1.通过ROWID的单行访问2.通过簇连接的单行访问3.通过散列键或主关键字的单行访问4.通过主关键字的单行访问5.簇连接6.散列簇键7.索引簇键8.复合键9.单列索引10.在索引列上的有边界搜索11.在索引列上的无边界搜索12.排序合并连接13.索引列的最大到最小14.通过索引列排序15.全表扫描优化路径–分析数据由DBA执行ANALYZE命令获得。–分析数据包括表、索引和列的数据ANALYZETABLEANALYZEINDEX–分析查看数据DBA|ALL|USER_TABLESDBA|ALL|USER_INDEXESDBA|ALL|USER_TAB_COLUMNS分析数据语法：ANALYZETABLE表COMPUTSTATISTICSANALYZETABLE表ESTIMATESTATISTICSSAMPLE值PERCENT|ROWSANALYZETABLE表DELETESTATISTICS注：•不要分析SYS表；•超过50%或超过总行数一半时进行全表统计；•分析表的同时，系统分析列和索引。表的分析表的分析ANALYZETABLEempCOMPUTSTATISTICS;ANALYZETABLEempESTIMATESTATISTICSSAMPLE10PERCENT;ANALYZETABLEempESTIMATESTATISTICSSAMPLE5ROWS;ANALYZETABLEempDELETESTATISTICS;表的分析数据–DBA|ALL|USER_TABLES相关列中。•NUM_ROWS表中行数•BLOCKS使用块数•EMPTY_BLOCKS空闲块数•AVG_SPACE块中自由空间字节数•CHAIN_CNT链接行数•AVG_ROW_LEN平均行长•LAST_ANALYZED最后一次分析ANALYZETABLE表COMPUTESTATISTICSFORCOLUMNS列[SIZEn],…ANALYZETABLE表COMPUTESTATISTICSFORALLINDEXEDCOLUMNS[SIZEn]ANALYZETABLE表ESTIMATESTATISTICSSAMPLE值PERCENT|ROWSFORCOLUMNS列[SIZEn]注：•SIZE子句是直方图的“bucket”数量（数值分断），列分析时缺省为75，表分析时为1。•列分析用于分布不均，查询条件使用固定值的列列分析例ANALYZETABLEempCOMPUTESTATISTICSFORCOLUMNSjobSIZE5列分析数据DBA|ALL|USER_TAB_COLUMNSDBA|ALL|USER_TAB_COL_STATISTICS•NUM_DISTINCT列中唯一值数量•LOW_VALUE列中最小值（二进制数的前32个字节）•HIGH_VALUE列中最大值（二进制数的前32个字节）•DENSITY列的密度•NUM_NULLS列的空值数•NUM_BUCKET分配给列的存储桶数直方图数据DBA|ALL|USER_TAB_HISTOGRAMS•TABLE_NAME•COLUMN_NAME•ENDPOINT_NUMBER•ENDPOINT_VALUE•ENDPOINT_ACTUAL_VALUE注：ENDPOINT_NUMBER=0为最小值索引分析ANALYZEINDEXindexCOMPUTSTATISTICSANALYZEINDEXindexESTIMATESTATISTICSSAMPLEnPERCENT|ROWSANALYZEINDEXindexDELETESTATISTICS索引分析数据DBA|ALL|USER_INDEXES•BLEVEL索引的层次•LEAF_BLOCKS叶的块数•DISTINCT_KEYS唯一索引的值•AVG_LEAF_BLOCKS_PER_KEY包括每个键的平均叶数•AVG_DATA_BLOCK_PER_KEY每个键对应的数据块数分析包–DBMS_UTILITY.ANALYZE_SCHEMA(）–DBMS_STATSANALYZE_SCHEMA•Schema用户模式•MethodESTIMATE,COMPUTE,DELETE•Estimate_row行数•Estimate_percent百分比•Method_optFORTABLEFORALLINDEXEDCOLUMNS例：DBMS_UTILITY.ANALYZE_SCHEMA('scott','compute');DBMS_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用户对象分析数据dbms_stats.create_stat_table(ownname,stattab,tablespace)dbms_stats.export_schema_stats(ownname,stattab)–导入SCOTT用户对象分析数据dbms_stats.import_schema_stats(ownname,stattab)使用OEM分析设置表的监视•系统自动收集表的UPATE、DELETE、INSERT和TRUCATE的行数，用于DBMS_STATS自动分析STALE表（10%改变行），需要在GATHERSTATS过程中设置“GATHERSTALE”选项。语法：CREATE|ALTERTABLE…MONITORINGdbms_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设置优化模式–实例级optimizer_mode=RULE|CHOOSE|FIRST_ROWS|ALL_ROWS–对话级ALTERSESSIONSEToptimizer_goal=RULE|CHOOSE|FIRST_ROWS|ALL_ROWS–语句级使用SQL提示模式选项–CHOOSE：在对象分析数据存在时CBO，否则使用RBO。CHOOSE是缺省值。–RULE：基于规则方式。–FIRST_ROWS:在对象分析数据存在时使用CBO，且选择响应时间最短执行计划。用于OLTP和小型的DSS。9i提供FIRST_ROWS_n。–ALL_ROWS:在对象分析数据存在时使用CBO，且选择最大吞吐量的执行计划。用于大规模的DSS。模式选项优化模式缺省是为CHOOSE。SQL语句涉及多个对象时，如果其中一个对象有分析数据（num_rows非空）则使用CBO，如果其它对象没有分析数据，则系统会对其它对象进行抽样临时分析，会降低执行效率。SQL提示在SQL动词后面加入暗示语句。格式：/*+提示*/或--+提示/优化方式：CHOOSERULEFIRST_ROWSFIRST_ROWS_nALL_ROWS例：SELECT/*+RULE*/*FROMEMPWHEREEMPNO=7934