My LOBs aren't getting released...?

[aehimself]

With the new LobCache enum I did some testing. Memory usage was checked in Task Manager (I know, I know... lazy to add it to my code directly), results are the following:

Before opening the CLOB query:
33,6 MB, nothing special

After opening CLOB query:
71,4 MB, nothing special

After scrolling through dataset without accessing anything:
219 MB, 27427 TZOracleClob objects created, with the total size of 6,5 MB (just the size of TZOracleClobs)

DeLeaker can make a diff between two snapshots, so I made an allocation snapshot after opening and scrolling through the query. Result is the following:

scrolldiff.png

All TZOracleclob objects were created at ZDbcOracleResultSet : 2333 (TZOracleAbstractResultSet.GetBlob)

Code: Select all

      SQLT_CLOB: with TZOracleColumnInfo(ColumnsInfo[ColumnIndex{$IFNDEF GENERIC_INDEX}-1{$ENDIF}]) do
          Result := TZOracleClob.Create(FOracleConnection, PPOCIDescriptor(P)^, CharSetForm, csid, FOpenLobStreams);

Oracle 12.1.0, client version: 19.8.0, database access component version: 8.0.0-d25d397bf.

I also think I managed to mess up my git repository as today after refreshing opening is blazing fast (as it should be) and nothing is preallocated after opening the query.

[aehimself]

@Mark,

Using your query my results are the same:

Before open:
31,6 MB, nothing special

After open:
69,5 MB, nothing special

After scrolling through dataset without accessing anything:
95,7 MB, 5000 TZOracleClob objects created, with the total size of 1,2 MB (just the size of TZOracleClobs)

4999 TZOracleClob objects created during scrolling through the dataset

[aehimself]

I forgot something really important, i'm not using FetchAll, not asking for recordcount (which calls fetchall effectively). My code also runs in a thread if it makes any difference.

[MJFShark]

Those settings are the same as mine (although for my test I'm not threading.) I don't *think* any of my changes made any functional change in the way things work. I did a "one to one" conversion from the old CachedLobs setting to the new LobCacheMode setting. I've gone back to older commits and not seen any changes in my testing. Btw, I'd love to be proven wrong on this, as I hate a "coding mystery" lol.

The one thing that did change (of course) is how the setting is set. If you had "CachedLobs=True" set before, it will now be false (if you don't replace it with "LobCacheMode=OnLoad". I know it's a long shot, but is there any way that this affects your code?

You mentioned that your test shows that 5000 TZOracleClob objects are created, which seem to me to be correct as far as I know? I'm under the assumption that one is created "per clob per record" and cached in the buffer list. Was that not true in an earlier version? I've tested and it seems to not have changed (again, I'd love to be wrong about this!) Using an older commit do you see different results for that?

-Mark

[aehimself]

I don't *think* any of my changes made any functional change in the way things work. I did a "one to one" conversion from the old CachedLobs setting to the new LobCacheMode setting.

Chill, dude :) There was one point when I thought it is making a difference but probably I messed something up in my checkout as today I got different results than yesterday :)

You mentioned that your test shows that 5000 TZOracleClob objects are created, which seem to me to be correct as far as I know? I'm under the assumption that one is created "per clob per record" and cached in the buffer list. Was that not true in an earlier version? I've tested and it seems to not have changed (again, I'd love to be wrong about this!) Using an older commit do you see different results for that?

It is understandable that they are created and no, this behavior did not change. What I have an issue with is that they remain alive even if we disabled caching. Let's face it - if you downloaded all data and just scrolling through a dataset it makes no sense for your application to consume more and more memory. In the live environment where the tool was processing millions of records, memory consumption was about 3 GB... this renders a low-spec PC out of memory quickly.

Summary: LOBs are getting released correctly, memory usage is growing because of the LOB "descriptors".

[MJFShark]

That's great new! Mystery solved and I'll chill with a brewsky later! I must have misunderstood some of this thread lol. I agree on what you say about the lobs IF the dataset is a one way, firehose type cursor. I'm not sure that's possible in the current architecture (maybe using dbc directly instead of the dataset interface which I don't do.) On another note I'd love to figure out how to get clobs to be faster on Oracle. I've played around with the prefetch settings and different character sets and stuff with no real improvements.

-Mark

[EgonHugeist]

Hi,

yet i have not enough time for the forum guys, my appologies for that. So just some quick answers: Glad to hear Mark's implementation did not cause any unwanted side effects. The more NICE job, Mark! Thank you.

For the record: A "Firehose" cursor can be acquired by using TZReadOnlyDataSet in Unidirectional mode. Usually that's lightning fast (except libpg/mySQL+StroreResult(use option DSProps_UseResult to get the FO+RO cursor) api). Hope you know that DBGrid components do not work with forward only cursors.

The Forwardonly+ReadOnly cursor type is default on ZDBC so should be first choice if someone implements an ORM or data-storing ObjectLists. The mORMot framework f.e. uses ZDBC only because of the TDataSet design performance bottleneck. I wrote my own ZDBC direct ORM which is again faster than the mORMot TSQLRecord idea.. however jfyi..

[aehimself]

Holy mother of sweet what the hell just happened?!

Setting the dataset to unidirectional did not just make the memory growth disappear (or really small) but increased the processing speed from 3-20 records per second to 30-50...?!

The more you know. I do consider unidirectional as a solution.

@Michael,
I understand that the LOB "descriptors" are required and therefore normal to exist, but wouldn't it make sense to free them up if we navigate away while not caching?

[aehimself]

@ Michael,

Is unidirectional mode working correctly in Zeos?
I have a TZReadOnlyQuery which is NOT connected to any DB aware components, loads 4 fields. Then when I am cycling through it like...

Code: Select all

   While Not InstitutesQuery.Eof Do
    Begin
     s := InstitutesQuery.FieldByName('NAME').AsString;

     InstitutesComboBox.Items.Add(s);
     InstitutesQuery.Next;
    End;

And it throws an exception:

EZSQLException was raised with the message Operation is not allowed in FORWARD ONLY mode

Stack trace:
0094a148 myapp.exe ZDbcResultSet 2594 TZAbstractResultSet.MoveAbsolute
00eb7fa3 myapp.exe ZAbstractRODataset 2528 TZAbstractRODataset.GetActiveBuffer
00ecd871 myapp.exe ZAbstractRODataset 9623 TZUnicodeStringField.IsRowDataAvailable
00ecd6c5 myapp.exe ZAbstractRODataset 9578 TZUnicodeStringField.GetAsString

I already had this when trying to extract a CLOB field with query.FieldByName.AsBytes but I thought .AsBytes is not supported in unidirectional. But .AsString...?

Edit: Nvm. It only happens if the .Open is called in a background thread while the query itself is on a form. Still, it'd be nice to know why...

[aehimself]

Opening a new thread. I think it's a bug in Zeos.

[EgonHugeist]

@Michael,
I understand that the LOB "descriptors" are required and therefore normal to exist, but wouldn't it make sense to free them up if we navigate away while not caching?

I do not see a regression here. All escriptors should be freed is the descriptors have been copied. See TZAbstractOracleBlob.FreeOCIResources.

Is unidirectional mode working correctly in Zeos?

Yes i'm sure..