TKPROF学习.docx

TKPROF学习Tkprof工具可用来格式化sql trace产生的文件，让你更容易看懂trace的内容 用法： tkprof tracefile outputfile [explain= ] [table= ] [print= ] [insert= ] [sys= ] [sort= ] ... 参数说明： tracefile：你要分析的trace文件 outputfile：格式化后的文件 explain=user/password@connectstring table=schema.tablename 注1：这两个参数是一起使用的，通过连接数据库对在trace文件中出现的每条sql语句查看执行计划，并将之输出到outputfile中 注2：该table必须是数据库中不存在的，如果存在会报错 print=n：只列出最初N个sql执行语句 insert=filename：会产生一个sql文件，运行此文件可将收集到的数据insert到数据库表中 sys=no：过滤掉由sys执行的语句 record=filename：可将非嵌套执行的sql语句过滤到指定的文件中去 waits=yes|no：是否统计任何等待事件 aggregate=yes|no：是否将相同sql语句的执行信息合计起来，默认为yes sort= option：设置排序选项，选项如下： prscnt：number of times parse was calledprscpu：cpu time parsingprsela：elapsed time parsingprsdsk：number of disk reads during parseprsqry：number of buffers for consistent read during parseprscu：number of buffers for current read during parseprsmis：number of misses in library cache during parseexecnt：number of execute was calledexecpu：cpu time spent executingexeela：elapsed time executingexedsk：number of disk reads during executeexeqry：number of buffers for consistent read during executeexecu：number of buffers for current read during executeexerow：number of rows processed during executeexemis：number of library cache misses during executefchcnt：number of times fetch was calledfchcpu：cpu time spent fetchingfchela：elapsed time fetchingfchdsk：number of disk reads during fetchfchqry：number of buffers for consistent read during fetchfchcu：number of buffers for current read during fetchfchrow：number of rows fetcheduserid：userid of user that parsed the cursor 可根据自己的需要设置排序 举例： 1.列出前2条sql语句的执行情况： C:\tkprof c:\oc_ora_2892.trc c:\cc.txt print=2 2.将数据保存到数据库： C:\tkprof c:\oc_ora_2892.trc c:\cc.txt insert=c:\insert.sql 执行后会在c:\产生insert.sql文件，执行该文件即可将数据保存到数据库，以下为insert.sql部分内容： REM Edit and/or remove the following CREATE TABLEREM statement as your needs dictate.CREATE TABLE tkprof_table(date_of_insert DATE,cursor_num NUMBER,depth NUMBER,user_id NUMBER,parse_cnt NUMBER,parse_cpu NUMBER,parse_elap NUMBER,parse_disk N