SQL调优04–阅读执行计划

1月 14th, 2012

Interpreting Execution Plans

  1. 执行计划的解释:
    1. SQL语句的执行计划是由语句中行源的执行计划组成;
    2. 执行计划是使用父子关系来描述的,像一个树的结构;
  2. 如何查看执行计划:
    1. PLAN_TABLE:是由EXPLAIN PLAN命令或者SQL/PLUS的autotrace产生的执行计划,是理论上的执行计划;
    2. v$sql_plan:在Shared Pool中的Library Cache中保存的实际使用的执行计划;
    3. v$sql_plan_monitor:11g中的执行计划监控;
    4. dba_hist_sql_plan:由AWR报告产生的执行计划;
    5. stats$sql_plan:是由Statspack生成的执行计划;
    6. SQL Management Base:是由SQL Plan Management Baselines产生的执行计划;
    7. SQL tuning set;
    8. DBMS_MONITOR产生的trace文件:相当于10046事件;
    9. 由10053事件产生的trace文件;
    10. 10gR2之后的dump跟踪文件;
  3. 查看执行计划的视图:
    1. 如果直接查看基表的话,根本无法直接看到执行计划间的关系,自己编写SQL语句查看很麻烦,可以使用DBMS_XPLAN包下面的函数来完成;
    2. DBMS_XPLAN.DISPLAY():用来显示plan_table中的执行计划;
    3. DBMS_XPLAN.DISPLAY_CURSOR():用来显示v$sql_plan中的执行计划;
    4. DBMS_XPLAN.DISPLAY_AWR():用来显示AWR中的执行计划;
    5. DBMS_XPLAN.DISPLAY_SQLSET():用来显示SQL tuning set中的执行计划;
    6. DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE():用来显示SQL Plan Management Baselines中的执行计划;
  4. EXPLAIN TABLE命令:
    1. 生成一个最优的执行计划,把它存在PLAN_TABLE中,但是并不实际执行SQL语句;
    2. 语法:EXPLAIN PLAN [SET STATEMENT_ID = ‘text’] [INTO plan_table] FOR statement;默认插入到PLAN_TABLE表中;
    3. PLAN_TABLE:
      1. 当执行EXPLAN_PLAN命令时自动创建PLAN_TABLE,它是一个同义词,指向sys.plan_table$的临时表;SELECT * FROM dba_synonyms WHERE synonym_name = ‘PLAN_TABLE’;SELECT table_name, TEMPORARY, duration FROM dba_tables WHERE table_name = ‘PLAN_TABLE$’;               
      2. 可以根据$ORACLE_HOME/rdbms/admin/utlxplan.sql脚本创建自己的表,因为默认是临时表,只能在当前session查看,导入到自己的表中就可以永久保存;
      3. 优点是SQL语句么有真正执行;缺点是可能不是真正的执行计划,只有使用绑定变量时执行计划不准,其它情况都准确;
      4. 表中的内容是层级结构,可以通过ID和PAREANT_ID列来关联;
    4. DBMS_XPLAN.DISPLAY函数语法:DBMS_XPLAN.DISPLAY(table_name, statement_id, format, filter_preds):
      1. table_name:默认是PLAN_TABLE表;
      2. statement_id:默认是空,可以根据这个参数获得指定的语句的执行计划;
      3. format:默认是TYPICAL类型,其他类型查帮助文档,显示的信息多少;
      4. 默认只查看上一条语句的执行计划;                                 
      5. 查看指定statement_id的执行计划;                            
      6. 查看更多的执行计划的信息;                                    
  5. AUTOTRACE:
    1. AUTOTRACE是sql*plus的功能,在oracle7.3版本后出现,也是把记录存放在PLAN_TABLE表中;
    2. 需要PLUSTRACE角色从v$视图中检索统计信息,使用$ORACLE_HOME/sqlplus/admin/plustrce.sql脚本创建;
    3. 默认情况下,在执行完查询语句后会生成执行计划和统计信息;
    4. 相当于执行了一次EXPLAIN PLAN命令然后执行了一次语句,如果使用绑定变量的话可能不是真实的计划;
    5. 语法:SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]];
      1. ON:要显示结果和trace信息;
      2. TRACEONLY:不显示结果;
    6. 查看当前的设置:show autotrace;
  6. 阅读统计信息:
    1. recursive calls:递归的调用,读取数据字典,权限,列的信息.第一次执行会很大,以后执行会变小;如果使用存储过的话,这个值一般会很大,属于正常;可以通过清除shared_pool测试:alter system flush shared_pool;
    2. db block gets:修改当前状态的数据块的block的块数.只有当DML语句会引起db block gets增加,因为当前块会被更新,SELECT语句的话不会增加,因为可以读取REDO或者构造的CR块;
    3. consistent gets:逻辑读的数量(不是BLOCK),表示返回记录的批次数,跟当前的arraysize有关;
      1. arraysize:表示一次返回的记录数,通过show arraysize命令查看;
      2. 粗略是算法是:consistent gets=rows processed/arraysize,记录越多越接近;
      3. 优化时应该关心在相同的arraysize下减小此值,即减小逻辑读;
    4. physical reads:物理读,即从硬盘读取的BLOCK的数量,BUFFER CACHE越大这个值越小,可以通过清除BUFFER CACHE测试:alter system flush buffer_cache;
    5. redo size:产生的日志的数量,一般DML语句才会产生;
    6. bytes sent via SQL*Net to client:服务器发送到客户端的字节数;
    7. bytes received via SQL*Net from client:服务器接收到客户端的字节数;
    8. SQL*Net roundtrips to/from client:SQL的网络流量的次数,也跟arraysize参数有关;
    9. sorts (memory):内存中的排序数量,主要是PGA;
    10. sorts (disk):在硬盘的排序,应该避免这个值;
    11. rows processed:处理的记录数;
  7. v$sql_plan:
    1. v$sql_plan:查看library cahce中真正使用的执行计划;PLAN_TABLE只是理论上的执行计划;
    2. 可以通过sql_id列与v$sql表关联,也可以使用address和hash_value的值;
    3. 主要的列:
      1. HASH_VALUE:父语句在library cache中的哈希值;
      2. ADDRESS:访问SQL语句的句柄,即内存地址;
      3. CHILD_NUMBER:使用此执行计划的子CURSOR数量;
      4. POSITION:具有相同PARENT_ID的操作的执行顺序;
      5. PARENT_ID:跳出过程的下一个执行的过程ID,这个很抽象,看到执行计划,很容易理解这一点;
      6. ID:每一个步骤的编号;
      7. PLAN_HASH_VALUE:执行计划的哈希值;
    4. 查看实际的执行计划:SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(‘sql_id’));
    5. v$sql_plan_statistics:提供实际执行时的统计信息
      1. 当STATISTICS_LEVEL设置为ALL时才会收集;
      2. 或者语句中指定了GATHER_PLAN_STATISTICS的hint;
      3. v$sql_plan_statistics_all:获得所有的实际执行的统计信息;
    6. v$sql_workarea:提供了SQL CURSOR使用的工作区的信息;                                            
  8. AWR:
    1. AWR是为了检测和自调整为目的的收集,处理,维护性能统计信息;
    2. 统计信息包括:
      1. 对象统计信息;
      2. 时间模型统计信息;
      3. 一些系统和session的统计信息;
      4. ASH(Active Session History)统计信息;
    3. 自动生成性能数据的快照;
    4. 重要的AWR视图:
      1. V$ACTIVE_SESSION_HISTORY;
      2. V$metric views;
      3. DBA_HIST views:
        1. DBA_HIST_ACTIVE_SESS_HISTORY;
        2. DBA_HIST_BASELINE;
        3. DBA_HIST_DATABASE_INSTANCE;
        4. DBA_HIST_SNAPSHOT;
        5. DBA_HIST_SQL_PLAN;
        6. DBA_HIST_WR_CONTROL;
    5. 指定sql_id查看AWR中的sql的执行计划: SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY_AWR(‘g22czkqq3pxmb’));
    6. 从AWR数据生成一个SQL报告:@$ORACLE_HOME/rdbms/admin/awrsqrpt;
  9. SQL Monitoring:11g;
  10. 阅读执行计划:
    1. 读执行计划的顺序:
      1. 从上往下看,第一个没有儿子节点的节点最先执行;
      2. 执行执行其兄弟节点;
      3. 最后执行父节点;
    2. 就是二叉树中的后序遍历的方式:
      1. 前序遍历:对任一子树,先访问根,然后遍历其左子树,最后遍历其右子树;
      2. 中序遍历:对任一子树,先遍历其左子树,然后访问根,最后遍历其右子树;
      3. 后序遍历:对任一子树,先遍历其左子树,然后遍历其右子树,最后访问根;
    3. 例子:
      1. 执行的顺序为:356421;                                                                                                          
      2. 执行的顺序为:43652871;                                                                                                        
      3. 执行顺序为:325410;                                          
    4. 查看执行计划的建议:
      1. 要使驱动表保持最好的过滤条件,即驱动表有最小的记录;
      2. 每一步返回的数据尽量最小;
      3. 正确使用视图,只是用一层,尽量不要嵌套;
      4. 避免使用笛卡尔积;
  11. 仅仅靠一个执行计划不能说明它是否是最好的,可以借助SQL Tuning Advisor工具;
标签:
目前还没有任何评论.