We want some kind of feedback from our program, but since programs just do what we ask them to do, it\u2019s up to us to find a way of getting the information we need. So how can you instrument your code? It\u2019s not that difficult, you just have to add some lines of codes that tell you what it\u2019s doing. When developing a procedure, and it\u2019s not immediately doing what you expect, you might add some lines of code using DBMS_OUTPUT. That\u2019s code instrumentation, by adding this you\u2019re able to locate the problem and correct it. But DBMS_OUTPUT is very limited and you should create, or find, a proper logging mechanism. You really should! But the aim of this article is to show you what\u2019s already present in the database and how we can get the most out of it.<\/p>\n
Setting your identifier<\/h3>\n
Luckily for us, Oracle has already put a lot of effort in instrumenting the database. When you need an answer to the question: \u201cWhich session do I need to kill?\u201d you can query the performance view \u201cV$SESSION\u201d, and most of the time you\u2019ll able to pinpoint the badly behaving session. It shows you what it\u2019s doing, which SQL-statement it last executed, or what it is waiting for. But when looking at the data in V$SESSION, you also see a lot of empty fields like client_identifier, client_info, module and action.<\/p>\n
These fields are free for you to use. If your DBA will allow grants on DBMS_SESSION and DBMS_APPLICATION_INFO you will be able to monitor your programs easily with the V$SESSION view:<\/p>\n Querying the V$SESSION view again, it will provide you with much more detailed information. Your DBA would love you for this! <\/p>\n Setting these identifiers makes enabling tracing these programs a breeze. Using DBMS_MONITOR you can trace a session by using SESSION_TRACE_ENABLE and specifying a sessions SID and serial number. However, it might by more useful not to trace a single session, but to trace a single module or action:<\/p>\n Now you can take a look at the trace files. Most of us have heard of TKPROF, a little command line tool that comes with the database. Have you also heard of TRCSESS? Ever wondered what all those extra parameters in TRCSESS mean? TRCSESS is another little command line tool allowing you to take the input from one or more trace files and apply filters to get the set of SQL statements you\u2019re interested in. Since these trace files also contain the module and action information you can easily create a trace file for the part that needs some investigation. In the raw data you can find the different actions in your module: <\/p>\n After filtering it for only one action, you have removed the statements that don\u2019t belong to this criterion:<\/p>\n This allows you to create a report using TKPROF which contains only the statements you’re interested in. This makies it easier to tune your applications.<\/p>\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2012\/12\/looking-at-the-data-in-VSESSION.png\" "\\"looking")
<\/a><\/p>\nBEGIN\r\ndbms_session.set_identifier(client_id=>'BB');\r\ndbms_application_info.set_client_info(client_info=>'Billy Bragg')\r\n--\r\ndbms_application_info.set_module(module_name=>'CREATE SONG', action_name=>'1.Write Melody');\r\nwrite_melody;\r\n--\r\ndbms_application_info.set_action(action_name=>'2.Write text');\r\nwrite_text;\r\n--...\r\ndbms_application_info.set_module(module_name=>'',action_name=>'');\r\nEND;<\/pre>\n
<\/a><\/p>\nTracing<\/h3>\n
BEGIN\r\ndbms_monitor.serv_mod_act_trace_enable(service_name \u00a0=> 'SYS$USERS'\r\n,module_name \u00a0\u00a0=> 'PLAY GIG'\r\n,waits \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0=> TRUE\r\n,binds\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0=> TRUE\r\n,instance_name => NULL);\r\nEND;<\/pre>\n
<\/a><\/p>\ntrcsess output=mytrace.trc action=\"1.Soundcheck\" \u00a0\u00a0\u00a0\u00a0 dev11g_ora_3788.trc<\/pre>\n
<\/a><\/p>\ntkprof mytrace.trc mytrace.txt sys=no sort=prsela,exeela,fchela<\/pre>\n