Intermittent issue with Oracle package returning empty tables

Question

We have a very complex Oracle package here (over 4,100 lines of code) that's giving us issues. I've been tasked to track down the problem. The issue is, when we call the execute_filter procedure, we expect to get back 6 tables. However, around 10-15 times a day our code crashes because table index 1 is out of range. When this repros, it seems to repro several times then a minute later, it works great again. I've still yet to be able to repro this under a debugger to see exactly what the data set is - but I have a theory the dataset is just a single empty table.

Digging through the Oracle package is almost impossible, as it's all one big run-on query with no formatting, no indention, and pages and pages of code that builds other queries by concatenating strings and what not. However, I have a theory about what's going on.

The execute_filter method calls one or more of dozens of other methods, for example filter_by_areas_name. Each of these methods queries some data and inserts this data into a table called tpm_temp_filter_project. An example of this is:

FOR I IN 1..areaState.COUNT LOOP
   INSERT INTO tpm_temp_filter_project
   (
      projectid,
      versionid
   )
   SELECT .. --Grabs the data it needs from other tables

At the end of each of these filter calls, we call a procedure called populate_result_table which copies stuff in tpm_temp_filter_project into another table and then does:

EXECUTE IMMEDIATE 'truncate table tpm_temp_filter_project';

So, my theory is that if two people run this query at the same time, the rows from these "holder" tables are getting truncated prematurely while another query still needs them.

What's the best way to prevent this sort of thing from happening? One idea I had would be to put:

LOCK TABLE tpm_temp_filter_project IN EXCLUSIVE MODE;

At the very beginning of execute_filter, and a COMMIT; as the very last line. In theory, this should only allow one person to run the command at the same time, and pending requests will "block" until the first filter is done. I haven't tried this yet, but I have a few questions.

1. Is this a good theory as to what's going on?
2. Is this a good fix, or is there a better solution to this issue?

I appreciate any insight into this problem.

UPDATE:

Here's the schema for the temp table:

CREATE GLOBAL TEMPORARY TABLE TPMDBO.TPM_TEMP_FILTER_PROJECT  ( 
    PROJECTID   NUMBER NULL,
    VERSIONID   NUMBER NULL 
    )
ON COMMIT DELETE ROWS

ANOTHER UPDATE:

This does NOT appear to be a conflict between two sessions. If I change:

EXECUTE IMMEDIATE 'truncate table tpm_temp_filter_project';

to:

DELETE tpm_temp_filter_project;

then the error still occurs. Even if I comment out that line completely, the error still eventually occurs. There is nothing else in that package body that deletes, truncates, or modifies any other data what so ever.

Second piece of evidence - I finally did repro the error under the Visual Studio debugger. The DataSet in .NET is completely empty. There's one table called table that has zero columns. If this was an issue with one session deleting data in these temp tables, then I would expect a valid schema with zero rows or perhaps rows from the wrong session.

Answer 1

The issue ended up being due to package state being reset intermittently. After several days of debugging (as the issue only repro'ed on production servers), I finally found the cause.

A procedure was being called in code which stored some data in a local variable. After that, some C# code got ran that internally, ended up calling Open() on the database connection again (even though the connection was already opened). Rather than no-op, calling Open() again seems to close and re-open the connection to the database - at least with the Oracle drivers we use. 99 out of 100 times, it would just choose the same connection from the connection pool and continue to work fine. However, every so often it would choose a different connection and our session ID would change, and the package state would get lost.

Commenting out that Open() call fixed the problem immediately.

Answer 2

truncate is DDL, so it automatically issues a commit, which is why you're seeing the effects across sessions.

The most obvious, and best, solution (as mentioned in a comment by @Glenn) is to use a global temporary table with on commit delete rows. This will ensure that the data in this table will exist only for the duration of the transaction that it is created in.

If you need to span transactions, you can use a global temporary table with on commit preserve rows, but then you will need to ensure that you use a single session for all access to that data. If you end the session at the end of processing, the data will be deleted automatically. However, if you re-use the session (i.e. you use session pooling or some such) then you'll need to delete the entire table at the end of processing.

Another solution is to use delete rather than truncate. delete will restrict the changes to the current session until a commit is issued. If you insert and delete within the same transaction, the effect will be largely the same as using a global temporary table.

The solution you ask about, explicitly locking the entire table, should work as well. However, you may find that performance suffers with this solution, as you will be effectively serializing your processing. The other solutions do not have this limitation.

---

Based on the revised question:

Since you're already using a global temporary table (GTT), most of my answers becomes irrelevant. The question now would seem to be "why is the GTT getting cleared prematurely". Since you're using on commit delete rows, the likely answer is that something is causing your transaction to end prematurely. Look for a commit or rollback nested where it would only execute occasionally. Another obvious culprit could be DDL being executed via an execute immediate, such as a sequence being reset.

If you can't find something like that, you might try changing the GTT to on commit preserve rows. This will allow the data in the GTT to persist across transactions and should still be safe if your process closes it's session when it completes.

Answer 3

Ok, so, based on your updates, you do have a global temporary table (GTT).

Since it's a GTT, you should not be truncating it. Load data into it, process the data, and when you're done, just do a commit. Since it's defined as 'on commit delete rows', there's no need to truncate, and the commit is better from a performance/scalability point of view as well.

Since it is a GTT, by the way, the scope/visibility of the data is at the session level. That is, whatever is written to the table by a specific session, can only ever be seen by that session.

So, your earlier comments about two people querying at the same time are not correct. Each session would see it's own unique set of data.

Hope that helps.

Answer 4

That should be good.

Anyway you could get better results if you find a good way to limit the number of dml executions to load the same data without involving more complexity. That would be the best thing in my opinion.

Answer 5

If this temp table is really meant as a session specific buffer (not meant to be shared across sessions), then I would think using collections (nested tables and/or assoc arrays) would be a better way to go, and probably much faster.