{"id":2408,"date":"2018-01-27T19:04:25","date_gmt":"2018-01-27T10:04:25","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=2408"},"modified":"2018-01-27T19:09:04","modified_gmt":"2018-01-27T10:09:04","slug":"%ed%8a%9c%eb%8b%9d-sqltrace-%eb%b0%a9%eb%b2%95","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=2408","title":{"rendered":"[\ud29c\ub2dd] SQL*Trace \ubc29\ubc95"},"content":{"rendered":"
<\/h5>\n

\ubaa8\ub4e0 \uc624\ub77c\ud074 \ubc84\uc804\uc5d0\uc11c \uac00\ub2a5\ud55c \ubc29\ubc95<\/h2>\n

\uc790\uae30 \uc138\uc158\uc5d0 \ub300\ud558\uc5ec<\/strong><\/p>\n

alter session set timed_statistics = true;<\/p>\n

alter session set max_dump_file_size = unlimited;<\/p>\n

alter session set sql_trace = TRUE;<\/p>\n

\ub610\ub294<\/p>\n

exec dbms_session.set_sql_trace(TRUE);<\/p>\n

\ub610\ub294<\/p>\n

alter session set events \u201810046 trace name context forever, level 8\u2019;<\/p>\n

— trace \ud560 SQL \uc2e4\ud589 —<\/p>\n

alter session set events \u201810046 trace name context off\u2019;<\/p>\n

 <\/p>\n

\ub2e4\ub978 \uc138\uc158\uc5d0 \ub300\ud558\uc5ec<\/strong><\/p>\n

alter system set timed_statistics = true;<\/p>\n

alter system set max_dump_file_size = unlimited;<\/p>\n

exec dbms_system.set_sql_trace_in_session(sid, serial#, true);<\/p>\n

— trace \ud560 SQL \uc2e4\ud589<\/p>\n

exec dbms_system.set_sql_trace_in_session(sid, serial#, false);<\/p>\n

 <\/p>\n

\uc624\ub80c\uc9c0 Session Monitor \uc5d0\uc11c \uc0ac\uc6a9\ud558\ub294 \ubc29\uc2dd<\/strong><\/p>\n

exec dbms_system.set_ev(sid, serial#, 10046, 8, \u2018\u2019); <= 8 Level SQL*Trace \uc124\uc815<\/p>\n

exec dbms_system.set_ev(sid, serial#, 10046, 0, \u2018\u2019); <= \ud574\uc81c<\/p>\n

 <\/p>\n

\uc624\ub77c\ud074 8.1.6 \uc774\uc0c1 \ubc84\uc804\uc5d0\uc11c \uac00\ub2a5\ud55c \ubc29\ubc95<\/h2>\n

\uc790\uae30 \uc138\uc158\uc5d0 \ub300\ud558\uc5ec (dbmssupp.sql \uc2e4\ud589\ud558\uc5ec dbms_support \ud328\ud0a4\uc9c0 \uc0dd\uc131)<\/strong><\/p>\n

alter session set timed_statistics = true;<\/p>\n

alter session set max_dump_file_size = unlimited;<\/p>\n

exec sys.dbms_support.start_trace;<\/p>\n

\ub610\ub294<\/p>\n

exec sys.dbms_support.start_trace(waits=>TRUE, binds=> TRUE)<\/p>\n

— trace \ud560 SQL\ubb38\uc7a5 \uc2e4\ud589 —<\/p>\n

exec sys.dbms_support.stop_trace;<\/p>\n

 <\/p>\n

\ub2e4\ub978 \uc138\uc158\uc5d0 \ub300\ud558\uc5ec<\/strong><\/p>\n

exec dbms_system.set_bool_param_in_session(sid, serial#, parnam=> \u2019TIMED_STATISTICS\u2019, bval => true);<\/p>\n

exec dbms_system.set_int_param_in_session(sid, serial#, parnam=> \u2018MAX_DUMP_FILE_SIZE, intval=>2147483647);<\/p>\n

exec sys.dbms_support.start_trace_in_session(sid, serial#);<\/p>\n

\ub610\ub294<\/p>\n

exec sys.dbms_support.start_trace_in_session(sid, serial#, waits=> true, binds=> true)<\/p>\n

— trace \ud560 SQL\ubb38\uc7a5 \uc2e4\ud589 —<\/p>\n

exec sys.dbms_support.stop_trace_in_session(sid, serial#);<\/p>\n

 <\/p>\n

\uc624\ub77c\ud074 10g \uc774\uc0c1 \ubc84\uc804\uc5d0\uc11c \uac00\ub2a5\ud55c \ubc29\ubc95<\/h2>\n

\uae30\uc874 \ubc29\uc2dd\ucc98\ub7fc sid, serial# \uc744 \uc774\uc6a9\ud558\uc5ec \uc138\uc158 \uae30\uc900\uc5d0 \uc758\ud55c trace \uc124\uc815<\/strong><\/p>\n

dbmsmntr.sql \uc744 \ud1b5\ud574 dbms_monitor \ud328\ud0a4\uc9c0 \uc0dd\uc131<\/p>\n

\ucd08\uae30\ud654 \ud30c\ub77c\ubbf8\ud130 \ud30c\uc77c\uc5d0\uc11c tracefile_identifier = \u2018test\u2019 <8.1.7 \uc774\uc0c1><\/p>\n

=> orcl_ora_560_test.trc<\/p>\n

exec dbms_monitor.session_trace_enable (session_id, serial_num, waits, binds);<\/p>\n

exec dbms_monitor.session_trace_disable(session_id, serial_num);<\/p>\n

service, action, module \uae30\uc900\uc5d0 \uc758\ud55c \ud2b8\ub808\uc774\uc2a4 \uc124\uc815<\/p>\n

exec dbms_monitor.serv_mod_act_trace_enable (service_name, module_name,action_name, waits=>true, binds=>true, instance_name => null);<\/p>\n

exec dbms_monitor.serv_mod_act_trace_disable (service_name, module_name,action_name);<\/p>\n

<\ucc38\uace0> exec dbms_application_info.set_module(\u2018module name\u2019, \u2018action name\u2019);<\/p>\n

<\ucc38\uace0> exec dbms_application_info.set_action(\u2018action name\u2019);<\/p>\n

 <\/p>\n

oradebug\ub97c \ud65c\uc6a9\ud55c \ubc29\ubc95<\/h2>\n

O\/S \ud504\ub85c\uc138\uc2a4 ID \ud639\uc740 \uc624\ub77c\ud074 \ud504\ub85c\uc138\uc2a4 ID \ud655\uc778<\/strong><\/p>\n

select s.username, p.spid as os_process_id, p.pid as oracle_process_id<\/p>\n

from v$session s, v$process p<\/p>\n

where s.paddr = p.addr<\/p>\n

and s.sid = :sid;<\/p>\n

MS-Windows \ud658\uacbd\uc5d0\uc11c\ub294 p.spid \ub294 \uc2a4\ub808\ub4dc id \uc774\ub2e4.<\/span><\/strong><\/p>\n

TRACE<\/p>\n

alter system set timed_statistics = true;<\/p>\n

oradebug setospid <os_process_id>;<\/p>\n

\ub610\ub294<\/p>\n

oradebug setmypid;<\/p>\n

oradebug unlimit;<\/p>\n

oradebug event 10046 trace name context forever, level 8;<\/p>\n

 <\/p>\n

— trace \ud560 SQL\ubb38\uc7a5 \uc2e4\ud589 —<\/p>\n

<\ucc38\uace0> oradebug tracefile_name<\/p>\n

oradebug event 10046 trace name context off;<\/p>\n","protected":false},"excerpt":{"rendered":"

\ubaa8\ub4e0 \uc624\ub77c\ud074 \ubc84\uc804\uc5d0\uc11c \uac00\ub2a5\ud55c \ubc29\ubc95 \uc790\uae30 \uc138\uc158\uc5d0 \ub300\ud558\uc5ec alter session set timed_statistics = true; alter session set max_dump_file_size = unlimited; alter session set sql_trace = TRUE; \ub610\ub294 exec dbms_session.set_sql_trace(TRUE); \ub610\ub294 alter session set events \u201810046 trace name context forever, level 8\u2019; — trace \ud560 SQL \uc2e4\ud589 — alter session set events \u201810046 trace name context […]<\/p>\n","protected":false},"author":1,"featured_media":2411,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[11],"tags":[],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/haisins.synology.me\/wordpress\/wp-content\/uploads\/2018\/01\/34aed586ca86225f5bf558a2e882a752.jpg?fit=500%2C353","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2408"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2408"}],"version-history":[{"count":3,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2408\/revisions"}],"predecessor-version":[{"id":2413,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2408\/revisions\/2413"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/2411"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2408"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2408"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2408"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}