Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Moru.

Asked: June 11, 2020 - 4:02 pm UTC

Last updated: July 27, 2020 - 2:17 am UTC

Version: 12c

Viewed 1000+ times

You Asked

Hello AskTom team,

My question is regarding a way to determine when a DDL command will finish.
Specifically, I am using a CTAS with a parallel of 20 and rebuilding indexes with
the parallel of 20 also.

I thought that v$session_longops would show me the time to completion but for some reason,
it does not. Here is my query below. 

SELECT opname, target, ROUND((sofar/totalwork),4)*100 Percentage_Complete, start_time, 
CEIL(time_remaining/60) Max_Time_Remaining_In_Min, sofar, totalwork,
FLOOR(elapsed_seconds/60) Time_Spent_In_Min 
FROM gv$session_longops 
WHERE TOTALWORK != 0
AND sofar != totalwork;


Using gv$session due to it being on a RAC system.

I was reading some articles on this and some folks don't think it's possible to return a
approx time to completion due to parallelism.

Maybe CTAS and Create indexes in parallel need to query some other metadata?

Any help or clues?

The query above didn't return any results. Strange as it sounds. I even modified the query to center in on the sid. Note: The OEM GUI DID show the fact that a table was being worked on. But, that tool only shows you the 'end time' when all is finished. There is not 'approx end time' kind of column.

FYI, love this site, and I am glad you still continue to keep it relevant after Tom retired!

and Connor said...

I replicated your observations (ie, nothing in session longops).

Some things you could look at

1) You can EXPLAIN a DDL to get an estimate before running, eg

SQL> create table t as select * from dba_objects;

Table created.

SQL> create table t1 as select level x from dual connect by level <= 5000;

Table created.

SQL> explain plan for
  2  create table mega_tab
  3  as select /*+ parallel */ t.*
  4  from t, t1;

Explained.

SQL> SELECT * from table(dbms_xplan.display)

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT             |          |   411M|    50G|  1366K  (1)| 00:00:54 |        |      |            |
|   1 |  PX COORDINATOR                    |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)              | :TQ10000 |   411M|    50G| 20968   (2)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT (HYBRID TSM/HWMB)| MEGA_TAB |       |       |            |          |  Q1,00 | PCWP |            |
|   4 |     OPTIMIZER STATISTICS GATHERING |          |   411M|    50G| 20968   (2)| 00:00:01 |  Q1,00 | PCWP |            |
|   5 |      MERGE JOIN CARTESIAN          |          |   411M|    50G| 20968   (2)| 00:00:01 |  Q1,00 | PCWP |            |
|   6 |       PX BLOCK ITERATOR            |          | 82245 |    10M|    41   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   7 |        TABLE ACCESS FULL           | T        | 82245 |    10M|    41   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   8 |       BUFFER SORT                  |          |  5000 |       | 20927   (2)| 00:00:01 |  Q1,00 | PCWP |            |
|   9 |        TABLE ACCESS FULL           | T1       |  5000 |       |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 12 because of degree limit

20 rows selected.


Obviously an estimate is strictly that ... an estimate. For example, just under 1min was estimated above, but I can hobble that with a tablespace that will need to grow as it goes, eg

SQL> create tablespace slow_ts datafile 'D:\oracle\oradata\db19\pdb1\slow01.dbf' size 10m autoextend on next 10m;

Tablespace created.

SQL> create table mega_tab tablespace slow_ts
  2  as select /*+ parallel */ t.*
  3  from t, t1;


And that has been running for 5mins already :-) But I can look at progress using SQL Monitor, eg

C:\>sql19

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 23 12:21:25 2020
Version 19.7.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Last Successful login time: Mon Jun 22 2020 14:48:58 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0

SQL> @sqltunet 6h89f750bkxkn

SQL Monitoring Report

SQL Text
------------------------------
create table mega_tab tablespace slow_ts parallel as select /*+ parallel */ t.* from t, t1

Global Information
------------------------------
 Status              :  EXECUTING
 Instance ID         :  1
 Session             :  MCDONAC (864:58478)
 SQL ID              :  6h89f750bkxkn
 SQL Execution ID    :  16777216
 Execution Started   :  06/23/2020 12:21:27
 First Refresh Time  :  06/23/2020 12:21:27
 Last Refresh Time   :  06/23/2020 12:21:27
 Duration            :  15s
 Module/Action       :  SQL*Plus/-
 Service             :  pdb1
 Program             :  sqlplus.exe

Global Stats
=================================================================================================
| Elapsed |   Cpu   |    IO    | Concurrency |  Other   | Buffer | Read | Read  | Write | Write |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   | Waits(s) |  Gets  | Reqs | Bytes | Reqs  | Bytes |
=================================================================================================
|     183 |    3.26 |     5.89 |         167 |     6.46 |  59567 |  605 |   9MB |   732 | 532MB |
=================================================================================================

