Monthly Archives: March 2012

Steven Feuerstein High Performance PL/SQL Seminar in Columbus

OOUG is hosting a one day seminar by Steven Feuerstein on April 16. The seminar is titled “Higher Performance PL/SQL and Oracle 11g PL/SQL New Features”. If you are in the Columbus area and work with PL/SQL, you will want to be there. Steven is the world’s leading expert in PL/SQL and by far one of the best presenters I have ever seen. Check out the OOUG website for more details.

PL/SQL Instrumentation – The Easy Way

Instrumentation (when referring to code) is code that is put in place to allow you to monitor the execution of the code. Instrumentation in PL/SQL code is something that is often missing. Worse, a lot of people don’t allow instrumentation because they worry that adding the extra code to do the instrumentation will slow down the code too much. It is true that there could be a small bit of performance decrease due to the extra code. However, it is so minimal, this should never be a consideration, especially with the extreme benefits it provides. Proper instrumentation will let you see exactly where your code is performing poorly, so you can focus your efforts where it will have the most benefit.

The best part is that Oracle provides built in packages that make it extremely easy to instrument your code. What I use the most is DBMS_APPLICATION_INFO to set the module and action, so I know what exactly is running:

dbms_application_info.set_module(module_name => 'Employee Maintenance',action_name => 'employee_update');

The module should refer to the business task, and the action should identify the specific process that is running for that task.

I also use DBMS_APPLICATION_INFO if I need my code to see what is currently running:

dbms_application_info.read_module(module_name => v_start_module, action_name => v_start_action);

I typically use this to keep track of what was previously running before I call set_module, so I can set the module and action back to what it was when my code is done.

Once module and action are being set, you will notice their values showing up in V$SESSION (columns module and action) and Enterprise Manager screens will even use these values in its performance reports.

For more information, check out the excellent presentation by Karen Morton of Method R titled “Performance Instrumentation for PL/SQL: When, Why, How”. It can be found on the Method-R presentations page