[patch_done] Oracle Prefetch Rows

[CompsSQL]

When performing a "select *" query on an Oracle Table with a huge number of fields, I noticed that the query was really slow (several minutes). After contacting our DBA's, they told me to set the Prefetch Rows to a higher number. I could not find this option in ZeosLib (but I might be overlooking it of course), so I added it myself by changing the "PrepareOracleStatement" procedure in "zdbcoracleutils.pas". Simply add a call to "PlainDriver.AttrSet" with the OCI_ATTR_PREFETCH_ROWS option. Example code :

Code: Select all

procedure PrepareOracleStatement(PlainDriver: IZOraclePlainDriver;
  SQL: string; Handle: POCIStmt; ErrorHandle: POCIError);
var
  Status: Integer;
  PrefetchCount: ub4;
begin
  PrefetchCount := 100;
  PlainDriver.AttrSet(Handle, OCI_HTYPE_STMT, @PrefetchCount, SizeOf(ub4),
    OCI_ATTR_PREFETCH_ROWS, ErrorHandle);
  Status := PlainDriver.StmtPrepare(Handle, ErrorHandle, PAnsiChar(AnsiString(SQL)),
    Length(AnsiString(SQL)), OCI_NTV_SYNTAX, OCI_DEFAULT);
  CheckOracleError(PlainDriver, ErrorHandle, Status, lcExecute, SQL);
end;

With this option set to 100, the execution time of my query dropped from several minutes to a few seconds. Our DBA's could not see a performance decrease on the server side, so for me this was the perfect solution.
Of course, it is a bad idea to hardcode this in the source, but it would be nice to have this option as it can dramatically improve Oracle performance.

[EgonHugeist]

I'm sure this option isn't aviable in the sources. But we use other way's to solve some server-specials.

Example:

Add PrefetchCount=100 to the TZConnection.Properties.

and on

procedure TZOracleConnection.Open;
you can add this Plaindriver-call in to the sources after "real-connect".

I think calling this once must be enough or do you need it each times? (if it is so, i'll commit your patch otherwise it would be fine if you work the way out i told you. Attach a patch-file and i'll commit it for you..)

Best regards,

Egonhugeist

[CompsSQL]

I need this only once for only 1 specific Oracle DB, so your solution is more than enough for me. Thank you.
(sorry for the probably silly question, I am still a little new to Zeos)

[EgonHugeist]

CompsSQL,

I think you missunderstand me here.

The last patch (; in PWD/USR)i did for you, because i know that wasn't a easy job and breaks our testsuites. So i did it for you.

I'm interested in adding your additional patch it seems helpfull for other Oracle users too. But we can't do everything by our selves.

Now it's up to you to patch it. The best way to solve this special things i told you. So it would be very nice if you can work out the patch and attach *.patch file. Do you've experiences with TortoiseSVN? It makes such patches very easy..

Then i'll commit your patch..

Best regards

[EgonHugeist]

Another solution...

Question: Do you need this command really each time before executing this statement?

If it isn't so i can add your post directly after connection...

I'd commit your proposal after connect. Does it work like expected?

[mdaems]

EgonHugeist,
CompsSQL,

It would be better if this could be introduced in statement (ZQuery) options. That way it can be set for the queries that need it.
However, I just committed the patch to testing branch as it would only hurt in cases where not all records of the resultset are really fetched. Blocks of 100 records are not extremely big, so the positive effects are likely to be bigger than the negative ones.

Mark