dcsimg
Login | Register   
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


advertisement
 

The ORA-01555 Error: A PL/SQL Developer Solution-2 : Page 2


advertisement

WEBINAR:

On-Demand

Application Security Testing: An Integral Part of DevOps


How the Error Occurs
Recently I've been working on optimizing the DeleteClassification stored procedure. It works mainly with two tables—ValidTransaction (parent) and Classification (child)—which are in a one-to-many relationship with the TransactionId foreign key.

The purpose of this stored procedure is to delete Classification records for all ValidTransactions that fall within a specified period/month. Using the TransactionDate field from the ValidTransaction table selects records in the date range. In my case, ValidTransaction contains data for 12 periods (one year) with 1.2 million rows total (100,000 rows per month on average). The Classification table has 10 rows on average for one row in the ValidTransaction table, with 12 million rows total. Cleaning up the Classification data for a specified period/month means deleting 1 million rows from the Classification table.

This presents two major problems:

  • Execution is slow. It takes about 30 minutes to delete 1 million Classification records from a total of 12 million records.

  • The "ORA-01555: snapshot too old" error keeps cropping up. Although it happens only occasionally, whenever it does so it always occurs during the last six minutes of the run. The error occurs on the line where data is fetched from the cursor (also shown in red in Listing 1):


    fetch TransactToReset into v_TransactionId;

Listing 1 shows a simplified version of the original DeleteClassification stored procedure. TransactionId values are selected into the TransactToReset cursor for all rows in the ValidTransaction table within a specified month. Then these TransactionIds are fetched into the v_TransactionId variable and are deleted one by one. The v_LoopIndex variable is used to count deleted records and commit every one-hundred of them.


Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.
Thanks for your registration, follow us on our social networks to keep up-to-date