Memory not released after closing TSmartQuery with LOBs

Discussion of open issues, suggestions and bugs regarding ODAC (Oracle Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
jdorlon
Posts: 163
Joined: Fri 05 Jan 2007 22:07

Memory not released after closing TSmartQuery with LOBs

Post by jdorlon » Thu 04 Feb 2021 16:22

Hello,

If I select data that does not contain LOBs, using a TSmartQuery (unidirectional=false), I see memory usage increase as I fetch rows. This is expected. And when I close the TSmartQuery, memory is released.

However if I repeat the process with a dataset that has LOBs, memory is not released when the TSmartQuery closes.

Is this a bug?

My sample project is below:

Unit1.pas

Code: Select all

unit Unit1;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, OraCall, Data.DB, DBAccess, Ora, Vcl.StdCtrls, MemDS, OraSmart, Vcl.ExtCtrls,
  PSAPI;

type
  TForm1 = class(TForm)
    edUser: TLabeledEdit;
    edPassword: TLabeledEdit;
    cboDatabase: TComboBox;
    Label3: TLabel;
    sess: TOraSession;
    qry: TSmartQuery;
    btnConnect: TButton;
    Memo1: TMemo;
    procedure btnConnectClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

function CurrentProcessMemory: Cardinal;
var
  MemCounters: TProcessMemoryCounters;
begin
  MemCounters.cb := SizeOf(MemCounters);
  if GetProcessMemoryInfo(GetCurrentProcess, @MemCounters, SizeOf(MemCounters)) then
    Result := MemCounters.WorkingSetSize
  else
    RaiseLastOSError;
end;

procedure TForm1.btnConnectClick(Sender: TObject);
var
  idx: Integer;
  DB: String;
begin
  if (edUser.Text = '') or
     (edPassword.Text = '') or
     (cboDatabase.Text = '') then
    MessageDlg('Enter username, password, and database.', mtInformation, [mbOK], 0)
  else
  begin
    DB := cboDatabase.Text;
    idx := cboDatabase.Items.IndexOf(DB);
    if idx = -1 then
    begin
      cboDatabase.Items.Insert(0, DB);
      cboDatabase.ItemIndex := 0;
    end
    else if idx > 0 then
    begin
      cboDatabase.Items.Delete(idx);
      cboDatabase.Items.Insert(0, DB);
      cboDatabase.ItemIndex := 0;
    end;

    qry.Options.DeferredLobRead := True;
    qry.Options.ExtendedFieldsInfo := false;
    qry.Options.SetFieldsReadOnly := false;
    qry.OptionsDS.RawAsString := True;
    qry.Options.StrictUpdate := false; // needed to edit IOTs
    Ora.UseDefSession := False;

    OraCall.OCIUnicode := False; // init
    Sess.Options.UseUnicode := False; // init
    Sess.Options.UnicodeEnvironment := False; // init
    Sess.Options.UseOCI7 := False; // init

    Sess.Username := edUser.Text;
    Sess.Password := edPassword.Text;
    Sess.Server := cboDatabase.Text;
    Screen.Cursor := crHourglass;

    try                                   //  Format('%n', [CurrentProcessMemory/1]));
      Sess.Connected := True;
      Memo1.Lines.Clear;
      Memo1.Lines.Add('Connected.  Memory used: ' + IntToStr(Round(CurrentProcessMemory/1048576)) + ' Mb');
      Memo1.Lines.Add('Selecting non-lob data');
      qry.sql.add('select * from dba_source where rownum < 100000');
      qry.execute;
      while Not qry.eof do
      begin
        if qry.RecordCount mod 5000 = 0 then
          Memo1.Lines[Memo1.Lines.Count-1] := 'Selecting non-lob data...' + IntToStr(qry.RecordCount) + ' rows...';
        qry.Next;
      end;
      Memo1.Lines.Add('Data feched. Memory used: ' + IntToStr(Round(CurrentProcessMemory/1048576)) + ' Mb');
      qry.Close;
      Memo1.Lines.Add('Query closed. Memory used: ' + IntToStr(Round(CurrentProcessMemory/1048576)) + ' Mb');
      qry.SQL.Clear;
      qry.sql.add('select other_xml from sys.WRI$_SQLSET_PLAN_LINES where rownum < 20000 and other_xml is not null');
      Memo1.Lines.Add('Selecting lob data');
      qry.execute;
      while Not qry.eof do
      begin
        if qry.RecordCount mod 500 = 0 then
          Memo1.Lines[Memo1.Lines.Count-1] := 'Selecting lob data...' + IntToStr(qry.RecordCount) + ' rows...';
        qry.Next;
      end;
      Memo1.Lines.Add('Data feched. Memory used: ' + IntToStr(Round(CurrentProcessMemory/1048576)) + ' Mb');
      qry.Close;
      Memo1.Lines.Add('Query closed. Memory used: ' + IntToStr(Round(CurrentProcessMemory/1048576)) + ' Mb');
    except
      on e:Exception do
        MessageDlg(e.Message, mtError, [mbOK], 0);
    end;
    Screen.Cursor := crDefault;
  end;
end;

end.
Unit1.dfm

Code: Select all

object Form1: TForm1
  Left = 0
  Top = 0
  Caption = 'Form1'
  ClientHeight = 300
  ClientWidth = 635
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'Tahoma'
  Font.Style = []
  OldCreateOrder = False
  DesignSize = (
    635
    300)
  PixelsPerInch = 96
  TextHeight = 13
  object Label3: TLabel
    Left = 289
    Top = 4
    Width = 46
    Height = 13
    Caption = 'Database'
  end
  object edUser: TLabeledEdit
    Left = 8
    Top = 19
    Width = 121
    Height = 21
    EditLabel.Width = 22
    EditLabel.Height = 13
    EditLabel.Caption = 'User'
    TabOrder = 0
  end
  object edPassword: TLabeledEdit
    Left = 146
    Top = 19
    Width = 121
    Height = 21
    EditLabel.Width = 46
    EditLabel.Height = 13
    EditLabel.Caption = 'Password'
    PasswordChar = '*'
    TabOrder = 1
  end
  object cboDatabase: TComboBox
    Left = 289
    Top = 19
    Width = 226
    Height = 21
    Anchors = [akLeft, akTop, akRight]
    TabOrder = 2
  end
  object btnConnect: TButton
    Left = 8
    Top = 47
    Width = 120
    Height = 25
    Caption = 'Connect and test'
    TabOrder = 3
    OnClick = btnConnectClick
  end
  object Memo1: TMemo
    Left = 8
    Top = 94
    Width = 467
    Height = 188
    Lines.Strings = (
      'Memo1')
    TabOrder = 4
  end
  object sess: TOraSession
    Options.EnableNumbers = True
    LoginPrompt = False
    Left = 323
    Top = 55
  end
  object qry: TSmartQuery
    Session = sess
    FetchRows = 500
    Options.RawAsString = True
    Options.SetFieldsReadOnly = False
    Options.ExtendedFieldsInfo = False
    ObjectView = True
    Left = 291
    Top = 52
  end
end
Project1.dpr

Code: Select all

program Project1;

uses
  Vcl.Forms,
  Unit1 in 'Unit1.pas' {Form1};

{$R *.res}

begin
  Application.Initialize;
  Application.MainFormOnTaskbar := True;
  Application.CreateForm(TForm1, Form1);
  Application.Run;
end.


Thanks

John Dorlon

MaximG
Devart Team
Posts: 1625
Joined: Mon 06 Jul 2015 11:34

Re: Memory not released after closing TSmartQuery with LOBs

Post by MaximG » Fri 12 Feb 2021 14:14

Thank you for the information. We will investigate the described issue and let you know the results shortly.

MaximG
Devart Team
Posts: 1625
Joined: Mon 06 Jul 2015 11:34

Re: Memory not released after closing TSmartQuery with LOBs

Post by MaximG » Fri 19 Feb 2021 09:57

We've identified that a memory leak occurs when using Oracle Client 19c. Please use Oracle Client 18 (or earlier) or the Direct mode while we're working on the issue.

jdorlon
Posts: 163
Joined: Fri 05 Jan 2007 22:07

Re: Memory not released after closing TSmartQuery with LOBs

Post by jdorlon » Tue 02 Mar 2021 19:53

Hello,

Is this line in the release history for the new version 11.4.1 related to this problem?

* LOB read/write performance is improved

-John

MaximG
Devart Team
Posts: 1625
Joined: Mon 06 Jul 2015 11:34

Re: Memory not released after closing TSmartQuery with LOBs

Post by MaximG » Thu 04 Mar 2021 12:36

No, the latest version of ODAC (11.4.1) doesn't have any changes related to this issue since we're not sure that the memory leaks are caused by our components. We're still investigating it.

jdorlon
Posts: 163
Joined: Fri 05 Jan 2007 22:07

Re: Memory not released after closing TSmartQuery with LOBs

Post by jdorlon » Thu 15 Apr 2021 13:12

Hi Maxim,

What is the status of this? My customers are asking.

Thank you,

-John

Post Reply