My first book is now physically in my hands. August 22, 2016
Posted by mwidlake in Instrumentation, PL/SQL, publications, SQL, writing.Tags: books, PL/SQL, SQL
7 comments
Proud “parent” of a bouncing baby book
Today a box arrived from Oracle Press. In it were a few copies of “Real-World SQL and PL/SQL” which I co-authored with Arup Nanda, Brendan Tierney, Heli Helskyaho and Alex Nuitjen. I know I only blogged about the book a couple of weeks back, how I became involved and the impact it had on my life for several months. But as I can now physically handle and read the final article, I could not resist putting up a quick post on it. Honestly, I’ll stop being a book bore soon.
My contribution to the book was three chapters in the section “Essential Everyday Advanced PL/SQL”. The idea was to covers some core, standard ways of using PL/SQL which are often overlooked or implemented without considering the impact they can have. There are a few things I cover that are often talked about, generally regarded as a good thing to do – but so often are not done! So just to quickly summarise my chapters:
Chapter 6 is about running PL/SQL from SQL, ie calling both built-in and user defined functions from SQL. It’s a great way to compartmentalise your business logic and extend the capabilities of Oracle’s SQL implementation in an easy and seamless manner. Only people are often unaware of the potential performance and read consistency impact it can have, or how Oracle 11g and 12c help reduce these issues.
Chapter 7, “Instrumenting and Profiling PL/SQL”, covers something that I feel is a major oversight in many PL/SQL development projects. Instrumenting your code, any code (not just PL/SQL), is vital to producing an application that is professional and will continue to work correctly for many, many years. However, it’s a bit like washing your hands after going to the loo – we all know it is the correct thing to do but so many people just don’t! Without instrumentation it is almost impossible to see how your code is performing, where time is spent and where problems are when they occur. I’m sick of having to guess where the problem is when people report slow performance when some basic and light-weight instrumentation will tell you exactly where the problem is. And as for profiling PL/SQL, it’s one of the rarest things to be done but it is so helpful.
It physically exists
Chapter 9 is on using PL/SQL for Automation and Administration. Like many people, I have automated many tasks with a PL/SQL harness – backups, partitions maintenance, metric gathering, data life-cycle management, regular data loads. You end up writing the same sort of process over and over again and usually there are several versions of such controlling frameworks across a company, written by different people (and sometimes the same people!). A large part of this chapter takes the code for creating the examples from chapter 6 and the instrumentation from chapter 7 and builds up a simple but comprehensive framework which can be used to control almost any data load or administrative task you need to do with an Oracle database. The key thing is it can be used for many, many processes so you need only the one framework. So you don’t have to keep writing them as it’s boring to keep writing them. And because the framework demonstrated includes instrumentation, the framework you implement will be easy to monitor and debug in years to come. I have to confess, I kind of wrote that chapter “just for me”. It is my standard automation framework that I now always use, so I can concentrate on the actual task being done and not the nuts-and-bolts of controlling it, and I’ve wanted to properly document it for years.
Something all the authors agreed on is that whilst most technical books describe how elements of a language or feature work, they do not really talk about the “how and why” you do things. The stuff you learn by using the language for a long time and doing a good deal of things wrong. In this book we attempt to put in some of that “how and why”. In my chapters there are a few anecdotes about when things have gone wrong and, as a result, you learn some of the “how not” 🙂
I’m at a lot of conferences over the next few months, including OOW16, DOAG16 and UKOUG Tech16. If you get a copy of the book and want it signed, you’ll find me very happy to do so. Many of my co-authors are at these events too, so you could get us all to scribble all over the book. NB this will not work for electronic versions 🙂
BTW do you like the t-shirt?
DBMS_APPLICATION_INFO for Instrumentation September 3, 2012
Posted by mwidlake in development, Instrumentation, performance.Tags: Architecture, performance, PL/SQL, Programming, system development
27 comments
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).
It is incredibly easy to use. You use the procedures SET_MODULE(module_name,action_name), SET_ACTION(action_name) and SET_CLIENT_INFO(client_info) to set the values of the corresponding columns in V$SESSION for your current session. This is done in memory, there is no inserting or updating of any table rows, so it is incredibly quick and light-weight. The below shows a subset of V$SESSION including the columns that get set:
desc v$session Name Null? Type ----------------------------------------- -------- ---------------- SADDR RAW(8) SID NUMBER SERIAL# NUMBER AUDSID NUMBER PADDR RAW(8) USER# NUMBER USERNAME VARCHAR2(30) COMMAND NUMBER ... MODULE VARCHAR2(64) MODULE_HASH NUMBER ACTION VARCHAR2(64) ACTION_HASH NUMBER CLIENT_INFO VARCHAR2(64) ...
Just something odd to note. MODULE, ACTION and CLIENT_INFO are shown at VC(64) but if you check the documentation you will see that:
MODULE is limited to 48 bytes
ACTION is limited to 32 bytes
CLIENT_INFO can be set to the whole 64 bytes
I have no idea why MODULE and ACTION are limited in this way but it might be historic, backward compatibility with prior versions.
As a quick example of their use:
test_11_2> --demo_dai.sql test_11_2> -- quick demo of dbms_application_info test_11_2> COL sid form 9999 test_11_2> col username form a12 test_11_2> col module form a18 test_11_2> col action form a22 test_11_2> col client_info form a15 test_11_2> -- set module and action test_11_2> EXEC DBMS_APPLICATION_INFO.SET_MODULE(MODULE_NAME=>'SALES_LOAD',ACTION_NAME=>'VALIDATE_ALL_PRICES') test_11_2> select sid,username,module,action,client_info from v$session where sid=650; SID USERNAME MODULE ACTION CLIENT_INFO ----- ------------ ------------------ ---------------------- --------------- 650 ERIC SALES_LOAD VALIDATE_ALL_PRICES test_11_2> -- test_11_2> -- Update the action within a module test_11_2> EXEC DBMS_APPLICATION_INFO.SET_ACTION(ACTION_NAME=>'Update all Prices') test_11_2> select sid,username,module,action,client_info from v$session where sid=650; SID USERNAME MODULE ACTION CLIENT_INFO ----- ------------ ------------------ ---------------------- --------------- 650 ERIC SALES_LOAD Update all Prices test_11_2> -- clear them test_11_2> EXEC DBMS_APPLICATION_INFO.SET_MODULE(null,null) test_11_2> select sid,username,module,action,client_info from v$session where sid=650; SID USERNAME MODULE ACTION CLIENT_INFO ----- ------------ ------------------ ---------------------- --------------- 650 ERIC test_11_2> -- you can set just an action test_11_2> EXEC DBMS_APPLICATION_INFO.SET_action(ACTION_NAME=>'orphan process') test_11_2> select sid,username,module,action,client_info from v$session where sid=650; SID USERNAME MODULE ACTION CLIENT_INFO ----- ------------ ------------------ ---------------------- --------------- 650 ERIC orphan process test_11_2> -- set them to something sensible test_11_2> EXEC DBMS_APPLICATION_INFO.SET_MODULE(MODULE_NAME=>'RETURNS_LOAD',ACTION_NAME=>'MATCH_TO_SALE') test_11_2> select sid,username,module,action,client_info from v$session where sid=650; SID USERNAME MODULE ACTION CLIENT_INFO ----- ------------ ------------------ ---------------------- --------------- 650 ERIC RETURNS_LOAD MATCH_TO_SALE test_11_2> -- client_info can be longer test_11_2> EXEC DBMS_APPLICATION_INFO.SET_client_info(client_info=>'entered this module at '||sysdate) test_11_2> select sid,username,module,action,client_info from v$session where sid=650; SID USERNAME MODULE ACTION CLIENT_INFO ----- ------------ --------------- --------------- ---------------------------------------- 650 ERIC RETURNS_LOAD MATCH_TO_SALE entered this module at 03-SEP-2012 13:07
The intention is that you set the MODULE as you enter a functional chunk of your application (be it a PL/SQL package or a chunk of JAVA, whatever {but be aware of the stateless nature of systems using middle tiers and pooled connections}) and then update the ACTION as you progress. You can set an action with no module, as demonstrated above, but I would recommend against it. On that topic I would make the following recommendations:
- Set the MODULE at the start, eg as you enter a PL/SQL package’s main module
- Set the MODULE before you do all the validation – you may wish to see if that validation is as quick as you think.
- Use SET_ACTION to update the action prudently. ie when the activity of the code alters but not all the time. It will come clearer with use, but what you are after is to be able to judge the relative run durations of parts of the application. Too much detail can mask the overall picture. If you can alter code you can always add more instrumentation.
- Clear the MODULE and ACTION explicitly as you exit a package. The activity is lightweight and you do not want any code that lacks this instrumentation to inherit values from calling code.
- Do not set an ACTION without having set the MODULE earlier in the code.
- Keep the text of these values short and specific.
You do not have to look at V$SESSION to see these values, which is good as you may lack the privs needed to do so. Since oracle 9 there have also been procedures to check what the values are set to. e.g:
test_11_2> BEGIN
2 DECLARE
3 V_MODULE VARCHAR2 (64);
4 V_ACTION VARCHAR2 (64);
5 begin
6 dbms_application_info.READ_MODULE(V_MODULE,V_ACTION);
7 DBMS_OUTPUT.PUT_LINE('Module IS '||V_MODULE||' Action IS '||V_ACTION);
8 END;
9 END;
10 /
Module IS RETURNS_LOAD Action IS MATCH_TO_SALE
test_11_2>
So as you can see, these values can be set, they can be accessed via the data dictionary or the package itself and, if you believe my wild claims, they are very lightweight to use. However, these values really come into themselves with eg OEM’s performance screens. I’ll leave examples of that to a second post. Here I just wanted to cover how easy it is to set and get the information.
That does lead me onto what I think is the main reason that DBMS_APPLICATION_INFO is not as widely used as it could {and should} be. Developers often lack access to eg OEM and so do not see the full benefit of using it. Also, developers tend to be {note the get-out-clause) more concerned with making the code work and less with monitoring it after it is released. DBA-types tend to be more concerned with monitoring it. Thus the DBA types would want it in the code and developers would not want to spend the time coding it in. We are all one big team though, so some negotiation and beer-buying (or management enforcement) might well resolve that one.
Martin Widlake's Yet Another Oracle Blog 