Data returned to client bigger than sent with Stored proc

[Joachim]

Hi,

I use oracle database 11.2.0.3 on WIN2008 Enterprise.
Oracle client 11.2.0.3
Delphi XE, Odac 8.01

I use devart component TOraQuery and TOraStoredProc
In both these components I call the same stored procedure with 2 parameters.

These are figures I get in v$mystat
NAME;STAT#;PROG;INFO2;BYTES
bytes sent via SQL*Net to client;589;Delphi;qry;125495;
bytes sent via SQL*Net to client;589;Delphi;stp;907115;
bytes received via SQL*Net from client;590;Delphi;qry;255233;
bytes received via SQL*Net from client;590;Delphi;stp;264213;

It is amazing there is almost 4 times more bytes returned than sent.

I expected way less bytes to be returned to the client.
Is there some properties I can switch off this returning of information ?
The data stream needs to go to the oracle, not return to client !

This is sql i used to create tables / sequences for this test:

Code: Select all

create table STPQRY (a number, b number);

create procedure stpins (in_a in number, in_b in number) as 
begin
  insert into stpqry (a, b) values (in_a, in_b);
end;
/   

CREATE TABLE TESTDATA
(
  INFO   VARCHAR2(100 BYTE),
  STAT#  NUMBER,
  VALUE  NUMBER,
  DT     DATE,
  SEQNR  NUMBER,
  PROG   VARCHAR2(20 BYTE),
  INFO2  VARCHAR2(20 BYTE)
);

create sequence testd_seq start with 1;

And this is the Delphi code for this test:

Code: Select all

unit uMain;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ExtCtrls, StdCtrls, Ora, DB, MemDS, DBAccess;

type
  TfrmMain = class(TForm)
    sess: TOraSession;
    rgType: TRadioGroup;
    btnStart: TButton;
    lbInfo: TListBox;
    Timer1: TTimer;
    procedure btnStartClick(Sender: TObject);
  private
    fCountStp: Integer;
    fCountQry: Integer;
    procedure RunQuery;
    procedure RunStp;
    procedure AddStats(aTest, aSubTest, aProg: string);
  public
    constructor Create(AOwner: TComponent); override;
  end;

var
  frmMain: TfrmMain;

implementation

uses StrUtils;

{$R *.dfm}

procedure TfrmMain.AddStats(aTest, aSubTest, aProg: string);
var qry: TOraQuery;
begin
  qry := TOraQuery.Create(Self);
  try
    qry.SQL.Text :=
      'insert into testdata (seqnr, info, info2, prog, stat#, value, dt) '+
      'select testd_seq.nextval, :aTEST, :aSubTest, :aProg, statistic#, value, sysdate '+
      'from v$mystat where statistic# in (589, 590)';
    qry.ParamByName('aTest').AsString := aTest;
    qry.ParamByName('aSubTest').AsString := aSubTest;
    qry.ParamByName('aProg').AsString := aProg;
    qry.ExecSQL;
  finally
    qry.Free;
  end;
end;

procedure TfrmMain.btnStartClick(Sender: TObject);
var aTest: string;
  I: Integer;
begin
  aTest := IfThen(rgType.ItemIndex = 0, 'qry', 'stp');
  AddStats('begin test', aTest, 'Delphi');
  for I := 1 to 1000 do
  begin
    if rgType.ItemIndex = 0 then // run query
      RunQuery
    else
      RunStp;
  end;
  lbInfo.Items.Values['QRY Count'] := IntToStr(fCountQry);
  lbInfo.Items.Values['STP Count'] := IntToStr(fCountStp);
  AddStats('end test', aTest, 'Delphi');
end;

constructor TfrmMain.Create(AOwner: TComponent);
begin
  inherited Create(AOwner);
  fCountStp := 0;
  fCountQry := 0;
  sess.Connect;
end;

procedure TfrmMain.RunQuery;
var qry: TOraQuery;
begin
  inc(fCountQry);
  qry := TOraQuery.Create(Self);
  try
    qry.Session := sess;
    qry.SQL.Clear;
    Qry.SQL.Add('BEGIN');
    qry.SQL.Add('  -- query method');
    qry.SQL.Add('  STPINS(:IN_A, :IN_B); ');
    qry.SQL.Add('END;');
    qry.ParamByName('IN_A').AsFloat := Random(1000);
    qry.ParamByName('IN_B').AsFloat := Random(1000);
    qry.ExecSQL;
  finally
    qry.Free;
  end;
end;

