接上一笔记［terry笔记］Oracle会话追踪（一）：SQL_TRACE&EVENT 10046
http://www.cnblogs.com/kkterry/p/3279282.html
在系统下执行tkprof，效果是把trace文件转换成可供人类阅读的格式
（个人感觉不是很爽，trace文件输出的时候直接弄个好格式不就得了）
语法：
tkprof tracefile output_file
explain=username/passwd ##对trace文件中的sql语句产生执行计划，会在这个用户下产生plan_table，并在tkprof结束时删除。 sys=yes|no ##选no，用来屏蔽oracle内部递归sql waits=yes|no ##选yes，记录等待事件概要 aggregate=yes|no ##选yes，oracle会组合相同sql文本的多个用户 record=filename ##会生成一个保存所有sql语句的文件 print=number ##只显示前N条语句，一般和sort一起用 insert=filename ##生成一个脚本，记录所追踪的sql语句的insert sort=parameters ##输出的内容按照某项排序：按照实际执行时间排序fchela，物理读排序fchdsk
例子：
tkprof ora11g_ora_11111.trc scott.txt explain=scott/tiger sys=no waits=yes sort=fchela
tkprof的输出（分上下两部分）：
TKPROF: Release 11.2.0.3.0 - Development on Sat Aug 24 13:51:24 2013 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Trace file: ora11g_ora_5829.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 ID: 9kcpyjv574vjc Plan Hash: 3617692013 select * from t1 where owner='SYSTEM' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 1 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 40 0.00 0.01 1065 1106 0 573 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 42 0.00 0.01 1065 1107 0 573 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 86 (U1) Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 573 573 573 TABLE ACCESS FULL T1 (cr=1106 pr=1065 pw=0 time=4866 us cost=298 size=71622 card=346) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT MODE: ALL_ROWS 573 TABLE ACCESS (FULL) OF 'T1' (TABLE)
Trace file: ora11g_ora_5829.trc Trace file compatibility: 11.1.0.7 Sort options: fchela 1 session in tracefile. 5 user SQL statements in trace file. ##这里面共有5个sql 1 internal SQL statements in trace file. ##1个sql是oracle内部递归 6 SQL statements in trace file. 6 unique SQL statements in trace file. 1 SQL statements EXPLAINed using schema: U1.prof$plan_table Default table was used. Table was created. Table was dropped. 159 lines in trace file. 24 elapsed seconds in trace file.
这里解释下query和current
query就是select语句在内存中检索了多少块
current就是update、delete等语句在内存中修改了多少块
一些指标：
好的情况是以很少的fatch获取更多的rows。
query的parse指的是从数据字典(dictionary cache)中获取多少块。
主要以CPU列和elapsed列来衡量资源的消耗。
要消灭全表扫描。