ITPub博客

首页 > 数据库 > Oracle > Explain Plan中AUTOTRACE引起的SP2-0618和SP2-0611错误

Explain Plan中AUTOTRACE引起的SP2-0618和SP2-0611错误

原创 Oracle 作者:YoungEric 时间:2007-10-11 16:15:03 0 删除 编辑

P.S:

[@more@]

今天测试Explain Plan功能碰到了这样的错误:

SQL> set autotrace on
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

查阅了一下相关资料,set autotrace可能导致的错误有:

SP2-0613: Unable to verify PLAN_TABLE format or existence

无法验证 PLAN_TABLE 格式或实体

SP2-0611: Error enabling EXPLAIN report

启用EXPLAIN报告时出现错误

SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled

无法找到会话标识符。启用检查 PLUSTRACE 角色

SP2-0611: Error enabling STATISTICS report

启用STATISTICS报告时出现错误

分析下错误的原因可能就在于PLAN_TABLEPLUSTRACE role以及为什么不能enabling EXPLAIN reportSTATISTICS report

解决方法:

1. SYS用户登录

CONNECT SYS/SYS@DEVP as SYSDBA ;

2. 创建PLAN_TABLE(如果未创建)

运行utlxplan.sql(ORACLE_HOME/rdbms/admin) 脚本;

rem

rem $Header: utlxplan.sql 29-oct-2001.20:28:58 mzait Exp $ xplainpl.sql

rem

Rem Copyright (c) 1988, 2001, Oracle Corporation. All rights reserved.

Rem NAME

REM UTLXPLAN.SQL

Rem FUNCTION

Rem NOTES

Rem MODIFIED

Rem mzait 10/26/01 - add keys and filter predicates to the plan table

Rem ddas 05/05/00 - increase length of options column

Rem ddas 04/17/00 - add CPU, I/O cost, temp_space columns

Rem mzait 02/19/98 - add distribution method column

Rem ddas 05/17/96 - change search_columns to number

Rem achaudhr 07/23/95 - PTI: Add columns partition_{start, stop, id}

Rem glumpkin 08/25/94 - new optimizer fields

Rem jcohen 11/05/93 - merge changes from branch 1.1.710.1 - 9/24

Rem jcohen 09/24/93 - #163783 add optimizer column

Rem glumpkin 10/25/92 - Renamed from XPLAINPL.SQL

Rem jcohen 05/22/92 - #79645 - set node width to 128 (M_XDBI in gendef)

Rem rlim 04/29/91 - change char to varchar2

Rem Peeler 10/19/88 - Creation

Rem

Rem This is the format for the table that is used by the EXPLAIN PLAN

Rem statement. The explain statement requires the presence of this

Rem table in order to store the descriptions of the row sources.

create table PLAN_TABLE (

statement_id varchar2(30),

timestamp date,

remarks varchar2(80),

operation varchar2(30),

options varchar2(255),

object_node varchar2(128),

object_owner varchar2(30),

object_name varchar2(30),

object_instance numeric,

object_type varchar2(30),

optimizer varchar2(255),

search_columns number,

id numeric,

parent_id 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));

3. 创建 plustrace 角色(如果未创建)

执行 plustrce.sql(ORACLE_HOME/sqlplus/admin/plustrce.sql)脚本;

--

-- Copyright (c) Oracle Corporation 1995, 2002. All Rights Reserved.

--

-- NAME

-- plustrce.sql

--

-- DESCRIPTION

-- Creates a role with access to Dynamic Performance Tables

-- for the SQL*Plus SET AUTOTRACE ... STATISTICS command.

-- After this script. has been run, each user requiring access to

-- the AUTOTRACE feature should be granted the PLUSTRACE role by

-- the DBA.

--

-- USAGE

-- sqlplus "/ as sysdba" @plustrce

--

-- Catalog.sql must have been run before this file is run.

-- This file must be run while connected to a DBA schema.

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

4. plustrace role赋给当前用户

grant plustrace to scott; grant plustrace to public;

注意的地方:对比网上一些其他的解决方法,

grant select on v_$sesstat to plustrace;
grant select on v_$statname to plustrace;
grant select on v_$session to plustrace;

有一个授权对象不一样,有解释说Oracle Sqlplus 9.2.0.3以上版本不需要再将v_$session授权,而我使用的是9.2.0.6版本,别忘了v_$mystat需要授权。

补充的知识:

AUTOTRACE是一项 SQL*Plus 功能,自动跟踪为 SQL 语句生成一个执行计划并且提供与该语句的处理有关的统计。

SQL*Plus AUTOTRACE 可以用来替代 SQL Trace 使用,AUTOTRACE 的好处是您不必设置跟踪文件的格式,并且它将自动为 SQL 语句显示执行计划。然而,AUTOTRACE 分析和执行语句;而EXPLAIN PLAN仅分析语句。

使用AUTOTRACE不会产生跟踪文件。

设置Autotrace的命令

序号 列名 解释

1 SET AUTOTRACE OFF 此为默认值,即关闭Autotrace

2 SET AUTOTRACE ON 产生结果集和解释计划并列出统计

3 SET AUTOTRACE ON EXPLAIN 显示结果集和解释计划不显示统计

4 SETAUTOTRACE TRACEONLY 显示解释计划和统计,尽管执行该语句,但您将看不到结果集

5 SET AUTOTRACE TRACEONLY STATISTICS 只显示统计

Autotrace执行计划的各列的涵义

序号 列名 解释

1 ID_PLUS_EXP 每一步骤的行号

2 PARENT_ID_PLUS_EXP 每一步的Parent的级别号

3 PLAN_PLUS_EXP 实际的每步

4 OBJECT_NODE_PLUS_EXP Dblink或并行查询时才会用到

AUTOTRACE Statistics常用列解释

序号 列名 解释

1 db block gets buffer cache中读取的block的数量

2 consistent gets buffer cache中读取的undo数据的block的数量

3 physical reads 从磁盘读取的block的数量

4 redo size DML生成的redo的大小

5 sorts (memory) 在内存执行的排序量

6 sorts (disk) 在磁盘上执行的排序量

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10484922/viewspace-975977/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-18

  • 博文量
    79
  • 访问量
    214879