Checking an SQL statement progress is never easy. There are so many parameters that affect your execution time and estimates are never correct. So in this post I’ll mention a few techniques I’ve been using over the years. They are not perfect and they have quite a bit of limitations sometimes, but they do help in some cases and I hope they may help you as well.
Long operations (v$session_longops)
This is probably the most well known method of all. When running a query, “long operations” (see below) will be recorded in the v$session_longops view and you can follow their progress there. This view records different information about the operation, including the time passed and estimated time to finish the operation.
Here is an example:
SQL> select sid,opname,target,sofar,totalwork,elapsed_seconds,time_Remaining
2 from v$session_longops
3 where sofar<>totalwork
4 order by sid;
SID OPNAME TARGET SOFAR TOTALWORK ELAPSED_SECONDS TIME_REMAINING
---------- ---------------------------------------- ---------------------------------------- ---------- ---------- --------------- --------------
134 Table Scan LIRON.LARGE_TABLE 464567 1306014 14 25
Important notes:
1. What is a “long operation”? Not every thing is a “long operation”, actually, only a few operations are. “Long operations” in this context are specific execution plan single operations. In this category you will find: “full table scan”, “index fast full scan”, “sort”, “hash join” and a few others.
2. It’s important to remember that a “long operation” is only a single operation in the execution. So if the query performs “full table scan” inside a loop (like in “nested loops”), v$session_longops will record each of those separately. Meaning, you’ll see a row for the full table scan, how long it’s been running, and how much time it still has. But when this full scan finishes, it doesn’t mean that the query finished. Wait a little and you might see another full scan on the same table (because the query performs another scan after the previous one had finished). You might also see operations on different objects as well (like full table scan of table1 followed by a full table scan on table2).
3. This view is not completely accurate at times (especially in the time estimation). This is for 2 reasons: first, the TOTAL_WORK column is taken from some statistics, so sometimes you’ll see that the ACTUAL_WORK column contain a higher number than the TOTAL_WORK. And second, the time calculation is based on how much time it took to perform this operation until now: If we have 100 blocks to scan and we scanned 50 in 50 seconds, v$session_longops will estimate that this operation will take another 50 seconds to complete. But no one says that the throughput stays the same. If the next 10 blocks will take 15 seconds (instead of 10 like before) the time will be adjusted, but it probably won’t meet the initial estimation of 50 seconds.
4. In some cases you won’t see data in v$session_longops. Remember that only certain operation are recorded there (“index range scan” for example, will never be recorded). Also, the recording starts only after the specific operation has been running for 6 seconds. So if you have multiple full table scans, the query takes a long time, but each full scan takes less then 6 seconds, you won’t see anything in v$session_longops.
SQL Monitoring
This is a great feature in Oracle, but it’s a bit too much for this post So I’ll just mention that it does require Tuning Pack license and it’s turned on for every query after 5 seconds of CPU+I/O time. With this feature you’ll be able to see exactly where the session is when running the query, how long each line of the execution plan takes and how much of the work was already been done. You can read more about it here.
Follow the Session Statistics (v$sesstat)
This is a bit trickier method and it requires some more research and also knowledge of the query itself, but it helped me many times. When running any command in Oracle, many statistics are gathered at the session level in v$sesstat view. This statistics are cumulative, so they start at 0 when the session connects and grow as operations are being done.
The secret of using this technique is knowing what statistics Oracle saves and knowing your query. I’ll give a simple example: lately I wanted to update a large table, so I wrote a PL/SQL block that updates the table in a loop and I commited every 1000 rows. When it was executing I didn’t know how fast it updates or how far it is in the process. I did add some “dbms_output” calls to show some statistics, but dbms_output has a buffer and it doesn’t print immediately on the screen, so it didn’t help to follow the process in real time. I could have added a log table and insert into this log table just before committing. However, there is a relatively easy way to follow the process. Oracle keeps the “user commits” statistic for each session, so you can find the relevant SID and follow the “user commits” value to see how fast it commits. If you also know how many rows you should commit you can definitely figure out when it is due to finish.
Here is a simple example (I’m using a single session here just to show how it works):
SQL> select name.name,stat.value
2 from
3 v$sesstat stat,
4 v$statname name
5 where name.statistic# = stat.statistic#
6 and name.name = 'user commits'
7 and stat.sid =390;
NAME VALUE
---------------------------------------------------------------- ----------
user commits 2
SQL> insert into test values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> select name.name,stat.value
2 from
3 v$sesstat stat,
4 v$statname name
5 where name.statistic# = stat.statistic#
6 and name.name = 'user commits'
7 and stat.sid =390;
NAME VALUE
---------------------------------------------------------------- ----------
user commits 3
In a more complicated cases I used the statistics “index fetch by key” and “table fetch by rowid”. For example, if a query is doing a lot of index processing (joins, scans, etc.) and only then accesses the table to get the rows, you can follow the number of rows with the “table fetch by rowid” to see the progress. I used this one when needed to do a distributed query (updating a local table based on a remote table). In this case I could follow the local updates based on the table access by primary key and see the progress while most of the time and work was spent on the remote database.
Summary
Oracle has great tools to provide estimation or progress of the query. SQL monitoring is probably the best but not always available, the great thing is that Oracle keeps so much information about what’s going on that we can actually use this information to understand what the process is doing and sometimes even know how far we are in the process.
If you’re looking for more info, check other statistics in v$statname, in 19c there are more than 2000 of them, some can be really useful.
Have another idea of how to get SQL progress information? Let me know in the comments below