oracle sqlplus生成trace,oracle SQL*Plus自动报告收集工具-AUTOTRACE

前言：

AUTOTRACE是SQL*Plus的一个工具，可以显示执行的查询的解释计划(explain plan)以及所用的资源。

要用AUTOTRACE首先得需要配置。

配置AUTOTRACE(方法不止一种)方法:

1.进入到[$ORACLE_HOME]/rdbms/admin

cd $ORACLE_HOME/rdbms/admin

2.以system或者sys用户登录

sqlplus / as sysdba

3.执行@utlxplan

SQL>@utlxplan

下面是utlxplan.sql文件的内容，创建一个计划表rem

rem $Header: utlxplan.sql 08-may-2004.12:53:19 bdagevil Exp $ xplainpl.sql

rem

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

Rem NAME

REM UTLXPLAN.SQL

Rem FUNCTION

Rem NOTES

Rem MODIFIED

Rem bdagevil 05/08/04 - add other_xml column

Rem bdagevil 06/18/03 - rename hint alias to object_alias

Rem ddas 06/03/03 - increase size of hint alias column

Rem bdagevil 02/13/03 - add plan_id and depth column

Rem ddas 01/17/03 - add query_block and hint_alias columns

Rem ddas 11/04/02 - revert timestamp column to DATE (PL/SQL problem)

Rem ddas 10/28/02 - change type of timestamp column to TIMESTAMP

Rem ddas 10/03/02 - add estimated_time column

Rem mzait 04/16/02 - add row vector to the plan table

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),

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

);

4.执行

create public synonyms plan_table_syn for plan_table;

5.

grant all on plan_table_syn to public;

也可以赋予某个用户

-------------------------------------------------------------

创建并授予PLUSTRACE角色

1.以system或者sys用户登录

2.运行@plustrace

文件内容：--

-- 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 "sys/knl_test7 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

3.赋权：

grant plustrace to public;

可以赋予某个用户

-----------------------------------------------------

报告设置：可以通过系统变量设置报告内容

set autotrace off:  不生成autotrace报告，默认是不生成

set autotrace on explain: autotrace报告只显示优化器执行路径

set autotrace on statistics: autotrace报告只显示SQL语句的执行统计信息

set autotrace on:autotrace报告既包括执行路径有包括SQL语句的执行统计信息

set autotrace traceonly:与on 类似，但是只显示用户的查询输出