{"id":376,"date":"2016-06-14T10:37:29","date_gmt":"2016-06-14T08:37:29","guid":{"rendered":"http:\/\/jal-bs.de\/blog\/?p=376"},"modified":"2016-06-14T10:37:29","modified_gmt":"2016-06-14T08:37:29","slug":"oracle-12c-optimizer-trace","status":"publish","type":"post","link":"https:\/\/jal-bs.de\/blog\/?p=376","title":{"rendered":"Oracle 12c optimizer trace"},"content":{"rendered":"

In 12c kann wie folgt ein Optimizer Trace gezogen werden:<\/h3>\n

\n

show parameter user_dump_dest<\/em><\/h5>\n

alter session set tracefile_identifier=’Josef_trace‘;<\/em><\/h5>\n
alter session set max_dump_file_size = unlimited;<\/em><\/h5>\n
alter session set events ‚trace[rdbms.SQL_Optimizer.*]‘;<\/em><\/h5>\n
–> SQL-Statement<\/em><\/h5>\n
alter session set events ‚trace[rdbms.SQL_Optimizer.*] off‘;<\/em><\/h5>\n

Ergebnis:<\/em><\/h3>\n
[oracle@vmware3 trace]$ pwd<\/em>
\n\/u01\/oracle\/diag\/rdbms\/ora121c\/ORA121C\/trace<\/em>
\n[oracle@vmware3 trace]$ ls -al *Josef*<\/em>
\n-rw-r—–. 1 oracle oinstall 96979 14. Jun 10:15 ORA121C_ora_3186_Josef_trace.trc<\/em>
\n-rw-r—–. 1 oracle oinstall 37570 14. Jun 10:15 ORA121C_ora_3186_Josef_trace.trm<\/em><\/h5>\n

Eine weitere Methode besteht darin mit bekannter SQL_ID den Trace zu erzeugen mit „DBMS_SQLDIAG.DUMP_TRACE“.<\/h3>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

In 12c kann wie folgt ein Optimizer Trace gezogen werden: show parameter user_dump_dest alter session set tracefile_identifier=’Josef_trace‘; alter session set max_dump_file_size = unlimited; alter session set events ‚trace[rdbms.SQL_Optimizer.*]‘; –> SQL-Statement alter session set events ‚trace[rdbms.SQL_Optimizer.*] off‘; Ergebnis: [oracle@vmware3 trace]$ pwd … Weiterlesen →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[],"_links":{"self":[{"href":"https:\/\/jal-bs.de\/blog\/index.php?rest_route=\/wp\/v2\/posts\/376"}],"collection":[{"href":"https:\/\/jal-bs.de\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jal-bs.de\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jal-bs.de\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jal-bs.de\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=376"}],"version-history":[{"count":3,"href":"https:\/\/jal-bs.de\/blog\/index.php?rest_route=\/wp\/v2\/posts\/376\/revisions"}],"predecessor-version":[{"id":379,"href":"https:\/\/jal-bs.de\/blog\/index.php?rest_route=\/wp\/v2\/posts\/376\/revisions\/379"}],"wp:attachment":[{"href":"https:\/\/jal-bs.de\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=376"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jal-bs.de\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=376"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jal-bs.de\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=376"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}