SQL Plan Monitoring Details (Plan Hash Value=4230380487)
==========================================================================================================================================================================================================
| Id   |              Operation               |   Name   |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Read | Read  | Write | Write | Mem  | Activity |          Activity Detail           |
|      |                                      |          | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs | Bytes | Reqs  | Bytes |      |   (%)    |            (# samples)             |
==========================================================================================================================================================================================================
| -> 0 | CREATE TABLE STATEMENT               |          |         |       |        16 |     +1 |    12 |        0 |      |       |       |       |    . |     7.81 | buffer busy waits (4)              |
|      |                                      |          |         |       |           |        |       |          |      |       |       |       |      |          | Cpu (1)                            |
|      |                                      |          |         |       |           |        |       |          |      |       |       |       |      |          | DLM cross inst call completion (1) |
|      |                                      |          |         |       |           |        |       |          |      |       |       |       |      |          | control file sequential read (1)   |
|      |                                      |          |         |       |           |        |       |          |      |       |       |       |      |          | Disk file operations I/O (4)       |
|      |                                      |          |         |       |           |        |       |          |      |       |       |       |      |          | direct path sync (4)               |
|    1 |   PX COORDINATOR                     |          |         |       |           |        |    12 |          |      |       |       |       |    . |          |                                    |
|    2 |    PX SEND QC (RANDOM)               | :TQ10000 |    411M | 20968 |           |        |    12 |          |      |       |       |       |    . |          |                                    |
| -> 3 |     LOAD AS SELECT (HYBRID TSM/HWMB) | MEGA_TAB |         |       |        16 |     +1 |    12 |        0 |    2 | 16384 |   372 | 330MB | 24MB |    90.62 | enq: HV - contention (165)         |
|      |                                      |          |         |       |           |        |       |          |      |       |       |       |      |          | log buffer space (3)               |
|      |                                      |          |         |       |           |        |       |          |      |       |       |       |      |          | Cpu (6)                            |
| -> 4 |      OPTIMIZER STATISTICS GATHERING  |          |    411M | 20968 |        15 |     +2 |    12 |       2M |      |       |       |       |  7MB |     1.56 | Cpu (3)                            |
| -> 5 |       MERGE JOIN CARTESIAN           |          |    411M | 20968 |        15 |     +2 |    12 |       2M |      |       |       |       |    . |          |                                    |
| -> 6 |        PX BLOCK ITERATOR             |          |   82245 |    41 |        15 |     +2 |    12 |      461 |      |       |       |       |    . |          |                                    |
| -> 7 |         TABLE ACCESS FULL            | T        |   82245 |    41 |        15 |     +2 |    12 |      461 |   12 | 944KB |       |       |    . |          |                                    |
| -> 8 |        BUFFER SORT                   |          |    5000 | 20927 |        15 |     +2 |   461 |       2M |      |       |       |       |  1MB |          |                                    |
|    9 |         TABLE ACCESS FULL            | T1       |    5000 |     2 |         1 |     +2 |    12 |    60000 |    2 | 65536 |       |       |    . |          |                                    |
==========================================================================================================================================================================================================

SQL> @sqltunet 6h89f750bkxkn
SQL Monitoring Report

SQL Text
------------------------------
create table mega_tab tablespace slow_ts parallel as select /*+ parallel */ t.* from t, t1

Global Information
------------------------------
 Status              :  EXECUTING
 Instance ID         :  1
 Session             :  MCDONAC (864:58478)
 SQL ID              :  6h89f750bkxkn
 SQL Execution ID    :  16777216
 Execution Started   :  06/23/2020 12:21:27
 First Refresh Time  :  06/23/2020 12:21:27
 Last Refresh Time   :  06/23/2020 12:21:27
 Duration            :  39s
 Module/Action       :  SQL*Plus/-
 Service             :  pdb1
 Program             :  sqlplus.exe

Global Stats
=================================================================================================
| Elapsed |   Cpu   |    IO    | Concurrency |  Other   | Buffer | Read | Read  | Write | Write |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   | Waits(s) |  Gets  | Reqs | Bytes | Reqs  | Bytes |
=================================================================================================
|     446 |    7.05 |       18 |         396 |       25 |   146K |  950 |  14MB |  1641 |   1GB |
=================================================================================================

SQL Plan Monitoring Details (Plan Hash Value=4230380487)
===================================================================================================================================================================================================================
| Id   |              Operation               |   Name   |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Read | Read  | Write | Write | Mem  | Activity |               Activity Detail               |
|      |                                      |          | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs | Bytes | Reqs  | Bytes |      |   (%)    |                 (# samples)                 |
===================================================================================================================================================================================================================
| -> 0 | CREATE TABLE STATEMENT               |          |         |       |        39 |     +1 |    12 |        0 |      |       |       |       |    . |     7.91 | buffer busy waits (12)                      |
|      |                                      |          |         |       |           |        |       |          |      |       |       |       |      |          | Cpu (2)                                     |
|      |                                      |          |         |       |           |        |       |          |      |       |       |       |      |          | DLM cross inst call completion (3)          |
|      |                                      |          |         |       |           |        |       |          |      |       |       |       |      |          | control file sequential read (2)            |
|      |                                      |          |         |       |           |        |       |          |      |       |       |       |      |          | Disk file operations I/O (13)               |
|      |                                      |          |         |       |           |        |       |          |      |       |       |       |      |          | direct path sync (5)                        |
|    1 |   PX COORDINATOR                     |          |         |       |           |        |    12 |          |      |       |       |       |    . |          |                                             |
|    2 |    PX SEND QC (RANDOM)               | :TQ10000 |    411M | 20968 |           |        |    12 |          |      |       |       |       |    . |          |                                             |
| -> 3 |     LOAD AS SELECT (HYBRID TSM/HWMB) | MEGA_TAB |         |       |        39 |     +1 |    12 |        0 |    2 | 16384 |  1011 | 899MB | 24MB |    91.03 | enq: HV - contention (388)                  |
|      |                                      |          |         |       |           |        |       |          |      |       |       |       |      |          | log buffer space (19)                       |
|      |                                      |          |         |       |           |        |       |          |      |       |       |       |      |          | log file switch (checkpoint incomplete) (6) |
|      |                                      |          |         |       |           |        |       |          |      |       |       |       |      |          | Cpu (7)                                     |
|      |                                      |          |         |       |           |        |       |          |      |       |       |       |      |          | direct path write (6)                       |
|    4 |      OPTIMIZER STATISTICS GATHERING  |          |    411M | 20968 |        36 |     +2 |    12 |       6M |      |       |       |       |  7MB |     1.07 | Cpu (5)                                     |
| -> 5 |       MERGE JOIN CARTESIAN           |          |    411M | 20968 |        36 |     +2 |    12 |       6M |      |       |       |       |    . |          |                                             |
| -> 6 |        PX BLOCK ITERATOR             |          |   82245 |    41 |        36 |     +2 |    12 |     1225 |      |       |       |       |    . |          |                                             |
| -> 7 |         TABLE ACCESS FULL            | T        |   82245 |    41 |        36 |     +2 |    12 |     1225 |   12 | 944KB |       |       |    . |          |                                             |
| -> 8 |        BUFFER SORT                   |          |    5000 | 20927 |        36 |     +2 |  1225 |       6M |      |       |       |       |  1MB |          |                                             |
|    9 |         TABLE ACCESS FULL            | T1       |    5000 |     2 |         1 |     +2 |    12 |    60000 |    2 | 65536 |       |       |    . |          |                                             |
===================================================================================================================================================================================================================


You can see from the EXECS column for T1 ... We are doing to 5000 of them, and we're slow working our way up. Not perfect, but good enough to get a rough idea.

My "sqltunet" script is just wrapper around

select DBMS_SQLTUNE.REPORT_SQL_MONITOR(
  sql_id=>...,
  session_id=>...,
  type=>'TEXT',
  report_level=>'ALL')
from dual;


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Somewhat confused...

A reader, June 29, 2020 - 2:34 am UTC

Thanks Connor,

I appreciate the ideas.

However, I am not 100% sure I understand. In the 'EXECS' column are you comparing the increase in numbers and then looking at the elapsed time? The 5000 is the rows estimate? Also, where is the script you are using as a wrapper, can you share?

I think the second approach is useful, i just a bit of help in seeing how you are deriving or estimated 'time to complete'.

Thank you again.
Connor McDonald
June 29, 2020 - 6:03 am UTC

Yes. Obviously in my case, it is easier because I *know* that the total number of execs will be 5000 for that particular part of the query, so I can use current elapsed time and the ratio of execs so far to 5000 as an estimate.

In a true, you'd be looking for an element that continues to rise that can be mapped back to some data you know about. Its not perfect i concede.

For sql tune, I just look for the SQL_ID of an active session, eg

set pagesize 0 echo off timing off linesize 1000 trimspool on trim on long 2000000 longchunksize 2000000 feedback off

variable sql_id varchar2(20)
begin
:sql_id := null;

select sql_id
into :sql_id
from
( select sql_id
from v$session
where status = 'ACTIVE'
and last_call_et > 2
and username = user
order by last_call_et desc
)
where rownum = 1;
end;
/

select DBMS_SQLTUNE.REPORT_SQL_MONITOR(
sql_id=>:sql_id,
type=>'TEXT',
report_level=>'ALL')
from dual;


V$SESSION_LONGOPS deprecated?

David D., July 24, 2020 - 2:25 pm UTC


Hello Connor,

I've read that V$SESSION_LONGOPS was deprecated by Oracle and replaced by V$SQL_MONITOR. Can you confirm because I find nothing on the Net.

Have a nice day

David D.
Connor McDonald
July 27, 2020 - 2:17 am UTC

I don't think its deprecated, but I know that (assuming I have the license) I would use V$SQL_MONITOR in preference - much better information.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.