OracleBlog

Want to know what has been calling the PL/SQL Procedure you've written?
Want to know how to tell how far along your long-running PL/SQL Procedure is?
What to know how to write fast mass insert/deletes like a pro?

I have found you the answer for all three, courtesy of my fine colleagues featured among my links. Allow me to guide you through these three great articles.

Who Is Calling Your Procedure?

The answer to our first question comes courtesy of Oracle's Blogger of the Year Eddie Awad.

Eddie won this award not just by virtue of being first in an alphabetical listing, but also by the sheer volume of focused articles of high interest, each one backed up by references and proven tests. His article about OWA_UTIL.WHO_CALLED_ME is no exception. His article includes:
1. A working example you can cut and paste,
2. A link to Oracle's documentation, and
3. A link to Dan Morgan's syntax reference

Thanks to his diligence, it literally took only minutes to add this type of instrumentation to my existing code. When something goes wrong, I know exactly who called my procedure, and where. This even works if the calling code is wrapped. (Proof forthcoming)

Given how easy it is, I plan on testing the overhead/performance to see how feasible it would be to start using this as a metric by creating this table and adding this line:

CREATE TABLE MyProcCalls
(caller_name VARCHAR2(100), line_number NUMBER(8));

INSERT INTO MyProcCalls (caller_name, line_number) VALUES (caller_name, line_number);

Then I'll start looking like a pro already.

How Far Along Is My Procedure?

This answer comes from my newest featured blogger Andy Campbell. He has been an Oracle DBA for 7 years and, like me, started a blog to record the things he learns and doesn't want to forget.

Let's take advantage of Andy's experience on how to instrument our PL/SQL code further by including calls to DBMS_APPLICATION_INFO to record our procedure's on-going progress.

Andy explains, complete with examples, how to use this supplied package (available as far back as Oracle 7) to have your procedure write information into session-tracking tables using SET_MODULE and then SET_ACTION.

Of course the big pay-off comes at the end of his article where he executes his long-running procedure and then queries the session-tracking tables like V$SESSION to find out how far along the procedure is. Brilliant!

Let me supplement his article with the following three references:

1. Supplied Packages References, Chapter 3: DBMS_APPLICATION_INFO

2. Another one of my favourite featured bloggers, Oracle ACE of the Year Tim Hall, is reknowned for his great articles. He also has a write-up on how to use DBMS_APPLICATION_INFO and the V$SESSION tables.

3. Finally, what list of mine would be complete without including Dan Morgan's reference?

Thanks to Andy's working example, it literally took me minutes to further instrument my PL/SQL code and fool people into thinking they were written by a true Oracle pro.

How Do I Write Mass Inserts/Deletes Like a Pro?

If we're going to code like a pro, we need to take at least one page out of Tom Kyte's playbook. Tom Kyte's work is beloved for its passion, dedication, completeness and accuracy.

Tom's has a passionate distaste for bad practises, including slow-by-slow processing. Programmers unused to thinking in sets are at a distinct disadvantage when programming database applications. Why? Because they do their inserts and deletes one-by-one in a for loop.

As database programmers know, this approach is completely unnecessary and wasteful. They know instead to look for a solution that does some kind of bulk processing when doing inserts, deletes or updates on a large number of records.

Tom illustrates this point with a specific case where particularly bad Oracle code had been written.

The original requirement was to prune down a list of employees, removing duplicate employees by choosing the one with the most recent hiring date.

What did the original programmer do? First, created a big master table of all employees, then when through them in a loop, each time deleting employees that produced more than one record when grouped by employee number.

Study how it was done by the original programmer. It might not look like an altogether unfamiliar approach. But what do you think about this result?

Well, you may find that the result was not only very slow because it would loop as often as your most common duplicate, but potentially logically incorrect because it would remove an employee entirely if it had duplicate records with the same hiring date.

Instead look how a true pro handles this situation. Tom handles this case in a single, efficient SQL statement.

He breaks the problem down to its simplest form: we need a list of each employee with its data, with only a single row per employee representing the most recent hiring date.

Therefore Tom populates such a table by first adding a column signifying the most recent hiring date for each employee (using PARTITION BY, a technique we've discussed many times before), then by selecting only the rows where the hiring date and this most recent hiring date match.

I admit this third tip is a little harder to digest than the first two, because you have to learn how to think like a pro. Break a problem down, and approach it like a database would by thinking in sets.

Summary

The true secret behind these three tips is actually to read. There are a lot of Oracle professionals just like you, and we're fortunate that so many of them are generous with their knowledge. Thanks!!!

Looking for more great articles whenever I'm quiet? Please bookmark the links along the side of the page. And visit the articles in my archives: you may have especially missed some of the early ones.