The best lesson I learnt from Steve McConnell

CodeComplete

I was made aware of the book Code Complete by Steve McConnell almost by accident. I was attending an Oracle presentation back in 2001 and the presenter, Steven Feuerstein mentioned the book. I can’t remember what context it was mentioned in but I remembered I had to get a copy.

Reading the first edition of Code Complete became a series of wow moments. As a self taught programmer each chapter heralded a new revelation. I say proudly that this one book has had the biggest positive impact in my professional life as a programmer.

It is therefore very difficult to pick the best lesson I learnt from Steve McConnell. It finally came down to a choice of two; Valid Reasons to Create a Routine and the chapter on Debugging.

In the end I decided that it was Valid Reasons to Create a Routine. I won’t reveal the most important reason here but the answer will surprise you. It’s not to avoid duplicate code BTW.

If you want to know the answer I recommend you buy the book, I already know you will not regret it.

Instrumenting your PL/SQL code

Introduction

As an exceptional Oracle Developer you already know the value of instrumenting your code. You have read enough of Tom Kyte, Cary Millsap & Steve McConnell to understand that with the correct instrumentation diagnosing problems especially time critical problems (are there any others?) becomes much easier.

So the question is not why you should instrument your code but how. In this article I will explain the installation and use of the PL/SQL Logger which was originally developed by Tyler Muth and is now available on Github.

Installation

Once you have downloaded Logger from Githib and unzip the contents to a directory that can be seen by your Oracle client (I use SQL Plus). navigate to the directory “releases” and expand the folder for the Logger release you wish to install. At the time of writing version 2.1.2 was the latest so that will be the version used in this article.

Whilst you can install logger into an existing schema, I am going to use the supplied @create_user.sql to install Logger into it’s own schema. So start SQL Plus and connect to the database as system or a user with the DBA role and run the following script

@create_user.sql

When prompted to do so, enter the username, tablespace, temporary tablespace and password for the new schema. In this example I have accepted the suggested defaults of logger_user, users and temp respectively.

Now connect to the database as the newly created logger_user and run the installation script:

@logger_install.sql

If you run into problems with the installation it is worth starting with the supplied documentation.

In this article I will be accessing the Logger objects from the standard Oracle HR user so the following grant is required:

GRANT EXECUTE ON logger TO hr
/

Once installation is complete, you can view the status of the logger by running the following command

exec logger.status;

which displays the following:


SQL> exec logger.status;
Project Home Page :
https://github.com/tmuth/Logger---A-PL-SQL-Logging-Utility/
Logger Version : 2.1.2
Debug Level : DEBUG
Capture Call Stack : TRUE
Protect Admin Procedures : TRUE
APEX Tracing : Enabled
SCN Capture : Disabled
Min. Purge Level : DEBUG
Purge Older Than : 7 days
Pref by client_id expire : 12 hours
For all client info see : logger_prefs_by_client_id

PL/SQL procedure successfully completed.

How to use

Below is a procedure that updates the salary of a given employee. Now lets pretend it is part of a overnight batch job and is just one of thousands of procedures that need to run. To the outside world this procedure is a blackbox. Without querying the employee after the procedure has run (and committed) you have no idea what has happened when it was called.


CREATE OR REPLACE PROCEDURE raise_salary
(
   p_emp_id     IN employees.employee_id%TYPE,
   p_new_salary IN employees.salary%TYPE
)
IS
BEGIN

   UPDATE employees e
      SET e.salary = p_new_salary
    WHERE e.employee_id = p_emp_id;

END raise_salary;
/

Lets change that by adding some instrumentation.


CREATE OR REPLACE PROCEDURE raise_salary
(
   p_emp_id     IN employees.employee_id%TYPE,
   p_new_salary IN employees.salary%TYPE
)
IS
BEGIN

 logger_user.logger.log('Start of raise_salary');

 UPDATE employees e
   SET e.salary = p_new_salary
 WHERE e.employee_id = p_emp_id;

 logger_user.logger.log('Number of rows updated: {' || TO_CHAR(SQL%ROWCOUNT) || '}'); 

 logger_user.logger.log('End of raise_salary'); 

END raise_salary;
/

The revised version of the procedure captures some basic information as the start of the procedure, how many rows were updated and finally that the procedure completed successfully.

