Oracle数据库培训_4.ppt

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 sqlPLUSTRACE授予用户创建PLAN TABLESETAUTOTRACE命令获得计划和统计数字SETAUTOT RACE OFF ON TRACE ONLY EXP LAIN STAT ISTICS SETTIMINGON OFF获得执行时间 Cache执行计划 v sql v sqlarea v sqltextv 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适合于相对静态数据 如OLAP 通过ROWID的单行访问通过簇连接的单行访问通过散列键或主关键字的单行访问通过主关键字的单行访问簇连接散列簇键索引簇键复合键单列索引在索引列上的有边界搜索在索引列上的无边界搜索排序合并连接索引列的最大到最小通过索引列排序全表扫描 优化路径 分析数据由DBA执行ANALYZE命令获得 分析数据包括表 索引和列的数据ANALYZETABLEANALYZEINDEX分析查看数据 TABLES INDEXES TAB COLUMNS 分析数据 语法 ANALYZETABLECOMPUTSTATISTICSANALYZETABLEESTIMATESTATISTICSSAMPLEPERCENT ROWSANALYZETABLEDELETESTATISTICS注 不要分析SYS表 超过50 或超过总行数一半时进行全表统计 分析表的同时 系统分析列和索引 表的分析 表的分析 ANALYZETABLEempCOMPUTSTATISTICS ANALYZETABLEempESTIMATESTATISTICSSAMPLE10PERCENT ANALYZETABLEempESTIMATESTATISTICSSAMPLE5ROWS ANALYZETABLEempDELETESTATISTICS 表的分析数据 TABLES相关列中 NUM ROWS表中行数BLOCKS使用块数EMPTY BLOCKS空闲块数AVG SPACE块中自由空间字节数CHAIN CNT链接行数AVG ROW LEN平均行长LAST ANALYZED最后一次分析 ANALYZETABLECOMPUTESTATISTICSFORCOLUMNS SIZE ANALYZETABLECOMPUTESTATISTICSFORALLINDEXEDCOLUMNS SIZE ANALYZETABLEESTIMATESTATISTICSSAMPLEPERCENT ROWSFORCOLUMNS SIZE 注 SIZE子句是直方图的 bucket 数量 数值分断 列分析时缺省为75 表分析时为1 列分析用于分布不均 查询条件使用固定值的列 列分析 例 ANALYZETABLEempCOMPUTESTATISTICSFORCOLUMNSjobSIZE5 列分析数据 TAB COLUMNS TAB COL STATISTICSNUM DISTINCT列中唯一值数量LOW VALUE列中最小值 二进制数的前32个字节 HIGH VALUE列中最大值 二进制数的前32个字节 DENSITY列的密度NUM NULLS列的空值数NUM BUCKET分配给列的存储桶数 直方图数据 TAB HISTOGRAMSTABLE NAMECOLUMN NAMEENDPOINT NUMBERENDPOINT VALUEENDPOINT ACTUAL VALUE注 ENDPOINT NUMBER 0为最小值 索引分析 ANALYZEINDEXCOMPUTSTATISTICSANALYZEINDEXESTIMATESTATISTICSSAMPLEPERCENT ROWSANALYZEINDEXDELETESTATISTICS 索引分析数据 INDEXESBLEVEL索引的层次LEAF BLOCKS叶的块数DISTINCT KEYS唯一索引的值AVG LEAF BLOCKS PER KEY包括每个键的平均叶数AVG DATA BLOCK PER KEY每个键对应的数据块数 分析包 DBMS UTILITY ANALYZE SCHEMA DBMS STATS ANALYZE SCHEMA Schema用户模式MethodESTIMATE COMPUTE DELETEEstimate 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 ALL ROWS 在对象分析数据存在时使用CBO 且选择最大吞吐量的执行计划 用于大规模的DSS 模式选项 优化模式缺