Exception when returning NULL from oracle database

jvillasantegomez · 2013-12-03 21:37:53

It happens that sometimes you want to return null from a select, this we do it to later have a unified interface on the client, so, you could easily do something like this:

SELECT one, two, NULL AS three FROM sometable;

But the problem here is that mORmot raises an exception (understandable) when you do stuff like that. So, i changed the following lines to let mormot return some null values from a db query:

Line: 2686 
    (original) => if ColumnValueDBSize=0 then    
    (change) => // if ColumnValueDBSize=0 then
  Line: 2687 
    (original) => raise ESQLDBOracle.CreateFmt('Column "%s": size=0',[ColumnName]);
    (change) => // raise ESQLDBOracle.CreateFmt('Column "%s": size=0',[ColumnName]);

only commented out those (2686 and 2687 on latest commit) two lines

best regards...

mpv · 2013-12-04 12:15:05

AB, we also found CRITICAL error in TSQLDBOracleStatement. It happens in case statement need to fetch BLOB field (CLOB in my case) and return zero record's.
In TSQLDBOracleStatement.Prepare in case of HasLob = true alocated 100 POCILobLocator. When if in TSQLDBOracleStatement.FetchTest in case of no data

if fRowFetched=0 then begin
    fRowCount := 0;

And when in TSQLDBOracleStatement.FreeHandles fRowCount = 0 and no DescriptorFree for allocated POCILobLocator happens.

for j := 1 to fRowCount do begin
          if PLOB^<>nil then begin
            if OCI.DescriptorFree(PLOB^,OCI_DTYPE_LOB)<>OCI_SUCCESS then
              SynDBLog.Add.Log(sllError,'Invalid Blob Release');
            PLOB^ := nil;
          end;
          inc(PLOB);
        end;

Result is server memory leak (in OLI dll) and Oracle server crash (shared memory not initialized) after couple of queries (in my case after 100 000)
Please, fix it ASAP. I have no idea how to do it elegantly.
Ticket - http://synopse.info/fossil/tktview?name=73aec05724

ab · 2013-12-04 13:15:40

@jvillasantegomez
You are right: I did not expect such statements, but it does make sense for them to occur!
I'm sometimes overprotecting my code... But from my little experiment, the sooner a potential error is identified, the better.
Should be fixed by http://synopse.info/fossil/info/5f7f6d8ae0
Thanks for the feedback.

@mpv
This was indeed an awful issue!