Even in this simple case it is easy for the real code to be submerged by the instrumentation code. There is no one size fits all solution. Careful consideration is required to decide how much instrumentation is required.

When the procedure is run, one of the Logger views can be queried and the output reviewed:


SELECT text
  FROM logger_user.logger_logs_5_min
/

Here is the output from the revised procedure after it has been run:


TEXT
----------------------------
Start of raise_salary
Number of rows updated: {1}
End of raise_salary

Level Up

The output from Logger is controlled by the level constant. The level can be set by calling the logger.set_level procedure. In the previous example I have used the log procedure to instrument my routine. This means that the output will insert an entry into the logger_logs table when the logger_level is set to debug.

There are a number of additional log_x routines that you can use with the various logging levels. For instance, log_warning will insert an entry into the logger_logs table when the logger level has been set to “warning”, likewise if you are only interested in capturing errors, you can set the Logger level to error and use the logger_error procedure.

One of the nicest features of the level facility is the ability to turn logging on for specified clients rather than system wide. In the following example, I have turned on debug logging for user 1 whilst user 2 continues to use the system but without instrumentation messages being inserted.

Logger_user session

The following commands are run:

exec logger_user.logger.set_level(p_level => 'OFF')
/

This system wide command turns Logger off. This means currently no instrumentation is being recorded.

exec logger_user.logger.set_level(p_level => 'DEBUG', p_client_id => 'user_one')
/

This command turns on debug logging for the client identifier user_one. (see user one session below for how this is set). This means any instrumentation that uses the logger.log procedure will be inserted into the log table.

The following query will show you the current clients that logging is enable for:

SELECT *
  FROM logger_prefs_by_client_id
/

User_one session

The following command sets the client identifier (this is used by the call to enable logging for this client by the Logger_user session above)


exec dbms_session.set_identifier('user_one');

I then execute the raise_salary procedure and commit.

User_two session

Almost a carbon copy of the user_one session with the exception of the identifier.


exec dbms_session.set_identifier('user_two');

I then execute the raise_salary procedure and commit.

Now when I query the logger table I see the following output which shows that only user_one’s actions have been captured.


SQL> SELECT client_identifier, text
2      FROM logger_user.logger_logs_5_min
3 /

CLIENT_IDENTIFIER TEXT
------------------------------------
user_one Start of raise_salary

user_one Number of rows updated: {1}

user_one End of raise_salary

But wait there is more…

All of the main Logger procedures allow you to give context to the information you are recording. The context would normally be where the message has originated from, i.e. Application, Package etc. The facility is provided by the p_scope parameter. Here is the raise_salary procedure used earlier refactored to include the p_scope parameter.


CREATE OR REPLACE PROCEDURE raise_salary_scope
(
   p_emp_id     IN employees.employee_id%TYPE,
   p_new_salary IN employees.salary%TYPE
)
IS

   l_scope logger_user.logger_logs.scope%type := 'raise_salary_scope';

BEGIN

   logger_user.logger.log('Start', l_scope);

   UPDATE employees e
      SET e.salary = p_new_salary
    WHERE e.employee_id = p_emp_id;

   logger_user.logger.log('Number of rows updated: {' || TO_CHAR(SQL%ROWCOUNT) || '}', l_scope);

   logger_user.logger.log('End', l_scope);

END raise_salary_scope;
/

Each of the calls to the log procedure now has the name of procedure contained within the l_scope parameter passed to it.

The following query shows results of using the scope parameter


SQL> SELECT scope, text
2      FROM logger_user.logger_logs_5_min
3    /

SCOPE                TEXT
------------------------------------------------
raise_salary_scope   Start

raise_salary_scope   Number of rows updated: {1}

raise_salary_scope   End

The logging of parameter values is handled within Logger by using the p_params object. This is easier to show with an example rather than even more text!

Here is the final revision of the raise_salary procedure.


CREATE OR REPLACE PROCEDURE raise_salary_params
(
   p_emp_id     IN employees.employee_id%TYPE,
   p_new_salary IN employees.salary%TYPE
)
IS

   l_scope    logger_user.logger_logs.SCOPE%TYPE := 'raise_salary_scope';
   l_params   logger_user.logger.tab_param;

