Oracle执行计划

进入执行计划

• 命令方式

  • 普通

  explain plan for sql;
  select * from table(dbms_xplan.display);
  

  • 高级

  select * from table(dbms_xplan.display(null,null,'advanced -projection'));
  

  半连接、反链接时可能需要用到

• plsql/developer中，sql窗口按F5

• sqlplus

  • set autot on：执行sql，显示结果、执行计划、统计信息
  • set autot trace：执行sql，显示执行计划、统计信息，一般用这个
  • set autot trace exp：DQL不执行，DML执行，显示执行计划
  • set autot trace stat：执行sql，显示统计信息
  • set autot off：关闭
  • 字段说明
    • recursive calls：递归调用次数
    • db block gets：发生变化的数据数量，一般因为DML产生
    • consistent gets：逻辑读块数
    • physical reads：物理读块数
    • redo size：重做日志字节数，一般因为DML产生
    • xxx to client：服务器传到客户端的字节数
    • xxx from client：客户端传到服务器的字节数
    • roundtrips：客户端与服务器交互次数，arraysize越大，交互次数越少
    • sorts（memory）：内存排序次数
    • sorts（disk）：磁盘排序次数
    • rows processed：sql返回数据行数

• A-Time

  • 在SqlPlus中查看
  • 方式一：
    • 1.alter session set statistics_level=all;
    • 2.执行sql
    • 3.select * from table(dbms_xplan.display_cursor(null,null,‘allstats last’));
  • 方式二：
    • 1.带HINT:/*+ gather_plan_statistics */ 执行sql
    • 2.select * from table(dbms_xplan.display_cursor(null,null,‘allstats last’));
  • 字段说明
    • starts：操作执行次数
    • e-rows：优化器估算的行数，就是普通执行计划中的rows
    • a-rows：真实的行数
    • a-time：累加总时间，是真实的
    • buffers：累加的逻辑读
    • reads：累加的物理读

• 查看正在执行sql的执行计划

  1.
  SELECT A.SID, A.EVENT, A.SQL_ID, A.SQL_CHILD_NUMBER, B.SQL_TEXT
    FROM V$SESSION A, V$SQL B
  WHERE A.SQL_ADDRESS = B.ADDRESS
    AND A.SQL_HASH_VALUE = B.HASH_VALUE
    AND A.SQL_CHILD_NUMBER = B.CHILD_NUMBER
  ORDER BY 1 DESC;
  2.
  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('sql_id', child_number));
  

• 结论：

  • sql的逻辑读远远大于所有表(全表扫描、hash join)的段大小之和，说明sql还有优化空间
  • sql关注statistics中rows processed部分
  • autotrace、explain plan for获取执行计划来自PLAN_TABLE（会话级临时表），是估算的；A-Time执行计划来自V$SQL_PLAN，来自共享池

定制执行计划

• 执行计划是树形结构，所以可以通过树查询定制
• 表的段大小
• 表的字段数
• 表的被访问字段数

执行计划内容

• 访问路径
• 连接方式
• 谓词信息
  • 谓词过滤
  • hash连接
  • ID列，前面有*号
• table access full 前面没有*
• table access full 前面有*
• table access by index rowid前面有*
  • 回表再过滤，索引不能完成全部过滤功能
• sort unique：排序、去重

阅读执行计划

• 关注哪些列
  • ID：是否带*
  • Operation：访问路径、连接方式
  • Name：数据库对象、CBO生成对象
  • Rows：CBO估算的的rows，不是真实的
• 入口
  • 光标移动：父子子先、兄弟兄先、不能穿墙
• 哪个表与哪个表是关联的
  • 光标对齐，谓词信息
• 可能出问题的地方
  • 从入口开始，逐级检查E-Rows与A-Rows是否差异过大
  • 被驱动表是大表，并且是全表扫描 考虑在连接上建索引

章节总结

• sql执行慢的原因：数据库本身(latch争用)、参数设置不合理、sql写法、索引、统计信息过期(无直方图)、优化器本身(bug)、业务本身(查询数据量大)
• sql调优就是通过各种手段和方法使优化器选择最佳执行计划，以最小资源消耗获取到想要的数据