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'
Performance Problems with different optimizer modes and vendor libs
-
- Posts: 2
- Joined: Tue 04 Jan 2005 08:53
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.
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.
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.
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;