ORACLE学习笔记系列（1）如何使用SQLPLUS分析SQL语句

 

 如何使用SQLPLUS分析SQL语句

        利用set autotrace来查看执行计划

        在sqlplus下执行：EXPLAIN PLAN FOR sql语句

 

方法一：autotrace

 

1、  connect  sys/密码 as  sysdba，在sys用户下运行

SQL>@E:\app\product\11.2.0\dbhome_1\sqlplus\admin

 

这段sql的实际内容如下：

set echo on
drop role plustrace;
create role plustrace;
grant select on v_$sesstat to plustrace;
grant select on v_$statname to plustrace;
grant select on v_$mystat to plustrace;
grant plustrace to dba with admin option;
set echo off

 

 

以上产生plustrace角色,然后在sys用户下把此角色赋予一般用户

 

SQL>grant PLUSTRACE to用户名;

 

2、然后在当前用户运行

SQL>@E:\app\product\11.2.0\dbhome_1\RDBMS\ADMIN\utlxplan.sql

它会创建一个plan_table，用来存储分析SQL语句的结果。

 

实际执行的是如下语句：

create table PLAN_TABLE (
        statement_id       varchar2(30),
        plan_id            number,
        timestamp          date,
        remarks            varchar2(4000),
        operation          varchar2(30),
        options            varchar2(255),
        object_node        varchar2(128),
        object_owner       varchar2(30),
        object_name        varchar2(30),
        object_alias       varchar2(65),
        object_instance    numeric,
        object_type        varchar2(30),
        optimizer          varchar2(255),
        search_columns     number,
        id                 numeric,
        parent_id          numeric,
        depth              numeric,
        position           numeric,
        cost               numeric,
        cardinality        numeric,
        bytes              numeric,
        other_tag          varchar2(255),
        partition_start    varchar2(255),
        partition_stop     varchar2(255),
        partition_id       numeric,
        other              long,
        distribution       varchar2(30),
        cpu_cost           numeric,
        io_cost            numeric,
        temp_space         numeric,
        access_predicates  varchar2(4000),
        filter_predicates  varchar2(4000),
        projection         varchar2(4000),
        time               numeric,
        qblock_name        varchar2(30),
        other_xml          clob
);

 

 

3、SQL/PLUS的窗口运行以下命令

 