Should be fixed now by http://synopse.info/fossil/info/afecbaa2f6
Thanks for tracking the problem down to the faulty line!
(ASAP fix = 1 hour - not bad, isn't it?)

mpv · 2013-12-04 15:08:40

@AB Many-Many thanks.
It took me more time to move to a newer mORMot version (I'm on two month later version) than what you have to fix the problem
I test carefully - no leak now!
Once more - many thanks!

Another problem exists. I did not write about it because it is not tested extensively enough to localize - if fUseCache for oracle is enabled I also discover some kind of leak( near 150 byte for one requests). I turn fUseCache = false in my descendatn. Server became 30% slower but stable. I have to make sure that it is not in my code and write details.

ab · 2013-12-04 16:30:37

fUseCache will enable also server-side cache on Oracle.

Perhaps using OCI_STMTCACHE_DELETE instead of OCI_DEFAULT in OCI.Check(OCI.StmtRelease(fStatement,fError,nil,0,OCI_DEFAULT),fError) ?

  { Enable OCI Server-Side Statement Caching }
  OCI_STMT_CACHE       = $40;
  OCI_STMTCACHE_DELETE = $10;

But documentation is a bit vague about it.
I'm not sure I fully understand every point...
See https://forums.oracle.com/thread/529189

Or perhaps we need to use OCI_STMTCACHE_DELETE=OCI_STRLS_CACHE_DELETE only in case of a failing statement.
See http://gcov.php.net/PHP_5_3/lcov_html/e … c.gcov.php
I've forced release for an incorrect statement, when Oracle server-side statement caching is enabled.
See http://synopse.info/fossil/info/06814d1585

You can at least do your testing with fUseServerSideStatementCache := false in TSQLDBOracleConnection.NewStatementPrepared() do see if the problem comes from the client-side statement cache, or the server-side statement cache...
I doubt OCI_STMTCACHE_DELETE would do anything usable, but flush the cache so would deny any speed benefit...

mpv · 2015-10-15 07:19:10

The problem with memory leak on Oracle if UseCache=true is still present, as reported in http://synopse.info/forum/viewtopic.php?pid=18078
I rewrite SynDBOracle to use OCILogon2 instead of OCISessionBegin, OCIStmtFetch2 instead of OCIStmtFetch, apply OCI_PREP2_CACHE_SEARCHONLY in Prepare to check statement already in statement cache but nothing help
I continue to explore the issue.

mpv · 2015-10-15 07:22:37

I'm sure this is a client-side problem, on the server level number of "cached" statements is equal to StatementCacheSize and server-side memory is not grow. If I replace OCI_DEFAULT to OCI_STMTCACHE_DELETE during statement release, then no leak, but statement cache is also not work.

mpv · 2015-10-15 12:22:09

Ok, I re-implement this C Oracle statement cache sample https://github.com/hongfengsun/oracle-s … cdemostc.c on Delphi using low-level calls of TSQLDBOracleLib - as a result no memory leak in sample, so on the OCI everything is OK, on the TSQLDBOracleLib level all constants and functions are defined correctly.

mpv · 2015-10-15 15:06:53

YEEEEEEESSSSSSSSSSSSS!!!! I found the root of problem.
In case client side statement cache is enabled during 2nd call to cached statement we got the same statement handler as before (it is cached).
And when in procedure TSQLDBOracleStatement.SetColumnsForPreparedStatement; we try to retrieve statement columns information using

ParamGet(fStatement,OCI_HTYPE_STMT,fError,oHandle,i);

But if we fetch the same column metadata for every execution of a statement (statement is the same), then the program will leak memory unless we explicitly free the parameter descriptor https://docs.oracle.com/cd/B19306_01/ap … tm#i540956

I prepare the fix

ab · 2015-10-15 15:25:31

Great!!

I'll include your fix as soon as you post it.

Thanks!

mpv · 2015-10-15 15:57:52

Should be fixed now by http://synopse.info/fossil/info/a1346c6 … 71cbe1f3b4
During search for solution I read many manuals about client-side statement cache.
In fact this feature work as such:
1) We try to execute some statement, 'select 1 from dual'
2) OCI client calculate some kind of hash for our statement and send statement text and hash to server
3) server memorize hash and perform a soft-parse phrase of prepare for our statement text
4) next time we try to execute the same statement text OCI will found it in the client statement cache and send to server only statement hash
5) server bypass soft-parse phrase, network traffic is minimized. We discover x3 times more RPS for retrieve something by ID during intensive operation. Execution time down from 1.2ms to 0.2ms

Client side OCI statement cache use MRU technique, so we can cache almost all statements, not only select, but for any kind of statement
So, @AB, if you don't mind, I refactor a SynDBOrecle a little...

UPD. Statement cache use LRU algorithm

Last edited by mpv (2015-10-16 20:15:18)

ab · 2015-10-15 16:15:32

Thanks a lot!

Feel free to do some refactoring.
(just run the regression tests with Delphi 7/2007 and a Delphi 2009+ compiler to avoid compilation problems)

mpv · 2015-10-15 18:06:06

I will be very attentive. This is SynLog for the 30 min from one of my production (1000 HTTP request per minute and 260 database query per second):

Log started at: 13.10.2015 10:40:16
Events count: 6779705
Methods count: 1948785
Threads count: 25
Time elapsed: 00:34:18

Per event stats
---------------

Info: 36009
Debug: 1669562
Warning: 20693
Error: 263
Enter: 1948785
Leave: 1948790
Exception: 611
SQL: 471613
DB: 487380
HTTP: 32163
Server: 62215
Service call: 62933
User auth: 37359
Custom 1: 595
Custom 2: 734

I expect very serious boots after I turn on statement cache...
Your software makes miracles, thanks a lot!!!

Last edited by mpv (2015-10-15 18:09:58)

ab · 2015-10-16 07:14:58

Thanks for the commit!

