Browse DevX
Sign up for e-mail newsletters from DevX


Don't Let Referential Integrity Degrade Your Database Performance-3 : Page 3




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Using TOAD to View SQL in Real Time
I decided to find out exactly what the database was executing during that six-hour period while sitting on the same DELETE statement. I used TOAD from Quest Software, Inc. as my main Oracle database developer's tool. (In one of my previous 10-Minute Solutions, "Profiling in Oracle 8i," I show how to utilize TOAD's profiling capabilities.)

In TOAD 7.1, the latest version, there's a Kill/Trace Session option under the DBA menu. Instead of killing a session, I was able to see all current sessions including the one that was running the procedure. After I selected that session, I saw SQL statements in the lower panel popping up and going away as they were being executed in real time. That was really useful!

I found that this SQL query was being executed again and again and again:

select count(0) from Credits where TransactionID = :b1;

This statement would appear on the screen for five to seconds, on average, only to go away and come back again.

Obviously, this was not part of the procedure's code. It was generated by the database due to the presence of the bind variable (:b1) in the SQL statement. It was the SQL generated by the declarative RI rules! Even though I had deleted all the child records, the database was not aware of this, so it was enforcing FK constraints by checking whether any children existed in four related tables. In the case of the Credits table, it was getting a record count for each TransactionID it was trying to delete to make sure it's zero. If it was greater than zero, it would've raised an exception error: "ORA-02292 integrity constraint violated child record found."

I generated an execution plan for that SQL statement to find out why it was taking so long to get a simple count. Sure enough, the execution plan showed that the optimizer selected a full table scan—which, in the case of the Credits table, amounted to a scan of 25 million rows for each of the 10,000 TransactionIDs selected from the Transactions table. The total time for the last DELETE was 50,000 seconds (5 seconds times 10,000) or almost 14 hours!

After adding an index to the TransactionID in the Credits table and running the procedure again, I saw that it took less than 14 minutes. Obviously, the database was using the newly created index to perform declarative RI checking.

Comment and Contribute






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



Thanks for your registration, follow us on our social networks to keep up-to-date