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)
jdorlon
Posts: 202
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: 1822
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: 1822
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: 202
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: 1822
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: 202
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

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

Re: Memory not released after closing TSmartQuery with LOBs

Post by MaximG » Fri 18 Jun 2021 11:43

We are still sure that the issue is on the Oracle side, because no error occurs in the Direct mode. We tried to contact Oracle regarding this, no response received yet.

cis-wurzen
Posts: 75
Joined: Tue 04 Jan 2005 10:26

Re: Memory not released after closing TSmartQuery with LOBs

Post by cis-wurzen » Mon 30 Aug 2021 09:55

Still having this issue with 19.12.0.0.210720 patch.

Any news from Oracle?

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

Re: Memory not released after closing TSmartQuery with LOBs

Post by MaximG » Wed 08 Sep 2021 08:57

Hi there!

Thank you for your question!
Unfortunately, we have not received any progress information from Oracle Support on this hotfix.
We hope that the required changes will be available in the next version of OracleClient.

raulk89
Posts: 3
Joined: Tue 09 Nov 2021 11:01

Re: Memory not released after closing TSmartQuery with LOBs

Post by raulk89 » Tue 09 Nov 2021 11:08

Hi

Is there any update regarding the issue..?
Have you tried client 21.3 version ?

cis-wurzen wrote: Mon 30 Aug 2021 09:55 Still having this issue with 19.12.0.0.210720 patch.
I believe you are referring to database release update here (19.12.0.0.210720).
But the problem here, as I understood is the client version.
19.12 client is latest for 19 at the moment. Haven't tried it yet though.

E: I can confirm, 19.12 nor the 21.3 client does not make a difference here. Same issue.

Regards
Raul

cis-wurzen
Posts: 75
Joined: Tue 04 Jan 2005 10:26

Re: Memory not released after closing TSmartQuery with LOBs

Post by cis-wurzen » Tue 09 Nov 2021 13:46

Patches are also for client available/applicable, like p32832237_190000_WINNT.zip for 32 bit Windows.
Replaces oci.dll and other files.

Server version doesn't matter. Got OCI-21500 [kgepop: no error frame to pop to] also with 19.12 Client and 11.2 server,


19.13.0.0.211019 for Windows should arrive soon. I'll test.


Devart should fix this with Oracle together.

cis-wurzen
Posts: 75
Joined: Tue 04 Jan 2005 10:26

Re: Memory not released after closing TSmartQuery with LOBs

Post by cis-wurzen » Tue 23 Nov 2021 15:59

19.13.0.0.211019 oci.dll (1397248 bytes, 22.10.2021 00:45) still broken.


OCI-21500: internal error code, arguments: [kgepop: no error frame to pop to], [], [], [], [], [], [], []
OCI-21503: program terminated by fatal error
OCI-04030: out of process memory when trying to allocate 16336 bytes (koh-kghu sessi,alloc lob locator)

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

Re: Memory not released after closing TSmartQuery with LOBs

Post by MaximG » Sun 28 Nov 2021 19:13

We are still waiting for resolution from Oracle

cis-wurzen
Posts: 75
Joined: Tue 04 Jan 2005 10:26

Re: Memory not released after closing TSmartQuery with LOBs

Post by cis-wurzen » Thu 19 May 2022 11:10

32 bit Patch 19.15.0.0.220419 is now available.

No fix.

We (and our clients) are still forced to use the 18.3 client, even though the server version is 19 or 21.

raulk89
Posts: 3
Joined: Tue 09 Nov 2021 11:01

Re: Memory not released after closing TSmartQuery with LOBs

Post by raulk89 » Mon 23 May 2022 06:42

MaximG wrote: Sun 28 Nov 2021 19:13 We are still waiting for resolution from Oracle
Any update from oracle..?
It has been quite a while.

Regards
Raul Kaubi

Post Reply