`

explain plan,autotrace,tkprof,执行计划和静态统计信息的解读

阅读更多
explain plan,autotrace,tkprof,执行计划和静态统计信息的解读



1、	执行计划

执行计划的设定
conn sys/pwd@gx as sysdba;

CREATE USER TOOL
  IDENTIFIED BY tool
  DEFAULT TABLESPACE EXAMPLE
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;
  -- 2 Roles for TOOL 
  GRANT RESOURCE TO TOOL;
  GRANT CONNECT TO TOOL;
  ALTER USER TOOL DEFAULT ROLE NONE;
  -- 3 System Privileges for TOOL 
  GRANT CREATE SESSION TO TOOL;
  GRANT CREATE TABLE TO TOOL;
  GRANT UNLIMITED TABLESPACE TO TOOL;

CREATE GLOBAL TEMPORARY TABLE tool.PLAN_TABLE
(
  STATEMENT_ID       VARCHAR2(30 BYTE),
  PLAN_ID            NUMBER,
  TIMESTAMP          DATE,
  REMARKS            VARCHAR2(4000 BYTE),
  OPERATION          VARCHAR2(30 BYTE),
  OPTIONS            VARCHAR2(255 BYTE),
  OBJECT_NODE        VARCHAR2(128 BYTE),
  OBJECT_OWNER       VARCHAR2(30 BYTE),
  OBJECT_NAME        VARCHAR2(30 BYTE),
  OBJECT_ALIAS       VARCHAR2(65 BYTE),
  OBJECT_INSTANCE    INTEGER,
  OBJECT_TYPE        VARCHAR2(30 BYTE),
  OPTIMIZER          VARCHAR2(255 BYTE),
  SEARCH_COLUMNS     NUMBER,
  ID                 INTEGER,
  PARENT_ID          INTEGER,
  DEPTH              INTEGER,
  POSITION           INTEGER,
  COST               INTEGER,
  CARDINALITY        INTEGER,
  BYTES              INTEGER,
  OTHER_TAG          VARCHAR2(255 BYTE),
  PARTITION_START    VARCHAR2(255 BYTE),
  PARTITION_STOP     VARCHAR2(255 BYTE),
  PARTITION_ID       INTEGER,
  OTHER              LONG,
  OTHER_XML          CLOB,
  DISTRIBUTION       VARCHAR2(30 BYTE),
  CPU_COST           INTEGER,
  IO_COST            INTEGER,
  TEMP_SPACE         INTEGER,
  ACCESS_PREDICATES  VARCHAR2(4000 BYTE),
  FILTER_PREDICATES  VARCHAR2(4000 BYTE),
  PROJECTION         VARCHAR2(4000 BYTE),
  TIME               INTEGER,
  QBLOCK_NAME        VARCHAR2(30 BYTE)
)
ON COMMIT PRESERVE ROWS;

grant all on TOOL.PLAN_TABLE to public;

CREATE PUBLIC SYNONYM PLAN_TABLE FOR TOOL.PLAN_TABLE;


使用方法:
truncate table PLAN_TABL;
explain plan select * from emp;
select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));

演示:
conn scott/tiger
SQL> explain plan for select * from dept where deptno=10;
Explained
SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3383998547
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    16 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| DEPT |     1 |    16 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("DEPTNO"=10)

13 rows selected

执行计划解读:--估算表
表v$sql_plan
cost概念
cardinality



查询路径—估算树
create table e
as select * from emp

create table d
as
select * from dept


Explain plan for
select ename,dname from d,e where e.deptno=d.deptno

select * from table(dbms_xplan.display());


Plan hash value: 1127375450
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    15 |   630 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN         |      |    15 |   630 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| D    |     4 |    88 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| E    |    15 |   300 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("E"."DEPTNO"="D"."DEPTNO")
 
Note
-----
   - dynamic sampling used for this statement

Explain plan for
select ename,dname from d, (select ename,deptno from e where rownum<2) e where e.deptno=d.deptno 

select * from table(dbms_xplan.display());

Plan hash value: 1791846393
 
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |    42 |     5   (0)| 00:00:01 |
|   1 |  NESTED LOOPS        |      |     1 |    42 |     5   (0)| 00:00:01 |
|   2 |   VIEW               |      |     1 |    20 |     2   (0)| 00:00:01 |
|*  3 |    COUNT STOPKEY     |      |       |       |            |          |
|   4 |     TABLE ACCESS FULL| E    |    15 |   300 |     2   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS FULL  | D    |     1 |    22 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter(ROWNUM<2)
   5 - filter("E"."DEPTNO"="D"."DEPTNO")
 
Note
-----
   - dynamic sampling used for this statement


驱动表概念
估算树

 
从左到右 从下到上

autotrace 
oracle_home\sqlplus\admin\

conn sys/pwd@gx as sysdba;
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;
grant plustrace  to public;

grant select on v_$sesstat to public;
grant select on v_$statname to public;
grant select on v_$mystat to public;
grant plustrace to dba with admin option;
grant plustrace  to public;


grant alter session to public;

使用命令
set autotrace on
set autotrace off
set autotrace on explain
set autotrace on statistics
set autotrace traceonly


autotrace输出内容解释
recursive calls:执行语句时、调用的oracle内部语句(如分析所用的sql)和其他语句(如触发器)。
测试举例:
举例1
conn scott/tiger@gx
set autotrace on
alter system flush shared_pool
set autotrace on
select * from emp
select * from emp


举例2
create table exchage_table
(
bill_code   number(10),
exchage_rate  number(16,3)
)
BILL_CODE	EXCHAGE_RATE
100	4.678
200	5.235
300	5.430
400	2.654


create or replace function today_exchage(p_code in number) return number is 
v_exange number(16,3);
begin
select exchage_rate into v_exange from exchage_table
where bill_code=p_code;
return v_exange;
end;

create table affair
(
 trans_id  number(10),
 bill_code number(10),
 balance   number(16,2)
)

TRANS_ID	BILL_CODE	BALANCE
1000	100	1234.00
2000	200	4324.32
3000	300	65464.23

较好的写法为
select  
trans_id, 
(select exchage_rate 
from exchage_table 
where bill_code=affair.bill_code )*balance 
from affair 

举例3
drop table tppp purge   
create table tppp(p integer)

create or replace trigger t_trigger
  before insert on tppp  
  for each row
declare
  -- local variables here
begin
if :new.p>5 then
raise_application_error(-20001,'bbbbbbbb');
end if;
  
end t_trigger;

统计信息
----------------------------------------------------------
         29  recursive calls
         19  db block gets
         54  consistent gets
          0  physical reads
       1172  redo size
        676  bytes sent via SQL*Net to client
        627  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          9  rows processed

在一次运行

统计信息
---------------------------------------------------------
         29  recursive calls
          0  db block gets
        117  consistent gets
          1  physical reads
          0  redo size
        483  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          9  rows processed
drop  trigger t_trigger;

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        108  consistent gets
          0  physical reads
          0  redo size
        483  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          9  rows processed

解决方法为
1、	编写高效的trigger
2、	用过程代替trigger

举例4:
自我管理表空间与数据字典表空间
本地管理的表空间能够减少递归sql


输出内容: 逻辑I/O  (DB BLOCKS| CONSISTENT GETS)
解释

 对于一个SQL 逻辑I/O越小越好,通常通过SQL调整实现的



TKPROF

使用 TKPROF 工具简介
TKPROF 工具简介
TKPROF 工具的使用步骤
TKPROF 工具如何分析 trace 文件
启用TKPROF
如何设置自动跟踪
1、设定执行表,autotrace。方法如前所述,这里再重复一边。
用system登录
执行$ORACLE_HOME/rdbms/admin/utlxplan.sql创建计划表
执行$ORACLE_HOME/sqlplus/admin/plustrce.sql创建plustrace角色
如果想计划表让每个用户都能使用,则
SQL>create public synonym plan_table for plan_table;
SQL> grant all on plan_table to public;

2、设定tkprof
ALTER SESSION  SET SQL_TRACE = TRUE
ALTER SESSION  SET TIMED_STATISTICS = TRUE;
alter session set events ‘10046 trace name context forever,level 12’;
alter session  set max_dump_file_size=unlimited;
alter session set events  '10046 trace name context off'



获取跟踪文件名称
跟踪的信息在user_dump_dest 目录下可以找到或通过如下脚本获得文件名称(适用于Win环境,如果是unix需要做一定修改)
conn system/pwd

SELECT p1.value||'\'||p2.value||'_ora_'||p.spid||'.trc' filename
FROM
v$process p,
v$session s,
v$parameter p1,
v$parameter p2
WHERE p1.name = 'user_dump_dest'
AND p2.name = 'db_name'
AND p.addr = s.paddr
AND s.audsid = USERENV ('SESSIONID')

在unix的目录下
http://www.eygle.com/faq/script/gettrcnameunix.sql

有了正确而详细的诊断数据之后,你需要以摘要的形式对其进行查看,这有助于你以最快的速度做出响应。
Cmd tkprof path\xxx.prc xxx.txt

报告解读:
parse(分析):在共享池中找到该查询(软分析)或者创建该查询的新计划(硬分析)
execute(执行):执行查询的所有工作
fetch(提取):显示select的提取工作,对于update,则没有内容

count(计数):执行的次数
cpu:此阶段cpu的耗时,以毫秒为单位
elapsed(占用时间):挂钟时间,如果大于cpu时间,则有等待时间
disk(磁盘):执行物理I/O的次数
QUERY(查询):检索一致性执行的I/O次数
CURRENT(当前):到当前多执行的逻辑I/O次数
ROW:此阶段被处理或者受到影响的行

如果一个UPDATE语句EXECUTE的QUERY,CURRENT,ROWS分别为2000 1000 500,表示这个语句访问了2000个块找到需要UPDATE的行记录,在UPDATE的时候只访问了1000个块,一共更新了500行。如果只获取很少的数据,而要访问了大量的块,表明SQL与需要优化了。


MISSES 缓存命中率:0 表示已经通过软分析
OPTIMIZER GOAL(优化程序目标)

执行计划:与前面的执行计划相比,增加了各个阶段涉及的行数

关闭
alter system set events '10046 trace name context off';

更好的方法是使用DBMS_SUPPORT包来激活扩展SQL跟踪: 
dbms_support.start_trace(waits=>;true, binds=>;true) 
/* code to be traced goes here */ 
dbms_support.stop_trace() 

请注意DBMS_SUPPORT 没有文档说明,可能也不是数据库默认安装的一部分。要了解DBMS_SUPPORT的信息,请参考MetaLink ( metalink.oracle.com)。  

跟踪别人的代码。如果你想跟踪没有读/写权限的代码,则激活扩展SQL跟踪就有点麻烦了。但也不会难很多。你首先要获得你想跟踪的会话的V$SESSION.SID和V$SESSION.SERIAL#值。然后使用下面的过程调用,可以设置所选会话的TIMED_STATISTICS和MAX_DUMP_FILE_SIZE参数:  


dbms_system.set_bool_param_in_session( 
   sid     =>; 42, 
   serial# =>; 1215, 
   parnam  =>; 'timed_statistics', 
   bval    =>; true) 
dbms_system.set_int_param_in_session( 
   sid     =>; 42, 
   serial# =>; 1215, 
   parnam  =>; 'max_dump_file_size', 
   intval  =>; 2147483647) 


(对于Oracle8 8.1.6以前的版本,你可以用ALTER SYSTEM命令处理这些参数。)  

接下来要激活跟踪。有几种方法可以采用,包括下面两个: 

方法一是使用DBMS_SUPPORT:  


dbms_support.start_trace_in_session( 
   sid     =>; 42, 
   serial# =>; 1215, 
   waits   =>; true, 
   binds   =>; true) 
/* code to be traced executes during this time window */ 
dbms_support.stop_trace_in_session( 
  sid      =>; 42, 
  serial   =>; 1215) 


若想激活扩展SQL跟踪,请不要使用名为SET_SQL_TRACE_IN_SESSION的DBMS_SUPPORT过程。该过程不允许在跟踪文件中指定等待和绑定的数据。  

第二种方法更为精致,但在Oracle数据库10g之前的版本中并不支持这种方法。 DBMS_MONITOR包的引入解决了许多复杂诊断数据收集问题,这些问题是由连接共享和多线程操作所引起的。你可以在Oracle数据库10g中指定要跟踪的服务、模块或行动,而不指定要跟踪的Oracle数据库会话:  


dbms_monitor.serv_mod_act_trace_enable( 
  service_name  =>; 'APPS1', 
  module_name   =>; 'PAYROLL', 
  action_name   =>; 'PYUGEN', 
  waits         =>; true, 
  binds         =>; true, 
  instance_name =>; null) 
/* code to be traced executes during this time window */ 
dbms_monitor.serv_mod_act_trace_disable( 
  service_name  =>; 'APPS1', 
  module_name   =>; 'PAYROLL', 
  action_name  =>; 'PYUGEN') 


利用DBMS_MONITOR包,Oracle可为要跟踪的特定的业务操作提供完全支持激活或停止诊断数据收集的方法。 


在PL/SQL中,由于不能执行alter session,可以使用
  
  dbms_session.set_sql_trace(TRUE);
  
  必须安装DBMS_SESSION包,并"直接"赋给用户alter session的权限。
当我们使用sql
For Unix:
 $ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Fri Oct 8 12:08:09 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> set echo on
SQL> @gettrcnameunix
SELECT       d.VALUE
         || '/'
         || LOWER (RTRIM (i.INSTANCE, CHR (0)))
         || '_ora_'
         || p.spid
         || '.trc' trace_file_name
    FROM (SELECT p.spid
            FROM v$mystat m, v$session s, v$process p
           WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
         (SELECT t.INSTANCE
            FROM v$thread t, v$parameter v
           WHERE v.NAME = 'thread'
             AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
         (SELECT VALUE
            FROM v$parameter
           WHERE NAME = 'user_dump_dest') d
TRACE_FILE_NAME
--------------------------------------------------------------------------------
/opt/oracle/admin/hsbill/udump/hsbill_ora_29630.trc

For Nt: 
SELECT    d.VALUE
        || '\'
        || LOWER (RTRIM (i.INSTANCE, CHR (0)))
        || '_ora_'
        || p.spid
        || '.trc' trace_file_name
   FROM (SELECT p.spid
           FROM v$mystat m, v$session s, v$process p
          WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
        (SELECT t.INSTANCE
           FROM v$thread t, v$parameter v
          WHERE v.NAME = 'thread'
            AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
        (SELECT VALUE
           FROM v$parameter
          WHERE NAME = 'user_dump_dest') d
TRACE_FILE_NAME
--------------------------------------------------------------------------------
e:\oracle\admin\eygle\udump\eygle_ora_3084.trc

 

分享到:
评论

相关推荐

    ORACLE EXPLAIN PLAN的总结

    Oracle的Explain Plan是数据库管理员和开发人员用来分析SQL查询执行计划的重要工具。它能提供关于如何执行SQL语句的详细信息,包括哪些操作(如全表扫描、索引访问等)将被使用,以及这些操作的预计成本、时间及资源...

    oracle explain plan总结

    这将会自动显示执行计划和统计信息。还可以使用以下选项来定制输出: - **只显示执行计划**: ```sql SET AUTOTRACE ON EXPLAIN ``` - **只显示统计信息**: ```sql SET AUTOTRACE ON STATISTICS ``` #### ...

    Oracle中EXPLAIN PLAN的使用技巧

    其中,EXPLAIN PLAN是一个不可或缺的诊断工具,它用于分析和展示SQL语句的具体执行计划,从而让开发者和数据库管理员能够窥探SQL语句在执行过程中的每一步操作。通过对执行计划的深入理解,可以有效地诊断查询瓶颈,...

    Oracle中explain_plan的用法

    通过 explain_plan,可以了解连接顺序、索引使用情况、数据库资源使用情况等信息,从而判断 SQL 语句的执行效率如何。 explain_plan 是一个非常有用的工具,能够帮助用户了解 Oracle 是如何执行 SQL 语句的,从而...

    explain plan interpreting

    在Oracle数据库系统中,"explain plan" 是一个关键的工具,用于分析和理解SQL查询的执行计划,也就是数据检索的路径。它可以帮助数据库管理员和开发人员优化查询性能,确保数据库高效运行。以下是对"explain plan ...

    数据库调优:ORACLE EXPLAIN PLAN的总结

    同时,对于复杂的查询,可能还需要借助于其他的数据库诊断工具,如`DBMS_XPLAN`包,它可以提供更详细的执行计划信息,如行源格式(format)和执行计划的详细成本分析。 总结来说,掌握`EXPLAIN PLAN`的使用和解析,...

    oracle怎么查看执行计划

    同样,与EXPLAIN PLAN相比,AUTOTRACE不仅展示了执行计划,还执行了SQL语句,从而能够提供更加准确的统计信息。 #### 准备工作 为了使用AUTOTRACE,需要进行以下准备工作: 1. **创建PLAN_TABLE** - 每个需要运行...

    Oracle执行计划详细解读.pptx

    Oracle 执行计划详细解读 Oracle 执行计划是 Oracle 数据库中的一种机制,用于优化 SQL 语句的执行效率。执行计划是 Oracle 数据库内部的一种机器级代码,决定如何访问存储器,得到需要的结果集。 执行计划的主要...

    oracle 的Autotrace介绍

    Autotrace 是 Oracle 10g 中的一项有用的功能,可以帮助开发者和 DBA 更好地了解 SQL 语句的执行计划和统计,从而优化数据库性能。但是,需要注意 Autotrace 的使用方法和相关的设置,以避免报错和影响数据库性能。

    Oracle执行计划1.ppt

    其中,Explain 是通过 Execute Plan 语句生成执行计划的,Autotrace 是通过设置 autotrace 参数生成执行计划的。 在 Oracle 中,生成执行计划需要创建一个 Plan_table 表,用于存储执行计划的信息。该表包括了执行...

    MySQL explain-MySQL执行计划解读.ppt

    MySQL explain-MySQL执行计划解读.ppt

    Oracle执行计划.ppt

    * Explain 语句:使用 Explain 语句可以获取执行计划,例如:Explain Plan Set STATEMENT_ID='testplan' For Select ….. * Autotrace 工具:Autotrace 是 Oracle 提供的一种工具,可以自动跟踪执行计划。 * 其他...

    Oracle性能分析——使用set_autotrace_on和set_timing_on来分析select语句的性能.doc

    set autotrace on 命令的结果包括执行计划、统计信息和trace 信息。执行计划展示了 SQL 语句的执行步骤,例如索引的使用、表扫描的方式等;统计信息展示了 SQL 语句的执行情况,例如recursive calls、db block gets...

    oracle执行计划建立与阅读

    `AUTOTRACE`是一种自动分析和显示执行计划及统计信息的工具。首先,需要给用户授予`PLUSTRACE`权限。然后在`SQL*Plus`中设置`AUTOTRACE`开关,例如: ``` SET AUTOTRACE ON EXPLAIN ``` 或者: ``` SET ...

    oracle 执行计划

    ### Oracle执行计划详解 ...通过正确地设置Autotrace选项或使用EXPLAIN PLAN命令,我们可以方便地查看和分析执行计划。同时,通过对执行计划的深入理解和分析,可以有效地优化SQL语句,从而提升整体系统的运行效率。

    获取SQL语句的执行计划v1

    - `set autotrace on explain`:仅显示查询结果和执行计划,不包括统计数据。 ```sql select * from emp; ``` - `set autotrace traceonly`:仅显示执行计划,不包括查询结果或统计数据。 ```sql select * ...

    ORACLE数据库查看执行计划

    - `SET AUTOTRACE ON EXPLAIN` 显示执行计划,不显示执行时间和统计信息。 - `SET AUTOTRACE ON` 显示执行计划、执行时间和统计信息。 - `SET AUTOTRACE TRACEONLY` 显示执行计划和统计信息,不输出查询结果。 -...

    Oracle执行计划.pptx

    执行计划中的每一步都有其特定的成本和预期的行数(cardinality),这些值是基于历史统计信息和系统资源情况估算的。 4. **显示结果集**: 在执行计划完成后,结果集会被返回给客户端。在这个过程中,可能还会涉及...

    达梦数据库执行计划操作符介绍

    达梦数据库执行计划操作符介绍 在达梦数据库7中,执行计划操作符是数据库查询优化的关键组件。理解这些操作符,可以更好地读懂执行计划,从而优化数据库性能。 1. PRJT2关系的“投影”(project)运算 PRJT2是...

    Oracle执行计划与SQL优化实例.pptx

    2. **SQL\*Plus AutoTrace功能**:除了显示执行计划,AutoTrace还提供执行过程中的统计信息,如逻辑读次数,这对于评估查询效率很有帮助。 3. **10046 Event**:通过设置`ALTER SESSION SET EVENTS '10046 TRACE ...

Global site tag (gtag.js) - Google Analytics