Determining Query's Progress (Oracle Pl/Sql)

How to retrieve the progress of oracle sql query?

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.

Oracle Check Huge Insert Progress

Short answer: There is no database engine which can do that out-of-the-box. You need to understand that before starting the execution, Oracle does not know how many rows will be merged. An option, as stated in the comments, would be to split the query in chunks in PLSQL and use a logging method to monitor rows being committed vs time spent. But that will be much slower than running a normal merge statement, and I can think why it would be worth it.

Nevertheless, this approach can help you somehow. I am assuming your merge statement is not running in parallel for the sake of the purpose. Oracle offers the v$session_longops dictionary view to monitor long operations, defined as those that might take more than 6 seconds. However, for operation, you must understand each step within an execution plan.

Let's do a PoC

Tables

SQL> create table test.huge_table ( c1 number, c2 varchar2(40) , c3 varchar2(40) ) ;

Table created.

SQL> create table another_huge_table ( c1 number, c2 varchar2(40) , c3 varchar2(40) ) ;

Table created.

Let's insert 5M rows in the target table, and 10m in the source table, so that we can issue a merge for inserting 5M rows.

SQL> declare
  2  begin
  3  for i in 1 .. 5000000
  4  loop
  5     insert into test.huge_table
  6     values
  7     ( i ,
  8      lpad(dbms_random.string('A',1),round(dbms_random.value(20,30)),dbms_random.string('A',1)),
  9      lpad(dbms_random.string('A',1),round(dbms_random.value(20,30)),dbms_random.string('A',1))
 10     );
 11  end loop;
 12  commit;
 13* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats ( ownname => 'TEST' , tabname => 'HUGE_TABLE' , block_sample => true ) ;

PL/SQL procedure successfully completed.

SQL> declare
  2  begin
  3  for i in 1 .. 10000000
  4  loop
  5     insert into test.another_huge_table
  6     values
  7     ( i ,
  8      lpad(dbms_random.string('A',1),round(dbms_random.value(20,30)),dbms_random.string('A',1)),
  9      lpad(dbms_random.string('A',1),round(dbms_random.value(20,30)),dbms_random.string('A',1))
 10     );
 11     if i = 5000000
 12     then
 13             commit ;
 14     end if;
 15  end loop;
 16  commit;
 17* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats ( ownname => 'TEST' , tabname => 'ANOTHER_HUGE_TABLE' , block_sample => true ) ;

PL/SQL procedure successfully completed.

Let's verify the plan

SQL> explain plan for
merge into test.huge_table t
using (
    select c1,c2,c3 from test.another_huge_table
) s
ON (t.c1 = s.c1)
when not matched then
insert (c1, c2, c3)
values ( s.c1, s.c2, s.c3 );  2    3    4    5    6    7    8    9

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4265949913

------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT         |                    |    10M|  1096M|       | 67324   (1)| 00:00:06 |
|   1 |  MERGE                  | HUGE_TABLE         |       |       |       |            |          |
|   2 |   VIEW                  |                    |       |       |       |            |          |
|*  3 |    HASH JOIN RIGHT OUTER|                    |    10M|  1106M|   333M| 67324   (1)| 00:00:06 |
|   4 |     TABLE ACCESS FULL   | HUGE_TABLE         |  5000K|   276M|       |  8232   (1)| 00:00:01 |
|   5 |     TABLE ACCESS FULL   | ANOTHER_HUGE_TABLE |    10M|   553M|       | 16291   (1)| 00:00:02 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("T"."C1"(+)="C1")

We then run the merge

SQL> merge into test.huge_table t
using (
    select c1,c2,c3 from test.another_huge_table
) s
ON (t.c1 = s.c1)
when not matched then
insert (c1, c2, c3)
values ( s.c1, s.c2, s.c3 );  2    3    4    5    6    7    8

5000000 rows merged.

Elapsed: 00:00:36.39

SQL> commit ;

Commit complete.

Elapsed: 00:00:00.01

Meanwhile the process was running, I could get information for the steps

