One little known feature of Oracle is connection metadata functionality. It might not sound as important as it actually is.
Here is a bit of a back story. When Oracle 10g came out one of the biggest features that it added was web-based Oracle Enterprise Manager (OEM) interface. For the first time in years developers and dbas got a web interface dashboard that finally shows what's going on inside the database. This one feature is a great breakthrough. Finally no need to download some cryptic looking scripts and trying to understand the multi-page reports to get a glimpse at the status of your database and which query just spiked the database load. Now with OEM finding what the query is is really easy (you might need to buy Oracle Performance pack to monitor vitals and performance information). So you know which query caused the spike. Do you know where that query is in your application code? Do you even know which of 10 different applications this one is coming from? You might have an idea based on table names to at least identify the application, and then you are very lucky if you can do that. A common way to solve this sort of query identity crisis is to start embedding comment information identifying where that query can be found in your code. But you already have a ton of applications written, having to go through every query to add this meta information is a ton of dev effort. What do you do?
With pecl oci8 1.4.0 release a couple of new functions were introduced that weren't available before. From changelog ( http://pecl.php.net/package-info.php?package=oci8&version=1.4.0 ):
1. Introduce connection attribute functions:
oci_set_module_name
oci_set_action
oci_set_client_info
oci_set_client_identifier
These set values that are visible/used by the database. They are useful for tracing, authentication and auditing.
So what can you do with all these new functions?
oci_set_client_info -> this is your application identification information. Something like App V1.2
oci_set_client_identifier -> as far as PHP manual explains it it's a possible application username that is logged into your applcation.
oci_set_module_name -> which module of your application is this related to
oci_set_action -> this one idenfies which action/function is performing this set of queries.
All these functions provide a lightweight way of attaching more information to your client connection without executing any more queries on the server. Actually this information is actually getting sent on next round-trip request to the server. So it's very lightweight. These functions are also great at providing tracing functionality that allows dbas to come back to developers and work with them on getting their application to be much better than before. It also makes things a lot easier to find in big or numerous applications within the enterprise. On server side this information can be queried from V$session view among other places. It will also show up in various places within the OEM interface. Also optionally tracing and logging can be turned on the server side based on some of specific identifiers set by your application. This would provide server-side tracing logs that would let you trace down problems you might be having with some pieces of your application.
Personally using these functions all over your code might be an overkill. It's also more of your own call of how much of it you want to integrate into your application. One thing I would at least recommend is to at least identify your application and/or possible module information and possibly the client identifier to see who is causing all the problems for you.
Gena01
![[Gena01 Logo]](/images/gena01_logo1.gif){kind=logo}
