Execution Time

[jmagdziarz]

Is it possible to get a running execution time in PL/SQL Developer? Perhaps similar to that in SQL Query Analyzer of SQL Server fame...

 

[CTzen]

SQL & Command windows have execution time in status bar.

 

[jmagdziarz]

No, I mean a running time, not a final time. So you can see how long a query has been running for.

 

[Marco Kalter]

You cannot get this information while the query is running. This is on the list of enhancement requests though.

 

[braykov]

... or see how much time has left.

 

[CTzen]

There are several things one can do to get current (or close enough) execution time, until it's implemented in PLD.

1. In Command Window run set time on. It will display current time in SQL prompt.

2. Querying v$session_longops for SQL statements that run longer then 6 seconds .

3. Querying last_call_et for active sessions from v .

 

[Joachim Rupik]

or, as usual , you can use Browser Extender and do it yourself. Below is an extension which will show in window status bar the following message:

Executing... [n]s

where n is elapsed time is seconds.

Cut and Paste then save to a file with .be extension and load in Browser Extender.

PL/SQL Developer Browser Extender Command

[MAIN]
NAME=Show elapsing time
CAPTION=Execute and show elapsing time...
OTYPE=commandwindow,procedurewindow,sqlwindow,testwindow
OOWNER=%
ONAME=%
SEPARATOR_ABOVE=N
SEPARATOR_BELOW=N
LOAD_AND_EXECUTE=N
LOAD_COMMAND=N
LOAD_AFTER=REPLACE
LOAD_WIN_TYPE=1
EXECUTE_AND_CLOSE=Y
PRIVILEGE_REQ=N
PRIVILEGE=
ORACLE_VERSION_REQ=N
ORACLE_VERSION=7.0.0
ONCLICK_CONFIRM=N
ONCLICK_CONFIRM_MSG=
ONCLICK_SPLASH=N
ONCLICK_SPLASH_MSG=
ONCLICK_SPLASH_MSG_BL=N
ONCLICK_SPLASH_MSG_AFTER=
ONCLICK_SPLASH_DELAY=0
ONCLICK_IGNORE_EXCEPTIONS=Y
ONPOPUP_IGNORE_EXCEPTIONS=Y

ONCLICK_CODE_TYPE=4
ONCLICK_EXTERNAL_CODE_FILE=
[ONCLICK]
program ShowElapsingTime;
{.$DEFINE DEBUG}                         // enables debug mode and related debug sub-items
const
  { Messages constants }
  WM_USER = 00;

  SB_SETTEXTA = WM_USER+1;
  SB_SETTEXTW = WM_USER+11;
  SB_SETTEXT  = SB_SETTEXTA;

  SB_GETPARTS = WM_USER+6;
  SB_GETTEXTA = WM_USER+2;
  SB_GETTEXTW = WM_USER+13;
  SB_GETTEXT  = SB_GETTEXTA;

  SB_GETTEXTLENGTHA       = WM_USER+3;
  SB_GETTEXTLENGTHW       = WM_USER+12;
  SB_GETTEXTLENGTH        = SB_GETTEXTLENGTHA;

  { MessageBox() Flags }
  MB_OK = ___SNIPPET___000000;
  MB_ICONASTERISK = ___SNIPPET___000040;

var
  sb_CurWinHandle, sb_CurWinStatusBarHandle : HWND;
  sb_Part : Integer;
  sb_TextA : String;
  sb_TextW : WideString;
  i: Integer;
  CurText: String;
  ElapsedSeconds: Integer;

  Timer : TTimer;

  function FindWindowEx(hwndParent: HWND; hwndChildAfter: HWND; lpszClass: PChar; lpszWindow: PChar): Longint; external 'FindWindowExA@user32.dll stdcall';
  function SendMessage(hWnd: HWND; Msg: Longint; wParam: Longint; lParam: PChar): Longint; external 'SendMessageA@user32.dll stdcall';
  function SendMessageW(hWnd: HWND; Msg: Longint; wParam: Longint; lParam: PChar): Longint; external 'SendMessageW@user32.dll stdcall';
  function MessageBox(Wnd: HWND; Text: PChar; Caption: PChar; Typ: Longint): Longint; external 'MessageBoxA@user32.dll stdcall';

  function ANSI2Wide(AString: String): WideString;
     var i: Integer;
  begin
      Result := '';
      For i := 1 to Length(AString) do
        begin
          Result := Result + AString\{i\} + #0;
        end;
  end;

  procedure OnTimer(Sender: TObject);
  begin
    for i:=0 to 255 do
      sb_TextA := sb_TextA + chr(0);

    SendMessage(sb_CurWinStatusBarHandle,SB_GETTEXT,sb_Part,sb_TextA);

    CurText := String(sb_TextA);
    If Pos('Executing',CurText) > 0 then
    begin
      ElapsedSeconds := ElapsedSeconds + 1;
      sb_TextW := ANSI2Wide('Executing... ' + IntToStr(ElapsedSeconds) + ' s');
      SendMessageW(sb_CurWinStatusBarHandle,SB_SETTEXTW,sb_Part,sb_TextW);
    end
    else begin
      Timer.Enabled := False;
      Timer.Free;
    end;
  end;

begin

  sb_CurWinHandle := IDE_GetChildHandle;

  { Starting version 710 the interface supports Unicode and Status bar Class name was changed }
  If  SYS_Version >= 710 then
    sb_CurWinStatusBarHandle := FindWindowEx(sb_CurWinHandle,0,'TControlStatusBar.UnicodeClass','')
  else
    sb_CurWinStatusBarHandle := FindWindowEx(sb_CurWinHandle,0,'TControlStatusBar','');

  { get the last panel index }
  sb_Part := SendMessage(sb_CurWinStatusBarHandle,SB_GETPARTS,0,'') - 1;

  for i:=0 to 255 do
    sb_TextA := sb_TextA + chr(0);

  IDE_Perform(paExecute);

  SendMessage(sb_CurWinStatusBarHandle,SB_GETTEXT,sb_Part,sb_TextA);

  ElapsedSeconds := 0;

  CurText := String(sb_TextA);
  If Pos('Executing',CurText) > 0 then
    begin
      Timer := TTimer.Create(nil);
      Timer.Enabled := false;
      Timer.Interval := 1000;
      Timer.OnTimer := @OnTimer;
      sb_TextW := ANSI2Wide('Executing... ' + IntToStr(ElapsedSeconds) + ' s');
      SendMessageW(sb_CurWinStatusBarHandle,SB_SETTEXTW,sb_Part,sb_TextW);
      Timer.Enabled := True;
    end;

end.

[ONPOPUP]

 

[jmagdziarz]

Is this still on the list...?

 

[Marco Kalter]

Yes