SQL> r
  1  select opname,
  2         target,
  3         round( ( sofar/totalwork ), 2 ) * 100 percentage_complete,
  4         start_time,
  5         ceil( time_remaining / 60 ) max_time_remaining_in_min,
  6         floor( elapsed_seconds / 60 ) time_spent_in_min
  7         from v$session_longops
  8*        where sofar != totalwork

OPNAME     TARGET          PERCENTAGE_COMPLETE START_TIM MAX_TIME_REMAINING_IN_MIN TIME_SPENT_IN_MIN
------------------- --------- ---------------------------------------------------
Table Scan TEST.ANOTHER_HUGE_TABLE          86 28-OCT-21                        1                 0

OPNAME     TARGET          PERCENTAGE_COMPLETE START_TIM MAX_TIME_REMAINING_IN_MIN TIME_SPENT_IN_MIN
------------------- --------- ---------------------------------------------------
Hash Join TEST.ANOTHER_HUGE_TABLE          42 28-OCT-21                        1                 0

OPNAME     TARGET          PERCENTAGE_COMPLETE START_TIM MAX_TIME_REMAINING_IN_MIN TIME_SPENT_IN_MIN
------------------- --------- ---------------------------------------------------
Hash Join TEST.ANOTHER_HUGE_TABLE          77 28-OCT-21                        1                 0

Summary

If Oracle thinks any of those steps will take more than 6 seconds, it will include them in the v$session_longops. You can query this viev to get an estimation per step, but it is not a real measure of how much time the whole DML will take. But it might be a good start.

Another approach, perhaps even better, would be to generate a baseline. You can include the merge in PLSQL, or in any other language for that matter, including a logging method to collect start time, end time and rows merged. Over time and dozens of executions, you can apply statistics and get average and/or standard deviation. I am using a similar approach to generate information of this type that I can use to make educated guesses, nothing more nothing less.

Hope this will help you somehow.

Check status sql query or percentage

Due to the set-based nature of SQL and database processing, you can't really get a "% complete" of a query since the oracle engine doesn't really know for sure. You could try looking at the view v$session_longops to see how far along parts of your SQL has gone (a large hash join or full table scan may show up here). Take a look at this Ask Tom for more info.

If your job has multiple SQL statements and you're trying to track how far along you are after each one, you could add some code to insert status updates on a control table after each statement.

How do I show running processes in Oracle DB?

I suspect you would just want to grab a few columns from V$SESSION and the SQL statement from V$SQL. Assuming you want to exclude the background processes that Oracle itself is running

SELECT sess.process, sess.status, sess.username, sess.schemaname, sql.sql_text
  FROM v$session sess,
       v$sql     sql
 WHERE sql.sql_id(+) = sess.sql_id
   AND sess.type     = 'USER'

The outer join is to handle those sessions that aren't currently active, assuming you want those. You could also get the sql_fulltext column from V$SQL which will have the full SQL statement rather than the first 1000 characters, but that is a CLOB and so likely a bit more complicated to deal with.

Realistically, you probably want to look at everything that is available in V$SESSION because it's likely that you can get a lot more information than SP_WHO provides.

Write an Oracle query that runs for a specific amount of time before returning

You can create pl/sql function and invoke it as part of the query. The function should have the logic to sleep for what ever time you want.

Here is the working code (the schema/user needs to have access on dbms_lock package).

create or replace function func
return number
as
begin
  dbms_lock.sleep(60);
  return 0;
end;
/

Function created.

SQL> set timing on
SQL> select func from dual;

      FUNC
----------
     0

Elapsed: 00:01:00.01

Oracle (11.2.0.1) : How to identify the row which is currently updated by the UPDATE statement

You can use the query to monitor long-running DML operations and rollback.
If the update field is not included in the index, then the value of the used_urec field from the v$transaction view will be very close to the number of rows.
When the update operation is performed, these values increase, if rollback is performed, the values are reduced to zero.

V$TRANSACTION lists the active transactions in the system.
    USED_UREC Number of undo records used
    USED_UBLK Number of undo blocks used
