See you there!!
Monday, January 25, 2016
40 Days.....
Friday, January 8, 2016
You need to be using DBMS_APPLICATION_INFO
I’m honestly very surprised how often I meet Oracle professionals
who are not familiar with this package. It’s
a great thing to help you in debugging and monitoring your application. It does require a little bit of “work” to
use, and it’s little, but the benefits are potentially huge.
Let say I’m the owner of a particular piece of code that is
pretty critical to the business, if it performs badly then lots of folks get
pretty upset. And being a proactive kind
of guy I’d like to be able to monitor its performance even when it’s not causing
problems. The best way to see how
something is running is to do a 10046 trace, or more formally, extended SQL
tracing.
But tracing something is hard, especially since I want to
trace a “thing” not a “user”. This has been rather difficult until rather
recently since all tracing was (are really still is) base on a session. But
with DBMS_MONITOR
we can now turn on tracing based on these things called “Module” and “Action”.
But to do this we need to set module and action otherwise
this is useless. This is where a few
lines of code can make the huge impact.
The process is very simple; it’s a process that we’ve all used in one fashion
or another in programming. We want to set
a variable to a value but we need to retain what it was set to, this way we can
then set it back to its original setting later.
Here is a simple block of code to illustrate the process. All I need to do to take advantage of these
setting is use a few calls from the package:
DBMS_APPLICATION_INFO.read_module
DBMS_APPLICATION_INFO.set_module
And maybe
DBMS_APPLICATION_INFO.set_action
In my example I’m not using the last one. I would use that in a more complicated code
block where I’d want to show I’m doing something different within the same main
code block.
Here is a simple block of code to illustrate the process.
01)
PROCEDURE
get_emp_instr IS
02)
fnlist_stack
fnlist_tab;
03)
lnlist_stack
lnlist_tab;
04)
preModuleName
VARCHAR2(32) := NULL;
05)
preActionName
VARCHAR2(32) := NULL;
06)
BEGIN
07)
DBMS_APPLICATION_INFO.read_module(module_name
=> preModuleName, action_name => preActionName);
08)
DBMS_APPLICATION_INFO.set_module(module_name
=> 'Human Resources', action_name => 'Get Employees');
09)
SELECT first_name, last_name BULK COLLECT
INTO fnlist_stack, lnlist_stack FROM employees;
010)
DBMS_APPLICATION_INFO.set_module(module_name
=> preModuleName, action_name => preActionName);
011)
EXCEPTION
012)
WHEN OTHERS THEN
013)
DBMS_APPLICATION_INFO.set_module(module_name
=> preModuleName, action_name => preActionName);
014)
raise_application_error (-20042,'ERROR in
get_emp_instr');
015)
END
get_emp_instr;
016)
/
Lines 4 and 5 set up holding locations for the previous
module and action names. In Line 7 we
capture the current setting of module and action and put them into the defined
variables. Line 8 then sets the module
and action name to what you want it set to for this code block. Then we would do whatever it is we would do
in the block. Also this is not intended
to set action every other line or something quite that detailed. The idea is that a module is a “large” unit
of work and that an action is a “significant” amount of work in a sub unit
under the module. I have found that
trying to set it too quickly and the kernel seems to ignore some of the settings,
or they go so fast they the change gets “lost”. (I didn’t see the changes in the trace file.)
Lines 10 and 13 both set the module and action back to the previous
settings. Line 10 is certainly needed,
since it not reset, when the code goes back to the calling environment the old
setting would be lost. However line 13
you may or may not want to have. If it’s
a locally handled exception and all it good to go then yes set it back to what
it was, but it’s really an error state you likely will not want to reset them
so these values are still set to what they were when the error occurred. A little thought is required about resetting
the module and action in an exception handler.
So there it is. With about 5-6 lines of code this block is instrumented
and as it is executed I can monitor its executions (these module and action
names get populated into several V$ views and such). And I could
even trace this by turning on trace like this:
EXEC DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE
(service_name =>'HOTSOS', module_name =>'Human Resources', action_name
=> DBMS_MONITOR.ALL_ACTIONS, waits => TRUE, binds => TRUE);
Notice there is a system defined constant for all actions
this way if I had a large code unit with the same module (for example a
package) this would allow me to get any action used within the defined
module. Inside the trace file(s) I would
see line like this:
*** MODULE
NAME:(Human Resources) 2016-01-04 09:08:49.535
*** ACTION
NAME:(get_emp) 2016-01-04 09:08:49.535
Of course you need to turn it off too so it would look like
this (for every enable there is a disable):
EXEC
DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE (service_name =>'HOTSOS',
module_name =>'Human Resources', action_name =>
DBMS_MONITOR.ALL_ACTIONS);
A key point is that if you turn it on my module action you
could end up with multiple trace files. Because
anyone running the named module/action combination will generate a trace, and
trace files are still session generated. So you can use trcsess to put them
all together like this (this is an OS level utility, you run this at the OS
command level):
trcsess
output=hr_test.trc action="get_emp" module="Human
Resources" *OP.trc
Pretty cool
stuff eh?
Oh and DBMS_APPLICAITON_INFO has been around since about 7.2 so
don’t try this “it’s a new thing that’s why I don’ know about it” excuse.
Subscribe to:
Posts (Atom)