{"id":721,"date":"2013-02-22T15:47:44","date_gmt":"2013-02-22T07:47:44","guid":{"rendered":"http:\/\/www.yallonking.com\/?p=721"},"modified":"2013-02-22T17:40:37","modified_gmt":"2013-02-22T09:40:37","slug":"scripts%e6%9f%a5%e7%9c%8b%e7%94%a8%e6%88%b7%e7%9a%84trace%e6%96%87%e4%bb%b6%e4%bd%8d%e7%bd%ae","status":"publish","type":"post","link":"http:\/\/www.yallonking.com\/?p=721","title":{"rendered":"scripts:\u67e5\u770b\u7528\u6237\u7684trace\u6587\u4ef6\u4f4d\u7f6e"},"content":{"rendered":"<p>\u4ee5\u4e0b\u4ecb\u7ecd2\u4e2d\u67e5\u770b\u5f53\u524d\u7528\u6237\u7684trace\u6587\u4ef6\u7684\u4f4d\u7f6e\u7684\u65b9\u6cd5<br \/>\n<strong>\u65b9\u6cd51\uff1a\u6807\u8bb0\u7528\u6237trace\u6587\u4ef6<\/strong><\/p>\n<pre class=\"wp-code-highlight prettyprint\">\r\nSQL&gt; alter session set tracefile_identifier=&#039;yallonking&#039;;\r\n\r\nSession altered.\r\n\r\nSQL&gt; set line 200   \r\n<\/pre>\n<p>\u6253\u5f00\u7528\u6237\u7684sql_trace\u5e76\u6536\u96c6sql\u6267\u884c\u7684\u4fe1\u606f\u540e\u5173\u95edsql_trace<\/p>\n<pre class=\"wp-code-highlight prettyprint\">\r\nSQL&gt; alter session set sql_trace=true;\r\n\r\nSession altered.\r\n\r\nSQL&gt; select * from scott.emp e where e.deptno in (select deptno from scott.dept) and rownum&lt;3;\r\n\r\n     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO\r\n---------- ---------- --------- ---------- --------- ---------- ---------- ----------\r\n      7369 SMITH      CLERK           7902 17-DEC-80        800                    20\r\n      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30\r\n\r\nSQL&gt; alter session set sql_trace=false;\r\n\r\nSession altered.\r\n<\/pre>\n<p>\u67e5\u627e\u7528\u6237trace\u6587\u4ef6<\/p>\n<pre class=\"wp-code-highlight prettyprint\">\r\nSQL&gt; !ls -ltr \/u01\/app\/oracle\/admin\/ora10gr2\/udump\/*yallonking*\r\n-rw-r----- 1 oracle oinstall   70 Feb 21 23:15 \/u01\/app\/oracle\/admin\/ora10gr2\/udump\/ora10gr2_ora_9540_yallonking.trc\r\n-rw-r----- 1 oracle oinstall 2003 Feb 21 23:16 \/u01\/app\/oracle\/admin\/ora10gr2\/udump\/ora10gr2_ora_9647_yallonking.trc\r\n-rw-r----- 1 oracle oinstall 2146 Feb 21 23:17 \/u01\/app\/oracle\/admin\/ora10gr2\/udump\/ora10gr2_ora_9655_yallonking.trc\r\n\r\n<\/pre>\n<p>\u67e5\u770b\u7528\u6237trace\u6587\u4ef6\u4e2d\u7684\u6267\u884c\u8ba1\u5212<\/p>\n<pre class=\"wp-code-highlight prettyprint\">\r\nSQL&gt; !vi \/u01\/app\/oracle\/admin\/ora10gr2\/udump\/ora10gr2_ora_9655_yallonking.trc\r\n<\/pre>\n<p><strong>\u65b9\u6cd52\uff1a\u4f7f\u7528sql\u67e5\u8be2\u7528\u6237trace\u6587\u4ef6<\/strong><\/p>\n<pre class=\"wp-code-highlight prettyprint\">\r\nSQL&gt; set line 200 \r\nSQL&gt; alter session set &quot;_optimizer_trace&quot;=all;\r\n\r\nSession altered.\r\n\r\nSQL&gt; select * from scott.emp e where e.deptno in (select deptno from scott.dept) and rownum&lt;4;\r\n\r\n     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO\r\n---------- ---------- --------- ---------- --------- ---------- ---------- ----------\r\n      7369 SMITH      CLERK           7902 17-DEC-80        800                    20\r\n      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30\r\n      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30\r\n\r\nSQL&gt; alter session set &quot;_optimizer_trace&quot;=none;\r\n\r\nSession altered.\r\n\r\n<\/pre>\n<p>\u67e5\u770b\u5f53\u524d\u4f1a\u8bdd\u7684trace\u6587\u4ef6\u4f4d\u7f6e<\/p>\n<pre class=\"wp-code-highlight prettyprint\">\r\nSQL&gt; SELECT a.VALUE\r\n  2  || b.symbol\r\n  3  || c.instance_name\r\n  4  || &#039;_ora_&#039;\r\n  5  || d.spid\r\n  6  || &#039;.trc&#039; trace_file\r\n  7  FROM (SELECT VALUE\r\n  8  FROM v$parameter\r\n  9  WHERE NAME = &#039;user_dump_dest&#039;) a,\r\n 10  (SELECT SUBSTR (VALUE, -6, 1) symbol\r\n 11  FROM v$parameter\r\n 12  WHERE NAME = &#039;user_dump_dest&#039;) b,\r\n 13  (SELECT instance_name\r\n 14  FROM v$instance) c,\r\n 15  (SELECT spid\r\n 16  FROM v$session s, v$process p, v$mystat m\r\n 17  WHERE s.paddr = p.addr AND s.SID = m.SID AND m.statistic# = 0) d\r\n 18  \/\r\n\r\nTRACE_FILE\r\n--------------------------------------------------------------------------------------------------------------------------------\r\n\/u01\/app\/oracle\/admin\/ora10gr2\/udump\/ora10gr2_ora_9759.trc\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>\u4ee5\u4e0b\u4ecb\u7ecd2\u4e2d\u67e5\u770b\u5f53\u524d\u7528\u6237\u7684trace\u6587\u4ef6\u7684\u4f4d\u7f6e\u7684\u65b9\u6cd5 \u65b9\u6cd51\uff1a\u6807\u8bb0\u7528\u6237trace\u6587\u4ef6 SQL&gt; alte [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[9],"tags":[126,125],"_links":{"self":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts\/721"}],"collection":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=721"}],"version-history":[{"count":4,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts\/721\/revisions"}],"predecessor-version":[{"id":735,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts\/721\/revisions\/735"}],"wp:attachment":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=721"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=721"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=721"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}