Chinaunix首页 | 论坛 | 博客
  • 博客访问: 236107
  • 博文数量: 39
  • 博客积分: 2108
  • 博客等级: 大尉
  • 技术积分: 432
  • 用 户 组: 普通用户
  • 注册时间: 2009-12-03 16:02
文章存档

2010年(38)

2009年(1)

我的朋友

分类: Oracle

2010-03-09 09:07:28

  Oracle10g获取sql语句的执行计划详解
  ---
  Oracle诊断或调优经常需要做的就是查看SQL语句的执行计划,很多时候我们需要得到sql语句在不同场景、不同时间段的执行计划。  
 
  一,通过explain plan命令获得sql语句的执行计划。
  explain plan的命令格式如下:
  sql>Explain plan for sql statement;
  蓝色部分(<>中)可以省略;红色部分为具体sql语句
  "set statement_id = ‘text’” ,其中statement_id是plan_table.statement_id,标记该条sql的id信息;
  "into your plan table”,默认的plan table是plan_table
  例如:
  SQL> Explain plan set statement_id = 'dd' into plan_table for select * from cmdba.cmcdms t where t.code_item_no='031' and t.code='1B';
  已解释。
  通过explain plan command获得sql语句的执行计划,最大的优点是不用直接运行sql语句,避免了由于返回结果时间过长过多带来的等待。
 
  二,Oracle10g平台以上获取sql语句的执行计划的方法有很多种,大致列举如下:

  0,通过PL/SQL查看
  打开SQLWINDOW,输入sql语句,按F5可以看到它的执行计划,但是这里没有物理读等统计信息。
 
  1,通过plan_table查看
  1) 需要先运行@$ORACLE_HOME/rdbms/admin/utlxpls.sql语句,生成plan_table.
  2) 执行explain plan命令获得sql语句的执行计划。
  SQL> Explain plan set statement_id = 'dd' into plan_table for select * from cmdba.cmcdms t where t.code_item_no='031' and t.code='1B';
  注意,执行完成后要提交,才能在plan_table中查看到。
  提交后,在PLAN_Table中可以查看到相关的记录。
  3)查看sql执行计划:select * from plan_table where statement_id='dd'
 
  2,使用dbms_xplan.display_cursor包从v$sql_plan里查看sql语句的执行计划。
  sql命令如下:
  sql>select plan_table_output from table(dbms_xplan.display_cursor(‘sql_id’));
  注意:sql_id可以通过v$sql 视图获得。另外,v$sql_plan_statistics_all记录着sql语句的统计信息,也可以结合该视图查看到历史sql的执行计划。
  如查询上述sql语句的sql_ID:select sql_id from v$Sql where sql_text like 'select * from cmdba.cmcdms t where t.code_item_no=%'
  得到sql_id=4skqqxzg5qkz9,查询执行计划:
  SQL> select plan_table_output from table(dbms_xplan.display_cursor('4skqqxzg5qkz9'));
  PLAN_TABLE_OUTPUT
  --------------------------------------------------------------------------------
  SQL_ID  4skqqxzg5qkz9, child number 0
  -------------------------------------
  select * from cmdba.cmcdms t where t.code_item_no='031' and t.code='1B'
  Plan hash value: 3600018637
  --------------------------------------------------------------------------------
  | Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| T
  --------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT            |           |       |       |     2 (100)|
  |   1 |  TABLE ACCESS BY INDEX ROWID| CMCDMS    |     1 |    71 |     2   (0)| 0
  |*  2 |   INDEX UNIQUE SCAN         | PK_CMCDMS |     1 |       |     1   (0)| 0
  --------------------------------------------------------------------------------
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  2 - access("T"."CODE_ITEM_NO"='031' AND "T"."CODE"='1B')
  19 rows selected
 
  3,直接在V$Sql_plan中查看执行计划:
  同样也需要在v$sql 视图中查询出sql_id。
  然后通过sql_id查询执行计划:
  select * from V$SQL_PLAN where sql_id='4skqqxzg5qkz9'
 
  4,使用dbms_xplan包,这里跟F5差不多,也是没有一些统计信息
  Oracle9i新引入的包dbms_xplan
  Oracle9i新引入的包dbms_xplan。函数display有三个参数:Table_name——执行计划所存放的表,默认为PLAN_TABLE;STATEMENT_ID——PLAN_TABLE表中的字段;FORMAT——显示格式。
 
  SQL> explain plan for select * from cmdba.cmcdms t where t.code_item_no='031' and t.code='1B';
 
  Explained
 
  SQL> select * from table(dbms_xplan.display);
 
  PLAN_TABLE_OUTPUT
  --------------------------------------------------------------------------------
  Plan hash value: 3600018637
  --------------------------------------------------------------------------------
  | Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| T
  --------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT            |           |     1 |    71 |     2   (0)| 0
  |   1 |  TABLE ACCESS BY INDEX ROWID| CMCDMS    |     1 |    71 |     2   (0)| 0
  |*  2 |   INDEX UNIQUE SCAN         | PK_CMCDMS |     1 |       |     1   (0)| 0
  --------------------------------------------------------------------------------
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  2 - access("T"."CODE_ITEM_NO"='031' AND "T"."CODE"='1B')
 
  14 rows selected
 
  优点:适合于bind var的SQL。可以直接执行,能够根据相关信息排序,显示界面友好。
 
  5.使用autotrace查看,可以查看一些统计信息
  首先执行脚本
  SQL>@D:oracleora92rdbmsadminutlxplan.sql 建立plan_table表
  SQL> set autotrace on
  SQL> set autotrace traceonly
  SQL> set timing on
  SQL> select * from cmdba.cmcdms;
 
  已选择3327行。
 
  已用时间:  00: 00: 00.13
 
  Execution Plan
  ----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=9 Card=3327 Bytes=23
  6217)
 
  1    0   TABLE ACCESS (FULL) OF 'CMCDMS' (TABLE) (Cost=9 Card=3327
  Bytes=236217)
 
  Statistics
  ----------------------------------------------------------
  0  recursive calls
  0  db block gets
  256  consistent gets
  0  physical reads
  0  redo size
  351787  bytes sent via SQL*Net to client
  24956  bytes received via SQL*Net from client
  446  SQL*Net roundtrips to/from client
  0  sorts (memory)
  0  sorts (disk)
  3327  rows processed
 
  或者:
 
  SQL> set autotrace trace explain
  SQL> select * from v$parameter;
  Execution Plan
  ----------------------------------------------------------
  Plan hash value: 1128103955
  ------------------------------------------------------------------------------
  | Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
  ------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT  |          |     1 |   926 |     1 (100)| 00:00:01 |
  |*  1 |  HASH JOIN        |          |     1 |   926 |     1 (100)| 00:00:01 |
  |*  2 |   FIXED TABLE FULL| X$KSPPI  |     1 |   249 |     0   (0)| 00:00:01 |
  |   3 |   FIXED TABLE FULL| X$KSPPCV |   100 | 67700 |     0   (0)| 00:00:01 |
  ------------------------------------------------------------------------------
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  1 - access("X"."INDX"="Y"."INDX")
      filter(TRANSLATE("KSPPINM",'_','#') NOT LIKE '#%' OR
      "KSPPSTDF"='FALSE' OR BITAND("KSPPSTVF",5)>0)
  2 - filter("X"."INST_ID"=USERENV('INSTANCE') AND
      TRANSLATE("KSPPINM",'_','#') NOT LIKE '##%')
 
  关于Autotrace几个常用选项的说明:
  SET AUTOTRACE OFF ---------------- 不生成AUTOTRACE 报告,这是缺省模式
  SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE只显示优化器执行路径报告
  SET AUTOTRACE ON STATISTICS -- 只显示执行统计信息
  SET AUTOTRACE ON ----------------- 包含执行计划和统计信息
  SET AUTOTRACE TRACEONLY ------ 同set autotrace on,但是不显示查询输出
  这种方法显示内容较多……
 
  6.使用sql_trace查看,这个跟3差不多
  在pl/sql或者sqlplus中,打开一个sql_window。
  (1)先运行:alter session set sql_trace=true;
  (2)再运行你那个返回结果不正确的SQL
  (3)再运行:alter session set sql_trace=false;
  (4)马上登陆到机器上,到$ORACLE_BASE/admin/sid/udump目录下。
  (5)找到刚生成的.trc文件(假设文件名是 xxx.trc),
  执行命令转储跟踪文件:tkprof xxx.trc aa.txt。
  查看aa.txt文件。这个文件里面有执行计划。看看执行计划每一步返回的结果集记录数是不是正确。
  (TTT:在udump目录下未找到新生成的*.trc,但在$ORACLE_BASE/admin/sid/bdump下找到相关文件,转储成功。oracle8i在$ORACLE_BASE\rdbms\trace\目录下。)
 
  7.通过10053事件来查看,这里面可以查到一些关于SQL成本的详细信息(TTT:未测试成功,第一条语句不明)
  在pl/sql或者sqlplus中,打开一个sql_window。
  (1)先运行:Alter session set events’10053 trace name context forever[,level {1/2}]’;
  (2)再运行你那个返回结果不正确的SQL
  (3)再运行:Alter session set events’10053 trace name context off’;
  (4)马上登陆到10.1.4.10机器上,到$ORACLE_BASE/admin/sid/udump目录下。找到刚生成的.trc文件)。
 
  8. 通过AWRRPT查看(TTT:未测试)
  同其他方式一样,要想获得sql语句的执行计划,必须获得该sql的sql_id。有了sql_id,并且确认该sql已经被记录在dba_hist_sqltext里,你就可以使用oracle10g提供的dbms_xplan.display_awr包显示指定sql_id的执行计划。
  比如,执行一条sql,通过awrrpt获取其执行计划的步骤如下:
  1)执行sql语句
  sql>select /*awrshow*/ id from test order by id;
  2)确认sql语句的sql_id
  sql>select sql_id,sql_text from v$sql where sql_text like '%awrshow%';
  3)确认该sql是否被记录在dba_hist_sqltext里
  sql>select sql_id,sql_text from dba_hist_sqltext where sql_id = '****';
  注意:如果没有该sql的信息,则手工设置AWR的snapshot,将sql信息记录在dba_hist_sqltext里。执行如下sql命令:
  sql>exec dbms_workload_repository.create_snapshot();
  4)使用dbms_xplan.display_awr的包显示指定sql_id的执行计划
  sql>select plan_table_output from table(dbms_xplan.display_awr('sql_id'));
  结合AWRRPT功能查看sql语句的执行计划最大的用处就是,当业务出现瓶颈或是峰值时,你可以获得异常时间段内问题sql语句的执行计划与正常表现时的进行对比。当然前提是,数据库是Oracle10g及以上版本,并使用了AWRRPT功能。

  懂得怎么获取执行计划只是掌握了工具,能够看懂执行计划和统计信息,并进行分析那才是高手!俺还不行……

    说明:本文是根据网上搜索资料总结测试而成,具体引用的文章来源未记录,实在不好意思!

转帖请注明:
本文源自TTT BLOG,原文地址:http://blog.chinaunix.net/u3/107265/showart_2192657.html
阅读(3262) | 评论(0) | 转发(1) |
给主人留下些什么吧!~~