细说tkprof的使用方法 - 数据库编程

----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 41 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TAB1 | 1 | 41 | 1 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IND1 | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------ Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- --------------------------------------------------- 2 - access("OBJECT_ID"=1000) Note ----- - dynamic sampling used for this statement (level=2) 已选择18行。 SQL> alter session set sql_trace=true; 会话已更改。 SQL> alter session set tracefile_identifier='test1'; 会话已更改。 SQL> select object_id,object_name,object_type from tab1 where object_id = 1000; 未选定行
跟踪文件：

********************************************************************************

SQL ID: 25kr54b04dq4v Plan Hash: 2086140937

select object_id,object_name,object_type 
from
 tab1 where object_id = 1000


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          2          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          4          0           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 83  
Number of plan statistics captured: 1

--Rows (1st) Rows (avg) Rows (max)  Row Source Operation
------------ ---------- ----------  ---------------------------------------------------
--         0          0          0  TABLE ACCESS BY INDEX ROWID TAB1 (cr=2 pr=0 pw=0 time=20 us cost=1 size=41 card=1)
--         0          0          0   INDEX RANGE SCAN IND1 (cr=2 pr=0 pw=0 time=16 us cost=1 size=0 card=1)(object id 101626)

********************************************************************************

如果游标没有关闭，则跟踪文件中不会包含该游标的执行计划信息。

SQL> Declare
--Cursor C Is Select Object_Id,Object_Name,Object_Type From Tab1 Where Object_Id = 1111;
Object_Id Number;
Object_Name Varchar2(30);
Object_Type Varchar2(19);
Begin
Open  C;
Fetch C Into Object_Id,Object_Name,Object_Type;
Dbms_Lock.Sleep(1000);
Close C;
 11  End;

********************************************************************************

SQL ID: dxz49cn75rxtz Plan Hash: 2086140937

SELECT OBJECT_ID,OBJECT_NAME,OBJECT_TYPE 
FROM
 TAB1 WHERE OBJECT_ID = 1111


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          5          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 83     (recursive depth: 1)

********************************************************************************

如果之前sql语句已经执行过，则会包含执行计划

Declare
--Cursor C Is Select Object_Id,Object_Name,Object_Type From Tab1 Where Object_Id = 1000;
Object_Id Number;
Object_Name Varchar2(30);
Object_Type Varchar2(19);
Begin
Open  C;
Fetch C Into Object_Id,Object_Name,Object

细说tkprof的使用方法(二)