settimeon;               (说明：打开时间显示）（可选）

set autotrace on;           (说明：打开自动分析统计，并显示SQL语句的运行结果）

set autotrace traceonly;    (说明：打开自动分析统计，不显示SQL语句的运行结果）

 

4、接下来就运行需要查看执行计划的SQL语句，看到其分析统计结果了。一般来讲，SQL语句应该避免对大表的全表扫描。

 

5、关闭以上功能，在SQL/PLUS的窗口运行以下命令

 

settimeoff;                      (说明：关闭时间显示）

set autotrace off;      

 

附：相关的set autotrace命令：

SET AUTOTRACE OFF

No AUTOTRACE reportis generated.

SET AUTOTRACE ONEXPLAIN

shows onlythe optimizer execution path.

SET AUTOTRACE ON

STATISTICS shows onlytheSQLstatement execution statistics.

SET AUTOTRACE ON

includes boththe optimizer execution pathandtheSQLstatement execution statistics.

SET AUTOTRACE TRACEONLY

LikeSET AUTOTRACE ON, but suppresses the printing oftheuser’s query output,if any.

 

说明：如果执行了set autotrace on语句，接下来的查询、插入、更新、删除语句就会显示执行计划和有用的统计信息，直到执行set autotrace off语句。

 

利用set autotrace来查看执行计划

 

设置Autotrace的命令。

分别在执行sql前设置set autotrace 的不同参数，得到不同的想观察的效果

用法:SET AUTOT[RACE] {OFF|ON|TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

--关闭跟踪执行计划和统计信息功能（默认关闭）。
SQL> set autotrace off;

--执行计划和统计信息都显示
SQL> set autotrace on ;

--只显示执行计划和统计信息，不显示sql执行结果。
SQL> set autotrace  traceonly;

--只显示执行计划
SQL> set autotrace on explain;

--只显示统计信息
SQL> set autotrace on  statistics;

 

 

 

使用autotrace功能时，oracle启用了两个session。

一个用来执行SQL。另一个用来记录执行计划和输出结果。

 

示例:

SQL>set autotrace traceexp;

SQL>set linesize 150;

SQL>select*from echo where object_id=1000;

 

 

方法二： EXPLAIN PLAN FOR sql

 

1、在sqlplus下执行：EXPLAINPLANFOR sql语句，执行后会提示已经解释。

2、然后执行如下查询，查询出执行计划

 

例子：

SQL> clear screen

SQL> SET LINESIZE 10000

SQL> explain plan FOR SELECT * FROM USER_TABLES;

已解释。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------
Plan hash value: 4102440123

----------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |               |  2443 |  5604K|   730   (7)| 00:00:09 |
|*  1 |  HASH JOIN                         |               |  2443 |  5604K|   730   (7)| 00:00:09 |
|   2 |   FIXED TABLE FULL                 | X$KSPPCV      |   100 |   196K|     0   (0)| 00:00:01 |
|   3 |   MERGE JOIN CARTESIAN             |               |  2443 |   796K|   729   (7)| 00:00:09 |
|*  4 |    HASH JOIN RIGHT OUTER           |               |  2443 |   665K|   686   (2)| 00:00:09 |
|   5 |     TABLE ACCESS FULL              | SEG$          |  6400 |   281K|    52   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------
|*  6 |     HASH JOIN RIGHT OUTER          |               |  2392 |   546K|   633   (1)| 00:00:08 |
|   7 |      TABLE ACCESS FULL             | USER$         |    95 |  1615 |     3   (0)| 00:00:01 |
|*  8 |      HASH JOIN RIGHT OUTER         |               |  2392 |   506K|   630   (1)| 00:00:08 |
|   9 |       TABLE ACCESS FULL            | DEFERRED_STG$ |  2781 | 69525 |     7   (0)| 00:00:01 |
|* 10 |       HASH JOIN OUTER              |               |  2392 |   448K|   622   (1)| 00:00:08 |
|  11 |        NESTED LOOPS OUTER          |               |  2392 |   429K|   563   (1)| 00:00:07 |
|* 12 |         HASH JOIN                  |               |  2392 |   359K|   361   (2)| 00:00:05 |
|  13 |          TABLE ACCESS FULL         | TS$           |     8 |   160 |     5   (0)| 00:00:01 |
|  14 |          NESTED LOOPS              |               |  2392 |   313K|   356   (2)| 00:00:05 |
|* 15 |           TABLE ACCESS FULL        | OBJ$          |  2392 | 86112 |   253   (2)| 00:00:04 |
|* 16 |           TABLE ACCESS CLUSTER     | TAB$          |     1 |    98 |     1   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------
|* 17 |            INDEX UNIQUE SCAN       | I_OBJ#        |     1 |       |     0   (0)| 00:00:01 |
|  18 |         TABLE ACCESS BY INDEX ROWID| OBJ$          |     1 |    30 |     2   (0)| 00:00:01 |
|* 19 |          INDEX RANGE SCAN          | I_OBJ1        |     1 |       |     1   (0)| 00:00:01 |
|  20 |        INDEX FAST FULL SCAN        | I_OBJ1        | 74188 |   579K|    58   (0)| 00:00:01 |
|  21 |    BUFFER SORT                     |               |     1 |    55 |   677   (8)| 00:00:09 |
|* 22 |     FIXED TABLE FULL               | X$KSPPI       |     1 |    55 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------
   1 - access("KSPPI"."INDX"="KSPPCV"."INDX")
   4 - access("T"."FILE#"="S"."FILE#"(+) AND "T"."BLOCK#"="S"."BLOCK#"(+) AND
              "T"."TS#"="S"."TS#"(+))
   6 - access("CX"."OWNER#"="CU"."USER#"(+))
   8 - access("T"."OBJ#"="DS"."OBJ#"(+))
  10 - access("T"."DATAOBJ#"="CX"."OBJ#"(+))
  12 - access("T"."TS#"="TS"."TS#")
  15 - filter("O"."OWNER#"=USERENV('SCHEMAID') AND BITAND("O"."FLAGS",128)=0)
  16 - filter(BITAND("T"."PROPERTY",1)=0)
  17 - access("O"."OBJ#"="T"."OBJ#")
  19 - access("T"."BOBJ#"="CO"."OBJ#"(+))

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------
  22 - filter("KSPPI"."KSPPINM"='_dml_monitoring_enabled')

已选择45行。

SQL>

 
注：除了上面的，还有一个很有用的函数dbms_xplan.display_awr(sql_id ,null,null,'ALL');这个可以结合AWR报告中的SQL语句的SQL_ID来获得历史语句的执行计划，然后通过视图 dba_hist_sqlbind 找到语句对应的绑定变量值。