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: 157
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: 1581
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: 1581
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.

Post Reply