Prefetching of LOB Data

[a-s-z]

Hi,

we have performance issues using our application over a vpn link with high latency, since a few tables contain lob columns with small lobs.

Oracle 11g has a new feature (Lob Data Prefetch), improving the lob performance with small lobs.

Is it possible to implement/use this feature in Odac (oci mode or direct mode)?

Best regards,
Andre

[AlexP]

Hello,

For the time being, this functionality is not implemented in ODAC, we will consider the possibility to add this feature to the next ODAC versions. Presently, you can use the DeferredLobRead option. If this option is set to True (False by default), then LOB data won't be read out on opening DataSet, but they will be retrieved directly when referring to a particular record, that allows to reduce memory usage when working with LOB data.

[a-s-z]

Hi,

thanks for the answer.

For the time being, this functionality is not implemented in ODAC, we will consider the possibility to add this feature to the next ODAC versions. Presently, you can use the DeferredLobRead option. If this option is set to True (False by default), then LOB data won't be read out on opening DataSet, but they will be retrieved directly when referring to a particular record, that allows to reduce memory usage when working with LOB data.

I am using DeferredLobRead already, but it won't help in my situation. Memory is not the issue but time. I have some data in a grid view e.g. 1500 records containing 3 clob fields. This will result in at least 4500 lob requests and since the link has high latency, the performance will decrease much. With the new 11g feature, I think there would be only a few extra requests since most clob data is short (< 2000 chars).

When using OCI mode, the changes should not be much since you only have to set some field attributes with OCI. Do you have some hints where I could patch the odac sources for oci mode?


Best regards,
Andre

[AlexP]

Hello,

All methods to be modified for this feature support are declared and implemented in the OraClasses.pas module. You can try yourself modifying the needed methods to support this feature. If you encounter any issues, contact us, and we will try to help you.

[a-s-z]

Hi,

All methods to be modified for this feature support are declared and implemented in the OraClasses.pas module. You can try yourself modifying the needed methods to support this feature. If you encounter any issues, contact us, and we will try to help you.

I have extended the current implementation. There are only a few modifications, since the new features do not require any change in fetching the lob. You have to set two attributes when describing the column to activate lob prefetch.

The code below has to be called every time when OraHome gets changed, since OCI functions will be reassigned eventually. Setting default prefetch for session (SetDefaultPrefetchSize) is not possible without changing sources, since the required member is declared private.

Code: Select all

uses
  Windows,
  SysUtils,
  CRTypes,
  OraCall;

const
{*--------- Attributes related to LOB prefetch------------------------------ *}
  OCI_ATTR_DEFAULT_LOBPREFETCH_SIZE    = 438;   {* default prefetch size *}
  OCI_ATTR_LOBPREFETCH_SIZE            = 439;   {* prefetch size *}
  OCI_ATTR_LOBPREFETCH_LENGTH          = 440;   {* prefetch length & chunk *}

var
  OraCallOCIDefineByPos         : _OCIDefineByPos;
  FDefaultLobPrefetchSize       : Integer = 1000;

function GetDefaultLobPrefetchSize: Integer;
begin
  Result := FDefaultLobPrefetchSize;
end;

procedure SetDefaultLobPrefetchSize(const Value: Integer);
begin
  FDefaultLobPrefetchSize := Value;
  if OraCall.LoadedOCI then
  begin
    if FDefaultLobPrefetchSize <= 0 then
      DeactivateLobPrefetch
    else
      ActivateLobPrefetch;
  end;
end;

function OCIDefineByPosLobPrefetch(stmtp: pOCIStmt; var defnpp: pOCIDefine; errhp: pOCIError;
    position: ub4; valuep: IntPtr; value_sz: sb4; dty: ub2;
    indp: IntPtr; rlenp: pub2; rcodep: pub2; mode: ub4): sword; cdecl;
var
  prefetch_size: ub4;
  prefetch_length: boolean;
  res: sword;
begin
  Result := OraCallOCIDefineByPos(stmtp, defnpp, errhp,
    position, valuep, value_sz, dty,
    indp, rlenp, rcodep, mode);
  if Result <> OCI_SUCCESS then
    Exit;
  if dty in [ SQLT_BLOB, SQLT_CLOB] then
  begin
    {* Set prefetch length attribute *}
    prefetch_length := TRUE;
    res := OraCall.OCIAttrSet1(defnpp,  OCI_HTYPE_DEFINE,
            @prefetch_length  {* attr value *},
            0,
            OCI_ATTR_LOBPREFETCH_LENGTH  {* attr type *},
            errhp );
    {* Override the default prefetch size to FDefaultLobPrefetchSize *}
    prefetch_size := FDefaultLobPrefetchSize;
    if prefetch_size > 0 then
    begin
      res := OraCall.OCIAttrSet1(defnpp,  OCI_HTYPE_DEFINE,
              @prefetch_size                             {* attr value *},
              0                  {* restricting prefetch size to be ub4 max val *},
              OCI_ATTR_LOBPREFETCH_SIZE                            {* attr type *},
              errhp);
    end;
  end;
end;

function SetDefaultPrefetchSize(stmtp: pOCISession; prefetch_size: ub4; errhp: pOCIError): sword;
begin
  if OCIVersion < 11100 then
    Exit(OCI_ERROR);
  Result := OraCall.OCIAttrSet1(stmtp,  OCI_HTYPE_SESSION,
          @prefetch_size                     {* attr value *},
          0                                  {* attribute size; not required to specify *},
          OCI_ATTR_DEFAULT_LOBPREFETCH_SIZE  {* attr type *},
          errhp);
end;

procedure ActivateLobPrefetch;
begin
  if FDefaultLobPrefetchSize <= 0 then
    Exit;

  try
    if not LoadedOCI then
      LoadOCI;

    if VersionStrToWord(OCIVersionSt) < 11100 then
      raise Exception.CreateFmt('Needs at least OCI version 11.1.0.0 (current %s).', [ OCIVersionSt ]);
    if @OraCallOCIDefineByPos <> @OraCall.OCIDefineByPos then
    begin
      OraCallOCIDefineByPos := @OraCall.OCIDefineByPos;
      OraCall.OCIDefineByPos := @OCIDefineByPosLobPrefetch;
    end;
  except
    on e: Exception do
      Windows.OutputDebugString(e.Message);
  end;
end;

procedure DeActivateLobPrefetch;
begin
  if Assigned(OraCallOCIDefineByPos) then
  begin
    if @OraCallOCIDefineByPos = @OraCall.OCIDefineByPos then
      OraCall.OCIDefineByPos := OraCallOCIDefineByPos;
    OraCallOCIDefineByPos := nil;
  end;
end;

Maybe you want to include this feature in one of the next odac versions, e.g. with a new session option LobPrefetchSize?

Best regards,
Andre

[AlexP]

Hello,

Thank you for the sample, we will check your implementation and try to add this feature in one of the next versions. For the time being, we are preparing to release the new version (planned for the next week), and, unfortunately, we have no more time to add this feature to this version.

[a-s-z]

Hi Alex,

Thank you for the sample, we will check your implementation and try to add this feature in one of the next versions. For the time being, we are preparing to release the new version (planned for the next week), and, unfortunately, we have no more time to add this feature to this version.

What is the status of this feature?

Best regards,
Andre

[AlexP]

Hello,

These features are included to our ODAC development roadmap, however, their implementation is not planned for the nearest future.

[AlexP]

We have already supported this feature. This functionality will be included in the next release. To test behavior we can send you a night build (for it send your license number and IDE version to support*devart*com).