procedure TfrmMain.RunStp;
var stp: TOraStoredProc;
begin
  inc(fCountStp);
  stp := TOraStoredProc.Create(Self);
  try
    stp.Session := sess;
    stp.StoredProcName := 'STPINS';
    stp.Prepare;
    stp.ParamByName('IN_A').AsFloat := Random(1000);
    stp.ParamByName('IN_B').AsFloat := Random(1000);
    stp.ExecProc;
  finally
    stp.Free;
  end;
end;

end.

the dfm code:

Code: Select all

object frmMain: TfrmMain
  Left = 0
  Top = 0
  Caption = 'Stored Procedure VERSUS Query'
  ClientHeight = 303
  ClientWidth = 538
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'Tahoma'
  Font.Style = []
  OldCreateOrder = False
  PixelsPerInch = 96
  TextHeight = 13
  object rgType: TRadioGroup
    Left = 32
    Top = 64
    Width = 185
    Height = 105
    Caption = 'rgType'
    ItemIndex = 0
    Items.Strings = (
      'Query'
      'Stored procedure')
    TabOrder = 0
  end
  object btnStart: TButton
    Left = 32
    Top = 9
    Width = 75
    Height = 25
    Caption = 'Start'
    TabOrder = 1
    OnClick = btnStartClick
  end
  object lbInfo: TListBox
    Left = 272
    Top = 32
    Width = 209
    Height = 209
    ItemHeight = 13
    TabOrder = 2
  end
  object sess: TOraSession
    Username = 'username'
    Password = 'password'
    Server = 'alias'
    LoginPrompt = False
    Left = 136
    Top = 216
  end
end

the query to retrieve the returned bytes:

Code: Select all

select n.name, t.stat#, t.prog, t.info2, max(t.value) - min(t.value) bytes 
from testdata t, v$statname n
where t.stat# = n.statistic# 
group by t.stat#, t.prog, t.info2, n.name
order by stat#,bytes;

Any ideas on how to reduce the traffic the stored procedure causes to return to the client ?

[Joachim]

The returning of so many bytes to the client is causing network problems.

The client is located in a remote location.
Statistical data is being sent to oracle across a 2Mb wan network.

The returning bytes are causing problems.
In my opinion, the volume of returning bytes being so high is not correct behavior.

Just imagine downloading a document from a website
and having 3 to 4 times more traffic upstream towards the website during the download

[AlexP]

Hello,

Your INSERT request does not insert data into the table, sinse the statistic # in (589, 590) condition is not met.

Try to use the latest version of ODAC 9.5.18, and also try to use the Direct mode and compare the results with the OCI mode.

[Joachim]

Really ?

I can not use direct mode in the production environment.
Connection is done via tns.
Thus, there is no point in testing this.

Try to use the last version of ODAC.
I can't: Devart deleted my customer account.
The devart website didn't even know my e-mail address anymore,
so resetting my password didn't work either.

Let me give some background info to make it easier to understand.

If the insert would fail, which would be indicated by Oracle, Delphi would have thrown an exception.
I didn't get exceptions. The count(*) currently reveals 55000 records.

Statistic number 589 and 590 are just 2 statistics which oracle maintains for every sessions.
These 2 specific statistics hold the value of how much bytes are sent and received for my session.

At the beginning of the test I record the values for these 2 statistics in a separate table.
At the end of the test I do the same.

This allows me to calculate the delta of those 2 values with a query on the database,
thus giving me the bytes sent and received by the client.

To my surprise:
The amount of bytes sent from oracle database to the client is greater than the amount of bytes sent from the client to oracle database.

What Devart should be doing is :
Create there own Delphi project.
I provided the code, so it shouldn't be too difficult.
Check what the stored procedure component is doing which causes so much bytes to be returned to the client.
Tell me which property I have to switch on or off on the connection object or stored procedure so this obvious bug does not happen.

The returned bytes should be less than the uploaded bytes.

Let me give a comparison of the problem:
Assume you upload a 100Mb file, and do nothing else.

Would you expect your network card to display these metrics afterwards ?
Uploaded traffic: 100Mb
Downloaded traffic: 700Mb

What we are doing:
Upload 100Mb using a stored procedure from client to oracle db.

What we are seeing in the oracle statistics:
bytes received by oracle db from client: 100Mb
bytes sent by oracle db to client: 700Mb

Can i make it any more clear ?

[AlexP]

This behavior is associated with that the Describe of the procedure will be called again in each iteration, if you bring the creation, naming and preparation outside the cycle, you will get results similar to the procedure call from the query.