Notes for my slot in session UGF2630: EOUC Database ACES Share Their Favorite Database Things: Part I – OpenWorld , Sunday 18th September 2016
DBMS_APPLICATION_INFO is probably one of the most undervalued and without any doubt the most underused of all the packages supplied with the database by Oracle. On the face it, it is a very simple package that does a few very simple things, but it is not possible to overstate its significance.
This is the fourth post on a serie of postings on how to get measurements out of the cell server, which is the storage layer of the Oracle Exadata database machine. Up until now, I have looked at the measurement of the kind of IOs Exadata receives, the latencies of the IOs as as done by the cell server, and the mechanism Exadata uses to overcome overloaded CPUs on the cell layer.
I’m always telling people to instrument their code. Invariably they don’t. Then this happens:
I just wanted to put up a post about DBMS_APPLICATION_INFO. This is a fantastic little built-in PL/SQL package that Oracle has provided since Oracle 8 to allow you to instrument your code. i.e record what it is doing. I’m a big fan of DBMS_APPLICATION_INFO and have used it several times to help identify where in an application time is being spent and how that pattern of time has altered.
Some PL/SQL developers use it and some don’t. It seems to me that it’s use comes down to where you work, as most PL/SQL developers are aware of it – but not everyone uses it (a friend of mine made the comment recently that “all good PL/SQL developers use it“. I can understand his point but don’t 100% agree).