1\u3001\u4ec0\u4e48\u662f10046\u4e8b\u4ef6<\/p>
? 10046\u4e8b\u4ef6\u662fOracle\u63d0\u4f9b\u7684\u4e00\u4e2a\u7528\u4e8e\u5206\u6790\u6027\u80fd\u7684\u5de5\u5177\uff0c\u5b83\u80fd\u5e2e\u52a9\u6211\u4eec\u89e3\u6790\u4e00\u6761\/\u591a\u6761SQL\u3001PL\/SQL\u8bed\u53e5\u7684\u8fd0\u884c\u72b6\u6001\uff0c\u8fd9\u4e9b\u72b6\u6001\u5305\u62ec \uff1aParse\/Fetch\/Execute\u4e09\u4e2a\u9636\u6bb5\u4e2d\u9047\u5230\u7684\u7b49\u5f85\u4e8b\u4ef6\u3001\u6d88\u8017\u7684\u7269\u7406\u548c\u903b\u8f91\u8bfb\u3001CPU\u65f6\u95f4\u3001\u6267\u884c\u8ba1\u5212\u7b49\u7b49\u3002<\/p>
2\u300110046\u4e8b\u4ef6\u7684Level<\/p>
\u4e0d\u540c\u7684Level\u5bf9\u5e94\u4e0d\u540c\u7684\u8ddf\u8e2a\u7ea7\u522b<\/p>
1 \u542f\u7528\u6807\u51c6\u7684SQL_TRACE\u529f\u80fd(\u9ed8\u8ba4)\u5305\u542b\u4e86SQL\u8bed\u53e5\u3001\u54cd\u5e94\u65f6\u95f4\u3001\u670d\u52a1\u65f6\u95f4\u3001\u5904\u7406\u7684\u884c\u6570\uff0c\u7269\u7406\u8bfb\u548c\u5199\u7684\u6570\u76ee\u3001\u6267\u884c\u8ba1\u5212\u4ee5\u53ca\u5176\u4ed6\u4e00\u4e9b\u989d\u5916\u4fe1\u606f\u3002\u5230\u7248\u672c10.2\u4e2d\u6267\u884c\u8ba1\u5212\u5199\u5165\u5230trace\u7684\u6761\u4ef6\u662f\u4ec5\u5f53\u76f8\u5173\u6e38\u6807\u5df2\u7ecf\u5173\u95ed\u65f6\uff0c\u4e14\u4e0e\u4e4b\u76f8\u5173\u7684\u6267\u884c\u7edf\u8ba1\u4fe1\u606f\u662f\u6240\u6709\u6267\u884c\u6b21\u6570\u7684\u603b\u548c\u6570\u636e\u3002\u5230\u7248\u672c11.1\u4e2d\u4ec5\u5728\u6bcf\u6b21\u6e38\u6807\u7684\u7b2c\u4e00\u6b21\u6267\u884c\u540e\u5c06\u6267\u884c\u8ba1\u5212\u5199\u5165\u5230trace\uff0c\u6267\u884c\u7edf\u8ba1\u4fe1\u606f\u4ec5\u4ec5\u548c\u8fd9\u7b2c\u4e00\u6b21\u6267\u884c\u76f8\u5173<\/p>
4 \u6bd4level 1\u65f6\u591a\u51fa\u7ed1\u5b9a\u53d8\u91cf\u7684trace<\/p>
8 \u6bd4level 1\u591a\u51fa\u7b49\u5f85\u4e8b\u4ef6\uff0c\u7279\u522b\u5bf9\u4e8e9i\u4e2d\u6307\u51falatchfree\u7b49\u5f85\u4e8b\u4ef6\u5f88\u6709\u7528\uff0c\u5bf9\u4e8e\u5206\u6790\u5168\u8868\u626b\u63cf\u548c\u7d22\u5f15\u626b\u63cf\u4e5f\u5f88\u6709\u7528<\/p>
12 \u6bd4level 1\u591a\u51fa\u7ed1\u5b9a\u53d8\u91cf\u548c\u7b49\u5f85\u4e8b\u4ef6<\/p>
11g\u53ca\u4ee5\u4e0a\u7248\u672c<\/p>
16 \u572811g\u4e2d\u4e3a\u6bcf\u4e00\u6b21\u6267\u884c\u751f\u6210STAT\u4fe1\u606f<\/p>
32 \u6bd4level 1\u5c11\u6267\u884c\u8ba1\u5212<\/p>
11.2.0.2\u53ca\u4ee5\u4e0a\u7248\u672c<\/p>
64 \u548clevel 1\u76f8\u6bd4\u5728\u7b2c\u4e00\u6b21\u6267\u884c\u540e\u8fd8\u53ef\u80fd\u751f\u6210\u6267\u884c\u8ba1\u5212\u4fe1\u606f\uff1b\u6761\u4ef6\u662f\u67d0\u4e2a\u6e38\u6807\u5728\u524d\u4e00\u6b21\u6267\u884c\u7684\u524d\u63d0\u4e0b\u8fd0\u884c\u8017\u65f6\u53d8\u957f\u4e86\u4e00\u5206\u949f\u3002<\/p>
3\u3001\u542f\u752810046\u4e8b\u4ef6<\/p>
1)\u5bf9\u672csession\u542f\u752810046\u4e8b\u4ef6<\/p>
a.<\/p>
?alter session set events '10046 trace name context forever,level 12'<\/p>
b.<\/p>
?oradebug setmypid<\/p>
?oradebug event 10046 trace name context ,level 12<\/p>
\u5176\u4e2d\u80fd\u4fee\u6539\u7684\u53ea\u6709level\u7ea7\u522b<\/p>
2)\u5bf9\u5176\u4ed6session\u542f\u752810046\u4e8b\u4ef6<\/p>
oradebug setospid|setorapid xxx<\/p>
oradebug event 10046 trace name context ,level 12<\/p>
4\u3001\u505c\u752810046\u4e8b\u4ef6<\/p>
\u5206\u522b\u5bf9\u5e94\u4e0a\u9762\u4e0d\u540c\u7684\u542f\u7528\u65b9\u5f0f<\/p>
alter session set events '10046 trace name context forever off'<\/p>
oradebug event 10046 trace name context off<\/p>
\u6216\u8005\u9000\u51fa\u542f\u752810046\u4e8b\u4ef6\u7684session<\/p>
5\u3001\u83b7\u53d610046\u4e8b\u4ef6\u751f\u6210\u7684trace\u6587\u4ef6<\/p>
a.\u5bf9\u4e8e11g\u53ca\u4ee5\u4e0a\u7684\u7248\u672c\uff0c\u4f7f\u7528\u5982\u4e0b\u8bed\u53e5\u53ef\u4ee5\u8f7b\u677e\u5f97\u5230<\/p>
select value from v$diag_info where name='Default Trace File';<\/p>
b.\u5bf9\u4e8e10g\u53ca\u4ee5\u524d\u7684\u7248\u672c\u4e2d\u9700\u8981\u4f7f\u7528\u5982\u4e0bsql<\/p>
SELECT D.VALUE || '' || LOWER(RTRIM(I.INSTANCE, CHR(0))) || '_ora_' ||
? ? ? P.SPID || '.trc' TRACE_FILE_NAME
? FROM (SELECT P.SPID
? ? ? ? ? FROM SYS.V$MYSTAT M, SYS.V$SESSION S, SYS.V$PROCESS P
? ? ? ? WHERE M.STATISTIC# = 1
? ? ? ? ? AND S.SID = M.SID
? ? ? ? ? AND P.ADDR = S.PADDR) P,
? ? ? (SELECT T.INSTANCE
? ? ? ? ? FROM SYS.V$THREAD T, SYS.V$PARAMETER V
? ? ? ? WHERE V.NAME = 'thread'
? ? ? ? ? AND (V.VALUE = 0 OR T.THREAD# = TO_NUMBER(V.VALUE))) I,
? ? ? (SELECT VALUE FROM SYS.V$PARAMETER WHERE NAME = 'user_dump_dest') D;<\/p>
c.\u5982\u679c\u4f7f\u7528oradebug\u547d\u4ee4\u5219\u4f7f\u7528\u76f8\u5bf9\u5e94\u7684oradebug tracefile_name\u5373\u53ef\u5f97\u5230trace\u6587\u4ef6<\/p>
6\u3001\u683c\u5f0f\u5316trace\u6587\u4ef6<\/p>
?10046\u4e8b\u4ef6\u6240\u4ea7\u751f\u7684\u539f\u59cbtrace\u6587\u4ef6\u4e60\u60ef\u79f0\u4e4b\u4e3a\u88f8trace\u6587\u4ef6(raw trace)\uff0cOracle\u8bb0\u5f55\u5728\u88f8trace\u6587\u4ef6\u4e2d\u7684\u5185\u5bb9\u4e00\u773c\u770b\u4e0a\u53bb\u5e76\u4e0d\u662f\u90a3\u4e48\u89c2\uff0c\u4e5f\u4e0d\u662f\u90a3\u4e48\u5bb9\u6613\u770b\u61c2\u3002\u4e3a\u4e86\u797ctrace\u6587\u4ef6\u80fd\u591f\u4ee5\u4e00\u79cd\u66f4\u76f4\u89c2\u3001\u66f4\u5bb9\u6613\u61c2\u7684\u65b9\u5f0f\u5c55\u73b0\u51fa\u6765\uff0cOracle\u63d0\u4f9b\u4e86tkprof\u547d\u4ee4\uff0c\u8fd9\u4e2a\u547d\u4ee4\u662fOracle\u81ea\u5e26\u7684\uff0c\u53ef\u4ee5\u7528\u5b83\u6765\u7ffb\u8bd1\u797ctrace\u6587\u4ef6\u3002<\/p>
tkprof\u7684\u8bed\u6cd5\u5982\u4e0b\uff1a<\/p>
[oracle@rhel6 10046]$ 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","orderid":"0","title":"Oracle 10046\u4e8b\u4ef6 \u4ecb\u7ecd(\u4e00)(\u4e00)","smalltitle":"","mid":"0","fname":"\u6570\u636e\u5e93\u7f16\u7a0b","special_id":"0","bak_id":"0","info":"0","hits":"675","pages":"3","comments":"0","posttime":"2017-02-21 08:16:00","list":"1487636160","username":"admin","author":"","copyfrom":"","copyfromurl":"","titlecolor":"","fonttype":"0","titleicon":"0","picurl":"https:\/\/www.cppentry.com\/upload_files\/","ispic":"0","yz":"1","yzer":"","yztime":"0","levels":"0","levelstime":"0","keywords":"Oracle<\/A> 10046<\/A> \u4e8b\u4ef6<\/A> \u4ecb\u7ecd<\/A>","jumpurl":"","iframeurl":"","style":"","template":"a:3:{s:4:\"head\";s:0:\"\";s:4:\"foot\";s:0:\"\";s:8:\"bencandy\";s:0:\"\";}","target":"0","ip":"14.17.22.31","lastfid":"0","money":"0","buyuser":"","passwd":"","allowdown":"","allowview":"","editer":"","edittime":"0","begintime":"0","endtime":"0","description":"Oracle 10046\u4e8b\u4ef6 \u4ecb\u7ecd(\u4e00)","lastview":"1714049294","digg_num":"9853","digg_time":"0","forbidcomment":"0","ifvote":"0","heart":"","htmlname":"","city_id":"0"},"page":"1"}