SQL 优化器

SQL优化器

SQL计划 • 在执行SQL语句前，系统根据优化方式转换为执行方案，称为SQL PLAN。 • SQL语句和SQL PLAN都存储在SHARED POOL，并产生一个HASH值。 • DBA对不同的优化后，通过SQL PLAN进行比较后获得最优的方案。

EXPLAIN PLAN • 使用PLAN_TABLE表存储SQL计划 • 创建PLAN_TABLE UTLXPLAN.SQL • 获得SQL的执行计划 EXPLAIN PLAN FOR SELECT…命令 • 查询PLAN_TABLE UTLXPLS.SQL select plan_table_output from table(dbms_xplan.display('PLAN_TABLE',null,'serial'));

SQL*PLUS的AUTOTRACE • 创建PLUSTRACE角色 sqlplus\admin\plustrce.sql • PLUSTRACE授予用户 • 创建PLAN_TABLE • SET AUTOTRACE命令获得计划和统计数字 SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]] • SET TIMING ON|OFF获得执行时间

Cache执行计划 • v$sql、v$sqlarea、v$sqltext • v$sql_plan Library Cache 中装载的子游标的执行计划 • v$sql_plan_statistics Library Cache 中装载的子游标的每行执行计划的统计数 • v$sql_plan_statistics_all Library Cache 中装载的子游标的每行执行计划的统计数，包括内存的使用（v$sql_workarea)

Cache执行计划 • SELECT sql_text,address,hash_value,child_number FROM v$sql WHERE sql_text like ‘…’ - select lpad(' ',2*(level-1))||operation||' '||options||' ' ||object_name||' ' ||decode(object_node,'','','['||object_node||'] ') ||decode(optimizer,'','','['||optimizer||'] ') ||decode(id,0,'Cost = '||position) query from (select * from v$sql_plan where address= ' …' and hash_value=‘….' and child_number=… ) start with id = 0 connect by prior id=parent_id

两种优化器 • 基于规则（RBO）: • 根据数据字典按系统预设的优化路径给出优化的SQL方案 • RBO适合于动态数据，如OLTP • 基于成本（CBO）: • 根据分析数据结果给出最优化路径。使用CBO，DBA需要及时对象进行分析，如果使用过时的分析数据可能产生不可估计的结构。 • CBO适合于相对静态数据，如OLAP

优化路径 • 通过ROWID的单行访问 • 通过簇连接的单行访问 • 通过散列键或主关键字的单行访问 • 通过主关键字的单行访问 • 簇连接 • 散列簇键 • 索引簇键 • 复合键 • 单列索引 • 在索引列上的有边界搜索 • 在索引列上的无边界搜索 • 排序合并连接 • 索引列的最大到最小 • 通过索引列排序 • 全表扫描

分析数据 • 分析数据由DBA执行ANALYZE命令获得。 • 分析数据包括表、索引和列的数据 ANALYZE TABLE ANALYZE INDEX • 分析查看数据 <DBA|ALL|USER>_TABLES <DBA|ALL|USER>_INDEXES <DBA|ALL|USER>_TAB_COLUMNS

表的分析 语法： ANALYZE TABLE <表> COMPUT STATISTICS ANALYZE TABLE <表> ESTIMATE STATISTICS SAMPLE <值> PERCENT|ROWS ANALYZE TABLE <表> DELETE STATISTICS 注： • 不要分析SYS表； • 超过50%或超过总行数一半时进行全表统计； • 分析表的同时，系统分析列和索引。

表的分析 ANALYZE TABLE emp COMPUT STATISTICS; ANALYZE TABLE emp ESTIMATE STATISTICS SAMPLE 10 PERCENT; ANALYZE TABLE emp ESTIMATE STATISTICS SAMPLE 5 ROWS; ANALYZE TABLE emp DELETE STATISTICS;

表的分析数据 • <DBA|ALL|USER> _TABLES相关列中。 • NUM_ROWS 表中行数 • BLOCKS 使用块数 • EMPTY_BLOCKS 空闲块数 • AVG_SPACE 块中自由空间字节数 • CHAIN_CNT 链接行数 • AVG_ROW_LEN 平均行长 • LAST_ANALYZED 最后一次分析

列分析 ANALYZE TABLE <表> COMPUTE STATISTICS FOR COLUMNS <列>[ SIZE <n>],… ANALYZE TABLE <表> COMPUTE STATISTICS FOR ALL INDEXED COLUMNS [ SIZE <n>] ANALYZE TABLE <表> ESTIMATE STATISTICS SAMPLE <值> PERCENT|ROWS FOR COLUMNS <列> [ SIZE <n> ] 注： • SIZE子句是直方图的“bucket”数量（数值分断），列分析时缺省为75，表分析时为1。 • 列分析用于分布不均，查询条件使用固定值的列

例 ANALYZE TABLE emp COMPUTE STATISTICS FOR COLUMNS job SIZE 5

列分析数据 <DBA|ALL|USER> _TAB_COLUMNS <DBA|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 为最小值

索引分析 ANALYZE INDEX <index> COMPUT STATISTICS ANALYZE INDEX <index> ESTIMATE STATISTICS SAMPLE <n> PERCENT|ROWS ANALYZE INDEX <index> DELETE STATISTICS

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

分析包 • DBMS_UTILITY.ANALYZE_SCHEMA(） • DBMS_STATS

ANALYZE_SCHEMA • Schema 用户模式 • Method ESTIMATE,COMPUTE,DELETE • Estimate_row 行数 • Estimate_percent 百分比 • Method_opt FOR TABLE FOR ALL INDEXED COLUMNS 例： 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%改变行），需要在GATHER STATS过程中设置“GATHER STALE”选项。语法： CREATE|ALTER TABLE … 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

模式选项 • 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动词后面加入暗示语句。格式： /*+ 提示* / 或 --+提示/ 优化方式： CHOOSE RULE FIRST_ROWS FIRST_ROWS_<n> ALL_ROWS 例： SELECT /*+ RULE*/ * FROM EMP WHERE EMPNO=7934

SQL 优化器

SQL 优化器

Presentation Transcript