select
   substr(s.username,1,28) username,
   substr(s.program,1,25) program,
   s.command,
   t.used_urec,
   t.used_ublk,
   decode(s.command,
     0,'No Command',
     1,'Create Table',
     2,'Insert',
     3,'Select',
     6,'Update',
     7,'Delete',
     9,'Create Index',
     15,'Alter Table',
     21,'Create View',
     23,'Validate Index',
     35,'Alter Database',
     39,'Create Tablespace',
     41,'Drop Tablespace',
     40,'Alter Tablespace',
     53,'Drop User',
     62,'Analyze Table',
     63,'Analyze Index',
     s.command||': Other') command
from 
   v$session     s,
   v$process     p,
   v$transaction t
where s.paddr = p.addr
and s.taddr = t.addr 
order by 1

For example 1. If you update a column that is not indexed, then the number of rows 39915830 and USED_UREC 40000562 approximately coincide .

create table test_update(p1,p2,p3,p4 )
  PCTFREE     1
  INITRANS    1
  MAXTRANS    255
  TABLESPACE  arhiv_data
as 
SELECT a.n_p_u, a.id_reg, a.id_vag, a.vrsvop
  FROM a_vag_atr a;

SELECT count(*)
          FROM test_update a
==>
COUNT(*)                                     
-------------------------------------------- 
                                    39915830 

Session 1

update test_update 
set p2=1234567890
==>
39915830 row(s) updated

Session 2
start update

USERNAME       PROGRAM         COMMAND  USED_UREC   USED_UBLK   COMMAND_1                                             
---------------- ---------------------- ------------------- --------------------
ASUDS          sqlnavigator.exe      6  4181959    62690           Update       

stop update

USERNAME       PROGRAM         COMMAND  USED_UREC   USED_UBLK   COMMAND_1                                             
---------------- ---------------------- ------------------- --------------------
ASUDS          sqlnavigator.exe      6   40000562   601871       Update       

For example 2. if you update the field indexed then the number of lines * 3 is approximately the USED_UREC. 39915830 *3=~116705429

create table test_update(p1,p2,p3,p4 )
  PCTFREE     1
  INITRANS    1
  MAXTRANS    255
  TABLESPACE  arhiv_data
as 
SELECT a.n_p_u, a.id_reg, a.id_vag, a.vrsvop
  FROM a_vag_atr a;

SELECT count(*) FROM test_update a
==>
COUNT(*)                                     
-------------------------------------------- 
                                    39915830 

CREATE INDEX test_ind ON test_update
  (
    p1                              ASC
  )

Session 1

update test_update 
set p1=12
==>
39915830 row(s) updated

Session 2
stop update

USERNAME       PROGRAM         COMMAND  USED_UREC   USED_UBLK   COMMAND_1                                             
---------------- ---------------------- ------------------- --------------------
ASUDS          sqlnavigator.exe      6  116705429   1392538        Update       

For example 3. if you insert into table not indexed then the number of rows is exactly the USED_UREC.

create table test_update(p1,p2,p3,p4 )
      PCTFREE     1
      INITRANS    1
      MAXTRANS    255
      TABLESPACE  arhiv_data

 SELECT count(*)
              FROM test_update a
==>
    COUNT(*)                                     
    -------- 
          0

Session 1

declare
 i pls_integer:=1;
begin
for i in 1..500000 loop
insert into test_update(p1,p2,p3,p4)
values(1,2,3,sysdate); 
end loop;
end;

select count(*) from  test_update
==>
COUNT(*)                                     
----------- 
     500000

Session 2

USERNAME       PROGRAM         COMMAND  USED_UREC   USED_UBLK   COMMAND_1                                             

---

    ASUDS          sqlnavigator.exe      2     500000    5815    Insert

For example 4. if you delete from table not indexed then the number of rows is exactly the USED_UREC.

Session 1

 SELECT count(*) FROM test_update a
    ==>
        COUNT(*)                                     
        -------- 
         500000
delete from  test_update
==>
500000 row(s) deleted

Session 2

    USERNAME       PROGRAM         COMMAND  USED_UREC   USED_UBLK   COMMAND_1                                             
---------------- ---------------------- ------------------- --------------------
        ASUDS      sqlnavigator.exe   7      500000      9616         Delete  

---

Related Topics