设置sql*plus的autotrace(摘自Oracle编程艺术)
来源:互联网 发布:mysql不允许远程连接 编辑:程序博客网 时间:2024/04/29 11:27
AUTOTRACE是Sql*Plus中的一个工具,可以显示所执行查询的解释计划(explain plan)以及所用的资源。
配置autotrace的方法不止一种,以下是《Oracle编程艺术》一书中介绍的方法:
1)作为system登录SQL*Plus,运行[ORACLE_HOME]/rdbms/admin/utlxplan.sql
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));
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));
2)创建一个表plan_table的同义词:
CREATE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE;
3)授权,使任何人都可以使用SQL*Plus进行跟踪,这样就不需要每个用户都安装自己的计划表:
GRANT all on plan_table to public;
4)创建并授予plustrace角色:
作为sys或sysdba登录SQL*Plus,运行[ORACLE_HOME]/sqlplus/admin/plustrce.sql
--
-- Copyright (c) Oracle Corporation 1995, 2000. 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_$session to plustrace;
grant plustrace to dba with admin option;
set echo off
-- Copyright (c) Oracle Corporation 1995, 2000. 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_$session to plustrace;
grant plustrace to dba with admin option;
set echo off
同时,将该角色授予public, 即:
grant plustrace to public;
关于autotrace:
AUTOTRACE会自动生成一个报告,其中可能列出SQL优化器所用的执行路径,以及语句的执行统计信息。成功执行SQL DML(即select,delete,update,merge和insert)语句后就会生成这个报告。它对于监视并调优这些语句的性能很有帮助。
- 设置sql*plus的autotrace(摘自Oracle编程艺术)
- 在login.sql文件中设置sql*plus的环境(摘自《Oracle编程艺术》)
- Oracle+9i&10g编程艺术:深入数据库体系结构读书笔记--------三、设置SQL*Plus的AUTOTRACE
- 在sql*plus下设置autotrace
- 在SQL*Plus中 设置autotrace
- ORACLE set autotrace in SQL*Plus
- 启用SQL*Plus的AutoTrace功能
- 在Oracle的SQL*PLUS中启用AutoTrace查看SQL执行计划和统计信息
- 3、oracle编程专家—设置AUTOTRACE
- set autotrace in SQL*Plus
- 【Oracle】SQL优化的重要工具--AUTOTRACE
- oracle—设置AUTOTRACE
- sql 设置 autotrace
- 在SQL*PLUS中应用AUTOTRACE REPORT
- 如何在SQL*PLUS中使用Autotrace?
- 在SQL*PLUS中应用AUTOTRACE REPORT
- 在SQL*PLUS中应用AUTOTRACE REPORT
- Setting Up AUTOTRACE in SQL*Plus
- HashTab v2.0.8 Shell Extension
- HashMyFiles v 1.15
- .NET编程中遇到的问题
- Md5Checker v3.2.4
- 2007新发经典语录
- 设置sql*plus的autotrace(摘自Oracle编程艺术)
- 关于DSN
- db4o Storing/Retrieving/Updating/Deleting structured objects
- ResEdit 1.3.6
- 银行自助设备详细介绍(四)——存款机
- 公司是技术人员还是市场人员更重要?
- 在login.sql文件中设置sql*plus的环境(摘自《Oracle编程艺术》)
- 智能设备 C#调用MFC DLL 传参值为汉字的解决方案 CString转存 unsigned char数组
- 北京信息工程学院、北京信息科技大学 软件工程课程设计全部代码,希望对北信的下一代有用!