如何能在执行计划中看到Starts

SQL> explain plan for
  2
  2  SELECT *
  3  FROM scott.emp
  4  WHERE NOT EXISTS (SELECT 0
  5  FROM scott.dept
  6  WHERE dept.dname = 'SALES' AND dept.deptno = emp.deptno)
  7  AND NOT EXISTS (SELECT 0
  8  FROM scott.bonus
  9  WHERE bonus.ename = emp.ename);

Explained

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 734347697
-----------------------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |     9 |   522 |     9  (12)| 00:00:01 |
|*  1 |  HASH JOIN ANTI     |       |     9 |   522 |     9  (12)| 00:00:01 |
|*  2 |   HASH JOIN ANTI    |       |     9 |   459 |     7  (15)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP   |    14 |   532 |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| DEPT  |     1 |    13 |     3   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL | BONUS |     1 |     7 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("BONUS"."ENAME"="EMP"."ENAME")
   2 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
   4 - filter("DEPT"."DNAME"='SALES')

使用advanced也不显示
SQL> explain plan for select * from scott.emp;

Explained

SQL> SELECT * FROM table(dbms_xplan.display(NULL,NULL,'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   532 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / EMP@SEL$1
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "EMP"@"SEL$1")

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('11.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - "EMP"."EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10],
       "EMP"."JOB"[VARCHAR2,9], "EMP"."MGR"[NUMBER,22],
       "EMP"."HIREDATE"[DATE,7], "EMP"."SAL"[NUMBER,22],
       "EMP"."COMM"[NUMBER,22], "EMP"."DEPTNO"[NUMBER,22]

要得到starts要么在SQLPLUS中将statistics_level设置all,并真实执行sql语句
因为display_cursor是从动态性能视图v$session,v$sql,v$sql_plan和v$sql_plan_statistics_all中获取直接计划的使用explain plan for不行
因为它只生成执行计划并没真正地执行而且执行计划是存储在plan_table表中
并使用display来显示

要么在要执行的sql语句中statistics_level 仍然保留’typical’ ,
然后用这个HINT/*+gather_plan_statistics */
SQL> alter session set statistics_level=all;

会话已更改。

SQL> select count(*) from scott.emp;

COUNT(*)
———-
14

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

SQL_ID  abj9tmfcs15bm, child number 0
-------------------------------------
select count(*) from scott.emp

Plan hash value: 2937609675

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

| Id  | Operation        | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

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

|   0 | SELECT STATEMENT |        |      1 |        |      1 |00:00:00.01 |  1      |

|   1 |  SORT AGGREGATE  |        |      1 |      1 |      1 |00:00:00.01 |  1      |

|   2 |   INDEX FULL SCAN| PK_EMP |      1 |     14 |     14 |00:00:00.01 |  1      |

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

已选择14行。

SQL>

SQL> alter session set statistics_level='typical';

会话已更改。

SQL> select /*+gather_plan_statistics */ count(*) from scott.emp;

  COUNT(*)
----------
        14

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

SQL_ID  2vku9s3sb55tz, child number 0
-------------------------------------
select /*+gather_plan_statistics */ count(*) from scott.emp

Plan hash value: 2937609675

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

| Id  | Operation        | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

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

|   0 | SELECT STATEMENT |        |      1 |        |      1 |00:00:00.01 |  1 |

|   1 |  SORT AGGREGATE  |        |      1 |      1 |      1 |00:00:00.01 |  1 |

|   2 |   INDEX FULL SCAN| PK_EMP |      1 |     14 |     14 |00:00:00.01 |  1 |

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

已选择14行。

使用dbms_xplan包来获得sql语句的执行计划

dbms_xplan包
dbms_xplan包可以用来显示存储在三个不同地方的执行计划:plan_table表,库缓存和awr.下面将会介绍dbms_xplan包中可用的函数.

输出
这里主要解释通过dbms_xplan包中函数返回的信息.下面是dbms_xplan.display_cursor输出信息的第一部分
SQL_ID 9nrttza3c2x2u, child number 0
————————————-
select * from scott.emp where empno=7788
Plan hash value: 2949544139

在这一部信息中指出与sql语句相关的以下信息:
sql_id识别父游标.这个信息只有当使用display_cursor和display_awr时才有

child number与sql_id一起用来识别子游标.这种信息只有当使用display_cursor时才有

sql语句的文本只有当使用display_cursor和display_awr函数时才有

第二部分显示的是一个表中的执行计划哈希值和执行计划本身:

Plan hash value: 2949544139
--------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |     1 (100)|
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    38 |     1   (0)| 00:0
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)|
--------------------------------------------------------------------------------

在这个表中,评估和每一操作的执行统计都被提供.这个表中的列数直接取决于可用信息的数量.例如,关于分区的信息,并行处理或执行统计.由于这个原因相同的函数和完全相同的参数可能产生两组不同的输出结果.在这种情况下,你将看一下通常可用的列:
包含执行计划的表中的列如下:
列 描述
id 在执行计划中标识每一个操作.如果在数字的前面有一 个星号.它意味着这一行是谓词信息
operation 被执行操作.也叫做行资源操作
name 被执行操作的对象

查询优化器评估
rows(e-rows) 评估操作所返回的行数
bytes(e-bytes) 评估操作所返回的数据量
TempSpc 评估操作使用的临时表空间大小
cost(%cpu) 评估操作的成本.在括号中指出了cpu成本的百分比.这个值是通过执行计划来计算的.
换句话说,父操作的成本包含了子操作的成本
Time 评估执行这个操作需要的时间(HH:MM:SS)

分区信息
pstart 第一个分区被访问的次数.如果在解析时未知,可以设置为key,key(I),key(mc),key(or)或
kye(sq)

pstop 最后一个分区被访问的次数.如果在解析时未知,可以设置为key,key(I),key(mc),key(or)
或kye(sq)

并行和分布式处理
inst 对于分布式处理,操作使用的数据库链路名称
tq 对于并行处理,在两个并行从属进程之间通信的表队列
in-out 并行或分布式操作之间的关系
pqdistrib 对于并行处理,由生产者使用分布处理将数据返回给消费者

运行时统计
starts 一个特定操作被执行的次数
a-rows 操作返回的实际行数
a-time 操作实际花费的时间(HH:MM:SS)

I/O统计
buffers 在执行时执行的逻辑读取的次数
reads 在执行时执行的物理读取的次数
writes 在执行时执行的物理写的次数

内存利用统计
0Mem 评估一个最优的执行所需要的内存总量
1Mem 评估一次通过执行所需要的内存总量
0/1/m 在最优,一次通过和多次通过模型下被执行的次数

used_mem 在最后一次执行操作时使用的内存量
used_tmp 在最后一次执行操作时使用的临时表空间量.它是以字节为单位

max_tmp 操作中使用临时表空间的最大量
下面的部分显示的是查询块的名字和对象别名:
Query Block Name / Object Alias (identified by operation id):
————————————————————-
1 – SEL$1 / EMP@SEL$1
2 – SEL$1 / EMP@SEL$1
对于执行计划中的每一个操作都会有一个查询与之相关

第四部分只在oracle10gr2中可用,下面显示是在oracle11G中的输出信息.它显示了为了强制特定的执行计划可以设置提示
这种设置提示叫做计划概要

Outline Data
-------------
/*+

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
BEGIN_OUTLINE_DATA
INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.2.0.1')
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/

下面的部分显示谓词信息
Predicate Information (identified by operation id):
—————————————————
2 – access(“EMPNO”=7788)
Column Projection Information (identified by operation id):
———————————————————–
1 – “EMPNO”[NUMBER,22], “EMP”.”ENAME”[VARCHAR2,10],
“EMP”.”JOB”[VARCHAR2,9], “EMP”.”MGR”[NUMBER,22], “EMP”.”HIREDATE”[DATE,7]
“EMP”.”SAL”[NUMBER,22], “EMP”.”COMM”[NUMBER,22], “EMP”.”DEPTNO”[NUMBER,22

PLAN_TABLE_OUTPUT
——————————————————————————–
2 – “EMP”.ROWID[ROWID,10], “EMPNO”[NUMBER,22]

display函数
display函数返回存储在plan_table表中的执行计划.它返回的是一个实例集合dbms_xplan_type_table.
集合中的元素是实例对象类型dbms_xplan_type.唯一属性的对象类型名叫plan_table_output是varchar2类型
这个函数有以下输出参数:
table_name:指定plan_table表名.缺省值是plan_table如果指定为null将使用缺省值

statement_id:指定sql语句名字是一个可选参数.当执行explain plan语句时.缺省值是null,如果使用缺省值.
那么最近插入到plan_table表中的执行计划会被显示(提供了filter_reds参数但没有指定)

format:指定什么信息被提供在输出信息中.这里有一些原始值(basic,typical,serial,all和advanced).为了
更好的控制有一些额外的修饰符(alias,bytes,cost,note,outline,parallel,partition,peeked_binds,
predicate,projection,remote和rows)可以被加到参数中.如果信息需要被添加可以通过字符+做为可选的修饰符
例如(basic+predicate).如果信息要被删除可以通过字符-做为可选的修饰符(例如,typical-bytes).多个修饰符
可以同时被指定例如(typical+alias-bytes-cost).缺省值是typical,原始值advanced和可用的修饰符只在
oracle10gr2中有.

filter_preds:当查询plan_table表时应用一个限制.这个限制是基于plan table表中某一列的一个常规sql谓词(
例如statement_id=’test’).缺省值是null.如果使用缺省值,那么最近插入plan_table表中的执行计划将会被显示.
这个参数只能在oracle10gr2中使用.

为了使用display函数,调用都只需要有dbms_xplan包的execute权限和plan_table表的select权限

格式参数的原始值表
值 描述
basic 只会显示最小量的信息,基本只有操作和被执行的对象的信息

typical 显示最相关的信息,除以别名,计划概要和列投影信息以外的信息

serial 象typical只有并行处理的信息不显示

all 显示了除了计划概要以外的所有信息

advanced 显示所有可用的信息

236

格式参数可以使用的修饰符
值 描述
alias 控制显示的查询块名字和对象别名
bytes 控制在执行计划表中列bytes的显示
cost 控制在执行计划表中列cost的显示
note 控制注释的显示
outline 控制概要的显示
parallel 控制并行处理信息的显示特别是执行计划表中的TQ,IN-OUT和PQ Distrib列的显示
partition 控制分区信息的显示特别是执行计划表中的Pstart和Pstop列的显示
peeked_binds 控制窥视绑定变量的显示
predicate 控制过滤和访问谓词的显示
projection 控制列投影信息的显示
remote 控制远程执行sql语句的显示
rows 控制执行计划表中的rows列的显示

下面的查询将显示使用不同的格式参数值basic,typical和advanced来显示执行计划.

SQL> explain plan for select * from scott.emp where empno=7788;

Explained

SQL> SELECT * FROM table(dbms_xplan.display(NULL,NULL,'basic'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2949544139
----------------------------------------------
| Id  | Operation                   | Name   |
----------------------------------------------
|   0 | SELECT STATEMENT            |        |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |
|   2 |   INDEX UNIQUE SCAN         | PK_EMP |
----------------------------------------------

9 rows selected

SQL> SELECT * FROM table(dbms_xplan.display(NULL,NULL,'typical'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    38 |     1   (0)| 00:0
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    38 |     1   (0)| 00:0
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("EMPNO"=7788)

14 rows selected

SQL> SELECT * FROM table(dbms_xplan.display(NULL,NULL,'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    38 |     1   (0)| 00:0
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    38 |     1   (0)| 00:0
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:0
--------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / EMP@SEL$1
   2 - SEL$1 / EMP@SEL$1
Outline Data
-------------
  /*+

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
      BEGIN_OUTLINE_DATA
      INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO"))
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('11.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("EMPNO"=7788)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - "EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10],
       "EMP"."JOB"[VARCHAR2,9], "EMP"."MGR"[NUMBER,22], "EMP"."HIREDATE"[DATE,7]
       "EMP"."SAL"[NUMBER,22], "EMP"."COMM"[NUMBER,22], "EMP"."DEPTNO"[NUMBER,22

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   2 - "EMP".ROWID[ROWID,10], "EMPNO"[NUMBER,22]

42 rows selected

下面的查询将显示使用格式化参数basic和typical并使用修饰符来增加或删除所要输出的信息.

SQL> SELECT * FROM table(dbms_xplan.display(NULL,NULL,'basic +predicate',NULL));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2949544139
----------------------------------------------
| Id  | Operation                   | Name   |
----------------------------------------------
|   0 | SELECT STATEMENT            |        |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |
----------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("EMPNO"=7788)

14 rows selected

SQL>
SQL> SELECT * FROM table(dbms_xplan.display(NULL,NULL,'typical -bytes -note',NULL));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2949544139
------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("EMPNO"=7788)

14 rows selected

display_cursor函数
display_cursor函数用来显示存储在库缓存中的执行计划.在oracle10g中可以使用,和display一样它返回也是实例集合
dbms_xplan_type_table.这个函数有以下输入参数:
sql_id:指定要被返回的执行计划的父游标.缺省值是null,如果使用缺省值会显示当前会话最后一次执行的sql语句的
执行计划

cursor_child_no:指定子游标号与sql_id一起用来识别被返回的执行计划的子游标.缺省值是0,如果指定为null,那么通过
sql_id找到的父游标的所有子游标

format:指定哪些信息被显示.这个参数与display的格式化参数一样.如果执行统计可用(换句话说,如查初始化参数
statistics_level设置为all或在sql语句中指定gather_plan_statistics提示),那么也支持修饰符.它的缺省值为
typical

为了使用display_cursor函数,调用都要对以下动态性能视图v$session,v$sql,v$sql_plan和v$sql_plan_statistics_all
有select权限.而select_catalog_role角色和select any dictionary系统权限提供了这些权限

格式化可以使用的修饰符
值 描述
allstats* 这是一个对于iostats,memstats的一个快捷方式
iostats* 控制I/O统计的显示
last* 默认是所有执行的累积统计被显示如果这个值被指定只有最后的执行统计被显示
memstats* 控制PGA相关统计的显示
runstats_last 和iostats last一样,它只能在oracle10gr1中使用
runstats_tot 和iostats一样,只能在oracle10gr1中使用

下面的例子显示在查询语句中使用gather_plan_statistics来生成执行计划.display_cursor函数针显示最后执行的
I/O统计.注意这里只会显示逻辑读取操作(buffers)因为这里没有物理读或写:

SQL> select /*+ gather_plan_statistics */ * from scott.emp where empno=7788;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20

SQL> select a.SQL_TEXT,a.SQL_ID from v$sqlarea a where a.SQL_TEXT
  2  like '% select /*+ gather_plan_statistics */ * from scott.emp where empno=7788 %' and a.sql_text not like  '%v$sqlarea%'
  3  ;

SQL_TEXT                                                                         SQL_ID
-------------------------------------------------------------------------------- -------------
 select /*+ gather_plan_statistics */ * from scott.emp where empno=7788          dzbmswjhdhk8t




SQL> SELECT * FROM table(dbms_xplan.display_cursor('dzbmswjhdhk8t',0, 'iostats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  dzbmswjhdhk8t, child number 0
-------------------------------------
 select /*+ gather_plan_statistics */ * from scott.emp where empno=7788
Plan hash value: 2949544139
--------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Starts | E-Rows | A-Rows |   A-Ti
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |      1 |        |      1 |00:00:0
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |      1 |      1 |      1 |00:00:0
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |      1 |      1 |      1 |00:00:0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("EMPNO"=7788)

19 rows selected

display_awr函数
这个函数返回存储在awr中的执行计划.在oracle10g中可以使用.和display函数一样,它返回的也是一个实例集合
dbms_xplan_type_table.这个函数有以下输入参数:
sql_id:指定要被返回执行计划的父游标.这个参数没有缺省值

plan_hash_value:指定要被返回执行计划的哈希值.缺省值是null.如果使用缺省值,与通过sql_id标识的父游标相关的
所有执行计划都会被返回

db_id:指定要返回哪个数据库的执行计划,这个参数的缺省值是null,如果使用缺省值就代表是当前数据库

format:指定哪些信息会被显示.与display的格式化参数相同,缺省值是typical

为了能使用display_awr函数,调用者至少要对以下视图dba_hist_sql_plan和dbs_hist_sqltext有select权限.
如果db_id参数没有指定,那么对v$database视图要有select权限.select_catalog_role角色提供了这些权限.

当对于一个特定的游标有多个执行计划存在时使用plan_hash_value参数进行查询是有帮助的

SQL> SELECT * FROM table(dbms_xplan.display_awr('4pqx4cy7p7tnp',2657262937,NULL,'basic'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 4pqx4cy7p7tnp
--------------------
select * from v$sql_plan
Plan hash value: 2657262937
--------------------------------------
| Id  | Operation        | Name      |
--------------------------------------
|   0 | SELECT STATEMENT |           |
|   1 |  FIXED TABLE FULL| X$KQLFXPL |
--------------------------------------

13 rows selected

有很多情况导致一个游标有多个执行计划.比如象增加了一个索引或都数据发生变化(对象统计改变).基本上查询优化器的工作环境随时发生变化所以可能会生成不同的执行计划.因此当一个sql语句执行比较长的时间又没有报错你对这个语句的性能产生怀疑的时候输出的执行计划的信息对于诊断性能问题是有帮助的.如果在这种情况下,你可以基于输出的信息推断出导致问题的原因.

linux中误删除oracle数据文件的恢复操作

下面来模拟误删除users表空中的数据文件users01.dbf

[root@jingyong jingyong]# ls
control01.ctl example01_bak.dbf jy01.dbf redo02.log sysaux01.dbf system01.dbf undotbs01.dbf users01.dbf.bak
control02.ctl example01.dbf redo01.log redo03.log system_01.dbf temp01.dbf users01.dbf

删除users01.dbf

[root@jingyong jingyong]# rm -rf users01.dbf
SQL> create table testjy(id number(20));
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4:
'/u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

检查dbwr的进程PID

[root@jingyong ~]# ps -ef|grep dbw0|grep -v grep
oracle 2236 1 0 06:40 ? 00:00:01 ora_dbw0_jingyong

dbwr会打开所有数据文件的句柄。在proc目录中可以查到,目录名是进程PID,fd表示文件描述符
[root@jingyong ~]# cd /proc/2236/fd

[root@jingyong fd]# ls -l
total 0
lr-x------ 1 oracle oinstall 64 May 31 08:15 0 -> /dev/null
l-wx------ 1 oracle oinstall 64 May 31 08:15 1 -> /dev/null
l-wx------ 1 oracle oinstall 64 May 31 08:15 10 -> /u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_2213.trc
l-wx------ 1 oracle oinstall 64 May 31 08:15 11 -> /u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_2213.trm
lr-x------ 1 oracle oinstall 64 May 31 08:15 12 -> /u01/app/oracle/product/11.2.0/db/rdbms/mesg/oraus.msb
lr-x------ 1 oracle oinstall 64 May 31 08:15 13 -> /dev/zero
lr-x------ 1 oracle oinstall 64 May 31 08:15 14 -> /proc/2236/fd
lr-x------ 1 oracle oinstall 64 May 31 08:15 15 -> /dev/zero
lrwx------ 1 oracle oinstall 64 May 31 08:15 16 -> /u01/app/oracle/product/11.2.0/db/dbs/hc_jingyong.dat
lrwx------ 1 oracle oinstall 64 May 31 08:15 17 -> /u01/app/oracle/product/11.2.0/db/dbs/lkJINGYONG
lrwx------ 1 oracle oinstall 64 May 31 08:15 18 -> /u01/app/oracle/product/11.2.0/oradata/jingyong/control01.ctl
lrwx------ 1 oracle oinstall 64 May 31 08:15 19 -> /u01/app/oracle/product/11.2.0/oradata/jingyong/control02.ctl
l-wx------ 1 oracle oinstall 64 May 31 08:15 2 -> /dev/null
lrwx------ 1 oracle oinstall 64 May 31 08:15 20 -> /u01/app/oracle/product/11.2.0/oradata/jingyong/system01.dbf
lrwx------ 1 oracle oinstall 64 May 31 08:15 21 -> /u01/app/oracle/product/11.2.0/oradata/jingyong/sysaux01.dbf
lrwx------ 1 oracle oinstall 64 May 31 08:15 22 -> /u01/app/oracle/product/11.2.0/oradata/jingyong/undotbs01.dbf
lrwx------ 1 oracle oinstall 64 May 31 08:15 23 -> /u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf (deleted)
lrwx------ 1 oracle oinstall 64 May 31 08:15 24 -> /u01/app/oracle/product/11.2.0/oradata/jingyong/example01.dbf
lrwx------ 1 oracle oinstall 64 May 31 08:15 25 -> /u01/app/oracle/product/11.2.0/oradata/jingyong/jy01.dbf
lrwx------ 1 oracle oinstall 64 May 31 08:15 26 -> /u01/app/oracle/product/11.2.0/oradata/jingyong/temp01.dbf
lr-x------ 1 oracle oinstall 64 May 31 08:15 27 -> /u01/app/oracle/product/11.2.0/db/rdbms/mesg/oraus.msb
l-wx------ 1 oracle oinstall 64 May 31 08:15 3 -> /u01/app/oracle/product/11.2.0/db/rdbms/log/jingyong_ora_2213.trc
lr-x------ 1 oracle oinstall 64 May 31 08:15 4 -> /dev/null
lr-x------ 1 oracle oinstall 64 May 31 08:15 5 -> /dev/null
lr-x------ 1 oracle oinstall 64 May 31 08:15 6 -> /dev/null
lrwx------ 1 oracle oinstall 64 May 31 08:15 7 -> /u01/app/oracle/product/11.2.0/db/dbs/hc_jingyong.dat

注意其中”/u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf (deleted)”字样,表示该文件已经被删除,
直接cp该句柄文件名回原位置

[root@jingyong fd]# cp 23 /u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf

数据文件users01.dbf恢复回来了,因为了用的是root用户操作的要修改一下权限

[root@jingyong jingyong]# ls -lrt
total 2564428
-rw-r----- 1 root root 723525632 May 16 13:33 system_01.dbf
-rw-r----- 1 root root 104865792 May 22 15:46 example01_bak.dbf
-rw-r----- 1 oracle oinstall 52429312 May 31 06:40 redo02.log
-rw-r----- 1 oracle oinstall 52429312 May 31 06:40 redo01.log
-rw-r----- 1 oracle oinstall 1056768 May 31 06:40 jy01.dbf
-rw-r----- 1 oracle oinstall 104865792 May 31 06:40 example01.dbf
-rw-r----- 1 root root 24911872 May 31 08:16 users01.dbf.bak
-rw-r----- 1 oracle oinstall 31465472 May 31 08:20 temp01.dbf
-rw-r----- 1 oracle oinstall 608182272 May 31 08:21 sysaux01.dbf
-rw-r----- 1 oracle oinstall 104865792 May 31 08:22 undotbs01.dbf
-rw-r----- 1 oracle oinstall 723525632 May 31 08:22 system01.dbf
-rw-r----- 1 oracle oinstall 52429312 May 31 08:22 redo03.log
-rw-r----- 1 root root 24911872 May 31 08:23 users01.dbf
-rw-r----- 1 oracle oinstall 10076160 May 31 08:23 control02.ctl
-rw-r----- 1 oracle oinstall 10076160 May 31 08:23 control01.ctl

[root@jingyong jingyong]# chown oracle:oinstall users01.dbf
[root@jingyong jingyong]# chmod 777 users01.dbf
[root@jingyong jingyong]# ls -lrt
total 2564428
-rw-r----- 1 root root 723525632 May 16 13:33 system_01.dbf
-rw-r----- 1 root root 104865792 May 22 15:46 example01_bak.dbf
-rw-r----- 1 oracle oinstall 52429312 May 31 06:40 redo02.log
-rw-r----- 1 oracle oinstall 52429312 May 31 06:40 redo01.log
-rw-r----- 1 oracle oinstall 1056768 May 31 06:40 jy01.dbf
-rw-r----- 1 oracle oinstall 104865792 May 31 06:40 example01.dbf
-rw-r----- 1 root root 24911872 May 31 08:16 users01.dbf.bak
-rw-r----- 1 oracle oinstall 31465472 May 31 08:20 temp01.dbf
-rw-r----- 1 oracle oinstall 723525632 May 31 08:22 system01.dbf
-rwxrwxrwx 1 oracle oinstall 24911872 May 31 08:23 users01.dbf
-rw-r----- 1 oracle oinstall 104865792 May 31 08:23 undotbs01.dbf
-rw-r----- 1 oracle oinstall 608182272 May 31 08:23 sysaux01.dbf
-rw-r----- 1 oracle oinstall 52429312 May 31 08:23 redo03.log
-rw-r----- 1 oracle oinstall 10076160 May 31 08:23 control02.ctl
-rw-r----- 1 oracle oinstall 10076160 May 31 08:23 control01.ctl

进行数据文件恢复

[oracle@jingyong ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri May 31 08:24:35 2013

Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database datafile 4 offline;

Database altered.

SQL> recover datafile 4;
Media recovery complete.
SQL> alter database datafile 4 online;

Database altered.

恢复的原理是,在Linux操作系统中,如果文件从操作系统级别被rm掉,之前打开该文件的进程仍然持有相应的文件句柄,所指向的文件仍然可以读写,并且该文件的文件描述符可以从/proc目录中获得。但是要注意的是,此时如果关闭数据库,则此句柄会消失.

oracle中的sql trace

sql trace
为了处理sql语句,数据库引擎(特别是sql引擎)会执行数据库调用(解析,执行和获取).对于每一种数据库调用都会给出总的概括,sql引擎:通过使用CPU它本身也做一些处理
利用其它资源(如,磁盘)通过一个同步点来保证多用户使用数据库引擎的能力(如latch闩锁)

sql trace的目的是双重的:第一,它将响应时间分成了服务时间和等待时,第二它提供了关于使用资源和同步点(latch)的详细信息.所有关于sql引擎和其它组件之间的交互信息都被写入到跟踪文件中了.

通过sql trace得到的各种信息可以通过tkprof工具抽取出来.它们包括sql语句的文本,一些执行统计,在处理阶段发生的等待和解析步骤生成的执行计划.注意,提供了通过应用程序执行的每一个sql语句的信息和数据库引擎本身的递归调用的信息.

SELECT CUST_ID, EXTRACT(YEAR FROM TIME_ID), SUM(AMOUNT_SOLD)
FROM SH.SALES
WHERE CHANNEL_ID = :B1
GROUP BY CUST_ID, EXTRACT(YEAR FROM TIME_ID)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 164 1.12 1.90 2588 1720 0 16348
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 166 1.13 1.90 2588 1720 0 16348
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 28 (SH) (recursive depth: 1)
Rows Row Source Operation
------ ---------------------------------------------------
16348 HASH GROUP BY
540328 PARTITION RANGE ALL PARTITION: 1 28
540328 TABLE ACCESS FULL SALES PARTITION: 1 28
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 30 0.01 0.07
db file scattered read 225 0.02 0.64
direct path write temp 941 0.00 0.00
direct path read temp 941 0.01 0.05

上面的信息是通过tkprof从跟踪文件抽取出来的,它不是直接将sql trace文件中的信息给输出.事实上,sql trace生成的跟踪文件中的信息是没有加工的信息.例如下面就是上面输出的源始跟踪文件信息,一般来说,对于每一个调用或等待,在跟踪文件中至少有一行信息

...
...
PARSING IN CURSOR #1 len=142 dep=1 uid=28 oct=3 lid=28 tim=1156387084566620
hv=1624534809 ad='6f8a7620'
SELECT CUST_ID, EXTRACT(YEAR FROM TIME_ID), SUM(AMOUNT_SOLD) FROM SH.SALES
WHERE CHANNEL_ID = :B1 GROUP BY CUST_ID, EXTRACT(YEAR FROM TIME_ID)
END OF STMT
PARSE #1:c=0,e=93,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1156387084566617
BINDS #1:
kkscoacd
Bind#0
oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1206001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=2a9721f070 bln=22 avl=02 flg=05
value=3
EXEC #1:c=1000,e=217,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1156387084566889
WAIT #1: nam='db file sequential read' ela= 19333 file#=4 block#=211 blocks=1
obj#=10293 tim=1156387084610301
WAIT #1: nam='db file sequential read' ela= 2962 file#=4 block#=219 blocks=1
obj#=10294 tim=1156387084613517
...
...
WAIT #2: nam='SQL*Net message from client' ela= 978 driver id=1413697536 #bytes=1
p3=0 obj#=10320 tim=1156387086475763
STAT #1 id=1 cnt=16348 pid=0 pos=1 obj=0 op='HASH GROUP BY (cr=1720 pr=2588 pw=941
time=1830257 us)'
STAT #1 id=2 cnt=540328 pid=1 pos=1 obj=0 op='PARTITION RANGE ALL PARTITION: 1 28
(cr=1720 pr=1647 pw=0 time=1129471 us)'
STAT #1 id=3 cnt=540328 pid=2 pos=1 obj=10292 op='TABLE ACCESS FULL SALES PARTITION:
1 28 (cr=1720 pr=1647 pw=0 time=635959 us)'
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0
obj#=10320 tim=1156387086475975

上面的输出中:
PARSING IN CURSOR and END OF STMT之间包含的是sql语句文本
PARSE, EXEC, and FETCH 分别对应 parse, execution, and fetch 调用

BINDS:定义绑定变量和变量值
WAIT 在处理阶段发生的等待事件
STAT:是执行计划和相关的统计信息

事实上,sql trace是基于调试事件10046的.10046事件支持多种级别,这些级别定义了写入跟踪文件中的信息量.当sql trace使用大于1的级别它也叫扩展sql trace.

10046诊断事件的跟踪级别
0级:诊断事件被禁用

1级:诊断事件被启用,对于每一个数据库调用进程都会记录以下信息:sql语句,响应时间,服务时间,处理的行数,逻辑读,物理读和写,执行计划和一些其它信息

4级:在1级的基础上,增加了关于绑定变量的信息,主要是数据类型,精度和每次执行时的值

8级:在1级的基础上增加了关于等待时间的详细信息,对于执行时的每一个等待都记录了以下信息:等待事件名称,持续时间和另外一些识别等待资源的参数信息

12级:同时包含级别4和级别8的信息

调试(诊断)事件
一个诊断事件是通过一个数字来进行标识的,它意味着在运行的数据库引擎中通过设置一种类型的标记来启用诊断事件.诊断事件的目的是改变其行为,例如可以通过启用或禁用一个事件,来测试或模拟错误或崩溃,或收集跟踪或调试信息,有一些诊断事件不是简单的标记它其实也能在服务器级别启用.每一种级都有属于它的行为.在有些情况下,这级别变成了一个数据块的地址或内存结构

你在使用诊断事件时要小心,只有当你在oracle客户支持或你已经了解这个诊断事件将会改变什么的情况下你才能设置诊断事件.诊断事件启用特定的代码路径.因此,如果当设置诊断事件后出现了问题,那么有必要在禁用该诊断事件的情况来检查该问题是否还是会出现.

一些诊断事件记录在oracle的文档中,如果文档存它通常是通过MetaLink来提供的,也就是说这些诊断事件是不会发布在oracle的官方文档中的.你能通$ORACLE_HOEM/rdbms/mesg/oraus.msg文件找到可用的完整的诊断事件列表.注意这个文件不会对所有平台都进行发布,10000到10999是保留给诊断事件使用的所有诊断事件

启用sql跟踪:遗留的启用方式
到oracle9i时,Database Performance Tuning Guide and Reference manual文档描述了三种启用sql跟踪的方式:
设置初始化参数sql_trace,使用dbms_session包中的set_sql_trace过程和使用dbms_system包中的
set_sql_trace_in_session过程.需要注意的是这三种方法它们都只是使用1级的sql跟踪功能.不幸的是,这些方式在实际
情况下是不足的.事实上,大多数情况下你需要完全分解响应时间并理解瓶颈出在哪里.由于这个原因我们将不会详细描述
这三种方法.这里我们将介绍一些非官方的在任何级别启用sql跟踪的方法.在oracle10g中的文档中已经介绍了启用或禁用
sql跟踪的方法了.因此,如果是在oracle10g中你将不必使用上面描述的方法来进行sql跟踪.然而如果是在oracle10g以前的
版本,你能够使用上面的方法因为它们都已经使用多年了.

为了能够启用或禁用任何级别的sql跟踪,这里有两种方法.你可以通过alter session语句来设置事件参数来启用特定的
跟踪事件或者你也能调用dbms_system包中的set_ev过程.前者只能对当前启用sql跟踪的会话进行跟踪而后都可以通过
设置会话ID(sid)和序列号(serial#)来对任何会话启用sql跟踪.下面是使用这些方法的例子

下面的例子对要启用跟踪的会话启用级别为12的sql跟踪,注意事件号和级别是怎样被指定的:

ALTER SESSION SET events '10046 trace name context forever, level 12'

下面的例子对要禁用跟踪的会话禁用sql跟踪,注意禁用sql跟踪不是通过将sql跟踪级别指定为0来实现的.

ALTER SESSION SET events '10046 trace name context off'

下面的pl/sql调用对sid=127,serial#=29的会话启用级别为12的sql跟踪.没有参数是默认值,尽管在这种情况下最后一个参数也必须指定

dbms_system.set_ev(si => 127, -- session id
se => 29, -- serial number
ev => 10046, -- event number
le => 12, -- level
nm => NULL)

下面的pl/sql调用对sid=127,serial#=29的会话禁用sql跟踪,注意这里只要将跟踪级别修改为0就可以了

dbms_system.set_ev(si => 127, -- session id
se => 29, -- serial number
ev => 10046, -- event number
le => 0, -- level
nm => NULL)

你能通过执行下面的查询来列出每一个连接到实例的用户的会话ID和会话序号

SQL>SELECT sid, serial#, username, machine FROM v$session WHERE type != 'BACKGROUND';

SID SERIAL# USERNAME MACHINE
---------- ---------- ------------------------------ ---------------------------
31 57 SYS WORKGROUP\JINGYONG
34 61 jingyong
40 65 SYS WORKGROUP\JINGYONG
51 68 jingyong

你能通过执行alter system语句来设置初始化参数事件.它的语法与alter session的语法相同.
在任何情况下通常都不需要在实例级启用sql跟踪,另外需要注意的是只有在会话创建启用跟踪后才能生效.

通常情况下,dbms_system包只能由sys用户来执行.如果执行权限被授予给其它用户,那么要小心因为这个包
包含了其它的过程和set_ev过程本身它能够用来设置其它事件.如果你真的需要让其它的用户有权来对任何
其它的会话启用或禁用sql跟踪,那么建议你使用另一个只包含启用或禁用sql跟踪的包比如dbms_support.
但是dbms_support缺省的情况下是没有安装的.dbms_support只在MetaLink中有记录,dbms_support包是不受
oracle官方支持的
为了安装dbms_supoort包并给它创建一个共用同义词,授予dba角色来执行它:

CONNECT / as sysdba
@?/rdbms/admin/dbmssupp.sql
CREATE PUBLIC SYNONYM dbms_support FOR dbms_support;
GRANT EXECUTE ON dbms_support TO dba;

这个包真地不应该用于不是oracle10g的数据库.如果你在安装dbms_support包时出现了警告(例如设置初始化
参数plsql_warning to enbale:all),当创建dbms_support包时出现了如下警告可以忽略它:

PLW-05005: function CURRENT_SERIAL returns without value at line 29

下面的pl/sql调用将对sid=127,serial#=29的会话启用级别为8的sql跟踪.注意设置sql跟踪级别的操作在启用
sql跟踪时被第三,第四个参数取代了.你能指定你是否想启用对等待和绑定变量信息的跟踪.因此第一,二两个
参数没有缺省值,waits参数缺省值为true,binds参数缺省值为false

dbms_support.start_trace_in_session(sid => 127,
serial => 29,
waits => TRUE,
binds => FALSE)

下面的pl/sql调用将对sid=127,serial#-29的会话禁用sql跟踪.这两个参数是没有缺省值

dbms_support.stop_trace_in_session(sid => 127,
serial => 29)

启用sql跟踪:当前使用的方式
对于oracle10g来说启用或禁用sql跟踪使用dbms_monitor包.使用这个dbms_monitor包你不仅有一个官方的方式来
让你完全使用sql跟踪的功能,更重要的是你能根据会话属性来启用或禁用sql跟踪.会话属性包括:客户端标识符,
服务名,模块名和操作名.这意味着如果程序正在被做正确的性能测量,你能独立于执行数据库调用的会话来启用或
禁用sql跟踪.对于今天来说这是特别有用的因为在许多情况下是使用连接池因此用户是不能系住一个特定的会话的.
下面是一些使用dbms_monitor包来在会话,客户端,组件和数据库级别来启用或禁用sql跟踪的例子.注意,缺省情况下,
只能有dba角色的用户能使用dbms_monitor

会话级
为了对一个会话启用或禁用sql跟踪.dbms_monitor提供了session_trace_enable和session_trace_disable过程
下面的pl/sql代码就是对sid=31,serial#=57的会话启用级别为8的sql跟踪.所有的参数都有缺省值.如果两个标识
会话的参数没有指定,那么会对执行这个plsql代码的会话启用sql跟踪.waits参数缺省值是true,binds参数的缺省
值是false

SQL>exec dbms_monitor.session_trace_enable(session_id => 31,
serial_num =>57,
waits => true,
binds => true);

如果是在oracle10gR2的版本中当使用dbms_monitor.session_trace_enable过程启用sql跟踪后那么在v$session
视图中的sql_trace,sql_trace_waits和sql_trace_binds字段会进行设置.警告:当使用session_trace_enable过程
启用sql跟踪后且至少有一个sql语句在这个启用跟踪的会话中被执行才会这样. 但是在oracle11G中只要启用这三个
字段就会被设置

SQL> select sql_trace,sql_trace_waits,sql_trace_binds from v$session where sid=31;

SQL_TRACE SQL_TRACE_WAITS SQL_TRACE_BINDS
--------- --------------- ---------------
ENABLED TRUE TRUE

下面的pl/sql代码是对sid=31,serial#=57的会话禁用sql跟踪.注意这两个参数都有缺省值,如果你没有指定标识会话的
两个参数那么就会对执行pl/sql代码的会话禁用sql跟踪.

SQL>exec dbms_monitor.session_trace_disable(session_id => 31,serial_num => 57);

注意,如果是在RAC中使用,那么session_trace_enable和session_trace_disable必须在会话所在的实例上执行.

客户端级别
为了对一个客户端启用或禁用sql跟踪可以分别执行dbms_monitor包中的client_id_trace_enable和
client_id_trace_disable过程.通常这些过程只有对哪些会话已经设置client标识符的会话使用.

下面的pl/sql代码对有客户端标识符作为参数的所有会话启用级别为8的sql跟踪.client_id参数没有缺省值,
waits参数的缺省值为true,binds参数的缺省值为false,要注意client_id参数是区分大小写的.

SQL>exec dbms_monitor.client_id_trace_enable(client_id => 'jingyong',waits => true,binds => false);

PL/SQL procedure successfully completed

dba_enabled_traces视图显示了哪些客户端标识所对应的会话通过client_id_trace_enable过程启用了sql跟踪.

SQL> select primary_id as client_id,waits,binds
2 from dba_enabled_traces
3 where trace_type='CLIENT_ID';

CLIENT_ID WAITS BINDS
---------------------------------------------------------------- ----- -----
jingyong TRUE FALSE

下面的pl/sql代码将对有客户端标识符作为参数的所有会话禁用sql跟踪.client_Id参数是没有缺省值的

SQL>exec dbms_monitor.client_id_trace_disable(client_id => 'jingyong');

PL/SQL procedure successfully completed

组件级别
为了对一个组件通过指定服务名,模块名和操作名来启用或禁用sql跟踪.dbms_monitor包分别提供了
serv_mod_act_trace_enable和serv_mod_act_trace_disable过程.为了使用这些过程必须要设置会话属性,
模块名和操作名.

下面的pl/sql代码对哪些指定会话属性参数的所有会话启用或禁用级别为8的sql跟踪.只有一个参数没有缺省值就是
第一个参数:service_name.module_name和action_name参数的缺省值是any_module和any_action.同时null是一个有效值.
如果action_name参数被指定那么module_name也必须要指定.如果不这样做会触发ora-13859错误.waits参数的缺省值为
true,binds参数的缺省值为false.如果在RAC中使用instance_name参数可以控制它只跟踪单个实例.缺省的情况下是对
所有实例启用.要注意参数service_name,module_name,action_name和instance_name是区分大小写的

dbms_monitor.serv_mod_act_trace_enable(service_name => 'DBM10203.antognini.ch',
module_name => 'mymodule',
action_name => 'myaction',
waits => TRUE,
binds => FALSE,
instance_name => NULL)

和在客户端启用sql跟踪一样,dba_enabled_traces视图会显示哪个组件启用了sql跟踪,哪些参数通过
serv_mod_act_trace_enable过程设置为启用了.在启用sql跟踪后你能得到以下信息.注意如果没有指定这三个参数(
service name,module name和action name)而启用了sql跟踪,那么dba_enabled_traces视图中的trace_type列的值
将会概括使用的参数被设置成SERVICE或SERVICE_MODULE

SQL>SELECT primary_id AS service_name, qualifier_id1 AS module_name,
2 qualifier_id2 AS action_name, waits, binds
3 FROM dba_enabled_traces
4 WHERE trace_type = 'SERVICE_MODULE_ACTION';
SERVICE_NAME MODULE_NAME ACTION_NAME WAITS BINDS
---------------------- ------------ ------------ ----- -----
DBM10203.antognini.ch mymodule myaction TRUE FALSE

下面的pl/sql代码将对指定会话参数的所有会话禁用sql跟踪.

dbms_monitor.serv_mod_act_trace_disable(service_name => 'DBM10203.antognini.ch',
module_name => 'mymodule',
action_name => 'myaction',
instance_name => NULL)

数据库级别
在oracle10gr2中,可以使用dbms_monitor包提供了database_trace_enable和database_trace_disable过程来
对所有连接到数据库的所有会话启用或禁用sql跟踪.

下面的pl/sql代码将对数据库启用级别为12的sql跟踪.所有的参数都有缺省值.waits参数的缺省值为true,
binds参数的缺省值为false.在rac中你可以通过使用instance_name参数来限制只对单个实例进行跟踪.
如果instance_name设置为null,null也是缺省值,那么将对所有实例启用sql跟踪.注意instance_name参数是区分大小写的.

dbms_monitor.database_trace_enable(waits => TRUE,
binds => TRUE,
instance_name => NULL)

和在客户端与在组件级别启用sql跟踪一样,dba_enabled_traces视图将会显示哪个实例已经启用了sql跟踪,通过
database_trace_enable过程设置哪些数为启用了.

SQL>SELECT instance_name, waits, binds
2 FROM dba_enabled_traces
3 WHERE trace_type = 'DATABASE';
INSTANCE_NAME WAITS BINDS
---------------- ----- -----
TRUE TRUE

下面的pl/sql代码将禁用对数据库的sql跟踪.如果instance_name被设置为null,null也是缺省值,将对会所有实例
禁用sql跟踪

dbms_monitor.database_trace_disable(instance_name => NULL);

触发sql跟踪
在前面已经介绍了各种不同的启用或禁用sql跟踪的方法.对于简单的情况,可以在sqlplus中手功执行显示sql语句或
pl/sql调用.有些时候然而需要自动触发sql跟踪,自动触发sql跟踪意味着需要增加代码.

最简单的方法是在数据库级别创建一个登录触发器.为了避免对所有用户启用sql跟踪,我通常建议创建一个角色(
例如叫sql_trace角色)和只是暂时授予给用户让其测试.通常也可能对单个方案定义触发器或执行其它的基本的检查.
例如,在userenv上下文中.注意除了启用sql跟外,也还可以设置其它与sql跟踪相关的参数.

CREATE ROLE sql_trace;
CREATE OR REPLACE TRIGGER enable_sql_trace AFTER LOGON ON DATABASE
BEGIN
IF (dbms_session.is_role_enabled('SQL_TRACE'))
THEN
EXECUTE IMMEDIATE 'ALTER SESSION SET timed_statistics = TRUE';
EXECUTE IMMEDIATE 'ALTER SESSION SET max_dump_file_size = unlimited';
dbms_monitor.session_trace_enable;
END IF;
END;
/

另一种方法是直接在应用程序控制中添加一些代码来启用sql跟踪.一些类型的参数将促使代码必须要被添加.
对于一个胖客户端程序的命令行参数或对于web程序来说的http参数就是这样的例子

跟踪文件中的时间统计信息
动态初始化参数timed_statistics可以动态地被设置为true或false.它将控制跟踪文件中的运行时间和cpu时间的
时间统计信息的可用性.如果timed_statistics参数设置为true,那么时间统计信息会被写入跟踪文件,如果设置为false
那么在跟踪文件中不会有时间统计信息,然而根据你工作的端口,它们可能是部分可用.timed_statistics参数的缺省值
还依赖于另一个初始化参数statistics_level.如果statistics_level被设置为basic,timed_statsitics缺省值为false
否则timed_statistics缺省值为true.

一般来说如果说时间统计信息不可用那么跟踪文件没有什么用.所以在启用sql跟踪之胶,timed_statistics参数要设置
为true,你也可以修改这个参数通过以下语句

ALTER SESSION SET timed_statistics = TRUE

动态初始化参数
有些初始化参数是静态地而有些是动态的.如果参数是动态参数它意味着它们不用重启实例就能修改.在这些动态
初始化参数中有一些只能在会话级进行修改,有一些只能在系统级进行修改还有一些可以在会话级和系统级进行修改.
为了在会话级和系统级修改初始化参数,你可以使用alter session和alter system语句.在实例级修改初始化参数会
立即生效或在会话级修改后会立即生效.在v$parameter视图中有许多列如isses_modifiable和issys_modifiable列.
从它们的值可以看出参数可以在什么级别被修改.

限制跟踪文件大小
通常,你可能不会关心跟踪文件的大小.如果需要设置跟踪文件大小,然而你可以在会话级或系统级设置动态初始化
参数max_dump_file_size.这个参数的值是以K或M以单位的,如果想设置跟踪文件大小不受限制,可以使用下面的语句
将该参数值设置为unlimited:

alter session set max_dump_file_size=unlimited

查找跟踪文件
跟踪文件是由运行在数据库服务器上的数据库引擎服务器进程所生成的.这意味着跟踪文件是写在数据库服务器上
可以访问的磁盘上的.根据进程生成的跟踪文件类型,它们会被存储在两个不同的目录:
专用的服务器进程创建跟踪文件的目录是由user_dump_dest初始化参数来设置的
后台进程创建的跟踪文件的目录是由background_dump_dest初始化参数来设置的

在oracle11g中引入了自动诊断资料库,user_dump_dest和background_dump_dest被弃用而使用diagnostic_dest目录
来代替.因此你只能使用新的初始化参数来设置基本目录,你能使用v$diag_info视图来查询跟踪文件的真实目录.
下面的查询显示了初始化参数值与跟踪文件目录的差别

SQL>conn sys/zzh_2046@jy_201 as sysdba
已连接。
SQL> SELECT value FROM v$parameter WHERE name = 'diagnostic_dest';

VALUE
--------------------------------------------------------------------------------

/u01/app/oracle

SQL> SELECT value FROM v$diag_info WHERE name = 'Diag Trace';

VALUE
--------------------------------------------------------------------------------

/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace

跟踪文件名称以前依赖于版本和平台,它们有如下结构:
{instance name}_{process name}_{process id}.trc

instance name:它是初始化参数instance_name值的小写,注意在RAC环境中,它是来自不同的初始化参数db_name.
在v$instance视图中的instance_name列是可用的

process name:这是生成跟踪文件的进程名的小写值.对于专用服务器进程,名字是ora,对于共享服务器进程,它的值
可以在v$dispatcher或v$shared_server视图中找到这个列名.对于并行服务器,可以从v$px_process视图中找到
server_name列,对于其它后台进程可以从v$bgprocess视图中找到这个列名.

process id:它是在操作系统级别来唯一标识进程的.它的值可以从v$process视图中的spid列得到.

基于这些信息,可以写一个快速查询每一个会话所生成的跟踪文件

SQL>SELECT s.sid,
2 s.server,
3 lower(
4 CASE
5 WHEN s.server IN ('DEDICATED','SHARED') THEN
6 i.instance_name || '_' ||
7 nvl(pp.server_name, nvl(ss.name, 'ora')) || '_' ||
8 p.spid || '.trc'
9 ELSE NULL
10 END
11 ) AS trace_file_name
12 FROM v$instance i,
13 v$session s,
14 v$process p,
15 v$px_process pp,
16 v$shared_server ss
17 WHERE s.paddr = p.addr
18 AND s.sid = pp.sid (+)
19 AND s.paddr = ss.paddr(+)
20 AND s.type = 'USER'
21 ORDER BY s.sid;
SID SERVER TRACE_FILE_NAME
---------- --------- --------------------------------------
145 DEDICATED dbm10203_ora_24387.trc
146 DEDICATED dbm10203_ora_24380.trc
147 NONE
149 DEDICATED dbm10203_ora_24260.trc
150 SHARED dbm10203_s000_24374.trc

而对于11g来说,对于当前会话你可以使用v$diag_info视图来查询:

SQL>SELECT value FROM v$diag_info WHERE name = 'Default Trace File';

VALUE
--------------------------------------------------------------------------------

/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_2594.trc

为了简单快速找到正确的跟踪文件,其实可以使用初始化参数tracefile_identifier.事实上使用这个参数,
你能给跟踪文件的名字加一个最多长度可达255个字符的标识符.如果使用这个参数那么跟踪文件的名称结构
就变成如下格式了:
{instance name}_{process name}_{process id}_{tracefile identifier}.trc
注意的是这个方法只适用于专用服务器进程,当一个新的跟踪文件创建时,这个参数的值会根据每个会话动态改变.
这个参数值是可从v$process视图中的traceid找到tracefile_identifier参数的值.

跟踪文件包含哪些机密信息
通常跟踪文件不是每个人都能访问的.这是因为跟踪文件可能包含机密信息.事实上,sql语句可能包含许多数据(文本值)
和绑定变量的值.这就意味着存储在数据库中的每一个数据片段都会被写入跟踪文件.

例如在unix/linux系统中,跟踪文件属于运行在数据库引擎上用户和组并且有-rw-r—权限.换句话说,也就是只有这个用户
和运行数据库的用户在同一个组才能读取这个跟踪文件.

然而真的没有必要阻上哪些可以访问数据库数据的人来访问这些跟踪文件,如果要求执行这样的操作,事实上从安全的角度
跟踪文件对于哪些没有访问数据库权限的人来说是一个有用的信息来源.对于这种情况,数据库引擎提供了一个没有记录在
文档中的参数_trace_files_public.缺省值被设置为false,如果设置为true,跟踪文件可以被每一个能访问系统的人读取.

例如在unix/linux中,将_trace_files_public设置为true,那么缺省的权限将变为-rw_r–r–,这样所有的用户都能读取\
这个跟文件.
跟踪文件的结构
一个跟踪文件包含了通过一个特定进程执行的数据库调用的信息.事实上,当进程ID在操作系统级别重用时,一个跟踪文件的
信息可能来自多个进程.因此一个进程可以被用于不同的会话(例如,共享服务或并行服务从属进程),每个会话可以有不同的
会话属性(例如,模块名和操作名),一个跟踪文件可以被分成几个逻辑部分

BEGIN
dbms_session.set_identifier(client_id=>'helicon.antognini.ch');
dbms_application_info.set_module(module_name=>'Module 1',
action_name=>'Action 11');
-- code module 1, action 11
dbms_application_info.set_module(module_name=>'Module 1',
action_name=>'Action 12');
-- code module 1, action 12
dbms_application_info.set_module(module_name=>'Module 1',
action_name=>'Action 13');
-- code module 1, action 13
dbms_application_info.set_module(module_name=>'Module 2',
action_name=>'Action 21');
-- code module 2, action 21
dbms_application_info.set_module(module_name=>'Module 2',
action_name=>'Action 22');
-- code module 2, action 22
END;

使用trcsess
你能使用命令行工具trcsess,它可用于oracle11g来抽取一个或多个跟踪文件中的部分信息.基于逻辑标记符
为了了解trcsess参数列表可以运行不带参数的trcsess

C:\Documents and Settings\Administrator>;trcsess
oracle.ss.tools.trcsess.SessTrcException: SessTrc-00002: 会话跟踪用法错误: 传递
了错误的参数。
trcsess [output=<output file name >] [session=<session ID>] [clientid=<clientid
>] [service=<service name>] [action=<action name>] [module=<module name>] <trace
file names>

output=<output file name> output destination default being standard output.
session=<session Id> session to be traced.
Session id is a combination of session Index & session serial number e.g. 8.13.

clientid=<clientid> clientid to be traced.
service=<service name> service to be traced.
action=<action name> action to be traced.
module=<module name> module to be traced.
<trace_file_names> Space separated list of trace files with wild card '*' suppor
ted.

就象你看到的一样,它可以指定一个会话,客户端id,服务名,模块名和操作名作为参数.例如你想从跟踪文件
dbm10203_ora_24433.trc中抽取操作为12的信息并将抽取到的信息写一个新的名为action12.trc的跟踪文件中
trcsess output=action12.trc action=”Action 12″ dbm10203_ora_24433.trc

记住,clientid,service,action和module参数是区分大小写的.这个trcsess工具也支持之前的版本生成的跟踪
文件.

剖析工具
当你已经识别正确的跟踪文件或使用trcsess工具截取它们的一部分.下面来分析它的内容,为了这个目的你可以
使用一种剖析工具.它的目的是根据原始的跟踪文件生成格式化的输出.oracle发布了数据库和客户端二进制文件
分析工具.它叫tkprof(它是标准的跟踪内核剖析器).虽然在有些情况下输出的信息是有用的,但有时它不能快速
识别性能问题.奇怪的是oracle没有重视这个工具的重要性,因此自从oracle7引入以来只做了稍微的改进.有很多
商业和免费的分析器可以使用.我还开发了一个自己的分析器叫TVD$XTAT,其它的分析器你可能会考虑Hotsos Profiler
itfprofSQL Analyzer和OraSRP.甚至是oracle的另一种分析器叫Trace Analyzer

下面来执行一段plsql代码并生成跟踪文件

SQL>declare
2 l_channel_id sh.sales.channel_id%type :=3;
3 begin
4 for c in (select cust_id,extract(year from time_id),sum(amount_sold)
5 from sh.sales
6 where channel_id=l_channel_id
7 group by cust_id,extract(year from time_id))
8 loop
9 null;
10 end loop;
11 end;
12 /

PL/SQL 过程已成功完成。
对于oracle11g获得当前会话的跟踪文件可以使用下面的查询

SQL>SELECT value FROM v$diag_info WHERE name = 'Default Trace File';

VALUE
--------------------------------------------------------------------------------

/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_2717.trc

也可以使用下面的方法来获得

SQL>oradebug setmypid
已处理的语句
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_2717.trc

使用tkprof
tkprof是一个命令行工具,它的主要目的是使用一个原始的跟踪文件作为输入并生成一个格式化的文本文件作为输出.
另外,它也能生成一个sql脚本在数据库中加载数据尽管这个功能从来没有使用过.

使用这个工具最简单的例子就是只指定一个输入文件和一个输出文件.在下面的例子中,输入文件为jingyong_ora_2717.trc
输出文件为jingyong_ora_2717.txt.尽管tkprof工具输出文件的缺省文件扩展名为prf.但是我总是使用txt.

[oracle@jingyong trace]$ tkprof jingyong_ora_2717.trc jingyong_ora_2717.txt

TKPROF: Release 11.2.0.1.0 - Development on Mon May 20 01:10:23 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

没有指定参数的分析只有当对小跟踪文件进行分析才有,在大多数情况下为了得到更好输出内容你必须指定一些参数

tkprof参数
如果你运行不带任何参数的tkprof,你将得到一个完整的参数列表并有它们的一个简短描述

[oracle@jingyong trace]$ tkprof
Usage: tkprof tracefile outputfile [explain= ] [table= ]
[print= ] [insert= ] [sys= ] [sort= ]
table=schema.tablename Use 'schema.tablename' with 'explain=' option.
explain=user/password Connect to ORACLE and issue EXPLAIN PLAN.
print=integer List only the first 'integer' SQL statements.
aggregate=yes|no
insert=filename List SQL statements and data inside INSERT statements.
sys=no TKPROF does not list SQL statements run as user SYS.
record=filename Record non-recursive statements found in the trace file.
waits=yes|no Record summary for any wait events found in the trace file.
sort=option Set of zero or more of the following sort options:
prscnt number of times parse was called
prscpu cpu time parsing
prsela elapsed time parsing
prsdsk number of disk reads during parse
prsqry number of buffers for consistent read during parse
prscu number of buffers for current read during parse
prsmis number of misses in library cache during parse
execnt number of execute was called
execpu cpu time spent executing
exeela elapsed time executing
exedsk number of disk reads during execute
exeqry number of buffers for consistent read during execute
execu number of buffers for current read during execute
exerow number of rows processed during execute
exemis number of library cache misses during execute
fchcnt number of times fetch was called
fchcpu cpu time spent fetching
fchela elapsed time fetching
fchdsk number of disk reads during fetch
fchqry number of buffers for consistent read during fetch
fchcu number of buffers for current read during fetch
fchrow number of rows fetched
userid userid of user that parsed the cursor

每一个参数的功能描述如下:
explain:它是指示tkprof对于跟踪文件中的每一个sql语句提供一个执行计划.这是通过执行explain plan sql语句来
完成的.很明显的是为了执行一个sql语句需要连接到数据库.因此必须指定用户和密码参数,如果需要还要指定连接串.
公认的格式为:explain=user/password@connect_string and explain=user/password.注意为了最大化你得到正确的
执行计划的机会,你应该指定一个能(和生成跟踪文件相同的用户)访问相同对象的用户并确保所有的查询优化器初始化
参数的设置和生成跟踪文件时是一样的.你应该小心初始化参数通过应用程序或登录触发器被修改.不用多说你使用
相同的用户是最好的.在任何情况下,尽管之前所说的所有条件都满足,通过explain plan生成的执行计划也不一定就是
和真实的相匹配.指定explain参数是不明智的.如果一个错误的用户名和密码或连接串被指定,那么处理这个跟踪文件时
不会有任何的错误信息,而在输出文件中可以找到如下错误信息:

error connecting to database using: scott/lion
ORA-01017: invalid username/password; logon denied
EXPLAIN PLAN option disabled

table:table参数只能与explain参数一起使用.它的目的是指定explain plan语句使用哪一个表来生成执行计划.
通常情况下可以不用指定它因为tkprof会自动在用户方案中创建一个名为prof$plan_table的表用于分析并最终
将其删除.在任何情况下如果用户不能创建表(例如因为create table权限丢失)那么就必须指定table参数.例如
为了指定system用户使用的plan_table表,那么这个参数必须指定为table=system.plan_table.执行分析的用户
必须对指定的表有select,insert和delete权限.同样地,在这种情况下错误也中出现在输出文件中.

注意:在oracle10gr1中因为bug3451410,tkprof不能自动创建计划表.因此会在输出文件记录ora-00922错误信息.

print:用来限制写入输出文件中的sql语句的数量.缺省值是没有限制的.它与sort参数一起使用才有意义.例如,
只获取10个sql语句,可以设置为print=10

aggregate:指示tkprof是否要对相同的语句分别处理.缺省情况不是这样的,换句话说,属于一个特定sql语句的
所有信息会被聚合在一起.注意这样做是不依赖于跟踪文件中出现的sql语句的数量的.就是在使用任何聚合的
情况下也有信息丢失.在这种情况下,一个游标有多个同执行计划的子游标的语句将会作为单个sql语句来处理.
尽管在大多数情况下缺省值已经够用了,但有些时候最好指定aggregate=no这样就可以单独的看到每一个语句.

insert:指示tkprof生成一个sql脚本在数据库中来存储所有的信息.这个脚本的名字可以通过参数来指定,如
insert=load.sql

sys:指定被写到输出文件中的sql语句是否由用户sys来执行(例如,在解析操作时对数据字典的解析调用).这个
缺省值是yes.但大多数时候我更喜欢设置它为no来避免在输出文件中写入不必要的信息.这是没有必要的,因为
你通常是不能通过sys用户来控制递归调用的sql语句的执行的.

record:指示tkprof生成一个sql脚本包含在跟踪文件中出现的所有不是递归的sql语句.这个脚本的名字是通过
record参数来指定的(例如record=replay.sql).根据文档,这个功能可以被用来手动重演sql语句.因为绑定变量
不能处理所以这通常是不可能的.

waits:决定是否等待事件信息将被写入到输出文件中.缺省值是写入,个人认为没有理由指定waits=no.如果指定
为no将不会有重要的等待事件信息写入到输出文件中.

sort:指定sql语句写入到输出文件中的顺序.缺省的顺序是在跟踪文件中找到的顺序.基本上通过指定一个推荐的
选项,你能根据资源利用情况来排序(例如,调用的次数,cpu时间和物理读取次数)或响应时间(指运行时间).你大
多数看到的选项(例如运行时间),每一种可用的数据库调用类型的值:例如,prsela解析游标所花的时间,exeela
执行游标所花的时间和fchela从游标中获取行记录所花的时间.尽管你有许多选项和选项组合,这里只有一种排序
顺序对于调查性能问题是有用的:响应时间.因此,你应该指定sort=prsela,exeela,fchela.当你指定一个以逗号
分隔的值时,tkprof会将这些值作为参数来传递.即使这些参数可能互不相容也是这样.注意当一个跟踪文件包含
多个会话且参数aggregate=no时,对于每个会话的sql语句将会单独排序.

基于前面的信息个人通常执行tkprof使用的参数如下:
tkprof {input trace file} {output file} sys=no sort=prsela,exeela,fchela

现在已经知道怎么样使用tkprof工具来分析跟踪文件了.下面来让我们来看看输出文件的内容.

解释tkprof的输出内容
分析是通过指定以下参数来完成的.注意
tkprof DBM11106_ora_9813.trc DBM11106_ora_9813.txt
sort=prsela,exeela,fchela print=3 explain=sh/sh aggregate=no

这个输出文件是从一个文件头开始的.大部分的信息是静态的.然则这在些信息中有非常有用的信息:跟踪文件名,
用于生成输出文件而使用的排序参数(sort)和用于标识跟踪会话的行.最后有用的信息仅当参数aggregate=no被
指定,这个头在两个属于不同会话的语句之间会重复出现和使用

TKPROF: Release 11.1.0.6.0 - Production on Tue Feb 29 10:37:08 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Trace file: DBM11106_ora_6334.trc
Sort options: prsela exeela fchela
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
--------------------------------------------------------------------------------
*** SESSION ID:(90.6) 2008-02-29 07:43:11.461

在这个头信息之后当连接到数据库或生成执行计划时可能会出现错误.

在头信息之后,接下来是每一个sql语句的信息:sql语句的文本信息,执行统计,解析信息,执行计划和等待事件.
执行计划和等待事件只有这些信息被存储在跟踪文件中才是可选的和可报告的.记住,只当游标被关闭执行计划
才会被存储而且只有当跟踪等待事件被启用等待事件信息才会被存储在跟踪文件中.

在有些情况下sql语句被格式化.不幸地是,格式化操作不能对所有情况提供正确的格式.对于实例来说,在使用
带有关键字from的extract函数与sql语句中的from子句混淆的情况.

SQL ID : g4h8jndhd8vst
SELECT CUST_ID, EXTRACT(YEAR
FROM
TIME_ID), SUM(AMOUNT_SOLD) FROM SH.SALES WHERE CHANNEL_ID = :B1 GROUP BY
CUST_ID, EXTRACT(YEAR FROM TIME_ID)

执行统计信息提供数据,聚合数据库调用.对于这些信息有以下的性能指标
count:数据库调用被执行的次数
cpu:花在处理数据库调用上的cpu时间以秒为单位
elapsed:是总的运行时间以秒为单位.花费在处理数据库调用上的总时间.如果这个值比cpu时间高,那么会在执行
统计信息下面找到关于资源或同步相关的等待事件信息

disk:物理读取的块数,注意,这不是物理I/O操作的总数.物理I/O操作的总数在等待事件部分有详细信息.如果这个
值比逻读取的块数高(disk>query+current),这意味着有些块被溢出到临时表空间中了

query:是指在一致性模式下从数据库缓冲区缓存中进行逻辑读取数据块的次数.通常这种类型的逻辑读是指查询.

current:是指在一致性模式下从数据库缓冲区缓存中进行逻辑读取数据块的次数,通常这种类型的逻辑读是指
insert,delete,merge和update语句.

rows:是指被处理的行数.对于查询来说,它是指获取的行数,对于insert,delete,merge和update语句来说.它是指
受影响的行数.从下面的输出中获取了16348行记录.这意味着平均每一次调用获取100行.

call    count  cpu       elapsed   disk       query      current    rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1     0.00       0.00          0          0          0          0
Execute      1     0.04       0.19          0          0          0          0
Fetch      164     1.09       4.50       2590       1720          0      16348
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total      166     1.13       4.70       2590       1720          0      16348

下面的行是对解析操作基本信息的一个汇总.前两个值(Misses in library cache)提供了在解析和执行调用期间
硬解析的次数.如果在执行调用期间没有发生硬解析,那么这年特定的行信息会丢失.优化器的模式和谁执行的解析
用户会被显示出来.注意这个用户的名字ops$cha,这只有在指不定期explain参数时才会被提供否则只会有用户id
最后片段信息是指递归调用的深度.它只对递归sql语句提供.通过应用程序直接执行sql语句的这个递归深度为0.
一个深度为n(现在这个例子为1)简单地意味着会执行n-1次其它的语句来完成这个语句的调用.

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 33 (OPS$CHA) (recursive depth: 1)

在解析信息之后,你可能还会看到执行计划信息.实际上如果参数explain被指定,那么可能会看到两次执行计划的信息.
第一次是被不准确地叫作行资源操作.当跟踪被激活游标被关闭时执行计划才会被写入到跟踪文件中.这意味着如果
一个应用程序重复使用游标而没有关闭,那么对于这个重复使用的游标的执行计划将不会写入到跟踪文件中.第二次是
叫作执行计划且只有当参数explain被指定时由tkprof生成的.因此它是后生成的所以也是不是必须的.

执行计划通过执行计划中每一个操作所提供的行数(不是处理的行数),在上面的例子中,表sales有918843行记录,而跟踪
文件只有540328行在通过where子句过滤后被返回.在通过group by子句之后减少到只有16348行.
对于每一个行资源操作,可能会提供以下运行时统计信息

cr:在一致性模式上逻辑读取的数据块的数量
pr:从磁盘中进行物理读取的数据块的数量
pw:使用物理写到磁盘中的数据块的数量
time:是处理操作总的运行时间以微秒为单位.注意这个值是静态被提供的所以不是很准确.事实上为了减小开销可以对它
进行抽样.
cost:评估操作的成本.这个值只能在11g中使用
size:评估操作返回的数据量大小.这个值只能在11g中使用
card:评估操作所返回的行数这个值只能在11g中使用
注意这些值除了card其它都是累计值.它们包括子行源操作.

Rows Row Source Operation
------ ---------------------------------------------------
16348 HASH GROUP BY (cr=1720 pr=2590 pw=2590 time=79 us cost=9990 size=11217129
card=534149)
540328 PARTITION RANGE ALL PARTITION: 1 28 (cr=1720 pr=1649 pw=1649 time=7744 us
cost=496 size=11217129 card=534149)
540328 TABLE ACCESS FULL SALES PARTITION: 1 28 (cr=1720 pr=1649 pw=1649
time=4756 us cost=496
size=11217129 card=534149)
Rows Execution Plan
------ ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
16348 HASH (GROUP BY)
540328 PARTITION RANGE (ALL) PARTITION: START=1 STOP=28
540328 TABLE ACCESS MODE: ANALYZED (FULL) OF 'SALES' (TABLE)
PARTITION: START=1 STOP=28

下面的部分是对sql语句所等待的等待事件信息的汇总:
times:一个等待事件已经等待的时间
Max.Wait:单个等待事件最等待时间最长的以秒为单位
Total Waited:一个等待事件总的等待时间.理论上所有等待事件的等待时间总和应该等待通执行统计所统计的运行时间
与cpu时间之差,其实也可以叫做未被计算的时间.

未被计算的时间
sql跟踪提供了数据库处理每一个操作所花的时间信息.理论上计算应该非常精确.不幸地是找到每个跟踪文件中的每个几分
这一秒这是很罕见的.每当真实的运行时间和在跟踪文件中所计算的时间有差异说明就存有没有被计算的时间存在.
unaccounted-for time = real elapsed time – accounted for time

存在没有被计算的时间有以下原因:
最明显的原因是因为缺少时间信息或等待事件.前者是因为timed_statistics参数被设置为false.后都可能使用的级别为1
或4的sql跟踪.在这两种情况下总是存在没有被计算的时间信息.当你正确使用扩展sql跟踪将会帮你避免这些问题.

一般来说,一个进程可能处在三种状态中:在一个cpu上运行,等待满足要求的设备或在运行队列中等待CPU.工具代码能够
计算前面两个状态的所花费的时间,但对在运行队列中等待所花的时间却一直没有办法计算.因此在cpu资源匮乏的情况下,
总是存在有些时间没有被计算这些时间可能很长.基本上你有两种方法来避免这个问题:要么增加可用cpu时间的数量要么
降低cpu利用率

通过测量工具来执行时间检查是精确的,然而在每一个检查中因为在计算机系统中实现计时器会存一些小的误差.特别是当
检测事件非常短的情况,这些量化的误差可能导致显著的没有被计算的时间存在.这些是自然而然存在的,识差可能会导致
没有被计算时间的值有可能是正数或负数.不幸地是你对它们无能为力.

如果你能消除上面的三个原因那么问题可能是因为检测代码没有对整个个代码进行计算.例如,写跟踪文件本身是没有被计算的
这通常不是问题.如果跟踪文件被写到一个低性能的设备上或都生成的跟踪信息很多它可能导致大量的开销.在这种情况下没有
被计算在内的时间值肯定是正数.为了避免这个问题,你应该简单将跟踪文件写到一个能维持必要吞吐量的设备上,在大多数情况
下你可以强制将跟踪文件写到裸设备上.

由于这些值是高度聚合的,它们将帮助你了解你正在等待什么类型的资源.例如,根据以下信息,几乎整个等待时间都花在物理读
上面了.事实上,db file sequential read等待事件与单块读有关,db file scattered read等待事件与多块读有关.另外
direct path write temp和direct path read temp等待事件与临时表空间有关.注意这里等待次数是941,完全与之前的
hash group by操作的物理写次数相同.

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 32 0.02 0.13
db file scattered read 225 0.04 1.76
direct path write temp 941 0.04 0.40
direct path read temp 941 0.03 1.00

在分析等待事件关键是要知道它们与什么操作相关.幸运地是尽管有上百种等待类型,但最常用的只有少数一些.你能在
oracle database reference手册的索引中找到它们.

接下来继续分析sql语句.

SQL>alter session set events '10046 trace name context forever,level 12';

Session altered.

SQL> DECLARE
2 l_channel_id sh.sales.channel_id%TYPE := 3;
3 BEGIN
4 FOR c IN (SELECT cust_id, extract(YEAR FROM time_id), sum(amount_sold)
5 FROM sh.sales
6 WHERE channel_id = l_channel_id
7 GROUP BY cust_id, extract(YEAR FROM time_id))
8 LOOP
9 NULL;
10 END LOOP;
11 END;
12 /

PL/SQL procedure successfully completed.

SQL>alter session set events '10046 trace name context off';

Session altered.

SQL>select name from v$diag_info where name='Default Trace File';

NAME
----------------------------------------------------------------
Default Trace File

SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_2803.trc
SQL> SELECT value FROM v$diag_info WHERE name = 'Default Trace File';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_2803.trc

[oracle@jingyong trace]$ tkprof jingyong_ora_2803.trc jingyong_ora_2803.txt explain=sys/zzh_2046 sort=prsela,exeela,fchela

pl/sql调用的执行统计是受限的.没有关于物理和逻辑读取可用的信息.这是因为资源是被递归调用的sql语句消耗的.
它们与父语句不相关.这意味着对于每一个sql语句来说你将只能看到sql语句本身的资源使用.

call    count  cpu      elapsed    disk       query      current    rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        2     0.04       0.10          0          0          0          0
Execute      2     0.00       0.01          0          0          0          1
Fetch        0     0.00       0.00          0          0          0          0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        4     0.05       0.11          0          0          0          1

因为pl/sql块不通过数据库递归执行,递归深度没有显示(因为递归深度为0).没有执行计划可用

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54 (OPS$CHA)

数据库等待SQL*Net message to client指示网络层发送数据到客户端(注意,发送数据跨越网络不被包括在这里面)
等待事件SQL*Net message from client指示从客户端等待数据

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 8.89 13.58

optimizer_index_cost_adj参数的作用

optimizer_index_cost_adj
这个初始化参数optimizer_index_cost_adj用于改变通过索引扫描来访问表数据的成本.参数的有效值从1到10000.
缺省值是100.如果这个参数的值大于100那么使用索引扫描的成本更昂贵因而会选择执行全表扫描.如果这个参数值
小于100那么使用索引扫描的成本更低.

为了理解这个参数对成本计算公式的影响.下面将说明一下关于索引范围扫描的成本计算公式

索引范围扫描有几个关键步骤:
1.访问索引的根块
2.通过分支块来定位包含第一个键的叶子块
3.对于满足搜索条件的每一个索引健进行以下操作;
抽取rowid引用的数据块
通过rowid访问数据块.

一个索引范围扫描所有执行的物理读取的次数等于定位包含第一个键的叶子块所要访问的分支块的个数(命名为blevel)
加上要扫描的叶子块的个数(命名为leaf_blocks)乘以操作的选择性,加上通过rowid要访问的数据块的个数
(命名为clustering_factor)乘以操作的选择性.另外还有考虑初始化参数optimizer_index_cost_adj的影响
计算公式大致如下:

io_cost=(blevel+(leaf_blocks+clustering_factor)*selectivity)*(optimizer_index_cost_adj/100)

下面进行测试(查询语句为select * from test where object_id<200)

create table test as select * from dba_objects;

create index idx_object_id on test(object_id);

analyze table test compute statistics;

SQL> select LEAF_BLOCKS,BLEVEL ,clustering_factor from user_indexes where index_name='IDX_OBJECT_ID';

LEAF_BLOCKS     BLEVEL CLUSTERING_FACTOR
----------- ---------- -----------------
        161          1              1665

SQL> select low_value,high_value from user_tab_col_statistics where table_name='TEST' and   column_name='OBJECT_ID';

LOW_VALUE                                                        HIGH_VALUE
---------------------------------------------------------------- ----------------------------------------------- -----------------
C103                                                             C3083632

SQL>
SQL> select utl_raw.cast_to_number('C3083632') high_value from dual;

HIGH_VALUE
----------
     75349
SQL> select utl_raw.cast_to_number('C103') low_value from dual;

 LOW_VALUE
----------
         2

其实列的最大值与最小值可以直接查询

SQL>select min(object_id),max(object_id) from test;

MIN(OBJECT_ID) MAX(OBJECT_ID)
-------------- --------------
2              75349
计算选择性
<谓词的选择性=(limit- low_value)/(high_vlaue-low_value)
limit就是查询条件的值
SQL>select round((200-2)/(75349-2),5) selectivity from dual;

SELECTIVITY
-----------
0.00263

因为io_cost的计算方法如下:

io_cost=(blevel+(leaf_blocks+clustering_factor)*selectivity)*(optimizer_index_cost_adj/100)
SQL> show parameter optimizer_index_cost_adj

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_cost_adj integer 100
optimizer_index_cost_adj=100
blevel=1;
leaf_blocks=161
clustering_factor=1665
selecttivity=0.00263

SQL>select (1+ceil(161*(round((200-2)/(75349-2),5)))+ceil(1665*(round((200-2)/(75349-2),5))))*(100/100) cost
from dual;

COST
----------
7

SQL>conn jy/jy@jy_201
已连接。
SQL>set autotrace trace explain
SQL> select  * from test where object_id<200;

执行计划
----------------------------------------------------------
Plan hash value: 985375477

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

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |               |   191 | 19100 |     7   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST          |   191 | 19100 |     7   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_OBJECT_ID |   191 |       |     2   (0)| 00:00:01 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"<200)

可以看到总的成本也和我们计算出来的一样也是7
当把optimizer_index_cost_adj设置为50时

SQL>alter session set optimizer_index_cost_adj=50;

Session altered.

SQL> show parameter optimizer_index_cost_adj;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_cost_adj integer 50

optimizer_index_cost_adj=50
blevel=1;
leaf_blocks=161
clustering_factor=1665
selecttivity=0.00263

SQL>select (1+ceil(161*(round((200-2)/(75349-2),5)))+ceil(1665*(round((200-2)/(75349-2),5))))*(50/100) cost from
dual;

COST
----------
3.5

我们计算出来是3.5四舍五入就是4与下面oracle计算的是一样

SQL> set autotrace trace explain
SQL> select  * from test where object_id<200;

Execution Plan
----------------------------------------------------------
Plan hash value: 985375477

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

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |               |   191 | 19100 |     4   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST          |   191 | 19100 |     4   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_OBJECT_ID |   191 |       |     1   (0)| 00:00:01 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"<200)

初始化参数optimizer_index_cost_adj会直接影响索引访问的成本.当这个参数设置的值比缺省值小时,
所有成本按比例减小.在有些情况下可能出现问题因为查询优化器对评估结果做了round操作.这意味着即使
一些索引的对象统计不同,它们可能会有相同的成本.如果一些成本是相同的,那么查询优化器将会根据索引
的名字来选择使用的索引.也就是按索引的第一个字母进行排序.这个问题用一个例子来说明.当改变初始化
参数optimizer_index_cost_adj后索引范围扫描操作选择索引的变化.

drop table test purge;

SQL> create table test
  2  as
  3  select rownum as id,
  4         round(dbms_random.normal*10000) as val1,
  5         100+round(ln(rownum/3.25+2)) as val2,
  6         100+round(ln(rownum/3.25+2)) as val3,
  7         dbms_random.string('p',250) as pad
  8  from all_objects
  9  where rownum< =1000
 10  order by dbms_random.value;

SQL> create index idx_val2 on test (val2);

Index created.

Elapsed: 00:00:00.18
SQL> create index idx_val3 on test(val3);

Index created.

Elapsed: 00:00:00.09

SQL> show parameter optimizer_index_cost_adj

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_cost_adj             integer     100

SQL> SELECT * FROM test WHERE val2 = 111 AND val3 = 111;
------------------------------------------------
| Id | Operation                   | Name     |
------------------------------------------------
|  0 | SELECT STATEMENT            |          |
|* 1 |  TABLE ACCESS BY INDEX ROWID| TEST     |
|* 2 |   INDEX RANGE SCAN          | IDX_VAL3 |
------------------------------------------------
1 - filter("VAL2"=11)
2 - access("VAL3"=11)

SQL> ALTER SESSION SET optimizer_index_cost_adj = 10;
SQL> SELECT * FROM test WHERE val1 = 111 AND val2 = 111;
------------------------------------------------
| Id | Operation                   | Name    |
------------------------------------------------
|  0 | SELECT STATEMENT            |         |
|* 1 |  TABLE ACCESS BY INDEX ROWID| TEST    |
|* 2 |   INDEX RANGE SCAN          | IDX_VAL2|
------------------------------------------------
1 - filter("VAL3"=111)
2 - access("VAL2"=111)

为了避免这种不稳定性,建议不要设置optimizer_index_cost_adj为一个很小的值.该参数是一个动态参数
可以在实例及会话级别进行修改.

Proudly powered by WordPress | Indrajeet by Sus Hill.