BEGIN

   logger_user.logger.append_param(l_params, 'p_emp_id', p_emp_id);
   logger_user.logger.append_param(l_params, 'p_new_salary', p_new_salary);

   logger_user.logger.log('Start', l_scope, null, l_params);

   UPDATE employees e
      SET e.salary = p_new_salary
    WHERE e.employee_id = p_emp_id;

   logger_user.logger.log('Number of rows updated: {' || TO_CHAR(SQL%ROWCOUNT) || '}', l_scope);

   logger_user.logger.log('End', l_scope);

END raise_salary_params;
/

The parameters are added to a pre-defined Logger associative array and that array is included in the first call to the log procedure.

The parameters are stored in the extra column and the following query shows how to display them.


SQL> SELECT text, extra
2      FROM logger_user.logger_logs_5_min
3    /

TEXT                         EXTRA
---------------------------------------------------------------------------------
Start                        *** Parameters *** p_emp_id: 100 p_new_salary: 4500

Number of rows updated: {1}

End

Summary

There is no getting away from the fact that the instrumentation is an overhead to your application. Trying to find the balance between too much and too little information that is to be captured, the extra code you need to develop and then the extra resources required to execute the larger code base all need to be carefully considered.

My opinion is that this is a price I am willing to pay. When bad things happen and you are under pressure to track down the cause you will find the problem far quicker with code that is telling you what exactly is happening. Compare this with the alternative. Peering into an enormous black box armed only with guesses.

Acknowledgements

Tyler Muth

Martin Giffy D’Souza

What are my favourite Oracle books? and why?

This post was inspired by a comment on Martin Windlake’s blog.

I am an Oracle Developer who actually likes reading technical books, not only about Oracle technologies but also the wider Software Development world. (Does that put me in a minority?)

As a self taught programmer I have read many Oracle books since I started working with the Database. Whilst most of the books I have read have been at best, perfunctory,  the books that have made it on to this list, which are in no particular order, all have had a very positive impact on I how work with the Oracle Database and it’s related technologies.

Effective Oracle By Design by Tom Kyte

This book is the Code Complete for Oracle developers.

It was the book that first made me aware, when working with PL/SQL less is definitely more and to start thinking in sets. It highlighted the importance of instrumentation within your code, to be wary of universal best practises and also included the only road map I have yet to see for the Oracle Documentation.

PL/SQL From SQL a chapter by Adrian Billington from the book Expert PL/SQL Practises

I found this book to be very hit and miss but I believe Adrian’s single chapter “PL/SQL from SQL” is by itself worth the price of the book.

For many years every relevant Oracle tome I have read had the dire warning “beware of context switching” Oracle Developers know it is has to affect performance when you switch from SQL to PL/SQL within the same statement but exactly how bad it actually is was rarely, if ever disclosed. Adrian’s chapter is the first I know which shows the true cost of context switching. The first part of the chapter explains the term “Context Switching” and goes on to show’s the cost with easy to follow “Then and Now”  SQL.  The second part of the chapter then moves on to explaining how you can start reducing the cost of PL/SQL functions when called from SQL along with some non – PL/SQL alternatives.

Troubleshooting Oracle Performance by Christian Antognini

The book on Oracle performance.

It covers the whole spectrum of Oracle Performance tuning. From identifying and the prioritisation of problems from a business perspective to in depth discussion of the DBMS_XPLAN package.

Oracle PL/SQL Programming by Steven Feuerstein

The seminal book on working with the PL/SQL language. I hesitate to recommend it for learning the language simply because I struggled to learn PL/SQL using the 2nd Edition but once you are up and running there is no better resource for PL/SQL.

Expert Application Express by John Scott, et al

This is a book that should be within reach if you are working with Oracle Application Express. With thirteen chapters written by many of today’s Application Express luminaries it covers topics from the myriad of choices you have for selecting the webserver to how to develop tabular forms effectively and working the Apex 4 features such as Dynamic Actions. My favourite is Doug Gault’s chapter on Debugging.

Where are the Jonathan Lewis books? Whilst I never miss a Jonathan Lewis presentation at the UKOUG events, I have struggled with his books and so at the moment they do not appear on my list. However this list is very much live so one may appear as I periodically review this post.