ORACLE学习笔记系列(1)怎么运用SQLPLUS剖析SQL句子ITeye - 众发娱乐

ORACLE学习笔记系列(1)怎么运用SQLPLUS剖析SQL句子ITeye

2019-01-11 15:33:52 | 作者: 怀曼 | 标签: 句子,履行,剖析 | 浏览: 1749

        运用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):
---------------------------------------------------                          
			
版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表众发娱乐立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章

阅读排行

  • 1

    PGA内存办理战略ITeye

    内存,办理,时刻
  • 2

    Memcached的定位ITeye

    定位,数据,一个
  • 3

    MySQL命令行ITeye

    输入,数据库,指令
  • 4

    Oracle 表空间与数据文件ITeye

    空间,数据文件,文件
  • 5

    【转】ORACLE近义词总结ITeye

    近义词,方针,数据库
  • 6
  • 7

    Mysql表结构、数据仿制ITeye

    仿制,结构,数据
  • 8
  • 9

    Hadoop 第一个实例ITeye

    一个,文件,输出
  • 10