Performance Problems with different optimizer modes and vendor libs

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for Oracle in Delphi and C++Builder
Post Reply
Andre' Brunner
Posts: 2
Joined: Tue 04 Jan 2005 08:53

Performance Problems with different optimizer modes and vendor libs

Post by Andre' Brunner » Thu 08 Sep 2005 05:36

Hello,

I have the following problem with DBExpress Driver for Oracle (Version
2.5.02):

I have a huge join over 11 tables with each about 10000 rows.
When I choose the /*+ RULE */ - hint the performance is fine in
both cases - Vendorlib OCI.dll and dbexpoda.dll!
When I choose the /*+ FIRST_ROWS */ - hint the performance is fine in
only the Vendorlib = OCI.dll case. And very bad with the dbexpoda.dll!

I am very surprised! Does the dbexpoda.dll somehow change the statement? The call is urgent because Oracle has depricated the RULE-Optimizer and I don't want to install the Oracle client on every client
of our custumers.

Please help!

Sincerely,

Andre'

Paul
Posts: 725
Joined: Thu 28 Oct 2004 14:06

Post by Paul » Fri 09 Sep 2005 07:33

DbxOda does not modify your query. It executes the query that was received from sqlExpress components.
DbxOda with "Oracle Net (Core Lab)" connection has worse performance than OCI connection. It is possible for some statements, because "Oracle Net (Core Lab)" uses different methods of working with Oracle server than OCI.

/*+ FIRST_ROWS */ is passed to server and you can see it after executing
EXPLAIN PLAN for this statement.

Guest

Post by Guest » Fri 09 Sep 2005 08:18

And now?

What can I do? I cannot want to switch to OCI or to /*+ rule */.

Is there a solution for this performace problems planned?

Sincerely,

Andre'

Guest

Post by Guest » Wed 02 Nov 2005 12:34

This thread should not die! I see no solution.

Paul
Posts: 725
Joined: Thu 28 Oct 2004 14:06

Post by Paul » Fri 04 Nov 2005 15:21

We cannot reproduce your problem with Oracle server 9.2.0.1. DbxOda 2.50.6, 2.50.5.
Oracle properly responds on /*+ FIRST_ROWS */ option with Net connection. You can see this in execution plan with the following example. The reason of the problem is probably somewhere else. Performance may get worse if there is some type conversion in fields or parameters WHERE clause. For example TIMESTAMP->DATE.
Please send us small sample to demonstrate it and include script to create server objects.

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
begin
  SQLQuery1.SQL.Text := 'truncate table plan_table';
  SQLQuery1.ExecSQL(false);
  SQLQuery1.SQL.Text := 'EXPLAIN PLAN FOR select /*+ FIRST_ROWS */  e.empno from dept d, emp e where e.deptno=e.deptno';
  SQLQuery1.ExecSQL(false);
  SimpleDataSet1.DataSet.CommandText := 'select operation, options from plan_table';
  SimpleDataSet1.Close;
  SimpleDataSet1.Open;
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
  SQLQuery1.SQL.Text := 'truncate table plan_table';
  SQLQuery1.ExecSQL(false);
  SQLQuery1.SQL.Text := 'EXPLAIN PLAN FOR select e.empno from dept d, emp e where e.deptno=e.deptno';
  SQLQuery1.ExecSQL(false);
  SimpleDataSet1.DataSet.CommandText := 'select operation, options from plan_table';
  SimpleDataSet1.Close;
  SimpleDataSet1.Open;
end;

Post Reply