Why did you change in http://synopse.info/fossil/info/a39ab48 … 73dbde6fe9

     Log(sllSQL,SQLWithInlinedParams,self,2048);

into

 Log(sllSQL,SQL,self,2048);

?

It is a feature, common to all features of the ORM, on other DB.
The log is about exact execution, not about high-level statistics: if you are concerned about query plan, you would rather use your own mechanism, as it does exist at ORM level.

But I've added a new TSQLDBOracleConnectionProperties.LogSQLWithoutValues property to allow what you need.
See http://synopse.info/fossil/info/9490178233

Thanks for sharing!

mpv · 2015-10-16 08:03:30

I change it, because developer copy SQL statement from log (including inline parameter) and try to get execution plane. Plane is OK.
But actually we execute query with ? and in this case sometimes plane is bad - execution plane is not the same for parametric and non-parametric query.
I'm tired of asking for parameterized queries before obtain execution plane, so decide to change logging..
For sure, LogSQLWithoutValues is more backward compatible way of this change. But this is also a way to very hard-to-reproduce developer mistakes.

mpv · 2015-10-26 12:39:25

In commit http://synopse.info/fossil/info/31031b8 … 8d4ff7b3d3 I change a little default behavoir of Oracle Client Side LRU statement cache.
First of all I add logging of cache HIT/miss, to help calculate StatementCacheSize.
The second - from my experiment it's better to ally cache for all types of statement, not only for select. Real life sample for update (1.18 vs 0.33ms):

debug 					Connection: "main", Dialect: "Oracle11"
debug 					Statemet cache miss
info  					StartTransaction for connection "mains"
debug 					P1: String(25)	UBA_USER_PWDCH_EVERYONE_1
debug 					P2: Int64		10
debug 					P3: Int64		3000000000113
 				 UPDATE uba_els SET   code = ?,   mi_modifyDate = CAST(SYS_EXTRACT_UTC(SYSTIMESTAMP) as date),   mi_modifyUser = ?   WHERE ID = ?
DB    				TSQLDBOracleStatement(0AB78320) 0 row(s) in 1.18ms

debug 					Connection: "main", Dialect: "Oracle11"
debug 					Statemet cache HIT
info  					StartTransaction for connection "mains"
debug 					P1: String(23)	UBA_USER_PWDCH_EVERYONE
debug 					P2: Int64		10
debug 					P3: Int64		3000000000113
 				 UPDATE uba_els SET   code = ?,   mi_modifyDate = CAST(SYS_EXTRACT_UTC(SYSTIMESTAMP) as date),   mi_modifyUser = ?   WHERE ID = ?
DB    				TSQLDBOracleStatement(0AB78320) 0 row(s) in 330us

Also I found good Oracle article about statement cache in WebLogic, but it is applicable for all OCI-based clients Arcticle is here

mORMot Open Source

#1 2013-12-03 21:37:53

Exception when returning NULL from oracle database

#2 2013-12-04 12:15:05

Re: Exception when returning NULL from oracle database

#3 2013-12-04 13:15:40

Re: Exception when returning NULL from oracle database

#4 2013-12-04 15:08:40

Re: Exception when returning NULL from oracle database

#5 2013-12-04 16:30:37

Re: Exception when returning NULL from oracle database

#6 2015-10-15 07:19:10

Re: Exception when returning NULL from oracle database

#7 2015-10-15 07:22:37

Re: Exception when returning NULL from oracle database

#8 2015-10-15 12:22:09

Re: Exception when returning NULL from oracle database

#9 2015-10-15 15:06:53

Re: Exception when returning NULL from oracle database

#10 2015-10-15 15:25:31

Re: Exception when returning NULL from oracle database

#11 2015-10-15 15:57:52

Re: Exception when returning NULL from oracle database

#12 2015-10-15 16:15:32

Re: Exception when returning NULL from oracle database

#13 2015-10-15 18:06:06

Re: Exception when returning NULL from oracle database

#14 2015-10-16 07:14:58

Re: Exception when returning NULL from oracle database

#15 2015-10-16 08:03:30

Re: Exception when returning NULL from oracle database

#16 2015-10-26 12:39:25

Re: Exception when returning NULL from oracle database

Board footer