Add new comment

Here are two runs of the same SQL from different programs. The top is an automated process, the bottom is Toad. (SQL is the same, but SQL_ID is different because of white space issues):

So why is one finished in 7.2 minutes and one still at 4.7 hours and still counting up?

Well in both cases the Oracle server delivered the first record out of 4.6 million in 2.1-2.2 minutes. The issue is in the client asking for the remainder of the records. The automated process (top) was busy working on the target side and had not requested all the records yet. The process on the bottom executed the query in 2.2 minutes and then accepted all the results in 5 minutes (7.2m – 2.2m) and then continued on its merry way.

So this is what we can gather from this summary info on the SQL Monitoring Summary and Detail screens in OEM:

• Database Time column = how long the query was busy running on the machine.
  This includes the sum of CPU Time, I/O Time, and other Wait events incurred during execution.
  In both cases it is in the 2.1-2.2 minute range. The query returns 4.6 million records (from outermost Execution Plan step in Detail screen).

• Duration column = Database Time + Time To Retrieve Records
  This is Database Time + the time for the client to request and receive all of the rows and then tell Oracle it is finished with the query results.
  This includes the sum of CPU Time, I/O Time, and other Wait events incurred during execution. In this case the client isn’t done processing all 4.6million records so Duration keeps counting up while the client finishes requesting the output of the query.

As we get more automated processes pulling and pushing data around this may become more of an issue, just wanted to show a situation we have been seeing with an automated process that slowly reads results from a query that runs on Exadata. I didn't mention Exadata earlier since this can just as easily happen on any database. But at least in this case we short circuited query tuning efforts and had them revisit their client system for speed improvements.

-Dave