5

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.

Improve this question
8
  • So this is one big query alone that is running? Not some pl/sql code with loops and stuff? Commented Sep 1, 2019 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 Commented Sep 2, 2019 at 3:46
  • 1
    if DML then progress is displayed in the transaction table v$transaction. stackoverflow.com/questions/54215043/… Commented Sep 2, 2019 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. Commented Sep 2, 2019 at 19:01
  • 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. Commented Sep 2, 2019 at 20:57

3 Answers 3

Reset to default
3

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).

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks, I will give it a shoot and see. Will keep you posted.
I've put my solution up. Please check it. Leave comments if you have questions.
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.

Improve this answer

Comments

0

Some kind of workaround could be the following

  1. Create job in DB, that runs your code, set it auto_drop = true and run it
  2. Loop while job is alive
  3. Count in the loop whatever you're expecting "from the over side"
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.