查看tkprof生成文件：
$ cat /data/app/scott_trace.log -- 开头概要部分，基本信息说明，包括tkprof版本、trace文件路径、排序选项和报告参数说明 TKPROF: Release 11.2.0.4.0 - Development on Thu Jan 17 17:41:04 2019 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Trace file: /data/app/oracle/diag/rdbms/dbabd/dbabd/trace/dbabd_ora_18629_SCOTT.trc Sort options: 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 ******************************************************************************** -- 跟踪的SQL语句文本、SQL ID和执行计划HASH SQL ID: a2dk8bdn0ujx7 Plan Hash: 3956160932 select * from emp -- SQL执行的统计信息 /* call:调用类型 count:调用执行次数 cpu:需要的CPU时间(单位:秒) elapsed:需要消耗的时间(单位:秒) disk:发生物理读次数 query:发生一致读次数 current:发生当前读次数 rows:获取的行数 */ 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 2 0.00 0.00 0 7 0 14 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 7 0 14 Misses in library cache during parse: 1 -- 库缓存没有命中数，说明是硬解析 Optimizer mode: ALL_ROWS -- 优化器模式 Parsing user id: 83 (SCOTT) -- 解析用户 Number of plan statistics captured: 1 -- 执行计划统计信息获取数 -- SQL语句实际执行消耗的资源信息 /* Rows:操作实际返回记录数 Row Source Operation:当前行操作的访问方式 cr:一致性读的数据块，对应query的Fetch值 pr:物理读的数据块，对应disk的Fetch值 pw:物理写的数据块 time:执行时间 cost:优化器执行成本 size:处理的字节数 card:处理的记录数 */ Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 14 14 14 TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0 time=211 us cost=3 size=532 card=14) -- 指定explain选项输出的执行计划 Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT MODE: ALL_ROWS 14 TABLE ACCESS MODE: ANALYZED (FULL) OF 'EMP' (TABLE) -- 指定选项waits=yes选项输出的等待事件信息统计 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 128.00 128.00 ******************************************************************************** SQL ID: bnqn0qyvy59qf Plan Hash: 0 BEGIN dbms_session.session_trace_enable(true, true, 'all_executions'); END; call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 0 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 61 0 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 1 0.00 0.00 0 61 0 1 Misses in library cache during parse: 0 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 83 (SCOTT) 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 68.47 68.47 ******************************************************************************** SQL ID: 23d3sap7cask4 Plan Hash: 0 BEGIN dbms_session.session_trace_disable(); END; 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 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 0 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 83 (SCOTT) ******************************************************************************** -- 开启跟踪期间所有非递归SQL语句执行信息统计汇总 OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.01 0.01 0 0 0 0 Execute 3 0.00 0.00 0 61 0 2 Fetch 2 0.00 0.00 0 7 0 14 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 7 0.01 0.01 0 68 0 16 Misses in library cache during parse: 2 Misses in library cache during execute: 1 Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 3 0.00 0.00 SQL*Net message from client 3 128.00 196.48 -- 开启跟踪期间所有递归SQL语句执行信息统计汇总 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 0 0.00 0.00 0 0 0 0 Execute 23 0.00 0.00 0 0 0 0 Fetch 37 0.00 0.00 2 82 0 28 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 60 0.00 0.00 2 82 0 28 Misses in library cache during parse: 0 Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ db file sequential read 2 0.00 0.00 -- 所有分析的SQL语句汇总统计 3 user SQL statements in session. 13 internal SQL statements in session. 16 SQL statements in session. 1 statement EXPLAINed in this session. ******************************************************************************** Trace file: /data/app/oracle/diag/rdbms/dbabd/dbabd/trace/dbabd_ora_18629_SCOTT.trc Trace file compatibility: 11.1.0.7 Sort options: fchela 1 session in tracefile. 3 user SQL statements in trace file. 13 internal SQL statements in trace file. 16 SQL statements in trace file. 16 unique SQL statements in trace file. 1 SQL statements EXPLAINed using schema: SCOTT.prof$plan_table Default table was used. Table was created. Table was dropped. 411 lines in trace file. 196 elapsed seconds in trace file. 总结