Hello everyone,
Today I would like to describe the DBMS_APPLICATION_INFO package in the oracle database.
here are some of the procedures it has:
Sets the name of the current action within the current module
| |
Sets the
client_info field of the session | |
Sets the name of the module that is currently running to a new module
|
for the full description refer to the link:
with this you can set some information in the current session, which can be queried from the v$session from the sys schema:
select module,action,client_info from v$session
this can be helpful to identify and trace database sessions, specially if you are using connections pools.
after getting the connection from the pool, the first thing to do is to set the client_info ,module or action fields using this package for example:set the module to the application name and set the client_info to user name. this way it will be easier for you or the dba to identify which session you want to trace.
here is an example:
recently we had a certain page in our production environment that was slow to load, the problem could not be reproduced in test environment, after some poking around we suspected that the issue might be caused by database. but since the application uses a connection pool ,we had several sessions connected to the database.and did not know which session to trace,so by using this package we set the value of the client_info to be the user name. after that we managed to trace the session ,used TKPROF on the output file and by inspecting the resulting file found that the issue was caused by loading an entire table(40,000+ record) into memory then searching them. after fixing the issue the page loaded fast.