How to retrieve the progress of oracle sql query?

Issue

I have a coding problem I can't solve.

The requirement is show progress of a sql query. I am using Spring Boot and Angularjs, so the idea is to show progress of sql to UI, not need to be real time, but preferred. Basically user click on button on UI, it will trigger API to retrieve data from db and then return the completed data to UI.

We have a computation extensive sql which will takes long time to finish. When the row we want to retrieve is about 10 million, it roughly takes about 15 minutes. We want to show the progress of the SQL so User have a idea of how long it will take. So the idea is to check how many rows has completed:

Say 1 million retrieved, then it should return 10% and so on. Next 1 million, then 20%.

I have no idea how to approach this. Need some suggestion.

Thanks in Advance.

Solution

Thanks Everyone. I've tried to use OldProgrammer's solution, but failed to do so. Somehow I just can't retrieve Hibernate sesison ID, and all related post I found were either too old or just manually create new session which I don't want.

I did a work around to this problem, not a solution though.

So what I did is that for that complex SQL, first I did total count, then chunk it into 100 chunks. Then with each chunk finish(Pagination), I will update the completion percentage. Meanwhile, on the UI, I will have time-interval to check the progress of this SQL from a different API endpoint, currently I set it to every 2 seconds.

So the outcome is like this, for fairly small amount of rows, it will jump from 2% to 6% for every API call; for large amount, only after few API progress check, it will go from 1% to 2%.

This is not a clean solution, but it did its purpose, it will give User some idea of how long it will take to finish.

Answered By - game wu