I will be giving 2 presentations for the Northeast Ohio Oracle Users Group in Cleveland on March 30. I will be doing my presentation on Execution Plans that I did at OOUG last year. I will also be doing a presentation on Index Myths that I am currently putting together right now. I will post the files for download when they are complete. If you are in the Cleveland area, stop by and say hello.
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.
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