Script query time

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Fri, 04 Aug 2006 6:30 pm

Guys, someone would have a script where you show the time remaining to complete a given DML/DDL command and the user that is executing that command?

Thanks, Everton
evertonsims
Location: Brasilia-DF

Everton Sims

Poston Wed, 09 Aug 2006 11:11 am

How long will it take, has no way of knowing. But you can see what SQL so running through this script :
Code: Select all
set linesize 1000;
COLUMN OSUSER   FORMAT A15;
COLUMN USERNAME FORMAT A15;
COLUMN TERMINAL FORMAT A15;
COLUMN SQL_TEXT FORMAT A60;
SELECT OSUSER,
       USERNAME,
       TERMINAL,
       SQL_TEXT
FROM V$SESSION, V$SQL
WHERE V$SESSION.SQL_ADDRESS = V$SQL.ADDRESS
AND V$SESSION.STATUS = 'ACTIVE'
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Thu, 10 Aug 2006 9:45 am

Q lack the time, in fact has no way of knowing.
Only the total time q took to run through the set timing on before running the sqlplus command.
ARF
Location: Criciúma - SC

Poston Fri, 01 Sep 2006 3:01 pm

see if this code you help
Code: Select all
column  sid         format   a10    heading 'Sid/Serial'
column  operation   format   a11    heading 'Acao'
column  object      format   a35    heading 'Objeto'
column  executado   format   99999999 heading 'BLK|Lidos'
column  total       format   99999999 heading 'BLK|Total'
column  pct         format   990.90   heading 'PCT(%)'
column  rate        format   a08      heading 'Rate|Mb/Min'
column  l_update    format   a11    heading 'Dt Start'
column  t_elap      format   a11 heading 'Elapsed|DD:HH:MI:SS'
column  t_remain    format   a11 heading 'Remaining|DD:HH:MI:SS'

column  unidade     format   a3     heading 'Uni'

accept SesID prompt 'Informe Sid: '

select to_char(sid) || ',' || ltrim(to_char(serial#)) sid,
       decode(opname,'Hash Join',    'Hash Join',
              'Index Fast Full Scan','Index Scan',
              'Sort Output',         'Sort Output',
              'Sort/Merge',          'Sort Merge',
              'Table Scan',          'Table Scan',
              '-') operation,
       target object,
       sofar executado,
       totalwork total,
       trunc((sofar/totalwork)*100,2) pct,
       to_char(60*sofar*8192/(24*60*(last_update_time - start_time))/1024/1024/60, '9990.90') Rate,
--       decode(units,'Blocks','Blk','-') unidade,
       to_char(start_time, 'DD/MM HH24:MI') l_update,
       trunc(elapsed_seconds/86400)|| ':'
            || to_char(to_date(mod(elapsed_seconds,86400), 'SSSSS'), 'HH24:MI:SS') t_elap,
       trunc(time_remaining/86400)|| ':'
            || to_char(to_date(mod(time_remaining,86400), 'SSSSS'), 'HH24:MI:SS') t_remain
from v$session_longops
where time_remaining > 0
  and sid like nvl('&sesid','%')
;
cacildoborges
Location: Sao Paulo

Poston Fri, 01 Sep 2006 4:24 pm

Very good!
Honestly, I didn't know it was possible to predict this. Congratulations for the script!
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered


  • See also
    Replies
    Views
    Last Post


Return to SQL

Who is online

Users browsing this forum: No registered users and 3 guests