SQLPLUS:AUTOTRACE是如何工作的
来源:互联网 发布:winform 界面框架源码 编辑:程序博客网 时间:2024/06/07 14:30
autotrace是sqlplus为我们提供的跟踪sql执行的优秀工具。其语法格式如下:
SQL> set autotrace用法: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
举例:
SET AUTOT[RACE] OFF 停止AutoTrace
SET AUTOT[RACE] ON 开启AutoTrace,显示AUTOTRACE信息和SQL执行结果
SET AUTOT[RACE] TRACEONLY 开启AutoTrace,仅显示AUTOTRACE信息
SET AUTOT[RACE] ON EXPLAIN 开启AutoTrace,仅显示AUTOTRACE的EXPLAIN信息
SET AUTOT[RACE] ON STATISTICS开启AutoTrace,仅显示AUTOTRACE的STATISTICS信息
从语法上可以看出,autotrace主要可以提供执行计划和统计信息的跟踪。那么这些信息是如何获取的那?
为了使用autotrace,我们首先需要执行安装脚本:$ORACLE_HOME/sqlplus/admin/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 ondrop 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
可以看出,要使用autotrace功能,首先需要创建角色PLUSTRACE,这个角色的权限主要是可以读取与统计信息相关的字典表。因此我们猜测:autotrace的统计信息是从与v_$sesstat,v_$statname,v$mystat相关的数据字典获取的。这也就导致如果我们需要查看统计信息,相应的sql语句必须先被执行,即便执行结果没有被显示。
下面我们看一下,执行计划信息是如何获取的。
SQL> alter session set events '10046 trace name context forever,level 10';会话已更改。SQL> alter session set sql_trace=true;会话已更改。SQL> set autotrace traceonly exp
查看跟踪文件:
SQL ID: 3y2ghhcs36h3y Plan Hash: 2927627013EXPLAIN PLAN SET STATEMENT_ID='PLUS374164' FOR update t1 set skew=skew-1call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.00 0 0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 0 0.00 0.00 0 0 0 0------- ------ -------- ---------- ---------- ---------- ---------- ----------total 2 0.00 0.00 0 0 0 0Misses in library cache during parse: 1Optimizer mode: ALL_ROWSParsing user id: 83Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 1 0.00 0.00 SQL*Net message from client 1 0.00 0.00
从跟踪文件可以看出,执行计划是通过EXPLAIN PLAN语句来获取的。
通过前面的解释我们可以知道:在查看统计信息时,sql语句是必须要执行的。那么在traceonly模式下只查看执行计划时,sql语句是否会执行那?
session 1:
SQL> set autotrace traceonly expSQL> select * from t1;执行计划----------------------------------------------------------Plan hash value: 3617692013--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 3240 | 1278K| 57 (0)| 00:00:01 || 1 | TABLE ACCESS FULL| T1 | 3240 | 1278K| 57 (0)| 00:00:01 |--------------------------------------------------------------------------SQL> update t1 set skew=skew-1;--已更新3240行。执行计划----------------------------------------------------------Plan hash value: 2927627013---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | UPDATE STATEMENT | | 3240 | 1278K| 57 (0)| 00:00:01 || 1 | UPDATE | T1 | | | | || 2 | TABLE ACCESS FULL| T1 | 3240 | 1278K| 57 (0)| 00:00:01 |---------------------------------------------------------------------------SQL> commit;提交完成。SQL>
session2:
SQL> l 1* select max(skew) from t1SQL> / MAX(SKEW)----------82SQL> / MAX(SKEW)----------81
跟踪文件:
********************************************************************************SQL ID: 27uhu2q2xuu7r Plan Hash: 0select *from t1call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.00 0 0 0 -- 0Execute 0 0.00 0.00 0 0 0 -- 0Fetch 0 0.00 0.00 0 0 0 -- 0------- ------ -------- ---------- ---------- ---------- ---------- ----------total 1 0.00 0.00 0 0 0 0Misses in library cache during parse: 0Optimizer mode: ALL_ROWSParsing user id: 83Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 1 0.00 0.00 SQL*Net message from client 1 0.00 0.00********************************************************************************SQL ID: 1uyzazqr6rs12 Plan Hash: 3617692013EXPLAIN PLAN SET STATEMENT_ID='PLUS374164' FOR select * from t1call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.00 0 0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 0 0.00 0.00 0 0 0 0------- ------ -------- ---------- ---------- ---------- ---------- ----------total 2 0.00 0.00 0 0 0 0Misses in library cache during parse: 1Optimizer mode: ALL_ROWSParsing user id: 83Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message from client 2 498.36 498.36 SQL*Net message to client 1 0.00 0.00********************************************************************************SQL ID: 8brhpuhjaa12w Plan Hash: 2927627013update t1 set skew=skew-1call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.00 0 0 0 0Execute 1 0.06 0.06 0 194 3672 -- 3240Fetch 0 0.00 0.00 0 0 0 0------- ------ -------- ---------- ---------- ---------- ---------- ----------total 2 0.06 0.06 0 194 3672 3240Misses in library cache during parse: 0Optimizer mode: ALL_ROWSParsing user id: 83Number of plan statistics captured: 1Rows (1st) Rows (avg) Rows (max) Row Source Operation---------- ---------- ---------- --------------------------------------------------- 0 0 0 UPDATE T1 (cr=194 pr=0 pw=0 time=63859 us) 3240 3240 3240 TABLE ACCESS FULL T1 (cr=194 pr=0 pw=0 time=6736 us cost=57 size=1308960 card=3240)Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 1 0.00 0.00 SQL*Net message from client 1 0.00 0.00********************************************************************************SQL ID: 3y2ghhcs36h3y Plan Hash: 2927627013EXPLAIN PLAN SET STATEMENT_ID='PLUS374164' FOR update t1 set skew=skew-1call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.00 0 0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 0 0.00 0.00 0 0 0 0------- ------ -------- ---------- ---------- ---------- ---------- ----------total 2 0.00 0.00 0 0 0 0Misses in library cache during parse: 1Optimizer mode: ALL_ROWSParsing user id: 83Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 1 0.00 0.00 SQL*Net message from client 1 0.00 0.00********************************************************************************SQL ID: 23wm3kz7rps5y Plan Hash: 0commitcall count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.00 0 0 0 0Execute 1 0.00 0.00 0 0 1 0Fetch 0 0.00 0.00 0 0 0 0------- ------ -------- ---------- ---------- ---------- ---------- ----------total 2 0.00 0.00 0 0 1 0Misses in library cache during parse: 0Parsing user id: 83Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ log file sync 1 0.00 0.00 SQL*Net message to client 1 0.00 0.00********************************************************************************
从上面可以看出,在traceonly exp模式下,select语句并没有真正执行,而update语句确执行了,相信insert和delete也会执行(未测试)
0 0
- SQLPLUS:AUTOTRACE是如何工作的
- 如何启用sqlplus的AutoTrace功能
- 如何启用sqlplus的AutoTrace功能
- 如何启用sqlplus的AutoTrace功能
- 如何启用sqlplus的AutoTrace功能
- 如何使用sqlplus的 autotrace 功能
- 如何启用sqlplus的AutoTrace功能
- 如何启用sqlplus的AutoTrace功能
- 如何启用sqlplus的AutoTrace功能
- sqlplus的autotrace的设置
- SQLPLUS 中Autotrace的使用
- Autotrace in SQLPLUS
- sqlplus AUTOTRACE功能
- o3-关于sqlplus autotrace
- oracle sqlplus autotrace功能
- sqlplus中的autotrace使用简介
- oracle是如何工作的
- Oracle是如何工作的
- Oracle拆分字符串函数
- Maven学习 搭建多模块企业级项目 maven工程下包含多个maven工程(模块)--good
- 圣诞web设计素材
- 在cocos2dx 中添加自定义 shaders --- 改变 HSL 色调,饱和度, 亮度
- switch入参:java中不可为long,C++中则可以
- SQLPLUS:AUTOTRACE是如何工作的
- 信息完整性
- C++ 隐式类型转换与关键字explicit
- 用artifactory搭建maven2内部服务器
- bash中一条管道命令的解释
- SharePoint 2010/2013 使用Javascript来判断权限的三种方法
- Maven学习 搭建多模块企业级项目 maven工程下包含多个maven工程(模块)--good
- FTP安装和虚拟用户的建立
- SQL纵向变成横向,横向变成纵向(经典案例添加自己的想法)