4

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.

game wu
  • 299
  • 5
  • 17
  • So this is one big query alone that is running? Not some pl/sql code with loops and stuff? – OldProgrammer Sep 01 '19 at 23:18
  • Thanks game wu. Is this a query (read-only), or is this doing DML -- an update? Can you also tell me -- is this stable/reliable in its cost and performance, or do different parameters (dramatically) change the execution plan? Thanks – alexgibbs Sep 02 '19 at 03:46
  • 1
    if DML then progress is displayed in the transaction table `v$transaction`. https://stackoverflow.com/questions/54215043/oracle-11-2-0-1-how-to-identify-the-row-which-is-currently-updated-by-the-up/54251384#54251384 – Dmitry Demin Sep 02 '19 at 10:20
  • Is this only for one specific SQL, or for any kind of SQL statement? If this is only for one statement, you can create some estimates based on what you know about the query. But if you're looking for a generic program to estimate the completion of any query, that task is [theoretically impossible](https://en.wikipedia.org/wiki/Halting_problem). – Jon Heller Sep 02 '19 at 19:01
  • @OldProgrammer It is just a select, but joined few tables with million of rows. That is why it takes long time – game wu Sep 02 '19 at 19:10
  • @alexgibbs It is just a select, but joined few tables with million of rows. That is why it takes long time – game wu Sep 02 '19 at 19:11
  • 1
    Thanks game wu. Since this is a select, I would hope that materialization/caching could be considered as a potential alternative to a lengthy ad-hoc query, but presuming that isn't viable, I'd think similarly to @JonHeller -- since this is a single statement, you should be able to profile it over its range of parameters (dynamic sampling, etc.). Armed with that profile, you could hit V$_SQL_MONITOR (or V$_SQL_PLAN_MONITOR ) to measure % progress. Or if it is very stable, you could just estimate. – alexgibbs Sep 02 '19 at 20:57
  • @alexgibbs I've put my solution up. Please check it. Leave comments if you have questions. – game wu Sep 04 '19 at 22:24

2 Answers2

2

Assuming this is one long select call, you can run the following query to get the stats on a long-running statement:

select  * from v$session_longops  where time_remaining > 0 and sid = ???

sid value should be replaced with the oracle session id of the session that is running the long query. You can determine that from looking in v$session. You will need to execute the above query in a separate thread (or whatever a concurrent unit of execution is in spring).

OldProgrammer
  • 12,050
  • 4
  • 24
  • 45
0

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.

game wu
  • 299
  • 5
  • 17