Modifying the DeleteClassification Procedure: First Iteration
The TransactToReset cursor definition can be improved to eliminate the inefficient DISTINCT clause, which forces an extra step of sorting and eliminating duplicates. Moreover, the original query joins two tables, ValidTransaction and Classification, even though it returns data only from the ValidTransaction table.
I've decided to use a correlated subquery with an EXISTS clause instead of joining tables. This is much more efficient and results in significant performance gains.
Here is the modified version of the TransactToReset cursor found in Listing 1:
cursor TransactToReset (p_StartDate IN date, p_EndDate IN date) IS
from ValidTransaction T
where T.TransactionDate >= p_StartDate
and T.TransactionDate <= p_EndDate
from Classification C
where C.TransactionId = T.TransactionId);
I've also decided to utilize some of Oracle 8i PL/SQL's new features, such as BULK BINDS and BULK COLLECT (described in my previous 10-Minute Solution, "New PL/SQL Features in Oracle 8i: Part II
"). These features improve the performance and also increase the number of rows committed in one transaction, thus decreasing the number of transactions and lowering the probability of the ORA-01555 error.
Listing 2 shows the improved DeleteClassification stored procedure. Here the BULK COLLECT feature bulk-fetches TransactionId values from the TransactToReset cursor into the v_TransactionId collection (a PL/SQL table). The BULK COLLECT operation fetches the data, starting at index 1, and successfully overwrites elements in the target collection (v_TransactionId) until it retrieves all rows.
|Fetching Across Commits: Where Is the Source of the Problem?
|Fetching across commits is a common and accepted practice in the Oracle programming world, even though it's not an ANSI-supported operation. Read on... |
I use the LIMIT clause to limit the number of rows fetched (see sidebar) from the cursor to a p_CommitBatchSize value, which is passed as a parameter. Using 10,000 as a default value of p_CommitBatchSize, 100,000 TransactionId values are retrieved in 10 bulk fetches versus 100,000 fetches in the original version. Moreover, using the BULK BINDS feature (FORALL clause), 100,000 TransactionId values are sent to the SQL engine in 10 batches, as opposed to 100,000 individual batches, as before. That leads to a significant performance gain.
The improved procedure executes 20 percent faster and the ORA-01555 error occurs less frequently. However, I cannot eradicate the error completely, even though I've reduced the number of transactions from 1,000 to 10. ORA-01555 errors still occur inconsistently during the day when transactional activity is high due to other users' activity.
Modifying the DeleteClassification Procedure: Second Iteration
It's clear that we can't leave the cursor open for fetching while we keep processing data and committing data changes. In order to eliminate this error completely, you need to modify the code so it supports the following algorithm:
- Opens the cursor.
- Fetches the whole cursor into the collection (no data manipulation/committing here).
- Closes the cursor.
- Loops through the collection, processes the data, and commits.
shows the final version of the DeleteClassification stored procedure. Now the whole TransactToReset cursor is fetched into the v_TransactionId PL/SQL table in one step using the BULK COLLECT clause. Then the cursor is closed immediately. This way you avoid fetching across commits and you eliminate the ORA-01555 error completely. In addition, the procedure's execution time becomes about 24 minutes, which is 20 percent faster than with the original version.
As you can see, restructuring PL/SQL code in combination with new Oracle 8i features, such as BULK BINDS and BULK COLLECT, allows you to eliminate the ORA-01555 error as well as significantly improve database performance.