Useful Queries

Predict the step run time

If you are using an OED (On ETL Driver), and assuming it has at least run some of the threads, you can predict the run time for that step with:-

WITH frst AS
 (SELECT oel.log_activity_group
    FROM pre_etl_owner.on_etl_log oel
   WHERE oel.log_id =
         (SELECT MAX(oel2.log_id)
            FROM pre_etl_owner.on_etl_log oel2
           WHERE oel2.log_activity_group != 'Monitor')),
ctx AS
 (SELECT /*+materialize */
   to_number(a.value) rpt
    FROM v$globalcontext a
   WHERE a.namespace = 'MIGRATION_CONTEXT'
     AND a.attribute = 'MGCTX_ROWS_PER_THREAD'),
concurrency AS
 (SELECT /*+materialize */
   to_number(a.value) con
    FROM v$globalcontext a
   WHERE a.namespace = 'MIGRATION_CONTEXT'
     AND a.attribute = 'MGCTX_CONCURRENCY'),
cnt AS
 (SELECT /*+materialize */
   o.partitions_in_set * (SELECT rpt FROM ctx) AS count_of_rows
    FROM on_etl_driver o
   WHERE rownum = 1),
/* OLD WAY  (SELECT +materialize COUNT(*) AS count_of_rows FROM on_etl_driver), */
avg_runtime_in_secs AS
 (SELECT /*+materialize */
   round(AVG(CAST(a.log_ts AS DATE) -
             CAST((SELECT b.log_ts
                     FROM pre_etl_owner.on_etl_log b
                    WHERE b.log_activity_group =
                          (SELECT frst.log_activity_group FROM frst)
                      AND b.log_entry =
                          'GENERATED : ' ||
                          ltrim(a.log_entry, 'TIDIED AWAY : ')) AS DATE)) * 24 * 60 * 60) AS secs
    FROM (SELECT /*+index(oel OEL_PK) */
           *
            FROM pre_etl_owner.on_etl_log oel
           WHERE oel.log_id >
                 (SELECT MAX(oel2.log_id) - 200
                    FROM pre_etl_owner.on_etl_log oel2)
             AND oel.log_activity_group =
                 (SELECT frst.log_activity_group FROM frst)
           ORDER BY oel.log_id DESC) a
   WHERE rownum < 100
     AND a.log_entry LIKE 'TIDIED AWAY : %'
     AND a.log_activity_group =
         (SELECT log_activity_group
            FROM (SELECT /*+index(oel OEL_PK) */
                   *
                    FROM pre_etl_owner.on_etl_log oel
                   WHERE oel.log_id >
                         (SELECT MAX(oel2.log_id) - 200
                            FROM pre_etl_owner.on_etl_log oel2)
                     AND oel.log_activity_group =
                         (SELECT frst.log_activity_group FROM frst)
                   ORDER BY oel.log_id DESC)
           WHERE log_entry LIKE 'JOB LAUNCHED%'
             AND log_id >
                 (SELECT MAX(oel2.log_id) - 200
                    FROM pre_etl_owner.on_etl_log oel2)
             AND rownum = 1)),
current_task AS
 (SELECT to_number(substr(ltrim(log_entry, 'TIDIED AWAY : mig_')
                         ,1
                         ,instr(ltrim(log_entry, 'TIDIED AWAY : mig_')
                               ,'_') - 1)) done_tasks
        ,log_activity_group
    FROM (SELECT * FROM on_etl_log ORDER BY log_id DESC)
   WHERE rownum = 1
     AND log_entry LIKE 'TIDIED AWAY : mig_%')
SELECT ct.log_activity_group
      ,round((count_of_rows / ctx.rpt) + 0.5) AS threads_in_task
      ,greatest(round(((count_of_rows / ctx.rpt) - ct.done_tasks)) - con
               ,0) AS remaining_threads
      ,concurrency.con AS concurrent_capacity
      ,avg_runtime_in_secs.secs average_thread_time_in_secs
      ,round((((((count_of_rows / ctx.rpt) - (ct.done_tasks - (con))) *
             avg_runtime_in_secs.secs) / concurrency.con) -
             avg_runtime_in_secs.secs) / 60
            ,2) mins_till_completion
  FROM cnt
 CROSS JOIN ctx
 CROSS JOIN current_task ct
 CROSS JOIN concurrency
 CROSS JOIN avg_runtime_in_secs
 WHERE (SELECT COUNT(*)
          FROM v$globalcontext a
         WHERE a.attribute LIKE 'MIG/_%' ESCAPE '/'
           AND a.value LIKE 'START%') > 0

You'll get a really nice output for example...

THREADS_IN_TASK  REMAINING_THREADS  CONCURRENT_CAPACITY  AVERAGE_THREAD_TIME_IN_SECS  MINS_TILL_COMPLETION
           1392                 81                    8                            5                  0.93